MySQL索引优化

MySQL索引

索引：排好序的快速查询的数据结构

存储引擎的不同，索引的数据类型也是不同，Innodb的索引B+树结构

数据都在树的叶子节点上

建表语句

create table T(
id int primary key, 
k int not null, 
name varchar(16),
index (k));

设置值：

(id,k)值分别为 (100,1)、(200,2)、(300,3)、(500,5) 和 (600,6)

id索引是主键索引，叶子节点就是一行数据

k索引是二级索引，叶子节点是主键值

回表

select * from T where id = 200;  //查询主键索引就可以查询到
select * from T where k = 2;  //查询k索引，拿到主键值，再到主键表中查询数据，叫回表

索引维护

B+树需要维护索引的有序性，新插入数据的时候，需要对索引做维护

索引树的节点是一页，一页是16K

页分裂

如果新插入的数据是在中间的值，比如插入id=400的，如果所在页已经满了，就需要申请一个新的数据页，挪动部分数据到新的页上，叫页分裂

页分裂影响性能和页的使用率

页合并

相邻的两个页数据有删除，利用率很低的时候，将数据页做合并

使用自增主键可以防止页分裂
使用逻辑删除可以防止页合并

覆盖索引

覆盖索引：尽可能减少回表查询

select * from T where k = 3;  //会回表查询

select id from T where k = 3;  //id在k索引上已经有了，就不用回表查询了，这是覆盖索引

建立联合索引k和name

select id,name from T where k = 3;  //也不用回表，联合索引上已经有需要的数据了

最左匹配

建立了多个字端的联合索引，比如name和age

select * from T where name like "穆%" and age = 10;

name索引是可以用上的，但是age字段用不到，范围之后失效

有了联合索引，单独的name索引就可以不用建立了，少维护一个索引，节省空间

索引下推

在最左匹配的时候

建立联合索引name 和age

select * from T where name like "张%" and age = 10;

5.6版本之前，查询到“张”开头的数据，然后回表查询数据行，然后比对字段值

5.6版本引入索引下推优化，对索引中包含的字段进行比较，直接过滤不满足条件的记录，减少回表次数

只需要回表2次就可以