欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

mySQL索引

程序员文章站 2024-01-20 20:45:16
...

什么是索引

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;
mySQL索引

阿里巴巴开源的 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表示最后执行

有三种情况:

  1. id相同 :执行顺序由上到下;
  2. id不相同 :如果是子查询,id的序号会递增。id值越大,优先级越高,越先被执行。
  3. id相同和不同,同时存在

示例:

id不相同 :
mySQL索引

先执行t3的子查询
再执行t1的子查询
最后执行t2查询

id相同和不同,同时存在

mySQL索引
在所有组中,
id越大,优先级越高,越先执行;
id相同的同一组中,从上往下,按顺序执行。

select_type – 查询类型

查询类型 有 simple , primary ,subquery (子查询),derived (衍生), union,union_result

simple 简单查询

简单的select查询,查询中不包含子查询或union

primary 复杂查询,最后被执行的SQL片段

查询中包含若干复杂的子部分,最外层的查询则被标记为primary。
就是最后被执行的SQL片段。
可以理解为“鸡蛋壳”

subquery (子查询)
这个子查询是返回值的。与 derived 返回临时表,有本质的不同。
mySQL索引

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就能将该查询转换为一个常量。
mySQL索引
id=1 可以只接确定一个具体的值。所以是const
由于t1子查询只能查询一行记录,所以最外层执行时,type就是system。

eq_ref

唯一性索引扫描,对于每个索引键,表中只有一条记录与之匹配。常见于主键或唯一索引扫描。

ref

非唯一性索引扫描,返回匹配某个单值的所有行。
本质上也是一种索引访问,它返回所有匹配某个单独值的行,
然后,它可能会找到多个符合条件的行,所以他应该属于查询和扫描的混合体。

一般情况下,能优化到ref,性能就很不错了。

mySQL索引

range

只检索给定的范围的行,使用一个索引来选择行。ke列显示使用了哪个索引
一般就是在你的where语句中出现了between、<、>、in等查询
这种范围扫描过引扫描 比全表扫描要好,因为它只需要开始于索引的某一点,而结束另一点,不用扫描全部索引。

index 全索引扫描

Full Index Scan 全索引扫描。 index与All的区别是index类型中遍历索引树。 通过比All快,因为索引文件通常比数据文件小。
(也就是说虽然all和Index都是读全表,但是index是从索引中读取的,而all是从硬盘)

all 全表扫描

表示 查询是全表扫描,性能是最差的

possible_keys :

显示可以应用到这张表中的索引(就是索引名称),一个或多个;
查询涉及到的字段上若存在索引,则该索引将被列出,但不一定被实际使用

key : 实际使用的索引

查询中若使用了覆盖索引,则该索引仅出现在key列表中。
如果为null,则没有使用索引。

ref

显示索引的哪一列被使用了。
如果可能的话,是一个常数。哪些列或常量被用于查找索引列上的值。

mySQL索引

rows

根据表统计信息及索引 选用情况,大致估算出找到所需的记录,所要读取的行数。

Extra

没有在其他列中显示,但十分要的额外信息。