甘肃网站建设方案服务至上,网站开发的单价,河南企起网站建设,博客网站源码带后台mysql中主键索引和联合索引的原理解析 一、主键索引二、什么是联合索引? 对应的B树是如何生成的?1、建立索引方式2、什么是最左前缀原则?3、回表4、为什么要遵守最左前缀原则才能利用到索引?5、什么是覆盖索引?6、索引扫描底层原理7、order by为什么会导致索引消失#x… mysql中主键索引和联合索引的原理解析 一、主键索引二、什么是联合索引? 对应的B树是如何生成的?1、建立索引方式2、什么是最左前缀原则?3、回表4、为什么要遵守最左前缀原则才能利用到索引?5、什么是覆盖索引?6、索引扫描底层原理7、order by为什么会导致索引消失8、有哪些情况会导致索引失效?9、索引设计原则 三、InnoDB 与MyISAM 的区别 一、主键索引
主键索引按照主键数据从小到大按照从左到右进行排序叶节点只存储数据区 接着将上面的页生成出来页只存储索引和指针指针指向数据域当通过主键查找数据时从B树的头部开始寻址数据、读取数据。 上面为索引页 下面为数据页
查询select * from table where a6会从上到下走法找索引 查询select * from table where a6会从上到下走法找索引 原因先找a6是走索引的找到a6数据后将左边的数据全部返回即可。 为了方便把前面数据给你采用双向指针
查询select * from table where b6会从左到右走法全表扫描因为b未有命中索引。 二、什么是联合索引? 对应的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 k1A and k2B and k3C3、回表 select * from t1 where b1 and c1 and d1执行逻辑 通过联合索引找到一条数据的3个字段但是select * 查找多个字段所以需要存储主键字段通过主键字段去主键索引里面找到完整的这条数据并将这条数据返回。
通过主键再去主键索引里面找数据的过程叫做回表 4、为什么要遵守最左前缀原则才能利用到索引?
explain select * from t1 where c1 and d1 and b1 ; 最左前缀原则和后面条件的顺序没有关系 不符合最左匹配原则*11
符合最左匹配原则:1*1
b1走联合索引 (需要回表1次)
b1:不走联合索引因为需要回表很多次 拿到主键会到主键索引里面拿数据回表7次
5、什么是覆盖索引?
查询的字段正好在联合索引字段里面不需要回表
select b from t1 where b1;(不需要回表了直接从联合索引字段中将该字段取出来)
select a,b,c,d from t1 where b1; 同样的也符合联合索引也不需要回表因为a字段主键在联合索引中。
select a,b,c,d,e from t1 where b1; 不走联合索引需要回表因为e字段不在联合索引中需要回表很多次
6、索引扫描底层原理
从bcd索引的叶子节点开始遍历 主键索引存储的是8条完整的数据 联合索引中存储的是8条不完整的数据并且b字段在联合索引中不会去回表到主键索引中。
从叶子节点开始扫描不需要符合最左匹配原则。 7、order by为什么会导致索引消失
因为走全表扫描效率更高不需要回表 走索引会回表多次效率低 8、有哪些情况会导致索引失效?
1、索引列使用!、not、is null、is not null查询的时候由于索引数据的检索效率非常低因此Mysql引擎会判断不走索引。 2、使用like通配符匹配后缀%xxx的时候由于这种方式不符合索引的最左匹配原则所以也不会走索引。
但是反过来如果通配符匹配的是前缀xxx%符合最左匹配也会走索引。
3、对索引列上进行函数运算、导致mysql无法识别索引列就不会走索引了。
4、使用or连接查询的时候or语句前后没有同时使用索引那么索引会失效。只有or左右查询字段都是索引列的时候才会生效。
5、当索引列存在隐式转化的时候 比如索引列是字符串类型但是在sql查询中没有使用引号。
9、索引设计原则
查询更快、占用空间更小 1.适合索引的列是出现在where子句中的列或者连接子句中指定的列 2.数据量较小的表索引效果较差没有必要在此列建立索引 3.使用短索引如果对长字符串列进行索引应该指定一个前缀长度这样能够节省大量索引空间如果搜索词 超过索引前缀长度则使用索引排除不匹配的行然后检查其余行是否可能匹配。 4.不要过度索引。索引需要额外的磁盘空间并降低写操作的性能。在修改表内容的时候索引会进行更新甚至重构索引列越多这个时间就会越长。所以只保持需要的索引有利于查询即可。 5.定义有外键的数据列一定要建立索引。 6.更新频繁字段不适合创建索引 7.若是不能有效区分数据的列不适合做索引列(如性别男女未知最多也就三种区分度实在太低) 8.尽量的扩展索引不要新建索引。比如表中已经有a的索引现在要加(a,b)的索引那么只需要修改原来的索引即可。 9.对于那些查询中很少涉及的列重复值比较多的列不要建立索引。 10.对于定义为text、image和bit的数据类型的列不要建立索引。
三、InnoDB 与MyISAM 的区别 聚簇索引:将数据存储与索引放到了一块、并且是按照一定的顺序组织的找到索引也就找到了数据数据的物理存放顺序与索引顺序是一致的即:只要索引是相邻的那么对应的数据一定也是相邻地存放在磁盘上的。 非聚簇索引:叶子节点不存储数据、存储的是数据行地址也就是说根据索引查找到数据行的位置再取磁盘查找数据这个就有点类似一本树的目录比如我们要找第三章第一节那我们先在这个目录里面找找到对应的页码后再去对应的页码看文章。
优势: 1、查询通过聚簇索引可以直接获取数据相比非聚簇索引需要第二次查询(非覆盖索引的情况下)效率要高 2、聚簇索引对于范围查询的效率很高因为其数据是按照大小排列的 3、聚簇索引适合用在排序的场合非聚簇索引不适合
