mysql 整理索引_Mysql索引整理

1、Mysql基本单位是页，大小为16kb=16384/1024

页是为了增加查询效率，减少IO的交互(局部性原理)

2、页与页之间是双向链表，插入的时候会根据主键id进行排序

单叶数据结构.jpg

3、在页上有一个页目录，相当于把数据进行分组，存放的是当前组最小的主键id，指针并且指向对应的数据

比如：每组存放4个，

目录1=1；

目录2=5

目录3=9

当我们想要查询id=7的时候，只需要在目录2中查找，如果找到就返回，否则查询不到符合条件的数据，减少全表扫描

页与页双向链表.jpg

叶子节点与非叶子节点.jpg

4、MySql的存储引擎InnoDB的数据结构为B+树，只有叶子节点存在数据，非叶子节点存放的是索引页目录，中间不存放具体的数据

B+树索引.jpg

5、索引的本质就是一种快速查找的数据结构，目的就是快速查询数据

聚集索引：主键索引，唯一

非聚集索引：非主键索引，可以有多个

索引扫描：按照B+树从根节点从上到下进行查找

全表扫描：从叶子节点从左到右进行查询

6、比如一张表

create table t_table{

a int primary key,

b varchar,

c varchar,

d varchar，

e char

}

当创建索引：create index index_bcd on t_table(b,c,d)

多字段的索引会从左到右进行比较，bcd组合成一个索引树

这种索引可以叫做辅助索引，二级索引等等，

查询的过程：先从索引树中进行查询

查询到索引叶子节点，获取对应数据，如果返回值是索引列，则直接返回

如果返回值中包含了非叶子节点的数据列，则需要根据叶子节点中保存的id，再到数据树中根据id查询到返回的列

这种就叫做回表。

用辅助索引查询的数据不完整，需要从主表中查询完整的的数据。

7、最左前缀原则

select * from t_table where c = 'xx' and d = 'aa';

SQL中没有查询条件b,在索引树的目录中相当于：cd,无法比较大小，无法定位到那一页，所以查询不会走索引

模糊查询也是相同的原理

如果是select * from t_table where b = 'xx' and d = 'aa';

相当于bd,在索引树中是可以比较大小的，只不过是一个范围，把这个范围的数据取出后再到Mysql server中进行筛选过滤

8、a> 1,是走索引的，会先根据a = 1进行索引查询，再定位到数据页中的的数据，则在a=1后面的数据都是满足条件的

b > 1可能会走索引，也可能不走索引，走索引肯会需要回表

会有分析器来确定,如果走索引的成本 > 全表扫描的成本，则进行全表扫描，否则走索引

9、select b from t_table

无where条件是会走索引的，因为辅助索引页上不会存储全部表数据，那就可以存储更多行的数据，非叶子节点就可以存储更多页，速度更快

10、在mysql中数据类型不一致

比如数据列c1是数值类型，where c1 = '11'则会把字符串11转换成数值11，如果where c1 = 'aa',则会把字符型aa转换成数值0

转换失败就为0

select 1= 1; true

select 1='a';false

select 0 = 'a';true

select 1= '1';true

如果C1是字符型，where c1 = 1,则不会走索引，因为需要将索引列中的c1全部转换成数值型，成本太高，不走索引

如果where a + 1 = 4，也不会走索引因为需要在索引中计算a+1，成本太高

所以在SQL中尽量不要使用函数

11、limit

越向后分页查询速度越慢，是因为它越向后找，起始页查询的成本越高

可以在上一次查询的时候获取他最后一条数据的idLast,在分页的时候添加 and id > idLast limit 10000,100

12、order by走不走索引也需要看是否存在回表，走索引的成本是不是太高，

在MySQL8以前创建索引不能指定升降序，8之后可以指定索引的排序

create index (b asc,c asc,d desc)