MySQL之索引原理和执行操持
媒介在表查询操作时我们提到了一个索引的概念，本文就具体介绍索引实现原理以及简单的介绍一下B+树。MySQL的索引在口试中会被经常问到，擦亮bling bling的大眼睛一起来瞅瞅吧~
索引简介
索引就相称于是书的目录，能够帮助我们快速定位到所需要的数据，可以起到优化查询的功能，索引可以通过B+树进行构建。
B+树简介
树状图是一种数据结构 ，它是由n（n>=1）个有限结点组成一个具有条理关系的集合。把它叫做“树”是因为它看起来像一棵倒挂的树，也就是说它是根朝上，而叶朝下的。每个结点有零个或多个子结点；没有父结点的结点称为根结点；每一个非根结点有且只有一个父结点；除了根结点外，每个子结点可以分为多个不相交的子树。
B+树是通过二叉查找树，再由平衡二叉树，B树演化而来，可以控制树的高度控制搜索数据时的IO次数。IO次数取决于b+数的高度level。
https://i-blog.csdnimg.cn/blog_migrate/8aa5272e7b18ca1b14a1d91e68c1e4a7.png
索引实现原理
在数据库中B+树的高度一样平常都在2到4层，就是说查找某一个记录最多只需要2到4次IO操作，数据库中B+树索引分为聚簇索引和辅助索引，不管是聚集索引还是辅助索引，其内部都是B+树的情势，即高度是平衡的，差别的是叶子结点存放的数据差别。
聚簇索引
聚簇索引的构建前提
在建表时指定主键列，MySQL的innodb引擎会将主键列作为聚簇索引列，类似于书籍目录的大标题，如果建表的时候没有指定主键列，innodb引擎会自动选择唯一列作为聚簇索引列，如果都没有的话innodb引擎会生成隐蔽的聚簇索引。
聚簇索引的作用
有了聚簇索引之后，插入的数据行在同一个区内都会按照主键值的顺序有序的在磁盘中存储数据，这为顺序IO提供了条件，当利用主键列作为查询条件的时候可以帮助我们快速的查找数据，理论上B+树为三层时，基于主键列只需要3次IO就可以锁定叶子节点中的数据页。
聚簇索引的构建过程
叶子节点：数据行地点的数据页(page,16kb)，由于聚簇索引在存储数据的时候是按照顺序的，叶子节点的数据页就直接生成了，而且叶子节点的数据是每条记录的完整数据。
支节点：主键值的范围抽取出来作为支节点，保存叶子节点的范围。
根节点：根据支节点生成根节点，保存支节点的范围。
辅助索引
辅助索引的作用
辅助索引是利用普通列构建的索引，需要人为的创建，类似于书籍目录中大标题下的小标题，辅助索引的作用就是优化利用非聚簇索引列之外的列作为条件进行的查询。但是辅助索引的缺点就是当利用辅助索引查询数据的时候由于辅助索引的叶子节点只有部分数据，有时还需要进行回表操作，就是还需要通过辅助索引查询的结果再通过聚簇索引得到完整的数据行，需要注意的是，创建辅助索引后，向表中插入数据的速度也会变慢，每个索引都需要占用磁盘空间，索引越多，需要的磁盘空间就越大。
辅助索引的构建过程
叶子节点：将聚簇索引列和辅助索引列取出，按照辅助索引列进行排序，生成叶子节点的数据页，是部分数据，只包含辅助索引和聚簇索引的数据。
支节点：辅助索引列的范围抽取出来作为支节点，保存叶子节点的范围。
根节点：根据支节点生成根节点，指向支节点的数据页，保存下层支节点的范围。
辅助索引的范例
辅助索引分为单列索引和联合索引，这里具体介绍一下联合索引。联合索引就是多个非主键列组合成的辅助索引，比如构建(a, b, c)这三个列的联合索引，相称于创建了a, ab, abc这三个索引。
利用联合索引需要遵照最左原则，在生成支节点时，只会将最左列作为支节点，因此查询条件必须包含最左列，因此建立联合索引的时候一定要选择重复值最少的列作为最左列，而且在查询时只要查询条件包含最左列都会走联合索引。可以分析一下查询时走联合索引的情况：
-- 联合索引全部覆盖：
select * from t1 where a= b= c= / a in b in c in /b= a= c=
-- 部分覆盖：
select * from t1 where a= b= / a= / a= c=
select * from t1 where a= b<= >= like and c= (只走ab索引)
-- 不覆盖：
select * from t1 where b= c=
回表操作
MySQL中表的数据行终极是存储到许多的page上，innodb存储引擎会按照聚簇索引有序的组织存储表数据到各个区的一连的page上，这些一连的page就成为了聚簇索引的叶子节点，即聚簇索引叶子节点存储的就是原表数据。以是回表操作就是回聚簇索引。
构建辅助索引的时候，是将主键值和辅助索引列值按照辅助索引列值进行排序构建辅助索引B树结构，当利用辅助索引作为查询条件的时候，会首先扫描辅助索引的B+树，如果辅助索引能够完全覆盖我们的查询结果就不需要进行回表操作，如果不能完全覆盖，只能通过得到的主键值回到聚簇索引（回表）扫描，终极得到想要的结果。
索引操作
上面说了这么多，如何查看和创建索引呢？请看下面的SQL：
-- 查询索引
desc 表名称;-- PRI聚簇索引MUL辅助索引UNI唯一索引
show index from 表名; -- 更加详细的索引信息
-- 创建索引：
alter table 表名 add index idx_na(列名);-- 单列索引,idx_na只是索引的名字，随便起
alter table 表名 add index idx_n_c_(列名1, 列名2);-- 联合索引
alter table 表名 add index idx_d(列名(5));-- 前五个字符构建的前缀索引
-- 删除索引
alter table 表名 drop index idx_na;
建立索引规范
为了使索引的利用效率更高，在创建索引时，必须考虑在哪些字段上创建索引和创建什么范例的索引，因此索引设计在项目开发中是非常重要的，下面小庄列出一些建立索引的规范：
①建表时一定要有主键列，一样平常是一个无关列；
②为经常需要where，order by，group by，join on等操作的字段创建辅助索引；
③限定索引的数目，索引并不是越多越多好；
④删除不利用大概很少利用的索引，可以利用percona toolkit工具；
⑤数据许多的表中如果加索引建议在业务不繁忙时段进行操作；
⑥只管不要在经常更新值的列上创建索引，容易引起索引失效。
不走索引的情况
虽然有时候创建了聚簇索引也创建了辅助索引，但是有时后查询速度还是很慢，原因就是很有可能查询语句没有走索引，下面小庄也列出了一些不走索引的情况的查询，供大家参考：
①没有查询条件，大概查询条件没有建立索引；
②查询的结果集超过了表中数据总行数的15-30%，优化器觉得没必要走索引，这个和数据库的预读能力和参数有关；
③索引失效，统计的数据不真实，索引有自我维护的能力，对于表内容变化比力频繁的情况下，统计信息禁绝确，有可能会出现索引失效，一样平常是删除重修。
④查询条件利用函数在索引列上，大概对索引列进行运算，运算包括(+，-，*，/，! 等)；
⑤隐式转换导致索引失效，这一点应当引起重视。也是开发中经常会犯的错误.查询的时候注意数据范例是否和表中定义的数据范例是否同等。
⑥< > ，not in 不走索引（辅助索引，单独的> < in有可能走索引也可能不走，只管结合业务添加limit,or 和 in 利用差别的条件进行测试，选择哪种具体的方案）。
⑦like "%_" 百分号在最前面不走，%linux%类的搜索需求，可以利用elasticsearch+mongodb 专门做搜索服务的数据库产品
执行计分别析
在执行SQL语句时，是由解析器生成执行方案，优化器选择执行方案，那么一条SQL语句的执行方案开发职员能不能看到呢？答案是肯定滴，就是通过执行操持，而且通过分析执行操持，开发职员可以根据情况对SQL语句进行优化。
执行操持就是优化器按照内置的代价计算算法得到的终极的执行操持，查看执行操持可以通过下述的SQL语句：
-- 二者任选其一
desc sql语句;
explain sql语句;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key| key_len | ref| rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
|1 | SIMPLE | city| NULL | ALL| NULL | NULL | NULL | NULL | 4046 | 100.00 | NULL|
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
开发职员就可以根据上述执行操持进行分析了，如何进行分析，就要看结果中的每个字段表现啥意思了，请听小庄娓娓道来~
table：执行操持涉及到的表，针对多表查询时，能够帮助确定查询较慢的表进行优化。
type：查询范例，分为全表扫描和索引扫描。
全表扫描ALL:不用到任何的索引（全表查询/ not in/ like '%&'等情形不会走任何索引）
索引扫描：index<range<ref<eq_ref<const(system)
index:全索引扫描，需要扫描整颗索引树
range:索引的范围查询，当查询>= <= like in or between and等条件时属于范围查询，对于id查询 not in !=是走range索引的
ref:辅助索引的等值查询
eq_ref:多表连接中，非驱动表的连接条件是主键或者唯一键
const(system):聚簇索引等值查询
possible_keys：所有可能用的索引，所有和此次查询有关的索引
key：最后选择的索引
-- 在查询数据的时候如果有多个条件子句，需要使用联合索引，单列索引不会生效。
select * from city where countrycode='CHN' order by population;-- population单列索引不会生效,index(countrycode, population)生效
key_len：联合索引覆盖长度，评估联合所用的应用长度，对于联合索引比如index(a, b, c)希望将来的查询语句对于联合索引的应用越充分越好，key_len可以帮助判断此次查询走了联合索引的几部分。
key_len计算规则： 在完全覆盖的情况下：key_len = a长度 + b长度 + c长度 长度受到数据范例、字符集的影响，指的是列的最大储值字节长度 没有约束not null时，需要单独一个字节存储列值是否是非空
数字列的储值长度：
数字范例/是否非空(not null)是否tinyint11+1int44+1bigint88+1字符列的储值长度：以utf8字符集为例，一个字符最大占3个字节，varchar需要1-2个字节存储字节长度
字符范例/是否非空(not null)是否char(10)3*103*10+1varchar(10)3*10+23*10+2+1 rows：此次查询需要扫描的行
extra：额外的信息
using filesort：表现此次查询利用到了文件排序，说明在查询中的排序操作没有利用到索引
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。
页:
[1]