MySQL学习笔记(三)性能优化1

转载 2012年03月25日 22:58:41

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 操作


相关文章推荐

MySQL学习笔记 8 -性能优化

SHOW STATUS LIKE ‘value'; 查询MySQL数据库的性能。 value参数 Connections:连接MySQL服务器的次数Uptime:MySQL服务器的上线时间Sl...

MySQL学习笔记(四)性能优化2

MySQL学习笔记(四)性能优化2 1.高效的模型设计 (1)适度冗余,让查询尽量减少Join 将别的表的数据拿出来一部分放在自己表里面,比如:发布信息表里面不仅保存用户ID,同时保存一份nic...

【学习笔记】《高性能MYSQL》对性能优化定义

本文属于读书笔记,大部分内容摘抄于《高性能MYSQL》,摘抄内容版权属于原作者。 在《高性能mysql》中将西能定义为完成某件任务所需要的时间度量,换句话说,性能即响应时间。按照这个定义,我...

MySQL入门很简单-学习笔记 - 第18章 性能优化

18.1、优化简介 SHOW STATUS LIKE ‘value’;   connections                 连接数 uptime                        ...
  • KimSoft
  • KimSoft
  • 2011年09月04日 16:52
  • 1954

《高性能Javascript》学习笔记 之web性能优化(1)

web性能优化的方法有: 1.脚本加载 (1)把标签放到body标签中, (2)减少script标签的数量   (3)动态脚本加载  (4)脚本合并 2.数据的访问  (1)尽量减少对象成员和数组...
  • vuturn
  • vuturn
  • 2015年01月24日 16:14
  • 401

Mysql学习笔记1

  • 2012年07月25日 16:45
  • 35KB
  • 下载
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:MySQL学习笔记(三)性能优化1
举报原因:
原因补充:

(最多只允许输入30个字)