深入浅出mysql优化--一篇博客让你精通mysql优化策略--上
一篇博客和大家一起学习mysql优化的通用策略。
内容花了好些时间来整理书写，如果觉得有用，还请点个赞，还有就是发现博客园的MD文本格式和在其他软件的不太同，格式调的也不好，还请将就一下
接下来一起来学习一下mysql优化的内容吧(注意 本文中使用的字符集是4个长度的)
1. 一条查询sql的执行过程
select * from T where ID=10; 的执行过程详解
-
MySQL 的逻辑架构
大体来说，MySQL可以分为 Server层 和 存储引擎层 两部分
Server层：
包括连接器、查询缓存、分析器、优化器、执行器等，涵盖 MySQL 的大多数核心服务功能，
以及所有的内置函数（如日期、时间、数学和加密函数等），所有跨存储引擎的功能都在这一层实现，
比如存储过程、触发器、视图等存储引擎层：
负责数据的存储和提取。
其架构模式是插件式的，支持 InnoDB、MyISAM、Memory 等多个存储引擎。
现在最常用的存储引擎是 InnoDB，它从 MySQL 5.5.5 版本开始成为了默认存储引擎执行create table建表的时候，如果不指定引擎类型，默认使用的就是InnoDB。 不过，也可以通过指定存储引擎的类型来选择别的引擎， 比如在 create table语句中使用 engine=memory, 来指定使用内存引擎创建表。 不同存储引擎的表数据存取方式不同，支持的功能也不同 从图中不难看出，不同的存储引擎共用一个Server层，也就是从连接器到执行器的部分。 -
第一步：连接器
第一步，先连接到这个数据库上，这时候接待的就是连接器。
连接器负责跟客户端建立连接、获取权限、维持和管理连接。
连接命令一般是这么写的： mysql -h$ip -P$port -u$user -p输完命令之后，需要在交互对话里面输入密码。
虽然密码也可以直接跟在 -p 后面写在命令行中，但这样可能会导致密码泄露。
如果连的是生产服务器，强烈建议不要这么做如果连接命令中的 mysql 是客户端工具，用来跟服务端建立连接。
在完成经典的 TCP 握手后，连接器就要开始认证身份，这个时候用的就是输入的用户名和密码1. 如果用户名或密码不对，就会收到一个"Access denied for user"的错误，然后客户端程序结束执行 2. 如果用户名密码认证通过，连接器会到权限表里面查出用户拥有的权限。 之后，这个连接里面的权限判断逻辑，都将依赖于此时读到的权限 这就意味着，一个用户成功建立连接后，即使用管理员账号对这个用户的权限做了修改， 也不会影响已经存在连接的权限，修改完成后，只有再新建的连接才会使用新的权限设置连接完成后，如果没有后续的动作，这个连接就处于空闲状态，可以在 show processlist 命令中看到它，
以下图其中的Command列显示为“Sleep”的这一行，就表示现在系统里面有一个空闲连接
客户端如果太长时间没动静，连接器就会自动将它断开。
这个时间是由参数 wait_timeout控制的，默认值是 8 小时
如果在连接被断开之后，客户端再次发送请求的话，就会收到一个错误提醒：Lost connection to MySQL server during query。
这时候如果要继续，就需要重连，然后再执行请求了
-
长连接和短连接
长连接： 是指连接成功后，如果客户端持续有请求，则一直使用同一个连接。
短连接： 是指每次执行完很少的几次查询就断开连接，下次查询再重新建立一个。建立连接的过程通常是比较复杂的，所以建议在使用中要尽量减少建立连接的动作，也就是尽量使用长连接
但是全部使用长连接后，可能会发现，有些时候 MySQL 占用内存涨得特别快，
这是因为 MySQL 在执行过程中临时使用的内存是管理在连接对象里面的。这些资源会在连接断开的时候才释放。
所以如果长连接累积下来，可能导致内存占用太大，被系统强行杀掉（OOM），从现象看就是 MySQL 异常重启了那么怎么解决这个问题呢？可以考虑以下两种方案
1. 定期断开长连接。使用一段时间，或者程序里面判断执行过一个占用内存的大查询后，断开连接，之后要查询再重连 2. 如果你用的是 MySQL 5.7 或更新版本，可以在每次执行一个比较大的操作后，通过执行 mysql_reset_connection 来重新初始化连接资源。这个过程不需要重连和重新做权限验证，但是会将连接恢复到刚刚创建完时的状态。 -
第二步：查询缓存
连接建立完成后，就可以执行 select 语句了。执行逻辑就会来到第二步：查询缓存
MySQL 拿到一个查询请求后，会先到 查询缓存 看看之前是不是执行过这条语句。
之前执行过的语句及其结果可能会以 key-value 对的形式被直接缓存在内存中。key 是查询的语句，value 是查询的结果。
如果当前的查询能够直接在这个缓存中找到 key，那么这个value 就会被直接返回给客户端如果语句不在查询缓存中，就会继续后面的执行阶段。执行完成后，执行结果会被存入查询缓存中。
可以看到，如果查询命中缓存，MySQL不需要执行后面的复杂操作，就可以直接返回结果，这个效率会很高。但是大多数情况下建议不要使用查询缓存，为什么呢？
因为查询缓存往往弊大于利 查询缓存的失效非常频繁，只要有对一个表的更新，这个表上所有的查询缓存都会被清空。 因此很可能费劲地把结果存起来，还没使用呢，就被一个更新全清空了。 对于更新压力大的数据库来说，查询缓存的命中率会非常低。除非业务就是有一张静态表，很长时间才会更新一次。 比如，一个系统配置表，那这张表上的查询才适合使用查询缓存好在 MySQL 也提供了这种“按需使用”的方式。
可以将参数 query_cache_type 设置成 DEMAND，这样对于默认的 SQL 语句都不使用查询缓存。
而对于确定要使用查询缓存的语句，可以用 SQL_CACHE 显式指定，像下面这个语句一样select SQL_CACHE * from T where ID=10 需要注意的是，MySQL 8.0 版本直接将查询缓存的整块功能删掉了，也就是说 8.0 开始彻底没有这个功能了 -
第三步：分析器
如果没有命中查询缓存，就要开始真正执行语句了。MySQL 需要知道要做什么，因此需要对 SQL 语句做解析。
分析器先会做“词法分析”。
输入的是由多个字符串和空格组成的一条 SQL 语句，MySQL需要识别出里面的字符串分别是什么，代表什么。MySQL 从输入的"select"这个关键字识别出来，这是一个查询语句。它也要把字符串“T”识别成“表名 T”，把字符串“ID”识别成“列 ID”
做完了这些识别以后，就要做“语法分析”。
根据词法分析的结果，语法分析器会根据语法规则，判断输入的这个 SQL 语句是否满足 MySQL 语法如果语句不对，就会收到“You have an error in your SQL syntax”的错误提醒，比如下面这个语句 select 少打了开头的字母“s”
elect * from t where ID=1 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'elect * from t where ID=1' at line 1 一般语法错误会提示第一个出现错误的位置，所以你要关注的是紧接“use near”的内容 -
第四步：优化器
经过了分析器，MySQL 就知道要做什么了。在开始执行之前，还要先经过优化器的处理
优化器是在表里面有多个索引的时候，决定使用哪个索引，或者在一个语句有多表关联（join）的时候，决定各个表的连接顺序。
比如执行下面这样的语句，这个语句是执行两表的 joinselect * from t1 join t2 using(ID) where t1.c=10 and t2.d=20; 既可以先从表 t1 里面取出 c=10 的记录的 ID 值，再根据 ID 值关联到表 t2，再判断 t2里面 d 的值是否等于 20 也可以先从表 t2 里面取出 d=20 的记录的 ID 值，再根据 ID 值关联到 t1，再判断 t1 里面 c 的值是否等于 10这两种执行方法的逻辑结果是一样的，但是执行的效率会有不同，而优化器的作用就是决定选择使用哪一个方案
优化器阶段完成后，这个语句的执行方案就确定下来了，然后进入执行器阶段。
-
第五步：执行器
MySQL 通过分析器知道了要做什么，通过优化器知道了该怎么做，于是就进入了执行器阶段，开始执行语句
开始执行的时候，要先判断一下当前用户对这个表 T 有没有执行查询的权限，如果没有，就会返回没有权限的错误，
如下所示 (在工程实现上，如果命中查询缓存，会在查询缓存返回结果的时候，做权限验证。查询也会在优化器之前调用 precheck 验证权限)select * from T where ID=10; ERROR 1142 (42000): SELECT command denied to user 'b'@'localhost' for table 'T'如果有权限，就打开表继续执行。打开表的时候，执行器就会根据表的引擎定义，去使用这个引擎提供的接口
比如这个例子中的表T中，ID 字段没有索引，那么执行器的执行流程是这样的：
1. 调用 InnoDB 引擎接口取这个表的第一行，判断 ID 值是不是 10，如果不是则跳过，如果是则将这行存在结果集中； 2. 调用引擎接口取“下一行”，重复相同的判断逻辑，直到取到这个表的最后一行。 3. 执行器将上述遍历过程中所有满足条件的行组成的记录集作为结果集返回给客户端至此，这个语句就执行完成了
对于有索引的表，执行的逻辑也差不多。
第一次调用的是“取满足条件的第一行”这个接口，之后循环取“满足条件的下一行”这个接口，这些接口都是引擎中已经定义好的在数据库的慢查询日志中可以看到一个 rows_examined 的字段，表示这个语句执行过程中扫描了多少行。
这个值就是在执行器每次调用引擎获取数据行的时候累加的在有些场景下，执行器调用一次，在引擎内部则扫描了多行，因此引擎扫描行数跟rows_examined 并不是完全相同的。
-
问题
如果表 T 中没有字段 k，而执行了这个语句 select * from T where k=1,
那肯定是会报“不存在这个列”的错误： “Unknown column ‘k’ in ‘where clause’”。
那么这个错误是在我们上面提到的哪个阶段报出来的呢？分析器
2. 一条更新sql的执行过程
create table T(ID int primary key, c int);
update T set c=c+1 where ID=2;
在第一部分说过，在一个表上有更新的时候，跟这个表有关的查询缓存会失效，
所以这条语句就会把表 T 上所有缓存结果都清空。这也就是我们一般不建议使用查询缓存的原因。
接下来，分析器会通过词法和语法解析知道这是一条更新语句。
优化器决定要使用 ID 这个索引。
然后，执行器负责具体执行，找到这一行，然后更新。
与查询流程不一样的是，更新流程还涉及两个重要的日志模块：
redo log（重做日志）和 binlog（归档日志）。
-
重要的日志模块：redo log
以《孔乙己》这篇文章作为例子， 酒店掌柜有一个粉板，专门用来记录客人的赊账记录。 如果赊账的人不多，那么他可以把顾客名和账目写在板上。 但如果赊账的人多了，粉板总会有记不下的时候，这个时候掌柜一定还有一个专门记录赊账的账本 如果有人要赊账或者还账的话，掌柜一般有两种做法: 1. 直接把账本翻出来，把这次赊的账加上去或者扣除 2. 先在粉板上记下这次的账，等打烊以后再把账本翻出来核算 在生意红火柜台很忙时，掌柜一定会选择后者，因为前者操作实在是太麻烦了。 首先，你得找到这个人的赊账总额那条记录。可能找要一段实践，找到后再拿出算盘计算，最后再将结果写回到账本上 这整个过程想想都麻烦。相比之下，还是先在粉板上记一下方便。 想想，如果掌柜没有粉板的帮助，每次记账都得翻账本，效率是不是低得让人难以忍受？ 同样，在 MySQL 里也有这个问题，如果每一次的更新操作都需要写进磁盘，然后磁盘也要找到对应的那条记录，然后再更新， 整个过程 IO 成本、查找成本都很高。为了解决这个问题，MySQL 的设计者就用了类似酒店掌柜粉板的思路来提升更新效率。 而粉板和账本配合的整个过程，其实就是 MySQL 里经常说到的 WAL 技术， WAL 的全称是 Write-Ahead Logging，它的关键点就是：先写日志，再写磁盘，也就是先写粉板，等不忙的时候再写账本 具体来说，当有一条记录需要更新的时候，InnoDB 引擎就会先把记录写到 redo log里面，并更新内存， 这个时候更新就算完成了。 同时，InnoDB 引擎会在适当的时候，将这个操作记录更新到磁盘里面，而这个更新往往是在系统比较空闲的时候做， 如果今天赊账的不多，掌柜可以等打烊后再整理。 但如果某天赊账的特别多，粉板写满了， 又怎么办呢？这个时候掌柜只好放下手中的活儿，把粉板中的一部分赊账记录更新到账本中， 然后把这些记录从粉板上擦掉，为记新账腾出空间 与此类似，InnoDB 的 redo log 是固定大小的，比如可以配置为一组 4个文件， 每个文件的大小是 1GB，那么这块“粉板”总共就可以记录 4GB 的操作。 从头开始写，写到末尾就又回到开头循环写， 如下面这个图所示
write pos是当前记录的位置，一边写一边后移，写到第 3 号文件末尾后就回到 0 号文件开头。 checkpoint 是当前要擦除的位置，也是往后推移并且循环的，擦除记录前要把记录更新到数据文件 write pos 和 checkpoint 之间的是“粉板”上还空着的部分，可以用来记录新的操作。 如果 write pos 追上 checkpoint，表示“粉板”满了，这时候不能再执行新的更新， 得停下来先擦掉一些记录，把 checkpoint 推进一下 有了 redo log，InnoDB 就可以保证即使数据库发生异常重启， 之前提交的记录都不会丢失，这个能力称为crash-safe 要理解 crash-safe 这个概念，可以想想前面赊账记录的例子。 只要赊账记录记在了粉板上或写在了账本上，之后即使掌柜忘记了，比如突然停业几天， 恢复生意后依然可以通过账本和粉板上的数据明确赊账账目 -
重要的日志模块：binlog
redo log 是 InnoDB 引擎特有的日志，而 Server 层也有自己的日志，称为 binlog
最开始 MySQL 里并没有 InnoDB 引擎。
MySQL 自带的引擎是 MyISAM，但是MyISAM 没有 crash-safe 的能力，binlog 日志只能用于归档。
而 InnoDB 是另一个公司以插件形式引入 MySQL 的，既然只依靠 binlog 是没有 crash-safe 能力的，
所以 InnoDB使用另外一套日志系统——也就是 redo log 来实现 crash-safe 能力两种日志有以下三点不同
1. redo log 是 InnoDB 引擎特有的；binlog 是 MySQL 的 Server 层实现的，所有引擎都可以使用。 2. redo log 是物理日志，记录的是“在某个数据页上做了什么修改”； binlog 是逻辑日志，记录的是这个语句的原始逻辑，比如“给 ID=2 这一行的 c 字段加 1 ”。 3. redo log 是循环写的，空间固定会用完；binlog 是可以追加写入的。 “追加写”是指 binlog 文件写到一定大小后会切换到下一个，并不会覆盖以前的日志update 语句时的内部流程
1. 执行器先找引擎取 ID=2 这一行。ID 是主键，引擎直接用树搜索找到这一行。如果 ID=2 这一行所在的数据页本来就在内存中，就直接返回给执行器；否则，需要先从磁盘 读入内存，然后再返回。 2. 执行器拿到引擎给的行数据，把这个值加上 1，比如原来是 N，现在就是 N+1，得到新 的一行数据，再调用引擎接口写入这行新数据。 3. 引擎将这行新数据更新到内存中，同时将这个更新操作记录到 redo log 里面，此时 redo log 处于 prepare 状态。然后告知执行器执行完成了，随时可以提交事务。 4. 执行器生成这个操作的 binlog，并把 binlog 写入磁盘。 5. 执行器调用引擎的提交事务接口，引擎把刚刚写入的 redo log 改成提交（commit）状态，更新完成update 语句的执行流程图，图中浅色框表示是在 InnoDB 内部执行的，深色框表示是在执行器中执行的
将 redo log 的写入拆成了两个步骤：
prepare 和 commit，这就是"两阶段提交"。
-
两阶段提交
存在两阶段提交为了让两份日志之间的逻辑一致，怎样让数据库恢复到半个月内任意一秒的状态？ binlog 会记录所有的逻辑操作，并且是采用“追加写”的形式。如果你的 DBA 承诺说半个月内可以恢复， 那么备份系统中一定会保存最近半个月的所有binlog，同时系统会定期做整库备份。 这里的“定期”取决于系统的重要性，可以是一天一备，也可以是一周一备。 当需要恢复到指定的某一秒时，比如某天下午两点发现中午十二点有一次误删表，需要找回数据，那你可以这么做： 首先，找到最近的一次全量备份，如果你运气好，可能就是昨天晚上的一个备份，从这个备份恢复到临时库； 然后，从备份的时间点开始，将备份的 binlog 依次取出来，重放到中午误删表之前的那个时刻。 这样你的临时库就跟误删之前的线上库一样了，然后你可以把表数据从临时库取出来，按需要恢复到线上库去。为什么日志需要“两阶段提交”
由于 redo log 和 binlog 是两个独立的逻辑，如果不用两阶段提交，要么就是先写完 redolog 再写 binlog， 或者采用反过来的顺序。看看这两种方式会有什么问题 仍然用前面的 update 语句来做例子。 假设当前 ID=2 的行，字段 c 的值是 0，再假设执行update 语句过程中在写完第一个日志后， 第二个日志还没有写完期间发生了 crash，会出现什么情况呢？ 1.先写 redo log 后写 binlog。 假设在 redo log 写完，binlog 还没有写完的时候，MySQL 进程异常重启。 由于前面说过的，redo log 写完之后，系统即使崩溃，仍然能够把数据恢复回来，所以恢复后这一行 c 的值是 1。 但是由于 binlog 没写完就 crash 了，这时候 binlog 里面就没有记录这个语句。 因此，之后备份日志的时候，存起来的 binlog 里面就没有这条语句。 然后会发现，如果需要用这个 binlog 来恢复临时库的话，由于这个语句的 binlog 丢失， 这个临时库就会少了这一次更新，恢复出来的这一行 c 的值就是 0，与原库的值不同。 2.先写 binlog 后写 redo log。 如果在 binlog 写完之后 crash，由于 redo log 还没写， 崩溃恢复以后这个事务无效，所以这一行 c 的值是 0。但是 binlog 里面已经记录了“把c 从 0 改成 1”这个日志。 所以，在之后用 binlog 来恢复的时候就多了一个事务出来，恢复出来的这一行 c 的值就是 1，与原库的值不同 可以看到，如果不使用“两阶段提交”，那么数据库的状态就有可能和用它的日志恢复出来的库的状态不一致。 这个概率是不是很低，平时也没有什么动不动就需要恢复临时库的场景呀？ 其实不是的，不只是误操作后需要用这个过程来恢复数据。当需要扩容的时候，也就是需要再多搭建一些备库来增加系统的读能力的时候， 现在常见的做法也是用全量备份加上应用binlog 来实现的，这个“不一致”就会导致你的线上出现主从数据库不一致的情况。 简单说，redo log 和 binlog 都可以用于表示事务的提交状态，而两阶段提交就是让这两个状态保持逻辑上的一致。 -
tip
redo log 用于保证 crash-safe 能力。 innodb_flush_log_at_trx_commit 这个参数设置成1 的时候，表示每次事务的 redo log 都直接持久化到磁盘。 这个参数建议设置成 1，这样可以保证 MySQL 异常重启之后数据不丢失。 sync_binlog 这个参数设置成 1 的时候，表示每次事务的 binlog 都持久化到磁盘。 这个参数也建议你设置成 1，这样可以保证 MySQL 异常重启之后 binlog 不丢失。 在什么场景下，一天一备会比一周一备更有优势呢？或者说，它影响了这个数据库系统的哪个指标？ 一天一备跟一周一备的对比。 好处是“最长恢复时间”更短。 在一天一备的模式里，最坏情况下需要应用一天的 binlog。 比如，每天 0 点做一次全量备份，而要恢复出一个到昨天晚上 23 点的备份。 一周一备最坏情况就要应用一周的 binlog 了。 系统的对应指标就是恢复目标时间 频繁全量备份需要消耗更多存储空间，所以这个 RTO 是成本换来的，需要根据业务重要性来评估
3. 深入浅出mysql索引
- 使用hash索引存储
如果要维护一个身份证信息和姓名的表，需要根据身份证号查找对应的名字，这时 对应的哈希索引的示意图如下所示
图中，User2 和 User3 根据身份证号算出来的值都是 n，后面还跟了一个链表。
如果这时候要查 card-2 对应的名字是什么，处理步骤就是：
首先，将 card-2 通过哈希函数算出n,然后，按顺序遍历，找到 User2。
需要注意的是，图中四个 card-n 的值并不是递增的，这样做的好处是增加新的 User 时速度会很快，只需要往后追加。
但缺点是，因为不是有序的，所以哈希索引做 区间查询 的速度是很慢的。
如果现在要找身份证号在 [card_X, card_Y] 这个区间的所有用户，就必须全部扫描一遍了。
所以，哈希表这种结构适用于只有等值查询的场景，比如 Memcached 及其他一些 NoSQL 引擎，这一点上一边索引类型介绍中已经说得很清楚了
- 有序数组
有序数组 在等值查询和范围查询场景中的性能就都非常优秀,以下是其索示意图
假设身份证号没有重复，这个数组就是按照身份证号递增的顺序保存的。
这时候如果要查 card_n2 对应的名字，用二分法就可以快速得到，这个时间复杂度是 O(log(N))。
同时很显然，这个索引结构支持范围查询。你要查身份证号在 [card_X, card_Y] 区间的user，
可以先用二分法找到 card_X（如果不存在card_X，就找到大于card_X 的第一个user），然后向右遍历，直到查到第一个大于card_Y 的身份证号，退出循环。
如果仅仅看查询效率，有序数组就是最好的数据结构了。
但是，在需要更新数据的时候却不好，你往中间插入一个记录就必须得挪动后面所有的记录，成本太高。
所以，有序数组索引只适用于静态存储引擎，比如你要保存的是2020年某个城市的所有人口信息，这类不会再修改的数据
- 二叉搜索树示意图
二叉搜索树的特点是：
每个节点的左儿子小于父节点，父节点又小于右儿子。这样如果你要查card_n2 的话，按照图中的搜索顺序就是按照 UserA -> UserC -> UserF -> User2 这个路径得到。这个时间复杂度是 O(log(N))。
当然为了维持 O(log(N)) 的查询复杂度，你就需要保持这棵树是平衡二叉树。为了做这个 保证，更新的时间复杂度也是 O(log(N))。
树可以有二叉，也可以有多叉。多叉树就是每个节点有多个儿子，儿子之间的大小保证从左 到右递增。
二叉树是搜索效率最高的，但是实际上大多数的数据库存储却并不使用二叉树。 其原因是，索引不止存在内存中，还要写到磁盘上。
你可以想象一下一棵 100 万节点的平衡二叉树，树高20。一次查询可能需要访问 20 个数据块。
在机械硬盘时代，从磁盘随机读一个数据块需要 10 ms 左右的寻址时间。也就是说，对于一个100万行的表，如果使用二叉树来存储，单独访问一个行可能需要 20 个10 ms 的时间
为了让一个查询尽量少地读磁盘，就必须让查询过程访问尽量少的数据块。
那么，我们就不应该使用二叉树，而是要使用“N 叉”树。这里，“N 叉”树中的“N”取决于数据块的大小。
以 InnoDB 的一个整数字段索引为例，这个N差不多是 1200。这棵树高是 4 的时候，就可以存 1200 的 3 次方个值，这已经 17 亿了。
考虑到树根的数据块总是在内存中的，一个 10 亿行的表上一个整数字段的索引，查找一个值最多只需要访问3次磁盘。
其实，树的第二层也有很大概率在内存中，那么访问磁盘的平均次数就更少了。
N叉树由于在读写上的性能优点，以及适配磁盘的访问模式，已经被广泛应用在数据库引擎中了。
在 MySQL 中，索引是在存储引擎层实现的，所以并没有统一的索引标准，即不同存储引 擎的索引的工作方式并不一样。而即使多个存储引擎支持同一种类型的索引，其底层的实现 也可能不同。由于 InnoDB 存储引擎在 MySQL 数据库中使用最为广泛，下面以 InnoDB为例子
- InnoDB 的索引模型
在 InnoDB 中，表都是根据主键顺序以索引的形式存放的，这种存储方式的表称为索引组织表。
InnoDB 使用了 B+ 树索引模型，所以数据都是存储在 B+ 树中的，每一个索引在 InnoDB 里面对应一棵 B+ 树。
假设，有一个主键列为 ID 的表，表中有字段 k，并且在 k 上有索引
CREATE TABLE T ( id INT PRIMARY KEY, k INT NOT NULL, NAME VARCHAR ( 16 ), INDEX ( k ) ) ENGINE = INNODB;
表中 R1~R5 的 (ID,k) 值分别为 (100,1)、(200,2)、(300,3)、(500,5) 和 (600,6)，两棵树 的示例示意图如下
从图中不难看出，根据叶子节点的内容，索引类型分为 主键索引 和 非主键索引 。
主键索引的叶子节点存的是整行数据。在 InnoDB 里，主键索引也被称为聚簇索引 （clustered index）。
非主键索引的叶子节点内容是主键的值。在 InnoDB 里，非主键索引也被称为二级索引 （secondary index）。
根据上面的索引结构说明，来讨论一个问题：基于主键索引和普通索引的查询有什么区别？
如果语句是 select * from T where ID=500，即主键查询方式，则只需要搜索 ID 这棵 B+ 树；
如果语句是 select * from T where k=5，即普通索引查询方式，则需要先搜索 k 索引 树，得到 ID 的值为 500，再到 ID 索引树搜索一次。
这个过程称为回表。
也就是说，基于非主键索引的查询需要多扫描一棵索引树。因此，在应用中应该尽量使用主键查询。.
-
索引维护
B+树为了维护索引有序性，在插入新值的时候需要做必要的维护。
以上面这个图为例，
如果插入新的行ID值为 700，则只需要在 R5 的记录后面插入一个新记录。
如果新插入的ID值为400，就相对麻烦了，需要逻辑上挪动后面的数据，空出位置。
而更糟的情况是，如果 R5 所在的数据页已经满了，根据 B+ 树的算法，这时候需要申请一个新的数据页，然后挪动部分数据过去。
这个过程称为页分裂。在这种情况下，性能自然会受影响。
除了性能外，页分裂操作还影响数据页的利用率。原本放在一个页的数据，现在分到两个页中，整体空间利用率降低大约50%。 -
基于上面的索引维护过程说明，讨论一个案例：
在一些建表规范里面见到过类似的描述，要求建表语句里一定要有自增主键。上一个文章中也提到了这点，这里再次描述。
分析一下哪些场景下应该使用自增主键，而哪些场景下不应该。自增主键是指自增列上定义的主键，在建表语句中一般是这么定义的： NOT NULL PRIMARY KEY AUTO_INCREMENT。
插入新记录的时候可以不指定 ID 的值，系统会获取当前 ID 最大值加 1 作为下一条记录的 ID 值。也就是说，自增主键的插入数据模式，正符合了我们前面提到的递增插入的场景。
每次插入一条新记录，都是追加操作，都不涉及到挪动其他记录，也不会触发叶子节点的分裂。
而有业务逻辑的字段做主键，则往往不容易保证有序插入，这样写数据成本相对较高。
除了考虑性能外，还可以从存储空间的角度来看。
假表中确实有一个唯一字段， 比如字符串类型的身份证号，那应该用身份证号做主键，还是用自增字段做主键呢？
由于每个非主键索引的叶子节点上都是主键的值（因为要根据非主键索引找到主键索引位置然后再找到数据，可看上图）。
如果用身份证号做主键，那么每个二级索引的叶子节点占用约 20 个字节，
而如果用整型做主键，则只要 4 个字节，如果是长整型 （bigint）则是 8 个字节。
显然，主键长度越小，普通索引的叶子节点就越小，普通索引占用的空间也就越小。
所以，从性能和存储空间方面考量，自增主键往往是更合理的选择。
什么场景适合用业务字段直接做主键的呢？有些业务的场景需求是如下：
1. 只有一个索引
2. 该索引必须是唯一索引 这就是典型的 KV 场景。
由于没有其他索引，所以也就不用考虑其他索引的叶子节点大小的问题。
这时候就要优先考虑上一段提到的“尽量使用主键查询”原则，直接将这个索引设置为主键，可以避免每次查询需要搜索两棵树。
对于上面例子中的 InnoDB 表 T，如果要重建索引k，可以写：
alter table T drop index k;
alter table T add index(k);
要重建主键索引，可以写
alter table T drop primary key;
alter table T add primary key(id);
这样写是否合理？
重建索引 k的做法是合理的，可以达到省空间的目的。
但是，重建主键的过程不合理。
不论是删除主键还是创建主键，都会将整个表重建。
所以连着执行这两个语句的话，第一个语句就白做了。
这两个语句，可以用这个语句代替 ：alter table T engine=InnoDB
-
sql扫描行数的探讨
CREATE TABLE T ( ID INT PRIMARY KEY, k INT NOT NULL DEFAULT 0, s VARCHAR ( 16 ) NOT NULL DEFAULT '', INDEX k( k ) ) ENGINE = INNODB; INSERT INTO T VALUES ( 100, 1, 'aa' ), ( 200, 2, 'bb' ), ( 300, 3, 'cc' ), ( 500, 5, 'ee' ), ( 600, 6, 'ff' ), ( 700, 7, 'gg' );
这个表 T 中，如果我执行以下sql 需要执行几次树的搜索操作，会扫描多少行？
select * from T where k between 3 and 5;
先来看看这条 SQL 查询语句的执行流程：
1. 在 k 索引树上找到 k=3 的记录，取得 ID = 300
2. 再到 ID 索引树查到 ID=300 对应的 R3
3. 在 k 索引树取下一个值 k=5，取得 ID=500
4. 再回到 ID 索引树查到 ID=500 对应的 R4
5. 在 k 索引树取下一个值 k=6，不满足条件，循环结束
在这个过程中，回到主键索引树搜索的过程，称为回表。
可以看到，这个查询过程读了k索引树的3条记录（步骤 1、3 和 5），回表了两次（步骤 2 和 4）
在这个例子中，由于查询结果所需要的数据只在主键索引上有，所以不得不回表。
那么，有没有可能经过索引优化，避免回表过程呢？
答案是覆盖索引
如果执行的语句是 select ID from T where k between 3 and 5，这时只需要查 ID 的值，
而 ID 的值已经在 k 索引树上了，因此可以直接提供查询结果，不需要回表。
也就是说，在这个查询里面，索引k已经“覆盖了”查询需求，称为覆盖索引。
由于覆盖索引可以减少树的搜索次数，显著提升查询性能，所以使用覆盖索引是一个常用的性能优化手段。
需要注意的是，在引擎内部使用覆盖索引在索引 k上其实读了三个记录，R3~R5（对应的索引 k 上的记录项）
但是对于 MySQL 的 Server 层来说，它就是找引擎拿到了两条记录，因此 MySQL 认为扫描行数是 2。(这个行数扫描后面需要注意)
基于上面覆盖索引的说明，讨论另一个问题：
在一个市民信息表上，是否有必要将身份证号和名字建立联合索引？
假设这个市民表的定义是这样的：
CREATE TABLE `tuser` (
`id` INT ( 11 ) NOT NULL,
`id_card` VARCHAR ( 32 ) DEFAULT NULL,
`name` VARCHAR ( 32 ) DEFAULT NULL,
`age` INT ( 11 ) DEFAULT NULL,
`ismale` TINYINT ( 1 ) DEFAULT NULL,
PRIMARY KEY ( `id` ),
KEY `id_card` ( `id_card` ),
KEY `name_age` ( `name`, `age` )
) ENGINE = INNODB;
身份证号是市民的唯一标识。
也就是说，如果有根据身份证号查询市民信息的需求，只要在身份证号字段上建立索引就够了。
而再建立一个（身份证号、姓名）的联合 索引，是不是浪费空间？
如果现在有一个高频请求，要根据市民的身份证号查询他的姓名，这个联合索引就有意义了。
它可以在这个高频请求上用到覆盖索引，不再需要回表查整行记录，减少语句的执行时间。
当然，索引字段的维护总是有代价的。因此，在建立冗余索引来支持覆盖索引时就需要权衡考虑了。
这些是业务 DBA，或者称为业务数据架构师的工作。
-
最左前缀原则 (后面会详细介绍)
看到这里你一定有一个疑问，如果为每一种查询都设计一个索引，索引是不是太多了。
如果我现在要按照市民的身份证号去查他的家庭地址呢？
虽然这个查询需求在业务中出现的概率不高，但总不能让它走全表扫描吧？
反过来说，单独为一个不频繁的请求创建一个（身份证号，地址）的索引又感觉有点浪费。应该怎么做呢？这里便可以利用b+tree索引的“最左前缀原则”
为了直观地说明这个概念，这里用（name，age）这个联合索引来分析。
可以看到，索引项是按照索引定义里面出现的字段顺序排序的。 当你的逻辑需求是查到所有名字是“张三”的人时，可以快速定位到ID4，然后向后遍历得到所有需要的结果 如果你要查的是所有名字第一个字是“张”的人，你的SQL语句的条件是"where namelike ‘张 %’"。 这时，你也能够用上这个索引，查找到第一个符合条件的记录是 ID3，然后向后遍历，直到不满足条件为止。 可以看到，不只是索引的全部定义，只要满足最左前缀，就可以利用索引来加速检索。 这个最左前缀可以是联合索引的最左N个字段，也可以是字符串索引的最左M个字符基于上面对最左前缀索引的说明，那么在建立联合索引的时候，如何安排索引内的字段顺序？
这里大多的评估标准是，索引的复用能力。 因为可以支持最左前缀，所以当已经有了 (a,b)这个联合索引后，一般就不需要单独在 a上建立索引了。 因此，第一原则是，如果通过调整顺序，可以少维护一个索引，那么这个顺序往往就是需要优先考虑采用 所以现在可以知道了，这段开头的问题里，要为高频请求创建 (身份证号，姓名）这个联合索引，并用这个索引支持“根据身份证号查询地址”的需求 那么，如果既有联合查询，又有基于 a、b 各自的查询呢？查询条件里面只有 b 的语句，是无法使用 (a,b) 这个联合索引的， 这时候你不得不维护另外一个索引，也就是说需要同时维护 (a,b)、(b) 这两个索引。 这时候，要考虑的原则就是空间了。 比如上面这个市民表的情况，name 字段是比age 字段大的 ，那建议创建一个（name,age) 的联合索引和一个 (age) 的单字段索引。 -
索引下推
上一段说到满足最左前缀原则的时候，最左前缀可以用于在索引中定位记录。那么那些不符合最左前缀的部分，会怎么样呢？
还是以市民表的联合索引（name, age）为例。如果现在有一个需求： 检索出表中“名字第一个字是张，而且年龄是 10岁的所有男孩”。那么，SQL 语句是这么写的 select * from tuser where name like '张 %' and age=10 and ismale=1; 已经知道了前缀索引规则，所以这个语句在搜索索引树的时候，只能用 “张”，找到第一个满足条件的记录 ID3。 这还不错，总比全表扫描要好。然后判断其他条件是否满足。 在 MySQL 5.6 之前，只能从 ID3 开始一个个回表。到主键索引上找出数据行，再对比字段值 而 MySQL 5.6 引入的索引下推优化（index condition pushdown)，可以在索引遍历过程中， 对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数 看下图分析
-
tip
实际上主键索引也是可以使用多个字段的。
假如DBA小吕在入职新公司的时候，就发现自己接手维护的库里面，有这么一个表，表结构定义类似这样的CREATE TABLE `geek` ( `a` INT ( 11 ) NOT NULL, `b` INT ( 11 ) NOT NULL, `c` INT ( 11 ) NOT NULL, `d` INT ( 11 ) NOT NULL, PRIMARY KEY ( `a`, `b` ), KEY `c` ( `c` ), KEY `ca` ( `c`, `a` ), KEY `cb` ( `c`, `b` ) ) ENGINE = INNODB; 公司的同事告诉他说，由于历史原因，这个表需要 a、b 做联合主键，这个小吕理解了 可是根据上面提到的内容，主键包含了 a、b 这两个字段，那意味着单独在字段 c 上创建一个索引， 就已经包含了三个字段了，为什么要创建“ca”“cb”这两个索引？ 同事告诉他，是因为他们的业务里面有这样的两种语句： select * from geek where c=N order by a limit 1; select * from geek where c=N order by b limit 1; 为了这两个查询模式，这两个索引是否都是必须的？为什么呢？ 假如表记录 –a--|–b--|–c--|–d-- 1 2 3 d 1 3 2 d 1 4 3 d 2 1 3 d 2 2 2 d 2 3 4 d 主键 a，b的聚簇索引组织顺序相当于 order by a,b ，也就是先按 a 排序，再按 b 排序，c 无序。 索引 ca 的组织是先按 c排序，再按 a 排序，同时记录主键 –c--|–a--|–主键部分b-- 2 1 3 2 2 2 3 1 2 3 1 4 3 2 1 4 2 3 这个跟索引 c 的数据是一模一样的。 索引 cb 的组织是先按 c 排序，再按 b 排序，同时记录主键 –c--|–b--|–主键部分a-- 2 2 2 2 3 1 3 1 2 3 2 1 3 4 1 4 3 2 ca索引可以去掉，cb索引可以保留。 ca索引，通过索引对数据进行筛选，回表的时候，a本身就是主键索引，所以可以保证有序； cb索引，b上并没有索引，ab索引也无法满足最左匹配原则，可以保留加快排序速度。 包含主键后应该是cab，根据最左匹配原则，cb是有必要的，ca没有必要 所以，结论是 ca 可以去掉，cb 需要保留。
4. Explain详解
-
Explain
使用EXPLAIN关键字可以模拟优化器执行SQL语句，分析你的查询语句或是结构的性能瓶颈在 select 语句之前增加 explain 关键字， MySQL 会在查询上设置一个标记，执行查询会返回执行计划的信息，而不是执行这条SQL 注意：如果 from 中包含子查询，仍会执行该子查询，将结果放入临时表中 drop table if exists actor; CREATE TABLE `actor` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(45) NOT NULL, `update_time` datetime(6) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; INSERT INTO `actor`(`id`, `name`, `update_time`) VALUES (1, 'a', '2020-12-29 22:23:44.000000'); INSERT INTO `actor`(`id`, `name`, `update_time`) VALUES (2, 'b', '2020-12-29 22:23:44.000000'); INSERT INTO `actor`(`id`, `name`, `update_time`) VALUES (3, 'c', '2020-12-29 22:23:44.000000'); drop table if exists film; CREATE TABLE film ( id int(11) NOT NULL AUTO_INCREMENT, name varchar(10) NOT NULL, PRIMARY KEY (id), key (name) ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; INSERT INTO film(id,name) values(1,'film1'); INSERT INTO film(id,name) values(2,'film2'); INSERT INTO film(id,name) values(3,'film0'); drop table if exists film_actor; CREATE TABLE film_actor ( id int(11) not null, film_id int(11) not null, actor_id int(11) not null, remark VARCHAR(255) null, PRIMARY key(id), KEY idx_film_actor_id(film_id,actor_id) )ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; INSERT INTO film_actor(id,film_id,actor_id) values(1,1,1); INSERT INTO film_actor(id,film_id,actor_id) values(2,1,2); INSERT INTO film_actor(id,film_id,actor_id) values(3,2,1); explain select * from actor;
在查询中的每个表会输出一行，如果有两个表通过 join 连接查询，那么会输出两行,输出数字越高，执行越先
-
explain 两个变种
explain extended 会在 explain 的基础上额外提供一些查询优化的信息。 紧随其后通过 show warnings 命令可以得到优化后的查询语句，从而看出优化器优化了什么。 额外还有filtered列，是一个半分比的值， rows*filtered/100 可以估算出将要和explain中前一个表进行连接的行数（前一个表指explain中的id值比当前表id值小的表） explain extended select * from film where id = 1；
show warnings;
explain partitions 相比explain多了个partitions字段，如果查询是基于分区表的话，会显示查询将访问的分区。 -
explain中的列介绍
-
id
id列的编号是 select 的序列号，有几个 select 就有几个id，并且id的顺序是按 select 出现的顺序增长的。
id列越大执行优先级越高，id相同则从上往下执行，id为NULL最后执行。 -
select_type
select_type 表示对应行是简单还是复杂的查询,其中还分为五种类型
1. simple：简单查询。查询不包含子查询和union explain select * from film where id = 2 2. primary：复杂查询中最外层的 select 3. subquery：包含在select中的子查询（不在from子句中） 4. derived：包含在from子句中的子查询。 MySQL会将结果存放在一个临时表中，也称为派生表（derived的英文含义） #在执行前 关闭mysql5.7新特性对衍生表的合并优化 之后关闭 set session optimizer_switch='derived_merge=off' explain select (select 1 from actor where id = 1) from (select * from film where id = 1)der; set session optimizer_switch='derived_merge=on' 5. union：在union中的第二个和随后的select explain select 1 union all select 1 ;
-
table
这一列表示explain的一行正在访问哪个表。 当from子句中有子查询时，table列是 <derivenN> 格式，表示当前查询依赖 id=N的查询，于是先执行id=N的查询。 当有union时，UNION RESULT的table列的值为<union1,2>，1和2表示参与union的select行id。 -
type
这一列表示关联类型或访问类型，即MySQL决定如何查找表中的行，查找数据行记录的大概范围。 依次从最优到最差分别为：system > const > eq_ref > ref > range > index > ALL 一般来说，得保证查询达到range级别，最好达到ref NULL: mysql能够在优化阶段分解查询语句，在执行阶段用不着再访问表或索引。 例如：在索引列中选取最小值，可以单独查找索引来完成，不需要在执行时访问表 explain select min(id) from film
const,system
mysql能对查询的某部分进行优化并将其转化成一个常量（可以看showwarnings 的结果）。
用于primary key或 unique key的所有列与常数比较时，所以表最多有一个匹配行，读取1次，速度比较快。
system是const的特例，表里只有一条元组匹配时为system
explain extended select * from (select * from film where id= 1) tmp;
使用show warnings;可以看到Message 已经是直接select常量了
eq_ref
primary key或unique key索引的所有部分被连接使用，最多只会返回一条符合件的记录。
这可能是在const之外最好的联接类型了，简单的select查询不会出现这种type。
explain select * from film_actor left join film on film_actor.film_id = film.id;
ref
相比eq_ref，不使用唯一索引，而是使用普通索引或者唯一性索引的部分前缀，索引要和某个值相比较，可能会找到多个符合条件的行。
1. 简单select查询，name是普通索引（非唯一索引）
explain select * from film where name = 'film1';
2.关联表查询，idx_film_actor_id是film_id和actor_id的联合索引，
这里使用到了film_actor的左边前缀film_id部分
explain select film_id from film left join film_actor on film.id = film_actor.film_id;
range
范围扫描通常出现在in(), between ,> ,<, >= 等操作中。使用一个索引来检索给定范围的行。
explain select * from actor where id > 1;
index
扫描全表索引，这通常比ALL快一些
ALL
即全表扫描，意味着mysql需要从头到尾去查找所需要的行。通常情况下这需要增加索引来进行优化了
-
possible_keys
这一列显示查询可能使用哪些索引来查找。 explain时可能出现possible_keys有列，而key显示NULL的情况， 这种情况是因为表中数据不多，mysql认为索引对此查询帮助不大，选择了全表查询。 如果该列是NULL，则没有相关的索引。 在这种情况下，可以通过检查where子句看是否可以创造一个适当的索引来提高查询性能，然后用explain查看效果 -
key
这一列显示mysql实际采用哪个索引来优化对该表的访问。如果没有使用索引，则该列是NULL。 如果想强制mysql使用或忽视possible_keys列中的索引，在查询中使用 force index、ignore index -
key
这一列显示了mysql在索引里使用的字节数，通过这个值可以算出具体使用了索引中的哪些列。 举例来说，film_actor的联合索引 idx_film_actor_id 由 film_id 和 actor_id 两个int列组成， 并且每个int是4字节。通过结果中的key_len=4可推断出查询使用了第一个列：film_id列来执行索引查找。 key_len计算规则如下： 字符串 char(n)：n字节长度 varchar(n)：2字节存储字符串长度，如果是utf-8，则长度: 3n+2 数值类型 tinyint：1字节 smallint：2字节 int：4字节 bigint：8字节 时间类型 date：3字节 timestamp：4字节 datetime：8字节 如果字段允许为 NULL，需要1字节记录是否为 NULL 索引最大长度是768字节，当字符串过长时，mysql会做一个类似左前缀索引的处理，将前半 部分的字符提取出来做索引 explain select * from film_actor where film_id = 2
-
ref
这一列显示了在key列记录的索引中，表查找值所用到的列或常量，常见的有：const（常量），字段名（例：film.id） -
rows
这一列是mysql估计要读取并检测的行数，注意这个不是结果集里的行数(这个是包括索引扫描、回表等加起来的) -
Extra列
1. Using index 使用覆盖索引 explain select film_id from film_actor where film_id = 1; 2. Using where 使用 where 语句来处理结果，查询的列未被索引覆盖 explain select * from actor where name = 'a'; 3. Using index condition 查询的列不完全被索引覆盖，where条件中是一个前导列的范围； explain select * from film_actor where film_id > 1;
4. Using temporary
mysql需要创建一张临时表来处理查询。出现这种情况一般是要进行优化的，首先是想到用索引来优化。
1. actor.name没有索引，此时创建了张临时表来distinct
explain select distinct name from actor;
2. film.name建立了idx_name索引，此时查询时extra是 using index,没有用临时表
explain select distinct name from film;
5. Using filesort
将用外部排序而不是索引排序，数据较小时从内存排序，否则需要在磁盘完成排序。
这种情况下一般也是要考虑使用索引来优化的。
1. actor.name未创建索引，会浏览actor整个表，保存排序关键字name和对应的id，然后排序name并检索行记录
explain select * from actor order by name;
2. film.name建立了idx_name索引,此时查询时 extra是 using index
explain select * from film order by name;
6. Select tables optimized away
使用某些聚合函数（比如 max、min）来访问存在索引的某个字段
explain select min(id) from film;
5. mysql索引最佳实践
-
表
CREATE TABLE employees ( id INT ( 11 ) NOT NULL AUTO_INCREMENT, NAME VARCHAR ( 24 ) NOT NULL DEFAULT '', age INT ( 11 ) NOT NULL DEFAULT 0, position VARCHAR ( 20 ) NOT NULL DEFAULT '', hire_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY ( id ), KEY idx_name_age_position ( NAME, age, position ) USING BTREE )ENGINE = INNODB AUTO_INCREMENT = 3 DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci; insert into employees(name,age,position) values('LiLei',22,'manager'); insert into employees(name,age,position) values('HanMeimei',23,'dev'); insert into employees(name,age,position) values('Lucy',23,'dev'); -
1.全值匹配
-- idx_name_age_position ( name, age, position ) 简历索引的顺序 -- 如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列 -- 根据创建索引的顺序 name, age, position 以下6条都会走索引,其中第三条顺序变了，但是mysql会优化调整顺序 -- 所以也是会走索引 但是前提是其中必须出现最左的索引(name) 不然的话是不会走索引的，比如第八条sql explain select * from employees where name = 'LiLei'; explain select * from employees where name = 'LiLei' and age = 22; explain select * from employees where age = 22 and name = 'LiLei'; explain select * from employees where name = 'LiLei' and age = 22 and position = 'manager';
-- 这条sql第一个しname，符合了原则，但是position是在第三个的，中间漏了age，所以不是全部走了索引，使用的是 Using index condition explain select * from employees where name = 'LiLei' and position = 'manager'; explain select * from employees where position = 'manager' and name = 'LiLei'; -
2.最左前缀
如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列 -- 以下2条sql没有遵守最左匹配原则 使用的是 Using where explain select * from employees where age = 22; explain select * from employees where age = 22 and position = 'manager';
-
3.索引列不要使用函数
不要在索引列上做任何操作（计算、函数、（自动or手动）类型转换），会导致索引失效而转向全表扫描 explain select * from employees where name = 'LiLei'; explain select * from employees where left(name,3) = 'LiLei'; 给hire_time增加一个普通索引，然后使用函数包装查询 alter table `employees` add index `idx_hire_time` (`hire_time`) using btree; explain select * from employees where date(hire_time) = '2018-09-30'; 优化为日期范围查询，走索引 explain select * from employees where hire_time >= '2018-09-30 00:00:00' and hire_time <= '2018-09-30 23:59:59'; 删除索引 alter table `employees` drop index `idx_hire_time`;
-
4.索引中范围条件右边的列无法使用索引
存储引擎不能使用索引中范围条件右边的列 explain select * from employees where name = 'LiLei' and age = 22 AND position = 'manager'; explain select * from employees where name = 'LiLei' and age > 22 AND position = 'manager';
-
5.使用覆盖索引
尽量使用覆盖索引（只访问索引的查询（索引列包含查询列）），减少select *语句 explain select name,age from employees where name = 'LiLei' AND age = 23 AND position = 'manager'; explain select * from employees where name = 'LiLei' AND age = 23 AND position = 'manager';
-6.使用不等于（！=或者<>）,is nul is not null 的时候无法使用索引
mysql在使用不等于（！=或者<>）的时候无法使用索引会导致全表扫描
is null,is not null 也无法使用索引
explain select * from employees where name != 'LiLei';
explain select * from employees where name is null;
-7.like以通配符开头（'$abc...'）mysql索引失效会变成全表扫描操作
explain select * from employees where name like '%Lei';
如何解决like'%字符串%'索引不被使用的方法？
1.使用覆盖索引，查询字段必须是建立覆盖索引字段
explain select name,age,position from employees where name like '%Lei%';
2.如果不能使用覆盖索引则可能需要借助搜索引擎
easysearch等
like KK% 相当于=常量，%KK和%KK% 相当于范围
-
8.字符串不加单引号索引失效
底层加了函数进行转换，使用了函数，无法使用索引 explain select * from employees where name = '1000'; explain select * from employees where name = 1000;
-9.少用or或in，用它查询时，mysql不一定使用索引，
mysql内部优化器会根据检索比例、表大小等多个因素整体评估是否使用索引，详见范围查询优化
explain select * from employees where name = 'LiLei' or name = 'HanMeimei';
-10.范围查询优化
给年龄添加单值索引
alter table `employees` add index `idx_age` (`age`) using btree;
explain select * from employees where age >=1 and age <=2000;
没走索引原因：(不一定)
mysql内部优化器会根据检索比例、表大小等多个因素整体评估是否使用索引。
比如这个例子，可能是由于单次数据量查询过大导致优化器最终选择不走索引
优化方法：可以讲大的范围拆分成多个小范围
-- 数据库中一共就三条数据22,23,23
-- 这条查询不会走索引 idx_age 因为所有数据都会返回
explain select * from employees where age >=22 and age <=1000;
-- 这条会走索引 idx_age
explain select * from employees where age >=23 and age <=1000;
-- 这条会走索引 idx_age
explain select * from employees where age >= 1001 and age <= 2000;
alter table `employees` drop index `idx_age`;
6. Mysql如何选择合适的索引
-
索引覆盖的实践优化
explain select * from employees where name > 'a'; 以上sql,如果用name索引需要遍历name字段联合索引树，然后还需要根据遍历出来的主键值去主键索引树里再去查出最终数据， 成本比全表扫描还高，可以用覆盖索引优化，这样只需要遍历name字段的联合索引树就能拿到所有结果，如下： explain select * from employees where name > 'a'; explain select name,age,position from employees where name > 'a'; explain select * from employees where name > 'zzz'; 对于上面这两种 name>'a'和name>'zzz' 的执行结果，mysql最终是否选择走索引或者一张表涉及多个索引， mysql最终如何选择索引，我们可以用trace工具来一查究竟，开启trace工具会影响mysql性能， 所以只能临时分析sql使用，用完之后立即关闭 set session optimizer_trace ="enabled=on",end_markers_in_json=on; ‐‐开启trace select * from employees where name > 'a' order by position; select * from information_schema.OPTIMIZER_TRACE; "steps": [ { /* ‐‐第一阶段：SQL准备阶段 */ "join_preparation": { "select#": 1, "steps": [ { "expanded_query": "/* select#1 */ select `employees`.`id` AS `id`,`employees`.`NAME` AS `NAME`,`employees`.`age` AS `age`,`employees`.`position` AS `position`,`employees`.`hire_time` AS `hire_time` from `employees` where (`employees`.`NAME` > 'a') order by `employees`.`position`" } ] /* steps */ } /* join_preparation */ }, { /* 第二阶段：SQL优化阶段 */ "join_optimization": { "select#": 1, "steps": [ { /* ‐‐条件处理 */ "condition_processing": { "condition": "WHERE", "original_condition": "(`employees`.`NAME` > 'a')", "steps": [ { "transformation": "equality_propagation", "resulting_condition": "(`employees`.`NAME` > 'a')" }, { "transformation": "constant_propagation", "resulting_condition": "(`employees`.`NAME` > 'a')" }, { "transformation": "trivial_condition_removal", "resulting_condition": "(`employees`.`NAME` > 'a')" } ] /* steps */ } /* condition_processing */ }, { "substitute_generated_columns": { } /* substitute_generated_columns */ }, { /* 表依赖详情 */ "table_dependencies": [ { "table": "`employees`", "row_may_be_null": false, "map_bit": 0, "depends_on_map_bits": [ ] /* depends_on_map_bits */ } ] /* table_dependencies */ }, { "ref_optimizer_key_uses": [ ] /* ref_optimizer_key_uses */ }, { /* 预估表的访问成本 */ "rows_estimation": [ { "table": "`employees`", "range_analysis": { /* 全表扫描情况 */ "table_scan": { "rows": 3, ‐‐扫描行数 "cost": 3.7 ‐‐查询成本 } /* table_scan */, "potential_range_indexes": [ ‐‐查询可能使用的索引 { "index": "PRIMARY", ‐‐主键索引 "usable": false, "cause": "not_applicable" }, { "index": "idx_name_age_position", ‐‐辅助索引 "usable": true, "key_parts": [ "NAME", "age", "position", "id" ] /* key_parts */ }, { "index": "idx_age", "usable": false, "cause": "not_applicable" } ] /* potential_range_indexes */, "setup_range_conditions": [ ] /* setup_range_conditions */, "group_index_range": { "chosen": false, "cause": "not_group_by_or_distinct" } /* group_index_range */, "analyzing_range_alternatives": { ‐‐分析各个索引使用成本 "range_scan_alternatives": [ { "index": "idx_name_age_position", "ranges": [ ‐‐索引使用范围 "a < NAME" ] /* ranges */, "index_dives_for_eq_ranges": true, "rowid_ordered": false, ‐‐使用该索引获取的记录是否按照主键排序 "using_mrr": false, "index_only": false, ‐‐是否使用覆盖索引 "rows": 3, ‐‐索引扫描行数 "cost": 4.61, ‐‐索引使用成本 "chosen": false, ‐‐是否选择该索引 "cause": "cost" } ] /* range_scan_alternatives */, "analyzing_roworder_intersect": { "usable": false, "cause": "too_few_roworder_scans" } /* analyzing_roworder_intersect */ } /* analyzing_range_alternatives */ } /* range_analysis */ } ] /* rows_estimation */ }, { "considered_execution_plans": [ { "plan_prefix": [ ] /* plan_prefix */, "table": "`employees`", "best_access_path": { ‐‐最优访问路径 "considered_access_paths": [ ‐‐最终选择的访问路径 { "rows_to_scan": 3, "access_type": "scan", ‐‐访问类型：为scan，全表扫描 "resulting_rows": 3, "cost": 1.6, "chosen": true, ‐‐确定选择 "use_tmp_table": true } ] /* considered_access_paths */ } /* best_access_path */, "condition_filtering_pct": 100, "rows_for_plan": 3, "cost_for_plan": 1.6, "sort_cost": 3, "new_cost_for_plan": 4.6, "chosen": true } ] /* considered_execution_plans */ }, { "attaching_conditions_to_tables": { "original_condition": "(`employees`.`NAME` > 'a')", "attached_conditions_computation": [ ] /* attached_conditions_computation */, "attached_conditions_summary": [ { "table": "`employees`", "attached": "(`employees`.`NAME` > 'a')" } ] /* attached_conditions_summary */ } /* attaching_conditions_to_tables */ }, { "clause_processing": { "clause": "ORDER BY", "original_clause": "`employees`.`position`", "items": [ { "item": "`employees`.`position`" } ] /* items */, "resulting_clause_is_simple": true, "resulting_clause": "`employees`.`position`" } /* clause_processing */ }, { "reconsidering_access_paths_for_index_ordering": { "clause": "ORDER BY", "steps": [ ] /* steps */, "index_order_summary": { "table": "`employees`", "index_provides_order": false, "order_direction": "undefined", "index": "unknown", "plan_changed": false } /* index_order_summary */ } /* reconsidering_access_paths_for_index_ordering */ }, { "refine_plan": [ { "table": "`employees`" } ] /* refine_plan */ } ] /* steps */ } /* join_optimization */ }, { "join_execution": { ‐‐第三阶段：SQL执行阶段 "select#": 1, "steps": [ { "filesort_information": [ { "direction": "asc", "table": "`employees`", "field": "position" } ] /* filesort_information */, "filesort_priority_queue_optimization": { "usable": false, "cause": "not applicable (no LIMIT)" } /* filesort_priority_queue_optimization */, "filesort_execution": [ ] /* filesort_execution */, "filesort_summary": { "rows": 3, "examined_rows": 3, "number_of_tmp_files": 0, "sort_buffer_size": 262080, "sort_mode": "<sort_key, packed_additional_fields>" } /* filesort_summary */ } ] /* steps */ } /* join_execution */ } ] /* steps */ } 结论：全表扫描的成本低于索引扫描，所以mysql最终选择全表扫描 select * from employees where name > 'zzz' order by position; select * from information_schema.OPTIMIZER_TRACE; 查看trace字段可知索引扫描的成本低于全表扫描，所以mysql最终选择索引扫描 set session optimizer_trace ="enabled=on",end_markers_in_json=off; ‐‐关闭trace
