mysql innodb 索引优化_MySQL索引优化

一、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 索引列上不能使用表达式或函数

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

create index index_name on table(col_name(n))

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

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

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

explain select actor_id, last_name from actor where last_name='Joe'\G

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

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

3.6 模拟Hash索引优化查询

# 新增一列用来存储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建表语句

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

# 删除索引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 删除重复和冗余的索引

下图是重复索引

下图是冗余索引

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;

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 ;

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

# 对索引

analyze table table_name;

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

optimize table table_name;