Mysql系统优化整理

Mysql系统优化整理

时间：2010-8-16

一、  Mysql参数调优

1.1   通用类

key_buffer_size

含义：用于索引块的缓冲区大小，增加它可得到更好处理的索引（对所有读和多重写）。

影响：对于MyISAM表的影响不是很大，MyISAM会使用系统的缓存来存储数据，所以大量使用MyISAM表的机器内存很快就会耗尽。但是，如果你将该值设得过大（例如，大于总内存的50%），系统将转换为页并变得极慢。MySQL依赖操作系统来执行数据读取时的文件系统缓存，因此你必须为文件系统缓存留一些空间。

建议：先设置为内存的25%，观察性能变化。

心得：这个值是控制索引的缓冲区大小的。调整它能够修改缓冲区大小从而改善表的读操作。其中MyISAM类型的数据表是使用系统的缓存来存储数据的。

 

table_open_cache

含义：为所有线程打开表的数量。

影响：增加该值能增加mysqld要求的文件描述符的数量。可以避免频繁的打开数据表产生的开销。打开一个表的开销可能很大，因为MyISAM会把MYI文件的文件头标识为正在使用，所以在内存中做这个操作比较好。，因为每个线程都需要打开表，连接数越大这个值要越大。

建议：我们有300多个表的话，大约2048差不多了。

心得：跟表的个数有关

thread_cache_size

含义：缓存可重用的线程数。

影响：这个参数设置线程的缓存，线程的创建和销毁的开销可能很大，因为每个线程的连接/断开都需要。如果应用程序中有大量的跳跃并发连接并且线程较多的话，就要加大它的值。它的目的是在通常的操作中无需创建新线程。

建议：通常至少设置为16。

 

query_cache_size

含义：指定MySQL查询结果缓冲区的大小。

影响：如果应用程序有大量读，而且没有应用程序级别的缓存，那么这很有用。不过不要设置太大，因为维护它也需要不少开销，这会导致MySQL变慢。

建议：通常设置为 32-512Mb。设置完之后最好是跟踪一段时间，查看是否运行良好。在一定的负载压力下，如果缓存命中率太低了，就启用它，如果命中率已经不错了，就可以把他调小一点。对于2G的内存，先从16M开始，一倍一倍的增加，直到命中率比较稳定为止。

query_cache_limit

含义：缓存单条SQL的结果集上限。默认4KB。

影响：当一条SQL返回的结果集大于这个限制的时候，将不被MySQL缓存。

建议：设置为1M即可。

query_cache_min_res_unit

含义：缓存为每个数据集存放的最小内存大小，默认4KB。

影响：如果太小的话，将会导致MySQL经常访问内存块去获取信息，如果设置太大则浪费内存。

建议：如果SQL返回的结果集都很小的话，参数也可以调小一点，避免内存浪费。如果结果集大部分都大于4KB的话，则考虑加大参数。

back_log

含义：在MySQL的连接请求等待队列中允许存放的最大连接请求数。系统默认值为50。

影响：如果系统在一个短时间内有很多连接，则需要增大该参数的值，该参数值指定到来的TCP/IP连接的侦听队列的大小。不同的操作系统在这个队列大小上有它自 己的限制。试图设定back_log高于你的操作系统的限制将是无效的。（OS有自已的TCP连接个数队列的大小限制）

建议：对于Linux系统推荐设置为小于512的整数。

sort_buffer_size

含义：为每个需要进行排序的线程分配该大小的一个缓冲区。

影响：增加这值加速ORDER BY或GROUP BY操作。不过该参数对应的分配内存是每连接独占的，如果有100个连接，那么实际分配的总共排序缓冲区大小为100×sort_buffer_size。

建议：一般设置为2M观察变化再调整。

read_buffer_size

含义：顺序查询操作所能使用的缓冲区大小。

影响：和sort_buffer_size一样，该参数对应的分配内存也是每连接独享。

建议：一般设置为2M再观察变化。

read_rnd_buffer_size

含义：随机查询操作所能使用的缓冲区大小。

影响：每个线程独享。

建议：一般设置为2M再观察变化。

query_cache_type

含义：查询缓冲类型。

影响：为1是使用缓冲，2是除非使用SQL_CACHE才进行缓冲。对于缓冲而言，数据并不是实时的，有一定的延时。但是对于实时性要求不高的查询短时间内多次执行，是不划算的，这个时候就需要缓存。并且缓存中是区分空格和大小写的，如果大小写不一致和空格不一致，也会认为是不同的SQL，不会利用到缓存。虽然不设置查询缓冲，有时可能带来性能上的损失，但有一些SQL语句需要实时地查询数据，或并不经常使用(可能一天就执行一两次)，这样就需要把缓冲关了，可以采用临时关闭的方法SELECT SQL_NO_CACHE。

建议：一般设置为1。

MyISAM类：

myisam_sort_buffer_size

MyISAM表发生变化时重新排序所需的缓冲。一般64M足矣。

InnoDB类：

innodb_buffer_pool_size

对InnoDB的效率影响很大。因为InnoDB会把尽可能多的数据和索引缓存在缓冲区，这个类似与Oracle的Buffer Pool：如果只采用InnoDB，可以把这个参数调大一点，大约内存的70%左右。

当然，如果数据量不会暴增并且不是特别大，这个参数还是不要太大了，浪费空间。

innodb_additional_pool_size

对数据库性能影响不是很大，至少内存足够的机器上不会有什么影响。

innodb_log_file_size

在高写入负载尤其是数据集很大的时候，这个值非常重要，值越高性能越好，不过可能会增加数据恢复的时候。我设置为128M。

innodb_log_buffer_size

默认的设置在中等强度的写入负载及短事物处理时，性能还可以。但是存在大量更新操作或者负载较大时，就要慢慢增加这个参数的值了。不过不要设置太大，会浪费内存。它每秒都会刷新一次，所以不用设置超过1s所需的内存空间，16M足够了。

innodb_flush_logs_at_trx_commit

这个参数对InnoDB及其重要，设置不好的话会比MyISAM慢1000倍！默认是1，这就是说每次更新事务都会被提交到磁盘，这是非常消耗资源的，硬盘和内存的速度是明显数量级的差距。

设置为0是最快的，但是很不安全，全部在缓存中，一掉电全没了。

设置为1很不好，每次都去写硬盘，没有必要。

设置为2是比较好的，日志不刷新到磁盘上，只刷新到操作系统缓存上。然后每秒钟写缓存。相对于现在数据库每秒4K条左右的SQL，性能已经可以提高不少。

二、解决MyISAM的表锁方案

表锁的特征：使用show processlist命令后，看到出现了很多状态为LOCKED的sql. 使用show status like 'table%'检查Table_locks_immediate和Table_locks_waited，发现Table_locks_waited偏 大。出问题的表是MyISAM，分析大概是MyISAM的表锁导致。

MyISAM适合于读频率远大于写频率这一情况。而我目前的应用可能会出现在某一时段读写频率相当。大致如下：

• 一个客户端发出需要长时间运行的SELECT
• 其他客户端在同一个表上发出INSERT或者UPDATE，这个客户将等待SELECT完成
• 另一个客户在同一个表上发出另一个SELECT；因UPDATE或INSERT比SELECT有更高有优先级，该SELECT将等待UPDATE或INSERT完成，也将等待第一个SELECT完成

也就是说对MyISAM表的读操作，不会阻塞其他用户对同一表的读请求，但会阻塞对同一表的写请求；对 MyISAM表的写操作，则会阻塞其他用户对同一表的读和写操作；MyISAM表的读操作与写操作之间，以及写操作之间是串行的！（难道是表级锁的？INNODB是行锁的。一个写操作不会影响到其他的写处理！）

 

解决方案大概有如下几种：

1.     MyISAM存储引擎有一个系统变量concurrent_insert，专门用以控制其并发插入的行为，其值分别可以为0、1或2。

o        0 不允许并发操作

o        1 如果MyISAM表中没有空洞（即表的中间没有被删除的行），MyISAM允许在一个进程读表的同时，另一个进程从表尾插入记录。这也是MySQL的默认设置。(这就是为什么我们不要做delete操作了用这个操作会出现空洞)

o        2 无论MyISAM表中有没有空洞，都允许在表尾并发插入记录

2.     使用--low-priority-updates启用mysqld。这将给所有更新(修改)一个表的语句以比SELECT语句低的优先级。在这种情况下，在先前情形的最后的SELECT语句将在INSERT语句前执行。(写不要影响到读)

3.     为max_write_lock_count设置一个低值，使得在一定数量的WRITE锁定后，给出READ锁定

4.     使用LOW_PRIORITY属性给于一个特定的INSERT，UPDATE或DELETE较低的优先级

5.     使用HIGH_PRIORITY属性给于一个特定的SELECT

6.     使用INSERT DELAYED语句

综合自己的业务需求，使用了方案2。看来需要不断监测服务器状态，再进行更合适的调整。
让读的优先级最高了。这样的话会优先考虑做SELECT处理。再去做CURD操作

结论：

1.     在启动MYSQLD的时候指定一个参数让写的优先级低于读操作!

2.     指定一个高的优先级给SELECT语句、或给定一个低的优先级给一个写语句

 

二、  一个查杀死锁的SHELL脚本

for id in `mysqladmin processlist | grep -i locked | awk '{print $1}'`
do
mysqladmin kill ${id}
done

 

三、  提升MYSQL查询的性能

3.1 通过查询缓冲提高查询的速度

在写SQL语句时，应尽量减少空格的使用，尤其是在SQL首和尾的空格。（减少空格的使用）

不想使用缓冲区的数据可以这样写

SELECT SQL_NO_CACHE * from table1

想使用缓冲区的数据可以这样写

SELECT SQL_CACHE * from table1

3.2 合理使用索引

当查询的结果集占据了全部记录的30%的时候就考虑不要再使用索引了。因为使用索引的情况下还要对索引做一次排序处理。不太划算。还不如使用整个表查询。

3.3 基于索引的排序

MYSQL的弱点之一就是它的排序。注意：MYSQL在查询的时候最多只能使用一个索引。如果查询条件中用了一个那排序就用不到了。解决方案就是做一次联合索引。这样的话就可以解决

3.4 使用各种查询关键字进行组合实现性能的优化

3.4.1 STRAIGHT_JOIN:强制连接顺序

当有多表做一次连接查询的时候要注意连接顺序。谁先连接谁

1.     SELECT TABLE1.FIELD1, TABLE2.FIELD2 FROM TABLE1 STRAIGHT_JOIN TABLE2 WHERE …

由上面的SQL语句可知，通过STRAIGHT_JOIN强迫MySQL按TABLE1、TABLE2的顺序连接表。

3.4.2 干预索引使用，提高性能

限制使用索引的范围

当MySQL对索引进行选择时，这些索引都在考虑的范围内。但有时我们希望MySQL只考虑几个索引，而不是全 部的索引，这就需要用到USE INDEX对查询语句进行设置。

示例：SELECT * ＦＲＯＭ ＴＡＢＬＥ１ ＵＳＥ INDEX(FIELD1,FIELD2)…

通过使用关键字USE INDEX进行强制指定索引

从以上SQL语句可以看出，无论在TABLE1中已经建立了多少个索引，MySQL在选择索引时，只考虑在FIELD1和FIELD2上建立的索 引。

限制不使用索引的范围

如果我们要考虑的索引很多，而不被使用的索引又很少时，可以使用IGNORE INDEX进行反向选取。在上面的例子中是选择被考虑的索引，而使用IGNORE INDEX是选择不被考虑的索引。

示例：SELECT * ＦＲＯＭ ＴＡＢＬＥ１ IGNORE INDEX(FIELD1,FIELD2)…

在上面的SQL语句中，TABLE1表中只有FIELD1和FIELD2上的索引不被使用。

强迫使用某一个索引

希望MySQL必须要使用某一个索引(由于 MySQL在查询时只能使用一个索引，因此只能强迫MySQL使用一个索引)。这就需要使用FORCE INDEX来完成这个功能。

示例：SELECT * ＦＲＯＭ ＴＡＢＬＥ１ FORCE INDEX(FIELD1)…

以上的SQL语句只使用建立在FIELD1上的索引，而不使用其它字段上的索引。

3.4.3 使用临时表提供查询性能

当查询的结果集中的数据比较多，可以使用SQL_BUFFER_RESULT将结果集放到临时表。这样就可以很快释放掉表锁。而且能够长时间为客户端提供大记录集

示例：SELECT  SQL_BUFFER_RESULT * ＦＲＯＭ ＴＡＢＬＥ１ FORCE INDEX(FIELD1)…

和SQL_BUFFER_RESULT.选项类似的还有SQL_BIG_RESULT，这个选项一般用于分组或DISTINCT关键字，这个选项通 知MySQL，如果有必要，就将查询结果放到临时表中，甚至在临时表中进行排序

1.     SELECT SQL_BUFFER_RESULT FIELD1, COUNT(*) FROM TABLE1 GROUP BY FIELD1

表示能够在临时表里面对此字段进行一次GROUP BY操作.

在程序设计中同样存在一个“二八原则”，即20%的代码用去了80%的时间。数据库应用程序的开发亦然。数据库应用程序的优化，重点在于SQL的执行效率。而数据查询优化的重点，则是使得数据库服务器少从磁盘中读数据以及顺序读页而不是非顺序读页。（尽可能让其做顺序读数据操作）

 

结论：

1.     开启查询缓存

2.     合理使用索引

3.     使用临时表SQL_BUFFER_RESULT

 

 

四、  MySQL性能优化

4.1 合理使用数据类型

让表在磁盘上占据的空间尽可能地小。尽可能地安排好字段类型以最小化原则做！

1) 使用正确合适的类型，不要将数字存储为字符串。
2) 尽可能地使用最有效(最小)的数据类型。MySQL有很多节省磁盘空间和内存的专业化类型。
3) 尽可能使用较小的整数类型使表更小。例如，MEDIUMINT经常比INT好一些，因为MEDIUMINT列使用的空间要少25%。
4) 如果可能，声明列为NOT NULL。它使任何事情更快而且每列可以节省一位。注意如果在应用程序中确实需要NULL，应该毫无疑问使用它，只是避免 默认地在所有列上有它。
5) 对于MyISAM表，如果没有任何变长列(VARCHAR、TEXT或BLOB列)，使用固定尺寸的记录格式。这比较快但是不幸地可能会浪费一些空间。即 使你已经用CREATE选项让VARCHAR列ROW_FORMAT=fixed，也可以提示想使用固定长度的行。
6) 使用sample character set，例如latin1。尽量少使用utf-8，因为utf-8占用的空间是latin1的3倍。可以在不需要使用utf-8的字段上面使用 latin1，例如mail，url等。（保证不会有中文的时候就可以考虑使用latin1类型）

4.2 索引

1) MySQL只会使用前缀，例如key(a, b) …where b=5 将使用不到索引。
2) 要选择性的使用索引。在变化很少的列上使用索引并不是很好，例如性别列。
3) 在Unique列上定义Unique index。（速度要更快）
4) 避免建立使用不到的索引。（占空间）
5) 在Btree index中（InnoDB使用Btree），可以在需要排序的列上建立索引。
6) 避免重复的索引。
7) 避免在已有索引的前缀上建立索引。例如：如果存在index（a，b）则去掉index（a）。
8) 控制单个索引的长度。使用key（name（8））在数据的前面几个字符建立索引。（指定长度）
9) 越是短的键值越好，最好使用integer。
10) 在查询中要使用到索引（使用explain查看），可以减少读磁盘的次数，加速读取数据。
11) 相近的键值比随机好。Auto_increment就比uuid好。（主键自增长效果最好）
12) Optimize table可以压缩和排序index，注意不要频繁运行。
13) Analyze table可以更新数据。

 

4.3 服务器端优化

MySQL有很多发行版本，最好使用MySQL AB发布的二进制版本。如果安装 MySQL的服务器使用的是Intel公司的处理器，可以使用intel c++编译的版本，在Linux World2005的一篇PPT中提到，使用intel C++编译器编译的MySQL查询速度比正常版本快30%左右。Intel c++编译版本可以在MySQL官方网站下载。

编译的时候指定的参数设置

--character-set：如果是单一语言使用简单的character set例如latin1。尽量少用Utf-8，utf-8占用空间较多。（但是现在默认都是使用utf-8的编码方式）
--memlock：锁定MySQL只能运行在内存中，避免 swapping，但是如果内存不够时有可能出现错误。
--max_allowed_packet：要足够大，以适应比较大的SQL查询，对性能 没有太大影响，主要是避免出现packet错误。
--max_connections：server允许的最大连接。太大的话会出现out of memory。
--table_cache：MySQL在同一时间保持打开的table的数量。打开table开销比较大。一般设置为512。
--query_cache_size： 用于缓存查询的内存大小。
--datadir：mysql存放数据的根目录，和安装文件分开在不同的磁盘可以提高一点性能。

一般存放在/www分区

 

4.4 存储引擎的优化

MyISAM类型的数据表

4.4.1 特性

1) 不支持事务，宕机会破坏表
2) 使用较小的内存和磁盘空间
3) 基于表的锁，并发更新数据会出现严重性能问题(对并发支持性能较弱)
4) MySQL只缓存Index，数据由OS缓存(与key_buffer_size参数无关由OS来缓存)所以当一个库里面的MYISAM类型的表过多的时候就有可能导致OS缓崩溃掉。注意是这样的

Key_buffer_size只负责缓存索引数据。不负责缓存data数据。实体数据由OS缓存来处理的。

4.4.2 用途

1) 日志系统
2) 只读或者绝大部分是读操作的应用
3) 全表扫描
4) 批量导入数据
5) 没有事务的低并发读/写

 

4.4.3 优化要点

1) 声明列为NOT NULL，可以减少磁盘存储。
2) 使用optimize table做碎片整理，回收空闲空间。注意仅仅在非常大的数据变化后运行。
3) Deleting/updating/adding大量数据的时候禁止使用index。使用ALTER TABLE t DISABLE KEYS。(因为数据变化了会导致索引重建所以事先将索引disabled掉)
4) 设置myisam_max_[extra]_sort_file_size足够大，可以显著提高repair table的速度。

 

4.4.4 表锁

1) 避免并发insert，update。
2) 可以使用insert delayed，但是有可能丢失数据。
3) 优化查询语句。
4) 水平分区。
5) 垂直分区。
6) 如果都不起作用，使用InnoDB。（不会出现表锁的情况）

因为在做一个大的查询语句的时候有可能导致出现一个查询语句锁住整个表。导致没法进行正常的更新操作了。

 

InnoDB类型的数据表

InnoDB 是为在处理巨大数据量时获得最大性能而设计的。它的CPU使用效率非常高。许多需要高性能的大型数据库站点上使用了 InnoDB引擎

4.4.1 特性

1) 支持事务，ACID，外键。
2) Row level locks。
3) 支持不同的隔离级别。
4) 和MyISAM相比需要较多的内存和磁盘空间。
5) 没有键压缩。（MyISAM引擎使用opti优化语句能够实现压缩索引KEY）
6) 数据和索引都缓存在内存hash表中。（不会放入到OS缓存区域的）

4.4.2 用途

1) 需要事务的应用。
2) 高并发的应用。
3) 自动恢复。
4) 较快速的基于主键的操作。

4.4.3 优化要点

1) 尽量使用short，integer的主键。
2) Load/Insert数据时按主键顺序。如果数据没有按主键排序，先排序然后再进行数据库操作。
3) 在Load数据是为设置SET UNIQUE_CHECKS=0，SET FOREIGN_KEY_CHECKS=0，可以避免外键和唯一性约束检查的开销。
4) 使用prefix keys。因为InnoDB没有key压缩功能。

4.4.4 服务器端设定

innodb_buffer_pool_size：这是InnoDB最重要的设置，对InnoDB性能有决定性的影响。默认的设 置只有8M，所以默认的数据库设置下面InnoDB性能很差。在只有InnoDB存储引擎的数据库服务器上面，可以设置60-80%的内存。更精确一点， 在内存容量允许的情况下面设置比InnoDB tablespaces大10%的内存大小。（因为数据与索引全部缓存在这个参数的4G）

innodb_data_file_path： 指定表数据和索引存储的空间，可以是一个或者多个文件。最后一个数据文件必须是自动扩充的，也只有最后一个文件允许自动扩充。这样，当空间用完后，自动扩充数据文件就会自动增长（以8MB为单位）以容纳额外的数据。

例如： innodb_data_file_path=/disk1/ibdata1:900M;/disk2/ibdata2:50M:autoextend两 个数据文件放在不同的磁盘上。数据首先放在ibdata1中，当达到900M以后，数据就放在ibdata2中。一旦达到50MB，ibdata2将以 8MB为单位自动增长。如果磁盘满了，需要在另外的磁盘上面增加一个数据文件。（先放在第一块磁盘上面然后再跑到第二块磁盘上面然后开始自增长）

innodb_autoextend_increment: 默认是8M, 如果一次insert数据量比较多的话, 可以适当增加.

innodb_data_home_dir：放置表空间数据的目录，默认在mysql的数据目录，设置到和MySQL安装 文件不同的分区可以提高性能。

innodb_log_file_size：该参数决定了recovery speed。太大的话recovery就会比较慢，太小了影响查询性能，一般取256M可以兼顾性能和recovery的速度

innodb_log_buffer_size： 磁盘速度是很慢的，直接将log写道磁盘会影响InnoDB的性能，该参数设定了log buffer的大小，一般4M。如果有大的blob操作，可以适当增大。

 

innodb_flush_logs_at_trx_commit=2： 该参数设定了事务提交时内存中log信息的处理。

1) =1时，在每个事务提交时，日志缓冲被写到日志文件，对日志文件做到磁盘操作的刷新。Truly ACID。速度慢。
2) =2时，在每个事务提交时，日志缓冲被写到文件，但不对日志文件做到磁盘操作的刷新。只有操作系统崩溃或掉电才会删除最后一秒的事务，不然不会丢失事务。
3) =0时， 日志缓冲每秒一次地被写到日志文件，并且对日志文件做到磁盘操作的刷新。任何mysqld进程的崩溃会删除崩溃前最后一秒的事务

innodb_file_per_table： 可以存储每个InnoDB表和它的索引在它自己的文件中。

transaction-isolation=READ-COMITTED: 如果应用程序可以运行在READ-COMMITED隔离级别，做此设定会有一定的性能提升。

innodb_flush_method： 设置InnoDB同步IO的方式：

1)     Default – 使用fsync（）。
2) O_SYNC 以sync模式打开文件，通常比较慢。
3) O_DIRECT，在Linux上使用Direct IO。可以显著提高速度，特别是在RAID系统上。避免额外的数据复制和double buffering（mysql buffering 和OS buffering）。