MySQL面试 索引篇

问：什么是索引？

索引是一种能帮助 MySQL 提高查询效率的数据结构。

问：为什么添加索引能提高查询效率？

索引可以将无序内容转换为有序的一个集合（相对），就如同新华字典，如果没有目录，那么查询一个汉字就需要很长时间了。如果没有索引我们查询数据是需要遍历双向链表来定位对应的page，现在通过索引创建的“目录”就可以很快定位对应页上了！
其实底层实现的结构就是B+树，B+树作为树的一种实现能够让我们很快查找出对应内容。

问：和索引相关的算法有哪些，底层都是怎么实现的？

和索引相关的算法：二分查找法、二叉查找树、平衡二叉树、B树、B+树。
对于 MySQL 而言，使用最频繁的就是 B+ 树索引，而 B+ 树是借鉴了二分查找法、二叉查找树、平衡二叉树、B 树的一些思想构建的。

二分查找法

二分查找法的查找过程是：将记录按顺序排列，查找时先以有序列的中点位置为比较对象，如果要找的元素值小于该中点元素，则将查询范围缩小为左半部分；如果要找的元素值大于该中点元素，则将查询范围缩小为右半部分。以此类推，直到查到需要的值。
比如要从 1、2、3、5、6、7、9 几个数字中找到 6，首先找到中点位置的值，这里是 5，因为 6 大于 5，所以查询以 5 为中点右边的数字（其实就是大于 5 的数字），又因为 6 小于 7，所以继续查询以 7 为中点左边的数字，发现数字 6，返回结果。

发现用了 3 次就查找到 6 这个数字了。如果是顺序查找，则需要查询 5 次（从第一个数字 1 开始，如果发现不是 6，则继续查找下一个，直到查询到 6）

二叉查找树

左子树比根节点小
右子树比根节点大
通过中序遍历，得到从小到大的排序

数据查找方式，如查询 5 的记录，
先找根节点 8，8大于5；
查找8的左子树，找到4，4小于5；
查找4的右子树，找到5；
一共找了3次

二叉查找树可以任意构造，极端情况下会退化成链表，如图所示

如果想要查询9，需要遍历整个链表，查询6次，查询效率就低了。这时候就需要这棵树是平衡的，于是就有了平衡二叉树，也叫AVL树

试想一下，如果 3 的右子树后面拖更多的数字，那查询效率得多低啊！
因此，如果想让二叉查找树性能最好，就需要这颗树是平衡的，此时，平衡二叉树出场了。

平衡二叉树（AVL树）

首先，满足二叉查找树定义
其次，所有节点的左右子树最大高度差不超过1

显然平衡二叉查找树的平均查找速度比顺序查找更快。
但是平衡二叉树有个缺点就是，每个节点最多只有两个分支，如果数据量比较大，要经历多层节点才能查询在叶子节点的数据。
如果在平衡二叉树的基础上，每个节点可以有多个分支，那即使在叶子节点的数据，是不是查询效率也比较高呢？这就引出了 B 树结构。

B 树

B 树可以理解为一个节点可以拥有多于 2 个子节点的多叉查找树。B 树中同一键值不会出现多次，要么在叶子节点，要么在内节点上。

与平衡二叉树相比，B 树利用多个分支（平衡二叉树只有两个分支）节点，减少获取记录时所经历的节点数。
B 树也是有缺点的，因为每个节点都包含 key 值和 data 值，因此如果 data 比较大时，每一页存储的 key 会比较少；当数据比较多时，同样会有：“要经历多层节点才能查询在叶子节点的数据” 的问题。这时，B+ 树站了出来。

B+ 树

B+ 树是 B 树的变体，定义基本与 B 树一致，与 B 树的不同点：
所有叶子节点中包含了全部关键字的信息
各叶子节点用指针进行连接
非叶子节点上只存储 key 的信息，这样相对 B 树，可以增加每一页中存储 key 的数量。
B 树是纵向扩展，最终变成一个 “瘦高个”，而 B+ 树是横向扩展的，最终会变成一个 “矮胖子”

在 B+ 树中，所有记录节点都是按键值的大小顺序存放在同一层的叶子节点上。B+ 树中的 B 不是代表二叉 (binary) 而是代表（balance），B+ 树并不是一个二叉树。

与B树最大的区别是：它的键一定会出现在叶子节点上，同时也有可能在非叶子节点中重复出现。而 B 树中同一键值不会出现多次。

问：说一说对 B+ 树的理解？

B+树是由B树发展来的，在数据库中，B+ 树的高度一般都在 2 ~ 4 层，所以查找某一行数据最多只需要 2 到 4 次 IO。而没索引的情况，需要逐行扫描，明显效率低很多，这也就是为什么添加索引能提高查询速度。
B+ 树索引并不能找到一个给定键值的具体行，B+ 树索引能找到的只是被查找数据行所在的页。然后数据库通过把页读入到缓冲池（buffer pool）中，在内存中通过二分查找法进行查找，得到需要的数据。
InnoDB 中 B+ 树索引分为聚集索引和辅助索引。
问：B树与B+树的区别
B树每个节点都存储数据。B+树只有叶子节点存储数据（B+数中有两个头指针：一个指向根节点，另一个指向关键字最小的叶节点），叶子节点包含了这棵树的所有数据，所有的叶子结点使用链表相连，便于区间查找和遍历，所有非叶节点起到索引作用。

B树中叶节点包含的关键字和其他节点包含的关键字是不重复的，B+树的索引项只包含对应子树的最大关键字和指向该子树的指针，不含有该关键字对应记录的存储地址。

B树中每个节点（非根节点）关键字个数的范围为m/2(向上取整)-1,m-1，并且具有n个关键字的节点包含（n+1）棵子树。B+树中每个节点（非根节点）关键字个数的范围为m/2(向上取整),m，具有n个关键字的节点包含（n）棵子树。

B+树中查找，无论查找是否成功，每次都是一条从根节点到叶节点的路径。

问：B树的优点

B树的每一个节点都包含key和value，因此经常访问的元素可能离根节点更近，因此访问也更迅速。

问：B+ 树的优点

1.所有的叶子结点使用链表相连，便于区间查找和遍历。B树则需要进行每一层的递归遍历。相邻的元素可能在内存中不相邻，所以缓存命中性没有B+ 树好。
2.b+ 树的中间节点不保存数据，能容纳更多节点元素。

问：B树和 B+ 树的共同优点

考虑磁盘IO的影响，它相对于内存来说是很慢的。索引是存储在磁盘上的，当数据量大时，就不能把整个索引全部加载到内存，只能逐一加载每一个磁盘页（对应索引树的节点）。所以我们要减少IO次数，对于树来说，IO次数就是树的高度，而“矮胖”就是b树的特征之一，m的大小取决于磁盘页的大小。

问：B+Tree索引和Hash索引区别？

大量不同数据等值精确查询， Hash 索引效率通常比 B+ Tree 高。Hash 索引是通过 Hash 函数，计算出 Hash 值，在表中找出对应的数据。但是 Hash 索引不支持模糊查询、排序、范围查询和联合索引中的最左匹配规则，而这些 B+ Tree 索引都支持。

哈希索引没办法利用索引完成排序

哈希索引不支持多列联合索引的最左匹配规则

如果有大量重复键值的情况下，哈希索引的效率会很低，因为存在哈希碰撞问题。

问：B+ 树的阶数是如何确定的，是创建数据结构的时候就确定了一个数字M，还是这个阶数可以动态变化？

B+ 树属于平衡树家族的树，这种数据结构有其特定的算法，会根据插入修改的数据自动生成对应的树结构，B+树1层可放468行数据，2层 56.3万，3层 6.77 亿，4 层8140亿，相信一般很难达到这个数据量，确实层数增加会增加 IO 次数，所以使用者也需要控制，尽量避免数据量过大。层数是随着数据量变化的

问：Innodb中 B+ 树索引是什么样的？

每个索引都对应一棵B+树，B+树分为好多层，最下边一层是叶子节点，其余的是内节点。所有用户记录都存储在B+树的叶子节点，所有目录项记录都存储在内节点。

InnoDB存储引擎会自动为主键（如果没有它会自动帮我们添加）建立聚簇索引，聚簇索引的叶子节点包含完整的用户记录。

我们可以为自己感兴趣的列建立二级索引，二级索引的叶子节点包含的用户记录由索引列 + 主键组成，所以如果想通过二级索引来查找完整的用户记录的话，需要通过回表操作，也就是在通过二级索引找到主键值之后再到聚簇索引中查找完整的用户记录。

B+树中每层节点都是按照索引列值从小到大的顺序排序而组成了双向链表，而且每个页内的记录（不论是用户记录还是目录项记录）都是按照索引列的值从小到大的顺序而形成了一个单链表。如果是联合索引的话，则页面和记录按照先按照联合索引前边的列排序，如果该列值相同，再按照联合索引后边的列排序。

通过索引查找记录是从B+树的根节点开始，一层一层向下搜索。由于每个页面都按照索引列的值建立了Page Directory（页目录），所以在这些页面中的查找非常快。

问：什么是聚集索引？

定义：数据行的物理顺序与列值的逻辑顺序相同。

由表主键构成的一棵B+树，也叫主键索引。叶子节点存放数据页，每个数据页之间通过双向链表进行链接，单个数据页中的数据是有序存放的。

InnoDB聚集索引的叶子节点存储行记录，因此InnoDB必须要有且只有一个聚集索引。
如果表定义了PK（Primary Key，主键），那么PK就是聚集索引。如果表没有定义PK，则第一个NOT NULL UNIQUE的列就是聚集索引，否则InnoDB会另外创建一个隐藏的ROWID作为聚集索引。

注：唯一索引不是聚簇索引，由于 InnoDB 表实际的数据页只能按照一颗 B+ 树进行排序，因此 InnoDB 每张表只能有一个聚集索引。

建表语句：

CREATE TABLE `t8` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`a` int(11) NOT NULL,
`b` char(2) NOT NULL,
PRIMARY KEY (`id`),
KEY `idx_a` (`a`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8mb4;
插入数据：insert into t8(a,b) values (1,'a'),(2,'b'),(3,'c'),(5,'e'),(6,'f'),(7,'g'),(9,'i');

InnoDB 的数据是按照主键顺序存放的，而聚集索引就是按照每张表的主键构造一颗 B+ 树，它的叶子节点存放的是整行数据。
InnoDB 的主键一定是聚集索引。如果没有定义主键，聚集索引可能是第一个不允许为 null 的唯一索引，也有可能是 row id。

创建语句：
alter table table_name add primary key(colum_name)

由于实际的数据页只能按照一颗 B+ 树进行排序，因此每张表只能有一个聚集索引（TokuDB 引擎除外）。查询优化器倾向于采用聚集索引，因为聚集索引能够在 B+ 树索引的叶子节点上直接找到数据。聚集索引对于主键的排序查找和范围查找速度非常快。

两点关键信息：
根据主键值创建了 B+ 树结构
每个叶子节点包含了整行数据

值得注意的是，最好还是在创建表的时候添加聚集索引，由于聚集索引的物理顺序上的特殊性，因此如果再在上面创建索引的时候会根据索引列的排序移动全部数据行上面的顺序，会非常地耗费时间以及性能。

聚簇索引(或称聚集索引)指的是B＋树的叶子节点记录的是行的内容，在innodb中只有主键索引是如此，而非主键索引(辅助索引)的叶子节点记录的是主键id，所以只有主键索引是聚簇索引。

问：什么是辅助索引？

由表除主键外的其他字段构成的一棵B+树
叶子节点存放索引键值 + 主键值

辅助索引：定义在主文件上的任意一个或者多个非排序字段的辅助存储结构。在innodb引擎中除了主键以外的索引都是辅助索引，也称为二级索引。
聚集索引的叶子节点存放了整行数据，而 InnoDB 存储引擎辅助索引的叶子节点并不会放整行数据，而存放的是键值和主键 ID。

查找数据时，先在辅助索引里查找，如果辅助索引里的字段满足查询返回所需的数据，则直接返回；否则进行回表查询，根据主键按照主键索引定位数据页，从数据页中找到所需的数据。

比如一颗高度为 3 的辅助索引树中查找数据，那需要对这颗辅助索引树遍历 3 次找到指定主键，如果聚集索引树的高度也为 3，那么还需要对聚集索引树进行 3 次查找，最终找到一个完整的行数据所在的页，因此获取数据一共需要 6 次逻辑 IO 访问。

t8表中a索引如下：
根据 a 字段的值创建了 B+ 树结构
每个叶子节点保存的是 a 字段自己的键值和主键 ID

比如有下面这条查询语句：
select * from t8 where a=3;
它先通过 a 字段上的索引树，得到主键 id 为 3，再到 id 的聚集索引树上找到对应的行数据。
而下面这条 SQL:
select * from t8 where id=3;
查询到的结果是一样的，而执行过程则只需要搜索 id 的聚集索引树。我们能看出辅助索引的查询比主键查询多扫描一颗索引树，所以，我们应该尽量使用主键做为条件进行查询。

辅助索引不是聚集索引，但是在 InnoDB 中，主键索引一定是聚集索引。

问：什么是非聚集索引？

定义：该索引中索引的逻辑顺序与磁盘上行的物理存储顺序不同，一个表中可以拥有多个非聚集索引。
其实按照定义，除了聚集索引以外的索引都是非聚集索引，只是人们想细分一下非聚集索引，分成普通索引，唯一索引，全文索引。

非聚集索引叶节点仍然是索引节点，只是有一个指针指向对应的数据块，此如果使用非聚集索引查询，而查询列中包含了其他该索引没有覆盖的列，那么他还要进行第二次回表的查询，查询节点上对应的数据行的数据。

问：什么是回表查询？

普通索引查询到主键索引后，回到主键索引树搜索的过程，我们称为回表查询。
如果语句是 select * from T where k=5，即普通索引查询方式，则需要先搜索 k 索引树，得到 ID 的值为 500，再到 ID 索引树搜索一次，这个过程称为回表查询。也就是说，基于非主键索引的查询需要多扫描一棵索引树。
因此，我们在应用中应该尽量使用主键查询。

如以下表t1：
id username score
1 小明 90
2 小红 80
3 小华 92
… … …
256 小英 70
表中有聚集索引clustered index(id), 非聚集索引index(username)。

使用以下语句进行查询，不需要进行二次查询，直接就可以从非聚集索引的节点里面就可以获取到查询列的数据。
select id, username from t1 where username = ‘小明’
select username from t1 where username = ‘小明’

但是使用以下语句进行查询，就需要二次的查询去获取原数据行的score：
select username, score from t1 where username = ‘小明’

问：如何解决非聚集索引的二次查询问题？

可以使用：复合索引（也叫联合索引）
建立两列以上的索引，即可查询复合索引里的列的数据而不需要进行回表二次查询，如index(col1, col2)，执行下面的语句
select col1, col2 from t1 where col1 = ‘213’;

要注意使用复合索引需要满足最左侧索引的原则，也就是查询的时候如果where条件里面没有最左边的一到多列，索引就不会起作用。

1.使用聚集索引的查询效率要比非聚集索引的效率要高，但是如果需要频繁去改变聚集索引的值，写入性能并不高，因为需要移动对应数据的物理位置。

2.非聚集索引在查询的时候可以的话就避免二次查询，这样性能会大幅提升。

问：覆盖索引注意事项有哪些？

又称为索引覆盖，从辅助索引中就可以得到查询的记录，而不需要查询聚集索引中的记录。使用覆盖索引，可以减少回表操作，降低IO操作。

不是所有类型的索引都可以成为覆盖索引的。因为覆盖索引必须要存储索引的列值，而哈希索引、空间索引和全文索引等都不存储索引列值，索引MySQL只能使用B-Tree索引做覆盖索引。
explain sql 如果extra列中显示【Using Index】说明用了覆盖索引。

问：主键与唯一索引有哪些区别

主键是一种约束，唯一索引是一种索引，不允许列有重复的值，和主键一样，同样可以提高访问效率。
一张表只能有一个主键，但可以创建多个唯一索引
主键创建后一定包含一个唯一索引，唯一索引并一定是主键
主键不能为null，唯一索引可以为null
主键可以做为外键，唯一索引不行

问：普通索引与唯一索引有哪些区别

讨论两者区别前，需要先说说insert buffer和change buffer。
Insert Buffer
对于非聚集索引的插入时，先判断插入的非聚集索引页是否在缓冲池中。如果在，则直接插入；如果不在，则先放入 Insert Buffer 中，然后再以一定频率和情况进行 Insert Buffer 和辅助索引页子节点的 merge 操作。这时通常能将多个插入合并到一个操作中（因为在一个索引页中），就大大提高了非聚集索引的插入性能。

为什么要增加 Insert Buffer？

增加 Insert Buffer 有两个好处：
减少磁盘的离散读取
将多次插入合并为一次操作

注意使用 Insert Buffer 得满足两个条件：
索引是辅助索引
索引不是唯一
Change buffer
InnoDB 从 1.0.x 版本开始引入了 Change Buffer，可以算是对 Insert Buffer 的升级。从这个版本开始，InnoDB 存储引擎可以对 insert、delete、update 都进行缓存。

影响参数有两个：
innodb_change_buffering：确定哪些场景使用 Change Buffer，它的值包含：none、inserts、deletes、changes、purges、all。默认为 all，表示启用所有。
innodb_change_buffer_max_size：控制 Change Buffer 最大使用内存占总 buffer pool 的百分比。默认25，表示最多可以使用 buffer pool 的 25%，最大值50。

跟 Insert Buffer 一样，Change Buffer 也得满足这两个条件：
索引是辅助索引
索引不是唯一

为什么唯一索引的更新不使用 Change Buffer ?

原因：唯一索引必须要将数据页读入内存才能判断是否违反唯一性约束。如果都已经读入到内存了，那直接更新内存会更快，就没必要使用 Change Buffer 了。

区别：
插入：
有普通索引的字段可以写入重复的值，而有唯一索引的字段不可以写入重复的值。

修改：
数据修改时，普通索引优于唯一索引，因为普通索引可以用 Change Buffer，而唯一索引不行。修改唯一索引必须判断是否违反唯一性约束，其实在 RR 隔离级别下，可能会出现一个比较严重的问题：死锁。

查询：
对于普通索引，查找到满足条件的第一个记录，还需要查找下一个记录，直到不满足条件。
对于唯一索引来说，查找到第一个记录返回结果就结束了。

但是 InnoDB 是按页从磁盘读取的，所以很大可能根据该普通索引查询的数据都在一个数据页里，因此如果通过普通索引查找到第一条满足条件所在的数据页，再查找后面的记录很大概率都在之前的数据页里，也就是多了几次内存扫描，实际这种消耗可以忽略不计。查询性能方面两者差别不大。

问：为什么RR级别下，普通索引比唯一索引出现死锁概率低

唯一索引在insert的时候时候会加上GAP锁来解决幻读的问题，GAP锁与意向插入锁冲突，所以容易死锁。

问：哪些情况下需要添加索引？

目前比较常见需要创建索引的场景有：数据检索时在条件字段添加索引、聚合函数对聚合字段添加索引、对排序字段添加索引、为了防止回表添加索引、关联查询在关联字段添加索引等。

数据检索时，建议在条件字段上添加索引。索引对聚合函数 count(*) 也有优化作用。
排序字段上创建索引
如果对单个字段排序，则可以在这个排序字段上添加索引来优化排序语句；
如果是多个字段排序，可以在多个排序字段上添加联合索引来优化排序语句；
如果是先等值查询再排序，可以通过在条件字段和排序字段添加联合索引来优化排序语句。

避免回表查询查询，可以通过添加覆盖索引让 SQL 不需要回表，从而减少树的搜索次数，让查询更快地返回结果。

多表关联查询的时候，通过在关联字段添加索引，让 BNL变成 NLJ 或者 BKA可以加速查询。

问：索引应该如何设计？

索引通俗的来说，索引就像是一本词典的目录，是为了提高数据查询的效率而设计的。
首先应该保证大部分的 SQL 语句都能流畅运行。当然在设计索引的过程中也不能滥用，在表上添加过多无用的索引确实会带来一些副作用，比如 DML 语句会变慢。

总的来说，在 OLTP 业务中，我们只需要保证大部分的高频 SQL 可以流畅运行就好了。如果某个 SQL 每天、每周甚至每个月才运行一次，也着实没有建立索引的必要。

问：创建索引会带来哪些代价

它在空间和时间上都会拖后腿：

空间上的代价

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

时间上的代价

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

所以说，一个表上索引建的越多，就会占用越多的存储空间，在增删改记录的时候性能就越差。

MySQL是如何执行SQL语句的

select * from tb_user where id = 1;

SQL执行流程

客户端与 MySQL Server建立连接，客户端和服务端都使用池化技术来管理连接
服务端为每个连接分配一个线程，通过线程来监听客户端的请求，当客户端发起一条SQL请求时，服务端线程接收SQL请求，交给SQL接口处理
SQL接口将SQL语句交给查询解析器处理
查询解析器进行词法分析，分析SQL是否符合数据库规则，select，insert等关键字是否正确，再进行语法分析，校验SQL语句是不是合法，如果不合法会报错，合法的话最后会生成一颗解析树
查询优化器将解析树转化为执行计划，一条复杂的select语句，有很多种执行方式，优化器会选择它认为最优的执行计划。

执行器有了执行计划后，就可以通过执行器按照计划中的步骤，调用存储引擎接口操作数据了
最后在存储引擎中操作内存或磁盘中的数据

update更新流程

Innodb update 语句执行流程
update tb_user set name = ‘张三’ where id = 1;

（1）先从缓冲池中查找 id = 1 的数据，如果找不到则读取磁盘数据，然后加载到缓冲池中，同时还会对这行数据进行加锁
（2）将 id = 1，name = muskmelon 的数据写入 undo log，用于回滚
（3）更新缓冲池中的数据 id = 1，name = 张三，此时缓冲池中的数据属于脏数据，因为数据还未刷入磁盘，磁盘上的 name = muskmelon
（4）提交事务，将缓冲池的数据写入 redo log
（5）通过参数 innodb_flush_log_at_trx_commit 来控制redo log是否需要刷盘：
0：事务提交后，每隔 innodb_flush_log_at_timeout时间写 os cache 且刷盘，速度最佳，但会丢失数据
1：事务提交后，写os cache 且刷盘，数据0丢失
2：事务提交后，先写到 os cache ，每隔 innodb_flush_log_at_timeout 时间进行刷盘，效率较直接刷盘高一些，但仍然会存在数据丢失情况
（6）写binlog日志，通过参数sync_binlog 来配置是否需要刷盘
0：不刷盘，写入os cache，由操作系统决定何时去刷盘
1：刷盘，写入 磁盘，当事务提交时，刷盘
N：当N 个事务提交时，刷盘
（7）向 redo log中写入binlog文件位置和 commit 标记，保证 redo log 与 binlog数据一致，至此整个事务才算真正的提交了。
（8）存储引擎内部通过IO线程将缓冲池中的数据刷入磁盘

SQL 执行过程

SQL 语言中，是按照一个固定的规则来执行的
(8) SELECT
(9) DISTINCT <select_list>
(1) FROM <left_table>
(3) <join_type> JOIN <right_table>
(2) ON <join_condition>
(4) WHERE <where_condition>
(5) GROUP BY <group_by_list>
(6) WITH {CUBE|ROLLUP}
(7) HAVING <having_condition>
(10) ORDER BY <order_by_condition>
(11) LIMIT <limit_number>

SQL 的执行大致是 11 个步骤，但是执行顺序和代码顺序不同，最先执行的是 FROM 子句，最后执行的是 LIMIT 子句。

在 SQL 执行的过程中，每一个步骤中都会产生一个虚拟表（Virtual Table，简称 VT），用来保存 SQL 的执行结果。

1.FROM。经过 FROM 语句得到一个虚拟表 VT1，如果有多表关联，会先执行笛卡尔积运算。
2.ON。对虚拟表 VT1 执行 ON 条件筛选，筛选出符合 <join_condition> 条件的行，结果放入虚拟表 VT2 中。
3.JOIN。如果是 OUTER JOIN 类型，上述表中未匹配到的行会作为外部行添加到虚拟表 VT2 中，生成虚拟表 VT3。
4.WHERE。对虚拟表 VT3 应用 WHERE 条件，将符合 <where_condition> 条件的行插入到虚拟表 VT4 中。
5.GROUP BY。根据 <group_by_list> 子句中的条件，对行记录进行分组处理，生成虚拟表 VT5。
6.WITH。对表 VT5 进行 CUBE 或 ROLLUP 操作，生成虚拟表 VT6。
7.HAVING。对虚拟表 VT6 的结果应用 HAVING 过滤，将符合 <having_condition> 条件的记录插入到虚拟表 VT7 中。
8.SELECT。根据 SELECT 中的条件，选出指定的列，生成虚拟表 VT8。
9.DISTINCT。对虚拟表 VT8 中的条件进行排重，产生虚拟表 VT9。
10.ORDER BY。对虚拟表 VT9 中的记录，按照 <order_by_condition> 的条件进行排序操作，生成虚拟表 VT10。
11.LIMIT。最后根据 LIMIT 的条件，取出指定的 LIMIT 区间的行，生成虚拟表 VT11，并将结果返回给用户。

如果没有索引，SQL 执行的第 1 个步骤会先执行笛卡尔积运算。

但是如果多表关联的条件上有可以利用的索引，MySQL 查询优化器的物理查询处理会对这个 SQL 语句进行优化，选择更优的路径执行表关联的操作，这可以避免笛卡尔表的产生，会大幅缩短整个 SQL 语句的执行时间。

因此，在进行 SQL 调优的时候，要按照 SQL 语句执行的顺序进行优化，重点处理执行成本比较高的部分：
1.如果是多表 JOIN，先看 JOIN 的条件是否合理，列上是否有索引，避免笛卡尔积的产生。
2.检查 WHERE 条件的索引是否合理，尽最大可能缩小结果集的大小。
3.检查 GROUP BY 条件上是否有索引，如果没法使用索引，MySQL 会通过临时表完成 GROUP BY 的操作。
4.检查 ORDER BY 条件是否利用到了索引，如果没有索引，使用排序算法将结果集放入临时表中进行排序。

查询优化器的估算

现代的关系型数据库，大多都使用了基于成本的优化器。
在估算成本的时候，查询代价估算基于 CPU 代价和 IO 代价。也就是：
总代价 = IO 代价 + CPU 代价

具体到 SQL 子句中，MySQL 数据库会认为 GROUP BY、ORDER BY 的操作不走索引的代价，会高于 WHERE 子句不走索引的代价。

因此在索引的选择上，会优先给 GROUP BY、ORDER BY 子句使用索引。

三星索引：

一星：WHERE 条件能通过索引取出需要的数据
二星：将排序操作（ORDER BY）的列加入到索引中
三星：可以通过索引直接取出整个 SQL 语句需要的数据，不必回表

问：MySQL 会错选索引吗？

MySQL 会错选索引，比如 k 索引的速度更快，但是 MySQL 并没有使用而是采用了 v 索引，这种就叫错选索引，因为索引选择是 MySQL 的服务层的优化器来自动选择的，但它在复杂情况下也和人写程序一样出现缺陷。

问：MySQL 为什么会选错索引？

分析索引先得查看表索引详情：show index from 表;

Non_unique：如果是唯一索引，则值为 0，如果可以有重复值，则值为 1
Key_name：索引名字
Seq_in_index：索引中的列序号，比如联合索引 idx_a_b_c (a,b,c) ，那么三个字段分别对应 1,2,3
Column_name：字段名
Collation：字段在索引中的排序方式，A 表示升序，NULL 表示未排序
Cardinality：索引中不重复记录数量的预估值，该值等会儿会详细讲解
Sub_part：如果是前缀索引，则会显示索引字符的数量；如果是对整列进行索引，则该字段值为 NULL
Null：如果列可能包含空值，则该字段为 YES；如果不包含空值，则该字段值为 ’ ’
Index_type：索引类型，包括 BTREE、FULLTEXT、HASH、RTREE 等

Cardinality 取值

Cardinality 表示该索引不重复记录数量的预估值。如果该值比较小，那就应该考虑是否还有必要创建这个索引。比如性别这种类型的字段，即使加了索引，Cardinality 值比较小，使用性别做条件查询数据时，可能根本用不到已经添加的索引。

Cardinality 值的统计频率是怎样的呢？

考虑到如果每次索引在发生操作时，都重新统计字段不重复记录数赋给 Cardinality，将会对数据库带来很大的负担。因此 Cardinality 不是每次操作都重新统计的，而是通过采样的方法来完成的。

Cardinality 统计信息的更新发生在两个操作中：INSERT 和 UPDATE。当然也不是每次 INSERT 或 UPDATE 就更新的，其更新时机为：

表中 1/16 的数据已经发生过变化
表中数据发生变化次数超过 2000000000

Cardinality 值是怎样统计和更新的呢？

默认Innodb存储引擎对8个叶子节点进行采样。受参数innodb_stats_sample_pages影响。
当执行语句analyze table、show table status、show index、访问information_schema.tables、访问information_schema.statistics时都会导致innodb存储引擎重新计算cardinality的值。如果表中数据大，且存在多个辅助索引，以上操作可能会很慢。

其他影响参数：

innodb_stats_transient_sample_pages：设置统计 Cardinality 值时每次采样页的数量，默认值为 8。

innodb_stats_method：用来判断如果对待索引中出现的 NULL 值记录，默认为 nulls_equal，表示将 NULL 值记录视为相等的记录。另外还有 nulls_unequal 和 nulls_ignored。nulls_unequal 表示将 NULL 视为不同的记录，nulls_ignored 表示忽略 NULL 值记录。

innodb_stats_persistent：是否将 Cardinality 持久化到磁盘。好处是：比如数据库重启，不需要再计算 Cardinality 的值。

innodb_stats_on_metadata：当通过命令 show table status、show index 及访问 information_chema 库下的 tables 表和 statistics 表时，是否需要重新计算索引的 Cardinality。目的是考虑有些表数据量大，并且辅助索引多时，执行这些操作可能会比较慢，而使用者可能并不需要更新 Cardinality。

统计信息不准会确导致选错索引。

在 MySQL 中，优化器控制着索引的选择。一般情况下，优化器会考虑扫描行数、是否使用临时表、是否排序等因素，然后选择一个最优方案去执行 SQL 语句。

而 MySQL 中扫描行数并不会每次执行语句都去计算一次，因为每次都去计算，数据库压力太大了。实际情况是通过统计信息来预估扫描行数。这个统计信息就可以看成 show index 中的 Cardinality。

单次选取的数据量过大会导致选错索引。

单次选取的数据量过大也有可能导致优化器选错索引，这种时候，可以尝试使用 force index 让 sql 强制走某个索引。

问：MySQL 哪些情况下会选错索引？

MySQL 选错索引的第一种情况，查询优化器的代价估算模型在个别特殊情况下会不太准确。因为在 MySQL 的代价估算模型中，排序的成本是远高于表扫描的。

MySQL 选错索引的第二种情况，是困难谓词。什么是困难谓词，如果一个谓词字段和索引无法进行匹配，那么对于优化器而言，处理起来会比较困难。
这些谓词有时也被称为_不可索引化_。比如最典型的 NOT 和 LIKE '% xx’语法，都是很难进行索引匹配的。

MySQL 选错索引的第三种情况，是隐性转换。在 MySQL 中，当操作符和不同类型的操作数一起使用时，会发生类型转换以使操作数兼容。
这种情况也会导致索引失效。

Where子句

在 WHERE 子句中，建议不要对索引列做任何的运算操作，在索引列上使用函数或者运算符，都会导致无法使用索引。
WHERE abs(id) = 1
或
WHERE -(id) = -1

有一种特殊的情况，从 MySQL 8.0 开始引入了函数索引
另外有一种情况是在 value 对象上使用索引或运算符时是可以用到索引的
WHERE id = abs(-1)
或
WHERE id = -1

运算

查询数据表，已经创建了索引，WHERE 条件中也包含索引列，但是列对象参与了运算。
例如：
WHERE col1 + col2 = 2
或
WHERE col1 = col2 + 1
即使两个列上都有索引，MySQL 仍然无法在表达式中使用这些索引。

范围操作符

查询数据表，已经创建了索引，WHERE 条件中也包含索引列，但是操作符是范围操作符 <> 或！=。这种情况下，MySQL 数据库无法正常使用索引
例如：
WHERE col1 <> 1
或
WHERE col1 != 1

隐性转换

发生了隐性转换也无法用到索引。对于隐性转换需要分两种情况讨论，因为在当前的版本中，MySQL 查询优化器已经可以转换字符型数字，从而可以使用到索引。也就是说，如果索引列是 INT 型的，隐性转换可以使用到索引。但如果索引列是字符型的，隐性转换无法使用索引。

困难谓词

困难谓词由于语义比较特殊，使用索引比较困难，常见的困难谓词有 LIKE、OR、IN 等。
LIKE 谓词中，如果值是’% xx’或’% xx%’，则通常情况下无法使用索引。

如果值是’xx%’，可以正常使用索引。那如果业务中必须要模糊匹配，而我们又不想引入其他组件呢，例如全文搜索引擎 ElasticSearch。

OR 操作符

在多个条件中使用 OR 操作符，对于优化器来说会比较困难，这种情况只有在多索引访问下才有可能消除全表扫描。

mysql> SELECT * FROM TABLE WHERE col_1 > 1 OR col_2 > 100;

在这个 SQL 语句中，由于 OR 操作符的存在，数据库不能通过单个索引直接获取到结果。
处理的办法有全索引扫描、全表扫描、多索引合并。

从 MySQL 5.6 开始已经支持了索引合并技术（index merge），因此 SQL 的性能在一定程度上可以接受。
索引合并就是分别对多个索引进行扫描，最后合并结果输出。MySQL 5.0 之前，一个表只能使用一个索引，无法同时使用多个索引分别进行条件扫描，在 MySQL 5.1 开始，引入了 index_merge 优化技术，对同一个表可以使用多个索引分别进行条件扫描。

1.OR 条件的两边都是同一个索引列的情况下，如果 WHERE 条件是主键，完全能使用索引。
2.OR 条件的两边都是同一个索引列的情况下，如果 WHERE 条件不是主键，则是否使用索引取决于 MySQL 查询优化器的代价估算。
3.OR 条件的两边是不同的索引列，是否使用索引也取决于 MySQL 查询优化器的代价估算。如果能使用索引，MySQL 会使用索引合并技术合并计算结果。如果代价太高仍然会走全表扫描。
4.如果多个 OR 条件中有其中一个条件没有索引，则必须进行全表扫描。

GROUP BY 子句

WHERE 条件中不包含索引列，但是 GROUP BY 子句的条件中包含索引列。会导致全表扫描。
Explain key字段显示使用了索引，那仅仅表明mysql使用这个索引做了聚合操作。

ORDER BY 子句

和group by字句类似，在 MySQL 查询优化器的代价估算模型中，ORDER BY 子句和前面的 GROUP BY 子句的代价，相对来说是比较高的。在使用中和 GROUP BY 子句一样，也是要注意防范 WHERE 条件上无索引导致的全表扫描性能低下。

复合索引

以上Group by 和 order by实际上只要给 SQL 语句中的 WHERE 子句和 ORDER BY/GROUP BY 子句加上一个复合索引就可以解决全表扫描的问题。

复合索引无法触发索引的几种情况：

1.没有使用到索引前缀
2.使用了复合索引中的全部列，但索引键不是 AND 操作

#先创建一个实验用的复合索引
mysql> create index idx_client_type_security_code on full_table_scan_test(client_type,security_code);

#没有使用到索引前缀的情况
mysql> explain select * from full_table_scan_test where security_code=‘xxx’;

#使用了索引中的全部列，但是用OR连接
mysql> explain select * from full_table_scan_test where client_type=1 or security_code=‘xxx’;
mysql> explain select * from full_table_scan_test where client_type=1 and security_code=‘xxx’;

问：如何解决 MySQL 错选索引的问题？

删除错选的索引，只留下对的索引；
使用 force index 指定索引；
修改 SQL 查询语句引导 MySQL 使用我们期望的索引，比如把 order by b limit 1 改为 order by b,a limit 1 语义是相同的，但 MySQL 查询的时候会考虑使用 a 键上的索引。

问：索引分别有哪些优点和缺点？

索引的优点如下：

快速访问数据表中的特定信息，提高检索速度。
创建唯一性索引，保证数据表中每一行数据的唯一性。
加速表与表之间的连接。
使用分组和排序进行数据检索时，可以显著减少查询中分组和排序的时间。

索引的缺点：

虽然提高了的查询速度，但却降低了更新表的速度，比如 update、insert，因为更新数据时，MySQL 不仅要更新数据，还要更新索引文件；

建立索引会占用磁盘文件的索引文件。

使用索引注意事项：

• 使用短索引，短索引不仅可以提高查询速度，更能节省磁盘空间和 I/O 操作；
• 索引列排序，MySQL 查询只使用一个索引，因此如果 where 子句中已经使用了索引的话，那么 order by 中的列是不会使用索引的，因此数据库默认排序可以符合要求的情况下，不要进行排序操作；
• 尽量不要包含多个列的排序，如果需要最好给这些列创建复合索引；
• like 语句操作，一般情况下不鼓励使用 like 操作，如果非使用不可， 注意 like “%aaa%” 不会使用索引，而like "aaa%"可以使用索引；
• 不要在列上进行运算；
• 不适用 NOT IN 和 <> 操作。

问：索引为什么会降低增删改速度

任何事情都有有利有弊，索引底层实现是B+树，B+树就是平衡树的一种。
平衡树它就是一颗空树或者说它左右两个子树的高度相差的绝对值不会超过1，并且左右两个子树都是一颗平衡二叉树。

如果一棵普通B树在极端情况下是有可能退化成链表的，这样所谓的查询提速也就不存在了。
B+树是一颗平衡树，如果我们对这棵树增删改的话，那肯定会破坏它的原有结构。
要维持平衡树，就必须做额外的工作。正因为这些额外的工作开销，导致索引会降低增删改的速度。

问：索引有几种类型？分别如何创建？

MySQL 的索引有两种分类方式：逻辑分类和物理分类。

按照逻辑分类，索引可分为：
主键索引：一张表只能有一个主键索引，不允许重复、不允许为 NULL；
唯一索引：数据列不允许重复，允许为 NULL 值，一张表可有多个唯一索引，但是一个唯一索引只能包含一列，比如身份证号码、卡号等都可以作为唯一索引；
普通索引：一张表可以创建多个普通索引，一个普通索引可以包含多个字段，允许数据重复，允许 NULL 值插入；
全文索引：让搜索关键词更高效的一种索引。

按照物理分类，索引可分为：
聚集索引：一般是表中的主键索引，如果表中没有显示指定主键，则会选择表中的第一个不允许为 NULL 的唯一索引，如果还是没有的话，就采用 Innodb 存储引擎为每行数据内置的 6 字节 ROWID 作为聚集索引。每张表只有一个聚集索引，因为聚集索引的键值的逻辑顺序决定了表中相应行的物理顺序。聚集索引在精确查找和范围查找方面有良好的性能表现（相比于普通索引和全表扫描），聚集索引就显得弥足珍贵，聚集索引选择还是要慎重的（一般不会让没有语义的自增 id 充当聚集索引）；

非聚集索引：该索引中索引的逻辑顺序与磁盘上行的物理存储顺序不同（非主键的那一列），一个表中可以拥有多个非聚集索引。

各种索引的创建脚本如下：
– 创建主键索引
alter table t add primary key add (id);
– 创建唯一索引
alter table t add unique (字段);
– 创建普通索引
alter table t add index 索引名称 (字段);
– 创建全文索引
alter table t add fulltext (字段);
– 删除索引
ALTER TABLE test.user DROP INDEX 索引名称;

问：MySQL 5.7.18 之后版本的 count(*) 有哪些特点

从 MySQL 5.7.18 开始，通过遍历最小的可用二级索引来处理 count(*) 语句，如果不存在二级索引，则扫描聚簇索引。原因是：InnoDB 二级索引树的叶子节点上存放的是主键，而主键索引树的叶子节点上存放的是整行数据，所以二级索引树比主键索引树小。因此优化器基于成本的考虑，优先选择的是二级索引。

问：主索引和唯一索引有什么区别？

主索引不能重复且不能为空，唯一索引不能重复，但可以为空；
一张表只能有一个主索引，但可以有多个唯一索引；
主索引的查询性能要高于唯一索引。

问：在 InnDB 中主键索引为什么比普通索引的查询性能高？

因为普通索引的查询会多执行一次检索操作。比如主键查询 select * from t where id=10 只需要搜索 id 的这棵 B+ 树，而普通索引查询 select * from t where f=3 会先查询 f 索引树，得到 id 的值之后再去搜索 id 的 B+ 树，因为多执行了一次检索，所以执行效率就比主键索引要低。

问：如何查询一张表的所有索引？

SHOW INDEX FROM T 查询表 T 所有索引。

问：MySQL 最多可以创建多少个索引列？

MySQL 中最多可以创建 16 个索引列。

问：如何让 like %abc 走索引查询？

我们知道如果要让 like 查询要走索引，查询字符不能以通配符（%）开始，如果要让 like %abc 也走索引，可以使用 REVERSE() 函数来创建一个函数索引，查询脚本如下：
select * from t where reverse(f) like reverse(’%abc’);

问：什么是联合索引？

联合索引也是一种辅助索引，联合索引又叫复合索引，是由多个字段组成的一个B+树。适合 where 条件中的多列组合，在某些场景可以避免回表。

遵循最左匹配原则

问：创建联合索引应该注意什么？

MySQL 中的联合索引，遵循最左匹配原则，比如，联合索引为 key(a,b,c)，则能触发索引的搜索组合是 a|ab|abc 这三种查询。所以在创建联合索引的时候选择性最大的列放在联合索引的最左边。

问：联合索引的作用是什么？

联合索引的作用如下：
用于多字段查询，比如，建了一个 key(a,b,c) 的联合索引，那么实际等于建了 key(a)、key(a,b)、key(a,b,c) 等三个索引，我们知道，每多一个索引，就会多一些写操作和占用磁盘空间的开销，尤其是对大数据量的表来说，这可以减少一部分不必要的开销；

覆盖索引，比如，对于联合索引 key(a,b,c) 来说，如果使用 SQL：select a,b,c from table where a=1 and b = 1 ，就可以直接通过遍历索引取得数据，而无需回表查询，这就减少了随机的 IO 操作，减少随机的 IO 操作，可以有效的提升数据库查询的性能，是非常重要的数据库优化手段之一；

索引列越多，通过索引筛选出的数据越少。

问：什么是最左匹配原则？它的生效原则有哪些？

最左匹配原则也叫最左前缀原则，是 MySQL 中的一个重要原则，说的是索引以最左边的为起点任何连续的索引都能匹配上，当遇到范围查询（>、<、between、like）就会停止匹配。 生效原则来看以下示例，比如表中有一个联合索引字段 index(a,b,c)：
where a=1 只使用了索引 a；
where a=1 and b=2 只使用了索引 a,b；
where a=1 and b=2 and c=3 使用a,b,c；
where b=1 or where c=1 不使用索引；
where a=1 and c=3 只使用了索引 a；
where a=3 and b like ‘xx%’ and c=3 只使用了索引 a,b。

问：列值为 NULL 时，查询会使用到索引吗？

在 MySQL 5.6 以上的 InnoDB 存储引擎会正常触发索引。但为了兼容低版本的 MySQL 和兼容其他数据库存储引擎，不建议使用 NULL 值来存储和查询数据，建议设置列为 NOT NULL，并设置一个默认值，比如 0 和空字符串等，如果是 datetime 类型，可以设置成 1970-01-01 00:00:00 这样的特殊值。

以下语句会走索引么？
select * from t where year(date)>2018;
不会，因为在索引列上涉及到了运算。

问：能否给手机号的前 6 位创建索引？如何创建？

可以，创建方式有两种：
alter table t add index index_phone(phone(6));
create index index_phone on t(phone(6));

问：什么是前缀索引？

前缀索引也叫局部索引，比如给身份证的前 10 位添加索引，类似这种给某列部分信息添加索引的方式叫做前缀索引。

问：为什么要用前缀索引？

前缀索引能有效减小索引文件的大小，让每个索引页可以保存更多的索引值，从而提高了索引查询的速度。但前缀索引也有它的缺点，不能在 order by 或者 group by 中触发前缀索引，也不能把它们用于覆盖索引。

问：什么情况下适合使用前缀索引？

当字符串本身可能比较长，而且前几个字符就开始不相同，适合使用前缀索引；相反情况下不适合使用前缀索引，比如，整个字段的长度为 20，索引选择性为 0.9，而我们对前 10 个字符建立前缀索引其选择性也只有 0.5，那么我们需要继续加大前缀字符的长度，但是这个时候前缀索引的优势已经不明显，就没有创建前缀索引的必要了。

问：什么是页？

页是计算机管理存储器的逻辑块，硬件及操作系统往往将主存和磁盘存储区分割为连续的大小相等的块，每个存储块称为一页。主存和磁盘以页为单位交换数据。数据库系统的设计者巧妙利用了磁盘预读原理，将一个节点的大小设为等于一个页，这样每个节点只需要一次磁盘 IO 就可以完全载入。

问：索引的常见存储算法有哪些？

哈希存储法：以 key、value 方式存储，把值存入数组中使用哈希值确认数据的位置，如果发生哈希冲突，使用链表存储数据；
有序数组存储法：按顺序存储，优点是可以使用二分法快速找到数据，缺点是更新效率，适合静态数据存储；
搜索树：以树的方式进行存储，查询性能好，更新速度快。

问：InnoDB 为什么不用 B 树、Hash、红黑树或二叉树建立索引？

因为 B 树、Hash、红黑树或二叉树存在以下问题：
B 树：不管叶子节点还是非叶子节点，都会保存数据，这样导致在非叶子节点中能保存的指针数量变少（有些资料也称为扇出），指针少的情况下要保存大量数据，只能增加树的高度，导致IO操作变多，查询性能变低；
Hash：虽然可以快速定位，但是没有顺序，IO 复杂度高；
二叉树：树的高度不均匀，不能自平衡，查找效率跟数据有关（树的高度），并且 IO 代价高；
红黑树：树的高度随着数据量增加而增加，IO 代价高。

问：为什么 InnoDB 要使用 B+ 树来存储索引？

B+Tree 中的 B 是 Balance，是平衡的意思，它在经典 B Tree 的基础上进行了优化，增加了顺序访问指针，在B+Tree 的每个叶子节点增加一个指向相邻叶子节点的指针，就形成了带有顺序访问指针的 B+Tree，这样就提高了区间访问性能：如果要查询 key 为从 18 到 49 的所有数据记录，当找到 18 后，只需顺着节点和指针顺序遍历就可以一次性访问到所有数据节点，极大提到了区间查询效率（无需返回上层父节点重复遍历查找减少 IO 操作）。

索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储的磁盘上，这样的话，索引查找过程中就要产生磁盘 IO 消耗，相对于内存存取，IO 存取的消耗要高几个数量级，所以索引的结构组织要尽量减少查找过程中磁盘 IO 的存取次数，从而提升索引效率。 综合所述，InnDB 只有采取 B+ 树的数据结构存储索引，才能提供数据库整体的操作性能。

问：唯一索引和普通索引哪个性能更好？

对于查询操作来说：普通索引和唯一索引的性能相近，都是从索引树中进行查询；
对于更新操作来说：唯一索引要比普通索引执行的慢，因为唯一索引需要先将数据读取到内存中，再在内存中进行数据的唯一效验，所以执行起来要比普通索引更慢。

问：优化器选择查询索引的影响因素有哪些？

优化器的目的是使用最小的代价选择最优的执行方案，影响优化器选择索引的因素如下：
扫描行数，扫描的行数越少，执行代价就越少，执行效率就会越高；
是否使用了临时表；
是否排序。

问：MySQL 是如何判断索引扫描行数的多少？

MySQL 的扫描行数是通过索引统计列（cardinality）大致得到并且判断的，而索引统计列（cardinality）可以通过查询命令 show index 得到，索引扫描行数的多少就是通过这个值进行判断的。

问：MySQL 是如何得到索引基数的？它准确吗？

MySQL 的索引基数并不准确，因为 MySQL 的索引基数是通过采样统计得到的，比如 InnoDb 默认会有 N 个数据页，采样统计会统计这些页面上的不同值得到一个平均值，然后除以这个索引的页面数就得到了这个索引基数。

问：MySQL 如何指定查询的索引？

在 MySQL 中可以使用 force index 强行选择一个索引，具体查询语句如下：
select * from t force index(index_t)

问：以下 or 查询有什么问题吗？该如何优化？

select * from t where num=10 or num=20;
答：如果使用 or 查询会使 MySQL 放弃索引而全表扫描，可以改为：
select * from t where num=10 union select * from t where num=20;

问：以下查询要如何优化？

表中包含索引：
KEY mid (mid)
KEY begintime (begintime)
KEY dg (day,group)

使用以下 SQL 进行查询：
select f from t where day=‘2010-12-31’ and group=18 and begintime<‘2019-12-31 12:14:28’ order by begintime limit 1;

答：此查询理论上是使用 dg 索引效率更高，通过 explain 可以对比查询扫描次数。由于使用了 order by begintime 则使查询放弃了 dg 索引，而使用 begintime 索引，从侧面印证 order by 关键字会影响查询使用索引，这时可以使查询强制使用索引，改为以下SQL：

select f from t use index(dg) where day=‘2010-12-31’ and group=18 and begintime< ‘2019-12-31 12:14:28’ order by begintime limit 1;

问：如何优化身份证的索引？

在中国因为前 6 位代表的是地区，所以很多人的前六位都是相同的，如果我们使用前缀索引为 6 位的话，性能提升也并不是很明显，但如果设置的位数过长，那么占用的磁盘空间也越大，数据页能放下的索引值就越少，搜索效率也越低。针对这种情况优化方案有以下两种：
使用身份证倒序存储，这样设置前六位的意义就很大了；
使用 hash 值，新创建一个字段用于存储身份证的 hash 值。

问：在 MySQL 中指定了查询索引，为什么没有生效？

我们知道在 MySQL 中使用 force index 可以指定查询的索引，但并不是一定会生效，原因是 MySQL 会根据优化器自己选择索引，如果 force index 指定的索引出现在候选索引上，这个时候 MySQL 不会在判断扫描的行数的多少直接使用指定的索引，如果没在候选索引中，即使 force index 指定了索引也是不会生效的。

问：为什么 MySQL 官方建议使用自增主键作为表的主键？

因为自增主键是连续的，在插入过程中尽量减少页分裂，即使要进行页分裂，也只会分裂很少一部分；并且自增主键也能减少数据的移动，每次插入都是插入到最后，所以自增主键作为表的主键，对于表的操作来说性能是最高的。

问：自增主键有哪些优缺点?

优点：
数据存储空间很小；
性能最好；
减少页分裂。

缺点：
数据量过大，可能会超出自增长取值范围；
无法满足分布式存储，分库分表的情况下无法合并表；
主键有自增规律，容易被破解；

综上所述： 是否需要使用自增主键，需要根据自己的业务场景来设计。如果是单表单库，则优先考虑自增主键，如果是分布式存储，分库分表，则需要考虑数据合并的业务场景来做数据库设计方案。

问：可以说说聚集索引和非聚集索引区别吗？

在 MySQL 中，有两大常用的存储引擎 MyISAM 和 InnoDB，MyISAM 使用的是非聚集索引也叫非聚簇索引，InnoDB 使用的是聚集索引，聚集索引就是以主键创建的索引，非聚集索引就是除了主键以外的索引。非聚集索引在建立的时候也未必是单列的，可以多个列来创建索引，本质区别是表记录的排列顺序和索引的排列顺序是否一致。

聚簇索引的叶节点就是数据节点，而非聚簇索引的叶节点仍然是索引节点，并保留一个链接指向对应数据块。

聚簇索引主键的插入速度要比非聚簇索引主键的插入速度慢很多。
相比之下，聚簇索引适合排序，非聚簇索引不适合用在排序的场合。因为聚簇索引叶节点本身就是索引和数据按相同顺序放置在一起，索引序即是数据序，数据序即是索引序，所以很快。

非聚簇索引叶节点是保留了一个指向数据的指针，索引本身当然是排序的，但是数据并未排序，数据查询的时候需要消耗额外更多的I/O，所以较慢。
当你需要取出一定范围内的数据时，用聚簇索引也比用非聚簇索引好。

问：聚集索引有什么缺点？

插入速度严重依赖于插入顺序，按照主键的顺序插入是加载数据到 InnoDB 表中速度最快的方式，但如果不是按照主键顺序加载数据，那么在加载完成后最好使用 optimize table 命令重新组织一下表。

更新聚集索引列的代价很高，因为会强制 InnoDB 将每个被更新的行移动到新的位置。

基于聚集索引的表在插入新行，或者主键被更新导致需要移动行的时候，可能面临页分裂的问题，当行的主键值要求必须将这一行插入到某个已满的页中时，存储引擎会将该页分裂成两个页面来容纳该行，这就是一次页分裂操作，页分裂会导致表占用更多的磁盘空间。

问：使用聚集索引为什么查询速度会变快？

聚簇索引对于那些经常要搜索范围值的列特别有效。使用聚簇索引找到包含第一个值的行后，便可以确保包含后续索引值的行在物理相邻。

数据存储结构方面：聚簇索引的索引页面指针指向数据页面，所以使用聚簇索引查找数据总是比使用非聚簇索引快。每张表只能建一个聚簇索引，并且建聚簇索引需要至少相当该表 120% 的附加空间，以存放该表的副本和索引中间页。

数据检索方面：在那些包含范围检查（between 、< 、<= 、> 、>=）或使用 group by 或 order by 的查询时，一旦找到具有范围中第一个键值的行，具有后续索引值的行保证物理上毗连在一起而不必进一步搜索，避免了大范围扫描，可以大大提高查询速度。

同样，如果对从表中检索的数据进行排序时经常要用到某一列，则可以将该表在该列上聚簇（ 物理排序 ），避免每次查询该列时都进行排序，从而节省成本。

问：建立聚集索引有什么需要注意的地方吗？

在聚簇索引中不要包含经常修改的列，因为码值修改后，数据行必须移动到新的位置，索引此时会重排，会造成很大的资源浪费。
选择聚簇索引应基于 where 子句和连接操作的类型。

问：为什么非聚集索引会降低插入和更新速度？

每当你改变了一个建立了非聚簇索引的表中的数据时，必须同时更新索引。因此你对一个表建立非聚簇索引时要慎重考虑。如果你预计一个表需要频繁地更新数据，那么不要对它建立太多非聚簇索引。

另外，如果硬盘和内存空间有限，也应该限制使用非聚簇索引的数量。

问：InnoDB 表中为什么只能有一个聚集索引？

每个表只能有一个聚簇索引，是因为一个表中的记录只能以一种物理顺序存放，实际的数据页只能按照一颗 B+ Tree 进行排序。

查询优化器倾向于采用聚集索引，因为聚集索引能够在 B+ Tree 索引的叶子节点上直接找到数据。聚簇索引类似于电话簿，后者按姓氏排列数据。由于聚簇索引规定数据在表中的物理存储顺序，因此一个表只能包含一个聚簇索引（ TokuDB 引擎除外 ）。

汉语字典也是聚簇索引的典型应用，在汉语字典里，索引项是字母+声调，字典正文也是按照先字母再声调的顺序排列。

问：表中最多可以有多少个非聚集索引？

一个表可以有不止一个非聚簇索引。实际上，对每个表你最多可以建立 249 个非聚簇索引。非聚簇索引需要大量的硬盘空间和内存。