mysql - 测试不同sql语句的查询效率

Question

没有深入学习过数据库，只是了解一些mysql基础和增删改操作，我想问问有没有工具(方法也行)，当数据表中的数据几百万条时，可以让我通过使用不同的sql语句，来直观的查看不同sql语句的执行效率，谢谢大家了

Answer 1

4.1. EXPLAIN
在MySQL中可以使用EXPLAIN查看SQL執行計劃，用法：EXPLAIN SELECT * FROM tb_item

4.2. 結果說明
4.2.1. id
SELECT識別符。這是SELECT查詢序號。這個不重要。
4.2.2. select_type
表示SELECT語句的型別。

有以下幾種值：
1、 SIMPLE
表示簡單查詢，其中不包含連接查詢和子查詢。
2、 PRIMARY
表示主查詢，或是最外面的查詢語句。

3、 UNION
表示連接查詢的第2個或後面的查詢語句。

4、 DEPENDENT UNION
UNION中的第二個或後面的SELECT語句，取決於外面的查詢。
5、 UNION RESULT
連接查詢的結果。
6、 SUBQUERY
子查詢中的第1個SELECT語句。

7、 DEPENDENT SUBQUERY
子查詢中的第1個SELECT語句，取決於外面的查詢。
8、 DERIVED
SELECT(FROM 子句的子查詢)。
4.2.3. table
表示查詢的表。
4.2.4. type（重要）
表示表的連接類型。
以下的連接類型的順序是從最佳類型到最差類型：

1、 system
表僅有一行，這是const類型的特列，平常不會出現，這個也可以忽略不計。
2、 const
數據表最多只有一個匹配行，因為只匹配一行數據，所以很快，常用於PRIMARY KEY或者UNIQUE索引的查詢，可理解為const是最優化的。

3、 eq_ref
mysql手冊是這樣說的:"對於每個來自於前面的表的行組合，從該表中讀取一行。這可能是最好的聯接類型，除了const類型。它用在一個索引的所有部分被聯接使用並且索引是UNIQUE或PRIMARY KEY"。 eq_ref可以用來使用=比較帶索引的欄位。

4、 ref
查詢條件索引既不是UNIQUE也不是PRIMARY KEY的情況。 ref可用於=或操作符的帶索引的列。

5、 ref_or_null
該聯結類型如同ref，但加入了MySQL可以專門搜尋包含NULL值的行。在解決子查詢中經常使用該聯結類型的最佳化。

上面這五種情況都是很理想的索引使用情況。

6、 index_merge
該聯結類型表示使用了索引合併最佳化方法。在這種情況下，key列包含了使用的索引的清單，key_len包含了使用的索引的最長的關鍵元素。
7、 unique_subquery
該類型替換了下面形式的IN子查詢的ref: value IN (SELECT primary_key FROM single_table WHERE some_expr)
unique_subquery是一個索引查找函數,可以完全替換子查詢,效率更高。
8、 index_subquery
該聯接型類似unique_subquery。可以替換IN子查詢,但只適合下列形式的子查詢中的非唯一索引: value IN (SELECT key_column FROM single_table WHERE some_expr)
9、 range
只檢索給定範圍的行,使用一個索引來選擇行。

10、 index
該聯結類型與ALL相同,除了只有索引樹被掃描。這通常比ALL快,因為索引檔通常比資料檔小。
11、 ALL
對於每個來自於先前的表的行組合,進行完整的表掃描。 （效能最差）
4.2.5. possible_keys
指出MySQL能使用哪個索引在該表中找到行。
如果該列為NULL，表示沒有使用索引，可以對該列建立索引來提高效能。
4.2.6. key
顯示MySQL實際決定使用的鍵(索引)。如果沒有選擇索引,鍵是NULL。

可以強制使用索引或忽略索引：

4.2.7. key_len
顯示MySQL決定使用的鍵長。如果鍵是NULL,則長度為NULL。

注意：key_len是確定了MySQL將實際使用的索引長度。

4.2.8. ref
顯示使用哪個列或常數與key一起從表中選擇行。
4.2.9. rows
顯示MySQL認為它執行查詢時必須檢查的行數。
4.2.10. Extra
該列包含MySQL解決查詢的詳細資訊
• Distinct:MySQL發現第1個符合行後,停止為目前的行組合搜尋更多的行。
• Not exists:MySQL能夠對查詢進行LEFT JOIN優化,發現1個匹配LEFT JOIN標準的行後,不再為前面的行組合在該表內檢查更多的行。
• range checked for each record (index map: #):MySQL沒有發現好的可以使用的索引,但發現如果來自前面的表的列值已知,可能部分索引可以使用。
• Using filesort:MySQL需要額外的一次傳遞,以找出如何按排序順序檢索行。
• Using index:從只使用索引樹中的資訊而不需要進一步搜尋讀取實際的行來檢索表中的列資訊。
• Using temporary:為了解決查詢,MySQL需要建立一個臨時表來容納結果。
• Using where:WHERE 子句用於限制哪一個行匹配下一個表或發送到客戶。
• Using sort_union(...), Using union(...), Using intersect(...):這些函式說明如何為index_merge聯結型別合併索引掃描。
• Using index for group-by:類似於訪問表的Using index方式,Using index for group-by表示MySQL發現了一個索引,可以用來查詢GROUP BY或DISTINCT查詢的所有列,而不要額外搜索硬碟訪問實際的表。

下面列出一些資料庫SQL最佳化方案：
（01）選擇最有效率的表名順序（筆試常考）

  数据库的解析器按照从右到左的顺序处理FROM子句中的表名， 
  FROM子句中写在最后的表将被最先处理，
  在FROM子句中包含多个表的情况下,你必须选择记录条数最少的表放在最后，
  如果有3个以上的表连接查询,那就需要选择那个被其他表所引用的表放在最后。
  例如：查询员工的编号，姓名，工资，工资等级，部门名
  select emp.empno,emp.ename,emp.sal,salgrade.grade,dept.dname
  from salgrade,dept,emp
  where (emp.deptno = dept.deptno) and (emp.sal between salgrade.losal and salgrade.hisal)          
  1)如果三个表是完全无关系的话，将记录和列名最少的表，写在最后，然后依次类推
  2)如果三个表是有关系的话，将引用最多的表，放在最后，然后依次类推

（02）WHERE子句中的連接順序（筆試常考）

  数据库采用自右而左的顺序解析WHERE子句,根据这个原理,表之间的连接必须写在其他WHERE条件之左,
  那些可以过滤掉最大数量记录的条件必须写在WHERE子句的之右。  
  例如：查询员工的编号，姓名，工资，部门名  
  select emp.empno,emp.ename,emp.sal,dept.dname
  from emp,dept
  where (emp.deptno = dept.deptno) and (emp.sal > 1500)   
  

（03）SELECT子句中避免使用*號

  数据库在解析的过程中,会将*依次转换成所有的列名，这个工作是通过查询数据字典完成的，这意味着将耗费更多的时间
  select empno,ename from emp;

（04）用TRUNCATE取代DELETE

（05）盡量多使用COMMIT

  因为COMMIT会释放回滚点

（06）用WHERE子句替換HAVING子句

  WHERE先执行，HAVING后执行
 

（07）多使用內部函數提高SQL效率

（08）使用表的別名

  salgrade s
 

（09）使用列的別名

  ename e

Answer 2

解釋一下

Answer 3

推薦你用一個視覺化工具，navicat，這個執行sql的時候會自動進行explain分析操作，操作起來比指令列方便。

Answer 4

剛實習沒多久，用的是sql server，也是別人那找來的紀錄下來。
DBCC DROPCLEANBUFFERS清空快取
DBCC FREEPROCCACHE 刪除計劃高速緩存中的元素
SET STATISTICS TIME ON 看CPU時間
SET STATISTICS IO ON 看邏輯讀取數
SET STATISTICS PROFILE ON 這個不會用