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

EXPLAIN

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 这个不会用