MySQL数据库性能优化之表结构优化

由于MySQL数据库是基于行(Row)存储的数据库,而数据库操作 IO 的时候是以 page(block)的方式,也就是说,如果我们每条记录所占用的空间量减小,就会使每个page中可存放的数据行数增大,那么每次 IO 可访问的行数也就增多了。反过来说,处理相同行数的数据,需要访问的 page 就会减少,也就是 IO 操作次数降低,直接提升性能。此外,由于我们的内存是有限的,增加每个page中存放的数据行数,就等于增加每个内存块的缓存数据量,同时还会提升内存换中数据命中的几率,也就是缓存命中率。

  数据类型选择

数据库操作中最为耗时的操作就是 IO 处理,大部分数据库操作 90% 以上的时间都花在了 IO 读写上面。所以尽可能减少 IO 读写量,可以在很大程度上提高数据库操作的性能。 Continue reading

MYSQL查询优化:数据类型与效率

这一部分提供了如何选择数据类型来帮助提高查询运行速度的一些指导:

在可以使用短数据列的时候就不要用长的。如果你有一个固定长度的CHAR数据列,那么就不要让它的长度超出实际需要。如果你在数据列中存储的最长的值有 40个字符,就不要定义成CHAR(255),而应该定义成CHAR(40)。如果你能够用MEDIUMINT代替BIGINT,那么你的数据表就小一些 (磁盘I/O少一些),在计算过程中,值的处理速度也快一些。如果数据列被索引了,那么使用较短的值带来的性能提高更加显著。不仅索引可以提高查询速度, 而且短的索引值也比长的索引值处理起来要快一些。 Continue reading

MySQL 数据库性能优化之缓存参数优化[转载]

在平时被问及最多的问题就是关于 MySQL 数据库性能优化方面的问题,所以最近打算写一个MySQL数据库性能优化方面的系列文章,希望对初中级 MySQL DBA 以及其他对 MySQL 性能优化感兴趣的朋友们有所帮助。

这是本系列的第一篇文章:MySQL 数据库性能优化之缓存参数优化

数据库属于 IO 密集型的应用程序,其主要职责就是数据的管理及存储工作。而我们知道,从内存中读取一个数据库的时间是微秒级别,而从一块普通硬盘上读取一个IO是在毫秒级别,二者相差3个数量级。所以,要优化数据库,首先第一步需要优化的就是 IO,尽可能将磁盘IO转化为内存IO。本文先从 MySQL 数据库IO相关参数(缓存参数)的角度来看看可以通过哪些参数进行IO优化

  • query_cache_size/query_cache_type (global)

Query cache 作用于整个 MySQL Instance,主要用来缓存 MySQL 中的 ResultSet,也就是一条SQL语句执行的结果集,所以仅仅只能针对select语句。当我们打开了 Query Cache 功能,MySQL在接受到一条select语句的请求后,如果该语句满足Query Cache的要求(未显式说明不允许使用Query Cache,或者已经显式申明需要使用Query Cache),MySQL 会直接根据预先设定好的HASH算法将接受到的select语句以字符串方式进行hash,然后到Query Cache 中直接查找是否已经缓存。也就是说,如果已经在缓存中,该select请求就会直接将数据返回,从而省略了后面所有的步骤(如 SQL语句的解析,优化器优化以及向存储引擎请求数据等),极大的提高性能。 Continue reading

关于Mysql的Qcache优化

query_cache_size = 64M

指定MySQL查询缓冲区的大小。可以通过在MySQL控制台执行以下命令观察:

# > SHOW VARIABLES LIKE '%query_cache%';
# > SHOW STATUS LIKE 'Qcache%';

# 如果Qcache_lowmem_prunes的值非常大,则表明经常出现缓冲不够的情况;
如果Qcache_hits的值非常大,则表明查询缓冲使用非常频繁,如果该值较小反而会影响效率,那么可以考虑不用查询缓冲;

Qcache_free_blocks,如果该值非常大,则表明缓冲区中碎片很多。

Qcache_free_blocks”:Query Cache 中目前还有多少剩余的blocks。如果该值显示较大,则说明Query Cache 中的内存碎片较多了,可能需要寻找合适的机会进行整理。
● “Qcache_free_memory”:Query Cache 中目前剩余的内存大小。通过这个参数我们可以较为准确的观察出当前系统中的Query Cache 内存大小是否足够,是需要增加还是过多了; Continue reading

MySQL之Handler_read_*

在MySQL里,我们一般使用SHOW STATUS查询服务器状态,语法一般来说如下:

SHOW [GLOBAL | SESSION] STATUS [LIKE 'pattern' | WHERE expr]

执行命令后会看到很多内容,其中有一部分是Handler_read_*,它们显示了数据库处理SELECT查询语句的状态,对于调试SQL语句有很大意 义,可惜实际很多人并不理解它们的实际意义,本文简单介绍一下: Continue reading

根据status信息对MySQL服务器进行优化[精典]

对于SQL查询语句对于服务器系统资源的使用情况见:发现瓶颈 - Profiling(程序剖析) -MySQL Profilinghttp://blog.haohtml.com/archives/4624

网上有很多的文章教怎么配置MySQL服务器,但考虑到服务器硬件配置的不同,具体应用的差别,那些文章的做法只能作为初步设置参考,我们需要根据自己的 情况进行配置优化,好的做法是MySQL服务器稳定运行了一段时间后运行,根据服务器的”状态”进行优化。

mysql> show global status;

可以列出MySQL服务器运行各种状态值,另外,查询MySQL服务器配置信息语句:

mysql> show variables;

一、慢查询

mysql> show variables like '%slow%';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| log_slow_queries | ON    |
| slow_launch_time | 2     |
+------------------+-------+

mysql> show global status like '%slow%';
+---------------------+-------+
| Variable_name       | Value |
+---------------------+-------+
| Slow_launch_threads | 0     |
| Slow_queries        | 4148 |
+---------------------+-------+

配置中打开了记录慢查询,执行时间超过2秒的即为慢查询,系统显示有4148个 慢查询,你可以分析慢查询日志,找出有问题的SQL语句,慢查询时间不宜设置过长,否则意义不大,最好在5秒以内,如果你需要微秒级别的慢查询,可以考虑 给MySQL打补丁:http://www.percona.com/docs/wiki/release:start,记得找对应的版本。

打开慢查询日志可能会对系统性能有一点点影响,如果你的MySQL是主-从结构,可以考虑打开其中一台从服务器的慢查询日志,这样既可以监控慢查 询,对系统性能影响又小。 Continue reading

mysql优化的重要参数 key_buffer_size table_cache 分享

MySQL服务器端的参数有很多,但是对于大多数初学者来说,众多的参数往往使得我们不知所措,但是哪些参数是需要我们调整的,哪些对服务器的性能影响最大呢?对于使用Myisam存储引擎来说,主要有key_buffer_size和table_cache两个参数。对于InnoDB引擎来说主要还是以innodb_开始的参数,也很好辨认。
查看MySQL参数,可以使用show variablesshow status命令查看,前者查看服务器静态参数,即在数据库启动后不会动态更改的值,比如缓冲区、字符集等。后者查看服务器的动态运行状态信息,即数据库运行期间动态变化的信息,比如锁,当前连接数等。 Continue reading

mysql优化数据库对象

12.1优化表的数据类型

表需要使用何种数据类型,是需要根据应用来判断的。虽然应用设计的时候需要考虑字段的长度留有一定的冗余,但是不推荐让很多字段都留有大量的冗余,这样即浪费存储也浪费内存。

我们可以使用PROCEDUREANALYSE()对当前已有应用的表类型的判断,该函数可以对数据表中的列的数据类型提出优化建议,可以根据应用的实际情况酌情考虑是否实施优化。

语法:

SELECT * FROM tbl_name PROCEDURE ANALYSE();

SELECT * FROM tbl_name PROCEDURE ANALYSE(16,256);

输出的每一列信息都会对数据表中的列的数据类型提出优化建议。第二个例子告诉PROCEDUREANALYSE()不要为那些包含的值多于16个或者256字节的ENUM类型提出建议。如果没有这样的限制,输出信息可能很长;ENUM定义通常很难阅读。

在对字段类型进行优化时,可以根据统计信息并结合应用的实际情况对其进行优化。

12.2通过拆分,提高表的访问效率这里我们所说的拆分,主要是针对Myisam类型的表,拆分的方法可以分成两种情况:

1.  纵向拆分:

纵向拆分是只按照应用访问的频度,将表中经常访问的字段和不经常访问的字段拆分成两个表,经常访问的字段尽量是定长的,这样可以有效的提高表的查询和更新的效率。

2.  横向拆分:

横向拆分是指按照应用的情况,有目的的将数据横向拆分成几个表或者通过分区分到多个分区中,这样可以有效的避免Myisam表的读取和更新导致的锁问题。

12.3逆规范化

数据库德规范化设计强调数据的独立性,数据应该尽可能少地冗余,因为存在过多的冗余数据,这就意味着要占用了更多的物理空间,同时也对数据的维护和一致性检查带来了问题。

但是对于查询操作很多的应用,一次查询可能需要访问多表进行,如果通过冗余纪录在相同表中,更新的代价增加不多,但是查询操作效率可以有明显提高,这种情况就可以考虑通过冗余数据来提高效率。

12.4使用冗余统计表

使用createtemporarytable语法,它是基于session的表,表的数据保存在内存里面,当session断掉后,表自然消除。

对于大表的统计分析,如果统计的数据量不大,利用insert。。。select将数据移到临时表中比直接在大表上做统计要效率更高。

12.5选择更合适的表类型

1、如果应用出现比较严重的锁冲突,请考虑是否更改存储引擎到innodb,行锁机制可以有效的减少锁冲突的出现。

2、如果应用查询操作很多,且对事务完整性要求不严格,则可以考虑使用Myisam存储引擎。

更多存储引擎选择的原则,请参考开发篇的相关章节。

mysql的默认查询优先还是更新(insert、update、delete)优先关系

MySQL还允许改变语句调度的优先级,它可以使来自多个客户端的查询更好地协作,这样单个客户端就不会由于锁定而等待很长时间。改变优先级还可以确保特定类型的查询被处理得更快。

我们首先应该确定应用的类型,判断应用是以查询为主还是以更新为主的,是确保查询效率还是确保更新的效率,决定是查询优先还是更新优先。

下面我们提到的改变调度策略的方法主要是针对Myisam存储引擎的,对于Innodb存储引擎,语句的执行是由获得行锁的顺序决定的。

MySQL的默认的调度策略可用总结如下:

1. 写入操作优先于读取操作。

2. 对某张数据表的写入操作某一时刻只能发生一次,写入请求按照它们到达的次序来处理。

3. 对某张数据表的多个读取操作可以同时地进行。

MySQL提供了几个语句调节符,允许你修改它的调度策略:

1. LOW_PRIORITY关键字应用于DELETE、INSERT、LOADDATA、REPLACE和UPDATE。

2. HIGH_PRIORITY关键字应用于SELECT和INSERT语句。

3. DELAYED关键字应用于INSERT和REPLACE语句。

如果写入操作是一个LOW_PRIORITY(低优先级)请求,那么系统就不会认为它的优先级高于读取操作。在这种情况下,如果写入者在等待的时候,第二个读取者到达了,那么就允许第二个读取者插到写入者之前。只有在没有其它的读取者的时候,才允许写入者开始操作。这种调度修改可能存在LOW_PRIORITY写入操作永远被阻塞的情况。

SELECT查询的HIGH_PRIORITY(高优先级)关键字也类似。它允许SELECT插入正在等待的写入操作之前,即使在正常情况下写入操作的优先级更高。另外一种影响是,高优先级的SELECT在正常的SELECT语句之前执行,因为这些语句会被写入操作阻塞。

如果你希望所有支持LOW_PRIORITY选项的语句都默认地按照低优先级来处理,那么请使用--low-priority-updates选项来启动服务器。通过使用INSERTHIGH_PRIORITY来把INSERT语句提高到正常的写入优先级,可以消除该选项对单个INSERT语句的影响。