深入浅出MySQL索引

1. 什么是索引

索引（index）是帮助MySQL进行高效数据查找的一种数据结构，在数据库中，除了数据本身之外，还维护着满足特定查找算法的数据结构，这些数据结构通过某种方式指向数据，从而可以通过这些数据结构实现高效的查找，这种数据结构就是索引。

1.1 索引的优缺点

• 优点：
  1. 大大提高数据的查找效率，提升查找速度，降低MySQL的IO成本；
  2. 可以通过索引列进行数据排序，降低MySQL的排序成本，也减少CPU等的消耗
• 缺点：
  1. 索引也需要占用空间，所以，在添加索引后，除数据占用的空间外，还需要占用一部分空间来保存索引
  2. 索引提升了查寻（ select） 的效率，但是降低了增 （insert）、删（delete）、改（update）的效率

1.2 索引结构

MySQL的索引是在存储引擎层实现的，不同的存储引擎有不同的结构
InnoDB引擎则是采用的B+Tree索引

索引结构	描述
B+Tree索引	常见的索引类型，大部分存储引擎都支持B+Tree索引
Hash索引	底层的数据结构是使用哈希表实现，只有在精确匹配到索引列的查询时才有效，不支持范围查询（如：>、<、between 等）
R-Tree空间索引	空间索引时MyISAM存储引擎中的一种索引类型，主要用于地理空间数据类型，使用较少
Full-text全文索引	是通过简历倒排索引，快速匹配或查询文档的方式，类似于Elasticsearch

1.3 MySQL的存储引擎

这里介绍MySQL3种常见的存储引擎，如：InnoDB、MyISAM、Memory

1.3.1 InnoDB引擎

• 在MySQL5.5版本之后，是默认的存储引擎，是一种高可靠性、高性能的存储引擎
• innoDB支持事务、行级锁、外键约束

1.3.2 MyISAM引擎

MyISAM是MySQL早期的存储引擎，现在已经采用InnoDB做为默认存储引擎

• 访问速度块
• 不支持事务、行级锁、外键
• 支持表级锁

1.3.3 Memory引擎

是MySQL种基于内存的存储引擎，将数据保存到内存中，在发生断电或者其他故障的情况下，内存中的数据将会丢失，所以，一般作为缓存或者临时表使用

• 在内存中存放
• 默认使用Hash索引

1.3.4 存储引擎如何选择

• InnoDB：MySQL的默认存储引擎，支持事务、表级锁、外键，如果应用对事务的完整性具有一定要求，且在高并发下对数据的一致性也有要求，除了插入、查询等操作外，还包含很多删除、更新的操作，InnoDB是很合适的选择
• MyISAM：不支持事务、外键，只支持表级锁，不支持行级锁，如果对读、插入操作为主，对更新、删除的操作比较少，以及对事务的完整性、并发性要求不是很高，可以选择MyISAM（常被NoSQL：MongoDB替代）
• Memory：数据保存在内存中，访问速度快，常用来做为缓存使用，缺点是内存对空间大小有限制，太大的表无法保存到内存中，而且数据的安全性、完整性也无法保证，如果用作缓存，可以选择（常被NoSQL：Redis替代）

2. 索引的分类

分类	描述	特点	关键字
主键索引	针对表中的主键创建的索引	默认自动创建，有且仅有一个	PRIMARY
唯一索引	避免一列中的值重复，强制要求值不相同	可以有多个	UNIQUE
常规索引	快速查找特定的数据	可以有多个
全文索引	针对文本中的关键词查找，而不是比较索引的值	可以有多个	FULLTEXT

在向字段添加了唯一约束后，会自动创建唯一索引
其中，索引又可以分为：聚簇索引、非聚簇索引（二级索引）
聚簇索引一般指代主键索引，主键索引默认为聚簇索引，其他非主键索引，是二级索引或非聚簇索引

聚簇索引的选取规则

• 默认为表的主键，自动为主键创建聚簇索引
• 如果表中没有主键，自动为第一个创建的唯一索引做为聚簇索引
• 如果表中没有主键，没有唯一索引，那么InnoDB会自动生成一个隐藏的 rowid 作为聚簇索引

3. 索引的语法

查看索引

-- table 表示 表的名称
show index from table

创建索引

create [UNIQUE | FULLTEXT] index 索引名称 on table(字段名称)

删除索引

-- table 表示 表的名称
drop index 索引名称 on table

4. SQL性能分析

4.1 查看SQL的执行频率

使用 show status 命令查询 select、insert、update、delete等语句的执行频率

show [global | session] status like 'Com_______';

如图，select 执行了18次

4.2 慢查询日志

在MySQL中，可通过配置来记录执行较慢的SQL，从而来对SQL进行分析
通过以下命令来查询，慢查询日志功能是否开启

show variables like '%slow_query_log%';

• slow_query_log：OFF是关闭
• slow_query_log_file：表示日志的存储路径

慢查询日志记录了所有的SQL执行时间超过了指定参数（long_query_time 单位是秒，默认为10秒）的所有SQL语句的日志

在配置文件 /etc/my.cnf 中进行配置

# 开启慢查询日志开关
slow_query_log=1

# 设置慢日志的时间为2秒，如果SQL执行的时间超过了2秒，就会被当做慢SQL，就会进行慢查询日志的记录
long_query_time=2

4.3 profile详情

show profiles 能够帮助我们在做SQL优化时，告诉我们在SQL执行的过程中，时间具体耗费到哪里去了

查看自己的MySQL是否支持profile，使用以下命令：

select @@have_profiling

• Yes表示支持，如果查询出来不支持，很可能是MySQL版本过旧

查看此功能是否已开启，使用以下命令：

-- 查看是否开启，0：关闭；1：开启
select @@profiling;

• 1：表示已开启

如果没有开启，使用以下命令开启：

-- 开启
set profiling = 1;

在执行了一系列的SQL之后，使用 show profiles 查看耗时情况

查看指定 Query_ID 的各阶段耗时情况

# 查看指定的query_id的SQL语句各个阶段的耗时情况
show profile for query query_id;

如：show profile for query 32 ，查询query_id是32的各阶段耗时

4.4 explain执行计划

查看SQL语句的执行计划，只需要在SQL的最前方添加 explain 关键字运行即可

-- 如下
explain select * from table

执行计划中各字段表示的含义：

• id：select查询的序列号，表示查询中操作表，或者select的执行顺序；id相同，从上到下执行，id不同，id越大越先执行
• select_type：查询类型，常见的有：
  • SIMPLE：简单查询，即不使用连接查询或子查询
  • PRIMARY：主查询
  • UNION：union中后面的查询语句
  • SUBQUERY：select、where中包含了子查询
• type：连接类型，性能：null > system > const > eq_ref > ref > range > index > all。null一般在业务中不会出现（在查询不涉及到表时，会出现），const在查询时涉及到了主键索引或者唯一索引会出现
• possible_key：可能用到的索引，可能一个，可能多个
• key：实际用到的索引，如果没有用到索引，值为null
• key_len：索引的长度
• rows：MySQL认为必须要执行的查询行数，是一个估计值，可能不总是准确
• filtered：返回结果的行数与读取行数的百分比，值越大越好
• Extra：额外信息
  • using index condition：查询使用了索引，但是需要回表查询数据
  • using where，using index：查询使用了索引，但是需要的数据全部能在索引列中找到，不需要回表查询

5. 索引的使用

5.1 最左前缀法则

最左前缀法则是指：查询从最左列开始，并且中间不跳过索引列，如果跳过了某一列，后面的索引字段将会失效。如果建立了联合索引（多列索引），要遵循最左前缀法则

5.2 索引失效的情况

• 使用了范围查询（>、<），范围查询右侧的索引将会失效，为避免失效，在业务允许的范围下，使用 >=、<= 的方式
• 在索引列上使用了函数运算，导致索引失效
• 字符串类型的数据，在查询时不加引号，索引失效
• 模糊查询，尾部模糊查询，索引不会失效；头部模糊查询，索引失效
• or分隔开的条件，如果一侧有索引，一侧没有索引，索引失效
• 数据分布影响，如果MySQL评估使用索引会比使用全表扫描更慢，则不会使用索引，会直接使用全表扫描

5.3 SQL提示

SQL提示是优化数据库的一个重要的手段，就是在SQL语句中，加入一些人为的操作，来达到优化的目的

• use index（索引名称）：建议MySQL使用指定的索引，实际使不使用在MySQL评估后，进行选择
• ignore index（索引名称）：忽略指定的索引，将指定的索引进行忽略
• force index（索引名称）：强制使用指定的索引，强制MySQL使用指定的索引

例如：

select * from table use index(idx_table_id) where id = 1;

select * from table ignore index(idx_table_id) where id = 1;

select * from table force index(idx_table_id) where id = 1;

5.4 覆盖索引

覆盖索引：查询使用了索引，并在查询返回的字段列上，在索引字段中全部能找到
注意： 如果查询返回的字段，没有在索引字段中找到，则需要回表查询，回表查询的效率会比直接通过索引查询的效率低
所以，在查询中，减少使用 select * ，因为 select * 非常容易造成回表查询

5.5 单列索引&联合索引

单列索引：一个索引只包含一列

联合索引：一个索引包含多个列

在业务场景中，如果根据多个查询条件进行查询，考虑建立联合索引，而非单列索引

在多条件联合查询时（如果多个条件都各自建立了单列索引），MySQL优化器会评估哪个索引字段的效率更高，从而选择该索引完成查询，也就是只用到其中一个索引，进而导致回表查询

6. 索引设计原则

• 针对数据量大的数据，且查询比较频繁的字段创建索引
• 常作为查询条件（where）、排序条件（order by）、分组条件（group by）的字段建立索引
• 选择区分度高的字段建立索引，尽量建立唯一索引，区分度越高，效率越高
• 如果是字符串类型，且长度比较长，可以考虑建立前缀索引
• 尽量使用联合索引，减少单列索引，联合索引很多时候可以避免回表查询
• 控制索引的数量，不是越多越好，索引越多，维护代价越大，增删改的效率越低
• 如果索引列不能存储null值，在创建表的时候，使用 not null 非空约束。当MySQL的优化器直到每列是否包含null值时，可以更好地确定哪个索引对查询更有效