2021-05-13-MySQL 索引总结

MySQL 索引总结

生活中的索引

MySQL 官方对索引的定义为：索引Index是帮助 MySQL 高效获取数据的 数据结构。可以得到索引的本质：索引是数据结构。
上面的理解比较抽象，举一个例子，平时看任何一本书，首先看到的都是目 录，通过目录去查询书籍里面的内容会非常的迅速。

MySQL中的索引

InnoDB 存储引擎支持以下几种常见的索引：B+树索引、全文索引、哈希索引，其中比较关键的是 B+树索引，其他的索引有：聚集索引/聚簇索引、辅助索引/二级索引、联合索引/复合索引、覆盖索引/索引覆盖、自适应哈希索引、全文检索之倒排索引。

也有这么分类的：

普通索引：即一个索引只包含单个列，一个表可以有多个单列索引。

唯一索引：索引列的值必须唯一，但允许有空值。

复合索引：即一个索引包含多个列。

聚簇索引(聚集索引)：并不是一种单独的索引类型，而是一种数据存储方式。具体细节取决 于不同的实现，InnoDB 的聚簇索引其实就是在同一个结构中保存了 B-Tree 索引(技术上来说 是 B+Tree)和数据行。 

非聚簇索引：不是聚簇索引，就是非聚簇索引。

HashMap 适合做数据库索引吗？

1、hash 表只能匹配是否相等，不能实现范围查找；

2、当需要按照索引进行 order by 时，hash 值没办法支持排序；

3、组合索引可以支持部分索引查询，如(a,b,c)的组合索引，查询中只用到了

a和 b 也可以查询的，如果使用 hash 表，组合索引会将几个字段合并 hash，没

办法支持部分索引；

4、当数据量很大时，hash 冲突的概率也会非常大。

B+Tree

B+树索引就是传统意义上的索引，这是目前关系型数据库系统中查找最常用 和最为有效的索引。B+树索引的构造类似于二叉树，根据键值（Key Value）快速 找到数据。注意 B+树中的 B 不是代表二叉(binary)，而是代表平衡(balance)，因 为 B+树是从最早的平衡二叉树演化而来，但是 B+树不是一个二叉树。
在了解 B+树索引之前先要知道与之密切相关的一些算法与数据结构，这有 助于更好的理解 B+树索引的工作方式，因为 B+树是通过二叉查找树，再由 平衡二叉树，B 树演化而来。

二分查找

二分查找法（binary search） 也称为折半查找法，用来查找一组有序的记录数组中的某一记录。
其基本思想是：将记录按有序化（递增或递减）排列，在查找过程中采用跳跃式方式查找， 即先以有序数列的中点位置作为比较对象，如果要找的元素值小于该中点元素，则将待查序 列缩小为左半部分，否则为右半部分。通过一次比较，将查找区间缩小一半。

给出一个例子，注意该例子已经是升序排序的，且查找 数字 48 
数据：5， 10， 19， 21， 31， 37， 42， 48， 50， 52 
下标：0， 1， 2， 3， 4， 5， 6， 7， 8， 9 
• 步骤一：设 low 为下标最小值 0 ， high 为下标最大值 9 ;
• 步骤二：通过 low 和 high 得到 mid ，mid=（low + high） / 2，初始时 mid 为下标 4 (也 可以=5，看具体算法实现)；
• 步骤三 ： mid=4 对应的数据值是 31，31 < 48（要找的数字）；
• 步骤四：通过二分查找的思路，将 low 设置为 31 对应的下标 4 ， high 保持不变为 9 ， 此时 mid 为 6 ；
• 步骤五 ： mid=6 对应的数据值是 42，42 < 48（要找的数字）；
• 步骤六：通过二分查找的思路，将 low 设置为 42 对应的下标 6 ， high 保持不变为 9 ， 此时 mid 为 7 ；
• 步骤七 ： mid=7 对应的数据值是 48，48 == 48（要找的数字），查找结束； 通过 3 次 二分查找 就找到了所要的数字，而顺序查找需 8

二叉树

每个节点至多只有二棵子树；

• 二叉树的子树有左右之分，次序不能颠倒；
• 一棵深度为 k，且有 个节点，称为满二叉树(Full Tree)；
• 一棵深度为 k，且 root 到 k-1 层的节点树都达到最大，第 k 层的所有节点都 连续集中 在 最左边,此时为完全二叉树（Complete Tree）

平衡二叉树（AVL-树）

• 左子树和右子树都是平衡二叉树；
• 左子树和右子树的高度差绝对值不超过 1；

平衡二叉树：

非平衡二叉树：

# 平衡二叉树的遍历 
# 前序 ：6 ,3, 2, 5,7, 8（ROOT 节点在开头, 中 -左-右 顺序） 
# 中序 ：2, 3, 5, 6,7, 8（中序遍历即为升序，左- 中 -右 顺序） 
# 后序 ：2, 5, 3, 8,7, 6 （ROOT 节点在结尾，左-右- 中 顺序）

平衡二叉树的旋转比较复杂，提供个博客专门详解：
平衡二叉树【旋转的超详细图解】

B+树

​ B+树是 B 树的一种变形形式，B+树上的叶子结点存储关键字以及相应记录的地址，叶子结点以上各层作为索引使用。一棵 m 阶的 B+树定义如下:

• 每个节点最多可以有 m 个元素；

• 除了根节点外，每个节点最少有 (m/2) 个元素；

• 如果根节点不是叶节点，那么它最少有 2 个孩子节点；

• 所有的叶子节点都在同一层；

• 一个有 k 个孩子节点的非叶子节点有 (k-1) 个元素，按升序排列；

• 某个元素的左子树中的元素都比它小，右子树的元素都大于或等于它；

• 非叶子节点只存放关键字和指向下一个孩子节点的索引，记录只存放在叶子节点中；

• 相邻的叶子节点之间用指针相连。

B+树的变体为 B*树，在 B+树的非根和非叶子结点再增加指向兄弟的指针；
B*树定义了非叶子结点关键字个数至少为(2/3)*M，即块的最低使用率为 2/3（代替 B+树的 1/2）。
概要的了解下 B 树和 B+树。

B+树是为磁盘或其他直接存取辅助设备设计的一种平衡查找树。在 B+树中， 所有记录节点都是按键值的大小顺序存放在同一层的叶子节点上，由各叶子节点 指针进行连接。比如：

InnoDB中的索引

聚集索引/聚簇索引

​ InnoDB 中使用了聚集索引，就是将表的主键用来构造一棵 B+树，并且将整 张表的行记录数据存放在该 B+树的叶子节点中。也就是所谓的索引即数据，数 据即索引。由于聚集索引是利用表的主键构建的，所以每张表只能拥有一个聚集 索引。

​ 聚集索引的叶子节点就是数据页。换句话说，数据页上存放的是完整的每行 记录。因此聚集索引的一个优点就是：通过过聚集索引能获取完整的整行数据。 另一个优点是：对于主键的排序查找和范围查找速度非常快。

​ 如果没有定义主键呢？MySQL 会使用唯一性索引，没有唯一性索引， MySQL 也会创建一个隐含列 RowID 来做主键，然后用这个主键来建立聚集索引。

辅助索引/二级索引

​ 上边介绍的聚簇索引只能在搜索条件是主键值时才能发挥作用，因为 B+树 中的数据都是按照主键进行排序的,那如果想以别的列作为搜索条件怎么 办？一般会建立多个索引，这些索引被称为辅助索引/二级索引。

​ 对于辅助索引(Secondary Index，也称二级索引、非聚集索引)，叶子节点 并不包含行记录的全部数据。叶子节点除了包含键值以外，每个叶子节点中的索 引行中还包含了一个书签( bookmark)。该书签用来告诉 InnoDB 存储引擎哪里可 以找到与索引相对应的行数据。因此 InnoDB 存储引擎的辅助索引的书签就是相 应行数据的聚集索引键。

比如辅助索引 index(node)，那么叶子节点中包含的数据就包括了(主键、 note)。

回表

​ 辅助索引的存在并不影响数据在聚集索引中的组织，因此每张表上可以有多个辅助索引。当通过辅助索引来寻找数据时，InnoDB 存储引擎会遍历辅助索引 并通过叶级别的指针获得指向主键索引的主键，然后再通过主键索引（聚集索引） 来找到一个完整的行记录。这个过程也被称为回表。也就是根据辅助索引的值查询一条完整的用户记录需要使用到 2 棵 B+树，即一次辅助索引，一次聚集索引。

​ 为什么还需要一次回表操作呢?直接把完整的用户记录放到辅助索引 d 的叶子节点不就好了么？如果把完整的用户记录放到叶子节点是可以不用回表， 但是太占地方了，相当于每建立一棵 B+树都需要把所有的用户记录再都拷贝一 遍，这就有点太浪费存储空间了。而且每次对数据的变化要在所有包含数据的索 引中全部都修改一次，性能也非常低下。
​ 很明显，回表的记录越少，性能提升就越高，需要回表的记录越多，使用二 级索引的性能就越低，甚至让某些查询宁愿使用全表扫描也不使用二级索引。
​ 那什么时候采用全表扫描的方式，什么时候使用采用二级索引 + 回表的方 式去执行查询呢？这个就是查询优化器做的工作，查询优化器会事先对表中的记 录计算一些统计数据，然后再利用这些统计数据根据查询的条件来计算一下需要 回表的记录数，需要回表的记录数越多，就越倾向于使用全表扫描，反之倾向于 使用二级索引 + 回表的方式。

联合索引/复合索引

​ 前面对索引的描述，隐含了一个条件，那就是构建索引的字段只有一个， 但实践工作中构建索引的完全可以是多个字段。所以，将表上的多个列组合起来 进行索引称之为联合索引或者复合索引，比如 index(a,b)就是将 a,b 两个 列组合起来构成一个索引。
​ 千万要注意一点，建立联合索引只会建立 1 棵 B+树，多个列分别建立索引 会分别以每个列则建立 B+树，有几个列就有几个 B+树，比如，index(note)、 index(b)，就分别对 note,b 两个列各构建了一个索引。
​ index(note,b)在索引构建上，包含了两个意思：
​ 1、先把各个记录按照 note 列进行排序。
​ 2、在记录的 note 列相同的情况下，采用 b 列进行排序。

覆盖索引/索引覆盖

​ 既然多个列可以组合起来构建为联合索引，那么辅助索引自然也可以由多个 列组成。
​ InnoDB 存储引擎支持覆盖索引(covering index，或称索引覆盖)，即从辅 助索引中就可以得到查询的记录，而不需要查询聚集索引中的记录。使用覆盖索 引的一个好处是辅助索引不包含整行记录的所有信息，故其大小要远小于聚集索 引，因此可以减少大量的 IO操作。所以记住，覆盖索引并不是索引类型的一种。

自适应哈希索引

​ InnoDB 存储引擎除了前面所说的各种索引，还有一种自适应哈希索引， 知道 B+树的查找次数,取决于 B+树的高度,在生产环境中,B+树的高度一般为 3~4层,故需要 3~4 次的 IO 查询。
​ 所以在 InnoDB 存储引擎内部自己去监控索引表，如果监控到某个索引经常 用，那么就认为是热数据，然后内部自己创建一个 hash 索引，称之为自适应哈 希索引( Adaptive Hash Index,AHI)，创建以后，如果下次又查询到这个索引， 那么直接通过 hash 算法推导出记录的地址，直接一次就能查到数据，比重复去 B+ Tree 索引中查询三四次节点的效率高了不少。
​ InnoDB 存储引擎使用的哈希函数采用除法散列方式，其冲突机制采用链表 方式。注意，对于自适应哈希索引仅是数据库自身创建并使用的，并不能对 其进行干预。通过命令 show engine innodb status 可以看到当前自适应哈希 索引的使用状况，测试查询结果如下:

=====================================
2021-05-12 03:11:44 0x7f54701ff700 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 28 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 114812 srv_active, 0 srv_shutdown, 4528160 srv_idle
srv_master_thread log flush and writes: 0
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 109540
OS WAIT ARRAY INFO: signal count 109523
RW-shared spins 12, rounds 12, OS waits 0
RW-excl spins 3561, rounds 105825, OS waits 3254
RW-sx spins 6, rounds 129, OS waits 3
Spin rounds per wait: 1.00 RW-shared, 29.72 RW-excl, 21.50 RW-sx
------------
TRANSACTIONS
------------
Trx id counter 2357106
Purge done for trx's n:o < 2357104 undo n:o < 0 state: running but idle
History list length 7
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 421475798915688, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421475798914816, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
--------
FILE I/O
--------
I/O thread 0 state: waiting for completed aio requests (insert buffer thread)
I/O thread 1 state: waiting for completed aio requests (log thread)
I/O thread 2 state: waiting for completed aio requests (read thread)
I/O thread 3 state: waiting for completed aio requests (read thread)
I/O thread 4 state: waiting for completed aio requests (read thread)
I/O thread 5 state: waiting for completed aio requests (read thread)
I/O thread 6 state: waiting for completed aio requests (write thread)
I/O thread 7 state: waiting for completed aio requests (write thread)
I/O thread 8 state: waiting for completed aio requests (write thread)
I/O thread 9 state: waiting for completed aio requests (write thread)
Pending normal aio reads: [0, 0, 0, 0] , aio writes: [0, 0, 0, 0] ,
 ibuf aio reads:, log i/o's:, sync i/o's:
Pending flushes (fsync) log: 0; buffer pool: 5
1630 OS file reads, 4387964 OS file writes, 3908576 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 0, seg size 2, 0 merges
merged operations:
 insert 0, delete mark 0, delete 0
discarded operations:
 insert 0, delete mark 0, delete 0
Hash table size 34679, node heap has 2 buffer(s)
Hash table size 34679, node heap has 3 buffer(s)
Hash table size 34679, node heap has 2 buffer(s)
Hash table size 34679, node heap has 3 buffer(s)
Hash table size 34679, node heap has 3 buffer(s)
Hash table size 34679, node heap has 3 buffer(s)
Hash table size 34679, node heap has 3 buffer(s)
Hash table size 34679, node heap has 12 buffer(s)
0.00 hash searches/s, 0.00 non-hash searches/s
---
LOG
---
Log sequence number          856480558
Log buffer assigned up to    856480558
Log buffer completed up to   856480558
Log written up to            856480558
Log flushed up to            856480558
Added dirty pages up to      856480558
Pages flushed up to          856480558
Last checkpoint at           856480558
1270972 log i/o's done, 0.00 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 137363456
Dictionary memory allocated 1032309
Buffer pool size   8192
Free buffers       5564
Database pages     2597
Old database pages 938
Modified db pages  0
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 2, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 1533, created 1064, written 1424848
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 2597, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
0 read views open inside InnoDB
Process ID=1, Main thread ID=140000311064320 , state=sleeping
Number of rows inserted 43526, updated 116136, deleted 549, read 30264535
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================

​ 哈希索引只能用来搜索等值的查询,如 SELECT* FROM table WHERE index co=xxx。而对于其他查找类型,如范围查找,是不能使用哈希索引的, 因此这里出现了 non- hash searches/s 的情况。通过 hash searches: non- hash searches 可以大概了解使用哈希索引后的效率。
​ 由于 AHI 是由 InnoDB 存储引擎控制的,因此这里的信息只供参考。不 过可以通过观察 SHOW ENGINE INNODB STATUS 的结果及参数innodb_adaptive_hash_index 来考虑是禁用或启动此特性,默认 AHI 为开启状态。
​ 什么时候需要禁用呢？如果发现监视索引查找和维护哈希索引结构的额外 开销远远超过了自适应哈希索引带来的性能提升就需要关闭这个功能。
​ 同时在 MySQL 5.7 中，自适应哈希索引搜索系统被分区。每个索引都绑定到 一个特定的分区，每个分区都由一个单独的 latch 锁保护。分区由 innodb_adaptive_hash_index_parts 配置选项控制 。在早期版本中，自适应哈 希索引搜索系统受到单个 latch锁的保护，这可能成为繁重工作负载下的争用 点。innodb_adaptive_hash_index_parts 默认情况下，该 选项设置为 8。最大 设置为 512。当然禁用或启动此特性和调整分区个数这个应该是 DBA 的工作，作为开发了解即可。

全文检索之倒排索引

​ 什么是全文检索（Full-Text Search）？
​ 它是将存储于数据库中的整本书或整 篇文章中的任意内容信息查找出来的技术。它可以根据需要获得全文中有关章、 节、段、句、词等信息，也可以进行各种统计和分析。圈子中比较熟知的 Elasticsearch、 Solr 等就是全文检索引擎，底层都是基于 Apache Lucene 的。

id	朝代(dynasty)	作者(author)	诗词年代(poetry_age)	标题(title)	诗词全文(contents)
1	唐	李白		静夜思	床前明月光，疑是地上霜。 举头望明月，低头思故乡。
2	宋	李清照		如梦令	常记溪亭日暮，沉醉不知归路，兴尽晚回舟，误入藕花深处。争渡，争渡，惊起一滩鸥鹭。
···	···	···	···	···	···

​ 要根据朝代或者作者寻找诗，都很简单，比如select 诗词全文 from 诗词表 where 作者=‘李白’，如果数据很多，查询速度很慢，怎么办？可以在对应的查询字段上建立索引加速查询。
​ 但是如果现在有个需求：要求找到包含“望”字的诗词怎么办？
​ 用 select 诗词全文 from 诗词表 where 诗词全文 like‘%望%’，这个意味着 要扫描库中的诗词全文字段，逐条比对，找出所有包含关键词“望”字的记录。
​ 基本上，数据库中一般的 SQL优化手段都是用不上的。数量少，大概性能还能接 受，如果数据量稍微大点，就完全无法接受了，更何况在互联网这种海量数据的 情况下呢？怎么解决这个问题呢，用倒排索引。
​ 比如现在有：

蜀道难（唐）李白 蜀道之难难于上青天，侧身西望长咨嗟。
静夜思（唐）李白 举头望明月，低头思故乡。 
春台望（唐）李隆基 暇景属三春，高台聊四望。 
鹤冲天(宋)柳永 黄金榜上，偶失龙头望。明代暂遗贤，如何向？未遂风云便， 争不恣狂荡。何须论得丧？才子词人，自是白衣卿相。烟花巷陌，依约丹青屏障。 幸有意中人，堪寻访。且恁偎红翠，风流事，平生畅。青春都一饷。忍把浮名， 换了浅斟低唱！ 

​ 都有望字，于是可以这么保存:

序号	关键字	蜀道难	静夜思	春台望	鹤冲天
1	望	1	1	1	1
2	上	1	0	0	1
​ 其实，上述诗词的中每个字都可以作为关键字，然后建立关键字和文档之间 的对应关系，也就是标识关键字被哪些文档包含。
​ 所以，倒排索引就是，将文档中包含的关键字全部提取处理，然后再将关键字和文档之间的对应关系保存起来，最后再对关键字本身做索引排序。用户在检索某一个关键字是，先对关键字的索引进行查找，再通过关键字与文档的对应关系找到所在文档。
​ 在存储在关系型数据库中的数据，需要事先分析将数据拆分为不同的字段，而在 es这类的存储中，需要应用程序根据规则自动提取关键字，并形成对应关系。

索引在查询中的使用

​ 索引在查询中的作用到底是什么？在的查询中发挥着什么样的作用 呢？
​ 请记住：
​ **1、一个索引就是一个 B+树，索引让的查询可以快速定位和扫描到 需要的数据记录上，加快查询的速度。 **
​ 2、一个 select 查询语句在执行过程中一般最多能使用一个二级索引，即 使在 where 条件中用了多个二级索引。

扫描区间

​ 对于某个查询来说，最简单粗暴的执行方案就是扫描表中的所有记录，判断每一条记录是否符合搜索条件。如果符合，就将其发送到客户端，否则就跳过该 记录。这就是全表扫描。
​ 对于使用 InnoDB 存储引擎的表来说，全表扫描意味着从聚簇索引第一个叶 子节点的第一条记录开始，沿着记录所在的单向链表向后扫描，直到最后一个叶 子节点的最后一条记录。虽然全表扫描是一种很笨的执行方案，但却是一种万能 的执行方案，所有的查询都可以使用这种方案来执行，只是效率不高。
​ 有了索引，利用B+树查找索引列值等于某个值的记录，这样可以明显减少需要扫描的记录数量。由于B+树叶子节点中的记录是按照索引列值由小到 大的顺序排序的，所以即使只扫描某个区间或者某些区间中的记录也可以明显减 少需要扫描的记录数量。
比如下面这个查询语句:

SELECT * FROM order_exp WHERE id >= 3 AND id<= 99;

​ 这个语句其实是想查找id值在[3,99]区间中的所有聚簇索引记录。可以 通过聚簇索引对应的 B+树快速地定位到 id 值为 3 的那条聚簇索引记录，然后沿着记录所在的单向链表向后扫描,直到某条聚簇索引记录的 id 值不在[3,99]区间 中为止。
​ 与全表扫描相比，扫描 id 值在[3,99]区间中的记录已经很大程度地减少了需 要扫描的记录数量，所以提升了查询效率。其实所谓的全表扫描，可以理解 为扫描的区间是[负无穷，正无穷]或者[第一条记录，最后一条记录]。

范围区间扫描

​ 其实对于 B+树索引来说，只要索引列和常数使用=、<=>、IN、NOT IN、IS NULL、 IS NOT NULL、>、<、>=、<=、BETWEEN、!=（不等于也可以写成<>）或者 LIKE 操作符连接起来，就可以产生一个区间。
​ 1、IN 操作符的效果和若干个等值匹配操作符=之间用OR连接起来是一样 的，也就是说会产生多个单点区间，比如下边这两个语句的效果是一样的：

SELECT * FROM order_exp WHERE insert_time IN (2021-03-22 18:23:42, yyyy); 
SELECT * FROM order_exp WHERE insert_time= 2021-03-22 18:23:42 OR insert_time = yyyy; 

​ 2、!=产生的扫描区间呢？
比如 SELECT * FROM order_exp WHERE order_no != 'DD00_9S' 此时使用 idx_expire_time 执行查询时对应的扫描区间就是[第一条记录 , 'DD00_9S']和[ 'DD00_9S',最后一条记录]。
​ 3、LIKE 操作符比较特殊，只有在匹配完整的字符串或者匹配字符串前缀时 才产生合适的扫描区间。

所有搜索条件都可以使用某个索引的情况

​ 有时候每个搜索条件都可以使用到某个索引，比如下边这个查询语句：

SELECT * FROM order_exp WHERE order_no > 'DD00_6S' AND order_no > 'DD00_9S';

​ 这个查询中的搜索条件都可以使用到 idx_order_no，也就是说每个搜索条件 都对应着一个 idx_order_no 的范围区间。这两个小的搜索条件使用 AND 连接起 来，也就是要取两个范围区间的交集，两者交集当然就是 order_no > 'DD00_9S' 了，也就是说上边这个查询使用 idx_order_no 的范围区间就是('DD00_9S', 最后 一条记录)。
​ 再看一下使用 OR 将多个搜索条件连接在一起的情况：

SELECT * FROM order_exp WHERE order_no > 'DD00_6S' OR order_no > 'DD00_9S'; 

​ OR 意味着需要取各个范围区间的并集，所以上边这个查询使用 idx_expire_time 的范围区间就是( 'DD00_6S' ,最后一条记录)。

有的搜索条件无法使用索引的情况

比如下边这个查询：
SELECT * FROM order_exp WHERE expire_time> '2021-03-22 18:35:09' AND order_note = 'abc';
请注意，这个查询语句中能利用的索引只有 idx_expire_time 一个，而 idx_expire_time 这个二级索引的记录中又不包含 order_note 这个字段，所以在使 用二级索引idx_expire_time定位记录的阶段用不到 order_note = 'abc'这个条件，这个条件是在回表获取了完整的用户记录后才使用的，而范围区间是为了到索引 中取记录中提出的概念，所以在确定范围区间的时候不需要考虑 order_note = 'abc'这个条件。

使用联合索引执行查询时对应的扫描区间

​ 联合索引的索引列包含多个列，B+树每一层页面以及每个页面中的记录采用 的排序规则较为复杂，以 order_exp 表的 u_idx_day_status 联合索引为例，它采 用的排序规则如下所示：
​ 先按照 insert_time 列的值进行排序。
​ 在 insert_time 列的值相同的情况下，再按照 order_status 列的值进行排序。
​ 在 insert_time 和 order_status 列的值都相同的情况下，再按照 expire_time 列的值进行排序。

创建和删除索引的语句

-- 查看索引 
SHOW INDEX FROM table_name;
-- 创建索引 
CREATE [UNIQUE ] INDEX indexName ON mytable(columnname(length)); 
ALTER TABLE 表名 ADD [UNIQUE ] INDEX [indexName] ON (columnname(length));
-- 删除索引 
DROP INDEX [indexName] ON mytable;

索引的代价

空间上的代价

​ 这个是显而易见的，每建立一个索引都要为它建立一棵 B+树，每一棵 B+树 的每一个节点都是一个数据页，一个页默认会占用 16KB 的存储空间，一棵很大 的 B+树由许多数据页组成会占据很多的存储空间。

时间上的代价

​ 每次对表中的数据进行增、删、改操作时，都需要去修改各个 B+树索引。 而且B+树每层节点都是按照索引列的值从小到大的顺序排序而组成了 双向链表。不论是叶子节点中的记录，还是非叶子内节点中的记录都是按照索引 列的值从小到大的顺序而形成了一个单向链表。
​ 而增、删、改操作可能会对节点和记录的排序造成破坏，所以存储引擎需要 额外的时间进行一些记录移位，页面分裂、页面回收的操作来维护好节点和记录 的排序。如果建了许多索引，每个索引对应的 B+树都要进行相关的维护操 作，这必然会对性能造成影响。

高性能索引创建策略

索引列的类型尽量小

​ 在定义表结构的时候要显式的指定列的类型，以整数类型为例，有 TTNYINT、NEDUMNT、INT、BIGTNT 这么几种，它们占用的存储空间依次递增， 这里所说的类型大小指的就是该类型表示的数据范围的大小。能表示的整数 范围当然也是依次递增，如果想要对某个整数列建立索引的话，在表示的整 数范围允许的情况下，尽量让索引列使用较小的类型，比如能使用 INT 就不 要使用 BIGINT，能使用 NEDIUMINT 就不要使用INT，这是因为:

• 数据类型越小，在查询时进行的比较操作越快（CPU 层次)
• 数据类型越小，索引占用的存储空间就越少，在一个数据页内就可以放下 更多的记录，从而减少磁盘I/O带来的性能损耗，也就意味着可以把更多的数据 页缓存在内存中，从而加快读写效率。

​ 这个建议对于表的主键来说更加适用，因为不仅是聚簇索引中会存储主键值， 其他所有的二级索引的节点处都会存储一份记录的主键值，如果主键适用更小的 数据类型，也就意味着节省更多的存储空间和更高效的 I/O。

索引选择性和前缀索引

​ 创建索引应该选择选择性/离散性高的列。索引的选择性/离散性是指，不重复的索引值（也称为基数，cardinality)和数据表的记录总数（N)的比值，范围从 1/N 到 1 之间。索引的选择性越高则查询效率越高，因为选择性高的索引可以让 MySQL 在查找时过滤掉更多的行。唯一索引的选择性是 1，这是最好的索引选择性，性能也是最好的。
​ 很差的索引选择性就是列中的数据重复度很高，比如性别字段，不考虑政治正确的情况下，只有两者可能，男或女。那么在查询时，即使使用这个索引， 从概率的角度来说，依然可能查出一半的数据出来。
​ 哪列做为索引字段最好？当然是姓名字段，因为里面的数据没有任何重复， 性别字段是最不适合做索引的，因为数据的重复度非常高。
​ 怎么算索引的选择性/离散性？比如 order_exp这个表：

select COUNT(DISTINCT order_no)/count(*) cnt from order_exp;

​ 有时候需要索引很长的字符列，这会让索引变得大且慢。一个策略是前面提 到过的模拟哈希索引。
​ 模拟哈希索引：
​ order_exp 表中 order_note 字段很长，想把它作为一个索引，可以增加 一个 order_not_hash 字段来存储 order_note 的哈希值，然后在 order_not_hash 上 建立索引，相对于之前的索引速度会有明显提升，一个是对完整的 order_note 做索引，而后者则是用整数哈希值做索引，显然数字的比较比字符串的匹配要高 效得多。
​ 但是缺陷也很明显：
​ 1、需要额外维护 order_not_hash 字段；
​ 2、哈希算法的选择决定了哈希冲突的概率，不良的哈希算法会导致重复值 很多；
​ 3、不支持范围查找。
​ 还可以做些什么改进呢？还可以索引开始的部分字符，这样可以大大节约索 引空间，从而提高索引效率。但这样也会降低索引的选择性。一般情况下需 要保证某个列前缀的选择性也是足够高的，以满足查询性能。（尤其对于 BLOB、 TEXT 或者很长的 VARCHAR 类型的列，应该使用前缀索引，因为 MySQL 不允许索 引这些列的完整长度）。
​ 诀窍在于要选择足够长的前缀以保证较高的选择性，同时又不能太长（以便 节约空间)。前缀应该足够长，以使得前缀索引的选择性接近于索引整个列。换 句话说，前缀的“基数”应该接近于完整列的“基数”。
​ 为了决定前缀的合适长度，可以找到最常见的值的列表，然后和最常见的前 缀列表进行比较。

只为用于搜索、排序或分组的列创建索引

​ 只为出现在 WHERE 子句中的列、连接子句中的连接列创建索引， 而出现在查询列表中的列一般就没必要建立索引了，除非是需要使用覆盖索引； 又或者为出现在 ORDER BY 或 GROUP BY 子句中的列创建索引

多列索引

​ 一个常见的错误就是，为每个列创建独立的索引，或者按照错误的顺序创建多列索引。
​ 遇到的最容易引起困惑的问题就是索引列的顺序。正确的顺序依赖于使 用该索引的查询，并且同时需要考虑如何更好地满足排序和分组的需要。在一个多列 B-Tree 索引中，索引列的顺序意味着索引首先按照最左列进 行排序，其次是第二列，等等。所以，索引可以按照升序或者降序进行扫描，以满足精确符合列顺序的 ORDER BY、GROUP BY 和 DISTINCT 等子句的查询需求。
​ 所以多列索引的列顺序至关重要。对于如何选择索引的列顺序有一个经验法则：将选择性最高的列放到索引最前列。当不需要考虑排序和分组时，将选择性最高的列放在前面通常是很好的。这时候索引的作用只是用于优化 WHERE 条件 的查找。在这种情况下，这样设计的索引确实能够最快地过滤出需要的行，对于在 WHERE 子句中只使用了索引部分前缀列的查询来说选择性也更高。
​ 然而，性能不只是依赖于索引列的选择性，也和查询条件的有关。可能需要 根据那些运行频率最高的查询来调整索引列的顺序，比如排序和分组，让这种情 况下索引的选择性最高。
​ 同时，在优化性能的时候，可能需要使用相同的列但顺序不同的索引来满足 不同类型的查询需求。

设计三星索引

啥是三星索引？

​ 对于一个查询而言，一个三星索引，可能是其最好的索引。
​ 如果查询使用三星索引，一次查询通常只需要进行一次磁盘随机读以及一次窄索引片的扫描，因此其相应时间通常比使用一个普通索引的响应时间少几个数量级。
​ 三星索引概念是在《Rrelational Database Index Design and the optimizers》 一 书中提出来的。

达成三星索引

现在有表

create table customer( cno int, lname varchar(10), fname varchar(10), sex int, weight int, city varchar(10)); 
-- 建立索引 
create index idx_cust on customer(city,lname,fname,cno);

对于下面的 SQL 而言，这是个三星索引

select cno,fname from customer where lname =’xx’ and city =’yy’ order by fname;

来评估下：
第一颗星：所有等值谓词的列，是组合索引的开头的列，可以把索引片缩得 很窄，符合。
第二颗星：order by 的 fname 字段在组合索引中且是索引自动排序好的，符合。
第三颗星：select中的 cno 字段、fname 字段在组合索引中存在，符合。

达不成三星索引

现在有表

CREATE TABLE `test`
(
    `id`        int(11) NOT NULL AUTO_INCREMENT,
    `user_name` varchar(100) DEFAULT NULL,
    `sex`       int(11)      DEFAULT NULL,
    `age`       int(11)      DEFAULT NULL,
    `c_date`    datetime     DEFAULT NULL,
    PRIMARY KEY (`id`)
) ENGINE = InnoDB
  AUTO_INCREMENT = 12
  DEFAULT CHARSET = utf8;

SQL 语句如下：

select user_name,sex,age from test where user_name like 'test%' and sex =1 ORDER BY age

如果建立索引(user_name,sex,age)：
​ 第三颗星，满足
​ 第一颗星，满足
​ 第二颗星，不满足，user_name 采用了范围匹配，sex是过滤列，此时 age 列 无法保证有序的。
​ 上述看到，此时索引(user_name,sex,age)并不能满足三星索引中的第二颗星（排序）。
​ 于是改改，建立索引(sex, age，user_name)：
​ ​ 第一颗星，不满足，只可以匹配到 sex，sex 选择性很差，意味着是一个宽索引片。
​ ​ 第二颗星，满足，等值 sex 的情况下，age 是有序的。
​ ​ 第三颗星，满足，select 查询的列都在索引列中。
​ 对于索引(sex,age，user_name)可以看到，此时无法满足第一颗星，窄索引片的需求。
​ 以上 2 个索引，都是无法同时满足三星索引设计中的三个需求的，只能尽力满足 2 个。而在多数情况下，能够满足 2 颗星，已经能缩小很大的查询范围 了，具体最终要保留那一颗星（排序星 or 窄索引片星），这个就需要看查询者 自己的着重点了，无法给出标准答案。

主键是很少改变的列

​ 行是按照聚集索引物理排序的，如果主键频繁改变(update)，物理顺序会改变，MySQL 要不断调整 B+树，并且中间可能会产生页面的分裂和合并等等，会导致性能会急剧降低。

冗余和重复索引

​ MySQL 允许在相同列上创建多个索引，无论是有意的还是无意的。MySQL 需要单独维护重复的索引，并且优化器在优化查询的时候也需要逐个地进行考虑， 这会影响性能。重复索引是指在相同的列上按照相同的顺序创建的相同类型的索 引。应该避免这样创建重复索引，发现以后也应该立即移除。

删除未使用的索引

​ 除了冗余索引和重复索引，可能还会有一些服务器永远不用的索引。这样的 索引完全是累赘，建议考虑删除。

高性能索引使用策略

先总结一下，索引的使用策略一共有13中。

• 1、 尽量全值匹配
• 2、最佳左前缀法则
• 3、 覆盖索引尽量用
• 4、不等于要慎用
• 5、Null/Not 有影响
• 6、Like 查询要当心
• 7、字符类型加引号
• 8、使用索引扫描来做排序和分组
• 9、排序要当心
• 10、尽可能按主键顺序插入行
• 11、优化 Count 查询
• 12、优化 limit 分页
  附上别人的记忆总结：

  # 全职匹配我最爱，最左前缀要遵守； 
  # 带头大哥不能死，中间兄弟不能断； 
  # 索引列上少计算，范围之后全失效； 
  # LIKE 百分写最右，覆盖索引不写*； 
  # 不等空值还有 OR，索引影响要注意； 
  # VAR 引号不可丢， SQL 优化有诀窍。

  下面针对每一种进行分析，测试：

  不在索引列上做任何操作

  通常会看到一些查询不当地使用索引，或者使得 MySQL 无法使用已有 的索引。如果查询中的列不是独立的，则 MySQL 就不会使用索引。“独立的列” 是指索引列不能是表达式的一部分，也不能是函数的参数。
  例如，假设 order_status 上有索引，但是下面这个查询无法使用 order_status 列的索引:

  SELECT * FROM order_exp WHERE order_status + 1 = 1; 

  凭肉眼很容易看出 WHERE 中的表达式其实等价于 order_status = 0，但是 MySQL 无法自动解析这个方程式。这完全是用户行为。应该养成简化 WHERE 条件的习惯，始终将索引列单独放在比较符号的一侧。
  下面是另一个常见的错误:

  SELECT ... WHERE TO_DAYS(insert_time) - TO_DAYS(expire_time) <= 10; 

  在索引列上使用函数，也是无法利用索引的。

  尽量全值匹配

  建立了联合索引列后，如果我们的搜索条件中的列和索引列一致的话，这种 情况就称为全值匹配，比方说下边这个查找语句：

  select * from order_exp where insert_time='2021-03-22 18:34:55' and order_status=0 and expire_time='2021-03-22 18:35:14'; 

  我们建立的u_idx_day_statusr索引包含的3个列在这个查询语句中都展现出 来了，联合索引中的三个列都可能被用到。
  也许有个疑问，WHERE 子句中的几个搜索条件的顺序对查询结果 有啥影响么？也就是说如果我们调换insert_time, order_status, expire_time这 几个搜索列的顺序对查询的执行过程有影响么？比方说写成下边这样：

  Select * from order_exp where order_status=1 and expire_time='2021-03-22 18:35:14' and insert_time='2021-03-22 18:34:55'; 

  放心，MySQL 没这么蠢，查询优化器会分析这些搜索条件并且按照可以使用的索引中列的顺序来决定先使用哪个搜索条件，后使用哪个搜索条件。
  所以，当建立了联合索引列后，能在 where 条件中使用索引的尽量使用。

  最佳左前缀法则

  建立了联合索引列，如果搜索条件不够全值匹配怎么办？在我们的搜索语句 中也可以不用包含全部联合索引中的列，但要遵守最左前缀法则。指的是查询从 索引的最左前列开始并且不跳过索引中的列。
  比如：

  select * from order_exp where insert_time='2021-03-22 18:23:42' and order_status=1； 

  或

  select * from order_exp where insert_time='2021-03-22 18:23:42' ； 

  那为什么搜索条件中必须出现左边的列才可以使用到这个 B+树索引呢？比如下边的语句就用不到这个 B+树索引么？

  SELECT * FROM order_exp WHERE order_status=1; 

  或

  Select * from order_exp where order_status=1 and expire_time='2021-03-22 18:35:14';

  explain查询了执行计划后，发现是全表扫描。
  想一下，因为 B+树的数据页和记录先是按照 insert_time 列的值排序的， 在 insert_time 列的值相同的情况下才使用 order_status 列进行排序，也就是说 insert_time 列的值不同的记录中 order_status 的值可能是无序的。而现在你跳过 insert_time 列直接根据order_status 的值去查找，怎么可能呢？expire_time 也是 一样的道理，那如果我就想在只使用expire_time 的值去通过 B+树索引进行查找 咋办呢？这好办，你再对 expire_time 列建一个 B+树索引就行了。
  但是需要特别注意的一点是，如果我们想使用联合索引中尽可能多的列，搜 索条件中的各个列必须是联合索引中从最左边连续的列。比方说联合索引 u_idx_day_status 中列的定义顺序是insert_time, order_status, expire_time，如 果我们的搜索条件中只有 insert_time 和 expire_time，而没有中间的 order_status，

  select * from order_exp where insert_time='2021-03-22 18:23:42' and expire_time='2021-03-22 18:35:14'; 

  只能用到 insert_time 列的索引，order_status 和 expire_time 的索引就用不上 了，道理不用多说了。

  范围条件放最后

  这一点，也是针对联合索引来说的，前面我们反复强调过，所有记录都是按 照索引列的值从小到大的顺序排好序的，而联合索引则是按创建索引时的顺序进 行分组排序。
  比如：

  select * from order_exp_cut where insert_time>'2021-03-22 18:23:42' and insert_time<'2021-03-22 18:35:00';

  由于B+树中的数据页和记录是先按 insert_time 列排序的，所以我们上边的 查询过程其实是这样的：
  找到 insert_time 值为2021-03-22 18:23:42的记录。
  找到 insert_time 值为2021-03-22 18:35:00的记录。
  由于所有记录都是由链表连起来的，所以他们之间的记录都可以很容易的取 出来，找到这些记录的主键值，再到聚簇索引中回表查找完整的记录。
  但是如果对多个列同时进行范围查找的话，只有对索引最左边的那个列进行 范围查找的时候才能用到 B+树索引：

  select * from order_exp_cut where insert_time>'2021-03-22 18:23:42' and insert_time<'2021-03-22 18:35:00' and order_status > -1;

覆盖索引尽量用

不等于要慎用

Null/Not 有影响

Like 查询要当心

字符类型加引号

使用索引扫描来做排序和分组

排序要当心

尽可能按主键顺序插入行

优化 Count 查询

优化 limit 分页