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 #使用不当会导致锁表
  • 0
    点赞
  • 9
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值