SQL优化相关

优化SQL语句的一般步骤

1.通过show status 查看数据库各种类型的sql执行频率

show status like 'Com_%';

Innodb: Innodb_rows_read/inserted;

其他引擎：Com_select/insert

事务相关：Com_commit和Com_rollback查看提交和回滚参数，如果回滚比例很大，比如提交了10次回滚了9次，说明代码有问题。

数据库基本情况：

（1）Connections:试图链接MySQL服务器的次数

（2）Uptime:服务器工作时间

（3）Slow_queries:慢查询次数

主要是查看增删改查的频率，分析数据库主要进行的操作是啥，比如是以查询为主还是修改插入为主。

2.定位执行效率低的sql语句。

(1) 服务器启动的时候设置慢查询日志，指定慢查询时间，超过这个时间就记录日志，命令为：--log-slow-queries[=file_name];

缺点：慢查询日志只能在查询结束后进行记录

(2) 时时查询正在执行的慢查询，show processlist 查看当前mysql在进行的线程，主要是查看线程状态。

3.通过EXPLAIN分析慢sql。

用法：explain select * from a;

显示：

id:

select_type:  

标示查询的类型

(1)SIMPLE:简单表，也就是这个查询只涉及一个表，没有连接查询或者子查询。

(2)PRIMARY:主查询，外层的查询

(3)UNION(联合查询中的第二个或者后面的查询语句)

(4)SUBQUERY(只查询中的第一个select)

table:a

type:

访问类型，表示MySQL在表中找到所需行的方式。

（1）ALL:全表扫描，效率最低

（2）index：索引全扫描，遍历整个索引在查找匹配行

（3）range：索引范围扫描，比如使用<,<=,>,>=,between等操作符。

（4）ref: 使用非唯一索引扫描或者唯一索引的前缀扫描，通常一个索引健值会扫描到多个记录，

（5）eq_ref:equal_ref, 跟ref类似只是每个索引健值会扫描到一个记录。

（6）const：表中最多有一行记录匹配，这个匹配行的其他列的值可以被优化器在当前查询中当做常量来处理。

（7）NULL:不用访问或者索引直接返回结果。

效率从上到下依次提高。

possible_keys:可能用到的索引

key:使用的索引

ref:

rows:扫描行数，不是返回行数

Extra:额外重要信息，比如 using index  和 using where

explain extended和show warnings可以用来显示编译器优化后的sql，sql在执行之前会经过编译器的优化，比如去掉恒等等，用最高效的方法执行。

explain partitions可以查看sql执行的分区。

4.通过show profile分析SQL

（1）查看是否支持select @@having_profiling;

（2）查看是否开启select @@profiling; set profiling=1;开启

查看过程：

（1）show profiles; 查询出所有的语句执行时间和Query ID

（2）show profile for query 4 :查看ID为4的sql的具体执行时间，包括每个状态的执行时间，这里跟show processlist显示的是相同的信息，只是show processlist显示的是动态的。

 

备注：Sending data:标示线程开发访问数据库并把结果返回给客户端，这里面涉及了很多I/O操作，所以时间比较长。 

5.通过trace分析优化器执行计划

 命令：

打开trace设置格式为json：SET OPTIMIZER_TRACE='enabled=on',END_MARKERS_IN_JSON=on;

设置内存大小：SET OPTIMIZER_TRACE_MAX_MEN_SIZE=1000000；

执行完sql后，执行select * from information_schema.optimizer_trace;就可以打印出上面一句的具体执行过程。

6.确定问题并解决

是否是全表扫描，也就是没有使用索引

（1）是否是没有建立索引

（2）索引没有被用到。

（3）使用的索引达不到效果。

7.索引问题

索引存储分类

（1）B-TREE索引 ：MyISAM InnoDB Memory

       备注：结构是平衡树，不是二叉树

支持：全关键字，关键字范围和关键字前缀查询

    (2) HASH索引：Memory 查询速度快，不支持范围查询，所以Memory只有在使用“=”的时候才能用到索引。

  （3）Full-text索引：MyISAM  InnoDB（5.6之后开始支持）

索引使用场景

（1）匹配全值（Match the full value）：对索引中的所有列都指定具体值，用等号。type=const。效率最高。

（2）匹配值的范围查询。type=range

        示例：select * from a where id >10;

        特殊说明：这句Extra：Using where 意思是无法从索引上获取到全部信息，需要再回表查询一次。相对的Using index说明可以直接从索引上获取到数据，这是一个效率提升点。

（3）匹配最左前缀：针对组合索引，a,b,c其实是建立了三个索引  a, ab, abc ,所以 bc是无法使用索引的。注意：ac是可以用到 a的索引的。根据结构特性就很好理解，需要先找到父节点才能使用索引。

        这个原则是B-Tree中的首要原则。

（4）匹配列前缀：如果想用模糊查询 %不能放到最前面。

（5）索引列一个精确另一个范围。

（6）支持null 

       示例：select * from a where a.aa is null;  可以用到aa上的索引。

索引无法使用场景

（1）以%开头的like

（2）数据类型出现隐式转换，比如列类型是字符串，必须把where后面的值用引号引起来。

（3）复合索引不满足最左原则

（4）全表扫描比使用索引快的时候，这种情况可以使用trace分析。

（5）or的两边有一边没有索引的情况。

查看索引使用情况

命令：show status like 'Handler_read%';

说明：

（1）Handler_read_key: 索引使用次数，这个值如果很低表示索引不经常使用，需要优化

（2）Handler_read_rnd_next：数据文件中读取下一行的请求数，这个值很高说明查询运行效率低，需要增加索引。

常用的sql优化

1.大批量插入数据

2.优化order by语句

mysql排序方式：

（1）通过有序索引顺序扫描直接返回有序数据，比如查询的列就是索引列，而且where后面也是用的这个列。Extra : Using Index查询效率较高。

（2）通过返回数据进行排序，比如select * , Extra：using filesort 所有不是通过索引直接返回排序结果的都是这种情况。filesort排序是在sort_buffer_size系统变量设置的内存中排序的，如果内存不够就进行分块，而且排序区是线程独占的，所以同一时刻，存在着多个排序区。

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

Oderby不能使用索引的情况：

（1）order by 字段混合使用desc，asc

（2）select * from a where key2=constant order by key1; 查询的列和排序的列不同

（3）对不同的字段使用order by ：select * from a order by key1,key2;

filesort优化：

两次扫描算法：第一次取出排序字段和行指针，排完序后二次查表获取其他信息。内存消耗较少，效率较低。

一次扫描算法：一次取出所有字段信息进行排序。内存消耗大，效率高。

如果内存足够大选择第二种，否则选择第一种

优化策略：

（1）适当加大系统变量max_length_for_sort_data的值，优先选择一次扫描算法。但是不能无限加大，否则会影响线程数量。

（2）不用使用select *，尽量使用必要字段。

3.优化group by

默认情况下group by会进行排序，如果不需要的话 可以指定Oder by null禁止排序，因为filesort很耗时。

4.优化嵌套查询

join 优于嵌套查询，因为join不需要创建临时表进行嵌套查询的工作。

5.OR优化

给两边都增加索引。

6.优化分页查询

分页过程：limit 1000，20 会把前1020条查询甚至排序出来，然后只返回后面的20条，前面的都被抛弃了，资源浪费严重。

优化思路：

（1）在索引上完成分页排序的操作，然后用主键回原表查询其他列内容。

（2）增加标识记录上一页最后一条记录的主键，查询下一页的时候只从这个主键开始查，避免从头扫描。

优化前：

SELECT
loan_document_id
FROM
t_loan_document
WHERE
(
loan_document_state = 0
OR loan_document_state = 5
OR loan_document_state = 7
)
AND ar_due_date < '2015-12-24 11:09:09'
AND delete_flag = 0
LIMIT 100,100;

优化后：

SELECT
loan_document_id
FROM
t_loan_document
WHERE
loan_document_id > 'LD120140519140913216'
AND
(
loan_document_state = 0
OR loan_document_state = 5
OR loan_document_state = 7
)
AND ar_due_date < '2015-12-24 11:09:09'
AND delete_flag = 0
LIMIT 100;

（3）去掉首页尾页，只显示固定页数，google就是这么处理的，在结合上面两种方式。

7.使用SQL提示

（1）using index：提供索引列表，忽略其他的索引，但是真正使用的时候是否使用还要看优化器的选择。

  示例：explain select count(*) from rental use index (idx_rental_date);

  (2) ignore index:忽略特定索引

  示例：explain select count(*) from rental ignore index (idx_rental_date);

  (3) force index:强制使用索引，即使优化器判断全表扫描优于索引。

   explain select count(*) from rental force index (idx_rental_date)

 

 

 

3. 聚簇索引和二级索引

3.1 聚簇索引

每个InnoDB的表都拥有一个索引，称之为聚簇索引，此索引中存储着行记录，一般来说，聚簇索引是根据主键生成的。为了能够获得高性能的查询、插入和其他数据库操作，理解InnoDB聚簇索引是很有必要的。

聚簇索引按照如下规则创建：

• 当定义了主键后，InnoDB会利用主键来生成其聚簇索引；
• 如果没有主键，InnoDB会选择一个非空的唯一索引来创建聚簇索引；
• 如果这也没有，InnoDB会隐式的创建一个自增的列来作为聚簇索引。

Note: 对于选择唯一索引的顺序是按照定义唯一索引的顺序，而非表中列的顺序, 同时选中的唯一索引字段会充当为主键，或者InnoDB隐式创建的自增列也可以看做主键。

聚簇索引整体是一个b+树，非叶子节点存放的是键值，叶子节点存放的是行数据，称之为数据页，这就决定了表中的数据也是聚簇索引中的一部分，数据页之间是通过一个双向链表来链接的，上文说到B+树是一棵平衡查找树，也就是聚簇索引的数据存储是有序的，但是这个是逻辑上的有序，但是在实际在数据的物理存储上是，因为数据页之间是通过双向链表来连接，假如物理存储是顺序的话，那维护聚簇索引的成本非常的高。

3.2 辅助索引

除了聚簇索引之外的索引都可以称之为辅助索引，与聚簇索引的区别在于辅助索引的叶子节点中存放的是主键的键值。一张表可以存在多个辅助索引，但是只能有一个聚簇索引，通过辅助索引来查找对应的航记录的话，需要进行两步，第一步通过辅助索引来确定对应的主键，第二步通过相应的主键值在聚簇索引中查询到对应的行记录，也就是进行两次B+树搜索。相反通过辅助索引来查询主键的话，遍历一次辅助索引就可以确定主键了，也就是所谓的索引覆盖，不用回表（查询聚簇索引）。

创建辅助索引，可以创建单列的索引，也就是用一个字段来创建索引，也可以用多个字段来创建副主索引称为联合索引，创建联合索引后，B+树的节点存储的键值数量不是1个，而是多个，如下图：

• 联合索引的B+树和单键辅助索引的B+树是一样的，键值都是排序的，通过叶子节点可以逻辑顺序的读出所有的数据，比如上图所存储的数据时，按照(a,b)这种形式(1,1),(1,2),(2,1),(2,4),(3,1),(3,2)进行存放，这样有个好处存放的数据时排了序的，当进行order by对某个字段进行排序时，可以减少复杂度，加速进行查询；
• 当用select * from table where a=? and ?可以使用索引(a,b)来加速查询，但是在查询时有一个原则，sql的where条件的顺序必须和二级索引一致，而且还遵循索引最左原则，select * from table where b=?则无法利用(a,b)索引来加速查询。
• 辅助索引还有一个概念便是索引覆盖，索引覆盖的一个好处便是辅助索引不高含行记录，因此其大小远远小于聚簇索引，利用辅助索引进行查询可以减少大量的IO操作。

4. SQL执行顺序

以下的每一步操作都会生成一个虚拟表，作为下一个处理的输入，在这个过程中，这些虚拟表对于用户都是透明的，只用最后一步执行完的虚拟表返回给用户，在处理过程中，没有的步骤会直接跳过。

以下为逻辑上的执行顺序：

(1) from：对左表left-table和右表right-table执行笛卡尔积(a*b)，形成虚拟表VT1;

(2) on: 对虚拟表VT1进行on条件进行筛选，只有符合条件的记录才会插入到虚拟表VT2中;

(3) join: 指定out join会将未匹配行添加到VT2产生VT3,若有多张表，则会重复(1)~(3);

(4) where: 对VT3进行条件过滤，形成VT4, where条件是从左向右执行的;

(5) group by: 对VT4进行分组操作得到VT5;

(6) cube | rollup: 对VT5进行cube | rollup操作得到VT6;

(7) having: 对VT6进行过滤得到VT7;

(8) select: 执行选择操作得到VT8，本人看来VT7和VT8应该是一样的;

(9) distinct: 对VT8进行去重，得到VT9;

(10) order by: 对VT9进行排序，得到VT10;

(11) limit: 对记录进行截取，得到VT11返回给用户。

Note: on条件应用于连表过滤，where应用于on过滤后的结果（有on的话），having应用于分组过滤

5. SQL优化建议

索引有如下有点：减少服务器扫描的数据量、避免排序和临时表、将随机I/O变为顺序I/O。

可使用B+树索引的查询方式

• 全值匹配：与索引中的所有列进行匹配，也就是条件字段与联合索引的字段个数与顺序相同；
• 匹配最左前缀：只使用联合索引的前几个字段；
• 匹配列前缀：比如like 'xx%'可以走索引；
• 匹配范围值：范围查询，比如>,like等；
• 匹配某一列并范围匹配另外一列：精确查找+范围查找；
• 只访问索引查询：索引覆盖，select的字段为主键；

范围查询后的条件不会走索引，具体原因会在下一节进行介绍。

列的选择性（区分度）

选择性（区分度）是指不重复的列值个数/列值的总个数，一般意义上建索引的字段要区分度高，而且在建联合索引的时候区分度高的列字段要放在前边，这样可以在第一个条件就过滤掉大量的数据，有利用性能的提升，对于如何计算列的区分度，有如下两种方法：

• 根据定义，手动计算列的区分度，不重复的列值个数/列值的总个数；
• 通过 MySQL的carlinality，通过命令show index from <table_name>来查看，解释一下，此处的carlinality并不是准确值，而且 MySQL在B+树种选择了8个数据页来抽样统计的值，也就是说carlinality=每个数据页记录总和/8*所有的数据页，因此也说明这个值是不准确的，因为在插入/更新记录时，实时的去更新carlinality对于 MySQL的负载是很高的，如果数据量很大的话，触发 MySQL重新统计该值得条件是当表中的1/16数据发生变化时。

但是选择区分度高的列作为索引也不是百试百灵的，某些情况还是不合适的，下节会进行介绍。

MySQL查询过程

当希望 MySQL能够高性能运行的时候，最好的办法就是明白 MySQL是如何优化和执行的，一旦理解了这一点，很多查询优化工作实际上就是遵循了一些原则让优化器能够按照预想的合理的方式运行————《引用自高性能 MySQL 》

当想 MySQL实例发送一个请求时， MySQL按照如下图的方式进行查询：

• 客户端先发送一条查询给服务器；
• 服务器先检查查询缓存，如果命中了缓存，则立刻返回给存储在缓存中的结果，否则进入下一个阶段；
• 服务器端进行SQL解析、预处理，再由优化器生成对应的执行计划；
• MySQL 根据优化器生成的执行计划，调用存储引擎的API来执行查询；
• 将结果返回客户端。

注意&建议

• 主键推荐使用整型，避免索引分裂；
• 查询使用索引覆盖能够提升很大的性能，因为避免了回表查询；
• 选择合适的顺序建立索引，有的场景并非区分度越高的列字段放在前边越好，联合索引使用居多；
• 合理使用in操作将范围查询转换成多个等值查询；
• in操作相当于多个等值操作，但是要注意的是对于order by来说，这相当于范围查询，因此例如select * from t1 where c1 in (x,x) order by c2的sql是不走索引的；
• 将大批量数据查询任务分解为分批查询；
• 将复杂查询转换为简单查询；
• 合理使用inner join，比如说分页时候。

6. 一些问题分析

这个部分是我在学习过程中产生的一些疑问，以及在工作中碰到的或者同事提起的一些问题，对此我做了些调研，总结了一下并添加了些自己的理解，如有错误还请指正。

索引分裂

此处提一下索引分裂，就我个人理解，在 MySQL插入记录的同时会更新配置的相应索引文件，根据以上的了解，在插入索引时，可能会存在索引的页的分裂，因此会导致磁盘数据的移动。当插入的主键是随机字符串时，每次插入不会是在B+树的最后插入，每次插入位置都是随机的，每次都可能导致数据页的移动，而且字符串的存储空间占用也很大，这样重建索引不仅仅效率低而且 MySQL的负载也会很高，同时还会导致大量的磁盘碎片，磁盘碎片多了也会对查询造成一定的性能开销，因为存储位置不连续导致更多的磁盘I/O,这就是为什么推荐定义主键为递增整型的一个原因， MySQL索引页默认大小是16KB，当有新纪录插入的时候， MySQL会留下每页空间的1/16用于未来索引记录增长，避免过多的磁盘数据移动。

自增主键的弊端

对于高并发的场景，在InnoDB中按照主键的顺序插入可能会造成明显的争用，主键的上界会成为“热点”，因为所有的插入都发生在此处，索引并发的插入可能会造成间隙锁竞争，何为间隙锁竞争，下个会详细介绍；另外一个原因可能是Auto_increment的锁机制，在 MySQL处理自增主键时，当innodb_autoinc_lock_mode为0或1时，在不知道插入有多少行时，比如insert t1 xx select xx from t2，对于这个statement的执行会进行锁表，只有这个statement执行完以后才会释放锁，然后别的插入才能够继续执行，但是在innodb_autoinc_lock_mode=2时，这种情况不会存在表锁，但是只能保证所有并发执行的statement插入的记录是唯一并且自增的，但是每个statement做的多行插入之间是不连接的。

优化器不使用索引选择全表扫描

比如一张order表中有联合索引(order_id, goods_id)，在此例子上来说明这个问题是从两个方面来说：

• 查询字段在索引中

select order_id from order where order_id > 1000,如果查看其执行计划的话，发现是用use index condition,走的是索引覆盖。

• 查询字段不在索引中

select * from order where order_id > 1000, 此条语句查询的是该表所有字段，有一部分字段并未在此联合索引中，因此走联合索引查询会走两步，首先通过联合索引确定符合条件的主键id,然后利用这些主键id再去聚簇索引中去查询，然后得到所有记录，利用主键id在聚簇索引中查询记录的过程是无序的，在磁盘上就变成了离散读取的操作，假如当读取的记录很多时（一般是整个表的20%左右），这个时候优化器会选择直接使用聚簇索引，也就是扫全表，因为顺序读取要快于离散读取，这也就是为何一般不用区分度不大的字段单独做索引，注意是单独因为利用此字段查出来的数据会很多，有很大概率走全表扫描。

范围查询之后的条件不走索引

根据 MySQL的查询原理的话，当处理到where的范围查询条件后，会将查询到的行全部返回到服务器端（查询执行引擎），接下来的条件操作在服务器端进行处理，这也就是为什么范围条件不走索引的原因了，因为之后的条件过滤已经不在存储引擎完成了。但是在 MySQL 5.6以后假如了一个新的功能index condition pushdown(ICP),这个功能允许范围查询条件之后的条件继续走索引，但是需要有几个前提条件：

• 查询条件的第一个条件需要时有边界的，比如select * from xx where c1=x and c2>x and c3<x,这样c3是可以走到索引的；
• 支持InnoDB和MyISAM存储引擎；
• where条件的字段需要在索引中；
• 分表ICP功能5.7开始支持；
• 使用索引覆盖时，ICP不起作用。

set @@optimizer_switch = "index_condition_pushdown=on" 开启ICP set @@optimizer_switch = "index_condition_pushdown=off" 关闭ICP

范围查询统计函数不遵循 MySQL索引最左原则

比如创建一个表：

create table `person`(
`id` int not null auto_increment primary key,
`uid` int not null,
`name` varchar(60) not null,
`time` date not null,
key `idx_uid_date` (uid, time)
)engine=innodb default charset=utf8mb4;

当执行select count(*) from person where time > '2018-03-11' and time < '2018-03-16'时，time是可以用到idx_uid_date`的索引的,看如下的执行计划：

其中extra标识use index说明是走索引覆盖的，一般意义来说是 MySQL是无法支持松散索引的，但是对于统计函数，是可以使用索引覆盖的，因此 MySQL的优化器选择利用该索引。

分页offset值很大性能问题

在 MySQL中，分页当offset值很大的时候，性能会非常的差，比如limit 100000, 20，需要查询100020条数据，然后取20条，抛弃前100000条，在这个过程中产生了大量的随机I/O,这是性能很差的原因，为了解决这个问题，切入点便是减少无用数据的查询，减少随机I/O。 解决的方法是利用索引覆盖，也就是扫描索引得到id然后再从聚簇索引中查询行记录，我知道有两种方式：

比如从表t1中分页查询limit 1000000,5

• 利用inner join

select * from t1 inner join (select id from t1 where xxx order by xx limit 1000000,5) as t2 using(id),子查询先走索引覆盖查得id,然后根据得到的id直接取5条得数据。

• 利用范围查询条件来限制取出的数据

select * from t1 where id > 1000000 order by id limit 0, 5，即利用条件id > 1000000在扫描索引是跳过1000000条记录，然后取5条即可,这种处理方式的offset值便成为0了，但此种方式通常分页不能用，但是可以用来分批取数据。

索引合并

SELECT * FROM tbl_name WHERE key1 = 10 OR key2 = 20;
SELECT * FROM tbl_name WHERE (key1 = 10 OR key2 = 20) AND non_key=30;
SELECT * FROM t1, t2 WHERE (t1.key1 IN (1,2) OR t1.key2 LIKE 'value%') AND t2.key1=t1.some_col;
SELECT * FROM t1, t2 WHERE t1.key1=1 AND (t2.key1=t1.some_col OR t2.key2=t1.some_col2);

对于如上的sql在 MySQL 5.0版本之前，假如没有建立相应的联合索引，是要走全表扫描的，但是在 MySQL 5.1后引入了一种优化策略为索引合并，可以在一定程度上利用表上的多个单列索引来定位指定行，其原理是将对每个索引的扫描结果做运算，总共有：交集、并集以及他们的组合，但是索引合并并非是一种合适的选择，因为在做索引合并时可能会消耗大量的CPU和内存资源，一般用到索引合并的情况也从侧面反映了该表的索引需要优化。