深入浅出Mysql——SQL优化

浅浅的花香味﹌ 2021-11-24 00:50 307阅读 0赞

**一.优化SQL语句的一般步骤**

1.1  show \[session | global\] status 显示session(会话，默认级别)或global（自数据库上次启动至今）所有统计参数的值：

执行show status like 'Com\_%'

Com\_xxx表示每个xxx语句执行的次数，通常有以下几个参数：

Com\_select  select次数，一次查询累加1

Com\_insert   insert次数，批量插入的只累加1次

Com\_update 执行update操作次数

Com\_delete  执行delete次数

针对InnoDB引擎，累加算法略有不同

Innodb\_rows\_read   select查询返回的行数  , 其他的均如此（影响的行数）。

1.2  定位执行效率较低的SQL语句

（1）通过慢查询日志定位执行效率较低的SQL。慢查询日志默认是关闭的，用--log-slow-queries选项启动时，mysqld写一个包含所有执行时间超过long\_query\_time秒的SQL语句的日志文件。目前推荐使用--slow-query-log显示指定慢查询的状态，使用slow\_query\_log\_file来指定慢查询日志的路径。

set long\_query\_time;(默认10s，可精确到0.01s)     more localhost-slow.log;(查看慢查询日志)

如果慢查询日志中记录内容很多，可以使用mysqldumpslow工具对慢查询日志进行分类汇总，

例：mysqldumpslow bj37-slow.log；

（2）可以使用show processlist 命令查看当前MYSQL在进行的线程，包括线程的状态，是否锁表等。实时查看SQL的执行情况。

1.3  通过Explain分析低效SQL的执行计划

执行计划有以下几个列，分别说明：

select\_type:表示select类型，有simple、primary、union、subquery等几种

table:输出结果集的表

ALL ：全表扫描

index ：索引全扫描

range ：索引范围扫描，常见<,<=,>,>=,between等操作符

ref ：使用非唯一索引扫描或唯一索引的前缀扫描    比如where条件下使用uid = 20;

eq\_ref ：使用唯一索引，比如where条件下使用a.aid = b.bid;

const/system ：单表中最多有一个匹配行，查询起来非常迅速，所以这个匹配行中的其他列的值可以被优化器在当前查询中当作常量来处理。例如根据主键primary key或唯一索引进行的查询，比如explain select \* from (select \* from customer where email = 'xxx@163.com'\\g);

NULL ：MYSQL不用访问表或者索引，直接就能够得到结果，例如： explain select 1 from dual where 1\\G

除此之外，mysql引入explain extended命令，加上show warnings，能够看到SQL在真正被执行之前优化器做了哪些SQL改写。

1.4 通过show profile分析SQL

通过have\_profiling参数，能够看到当前MYSQL是否支持profile  :  select @@have\_profiling;

默认profiling是关闭的，可以通过set语句在Session级别开启profiling  : select @@profiling

通过profile,我们能够更清楚地了解SQL执行的过程

首先执行语句 : select count(\*) from payment;

然后对该语句进行分析 : show profiles;

根据该语句的query ID为4,选择分析该语句 : show profile for  query 4;  该语句可以看到执行过程中每个状态和消耗的时间

其次，还可以通过 show profile source for query 查看SQL解析执行过程中每个步骤对应的源码文件、函数名以及具体的源文件行数。

1.5 通过trace分析优化器如何选择执行计划

首先打开trace，设置格式为JSON，设置最大能够使用的内存大小

set OPTIMIZER\_TRACE=“enabled=ON”,END\_MARKERS\_IN\_JSON=on;

set OPTIMIZER\_TRACE\_MAX\_MEM\_SIZE=1000000;

输入执行语句： select count(\*) from payment;

检查INFORMATION\_SCHEMA.OPTIMIZER\_TRACE就可以知道MYSQL是如何执行SQL的

select \* from INFORMATION\_SCHEMA.OPTIMIZER\_TRACE/G;

**二、索引问题（*具体参见  [https://blog.csdn.net/liutong123987/article/details/79384395][https_blog.csdn.net_liutong123987_article_details_79384395]*）**

2.1 Mysql目前主要有以下几种索引类型：FULLTEXT，HASH，BTREE，RTREE。

1. FULLTEXT  
　　　　即为全文索引，目前只有MyISAM引擎支持。其可以在CREATE TABLE ，ALTER TABLE ，CREATE INDEX 使用，不过目前只有 CHAR、VARCHAR ，TEXT 列上可以创建全文索引。全文索引并不是和MyISAM一起诞生的，它的出现是为了解决WHERE name LIKE “%word%"这类针对文本的模糊查询效率较低的问题。

2. HASH  
　　　　由于HASH的唯一（几乎100%的唯一）及类似键值对的形式，很适合作为索引。HASH索引可以一次定位，不需要像树形索引那样逐层查找,因此具有极高的效率。但是，这种高效是有条件的，即只在“=”和“in”条件下高效，对于范围查询、排序及组合索引仍然效率不高。

3. BTREE  
　　　　BTREE索引就是一种将索引值按一定的算法，存入一个树形的数据结构中（二叉树），每次查询都是从树的入口root开始，依次遍历node，获取leaf。这是MySQL里默认和最常用的索引类型。

4. RTREE  
　　　　RTREE在MySQL很少使用，仅支持geometry数据类型，支持该类型的存储引擎只有MyISAM、BDb、InnoDb、NDb、Archive几种。相对于BTREE，RTREE的优势在于范围查找。

2.2 索引种类  
　　普通索引：仅加速查询

唯一索引：加速查询 + 列值唯一（可以有null）

主键索引：加速查询 + 列值唯一（不可以有null）+ 表中只有一个

组合索引：多列值组成一个索引，专门用于组合搜索，其效率大于索引合并

全文索引：对文本的内容进行分词，进行搜索

索引合并，使用多个单列索引组合搜索

覆盖索引，select的数据列只用从索引中就能够取得，不必读取数据行，换句话说查询列要被所建的索引覆盖

操作索引：

1. 创建索引  
\--创建普通索引CREATE INDEX index\_name ON table\_name(col\_name);

\--创建唯一索引CREATE UNIQUE INDEX index\_name ON table\_name(col\_name);

\--创建普通组合索引CREATE INDEX index\_name ON table\_name(col\_name\_1,col\_name\_2);

\--创建唯一组合索引CREATE UNIQUE INDEX index\_name ON table\_name(col\_name\_1,col\_name\_2);

2. 通过修改表结构创建索引  
ALTER TABLE table\_name ADD INDEX index\_name(col\_name);

3. 创建表时直接指定索引  
CREATE TABLE table\_name (  
    ID INT NOT NULL,col\_name VARCHAR (16) NOT NULL,INDEX index\_name (col\_name)  
);

4. 删除索引  
\--直接删除索引DROP INDEX index\_name ON table\_name;--修改表结构删除索引ALTER TABLE table\_name DROP INDEX index\_name;

Mysql能够使用索引的场景：

1.匹配全值：对索引具体列都指定具体值，分析语句发现 type = const

2.匹配值的范围查询：对索引值能够进行范围查找。type = range

3.匹配最左前缀：仅仅使用索引的最左边列进行查找。type = ref ; 例如索引为 col1+col2+col3 联合索引，col1、col1+col2、col1+col3等值可以利用索引查到，但col2、col2+col3不能够利用索引查到

4.仅仅对索引进行查询，当查询的列都在索引的字段中时，查询的效率更高。分析语句发现 type = ref , Extra = Using index;

5.匹配列前缀：仅仅使用索引的第一列，并且只包含索引第一列的开头一部分进行查找。type = range

6.能够实现索引匹配部分精确而其他部分进行范围匹配，type = ref

7.如果列名是索引，那么使用 column\_name is null就会使用索引（区别于oracle）

存在索引但不能使用索引的典型场景

1.以%开头的LIKE查询不能够利用B-Tree索引，执行计划的key的值为NULL表示没有使用索引

例如explain selecy \* from actor where last\_name like '%NI%'\\G;  无法使用索引，一般情况下使用全文索引来解决类似问题，该例的一种优化方式是首先扫描二级索引 idx\_last\_name获得满足条件的 主键 actor\_id列表，之后根据主键回表去检索记录，避开全表扫描actor表产生的大量IO请求。

2.数据类型出现隐式转换的时候也不会使用索引，特别是当列类型是字符串，一定记得在where条件中把字符常量值用引号引起来。

3.复合索引的情况下，不满足最左前缀原则，是不会使用复合索引的

4.如何Mysql估计使用索引比全表扫描更慢，则不使用索引

5.用or分割开的条件，如果只有前面的条件列有索引，后面没有，那么涉及到的索引都不会被用到

查看索引的使用情况

show status like 'Handler\_read%';

Handler\_read\_rnd\_next 的值代表数据文件中读下一行的请求数，如果该值较高通常说明表索引不正确或写入的查询没有利用索引。

2.3 简单实用的优化方法

定期分析表和检查表： analyse table tbl\_name，用于分析和存储表的关键字分布，分析的结果将可以使得系统得到准确的统计信息，使得SQL能够生成正确的执行计划。如果用户感觉实际执行计划并不是预期的执行计划，执行一次分析表可能会解决问题。

检查表：check table tbl\_name; 检查表的作用格式检查一个或多个表是否有错误，也可以检查视图是否有错误，比如在视图定义中被引用的表已不存在。

定期优化表：optimize table tbl\_name;这个命令可以将表中的空间碎片进行合并，并且可以消除由于删除或者更新造成的空间浪费。

三、常用SQL的优化

3.1 大批量插入

对于InnoDB表，有以下几种方式：

（1）因为InnoDB表是按照主键的顺序进行保存的，所以将导入的数据按照主键的顺序排列，可以有效提高导入数据效率

（2）导入数据前执行 SET UNIQUE CHECKS=0，关闭唯一性校验，导入结束后执行SET UNIQUE CHECKS=1，恢复唯一性校验

（3）如果应用使用自动提交的方式，建议在导入前执行SET AUTOCOMMIT=0，关闭自动提交，导入结束后再执行SET AUTOCOMMIT=1，打开自动提交

对于MyISAM表，可以通过以下方式：

ALTER table tbl\_name DISABLE KEYS;(关闭非唯一索引的更新)

loading the data

ALTER table tbl\_name ENABLE KEYS;

3.2 INSERT语句

（1）尽量使用多个值表的insert语句，insert into test values(1,2),(1,3),(1,4)... 大大所见客户端于数据库的连接 、关闭等消耗

（2）如果是不同客户插入很多行，可以通过INSERT DELAYED语句得到更高的速度，DELAYED的含义是让INSERT语句马上执行，其实数据都被存放在内存队列中，并没有真正写入磁盘，这比每条语句分别插入要快得多。

（3）当从一个文本文件装载一个表时，使用LOAD DATA INFILE通常比使用很多INSERT 要快20倍

3.3 ORDER BY语句

尽量减少额外的排序，通过索引直接返回有序数据。

（1）避免混合使用DESC和ASC   sekect \* from tbl\_name ordery by key1 DESC , key2 ASC;

（2）用于查询行的关键字于order by中使用的不相同　　select \* from tbl\_name where key2='constant' order by key1;

（3）对不同的关键字使用order by       select \* from tbl\_name order by key1,key2;

Filesort的优化：一次扫描算法？一次性取出满足条件的的行的所有字段，然后再排序区sort buffer中排序后直接输出结果集。

（1）适当加大系统变量max\_length\_for\_sort\_data的值，能够让MYSQL选择更优化的Filesort排序算法

（2）适当加大sort\_buffer\_size排序区，尽量让排序在内存中完成，而不是通过创建临时表放在文件中进行。

（3）尽量只使用必要的字段，SELECT具体的字段名称而不是用\*解决

3.4 GROUP BY语句

默认情况，MySQL对所有group by的字段进行排序，这于在查询中指定order by类似，因此，如果显示包括一个包含相同列的order by子句，则对MySQL的实际执行性能没有什么影响。如果查询包括GROUP BY但用户想要避免排序结果的消耗，则可以指定order by null 禁止排序，从而避免Filesort的出现。

3.4 嵌套查询

使用join，左右连接、内外连接等

3.5 OR条件

如果要利用索引，需要保证or每个条件列都必须用到索引，如果没有则考虑增加索引。

3.6 分页查询

一般分页查询，通过创建覆盖索引能够比较好的提高性能。

四、使用SQL提示

4.1 USE INDEX

提价use index来提供希望MYSQL区参考的索引列表，就可以让MYSQL不再考虑其他可用的索引。

4.2 IGNORE INDEX

让用户忽略一个或多个索引 。

4.3 FORCE INDEX