阅读 190

细说mysql 索引&常用优化查询方案

一、索引类型

索引可以提升查询速度,会影响where查询,以及order by排序。这里大家容易对各种索引分类产生疑惑,其实就是按不同类型区分的,mysql索引分类如下:

  • 从索引存储结构划分:B Tree索引、Hash索引、FULLTEXT全文索引、R Tree索引
  • 从应用层次划分:普通索引、唯一索引、主键索引、复合索引
  • 从索引键值类型划分:主键索引、辅助索引(二级索引)
  • 从数据存储和索引键值逻辑关系划分:聚集索引(聚簇索引)、非聚集索引(非聚簇索引)

1. 普通索引

最基本的索引类型,基于普通字段建立的索引,没有任何限制。

2. 唯一索引

与"普通索引"类似,不同的就是:索引字段的值必须唯一,但允许有空值 。在创建或修改表时追加唯一 约束,就会自动创建对应的唯一索引。

3. 主键索引

它是一种特殊的唯一索引,不允许有空值。在创建或修改表时追加主键约束即可,每个表只能有一个主键。

4. 复合索引 (常用)

可以在多个列上建立索引,这种索引叫做组复合索引(组合索引),相比多个单一索引复合 索引所需的开销更小。

索引同时有两个概念叫做窄索引和宽索引,窄索引是指索引列为1-2列的索引,宽索引也就是索引列超过2列的索引,设计索引的一个重要原则就是能用窄索引不用宽索引,因为窄索引往往比组合索引更有效。

复合索引使用注意事项:

  • 何时使用复合索引,要根据where条件建索引,注意不要过多使用索引,过多使用会对更新操作效率有很大影响。
  • 如果表已经建立了(col1,col2),就没有必要再单独建立(col1);如果现在有(col1)索引,如果查询需要col1和col2条件,可以建立(col1,col2)复合索引,对于查询有一定提高。

5. 全文索引(一般用中间件替代)

查询操作在数据量比较少时,可以使用like模糊查询,但是对于大量的文本数据检索,效率很低。如果 使用全文索引,查询速度会比like快很多倍。在MySQL 5.6 以前的版本,只有MyISAM存储引擎支持全文索引,从MySQL 5.6开始MyISAM和InnoDB存储引擎均支持

和常用的like模糊查询不同,全文索引有自己的语法格式,使用 match 和 against 关键字,比如:

select * from user where match(name) against('aaa');
复制代码

全文索引使用注意事项:

  • 全文索引必须在字符串、文本字段上建立。
  • 全文索引字段值必须在最小字符和最大字符之间的才会有效。(innodb:3-84;myisam:4-

84)

  • 全文索引字段值要进行切词处理,按syntax字符进行切割,例如b+aaa,切分成b和aaa
  • 全文索引匹配查询,默认使用的是等值匹配,例如a匹配a,不会匹配ab,ac。如果想匹配可以在布尔模式下搜索a*

索引原理

MySQL官方对索引定义:是存储引擎用于快速查找记录的一种数据结构。需要额外开辟空间和数据维护 工作。

  • 索引是物理数据页存储,在数据文件中(InnoDB,ibd文件),利用数据页(page)存储。
  • 索引可以加快检索速度,但是同时也会降低增删改操作速度,索引维护需要代价。

1. 二分查找法

优点是等值查询、范围查询性能优秀,缺点是更新数据、新增数据、删除数据维护成本高。

2. Hash结构

根据键值 <key,value> 存储数据的结构。非常适合根据key查找value值,也就是单个key查询。其结构如下所示:

Hash索引可以方便的提供等值查询,但是对于范围查询就需要全表扫描了。

InnoDB提供的自适应哈希索引功能,如下介绍 :

InnoDB自适应哈希索引是为了提升查询效率,InnoDB存储引擎会监控表上各个索引页的查询,当InnoDB注意到某些索引值访问非常频繁时,会在内存中基于B+Tree索引再创建一个哈希索引,使得内存中的 B+Tree 索引具备哈希索引的功能,即能够快速定值访问频繁访问的索引页。

可以为为某些热点页建立哈希索引来加速访问,不过只能选择开启或关闭功能,无法进行人工干涉。

show engine innodb status \G;
show variables like '%innodb_adaptive%';
复制代码

3. B+Tree结构

MySQL数据库索引采用的是B+Tree结构,在B-Tree结构上做了优化改造。

  • B-Tree结构
    • 索引值和data数据分布在整棵树结构中

    • 每个节点可以存放多个索引值及对应的data数据

    • 树节点中的多个索引值从左到右升序排列

B树的搜索:从根节点开始,对节点内的索引值序列采用二分法查找,如果命中就结束查找。没有 命中会进入子节点重复查找过程,直到所对应的的节点指针为空,或已经是叶子节点了才结束。

  • B+Tree结构

    • 非叶子节点不存储data数据,只存储索引值,这样便于存储更多的索引值
    • 叶子节点包含了所有的索引值和data数据
    • 叶子节点用指针连接,提高区间的访问性能

相比B树,B+树进行范围查找时,只需要查找定位两个节点的索引值,然后利用叶子节点的指针进 行遍历即可。而B树需要遍历范围内所有的节点和数据,显然B+Tree效率高。

4. 聚簇索引和辅助索引

聚簇索引和非聚簇索引:B+Tree的叶子节点存放主键索引值和行记录就属于聚簇索引;如果索引值和行 记录分开存放就属于非聚簇索引。

主键索引和辅助索引:B+Tree的叶子节点存放的是主键字段值就属于主键索引;如果存放的是非主键值 就属于辅助索引(二级索引)。

  • 聚簇索引(聚集索引)

    • B+Tree的叶子节点就是行记录,行记录和主键值紧凑地存储在一起。 这也意味着 InnoDB 的主键索引就是数据表本身,它按主键顺序存放了整张表的数据,占用的空间就是整个表数据量的大小。通常说的主键索引就是聚集索引。

    • InnoDB的表要求必须要有聚簇索引:

      • 如果表定义了主键,则主键索引就是聚簇索引
      • 如果表没有定义主键,则第一个非空unique列作为聚簇索引
      • 否则InnoDB会从建一个隐藏的row-id作为聚簇索引
    • 辅助索引

    一个表InnoDB只能创建一个聚簇索引,但可以创建多个辅助索引。

  • 非聚簇索引 : MyISAM数据表的索引文件和数据文件是分开的,被称为非聚簇索引结

构。

三、索引分析与优化

1. EXPLAIN

使用常客,这里就简单回顾下字段含义,作为查缺补漏 :

EXPLAIN SELECT * from user WHERE id < 3;
复制代码
  • select_type :表示查询的类型。常用的值如下:

    SIMPLE : 表示查询语句不包含子查询或union (最常见)
    PRIMARY:表示此查询是最外层的查询
    UNION:表示此查询是UNION的第二个或后续的查询
    EXPLAIN SELECT * from user WHERE id < 3;
    DEPENDENT UNION:UNION中的第二个或后续的查询语句,使用了外面查询结果
    UNION RESULT:UNION的结果
    SUBQUERY:SELECT子查询语句
    DEPENDENT SUBQUERY:SELECT子查询语句依赖外层查询的结果。
    复制代码
  • type : 表示存储引擎查询数据时采用的方式。比较重要的一个属性,通过它可以判断出查询是全表扫描还是基于索引的部分扫描。常用属性值如下,从上至下效率依次增强。

    ALL:表示全表扫描,性能最差。
    index:表示基于索引的全表扫描,先扫描索引再扫描全表数据。
    range:表示使用索引范围查询。使用>、>=、<、<=、in等等。
    ref:表示使用非唯一索引进行单值查询。
    eq_ref:一般情况下出现在多表join查询,表示前面表的每一个记录,都只能匹配后面表的一
    行结果。
    const:表示使用主键或唯一索引做等值查询,常量查询。
    NULL:表示不用访问表,速度最快。
    复制代码
  • possible_keys : 表示查询时能够使用到的索引。注意并不一定会真正使用,显示的是索引名称。

  • key : 表示查询时真正使用到的索引,显示的是索引名称。

  • rows : MySQL查询优化器会根据统计信息,估算SQL要查询到结果需要扫描多少行记录。原则上rows是越少效率越高,可以直观的了解到SQL效率高低。

  • key_len : 表示查询使用了索引的字节数量。可以判断是否全部使用了组合索引。

  • Extra : 表示很多额外的信息,各种操作会在Extra提示相关信息,常见几种如下:

     Using where
     表示查询需要通过索引回表查询数据。
     Using index
     表示查询需要通过索引,索引就可以满足所需数据。
     Using filesort
     表示查询出来的结果需要额外排序,数据量小在内存,大的话在磁盘,因此有Using filesort
     建议优化。
     Using temprorary
     查询使用到了临时表,一般出现于去重、分组等操作。
    复制代码

2. 回表查询

需要扫码两遍索引树。先通过辅助索引定位主键值,然后再通过聚簇索引定位行记 录,这就叫做回表查询,它的性能比扫一遍索引树低。

3. 覆盖索引

只需要在一棵索引树上就能获取SQL所需的所有列数据,无需回表,速度更快,这就叫做索引覆盖。

实现索引覆盖最常见的方法就是:将被查询的字段,建立到组合索引。

4. 最左前缀原则

顾名思义,就是最左优先,即查询中使用到最左边的列,那么查询就会使用到索引,如果从索引的第二列开始查找,索引将失效。

5. LIKE查询

MySQL在使用Like模糊查询时,索引是可以被使用的,只有把**%字符写在后面才会使用到索引**。

6. NULL查询

对MySQL来说,NULL是一个特殊的值,从概念上讲,NULL意味着“一个未知值”,它的处理方式与其他 值有些不同。比如:

  • 不能使用=,<,>这样的运算符
  • 对NULL做算术运算的结果都是NULL
  • count时不会包括NULL行等
  • NULL比空字符串需要更多的存储空间等

虽然MySQL可以在含有NULL的列上使用索引,但NULL和其他数据还是有区别的,不建议列上允许为 NULL。最好设置NOT NULL,并给一个默认值,比如0和 ‘’ 空字符串等,如果是datetime类型,也可以设置系统当前时间或某个固定的特殊值,例如'1970-01-01 00:00:00'。

7. 索引与排序

MySQL查询支持filesort和index两种方式的排序。

  • filesort是先把结果查出,然后在缓存或磁盘进行排序操作,效率较低。
    • filesort有两种排序算法:双路排序和单路排序。
    • 双路排序:需要两次磁盘扫描读取,最终得到用户数据。第一次将排序字段读取出来,然后排序;第二次去读取其他字段数据。
    • 单路排序:从磁盘查询所需的所有列数据,然后在内存排序将结果返回。如果查询数据超出缓存sort_buffer,会导致多次磁盘读取操作,并创建临时表,最后产生了多次IO,反而会增加负担。解决方案:少使用select *;增加sort_buffer_size容量和max_length_for_sort_data容量。
  • 使用index是指利用索引自动实现排序,不需另做排序操作,效率会比较高。

总结:

Explain分析SQL,结果中Extra属性显示Using filesort,表示使用了filesort排序方式,需要优化。如果Extra属性显示Using index时,表示覆盖索引,也表示所有操作在索引上完成,也可以使用index排序方式,建议大家尽可能采用覆盖索引。

demo 示例:

  • 以下几种情况,会使用index方式的排序。(都是最左原则)
explain select id from user order by id; //对应(id)、(id,name)索引有效
复制代码
explain select id from user where age=18 order by name; //对应(age,name)索引
复制代码
  • 以下几种情况,会使用filesort方式的排序。
# 对索引列同时使用了ASCDESC
explain select id from user order by age asc,name desc; //对应(age,name)索引
复制代码
# WHERE子句和ORDER BY子句满足最左前缀,但where子句使用了范围查询(例如><in
等)
explain select id from user where age>10 order by name; //对应(age,name)索引
复制代码
# ORDER BY或者WHERE+ORDER BY索引列没有满足索引最左前列
explain select id from user order by name; //对应(age,name)索引
复制代码
# 使用了不同的索引,MySQL每次只采用一个索引,ORDER BY涉及了两个索引
explain select id from user order by name,age; //对应(name)、(age)两个索引
复制代码
# WHERE子句与ORDER BY子句,使用了不同的索引
explain select id from user where name='tom' order by age; //对应(name)、(age)索引
复制代码
# WHERE子句或者ORDER BY子句中索引列使用了表达式,包括函数表达式
explain select id from user order by abs(age); //对应(age)索引
复制代码

四、查询优化

1. 慢查询定位

  • 开启慢查询日志
# 查看 MySQL 数据库是否开启了慢查询日志和慢查询日志文件的存储位置
SHOW VARIABLES LIKE 'slow_query_log%'
复制代码
# 开启慢查询日志
SET global slow_query_log = ON;
SET global slow_query_log_file = 'OAK-slow.log';
# 表示会记录没有使用索引的查询SQL。前提是slow_query_log的值为ON,否则不会奏效。
SET global log_queries_not_using_indexes = ON;
# 指定慢查询的阀值,单位秒。如果SQL执行时间超过阀值,就属于慢查询记录到日志文件中。
SET long_query_time = 10;
复制代码
  • 查看慢查询日志

    • 用文本编辑器打开slow.log

      time:日志记录的时间
      User@Host:执行的用户及主机
      Query_time:执行的时间
      Lock_time:锁表时间
      Rows_sent:发送给请求方的记录数,结果数量
      Rows_examined:语句扫描的记录条数
      SET timestamp:语句执行的时间点
      select....:执行的具体的SQL语句
      
    复制代码
    • 用mysqldumpslow查看: MySQL bin目录下执行下面命令可以查看该使用格式。
    perl mysqldumpslow.pl --help
    perl mysqldumpslow.pl -t 5 -s at C:\ProgramData\MySQL\Data\OAK-slow.log
    复制代码

2. 慢查询优化

  • 索引和慢查询

使用索引不一定查询快,如下:

select * from user where id>0;
复制代码

虽然使用了索引,但是还是从主键索引的最左边的叶节点开始向右扫描整个索引树,进行了 全表扫描,此时索引就失去了意义。是否使用了索引和是否是慢查询两者之间没有必然的联系。

我们在使用索引时,不要只关注是否起作用,应该关心索引是否减少了查询扫描的数据行数,如果 扫描行数减少了,效率才会得到提升。对于一个大表,不止要创建索引,还要考虑索引过滤性,过 滤性好,执行速度才会快。

  • 提高索引过滤性

explain 查看rows 和 filterd ,一般都用联合索引,如果有like XX%,可以引入的虚拟列来实现。

# 为user表添加first_name虚拟列,以及联合索引(first_name,age)
alter table student add first_name varchar(2) generated always as 
(left(name, 1)), add index(first_name, age);

explain select * from student where first_name='张' and age=18;
复制代码
  • 慢查询原因总结:

    • 全表扫描:explain分析type属性all
    • 全索引扫描:explain分析type属性index
    • 索引过滤性不好:靠索引字段选型、数据量和状态、表设计
    • 频繁的回表查询开销:尽量少用select *,使用覆盖索引

3. 分页查询优化

  • 一般性分页 使用 limit [offset,] rows 偏移量的问题在于如下两种:

    • 如果偏移量固定,返回记录量对执行时间有什么影响?
    select * from user limit 10000,1;
    select * from user limit 10000,10;
    select * from user limit 10000,100;
    select * from user limit 10000,1000;
    select * from user limit 10000,10000;
    复制代码
    • 如果查询偏移量变化,返回记录数固定对执行时间有什么影响?
    select * from user limit 1,100;
    select * from user limit 10,100;
    select * from user limit 100,100;
    select * from user limit 1000,100;
    select * from user limit 10000,100;
    复制代码

    结果:在查询记录时,如果查询记录量相同,偏移量超过100后就开始随着偏移量增大,查询时间

急剧的增加。(这种分页查询机制,每次都会从数据库第一条记录开始扫描,越往后查询越慢,而 且查询的数据越多,也会拖慢总查询速度。

  • 分页优化方案

    • 利用覆盖索引优化
    select * from user limit 10000,100;
    select id from user limit 10000,100;
    复制代码
    • 利用子查询优化
    select * from user limit 10000,100;
    select * from user where id>= (select id from user limit 10000,1) limit 100;
    复制代码

使用了id做主键比较(id>=),并且子查询使用了覆盖索引进行优化。

lagou edu 笔记总结

文章分类
后端
文章标签