Mysql 优化 SQL

查看SQL执行频率
Jave代码

show status like "Com_%";  

记录慢语句
Jave代码

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

查看没有使用索引
Jave代码

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)  

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)

查看一个表的索引设置
Jave代码

show index from finance_records;  

查看索引使用情况
Jave代码

show status like "handle_read%";  

优化表
Jave代码

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)