第12课:Mysql优化技巧

Mysql 性能优化

一.系统级优化
加SSD硬盘固态硬盘,因为数据库数据是放在硬盘上的,硬盘的读写速度直接影响了数据库效率
升级到最新的稳定版本,数据库每个大版本都有很大的性能提升,有时候半天的优化不如升级版本来的实在

二.常用优化方式
1. 选取最适用的字段属性
2. 使用连接(JOIN)来代替子查询,当然了,join也是要尽量避免使用的,最好是对SQL拆分,保持原子性操作
3. 使用联合 (UNION)来代替手动创建的临时表
4. 使用索引,这个是最常用的SQL优化手段,但是注意不要滥用,索引毕竟也是有代价的,写操作会变慢
在建有索引的字段上尽量不要使用函数进行操作,会导致索引失效
5. 在海量查询时尽量少用格式转换
6. 尽量少用select *查询,把要查的字段完整写出来
7. 用explain来调试优化SQL
8. 任何对列的操作都将导致表扫描,它包括数据库函数、计算表达式等等,查询时要尽可能将操作移至等号右边
9. IN、OR子句常会使用工作表,使索引失效。如果不产生大量重复值,可以考虑把子句拆开。拆开的子句中应该包含索引
10. 尽量少用连表操作主要的表和大表,尽量拆成多条SQL在程序层面进行拼装组合
11. 只要能满足你的需求,应尽可能使用更小的数据类型:例如使用MEDIUMINT代替INT 
12. 尽量把所有的列设置为NOT NULL,如果你要保存NULL,手动去设置它,而不是把它设为默认值.不能用null作索引,任何包含null值的列都将不会被包含在索引中
13. 尽量少用TEXT、BLOB类型
14. 优化配置文件my.cnf
back_log         如果需要大量新连接,修改它。
thread_cache_size         如果需要大量新连接,修改它。
key_buffer_size         索引页池,可以设成很大。
bdb_cache_size         BDB表使用的记录和键吗高速缓存。
table_cache         如果有很多的表和并发连接,修改它。
delay_key_write         如果需要缓存所有键码写入,设置它。
log_slow_queries         找出需花大量时间的查询。
max_heap_table_size         用于GROUP BY
sort_buffer         用于ORDER BY和GROUP BY
myisam_sort_buffer_size         用于REPAIR TABLE
join_buffer_size         在进行无键吗的联结时使用。
15.尽量避免性能很差的模糊查询,distinct查询与随机 order by rand()
16.用EXISTS替代IN、用NOT EXISTS替代NOT IN
(高效)SELECT * FROM  EMP (基础表)  WHERE  EMPNO > 0  AND  EXISTS (SELECT 'X'  FROM DEPT  WHERE  DEPT.DEPTNO = EMP.DEPTNO  AND  LOC = 'www.godeye.org')
(低效)SELECT  * FROM  EMP (基础表)  WHERE  EMPNO > 0  AND  DEPTNO IN(SELECT DEPTNO  FROM  DEPT  WHERE  LOC = 'www.godeye.org')
17.Where子句中条件的顺序对性能没有影响,注意,额外说一下,这里只是说条件的顺序,不包含表的顺序。在RBO优化器模式下,表应按结果记录数从大到小的顺序从左到右来排列,因为表间连接时,最右边的表会被放到嵌套循环的最外层。最外层的循环次数越少,效率越高
18.实际操作中还会碰到批量操作,你是选择一条条的循环写数据库,还是选择用where in语句,多个ID一起执行。大部分情况下,我们选择用经常说的效率很低的in语句,经过测试,in语句效率再低,他是一条数据操作,比循环连接数据库,不停的批量写效果要好

如何调试
1. 使用 show processlist查看当前MYSQL的线程
2. 用explain来优化SQL
   返回值中有一个type字段
   type显示的是访问类型,是较为重要的一个指标,结果值从好到坏依次是:
   system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL
   一般来说,得保证查询至少达到range级别,最好能达到ref
3. 可以通过慢查询日志定位那些执行效率较低的 sql 语句