Go after
0%

MySQL-索引

发表于 更新于 分类于 阅读次数：
本文字数： 4.5k 阅读时长 ≈ 4 分钟

索引原理

索引

索引介绍

索引优劣势

优点：提高查询速度，以索引为条件。索引有序，可以用查找算法快速找到。否则只能遍历。

缺点：额外占存储空间，影响更新表效率。增删操作不应更新数据，还会更新索引。

索引分类

主键索引：主键自带

单列索引：普通索引，唯一索引，全文索引（鸡肋，对文本的索引。ES的效率更好）

组合索引：组合多个列

空间索引

位图索引：Oracle

索引语法

创建

普通索引:

1
2
CREATE INDEX index_name ON table(column(length)) ; 
ALTER TABLE table_name ADD INDEX index_name (column(length)) ;

唯一索引

1
2
CREATE UNIQUE INDEX index_name ON table(column(length)) ; 
alter table table_name add unique index index_name(column);

组合索引

1
ALTER TABLE article ADD INDEX index_titme_time (title(50),time(10)) ;

删除

1
DROP INDEX index_name ON table

查看

1
SHOW INDEX FROM table_name

例子：

show index from table_name

空间复杂度：O(1)

排序方式：In-place （占用常数内存，不占用额外内存）

稳定性：稳定

Implementation:Primary

主键索引Primary 索引方式

Non_unique key_name seq_in_index coulmn_name
主键 0 primary 1 id btree（B+tree）
唯一索引 0 ids_loginname 1 loginname btree（B+tree）
组合索引 1 ids_age_sex_name 1 age btree（B+tree）
组合索引 1 ids_age_sex_name 2 sex btree（B+tree）
组合索引 1 ids_age_sex_name 3 name btree（B+tree）
普通索引 1 ids_dep 1 dep btree（B+tree）

索引原理

索引使用的数据结构

数据量：三层20G，四层几十T

树演示网址：https://www.cs.usfca.edu/~galles/visualization/Algorithms.html

B+tree

特点：

  • 所有数据存储在叶子节点。
  • 叶子节点排成了一个链表
  • 叶子节点的链表顺序取出可得到有序的数据

Btree

特点 ：

  • 数据存储在叶子节点和非叶子节点上。
  • 无链表结构

索引树：

每建立一个索引会建立一颗索引树

非聚集索引-MyISAM

主键索引：存储主键和指向数据的地址。

辅助索引：存储辅助索引值和指向数据的地址。

聚集索引-InnoDB

主键索引

存储主键值和数据

索引是排序后建立的。主键的创建最好用自增整数。不要用大字符串比如 uuid —- 》 雪花算法 snowflflakes

辅助索引

存储索引值和主键值

组合索引

存储组合索引中的各个值和主键值

组合索引优点：一定程度上避免回表。回表：组合索引树没有要查询的列数据，根据组合索引树的主键回到主键索引树查找数据。

索引使用场景

什么情况建立索引

  • 主键自动创建主键索引
  • 表数据量大，100万条。
  • 频繁作为查询字段
  • 多表关联查询，on两端都要建立索引
  • 查询中排序的字段创建B+Tree，B+Tree可直接有序取出
  • 覆盖索引，使用组合索引防止回表
  • 统计或分组字段，应该创建索引

什么情况没必要建立索引

  • 表数据量少

  • 频繁更新，索引需要维护

  • 查询字段使用频率低

索引的执行

数据量少时，返回的数据量超过一半时，不使用索引。数据量多时，返回的数据量超过1/3不使用索引。

组合索引的创建顺序是使用顺序。

最左前缀原则:

  1. 前缀索引

    like 常量% 使用索引

    like %常量不使用索引

  2. 最左前缀

​ 按照索引的建立顺序搜索，范围查询（><between）后的索引失效。

where id =1 and name =”han” and a > 1 and b =1

查看执行 计划

参数说明

1
id、select_type、table、type、possible_keys、key、key_len、ref、rows、Extra

id:

  • 每个 SELECT语句都会自动分配的一个唯一标识符.
  • id相同：执行顺序由上到下,id不同：如果是子查询，id号会自增，id越大，优先级越高。id相同的不同的同时存在
  • id列为null的就表示这是一个结果集，不需要使用它来进行查询。
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
  • union result:包含union的结果集，在union和union all语句中,因为它不需要参与查询，所以id字段为null
  • derived:from字句中出现的子查询，也叫做派生表，其他数据库中可能叫做内联视图或嵌套select

table:

  • 显示的查询表名，如果查询使用了别名，那么这里显示的是别名
  • 如果不涉及对数据表的操作，那么这显示为null
type:
索引的类型（最好→最坏）：
1
system，const，eq_ref，ref，fulltext,ref_or_null,unique_subquery,index_subquery,range，index_merge，index，ALL

除了all之外，其他的type都可以使用到索引，除了index_merge之外，其他的type只可以用到一个索引

system：空表,数据只有一行。

const（重要）：使用主键索引或唯一索引,返回1行记录。

eq_ref（重要）:连接字段是主键或者唯一性索引。

ref（重要）:针对非唯一性索引，使用等值（=）查询非主键。或者是使用了最左前缀规则索引的查询。

index 列过滤，回表。比如组合索引没命中全部字段，回主键索引树查询数据。

fulltext：全文索引检索。全文索引的优先级很高，若全文索引和普通索引同时存在时，mysql优先选择使用全文索引

unique_subquery：用于where中的in形式子查询，子查询返回不重复值唯一值

index_subquery：用于in形式子查询使用到了辅助索引或者in常数列表，子查询可能返回重复值，可以使用索引将子查询去重。

range（重要）：索引范围扫描，常见于使用>,<,is null,between ,in ,like等运算符的查询中。

index_merge：表示查询使用了两个以上的索引，最后取交集或者并集，常见and ，or的条件使用了不同的索引

index（重要）：

  • 条件出现在索引树中的节点。可能没有完全匹配索引。
  • 索引全表扫描，把索引从头到尾扫一遍，常见于使用索引列就可以处理不需要读取数据文件的查询、可以使用索引排序或者分组的查询。
  • explain select loginname from tuser; #单索引
  • explain select age from tuser; #组合索引

all（重要）：是全表扫描数据文件，然后再在server(layer(解析器、优化器))层进行过滤返回符合要求的记录。explain select * from tuser;

possible_keys：此次查询中可能选用的索引，一个或多个。

key：查询真正使用到的索引，select_type为index_merge时，这里可能出现两个以上的索引，其他的select_type这里只会出现一个。

key_len：用于处理查询的索引长度，如果是单列索引，那就整个索引长度算进去，如果是多列索引，会计算具体使用到了多少个列的索引。

ref：

  • 如果是等值查询，这里会显示const
  • 如果是连接查询，被驱动表的执行计划这里会显示驱动表的关联字段
  • 如果是条件使用了表达式或者函数，或者条件列发生了内部隐式转换，这里可能显示为func

rows：估算的扫描行数，不是精确值（InnoDB不是精确的值，MyISAM是精确的值

extra（重要）：

包含不适合在其他列中显示单十分重要的额外的信息，这个列可以显示的信息非常多，有几十

种，常用的有下列几种：

  • using fifilesort（重要）：
    • 排序时无法使用到索引时，就会出现这个。常见于order by和group by语句
    • MySQL会使用一个外部的索引排序，而不是按照索引顺序进行读取。
    • MySQL中无法利用索引完成的排序操作称为“文件排序“
  • using index（重要）：
    • 查询时不需要回表查询，直接通过索引就可以获取查询的数据。
    • 表示相应的SELECT查询中使用到了覆盖索引
    • 如果同时出现Using Where ，说明索引被用来执行查找索引键值
    • 如果没有同时出现Using Where ，表明索引用来读取数据而非执行查找动作
  • using where（重要）：表示存储引擎返回的记录并不是所有的都满足查询条件，需要在server层进行过滤。
  • using index condition：5.6.x之后支持ICP特性，可以把检查条件（非主键索引）也下推到存储引擎层，不符合检查条件和限制条件的数据，直接不读取，这样就大大减少了存储引擎扫描的记录数量。

索引失效分析

口诀:

1
2
3
4
5
6
7
8
9
10
1.全值匹配我最爱
2.最佳左前缀法则
3.不在索引列上做任何操作（计算、函数、（自动or手动）类型转换），会导致索引失效而转向全表扫描
4.存储引擎不能使用索引中范围条件右边的列
5.尽量使用覆盖索引（只访问索引的查询（索引列和查询列一致）
6.mysql在使用不等于（！=或者<>）的时候无法使用索引会导致全表扫描(5.6之后主键是特例)
7.is null，is not null 也无法使用索引（有特例）
8.like以通配符开头（%abc..）mysql索引失效会变成全表扫描的操作（有特例）
9.字符串不加单引号索引失效
10.少用or，用它来连接时会索引失效

索引字段上使用 is null 判断时，可使用索引

解决like ‘%字符串%’时，索引失效问题的方法？使用覆盖索引可以解决。

explain select name , age,sex from tuser where name like ‘%a%’;

2.最佳左前缀法则

组合索引

1
带头索引不能死，中间索引不能断

如果索引了多个列，要遵守最佳左前缀法则。指的是查询从索引的最左前列开始 并且不跳过索引中的列。