MySQL性能优化-索引
索引在MySQL中也叫“键（key）”，索引对性能影响非常关键，当数据量越来越大时，恰当的索引能大幅提高性能，反之，不恰当的索引会降低性能。索引是存储引擎层的而不是服务器层的，不同存储引擎下的索引有不同的工作方式。
1、索引的分类（Index Type）
- 主键索引
- NORMAL普通索引
- UNIQUE唯一索引
- FULLTEXT全文索引
- SPATIAL空间索引
主键是特殊的唯一索引，相比唯一索引，它要求字段不能为空；外键字段一定同时是个普通索引。全文索引针对字符类型，相当于ES全文检索。
2、索引方法（Index Method）
- BTREE
- HASH
BTREE是主流使用的，一般没有特指，我们都认为是BTREE。BTREE是一个多叉排序树，索引数据是顺序存储的，很适合范围查找。
HASH索引是基于哈希表实现的，它的检索效率非常高，一次定位就查找到数据，同时因为它不是顺序存储的，它不适合范围查找，只支持等值比较查询（=、in、<=>），其他比较操作（>、<等）不会使用索引。不过MySQL各种引擎中只有Memory引擎显式支持支持哈希索引，NDB支持唯一哈希索引。
全文索引不能设置索引方法。
InnoDB有个特殊的功能叫做“自适应哈希索引（adaptive hash index）”，当InnoDB注意到某些索引值用的非常频繁，它会在内存中基于BTREE索引上再创建一个哈希索引，这样BTREE索引页具有了哈希索引的优点。这是一个完全自动的内部行为，用户无法控制或修改配置，若有必要，用户可关闭这个功能。
show variables like ‘innodb_adaptive_hash_index‘; # 查看是否开启自适应哈希索引 set global innodb_adaptive_hash_index=0; # 关闭自适应哈希索引
3、创建索引的原则
- 数据量小时索引意义不大，这样的索引对性能提升不大，反而消耗系统资源。
- 索引不是越多越好，索引会影响insert、update、delete操作性能
- 避免对更新频繁的表和字段创建索引，当数据更改时，索引也同时会更新，消耗资源
- 不要在区分度低的字段上建立索引，如在性别（男、女）创建索引完全没有优化效果
- 当字段值具有唯一性时使用唯一性索引能提升性能，如果账户的邮箱和手机号字段
- 对频繁排列和分组的字段创建组合索引
- 使用短索引
- 删除未使用的索引
4、
5、覆盖索引
覆盖索引是指索引包含了所有要查询的字段信息。通常我们根据where条件来创建索引，不过这只是索引优化的一个方面。优秀的索引设计还应该考虑整个查询，如果索引的叶子节点中已经包含了要查询的数据，我们还有什么必要回差表呢？当发起一个被索引覆盖的查询时，explain的extra列会出现“Using index”信息
6、索引性能测试
测试环境：MySQL8.0.19，默认配置，使用CentOS虚拟机安装，虚拟机配置4G内存2CPU.
1）创建一个用户表，并在表中插入300W条数据
CREATE TABLE `bd_user` ( `id` bigint(0) NOT NULL, `dept_id` bigint(0) NULL DEFAULT NULL COMMENT ‘部门ID‘, `user_code` char(10) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT ‘用户编码‘, `user_name` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT ‘用户名称‘, `birthday` date NULL DEFAULT NULL COMMENT ‘生日‘, `usable` bit(1) NULL DEFAULT NULL COMMENT ‘是否可用‘, PRIMARY KEY (`id`) USING BTREE ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = ‘用户‘ ROW_FORMAT = DYNAMIC;
2）测试语句一
select * from bd_user where user_code in(‘0000000014‘,‘0000001000‘,‘0000101000‘);
在user_code不使用索引时，查询时间约1秒，对该字段添加普通索引，查询时间约0.027秒，唯一索引的效果跟普通索引差不多。