MySQL数据库|MySQL数据库二（MySQL索引） mysql|sql|数据库

一、索引原理
MySQL官方对索引定义：是存储引擎用于快速查找记录的一种数据结构。需要额外开辟空间和数据维护工作。

索引是物理数据页存储，在数据文件中（InnoDB，ibd文件），利用数据页(page)存储。
索引可以加快检索速度，但是同时也会降低增删改操作速度，索引维护需要代价。

1. 索引分类 a. 普通索引
最基本的索引类型，基于普通字段建立的索引，没有任何限制
b. 唯一索引
索引字段的值必须唯一，但允许有空值。在创建或修改表时追加唯一约束，就会自动创建对应的唯一索引。
c. 主键索引
一种特殊的唯一索引，不允许有空值。在创建或修改表时追加主键约束即可，每个表只能有一个主键。
d. 复合索引
在多个列上建立索引，这种索引叫做组复合索引（组合索引）。复合索引可以代替多个单一索引，相比多个单一索引复合索引所需的开销更小。
e. 全文索引
查询操作在数据量比较少时，可以使用like模糊查询，但是对于大量的文本数据检索，效率很低。如果使用全文索引，查询速度会比like快很多倍。
和常用的like模糊查询不同，全文索引有自己的语法格式，使用 match 和 against 关键字

select * from user where match(name) against('aaa');

全文索引使用注意事项：

全文索引必须在字符串、文本字段上建立。
全文索引字段值必须在最小字符和最大字符之间的才会有效。（innodb：3-84；myisam：4-84）
全文索引字段值要进行切词处理，按syntax字符进行切割，例如b+aaa，切分成b和aaa
全文索引匹配查询，默认使用的是等值匹配，例如a匹配a，不会匹配ab,ac。如果想匹配可以在布尔模式下搜索a*
select * from user where match(name) against('a*' in boolean mode);

2. 二分查找法二分查找法也叫作折半查找法，它是在有序数组中查找指定数据的搜索算法。它的优点是等值查询、范围查询性能优秀，缺点是更新数据、新增数据、删除数据维护成本高。
具体过程是：

首先定位left和right两个指针
计算(left+right)/2得到索引位置，将索引位置值与目标值的大小比对
索引位置值大于目标值就将索引位置-1，并将right移动到索引位置
如果小于目标值就将索引位置+1，并将left移动到索引位置

文章图片

文章图片

文章图片

文章图片

3. Hash索引 Hash索引底层是由Hash表来实现的，是根据键值存储数据的结构。非常适合根据key查找value值，也就是单个key查询，或者说等值查询，但是对于范围查询就需要全表扫描了。

文章图片

InnoDB 自适应哈希索引：
InnoDB存储引擎会监控表上各个索引页的查询，当InnoDB注意到某些索引值访问非常频繁时，会在内存中基于B+Tree索引再创建一个哈希索引，使得内存中的 B+Tree 索引具备哈希索引的功能，即能够快速定值访问频繁访问的索引页。
4. B+树索引 MySQL数据库索引采用的是B+Tree结构，它在B-Tree结构上做了优化改造。
B树 vs B+树
B树结构：

索引值和data数据分布在整棵树结构中
每个节点可以存放多个索引值及对应的data数据
树节点中的多个索引值从左到右升序排列

文章图片

B树的搜索：

从根节点开始，对节点内的索引值序列采用二分法查找，如果命中就结束查找。没有命中会进入子节点重复查找过程，直到所对应的的节点指针为空，或已经是叶子节点了才结束。

B+树结构：

非叶子节点不存储data数据，只存储索引值，这样便于存储更多的索引值
叶子节点包含了所有的索引值和data数据
叶子节点用指针连接，提高区间的访问性能

文章图片

B+树的搜索：

相比B树，B+树进行范围查找时，只需要查找定位两个节点的索引值，然后利用叶子节点的指针进行遍历即可。而B树需要遍历范围内所有的节点和数据，显然B+Tree效率高。

5. 聚簇索引和辅助索引聚簇索引 vs 非聚簇索引

B+Tree的叶子节点存放主键索引值和行记录就属于聚簇索引
如果索引值和行记录分开存放就属于非聚簇索引。

主键索引 vs 辅助索引

B+Tree的叶子节点存放的是主键字段值就属于主键索引
如果存放的是非主键值就属于辅助索

InnoDB的索引
在InnoDB中，主键索引采用的是聚簇索引，非主键索引采用辅助索引
对于主键索引，B+Tree的叶子节点就是行记录，行记录和主键值紧凑地存储在一起。这也意味着 InnoDB 的主键索引就是数据表本身，它按主键顺序存放了整张表的数据，占用的空间就是整个表数据量的大小。
InnoDB的表要求必须要有聚簇索引：

如果表定义了主键，则主键索引就是聚簇索引
如果表没有定义主键，则第一个非空unique列作为聚簇索引
否则InnoDB会建一个隐藏的row-id作为聚簇索引

对于非主键索引，采用辅助索引，也叫作二级索引，是根据索引列构建 B+Tree结构。但在 B+Tree 的叶子节点中只存了索引列和主键的信息。二级索引占用的空间会比聚簇索引小很多，通常创建辅助索引就是为了提升查询效率。一个表InnoDB只能创建一个聚簇索引，但可以创建多个辅助索引。

文章图片

MyISAM的索引
MyISAM的主键和非主键索引采用的都是非聚簇索引。

文章图片

二、查询分析
1. EXPLAIN命令 MySQL 提供了一个 EXPLAIN 命令，它可以对 SELECT 语句进行分析，并输出 SELECT 执行的详细信息，供开发人员有针对性的优化。
EXPLAIN 命令执行后包含以下参数：
a. select_type
【MySQL数据库|MySQL数据库二（MySQL索引）】表示查询的类型。常用的值如下：

SIMPLE ：表示查询语句不包含子查询或union
PRIMARY：表示此查询是最外层的查询
UNION：表示此查询是UNION的第二个或后续的查询
EXPLAIN SELECT * from user WHERE id < 3;
DEPENDENT UNION：UNION中的第二个或后续的查询语句，使用了外面查询结果
UNION RESULT：UNION的结果
SUBQUERY：SELECT子查询语句
DEPENDENT SUBQUERY：SELECT子查询语句依赖外层查询的结果。

b. type
表示存储引擎查询数据时采用的方式，常用属性值如下，从上至下效率依次增强：

ALL：表示全表扫描，性能最差。
index：表示基于索引的全表扫描，先扫描索引再扫描全表数据。
range：表示使用索引范围查询。使用>、>=、<、<=、in等等。
ref：表示使用非唯一索引进行单值查询。
eq_ref：一般情况下出现在多表join查询，表示前面表的每一个记录，都只能匹配后面表的一行结果。
const：表示使用主键或唯一索引做等值查询，常量查询。
NULL：表示不用访问表，速度最快。

c. possible_keys
表示查询时能够使用到的索引。注意并不一定会真正使用，显示的是索引名称
d. key
表示查询时真正使用到的索引，显示的是索引名称
e. rows
MySQL查询优化器会根据统计信息，估算SQL要查询到结果需要扫描多少行记录。原则上rows是越少效率越高，可以直观的了解到SQL效率高低
f. key_len
表示查询使用了索引的字节数量。可以判断是否全部使用了组合索引。
g. Extra
表示额外的信息，常见几种如下：

Using where：表示查询需要通过索引回表查询数据。
Using index：表示查询需要通过索引，索引就可以满足所需数据。
Using filesort：表示查询出来的结果需要额外排序，数据量小在内存，大的话在磁盘，因此有Using filesor建议优化。
Using temprorary：查询使用到了临时表，一般出现于去重、分组等操作

2. 回表查询 InnoDB辅助索引的叶子节点存储的是主键值和索引字段值，通过辅助索引无法直接定位行记录，通常情况下，需要扫码两遍索引树。
先通过辅助索引定位主键值，然后再通过聚簇索引定位行记录，这就叫做回表查
3. 覆盖索引只需要在一棵索引树上就能获取SQL所需的所有列数据，无需回表，速度更快，这就叫做索引覆盖。
实现索引覆盖最常见的方法就是：将被查询的字段，建立到组合索引
4. 最左前缀原则复合索引使用时遵循最左前缀原则，即查询中使用到最左边的列，那么查询就会使用到索引，如果从索引的第二列开始查找，索引将失效。

文章图片

5. Like查询 MySQL在使用Like模糊查询时，索引是可以被使用的，但只有把%字符写在后面才会使用到索引。

select * from user where name like '%o%'; //不起作用 select * from user where name like 'o%'; //起作用 select * from user where name like '%o'; //不起作用

6. NULL查询对MySQL来说，NULL是一个特殊的值，从概念上讲，NULL意味着“一个未知值”，它的处理方式与其他值有些不同。比如：不能使用=，<，>这样的运算符，对NULL做算术运算的结果都是NULL，count时不会包括NULL行等，NULL比空字符串需要更多的存储空间等。
虽然MySQL可以在含有NULL的列上使用索引，但NULL和其他数据还是有区别的，不建议列上允许为NULL。最好设置NOT NULL，并给一个默认值，比如0和 ‘’ 空字符串等，如果是datetime类型，也可以设置系统当前时间或某个固定的特殊值，例如’1970-01-01 00:00:00’。
7. 索引与排序 MySQL查询支持filesort和index两种方式的排序，filesort是先把结果查出，然后在缓存或磁盘进行排序操作，效率较低。使用index是指利用索引自动实现排序，不需另做排序操作，效率会比较高
以下几种情况，会使用index方式的排序：

ORDER BY 子句索引列组合满足索引最左前列
select id from user order by id; //对应(id)、(id,name)索引有效
WHERE子句+ORDER BY子句索引列组合满足索引最左前列
select id from user where age=18 order by name; //对应(age,name)索引

以下几种情况，会使用filesort方式的排序：

对索引列同时使用了ASC和DESC
select id from user order by age asc,name desc; //对应(age,name)索引
WHERE子句和ORDER BY子句满足最左前缀，但where子句使用了范围查询（例如>、<、in等）
select id from user where age>10 order by name; //对应(age,name)索引
ORDER BY或者WHERE+ORDER BY索引列没有满足索引最左前列
select id from user order by name; //对应(age,name)索引
使用了不同的索引，MySQL每次只采用一个索引，ORDER BY涉及了两个索引
select id from user order by name,age; //对应(name)、(age)两个索引
WHERE子句与ORDER BY子句，使用了不同的索引
select id from user where name='tom' order by age; //对应(name)、(age)索引
WHERE子句或者ORDER BY子句中索引列使用了表达式，包括函数表达式
explain select id from user order by abs(age); //对应(age)索引

三、查询优化
1. 慢查询的定位查看 MySQL 数据库是否开启了慢查询日志和慢查询日志文件的存储位置的命令如下：

SHOW VARIABLES LIKE 'slow_query_log%'

通过如下命令开启慢查询日志：

SET global slow_query_log = ON; SET global slow_query_log_file = 'OAK-slow.log'; SET global log_queries_not_using_indexes = ON; SET long_query_time = 10;

long_query_time：指定慢查询的阀值，单位秒。如果SQL执行时间超过阀值，就属于慢查询记录到日志文件中。
log_queries_not_using_indexes：表示会记录没有使用索引的查询SQL。前提是slow_query_log的值为ON，否则不会奏效。

开启慢查询日志后，定位到慢查询日志的文件，即可进行慢查询的分析
2. 慢查询的优化如何定义慢查询？

MySQL判断一条语句是否为慢查询语句，主要依据SQL语句的执行时间，它把当前语句的执行时间跟 long_query_time 参数做比较，如果语句的执行时间 > long_query_time，就会把这条执行语句记录到慢查询日志里面。long_query_time 参数的默认值是 10s，该参数值可以根据自己的业务需要进行调整。

确定慢查询之后，即可利用EXPLAIN命令对查询进行分析。
有索引一定查询快吗？

查询是否使用索引，只是表示一个SQL语句的执行过程；而是否为慢查询，是由它执行的时间决定的，也就是说是否使用了索引和是否是慢查询两者之间没有必然的联系。
在使用索引时，不要只关注是否起作用，应该关心索引是否减少了查询扫描的数据行数，如果
扫描行数减少了，效率才会得到提

慢查询原因总结：

全表扫描：explain分析type属性all
全索引扫描：explain分析type属性index
索引过滤性不好：靠索引字段选型、数据量和状态、表设计
频繁的回表查询开销：尽量少用select *，使用覆盖索引

3. 分页查询的优化分页查询使用简单的 limit 子句就可以实现：

SELECT * FROM 表名 LIMIT [offset,] rows

第一个参数指定第一个返回记录行的偏移量，注意从0开始；
第二个参数指定返回记录行的最大数目；
如果只给定一个参数，它表示返回最大的记录行数目；

偏移量固定，返回记录量对执行时间的影响

select * from user limit 10000,1; select * from user limit 10000,10; select * from user limit 10000,100; select * from user limit 10000,1000; select * from user limit 10000,10000;

在查询记录时，返回记录量低于100条，查询时间基本没有变化，差距不大。随着查询记录
量越大，所花费的时间也会越来越多

果查询偏移量变化，返回记录数固定对执行时间的影响

select * from user limit 1,100; select * from user limit 10,100; select * from user limit 100,100; select * from user limit 1000,100; select * from user limit 10000,100;

在查询记录时，如果查询记录量相同，偏移量超过100后就开始随着偏移量增大，查询时间
急剧的增加

分页查询的优化：

第一步：利用覆盖索引优化
select * from user limit 10000,100; select id from user limit 10000,100;
第二步：利用子查询优化
select * from user limit 10000,100; select * from user where id>= (select id from user limit 10000,1) limit 100;