要优化mysql首先要知道什么地方需要优化，然后才能针对具体问题进行优化？难道不是吗？什么分库分表，建立索引....摆脱不要那么官方好吗？
1.学会和培养使用mysql的查看命令的使用习惯
什么你忘记如何创建表的语句了？？
mysql> ? table Many help items for your request exist. To make a more specific request, please type 'help <item>', where <item> is one of the following topics: ALTER TABLE ANALYZE TABLE CHECK TABLE CHECKSUM TABLE CREATE TABLE DROP TABLE OPTIMIZE TABLE RENAME TABLE REPAIR TABLE SHOW SHOW CREATE TABLE SHOW TABLE STATUS SPATIAL TRUNCATE TABLE
mysql> ? SHOW CREATE TABLE
Name: 'SHOW CREATE TABLE'
Description:
Syntax:
SHOW CREATE TABLE tbl_name
Shows the CREATE TABLE statement that creates the named table. To use
this statement, you must have some privilege for the table. This
statement also works with views.
SHOW CREATE TABLE quotes table and column names according to the value
of the sql_quote_show_create option. See
http://dev.mysql.com/doc/refman/5.5/en/server-system-variables.html.
URL: http://dev.mysql.com/doc/refman/5.5/en/show-create-table.html
Examples:
mysql> SHOW CREATE TABLE t\G
*************************** 1. row ***************************
Table: t
Create Table: CREATE TABLE t (
id INT(11) default NULL auto_increment,
s char(60) default NULL,
PRIMARY KEY (id)
) ENGINE=MyISAM
这回你知道了吧！！！
另外 ？ contents会告诉你你可以查看那些命令
mysql> ? contents; You asked for help about help category: "Contents" For more information, type 'help <item>', where <item> is one of the following categories: Account Management Administration Compound Statements Data Definition Data Manipulation Data Types Functions Functions and Modifiers for Use with GROUP BY Geographic Features Help Metadata Language Structure Plugins Procedures Storage Engines Table Maintenance Transactions User-Defined Functions Utility
上面这个查看帮助文档的使用，可以帮助你在查找需要优化的sql语句。
2.mysql的慢查询日志
mysql> show global variables like 'log_%'; +---------------------------------+--------------------------+ | Variable_name | Value | +---------------------------------+--------------------------+ | log_bin | OFF | | log_bin_trust_function_creators | OFF | | log_error | /var/log/mysql/error.log | | log_output | FILE | | log_queries_not_using_indexes | OFF | | log_slave_updates | OFF | | log_slow_queries | OFF | | log_warnings | 1 | +---------------------------------+--------------------------+ 8 rows in set (0.01 sec) mysql> show session variables like 'log_%'; +---------------------------------+--------------------------+ | Variable_name | Value | +---------------------------------+--------------------------+ | log_bin | OFF | | log_bin_trust_function_creators | OFF | | log_error | /var/log/mysql/error.log | | log_output | FILE | | log_queries_not_using_indexes | OFF | | log_slave_updates | OFF | | log_slow_queries | OFF | | log_warnings | 1 | +---------------------------------+--------------------------+ 8 rows in set (0.00 sec)
第一个是mysql的全局配置，第二个是mysql的本次链接的配置。这几个 日志介绍。默认mysql是没有开启慢查询日志的。需要将其设置为开启，并设置慢查询的配置。
3.通过mysql的慢查询日志很容易就能够知道具体那个sql语句出现了问题。定位到问题，接着就是分析这个sql语句的执行情况。使用的表是否有索引，索引是否使用了，是否走全表扫描了，是否使用了临时表.....
mysql> explain select * from test\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: test
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 2
Extra:
1 row in set (0.00 sec)
使用explain或desc查看sql的执行信息，\G是使这些信息以垂直的方式显示。 其中列名及其值代表的意义。
4.针对3的分析结果进行相应的优化。