索引-MySQL

一 MySQL 索引分类

索引是一种数据结构.

作用:是数据库管理系统中一个排序的数据结构，以协助快速查询、更新数据库表中数据。

1.按存储结构分类

BTree索引（B+Tree索引），Hash索引，full-index全文索引，R-Tree索引

Btree索引( MySQL默认)

UNIQUE KEY `idx_no` (`num`) USING BTREE

BTree 索引顺序存储,便于范围查找.

Hash索引

基于哈希表实现，只有精确匹配索引所有列的查询才有效，对于每一行数据，存储引擎都会对所有的索引列计算一个哈希码（hash code），并且Hash索引将所有的哈希码存储在索引中，同时在索引表中保存指向每个数据行的指针。

适合等值查询，如=、in()、<=>，不支持范围查询 ；

因为不是按照索引值顺序存储的，就不能像B+Tree索引一样利用索引完成**排序** ；

Hash索引在查询等值时非常快 ；

full-index全文索引

通过查找文本中的关键词，类似于搜索引擎，而不是简单的where条件匹配.

索引的优点：

1.索引大大减少了服务器需要扫描的数据量

2.索引可以帮助服务器避免排序和临时表

3.索引可以将随机IO变成顺序IO

索引的策略：

如果索引列是表达式的一部分或者是函数的参数，则不会使用到索引 如：where id+1=5;

始终将索引列单独放在比较符号的一侧

R-Tree索引(空间数据索引)

MyISAM支持空间索引，可以用作地理数据存储，R-tree无须前缀索引。空间索引会从所有维度来索引数据。查询时，可以有效地使用任意维度来组合查询。

前缀索引(短索引)

create index indexname on tablename(columnname(10));    //单列的前10个字符创建前缀索引

通过索引开始的部分字符，提高索引效率且可以节约索引空间，但会降低索引的选择性

(选择性:不重复的索引值和数据表的记录总数)，索引选择性越高则查询效率越高

对于BLOB、text或者很长的varchar类型的列必须使用前缀索引。

前缀应该足够长，使得前缀索引的选择性接近于索引整个列。

前缀索引无法使用order by和group by也无法使用覆盖索引

创建前缀索引流程:

计算完整列的选择性 :select count(distinct column1)/count(1) from table_1

再计算不同前缀长度(4,5,6,…)的选择性 :select count(distinct left(column1,4))/count(1) from table_1

找到最优长度(越大越好)之后，创建前缀索引 :create index idx_front on table_1 (column1(4))

2. 应用层次分类

普通索引，唯一索引，复合索引

普通索引：即一个索引只包含单个列，一个表可以有多个单列索引

create index indexname on tablename(columnname);

唯一索引：索引列的值必须唯一，但允许有空值

create unique index indexname on tablename(columnname); 

复合索引：多列值组成一个索引，专门用于组合搜索，其效率大于索引合并

create index indexname on tablename(columnname1，columnname2);    //多列的复合索引

3. 数据存储顺序与逻辑顺序

根据中数据的物理顺序与键值的逻辑（索引）顺序关系： 聚集索引，非聚集索引

​

二 聚簇索引 Clustered Index

聚簇索引的物理意义

聚簇索引,也称为聚集索引，聚类索引，簇集索引，聚簇索引确定表中数据的物理顺序。

聚簇索引的数据的物理存放顺序与索引顺序是一致的，即：只要索引是相邻的，那么对应的数据一定也是相邻地存放在磁盘上的。

每张表只能建一个聚簇索引.

MySQL中的聚簇索引

1. 有主键时，根据主键创建聚簇索引
2. 没有主键时，会用一个唯一且不为空的索引列做为主键，成为此表的聚簇索引
3. 如果以上两个都不满足那InnoDB自己创建一个虚拟的聚集索引

InnoDB的数据文件本身就是索引文件，B+Tree的叶子节点上的data就是数据本身，key为主键，这是聚簇索引。

非聚簇索引，叶子节点上的data是主键(所以聚簇索引的key，不能过长)。

什么是非聚簇索引？

非聚簇索引 / 二级索引 / 辅助索引

非聚簇索引的存储和数据的存储是分离的，也就是说找到了索引但没找到数据，需要根据索引上的值(主键)再次回表查询,非聚簇索引也叫做辅助索引。

聚簇索引的例子

下面我们创建了一个表，来区分是聚簇索引，索引覆盖

create table teacher(
id bigint,
num varchar(20) ,
name varchar(20) ,
PRIMARY KEY (`index_id`) USING BTREE,
UNIQUE KEY `idx_no` (`num`) USING BTREE
)ENGINE=InnoDB ;

一，聚簇索引,根据主键查询所有字段数据，此时主键是聚簇索引，因为主键对应的索引叶子节点存储了id=1的数据。

select * from teacher where id = 1

二，非聚簇索引,回表,根据编号查询编号和名称，编号本身是一个唯一索引，结果包含了编号和名称，

当命中编号索引时，该索引的节点的数据存储的是主键ID，需要根据主键ID重新查询一次，所以这种查询下no不是聚簇索引.

select num,name from teacher where num = 'ab12'

三，非聚簇索引,不回表,根据编号查询编号，这种查询命中编号索引时，直接返回编号，因为所需要的数据就是该索引，不需要回表查询

在索引的叶子节点上，已经包含了nuim信息，不会再次进行回表查询。

select num from teacher where num = 'ab12'

三 索引相关常用命令

0) 查看索引
show index from tablename;
show create table pk_tab2;

1) 创建主键
 CREATE TABLE `pk_tab2` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `a1` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

2) 创建唯一索引 UNIQUE INDEX
create unique index indexname on tablename(columnname); 
alter table tablename add unique index indexname(columnname);

3) 创建单列一般索引
create index indexname on tablename(columnname);
alter table tablename add index indexname(columnname);

4) 创建单列前缀索引
create index indexname on tablename(columnname(10));    //单列的前10个字符创建前缀索引
alter table tablename add index indexname(columnname(10)); //单列的前10个字符创建前缀索引

5) 创建复合索引
create index indexname on tablename(columnname1，columnname2);    //多列的复合索引
create index indexname on tablename(columnname1，columnname2(10));    //多列的包含前缀的复合索引
alter table tablename add index indexname(columnname1，columnname2); //多列的复合索引
alter table tablename add index indexname(columnname1，columnname(10)); //多列的包含前缀的复合索引

6) 删除索引
drop index indexname on tablename;;
alter table tablename drop  index indexname;

7) 使用强制索引查询
select | update | delete column1 force INDEX(index_name) table_name where condition

四 索引的空间大小

待写

五 索引的优点

① 建立索引的列可以保证行的唯一性，生成唯一的rowId

② 建立索引可以有效缩短数据的检索时间

③ 建立索引可以加快表与表之间的连接

④ 为用来排序(order)或者是分组(group 、partition)的字段添加索引可以加快分组和排序顺序

六 索引的缺点

① 创建索引和维护索引需要时间成本，这个成本随着数据量的增加而加大

② 创建索引和维护索引需要空间成本，每一条索引都要占据数据库的物理存储空间，数据量越大，占用空间也越大（数据表占据的是数据库的数据空间）

③ 会降低表的增删改的效率，因为每次增删改索引需要进行动态维护，导致时间变长

七 高性能索引策略

1. 独立的列

   索引列不能是表达式的一部分

   索引列不能是函数的一部分

   where count(num) x

   where id + 1 = 2 x

   where id = 1 √

2. 最左匹配原则

   需要根据业务场景

   需要考虑order by,group by的场景

   index (a,b)

   order by a,b √

   order by b,a x

3. 使用短索引(前缀索引)

   假如索引太长,只匹配部分前缀即可

   create index indexname on tablename(columnname(10)); //单列的前10个字符创建前缀索引

4. 避免空间索引(Spatial Indexes)列包含null

   有NULL值的字段上使用常用的索引，如普通索引、复合索引、全文索引等不会使索引失效。

   在官网查看在空间索引的情况下，说明了 索引列必须为NOT NULL。MySQL 官方文档

5. 使用聚簇索引

6. 使用索引覆盖(联合索引)

7. 主键(聚簇索引) ,外键 建立索引

8. 表之间连接字段建立索引

9. 索引应该小字段,大字段可以采用前缀索引

10. 索引建立在选择性高的字段上

11. 频繁数据操作的表,不要建立太多索引

12. 删除无用的索引,避免对执行计划造成负面影响

八 其他概念

示例如下，假设这个表有联合索引INDEX(zipcode, lastname, age)

SELECT * FROM people
WHERE zipcode='95054'
AND lastname LIKE '%etrunia%'
AND age > 10;

1.最左匹配原则( 索引查询,排序)

查询

最左边的为起点任何连续的索引都能匹配上。遇到范围查询(>、<、between、like)就会停止匹配。

如(zipcode),(zipcode(5)) (zipcode, lastname, age) 能匹配

但(lastname, age) 就不行.

排序

index(a,b,c)

select * from table_name order by a,b,c limit 10;

因为索引(a,b,c)就是按照order排序，可以直接从索引中提取数据，然后回表操作取出该索引中不包含的列就好了

order by的后面的顺序必须按照索引列的顺序给出，比如

select * from table_name order by b,c,a limit 10;

这种颠倒顺序的没有用到索引

select * from table_name order by a limit 10;
select * from table_name order by a,b limit 10;

这种用到部分索引

select * from table_name where a =1 order by b,c limit 10;

联合索引左边列为常量，后边的列排序可以用到索引

2.索引下推-ICP-(Index Condition Pushdown)

对索引中包含的字段先做判断，过滤掉不符合条件的记录,减少回表记录

将过滤表达式尽可能移动至靠近数据源的位置，以使真正执行时能直接跳过无关的数据。

索引遇到范围查询(>、<、between、like)就会停止匹配。(后面解释)

SELECT * FROM people
WHERE zipcode='95054'
AND lastname LIKE '%etrunia%'
AND age > 10;

不用ICP，只使用最左匹配原则。那么只能使用联合索引的zipcode，回表查询返回所有符合lastname 或 age 的记录,回表记录不能有效去除。

使用ICP，除了匹配zipcode的条件之外，额外匹配联合索引的lastname，看其是否符合where条件中的'%etrunia%'，然后进行回表。如此一来，使用联合索引就可以尽可量排除不符合where条件的记录。这就是ICP优化的真谛。

前者使用条件 zipcode = ‘95054’ 回表后,返回值.

后者使用条件 自拍code=‘95054’ & lastname like ‘%erunith%’ 回表

如何确定某条语句使用了索引条件下推ICP？

Explain sql语句时的输出项的Extra会显示Using index condition

3.回表

index(name)
clustered index(id)
select age from table_name where name = "lihua";

先通过辅助索引查询(name)，查询到 辅助索引-聚簇索引(name-id)，再根据聚簇索(id)引查对应的值(age)，需要查询两次，也称为回表查询。

4.索引覆盖

覆盖索引就是指索引中包含了查询中的所有字段，这种情况下就不需要再进行回表查询了.

1. index(a,b,c)
2. clustered index(a) (聚簇索引默认为主键)
3. index(b)
4. index(b,c)
select b,c from table_name where a="wh"; // √ 聚簇索引 2 无需回表
select a,b from table_name where a="dd" //√ 聚簇索引 2 无需回表
select a,b,d from tn where b ='dd' // × 能命中普通索引3,索引3包含了主键a ,所以可获取(a,b) 但d 需要通过a回表查
select a,b,c from tn where a=1 and b = 'd' and c='d' //√ 命中聚簇索引2,普通索引1
select b,c from tn where b ="dd" //√ 命中索引4 索引覆盖

explain的输出结果Extra字段为Using index时，能够触发索引覆盖。

5.索引选择性

选择性是指，不重复的索引值和数据表的记录总数（N）的比值，范围从1/N 到 1 之间。

索引与查询效率正相关，选择性高的索引可以在查询时过滤掉更多的行。

唯一索引和聚簇索引的选择性是 1，性能是最好的。

select * from payment where id = 2 and customer_id = 584;

是应该创建一个（id，customer_id）索引还是颠倒一下顺序？(最左匹配原则)

解决思路：确定哪个列的选择性更高。

运行以下语句：

select 
count(distinct id) / count(*) as id_selectivity, 
count(distinct customer_id) / count(*) as customer_selectivity, 
count(*) 
from payment

如果id_selectivity>customer_selectivity 则创建 (id,customer_id)索引

九 索引优化与失效 (结合七)

1. 最左匹配原则

   mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配;

   index(a,b,c)
   where a=1 and b='d' and c>3; √ 有效
   where a=1 and c>3 and b='d'; x 遇到范围查询(>、<、between、like)就停止匹配;到c>3时就失效了
   

2. in 和 = 内可以乱序

   index(a,b,c)
   where b ='d' and a=1 and c>3; √  查询优化器会优化
   

3. 函数失效

   index(create_time)
   WHERE DATE(create_time) = '2021-04-21';
   

4. or 索引失效

   WHERE `name` = '张三' OR height = '175';
   

5. 数据类型的隐形转换

   a char;
   index(a)
   where a = '1'; √
   where a = 1 ; x
   

6. 避免使用 Select *

   table(id,a,b,c,d)
   index(b,c,d)
   
   只查b,c,d
   select * from table where b = 'dddd' ;//全表扫描
   select b,c,d from table where b='dddd';// 索引覆盖,走二级索引且无回表
   
   另 :
   连接查询时，* 无法进入缓冲池
   

十 explain 与索引

Explain sql语句时的输出项的Extra会显示Using index condition

十一 参考:

innodb 建立聚簇索引

MySQL聚簇索引和非聚簇索引的原理及使用

一分钟明白MySQL聚簇索引和非聚簇索引

Java面试题篇·MySQL索引连环18问！(面试必备八股文)

★MySQL索引条件下推

★Mysql最左匹配原则

索引优化：索引选择性

回表与覆盖索引，索引下推(索引下推)