MySQL的优化

通过show status了解SQL执行频率

mysql> show status like 'com_%';
+-----------------------------+-------+
| Variable_name               | Value |
+-----------------------------+-------+
| Com_admin_commands          | 0     |
| Com_assign_to_keycache      | 0     |
| Com_alter_db                | 0     |
| Com_alter_db_upgrade        | 0     |
| Com_alter_event             | 0     |
| Com_alter_function          | 0     |
| Com_alter_instance          | 0     |
| Com_alter_procedure         | 0     |
| Com_alter_server            | 0     |
| Com_alter_table             | 0     |
| Com_alter_tablespace        | 0     |
| Com_alter_user              | 0     |
| Com_analyze                 | 0     |
| Com_begin                   | 0     |
| Com_binlog                  | 0     |
| Com_call_procedure          | 0     |
| Com_change_db               | 1     |
| Com_change_master           | 0     |
| Com_change_repl_filter      | 0     |
| Com_check                   | 0     |
| Com_checksum                | 0     |
| Com_commit                  | 0     |
...

Com_xxx表示每个xxx语句执行的次数

参数说明

• Com_select:执行select操作的次数
• Com_insert:执行insert操作的次数

定位执行效率较低的SQL

#在my.cnf中配置
log-slow-queries=/usr/local/mysql/log_slow_queries.log
long_query_time=0.01

• 通过show processlist命令查看的当期那MySQL在进行的线程

  • 线程的状态
  • 是否锁表

通过explain 分析低效SQL的执行计划

本优化案例下载地址http://downloads.mysql.com/docs/sakila-db.zip MySQL官方提供的电影出租厅信息管理系统

mysql> explain select sum(amount) from customer a, payment b where 1=1 and a.customer_id = b.customer_id and email='JANE.BENNETT@sakilacustomer.org'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: a
   partitions: NULL
         type: ALL
possible_keys: PRIMARY
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 599
     filtered: 10.00
        Extra: Using where
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: b
   partitions: NULL
         type: ref
possible_keys: idx_fk_customer_id
          key: idx_fk_customer_id
      key_len: 2
          ref: sakila.a.customer_id
         rows: 26
     filtered: 100.00
        Extra: NULL
2 rows in set, 1 warning (0.00 sec)

参数简单说明

 select_type:select类型
      SIMPLE:简单表，不使用表连接或者子查询
      PRIMARY:主查询，即外层查询
      UNION：第二或者后面的查询语句
      SUBQUERY:子查询中第一个SELECT

table:输出结果集的表
type:表示MySQL在表中找到所需行的方式
      type=ALL:全表扫描（性能最差）
      type=index:索引全扫描，MySQL遍历整个索引来查询匹配的行
      type=range：索引范围扫描，常见于<、<=、>、>=、between等操作符
      type=ref:使用非唯一索引扫描或唯一索引的前缀扫描，返回匹配某个单独值的记录行
     type=eq_ref
     type=const/system
     type=NULL(性能最好)

案例