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秒，唯一索引的效果跟普通索引差不多。