MYSQL系列——索引知识点归纳

内容整理范围来源较广，若涉及原作者引用，立即更新。

文章目录

• MYSQL系列——索引知识点归纳
• 一、索引使用及分类
• • MYSQL索引分类
  • • 索引创建：
    • • 普通（二级）索引：
      • 唯一索引：
      • 主键索引：
      • 前缀索引：
      • 后缀索引（suffix index）（一般不用）
      • 全文索引：
      • 组合索引：
      • 降序索引的应用
      • 自适应哈希索引
      • • 开启自适应hash索引
        • 触发器维护hash值：
        • 哈希索引使用情况查看
        • • 插入缓冲和自适应哈希索引
        • 哈希索引弊端
  • 索引原理
  • • B+树
    • • B+树的特征：
      • B+树的优势：
      • B+树分裂原理（基于mysql8版本）
      • 启发式优化规则（MYSQL8版本）
    • 聚族索引
    • • 聚族索引的目的
      • 聚族索引的弊端：
      • 聚族索引原理
      • 辅助索引
    • LSM-TREE概述
  • 索引设计
  • • 建立的索引是否适合
    • • 三星索引构建：
    • 相关知识点
    • • 数据扫描方式：
      • IO逻辑
• 二、MYSQL索引问题排查
• • 相关参数
  • • 检查是否禁用索引扩展提高性能
    • Handler类参数调用
  • 索引失效场景汇总
  • 注意事项：
  • 索引统计
  • 二级索引快速建立
  • • Innodb快速在线索引创建功能
    • 索引并行相关参数
  • 索引-----锁关系
  • 索引使用情况查询工具推荐-----pt-index-usage
  • 冗余索引查询工具-----pt-duplicate-key-checker

一、索引使用及分类

MYSQL索引分类

索引创建：

普通（二级）索引：

create index index_name on table_name(column_list);
alter table table_name add index(column_list);

唯一索引：

#允许为空，防止数据重复
create unique index index_name on table_name(column_list);
alter table table_name add constraint constraint_name unique key(name1,name2);

主键索引：

alter table table_name add PRIMARY key(‘column’)

前缀索引：

alter table city_demo add key （city(7)）;
#前缀索引不支持order by 、group by ;也无法做到覆盖扫描；
#区分度检测（前缀长度经过测试为7为最佳；如果再增加前缀长度，选择性提升幅度就很小了）
select count(distinct right(shi_Guid,6))/count(*) from ZhuanJia_Info;

后缀索引（suffix index）（一般不用）

例如查找某个域名的所有电子邮件地址；MYSQL原生不支持反向索引，但可以把字符串反转后存储，基于此建立前缀索引通过触发器来维护这种索引

全文索引：

create table test(
id int auto_increment not null primary key,
title varchar(200),
body TEXT,
fulltext(title,body)
)type=myisam;

CREATE TABLE article (
id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
title VARCHAR (200),
body TEXT,
FULLTEXT (title, body) WITH PARSER ngram ## 全文索引
) ENGINE = INNODB DEFAULT CHARSET=utf8mb4 COMMENT=‘文章表’;
#创建
alter table test add FULLTEXT index idx_name (name)
ALTER TABLE article ADD FULLTEXT INDEX title_body_index (title,body) WITH PARSER ngram;
#自然语言搜索模式 (默认)
SELECT * FROM article WHERE MATCH (title,body) AGAINST (‘精神’ IN NATURAL LANGUAGE MODE);
SELECT * FROM article WHERE MATCH (title,body) AGAINST (‘精神’);

#索引不支持查询字符串：
SELECT * FROM information_schema.INNODB_FT_DEFAULT_STOPWORD;

组合索引：

alter table a add index idx_sellname_gmt_sellid(gmt_create,seller_name,seller_id);
#最左前缀原则：
1、where 条件调用组合索引最左边的字段才会触发组合索引，当遇到范围查询（>、<、between、like）停止匹配，后面字段不会使用索引;
2、字段顺序无影响，MYSQL内部会自动优化；
3、基于B+树特点，所有叶节点存储在同一层，组合索引可以最大发挥B+树顺序查找优势；
4、ORDER BY列跟在匹配列（它们都使用等值条件）的后面，从而规避了排序；则该查询只需访问索引而无须回表；
例如：
对(a,b,c,d)建立索引，查询条件为a = 1 and b = 2 and c > 3 and d = 4，那么a、b和c三个字段能用到索引，而d无法使用索引。因为遇到了范围查询。
#分页优化（深度分页问题）

update tb_user_info set user_img=replace(user_img,'http://','https://') limit 1,1000;

mysql的limit游标进行的范围查找原理，是下沉到B+数的叶子节点进行的向后遍历查找，在limit数据比较小的情况下还好，limit数据量比较大的情况下，效率很低接近于全表扫描。
如果对于有where 条件，又想走索引用limit的，必须设计一个索引，将where 放第一位，limit用到的主键放第2位，而且只能select 主键！

SELECT * FROM table WHERE ID>=( SELECT ID FROM table ORDER BY ID LIMIT 90000,1) limit 100;

降序索引的应用

对单列进行排序，5.7和8版本没区别；MySQL8.0开始真正支持降序索引，只有InnoDB引擎支持降序索引，且必须是BTREE降序索引，MySQL8.0不再对group by操作进行隐式排序。

当查询需要对多列排序，顺序要求不一致时，就可以使用降序索引来避免filesort了;

索引定义中的DESC不再被忽略，而是按降序存储键值。以前，可以以相反的顺序扫描索引，但是会导致性能损失。下行索引可以按前向顺序扫描，效率更高。当最有效的扫描顺序混合了某些列的升序和其他列的降序时，降序索引也使优化器能够使用多列索引。

降序索引利用的是MYSQL的反向扫描；
在8版本的explain中，extr新增了 Backward index scan 专门描述反向扫描；

自适应哈希索引

MySQL并没有显式支持Hash索引，而是作为内部的一种优化。具体在Innodb存储引擎里，会监控对表上二级索引的查找，如果发现某二级索引被频繁访问，二级索引成为热数据，就为之建立hash索引。因此，在MySQL的Innodb里，对于热点的数据会自动生成Hash索引。
一般情况下查找时间复杂度为O（1），仅需一次查找就能定位数据；
#自适应哈希索引是通过缓冲池的B+树页构造的；建的很快，而且不需要对整张表构建哈希索引；innodb会自动根据访问的频率和模式来自动为某些热点页建立哈希索引
据官方解释：启动AHI后，读取和写入速度可提高2倍，辅助索引连接操作性能提高5倍；无需人工对数据库调整；
#仅使用=或<=>非常快；
#不能适用于order by;
#只能使用整个键来搜索行；

#MEMORY引擎默认使用哈希索引，将键的哈希值和指向数据行的指针保存在哈希索引中。
优点：访问速度较快。
缺点：
哈希索引数据不是按照索引值顺序存储，无法用于排序。
不支持部分索引匹配查找，因为哈希索引是使用索引列的全部内容来计算哈希值的。
只支持等值比较，不支持范围查询。
当出现哈希冲突时，存储引擎需要遍历链表中所有的行指针，逐行进行比较，直到找到符合条件的行。

开启自适应hash索引

在等值查询确实会提升效率50%以上;
#前提条件
AHI的要求是对这个页的连续访问模式必须一样；
例如：对于（a,b）这样的联合索引页，其访问模式可以是：
where a=xxx
where a=xxx and b=xxx
1）访问模式一样是查询条件一样，如果交替进行，则Innodb不会对该页构造AHI；
2）按该模式访问了100次
3）页通过该模式访问了N次，其中N=页中记录*1/16;

内部判断索引值使用非常频繁，会基于BTree上创建一个哈希索引，这种哈希查找是内部行为，如果有必要可以关闭该功能
set global innodb_adaptive_hash_index=0;

哈希索引可以提高查询性能，但是，在高并发情况下，会造成 RW-latch争用，进而堵塞进程。可以使用命令“show engine innodb status\G;”来监控SEMAPHORES
由于自适应哈希索引造成大量的锁争用， 进而堵塞很多进程，最终导致MySQL崩溃重启。
InnoDB: Warning: a long semaphore wait --Thread 140570431108864 has waited at btr0cur.c line 528 for 241.00 secondsthe semaph

本文来自互联网用户投稿，文章观点仅代表作者本人，不代表本站立场，不承担相关法律责任。如若转载，请注明出处。 如若内容造成侵权/违法违规/事实不符，请点击【内容举报】进行投诉反馈！