MySQL索引及索引优化分析

一、前言

在介绍索引之前我们先讲讲SQL性能下降的原因，以及SQL在MySQL中的执行顺序

1. SQL慢性能下降，执行时间长，等待时间长的原因？

• SQL语言写的不好，造成性能下降
• 索引失效导致的
• 关联查询太多join
• 服务器调优及其各参数设置

2. SQL执行顺序

2.1 手写

 select 【distinct】查询列表
	from 表名
	join 表名
	on 连接条件
	where 筛选条件
	group by 字段
	having 筛选条件
	order by 字段 asc/desc
	limit (page-1)size  size

2.2 机读

from 表名
	on 连接条件
	join 表名
	where 筛选条件
	group by 字段
	having 筛选条件
	select  【distinct】 查询列表
	order by 字段 asc/desc
	limit (page-1)size  size

2.3 7种join

二、索引简介

1. 定义

• 索引是帮助MySQL的高效获取数据的数据结构，即索引是数据结构
• 可以理解为排好序的快速查找数据结构，数据本身之外，数据库还维护着一个满足特定查找的数据结构，这些数据结构以某种方式指向数据，这样就可以在这些数据结构的基础上实现高级查找算法，这种数据结构就是索引
• 一般索引本身也很大，不可能全部存储在内存中，因此索引以索引文件的形式存储在磁盘中
• 我们平时说的索引，如果没有特别指明，都是指B树结构组织的索引，其中聚集索引，次要索引，覆盖索引，复合索引，前缀索引，唯一索引默认都是使用B+树索引

2. 索引的优缺点

2.1 优势

• 提高数据检索的效率，降低数据库IO的成本
• 提高数据排序的效率，降低CPU的消耗

2.2 劣势

• 实际上索引也是一张表，该表保存了主键与索引字段并指向实体表的记录，所以索引列也需要占用空间
• 虽然索引大大提高了查询速度，同时确会降低更新表的速度，如对表进行insert、update和delete，因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件每次更新添加了索引列的字段，都会调整因为更新所带来的键值变化后的索引信息
• 索引只是提高效率的一个因素，如果你的MySQL有大数据量的表，就需要花时间研究建立最优秀的索引，或者优化查询语句

3. 索引分类

• 单值索引：一个索引只包含单个列，一个表可以有多个单列索引
• 唯一索引：索引列的值必须唯一，但允许有空值
• 复合索引：即一个索引包含多个列

4. MySQL索引结构

• BTree 索引
• Hash索引
• full-text全文索引
• R-Tree索引

5. 基本语法

5.1 创建

create index 索引名 on 表名(列名)

alter table 表名 add 【unique】index 索引名 on 列名

5.2 删除

drop index 索引名 on 表名

5.3 查看

show index from 表名【\G】g代表格式化，可加可不加

6. 哪些情况需要创建索引

• 主键自动创建唯一索引
• 频繁作为查询条件的字段应该创建索引
• 外键关系建立索引
• 查询中排序的字段，创建索引会大大提高排序速度
• 查询中统计或者分组字段
• 在高并发下倾向复合索引

7. 哪些情况不需要创建索引

• 表记录太少
• 经常增删改的表—提高了查询速度，同时降低了更新表的速度
• 数据重复且分布平均的表字段，例如性别这样的字段，只有男或女，这样的字段建立索引就没有太大的实际效果
• where中用不到的字段不创建索引

三、索引优化

1. MySQL常见瓶颈

• CPU：CPU在饱和的时候一般发生在数据装入内存或从磁盘上读取数据的时候
• IO：磁盘I/O瓶颈发生在装入数据远大于内存容量的时候
• 服务器硬件的性能瓶颈：top，free、iostat、vmstat查看系统的性能状态

2. explain关键字

2.1 定义

• 使用explain关键字可以模拟优化器执行SQL查询语句，从而知道MySQL是如何处理你的SQL语句的，分析你的查询语句或表结构的性能瓶颈

2.2 如何使用

explain SQL语句

2.3 执行计划包含的信息

2.4 名称解释

• id

1. select查询的序列号，包含一组数字，表示查询中执行select子句或操作表的顺序
2. 三种情况

• id相同，执行顺序由上至下
• id不同，如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行
• id相同不同同时存在，也按上俩条规则执行

• select_type

查询类型

• simple 用于简单的select查询，不包含子查询或者union查询
• primary 查询包含子查询，标记最外层查询
• subquery 在select或where列表中包含了子查询
• derived 在from列表中包含的子查询被标记为derived
• union 第二个select出现在union之后，被标记为derived
• union result 从union表获取结构的select

• table

• 显示这一行的数据是关于哪个表的

• type

all	index	range	ref	eq_ref	const	system	NULL
将遍历全表找到匹配的行	index只遍历索引树，通常比all快，因为索引文件通常比数据文件小	只检索给定范围的行。使用一个索引来选择行，key列显示了使用了哪个索引，一般就是你的where语句中出现了between、<、>、in等查询	非唯一性索引扫描，返回匹配某个单独值的所有行	唯一性索引扫描，对应每个索引值，表中只有一条数据与之匹配，常见于主键或唯一索引扫描	表示通过索引一次就找到了，const用于比较primary key或者unique索引。因为只匹配一行数据，所以很快	表只有一条记录，这是const类型的特列，平时不会出现，可以忽略

从最好到最差依次是

• system>const>eq_ref>ref>range>index>all

一般来说，得保证查询至少达到range级别

• possible_keys

• 显示可能应用在这张表中的索引，一个或多个

• key

• 实际使用的索引，如果为null，则没有使用索引
• 查询中若使用了覆盖索引，则该索引和查询的select字段重合

• key_len

• 表示索引使用的字节数，可通过该列计算查询索引使用的长度，在不损失精确性的情况下，长度越短越好
• key_len显示的值为索引字段的最大可能长度，并非实际长度，即key_len是根据表定义计算而得，不是通过表内检索出的

• ref

• 显示索引那一列被使用了，如果可能的话是一个常数。哪些列或常量被用于查找索引列上的值

• rows

• 根据表统计信息及索引选用情况，大致估算出找到所需记录所需要读取的行数

• extra

using filesort (坏)	using temporary(坏)	using index(好)
MySQL会对数据使用一个外部的索引排序，而不是按照索引顺序进行读取，统称为文件排序	使用了临时表保存中间结果，MySQL在对查询结果排序时使用临时表，常见于order by和group by	select操作使用了覆盖索引，避免访问了表的数据行，效率不错。 如果同时出现了using where，表明索引被用来执行键的查找 如果没有同时出现using where，表明索引用来读取数据而非执行查找动作

注意：上面三个比较重要

using where	using join buffer	impossible where	select tables optimized away	distinct
表明使用了where过滤	使用了连接缓存	where子句的值总为false，不能用来获取任何元组	在没有group by子句的情况下，基于索引优化min、max操作	优化distinct操作，找到第一个匹配的元组后即停止找同样值的动作

3.索引失效如何避免

• 全值匹配
• 最佳左前缀法则
• 不要在索引列上做任何操作
• 存储引擎不能使用索引中范围条件右边的列
• 尽量使用覆盖索引
• MySQL在使用不等于的时候无法使用索引会导致全盘扫描
• is null，is not null也无法使用索引
• like以通配符开头会导致索引失效
• 字符串不加单引号索引失效
• 少用or，用它来连接时会索引失效

4.如何解决like索引失效问题

create database bigdata;
use bigdata;

create table user(
	id int primary key auto_increment,
    name varchar(20),
    age int,
    gender varchar(20)
);

insert into user(name,age,gender) values
('11av11','20','1'),
('22av22','20','1'),
('33av33','20','1'),
('44av44','20','1');

alter table user add index user_nameage(name,age);

首先创建一个表，插入一些数据，建立索引，进行试验

explain select * from user where name like 'av%';
explain select name from user where name like 'av%';
explain select age from user where name like 'av%';
explain select gender from user where name like 'av%';
explain select name,age from user where name like 'av%';
explain select name,gender from user where name like 'av%';
explain select age,gender from user where name like 'av%';
explain select name,age,gender from user where name like 'av%';

这几条语句我们发现都使用了索引，通过分析如果like通配符不在第一位是%，都会遵循普通索引的规则，相当于一个范围

explain select * from user where name like '%av%';-----失效
explain select name from user where name like '%av%';
explain select age from user where name like '%av%';
explain select gender from user where name like '%av%';
explain select name,age from user where name like '%av%';
explain select name,gender from user where name like '%av%';
explain select age,gender from user where name like '%av%';
explain select name,age,gender from user where name like '%av%';

这几条运行之后，只要查询列表中包含了gender字段都索引失效了，查询列表必须与索引列表一致

下面我们重新创建索引，将gender也加入列表

运行上面的SQL语句，发现都使用了SQL语句，都使用了索引，通过分析，字段必须完全覆盖，则索引不会失效

总结：右边有%，遵循索引的规则，要想使用左右都有%，必须使用覆盖索引

5.查询优化

5.1永远小表驱动大表

• 当进行连接查询时，使用数据量小的去驱动数据量大的表
• 连接查询时将索引建到从表上
• 当外层为小表时，使用exists的效率大于使用in的效率

5.2 order by 关键字优化

• order by子句，尽量使用index方式排序，避免使用FileSort方式排序

满足这俩种情况，会使用index方式排序

• order by语句使用索引最左前缀
• 使用where子句与order子句条件列组合满足最左前缀

• 尽可能在索引列上完成排序操作，遵照索引键的最佳左前缀
• 如果不在索引列上，filesort有俩种算法
  双路排序和单路排序

双路排序

• MySQL4.1之前使用双路排序，俩次扫描磁盘，最终得到数据，读取行指针和order by列，对他们进行排序，然后扫描已经排序好的列表，按照列表中的值重新从列表中读取对应的数据输出
• 从磁盘取排序字段，在buffer进行排序，在从磁盘读取其他字段

取一批数据，要对磁盘进行俩次扫描，I/O很耗时，即出现单路排序对其进行改进
单路排序

• 从磁盘查出需要的所有列，按照order by列在buffer对他们进行排序，然后扫描排序后的列表进行输出
• 它的效率更快一些，避免了第二次读取数据，并且把随机IO变成了顺序IO，但是需要更多的空间

• 单路是后出的，总体而言好过多路，但是可能sortbuffer空间不够使用，数据大于sort buffer空间时，就需要多次读取IO

优化策略

• 增大sort_buffer_size参数的设置
• 增大max_length_for_sort_date参数的设置

5.3 group by关键字优化

• group by实质是先排序后分组，遵循索引的最佳左前缀
• 当无法使用索引列，增大max_length_for_sort_date参数的设置，增大sort_buffer_size参数的设置
• where高于having，能写在where的条件就不要写在having