从某次测试过程中，得到的MySQL性能优化的建议，和定位问题的方法

原创: 2016-06-07 17:37:25 627浏览

MySQL版本5.5某次测试优化过程中碰到的问题和解决问题的方法，方案。以供各位参考。一，表结构的优化问题1，表与字段使用不同的字符集会导致索引失效我在自己的

MySQL版本5.5

某次测试优化过程中碰到的问题和解决问题的方法，方案。以供各位参考。
一，表结构的优化
问题1，表与字段使用不同的字符集会导致索引失效
我在自己的机器上，没试出来。可能与记录数太少有关。有时，记录数太少，得出的结果有
些不一致。但此问题，是存在的。
问题2，表字段太长，会导致order by时索引失效
mysql> show create table t2\G
*************************** 1. row ***************************
Table: t2
Create Table: CREATE TABLE `t2` (
`id` int(11) NOT NULL,
`name` varchar(2000) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `idx_name` (`name`(767))
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
mysql> explain select * from t2 order by name\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t2
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 1
Extra: Using filesort
1 row in set (0.00 sec)
修改后：
mysql> show create table t2\G
*************************** 1. row ***************************
Table: t2
Create Table: CREATE TABLE `t2` (
`id` int(11) NOT NULL,
`name` varchar(100) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `idx_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
mysql> explain select * from t2 order by name\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t2
type: index
possible_keys: NULL
key: idx_name
key_len: 103
ref: NULL
rows: 1
Extra: Using index
1 row in set (0.00 sec)
问题3，表的一行记录的字符数太长，排序异常慢
当表的一行的记录数超过1K, max_length_for_sort_data默认值时，使用order by会导致排序
异常的慢。当存在这种数据表时，请设置max_length_for_sort_data=2K或更高。
对于这种情况，可以使用explain去查找错误原因。当出现此情况时，会发现explain的显示
中有如下内容
Extra: Using filesort
问题4，，错误使用MyISAM引擎，查询速度异常慢
由于，测试项目只使用Innodb引擎，在配置文件中关闭了关于MyISAM引擎的相关配置。
导入数据时，由于错误使用了Innodb引擎，导致查询很慢。
因为刚开始，我并不知道原因是使用了MyISAM引擎，我通过如下的命令，快速找到了查
询慢的语句。再通过show create table t_corpcustomize 即找到了错误的表。
关于查询慢，可以通过show full processlist，查看，你可以看到类似如下的查询语句
| Id | User | Host | db | Command | Time | State | Info
| 67 | root | linux70:55737 | bi | Execute | 0 | Sending data | select whoset, specialphone,
settype, begintime,endtime from t_corpcustomize where status = 1
二，服务器参数优化配置
一，去除所有不用的功能
1. 因为没用query cache, 去除配置如下
query_cache_type=0
query_cache_size = 0
2. 因为没用MyISAM, 去除配置如下
key_buffer_size=8M
其它所有的都注释掉
二，停止所有与MySQL相关的服务
测试过程中，发现MySQL机器上，还有oracle, java, tomcat进程在运行。
虽然，他们没有业务，但使用了不少内存。去除后，性能得到了一些提升。
三，参数合理调整
一，调整与内存有关的配置
innodb_file_per_table = true
# Data files must be able to hold your data and indexes
# Set buffer pool size to 50-80% of your computer's memory,
# but make sure on Linux x86 total memory usage is innodb_buffer_pool_size=5G
#When innodb_buffer_pool_size > 1G, set this parameter to divide the buffer pool size maybe just
above 1G
innodb_buffer_pool_instances = 4
innodb_additional_mem_pool_size=20M
# Set the log file size to about 25% of the buffer pool size
innodb_log_file_size = 2047M
innodb_log_files_in_group = 2
innodb_log_buffer_size=8M
max_connection
*
(
#顺序读时使用，一般不用太大
read_buffer_size = 4M
#随机读时使用，当有大量order by, group by操作时加大
read_rnd_buffer_size = 32M
#排序时使用，当有大量order by, group by操作时加大
sort_buffer_size = 64M
#一般不用太大，有复杂的join操作时加大
join_buffer_size = 8M
#一般不用太大，默认即可
thread_stack = 192K
#使用binlog时，配置，一般默认即可
binlog_cache_size
)
二，调整与I/O，文件,log缓存等有关的配置
1. table_open_cache 调整
一般来说，可以在phpmyadmin中查看Open_tables与Opened_tables的值，也可以执行
1 mysql> show global status like 'open%_tables';
查看当前的open_tables情况
来查看这两个参数的值。其中Open_tables是当前正在打开表的数量，Opened_tables是所有
已经打开表的数量。
如果Open_tables的值已经接近table_cache的值，且Opened_tables还在不断变大，则说明
mysql正在将缓存的表释放以容纳新的表，此时可能需要加大table_cache的值。对于大多数
情况，
比较适合的值：
Open_tables / Opened_tables >= 0.85
Open_tables / table_cache 2. I/O 调整
#如下两个参数，一般设置为cpu个数即可
innodb_write_io_threads = 4
innodb_read_io_threads = 4
#下面设置为cpu个数*2
innodb_thread_concurrency = 8
#io capacity，这个参数需要根据IOPS来进行设置，需要查看硬件手册。
3. log参数调整
#log flush mode，见参考文档，通常在linux设为O_DIRECT,

html#sysvar_innodb_flush_log_at_trx_commit
innodb_flush_method=O_DIRECT
#1=保证事务的一致性，每次commit刷新到磁盘，0=缓存起来，每秒刷新磁盘，2=写入文件，
再每秒刷新到磁盘。推荐为2，配置为2时，性能会有不少提升，而只会在掉电时，出现事
务丢失的情况。
innodb_flush_log_at_trx_commit=1
三，调整隔离级别
因为oracle默认隔离级别为READ-COMMITTED, 而mySQL默认为REPEAT-READ。不同的
隔离级别对于性能是有较大的出入的。因为其对于并发的控制的严格度不同，换句话说低的
隔离级别，需要应用程序多做很多工作保证一致性，而高的就由数据库本身来保证。READCOMMITTED
其实也没太多影响，只会带来幻影数据问题。MySQL关于READCOMMITTED
的安全配置如下。
transaction_isolation = READ-COMMITTED
innodb_autoinc_lock_mode=2
innodb_locks_unsafe_for_binlog = 1
innodb_support_xa = 0
四，把Innodb data, tablespace, log files存储于不同磁盘
经过测试，我发现系统的瓶颈是I/O。如下，通过iostat的操作，可以看到高峰时每秒io r/s
读65, w/s写437.67个。
系统性能在目前的位置无法提升，原因是MySQL总在等待I/O的操作。
evice: rrqm/s wrqm/s r/s w/s rMB/s wMB/s avgrq-sz avgqu-sz await svctm %util
sda 0.00 7.00 4.33 4.33 0.16 0.04 48.92 0.16 18.00 4.00 3.47
sdb 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
sdc 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
sdd 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
sde 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
sdf 0.67 158.67 65.00 437.67 0.68 9.80 42.73 2.11 4.21 0.55 27.73
sdg 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
VxVM56000 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
dm-0 0.00 0.00 65.67 589.00 0.68 9.80 32.81 3.56 5.44 0.43 27.87

所以，建议把data file, innodb space file, innodb log file分成三个不同的盘，看性能是否有提
升。
三，常用定位问题方法
一，mysql>explain
详见如下文档：
mysql> EXPLAIN SELECT * FROM students WHERE or sex=1\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: students
type: ALL
possible_keys: index_name
key: NULL
key_len: NULL
ref: NULL
rows: 4
Extra: Using where
1 row in set (0.00 sec)
二，mysql>show full processlist
见第一章，问题4
三，slow log
1. 配置如下
slow_query_log=1
slow_query_log_file=/opt/upora/slow.log
#超过long_query_time的SQL写入日志，时间为秒
long_query_time = 1
#未使用索引的SQL写入日志
log-queries-not-using-indexes
2. slow log内容如下
例如：
use cust;
SET timestamp=1356921142;
select * from cust_contact ct where ct.custid in
(select c.custid from customer c, cust_group g,(select groupid from groups_defin
e where) gr where c.custid=g.custid and g.groupid =gr.groupid);
3. 用mysqldumpslow工具统计
例如：
#mysqldumpslow slow.log
Count: 1 Time=1.06s (1s) Lock=0.02s (0s) Rows=7.0 (7), root[root]@localhost
select count(*), engine,data_length from information_schema.tables group by engine
四，mysql> show global status
这个命令得到的数据会比较多，不太直观，需要计算后才能得出结论。
下面列出了常用的一些计算选项。
(1)QPS(每秒Query量)
QPS = Questions(or Queries) / seconds
mysql > show global status like 'Question%';
(2)TPS(每秒事务量)
TPS = (Com_commit + Com_rollback) / seconds
mysql > show global status like 'Com_commit';
mysql > show global status like 'Com_rollback';
(3)key Buffer 命中率
mysql>show global status like 'key%';
key_buffer_read_hits = (1-key_reads / key_read_requests) * 100%
key_buffer_write_hits = (1-key_writes / key_write_requests) * 100%
(4)InnoDB Buffer命中率
mysql> show status like 'innodb_buffer_pool_read%';
innodb_buffer_read_hits = (1 - innodb_buffer_pool_reads / innodb_buffer_pool_read_requests) *
100%
(5)Query Cache命中率
mysql> show status like 'Qcache%';
Query_cache_hits = (Qcahce_hits / (Qcache_hits + Qcache_inserts )) * 100%;
(6)Table Cache状态量
mysql> show global status like 'open%';
比较 open_tables 与 opend_tables 值
(7)Thread Cache 命中率
mysql> show global status like 'Thread%';
mysql> show global status like 'Connections';
Thread_cache_hits = (1 - Threads_created / connections ) * 100%
(8)锁定状态
mysql> show global status like '%lock%';
Table_locks_waited/Table_locks_immediate=0.3% 如果这个比值比较大的话，说明表锁造成的
阻塞比较严重
Innodb_row_lock_waits innodb行锁，太大可能是间隙锁造成的
五，mysql>show engine innodb status
这个命令可以统计innodb的信息，还可以定位如死锁等问题。
具体见：
六，iostat, top, vmstat
此可以用来，查看cpu, 内存的使用情况。这也可算是所有数据库查找性能瓶颈的方法。在此
不再多述