6. 查詢速度由好到壞
? 1. where 條件使用在 primary key 上 (system)
? 2. where 條件使用在 index 上 (type:using index)
? 3. where 條件使用在一般資料上 (type:using where)
6
7. 一個複雜的查詢 VS 多個簡單查詢？(1/2)
? 一個複雜的查詢
– SELECT post.*
– FROM tag
– JOIN tag_post ON tag_post.tag_id = tag.id
– JOIN post ON tag_post.post_id = post.id
– WHERE tag.tag = 'mysql';
? 多個簡單查詢
– SELECT * FROM tag WHERE tag = 'mysql';
– SELECT * FROM tag_post WHERE tag_id = 123;
– SELECT * FROM post WHERE post_id IN (123,456,789);
7
10. mysql 優化器的一些限制(1/3)
? 一個關聯式的查詢：
– SELECT *
– FROM sakila.film
– WHERE film_id IN (
– SELECT film_id FROM sakila.film_actor WHERE actor_id = 1
– );
? 我們以為的執行方式：
– Step1. SELECT film_id FROM sakila.film_actor WHERE actor_id = 1;
– ANS : film_id = 1,2,3,4,5,6
– Step2. SELECT * FROM sakila.film WHERE film_id IN (1,2,3,4,5,6);
10
11. mysql 優化器的一些限制(2/3)
? 一個關聯式的查詢：
– SELECT *
– FROM sakila.film
– WHERE film_id IN (
– SELECT film_id FROM sakila.film_actor WHERE actor_id = 1
– );
? 實際上執行的方式：
– SELECT *
– FROM sakila.film
– WHERE EXISTS (
– SELECT *
– FROM sakila.film_actor
– WHERE actor_id = 1 AND film_actor.film_id = film.film_id
– );
11
12. mysql 優化器的一些限制(3/3)
? 解決的方法(1). 使用INNER JOIN
– SELECT film.*
– FROM sakila.film
– JOIN sakila.film_actor USING (film_id)
– WHERE film.actor_id = 1;
? 解決的方法(2). 使用 GROUP_CONCAT 作為 subquery 的
index
– SELECT *
– FROM sakila.film
– WHERE film_id IN (
– SELECT GROUP_CONCAT(film_id)
– FROM sakila.film_actor WHERE actor_id = 1
– );
12
14. 如何優化關連式查詢？
? 1. 確保 ON 或 USING 的子句內，使用的列(column)最好是
index
– table-A & table-B 使用 column-C 做關聯，如果關聯的順序 B -> A，
那這樣就只需要在A中建立index即可
– 一般來說只需要在關聯順位第二個加上index即可
? 2. 確保 GROUP BY 或 ORDER BY 指令只牽扯到同一個資料表
中的列(column)，
這樣mysql才有可能會使用index來處理！
14
15. 如何優化子查詢(subquery)？
? 若無法使用 index，mysql 會採用臨時資料表(tempo table)，或
是直接將檔案排序 (filesort)
? 我們在寫的時候盡量以使用 index為目標!
? Before
– SELECT actor.first_name, actor.last_name, COUNT(*)
– FROM sakila.film_actor
– INNER JOIN sakila.actor USING(actor_id)
– GROUP BY actor.first_name, actor.last_name;
? After (利用演員姓名和ID都屬於同一筆資料的特性！)
– SELECT actor.first_name, actor.last_name, COUNT(*)
– FROM sakila.film_actor
– INNER JOIN sakila.actor USING(actor_id)
– GROUP BY film_actor.actor_id;
15
16. 如何優化LIMIT？(1/2)
? 在mysql中，LIMIT 5000, 5
– SELECT film_id, description FROM sakila.film ORDER BY title LIMIT
5000, 5;
– 需要掃描 5005筆，但是只取 5筆資料出來使用
? 再讀取下一批，LIMIT 5005, 5
– SELECT film_id, description FROM sakila.film ORDER BY title LIMIT
5005, 5;
– 需要掃描 5010筆，但是只取 5筆資料出來使用
? 重複10回，執行的代價相當的高！
16
21. 2. Select_type
? (1) SIMPLE : 最簡單的 SELECT(不使用UNION或子查询等)
? (2) PRIMARY : 最外層的SELECT.
? (3) UNION : UNION中的第二個或是後面的SELECT语句.
? (4) DEPENDENT UNION :
UNION的第二個或後面SELECT的來源是子查詢
? (5) UNION RESULT : UNION的结果.
? (6) SUBQUERY : 子查詢中的第一個SELECT.
– select * from t3 where id = (select id from t3 where id=3952602 ) ;
? (7) DEPENDENT SUBQUERY : 子查詢中的第一個SELECT
– select * from t3 where id IN (select id from t3 where id=3952602 ) ;
? (8) DERIVED : FROM子句的子查詢(暫存表)
21
29. 10. Extra (1/2)
? (1) Distinct：當MySQL找到相關連的資料時，就不再搜尋。
? (2) Not exists：
MySQL優化 LEFT JOIN，
一旦找到符合的LEFT JOIN資料後，就不再搜尋。
? (3) Range checked for each Record：
MySQL無法找到理想的索引。此為最慢的使用索引。
? (4) Using filesort：
當出現這個值時，表示此SELECT語法需要優化。
因為MySQL必須進行額外的步驟來進行資料掃描，
此情況會發生在針對不同的資料進行ORDER BY或是GROUP BY
? (5) Using index：
返回的資料是從index中取得的資料，而不是從實際的table中返回，
當返回的資料都出現在索引中的資料時就會發生此情況。
? (6) Using temporary：
此為MySQL必須建立一個暫時的資料表(Table)來儲存結果。
29
