MySQL索引
- 索引介绍
- 索引原理与分析
- 组合索引
- 索引失效分析
索引介绍
- 什么是索引
索引：包括聚集索引、覆盖索引、组合索引、前缀索引、唯一索引等，没有特别说
明，默认都是使用B+树结构组织（多路搜索树，并不一定是二叉的）的索引。
存储：存储在磁盘文件中
索引是帮助MySQL高效获取数据的数据结构。更通俗的说，数据库索引好比是一本书前面的目录，能加快数据库的查询速度。 - 索引的优势和劣势
优势：提高数据的检索效率，降低数据库的IO成本；对数据进行排序
劣势： 占据磁盘空间，降低更新表的效率 - 索引的分类
单列索引、组合索引、全文索引、空间索引 - 索引的使用
创建索引
CREATE INDEX index_name ON table(column(length)) ;
ALTER TABLE table_name ADD INDEX index_name (column(length));
//唯一索引：
CREATE UNIQUE INDEX index_name ON table(column(length)) ;
alter table table_name add unique index index_name(column);
//全文索引：
CREATE FULLTEXT INDEX index_name ON table(column(length)) ;
alter table table_name add fulltext index_name(column)
//组合索引：
ALTER TABLE article ADD INDEX index_titme_time (title(50),time(10)) ;
删除索引：
DROP INDEX index_name ON table
查看索引：
SHOW INDEX FROM table_name \G
索引原理与分析
- 索引的存储结构
索引的存储结构：
不同的存储引擎，会使用不同的索引
MyISAM和InnoDB存储引擎：只支持B+ TREE索引， 也就是说默认使用BTREE，不能够更换
MEMORY/HEAP存储引擎：支持HASH和BTREE索引
- B树和B+树：
B树
B树和B+树的最大区别在于非叶子节点是否存储数据的问题。
- B树是为了磁盘或其它存储设备而设计的一种多叉（下面你会看到，相对于二叉，B树每个内结点有多个分支，即多叉）平衡查找树。
B树的高度一般都是在2-4这个高度，树的高度直接影响IO读写的次数。
如果是三层树结构---支撑的数据可以达到20G，如果是四层树结构---支撑的数据可以达到几十T
B树是非叶子节点和叶子节点都会存储数据。
B+树只有叶子节点才会存储数据，而且存储的数据都是在一行上，而且这些数据都是有指针指向的，也
就是有顺序的。 索引列 order by
- 非聚集索引（MyISAM）
B+树的索引不在一起就是非聚集索引
非聚集索引一般包含主键索引 和辅助索引都会存储指针的值。 - 聚焦索引（InnoDB）
数据和索引在一起就是聚焦索引。
主键索引的叶子节点会存储数据行。辅助索引只会存储主键值。
主键索引：1.InnoDB 要求表必须有主键(MyISAM 可以没有),如果没有显式指定,则 MySQL系统会自动选择一个可以唯一标识数据记录的列作为主键,如果不存在这种列,则MySQL 自动为 InnoDB 表生成一个隐含字段作为
主键,类型为长整形。
辅助索引：2.第二个与 MyISAM 索引的不同是 InnoDB 的辅助索引 data 域存储相应记录主键的值而不是地址。换句话说,InnoDB 的所有辅助索引都引用主键作为 data 域。
select * from user where name='Alice' 回表查询 检索两次 非主键索引 --- pk---索引--->数据
select id,name from user where name='Alice' 不需要回表 在辅助索引树上就可以查询到了 覆盖索
引(多用组合索引)
引申:为什么不建议使用过长的字段作为主键?
因为所有辅助索引都引用主索引,过长的主索引会令辅助索引变得过大。
同时,请尽量在 InnoDB 上采用自增字段做表的主键。
-
MyISAM 和 InnoDB的存储结构
InnoDB 主键索引
辅助索引
组合索引
-
哪些情况需要创建索引
- 主键自动建立唯一索引
- 频繁作为查询条件的字段应该创建索引
- 多表关联查询中，关联字段应该创建索引 on 两边都要创建索引
- 查询中排序的字段，应该创建索引
- 频繁查找字段 覆盖索引
- 查询中统计或者分组字段，应该创建索引 group by
-
哪些情况不需要创建索引
- 表记录太少
- 经常进行增删改操作的表
- 频繁更新的字段
- where条件里使用频率不高的字段
-
为什么儿使用组合索引
为了节省mysql索引存储空间以及提升搜索性能
ALTER TABLE 'table_name' ADD INDEX index_name('col1','col2','col3'） -
索引原则
- 一颗索引树上创建3个索引 ： 省空间
- 三颗索引树上分别创建1个索引， 更容易实现覆盖索引
- 遵循最左前缀原则
- 前缀 索引 like a%
- 从左到右匹配直到遇到范围查询 > < between like
- 创建组合索引(a,b,c,d)where a=1 and b=1 and c>3 and d=1到 a>3 停下来了
- (a,b,d,c)create index idx_com on t1(a,b,d,c);
索引失效分析
- 查看执行计划 explain
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)
);
--创建普通索引
mysql> alter table tuser add index idx_dep(dep);
--创建唯一索引
mysql> alter table tuser add unique index idx_loginname(loginname);
--创建组合索引
mysql> alter table tuser add index idx_name_age_sex(name,age,sex);
--创建全文索引
mysql> alter table taddr add fulltext ft_addr(addr);
1. id
- 每个 SELECT语句都会自动分配的一个唯一标识符.
- 表示查询中操作表的顺序，有三种情况：
-- id相同：执行顺序由上到下
-- id不同：如果是子查询，id号会自增，id越大，优先级越高。
-- id相同的不同的同时存在 - id列为null的就表示这是一个结果集，不需要使用它来进行查询。
2. select_type（重要）
- 查询类型，主要用于区别普通查询、联合查询(union、union all)、子查询等复杂查询。
simple
表示不需要union操作或者不包含子查询的简单select查询。有连接查询时，外层的查询为simple，且只有一个
primary
一个需要union操作或者含有子查询的select，位于最外层的单位查询的select_type即为primary。且只有一个
subquery
除了from字句中包含的子查询外，其他地方出现的子查询都可能是subquery
dependent subquery
与dependent union类似，表示这个subquery的查询要受到外部表查询的影响
union
union连接的两个select查询，第一个查询是PRIMARY，除了第一个表外，第二个以后的表select_type都是union
dependent union
与union一样，出现在union 或union all语句中，但是这个查询要受到外部查询的影响
union result
包含union的结果集，在union和union all语句中,因为它不需要参与查询，所以id字段为null
derived
from字句中出现的子查询，也叫做派生表，其他数据库中可能叫做内联视图或嵌套select
3. table
- 显示的查询表名，如果查询使用了别名，那么这里显示的是别名
- 如果不涉及对数据表的操作，那么这显示为null
- 如果显示为尖括号括起来的就表示这个是临时表，后边的N就是执行计划中的id，表示结果来自于这个查询产生。
- 如果是尖括号括起来的<union M,N>，与类似，也是一个临时表，表示这个结果来自于union查询的id为M,N的结果集。
4. 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，表中只有一行数据或者是空表。
const，使用唯一索引或者主键，返回记录一定是1行记录的等值where条件时，通常type是const。其他数据库也叫做唯一索引扫描
eq_ref，关键字:连接字段主键或者唯一性索引。都只能匹配到后表的一行结果
ref，针对非唯一性索引，使用等值（=）查询非主键。或者是使用了最左前缀规则索引的查询。
fulltext，全文索引检索
ref_or_null，增加了null值的比较
unique_subquery，用于where中的in形式子查询，子查询返回不重复值唯一值
index_subquery，用于in形式子查询使用到了辅助索引或者in常数列表，子查询可能返回重复值，可以使用索引将子查询去重。
range，索引范围扫描，常见于使用>,<,is null,between ,in ,like等运算符的查询中。
index_merge，表示查询使用了两个以上的索引，最后取交集或者并集
index，关键字：条件是出现在索引树中的节点的。可能没有完全匹配索引。索引全表扫描
ALL, 全表扫描数据文件
5. possible_keys: 此次查询中可能选用的索引，一个或多个、
6. key : 查询真正使用到的索引
7. key_len: 处理查询的索引长度,key_len只计算where条件用到的索引长度，而排序和分组就算用到了索引，也不会计算到key_len中
8. ref
- 如果是使用的常数等值查询，这里会显示const
- 如果是连接查询，被驱动表的执行计划这里会显示驱动表的关联字段
- 如果是条件使用了表达式或者函数，或者条件列发生了内部隐式转换，这里可能显示为func
9. rows: 执行计划中估算的扫描行数，不是精确值（InnoDB不是精确的值，MyISAM是精确的值，主要原因是InnoDB里面使用了MVCC并发机制）
10. Extra
- using temporary: 使用了临时表存储中间结果,MySQL在对查询结果order by和group by时使用临时表; 临时表可以是内存临时表和磁盘临时表，执行计划中看不出来，需要查看status变量，used_tmp_table，used_tmp_disk_table才能看出来。
- no tables used : 使用not in()形式子查询或not exists运算符的连接查询，这种叫做反连接
- using filesort : MySQL中无法利用索引完成的排序操作称为“文件排序”
- using index
- using where: 存储引擎返回的记录并不是所有的都满足查询条件，需要在server层进行过滤。
- firstmatch(tb_name): 5.6.x开始引入的优化子查询的新特性之一，常见于where字句含有in()类型的子查询。如果内表的数据量比较大，就可能出现这个
- loosescan(m..n): 在in()类型的子查询中，子查询返回的可能有重复记录时，
就可能出现这个- filtered : 5.7之后的版本默认就有这个字段，不需要使用explain extended了.这个字段表示存储引擎返回的数据在server层过滤后，剩下多少满足查询的记录数量的比
例，注意是百分比，不是具体记录数。
参考网站
https://segmentfault.com/a/1190000008131735
https://blog.csdn.net/rewiner120/article/details/70598797
- 索引失效分析
1.全值匹配 *
2.最佳左前缀法则 组合索引->带头索引不能死，中间索引不能断
3.不要在索引上做计算
4.范围条件右边的列失效
5.尽量使用覆盖索引 也就是索引列和查询列一致，减少select *
6.索引字段上不要使用不等,会导致索引失效而转向全表扫描
7.主键索引字段上不可以判断null
主键字段上不可以使用 null
索引字段上使用 is null / is not null 判断时，可使用索引
8.索引字段使用like不以通配符开头
9.索引字段字符串要加单引号
10.索引字段不要使用or索引字段使用 or 时，会导致索引失效而转向全表扫描
总结
image.png
网友评论