mysql的sql优化(前奏)

要优化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是没有开启慢查询日志的。需要将其设置为开启，并设置慢查询的配置。

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的分析结果进行相应的优化。