MySQL語句最佳化

標籤：

一、最佳化SQL的一般步驟

　　a)通過SHOW STATUS;命令瞭解各種SQL的執行頻率
　　　　SHOW [SESSION|GLOBAL] STATUS;
　　　　SESSION (預設)表示當前串連
　　　　GLOBAL 表示自資料庫啟動至今
　　　　SHOW GLOBAL STATUS LIKE ‘com_%‘

　　b)查看本次登入以來增刪改查的次數
　　　　SHOW STATUS LIKE ‘com_select%‘
　　　　SHOW STATUS LIKE ‘com_update%‘
　　　　SHOW STATUS LIKE ‘com_insert%‘
　　　　SHOW STATUS LIKE ‘com_delete%‘

　　c)針對innodb的增刪改查
　　　　SHOW STATUS LIKE ‘innodb_rows%‘

　　d)伺服器已經工作的秒數
　　　　SHOW STATUS LIKE ‘uptime%‘

　　e)慢查詢

　　　　查看是否開啟了慢查詢
　　　　　　SHOW VARIABLES LIKE ‘%slow%‘ (slow_query_log表示開啟或關閉)
　　　　查看慢查詢的時間定義
　　　　　　SHOW VARIABLES LIKE ‘%long%‘
　　　　查看慢查詢的次數
　　　　　　SHOW STATUS LIKE ‘slow_queries‘
　　　　慢查詢日誌中會記錄慢查詢的SQL
　　　　分析運行慢的SQL
　　　　　　EXPLAIN SQL
　　　　　　DESC SQL
　　　　　　+----+-------------+-------+------+---------------+------+---------+------+------+-------+
　　　　　　| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
　　　　　　+----+-------------+-------+------+---------------+------+---------+------+------+-------+
　　　　　　| 1 | SIMPLE | book | ALL | NULL | NULL | NULL | NULL | 3 | |
　　　　　　+----+-------------+-------+------+---------------+------+---------+------+------+-------+
　　　　　　select_type:查詢類型
　　　　　　　　ALL:本次是簡單的單表查詢
　　　　　　　　PRIMARY:本次查詢用到了主鍵
　　　　　　　　UNION:多表查詢
　　　　　　type:
　　　　　　　　ALL:通過全表掃描得到的資料
　　　　　　　　possible_keys:可能用到的索引
　　　　　　　　key:本次用到的索引
　　　　　　　　rows:掃描的行數,這裡越小查詢效率越高
　　　　　　　　Extra:執行情況的說明和描述

二、索引問題

　　a)MyISAM儲存引擎的表資料和索引是分開的,各自是獨一的一個檔案。InnoDB儲存引擎的資料和索引儲存在同一個資料表空間裡,但可以有多個檔案組成。

　　可以對列的某一部分進行索引:

　　　　CREATE INDEX index_name ON table_name(column(4))

　　b)哪些欄位適合加索引

　　　　where條件、group by條件 having條件 order by條件

　　c)索引會被使用的情況

　　　　對於多列索引,查詢條件中用到了最左邊的列,索引一般會被使用

　　　　CREATE INDEX index_name ON table_name(company_id,moneys);

　　　　WHERE company_id=123 會使用索引

　　　　WHERE moneys=22 不會使用索引

　　如果OR前的條件中的列有索引,後面的列沒有索引,那麼涉及的索引都不會被用到。只有or前後的欄位都加了索引,查詢才可能用到索引

　　如果列類型是字串,但是把數字當作條件,索引不會被用到

　　handler_read_rnd_next的值越高,則意味著查詢效率越低,並應該建立索引補救。SHOW STATUS LIKE ‘handler_read%‘

三、表最佳化

　　CHECK TABLE table_name; 分析表是否有錯誤,例如表被刪除後,依賴他的視圖將無效

　　OPTIMIZE TABLE table_name; 如果已經刪除了表的一大部分,或者如果已經對含有可變長度行的表進行了多次改動,則需要做定期最佳化。這個命令可以將表中的空間片段進行合并,對MyISAM InnoDB有效

四、常用SQL的最佳化　

　　當資料量比較大的時候,匯入匯出表會比較耗費系統資源,應該使用 infile和outfile
　　? load data 查看協助
　　LOAD DATA INFILE ‘data.txt‘ INTO TABLE db2.my_table 快速匯入
　　樣本:load data infile ‘e:/sql.txt‘ into table book(bookname,publisher,author,price,ptime,pic,detail);

　　如果有索引的話,可以先關閉索引,在匯入資料,然後開啟索引 這樣匯入會更快DISABLE KEYS/ENABLE KEYS 關閉開啟表的非唯一索引,對InnoDB無效
　　ALTER TABLE table_name DISABLE KEYS
　　匯入資料
　　ALTER TABLE table_name ENABLE KEYS

　　關閉唯一索引以提高匯入效率(前提是要確保資料不重複)
　　SET UNIQUE_CHECKS=0 關閉唯一校正
　　SET UNIQUE_CHECKS=1 開啟唯一校正

　　針對innodb表可以關閉自動認可以提高匯入效率(因為每匯入一行都要校正是否提交)
　　SET AUTOCOMMIT=0 關閉自動認可
　　SET AUTOCOMMIT=0 開啟自動認可

　　? outfile

　　SELECT * FROM table_name INTO OUTFILE ‘file_name‘ 快速匯出

　　group by分組後預設根據分組列升序排列,如果不想排序可以使用 order by null禁止排序

　　盡量避免使用子查詢,用串連查詢來代替,因為子查詢不會用到索引

 

MySQL語句最佳化