索引

索引(index)是帮助MySQL高效获取数据的数据结构(有序)。在数据之外,数据库系统还维护着满足特定查找算法的数据结构,这些数据结构以某种方式引用(指向)数据,这样就可以在这些数据结构上实现高级查找算法,这种数据结构就是索引。

  • 无索引的查找:全表扫描(将整张表遍历一遍),性能极低。

  • 有索引的查找:数据库系统在存储数据的同时会维护一种数据结构(如二叉树),当需要查找时,利用该数据结构进行查找,性能较高。

  • 索引的优缺点

一. 索引结构

MySQL的索引是在存储引擎层实现的,不同的存储引擎有不同的结构。

  • 索引在存储引擎中的支持情况

  • 平常所说的索引,如果没有特别指明,一般都是说B+树结构组织的索引

1. B+树1.1 二叉树

  • 一种经典的数据结构。

  • 二叉树的两个缺点:

    • 顺序存储二叉树时,会形成一条链表,二叉树的深度很大,效率很低。
    • 二叉树的度不大于2,在数据库中存有大量数据的时候深度很大,效率很低。

1.2 红黑树

  • 一种自平衡的特殊二叉树。

  • 可以解决二叉树可能形成链表的缺点,但是依旧存在数据量大时深度很大的问题。

1.3 B树(平衡多路查找树)

  • 一种自平衡的树。可以解决二叉树的两个缺点。
  • 一个节点可以拥有两个以上的子节点。

1.4 B+树

  • B树的变种。
  • B+树与B树的区别:
    • 所有的元素都会出现在叶子节点。
    • 叶子节点形成了一条单向链表。

1.5 MySQL中的B+树

  • 在MySQL中,对B+树进行了优化。在原有基础上,叶子节点改为了双向循环链表,提高区间访问的性能。

2. Hash

哈希索引就是采用一定的Hash算法,将键值换算成新的Hash值,映射到对应的槽位上,然后存储在Hash表中。

如果两个(或多个)键值映射到同一个槽位上,产生了Hash冲突,可以通过链表解决。

2.1 Hash索引的特点

  1. 只能用于对等比较(=,in),不支持范围查询(between,>,<,…)。
  2. 无法利用索引完成排序操作。
  3. 查询效率高,通常只需要一次检索(不出现hash冲突),效率高于B+树。

2.2 存储引擎支持

在MySQL中,支持hash索引的是Memory引擎,而InnoDB中具有自适应hash功能,hash索引是存储引擎根据B+树索引在指定条件下自动构建的。

二. 索引分类

  • 在InnoDB存储引擎中,根据索引的存储形式,又可以分为以下两种:

  • 聚集索引的选取规则:
    • 如果存在主键,主键索引就是聚集索引。
    • 如果不存在主键,将适用第一个唯一(UNIQUE)索引作为聚集索引。
    • 如果不存在主键,或没有合适的唯一索引,则InnoDB会自动生成一个rowid作为隐藏的聚集索引。
  • 聚集索引和二级索引的示意图:

  • 在查找时,先走二级索引,找到对应的主键后,再走聚集索引,找到对应的整个行。(回表查询)

三. 索引语法1. 创建索引

create [unique|fulltext] index {索引名} on {表名} ({字段名},...);
  • unique 唯一索引 |fulltext 全文索引 |不加这两个则说明是常规索引。
  • 一个索引可以关联多个字段,如果一个索引只关联一个字段,叫单列索引,如果关联多个字段,叫联合索引(组合索引)
  • 联合索引的字段顺序是有讲究的。
  • 索引名一般的命名规则:idx _ 表名 _ 字段名

2. 查看索引

show index form {表名};

3. 删除索引

drop index {索引名} on {表名};

四. SQL性能分析

做性能分析是为了做SQL优化,SQL主要是做查询优化,因为查询操作比增删改多,查询优化的关键在于索引。

1. SQL执行频率

# 查看当前数据库的增删改查的访问频次show global status like 'Com_______';# 模糊匹配'Com'后面是7个下划线

  • 根据执行频率来判断SQL优化需要在哪方面进行,也就是说这个数据库哪个操作频率高就优化哪个操作。

2. 慢查询日志

慢查询日志记录了所有执行时间超过制定参数 (long_query_time,单位:秒,默认10秒) 的所有sql语句的日志

慢查询日志用于找到执行慢的sql语句,进行针对性优化。

2.1 开启慢查询日志

MySQL的慢查询日志默认没有开启,需要在MySQL的配置文件(/etc/my.cnf) 中配置。

  • 查询是否开启
show variables like 'slow_query_log';
  • 开启慢查询日志

在MySQL的配置文件(/etc/my.cnf) 中配置如下信息:

# 开启MySQL慢查询日志开关show_query_log = 1# 设置慢查询日志的时间为2秒,SQL语句执行时间超过2秒就会被记录long_query_time = 2

配置完毕后,需要重启服务器。

# 重启服务器systemctl restart mysqld

2.2 查看慢查询日志

# 慢查询日志存放地址 Linux下/var/lib/mysql/localhost-slow.log

3. profile详情

Show profiles 能够在做SQL优化时帮助我们了解时间都耗费到哪里去了。

3.1 查看MySQL是否支持profile操作

select @@have_profiling;

3.2 打开profile开关

# 查看是否打开select @@profiling;# 打开profile开关set [session|global] profiling = 1;
  • profile默认是关闭的。

  • [session|global] 可以指定是会话级别的还是全局的。

3.3 查看profile详情

# 查看每一条SQL的耗时基本情况show profiles;# 查看指定query_id的SQL语句各个阶段的耗时情况show profile for query query_id;# 查看指定query_id的SQL语句CPU的使用情况show profile cup for query query_id;
  • query_id指的是在profiles中的某一条指令的id,可以在show profiles中看到。

4. explain执行计划

explain 或者 desc命令获取MySQL如何执行select语句的信息,包括在select语句执行过程中表如何连接和连接的顺序。

# 直接在select语句之前加上关键字explain/descexplain select {字段列表} from {表名} where {条件};
  • explain执行计划各字段含义

五. 索引使用1. 最左前缀法则

  • 如果索引了多列(联合索引),要遵循最左前缀法则。最左前缀法则是指查询从索引的最左列开始,并且不跳过索引中的列。

  • 如果跳跃某一列,索引将部分失效(后面的字段索引失效)

  • 查询时左边字段存在即符合最左前缀法则,不管它在代码中的位置。

2. 范围查询

  • 联合索引中,出现范围查询(> , <),范围查询右侧的列索引失效
  • 用(>= , <=)不会出现失效情况。

3. 索引列运算

  • 不要在索引列上进行运算操作,否则索引将失效

4. 字符串不加引号

  • 字符串类型字段使用时,不加引号,索引将失效

5. 模糊查询

  • 如果是尾部进行模糊查询,索引不会失效;如果是头部进行模糊查询,索引会失效

6. or连接的条件

  • 用or分割开的条件,如果or前的条件中的列有索引,二后面的列没有索引,那么涉及的索引都不会被用到
  • 只要把没有索引的建立一个索引就可以解决失效问题。

7. 数据分布影响

  • 如果MySQL评估使用索引比全表更慢,则不使用索引

8. SQL提示

SQL提示是优化数据库的一个重要手段。在SQL语句中加入一些人为的提示来达到优化操作的目的。

8.1 use index

  • 使用指定索引(建议)
select * from {表名} use index({索引名}) where...;

8.2 ignore index

  • 不使用某个索引
select * from {表名} ignore index({索引名}) where...;

8.3 force index

  • 使用指定索引(必须)
select * from {表名} force index({索引名}) where...;

9. 覆盖索引

  • 尽量使用覆盖索引(查询使用了索引,并且需要返回的列,在该索引中已经全部能够找到),减少使用select *。

  • 使用覆盖索引和没有使用覆盖索引,在explain中的Extra列有不一样的提示:

    • (没使用)using index condition : 查找使用了索引,但是需要回表查询数据。
    • (使用了)using where; using index : 查找使用了索引,但是需要的数据都在索引列中能够找到,所以不需要回表查询数据。
  • 覆盖索引直接在二级索引中获取了返回所需的所有数据,所以不需要回表查询,查询速度快。

  • 如果不是覆盖查询,在二级索引中查询到数据后,还需要拿到对应数据的主键,到聚焦索引中查询行数据,这就叫回表查询,所以速度慢。

10. 前缀索引

当字段类型为字符串时,有时候需要存储很长的字符串,如果建立索引,索引会变得很大,浪费大量磁盘IO,影响查询效率。

此时可以只用字符串的一部分前缀来建立索引(前缀索引),可以大大节约索引空间,从而提高效率。

10.1 创建前缀索引

create index {索引名} on {表名}({字段名}({前缀的字符数}));

10.2 前缀长度的选择

  • 可以根据索引的选择性来决定。

  • 选择性:不重复的索引值和数据表的记录总数的比值。索引选择性越高,效率越高。唯一索引的选择性是1,是性能最好的。

  • 求选择性:

select count(distinct substring({字段名},1,{截取长度}))/count(*) from {表名};

11. 单列索引和联合索引

  • 单列索引:一个索引只包含单个列

  • 联合索引:一个索引包含了多个列

  • 在业务场景中,如果存在多个查询条件,考虑针对查询字段建立索引时,建议使用联合索引。

  • 联合索引的存储结构:

六. 索引设计原则