MySql索引优化-白红宇

MySql索引优化

阅读量：5010 次

发布时间：2019-06-12

本文共 3374 字，大约阅读时间需要 11 分钟。

在开发中我们经常免不了在业务量大的时候，需要针对数据库的庞大数据量进行优化，以满足响应的效率，今天我们来谈一谈索引的优化。

一、索引的数据结构

mysql主要使用 B-tree 平衡树，索引分为聚簇索引与非聚簇索引，其中：

聚簇索引：索引的叶节点指向数据

非聚簇索引：索引的叶节点指向数据的引用

常用的引擎myisam使用非聚簇索引，innodb使用聚簇索引

对于innodb引擎：

主键索引既存储索引值，又在叶中存储行数据

如果没有主键，则会使用 unique key 做主键

如果没有unique，则mysql会生成一个rowid做主键

其优点在于查询数据少时，无须回行，而缺点则是不规则插入数据，频繁的页分裂

二、索引类型

1. 主键索引

primary key() 要求关键字不能重复，也不能为null,同时增加主键约束

主键索引定义时，不能命名

2. 唯一索引

unique index() 要求关键字不能重复，同时增加唯一约束

3. 普通索引

index() 对关键字没有要求

4. 全文索引

fulltext key() 关键字的来源不是所有字段的数据，而是字段中提取的特别关键字（可以是某个字段或多个字段，多个字段称为复合索引）

1 建表： 2 creat table student( 3     stu_id int unsigned not null auto_increment, 4     name varchar(32) not null default '', 5     phone char(11) not null default '', 6     stu_code varchar(32) not null default '', 7     stu_desc text, 8     primary key ('stu_id'),     //主键索引 9     unique index 'stu_code' ('stu_code'), //唯一索引10     index 'name_phone' ('name','phone'),  //普通索引，复合索引11     fulltext index 'stu_desc' ('stu_desc'), //全文索引12 ) engine=myisam charset=utf8;13 14 更新：15 alert table student16     add primary key ('stu_id'),     //主键索引17     add unique index 'stu_code' ('stu_code'), //唯一索引18     add index 'name_phone' ('name','phone'),  //普通索引，复合索引19     add fulltext index 'stu_desc' ('stu_desc'); //全文索引20 21 删除：22 alert table sutdent23     drop primary key,24     drop index 'stu_code',25     drop index 'name_phone',26     drop index 'stu_desc';

三、索引使用原则

1. 列独立

保证索引包含的字段独立在查询语句中，不能是在表达式中

2. 左前缀

like:匹配模式左边不能以通配符开始，才能使用索引

注意：前缀索引在排序 order by 和分组 group by 操作的时候无法使用。

3. 复合索引由左到右生效

建立联合索引，要同时考虑列查询的频率和列的区分度。

例如：index（a,b,c）

注：or的两边都有存在可用的索引，该语句才能用索引。

4. 不要滥用索引，多余的索引会降低读写性能

即使满足了上述原则，mysql还是可能会弃用索引，因为有些查询即使使用索引，也会出现大量的随机io，相对于从数据记录中的顺序io开销更大。

四、存在索引但不能使用索引的典型场景

有些时候虽然有索引，但是并不被优化器选择使用，下面举例几个不能使用索引的场景。

1.以%开头的 like 查询不能利用 B-Tree 索引，执行计划中 key 的值为 null 表示没有使用索引, 因为 B-Tree 索引的结构，所以以%开头的插叙很自然就没法利用索引了。一般推荐使用全文索引（Fulltext）来解决类似的全文检索的问题。或者考虑利用 innodb 的表都是聚簇表的特点，采取一种轻量级别的解决方式：一般情况下，索引都会比表小，扫描索引要比扫描表更快，而Innodb 表上二级索引 idx_last_name 实际上存储字段 last_name 还有主键,那么理想的访问应该是首先扫描二级索引 idx_last_name 获得满足条件的last_name like '%NI%' 的主键列表，之后根据主键回表去检索记录，这样访问避开了全表扫描数据表产生的大量 IO 请求。

2. 复合索引的情况下，假如查询条件不包含索引列最左边部分，即不满足最左原则 leftmost，是不会使用复合索引的。

3. 如果 MySQL 估计使用索引比全表扫描更慢，则不使用索引。

4. 用 or 分割开的条件，如果 or 前的条件中的列有索引，而后面的列中没有索引，那么涉及的索引都不会被用到。