mysql深入理解（一）

1.聚簇索引和非聚簇索引的区别

聚簇索引：叶子节点中将数据存储和索引放到了一块，找到了索引也就找到了数据，数据的物理存放顺序和索引顺序是一致的（只要索引是相邻的，那么数据也一定是相邻的存放在磁盘上的）。
例：如要找5-7的索引位置，那么也就找到了5-7的数据位置（有利于范围查询）

非聚簇索引（二级索引）：数据是数据，索引是索引，叶子节点不存储数据，在innodb中存储的是主键值。

优势：
1.查询通过聚簇索引就可以直接获取数据，而非聚簇索引需要二次查询才能获取数据
2.聚簇索引适合于范围查询和排序的场合
劣势：
1.维护索引很昂贵，当插入操作造成页溢出的话，后面上的数据要移动到新页里面去，效率很低
2.如果主键比较大的话那么辅助索引将会变得更大，因为辅助索引的叶子节点上存储的是主键值，如果主键过长，会导致非叶子节点占用更多的物理空间
误区：
1.聚簇索引一定是主键吗：聚簇索引默认是主键，如果表中没有定义主键，InnoDB 会选择一个唯一的非空索引代替。如果没有这样的索引，InnoDB 会隐式定义一个主键row-id(6字节)来作为聚簇索引。
2.非聚簇索引一定会回表查询吗：这涉及到查询语句所要求的字段全部命中了索引，那么就不必再进行回表查询。
例：假设在员工表的age上建立了索引，那么当进行select age from employee where age < 20的查询时，在索引的叶子节点上，已经包含了age信息，不会再次进行回表查询。

2.InnoDB为什么采⽤B+树索引

二叉树：
如果索引都是顺序增长的，磁盘io次数取决于树的高度，想减少磁盘IO的次数就必须要压缩树的高度，让瘦高的树尽量变成矮胖的树。
红黑树：
虽然避免了二叉树单边增长的情况，它会做自动平衡，但红黑树和二叉树有相同缺点，每个节点存储一个关键词，数据量大时，导致红黑树的深度很深，mysql每次读取时消耗大量io
HASH：
通过hash函数算出来的值指向地址，仅通过一次运算就能精准的定位到磁盘的某个地址，与磁盘做1次io就能找到数据，但不能解决查范围的问题，因为hash结构，每个键只对应一个值，而且是散列的方式分布的
B树：
有序数组+平衡多叉树，比如说，我们要查找关键字范围在3到7的关键字，在找到第一个符合条件的数字3后，访问完第一个关键字所在的块后，得遍历这个B树，获取下一个块，直到遇到一个不符合条件的关键字，遍历的过程是比较复杂的。
B+树：
有序数组链表+平衡多叉树，B+树所有索引数据都在叶子节点上，并且增加了顺序访问指针，每个叶子节点都有指向相邻叶子节点的指针。这样做是为了提高区间效率，例如查询key为从18到49的所有数据记录，当找到18后，只要顺着节点和指针顺序遍历就可以以此向访问到所有数据节点，极大提高了区间查询效率，大大减少磁盘I/O读取

3.B+树的性质

1.有k个子树的中间节点包含有k个元素（B树中是k-1个元素），每个元素不保存数据，只用来索引，所有数据都保存在叶子节点。

2.所有的叶子结点中包含了全部元素的信息，及指向含这些元素记录的指针，且叶子结点本身依关键字的大小自小而大顺序链接。

3.所有的中间节点元素都同时存在于子节点，在子节点元素中是最大（或最小）元素。

优势：
1.单一节点存储更多的元素，使得查询的IO次数更少。

2.所有查询都要查找到叶子节点，查询性能稳定。

3.所有叶子节点形成有序链表，便于范围查询。

4.什么叫覆盖索引？

就是select的数据列只用从索引中就能够取得，不必从数据表中读取。发起一个被索引覆盖的查询时，在explain的Extra列可以看到“Using index”的信息。

5.什么叫索引下推？

索引下推在非主键索引上的优化，可以有效减少回表的次数，大大提升了查询的效率。

5.6之前的版本是没有索引下推这个优化的，因此执行的过程如下图：
例：

会忽略age这个字段，直接通过name进行查询，在(name,age)这课树上查找到了两个结果，id分别为2,1，然后拿着取到的id值一次次的回表查询，因此这个过程需要回表两次。

5.6版本添加了索引下推这个优化，执行的过程如下图：

没有忽略age这个字段，而是在索引内部就判断了age是否等于20，对于不等于20的记录直接跳过，因此在(name,age)这棵索引树中只匹配到了一个记录，此时拿着这个id去主键索引树中回表查询全部数据，这个过程只需要回表一次。

可以根据explain解析结果中Extra的值为Using index condition，表示已经使用了索引下推。

6.索引失效场景

where语句中包含or时，可能会导致索引失效
使用or并不是一定会使索引失效，你需要看or左右两边的查询列是否命中相同的索引
例：
假设USER表中的user_id列有索引，age列没有索引

select * from `user` where user_id = 1 or user_id = 2;（命中索引）

select * from `user` where user_id = 1 or age = 20;（未命中索引）

假设age列也有索引

select * from `user` where user_id = 1 or age = 20;（未命中索引）

where语句中索引列使用了负向查询，可能会导致索引失效
负向查询包括：NOT、!=、<>、!<、!>、NOT IN、NOT LIKE等，负向查询并不绝对会索引失效，这要看MySQL优化器的判断，全表扫描或者走索引哪个成本低。

在索引列上使用内置函数，一定会导致索引失效

select * from `user` where DATE_ADD(login_time, INTERVAL 1 DAY) = 7;

优化建议，尽量在应用程序中进行计算和转换

隐式类型转换导致的索引失效

 select * from `user` where user_id = 12;

select * from `user` where CAST(user_id AS signed int) = 12;

隐式字符编码转换导致的索引失效
当两个表之间做关联查询时，如果两个表中关联的字段字符编码不一致的话，MySQL可能会调用CONVERT函数，将不同的字符编码进行隐式转换从而达到统一。作用到关联的字段时，就会导致索引失效。
比如下面这个语句，其中d.tradeid字符编码为utf8，而l.tradeid的字符编码为utf8mb4。因为utf8mb4是utf8的超集，所以MySQL在做转换时会用CONVERT将utf8转为utf8mb4。简单来看就是CONVERT作用到了d.tradeid上，因此索引失效。

select l.operator from tradelog l , trade_detail d where d.tradeid=l.tradeid and d.id=4;

这种情况一般有两种解决方案。
方案1: 将关联字段的字符编码统一。
方案2: 实在无法统一字符编码时，手动将CONVERT函数作用到关联时=的右侧，起到字符编码统一的目的，这里是强制将utf8mb4转为utf8，当然从超集向子集转换是有数据截断风险的。如下：

select d.* from tradelog l , trade_detail d where d.tradeid=CONVERT(l.tradeid USING utf8) and l.id=2;

对索引列进行运算，一定会导致索引失效

select * from `user` where age - 1 = 10;

like通配符可能会导致索引失效
like查询以%开头时，会导致索引失效。解决办法有两种：

将%移到后面：

select * from `user` where `name` like '李%';

利用覆盖索引来命中索引：

select name from `user` where `name` like '%李%';

联合索引中，where中索引列违背最左匹配原则，一定会导致索引失效
当创建一个联合索引的时候，如(k1,k2,k3)，相当于创建了(k1)、(k1,k2)和(k1,k2,k3)三个索引，这就是最左匹配原则。
比如下面的语句就不会命中索引：

select * from t where k2=2;
select * from t where k3=3;
slect * from t where k2=2 and k3=3;

下面的语句只会命中索引(k1):

select * from t where k1=1 and k3=3;

优化索引的一些建议：
1.禁止在更新十分频繁、区分度不高的属性上建立索引。
2.更新会变更B+树，更新频繁的字段建立索引会大大降低数据库性能。
3.“性别”这种区分度不大的属性，建立索引是没有什么意义的，不能有效过滤数据，性能与全表扫描类似。
4.建立组合索引，必须把区分度高的字段放在前面。