面试问题
- 什么是聚集索引和非聚集索引
- MVCC的理解
- 日常工作中是怎么优化SQL?
- Mysql为什么使用B+Tree作为索引结构
- Mysql索引的优点和缺点？
- 索引什么时候失效？
- InnoDB 与MyISAM有什么区别
- 为什么 SQL 语句不要过多的 join？
一、什么是索引
官方定义:索引是帮助MySQL高效获取数据的数据结构，可以理解为“排好序的可以快速查找数据的数据结构”。
二、索引优缺点
1、优点
提高数据检索的效率，降低数据库的Io成本。
通过索引对数据进行排序，降低数据排序的成本，降低了CPU的消耗。
2、缺点
数据的增加、修改、删除，需要涉及到索引的维护，当数据量较大的情况下，索引的维护会带来较大的性能开销。
一个表中允许存在一个聚簇索引和多个非聚簇索引，但是索引数不能创建太多，否则造成的索引维护成本过高。
创建索引的时候，需要考虑到索引字段值的分散性，如果字段的重复数据过多，创建索引反而会带来性能降低。
三、索引数据结构
1、⼆叉树
将数据按照顺序⼆叉树排列好每次查询就可以使⽤⼆分法即从根节点出发，查询效率就增加了
但是如果删除了⼀些数据⽐如0，6，1，3，4删除了那么右边的数据就有变成了线性的了，查询效率就会有所浪 费
2、平衡二叉树
如果有这样⼀棵树左右⼦树⾼度差不超过 1，⽽且也满⾜顺序排列就可以继续⾼效下去，然后平衡⼆叉树就出现 了。每当删除⼀个节点都应该发⽣相应的节点位置转换反转保证⼆叉树的平衡。
说了这么多我们⽆⾮就是想让查找效率变高，从线性的 O（n） 到 O (logn），好像还有疑问为什么不⽤更⾼效的 Hash 地址法来查找呢？这样可以降到 O（1），答案是在查询过程中我们不仅有等值查询还有范围查询 模糊查 询，使⽤ Hash 存储其位置的不确定性，如果要查询 范围我们就要遍历全表。⽽⼆叉树只要遍历左右节点。
3、B树
由于平衡⼆叉树的⼆叉特点，它每⼀个节点最多只有 2 个叉，假设有 100000 个数据，那么树的深度将会变得特别 深，⽽每次⽐较就是拿⽐较的树和节点上的数在内存⽐较，所以每⽐较⼀次就是⼀次 IO 操作就下降⼀层，层数越 多时间就越久。所以B树就来了，他是多叉平衡树，每个节点维护了多个⽐较范围（即⼦节点）
这样就降低了⾼度，每个圆圈可以理解为⼀⻚，16kb的数据. 所以他的每个节点都存储数据就会造成每个结点的分 叉数减少，⽽且会造成先靠近根节点的先查到，靠近叶⼦结点的后查到。同样范围查找也会出现多次回退到⽗节点 在到另⼀个兄弟节点的低效率问题。
4、B+树
我们改造⼀下B树为 B + 树 ，每个⾮叶⼦节点只存索引，真实数据都存在叶⼦节点，这样⾮叶⼦节点的空间单个数据空间减少数量即分叉就可以增⼤。每次查询⽆论如何必须遍历到叶⼦节点才会结束，这样深度⼜减少了，同时我们把每个叶⼦结点⽤双向链表连接起来，范围查询就更快。
四、索引分类
索引分为聚集索引和非聚集索引，聚集索引就是基于主键创建的索引，除了主键索引以外的其他索引，称为非聚集索引，也叫做二级索引。
聚集索引并不仅仅是一种索引类型，还代表着一种数据的存储方式。
1、聚集索引
1.1、主键索引
表中的列设定为主键后，数据库会自动建立主键索引。InnoDB里面只能存在一个聚集索引。
2、非聚集索引
2.1、 唯一索引
表中的列创建了唯一约束时，数据库会自动建立唯一索引。
2.2、单值索引
即一个索引只包含单个列，一个表可以有多个单值索引。
2.3、复合索引
即一个索引包含多个列
2.4、函数索引（MySql8以上）
五、性能分析
使用EXPLAIN关键字可以模拟优化器执行SQL查询语句,从而知道MYSQL是如何处理SQL语句的,可以用来分析查询语句或是表的结构的性能瓶颈。
通过性能分析，可以知道：
- 表的读取顺序
- 哪些索引能够使用
- 数据读取操作的操作类型
- 哪些索引被实际使用
- 表之间的引用
- 每张表有多少行被优化器查询
1、id
- id相同时，执行顺序是从上往下
- id不同时，id的序号会递增，id的值越大优先级越高，则先被执行
- id相同和不同都存在时，id相同的可以理解为一组，从上往下执行，所有组中，id值越大，优先级越高
2、select_type
- SIMPLE :简单的select查询，查询中不包含子查询或者UNION
- PRIMARY：查询中若包含任何复杂的子部分，最外层的查询则会被标记为PRIMARY
- DERIVED：在FROM列表中包含的子查询表被标记为DERIVED（派生表），MySQL会递归执行这些子查询，把结果放到临时表中
- SUBQUERY：在SELECT或者WHERE列表中包含了子查询
- UNION：union 操作中第二个及之后的查询
- MATERIALIZED：物化查询视图
- DEPENDENT SUBQUERY：子查询中的第一个查询，并且该查询依赖于外部查询。
3、table
查询⽤到的表名
4、Type
★连接的类型，常⻅的类型有（性能从好到差）,一般来说，得保证查询至少达到 range 级别，最好能达到 ref。
- system：存储引擎能够直接知道表的⾏数（如 MyISAM）并且只有⼀⾏数据
- const：通过索引⼀次找到，通常在⽤主键或唯⼀索引时出现
- eq_ref：⽤主键或唯⼀索引字段作为连接表条件
- ref：⽤普通索引的字段作为连接表条件
- fulltext:
- ref_or_null：
- index_merge ：
- unique_subquery ：
- index_subquery ：
- range：对索引列进⾏范围查询
- index：利⽤索引扫描全表
- all：全表扫描
5、possible_keys
显示可能应用在这张表的索引，一个或者多个，查询涉及到的字段上如果存在索引，则该索引将会被列出来，但不一定会用到，因为存在索引失效的情况
6、key
★查询中实际使用的索引，如果为null，则表示没有使用索引
7、ref
当使⽤索引等值查询时，与索引作⽐较的列或常量
8、key_len
索引长度
9、row
★预计扫描的⾏数，值越⼤，查询性能越差
10、Extra
★：有关查询执⾏的其他信息
- using index：使⽤覆盖索引，不⽤回表查询
- using where：使⽤ where ⼦句来过滤结果集
- using temporary：使⽤临时表来存储中间结果，可能会导致性能问题
- using filesort：查询需要进⾏⽂件排序操作，可能会导致性能问题
- using index condition：先根据能⽤索引的条件获取符合条件的数据⾏，然后在根据其他条件去过滤数据
- Using join buffer (Block Nested Loop)：使用联接缓冲区（块嵌套循环）
六、索引失效
1、函数失效
在索引列上做运算，比如使用函数，Mysql在生成执行计划的时候，它是根据统计信息来判断是否要使用索引的。 而在索引列上加函数运算，导致Mysql无法识别索引列，也就不会再走索引了。（不过从Mysql8开始，增加了函数索引可以解决这个问题。）
2、最左匹配法则失效
在一个由多列构成的组合索引中，需要按照最左匹配法则，也就是从索引的最左列开始顺序检索，否则不会走索引。在组合索引中，索引的存储结构是按照索引列的顺序来存储的，因此在sql中也需要按照这个顺序才能进行逐一匹配。否则InnoDB无法识别索引导致索引失效。
3、隐式转化失效
当索引列存在隐式转化的时候， 比如索引列是字符串类型，但是在sql查询中没有使用引号。那么Mysql会自动进行类型转化，从而导致索引失效
4、不等于失效
在索引列使用不等于号、not查询的时候，由于索引数据的检索效率非常低，因此Mysql引擎会判断不走索引。
5、like失效
使用like通配符匹配后缀%xxx的时候，由于这种方式不符合索引的最左匹配原则，所以也不会走索引。但是反过来，如果通配符匹配的是前缀xxx%，符合最左匹配，也会走索引。
6、or失效
使用or连接查询的时候，or语句前后没有同时使用索引，那么索引会失效。只有or左右查询字段都是索引列的时候，才会生效。
7、多表连接失效
对于多表连接查询的场景中，连接顺序也会影响索引的使用。
8、字符集差异失效
对于多表联合查询的场景中，A表字段1和B表字段1字符集和排序规则存在差异，也会导致索引失效。
9、效率相仿失效
对于多表联合查询的场景中，如A表（m条）inner join B表（n条）、on条件A表非主键字段=B表主键字段，此时数据库会查询数据m*n次，索引没有生效，此时可以使用exists或in来进行关联，确保主表索引能够生效。
exists 不同环境的优化策略不一样，有的是MATERIALIZED，有的是DEPENDENT SUBQUERY，DEPENDENT SUBQUERY非常慢.
还没有弄懂为什么，哭了，有没有大神救救俺
10万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
