mysql中主键索引和联合索引的原理解析
https://img-blog.csdnimg.cn/direct/da26d98eec664f3392d1bd1557e4cd78.gif#pic_center一、主键索引
主键索引：按照主键数据从小到大按照从左到右进行排序，叶节点只存储数据区；
接着将上面的页天生出来，页只存储索引和指针，指针指向数据域，当通过主键查找数据时，从B+树的头部开始寻址数据、读取数据。
https://img-blog.csdnimg.cn/direct/c6eb300670244ee8b6cf1b88346f086d.png
上面为索引页
下面为数据页
查询select * from table where a=6，会从上到下走法，找索引
https://img-blog.csdnimg.cn/img_convert/4e7b431c7b07a6349f8be3fcb8b422f7.png
查询select * from table where a<6，会从上到下走法，找索引；
原因：先找a=6是走索引的，找到a=6数据后，将左边的数据全部返回即可。
https://img-blog.csdnimg.cn/img_convert/bb31602ef230cb81e104d385b1fdb027.png
为了方便把前面数据给你接纳双向指针
查询select * from table where b=6，会从左到右走法，全表扫描，由于b未有掷中索引。
https://img-blog.csdnimg.cn/img_convert/4ad2cf1d876695ec2986bb381dfa0ef0.png
二、什么是联合索引? 对应的B+树是如何天生的?
联合索引：将数据库表中多个字段构成一个索引。bcd联合索引；bcd三个字段进行排序
1、创建索引方式
create index idx_t1_bcd on t1(b,c,d);bcd三个字段进行排序
2、什么是最左前缀原则?
复合索引，也叫联合索引，用户可以在多个列上创建索引,这种索引叫做复合索引。
当我们创建一个组合索引的时间，如(k1,k2,k3)，相称于创建了(k1)、
(k1,k2)和(k1,k2,k3)三个索引，这就是最左匹配原则。
select * from table where k1=A and k2=B and k3=C
3、回表
https://img-blog.csdnimg.cn/direct/6f54a69d9292472ca020c3b3b3738580.png
select * from t1 where b=1 and c=1 and d=1执行逻辑：
通过联合索引找到一条数据的3个字段，但是select * 查找多个字段，所以需要存储主键字段，通过主键字段去主键索引内里找到完备的这条数据，并将这条数据返回。
通过主键再去主键索引内里找数据的过程叫做回表
https://img-blog.csdnimg.cn/img_convert/32685aa1ba876213c7ab4c8dc49ecc85.png
https://img-blog.csdnimg.cn/img_convert/bab3c59810bdd318dbb63aeeb2c09fb9.png
4、为什么要遵守最左前缀原则才能利用到索引?
explain select * from t1 where c=1 and d=1 and b=1 ;
最左前缀原则和后面条件的顺序没有关系
https://img-blog.csdnimg.cn/img_convert/47279358fd77717fb8804b234cef3fdb.png
不符合最左匹配原则：*11
https://img-blog.csdnimg.cn/img_convert/69d204a4f9a3dc5f180d6338772cfad1.png
符合最左匹配原则:1*1
https://img-blog.csdnimg.cn/img_convert/22ed0f271549e0fa3c0d17a47dab9ed5.png
b=1：走联合索引 (需要回表1次)
https://img-blog.csdnimg.cn/img_convert/6b4d508eda4c9c6755fe95c96ee69dc4.png
b>1:不走联合索引（由于需要回表许多次）
https://img-blog.csdnimg.cn/img_convert/4dadac536bc448fea05e7be91420b1ea.png
https://img-blog.csdnimg.cn/img_convert/2f25a2df1fbd30f8bbe387803a19e3ef.png
拿到主键会到主键索引内里拿数据（回表7次）
5、什么是覆盖索引?
查询的字段恰好在联合索引字段内里，不需要回表
select b from t1 where b>1;(不需要回表了，直接从联合索引字段中将该字段取出来)
https://img-blog.csdnimg.cn/img_convert/c9b396cb98ebb1179cfd7ab75b608cb5.png
select a,b,c,d from t1 where b>1;
同样的也符合联合索引，也不需要回表，由于a字段（主键）在联合索引中。
https://img-blog.csdnimg.cn/img_convert/a2e1a0f5bec7503415e797f65c7e3620.png
select a,b,c,d,e from t1 where b>1;
不走联合索引，需要回表，由于e字段不在联合索引中，需要回表许多次
https://img-blog.csdnimg.cn/img_convert/a598e9d4603595b4b7f4a1c401ad92f0.png
6、索引扫描底层原理
从bcd索引的叶子节点开始遍历
主键索引存储的是8条完备的数据
联合索引中存储的是8条不完备的数据，而且b字段在联合索引中，不会去回表到主键索引中。
从叶子节点开始扫描不需要符合最左匹配原则。
https://img-blog.csdnimg.cn/img_convert/6a3e188ef996b193327a8a37ef2c0b35.png
https://img-blog.csdnimg.cn/img_convert/6392b9e2f88a3bd0b14fc4aec07fc4a2.png
https://img-blog.csdnimg.cn/img_convert/52ba543c77b13ffc83bc8bb3d13429d3.png
7、order by为什么会导致索引消失？
由于走全表扫描效率更高，不需要回表
走索引会回表多次，效率低
https://img-blog.csdnimg.cn/img_convert/1302c81168054bfafe25a7a339094ba7.png
https://img-blog.csdnimg.cn/img_convert/b6e33bf07b17db7ef1ced6bd1fd5b568.png
8、有哪些情况会导致索引失效?
1、索引列利用!=、not、is null、is not null查询的时间，由于索引数据的检索效率非常低，因此Mysql引擎会判定不走索引。
2、利用like通配符匹配后缀%xxx的时间，由于这种方式不符合索引的最左匹配原则，所以也不会走索引。
[*]但是反过来，如果通配符匹配的是前缀xxx%，符合最左匹配，也会走索引。
3、对索引列上进行函数运算、导致mysql无法辨认索引列，就不会走索引了。
https://img-blog.csdnimg.cn/img_convert/b9282ce473f12431ad2720233711fc6a.png
4、利用or毗连查询的时间，or语句前后没有同时利用索引，那么索引会失效。只有or左右查询字段都是索引列的时间，才会生效。
5、当索引列存在隐式转化的时间， 比如索引列是字符串范例，但是在sql查询中没有利用引号。
9、索引计划原则
查询更快、占用空间更小
1、选择合适的列作为索引
[*]1.经常作为查询条件（where子句）、排序条件（order by子句） 分组条件（group by子句）的列创建索引。
[*]2、区分度低的字段，比方性别，不要创建索引
[*]3、更新频繁字段不适合创建索引
[*]4、对于定义为text、image和bit的数据范例的列不要创建索引
[*]5、定义有外键的数据列肯定要创建索引。
[*]6、数据量较小的表，索引结果较差，没有须要在此列创建索引
[*]7、尽量利用扩展索引，不要新建索引。比如表中已经有a的索引，现在要加(a,b)的索引，那么只需要修改原来的索引即可。
[*]8、利用短索引，如果对长字符串列进行索引，应该指定一个前缀长度，如许可以或许节流大量索引空间，如果搜索词
超过索引前缀长度，则利用索引清除不匹配的行，然后查抄其余行是否大概匹配。
2、制止利用过多的索引
[*]1、每个索引都需要占用额外的磁盘空间
[*]2、更新表（update、insert、delete操纵时），所有的索引都需要被更新
[*]3、维护索引需要成本
三、InnoDB 与MyISAM 的区别
https://img-blog.csdnimg.cn/img_convert/7a31b8de96f223b6ffbb85c337b20a93.png
https://img-blog.csdnimg.cn/img_convert/8581a405207fd3e601c24a497b462688.png
聚簇索引:将数据存储与索引放到了一块、而且是按照肯定的顺序组织的，找到索引也就找到了数据，数据的物理存放顺序与索引顺序是一致的，即:只要索引是相邻的，那么对应的数据肯定也是相邻地存放在磁盘上的。
非聚簇索引:叶子节点不存储数据、存储的是数据行地址，也就是说根据索引查找到数据行的位置再取磁盘问找数据，这个就有点类似一本树的目录，比如我们要找第三章第一节，那我们先在这个目录内里找，找到对应的页码后再去对应的页码看文章。
优势:
1、查询通过聚簇索引可以直接获取数据，相比非聚簇索引需要第二次查询(非覆盖索引的情况下)效率要高
2、聚簇索引对于范围查询的效率很高，由于其数据是按照巨细排列的
3、聚簇索引适合用在排序的场合，非聚簇索引不适合
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。
页:
[1]