Mysql 索引

Learn1993
181 阅读8分钟

这是我参与11月更文挑战的第5天，活动详情查看：2021最后一次更文挑战

索引类型

索引可以提升查询速度， 会影响where查询和order排序

普通索引

create index indexName on tableName fieldName
alter table tablename add index indexName fieldName
create table tableName([...], index indexName(fieldName))

唯一索引

索引字段必须唯一，但允许有空值。

create unique index indexName on tableName
alter table tableName add unique index indexName(fieldName)
create table tableName([...], unique indexName(fieldName))

主键索引

特殊的唯一索引，不允许有空值, 每个表只能有一个主键

create table tableName([...], primary key(fieldName))
alter table tableName add primary key(fieldName)

复合索引

在多个列上建立索引， 可以代替多个单一索引， 相比起来开销更小。

窄索引: 1-2列索引

宽索引: 索引列超过2列

create index indexName on tablename(fieldName1,fieldName2...);
alter table tableName add index indexName(fieldName1, fieldName2);
create table tableName ([...], index indexName(fieldName1, fieldName2))

全文索引

create fulltext index indexName on tableName(fieldName);
alter table tableName add fulltext indexName (fieldName)
create table tableName（[...], fulltext key indexName(fieldName)）

查询的时候要用到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*

    select * from user  where match(name) against('a*' in boolean mode);

聚簇索引

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

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

辅助索引 InnoDB辅助索引，也叫作二级索引，是根据索引列构建 B+Tree结构。但在 B+Tree 的叶子节点中只存了索引列和主键的信息。二级索引占用的空间会比聚簇索引小很多， 通常创建辅助索引就是为了提升查询效率。一个表InnoDB只能创建一个聚簇索引，但可以创建多个辅助索引

索引原理

  • 二分查找法

  • 哈希

    InnoDB自适应哈希索引是为了提升查询效率，InnoDB存储引擎会监控表上各个索引页的查询，当InnoDB注意到某些索引值访问非常频繁时，会在内存中基于B+Tree索引再创建一个哈希索引，使得内存中的 B+Tree 索引具备哈希索引的功能，即能够快速定值访问频繁访问的索引页。InnoDB自适应哈希索引的功能，用户只能选择开启或关闭功能，无法进行人工干涉。

    show engine innodb status \G;
show variables like '%innodb_adaptive%';

  • B+ Tree

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

索引优化

explain

explain select * from user where id > 4

  • select_type

    • SIMPLE: 表示不包含子查询或union
    • PRIMARY：表示是最外层的查询
    • UNION： 表示是UNION第二个或后续的查询
    • DEPENDENT UNION: 表示是UNION第二个或后续的查询, 使用了外面查询结果
    • UNION RESULT: UNION 的结果
    • SUBQUERY: 子查询
    • DEPENDENT SUBQUERY: 子查询依赖外层结果

  • Type

    • ALL: 全表扫描，性能最差
    • index: 基于索引的全表扫描， 先扫描索引表再扫描全表数据。
    • range: 使用索引范围查询
    • ref: 非唯一索引单值查询
    • eq_ref: 多表join， 前面表的每一条记录，只能匹配后面表的一行结果
    • const: 主键或唯一索引等值查询
    • NULL： 不用访问表

  • possible keys

    可能使用的索引

  • key

    真正使用的索引

  • rows

    估算扫描的行数

  • key_len

    表示查询使用了索引的字节数量。可以判断是否全部使用了组合索引。 key_len的计算规则如下：

    • 字符串类型 字符串长度跟字符集有关：latin1=1、gbk=2、utf8=3、utf8mb4=4 char(n)：n*字符集长度 varchar(n)：n * 字符集长度 + 2字节
    • 数值类型 TINYINT：1个字节 SMALLINT：2个字节 MEDIUMINT：3个字节 INT、FLOAT：4个字节 BIGINT、DOUBLE：8个字节
    • 时间类型 DATE：3个字节 TIMESTAMP：4个字节 DATETIME：8个字节
    • 字段属性 NULL属性占用1个字节，如果一个字段设置了NOT NULL，则没有此项。

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

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

回表查询和覆盖索引

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

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

最左前缀

复合索引使用时遵循最左前缀原则，就是最左优先，查询中使用到最左边的列，那么查询就会使用到索引，如果从索引的第二列开始查找，索引将失效。

like

只有把%字符写在后面才会使用到索引

NULL

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

NULL列需要增加额外空间来记录其值是否为NULL。对于MyISAM表，每一个空列额外占用一位，四舍五入到最接近的字节

不建议列上允许为NULL。最好设置NOT NULL，并给一个默认值

索引和排序

MySQL查询支持filesort和index两种方式的排序，filesort是先把结果查出，然后在缓存或磁盘进行排序操作，效率较低。使用index是指利用索引自动实现排序，不需另做排序操作，效率会比较高。

filesort有两种排序算法：双路排序和单路排序。 双路排序：需要两次磁盘扫描读取，最终得到用户数据。第一次将排序字段读取出来，然后排序；第二次去读取其他字段数据。 单路排序：从磁盘查询所需的所有列数据，然后在内存排序将结果返回。如果查询数据超出缓存sort_buffer，会导致多次磁盘读取操作，并创建临时表，最后产生了多次IO，反而会增加负担。解决方案：少使用select *；增加sort_buffer_size容量max_length_for_sort_data容量。

使用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

  • 索引列同时使用asc和desc

    explain select id from user order by age asc,name desc; //对应(age,name)索引

  • where和order满足最左前缀，但where使用了范围查询

    explain select id from user where age>10 order by name; //对应(age,name)索引

  • order by 或where +orderby 没有满足最左前缀

    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)索引

查询优化

慢查询

SHOW VARIABLES LIKE 'slow_query_log%'
SET global slow_query_log = ON;
SET global slow_query_log_file = 'OAK-slow.log';
SET global log_queries_not_using_indexes = ON;
SET long_query_time = 10;

perl mysqldumpslow.pl --help
perl mysqldumpslow.pl -t 5 -s at OAK-slow.log

索引要提高过滤性。尽量让索引条件下推。除此外还可以通过虚拟列来优化查询

alter table student add first_name varchar(2) generated always as (left(name, 1)), add index(first_name, age);
  • 全表扫描：explain分析type属性all
  • 全索引扫描：explain分析type属性index
  • 索引过滤性不好：靠索引字段选型、数据量和状态、表设计
  • 频繁的回表查询开销：尽量少用select *，使用覆盖索引
avatar
文章
阅读
粉丝