mysql索引学习(2) ｜青训营笔记这是我参与「第五届青训营」伴学笔记创作活动的第 16天（接上文查询方式从

这是我参与「第五届青训营」伴学笔记创作活动的第 16天

（接上文

查询方式

从根节点出发，根据搜索树的性质，用索引键定位到唯一的页节点，即查出了一个页
根据页目录，用索引键二分目标所在组
遍历组内record，记录结果

如果是范围查询，就从起始页节点沿着双链表的一个方向遍历，进行查询

聚簇索引和二级索引

针对B+tree叶子中数据类型，可分为两类

聚簇索引的叶子节点存放的是实际数据，所有完整的用户记录都存放在聚簇索引的叶子节点；
二级索引的叶子节点存放的是主键值，而不是实际数据。

一张表只能有一个聚簇索引tree，根据聚簇索引可以查询到完整的 record 记录

而为了实现非主键字段的快速搜索，可以从二级索引的B+tree中找到主键值，即而

如果主键值就是目标，则叫索引覆盖
否则，那主键值在聚簇索引中搜索得到record，这叫回表

最后，InnoDB 创建聚簇索引的策略：

如果有主键，默认会使用主键作为聚簇索引的索引键；（Primary Key）
如果没有主键，就选择第一个无 NULL 值的==唯一列==作为聚簇索引的索引键；（Unique）
在上面两个都没有的情况下，InnoDB 将自动生成一个==隐式自增 id 列== 作为聚簇索引的索引键

联合索引

多个索引组成

存在最左匹配原则，即先按照最左优先的方式进行索引的匹配。

如果不遵循「最左匹配原则」，联合索引会失效

最左匹配原则

例子：如果创建了一个 (a, b, c) 联合索引

符合最左匹配原则的：

where a=1；
where a=1 and b=2 and c=3；
where b=2 and a=1；

（需要注意的是，因为有查询优化器，所以 a 字段在 where 子句的顺序并不重要。

不符合

where b=2；
where c=3；
where b=2 and c=3；

（因为a不固定，不能根据最左匹配原则匹配索引，所以会失效

进一步，利用索引的前提是索引里的 key 是有序的，

对于a，它是全局有序的，只有搜索范围中的作前缀索引都相同，

后面单索引才是有序的，否则是无序的！！即无法执行索引算法

联合索引范围查询

在「范围查询」字段的后面的字段无法用到联合索引

例如：

Q1: select * from t_table where a > 1 and b = 2
Q2: select * from t_table where a >= 1 and b = 2
Q3: select * from t_table where a between 2 and 8 and b = 2
Q4: select * from t_user where name like 'j%' and age = 22

Q中哪个字段用到联合索引进行索引查询？

（a, b）只有a用到了，b没有

（a, b）都用到了用到索引查询（在 a==1时，b是有序的，可以用索引搜索；而对于a>=1范围里，b是全局无序的

（a, b）对MySQL来说，是闭区间，所以用到了索引查询，同上

（name, age）对于 name==‘j’或相等时，age会用到索引查询

总结：

联合索引的最左匹配原则，在遇到范围查询（如 >、<）的时候，就会停止匹配，

也就是范围查询的字段可以用到联合索引，但是在范围查询字段的后面的字段无法用到联合索引。

注意，对于 >=、<=、BETWEEN、like前缀匹配的范围查询，并不会停止匹配

索引下推

索引区分度

联合索引进行排序

按「字段特性」分类的索引

主键索引

索引列的值不允许有空

CREATE TABLE table_name  (
  ....
  PRIMARY KEY (index_column_1) USING BTREE
);

唯一索引

建立在 UNIQUE 字段上的索引，一张表可以有多个唯一索引，索引列的值必须唯一，但是允许有空值

声明一个候选码

CREATE TABLE table_name  (
  ....
  UNIQUE KEY(index_column_1,index_column_2,...) 
);

CREATE UNIQUE INDEX index_name
ON table_name(index_column_1,index_column_2,...);

create index on ()

普通索引

既不要求字段为主键，也不要求字段为 UNIQUE

CREATE TABLE table_name  (
  ....
  INDEX(index_column_1,index_column_2,...) 
);

CREATE INDEX index_name
ON table_name(index_column_1,index_column_2,...);

前缀索引

指对字符类型字段的前几个字符(即前缀)建立的索引

段类型为 char、 varchar、binary、varbinary

CREATE TABLE table_name(
    ....
    INDEX(column_name(length)) # column_name[:len] 作为
); 

CREATE INDEX index_name
ON table_name(column_name(length));

mysql索引学习(2) ｜ 青训营笔记