Mysql 优化 SQL

Robin2Wu

于 2009-01-15 12:51:54 发布

阅读量104

点赞数

分类专栏：深入浅出 - Mysql 数据库开发、优化与管理维护文章标签： MySQL SQL IDEA UP

深入浅出 - Mysql 数据库开发、优化与管理维护专栏收录该内容

6 篇文章 0 订阅

订阅专栏

查看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)