MySQL索引优化

「 MYSQL 」 八月 29, 2021

MySQL索引优化

文章字数 8.1k 阅读约需 7 mins. 阅读次数 0

Mysql索引优化

mysql索引数据结构 B+Tree

B+Tree是在BTree的基础上做的一种优化

  • B+Tree每个节点可以包含更多的节点.

    作用:

    1. 降低树的高度.

    2. 将数据范围变为多个区间,区间越多,数据检索越快.

  • 非叶子节点存储key,叶子节点存储key和数据,

  • 叶子节点两两指针相互连接,顺序查找性能越高.

索引的优点

  • 大大减少服务器需要扫描的数据量
  • 帮助服务器避免排序和临时表
  • 将随机io变成顺序io

索引的用处

  • 快速查找匹配where子句的行
  • 从consideration中消除行,如果可以在多个索引之间进行选择,mysql通常会使用找到最少行的索引.
  • 如果表具有多列索引,则优化器可以使用索引的任何最左前缀来查找行.
  • 当有表连接的时候,从其他表检索行数据.
  • 查找特定索引列的min或max值
  • 如果排序或分组时在可用索引的最左前缀上完成时,则对表进行排序和分组.
  • 可以优化查询以检索值二无需查询数据行.

索引的分类

  1. 主键索引.(主键:唯一且非空,所以数据库建数据库并非给主键建的.而是给唯一键建的.)

  2. 唯一索引,(该列字段每行都唯一,索引类型为unique)

  3. 普通索引, 给普通列创建一个普通索引

  4. 全文索引,给字符串字段建立的索引.char,varchar,text

  5. 组合索引,将多个列合并成一个索引.由于查询的时候一般是多条件组合查询.

    会涉及到多个技术名词

    ​ 回表: 通过索引条件查询周到叶子节点上的值,其值为主键列的值;再通过这个值去主键的B+Tree获取其叶子节点的值.此值为该行记录的完整数据.

    ​ 覆盖索引: 不存在回表的查询则为索引覆盖.当查询字段都存在于当前索引中.则不需要再去主键的B+Tree获取完整数据.最常见的是关联查询的时候查询id.

    ​ 最左匹配: 在使用组合索引的时候,where要按照组合索引的顺序来写where查询.

    ​ eg: 组合索引为ename和job两个字段,则where可以为 只查询enmae或查询ename与job,或更多;但不可以没有ename查询条件,顺序无所谓,优化器会帮助优化.

    ​ 小技巧: 当需要创建组合索引且又要单独建立普通索引的时候,在满足业务场景需求的情况下,选择存储量少的索引建立.

    ​ eg: age 与 name 建立索引

    1. age_name索引 name索引

    2. name_age索引 age索引

      选用第二种,第二种索引消耗的内存少.但两者都能实现满足相同的查询.

    ​ 索引下推: 对组合索引中包含的字段先做判断，过滤掉不符合条件的记录，减少回表字数。(在mysql5.6版本引入)

其他索引结构

HASH

基于哈希表的实现，只有精确匹配索引所有列的查询才有效;

在mysql中，只有memory的存储引擎显式支持哈希索引;

哈希索引自身只需存储对应的hash值，所以索引的结构十分紧凑，这让哈希索引查找的速度非常快;

限制:

  • 哈希索引只包含哈希值和行指针，而不存储字段值，索引不能使用索引中的值来避免读取行.
  • 哈希索引数据并不是按照索引值顺序存储的，所以无法进行排序
  • 哈希索引不支持部分列匹配查找，哈希索引是使用索引列的全部内容来计算哈希值
  • 哈希索引支持等值比较查询，也不支持任何范围查询
  • 访问哈希索引的数据非常快，除非有很多哈希冲突，当出现哈希冲突的时候，存储引擎必须遍历链表中的所有行指针，逐行进行比较，直到找到所有符合条件的行
  • 哈希冲突比较多的话，维护的代价也会很高(所以需要优秀的hash算法)

索引匹配方式

eg:

create table staffs(
id int primary key auto_increment,
name varchar(24) not null default ‘’ comment ‘姓名’,
age int not null default 0 comment ‘年龄’,
pos varchar(20) not null default ‘’ comment ‘职位’,
add_time timestamp not null default current_timestamp comment ‘入职时间’
) charset utf8 comment ‘员工记录表’;

alter table staffs add index idx_nap(name, age, pos);

全值匹配

和索引中的所有列进行匹配

eg: explain select * from staffs where name = ‘July’ and age = ‘23’ and pos = ‘dev’;

匹配最左前缀

只匹配前面几列.

explain select * from staffs where name = ‘July’ and age = ‘23’;

explain select * from staffs where name = ‘July’ ;

匹配列前缀

匹配某一列的值的开头部分

explain select * from staffs where name like ‘J%’;

explain select * from staffs where name like ‘%y’; 将进行全表扫面

匹配范围值

查找某一个范围的数据,如果为字符串则匹配字符串的hash值.

explain select * from staffs where name > ‘Mary’;

精确匹配某一列并范围匹配另一列

可以查询第一列的全部和第二列的部分

explain select * from staffs where name = ‘July’ and age > 25;

只访问索引的查询

查询的时候只需要访问索引，不需要访问数据行，本质上就是覆盖索引

explain select name,age,pos from staffs where name = ‘July’ and age = 25 and pos = ‘dev’;

聚簇索引与非聚簇索引

聚簇索引

不是单独的索引类型，而是一种数据存储方式，指的是数据行跟相邻的键值紧凑的存储在一起.innoDB属于聚簇索引.

优点:

  1. 可以把相关数据保存在一起
  2. 数据访问更快，因为索引和数据保存在同一个树中
  3. 使用覆盖索引扫描的查询可以直接使用页节点中的主键值

缺点:

  1. 聚簇数据最大限度地提高了IO密集型应用的性能，如果数据全部在内存，那么聚簇索引就没有什么优势
  2. 插入速度严重依赖于插入顺序，按照主键的顺序插入是最快的方式
  3. 更新聚簇索引列的代价很高，因为会强制将每个被更新的行移动到新的位置
  4. 基于聚簇索引的表在插入新行，或者主键被更新导致需要移动行的时候，可能面临页分裂的问题
  5. 聚簇索引可能导致全表扫描变慢，尤其是行比较稀疏，或者由于页分裂导致数据存储不连续的时候

小技巧:当导入大量数据的时候可以先将数据库表索引关闭,导入后再打开.

关闭：ALTER TABLE test DISABLE KEYS
开启：ALTER TABLE test ENABLE KEYS

查看索引信息: show INDEX FROM test

非聚簇索引

数据文件跟索引文件分开存放.MYISAM属于非聚簇索引.

索引覆盖

  1. 如果一个索引包含所有需要查询的字段的值，我们称之为覆盖索引
  2. 不是所有类型的索引都可以称为覆盖索引，覆盖索引必须要存储索引列的值
  3. 不同的存储实现覆盖索引的方式不同，不是所有的引擎都支持覆盖索引，memory不支持覆盖索引

优势:

  1. 索引条目通常远小于数据行大小，如果只需要读取索引，那么mysql就会极大的较少数据访问量
  2. 因为索引是按照列值顺序存储的，所以对于IO密集型的范围查询会比随机从磁盘读取每一行数据的IO要少的多
  3. 一些存储引擎如MYISAM在内存中只缓存索引，数据则依赖于操作系统来缓存，因此要访问数据需要一次系统调用，这可能会导致严重的性能问题
  4. 由于INNODB的聚簇索引，覆盖索引对INNODB表特别有用

细小优化

  • 当使用索引列进行查询的时候尽量不要使用表达式，把计算放到业务层而不是数据库层

  • 尽量使用主键查询，而不是其他索引，因为主键查询不会触发回表查询

  • 使用前缀索引,给字符串类型的字段设置前缀索引,来优化查询速度.

  • 使用索引扫描来排序,

    explan中的type为index时,使用了索引扫描来做排序.

    只有当索引的列顺序和order by子句的顺序完全一致，并且所有列的排序方式都一样时，mysql才能够使用索引来对结果进行排序，

    如果查询需要关联多张表，则只有当orderby子句引用的字段全部为第一张表时，才能使用索引做排序。

  • union all,in,or都能够使用索引，但是推荐使用in.

    union all : explan时会两阶段执行.(union 会去重,union all不会)

    in : explan时与or相同,但show profiles时执行时间比or小.

    or : explan时与in相同.但由于or需要一句一句判断,时间会比in这样集合中筛选来的慢.

  • 范围列可以用到索引

    范围条件: <,<=,>,>=,between

    范围列可以用到索引,但范围列后面的列无法用到索引,索引最多用于一个范围列.

  • 强制类型转换会全表扫描

    where条件中的值类型必须与字段类型一致.

  • 更新十分频繁，数据区分度不高的字段上不宜建立索引

    • 更新会变更B+树，更新频繁的字段建议索引会大大降低数据库性能
    • 类似于性别这类区分不大的属性，建立索引是没有意义的，不能有效的过滤数据，
    • 一般区分度在80%以上的时候就可以建立索引，区分度可以使用 count(distinct(列名))/count(*) 来计算

  • 创建索引的列，不允许为null，可能会得到不符合预期的结果

  • 当需要进行表连接的时候，最好不要超过三张表，因为需要join的字段，数据类型必须一致

    join的三种方式

    • simple nested_loop join:

    • index nested_loop join:

      • 这个要求非驱动表上有索引,可以通过索引来减少比较,加速查询.
      • 在查询时,驱动表会根据关联字段的索引进行查询,只有到匹配到索引后才进行回表查询.

    • block netsted_loop join:

      • 如果join列没有索引,时将驱动表的所有join相关的列都缓存到joinbuffer中,然后批量匹配.
      • 将第一种多次比较合并成一次,降低了非驱动表的访问频率.
      • join_buffer_size默认为256K.会将select的列也存放在buffer中,如果存不下需要适当调高值.
      • 在一个n个join关联的sql中,会在执行时候分配N-1个join buffer.

  • 能使用limit的时候尽量使用limit,在明确只有一条语句的时候 带上limit1.

  • 单表索引建议控制在5个以内,会增加树的大小,增加io量.

  • 组合索引中,单索引字段数不超过5个,由于最左匹配原则,过多的字段需要满足的条件过多,增加了io量,还增加了限制条件.

索引监控

sql: show status like 'Handler_read%';

参数解释:

  1. Handler_read_first：读取索引第一个条目的次数
  2. Handler_read_key：通过index获取数据的次数
  3. Handler_read_last：读取索引最后一个条目的次数
  4. Handler_read_next：通过索引读取下一条数据的次数
  5. Handler_read_prev：通过索引读取上一条数据的次数
  6. Handler_read_rnd：从固定位置读取数据的次数
  7. Handler_read_rnd_next：从数据节点读取下一条数据的次数

只是个大致的索引信息,展示的整体索引使用情况.

空痕影

学而不思则罔,思而不学则殆.
0%