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

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

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

一、索引使用及分类

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 semaphore: X-lock on RW-latch at 0x7fd9142bfcc8 created in file dict0dict.c line 1838

触发器维护hash值：

DELIMITER //
create trigger pseudohash_crc_ins BEFORE INSERT ONpseudohash FOR EACH ROW BEGIN SET NEW.url_crc=crc32(NEW.url);
END;
//
create trigger pseudohash_crc_upd before update onpseudohash for each row begin set new.url_crc=crc32(NEW.url);
END；
//

DELIMITER ;

insert into pseudohash (url) values(‘http://www.mysql.com’);
select * from pseudohash;
update pseudohash set url='http://www.mysql.com/111’where id=1;
select * from pseudohash;

#不要使用SHA1()和MD5（）作为哈希函数；因为值非常长，虽然是强加密函数，但涉及目标是最大限度消除冲突；
如果数据量非常大，crc32()会出现大量哈希冲突，
建议用MD5（）函数返回值得一部分作为自定义哈希函数；可能比自己写一个哈希算法性能要差；
#哈希查询必须where条件带哈希值和对应列值；
因为生日悖论，出现哈希冲突概率增长速度可能比想象的快的多；crc32()返回的是32位整数，当索引93000条时，出现冲突概率为1%；冲突会返回多条记录；
#错误写法
select word,crc from words where crc = CRC32(‘gnu’);
#正确：
select word,crc from words where crc = CRC32(‘gnu’)and word =‘gnu’;

哈希索引使用情况查看

可通过该show engine innodb status
#1640.60 hash searches/s, 3709.46 non-hash searches/s
#AHI大小、使用情况、每秒使用AHI搜索；

插入缓冲和自适应哈希索引

INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 0, seg size 2, 0 merges
#Ibuf:表示缓冲索引树的当前大小，free list len:空闲列表长度
seg size:文件段中已分配段的个数，merges:合并页的个数；
merged operations:
 insert 0, delete mark 0, delete 0
#insert插入缓冲的次数，标记删除的次数，delete表示purge的次数
discarded operations:
 insert