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性能优化的最佳21条经验

今天,数据库的操作越来越成为整个应用的性能瓶颈了,这点对于Web应用尤其明显。关于数据库的性能,这并不只是DBA才需要担心的事,而这更是我们程序员需要去关注的事情。当我们去设计数据库表结构,对操作数据...
  • kaka1121
  • kaka1121
  • 2016年11月29日 17:09
  • 3280

inner join MySQL Profiles 三表内联查询效率优化

今天系统爆出一个慢SQL语句,经过分析确定查询时间过长的原因主要是数据量大三张表内联耗时较长;经过重新设计将不必要的内联逻辑排除,成功的将查询时间降低一个数量级。...
  • mrlichengyi
  • mrlichengyi
  • 2017年06月25日 17:29
  • 518

MySQL性能优化方案总结

MySQL进行优化, 效果: SQL和索引 > 数据库表结构 > 系统配置 > 硬件 ;但成本从低到高。...
  • sinat_23080035
  • sinat_23080035
  • 2016年10月13日 00:26
  • 3847

Mysql性能优化之几个实际优化示例

数据库性能优化的文章铺天盖地,但最重要的是把这些恰当的应用到实际生产环境中,本文以真实的优化案例来详细的介绍Mysql数据库方面的先化技巧,主要的优化技术为:(1)把逐个循环的子查询变为一个查询统计语...
  • cpaqyx
  • cpaqyx
  • 2014年12月06日 17:37
  • 2584

mysql通过将or改成union来优化sql性能问题一例

某系统测试环境有支SQL执行时间较长,开发人员请求dba协助优化。 原SQL如下: SELECT   g.id,           ----省略-----     FROM    g,  ...
  • zengxuewen2045
  • zengxuewen2045
  • 2016年10月27日 22:49
  • 2443

Mysql查询性能优化-善用Explain语句

Mysql查询性能优化-善用Explain语句     在项目中验证sql语句执行效率的时候最直观的方式就是查看其执行时间,但是在线上环境中如果不慎运行一个效率十分低下的sql导致数据库down掉了,...
  • u012091092
  • u012091092
  • 2016年11月28日 14:50
  • 2367

浅析Mysql Join语法以及性能优化

注:转载之>>>http://www.jb51.net/article/50427.htm 内外联结的区别是内联结将去除所有不符合条件的记录,而外联结则保留其中部分。外左联结与外右联结的区别在于...
  • b1198103958
  • b1198103958
  • 2015年08月19日 09:42
  • 654

服务器性能优化和Mysql性能优化

影响性能的几个因素 服务器硬件 CPU 内存 IO子系统 服务器系统 CentOS系统参数优化 sysctlconf 优化 limitconf参数优化 磁盘调度策略 文件系统对性能的影响 数据库存储引...
  • liuzp111
  • liuzp111
  • 2017年01月07日 00:11
  • 3281

性能优化之MySQL优化(三)- count()和max()查询优化

一、max()查询优化         rows一行的值为16451说明需要查询16451行才能找到,当数据量很大的时候查询起来会很耗时,此时我们可以在payment_date列建立索引 ...
  • u011712163
  • u011712163
  • 2016年11月23日 16:36
  • 3306

MySQL性能优化的最佳经验

今天,数据库的操作越来越成为整个应用的性能瓶颈了,这点对于Web应用尤其明显。关于数据库的性能,这并不只是DBA才需要担心的事,而这更是我们程序员需要去关注的事情。当我们去设计数据库表结构,对操作数据...
  • z15818264727
  • z15818264727
  • 2016年08月24日 16:45
  • 5291
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:MySQL学习笔记(三)性能优化1
举报原因:
原因补充:

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