MySql数据库优化维度
MySql数据库优化维度
首先,正常来说一个业务对应的数据,有很多复杂的数据库操作。不管是刚开始的业务,还是越发复杂的业务,都需要考虑数据库的设计、性能,并如何去调优。
数据库设计步骤
选择合适数据库
根据业务需求动向,分析业务数据的处理行为,分成两大类:联机事务处理OLTP(on-line transaction processing)、联机分析处理OLAP(On-Line Analytical Processing)
数据表结构关系设计
一些比较复杂的大业务,可通过一些方式拆分业务,合理的建立一个业务表之间的关系,以及利用一些技巧提高一些效率。
- 表结构要尽量遵循三范式的原则
- 如果分析查询表比较多,尤其是需要进行多表联查的时候(尽量减少多表联查),可以采用反范式进行优化。反范式采用空间换时间的方式,通过增加冗余字段提高查询的效率。
- 表字段的数据类型选择,关系到了查询效率的高低以及存储空间的大小。一般来说,如果字段可以采用数值类型就不要采用字符类型;字符长度要尽可能设计得短一些。针对字符类型来说,当确定字符长度固定时,就可以采用 CHAR 类型;当长度不固定时,通常采用 VARCHAR 类型。
数据表的结构设计很基础,也很关键。好的表结构可以在业务发展和用户量增加的情况下依然发挥作用,不好的表结构设计会让数据表变得非常臃肿,查询效率也会降低。
查询优化
看到一个sql的时候,首先需要考虑分析的是sql的业务逻辑是什么,想要干什么。
在上一步设计好业务数据表结构之间的关系,则sql的逻辑将不会复杂,大多都是单表查询,极个别会联表查询。
主要的查询优化步骤
- 只要简单的看一下Sql语句是否符合规范
- 根据数据特性查看是否需要建立索引,Explain分析一下索引使用情况:MySQL中EXPLAIN执行计划分析
- 分析一下查询是否有回表问题
- 深度的看一下复杂语句(Group By、Order By等)是否跟索引有关系
- 检查数据库的配置,如Join_buffer、Buffer_pool等
Sql语句是否符合规范
• 能够快速缩小结果集的 WHERE 条件写在前面,如果有恒量条 件,也尽量放在前面 ,例如 where 1=1
(这个我觉得顺序没有太大关系)
• 避免使用 GROUP BY、DISTINCT 等语句的使用,避免联表查询和子查询
使用GROUP BY时,建议在建索引时考虑GROUP BY后面字段的顺序,减少排序,与 Order By类似,都可以利用索引来排序
DISTINCT注意使用,后面的所有字段去重
• 能够使用索引的字段尽量进行有效的合理排列
适量建索引,过多也会产生问题,如:导致占有内存过多,Buffer_Pool占用
• 针对索引字段使用 >, >=, =, <, <=, IF NULL 和 BETWEEN 将会 使用索引,如果对某个索引字段进行 LIKE 查询,使用 LIKE ‘%abc%’ 不能使用索引,使用 LIKE ‘abc%’ 将能够使用索引
使用的时候尽量使用Explain分析一下是否真用上了(一般很准确的)
• 如果在 SQL 里使用了 MySQL部分自带函数,索引将失效
因为带函数了,谁知道你想咋转换,咋玩的,导致全表扫描,针对的是左边字段的函数,还有类型转换也会导致索引失效
• 避免直接使用 select *,只取需要的字段,增加使用覆盖索引使用的可能
注意覆盖索引,数据数量大,效果很明显
• 对于大数据量的查询,尽量避免在 SQL 语句中使用 order by 字句
用的时候注意利用索引,来减少排序
• 连表查询的情况下,要确保关联条件的数据类型一致,避免嵌套子查询
数量大的时候注意是否创建被驱动表的的索引 (INL)
• 对于连续的数值,使用 Between 代替 In
在数据范围连续的情况,推荐使用Between
• 对于Exist和In的的使用需要注意实际业务数据大小
In的内表全扫描,Exist外表全扫描。 外表小时用exist,内表小时用in。核心是尽量扫描小的数据,将大的数据使用索引
• where 语句中尽量不要使用 CASE 条件
会导致sql复杂,且不宜理解
• 当只要一行数据时使用 LIMIT 1
索引使用
• 索引占磁盘空间,不要重复的索引,尽量短
• 只给常用的查询条件加索引
• 过滤性高的列建索引,取值范围固定的列不建索引
• 唯一的记录添加唯一索引
• 频繁更新的列不要建索引
• 不要对索引列运算
• 同样过滤效果下,保持索引长度最小
• 合理利用组合索引,注意索引字段先后顺序
• 多列组合索引,过滤性高的字段最前
• order by 字段建立索引,避免 filesort
• 组合索引,不同的排序顺序不能使用索引
• <>!=无法使用索引
参考地址:https://developer.aliyun.com/article/709387
坚定信念,持之以恒
作者:坚持到底gl
本文版权归作者和博客园共有,欢迎转载,但未经作者同意必须在文章页面给出原文连接,否则保留追究法律责任的权利。