聚集索引、辅助索引、MySQL索引管理、如何正确建立索引、

四.聚集索引与辅助索引

数据库中的 B+树 索引可以分为聚集索引（clustered index）和辅助索引（secondary index）, 聚集索引与辅助索引相同的是：不管是聚集索引还是辅助索引，其内部都是B+树的形式，即高度是平衡的, 不同的是 :

聚集索引的叶子节点存放的是一整行完整的信息, 而辅助索引的叶子节点存放的并非完整信息(下面介绍)

1.聚集索引 (Clustered Index)

InnoDB 聚集索引的叶子节点存储行记录，因此 InnoDB 必须要有且只有一个聚集索引

• 如果表定义了 PK （Primary Key，主键），那么 PK 就是聚集索引

• 如果表没有定义 PK，则第一个不为空且唯一(NOT NULL UNIQUE) 的列就是聚集索引

• 否则 InnoDB 会另外创建一个隐藏的 ROWID 作为聚集索引

由于这种机制是直接定位行记录，因此使得基于 PK 的查询速度非常快

2.辅助索引（ Secondary Index ）

表中除了聚集索引外其他索引都是辅助索引（Secondary Index，也称为非聚集索引）

• 与聚集索引的区别是：辅助索引的叶子节点不包含行记录的全部数据。叶子节点除了包含键值以外，每个叶子节点中的索引行中还包含一个书签（bookmark）。该书签用来告诉InnoDB存储引擎去哪里可以找到与索引相对应的行数据

• 辅助索引的存在并不影响数据在聚集索引中的组织，因此每张表上可以有多个辅助索引，但只能有一个聚集索引

• 当通过辅助索引来寻找数据时，InnoDB存储引擎会遍历辅助索引并通过叶子级别的指针获得指向主键索引的主键，然后再通过主键索引来找到一个完整的行记录

五.Mysql 索引管理

1.功能

• 索引的功能就是加速查找
• mysql中的primary key，unique，联合唯一也都是索引，这些索引除了加速查找以外，还有约束的功能

2.mysql 中常用的索引(键)

• 普通索引: index 加速查找
• 唯一索引:
  • 主键索引: primary key 加速查找+约束
  • 唯一索引: unique key 加速查找+约束
• 联合索引:
  • 联合主键索引: primary key
  • 联合唯一索引: unique(字段1, 字段2, …)
  • 联合普通索引: unique(字段1, 字段2, …)

上面的三种索引, 唯一索引除了可以增加查询速度之外各自还具有约束条件, 而普通索引index key没有任何的约束条件，只是用来帮助你加快速查询数据

注意：联合索引不是用来加速查询用的，不在我们的而研究范围之内

3.索引的两大类型

• 我们可以在创建索引的时候, 为其指定索引类型(两类)

hash类型的索引：查询单条快，范围查询慢
btree类型的索引：b+树，层数越多，数据量指数级增长（我们就用它，因为innodb默认支持它）

• 不同的存储引擎支持的索引类型也不一样

InnoDB 支持事务，支持行级别锁定，支持 B-tree、Full-text 等索引，不支持 Hash 索引
MyISAM 不支持事务，支持表级别锁定，支持 B-tree、Full-text 等索引，不支持 Hash 索引
Memory 不支持事务，支持表级别锁定，支持 B-tree、Hash 等索引，不支持 Full-text 索引
NDB 支持事务，支持行级别锁定，支持 Hash 索引，不支持 B-tree、Full-text 等索引
Archive 不支持事务，支持表级别锁定，不支持 B-tree、Hash、Full-text 等索引

六.创建和删除索引语法

1.创建的三种方法

• 语法

🍓方式一 : 创建表时建索引
create table [表名] (
    [unique|fulltext|spatial] [index|key] [索引名] [字段名(长度)] [asc|desc]
    );
    
🍓方式二 : 在已存在的表上创建
create [unique|fulltext|spatial] index [索引名]
    on [表名] [字段名(长度)] [asc|desc];

🍓方式二 : alter 在已存在的表上创建索引
alter table [表名] add [unique|fulltext|spatial] index 
    [索引名] [字段名(长度)] [asc|desc];

• 示例代码

🍓方式一
create table t01(
    id int,
    name char(10),
    age int,
    sex enum("male","female"),
    unique key unique_id(id),
    index index_name(name)  # index没有key
);

🍓方式二
create index index_age on t01(age);

🍓方式三
alter table t01 add index index_sex(sex);

2.删除索引

drop index [索引名] on t01;    # 语法
drop index index_age on t01;  # 示例

七.索引测试

1.先准备一张表, 并插入大量的数据

🍓创建表
create table t01(
    id int,
    name varchar(10),
    sex enum("male","female"),
    email varchar(18)
);

🍓创建存储过程,进行自动插入记录
delimiter %%%
create procedure p01()
begin
    declare i int default 1;
    while(i<3000000)do
        insert t01 value(i,"shawn","male",concat("shawn",i,"@163.com"));
        set i=i+1;
    end while;
end %%%
delimiter ;

🍓查看存储过程
show create procedure p01\G  # \G 垂直显示结果

🍓调用存储过程
call p01();  # windows执行测试大概一个半小时,3百万条记录,200多M

🍓删除存储过程
drop procedure p01;

2.没有建立索引的情况下测试查询速度

select * from t01 where id=3000000;

没有索引,mysql不知道有没有这条记录, 所以从头到尾的对记录进行遍历,有多少磁盘块就要进行多少I\O,速度很慢

3.为表的某个字段建立索引(表已经存在大量记录,创建速度会很慢)

create index index_id on t01(id);  # 为 id 字段建立普通索引

观察 data 文件夹下的 t01 表数据文件大小增加了

4.使用建立了索引的字段设置为条件进行查询

select * from t01 where id=3000000;  # 可以观察到速度明显的提升

5.步骤分析

• mysql先去索引表里根据 b+树 的搜索原理很快搜索到 id 等于3000000的记录，直接命中索引, IO大大降低，因而速度明显提升

• 我们以没有建立索引的字段设置为条件来进行查询, 可以发现速度依然很慢

  select * from t01 email="shawn3000000@163.com";  # 并且记录越大查询越慢
  

  

• 对 email 字段建立索引试试

create index index_email on t01(email);  # 字段数据越大,建立的时间越长(所以建议不要使用数据很大的字段建立索引,这里只是做实验)
select * from t01 where email="shawn3000000@163.com";  # 再去查询,可以发现速度是数量级的提升

八.正确使用索引

并不是说创建了索引就一定能加速查询, 有些情况就算命中了索引也未必能起到很好的提速效果, 下面来测试一下各种情况 (如果不想看过程,可以直接看小结末尾的结论)

1.范围查询情况 : 或者说条件不明确, 条件中有 : >、>=、<、<=、!= 、between…and…、like

• 大小于 : >、<

• 不等于 : !=

• between…and…

• 模糊匹配 : like

2.区分度情况 : 区分度表示字段不重复的比例, 区分度越大, 扫描的速度就越快,像主键唯一, 而性别字段区分度就很低

出现上面的情况就是因为字段的区分度太低, 在 B+树 中对于这些字段无法比较大小, 因为值都是相等的, 毫无疑问,只能增加树的高度来保证这些数据的存储, 树的高度越高, 查询速度就越慢

3.=和in可以乱序，比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序，mysql的查询优化器会帮你优化成索引可以识别的形式

4.索引列不要参与计算或者函数, 比如薪资乘与12 : 不要使用字段相乘(salary*12=10000), 可以使用该字段所对应的值相乘(salary=10000*12)

5.索引下推技术

🍓"and"与"or"的逻辑
    [条件1] and [条件2] : 所有条件都成立才算成立，但凡要有一个条件不成立则最终结果不成立
    [条件1] or [条件2] : 只要有一个条件成立则最终结果就成立

🍓"and"的工作原理
    条件：        
        a = 10 and b = 'xxx' and c > 3 and d =4    
    索引：        
        制作联合索引(d,a,b,c)    
    工作原理:        
        对于连续多个and：mysql会按照联合索引，从左到右的顺序找一个区分度高的索引字段(这样便可以快速锁定很小的范围)，加速查询，即按照d—>a->b->c的顺序

🍓"or"的工作牌原理
    条件：        
        a = 10 or b = 'xxx' or c > 3 or d =4    
    索引：        
        制作联合索引(d,a,b,c)            
    工作原理:        
        对于连续多个or：mysql会按照条件的顺序，从左到右依次判断，即a->b->c->d

6.最左前缀匹配原则

7.总结 (怎么建立索引能提高查询效率)

• 对区分度高并且占用空间小的字段建立索引
• 针对范围查询命中了索引，如果范围很大，查询效率依然很低，如何解决
  • 要么把范围缩小
  • 要么就分段取值，一段一段取最终把大范围给取完
• 不要把查询字段放到函数或者参与运算
• 索引下推技术,mysql自动选择查询速度最优的那条语句 (默认是开启）
• 索引覆盖 (下面介绍)
• 最左前缀匹配原则 (下面介绍)

8.其他注意事项

• 避免使用select *
• count(1)或count(列) 代替 count(*)
• 创建表时尽量时 char 代替 varchar
• 表的字段顺序固定长度的字段优先
• 组合索引代替多个单列索引（经常使用多个条件查询时）
• 尽量使用短索引
• 使用连接（JOIN）来代替子查询(Sub-Queries)
• 连表时注意条件类型需一致
• 索引散列值（重复少）不适合建索引，例：性别不适合