索引的自我介绍
什么是索引
官方的介绍我是帮助MySQL高效获取数据的数据结构。好比是本文的目录,能加快数据库的查询速度。
优势和劣势
优势
-
检索
可以提高数据检索的效率,降低数据库的IO成本,比如书的目录 -
排序
通过索引列对数据进行排序,降低数据排序成本,降低cpu消耗
被索引的列会自动排序,包括单列索引和组合索引,组合索引的排序更复杂一些
在使用 order by 索引列的时候,效率会高很多使用where索引列,结果集的过滤实际是在存储引擎层处理的,效果更好
劣势
- 占用磁盘空间
- 降低表的更新效率
每次对表进行insert,update,delete操作的时候,不仅要保存数据文件,还要保存或者更新对应的索引文件
索引的分类
- 单列索引
- 组合索引
- 全文索引
全文索引是mysql 5.5之后支持的,一般都会使用es做全文检索
- 空间索引
- 位图索引(Oracle独有)
索引的使用
创建索引
- 单列索引之普通索引
CREATE INDEX index_name ON table_name(column(length)) ;
ALTER TABLE table_name ADD INDEX index_name (column(length)) ;
- 单列索引之唯一索引
CREATE UNIQUE INDEX index_name ON table_name(column(length)) ;
ALTER TABLE table_name ADD UNIQUE INDEX index_name(column);
- 单列索引之全文索引
CREATE FULLTEXT INDEX index_name ON table_name(column(length)) ;
ALTER TABLE table_name ADD FULLTEXT index_name(column);
- 组合索引
ALTER TABLE table_name ADD INDEX index_name (column1(length),column2(length)) ;
删除索引
DROP INDEX index_name ON table_name;
查看索引
SHOW INDEX FROM table_name;
索引原理分析
索引的存储结构
- 索引是在存储引擎中实现的,也就是说不同的存储引擎,会使用不同的索引
- MyISAM和InnoDB存储引擎:只支持B+ TREE索引, 也就是说默认使用BTREE,不能够更换
- MEMORY/HEAP存储引擎:支持HASH和BTREE索引
B树和B+树
B树是一种多叉平衡查找树
B树的高度一般都是在2-4这个高度,树的高度直接影响IO读写的次数
如果是三层树结构—支撑的数据可以达到20G,如果是四层树结构—支撑的数据可以达到几十T
B树和B+树区别
B树和B+树的最大区别在于非叶子节点是否存储数据的问题。
B树是非叶子节点和叶子节点都会存储数据。
B+树只有叶子节点才会存储数据,而且存储的数据都是在一行上,而且这些数据都是有指针指向的,也就是有顺序的。
非聚集索引(MyISAM)
主键索引
MyISAM的B+树叶子节点存储的数据是数据的指针值
还记得之前说过的MyISAM存储引擎的文件是怎么存放的吗?
myi存储的是索引文件,myd存储的数据文件,数据文件和索引文件分开存放
如何通过主键索引来查找这条记录的全部内容呢?
🌰select * from table where id = 18
- 在myi索引文件中通过索引树找到18的叶子节点
- 在myd数据文件中,通过18索引存储的内存地址找到此记录
辅助索引(次要索引)
存储方式同主键索引
聚集索引(InnoDB)
主键索引
主键索引的叶子节点存储的此记录的全部内容
我们平常在创建表的时候都会为该表建立一个主键,那么如果没有创建主键会怎么样呢?
MySQL会找这个表的唯一字段当主键,如果没有,那么就会建立一个伪列为主键,所以为了优化效率,省去此步骤,我们在创建表的时候都要建立一个主键字段
辅助索引(次要索引)
叶子节点存储的内容是主键
如果是非主键查询,需要搜索2次索引树,第一次搜索辅助索引树,找到存储的主键值,再搜索主键索引树得到全部内容
🌰 select * from t where name = “Alice”
- 查询name索引树,找到索引得到主键
- 查询主键索引主树,得到全部记录
为了提高效率,避免回表,我们可以将需要查询的字段建立一个组合索引完成索引覆盖,就不用回表了
索引的使用场景
哪些情况需要创建索引
- 主键自动建立唯一索引
- 频繁作为查询条件的字段应该创建索引
- 多表关联查询中,关联字段应该创建索引
on 两边都要创建索引 - 查询中排序的字段,应该创建索引
B + tree 有顺序 - 覆盖索引
不需要回表 建立组合索引 - 统计或者分组的字段
哪些情况不需要创建索引
- 表记录太少 索引是要有存储的开销
- 频繁更新的字段, 索引要维护
- 查询字段使用频率不高
为什么使用组合索引
什么是组合索引呢?
由多个字段组成的索引, 使用顺序就是创建的顺序
ALTER TABLE 'table_name' ADD INDEX index_name(col1,col2,col3)
组合索引的索引树上,一个叶子节点上有多个字段,可以节省空间,在进行select * 或者查找某些列的时候,可以进行索引覆盖,优化查询效率
组合索引的使用
组合索引的使用遵循最左前缀原则
- 前缀索引
使用like时,如果是常量%
则可以使用索引,如果是%常量
则索引失效 - 最左前缀
从左向右匹配直到遇到范围查询 > < between 时,索引失效
🌰
# 创建表 t1
CREATE TABLE t1 (
id INT PRIMARY KEY,
a INT,
b INT,
c INT,
d INT
);
# 创建组合索引
ALTER TABLE t1 add index idx_a_b_c_d(a,b,c,d);
# 查看索引情况
SHOW INDEX FROM t1
Seq_in_index
显示了我们创建组合索引中column的顺序
执行explain select * from t1 where a=1 and b=1 and c=1 and d=1 ;
可以看到是使用了索引的,key_len 是20,我们使用了全部4个索引
执行 explain select * from t1 where a=1 and b=1 and c>1 and d=1 ;
可以看到key_len是10,我们只使用了2个索引
即使执行 explain select * from t1 where a=1 and b=1 and d=1 and c>1 ;
也是使用2个索引,虽然SQL中d在前面,但是索引中建立的顺序是c在前面,查询的时候还是会根据索引建立顺序
索引失效
首先我们要先学会看SQL的执行计划
查看执行计划
参数说明
explain出来的信息有10列,分别是
id、select_type、table、type、possible_keys、key、key_len、ref、rows、Extra
创建案例表
--用户表
CREATE TABLE tuser(
id INT PRIMARY KEY,
loginname VARCHAR(100),
name VARCHAR(100),
age INT,
sex CHAR(1),
dep INT,
address VARCHAR(100)
);
--部门表
CREATE TABLE tdep(
id INT primary key,
name VARCHAR(100)
);
--地址表
CREATE TABLE taddr(
id INT primary key,
addr VARCHAR(100)
);
--创建普通索引
ALTER TABLE tuser ADD INDEX idx_dep(dep);
--创建唯一索引
ALTER TABLE tuser ADD UNIQUE INDEX idx_loginname(loginname);
--创建组合索引
ALTER TABLE tuser ADD INDEX idx_name_age_sex(name,age,sex);
--创建全文索引
ALTER TABLE taddr ADD FULLTEXT ft_addr(addr);
id
- 每个 SELECT语句都会自动分配的一个唯一标识符
- 表示查询中操作表的顺序
- id相同:执行顺序由上到下
- id不同:如果是子查询,id号会自增,id越大,优先级越高。
- id列为null的就表示这是一个结果集,不需要使用它来进行查询
select_type
查询类型,主要用于区别普通查询、联合查询(union、union all)、子查询等复杂查询
-
simple
表示不需要union操作或者不包含子查询的简单select查询。有连接查询时,外层的查询为simple,且只有一个 -
primary
一个需要union操作或者含有子查询的select,位于最外层的单位查询的select_type即为primary。且只有一个 -
subquery
除了from字句中包含的子查询外,其他地方出现的子查询都可能是subquery
🌰explain select (select name from tuser) from tuser ;
-
derived
from字句中出现的子查询,也叫做派生表
🌰explain select * from (select * from tuser where sex='1');
-
dependent subquery
表示这个subquery的查询要受到外部表查询的影响
🌰explain select id,name,(select name from tdep a where a.id=b.dep) from tuser b;
-
union
union连接的两个select查询,第一个查询是PRIMARY,除了第一个表外,第二个以后的表select_type 都是union
🌰explain select * from tuser where sex='1' union select * from tuser where sex='2';
-
dependent union
与union一样,出现在union 或union all语句中,但是这个查询要受到外部查询的影响 -
union result
包含union的结果集,在union和union all语句中,因为它不需要参与查询,所以id字段为null
🌰explain select * from tuser where sex in (select sex from tuser where sex='1' union select sex from tuser where sex='2');
table
- 显示的查询表名,如果查询使用了别名,那么这里显示的是别名
- 如果不涉及对数据表的操作,那么这显示为null
- 如果显示为尖括号括起来的就表示这个是临时表,后边的N就是执行计划中的id,表示结果来自于这个查询产生。
- 如果是尖括号括起来的<union M,N>,与类似,也是一个临时表,表示这个结果来自于union查 询的id为M,N的结果集。
type
依次从好到差
system,const,eq_ref,ref,fulltext,ref_or_null,unique_subquery, index_subquery,range,index_merge,index,ALL
除了all之外,其他的type都可以使用到索引,除了index_merge之外,其他的type只可以用到一个索引,查询优化器会选择一个最优索引
最少要索引使用到range级别。
-
system
表中只有一行数据或者是空表。使用的时候很少见到
🌰explain select * from (select * from tuser where id=1) a;
-
const
使用唯一索引或者主键,返回记录一定是1行记录的等值where条件时,通常type是const。其他数据库也叫做唯一索引扫描
🌰explain select * from tuser where id=1;
explain select * from tuser where loginname = 'zy';
-
eq_ref
连接字段主键或者唯一索引。
此类型通常出现在多表的 join 查询, 表示对于前表的每一个结果, 都只能匹配到后表的一行结果。 并且查询的比较操作通常是 ‘=’, 查询效率较高
🌰explain select a.id from tuser a left join tdep b on a.dep=b.id;
-
ref
针对非唯一性索引,使用等值(=)查询非主键。或者是使用了最左前缀规则索引的查询
🌰
--非唯一索引
explain select * from tuser where dep=1;
--等值非主键连接
explain select a.id from tuser a left join tdep b on a.name=b.name;
--最左前缀
explain select * from tuser where name = 'zhaoyun';
思考 explain select * from tuser where sex = '1';
有没有使用索引?
答案是没有的,因为name,age,sex是组合索引,前面说过组合索引的使用需要符合最左前缀原则
-
fulltext
全文索引检索,要注意,全文索引的优先级很高,若全文索引和普通索引同时存在时,mysql不管代价,优先选择使用全文索引
🌰explain select * from taddr where match(addr) against('bei');
-
ref_or_null
与ref方法类似,只是增加了null值的比较。实际用的不多。 -
unique_subquery
用于where中的in形式子查询,子查询返回不重复值唯一值 -
index_subquery
用于in形式子查询使用到了辅助索引或者in常数列表,子查询可能返回重复值,可以使用索引将子查询 去重。 -
range
索引范围扫描,常见于使用>,<,is null,between ,in ,like等运算符的查询中。 -
index_merge
表示查询使用了两个以上的索引,最后取交集或者并集,常见and ,or的条件使用了不同的索引,官方排序这个在ref_or_null之后,但是实际上由于要读取所有索引,性能可能大部分时间都不如range -
index
条件是出现在索引树中的节点的。可能没有完全匹配索引
索引全表扫描,把索引从头到尾扫一遍,常见于使用索引列就可以处理不需要读取数据文件的查询、可以使用索引排序或者分组的查询。
🌰
--单索引
explain select loginname from tuser;
--组合索引
explain select age from tuser;
explain select loginname,age from tuser;
不会使用索引,回表了
- all
这个就是全表扫描数据文件,然后再在server层进行过滤返回符合要求的记录。
possible_keys
此次查询中可能选用的索引,一个或多个
key
查询真正使用到的索引,select_type为index_merge时,这里可能出现两个以上的索引,其他的 select_type这里只会出现一个。
key_len
- 用于处理查询的索引长度,如果是单列索引,那就整个索引长度算进去,如果是多列索引,那么查询不一定都能使用到所有的列,具体使用到了多少个列的索引,这里就会计算进去,没有使用到的列,这里不会计算进去
- 留意下这个列的值,算一下你的多列索引总长度就知道有没有使用到所有的列了
- key_len只计算where用到的索引长度,排序和分组就算用到了索引也不会计算到key_len中
ref
- 如果是使用的常数等值查询,这里会显示const
- 如果是连接查询,被驱动表的执行计划这里会显示驱动表的关联字段
- 如果是条件使用了表达式或者函数,或者条件列发生了内部隐式转换,这里可能显示为func
rows
这里是执行计划中估算的扫描行数,不是精确值(InnoDB不是精确的值,MyISAM是精确的值,主要原因是InnoDB里面使用了MVCC并发机制)
extra
这个列包含不适合在其他列中显示单十分重要的额外的信息,这个列可以显示的信息非常多,有几十种,常用的有
-
distinct
在select部分使用了distinct关键字 -
no tables used
不带from字句的查询或者From dual查询 -
使用not in()形式子查询或not exists运算符的连接查询,这种叫做反连接 即,一般连接查询是先查询内表,再查询外表,反连接就是先查询外表,再查询内表
-
using filesort
排序时无法使用到索引时,就会出现这个。常见于order by和group by语句中
说明MySQL会使用一个外部的索引排序,而不是按照索引顺序进行读取
MySQL中无法利用索引完成的排序操作称为“文件排序”
🌰explain select * from tuser order by address;
-
using index
查询时不需要回表查询,直接通过索引就可以获取查询的数据
表示相应的SELECT查询中使用到了覆盖索引(Covering Index),避免访问表的数据行,效率不错!
如果同时出现Using Where ,说明索引被用来执行查找索引键值
如果没有同时出现Using Where ,表明索引用来读取数据而非执行查找动作。
🌰explain select name,age,sex from tuser ;
-
using temporary
表示使用了临时表存储中间结果。
MySQL在对查询结果order by和group by时使用临时表
临时表可以是内存临时表和磁盘临时表,执行计划中看不出来,需要查看status变量, used_tmp_table,used_tmp_disk_table才能看出来
🌰explain select distinct a.id from tuser a,tdep b where a.dep=b.id;
-
using where
表示存储引擎返回的记录并不是所有的都满足查询条件,需要在server层进行过滤
--查询条件无索引
explain select * from tuser where address='beijing';
--索引失效
explain select * from tuser where age=1;
explain select * from tuser where id in(1,2);
- using index condition
查询条件中分为限制条件和检查条件,5.6之前,存储引擎只能根据限制条件扫描数据并返回,然后server层根据检查条件进行过滤再返回真正符合查询的数据。5.6.x之后支持ICP特性,可以把检查条件也下推到存储引擎层,不符合检查条件和限制条件的数据,直接不读取,这样就大大减少了存储引擎扫描的记录数量。extra列显示using index condition
🌰explain select * from tuser where name='asd';
索引失效分析
-
全值匹配我最爱
条件与索引一一对应
🌰explain select * from tuser where name='zhaoyun' and age=1 and sex='1';
-
最佳左前缀法则
组合索引中带头索引不能死,中间索引不能断
如果索引了多个列,要遵守最佳左前缀法则。指的是查询从索引的最左前列开始 并且不跳过索引中的列
错误🌰
带头索引死:explain select * from tuser where age=23;
中间索引断(带头索引生效,其他索引失效) -
不要在索引上做计算
-
范围条件右边的列失效
不能继续使用索引中范围条件(bettween、<、>、in等)右边的列 -
尽量使用覆盖索引
尽量使用覆盖索引(只查询索引的列),也就是索引列和查询列一致,减少select * -
索引字段上不要使用不等
索引字段上使用(!= 或者 < >)判断时,会导致索引失效而转向全表扫描 -
主键索引字段上不可以判断null
主键字段上不可以使用 null
索引字段上使用 is null 判断时,可使用索引 -
索引字段使用like不以通配符开头
索引字段使用like以通配符开头(‘%字符串’)时,会导致索引失效而转向全表扫描
like以通配符结束相当于范围查找,索引不会失效。与范围条件(bettween、<、>、in 等)不同的是:不会导致右边的索引失效。
问题:解决like ‘%字符串%’时,索引失效问题的方法? 使用覆盖索引可以解决。 -
索引字段字符串要加单引号
索引字段是字符串,但查询时不加单引号,会导致索引失效而转向全表扫描 -
索引字段不要使用or
索引字段使用 or 时,会导致索引失效而转向全表扫描
索引优化口诀
全职匹配我最爱,最左前缀要遵守
带头大哥不能死,中间兄弟不能断
索引列上少计算,范围之后全失效
LIKE百分写最右,覆盖索引不写星
不等空值还有or,索引失效要少用