Mysql 优化 SQL

查看SQL执行频率

show status like "Com_%";

记录慢语句

vi /etc/mysql/my.cnf
log_slow_queries  = /var/log/mysql/mysql-slow.log

查看没有使用索引

explain select * from finance_records where ref=""\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: finance_records
         type: ref
possible_keys: finance_records_ref
          key: finance_records_ref
      key_len: 48
          ref: const
         rows: 1
        Extra: Using where
1 row in set (0.00 sec)

查看一个表的索引设置

show index from finance_records;

查看索引使用情况

show status like "handle_read%";

优化表

mysql> analyze table sales;
+------------+---------+----------+----------
| Table      | Op      | Msg_type | Msg_text
+------------+---------+----------+----------
| test.sales | analyze | status   | OK
+------------+---------+----------+----------
1 row in set (0.02 sec)

mysql> check table sales;
+------------+-------+----------+----------+
| Table      | Op    | Msg_type | Msg_text |
+------------+-------+----------+----------+
| test.sales | check | status   | OK       |
+------------+-------+----------+----------+
1 row in set (0.03 sec)

mysql> optimize table sales;
+------------+----------+----------+-----------------------------+
| Table      | Op       | Msg_type | Msg_text                    |
+------------+----------+----------+-----------------------------+
| test.sales | optimize | status   | Table is already up to date |
+------------+----------+----------+-----------------------------+
1 row in set (0.00 sec)

评论

1 楼 huamengxing 2009-01-16  

          :     )