MySQL学习（十）索引

1.索引的种类

  • 聚簇索引，非聚簇索引
  • 主键索引，唯一索引，普通索引（前缀索引），全文索引
  • 单值索引，复合索引
  • 二级索引
  • 覆盖索引

1.1 聚簇索引，非聚簇索引

参考文档：
https://www.cnblogs.com/jiawen010/p/11805241.html

https://learnku.com/articles/45521

总结（根据第二个博客）：

  • 聚簇索引
    • 索引必须为唯一索引
    • 叶子节点存储的是整行数据
  • 非聚簇索引
    • 索引值不唯一
    • 叶子节点存储的是索引行和主键

Q：聚簇索引一定是主键吗？
A：不一定，但主键一定是聚簇索引。如果没有主键索引，InnoDB会选择内置6字节长的ROWID作为隐含的聚集索引。

1.2.前缀索引

参考博客：https://blog.51cto.com/u_15144024/2860391
主要针对较长的varchar类型字段以及TEXT、BLOB类型类型。

通常可以索引开始的部分字符，这样可以大大节约索引空间，从而提高索引效率。但这样也会降低索引的选择项。索引的选择性是指，不重复的索引值（也称为基数 cardinality）和数据表的记录总数（#T）的比值，范围从1/#T到1之间。索引的选择项越高则查询效率越高，唯一索引的选择性是1，这是最好的索引选择性，性能也是最好的。

对于BLOB、TEXT或者很长的varchar类型的列，必须使用前缀索引，因为mysql不允许索引这些列的完整长度。

诀窍在于要选择足够长的前缀以保证较高的选择性，同时又不能太长（以便节约空间）。前缀应该足够长，以使得前缀索引的选择性接近于索引整个列。换句话说，前缀的"基数"应该接近于完整列的"基数"

-- 计算完整列的选择性
select count(distinct city)/count(*) from city_demo;

mysql无法使用前缀索引做order by和group by，也无法使用前缀索引做覆盖索引

1.3.全文索引

参考博客：https://blog.csdn.net/mrzhouxiaofei/article/details/79940958

之前只能用myisam创建全文索引，5.6之后innodb也可以了

查找文本中的关键词，而不是直接比较索引中的值。它有许多需要注意的细节，如停用词、词干和复数、布尔搜索等。全文索引更类似于搜索引擎做的事情，而不是简单的where条件匹配。在相同的列上同时创建全文索引和基于值的B-Tree索引不会有冲突，全文索引适用于match against操作，而不是普通的where条件操作。

有时候后缀索引也有用途，mysql原生不支持反向索引，但是可以把字符串反转后存储，并基于此建立前缀索引。 姓名把名反转放前面，做名的右模糊查询，但中间的没法模糊查询
参考博客：https://blog.csdn.net/weixin_38106322/article/details/106583450

1.4.二级索引

主键索引之外的都可以统称二级索引

1.5.覆盖索引

：不需要回表查询；多字段创建一个索引，查询只需要返回覆盖索引包含的字段

2.索引的B+树如何生长的

3.索引优化

查询某张表有哪些索引：show index from tablename;

4.索引优化之explain

  • id：编号
  • select_type：查询类型
  • type：索引类型
  • table：表名
  • possible_keys：预测用到的索引
  • key：实际使用的索引
  • key_len：实际使用索引的长度
  • ref：表之间的引用
  • rows：被索引优化查询的数据个数
  • extra：额外信息

4.1 id

针对连表查询：

  • id值相同，从上往下，顺序执行
  • id值不同：id值越大越优先查询（在嵌套子查询时，先查内层，再查外层）
  • 数据小的表，优先查询（rows）
  • id值不同，id值越大越优先查询

4.2 select_type

  • primary：包含子查询SQL中的主查询（最外层）
  • subquery：包含子查询SQL中的子查询（非最外层）
  • simple：简单查询
  • derived：衍生查询（使用到了临时表）
    • 在from子查询中只有一张表
    • 在from子查询中，如果有table1 union table2，则table1就是derived

4.3 type

system > const > eq_ref > ref > range > index > all，要对type优化的前提是：有索引
system、const 理想情况
ref、range 实际能达到

  • system：只有一条数据的系统表，或衍生表只有一条数据的主查询

查询返回的字段有些有索引，有些没有

  • const：仅仅能查到一条数据的SQL，用于primary key或unique索引
  • eq_ref：唯一性索引，对于每个索引键的查询，返回匹配唯一行数据（有且只有1个，不能多、不能0）
    select ... from ...where name = ... 常见于唯一索引和主键索引
  • ref：非唯一性索引，返回匹配的所有行（0，多）
  • range：检索指定范围的行，where后面是一个范围查询(between..in,>,<=)

特殊：in有时候会失效，从而转为无索引all

  • index：查询全部索引中数据
  • all：查询全部表中数据

总结:

  • system/const：结果只有一条数据
  • eq_ref：结果多条，但是每条数据是唯一的
  • ref：结果多条，但是每条数据是0或是多条

4.4 possible_keys

如果possible_keys/key是null，则说明没用索引

4.5 key_len

：索引的长度，用于判断复合索引是否被完全使用

一个字符占3个字节，一个字节表示null，两个字节标识可变长度

4.6 extra

  • using filesort：性能消耗比较大，需要"额外"的依次排序（查询），常见于order by语句中
  • using tempoary：性能损耗大，用到了临时表，一般出现在groupby（已经有表了，但不适用，必须再来一张表）
  • using where：需要回原表
  • using index：性能提升，索引覆盖（覆盖索引）。原因，不读取原文件，只从索引文件中获取数据（不需要回表查询）
    • 如果用到了索引覆盖时，会对possible_keys和key造成影响
      • 如果没有where，则索引只出现在key中
      • 如果有where，则索引出现在key和possible_keys中
  • using join buffer：mysql主动优化，加了缓存
  • impossible where：WHERE子句始终为false，不能选择任何行 select ... from ... where 1 < 0;
  • using index condition：using index + 回表 + where 过滤

5.索引优化之慢日志查询

注：不要在生产环境测试（测试环境数据量最好和生产环境一致）
：mysql提供的一种日志记录，用于记录mysql中响应时间超过阈值（long_query_time 默认10s），默认是关闭的，最终部署是关闭。
查询mysql是否开启慢日志查询： show variables like'%slow_query_log%'
image
临时开启： set global slow_query_log = 1;
设置慢查询阈值：set global long_query_time = 5; (修改后，重新登录后起效，不需要重启服务)
在内存中开启；重启 mysql service restart mysql
永久开启：在 /etc/my.cnf 中追加配置：

slow_query_log=1
slow_query_log_file=/var/lib/mysql/localhost-slow.log
# 超时时间
long_query_time=3

image
查看超过阈值的sql：show global status like '%slow_queries%';
或者去指定的日志文件去看 /var/lib/mysql/localhost-slow.log

6.单表优化

  • 最佳左前缀，保持索引的定义和使用的顺序一致性
  • 索引需要逐步优化
  • 将含In的范围查询放where条件的最后，防止失效
  • 通过key_len证明in可以使索引失效

7.两表优化

teacher ：tid，cid
course ：cid，cname
索引往哪加？-小表驱动大表[一般情况下对于左外连接，给左表加索引；右外连接，给右表加索引]

8.三表优化

  • 小表驱动大表
  • 索引建立在经常查询的字段上

9.避免索引失效的一些原则

  • 复合索引，不要跨列或无序使用（最佳左前缀）
  • 复合索引，尽量使用全索引匹配
  • 不要在索引上进行任何操作（计算，函数，类型转换），否则索引失败
  • 对于复合索引，如果左边失效，右边全部失效
  • 复合索引不能使用不等于（!= <>）或is null（is not null），否则自身以及右侧所有全部失效；复合索引有 > ，则自身和右侧索引全部失效
  • %x% 会导致索引失效， x% 不会 ，或者用索引覆盖
  • 尽量不要使用类型转换（显式、隐式），否则索引失效
  • 尽量不要使用or，否则索引失效，将or左侧的索引失效

10.一些其他的优化方法

  • 如果主查询的数据集大，则使用in
  • 如果子查询的数据集大，则使用exist。 select...from table where exist/in (子查询);

exist: 将主查询的结果，放到子查询中进行条件校验，如果复合校验，则保留数据

  • order by 优化
    • using filesort：双路排序、单路排序（根据IO的次数）
    • 选择使用单路、双路；调整buffer的容量大小
    • 避免select * ...
    • 复合索引 不要跨列使用，避免using filesort
    • 保证全部的排序字段，排序的一致性（都是升序 或 降序）

mysql4.1 默认使用 双路排序（扫描两次磁盘 【1.从磁盘读取排序字段
｛在buffer中进行的排序｝ 2.扫描其他字段】）；4.1之后，默认使用单
路排序 （只读取一次【全部字段｛在Buffer中进行排序｝】），有一定
隐患，不一定是一次，可能是多次IO

原因：如果数据量特别大，则无法将所有字段的数据一次性读完，索引
会进行"分片读取"

注意：单路排序比双路排序，会占用更多的Buffer。如果数据大，可以
考虑调大buffer的容量大小：set max_length_for _sort_data = 1024
byte

如果max_length_for _sort_data太低（需要排序的列总大小超过了
max_length_for _sort_data定义的字节数），则mysql会自动从单路切
换到双路

【推广】 免费学中医，健康全家人
原文地址：https://www.cnblogs.com/lhxBlogs/p/15661206.html