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

Question

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

Answer 1

4.1. EXPLAIN
In MySQL, you can use EXPLAIN to view the SQL execution plan. Usage: EXPLAIN SELECT * FROM tb_item

4.2. Result description
4.2.1. id
SELECT identifier. This is the SELECT query sequence number. This is not important.
4.2.2. select_type
indicates the type of SELECT statement.

has the following values:
1. SIMPLE
represents a simple query, which does not include join queries and subqueries.
2. PRIMARY
represents the main query, or the outermost query statement.

3. UNION
represents the second or subsequent query statement of the connection query.

4. DEPENDENT UNION
The second or subsequent SELECT statement in UNION depends on the external query.
5. UNION RESULT
The result of the connection query.
6. SUBQUERY
The first SELECT statement in the subquery.

7. DEPENDENT SUBQUERY
The first SELECT statement in the subquery depends on the external query.
8. DERIVED
SELECT (subquery of FROM clause).
4.2.3. table
represents the query table.
4.2.4. type (important)
indicates the connection type of the table.
The following connection types are ordered from best to worst:

1. The system
table has only one row. This is a special column of const type and does not usually appear. This can be ignored.
2. const
The data table can only have one matching row at most. Because it only matches one row of data, it is very fast. It is often used for queries on PRIMARY KEY or UNIQUE indexes. It can be understood that const is the most optimized.

3. eq_ref
MySQL manual says this: "For each combination of rows from the previous table, read one row from that table. This is probably the best join type, except for the const type. It is used in a All parts of the index are used by the join and the index is UNIQUE or PRIMARY KEY". eq_ref can be used to compare indexed columns using =.

4. ref
The query condition index is neither UNIQUE nor PRIMARY KEY. ref can be used on indexed columns with the = or < or > operators.

5. ref_or_null
This join type is like ref, but with the addition of MySQL, it can specifically search for rows containing NULL values. This join type of optimization is often used in solving subqueries.

The above five situations are all ideal index usage situations.

6. index_merge
This join type indicates that the index merge optimization method is used. In this case, the key column contains the list of indexes used, and key_len contains the longest key element of the index used.
7. unique_subquery
This type replaces the ref of the IN subquery in the following form: value IN (SELECT primary_key FROM single_table WHERE some_expr)
unique_subquery is an index lookup function that can completely replace the subquery and is more efficient.
8. index_subquery
This connection type is similar to unique_subquery. The IN subquery can be replaced, but only for non-unique indexes in subqueries of the following form: value IN (SELECT key_column FROM single_table WHERE some_expr)
9, range
Only retrieve rows in a given range, using an index to select rows.

10. index
This join type is the same as ALL, except that only the index tree is scanned. This is usually faster than ALL because index files are usually smaller than data files.
11. ALL
Perform a complete table scan for each combination of rows from the previous table. (Worst performance)
4.2.5. possible_keys
Indicates which index MySQL can use to find rows in this table.
If the column is NULL, it means no index is used. You can create an index on the column to improve performance.
4.2.6. key
Shows the key (index) that MySQL actually decided to use. If no index is selected, the key is NULL.

You can force the use of indexes or ignore them:

4.2.7. key_len
Shows the key length that MySQL decides to use. If the key is NULL, the length is NULL.

Note: key_len determines the actual index length that MySQL will use.

4.2.8. ref
Shows which column or constant is used with key to select rows from the table.
4.2.9. rows
Displays the number of rows that MySQL thinks it must check when executing the query.
4.2.10. Extra
This column contains the details of how MySQL solved the query
• Distinct: After MySQL finds the first matching row, it stops searching for more rows for the current row combination.
• Not exists: MySQL can perform LEFT JOIN optimization on the query. After finding a row matching the LEFT JOIN standard, it will no longer check more rows in the table for the previous row combination.
• range checked for each record (index map: #): MySQL did not find a good index that can be used, but found that if the column values ​​from the previous table are known, some indexes may be used.
• Using filesort: MySQL requires an extra pass to figure out how to retrieve the rows in sorted order.
• Using index: Retrieve column information from a table by reading the actual rows using only the information in the index tree without further searching.
• Using temporary: In order to solve the query, MySQL needs to create a temporary table to hold the results.
• Using where:WHERE clause is used to limit which row matches the next table or is sent to the client.
• Using sort_union(...), Using union(...), Using intersect(...): These functions illustrate how to merge index scans for the index_merge join type.
• Using index for group-by: Similar to the Using index method of accessing a table, Using index for group-by means that MySQL has found an index that can be used to query all columns of GROUP BY or DISTINCT queries without additional search for hard disk access. actual table.

Listed below are some database SQL optimization solutions:
(01) Choose the most efficient order of table names (frequently tested in written exams)

  数据库的解析器按照从右到左的顺序处理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) Connection order in WHERE clause (frequent written test)

  数据库采用自右而左的顺序解析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）Avoid using * sign in SELECT clause

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

(04) Replace DELETE with TRUNCATE

(05) Use COMMIT as much as possible

  因为COMMIT会释放回滚点

(06) Replace HAVING clause with WHERE clause

  WHERE先执行，HAVING后执行
 

(07) Use more internal functions to improve SQL efficiency

(08) Using table aliases

  salgrade s
 

(09) Using column aliases

  ename e

Answer 2

EXPLAIN

Answer 3

It is recommended that you use a visual tool, navicat, which will automatically perform explain analysis operations when executing SQL, and is more convenient to operate than the command line.

Answer 4

Not long after my internship, I used SQL Server, which was also found and recorded by others.
DBCC DROPCLEANBUFFERS clears the cache
DBCC FREEPROCCACHE deletes elements in the plan cache
SET STATISTICS TIME ON to see the CPU time
SET STATISTICS IO ON to see the number of logical reads
SET STATISTICS PROFILE ON This will not work