9、什么是回表（使用索引查询完整数据过程）？
当我们需要查询一条完整的数据的时候：
- 如果是通过聚簇索引来查询数据，例如 select * from user where id=100，那么此时只需要搜索聚簇索引的 B+Tree 就可以找到数据。
- 如果是通过非聚簇索引来查询数据，例如 select * from user where username=zhangsan'，那么此时需要先搜索 username 这一列索引的 B+树，搜索完成后得到主键的值，然后再去搜索聚簇索引的 B+树，就可以获取到一行完整的数据。
对于第二种查询方式而言，一共搜索了两棵 B+树，第一次搜索 B+树 拿到主键值后再去搜索聚簇索引的 B+树，这个过程就是所谓的回表。
10、回表的代价？
回表的代价：
- 需要回表的记录越多，使用二级索引的性能就越低，甚至让某些查询宁愿使用全表扫描也不使用二级索引 。
- 比方说 name 值在 Asa ～ Barlow 之间的用户记录数量占全部记录数量90%以上，那么如果使用 idx_name_birthday_phone_number 索引的话，有90%多的 id 值需要回表，这不是吃力不讨好么，还不如直接去扫描聚簇索引（也就是全表扫描）。
那什么时候采用全表扫描的方式，什么时候使用采用 二级索引 + 回表 的方式去执行查询呢？
- 这个就是查询优化器做的工作，查询优化器会事先对表中的记录计算一些统计数据，然后再利用这些统计数据根据查询的 条件来计算一下需要回表的记录数，需要回表的记录数越多，就越倾向于使用全表扫描，反之倾向于使用 二级索引 + 回表 的方式。
11、非聚簇索引一定会回表查询吗？
不一定：
- 这涉及到查询语句所要求的字段是否全部命中了索引，如果全部命中了索引，那么就不必再进行回表查询。一个索引包含所有需要查询字段的值，被称之为"覆盖索引"。
- 举个简单的例子，假设我们在学生表的成绩上建立了索引，那么当进行
select score from student where score > 90的查询时，在索引的叶子节点上，已经包含了score 信息，不会再次进行回表查询。
12、联合索引是什么？为什么需要注意联合索引中的顺序？
MySQL可以使用多个字段同时建立一个索引，叫做联合索引。在联合索引中，如果想要命中索引，需要按照建立索引时的字段顺序挨个使用，否则无法命中索引。
具体原因为:
- MySQL使用索引时需要索引有序，假设现在建立了"name，age，school"的联合索引，那么索引的排序为: 先按照name排序，如果name相同，则按照age排序，如果age的值也相等，则按照school进行排序。
- 当进行查询时，此时索引仅仅按照name严格有序，因此必须首先使用name字段进行等值查询，之后对于匹配到的列而言，其按照age字段严格有序，此时可以使用age字段用做索引查找，以此类推。因此在建立联合索引的时候应该注意索引列的顺序，一般情况下，将查询需求频繁或者字段选择性高的列放在前面。此外可以根据特例的查询或者表结构进行单独的调整。
13、创建索引方式？⚡
创建索引有三种方式：
- 使用CREATE INDEX命令创建；
CREATE [UNIQUE|FULLTEXT] INDEX 索引名称 [USING 索引类型] ON 表名(列名...); -- 索引类型默认是 B+TREE
- 使用ALTER TABLE命令去增加索引；
ALTER TABLE table_name ADD INDEX index_name (column_list);
- 在执行CREATE TABLE时创建索引 。
CREATE TABLEtable_name( c1 INT PRIMARY KEY, c2 INT NOT NULL, c3 INT NOT NULL, c4 VARCHAR(10), INDEX (c2,c3) -- 创建两列c2和c3组成的索引。 );
14、创建索引的设计原则有哪些？
创建索引时的原则：
- 只为用于搜索、排序或分组的列创建索引：
- 只为出现在 WHERE 子句中的列、连接子句中的连接列，或者出现在 ORDER BY 或 GROUP BY 子句中的列创建索引。而出现在查询列表中的列就没必要建立索引了。
- 为列的基数（区分度）大的列创建索引：
- 列的基数指的是某一列中不重复数据的个数，这个指标非常重要，直接影响我们是否能有效的利用索引。
- 假设某个列的基数为 1 ，也就是所有记录在该列中的值都一样，那为该列建立索引是没有用的，因为所有值都一样就无法排序，无法进行快速查找。
- 索引列的类型尽量小：
- 数据类型越小，在查询时进行的比较操作越快（这是CPU层次的东东）
- 数据类型越小，索引占用的存储空间就越少，在一个数据页内就可以放下更多的记录，从而减少磁盘 I/O 带来的性能损耗，也就意味着可以把更多的数据页缓存在内存中，从而加快读写效率。
- 可以只对字符串值的前缀建立索引：
- 对一个完整字符串建立索引时，字符串越长，在索引中占用的存储空间越大。
- 可以只对字符串的前几个字符进行索引，既节约空间，又减少了字符串的比较时间，还大概能解决排序的问题。
- 让索引列在比较表达式中单独出现 ：
- 如果索引列在比较表达式中不是以单独列的形式出现，而是以某个表达式，或者函数调用形式出现的话，是用不到索引的。
- 使用短索引：
- 在给定大小的存储块内可以存储更多的索引值，相应的可以有效的提升 MySQL 访问索引的 I/O 效率。
- 让主键拥有 AUTO_INCREMENT 属性
- 让主键具有 AUTO_INCREMENT ，让存储引擎自己为表生成主键，而不是我们手动插入，尽可能的减少页面分裂和记录移位。
- 尽量使用覆盖索引进行查询，避免回表带来的性能损耗。（一个索引包含（覆盖）所有需要查询字段的值，被称之为"覆盖索引"）
- 其他：
- 对于插入、更新、删除等 DML 操作比较频繁的表，不适合建立索引
- 尽量的扩展索引，不要新建索引。比如表中已经有a的索引，现在要加(a,b)的索引，那么只需要修改原来的索引即可。
15、有哪些索引失效场景？⚡
- 模糊搜索导致的索引失效
- 当
%放在匹配字段前是不走索引的，放在后面才会走索引。
- OR引起的索引失效
or语句前后没有同时使用索引，或者or前后连接的不是同一个字段。
- 运算符导致的索引失效
- 如果你对列进行了（+，-，*，/，!）, 那么都将不会走索引。
- 在索引字段上使用not，<>， ! = (这样处理的是全表扫描)
- 类型不一致导致的索引失效
- 比如列类型是字符串，一定要在条件中将数据用引号引用，否则失效（隐式转换类型)
- 函数导致的索引失效
- 如果使用函数在索引列，这是不走索引的。
- NOT IN、NOT EXISTS、 in 、exists 导致索引失效
16、最左匹配原则？
概念：
- 所谓最左原则指的就是如果你的 SQL 语句中用到了联合索引中的最左边的索引，那么这条 SQL 语句就可以利用这个联合索引去进行匹配，值得注意的是，当遇到范围查询(>、<、between、like)就会停止匹配。
- 即最左优先，从左往右匹配，直到遇到范围查询，比如建立联合索引 (a,b,c)，实际上是创建了三个索引，分别是(a)、(a,b)、(a,b,c)。索引是先根据a排序，a相同时b有序，a不同无序，以此类推，直到遇到范围查询(>、<、between、like)就会停止。
最左匹配的原理
假设，我们对(a,b)字段建立联合索引，那么如下图所示：
按照a来进行排序，在a相等的情况下，才按b来排序，因此a【2,4,4,7,8】是有序的，而b【4,1,3,6,7】是全局无序，而局部相对有序状态。
因此，当执行a = 1 and b = 2是a,b字段能用到索引的。而你执行a > 1 and b = 2时，a字段能用到索引，b字段用不到索引。因为a的值此时是一个范围，不是固定的，在这个范围内b值不是有序的，因此b字段用不上索引。
综上所示，最左匹配原则，在遇到范围查询的时候，就会停止匹配。
经典题型：
题型一：
如果sql为：
SELECT * FROM table WHERE a = 1 and b = 2 and c = 3;
如何建立索引？
正确回答：
- (a,b,c)或者(c,b,a)或者(b,a,c)都可以，重点要的是将区分度高的字段放在前面，区分度低的字段放后面。像性别、状态这种字段区分度就很低，我们一般放后面。
- 假设区分度由大到小为b,a,c。那么我们就对(b,a,c)建立索引。如果我们的搜索条件中的列和索引列一致的话，这种情况就称为全值匹配，用到索引，与顺序无关，在执行sql的时候，查询优化器会自动帮我们优化查询顺序，让我们用上索引。
题型二：
如果sql为：
SELECT * FROM table WHERE a > 1 and b = 2;
如何建立索引？
正确回答：
- 对(b,a)建立索引。如果你建立的是(a,b)索引，那么只有a字段能用得上索引，毕竟最左匹配原则遇到范围查询就停止匹配。
- 如果对(b,a)建立索引那么两个字段都能用上，查询优化器会帮我们调整where后a,b的顺序，让我们用上索引。
题型三：
如果sql为：
SELECT * FROM `table` WHERE a > 1 and b = 2 and c > 3;
如何建立索引？
正确回答：
- 对(b,a)或者(b,c)都可以，要结合具体情况具体分析（如字段的区分度）。
题型四：
如果sql为：
SELECT * FROM `table` WHERE a = 1 ORDER BY b;
如何建立索引？
正确回答：
- 对(a,b)建索引，当a = 1的时候，b相对有序，可以避免再次排序！
- 分析：一条SQL实际上可以分为三步
- 第一步：根据where条件和统计信息生成执行计划，得到数据；
- 第二步：将得到的数据排序，当执行处理数据（order by）时，数据库会先查看第一步的执行计划，看order by 的字段是否在执行计划中利用了索引。如果是，则可以利用索引顺序而直接取得已经排好序的数据。如果不是，则排序操作。
- 第三步：返回排序后的数据。
题型五：
如果sql为：
SELECT * FROM `table` WHERE a > 1 ORDER BY b;
如何建立索引？
正确回答：
- 对(a)建立索引，因为a的值是一个范围，这个范围内b值是无序的，没有必要对(a,b)建立索引。
题型六：
如果sql为：
SELECT * FROM `table` WHERE a = 1 AND b > 3 ORDER BY c;
如何建立索引？
正确回答：
- 对(a,b)建立索引。
- 分析：
- 在where和order by出现索引设计冲突时，一般都是让where条件去使用索引来快速筛选出来一部分指定的数据，接着再进行排序；
- 因为大多数情况基于索引进行where筛选可以最快速度筛选出想要的少部分数据，然后做排序的成本可能会小很多。
拓展：
MySQL支持两种方式的排序Using index排序和Using filesort排序：
- Using index排序：指MySQL扫描索引本身完成排序索引，可以保证数据的有序性，因此不需要再进行排序；
- Using filesort排序：指文件排序，一般在内存中进行排序，占用CPU较多。如果待排结果较大，会产生临时文件I/O到磁盘进行排序，效率较低。
ORDER BY满足两种情况会使用Using index排序：
- order by语句使用索引最左前缀；
- 使用where子句与order by子句条件列组合满足索引最左前缀。
17、讲一讲前缀索引？
当要索引的字段很长时，索引就会变得会变大变慢，如果只把很长字段的前面的公共部分作为一个索引，就会节约索引空间，提高索引效率。
注意：使用前缀索引系统就会忽略覆盖索引对查询性能的优化了。
场景：存储身份证
- 直接创建完整索引，这样可能比较占用空间；
- 创建前缀索引，节省空间，但会增加查询扫描次数，并且不能使用覆盖索引；
- 倒序存储，再创建前缀索引，用于绕过字符串本身前缀的区分度不够的问题（前 6 位相同的很多）；
- 创建 hash 字段索引，查询性能稳定，有额外的存储和计算消耗，跟第三种方式一样，都不支持范围扫描。
18、了解索引下推优化不？
索引条件下推优化是 MySQL5.6 添加，可以在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。
索引下推充分利用了索引中的数据，在查询出整行数据之前过滤掉无效的数据，再去主键索引树上查找。
举个例子：
用户表 user，(name, age) 是联合索引：
SELECT * FROM user WHERE name LIKE '张%' AND age = 10; -- 头部模糊匹配会造成索引失效
- 优化前：在非主键索引树上找到满足第一个条件的行，然后通过叶子节点记录的主键值再回到主键索引树上查找到对应的行数据，再对比 AND 后的条件是否符合，符合返回数据，需要 4 次回表。
- 优化后：检查索引中存储的列信息是否符合索引条件，然后交由存储引擎用剩余的判断条件判断此行数据是否符合要求，不满足条件的不去读取表中的数据，满足下推条件的就根据主键值进行回表查询，2 次回表。
19、普通索引和唯一索引怎样选？
- 查询比较：
- 查询会以页为单位将数据页加载进内存，不需要一条记录一条记录读取磁盘。然后唯一索引根据条件查询到记录时就返回结果，普通索引查到第一条记录往后遍历直到不满足条件，由于都在内存中，不需要磁盘读取那么大开销，带来的额外查询开销忽略不计，所以查询性能几乎一致。
- 更新比较：
- 唯一索引由于更新时要检查唯一性，所以需要将数据页先加载进内存才能判断，此时直接操作内存，不需要操作change buffer。
- 普通索引若数据在内存中直接内存中更新，否则会将更新操作先记录到change buffer中，等下一次查询将数据读到内存中，再进行change buffer里相关更新操作后将数据返回（也就是将更新索引页推迟到了查询的时候），这样一来，再写多读少的情况下就减少了磁盘lO，若写完就马上查询，就不必用change buffer，不但没提高多少效率还造成维护change buffer额外消耗。
- change buffer 只限于用在普通索引的场景下，而不适用于唯一索引。
- 适合场景
- 写多读少，选用普通索引更好，可以利用change buffer进行性能优化减少磁盘lO，将更新操作记录到change bufer，等查询来了将数据读到内存再进行修改.
- 总结：
- 由于唯一索引用不上 change buffer 的优化机制，因此如果业务可以接受，从性能角度出发 建议优先考虑普通索引。
20、如何选择索引列的顺序？
建立索引的目的是：希望通过索引进行数据查找，减少随机 IO，增加查询性能 ，索引能过滤出越少的数据，则从磁盘中读入的数据也就越少。
- 区分度最高的放在联合索引的最左侧（区分度=列中不同值的数量/列的总行数）
- 尽量把字段长度小的列放在联合索引的最左侧（因为字段长度越小，一页能存储的数据量越大，IO 性能也就越好）
- 使用最频繁的列放到联合索引的左侧（这样可以比较少的建立一些索引）
21、覆盖索引是什么？
覆盖索引：
- 包含了所有查询字段 (where,select,order by,group by 包含的字段) 的索引。
- 通过索引值可以直接找到要查询字段的值，而不需要通过主键值回表查询。
优点：
- 避免 Innodb 表进行索引的二次查询: Innodb 是以聚集索引的顺序来存储的，对于 Innodb 来说，非聚簇索引在叶子节点中所保存的是行的主键信息，如果是用非聚簇索引查询数据的话，在查找到相应的键值后，还要通过主键进行二次查询才能获取我们真实所需要的数据。而在覆盖索引中，非聚簇索引的键值中可以获取所有的数据，避免了对主键的二次查询 ，减少了 IO 操作，提升了查询效率。
- 可以把随机 IO 变成顺序 IO 加快查询效率: 由于覆盖索引是按键值的顺序存储的，对于 IO 密集型的范围查找来说，对比随机从磁盘读取每一行的数据 IO 要少的多，因此利用覆盖索引在访问时也可以把磁盘的随机读取的 IO 转变成索引查找的顺序 IO。
22、百万级以上的数据如何删除？
关于索引：
由于索引需要额外的维护成本，因为索引文件是单独存在的文件，所以当我们对数据的增加，修改，删除，都会产生额外的对索引文件的操作，这些操作需要消耗额外的IO，会降低增/改/删的执行效率。
- 所以我们想要删除百万数据的时候可以先删除索引；
- 然后删除其中无用数据 ；
- 删除完成后重新创建索引创建索引也非常快
23、索引区分度？
索引区分度：
- 索引的区分度等于列中不同值的数量/列的总行数，表示字段不重复的比例。 MySQL的索引都是排好序的，索引区分度越高，则排序越快，区分度越低，则排序越慢。
索引性能分析：
- 对于选择性不高的列甚至可以不创建索引。如果选择性不高，极端性情况下可能会扫描全部或者大多数索引，然后再回表，这样反而增加了IO的消耗，这个过程可能不如直接走主键索引性能高。
- 唯一键的区分度是1，而对于一些状态值，性别等字段区分度往往比较低，在数据量比较大的情况下，甚至有无限接近0。
- 由于索引也是需要占用内存的，所以在内存较为有限的环境下或者为了提高性能的情况下，区分度不高的索引几乎没有意义。
索引查看命令：
-- 选择性查看索引区分度 select count(DISTINCT column_name) / COUNT(*) from table_name; -- 查看索引区分度，我们关注Cardinality(基数)列 SHOW INDEX FROM table_name;
