mysql innodb 索引优化_MySQL索引优化

红太狼 2022-10-25 01:18 317阅读 0赞

一、Btree索引和Hash索引

索引是在存储引擎层实现的，因此不同存储引擎的索引其底层实现不同。

1.1 B-tree索引

特点：

以B+树的结构存储数据

更适合进行范围查找

使用场景：

全值匹配 order\_sn = '982222121'

匹配最左前缀的查询 联合索引，最左列

匹配列前缀

匹配范围值的查询 order\_sn > '999111'

精确匹配左前列并范围匹配另外一列

只访问索引的查询

使用限制：

如果不是按照索引最左列开始查找，则无法使用索引

使用索引时不能跳过索引中的列

not in 和<>操作无法使用索引

如果查询中有某个列的范围查询，则其右边所有列都无法使用索引

1.2 Hash索引

特点：

基于hash表实现的，只有查询条件精确匹配Hash索引中的所有列时，才能够使用到Hash索引。

对于Hash索引中的所有列，存储引擎都会为每一行计算一个Hash码，Hash索引中存储的就是Hash码。

使用限制：

必须进行二次查找

无法使用排序

不支持部分索引查找也不支持范围查找

Hash码的计算也可能存在Hash冲突

1.3 为什么要使用索引

索引大大减少了存储引擎需要扫描的数据量

索引可以帮助我们进行排序以避免使用临时表

索引可以把随机I/O变为顺序I/O

1.4 索引不是越多越好

索引会增加写操作的成本

太多的索引会增加查询优化器的选择时间

二、安装演示数据库

\# 下载

wget http://downloads.mysql.com/docs/sakila-db.tar.gz

\# 解压

tar -xzvf sakila-db.tar.gz

cd sakila-db/

\# 导入

mysql < sakila-schema.sql

mysql < sakila-data.sql

三、索引优化策略

3.1 索引列上不能使用表达式或函数

![20180321\_017.png][20180321_017.png]

3.2 前缀索引和索引列的选择性

create index index\_name on table(col\_name(n))

索引的选择性是不重复的索引值和表的记录数的比值

记录数为4，n为2时选择性为2/4，n为3时选择性为4/4

![20180321\_018.png][20180321_018.png]

3.3 联合索引

如何选择索引列的顺序：

经常会被使用到的列优先

选择性高的列优先

宽度小的列优先

3.4 覆盖索引

查询语句中的所有列都在索引中

覆盖索引优点：

可以优化缓存，减少磁盘I/O操作

可以减少随机I/O，变随机I/O操作为顺序I/O操作

可以避免对InnoDB主键索引的二次查询

可以避免MyISAM表进行系统调用

覆盖索引，无法使用情况：

有的存储引擎不支持覆盖索引，如Memory

查询中使用了太多的列

使用了双%号的like查询

举例：

\# 使用覆盖索引

explain select language\_id from film where language\_id=1\\G

\# 未使用索引

explain select \* from film where language\_id=1\\G

![20180321\_019.png][20180321_019.png]

explain select actor\_id, last\_name from actor where last\_name='Joe'\\G

\# actor\_id未建索引，该查询语句却使用了索引，因为InnoDB为主键自动创建了索引

![20180321\_020.png][20180321_020.png]

3.5 使用索引扫描来优化排序

索引的列顺序和Order By子句的顺序完全一致

索引中所有列的方向(长降)和Order by子句完全一致

Order by中的字段全部在关联表中的第一张表中

\# 复制rental表

\# create table rental\_myisam select \* from rental;

create table rental\_myisam like rental;

\# 将其引擎改为MyISAM

alter table rental\_myisam engine=myisam;

\# 插入数据

insert into rental\_myisam select \* from rental;

explain select \* from rental\_myisam where rental\_date>'2005-01-01' order by rental\_id\\G

explain select \* from rental where rental\_date>'2005-01-01' order by rental\_id\\G

explain select \* from rental where rental\_date>'2015-01-01' order by rental\_id\\G

![20180321\_022.png][20180321_022.png]

![20180321\_021.png][20180321_021.png]

3.6 模拟Hash索引优化查询

![20180321\_023.png][20180321_023.png]

\# 新增一列用来存储title的md5值

alter table film add title\_md5 varchar(32);

\# 更新title\_md5，实际运行中可以通过触发器来更新

update film set title\_md5=md5(title);

\# 为title\_md5创建索引

create index idx\_md5 on film(title\_md5);

\# 注意查询时同时查询title\_md5和title以避免Hash冲突

explain select \* from film where title\_md5=md5('EGG\_IGBY') and title='EGG IGBY'\\G

模拟Hash索引的限制

只能处理键值的全值匹配查找

所使用的Hash函数决定着索引键的大小

3.7 利用索引优化锁

索引可以减少锁定的行数

索引可以加快处理速度，同时也加快了锁的释放

actor建表语句

![20180321\_027.png][20180321_027.png]

演示没有索引情况下带来的锁表问题：

\# 删除索引idx\_actor\_last\_name

drop index idx\_actor\_last\_name on actor;

\# 使用两个mysql命令行演示

\# ---------------命令行1 session1---------------

\# 启动一个事务

begin;

\# 查询并添加一个排他锁

select \* from actor where last\_name='WOOD' for update;

\# ---------------命令行2 session2---------------

\# 启动事务

begin;

\# 查询并添加一个排他锁

select \* from actor where last\_name='Wills' for update;

\# 阻塞了

\# 添加索引后再执行上述操作，session2就不会阻塞了

create index idx\_actor\_last\_name on actor(last\_name);

\# session1释放锁

rollback;

四、索引的维护和优化

4.1 删除重复和冗余的索引

下图是重复索引

![20180321\_025.png][20180321_025.png]

下图是冗余索引

![20180321\_026.png][20180321_026.png]

4.2 pt-duplicate-key-checker

待研究。。。

检查重复的冗余的索引

pt-duplicate-key-checker这款工具也是percona-toolkit中一款非常适用的工具，它可以帮助你检测表中重复的索引或者主键

pt-duplicate-key-checker h=127.0.0.1

4.3 查找未被使用过的索引

select object\_schema, object\_name, index\_name, b.table\_rows

from performance\_schema.table\_io\_waits\_summary\_by\_index\_usage a

join information\_schema.tables b on a.object\_schema = b.table\_schema

and a.object\_name = b.table\_name

where index\_name is not null

and count\_star = 0

order by object\_schema, object\_name;

![20180321\_029.png][20180321_029.png]

SELECT t.TABLE\_SCHEMA, t.TABLE\_NAME, INDEX\_NAME, s.CARDINALITY, t.TABLE\_ROWS

, s.CARDINALITY / t.TABLE\_ROWS AS SELECTIVITY

FROM information\_schema.TABLES t, (SELECT t1.database\_name, t1.table\_name, t1.index\_name, t2.stat\_value AS CARDINALITY

FROM (SELECT database\_name, table\_name, index\_name, MAX(substring(stat\_name, 11)) AS max\_stat\_name

FROM mysql.innodb\_index\_stats

WHERE stat\_name LIKE 'n\_diff\_pfx%'

GROUP BY database\_name, table\_name, index\_name

) t1, mysql.innodb\_index\_stats t2, (SELECT database\_name, table\_name, group\_concat(index\_name) AS all\_indexs

FROM mysql.innodb\_index\_stats

WHERE stat\_name LIKE 'n\_diff\_pfx%'

GROUP BY database\_name, table\_name

) t3

WHERE t2.database\_name = t1.database\_name

AND t2.table\_name = t1.table\_name

AND t2.index\_name = t1.index\_name

AND t2.stat\_name LIKE 'n\_diff\_pfx%'

AND t2.database\_name = t3.database\_name

AND t2.table\_name = t3.table\_name

AND t3.all\_indexs REGEXP '^PRIMARY,'

AND t2.index\_name != 'PRIMARY'

AND substring(t2.stat\_name, 11) = t1.max\_stat\_name - 1

UNION ALL

SELECT t1.database\_name, t1.table\_name, t1.index\_name, t2.stat\_value AS CARDINALITY

FROM (SELECT database\_name, table\_name, index\_name, MAX(substring(stat\_name, 11)) AS max\_stat\_name

FROM mysql.innodb\_index\_stats

WHERE stat\_name LIKE 'n\_diff\_pfx%'

GROUP BY database\_name, table\_name, index\_name

) t1, mysql.innodb\_index\_stats t2, (SELECT database\_name, table\_name, group\_concat(index\_name) AS all\_indexs

FROM mysql.innodb\_index\_stats

WHERE stat\_name LIKE 'n\_diff\_pfx%'

GROUP BY database\_name, table\_name

) t3

WHERE t2.database\_name = t1.database\_name

AND t2.table\_name = t1.table\_name

AND t2.index\_name = t1.index\_name

AND t2.stat\_name LIKE 'n\_diff\_pfx%'

AND t2.database\_name = t3.database\_name

AND t2.table\_name = t3.table\_name

AND t3.all\_indexs REGEXP '^PRIMARY'

AND t2.index\_name = 'PRIMARY'

AND substring(t2.stat\_name, 11) = t1.max\_stat\_name

UNION ALL

SELECT t1.database\_name, t1.table\_name, t1.index\_name, t2.stat\_value AS CARDINALITY

FROM (SELECT database\_name, table\_name, index\_name, MAX(substring(stat\_name, 11)) AS max\_stat\_name

FROM mysql.innodb\_index\_stats

WHERE stat\_name LIKE 'n\_diff\_pfx%'

GROUP BY database\_name, table\_name, index\_name

) t1, mysql.innodb\_index\_stats t2, (SELECT database\_name, table\_name, group\_concat(index\_name) AS all\_indexs

FROM mysql.innodb\_index\_stats

WHERE stat\_name LIKE 'n\_diff\_pfx%'

GROUP BY database\_name, table\_name

) t3

WHERE t2.database\_name = t1.database\_name

AND t2.table\_name = t1.table\_name

AND t2.index\_name = t1.index\_name

AND t2.stat\_name LIKE 'n\_diff\_pfx%'

AND t2.database\_name = t3.database\_name

AND t2.table\_name = t3.table\_name

AND t3.all\_indexs NOT REGEXP '^PRIMARY'

AND t2.index\_name != 'PRIMARY'

AND substring(t2.stat\_name, 11) = t1.max\_stat\_name

) s

WHERE t.table\_schema = s.database\_name

AND t.table\_name = s.table\_name

AND t.table\_rows != 0

AND t.table\_schema NOT IN ('mysql', 'performance\_schema', 'information\_schema')

HAVING SELECTIVITY <=0.4

ORDER BY SELECTIVITY ;

![20180321\_028.png][20180321_028.png]

4.4 更新索引统计信息及减少索引碎片

\# 对索引

analyze table table\_name;

\# 对表 使用不当会导致锁表

optimize table table\_name;

[20180321_017.png]: http://7xkmkl.com1.z0.glb.clouddn.com/20180321_017.png
[20180321_018.png]: http://7xkmkl.com1.z0.glb.clouddn.com/20180321_018.png
[20180321_019.png]: http://7xkmkl.com1.z0.glb.clouddn.com/20180321_019.png
[20180321_020.png]: http://7xkmkl.com1.z0.glb.clouddn.com/20180321_020.png
[20180321_022.png]: http://7xkmkl.com1.z0.glb.clouddn.com/20180321_022.png
[20180321_021.png]: http://7xkmkl.com1.z0.glb.clouddn.com/20180321_021.png
[20180321_023.png]: http://7xkmkl.com1.z0.glb.clouddn.com/20180321_023.png
[20180321_027.png]: http://7xkmkl.com1.z0.glb.clouddn.com/20180321_027.png
[20180321_025.png]: http://7xkmkl.com1.z0.glb.clouddn.com/20180321_025.png
[20180321_026.png]: http://7xkmkl.com1.z0.glb.clouddn.com/20180321_026.png
[20180321_029.png]: http://7xkmkl.com1.z0.glb.clouddn.com/20180321_029.png
[20180321_028.png]: http://7xkmkl.com1.z0.glb.clouddn.com/20180321_028.png