这是我参与「第五届青训营 」伴学笔记创作活动的第 10 天
MySQL索引应用
-
pros & cons
-
优势
- ①整个数据库中，数据表的查询速度直线提升，数据量越大时效果越明显。
- ②通过创建唯一索引，可以确保数据表中的数据唯一性，无需额外建立唯一约束。
- ③在使用分组和排序时，同样可以显著减少SQL查询的分组和排序的时间。
- ④连表查询时，基于主外键字段上建立索引，可以带来十分明显的性能提升。
- ⑤索引默认是B+Tree有序结构，基于索引字段做范围查询时，效率会明显提高。
- ⑥从MySQL整体架构而言，减少了查询SQL的执行时间，提高了数据库整体吞吐量。
-
劣势
- ①建立索引会生成本地磁盘文件，需要额外的空间存储索引数据，磁盘占用率会变高。
- ②写入数据时，需要额外维护索引结构，增、删、改数据时，都需要额外操作索引。
- ③写入数据时维护索引需要额外的时间开销，执行写SQL时效率会降低，性能会下降。
-
-
警惕
- 主键——使用带顺序性的值（如自增ID）rather than UUID无序
- 联合——查询条件中需要包含联合索引的第一个字段才能启用，建立索引时需要考虑命中率
- 前缀——无法完成ORDER BY、GROUP BY等分组排序工作、覆盖扫描等操作
-
全文——分词问题
- 分词处理后的全文检索文件过大
- 分词需要时间，so修改字段数据后不会立马自动更新全文索引，需要写存储过程，并调用它手动更新全文索引中的数据
- 中文分词的精准度不够
- 唯一——查询更快 插入更慢
- 哈希——查询no.1 但无法分组排序
-
how to build index
-
回表问题——一条查询SQL经历了两次查询才获取到数据
- 尽量基于主键做查询，如果实在需要使用非主键字段查询，尽量写明查询的结果字段，而并非使用*
- 建立联合索引，利用索引覆盖特性，从而避免使用辅助索引，这样也能够消除回表动作
-
原则
- ①经常频繁用作查询条件的字段应酌情考虑为其创建索引。
- ②表的主外键或连表字段，必须建立索引，因为能很大程度提升连表查询的性能。
- ③建立索引的字段，一般值的区分性要足够高，这样才能提高索引的检索效率。
- ④建立索引的字段，值不应该过长，如果较长的字段要建立索引，可以选择前缀索引。
- ⑤建立联合索引，应当遵循最左前缀原则，将多个字段之间按优先级顺序组合。
- ⑥经常根据范围取值、排序、分组的字段应建立索引，因为索引有序，能加快排序时间。
- ⑦对于唯一索引，如果确认不会利用该字段排序，那可以将结构改为Hash结构。
- ⑧尽量使用联合索引代替单值索引，联合索引比多个单值索引查询效率要高。
-
注意点
- ❶值经常会增删改的字段，不合适建立索引，因为每次改变后需维护索引结构。
- ❷一个字段存在大量的重复值时，不适合建立索引，比如之前举例的性别字段。
- ❸索引不能参与计算，因此经常带函数查询的字段，并不适合建立索引。
- ❹一张表中的索引数量并不是越多越好，一般控制在3，最多不能超过5。
- ❺建立联合索引时，一定要考虑优先级，查询频率最高的字段应当放首位。
- ❻当表的数据较少，不应当建立索引，因为数据量不大时，维护索引反而开销更大。
- ❼索引的字段值无序时，不推荐建立索引，因为会造成页分裂，尤其是主键索引。
-
联合索引的最左前缀原则
- 才匹配到范围查询时会停止匹配，比如>、<、between、like这类范围条件，并不会继续使用联合索引
-
对于一条查询SQL是否用到了索引，或者一条查询SQL到底用了那个索引，可通过MySQL自带的explain工具分析 (在SQL语句前加上EXPLAIN)
- id：这是执行计划的ID值，这个值越大，表示执行的优先级越高。
-
select_type：当前查询语句的类型，有如下几个值：
- simple：简单查询。
- primary：复杂查询的外层查询。
- subquery：包含在查询语句中的子查询。
- derived：包含在FROM中的子查询。
- table：表示当前这个执行计划是基于那张表执行的。
-
type：当前执行计划查询的类型，有几种情况：
- all：表示走了全表查询，未命中索引或索引失效。
- system：表示要查询的表中仅有一条数据。
- const：表示当前SQL语句的查询条件中，可以命中索引查询。
- range：表示当前查询操作是查某个区间。
- eq_ref：表示目前在做多表关联查询。
- ref：表示目前使用了普通索引查询。
- index：表示目前SQL使用了辅助索引查询。
- possible_keys：执行SQL时，优化器可能会选择的索引（最后执行不一定用）。
- key：查询语句执行时，用到的索引名字。
- key_len：这里表示索引字段使用的字节数。
- ref：这里显示使用了那种查询的类型。
- rows：当前查询语句可能会扫描多少行数据才能检索出结果。
-
Extra：这里是记录着额外的一些索引使用信息，有几种状态：
- using index：表示目前使用了覆盖索引查询（稍后讲）。
- using where：表示使用了where子句查询，通常表示没使用索引。
- using index condition：表示查询条件使用到了联合索引的前面几个字段。
- using temporary：表示使用了临时表处理查询结果。
- using filesort：表示以索引字段之外的方式进行排序，效率较低。
- select tables optimized away：表示在索引字段上使用了聚合函数。
-
-
索引失效
- 查询中带有OR——多SQL或子查询代替
- 模糊查询中like以%开头——建立全文索引
- 字符类型查询时不带引号触发MySQL的隐式转换
- 索引字段参与计算——尽量将计算工作放在客户端中完成
- 字段被用于函数计算——将函数计算放在=后面
- 违背最左前缀原则
- 不同字段值对比——拆分为连表查询，使用临时表代替
- 反向范围/不等性操作（NOT IN、NOT LIKE、IS NOT NULL、!=、<>...）
- 走索引扫描的行数超过表行数的30%
- 跟索引的数据结构、MySQL的版本、存储引擎的不同有关
-
how to use index
-
索引覆盖——要查询的列在使用的索引中已经包含，被所使用的索引覆盖
- 查询数据用* ——会导致索引覆盖失效，造成回表
-
索引下推——将Server层筛选数据的工作，下推到引擎层处理
- 默认开启，可手动管理：set optimizer_switch='index_condition_pushdown=off|on';
-
Multi-Range Read机制——针对于辅助索引的回表查询，减少离散IO，并且将随机IO转换为顺序IO，从而提高查询效率
- 回表会导致产生大量磁盘IO和离散IO
- 对于辅助索引中查询出的ID，会将其放到缓冲区的read_rnd_buffer中，然后等全部的索引检索工作完成后，或者缓冲区中的数据达到read_rnd_buffer_size大小时，此时MySQL会对缓冲区中的数据排序，从而得到一个有序的ID集合：rest_sort，最终再根据顺序IO去聚簇/主键索引中回表查询数据。
- 默认开启，可手动管理：SET @@optimizer_switch='mrr=on|off,mrr_cost_based=on|off';
-
索引跳跃式扫描机制——使得即使查询条件中没有使用联合索引的第一个字段，依旧可以使用联合索引
- 适用于唯一性较差时
- 优化器重构了SQL
- 限制：如多表联查时无法触发、SQL条件中有分组操作也无法触发、SQL中用了DISTINCT去重也无法触发……
- set @@optimizer_switch = 'skip_scan=off|on';
-
-
查询
- show status like '%Handler_read%';查看当前会话的索引使用情况。
- show global status like 'Handler_read%';：查询全局索引使用情况。
