Mysql调优之索引优化

Mysql调优之索引优化

1. 什么是索引

索引是为了加速对表中数据行的检索而创建的一种分散的存储结构。索引是针对表而建立的，它是由数据页面以外的索引页面组成的，每个索引页面中的行都会含有逻辑指针，以便加速检索物理数据。

2. 索引的优缺点

1. 优点

1. 能减少查询时服务器需要扫描的数据量，大大提高性能。
2. 能够减少排序和临时表的创建。
3. 能够将服务器随机IO变成顺序IO。

2. 缺点

1. 需要占用额外的存储空间。
2. 增删改查操作的时候需要动态维护索引，降低了数据的维护速度。

3. 索引分类

索引语法：

创建	CREATE [UNIQUE ] INDEX [index_name] ON table_name(column))
删除	DROP INDEX [indexName] ON table_name;
查看	SHOW INDEX FROM table_name\G

1. 主键索引

在建表的时候指定了主键，就自动创建主键索引。
主键索引也是唯一索引，但是不允许有空值。
创建方式1：创建表的时候指定主键。
创建方式2：

ALTER TABLE `table_name` ADD PRIMARY KEY ( `column` )

2. 唯一索引

唯一索引是一种特殊的普通索引，允许有空值，但是不允许重复。
创建方式1：建表时指定唯一键。
创建方式2：

ALTER TABLE `table_name` ADD UNIQUE  [indexName] (`column`)

创建方式3：

CREATE UNIQUE INDEX `index_name` ON table_name (`column_name`)

3. 普通索引

最基本的索引，没任何限制。
创建方式1:

ALTER TABLE `table_name` ADD INDEX index_name ( `column` )

创建方式2:

CREATE INDEX index_name ON table_name (column_name)

4. 全文索引

对文本的内容进行分词，进行搜索。在 mysql 中全文索引不支持中文。
创建方式1:

ALTER TABLE `table_name` ADD FULLTEXT ( `column` )

5. 组合索引

多个列组成的一个索引，专门用于组合搜索，其效率大于索引合并。
创建方式:

ALTER TABLE `table_name` ADD INDEX `index_name` ( `column1`, `column2`, `column3` )

4. 相关优化

1. 聚集索引

聚集索引就是基于主键创建的索引，又被称为聚簇索引。InnoDB的默认数据结构是聚集索引，并不是一种单独的索引类型，而是一种数据存储方式。
当表有了聚集索引的时候，表的数据行都存放在索引树的叶子页中。无法把数据行放到两个不同的地方，所以一张表只允许有一个聚簇索引。InnoDB的聚集索引实际上是将索引和数据保存中同一个B-Tree中。InnoDB通过主键聚集数据，如果没有定义主键，InnoDB会选择第一个唯一的的非空索引代替。如果没有这样的索引，InnoDB会定义一个隐藏的row_id来作为聚集索引。

1. 优点

1. 可以把相关数据保存在一起
2. 数据访问更快，因为索引和数据保存在同一个树中
3. 使用覆盖索引扫描的查询可以直接使用页节点中的主键值

2. 缺点

1. 聚集索引最大限度地提高了IO密集型应用的性能，如果数据在内存里，则聚集索引就无效了。
2. 插入速度严重依赖于插入顺序，按照主键的顺序插入是最快的方式
3. 更新聚集索引列的代价很高，因为会强制将每个被更新的行移动到新的位置
4. 基于聚集索引的表在插入新行，或者主键被更新导致需要移动行的时候，可能面临页分裂的问题
5. 聚簇索引可能会导致全表扫描变慢，尤其是行比较稀疏，或者由于页分裂导致数据存储不连续的时候。
6. 一张表只能有一个聚集索引

2. 非聚集索引

非聚集索引又被称为二级索引，二级索引中叶子结点保存的是行的主键值，当通过非聚集索引查找数据时，首先会查找到叶子结点的主键值，然后根据主键值去聚集索引中查找数据行，需要两次查找。
数据文件跟索引文件分开存放

1. 优点

1. 一张表可以有多个非聚集索引

2.缺点

1. 需要两次查询（俗称回表），效率相对聚集索引较低。

1. 回表

使用二级索引进行查找时，会查找到主键，然后在根据主键进行查找，就产生了回表。

2. 覆盖索引

只需要在一棵索引树上就能获取SQL所需的所有列数据，无需回表，速度更快。
查询字段都在索引中（可以创建组合索引）。
explain的输出结果Extra字段为Using index时，能够触发索引覆盖。

3. 最左匹配

查询字段与索引字段顺序的不同会导致索引无法充分使用，甚至索引失效！
原因：使用复合索引，需要遵循最佳左前缀法则，即如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列。
过滤条件要使用索引必须按照索引建立时的顺序，依次满足，一旦跳过某个字段，索引后面的字段都无法被使用。

4. 索引下推

索引下推是5.6版本的特性，是在非主键索引上的优化，能够减少回表次数，提高查询效率。
使用explain查看是否使用索引下推，当Extra列的值为Using index condition，则表示使用了索引下推。
创建索引：

CREATE INDEX zh_index  ON employees.employees (first_name,last_name,gender);

使用索引下推：

过程：在索引遍历的过程中，对索引字段先做判断，过滤掉不符合条件的索引项，也就是判断last_name 是否为Facello，如果不为这个值，那么就跳过，这个过程回表1次。
不使用索引下推过程：
如果不使用索引下推，那么将先匹配到first_name为G开头的，然后将匹配到的结果回表找到数据，然后再执行last_name='Facello’的条件。

5. 适合创建索引的情况

1. 主键自动建立唯一索引；
2. 频繁作为查询条件的字段应该创建索引
3. 查询中与其它表关联的字段，外键关系建立索引
4. 单键/组合索引的选择问题， 组合索引性价比更高
5. 查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度
6. 查询中统计或者分组字段

6. 不适合创建索引的情况

1. 表记录太少
2. 经常增删改的表或者字段
3. Where条件里用不到的字段不创建索引
4. 过滤性不好的不适合建索引

5. 索引的数据结构

1. B+Tree索引

Innodb引擎采用的就是这种类型的索引，它能够加快访问数据的速度，因为不需要进行全表扫描来获取需要的内容，从索引的根节点开始进行搜索，且根节点的槽中存放了下一个子节点的指针，根据这些指针再向下层查找。最后存储引擎要么找到对应的值，要么找不到。而这里的节点(叶子节点)它们的指针指向被索引的数据，而不是其它的节点页

正是由于B+Tree的特性，因此在索引匹配规则里，最左匹配原则才会更快。

2. 哈希索引

哈希索引是均匀哈希表实现的，只有精确匹配到索引所有列的查询才有小，对于每一行数据，存储引擎都会对所有的索引列计算一个哈希码，哈希码是一个较小的值，并且不同键值的行计算出来的哈希码也不以昂，哈希索引将所有的哈希码存储在索引中，同事在哈希表中保存指向每个数据行的指针。
在MySQL中，只有Memory引擎显示支持哈希索引，并且是默认索引类型，Memory引擎也支持B-Tree索引。

6. 索引匹配规则

1. 全值匹配

全值匹配指的是和索引中的所有列进行匹配
	explain select * from staffs where name = 'July' and age = '23' and pos = 'dev';

2. 匹配最左前缀

只匹配前面的几列
	explain select * from staffs where name = 'July' and age = '23';
	explain select * from staffs where name = 'July';

3. 匹配列前缀

可以匹配某一列的值的开头部分
	explain select * from staffs where name like 'J%';
	explain select * from staffs where name like '%y';

4. 匹配范围值

可以查找某一个范围的数据
	explain select * from staffs where name > 'Mary';

5. 精确匹配某一列并范围匹配另外一列

可以查询第一列的全部和第二列的部分
	explain select * from staffs where name = 'July' and age > 25;

6. 只访问索引的查询

查询的时候只需要访问索引，不需要访问数据行，本质上就是覆盖索引
	explain select name,age,pos from staffs where name = 'July' and age = 25 and pos = 'dev';

7. 单表索引失效的情况

1. 不要在索引列上做任何操作，包括加减乘除或字符串操作以及类型转换。
2. 索引列上有范围查询时，右边的列将失效。
3. 使用不等于的时候索引将失效。
4. is not null 不能使用索引，is null 可以
5. like以通配符开头或下划线开头则不能使用索引。
6. 字符串不加单引号不能走索引。

举例：假设index(a,b,c)；

Where语句	索引是否被使用
where a = 3	Y,使用到a
where a = 3 and b = 5	Y,使用到a，b
where a = 3 and b = 5 and c = 4	Y,使用到a,b,c
where b = 3 或者 where b = 3 and c = 4 或者 where c = 4	8.0之后会用 Using index for skip scan
where a = 3 and c = 5	使用到a， 但是c不可以，b中间断了
where a = 3 and b > 4 and c = 5	使用到a和b， c不能用在范围之后，b断了
where a is null and b is not null	is null 支持索引 但是is not null 不支持,所以 a 可以使用索引,但是 b不可以使用
where a <> 3	不能使用索引
where abs(a) =3	不能使用 索引
where a = 3 and b like ‘kk%’ and c = 4	Y,使用到a,b,c
where a = 3 and b like ‘%kk’ and c = 4	Y,只用到a
where a = 3 and b like ‘%kk%’ and c = 4	Y,只用到a
where a = 3 and b like ‘k%kk%’ and c = 4	Y,使用到a,b,c