高级DBA运维
成长之路

MySQL索引应用规范及建索引原则

索引应用规范说明

为了使索引的使用效率更高,在创建索引时,必须考虑在哪些字段上创建索引和创建什么类型的索引。那么索引设计原则又是怎样的?

1.建表时必须要有主键,如果没有可以做为主键条件的列,创建无关列

2.选择唯一性索引

唯一性索引的值是唯一的,可以更快速的通过该索引来确定某条记录。例如,学生表中学号是具有唯一性的字段。
为该字段建立唯一性索引可以很快的确定某个学生的信息。如果使用姓名的话,可能存在同名现象,从而降低查询速度。
联合索引时把唯一性的列放在最左侧

优化方案:

(1) 如果非得使用重复值较多的列作为查询条件(例如:男女),可以将表逻辑拆分
(2) 可以将此列和其他的查询类,做联和索引,唯一值多的列做最左列
判断列的唯一值多少方法:
select count(*) from world.city;总行数
select count(distinct countrycode) from world.city;判断列去重
select count(distinct countrycode,population ) from world.city;联合索引可以解决唯一值较少的问题

3.经常需要where 、ORDER BY、GROUP BY,join on,distinct 的条件(业务:产品功能+用户行为)等操作的字段建立索引

排序操作会浪费很多时间。为其建立索引,优化查询

4.使用前缀来索引

如果索引字段的值很长,最好使用值的前缀来索引,可以减少索引树的高度

5.限制索引的数目

索引的数目不是越多越好。
(1) 每个索引都需要占用磁盘空间,索引越多,需要的磁盘空间就越大。
(2) 修改表时,对索引的重构和更新很麻烦。越多的索引,会使录入数据更新表变得很浪费时间。
(3) 优化器的负担会很重,有可能会影响到优化器的选择.
percona-toolkit工具专门分析索引是否有用

6.删除不再使用或者很少使用的索引

percona-toolkit工具包中的pt-duplicate-key-checker工具
sqlyog里的信息模块可以查看。
视图:schema_unused_indexes
表中的数据被大量更新,或者数据的使用方式被改变后,原有的一些索引可能不再需要。数据库管理员
应该定期找出这些索引,将它们删除,从而减少索引对更新操作的影响。

7.大表加索引,要在业务不繁忙期间操作
8.尽量少在经常更新值的列上建索引,可能会导致索引失效

建索引原则

1.必须要有主键,如果没有可以做为主键条件的列,创建无关列
2.经常作为where条件列order by group by join on distinct的条件(业务:产品功能+用户行为)
3.最好使用唯一值多的列作为索引,如果索引列的重复值较多,可以考虑使用联合索引
4.列值长度较长的索引列,建议使用前缀索引
5.降低索引条目,一方面不要创建无用索引,不常使用的索引清理,percona toolkit
6.索引维护要避开业务繁忙期
赞(1)

评论 抢沙发

评论前必须登录!

 

LNMP社群 不仅仅是技术

关于我们网站地图