MySQL執行語句優化（下）

本文是內部分享概要,主要是問題點,不適合作為知識點系統學習.

1. 本文主要解決三個問題:
1. use index conditon出現在什麼場景？這裡是否遵守最左前綴匹配（left-most）
2. groupby 如何才能不能臨時表？
3. mysql執行插入操作哪一步最費時？
2. 讀之前可需要初步了解下面2個概念?
1. 什麼叫覆蓋索引？
2. 如何查看mysql執行計劃，一般參考那些？

1.Index Condition Pushdown Optimization

idx_zip_com_add (zipcode,company,address(255))

explain ... where zipcode = 843000 and company like %醫院% and address like %新疆%;

限制：如果where條件的欄位不在索引列中,還是要讀取整表的記錄到server端做where過濾。

show global variables like %optimizer_switch%; #index_condition_pushdown

Using Index Condition Pushdown, the scan proceeds like this instead:

1. Get the next rows index tuple (but not the full table row).
2. Test the part of the WHERE condition that applies to this table and can be checked using only index columns. If the condition is not satisfied, proceed to the index tuple for the next row.
3. If the condition is satisfied, use the index tuple to locate and read the full table row.
4. Test the remaining part of the WHERE condition that applies to this table. Accept or reject the row based on the test result.

課後習題：address(255)啥意思？參考鏈接

explain select * from promoter_order_settle where kdt_id=27666536 and settle_no like S20180% ;

explain select * from promoter_order_settle where kdt_id=27666536 and settle_no like S201802121705010271117% ;

2.Multi-Range Read Optimization

SELECT * FROM t WHERE key_part1 >= 1000 AND key_part1 < 2000 AND key_part2 = 10000;

3.ORDER BY Optimization

explain select * from promoter_order_settle where kdt_id=40927013 order by settle_no desc limit 200;explain select * from promoter_order_settle where kdt_id=40927013 order by settle_no,id desc limit 200;

1.如果有索引會優先使用索引，否則就需要使用文件排序，文件排序分兩種：

4.group by 優化

Loose Index Scan，Tight Index Scan：

前者支持部分匹配，後者可以不滿足前綴匹配，但是會讓所有索引條件出現

Loose Index Scan

SELECT c1, c2 FROM t1 GROUP BY c1, c2;SELECT DISTINCT c1, c2 FROM t1;SELECT c1, MIN(c2) FROM t1 GROUP BY c1;SELECT c1, c2 FROM t1 WHERE c1 < const GROUP BY c1, c2;SELECT MAX(c3), MIN(c3), c1, c2 FROM t1 WHERE c2 > const GROUP BY c1, c2;SELECT c2 FROM t1 WHERE c1 < const GROUP BY c1, c2;SELECT c1, c2 FROM t1 WHERE c3 = const GROUP BY c1, c2;##Were the query to include WHERE c3 = const, loose index scan could be used. SELECT c1, c3 FROM t1 GROUP BY c1, c2;

Tight Index Scan

SELECT c1, c2, c3 FROM t1 WHERE c2 = a GROUP BY c1, c3;

distinct是一種特殊group

5.Optimizing INSERT Statements

使用批量插入。msyql插入時間開銷

• Connecting: (3)
• Sending query to server: (2)
• Parsing query: (2)
• Inserting row: (1 × size of row)log2N
• Inserting indexes: (1 × number of indexes)
• Closing: (1)

insert into my_test (id,kdt_id)VALUES(0,4),(0,5),(0,6)

推薦閱讀：