MySQL索引优化

最新推荐文章于 2022-08-01 00:30:41 发布

四月丶丶

最新推荐文章于 2022-08-01 00:30:41 发布

阅读量122

点赞数

分类专栏： MySQL 文章标签： mysql

本文链接：https://blog.csdn.net/rzpy_qifengxiaoyue/article/details/108561673

版权

MySQL 专栏收录该内容

22 篇文章 3 订阅

订阅专栏

1. 回表查询

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

2. 覆盖索引

只需要在一棵索引树上就能获取SQL所需的所有列数据，无需回表，速度更快，这就叫做索引覆盖。
实现索引覆盖最常见的方法就是:将被查询的字段，建立到组合索引。

3. 最左前缀原则

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

4. LIKE查询

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

5. 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’。

6. 索引与排序

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

7. 查询优化

7.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，否则不会奏效。

7.2 查看慢查询日志

直接vim打开slow.log日志即可。

[root@rpp data]# cat rpp-slow.log 
/usr/local/mysql/bin/mysqld, Version: 5.7.14 (MySQL Community Server (GPL)). started with:
Tcp port: 3306  Unix socket: /usr/local/mysql/mysql.sock
Time                 Id Command    Argument
# Time: 2020-06-14T06:24:35.677773Z
# User@Host: root[root] @  [111.199.185.51]  Id:    98
# Query_time: 0.000069  Lock_time: 0.000000 Rows_sent: 0  Rows_examined: 0
use test;
SET timestamp=1592115875;
set long_query_time=0;
# Time: 2020-06-14T06:24:39.772303Z
# User@Host: root[root] @  [111.199.185.51]  Id:    98
# Query_time: 0.394501  Lock_time: 0.000121 Rows_sent: 10001  Rows_examined: 10001
SET timestamp=1592115879;
select * from t where a between 10000 and 20000;
# Time: 2020-06-14T06:24:44.908519Z
# User@Host: root[root] @  [111.199.185.51]  Id:    98
# Query_time: 0.125640  Lock_time: 0.000097 Rows_sent: 10001  Rows_examined: 10001
SET timestamp=1592115884;
select * from t force index(a) where a between 10000 and 20000;

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

使用mysqldumpslow查看

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

[root@rpp bin]# ./mysqldumpslow --help   
Usage: mysqldumpslow [ OPTS... ] [ LOGS... ]

Parse and summarize the MySQL slow query log. Options are

  --verbose    verbose
  --debug      debug
  --help       write this text to standard output

  -v           verbose
  -d           debug
  -s ORDER     what to sort by (al, at, ar, c, l, r, t), 'at' is default
                al: average lock time
                ar: average rows sent
                at: average query time
                 c: count
                 l: lock time
                 r: rows sent
                 t: query time  
  -r           reverse the sort order (largest last instead of first)
  -t NUM       just show the top n queries
  -a           don't abstract all numbers to N and strings to 'S'
  -n NUM       abstract numbers with at least n digits within names
  -g PATTERN   grep: only consider stmts that include this string
  -h HOSTNAME  hostname of db server for *-slow.log filename (can be wildcard),
               default is '*', i.e. match all
  -i NAME      name of server instance (if using mysql.server startup script)
  -l           don't subtract lock time from total time

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

[root@rpp bin]# ./mysqldumpslow -t 5 -s at ../data/rpp-slow.log 

Reading mysql slow query log from ../data/rpp-slow.log
Count: 1  Time=0.00s (0s)  Lock=0.00s (0s)  Rows=0.0 (0), 0users@0hosts
  Time: N-N-14T06:N:N.908519Z
  # User@Host: root[root] @  [N.N.N.N]  Id:    N
  # Query_time: N.N  Lock_time: N.N Rows_sent: N  Rows_examined: N
  SET timestamp=N;
  select * from t force index(a) where a between N and N

Count: 1  Time=0.00s (0s)  Lock=0.00s (0s)  Rows=0.0 (0), 0users@0hosts
  Time: N-N-14T06:N:N.772303Z
  # User@Host: root[root] @  [N.N.N.N]  Id:    N
  # Query_time: N.N  Lock_time: N.N Rows_sent: N  Rows_examined: N
  SET timestamp=N;
  select * from t where a between N and N

Count: 1  Time=0.00s (0s)  Lock=0.00s (0s)  Rows=0.0 (0), 0users@0hosts
  # Time: N-N-14T06:N:N.677773Z
  # User@Host: root[root] @  [N.N.N.N]  Id:    N
  # Query_time: N.N  Lock_time: N.N Rows_sent: N  Rows_examined: N
  use test;
  SET timestamp=N;
  set long_query_time=N

Died at mysqldumpslow line 161, <> chunk 3.

7.3 慢查询优化

如何判断是否为慢查询?
MySQL判断一条语句是否为慢查询语句，主要依据SQL语句的执行时间，它把当前语句的执行时间跟 long_query_time 参数做比较，如果语句的执行时间 > long_query_time，就会把这条执行语句记录到慢查询日志里面。long_query_time 参数的默认值是 10s，该参数值可以根据自己的业务需要进行调整。
如何判断是否应用了索引?
SQL语句是否使用了索引，可根据SQL语句执行过程中有没有用到表的索引，可通过 explain命令分析查看，检查结果中的 key 值，是否为NULL。
应用了索引是否一定快?
下面我们来看看下面语句的 explain 的结果，你觉得这条语句有用上索引吗?

select * from user where id>0;

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

查询是否使用索引，只是表示一个SQL语句的执行过程;而是否为慢查询，是由它执行的时间决定的，也就是说是否使用了索引和是否是慢查询两者之间没有必然的联系。

我们在使用索引时，不要只关注是否起作用，应该关心索引是否减少了查询扫描的数据行数，如果扫描行数减少了，效率才会得到提升。对于一个大表，不止要创建索引，还要考虑索引过滤性，过滤性好，执行速度才会快。

提高索引过滤性
假如有一个5000万记录的用户表，通过sex='男’索引过滤后，还需要定位3000万，SQL执行速度也不会很快。其实这个问题涉及到索引的过滤性，比如1万条记录利用索引过滤后定位10条、100 条、1000条，那他们过滤性是不同的。索引过滤性与索引字段、表的数据量、表设计结构都有关系。
下面我们看一个案例:
表:tbl_user
字段:id,age,name
造数据:insert into tbl_user (age,name) select age,name from tbl_user; 如下

mysql> insert into tbl_user (age,name) select age,name from tbl_user;
Query OK, 32 rows affected (0.01 sec)
Records: 32  Duplicates: 0  Warnings: 0
mysql> insert into tbl_user (age,name) select age,name from tbl_user;
Query OK, 64 rows affected (0.01 sec)
Records: 64  Duplicates: 0  Warnings: 0
mysql> 
mysql> insert into tbl_user (age,name) select age,name from tbl_user;
Query OK, 128 rows affected (0.01 sec)
Records: 128  Duplicates: 0  Warnings: 0
mysql> insert into tbl_user (age,name) select age,name from tbl_user;
Query OK, 256 rows affected (0.01 sec)
Records: 256  Duplicates: 0  Warnings: 0
mysql> insert into tbl_user (age,name) select age,name from tbl_user;
Query OK, 512 rows affected (0.02 sec)
Records: 512  Duplicates: 0  Warnings: 0
mysql> insert into tbl_user (age,name) select age,name from tbl_user;
Query OK, 1024 rows affected (0.03 sec)
Records: 1024  Duplicates: 0  Warnings: 0
mysql> insert into tbl_user (age,name) select age,name from tbl_user;
Query OK, 2048 rows affected (0.03 sec)
Records: 2048  Duplicates: 0  Warnings: 0
mysql> insert into tbl_user (age,name) select age,name from tbl_user;
Query OK, 4096 rows affected (0.06 sec)
Records: 4096  Duplicates: 0  Warnings: 0
mysql> insert into tbl_user (age,name) select age,name from tbl_user;
Query OK, 8192 rows affected (0.25 sec)
Records: 8192  Duplicates: 0  Warnings: 0
mysql> insert into tbl_user (age,name) select age,name from tbl_user;
Query OK, 16384 rows affected (0.11 sec)
Records: 16384  Duplicates: 0  Warnings: 0
mysql> insert into tbl_user (age,name) select age,name from tbl_user;
Query OK, 32768 rows affected (0.18 sec)
Records: 32768  Duplicates: 0  Warnings: 0
mysql> insert into tbl_user (age,name) select age,name from tbl_user;
Query OK, 65536 rows affected (0.39 sec)
Records: 65536  Duplicates: 0  Warnings: 0

SQL案例:select * from tbl_user where age=18 and name like ‘张%’;(全表扫描)

优化1

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

优化2

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

优化3
通过上面的优化可以达到不错的效果。再进一步优化，我们可以把名字的第一个字和年龄做一个联合索引，这里可以使用 MySQL 5.7 引入的虚拟列来实现。

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

慢查询原因总结

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

7.4 分页查询优化

一般的分页查询使用简单的 limit 子句就可以实现。limit格式如下:

SELECT * FROM 表名 LIMIT [offset,] rows

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

思考1:如果偏移量固定，返回记录量对执行时间有什么影响?

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

通过如下设置可以查看之前的查询耗时情况

mysql> show variables like 'profiling';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| profiling     | OFF   |
+---------------+-------+
1 row in set (0.01 sec)
 
mysql> set profiling=1;
Query OK, 0 rows affected (0.01 sec)
 
mysql> show variables like 'profiling';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| profiling     | ON    |
+---------------+-------+
1 row in set (0.01 sec)

查看sql耗时

mysql> show profiles;
+----------+------------+------------------------------------------+
| Query_ID | Duration   | Query                                    |
+----------+------------+------------------------------------------+
|        1 | 0.00137700 | show variables like 'profiling'          |
|        2 | 0.00224300 | select * from tbl_user limit 10000,1     |
|        3 | 0.00217400 | select * from tbl_user limit 10000,10    |
|        4 | 0.00228075 | select * from tbl_user limit 10000,100   |
|        5 | 0.00268900 | select * from tbl_user limit 10000,1000  |
|        6 | 0.00680325 | select * from tbl_user limit 10000,10000 |
+----------+------------+------------------------------------------+
6 rows in set (0.01 sec)

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

思考2:如果查询偏移量变化，返回记录数固定对执行时间有什么影响?

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

mysql> show profiles;
+----------+------------+------------------------------------------+
| Query_ID | Duration   | Query                                    |
+----------+------------+------------------------------------------+
|        1 | 0.00137700 | show variables like 'profiling'          |
|        2 | 0.00224300 | select * from tbl_user limit 10000,1     |
|        3 | 0.00217400 | select * from tbl_user limit 10000,10    |
|        4 | 0.00228075 | select * from tbl_user limit 10000,100   |
|        5 | 0.00268900 | select * from tbl_user limit 10000,1000  |
|        6 | 0.00680325 | select * from tbl_user limit 10000,10000 |
|        7 | 0.00034050 | select * from tbl_user limit 1,100       |
|        8 | 0.00032850 | select * from tbl_user limit 10,100      |
|        9 | 0.00035300 | select * from tbl_user limit 100,100     |
|       10 | 0.00050275 | select * from tbl_user limit 1000,100    |
|       11 | 0.00271375 | select * from tbl_user limit 10000,100   |
+----------+------------+------------------------------------------+
11 rows in set (0.01 sec)

结果:在查询记录时，如果查询记录量相同，偏移量超过100后就开始随着偏移量增大，查询时间急剧的增加。(这种分页查询机制，每次都会从数据库第一条记录开始扫描，越往后查询越慢，而且查询的数据越多，也会拖慢总查询速度。)

分页优化方案

第一步:利用覆盖索引优化

select * from tbl_user limit 10000,100;
select id from tbl_user limit 10000,100;

第二步:利用子查询优化

select * from tbl_user limit 10000,100;
select * from tbl_user where id>= (select id from tbl_user limit 10000,1) limit 100;

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

四月丶丶

关注

0
点赞
踩
0

收藏

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

1. 回表查询InnoDB索引有聚簇索引和辅助索引。聚簇索引的叶子节点存储行记录，InnoDB必须要有，且只有一个。辅助索引的叶子节点存储的是主键值和索引字段值，通过辅助索引无法直接定位行记录，通常情况下，需要扫码两遍索引树。先通过辅助索引定位主键值，然后再通过聚簇索引定位行记录，这就叫做回表查询，它的性能比扫一遍索引树低。2. 覆盖索引只需要在一棵索引树上就能获取SQL所需的所有列数据，无需回表，速度更快，这就叫做索引覆盖。实现索引覆盖最常见的方法就是:将被查询的字段，建立到组合索引。3.
复制链接

扫一扫

专栏目录