在本节介绍中，mysql的优化从两部分说明：基本的配置优化和索引的基本优化。
首先介绍两个优化工具mysqltuner.pl和Percona Toolkit 。
MysqlTuner
mysqltuner是一个 Perl 脚本，可以用来分析您的 MySQL 性能，并且基于收集到的信息给出相应的优化建议。这样子，您就可以调整 my.cnf 从而优化您的 MySQL 设置。
下载mysqltuner后，直接可以运行：
perl mysqltuner.pl
结果可能如下所示
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 |
>> MySQLTuner 1.3.0 - Major Hayden <<span style="text-decoration: underline;">major@mhtx.net</span>> >> Bug reports, feature requests, and downloads at <span style="text-decoration: underline;">http://mysqltuner.com/</span> >> Run with '--help' for additional options and output filtering Please enter your MySQL administrative login: root Please enter your MySQL administrative password: [OK] Currently running supported MySQL version 5.1.73-log [OK] Operating on 64-bit architecture -------- Storage Engine Statistics ------------------------------------------- [--] Status: +CSV +InnoDB +MRG_MYISAM [--] Data in MyISAM tables: 3M (Tables: 26) [--] Data in InnoDB tables: 60M (Tables: 21) [!!] Total fragmented tables: 31 -------- Security Recommendations ------------------------------------------- [!!] User 'root@127.0.0.1' has no password set. [!!] User 'root@li386-247' has no password set. -------- Performance Metrics ------------------------------------------------- [--] Up for: 15d 16h 43m 24s (94K q [0.070 qps], 3K conn, TX: 304M, RX: 14M) [--] Reads / Writes: 93% / 7% [--] Total buffers: 34.0M global + 2.7M per thread (151 max threads) [OK] Maximum possible memory usage: 449.2M (45% of installed RAM) [OK] Slow queries: 0% (7/94K) [OK] Highest usage of available connections: 1% (2/151) [OK] Key buffer size / total MyISAM indexes: 8.0M/413.0K [OK] Key buffer hit rate: 100.0% (748 cached / 0 reads) [!!] Query cache is disabled [OK] Sorts requiring temporary tables: 0% (0 temp sorts / 8K sorts) [!!] Temporary tables created on disk: 38% (3K on disk / 8K total) [!!] Thread cache is disabled [OK] Table cache hit rate: 27% (64 open / 232 opened) [OK] Open file limit used: 8% (87/1K) [OK] Table locks acquired immediately: 100% (100 immediate / 100 locks) [!!] InnoDB buffer pool / data size: 8.0M/61.0M [OK] InnoDB log waits: 0 -------- Recommendations ----------------------------------------------------- General recommendations: Run OPTIMIZE TABLE to defragment tables for better performance When making adjustments, make tmp_table_size/max_heap_table_size equal Reduce your SELECT DISTINCT queries without LIMIT clauses Set thread_cache_size to 4 as a starting value Variables to adjust: query_cache_size (>= 8M) tmp_table_size (> 16M) max_heap_table_size (> 16M) thread_cache_size (start at 4) innodb_buffer_pool_size (>= 60M) |
上面列出了一系列的优化建议。 针对每一条的建议，确认方案后，再着手进行优化。
举例说明一下如何实施建议优化。上面提到的建议中有一条：[!!] Query cache is disabled。查询缓存没有开启。这是一条很重要的优化措施，开启查询缓存，mysql会缓存查询后的sql结果。
首先，确认下相关配置
|
1 2 3 4 5 6 7 8 9 10 11 |
mysql> show variables like '%query_cache%'; +------------------------------+---------+ | Variable_name | Value | +------------------------------+---------+ | have_query_cache | YES | | query_cache_limit | 1048576 | | query_cache_min_res_unit | 4096 | | query_cache_size | 0 | | query_cache_type | ON | | query_cache_wlock_invalidate | OFF | +------------------------------+————+ |
从上面可以看到，我们开启了缓存优化，但没有设置query_cache_size。 我们只需要把此值设置成合适的。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
mysql> set global query_cache_size=1024*1024; Query OK, 0 rows affected (0.00 sec) mysql> show variables like '%query_cache%'; +------------------------------+---------+ | Variable_name | Value | +------------------------------+---------+ | have_query_cache | YES | | query_cache_limit | 1048576 | | query_cache_min_res_unit | 4096 | | query_cache_size | 1048576 | | query_cache_type | ON | | query_cache_wlock_invalidate | OFF | +------------------------------+---------+ 6 rows in set (0.00 sec) |
其余的优化措施只要重复以上步骤即可。
注意：如果修改my.cnf配置文件，要重启mysql才能生效。优化完成之后，需要mysql运行一段时间后，才可以执行mysqltuner.pl。这样得出的优化建议才更准确。
Percona Toolkit
Percona Toolkit 是一组高级命令行工具的集合，用来执行各种通过手工执行非常复
杂和麻烦的 mysql 任务和系统任务。这些工具主要包括开发、性能、配置、监控、复制、系统、实用六大类。
pt-variable-advisor
pt-variable-advisor：分析 mysql 的参数变量，并对可能存在的问题提出建议。
执行
|
1 |
./pt-variable-advisor --user=root --password=123456 localhost |
结果可能如下：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
# WARN delay_key_write: MyISAM index blocks are never flushed until necessary. # WARN innodb_log_file_size: The InnoDB log file size is set to its default value, which is not usable on production systems. # NOTE log_warnings-2: Log_warnings must be set greater than 1 to log unusual events such as aborted connections. # NOTE max_connect_errors: max_connect_errors should probably be set as large as your platform allows. # WARN slave_net_timeout: This variable is set too high. # NOTE innodb_data_file_path: Auto-extending InnoDB files can consume a lot of disk space that is very difficult to reclaim later. # NOTE innodb_flush_method: Most production database servers that use InnoDB should set innodb_flush_method to O_DIRECT to avoid double-buffering, unless the I/O system is very low performance. # WARN log_bin: Binary logging is disabled, so point-in-time recovery and replication are not possible. # WARN myisam_recover_options: myisam_recover_options should be set to some value such as BACKUP,FORCE to ensure that table corruption is noticed. |
pt-duplicate-key-checker
pt-duplicate-key-checker:这个工具检查mysql表中的重复的索引和外键，并把重复的索引和外键都列出来，并且生成了删除重复索引和外键的sql语句。
执行
|
1 |
./pt-duplicate-key-checker --host localhost --password 123456 --user root |
结果可能是：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
# ######################################################################## # book.album # ######################################################################## # country_id is a left-prefix of m2 # Key definitions: # KEY `country_id` (`country_id`), # KEY `m2` (`country_id`,`album_type_id`) # Column types: # `country_id` smallint(5) unsigned default null # `album_type_id` int(10) unsigned not null # To remove this duplicate index, execute: ALTER TABLE `book`.`album` DROP INDEX `country_id`; |
可以看到country_id是重复索引，用show create table 语句确认下
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
*************************** 1. row *************************** Table: album Create Table: CREATE TABLE `album` ( `album_id` int(10) unsigned NOT NULL, `artist_id` int(10) unsigned NOT NULL, `album_type_id` int(10) unsigned NOT NULL, `name` varchar(255) NOT NULL, `first_released` year(4) NOT NULL, `country_id` smallint(5) unsigned DEFAULT NULL, PRIMARY KEY (`album_id`), KEY `name` (`name`), KEY `artist_id` (`artist_id`), KEY `album_type_id` (`album_type_id`), KEY `country_id` (`country_id`), KEY `m2` (`country_id`,`album_type_id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
m2是多级索引，m2索引适用
country_id
country_id album_type_id
以上两种形式的索引。因此索引country_id就没有存在的必要了，直接删除即可。