MySQL 索引分析与优化

缘在有你

于 2022-06-22 23:03:59 发布

阅读量139

点赞数

分类专栏： DBMS-MySQL数据库文章标签：数据库开发

本文链接：https://blog.csdn.net/chenxg1912/article/details/125417529

版权

DBMS-MySQL数据库专栏收录该内容

3 篇文章 0 订阅

订阅专栏

MySQL 索引分析与优化

EXPLAIN

MySQL 提供了一个 EXPLAIN 命令，它可以对 SELECT 语句进行分析，并输出 SELECT 执行的详细信息，供开发人员有针对性的优化。例如：

EXPLAIN SELECT * from table_name WHERE id < 3;

EXPLAIN 命令的输出内容大致如下：

字段名	值	说明
id	1
select_type	查询的类型
table	table_name
partitions	NULL
type	range
possible_keys	PRIMARY
key	PRIMARY
key_len	8
ref	null
rows	1
filtered	100.00
Extra	Using where

select_type: 表示查询的类型。常用的值如下：
- SIMPLE ：表示查询语句不包含子查询或union
- PRIMARY：表示此查询是最外层的查询
- UNION：表示此查询是UNION的第二个或后续的查询
- DEPENDENT UNION：UNION中的第二个或后续的查询语句，使用了外面查询结果
- UNION RESULT：UNION的结果
- SUBQUERY：SELECT子查询语句
- DEPENDENT SUBQUERY：SELECT子查询语句依赖外层查询的结果。

最常见的查询类型是SIMPLE，表示我们的查询没有子查询也没用到UNION查询。

type 表示存储引擎查询数据时采用的方式。比较重要的一个属性，通过它可以判断出查询是全表扫描还是基于索引的部分扫描。常用属性值如下，从上至下效率依次增强。
- ALL：表示全表扫描，性能最差。
- index：表示基于索引的全表扫描，先扫描索引再扫描全表数据。
- range：表示使用索引范围查询。使用>、>=、<、<=、in等等。
- ref：表示使用非唯一索引进行单值查询。
- eq_ref：一般情况下出现在多表join查询，表示前面表的每一个记录，都只能匹配后面表的一行结果。
- const：表示使用主键或唯一索引做等值查询，常量查询。
- NULL：表示不用访问表，速度最快。
possible_keys 表示查询时能够使用到的索引。注意并不一定会真正使用，显示的是索引名称。
key 表示查询时真正使用到的索引，显示的是索引名称。
rows MySQL查询优化器会根据统计信息，估算SQL要查询到结果需要扫描多少行记录。原则上rows是越少效率越高，可以直观的了解到SQL效率高低。
key_len 表示查询使用了索引的字节数量。可以判断是否全部使用了组合索引。 key_len的计算规则如下：
- 字符串类型字符串长度跟字符集有关：
  - latin1=1
  - gbk=2
  - utf8=3
  - utf8mb4=4
  - char(n)：n*字符集长度
  - varchar(n)：n * 字符集长度 + 2字节
- 数值类型
  - TINYINT：1个字节
  - SMALLINT：2个字节
  - MEDIUMINT：3个字节
  - INT、FLOAT：4个字节
  - BIGINT、DOUBLE：8个字节
- 时间类型
  - DATE：3个字节
  - TIMESTAMP：4个字节
  - DATETIME：8个字节
- 字段属性 NULL属性占用1个字节
- 如果一个字段设置了NOT NULL，则没有此项。
Extra Extra表示很多额外的信息，各种操作会在Extra提示相关信息，常见几种如下：
- Using where 表示查询需要通过索引回表查询数据。
- Using index 表示查询需要通过索引，索引就可以满足所需数据。
- Using filesort 表示查询出来的结果需要额外排序，数据量小在内存，大的话在磁盘，因此有Using filesort建议优化。
- Using temprorary 查询使用到了临时表，一般出现于去重、分组等操作。

回表查询

InnoDB索引有聚簇索引和辅助索引。聚簇索引的叶子节点存储行记录，InnoDB必须要有，且只有一个。辅助索引的叶子节点存储的是主键值和索引字段值，通过辅助索引无法直接定位行记录，通常情况下，需要扫码两遍索引树。先通过辅助索引定位主键值，然后再通过聚簇索引定位行记录，这就叫做回表查询，它的性能比扫一遍索引树低。总结：通过索引查询主键值，然后再去聚簇索引查询记录信息

覆盖索引

在MySQL官网，类似的说法出现在explain查询计划优化章节，即explain的输出结果Extra字段为Using index时，能够触发索引覆盖。
只需要在一棵索引树上就能获取SQL所需的所有列数据，无需回表，速度更快，这就叫做索引覆盖。实现索引覆盖最常见的方法就是：将被查询的字段，建立到组合索引。

最左前缀原则

复合索引使用时遵循最左前缀原则，最左前缀顾名思义，就是最左优先，即查询中使用到最左边的列，那么查询就会使用到索引，如果从索引的第二列开始查找，索引将失效。

常见问题

LIKE查询

MySQL在使用like模糊查询时，索引能不能起作用？
MySQL在使用Like模糊查询时，索引是可以被使用的，只有把%字符写在后面才会使用到索引。
select * from user where name like ‘%o%’; //不起作用
select * from user where name like ‘o%’; //起作用
select * from user where name like ‘%o’; //不起作用

NULL查询

如果MySQL表的某一列含有NULL值，那么包含该列的索引是否有效？
对MySQL来说，NULL是一个特殊的值，从概念上讲，NULL意味着“一个未知值”，它的处理方式与其他值有些不同。比如：不能使用=，<，>这样的运算符，对NULL做算术运算的结果都是NULL，count时不会包括NULL行等，NULL比空字符串需要更多的存储空间等。

NULL列需要增加额外空间来记录其值是否为NULL。对于MyISAM表，每一个空列额外占用一位，四舍五入到最接近的字节。

虽然MySQL可以在含有NULL的列上使用索引，但NULL和其他数据还是有区别的，不建议列上允许为 NULL。最好设置NOT NULL，并给一个默认值，比如0和 ‘’ 空字符串等，如果是datetime类型，也可以设置系统当前时间或某个固定的特殊值，例如’1970-01-01 00:00:00’

索引与排序

MySQL查询支持filesort和index两种方式的排序，filesort是先把结果查出，然后在缓存或磁盘进行排序操作，效率较低。使用index是指利用索引自动实现排序，不需另做排序操作，效率会比较高。 filesort有两种排序算法：双路排序和单路排序。双路排序：需要两次磁盘扫描读取，最终得到用户数据。第一次将排序字段读取出来，然后排序；第二次去读取其他字段数据。单路排序：从磁盘查询所需的所有列数据，然后在内存排序将结果返回。如果查询数据超出缓存 sort_buffer，会导致多次磁盘读取操作，并创建临时表，最后产生了多次IO，反而会增加负担。解决方案：少使用select *；增加sort_buffer_size容量和max_length_for_sort_data容量。

如果我们Explain分析SQL，结果中Extra属性显示Using filesort，表示使用了filesort排序方式，需要优化。如果Extra属性显示Using index时，表示覆盖索引，也表示所有操作在索引上完成，也可以使用 index排序方式，建议尽可能采用覆盖索引。

以下几种情况，会使用index方式的排序。
ORDER BY 子句索引列组合满足索引最左前列

explain select id from user order by id; //对应(id)、(id,name)索引有效

WHERE子句+ORDER BY子句索引列组合满足索引最左前列

explain select id from user where age=18 order by name; //对应 (age,name)索引

以下几种情况，会使用filesort方式的排序。
对索引列同时使用了ASC和DESC

explain select id from user order by age asc,name desc; //对应 (age,name)索引

WHERE子句和ORDER BY子句满足最左前缀，但where子句使用了范围查询（例如>、<、in等）

explain select id from user where age>10 order by name; //对应 (age,name)索引

ORDER BY或者WHERE+ORDER BY索引列没有满足索引最左前列

explain select id from user order by name; //对应(age,name)索引

使用了不同的索引，MySQL每次只采用一个索引，ORDER BY涉及了两个索引

explain select id from user order by name,age; //对应(name)、(age)两个索 引

WHERE子句与ORDER BY子句，使用了不同的索引

explain 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)索引

查询优化

慢查询定位

开启慢查询日志查看 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，否则不会奏效。

查看慢查询日志
- 文本方式查看直接使用文本编辑器打开slow.log日志即可。

time：日志记录的时间
User@Host：执行的用户及主机
Query_time：执行的时间
Lock_time：锁表时间
Rows_sent：发送给请求方的记录数，结果数量 Rows_examined：语句扫描的记录条数
SET timestamp：语句执行的时间点 select…：执行的具体的SQL语句

使用mysqldumpslow查看 MySQL 提供了一个慢查询日志分析工具mysqldumpslow，可以通过该工具分析慢查询日志内容。在 MySQL bin目录下执行下面命令可以查看该使用格式。

perl mysqldumpslow.pl --help

运行如下命令查看慢查询日志信息：

perl mysqldumpslow.pl -t 5 -s at C:\ProgramData\MySQL\Data\OAK-slow.log

除了使用mysqldumpslow工具，也可以使用第三方分析工具，比如pt-query-digest、mysqlsla等。

慢查询优化

索引和慢查询

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

如何判断是否应用了索引？ SQL语句是否使用了索引，可根据SQL语句执行过程中有没有用到表的索引，可通过 explain命令分析查看，检查结果中的 key 值，是否为NULL。

应用了索引是否一定快？看下面的语句：

select * from user where id>0;

虽然使用了索引，但是还是从主键索引的最左边的叶节点开始向右扫描整个索引树，进行了全表扫描，此时索引就失去了意义。而像 select * from user where id = 2; 这样的语句，才是我们平时说的使用了索引。它表示的意思是，我们使用了索引的快速搜索功能，并且有效地减少了扫描行数。

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

提高索引过滤性

假如有一个5000万记录的用户表，通过sex='男’索引过滤后，还需要定位3000万，SQL执行速度也不会很快。其实这个问题涉及到索引的过滤性，比如1万条记录利用索引过滤后定位10条、100条、1000条，那他们过滤性是不同的。索引过滤性与索引字段、表的数据量、表设计结构都有关系。案例：

表：student
字段：id,name,sex,age
造数据：insert into student (name,sex,age) select name,sex,age from student;
SQL案例：select * from student where age=18 and name like '张%';（全表扫 描）

优化1

alter table student add index(name); //追加name索引

优化2

alter table student add index(age,name); //追加age,name索引

优化3
可以看到，index condition pushdown 优化的效果还是很不错的。
再进一步优化，我们可以把名字的第一个字和年龄做一个联合索引，这里可以使用 MySQL 5.7 引入的虚拟列来实现。

//为user表添加first_name虚拟列，以及联合索引(first_name,age)
alter table student add first_name varchar(2) generated always as (left(name, 1)), add index(first_name, age);
explain select * from student where first_name='张' and age=18;

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

分页查询优化

一般性分页

一般的分页查询使用简单的 limit 子句就可以实现。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;

原因：使用了id做主键比较(id>=)，并且子查询使用了覆盖索引进行优化。

缘在有你

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
MySQL 索引分析与优化

MySQL 数据库索引分析与优化
复制链接

扫一扫

专栏目录

MySQL 索引分析与优化

MySQL 索引分析与优化

EXPLAIN

回表查询

覆盖索引

最左前缀原则

常见问题

LIKE查询

NULL查询

索引与排序

查询优化

慢查询定位

慢查询优化

索引和慢查询

提高索引过滤性

分页查询优化

一般性分页

分页优化方案

“相关推荐”对你有帮助么？