前言
本文针对 MySQL 数据库的 InnoDB 存储引擎，介绍其中索引的实现以及索引在慢 SQL 优化中的作用。
本文次要探讨不同场景下索引失效与生效的起因。
慢SQL与索引的关系
慢SQL优化准则
数据库也是利用，MySQL 作为一种磁盘数据库，属于典型的 IO 密集型利用，并且随机 IO 比程序 IO 更低廉。
实在的慢 SQL 往往会随同着大量的行扫描、临时文件排序，间接影响就是磁盘 IO 升高、CPU 使用率升高，失常 SQL 也变为了慢 SQL，对于利用来说就是大面积执行超时。
线上很多事变都与慢 SQL 无关，因而慢 SQL 治理已成为 DBA 与业务研发的共识。
[]()
慢SQL的优化准则为：缩小数据访问量与缩小计算操作
缩小访问量：
•创立适合的索引
•缩小不必要拜访的列
•应用笼罩索引
•语句改写
•数据结转
缩小计算操作：
•排序列退出索引
•适当的列冗余
•SQL 拆分
•计算性能拆分
能够将慢 SQL 优化的办法分为三类：
•查问优化
•索引优化
•库表构造优化
其中索引是数据库中用来晋升性能的最常用工具。
可是，为什么索引能够放慢查问，索引肯定能够放慢查问吗？
索引的作用
要答复这个问题，能够比照没有索引与有索引时查问操作的性能差别。
在此之前，首先介绍下查问操作的解决流程。
查问操作能够分为以下两步：
•定位到记录所在的页
•从所在的页中定位到具体的记录
其中从页中定位记录的办法依赖每个页面中创立的Page Directory（页目录），因而关键在于如何定位页。
数据保留在磁盘上，数据处理产生在内存中，数据页是磁盘与内存之间交互的根本单位，也是 MySQL 治理存储空间的根本单位，大小默认为 16KB。
因而通常一次起码从磁盘中读取 16KB 的内容到内存中，一次起码把内存中的 16KB 内容刷新到磁盘中。
要了解索引的作用，须要首先明确没有索引时如何定位页。
没有索引时，因为每个页中的数据没有法则，因而无奈疾速定位记录所在的页，只能从第一个页沿双向链表向后遍历，也就是说须要遍历所有数据页顺次判断是否满足查问条件。
简略来说，没有索引时每次查问都是全表扫描。
因而索引须要解决的次要问题就是实现每个数据页中数据有法则，具体是保障下一个数据页中用户记录的索引列值必须大于上一个页中用户记录的索引列值。
索引是存储引擎用于疾速查找的一种排序的数据结构。
有索引时，优化器首先基于老本主动抉择最优的执行打算，而后基于索引的有序性能够通过扫描更少的数据页定位到满足条件的数据。
具体起因与索引的数据结构无关，上面基于索引的数据结构介绍常见的索引失效与索引生效的场景。
索引
索引的数据结构
索引是一种以空间换工夫思维的具体实现，用于减速查问。
MySQL 中由存储引擎层实现索引，InnoDB 存储引擎中基于 B+ 树实现，因而每个索引都是一棵 B+ 树。
索引用于组织页，页用于组织行记录。在介绍索引的构造之前首先介绍页的构造，如下图所示。
[]()
其中：
•每个数据页中的记录会依照主键值从小到大的程序组成一个单向链表，依赖行记录的Page Header中next_record属性实现，其中保留下一条记录绝对于本条记录的地址偏移量；
•数据页之间组成一个双向链表，依赖数据页的File Header中FIL_PAGE_PREV和FIL_PAGE_NEXT属性实现，其中保留本页的上一个和下一个页的页号。
多个页通过树进行组织，其中保留用户数据与目录项。目录项中保留页的用户记录中主键的最小值与页号，从而保障下一个数据页中用户记录的主键值大于上一个页中用户记录的主键值。
[]()
其中：
•用户数据保留在叶子节点，目录项保留在非叶子节点，每个节点中可能保留多个页；
•最下面的节点称为根节点，根节点的地址保留在内存的数据字典中；
•B+ 树的深度个别管制在 3 层以内，因而定位到单条记录不超过 3 次 IO。
因而，页面和记录是排好序的，就能够通过二分法来疾速定位查找。
有索引时，查问操作变成了什么样呢？
•从 B+ 树的根节点登程，一层一层向下搜寻目录项，因为下层节点保留的都是上层节点的最小值，因而能够疾速定位到数据可能所在的页；
•如果数据页在缓存池中，间接从内存中获取，否则从磁盘加载到内存中；
•数据页外部二分查找定位满足条件的记录行。
[]()
索引保留的数据
索引中保留的数据与索引的类型无关。
索引能够分为两种类型：
•聚簇索引，主键索引。叶子节点中保留主键值+对应的残缺行记录，目录项中保留主键最小值+页号。InnoDB 属于索引组织表，每张表都有聚簇索引，因而表必须有主键，表中行的物理程序与索引的逻辑程序雷同；
•非聚簇索引，二级索引，在非主键的其余列上建的索引。叶子节点中保留索引列的值+对应的主键值，目录项中保留索引列最小值+对应的主键值+页号。
[]()
介绍三个与索引性能相干的概念：
| 概念 | explain.extra |
|---|---|
| 笼罩索引 | Using index |
| 回表 | Using where |
| 索引下推 | Using index condition |
•笼罩索引，当二级索引中蕴含要查问的所有字段时，这个索引称为笼罩索引；
•回表，当二级索引中不蕴含要查问的所有字段时，就须要先通过二级索引查出主键索引，再通过主键索引查问二级索引中没有的其余列的数据，这个过程叫做回表；
•索引下推，用于优化应用二级索引从表中检索行的实现。条件过滤能够下推到存储引擎层进行，先由索引元组（index tuple）依据查问条件进行过滤，满足条件的前提下才回表，否则跳过，相当于提早加载数据行，因而 ICP 能够升高回表次数与 IO 次数。
失常状况下看不到二级索引中暗藏的主键，但实际上，如下所示查看锁信息，显示LOCK_DATA: '17118168721', 2，其中 2 就是二级索引中保留的主键值。
ENGINE:INNODB ENGINE_LOCK_ID:140123070938328:14:7:4:140122972537552ENGINE_TRANSACTION_ID:2032566 THREAD_ID:157 EVENT_ID:44 OBJECT_SCHEMA: test_zk OBJECT_NAME: t_lock_test PARTITION_NAME:NULL SUBPARTITION_NAME:NULL INDEX_NAME: idx_uk_mobileOBJECT_INSTANCE_BEGIN:140122972537552 LOCK_TYPE: RECORD LOCK_MODE: X,REC_NOT_GAP LOCK_STATUS: GRANTED LOCK_DATA:'17118168721',2如下所示，工作中屡次遇到研发创立二级索引时显式指定主键，实际上是不须要的，二级索引开端主动保留主键。
alter table payable_unsettled add index idx_seller_no_recno_id(seller_no, receipt_no,id) using BTREE;因而二级索引+主键与联结索引的相同点是顺次排序，不同点是索引中保留的数据不同。
索引失效的场景
等值查问
线上环境屡次遇到表没有创立二级索引，只有主键索引。
SQL
select sys_no,area_no,area_name,dc_no,dc_name,wh_no,wh_name, business_type,business_no,item_code,item_grade, item_result,item_remark,status, yn,ts,create_time,create_pin,update_time,update_pinfrom evaluate_resultwhere yn =1 and wh_no ='611-887-2' and business_no ='QNSYKF23020900000018' and create_pin ='13940137489'orderby update_time desc;# 执行用时5 rows in set(7.311125 sec)执行打算，显示全表扫描
[]()
表构造，显示查问字段无索引
mysql>show create table evaluate_result \G***************************1.row*************************** Table: evaluate_resultCreateTable:CREATE TABLE `evaluate_result`( `sys_no` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '外部主键', `wh_no` varchar(32) NOT NULL DEFAULT '' COMMENT'仓库编码', `business_no` varchar(20) NOT NULL DEFAULT '' COMMENT'调研业务主键', ... PRIMARY KEY(`sys_no`))ENGINE=InnoDB AUTO_INCREMENT=2007412 DEFAULT CHARSET=utf8 COMMENT='评估后果表'1rowinset(0.00 sec)优化办法：创立索引
alter table evaluate_result add index idx_wh_bus_no(wh_no,business_no);执行打算
***************************1.row*************************** id:1 select_type: SIMPLE table: evaluate_result partitions:NULL type: refpossible_keys: idx_wh_bus_no key: idx_wh_bus_no key_len:160 ref: const,const rows:5 filtered:1.00 Extra:Using index condition;Using where;Using filesort1rowinset,1 warning (0.00 sec)# 执行用时5 rows in set(0.01 sec)其中：
•key_len: 160，表明联结索引的两个字段都用到了，(32+20) 3+2 2 = 160。
等值查问索引失效的起因是雷同值的数据组成单向链表，因而定位到满足条件的 5 行数据须要扫描的行数从 1377442 行升高到 5 行。
范畴查问
SQL
select idfrom board_chutewhere status=1 and create_time <= date_sub(now(),interval 24 hour);执行打算，显示全表扫描
***************************1.row*************************** id:1 select_type: SIMPLE table: board_chute partitions:NULL type:ALLpossible_keys: idx_create_time key:NULL key_len:NULL ref:NULL rows:407632 filtered:5.00 Extra:Using where1rowinset,1 warning (0.00 sec)查问字段有索引，然而索引生效
KEY`idx_create_time`(`create_time`),status 字段的区分度
mysql> select status,count(*) from board_chute group by status;+--------+----------+| status | count(*) |+--------+----------+| 0 | 407317 || 1 | 4309 |+--------+----------+2 rows in set (0.17 sec)因而范畴查问索引生效的起因是查看数据量大并且须要回表。
优化办法：创立联结索引实现笼罩索引
alter table board_chute add index idx_status_create_time(status, create_time);执行打算，显示 Using index 表明用到了笼罩索引，不须要回表。
*************************** 1. row *************************** id: 1 select_type: SIMPLE table: board_chute partitions: NULL type: rangepossible_keys: idx_create_time,idx_status_create_time key: idx_status_create_time key_len: 8 ref: NULL rows: 203816 filtered: 10.00 Extra: Using where; Using index1 row in set, 1 warning (0.00 sec)范畴查问索引失效的起因是叶子节点中除了保留索引，还保留指向下个节点的指针，因而遍历叶子节点就能够取得范畴值。
因而倡议应用 between and 代替 in，如select from T where k in (1,2,3,4,5);对应 5 次树的搜寻，而select from T where k between 1 and 5;对应 1 次树的搜寻。
假如索引基于哈希表实现，能够通过散列函数将 key 值转换成一个固定的地址，如果产生哈希碰撞就在这个地位拉出一个链表。因而哈希表的长处是插入操作的速度快，依据 key 间接往后追加即可。但因为散列函数的离散个性，通过散列函数解决后的 key 将失去原有的程序，所以哈希表无奈满足范畴查问，只适宜等值查问。
留神上述索引失效的场景并非相对成立，须要回表的记录越多，优化器越偏向于应用全表扫描，反之偏向于应用二级索引 + 回表的形式。
回表查问老本高有两点起因：
•须要应用到两个 B+ 树索引，一个二级索引，一个聚簇索引；
•拜访二级索引应用程序 I/O，拜访聚簇索引应用随机 I/O。
因而有两条倡议：
•倡议为区分度高的字段创立索引，并且将区分度高的字段优先放在联结索引后面；
•倡议优先应用笼罩索引，必须要回表时也须要管制回表的记录数，从而升高索引生效的危险。
索引生效的场景
违反最左匹配准则
SQL
select count(*)from sort_cross_detailwhere yn =1 and org_id =3 and site_type =16 and site_code ='121671';执行打算，显示全表扫描
[]()
只管以后有联结索引 idx_site_type(SUB_TYPE, THIRD_TYPE, SITE_TYPE )，但因为查问条件中不包含 SUB_TYPE 字段，因而违反最左匹配准则，导致索引生效。
以后查问条件的多个字段区分度由高到低为 site_code、org_id、site_type。
[]()
优化办法：site_code 字段区分度很高，创立单列索引。
alter table sort_cross_detail add index `idx_site_code` (`SITE_CODE`);执行打算
*************************** 1. row *************************** id: 1 select_type: SIMPLE table: sort_cross_detail partitions: NULL type: refpossible_keys: idx_site_code key: idx_site_code key_len: 99 ref: const rows: 1336 filtered: 0.10 Extra: Using where1 row in set, 1 warning (0.00 sec)其中：
•应用联结索引过程中能够通过执行打算中的 key_len 字段评估具体 SQL 应用到了联结索引中的几个字段；
•联结索引中页面和记录首先依照联结索引后面的列排序，如果该列值雷同，再依照联结索引后边的列排序。
违反最左匹配准则导致索引生效的起因是只有当索引后面的列雷同时，前面的列才有序。
上面联合 innodb_ruby 工具解析 InnoDB 数据文件查看记录保留的程序验证联结索引中索引后面的列不同时，前面的列可能无序。
筹备测试数据。
mysql> show create table t_index \G*************************** 1. row *************************** Table: t_indexCreate Table: CREATE TABLE `t_index` ( `id` int(11) NOT NULL AUTO_INCREMENT, `age` int(11) DEFAULT '0', `name` varchar(10) DEFAULT '', PRIMARY KEY (`id`), KEY `idx_age_name` (`age`,`name`)) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf81 row in set (0.00 sec)mysql> insert into t_index(age, name) values(8, "Tom"),(8, "David"), (10, "Andy");Query OK, 3 rows affected (0.00 sec)Records: 3 Duplicates: 0 Warnings: 0mysql> select * from t_index;+----+------+-------+| id | age | name |+----+------+-------+| 2 | 8 | David || 1 | 8 | Tom || 3 | 10 | Andy |+----+------+-------+3 rows in set (0.00 sec)mysql> explain select * from t_index \G*************************** 1. row *************************** id: 1 select_type: SIMPLE table: t_index partitions: NULL type: indexpossible_keys: NULL key: idx_age_name key_len: 38 ref: NULL rows: 3 filtered: 100.00 Extra: Using index1 row in set, 1 warning (0.00 sec)其中：
•insert 时，三条记录依照 name 字段逆序；
•select 时，三条记录依照联结索引排序，并不是依照主键排序。
别离查看索引以及索引中保留的数据
[root@exps-test3 data]# innodb_space -s ibdata1 -T test_zk/t_index space-indexesid name root fseg fseg_id used allocated fill_factor 218 PRIMARY 3 internal 1 1 1 100.00% 218 PRIMARY 3 leaf 2 0 0 0.00% 219 idx_age_name 4 internal 3 1 1 100.00% 219 idx_age_name 4 leaf 4 0 0 0.00% [root@exps-test3 data]# [root@exps-test3 data]# innodb_space -s ibdata1 -T test_zk/t_index -p 3 page-recordsRecord 127: (id=1) → (age=8, name="Tom")Record 158: (id=2) → (age=8, name="David")Record 191: (id=3) → (age=10, name="Andy")[root@exps-test3 data]# [root@exps-test3 data]# innodb_space -s ibdata1 -T test_zk/t_index -p 4 page-recordsRecord 145: (age=8, name="David") → (id=2)Record 127: (age=8, name="Tom") → (id=1)Record 165: (age=10, name="Andy") → (id=3)其中：
•主键与二级索引的根节点页号别离是 3 与 4；
•查看聚簇索引中保留的记录，依照主键排序；
•查看二级索引中保留的记录，联结索引中当 age 雷同时，name 有序，age 不同时，name 无序。
下面提到，数据字典中保留表的根节点的地址，具体是 INFORMATION_SCHEMA.INNODB_SYS_INDEXES 零碎表的 space 与 page_no 字段，别离保留表空间 ID 与根节点页号。
mysql> SELECT tables.name, indexs.space, indexs.name, indexs.page_noFROM INFORMATION_SCHEMA.INNODB_SYS_TABLES as tables inner join INFORMATION_SCHEMA.INNODB_SYS_INDEXES as indexs on tables.table_id = indexs.table_idWHERE tables.NAME = 'test_zk/t_index';+-----------------+-------+--------------+---------+| name | space | name | page_no |+-----------------+-------+--------------+---------+| test_zk/t_index | 106 | PRIMARY | 3 || test_zk/t_index | 106 | idx_age_name | 4 |+-----------------+-------+--------------+---------+2 rows in set (0.00 sec)其中 test_zk/t_index 表有两个索引，对应的根节点页号别离等于 3 与 4，与下面数据文件解析的后果统一。
order by limit
SQL，不倡议应用 select *
select * from waybill_order_added_value_report_detail goodsInfo WHERE goodsInfo.is_delete = 0 AND goodsInfo.org_no = '418' AND goodsInfo.distribute_no = '636' AND ( goodsInfo.company_code = 'EBU4418046542406' OR goodsInfo.company_name = 'EBU4418046542406' ) AND goodsInfo.network_type = 1 AND goodsInfo.should_operator_time >= concat('2022-05-01', ' 00:00:00') AND goodsInfo.should_operator_time <= concat('2022-05-31', ' 23:59:59') AND goodsInfo.uniform_status = 0 ORDER BY goodsInfo.id DESC LIMIT 0, 20 \G # 执行用时2 rows in set (1 min 9.71 sec)执行打算，主键全索引扫描，联结索引生效
*************************** 1. row *************************** id: 1 select_type: SIMPLE table: goodsInfo type: indexpossible_keys: idx_org_dc_operator_time,idx_operator_time_network key: PRIMARY key_len: 8 ref: NULL rows: 16156 Extra: Using where1 row in set (0.00 sec)表构造与查问条件
# 查问条件WHERE goodsInfo.org_no = '418' AND goodsInfo.distribute_no = '636' AND goodsInfo.should_operator_time >= concat('2022-05-01', ' 00:00:00') AND goodsInfo.should_operator_time <= concat('2022-05-31', ' 23:59:59') # 索引 KEY `idx_org_dc_operator_time` (`org_no`,`distribute_no`,`should_operator_time`), KEY `idx_operator_time_network` (`should_operator_time`,`network_type`)将 limit 20 批改为 limit 30，SQL 如下所示。
select * from waybill_order_added_value_report_detail goodsInfo WHERE ...ORDER BY goodsInfo.id DESC LIMIT 0, 30 \G# 执行用时2 rows in set (0.06 sec)执行打算显示当改为 limit 30 时，联结索引失效。
*************************** 1. row *************************** id: 1 select_type: SIMPLE table: goodsInfo type: rangepossible_keys: idx_org_dc_operator_time,idx_operator_time_network key: idx_org_dc_operator_time key_len: 132 ref: NULL rows: 19024 Extra: Using index condition; Using where; Using filesort1 row in set (0.00 sec)可见，索引的抉择与 limit n 的 n 值也有关系。
从景象上看，当 limit n 的 n 值变大时，SQL的执行反倒有可能变快了。
实际上，这是 MySQL 低版本中的 bug #97001，优化器认为排序是个低廉的操作，因而在执行 order by id limit 这条 SQL 时，为了防止排序，并且认为当 limit n 的 n 很小时，全表扫描能够很快执行完，因而抉择应用全表扫描，以防止额定的排序。
针对 MySQL 中 order by limit 或 group by limit 优化器抉择谬误索引的场景，常见的优化办法有四种：
•强制索引，通过 hint 固化执行打算，比方能够通过 force index 指定应用的索引，然而当条件发生变化时有可能生效，因而生产环境中不倡议应用；
•prefer_ordering_index，5.7.33 中已修复该 bug，因而倡议新申请时应用 5.7.33 及以上版本，存量低版本倡议降级，倡议优先应用该办法；
•联结索引，倡议在适合的字段加联结索引， 加强可选索引的区分度，让优化器认为这种形式优于有序索引；
•order by (id+0)，通过 trick 的形式坑骗优化器，因为 id 上进行了加法这种耗时操作，使优化器认为此时基于全表扫描的会更耗性能，因而抉择基于老本抉择的索引。
优化办法：order by (id+0)
select ...ORDER BY goodsInfo.id+0 DESCLIMIT 0, 20\G执行打算
*************************** 1. row *************************** id: 1 select_type: SIMPLE table: goodsInfo type: rangepossible_keys: idx_org_dc_operator_time,idx_operator_time_network key: idx_org_dc_operator_time key_len: 132 ref: NULL rows: 19024 Extra: Using index condition; Using where; Using filesort1 row in set (0.00 sec)order by limit 导致索引生效的起因是当查问字段与排序字段不同时，如果应用查问字段的索引，排序字段将无序。优化器认为排序操作低廉，因而优先应用排序字段的索引。
隐式转换
字段类型不统一或字符集不统一时主动隐式转换将导致索引生效。
SQL
SELECT idFROM base_operating_reportWHERE yn = 1 and ec_code = 42order by inbound_time desc;执行打算，显示索引生效全表扫描
*************************** 1. row *************************** id: 1 select_type: SIMPLE table: base_operating_report partitions: NULL type: ALLpossible_keys: idx_ecCode_transferCode key: NULL key_len: NULL ref: NULL rows: 36524 filtered: 1.00 Extra: Using where; Using filesort1 row in set, 3 warnings (0.00 sec)查看正告信息，显示隐式转换导致索引生效
mysql> show warnings \G*************************** 1. row *************************** Level: Warning Code: 1739Message: Cannot use ref access on index 'idx_ecCode_transferCode' due to type or collation conversion on field 'ec_code'*************************** 2. row *************************** Level: Warning Code: 1739Message: Cannot use range access on index 'idx_ecCode_transferCode' due to type or collation conversion on field 'ec_code'*************************** 3. row *************************** Level: Note Code: 1003Message: /* select#1 */ select `dms_uat`.`base_operating_report`.`id` AS `id` from `dms_uat`.`base_operating_report` where ((`dms_uat`.`base_operating_report`.`yn` = 1) and (`dms_uat`.`base_operating_report`.`ec_code` = 42)) order by `dms_uat`.`base_operating_report`.`inbound_time` desc3 rows in set (0.00 sec)表构造
# 索引信息KEY `idx_ecCode_transferCode` (`ec_code`, `transfer_code`)# 字段类型`ec_code` varchar(64) DEFAULT NULL COMMENT '仓库编码'优化办法：将参数中的数值类型转换成字符串
SELECT idFROM base_operating_reportWHERE yn = 1 and ec_code = '42'order by inbound_time desc;执行打算，显示索引失效。
*************************** 1. row *************************** id: 1 select_type: SIMPLE table: base_operating_report partitions: NULL type: refpossible_keys: idx_ecCode_transferCode key: idx_ecCode_transferCode key_len: 195 ref: const rows: 443 filtered: 10.00 Extra: Using index condition; Using where; Using filesort1 row in set, 1 warning (0.00 sec)隐式转换导致索引生效的起因是字段上有函数，而函数并不一定是枯燥函数，因而会毁坏索引自身的有序性。
IN
SQL
select IFNULL(count(DISTINCT (awi.id)), 0)from tc_attorney_waybill_info awiwhere awi.is_delete = 0 and awi.cur_transit_center_code in ('2008' , '2052' , '2053' , '2054' , '2055' , '2056' , '2057' , '2058' , '2059' , '2061' , '2064' , '2069' , '2079' , '2084' , '2085' , '2094' , '2171' , '2201' , '2202' , '2207' , '2216' , '2258' , '2292' , '2301' , '2311' , '2324' , '2332' , '2334' , '2336' , '2349' , '2354' , '2355' , '2359' , '2367' , '2369' , '2373' , '2381' , '2385' ) and awi.send_time >= '2022-10-20 00:00:00' and awi.send_time <= '2022-11-18 23:59:59' and awi.split_send_package_times > 0 and awi.first_split_type = 1;# 执行用时1 rows in set (1.309 sec)执行打算，显示索引生效全表扫描
[]()
trace，显示以后的索引中全表扫描的老本最低，因而索引生效。
"analyzing_range_alternatives": { "range_scan_alternatives": [ { "index": "idx_transit_code_waybill_code", "ranges": [ "2008 <= cur_transit_center_code <= 2008", "2052 <= cur_transit_center_code <= 2052", "2053 <= cur_transit_center_code <= 2053", "2054 <= cur_transit_center_code <= 2054", "2055 <= cur_transit_center_code <= 2055", "2056 <= cur_transit_center_code <= 2056", "2057 <= cur_transit_center_code <= 2057", "2058 <= cur_transit_center_code <= 2058", "2059 <= cur_transit_center_code <= 2059", "2061 <= cur_transit_center_code <= 2061", "2064 <= cur_transit_center_code <= 2064", "2069 <= cur_transit_center_code <= 2069", "2079 <= cur_transit_center_code <= 2079", "2084 <= cur_transit_center_code <= 2084", "2085 <= cur_transit_center_code <= 2085", "2094 <= cur_transit_center_code <= 2094", "2171 <= cur_transit_center_code <= 2171", "2201 <= cur_transit_center_code <= 2201", "2202 <= cur_transit_center_code <= 2202", "2207 <= cur_transit_center_code <= 2207", "2216 <= cur_transit_center_code <= 2216", "2258 <= cur_transit_center_code <= 2258", "2292 <= cur_transit_center_code <= 2292", "2301 <= cur_transit_center_code <= 2301", "2311 <= cur_transit_center_code <= 2311", "2324 <= cur_transit_center_code <= 2324", "2332 <= cur_transit_center_code <= 2332", "2334 <= cur_transit_center_code <= 2334", "2336 <= cur_transit_center_code <= 2336", "2349 <= cur_transit_center_code <= 2349", "2354 <= cur_transit_center_code <= 2354", "2355 <= cur_transit_center_code <= 2355", "2359 <= cur_transit_center_code <= 2359", "2367 <= cur_transit_center_code <= 2367", "2369 <= cur_transit_center_code <= 2369", "2373 <= cur_transit_center_code <= 2373", "2381 <= cur_transit_center_code <= 2381", "2385 <= cur_transit_center_code <= 2385" ], "index_dives_for_eq_ranges": true, "rowid_ordered": false, "using_mrr": false, "index_only": false, "rows": 1328061, "cost": 1.59e6, "chosen": false, "cause": "cost" }, { "index": "idx_outstoretime", "ranges": [ "0x99ae280000 <= send_time <= 0x99ae657efb" ], "index_dives_for_eq_ranges": true, "rowid_ordered": false, "using_mrr": false, "index_only": false, "rows": 1597295, "cost": 1.92e6, "chosen": false, "cause": "cost" } ], "analyzing_roworder_intersect": { "usable": false, "cause": "too_few_roworder_scans" } } } } ] }, { "considered_execution_plans": [ { "plan_prefix": [ ], "table": "`tc_attorney_waybill_info` `awi`", "best_access_path": { "considered_access_paths": [ { "rows_to_scan": 3194590, "access_type": "scan", "resulting_rows": 3.19e6, "cost": 677027, "chosen": true } ] },优化办法：
•创立一个更多查问字段的联结索引，缩小回表次数；
•放大查问的工夫范畴，因为查问的数据量比拟大，而且用到的字段比拟多，导致回表老本高。
IN 导致索引生效的起因是符合条件的数据量过大导致回表老本高于全表扫描。
分组字段无索引
提数，创立惟一键之前分组查问是否有反复数据。
SQL
select operate_id, waybill_code, private_call_idfrom tos_resource.courier_call_out_record_0group by operate_id, waybill_code, private_call_idhaving count(*) > 1;执行打算，显示全表扫描
*************************** 1. row *************************** id: 1 select_type: SIMPLE table: courier_call_out_record_0 partitions: NULL type: ALLpossible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 1470107 filtered: 100.00 Extra: Using temporary; Using filesort1 row in set, 1 warning (0.01 sec)分组字段无联结索引，有两个索引笼罩三个分组字段，因而索引无奈应用。
KEY `courier_call_out_record_0_operate_id_IDX` (`operate_id`,`waybill_code`), KEY `courier_call_out_recourd_0_waybill_code` (`waybill_code`)优化办法：给分组字段创立联结索引
alter table courier_call_out_record_0 add index `courier_call_out_record_0_composite_key` (`operate_id`,`waybill_code`,`private_call_id`);执行打算
*************************** 1. row *************************** id: 1 select_type: SIMPLE table: courier_call_out_record_0 partitions: NULL type: indexpossible_keys: courier_call_out_record_0_composite_key key: courier_call_out_record_0_composite_key key_len: 907 ref: NULL rows: 2230391 filtered: 100.00 Extra: Using index1 row in set, 1 warning (0.00 sec)分组字段无联结索引导致全表扫描的起因是分组时须要先排序，因而只有当分组字段在同一个索引中时才能够保障有序。
索引的优缺点
索引的代价
通过以上剖析能够发现索引不是万能的，实际上有时候索引甚至会有副作用。
创立索引的代价能够分为两类：
•空间代价，索引须要占用磁盘空间，并且删除索引并不会立刻开释空间，因而无奈通过删除索引的形式升高磁盘使用率；
•工夫代价，有的时候会发现创立索引后导致写入变慢，起因是每次数据写入后还须要对该记录依照索引排序。因而常常更新的列不倡议创立索引。
可见，索引的长处是能够放慢查问速度，毛病是占用内存与磁盘空间，同时减慢了插入与更新操作的速度。
因而，B+ Tree 实用于读多写少的业务场景，绝对应的 LSM-Tree 实用于写多读少的业务场景，起因是每次数据写入对应一条日志追加写入磁盘文件，用程序 IO 代替了随机 IO。
索引应用的倡议
对于索引的应用有以下几点倡议：
•倡议给区分度高的字段创立索引；
•倡议删除冗余索引，否则优化器可能应用 index_merge 导致抉择到谬误的索引；
•不倡议应用强制索引，比方当数据量或统计信息发生变化时，强制索引不肯定最优。
上面测试下与索引相干的两个操作：
•如果 SQL 中强制指定已删除的索引，SQL 执行会报错吗？
•如果删除字段，索引也会主动删除吗？
筹备数据
mysql> create table t_index_drop like t_index;Query OK, 0 rows affected (0.02 sec)mysql> show create table t_index_drop \G*************************** 1. row *************************** Table: t_index_dropCreate Table: CREATE TABLE `t_index_drop` ( `id` int(11) NOT NULL AUTO_INCREMENT, `age` int(11) DEFAULT '0', `name` varchar(10) DEFAULT '', PRIMARY KEY (`id`), KEY `idx_age_name` (`age`,`name`)) ENGINE=InnoDB DEFAULT CHARSET=utf81 row in set (0.00 sec)如果 SQL 中强制指定已删除的索引，SQL 将间接报错，生产环境中遇到过相干案例。
mysql> alter table t_index_drop drop index idx_age_name;Query OK, 0 rows affected (0.01 sec)Records: 0 Duplicates: 0 Warnings: 0mysql> select * from t_index_drop force index(idx_age_name);ERROR 1176 (42000): Key 'idx_age_name' doesn't exist in table 't_index_drop'如果删除字段，索引也会主动删除。
mysql> alter table t_index_drop add index idx_age(age);Query OK, 0 rows affected (0.02 sec)Records: 0 Duplicates: 0 Warnings: 0mysql> show create table t_index_drop \G*************************** 1. row *************************** Table: t_index_dropCreate Table: CREATE TABLE `t_index_drop` ( `id` int(11) NOT NULL AUTO_INCREMENT, `age` int(11) DEFAULT '0', `name` varchar(10) DEFAULT '', PRIMARY KEY (`id`), KEY `idx_age_name` (`age`,`name`), KEY `idx_age` (`age`)) ENGINE=InnoDB DEFAULT CHARSET=utf81 row in set (0.00 sec)mysql> alter table t_index_drop drop column age;Query OK, 0 rows affected (0.02 sec)Records: 0 Duplicates: 0 Warnings: 0mysql> show create table t_index_drop \G*************************** 1. row *************************** Table: t_index_dropCreate Table: CREATE TABLE `t_index_drop` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(10) DEFAULT '', PRIMARY KEY (`id`), KEY `idx_age_name` (`name`)) ENGINE=InnoDB DEFAULT CHARSET=utf81 row in set (0.00 sec)基于优化器抉择索引有可能选错索引导致性能降落，而应用强制索引可能导致 SQL 执行间接报错。
论断
慢SQL的优化准则是缩小数据访问量与缩小计算操作，其中索引是数据库中用来晋升性能的最常用工具。
索引是一种用于疾速查找的一种排序的数据结构，基于以空间换工夫的思维实现。
MySQL 中由存储引擎层实现索引，InnoDB 存储引擎中基于 B+ 树实现，因而每个索引都是一棵 B+ 树。
索引用于组织页，页用于组织行记录。
其中：
•每个数据页中的记录会依照主键值从小到大的程序组成一个单向链表，依赖行记录的Page Header中next_record属性实现，其中保留下一条记录绝对于本条记录的地址偏移量；
•数据页之间组成一个双向链表，依赖数据页的File Header中FIL_PAGE_PREV和FIL_PAGE_NEXT属性实现，其中保留本页的上一个和下一个页的页号。
查问操作能够分为以下两步：
•定位到记录所在的页
•从所在的页中定位到具体的记录
比照没有索引与有索引时查问操作的性能差别：
•没有索引时每次查问都是全表扫描；
•有索引时从 B+ 树的根节点登程，一层一层向下搜寻目录项，因为下层节点保留的都是上层节点的最小值，因而能够疾速定位到数据可能所在的页。
对于索引生效的场景总结以下两点：
•索引的实质是有序的数据结构，因而毁坏索引有序性的操作都有可能导致索引生效或局部失效；
•回表老本较高，因而优先应用笼罩索引，必须要回表时也须要管制回表的记录数，从而升高索引生效的危险。
参考教程
•《MySQL 是怎么运行的：从根儿上了解 MySQL》
•MySQL工具之innodb_ruby：探索InnoDB存储构造的利器
•你管这破玩意叫B+树?
作者：京东物流 张凯
起源：京东云开发者社区