MySQL性能优化-索引

xiaojiang0 2020-05-02

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

相关推荐