MySQL
索引
索引实际上就是关键字和数据的映射关系，关键字是从数据中提取的用于标识、检索数据的特定内容
关键字相对于数据来说，关键字的数据量要小的多
关键字应该是有序的，二分查找可以快速确定位置
MySQL
中的索引类型可以分为普通索引、唯一索引、主键索引、全文索引和空间索引（针对空间类型的数据）
普通索引：对关键字没有限制
唯一索引：用于实现关键字不允许重复的约束
主键索引：要求主键唯一且不允许为空
基础命令
show create table
表名称
;
desc
表名称
;
创建索引
创建索引有
2
种方式：
创建表的同时建立对应索引，例如主键、唯一性约束
创建表之后建立索引
create table tb_users(
id bigint auto_increment, -- 实际会有报错
first_name varchar(16),
last_name varchar(16),
id_card varchar(18),
memo text,
primary key(id), -- 主键索引
key name1(first_name,last_name),
unique key(id_card),
fulltext key(memo)
);
create table tb_users(
id int auto_increment primary key, -- 建立了主键索引
username varchar(20) unique, -- 建立唯一性索引
memo text
);
alter table tb_users drop column username;
alter table tb_users add first_name varchar(16);
alter table tb_users add last_name varchar(16);
//一个列可以构成索引，索引也可以是由多个列构成
-- 建立一个复合索引，并且命名为name1
alter table tb_users add key name1(first_name,last_name);
-- 创建全文索引,具体存储采用倒排索引的方式，不支持中文分词。如果业务需要使用搜索引擎，建议使用ES
实现全文搜索
可以根据索引名称执行删除操作，对应的是普通索引、唯一索引、全文索引
语法：
alter table
表名称
drop key
索引名称
删除主键索引：
alter table
表名
drop primary key
，这里没有索引名称，因为一个表只能有一个主键，主键可以是多个列构成。需要注意的是：如果主键自增长，那么不能直接删除。因为auto_increment
需要依赖于主键索引
如果需要删除则必须先取消自增长，然后删除主键索引
实际上在具体开发中通常不会删除主键，因为主键有自然主键和代理主键两种，建议的使用的是代理主键，代理主键和业务规则无关。
执行计划
当执行
SQL
语句时，首先会分析、优化，形成执行计划，最后按照执行计划执行操作，然会操作结果集。可以通过explain
来分析
sql
执行前的执行计划。
查看查询操作的执行计划
alter table tb_users add fulltext(memo);
alter table tb_users drop key name1; -- 删除名称为name1的复合索引
alter table tb_users drop key id_card; -- 删除名称为id_card的唯一性索引
alter table tb_users drop key memo;
alter table tb_users modify id bigint;
alter table tb_users drop primary key;
create table tb_users(
id bigint primary key auto_increment,
username varchar(20) unique,
city varchar(10),
province varchar(10),
key name1 (province,city)
)
insert into tb_users values(null,'zhangsan','西安','陕西');
explain select * from tb_users where id<10
select_type
显示查询的类型
table
显示本次查询相关的表
partitions
查询匹配的分区
type
显示访问表使用哪种类型，
ALL
全表查询、
index
使用索引树、
range
给定范围查询
possible_keys
查询时可能会使用的索引
key
使用的索引
key_len
索引字段的长度
ref
使用索引列等值查询时，和索引列进行等值匹配的对象信息
rows
扫描出的行数
filtered
按照表条件过滤出的行百分比
Extra
执行形况的描述和说明
可以使用查看执行计划判断定义索引是否对特定查询起到优化查询效率的作用
这里表示没有任何索引生效，整表扫描查询数据
索引的使用场景
1
、针对经常需要搜索的列和主键列
alter table
tb_users
add
sex
char
(
1
)
default
'
男
'
;
explain select
*
from
tb_users
where
sex=
'
男
'
;
explain select
*
from
tb_users
where
id>
1
根据
id
查询记录，因为
id
列上创建了主键索引，因此
sql
语句执行的可选索引
possible_keys
只有主键索
引，如果有多个不同索引，最终会选择一个较好的作为检索的依据
2
、
order by
排序或者范围查询条件的列
执行
order by
排序操作时，如果该字段没有建立索引，那么执行计划会将查询出的所有数据使用外部排 序。操作很影响性能。可以使用 alter table
表名称
add index(
列名称
)
建立索引，由于索引本身就 是有序的，所以直接按照索引顺序逐条读取数据即可。如果还涉及分页操作时，只用获取索引表中某个 范围内的索引对应的数据，而不是获取所有数据
具体开发中做查询必做分页
逻辑分页就是将数据先全部加载到内存中，然后获取部分数据进行显示
物理分页就是查询时只获取所需要显式的数据，不显示的数据不加载。
select * from
tb_users limit
起始行号，每页行数
3
、需要进行连接查询
join
对
join
语句匹配关系
on
所涉及的字段建立索引可以提供连接查询的执行效率
索引覆盖
如果要查询的字段都建立了索引，那么存储引擎会直接在索引表中进行查询，而不会访问原始数据，这个叫做索引覆盖。因此需要尽可能在select
后写上必要查询的字段，以增加索引覆盖的几率
注意：不要在每个字段上建立索引，因为优先使用索引的优势在于索引的体积小。
针对一个表一般建议建立的索引数目不要超过
6
个，因为索引除了需要额外的存储空间外，还会对
数据的增删改性能造成影响
索引失效
在满足所用使用的情况下，例如
where
、
order by
、
join...on
或者索引覆盖，索引也不一定被使用
1
、字段要独立出现
2
、
like
查询
3
、复合索引要求对左边的字段有效，最左前缀匹配原则
具体的实现原理是先按照
first_name
中提取的关键字进行排序，如果无法确定先后时再按照从
last_name
中提取的关键排序，索引复合索引生效的前提是左端的列参与的查询
select * from tb_users where id=20-1; -- 会使用主键索引
select * from tb_users where id+1=20; -- 不会使用主键索引,针对列使用了表达式计算或者函
数
select * from tb_users where name like '%方%'; -- 由于使用%统配符开头，所以在name上
创建的索引无效，只能进行全表扫描，效率极低，实际开发中不建议使用；如果使用较为频繁建议引入第三方
的全文索引来实现
select * from tb_users where name like '方%'; -- name上创建的索引是有效
alter table tb_users add index(first_name,last_name);从查询条件 first_name=? and last_name=? 来说，针对first_name和last_name创建复合索引的效率
比分别创建first_name索引和last_name索引的查询效率高
4
、
or
条件查询
or
两边都有索引时索引生效，一边有一边没有会导致整个
sql
语句的全表扫描
5
、状态值不用索引
取值的可能性较少，例如
sex
或者订单状态之类的字段，这种字段即使创建索引，也往往利用不上，因为 一个状态值可能匹配大量的记录，这种情况下mysql
会认为索引比全表扫描的效率低，从而弃用索引。
如何创建索引
建立索引是以浪费空间为代价，换取高查询效率。建立索引会对修改操作的性能造成影响
1
、建立基础索引：在使用
where
、
order by
、
join
字段上建立索引
2
、优化组合索引：基于业务规则和业务逻辑
如果条件经常性的出现在一起，可以考虑将多个字段的索引升级为复合索引
如果添加个别字段的索引就可能出现索引覆盖，可以考虑为该字段建立索引
3
、如果查询时不常出现的索引应该删除掉
4
、建立索引后注意
sql
语句的使用
%
开头的模糊查询导致索引失效
索引列最好非空，
null
值不会出现在索引中。
sex boolean default 1
少用
not in
或者
!=
查询，
not in
可以使用
not exists
替代
不要针对列进行计算，针对列建议的索引无效
5
、使用
explain
查看执行计划，判断索引是否生效
前缀索引
一般希望索引表应该比具体的数据表要小。当建立索引时默认使用字段的全部内容建立索引，可以指定使用字段的前10
个字符建立索引，而不是整个字段内容。语法：
index(
列名称
(
长度
))
使用前缀索引的前提是：字段的前缀标识度比较高，例如口令比较适合创建前缀索引，因为各个密码几乎都不相同
使用前缀索引的难度就是如何定义前缀截取的长度
可以使用
select count(*)/count(distinct left(password,len))
进行比较，通过调整
len
值的大
小查看不同前缀的平均匹配度，接近
1
时就可以了
索引的具体实现
Innodb
存储引擎采用的是索引组织表，在
Innodb
中表数据文件本身就是按照
B+
树组织的一个索引结构。MyISAM
中主索引和辅助索引在结构上没有任何区别，只是主索引要求
key
值唯一的，而辅助索引的key值允许重复
select
*
from
tb_users
where
first_name=’zhangsan‘;
--
可以使用索引
select
*
from
tb_users
where
first_name=
'zhang'
and
last_name=
'san'
;
--
索引生效
select
*
from
tb_users
where
last_name=
"lisi"
;
--
无法利用索引
Innodb
存储引擎
在
innodb
中的数据文件本身就是按照主键聚集存放【聚集索引】，所以要求
innodb
中的数据表必须有主键，MyISAM
可以没有。如果没有显式指定主键，
mysql
会自动选择一个可以标识的列作为主键，如果不存在可以标识的列，mysql
自动为
innodb
表生成一个隐含字段充当主键，这个字段为
6B
辅助索引首先检索辅助索引表获取对应的主键，然后再用主键到主索引表中检索对应的数据
在
Innodb
中索引不建议使用过长的字段作为主键，因为所有的辅助索引都要使用主索引，过长的主索引 会导致辅助索引变大，建议使用整型数据充当主键，占用空间少，比较速度快。另外使用非单调的字段 充当主键不是个好主意，因为innodb
数据文件本身就是一个
B+
树，非单调的主键会造成插入新记录时数据文件为了维护B+
树的特性而出现频繁的分裂调整，十分低效。最佳实践；可以考虑使用
bigint auto_increment的代理主键
MyISAM
存储引擎
MyISAM
主索引使用的是
B+
树作为索引结构，叶子节点
data
域中存储的是数据记录的地址
MyISAM
辅助索引也是
B+
树，
data
域中保存数据记录的地址。
MyISAM
索引的检索算法是首先按照
B+
树的搜索算法查找对应的
key
，如果
key
存在，则获取存储在
data
域中的地址值，最后根据地址值获取对应行的数据，这种叶子节点不存储具体数据的方式叫做非聚集索引
hash
索引
在索引被载入内存中，使用
hash
结构存储数据
类似
HashMap
实现
Innodb
中的锁机制
使用数据库的目的在于数据共享，需要考虑数据并发访问问题。解决方案就是锁机制
锁主要包括全局锁、表锁、行锁、乐观锁和悲观锁，需要解决的问题是死锁
存储引擎
存储引擎定义
MySQL
中的数据、索引以及其它的数据库对象如何存储，是一套文件系统的实现
早期的面试题：如何选择
MyISAM
和
Innodb?
现在已经基本不存在这个问题，因为
MySQL
的
Innodb
存储引擎不端完善，各方面基本上都在赶超
MyISAM
，也是目前
MySQL
默认的引擎
查看所有的存储引擎
show engines
;
常见的存储引擎：
MyISAM
、
Innodb
、
memory
MyISAM
采用的是全表锁，具有较高的查询执行速度，不支持事务和外键，并发性能差，但是占用
空间相对较小，对于事务没有要求，一般主要以
select
和
insert
为主的应用可以使用这个引擎
Innodb
采用的是行级锁，可以提供具有提交、回滚和恢复能力的事务安全，支持自增长类，支持
外键约束，并发性能强，一般所需要占用的空间是
MyISAM
的
2.5
倍以上，处理效率相对差一些
Memory
采用的是全表锁，存储数据在内存中，速度快，但是占用和数据量成正比的内存空间，而
且数据在
MySQL
重启时丢失，默认使用
hash
索引，检索效率高，但是不适合范围查找，主要用于
缓存内容变化不频繁的表 archive 这种类型的表只支持
insert
、
select
，不支持
delete
、
update
、
replace
，不使用索引 csv这些表保存在服务器的单个文件中，它包含了用逗号间隔的数据。
Innodb
和
MyISAM
的区别
Innodb
支持事务，
MyISAM
不支持，
Innodb
针对每条
SQL
语句都默认封装为事务，自动提交，这样
会影响执行速度
Innodb
支持外键约束，
MyISAM
不支持。所以对于一个包含外键的
innodb
表转换为
MyISAM
会失败
Innodb
采用的时聚集索引，数据文件和索引绑定，必须有主键，通过主键查询效率很高；但是辅
助索引需要两次查询，因此在
Innodb
中主键不应该过大；
MyISAM
采用的是非聚集索引，所以数据
文件分离
MyISAM
147
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
