mySQL索引
什么是索引
MySql官方对索引的定义: 索引(Index)是帮助MySql高效获取数据的数据结构。
所以索引的本质: 索引是数据结构
我们通俗、简单的理解: 排好序的快速查找数据结构
索引分类
单值索引、主键索引、唯一索引、复合索引 、全文索引等
基本语法
创建:
create [unique] index indexName on tableName (columnName(length))
alert myTableName add [unique] index [indexName] on (columnName(length))
删除:
drop index indexName on myTableName;
查看索引:
show index from tableName\G
(复习)存储引擎
show engines;
阿里巴巴开源的 AliSql 、AliRedis
存储引擎 | 事务 | 锁 | 支持的索引结构 | 不支持的索引结构 |
---|---|---|---|---|
InnoDB | 支持事务 | 行锁 | 支持 B-tree、Full-text 等索引 | 不支持 Hash 索引 |
MyISAM | 不支持事务 | 表锁 | 支持 B-tree、Full-text 等索引 | 不支持 Hash 索引 |
Memory | 不支持事务 | 表锁 | 支持 B-tree、Hash 等索引 | 不支持 Full-text 索引 |
索引结构
BTree索引、Hash索引、full-text全文索引、R-Tree索引
Btree类型的索引:b+树,层数越多,数据量指数级增长(我们就用它,因为innodb默认支持它)
Hash类型的索引:查询单条快,范围查询慢
mysql explain
mysql explain 的返回项:id , select_type , table , type ,possible_keys , key ,key_len , ref , rows ,Extra
下面我们一 一解绍
id – SQL片段的执行顺序
select 查询的***,包含一组数字。表示SQL片段的执行顺序。
id越大表示越先被执行 , null表示最后执行
有三种情况:
- id相同 :执行顺序由上到下;
- id不相同 :如果是子查询,id的序号会递增。id值越大,优先级越高,越先被执行。
- id相同和不同,同时存在 :
示例:
id不相同 :
先执行t3的子查询
再执行t1的子查询
最后执行t2查询
id相同和不同,同时存在
在所有组中,
id越大,优先级越高,越先执行;
id相同的同一组中,从上往下,按顺序执行。
select_type – 查询类型
查询类型 有 simple , primary ,subquery (子查询),derived (衍生), union,union_result
simple 简单查询
简单的select查询,查询中不包含子查询或union
primary 复杂查询,最后被执行的SQL片段
查询中包含若干复杂的子部分,最外层的查询则被标记为primary。
就是最后被执行的SQL片段。
可以理解为“鸡蛋壳”
subquery (子查询)
这个子查询是返回值的。与 derived 返回临时表,有本质的不同。
derived [dɪ’raɪvd] 衍生; 起源
在这里表示 跟在 from 后面的 select子查询返回的临时表 。
union
若第2个select出现在union之后,则被标记为union;
若union包含在from子询的子查询中,外层的select将被标记为:derived
union_result
两个union表查询结果的合并
type – 访问类型
访问种类:all , index ,range, ref, eq_ref, constant ,system
从最好 到 最差 依次是
system > constant > eq_ref > ref > range > index > all
system :
表只有一行记录,这是const类型的特例,平时不会出现,这个可以忽略不计
const :
表示通过索引一次就找到了,const用于比较primary key或者unique索引。因为只匹配一行数据,所以很快。如将主键置于where列表中,Mysql就能将该查询转换为一个常量。
id=1 可以只接确定一个具体的值。所以是const
由于t1子查询只能查询一行记录,所以最外层执行时,type就是system。
eq_ref
唯一性索引扫描,对于每个索引键,表中只有一条记录与之匹配。常见于主键或唯一索引扫描。
ref
非唯一性索引扫描,返回匹配某个单值的所有行。
本质上也是一种索引访问,它返回所有匹配某个单独值的行,
然后,它可能会找到多个符合条件的行,所以他应该属于查询和扫描的混合体。
一般情况下,能优化到ref,性能就很不错了。
range
只检索给定的范围的行,使用一个索引来选择行。ke列显示使用了哪个索引
一般就是在你的where语句中出现了between、<、>、in等查询
这种范围扫描过引扫描 比全表扫描要好,因为它只需要开始于索引的某一点,而结束另一点,不用扫描全部索引。
index 全索引扫描
Full Index Scan
全索引扫描。 index与All的区别是index类型中遍历索引树。 通过比All快,因为索引文件通常比数据文件小。
(也就是说虽然all和Index都是读全表,但是index是从索引中读取的,而all是从硬盘)
all 全表扫描
表示 查询是全表扫描,性能是最差的
possible_keys :
显示可以应用到这张表中的索引(就是索引名称),一个或多个;
查询涉及到的字段上若存在索引,则该索引将被列出,但不一定被实际使用
key : 实际使用的索引
查询中若使用了覆盖索引,则该索引仅出现在key列表中。
如果为null,则没有使用索引。
ref
显示索引的哪一列被使用了。
如果可能的话,是一个常数。哪些列或常量被用于查找索引列上的值。
rows
根据表统计信息及索引 选用情况,大致估算出找到所需的记录,所要读取的行数。
Extra
没有在其他列中显示,但十分要的额外信息。