MySQL底层概述—7.优化原则及慢查询
大纲
1.Explain概述
2.Explain详解
3.索引优化数据准备
4.索引优化原则详解
5.慢查询设置与测试
6.慢查询SQL优化思路
1.Explain概述
使用Explain关键字可以模拟查询优化器来执行SQL查询语句，从而知道MySQL是如何处理SQL语句的，从而分析出查询语句和表结构的性能瓶颈。
MySQL查询过程：
通过Explain可以获得以下信息：
一.表的读取顺序
二.数据读取操作的操作类型
三.哪些索引可以被使用
四.哪些索引真正被使用
五.表的直接引用
六.每张表的有多少行被优化器查询了
Explain使用方式：Explain + SQL语句，通过执行Explain可以获得SQL语句执行的相关信息。
EXPLAIN SELECT * FROM L1;
2.Explain详解
(1)数据准备
(2)ID字段说明
(3)select_type和table字段说明
(4)type字段说明
(5)possible_keys与key说明
(6)key_len字段说明
(7)ref字段说明
(8)rows字段说明
(9)filtered字段说明
(10)extra字段说明
(1)数据准备
-- 创建数据库CREATE DATABASE test CHARACTER SET 'utf8';-- 创建表CREATE TABLE L1(id INT PRIMARY KEY AUTO_INCREMENT,title VARCHAR(100) );CREATE TABLE L2(id INT PRIMARY KEY AUTO_INCREMENT,title VARCHAR(100) );CREATE TABLE L3(id INT PRIMARY KEY AUTO_INCREMENT,title VARCHAR(100) );CREATE TABLE L4(id INT PRIMARY KEY AUTO_INCREMENT,title VARCHAR(100) );-- 每张表插入3条数据INSERT INTO L1(title) VALUES('test001'),('test002'),('test003');INSERT INTO L2(title) VALUES('test004'),('test005'),('test006');INSERT INTO L3(title) VALUES('test007'),('test008'),('test009');INSERT INTO L4(title) VALUES('test010'),('test011'),('test012');
(2)ID字段说明
ID字段代表SELECT查询的序列号，它是一组数字，表示的是查询或操作表的顺序。
一.ID相同，执行顺序就是由上至下
EXPLAIN SELECT * FROM L1,L2,L3 WHERE L1.id=L2.id AND L2.id = L3.id;
二.ID不同，如果有子查询，ID号会递增
ID值越大优先级越高，越先被执行。
EXPLAIN SELECT * FROM L2 WHERE id = (
SELECT id FROM L1 WHERE id = (SELECT L3.id FROM L3 WHERE L3.title = 'test007')
);
(3)select_type和table字段说明
select_type表示查询类型，主要用于区别普通查询还是子查询等，table表示被操作的表。
一.SIMPLE：简单的SELECT查询，查询中不包含子查询或者UNION
EXPLAIN SELECT * FROM L1 where id = 1;
二.PRIMARY：在有子查询的情况下，最外层被标记为PRIMARY
三.SUBQUERY：在SELECT或WHERE列表中包含了子查询
EXPLAIN SELECT * FROM L2 WHERE id = (
SELECT id FROM L1 WHERE id = (SELECT L3.id FROM L3 WHERE L3.title = 'test08')
);
四.UNION：UNION连接的两个SELECT查询
在使用UNION时，左边的表的select_type是DERIVED，右边的表的select_type是UNION。
五.DERIVED：在FROM列表中包含的子查询被标记为DERIVED派生表
MySQL会递归执行这些被标记为DERIVED的子查询，然后把结果放到临时表中。
六.UNION RESULT：UNION的结果
EXPLAIN SELECT * FROM (SELECT * FROM L3 UNION SELECT * FROM L4) a;
(4)type字段说明
type字段表示的是连接类型，描述了找到所需数据而使用的扫描方式。
下面给出各种连接类型，按照从最好类型到最差类型进行排序：system -> const -> eq_ref -> ref -> fulltext -> ref_or_null -> index_merge -> unique_subquery -> index_subquery -> range -> index -> ALL
简化后，可以只关注以下几种 ：system -> const -> eq_ref -> ref -> range -> index -> ALL
一般来说，需要保证查询至少达到range级别，最好能达到ref级别，否则就要就行SQL的优化调整。
下面介绍type字段不同值表示的含义：
一.system
表示表中仅有一行数据，这是const连接类型的一个特例，很少出现。
二.const
表示命中主键索引(primary key)或唯一索引(unique)，通过主键索引或唯一索引一次就找到了数据。因为只匹配一条记录，所以被连接的部分是一个常量。如果将主键放在where条件中，MySQL就能将该查询转换为一个常量。这种类型非常快，例如以下查询：
EXPLAIN SELECT * FROM L1 WHERE id = 3;-- 为L1表的title字段添加唯一索引ALTER TABLE L1 ADD UNIQUE(title);EXPLAIN SELECT * FROM L1 WHERE title = 'test001';
三.eq_ref
表示的是使用了唯一索引。比如连表查询中，对于前一个表中的每一行，后表只有一行被扫描。除了system和const类型之外，这是最好的连接类型。只有在连表时使用的索引都是主键或唯一索引时，才会出现这种类型，例如以下查询：
EXPLAIN SELECT L1.id,L1.title FROM L1 LEFT JOIN L2 ON L1.id = L2.id;
四.ref
表示使用了普通索引，即非唯一性索引。比如连表时对于前表的每一行，后表可能有多于一行的数据被扫描，例如以下查询：
-- 为L1表的title字段添加普通索引ALTER TABLE L1 ADD INDEX idx_title (title);EXPLAIN SELECT * FROM L1 INNER JOIN L2 ON L1.title = L2.title;-- 如果L1表的title字段没有唯一索引，只有普通索引，如下查询也是refEXPLAIN SELECT * FROM L1 WHERE title = 'test001';
五.range
表示的是进行了索引上的范围查询，检索了给定范围的行，比如between、in函数、>都是典型的范围查询，例如以下查询：
EXPLAIN SELECT * FROM L1 WHERE L1.id BETWEEN 1 AND 10;
注意：当in函数中的数据很大时，可能会导致效率下降，最终不走索引。
六.index
当可以使用索引覆盖，但需要扫描全部索引记录时，则type为index。
当需要执行全表扫描，且需要对主键进行排序时，则type也为index。
所以如果type的值等于index，那么就需要进行优化了。因为出现index表示没有通过索引进行过滤，需要扫描索引的全部数据。index会遍历扫描索引树，比ALL快一些。如果索引文件过大，index的速度还是会很慢的。
总结：
当遍历二级索引不需要回表或者主键排序全表扫描时，type就为index。
注意：
使用索引进行排序分组时，可能会出现这种type值为index的情况。比如进行统计操作时，会出现type值为index的情况。
EXPLAIN SELECT * FROM L2 GROUP BY id ORDER BY id;-- 该count查询需要通过扫描索引上的全部数据来计数EXPLAIN SELECT count(*) FROM L2;
七.ALL
表示没有使用到任何索引，连表查询时对于前表的每一行，后表都要被全表扫描。
EXPLAIN SELECT * FROM L3 inner join L4 on L3.title = L4.title;
总结各类type类型的特点：
system：不进行磁盘IO，查询系统表，仅仅返回一条数据。
const：查找主键索引，最多返回1条或0条数据，属于精确查找。
eq_ref：查找唯一性索引，返回数据最多一条，属于精确查找。
ref：查找非唯一性索引，返回匹配的多条数据，属于精确查找。
range：查找索引中给定范围的行，属于范围查找(>、<、in、between)。
index：使用了索引但扫描全部了，比all快，因索引文件比数据文件小。
index：比如遍历二级索引不需要回表或者主键排序全表扫描。
all：不使用任何索引，直接进行全表扫描。
(5)possible_keys与key说明
一.possible_keys
表示可能用于查询的表上的索引。查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询实际使用。
二.key
表示实际使用的索引。若为null，则表示没有使用到索引或索引失效。查询中若使用了覆盖索引，则该索引仅出现在key列表中。
情形一：理论和实际都用到了索引
EXPLAIN SELECT * FROM L1 WHERE id = 1;
情形二：理论上没有使用索引，但实际上使用了
EXPLAIN SELECT L3.id FROM L3;
情形三：理论和实际上都没有使用索引
EXPLAIN SELECT * FROM L3 WHERE title = 'test007';
(6)key_len字段说明
表示索引中使用的字节数，通过该列可以计算查询中使用索引的长度。key_len字段能够帮我们检查是否充分利用了索引，ken_len越长越好，说明索引使用的越充分。
一.创建表
CREATE TABLE L5(a INT PRIMARY KEY,b INT NOT NULL,c INT DEFAULT NULL,d CHAR(10) NOT NULL);
二.使用EXPLAIN进行测试
-- 下面的查询只用到了主键a的索引EXPLAIN SELECT * FROM L5 WHERE a > 1 AND b = 1;
观察key_len的值，用到了主键索引，是int类型的，所以key_len是4字节。
三.为b字段添加索引，进行测试
ALTER TABLE L5 ADD INDEX idx_b(b);-- 执行SQL，这次将b字段也作为条件EXPLAIN SELECT * FROM L5 WHERE a > 1 AND b = 1;
ken_len还是4。
四.为c、d字段添加联合索引，然后进行测试
ALTER TABLE L5 ADD INDEX idx_c_b(c,d);EXPLAIN SELECT * FROM L5 WHERE c = 1 AND d = 'A';
c字段是int类型4个字节，d字段char(10)代表的是10个字符30个字节。因为数据库的字符集是utf8，一个字符3个字节。d字段是char(10)代表的是10个字符相当30个字节。多出的一个字节用来表示是联合索引。
下面这个例子虽然使用了联合索引，但没充分利用索引，还有优化空间。因为可以根据ken_len的长度推测出该联合索引只使用一部分。
EXPLAIN SELECT * FROM L5 WHERE c = 1;
(7)ref字段说明
表示的是显示索引的哪一列被使用了，如果可能的话，最好是一个常数。表示的是哪些列或常量被用于查找索引列上的值。
如下的"L1.id=1"中，由于1是常量，所以ref = const，此时的ref = const表示着查询过程中使用到了常量。
EXPLAIN SELECT * FROM L1 WHERE L1.id = 1;
(8)rows字段说明
表示MySQL为了找到所需的记录，一共访问了多少行(预估的)。L3中的title没有添加索引，所以L3中有3条记录，就需要访问3条记录。
EXPLAIN SELECT * FROM L3,L4 WHERE L3.id = L4.id AND L3.title LIKE 'test007';
需要注意的是rows只是一个估算值，并不准确。所以rows行数过大的问题并不值得过多考虑，主要分析的还是索引是否使用正确了。
(9)filtered字段说明
它指返回结果的行占需要读到的行(rows列的值)的百分比。
(10)extra字段说明
Extra是EXPLAIN输出中另外一个很重要的列，该列显示MySQL在查询过程中的一些详细信息。
一.准备数据
CREATE TABLE users (uid INT PRIMARY KEY AUTO_INCREMENT,uname VARCHAR(20),age INT(11));INSERT INTO users VALUES(NULL, 'lisa', 10);INSERT INTO users VALUES(NULL, 'lisa', 10);INSERT INTO users VALUES(NULL, 'rose', 11);INSERT INTO users VALUES(NULL, 'jack', 12);INSERT INTO users VALUES(NULL, 'sam', 13);
二.Using filesort(需要进行文件排序)
执行结果Extra为Using filesort，说明得到所需结果集，需要对所有记录进行文件排序。表示执行的SQL语句性能极差，需要进行优化。
下面就是在一个没有建立索引的列上进行order by，此时会触发filesort。优化方案是，在order by的列上添加索引，避免每次查询都全量排序。
EXPLAIN SELECT * FROM users ORDER BY age;
三.Using temporary
表示使用了临时表来存储结果集，常见于排序和分组查询。
EXPLAIN SELECT COUNT(*),uname FROM users GROUP BY uname;
四.Using where
表示使用了全表扫描或者在查找时使用索引的情况下，还有查询条件不在索引字段中需要回表。
注意一：返回所有记录的SQL，不使用where条件过滤数据，大概率不符合预期，这类SQL往往需要进行优化。
注意二：使用了where条件的SQL，并不代表不需要优化，往往需要配合explain结果中的type(连接类型)来综合判断。例如下面查询的age未设置索引，所以返回的type为ALL，仍有优化空间，可建立索引优化查询。
EXPLAIN SELECT * FROM users WHERE age = 10;
五.Using index
表示直接访问索引就能获取所需数据(覆盖索引)，不需要回表。
-- 为uname创建索引ALTER TABLE users ADD INDEX idx_uname(uname);EXPLAIN SELECT uid,uname FROM users WHERE uname='lisa';
六.Using join buffer
表示使用了连接缓存，还会显示join连接查询时使用的算法。
EXPLAIN SELECT * FROM users u1 LEFT JOIN(SELECT * FROM users WHERE age = 1) u2 ON u1.age = u2.age;
Using join buffer(Block Nested Loop)说明，需要进行嵌套循环计算。这里每个表都有五条记录，内外表查询的type都为ALL。两个表通过字段age进行关联，且age字段未建立索引。
七.Using index condition
表示的是使用了索引，但是只使用了索引的一部分。一般发生在使用联合索引时，需要回表查询。
EXPLAIN SELECT * FROM L5 WHERE c > 10 AND d = '';
八.Extra主要指标的含义总结
using index：查找时使用了覆盖索引的时候就会出现，不需要回表。
using where：查找时使用索引的情况下需要回表或全表扫描。
using index condition：查找时使用了索引但只用一部分索引需要回表。
Using filesort：在一个没有建立索引的列上order by，发生文件排序。
Using temporary：使用了临时表存储结果集，常见于排序和分组查询。
当遍历二级索引不需要回表或者主键排序全表扫描时，type就为index。
查找非唯一性索引，返回匹配的多条数据，type就为ref。
查找唯一性索引，返回匹配的数据最多一条，type就为eq_ref。
查找索引中给定范围的行，type就为range。
3.索引优化数据准备
(1)创建数据库、表，插入数据
create database idx_optimize character set 'utf8';CREATE TABLE users(id INT PRIMARY KEY AUTO_INCREMENT,user_name VARCHAR(20) NOT NULL COMMENT '姓名',user_age INT NOT NULL DEFAULT 0 COMMENT '年龄',user_level VARCHAR(20) NOT NULL COMMENT '用户等级',reg_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '注册时间');INSERT INTO users(user_name,user_age,user_level,reg_time)VALUES('tom',17,'A',NOW()),('jack',18,'B',NOW()),('lucy',18,'C',NOW());
(2)创建联合索引
ALTER TABLE users ADD INDEX idx_nal (user_name,user_age,user_level) USING BTREE;4.索引优化原则详解
(1)最左侧列匹配和最左前缀匹配法则
(2)不要在索引列上做任何计算
(3)范围之后全失效
(4)避免使用is null、is not null、!= 、or
(5)like以%开头会使索引失效
(6)索引优化原则总结
(1)最左侧列匹配和最左前缀匹配法则
如果创建的是联合索引，就要遵循该法则。where后面的条件需从索引的最左侧列开始，且不能跳过索引中的列。如果where只匹配一个列，那么该列在索引最左侧，且只匹配前缀字段。
一.最左侧列匹配和最左前缀匹配的场景
场景1：按照索引字段顺序使用，三个字段都使用了索引，没有问题。
EXPLAIN SELECT * FROM usersWHERE user_name = 'tom' AND user_age = 17 AND user_level = 'A';
场景2：直接跳过user_name使用索引字段，索引无效，未使用到索引。
EXPLAIN SELECT * FROM users WHERE user_age = 17 AND user_level = 'A';
场景3: 不按照创建联合索引的顺序，使用索引。
EXPLAIN SELECT * FROM users WHERE user_age = 17 AND user_name = 'tom' AND user_level = 'A';
where后面查询条件顺序是user_age、user_level、user_name，这与创建的索引顺序user_name、user_age、user_level不一致。为什么还是使用了索引，原因是MySQL底层优化器对其进行了优化。
场景4：只要包含最左侧字段，索引就可以生效
但从key_len可知只是用到索引的一部分。
EXPLAIN SELECT * FROM users WHERE user_name = 'tom';
二.最左侧列匹配和最左前缀匹配的原理
InnoDB创建联合索引的规则是：
首先会对联合索引最左边的字段进行排序，例子中是user_name。在第一个字段的基础之上再对第二个字段进行排序，例子中是user_age。所以最佳左前缀原则其实是和B+树的结构有关系，最左字段肯定是有序的，第二个字段则是无序的。
联合索引的排序方式是：
先按第一个字段进行排序，如果第一个字段相等再根据第二个字段排序。所以如果直接使用第二个字段user_age通常是使用不到索引的。
(2)不要在索引列上做任何计算
不要在索引列上做任何操作，否则会导致索引失效，从而转向全表扫描。比如计算、使用函数、自动或手动进行类型转换(字符串不加双引号)。
一.插入数据
INSERT INTO users(user_name,user_age,user_level,reg_time) VALUES('11223344',22,'D',NOW());场景1：使用系统函数left()函数，对user_name进行操作
EXPLAIN SELECT * FROM users WHERE LEFT(user_name, 6) = '112233';
场景2：字符串不加单引号(隐式类型转换)
对于varchar类型的字段，如果查询时不加单引号就会进行隐式转换，导致索引失效转向全表扫描。
EXPLAIN SELECT * FROM users WHERE user_name = 11223344;
(3)范围之后全失效
where条件中如果有范围条件，并且范围条件之后还有其他过滤条件，那么范围条件之后的列就都将会索引失效。
场景1：条件单独使用user_name时，type=ref、key_len=62。
-- 条件只有一个 user_nameEXPLAIN SELECT * FROM users WHERE user_name = 'tom';
场景2：条件增加一个user_age(使用常量等值)，type= ref、key_len = 66。
EXPLAIN SELECT * FROM users WHERE user_name = 'tom' AND user_age = 17;
场景3：使用全值匹配，type = ref、key_len = 128，索引都利用上了。
EXPLAIN SELECT * FROM users WHERE user_name = 'tom' AND user_age = 17 AND user_level = 'A';
场景4：使用范围条件时，avg > 17、type = range、key_len = 66。与场景3比较，可发现user_level索引没用上。
EXPLAIN SELECT * FROM users WHERE user_name = 'tom' AND user_age > 17 AND user_level = 'A';
(4)避免使用is null、is not null、!= 、or
一.使用is null会使索引失效
EXPLAIN SELECT * FROM users WHERE user_name IS NULL;
Impossible Where：表示where条件不成立，不能返回任何行。
二.使用is not null会使索引失效
EXPLAIN SELECT * FROM users WHERE user_name IS NOT NULL;
三.使用!=和or会使索引失效
EXPLAIN SELECT * FROM users WHERE user_name != 'tom';EXPLAIN SELECT * FROM users WHERE user_name = 'tom' or user_name = 'jack';
(5)like以%开头会使索引失效
一.like查询中%出现在左边则索引失效，%出现在右边索引未失效
场景1：两边都有%或者%在左边，索引都会失效
EXPLAIN SELECT * FROM users WHERE user_name LIKE '%tom%';EXPLAIN SELECT * FROM users WHERE user_name LIKE '%tom';
场景2：%在右边，索引生效
EXPLAIN SELECT * FROM users WHERE user_name LIKE 'tom%';
二.解决%出现在左边索引失效的方法——使用覆盖索引
EXPLAIN SELECT user_name FROM users WHERE user_name LIKE '%jack%';EXPLAIN SELECT user_name,user_age,user_level FROM users WHERE user_name LIKE '%jack%';
对比场景1可以知道：通过使用覆盖索引type = index，并且extra = Using index，从原来的全表扫描变成了全索引扫描，也就是索引的全表扫描。
三.like失效的原理
原理一：%号在右
由于B+树的索引顺序，是按照首字母的大小进行排序，而%号在右时的匹配又会匹配首字母，所以能在B+树上进行有序的查找。也就是查找出首字母符合要求的数据，所以%号在右可以用到索引。
原理二：%号在左是匹配字符串尾部的数据
由于尾部的字母是没有顺序的，所以不能按索引顺序查询，用不到索引。
原理三：两个%%号
这个是查询任意位置的字母满足条件即可。只有首字母是进行索引排序的，其他位置的字母都是相对无序的，所以查找任意位置的字母是用不上索引的。
(6)索引优化原则总结
一.最左侧列匹配和最左前缀匹配
二.索引列上不计算不转换
三.范围之后全失效
四.最好使用覆盖索引
五.!=、is null、is not null、or会索引失效
六.like百分号加右边，加左边导致索引失效的解决方法是使用覆盖索引
5.慢查询设置与测试
(1)慢查询介绍
(2)慢查询参数
(3)慢查询配置方式
(4)慢查询测试
(5)慢日志内容
(1)慢查询介绍
MySQL的慢查询全名是慢查询日志，是MySQL提供的一种日志记录。慢查询日志会记录在MySQL中响应时间超过阈值的语句。MySQL数据库默认不启动慢查询日志，需要手动来设置这个参数。
如果不是调优需要的话，一般不建议启动该参数。因为开启慢查询日志会或多或少带来一定的性能影响，慢查询日志支持将日志记录写入文件和数据库表。
(2)慢查询参数
执行下面的语句
mysql> show variables like '%slow_query_log%';+---------------------+------------------------------+| Variable_name | Value |+---------------------+------------------------------+| slow_query_log | ON || slow_query_log_file | /var/lib/mysql/test-slow.log |+---------------------+------------------------------+mysql> show variables like '%long_query%';+-----------------+-----------+| Variable_name | Value |+-----------------+-----------+| long_query_time | 10.000000 |+-----------------+-----------+
MySQL慢查询的相关参数解释：
一.slow_query_log：是否开启慢查询日志。
二.slow-query-log-file：慢查询日志存储路径。
三.long_query_time：慢查询阈值，查询时间多于设定阈值则记录日志。
(3)慢查询配置方式
一.默认情况下slow_query_log的值为OFF，表示慢查询日志是禁用的
mysql> show variables like '%slow_query_log%';+---------------------+------------------------------+| Variable_name | Value |+---------------------+------------------------------+| slow_query_log | ON || slow_query_log_file | /var/lib/mysql/test-slow.log |+---------------------+------------------------------+
二.可以通过设置slow_query_log的值来开启
mysql> set global slow_query_log=1;三.set global slow_query_log=1开启慢查询日志当前生效重启失效
如果要永久生效，就必须修改配置文件my.cnf，其它系统变量也是如此。
-- 编辑配置vim /etc/my.cnf-- 添加如下内容slow_query_log =1slow_query_log_file=/var/lib/mysql/ruyuan-slow.log-- 重启MySQLservice mysqld restartmysql> show variables like '%slow_query%';+---------------------+--------------------------------+| Variable_name | Value |+---------------------+--------------------------------+| slow_query_log | ON || slow_query_log_file | /var/lib/mysql/ruyuan-slow.log |+---------------------+--------------------------------+
四. 开启了慢查询日志后，什么样的SQL才会记录到慢查询日志里
这个由参数long_query_time控制，默认long_query_time的值为10秒。
mysql> show variables like 'long_query_time';+-----------------+-----------+| Variable_name | Value |+-----------------+-----------+| long_query_time | 10.000000 |+-----------------+-----------+mysql> set global long_query_time=1;Query OK, 0 rows affected (0.00 sec)mysql> show variables like 'long_query_time';+-----------------+-----------+| Variable_name | Value |+-----------------+-----------+| long_query_time | 10.000000 |+-----------------+-----------+
五.修改变量long_query_time，但查询值还是10
执行命令set global long_query_time=1后，需要重新连接或者打开新开会话才能看到修改值。
mysql> show variables like 'long_query_time';+-----------------+----------+| Variable_name | Value |+-----------------+----------+| long_query_time | 1.000000 |+-----------------+----------+
六.log_output参数是指定日志的存储方式
log_output=FILE表示将日志存入文件，默认值是FILE。log_output=TABLE表示将日志存入数据库，这样日志信息就会被写入到mysql.slow_log表中。
MySQL数据库可以同时支持两种日志存储方式，配置的时候以逗号隔开即可，如：log_output='FILE,TABLE'。
日志记录到系统的专用日志表中，要比记录到文件耗费更多的系统资源。因此如果启用慢查询日志+获得更高系统性能，则建议优先记录到文件。
mysql> SHOW VARIABLES LIKE '%log_output%';+---------------+-------+| Variable_name | Value |+---------------+-------+| log_output | FILE |+---------------+-------+
七.开启系统变量让未使用索引的查询也被记录到慢查询日志中
这个系统变量就是log-queries-not-using-indexes，所以在进行调优时，可以开启这个选项。
mysql> show variables like 'log_queries_not_using_indexes';+-------------------------------+-------+| Variable_name | Value |+-------------------------------+-------+| log_queries_not_using_indexes | OFF |+-------------------------------+-------+mysql> set global log_queries_not_using_indexes=1;Query OK, 0 rows affected (0.00 sec)mysql> show variables like 'log_queries_not_using_indexes';+-------------------------------+-------+| Variable_name | Value |+-------------------------------+-------+| log_queries_not_using_indexes | ON |+-------------------------------+-------+
(4)慢查询测试
一.执行test_index.sql脚本，监控慢查询日志内容
[root@localhost mysql]# tail -f /var/lib/mysql/test-slow.log
/usr/sbin/mysqld, Version: 5.7.30-log (MySQL Community Server (GPL)). started with:
Tcp port: 0 Unix socket: /var/lib/mysql/mysql.sock
Time Id Command Argument二. 执行下面的SQL，执行超时(超过1秒)我们去查看慢查询日志
SELECT * FROM test_index WHEREhobby = '20009951' OR hobby = '10009931' OR hobby = '30009931'OR dname = 'name4000' OR dname = 'name6600' ;
(5)慢日志内容
我们得到慢查询日志后，最重要的一步就是去分析这个日志。先来看慢日志里到底记录了哪些内容，如下是慢日志里其中一条记录，可以看到有时间戳、用户、查询时长及具体的SQL等信息。
Time Id Command Argument
# Time: 2022-02-23 T03:55:15. 336037Z
# User@Host: root[root] @ localhost [] Id: 6
# Query_time: 2.375219 Lock_time: 0.000137 Rows_sent: 3 Rows_examined: 5000000
use db4;
SET timestamp=1645588515;
SELECT * FROM test_index WHERE hobby = '20009961' OR hobby = '10009941' OR hobby = '30009961' OR dname = 'name4001' OR dname = 'name6601';Time：执行时间；
Users：用户信息；
Query_time：查询时长；
Lock_time：等待锁时长；
Rows_sent：结果行统计数量；
Rows_examined：扫描的行数；
6.慢查询SQL优化思路
(1)SQL性能下降的原因
(2)慢查询优化思路
(1)SQL性能下降的原因
导致SQL执行性能下降的原因可体现在以下两方面：
一.等待时间长
锁表导致查询一直处于等待状态。
二.执行时间长
查询语句没优化、索引失效、关联查询太多join、机器及参数没调优。
(2)慢查询优化思路
一.优先选择优化高并发执行的SQL
因为高并发的SQL出现问题带来后果更严重，比如下面两种情况：SQL1每小时执行10000次，每次20个IO，优化后每次18个IO，每小时节省2万次IO；SQL2每小时10次，每次20000个IO，每次优化减少2000个IO，每小时节省2万次IO。此时SQL2更难优化，SQL1更好优化。但是第一种属于高并发SQL，更急需优化，因为成本更低。
二.定位优化对象的性能瓶颈
在去优化SQL时，选择优化分方向有三个：
方向1：IO，数据访问消耗了太多时间，查看是否正确使用索引。
方向2：CPU，数据运算花费了太多时间，数据的运算分组、排序是不是有问题。
方向3：网络带宽，加大网络带宽。
三.明确优化目标
根据数据库当前状态、当前SQL的具体功能，来确定最好情况下消耗的资源和最差情况下消耗的资源。因为优化的结果只有一个，即给用户一个好的体验。
四.从explain执行计划入手
只有explain能告诉我们当前SQL的执行状态。
五.永远用小的结果集驱动大的结果集
小的数据集驱动大的数据集，减少内层表读取次数。
//类似于嵌套循环
for (int i = 0; i < 5; i++) {
for (int i = 0; i < 1000; i++) {
}
}六.尽可能在索引中完成排序
排序操作用得比较多，所以order by后面的字段尽量使用上索引。因为索引本来就是排好序的，所以速度很快。没有索引的话，就需要从表中拿数据，在内存中进行排序。如果内存空间不够还会发生临时文件落盘操作。
七.只获取自己需要的列
不要使用select *，因为select * 很可能不使用索引，而且数据量过大。
八.只使用最有效的过滤条件
where后面的条件并非越多越好，应该用最短的路径访问到数据。
九.尽可能避免复杂的join和子查询
每条SQL的JOIN操作建议不要超过三张表。将复杂的SQL，拆分成多个小的SQL，单个表执行，然后对获取的结果在程序中进行封装。因为如果join占用的资源比较多，会导致其他进程等待时间变长。
十.合理设计并利用索引
也就是合理判断是否需要创建索引，以及合理选择合适索引。
(3)如何判定是否需要创建索引
一.频繁作为查询条件的字段应该创建索引
二.唯一性太差的字段不适合单独创建索引，即使它频繁作为查询条件
唯一性太差的字段主要是指哪些呢？如状态字段、类型字段等。这些字段中的数据可能总共就是那么几个几十个数值重复使用。当一条Query所返回的数据超过了全表的15%时，就不应该再使用索引扫描来完成这个Query了。
三.更新非常频繁的字段不适合创建索引
因为索引中的字段被更新时，不仅要更新表的数据，还要更新索引数据。
四.不会出现在WHERE子句中的字段不该创建索引
(4)如何选择合适索引
一.单键索引，尽量选择针对当前Query过滤性更好的索引。
二.联合索引，当前查询中过滤性最好的字段在索引字段顺序中排列靠前。
原文地址：https://blog.csdn.net/mjunz/article/details/144163279
免责声明：本站文章内容转载自网络资源，如本站内容侵犯了原著者的合法权益，可联系本站删除。更多内容请关注自学内容网（zxcms.com）！