mysql效能最佳化

標籤：

sql及索引最佳化開啟慢查詢日誌查看慢查詢日誌是否開啟

mysql> show variables like ‘slow_query_log‘;

+----------------+-------+
| Variable_name | Value |
+----------------+-------+
| slow_query_log | OFF |
+----------------+-------+
1 row in set (0.00 sec)

mysql> set global slow_query_log=on;
Query OK, 0 rows affected (0.05 sec)

慢查詢記錄檔儲存位置

mysql> show variables like ‘%slow_query_log_file%‘;
+---------------------+----------------------------------------------+
| Variable_name | Value |
+---------------------+----------------------------------------------+
| slow_query_log_file | C:\xampp\mysql\data\80CEAE742547827-slow.log |
+---------------------+----------------------------------------------+
2 rows in set (0.00 sec)

是否記錄沒有使用索引的sql

mysql> show variables like ‘%log_queries_not_using%‘;
+-------------------------------+-------+
| Variable_name | Value |
+-------------------------------+-------+
| log_queries_not_using_indexes | OFF |

+-------------------------------+-------+
1 row in set (0.00 sec)

mysql> set global log_queries_not_using_indexes=on;
Query OK, 0 rows affected (0.00 sec)

執行時間大於N秒的SQL會被記錄

mysql> show variables like ‘%long_query%‘;
+-----------------+-----------+
| Variable_name | Value |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
1 row in set (0.00 sec)

mysql> set global long_query_time=1;
Query OK, 0 rows affected (0.00 sec)

group by語句最佳化

最佳化前

select actor.first_name, actor.last_name, count(*)from sakila.film_actorINNER JOIN sakila.actor USING(actor_id)GROUP BY film_actor.actor_id

 

使用join子查詢的方式最佳化後，actor表沒有在使用檔案排序和暫存資料表

最佳化後

select actor.first_name, actor.last_name, c.cntfrom sakila.actorINNER JOIN (    select actor_id, count(*) as cnt from sakila.film_actor GROUP BY actor_id) as c using(actor_id)

雖然最佳化後仍然掃描了200行的記錄，但是actor表沒有使用檔案排序和暫存資料表

資料結構最佳化系統配置最佳化伺服器硬體最佳化

mysql效能最佳化