索引调优技巧

长字段的索引

示例

SELECT
    * 
FROM
    employees 
WHERE
    first_name = 'Duangkaew'

如果索引对应的字段内容长度一般,可以直接使用索引即可,如果内容的长度特别长,那么创建索引占用的空间也就会比较大,而且查询的要率也比较低

方法
创建额外的字段,如first_name_hash,存储时,使用mysql内置的CRC32("123...")方法存储

INSERT INTO employees 
( emp_no,
birth_date, 
first_name, 
last_name, 
gender, 
hire_date, 
first_name_hash )
VALUES
    (111111,
    '2022-12-07',
    'beo........',
    '12',
    'M',
    '2022-11-07',
    CRC32( 'beo........' ))

只需在first_name_hash上添加索引即可,查询修改为

SELECT
    * 
FROM
    employees 
WHERE
    first_name_hash = CRC32('Duangkaew')
AND -- 即便存在hash冲突,也能返回正确结果
    first_name = 'Duangkaew'

first_name_hash应具备以下条件:

  • 字段的长度应该比较小,SHA1/MD5 是不合适的
  • 应当尽量避免hash冲突,目前比较流行的是CRC32()和FNV64()

示例

SELECT
    * 
FROM
    employees 
WHERE
    first_name = 'Duangka%'

对于这种索引,是无法通过添加额外列进行处理,但是mysql支持前缀索引

alter table employees add key (first_name(5))

索引值越大,性能越差,可以根据索引选择性公式进行设置


索引选择性 = 不重复的索引值/数据表的总记录数

对应的sql示例

select count(distinct LEFT(first_name,5))/count(*) from employees

如果业务场景是通过后缀进行查询,但是mysql是没有后缀索引的,所以可以通过建立一个新的字段,例:first_name_reverse ,存储的时候,将字段反转过来进行存储,然后对该字段添加前缀索引,即可实现后缀索引
局限性
无法做order by,group by ;无法使用覆盖索引

单列索引 VS 组合索引

  • SQL 存在多个条件,多个单列索引,会使用索引合并
  • 如果出现索引合并,往往说明索引不够合理
  • 如果SQL暂时没有性能问题,暂时可以不做处理
  • 组合索引要注意索引列顺序,要符合【最左前缀原则】

示例

SELECT
    * 
FROM
    salaries 
WHERE
    from_date = '1986-05-06' 
    AND to_date = '1987-05-06'

在未创建索引的时候,运行sql耗时:
file

接着,为这两个字段分别创建单列索引后再次运行

ALTER TABLE employees.salaries 
ADD INDEX f(from_date),
ADD INDEX t(to_date);

file

使用explain查看运行情况
file
type提示触发了索引合并,使用的key为f和t,extra中提示为f和t做了求交集的操作

接着删除所有的索引,然后为这两个字段创建组合索引,再次运行

ALTER TABLE employees.salaries 
DROP INDEX f,
DROP INDEX t,
ADD INDEX f_t(from_date, to_date);

file

运行explain
file
type变成了ref,性能比两个单列索引性能触发的index_merge要好一些,因为两个索引的求交集行数并不多,多以示例的差异性并不明显,如果求交集的行数增多,那么求交集的开销也会增加,那么性能差异就会更加明显

覆盖索引

对于索引x,select的字段只需要从索引中就能获得,而无需到表数据里获取,这样的索引就叫覆盖索引。
对于查询字段非主键索引时,如果不覆盖索引,需要先通过非主键获取到主键,然后再通过主键到表中找到对应的数据,如果覆盖索引,则可以直接返回索引,省略掉两步

  • 尽量只返回想要的字段,避免写 select * ,这样如果索引的条件能满足就回使用覆盖索引,同时减少网络的开销
SELECT
    from_date ,
    to_date
FROM
    salaries 
WHERE
    from_date = '1986-05-06' 
    AND to_date = '1987-05-06'

运行EXPLAIN
file
使用覆盖索引,并不会修改执行过程,但是一旦使用了覆盖索引,会在extra中提示useing index
file
相应的查询时间,也有所减少

重复索引、冗余索引、未使用的索引

索引也不是万能的,在增删改的时候,索引的维护也是有开销的。索引越多,开销愈大,所以在条件允许的情况下,要创建尽可能少的索引。

重复索引

在相同的列上按照相同的顺序创建的索引

应该尽量避免重复索引,如果发生重复索引,应该删除

冗余索引

如果已经存在了索引Index(A,B),又创建了索引Index(A),那么索引Index(A)就是索引Index(A,B)的冗余索引

冗余索引也要尽量避免,但是要注意,避免掉进坑里

示例
为salaries表创建索引Index(from_date, to_date),然后用Explain执行语句

EXPLAIN SELECT
    * 
FROM
    salaries 
WHERE
    from_date = '1986-06-26' 
ORDER BY
    emp_no;

结果
file

type为ref,extra为Using where; Using filesort,无法为order by是用索引,使用了文件排序

为salaries表创建索引Index(from_date),然后用Explain执行语句
file
type为ref,extra为Using where;

因为对于非主键索引,会在叶子节点上存储主键值,所以会先查找存储的主键值,然后再去查找对应的数据,所以对于Index(from_date)来说,某种意义上,等同于Index(from_dateemp_no),所以对于order by 字句也可以使用索引,而对于Index(from_date, to_date)来说等同于Index(from_date, to_dateemp_no),但是我们的查询语句中并没有to_date,所以order by 字句无法使用索引。
所以如果项目中出现了这种的冗余索引,是不可删除的。

未使用的索引

某个索引根本就没有使用过。
需要避免,直接删除

发表回复

您的电子邮箱地址不会被公开。 必填项已用*标注