【MySql】效能最佳化之分析命令

標籤：

【MySql】效能最佳化之分析命令

一

當發現程式運行比較慢的時候，首先排除物力資源問題之後，就將注意力轉向mysq資料庫：

1、首先確定運行慢的sql語句：

mysql> show full processlist;

2、確認低效的查詢： 多次執行第一步發現time耗費大的sql語句。查看耗費的時間。

 

3、為sql產生一個執行計畫query Execution plan（QEP）

mysql> explain select * from tbal_name where ...;

 

4、查看建立表的語句：

show create table table_name \G;

 

5、查看錶的狀態：

show table status like ‘table_name‘ \G;

 

---------------------------------------------------

二

總結一些分析的命令：

1、explain：解釋sql的執行計畫，後邊的sql不執行

2、explain partitions ：用於查看存在分區的表的執行計畫

3、explain extended：--書上說存在filtered列，但是檢查後沒有發現這一列，待驗證

4、show warnings:

5、show create table:查看錶的詳細的建立語句，便於使用者對錶進行最佳化

6、show indexes :產看錶的所有索引，show indexes from table_name，同樣也可以從information_schema.statistics表中獲得同樣的資訊。cardinality列很重要，表示資料量。

7、show tables status: 查看資料庫表的底層大小以及表結構，同樣可以從information_schema.tables表中獲得底層表的資訊。

8、show [global|session]status:可以查看mysql伺服器當前內部狀態資訊。可以協助卻行mysql伺服器的負載的各種指標。預設是session。同information_schema.global_status和information_schema.session_status

9、show [global|session] variables ：查看當前mysql系統變數的值，其中一些值能影響到sql語句的執行方式。同information_schema.global_variables和information_schema.session_variables;

10、information_schema:包含的表的數量和mysql的版本有關係。

 

-------------------------

三、索引

 1、資料完整性：通過主鍵和唯一鍵來確保資料唯一性

主鍵（primary key）：每個表只能有一個，主鍵不為null，定義了auto_increment列，那麼此列就必須是主鍵的一部分。

唯一鍵（unique key）：表中可以存在多個唯一鍵，每個key可以為null，即null！=null

2、索引術語：

索引技術：

索引實現：

索引類型：

3、建立單列索引：這裡要注意的是可以在一列上建立多個索引，但是這樣會產生效能開銷，

alter table table_name add primary key|index index_name (coumn_name);

4、當存在多個索引的時候，如何確定選擇使用哪個索引更高效？

根據索引中唯一值和索引中總行數做比較，唯一值越多，使用這個索引時以更少讀得到查詢結果。

禁用最佳化器設定：set @@session.optimizer_switch=‘index_merge_intersection=off‘;

5、在like查詢中（%oo）不會走索引

6、不支援函數索引。另外在索引上使用函數，執行計畫也不會走索引。

7、唯一索引：提供資料完整性，保證在列中任何值都出現一次，告知最佳化器，給定的記錄最多隻有一條結果返回，避免了額外的索引掃描，是否進行了額外的索引掃描，可以使用下邊的語句查看：

flush status;

show session status like ‘handler_read_next‘;

select name from student where name=‘Randy‘;

show session status like ‘handler_read_next‘;

8、對於結果的排序：如果不是根據索引欄位進行排序，mysql會使用內部檔案排序演算法對返回額行按照指定順序進行排序。使用索引欄位進行排序的話將免去分類的過程。

9、複合式索引：索引的執行順序是按照複合式索引中每個欄位的cardinality來確定使用哪個複合式索引。有的時候交換一下複合式索引列的順序，執行效率會更好。主要可能複合式索引的最左列。

複合式索引列不要太寬，ref表示使用了那幾個複合式索引欄位，key_len表示索引欄位的長度

10、查詢提示：

通過查詢提示來修改查詢的執行計畫。

straight_join: 強制按照from後邊的關聯順序去執行連結操作，不管執行計畫是否是最優的。

11、索引提示：

索引提示都會被連結中的表來使用，為每張表定義use、ignore、force類表

use [index|key] [for( join|order_by|group_by)] [index_list]  --讓最佳化器傾向於使用指定的索引掃描

ignore [index|key] [for( join|order_by|group_by)] [index_list]

force [index|key] [for( join|order_by|group_by)] [index_list] --讓最佳化器傾向於索引掃描而不是全表掃描

12、索引DML操作的影響：影響寫操作的效能。重複索引、

13、通過 show index_staistics來查看索引的使用方式，來確定哪些索引沒有被使用。沒有被使用的索引佔用空間，影響寫入效能，

14、DDL：在修改表索引的時候會鎖表。會影響儲存空間的大小。

-----------------

 

四、搜尋引擎

MySIAM：一種非事物行的儲存引擎

InnoDB：事務性儲存引擎

Memory：基於記憶體的、非事物性的以及非持久性的儲存引擎

查看錶的儲存引擎：

show create table \show table status \ information_schema.tables可以查看錶的儲存引擎

【MySql】效能最佳化之分析命令