MySQL学习笔记（三）性能优化1

MySQL学习笔记（三）性能优化1

1.MySQL 数据库锁定机制

MySQL的数据库锁定分为表级锁定，行级锁定，页级锁定。Innodb 存储引擎和MyISAM 存储引擎最大区别主要有四点，第一点是缓存机制，第二点是事务支持，
第三点是锁定实现，最后一点就是数据存储方式的差异。

1.1 表级锁定

MySQL的表级锁定分为读锁定和写锁定，通过四个队列来维护这两周锁定，分为当前锁定的正在读写的队列和当前正在等待的读写队列：

• Current read-lock queue (lock->read)
• Pending read-lock queue (lock->read_wait)
• Current write-lock queue (lock->write)
• Pending write-lock queue (lock->write_wait)

读锁定：

当一个客户端在申请读请求的时候，需要满足两个条件：

1、请求的读锁定资源没有被写锁定；

2、被锁定等待资源（Pending write-lock queue）中没有更高优先级的锁定

如果满足上面两个条件时，则将读请求放入Current read-lock queue队列，如果没有满足条件时，则放入Pending read-lock queue中等待资源释放。

写锁定：

当一个客户端发出写锁定请求的时候，首先判断Current write-lock queue是否存在，不存在的话，再检查Pending write-lock queue，如果找到了，则进入Pending write-lock queue，并暂停自身线程，如果没有找到，则再检查Current read-lock queue，如果有锁定存在，一样需要等待。总结起来检查的顺序是：

Current read-lock queue --> Pending write-lock queue --> Pending write-lock queue

优化：

1.缩短锁定时间，(拆分sql，建立索引，控制字段)

2.分离能并行的操作（并发插入特性Concurrent_insert属性）

3.合理利用读写优先级

1.2 行级锁定

行级锁定不是MySQL自身实现的锁定方式，而是由其他的存储引擎如：Innodb实现的，而Innodb是目前事务型存储引擎中使用最为广泛的存储引擎，它使用的是“Next key locking”间隙锁“，锁定的是指向记录的前一个索引键和最后一个索引键之间的空域，有下面几个特点：

1.无法利用索引时，采用表级锁定。

2.使用的索引键可能包含一些不被需要的值时，也会被锁定。

3.使用索引定位数据的时候，索引键一样但是数据行不同的时候，一样会被锁定。

优化：

1.尽可能的使用索引，

2.合理设计索引

3.减少过滤条件

4.控制事务大小

2.Query 优化

1. 优化更需要优化的Query；

2. 定位优化对象的性能瓶颈；

3. 明确的优化目标；

4. 从Explain 入手；

5. 多使用profile

6. 永远用小结果集驱动大的结果集；

7. 尽可能在索引中完成排序；

8. 只取出自己需要的Columns；

9. 仅仅使用最有效的过滤条件；

10. 尽可能避免复杂的Join 和子查询；

创建索引的条件：

1.较频繁作为插入条件的字段是和建立索引；

2.唯一性太差的字段不适合创建索引；（每个数据页会存放多条记录，频繁的读取不同的数据页）

3.更新频繁的数据不适合创建索引；

4.不会出现在where里面的字段不适合创建索引

Join语句的优化：

joinbuffer的概念，

1.尽可能减少join中嵌套循环的次数

2.优先优化内层循环

3.保证join条件字段被索引

4.加大join buffer的设定

group by语句的优化：

1.使用松散索引扫描实现group by

如下图所示：

这样读取数量与分组数量一致，但是有使用范围：

(1).group by字段必须是索引中最前面的位置；

(2).只能使用max和min两个聚合函数

(3).引用别的字段的时候必须以常量形式存在。

2.使用紧凑索引实现,

区别在于扫描索引时，读取所有满足条件的索引值，然后根据条件group by来得到结果

3.使用临时表

1. 尽可能让MySQL 可以利用索引来完成GROUP BY 操作，当然最好是松散索引扫描的方式最佳。
在系统允许的情况下，我们可以通过调整索引或者调整Query 这两种方式来达到目的；
2. 当无法使用索引完成GROUP BY 的时候，由于要使用到临时表且需要filesort，所以我们必须
要有足够的sort_buffer_size 来供MySQL 排序的时候使用，而且尽量不要进行大结果集的GROUP
BY 操作，因为如果超出系统设置的临时表大小的时候会出现将临时表数据copy 到磁盘上面再进行
操作，这时候的排序分组操作性能将是成数量级的下降；

DISTINCT 实际上和GROUP BY 的操作非常相似，只不过是在GROUP BY 之后的每组中只取出一条记
录而已。所以，DISTINCT 的实现和GROUP BY 的实现也基本差不多，没有太大的区别。同样可以通过松
散索引扫描或者是紧凑索引扫描来实现，当然，在无法仅仅使用索引即能完成DISTINCT 的时候，MySQL
只能通过临时表来完成。但是，和GROUP BY 有一点差别的是，DISTINCT 并不需要进行排序。也就是
说，在仅仅只是DISTINCT 操作的Query 如果无法仅仅利用索引完成操作的时候，MySQL 会利用临时表
来做一次数据的“缓存”，但是不会对临时表中的数据进行filesort 操作