MySQL数据库优化之索引优化

学习索引优化之前安装演示数据库以供练习

使用mysql提供的sakila数据库

文件下载: http://downloads.mysql.com/docs/sakila-db.tar.gz
解压后导入数据库
shell> mysql -uroot -p < salila-schema.sql
shell> mysql -uroot -p < salika-data.sql

索引优化策略
1.索引列上不能使用表达式或函数

2.前缀索引和索引列的选择性
前缀索引：a.字段长度小; b.索引选择性好

如果选择前2个字符（索引长度是2个字符）作为前缀索引，那么索引选择性为2/4=0.5， 索引选择性不好；
如何选择前3个字符（索引长度是3个字符）作为前缀索引，那么索引选择性为4/4=0.5， 索引选择性好；

备注：对于BLOB，TEXT，或者很长的VARCHAR类型的列，必须使用前缀索引，因为MySQL不允许索引这些列的完整长度

3.联合索引：如何选择索引列的顺序
- 经常会被使用到的列优先
- 选择性高的列优先
- 宽度小的列优先

4.覆盖索引
如果一个索引包含(或覆盖)所有需要查询的字段的值，称为‘覆盖索引’。即只需扫描索引而无须回表
优点：

• 可以优化缓存，减少磁盘IO操作
• 可以减少随机IO，变随机IO操作变为顺序IO操作
• 可以避免对Innodb主键索引的二次查询
• 可以避免MyIsam表进行系统调用

无法使用覆盖索引的情况

• 存储引擎不支持覆盖索引
• 查询中使用了太多的列
• 使用了双%号的like查询

使用索引来有优化查询

1.使用索引扫描来优化排序

• 索引的列顺序和Order By子句的顺序完全一致
  
  联合索引rental_date，列顺序依次为rental_date, inventory_id, customer_id

explain select * from rental where rental_date='2005-05-09' order by  inventory_id, customer_id;

而如果列顺序不一致呢？下面这个query结果出现了filesort

explain select * from rental where rental_date='2005-05-09' order by  customer_id， inventory_id;

• 索引中所有列的方向（升序，降序）和Order by子句完全一致

inventory_id desc 方向不一致，导致filesort的出现

explain select * from rental where rental_date='2005-05-09' order by  inventory_id desc, customer_id;

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

2.模拟Hash索引优化查询：以film表说明

mysql> show columns from film;

对title进行查找，一般需要建立前缀索引，但是这个介绍一种模拟hash索引查询

步骤

mysql> alter table film add title_md5 varchar(32);
mysql> update film set title_md5=md5(title);
mysql> create index idx_md5 on film(title_md5);
mysql> explain select * from film where title_md5=md5('EGG IGBY') and title = 'EGG IGBY';

使用Hash索引优化查询的注意点：

• 只能处理键值的全值匹配查找
• 所使用的Hash函数决定着索引键的大小

3.利用索引优化锁

• 索引可以减少锁定的行数
• 索引可以加快处理速度，同时也加快了锁的释放

索引的维护和优化

1.删除重复和冗余的索引

• 重复索引

• 冗余索引
  

重复的索引是完全不必要的，但是冗余的索引，有的时候有意建立的

如果判断索引是重复还是冗余，使用工具pt-duplicate-key-checker
关于该工具的使用见pt-duplicate-key-checker检查数据库的重复索引

2.查询未被使用的索引：通过sql语句来查询

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

analyze table table_name
# 维护表的碎片
optimize table table_name #使用不当会导致锁表