MySQL数据库Query的优化
一.MySQL Query Optimizer基本工作原理.
二.Query语句优化基本思路和原则
a) 永远用小结果集驱动大的结果集,(Join语句)
b) 尽可能在索引中完成排序
c) 只取自己需要的Columns
d) 仅仅使用最有效的过滤条件
e) 尽可能避免复杂的Join和子查询
f) 合理设计并利用索引
三.MySQL中主要的四种索引类型
a) B-Tree索引
- MySQL数据库使用最频繁的索引,以B-Tree的数据结构存储
- Innodb存储引擎是B+Tree(分为主键索引和Secondary index)主键索引效率较高
b) Hash索引
- 仅Memory存储引擎使用,通过Hash算法一次定位,效率最高
- 弊端:只能进行= in<=>查询,不能范围查询,不能避免表扫描,排序
c) Full-text索引
- 仅MyISAM支持,全文索引,模糊查找时使用like%%
d) R-Tree索引
- 用于范围查找,B-Tree索引不行
四.索引的利弊
a) 提高检索效率,降低IO成本
b) 降低数据的排序成本
c) 更新索引的字段需要额外更新索引
五.判断是否需要创建索引
a) 较频繁的作为查询条件的字段应该创建索引
b) 唯一性较差的字段不适合单独创建索引,即使频繁作为查询条件()
c) 更新非常频繁的索引不适合创建索引
d) 不出现在where子句中的字段不该创建索引
六.MySQL使用索引的限制
a) MyISAM存储引擎索引键长度总和不能超过1000字节
b) BLOB和TEXT类型的列只能创建前缀索引
c) Mysql目前不支持函数索引
d) 使用不等于(!=或者<>)的时候MySQL无法使用索引
e) 过滤字段使用了函数运算后(如abs(column))MySQL无法使用索引
f) Join语句中Join条件字段类型不一致的时候MySQL无法使用索引
g) 使用LIKE操作的时候如果条件以通配符开始(‘%avd...’)MySQL无法使用索引
h) 使用非等值查询的时候MySQL无法使用Hash索引
七.Joiny的实现原理及优化思路
a) 算法是Nested Loop Join即通过驱动表的结果集作为循环基础数据进行循环
b) 尽可能减少Join语句的Nested Loop的循环总次数,即用小结果集驱动大结果集
c) 优先优化Nested Loop的内层循环
d) 保证Join语句中被驱动表上Join条件字段已经被索引(优化内循环的实现优化方法)
e) 在无法保证被驱动表的Join条件字段被索引且内层充足的情况下,设置Join Buffer
八.ORDRE BY,GROUP BY,和DISTINCT的优化
a) ORDER BY的实现及优化
- 一种是通过有序索引直接获得有序数据
- 另一种是通过MySQL的排序算法将数据排序
- 共两种算法,第一种只取排序字段,排序完在去其他字段
- 第二种全部取出,非排序字段放入一块内存区域
- 节省了IO操作,利用内存空间换取时间的优化方式
iii.尽量使用第二种排序算法,减少IO操作,提高排序工作效率
1.加大max_length_for_sort_data参数的设置(字段的最大长度)
2.去掉不必要的返回字段
3.增大sort_buffer_size参数设置(减少排序中将数据进行分段)
b) GROUP BY的实现和优化
i. GROUP BY也需要排序操作,比ORDER BY 多了分组操作.实现由三种方式
1.松散索引扫描实现,即完全利用索引的时候,不必扫描所有的索引
2.紧凑索引扫描,扫描所有索引
3.使用临时表实现
ii.尽可能通过索引完成group by操作,
Iii.不能利用索引的时候,设置sort_buffer_size来供排序的时候建立临时表
尽量不要大结果集的group by超过临时表的大小将copy到磁盘上,效率急速下降
c).DISTINCT的实现和优化
i. 和group by 的操作相似,只抽取一条记录
ii. 在利用临时表distinct的时候,不排序
九.MySQL数据库Schema设计的性能优化
1.高效的模型设计
a) 适量冗余,让Query减少Join
b) 大字段垂直拆分(user和userinfo,类型text的字段等大字段)
c) 大表水平拆分-基于类型的分拆优化(普通人发的帖子和管理员发的置顶帖子分开)
d) 统计表-准实时优化(定时统计而不是随时统计)(总帖数,回帖数,top榜)
2.合适的数据类型
a) 尽量少使用浮点型,可通过乘以一个固定值转整数存储,高效
十.MySQL Server性能优化
a) 日志设置优化
- 系统默认只打开错误日志