大家好,又见面了,我是全栈君。
做一个积极的人 编码、改bug、提升自己 我有一个乐园,面向编程,春暖花开
使用 explain 查询和分析SQl的执行记录,可以进行sql的性能优化!
mysql> explain select * from students;
+----+-------------+----------+------+---------------+------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------+------+---------------+------+---------+------+------+-------+
| 1 | SIMPLE | students | ALL | NULL | NULL | NULL | NULL | 3 | |
+----+-------------+----------+------+---------------+------+---------+------+------+-------+
1 row in set
mysql> explain extended select * from students;
+----+-------------+----------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+----------+------+---------------+------+---------+------+------+----------+-------+
| 1 | SIMPLE | students | ALL | NULL | NULL | NULL | NULL | 3 | 100 | |
+----+-------------+----------+------+---------------+------+---------+------+------+----------+-------+
1 row in set
SELECT类型,可以为以下任何一种:
输出的行所引用的表!
联接类型。下面给出各种联接类型,按照从最佳类型到最坏类型进行排序:
指出MySQL能使用哪个索引在表中找到记录,查询涉及到的字段上若存在索引,则该索引将被列出,但不一定被查询使用
显示MySQL在查询中实际使用的索引,若没有使用索引,显示为NULL
表示索引中使用的字节数,可通过该列计算查询中使用的索引的长度(key_len显示的值为索引字段的最大可能长度,并非实际使用长度,即key_len是根据表定义计算而得,不是通过表内检索出的)
表示上述表的连接匹配条件,即哪些列或常量被用于查找索引列上的值
显示MySQL认为它执行查询时必须检查的行数。多行之间的数据相乘可以估算要处理的行数
显示了通过条件过滤出的行数的百分比估计值。
该列包含MySQL解决查询的详细信息
mysql> show keys from students;
+----------+------------+-------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+----------+------------+-------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| students | 0 | PRIMARY | 1 | stud_id | A | 3 | NULL | NULL | | BTREE | | |
| students | 1 | index_create_date | 1 | create_date | A | 3 | NULL | NULL | YES | BTREE | | |
+----------+------------+-------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set
mysql> explain select * from students where stud_id = '1';
+----+-------------+----------+-------+---------------+---------+---------+-------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------+-------+---------------+---------+---------+-------+------+-------+
| 1 | SIMPLE | students | const | PRIMARY | PRIMARY | 4 | const | 1 | |
+----+-------------+----------+-------+---------------+---------+---------+-------+------+-------+
1 row in set
mysql> explain select * from students where create_date = '2010-01-01';
+----+-------------+----------+------+-------------------+-------------------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------+------+-------------------+-------------------+---------+-------+------+-------------+
| 1 | SIMPLE | students | ref | index_create_date | index_create_date | 4 | const | 1 | Using where |
+----+-------------+----------+------+-------------------+-------------------+---------+-------+------+-------------+
1 row in set
发布者:全栈程序员栈长,转载请注明出处:https://javaforall.cn/121081.html原文链接:https://javaforall.cn