SQL优化

良好的数据库设计是SQL搞效运行的一个关键，如果还不完全了解如何设计出一个良好的数据库结构的话，建议先看看数据库结构设计的文章。

优化SQL的一般步骤

发现问题

在MySQL中如何发现存在性能问题的SQL语句呢？

常见问题发现渠道

用户主动上报应用性能问题

比如最常见的就是，当应用上线后或者在测试过程中，测试人员或者最终的用户来反馈给我们开发人员，某些用户或者某些页面打开的很慢。这个时候，开发人员通常就会检查存在性能问题的相关的应用所包含的所有服务，这个时候的通常就包括了这个应用访问的数据库，这样我们也就知道了那些SQL存在的性能问题。我们就可以着手对这些SQL来进行优化了，以提高应用的响应效率。

这种获取具有性能问题的SQL的渠道呀，可以说是最为常见的一种方式，而且很有针对性，可以直接定位到这些SQL会对那些应用产生影响。只不过使用这种方式来获取要优化的SQL，相对来说对于我们来说是比较被动的，只有在发现问题的时候才能知道那些SQL存在问题。而对于一个繁忙的业务系统来说，这些性能问题已经对业务产生巨大的影响，至少已经影响到了部分用户的正常使用。因此，这种方式虽然很有针对性，但是使用的越少越好，特别是对于一个在线上已经运行的系统来说。

分析慢查询日志发现存在问题的SQL

慢查询日志是MySQL提供给我们的一种可以把所有的在MySQL服务器上执行成功的，并且符合一定条件的SQL记录下来的日志。

也就是说，我们可以指定一个时间的阈值，只要执行时间超过了阈值的查询呢，都会被记录到这个慢查询日志中，然后我们可以通过对慢查询日志的分析，来获得哪些SQL存在性能问题的，需要我们再进行优化。

相对来说，使用这种方式来获取存在问题的SQL呀，要比等待用户反馈的方式主动的多。这是因为大多数情况下，应用在这个刚刚上线时候，由于数量和访问量都不是很大，所以就算SQL存在着问题，可能也不会被用户察觉到，而我们却可以通过慢查询日志和SQL的执行计划来发现这个SQL，从而对其进行优化，这样就避免了由于在数据量访问量增大后，出现的查询性能问题对用户的正常访问所造成的影响。

数据库实时监控长时间运行的SQL

通过慢查询日志虽然已经可以大部分发现这个存在性能问题的SQL了，但是通常情况下，进行慢查询日志的分析呢，多少还是存在着一定的延迟，可能在有些情况下，我们更希望实时的了解到那些SQL存在性能问题的。比如我们的服务器当前用户突增，这个时候我们就需要一种方法实时获取存在性能问题的SQL，已解决当前系统存在的问题。由于数据库服务器压力突增，通常会使得整个网站或者应用都变得十分缓慢，所以这个时候不能指望用户来反馈具体是哪个页面出现了问题，而如果走慢查询日志记录后再进行分析显然也来不及了。

通过慢查询日志发现问题

配置MySQL慢查询日志

默认情况下MySQL 并不会启动慢查询日志，如果想要启动慢查询日志的话，就必须先要对其进行相关的配置。因此我们先来看一下如何配置：

set global slow_query_log = [ON|OFF]

slow_query_log 参数是一个全局的动态参数，也就是说我们可以随时的使用set global命令来使用这个参数，开启或者关闭这个慢查询日志的输出。
参数的默认值是OFF，如果我们需要开启记录慢查询日志的话，则需要把这个参数设置为ON。

set global slow_query_log_file =/sql_log/slowlog.log

默认情况启动慢查询日志之后，MySQL会把慢查询日志记录的MySQL的数据目录之下，并且是以主机名+slow_log做为慢查询日志的名。
如果我们不希望使用默认的慢查询日志的记录位置，则可以使用本配置。来指定我们想要把慢查询日志记录到什么位置。
比如记录到"/sql_log/slowlog.log"。是否记得在初始化MySQL服务的时候，建立的一个sql_log，这个sql_log目录就是用来记录MysQL下的所有日志的，所以我们这里就把慢查询日志记录到了sql_log目录中，并把慢查询日志的名设置为了slowlog.log。这里需要注意一点，用于启动MySQL服务的Linux系统账号一定要对sql_log目录有写权限。

set global long_query_time = xx.xxx秒

我们知道MMySQL的慢查询日志只会记录符合条件的查询到日志中。这个参数的值呀是一个以秒为单位的时间值，这个时间可以有6位的小数，因此可以到微秒的。当这个SQL执行的时间超过了这个设置的时间阈值的话，就会被记录到慢查询日志中。
如果我们想要记录所有的sql的话，直接把这个参数的值设置为0就可以了

set global log_queries_not_using_indexes = [ON|OFF]

当把这个值设置为ON的时候，就会记录到所有未使用到索引的SQL，都会把它们记录到这个慢查询日志中。一般来说未使用到索引的SQL呢，也是存在性能问题的SQL，也是我们需要着重进行优化的SQL语句。

分析MySQL慢查询日志

如果我们把慢查询的日志记录条件设置的比较宽泛，比如我们把这个long_query_time设置成了0.01秒，那么每天就会产生大量的慢查询日志，如果要人工的对这些慢查询日志进行分析，找出需要优化的SQL，显然就是一个不可能完成的任务，那我们如何高效的对慢查询日志进行分析呢 ？
好在，MySQL官方也替我们想到了这点，并为我们提供了一个工具：↓

mysqldumpslow [ OPTS… ] [ LOGS… ]

这个命令的使用非常的简单，通常情况下我们只需要把查询日志作为这个参数来调用这个命令就可以了

pt-query-digest [ OPTIONS ] [ FILES] [ DSN ]

这个工具更好用，因为这个pt工具相比于mysqldumpslow可以给我们提供更多的有用的信息，我们甚至可以直接使用这个工具来打印出慢查询日志中每个SQL的执行计划，同时这两个工具都可以抽取出慢查询日志中所有的SQL特征，把多次出现的SQL抽象为一个SQL来进行输出，这样就大大减少了我们需要分析SQL的数量

percona工具安装

我们可以通过官方网站来获取这个工具，在这里可以下载

接下来我们在linux系统中使用wget命令来下载它的软件包

wget https://www.percona.com/downloads/Percona-Server-LATEST/Percona-Server-8.0.16-7/binary/redhat/7/x86_64/percona-server-debuginfo-8.0.16-7.1.el7.x86_64.rpm

下载完成后，在安装这个包之前我们需要安装一些依赖包，依赖包有哪些呢？我们可以使用yum来进行安装

yum install -y perl-DBD-MySQL.X86_64 perl-DBI.X86 perl-Time-HiRes.X86_64  perl-IO-Socket-SSL.noarch perl-TermReadKey.X86_64 perl-Digest-MD5

接下来，我们就可以安装我们的percona工具集了，使用rpm命令来按照

rpm -ivh percona-tookit-3.0.13-1.el

安装完成之后，我们只需要输入pt命令，就会列出来一系列的percona的工具集的软件，其中的pt-query-digest就是我们进行慢查询日志分析所需要用到的工具软件

实战：启用慢查询日志

我们先看下配置慢查询日志的变量

show variables like 'long_query_time';

可以看到是10秒，这是MySQL一个默认的参数值，这个时间对我们来说显然太长了，所以我们把它设置成0，也就是我们要记录所有的sql，当然在真正工作中使用中，一般是设置成0.1也就是100毫秒就足够了

set global long_query_time=0

接下来再看看另一个参数

show variables like 'slow_query_log';

我们再来确认下慢查询日志会记录到哪里

show VARIABLES like 'slow_query_log_file'

这个时候，我们可以随便输入一些SQL，比如show databases;

实战：分析慢查询日志

让我们来看下slowlog文件中的内容：

接下来，首先使用mysqldumpslow工具分析，使用很简单，我们只需要使用命令行工具，后面跟上日志名就可以了

mysqldumpslow  slowlog.log

上面就是我们通过mysqldumpslow分析出来的慢查询的一个执行结果，这个结果中都有哪些内容呢？

• sql的执行次数
• 执行的时间
• 锁的时间
• 行数
• 执行的用户名
• sql的内容
  接下来，再使用pt-query-digest来分析慢查询日志，看看之间有什么差异

# Query 1: 0.41 QPS, 0.62x concurrency, ID 0x6B78B35582099676 at byte 5708854
# Scores: V/M = 0.01
# Time range: 2016-07-25 08:17:47 to 19:16:35
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count         70   16194
# Exec time     43  24511s      1s      3s      2s      2s    94ms      1s
# Lock time     60      1s    37us   791us    90us   125us    29us    98us
# Rows sent      0   1.04k       0       3    0.07    0.99    0.28       0
# Rows examine  11   2.05G   8.41k 391.42k 132.46k 380.41k 163.63k  23.58k
# Merge passes   0       0       0       0       0       0       0       0
# Query size    60   7.64M     495     495     495     495       0     495
# Boolean:
# Full join    100% yes,   0% no
# Full scan    100% yes,   0% no
# Tmp table    100% yes,   0% no
# String:
# Databases    lots
# Hosts
# Users        lotsprd
# Query_time distribution
#   1us
#  10us
# 100us
#   1ms
#  10ms
# 100ms
#    1s  ################################################################
#  10s+
# Tables
#    SHOW TABLE STATUS FROM `lots` LIKE 't_wo_message_log'\G
#    SHOW CREATE TABLE `lots`.`t_wo_message_log`\G
#    SHOW TABLE STATUS FROM `lots` LIKE 't_work_order'\G
#    SHOW CREATE TABLE `lots`.`t_work_order`\G
# EXPLAIN /*!50100 PARTITIONS*/
SELECT  wo.id as woId,
                 w
.....................
 

可以看到比mysqldumpslow分析的结果返回的更加的丰富，可以看到：

• 一个统计信息
  sql在总sql执行中的百分比，接着时间的统计
  执行的主机
  执行的表
  …

具体在我们生产环境或者工作环境中是有什么样的sql，大家还是要具体的来通过方法进行分析下，才能得出具体的平常工作中所要进行优化的SQL是什么样的。通常来说，我们关心的SQL通常是那些 执行次数非常的多，同时呢扫描数据行又远远大于所输出的行的SQL，比如说重要的执行次数，并且返回的行数要远远小于扫描的行数等等

通过实时监控的方式发现问题

监控长时间运行的SQL

SELECT  id,user,host,DB,command,time,state,info
--这条视图中记录了所有正在执行的SQL语句的一些信息
FROM information_schema.PROCESSLIST
-- 每个SQL所执行的时长
WHERE TIME>=60

提醒一下，如果mysql的账号不具有搜索权限的用户的话，那么在PROCESSLIST表中就只能查看到自己所使用的账号在执行的一些SQL，而无法查询到服务器上所有账号在执行的SQL

分析执行计划

我们为什么要关注执行计划?

因为一条SQL的执行计划可以告诉我们很多的如何优化SQL的信息，首先我们可以

• 了解SQL如何访问表中的数据
  是使用全表扫描的方式来获取符合条件的数据呢？还是使用索引扫描或是索引查找的方式来获取表中的数据的，这对于SQL的执行效率来说，是有很大的不同的。

• 了解SQL如何使用表中的索引
  另外一个表中可能会存在多个不同的索引，通过SQL的执行计划呢，我们还可以清楚的知道SQL是如何使用表中的索引的，是否使用到了正确的索引，也会对SQL的执行效率带来很严重的影响，

• 了解SQL使用的查询类型
  是否使用到了子查询，是否是关联查询等等这些信息。

如何获取执行计划

只需要在所要分析的执行计划的语句之前加上这个EXPLAIN 关键字就可以了，在EXPLAIN关键字之后呢，就是所要分析执行计划的SQL语句。
在MySQL5.6之前，只支持对SELECT语句分析执行计划

EXPLAIN
	SELECT statement

在MySQL8.0中，是支持对这个SELECT、DELETE、UPDATE、INSERT以及REPLACE语句来分析执行计划的

EXPLAIN
	{explainable_stmt | FOR CONNECTION connection_id }

explainable_stmt :{
	  SELECT statemnet
	| DELETE statemnet
	| INSERT statemnet
	| REPLACE statemnet
	| UPDATE statemnet
}

REPLACE语句我们没有见到过，其实它和INSERT语句是非常类似的，只是当要插入的数据和表中存在的数据存在重复的话，不管是主键还是唯一索引，都会把原来的数据替换为新插入的数据，而如果表中不存在同要插入的数据存在唯一索引或主键冲突的情况，则会直接插入新的数据。
FOR CONNECTION connection_id是MySQL8.0支持的，可以对正在执行的SQL进行获取执行计划，这个connection_Id就是performation_schema.PROCESSLIST表中查出来的正在执行的SQL连接的id

执行计划内容分析

下面是一个执行计划的内容

			id:1
   select_type: SIMPLE
         table: imc_course
    partitions: NULL
          type:ALL
 possible_keys: NULL
           key: NULL
       key_len: NULL
           ref: NULL
          rows: 100
      filtered: 33.33
         Extra: Using where

id列

id列的值只能有两种情况，一种就是为一组数字，比如1、2、3、4这样的一个序列。另一种情况就是NULL，也就是空值。
如果id列的值为一个数字序列，则说明查询中的SQL语句对这个数据库对象的操作的顺序；如果id列的值为NULL的情况下，则代表的是数据是由另外两个查询进行union操作后，所产生的的一个结果集。

• ID表示查询执行的顺序
• ID相同时由上到下执行
• ID值不同，由大到小执行

ID值相同的示例

EXPLAIN 
SELECT course_id,class_name,level_name,title,study_cnt
FROM imc_course a
JOIN imc_class b ON a.class_id=b.class_id
JOIN imc_level c  ON c.level_id=a.level_id
WHERE study_cnt>3000

id	select_type	trable	partitions	type	possible_keys	key	key_len	…
1	SIMPLE	c	(…	ALL	PRIMARY	(NULL)	(NULL)	…
1	SIMPLE	a	(…	ALL	index_classId	(NULL)	(NULL)	…
1	SIMPLE	b	(…	eq_ref	PRIMARY	PRIMARY	2	…

这个执行计划中，一共返回了三行结果，并且三行的返回结果id值都相同，是1。我们前面说过，这个Id值实际上可以看作是SQL中所查询的操作的执行的顺序，也就是sql中第一个执行的是id为1的，第二次执行的就是id值为2的，依次类推，由于在当前的SQL中只有一个查询操作，因此虽然是关联了多个表，但是id值的也全都为1的，在这种情况下，按照刚才我们所说的就应该由上到下执行sql的读取sql的执行计划。
这里我们可以看到第一行记录是对c表进行操作，c表就是课程的难度表，这个查询就是以c表为基础来进行的循环嵌套的一个关联查询，这也就是SQL优化器会根据表的统计信息来进行一些数据的调整。

ID值不同的查询

--查询再课程章节表中不存在章节的课程，即没有章节的课程
SELECT a.course_id,a.title
FROM imc_course a 
WHERE a.couorse_id NOT IN (
	SELECT course_id
	FROM imc_chapter b
)

id	select_type	trable	partitions	type	possible_keys	key	key_len	…
1	PRIMARY	a	(…	index	(NULL)	udx_classid	(NULL)	…
2	DEPENDENT SUBQUERY	b	(…	index_subquery	udx_classid	udx_classid	(NULL)	…

select_type

显示出了我们所执行查询的查询类型，可能会出现的值有很多，都有哪些呢？我们接下来看一下：

值	含义
SIMPL	不包含子查询或不包含UNION操作的查询
PRIMARY	查询中如果包含子查询，那么最外层的查询对应的表则被标记为PRIMARY
SUBQUERY	SELECT子句里列表中的子查询
DEPENDENT SUBQUERY	依赖外部结果的子查询

例如下面的

id	select_type	trable	partitions	type	possible_keys	key	key_len	…
1	PRIMARY	a	(…	index	(NULL)	udx_classid	(NULL)	…
2	DEPENDENT SUBQUERY	b	(…	index_subquery	udx_classid	udx_classid	(NULL)	…

id为1的查询结果依赖id为2的子查询的结果的，id为2的这一行的select_type值就是DEPENDENT SUBQUERY——被依赖的一个子查询

select_type的值还有可能是下面的值：

值	含义
UNION	nion操作的第二个或是之后的查询的值为union
DEPENDENT UNION	当union做子查询时，第二个或是第二个后的查询的select_type值
UION_RESULT	UION产生的结果集，查询中一个表是UNION操作产生的结果集
DERDERIVED	如果FROM子句中有子查询时候，也就是说是导出表，MySQL会递归执行并将结果放到一个临时表中，这个临时表就是我们刚才所说的派生表（导出表）

table

table的值就是输出数据行所在的表的名称。如果我们在FROM子句中为这个表起一个别名，那么table显示的就是起的别名。
另外，table的值为 <union M,N>，代表的是由ID为M、N查询union产生的结果集
另外情况，table的值会显示为<derived N>/<subquery N>，代表ID为N的查询产生的结果，这ID为N的查询通常也是子查询，子查询的结果集通常会产生临时表，table的值就是N来的临时表(导出表)

partitions

改值只有在查询分区表的时候才有意义，当我们查询分区表时，这一列就会显示所查询分区的id，对于非分区表，就是会显示为NULL

type

通过type列，我们能知道查询连接的类型。关于连接查询需要说明，在MySQL中的查询并不是使用JOIN关键字进行的查询才叫做关联查询，实际上MySQL会认为每一个查询会是关联查询，就算我们只查询了一个表，对于MySQL来说也是一个关联查询，在分析执行计划的时候也会有一个连接类型。
我们可以这样认为，type列的取值，实际上体现了MySQL访问数据的一种方式，MySQL执行计划中type的值按照性能从高到底排列：↓

性能	值	含义
高	system	这是const联接类型的一个特列，当查询的表只有一行时使用
.	const	表中有且仅有一个匹配行时使用，如对主键或是唯一索引的查询，这是效率最高的联接方式
.	req_ref	唯一索引或主键查找，对于每个索引键，表中只有一条记录与之匹配。前表返回的数据行为基础，对于每一个前表返回的行都会从本表中读取一行数据，这种情况会使用。通常会出现在JOIN查询中，对于关联键本表都会有且只有一条与之匹配的，这种情况也只会出现在使用唯一索引或主键进行关联的情况下
.	ref	常见于使用非唯一索引进行数据过滤的查询，会返回所有匹配某个索引关键字的行，同req_ref不同的是ref可能会返回多个数据
.	ref_or_null	l类似于ref类型的查询，但附加了对NULL值列的查询
.	index_merge	当前查询会使用索引和列来进行优化，在一般情况下MySQL只能使用到表中的一个索引来对查询进行优化，但是在5.6版本之后，MySQL支持了索引合并(支持多个索引来进行查询数据后再进行合并优化)
.	range	索引范围扫描，常见于between、>、<过滤的查询条件
.	index	FULL index scan 全索引扫描，跟全表扫描类似，区别是扫描的是索引树
低	ALL	FULL Table Scan 全表扫描，查询中会从头到尾对表进行扫描，这是效率最差的联接方式，通常说明表中没有索引，需要着重进行优化的地方

possible_keys

指明了可能用到哪些索引，这是基于查询所使用的列和过滤条件进行判断的，查询过滤条件所使用到的索引都会被列出来，但是不一定会被使用到

key

MySQL在查询优化时候实际使用到的索引，如果在表中没有可用索引，那么key这一列的值就会显示为NULL，同时出现在key中实际使用的索引页可能不会出现在上面的possible_keys中，这时候说明查询可能使用到了覆盖索引(没有用到表，用到索引查询)

key_len

显示了MySQL在索引里使用的字节数。注意，在一个联合索引中，如果有3列，这3列的总长度是100字节的话，key_len的值可能少于100字节的，比如只有30个字节，这就说明了在查询中并没有使用到联合索引的所有列，而是使用到了前面的一列或两列，key_len的值实际上是显示的MySQL所能使用到的索引的最大长度。另外需要注意的是，key_len的长度是由表中定义的长度来计算的，并不是存储的数据的实际长度，所以这也是为什么说在进行表的设计时候一定要使用能够满足我们使用需要的最短的数据长度，来进行数据存储的，因为会影响到SQL执行计划的生成

ref

表示那些列或者常量(const)会用于通过索引和过滤来进行数据库过滤，如果查询并不使用任何索引，那ref的值就是NULL

rows

• 体现了MySQL根据统计信息预估出来的要获得所需要查询数据的行数
• 关联查询中，rows列的值代表的内嵌循环的次数，在内嵌循环中获取一次所要查询的值，都要对目标表进行一次查找，所以循环的次数越多，性能也就越差

filtered

反馈了MySQL预估的返回符合条件的数据集占其所扫描数据行的百分比，这也是一个预估值，并不准确，但是也在一定程度上评估MySQL的查询成本。百分比越高说明查询性能越好

Extra

包括了不适合在其他列中所显示的额外信息，常见的值就是以下这些：↓

值	含义
Distinct	表示当前MySQL处理的查询是使用了Distinct优化操作的，那么在找到第一个匹配的数据后呢，会停止继续查找同样值的动作，这样的优化是可以提升查询性能的
Not exists	表示MySQL的处理不存在于某一个条件中这种数据查询呀，就使用到了Not exists优化，也是为了提升查询的性能 ，比如where id not in (…)
Using filesort	使用文件来进行排序，通常会出现在order by或group by查询中，表示需要使用外部索引进行排序，而不是直接使用索引进行排序
Using index	使用了覆盖索引进行查询
Using temporay	MySQL需要使用临时表来处理查询，常见于排序、子查询和分组查询，这种情况也是需要我们关注的，因为使用到了外部的临时表，性能并不会太好
Using where	需要在MySQL服务器层使用WHERE条件来过滤数据
select tables optimized away	直接通过索引来获得数据，不访问表

SQL优化的手段

我们知道了如何来分析一个SQL的执行计划，以及这些执行计划能够为我们提供的那些信息。接下来我们要根据执行计划给我们提供的信息来进行SQL优化了，一般来说只有两个手段：↓

• 优化SQL查询所涉及到的表中的索引
  目的是为了SQL可以更好的利用索引来获取所需要的数据，而不用再对表进行扫描。
• 改写SQL以达到更好的利用索引的目的
  当单纯的索引优化无效的时候，我们就要想办法对SQL来进行改写。

索引优化

索引优化，我们曾经多次的提高，相信在日常工作中也是或多或少的能够接触到索引。

索引优化的作用

什么是索引，索引的作用又是什么呢？

• 高数存储引擎如何快速的查找到所需要的数据
  简单来说，索引就同我们数据的存储目录一样，最主要的作用就是告诉我们存储引擎去哪里可以查找到我们所需要的数据。举个例子：
  
  比如在技术方向列有索引，同时在技术方向列中存在几种不同的值，分别为MySQL、PgSQL等，这些值就是索引的一个节点，而他们所指向的就是实际存储这些课程记录的物理地址，比如我们呢可以在物理地址为0001和0003的地址找到Mysql的课程，这样通过索引我们就可以快速的定位到我们所要查找到数据的位置，如果没有索引，mySQL就要顺序的扫描，从0001到0111开始这个物理块扫才能查到所要查找的数据。就好比看书的时候，我们只对一本书的章节感兴趣，我们可以通过目录来找到这个章节所在的页，直接翻到那一页直接进行查看；如果没有目录的话，我们要从书的第一页开始一直翻看，直到找到我们感兴趣的章节为止。

Innodb支持的索引类型

MySQL的存储引擎是在存储引擎层实现的，而不是在服务层实现的，这就决定了不同的存储引擎，工作方式上可能不同的。同时也不是所有存储引擎都支持所有类型的索引。即使同一种索引的类型在不同的存储引擎中在底层的实现上也是不相同的。
我们看下Innodb存储引擎支持的索引类型：

• Btree索引
  我们在平常工作中，如果没有特别的说明，所指的素引全是这种索引。同时Btree索引也是我们在MySQL中最常用的一种索引类型。
• 自适应HASH索引
  之所以称之为自适应的HASH索引，是因为这个索引是Innodb存储引擎为了优化查询性能而自动建立的，并不需要开发人员或者DBA来手动管理，我们一般并不用关注这类索引。
• 全文索引
  在MySQL5.7之后，Innodb还支持了全文索引。用于搜索引擎，主要用于字符串类型的数据列。不过其对中文的支持不是太好，索引我们需要使用全文索引的话，还是建议不如考虑直接使用搜索引擎类的服务来代替全文索引
• 空间索引
  在MySQL5.7之后，Innodb还支持了全文索引。是使用在点线面这种空间数据类型之上。

Btree索引的特点

• B+树的结构来存储数据
  那么在B+树的每一列的节点中，都包含了指向下一列的指针，这样的目的是为了方便叶子节点进行遍历。对于不熟悉数据结构的人来说，什么是B+树可能很难理解，下面我们通过一个简单的图示来直观的展示下这种存储结构

这张图我们可以看到，B+树本身是一种平衡的二叉树，每一列的节点到根的距离都是相同的，并且所有记录节点都是按键值的大小顺序的放在同一层的叶子节点上的，并且每一列的叶子节点间是通过指针进行连接的。这样做的好处是可以方便的进行快速查找，这就是典型的B+树存储结构，但是对于不同的存储引擎可能具体实现又有所不同。比如MySAM的B+树索引在叶子节点上所指向的应该是数据行的物理存储位置，就是磁盘上的一个物理位置，而Innodb存储引擎在B+树的叶子节点上指向的数据行的主键位置

• Btree索引适用于全值匹配的查询
  什么是全值匹配查询呢？举个例子，如果我们想要查询课程类型为MySQL的课程信息我们就可以使用class_name="mysql’这样的过滤条件，只有那些课程类型的值同‘mysql’这个字符串完全匹配的课程才会被查询出来，这就是全值匹配的查询。
  上面例子是对一个值的全值匹配，其实对class_name in (‘mysql’,‘postgreSQL’)这样的查询，其实也是一种全值匹配的查询，这个时候我们会查询出所有课程类型中通‘mysql’、'postgreSQL‘完全匹配的课程信息。之前存在一种误解，认为MySQL使用in列表查询，是无法使用到索引的那么通过上面的分析我们可以知道这种看法呢是不正确的，在MySQL中使用in列表查询也是可以使用到Btree索引的，只有当in列的值过多时，MySQL优化器呢才可能会认为这种使用全表扫描的方式，来进行过滤数据要优于使用索引查找的方式，从而不会使用到索引

• Btree索引适合处理范围查找
  由于Btree索引是按照索引之间的顺序排列的，也是适合工作在一种范围查找的情景下，所谓的范围查找就是指对数据在一个范围内进行过滤的情况下， 比如我们可以使用 between and 查找，还可以使用< 或者> ，这种查询同样也是范围查找。

• Btree索引从索引的最左侧列开始匹配查找列
  假设我们使用create index idx_title_studyCnt on imc_course (title,study_cnt) 建立复合索引，下面3个查询中，哪一个会用到索引
  A) study_cnt > 3000
  B) study_cnt > 3000 and title='MySQL‘
  C) title=‘MySQL’
  选项A)只使用了study_cnt列作为过滤条件，但是我们索引最左侧列是title列，B树索引要求索引必须同最左侧列开始匹配，所以选项A)是无法使用到索引的
  选项B)是同时对study_cnt和title列进行了过滤，虽然说在过滤的顺序上是先对study_cnt进行过滤再对title列进行过滤，但是仍然是可以使用到复合索引的。这有个我们另外一个对索引的误解——查询的过滤的顺序必须要和索引的顺序相同才能使用到索引，这种看法也是错误的，MySQL的查询优化器可以自动的调整查询过滤条件的顺序以适应索引，从而利用到我们的索引
  选项C)只使用了title列进行了过滤，但是我们索引的最左侧类恰恰也是title，索引选项C)也是可以使用到title列和study_cnt这样的符合索引的

应该在什么列上建立索引？

说实话，并没有一个统一的答案，也不存在使用于所有的场景下的答案。通常我们新建立一个索引，都要结合具体的查询语句以及表中的索引来进行分析，从而决定来建立什么索引。
通常情况下，我们可以在以下列中建立索引

• WHERE 子句中的列
  首先我们可以考虑，在SELECT、DELETE、UPDATE语句中的WHERE子句来建立索引，但是也并不是说所有出现在WHERE子句中的列全要建立索引，我们还要将一些其它的条件，比如这列是否有很好的筛选性，所谓的筛选性就是指这一列的数据是否有很多的重复值，如果一个列的数据全是不相等的，比如主键列，我们就说这样的列筛选性很好，如果一个列中的数据基本上全是重复的，那么久说明这一列筛选性很差。我们要在筛选性好的列上建立索引，这个时候可以来看一个例子：
  ‘我们要查询出2019年1月1号之后注册的男性会员的昵称

EXPLAIN
SELECT user_nick
FROM imc_user
WHERE sex=1 AND reg_time>'2019-01-01'

执行计划

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	imc_user	(…	ALL	(NULL)	(NULL)	(NULL)	(NULL)	2530	3.33	Using where

可以看到查询时非常的差的，那么我们如何对这一列进行优化呢？我们来建立索引，我们过滤条件有两列：一个是sex列，另一个是reg_time列，先来比较下这两列的筛选性

	    -- 性别不同的数据总共有多少个
 SELECT COUNT(DISTINCT sex)
 	    --date不同的总个数，不要时分秒是因为加上的话将会很多的个数
       ,COUNT(DISTINCT date_format(reg_time,'%Y-%m-%d'))
       -- 总个数
       ,count(*)
       --性别不同的总个数/总个数
       ,COUNT(DISTINCT sex)/COUNT(*) 
       -- date不同的总个数/总个数
       ,COUNT(DISTINCT date_format(reg_time,'%Y-%m-%d'))/COUNT(*)
FROM imc_user

count(…	count(…	count(*)	count(DIS…)	count(…
2	454	2530	0.0008	0.1794

通过以上分析，注册时间的筛选性要远远大于性别列，我们要在reg_time列上建立索引

CREATE INDEX idx_regTime ON imc_user(reg_time)

再来看下执行计划

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	imc_user	(…	range	idx_regTime	idx_regTime	5	(NULL)	516	10.00	Using Index condition;Using where

可以看到，filtered是10%，筛选性要远远的好于用不到索引的情况

如果我们在性别列上创建索引，它的执行计划是

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	imc_user	(…	ALL	idx_sex	(NULL)	(NULL)	(NULL)	2530	3.33	Using where

看到是没有使用索引，过滤性也是3.33，跟没有创建索引的情况是一样的，这就证明了在筛选性不好的列上建立索引实际上是没有任何的作用的。

• 包含在ORDER BY 、GROUP BY 、DISTINCT 中的字段
  在条件允许的情况下，在ORDER BY 、GROUP BY 、DISTINCT 字段上所建立的索引可以提高排序的性能，并避免排序过程中，使用到临时表，但是要注意并不是所有的ORder by 操作都可以使用到索引，MySQL只有在满足下面的条件时order by操作上才会使用到索引，那些条件呢？

  1. 索引列的顺序和order by子句中列的顺序要完全一致
  2. 其次索引中列的方向（比如升序降序）和order by子句中规定列的方向都要一致
  3. 在多个表的关联查询中，order by中的字段呀要全部在第一张表中

  可以看到，条件还是相当苛刻的

• 多表的JOIN的关联列

示例

EXPLAIN
-- 查询高级别的分类为‘MySQL’的课程的一些信息
SELECT course_id,b.class_name,d.type_name,c.level_name,title,score
FROM imc_course a
JOIN imc_class b ON b.class_id=a.class_id
JOIN imc_level c  ON c.level_id=a.level_id
JOIN imc_type d ON d.type_id=a.type_id
WHERE c.level_name='高级'
AND b.class_name='MySQL'

执行计划：

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	EXtra
1	SIMPLE	b	(NULL)	const	PRIMARY,uqx_classname	uqx_classname	32	const	1	100.00	Using
1	SIMPLE	c	(NULL)	ALL	PRIMARY	(NULL)	(NULL)	(NULL)	4	25.00	Using
1	SIMPLE	a	(NULL)	ALL	(NULL)	(NULL)	(NULL)	(NULL)	100	1.00	Using
1	SIMPLE	d	(NULL)	req_ref	PRIMARY	PRIMARY	2	imc_db.a.type_id	1	100.00	(NULL)

可以看到id都为1，我们应该从上往下看，查询类型都是SIMPLE，驱动表是b表。
在这个查询中通过type列可以看出来，c、a表都是用的全表扫描ALL，只有b、d表用到了索引。而获取到查询所需要的所有数据，大概需要14100*1=400次查询，以为是一个嵌套循环嘛，在嵌套循环里rows行的值就代表了嵌套循环的次数，也就是说b表中获取1行，我们需要从下面的c表中再查询4条，在下面的表中再查询100次，因此一共是400次查询才能够查询出所需要的数据。其中查询次数最多的就是a表100次，接下来我们看下a表的索引，看看为什么用到全表扫描

SHOW CREATE TABLE imc_course

可以看到表中只有主键索引和title列上的唯一索引，也就是说在a表和b、c、d表的关联上并没有索引。我们知道在表的关联列上一定要建立好索引，所以呢我们在这里先把a表的索引建好

--a表和b表是根据class_id关联的，a表和c表是根据level_id关联的，所以我们要建立一个联合索引
-- a表和d表根据type_id来关联的，顺序按照键值的可筛选性来排序的，可以看到分类>方向>难度
CREATE INDEX idx_classId_typeid_levelId ON  imc_course(class_id,type_id,level_id)

索引建立好后，我们再来看下执行计划的变化：

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	EXtra
1	SIMPLE	b	(NULL)	const	PRIMARY,uqx_classname	uqx_classname	32	const	1	100.00	Using index
1	SIMPLE	c	(NULL)	ALL	PRIMARY	(NULL)	(NULL)	(NULL)	4	25.00	Using where
1	SIMPLE	a	(NULL)	ref	idx_classid_typeid_levelid	idx_classid_typeid_levelid	(NULL)	(NULL)	7	10.0	Using index
1	SIMPLE	d	(NULL)	req_ref	PRIMARY	PRIMARY	2	imc_db.a.type_id	1	100.00	(NULL)

我们可以看到a表已经使用到索引了，所使用的就是刚才建立的复合索引。再看rows行是7，a表只需查询7次就可以了，而整个获取到的结果集只需要4*7=28次就可以获取到之前需要400次获取到的这个结果集，那么从这一点来看呢查询效率已经有了很大的提升。但是还并没有完，我们来看查询中关于c表的查询有那些呢？有level_id同a表进行关联，还有where子句中level_name进行过滤的，所以我们在c表的level_name上建立一个二级索引，我们知道Innodb的非主键索引的叶子节点指向都是主键，它会把主键索引自动带上

CREATE INDEX idx_levelname ON imc_level(level_name)

建立好索引后，我们在回头看下执行计划

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	EXtra
1	SIMPLE	b	(NULL)	const	PRIMARY,uqx_classname	uqx_classname	32	const	1	100.00	Using index
1	SIMPLE	c	(NULL)	ref	PRIMARY,udx_levelname	udx_levelname	32	const	1	100.00	Using where
1	SIMPLE	a	(NULL)	ref	idx_classid_typeid_levelid	idx_classid_typeid_levelid	2	const	7	10.0	Using index
1	SIMPLE	d	(NULL)	req_ref	PRIMARY	PRIMARY	2	imc_db.a.type_id	1	100.00	(NULL)

可以看到已经都可以用到索引了，并且呢最后只需要执行7次就可以把需要的数据集查取出来，已经比400次少了很多，性能也提升了很多。
上面就是一个SQL优化的步骤，在工作中要优化的SQL就可以按照这个步骤，对它进行优化，我们的目标是要使每一个查询都用到索引，并且减少嵌套循环的次数，增加过滤的百分比。

如何选择符合索引键的顺序？

• 区分度最高的列放在联合索引的最左侧
  由于B数索引是从左侧键值开始过滤数据的，所以第一个键值的区分度越高就能过滤的数据量也就越多，后面处理数据的性能也会越好
• 使用最频繁的列放在联合索引的最左侧
  在区分度差不多的情况下，应该把最频繁使用的列放在联合索引的最左侧。同样是由于B树索引是从最左侧开始使用索引的，把最频繁的列放在最左侧，是可以提高索引的利用率的。
• 尽量把字段长度小的列放在联合索引列的最左侧
  在以上两个条件都差不多的情况下，我们可以把字段长度小的列放在联合索引的最左侧。对于Innodb来说，它一叶大小是16k，那么键值对的长度越小，那么一叶所能记录的数据也就越多，查询的io性能相对来说也会越好

在工作中，我们经常会遇到明明查询上建立了索引，但是查询却无法使用索引的情况，那么这样和很多因素是有关的。比如我们使用索引所命中的数据占了全表中的大部分数据的时候，那么MySQL查询优化器就会认为使用全表扫描的方式性能会更高，所以就不会使用到索引来对查询进行优化了。

Btree索引的限制

如果我们按照受限的方式来使用Btree索引，同样呢也不能用到相应的索引。下面来看看限制的条件：

• 只能从最左侧开始按索引键的顺序使用索引，不能跳过索引键
  意思是，如果我们不是按照索引最左列开始查找的，则无法使用到索引。“不能跳过素引键” 这种指的和前一句稍微有些不同，指的是左边的列而不是最左边的列，举个例子：假设我们有一个三个列A、B、C组成的联合索引，如果查询只包含了A、C列，那么对于这个查询来说就只能用到索引中A列来进行查询和过滤，而无法用到C列
• NOT IN 和<>操作无法使用索引
• 索引列上不能使用表达式（计算）或是函数

索引使用的误区

大家知道索引对数据库的性能非常的关键，好的索引可以提高数据库的查询性能，而不好的索引同样也可以降低数据库的查询性能。

• 索引越多越好
  一方面索引虽说在一定程度上可以增加查询效率，当时同样会降低插入和更新数据的效率；另一方面索引甚至在一些情况下，索引同样会降低查询效率，这是因为MySQL优化器在选择如何优化查询的时候，会根据统计信息对每一个可以用到的索引评估，以生成一个最优的执行计划，而如果我们同时有很多索引都可以用于查询的话，这就增加了MySQL优化器生成查询计划的时间，这样也就降低了SQL的查询性能
• 使用IN列表查询不能使用到索引
  在MySQL中如果使用或运算符，关联多个查询的话，可能无法用到素引，但是IN列表查询不同于OR查询，是可以用到索引的。索引我们在查询改写的时候方法就是把OR关联查询改为IN列表查询，但是如果IN列表中的值非常的多，多到了已经可以把表中的大部分数据都查询出来的时候，MySQL的查询优化器就会认为使用全表扫描的方法来获取数据的性能会优于索引查找，就会造成无法使用索引的情况
• 查询的过滤条件必须同索引键顺序相同才可以用到索引
  MySQL优化器会自动调整这个过滤条件的顺序，以适应素引键值的顺序，从而正确的用到索引。

SQL改写优化

SQL改写的原则

• 使用outer join 代替not in

EXPLAIN
--需求： 查询出不存在课程的分类名称
SELECT class_name
FROM imc_class
WHERE class NOT  IN (SELECT class_id FROM imc_course)

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	EXtra
1	PRIMARY	imc_class	(NULL)	index	(NULL)	uqx_classname	32	const	1	100.00	Using index
2	DEPENDENT SUBQUERY	imc_course	(NULL)	index_subquery	idx_classid_typeid_levelid	idx_classid_typeid_levelid	32	const	1	100.00	Using where

可以看到执行计划使用到了索引，为什么呢？我们继续看下改写后的情况

EXPLAIN
SELECT class_name
FROM imc_class a
LEFT JOIN imc_course b ON a.class_id=b.class_id
WHERE b.class_id IS NULL

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	EXtra
1	PRIMARY	imc_class	(NULL)	index	(NULL)	uqx_classname	32	const	1	100.00	Using index
2	DEPENDENT SUBQUERY	imc_course	(NULL)	index_subquery	idx_classid_typeid_levelid	idx_classid_typeid_levelid	32	const	1	100.00	Using where

可以发现执行计划和没有改写前是一模一样的，这是为什么呢？是因为在MySQL8.0中，对这种情况已经可以自动的来进行优化，也就是说如果使用MySQL5.5或者5.6的版本就需要手动来进行查询的改写，来让它能够用到索引，而在MySQL5.7或8.0版本中MySQL查询优化器就已经支持自动的来对这种NOT IN 的查询优化成LEFT JOIN的查询。
但是不是说这种优化是不正确的，只能说现在的查询优化器比较智能，替我们做到了一些我们原来需要手动做的事情。

• 使用CTE代替子查询
  其实公共表表达式和子查询基本上是类似的，都是会生成一个临时性表，只不过公共表表达式所生成的临时表是一个命名类型的临时表，并且在查询中是可以多次引用的。代替子查询一方面可以简化SQL来增加SQL的可读性，另一方面也有利于查询优化器对查询进行优化。
• 拆分复杂的大SQL为多个简单的小SQL
  目前来说，MySQL还并不支持并发查询，也就是说一个SQL只能使用到一个CPU的核心，虽然在最新的MySQL8.0.15版本中已经有了一些改变，对count操作已经支持使用到多个CPU来进行并行计算了，但是对于其它类型的SQL同样还是不能使用到多个CPU运行的。同样如果把一个大的复杂的SQL，来改写成多个小的复杂的SQL，然后再进行合并计算，同样也是一种常见的改写SQL的优化方案
• 巧用计算列优化查询
  巧用计算列来优化必需要用函数进行查询的列，以使可以在这一列上用到索引。计算列是MySQL5.7后所使用到的一个功能