<pre name="code" class="sql">type=const表示通过索引一次就找到了;
key=primary的话,表示使用了主键;
type=all,表示为全表扫描;
key=null表示没用到索引。
type=ref,因为这时认为是多个匹配行,在联合查询中,一般为REF。
ALL: 扫描全表
index: 扫描全部索引树
range: 扫描部分索引,索引范围扫描,对索引的扫描开始于某一点,返回匹配值域的行,常见于between、<、>等的查询
ref: 非唯一性索引扫描,返回匹配某个单独值的所有行。常见于使用非唯一索引即唯一索引的非唯一前缀进行的查找
eq_ref:唯一性索引扫描,对于每个索引键,表中只有一条记录与之匹配。常见于主键或唯一索引扫描
const, system: 当MySQL对查询某部分进行优化,并转换为一个常量时,使用这些类型访问。如将主键置于where列表中,MySQL就能将该查询转换为一个常量。system是const类型的特例,当查询的表只有一行的情况下, 使用system。
NULL: MySQL在优化过程中分解语句,执行时甚至不用访问表或索引。
mysql> explain SELECT cpi.personName, ccd.clientSn, ccd.income, ccd.pay, ccd.accountBalance, ccd.createdTime, ccd.remark from
-> (select * from ClientCashDetail ccd_int where
-> 1 >
-> (SELECT count(clientSn) from ClientCashDetail
-> where clientSn= ccd_int.clientSn and ccd_int.createdTime < createdTime and createdTime < TIMESTAMP(@dated_time) )
-> and ccd_int.createdTime < TIMESTAMP(@dated_time)
-> ) ccd
-> RIGHT JOIN ClientPersonalInfo cpi on cpi.clientSn = ccd.clientSn
-> where ccd.clientSn in (SELECT clientSn from ClientPersonalInfo where personName in (
-> '蔡明',
-> '苑秀凤',
-> ))
-> ORDER BY cpi.personName, ccd.clientSn, ccd.createdTime DESC;
+----+--------------------+--------------------+--------+---------------+-------------+---------+-------------------+------+---------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------------+--------------------+--------+---------------+-------------+---------+-------------------+------+---------------------------------+
| 1 | PRIMARY | cpi | ALL | PRIMARY | NULL | NULL | NULL | 937 | Using temporary; Using filesort |
| 1 | PRIMARY | ClientPersonalInfo | eq_ref | PRIMARY | PRIMARY | 4 | zjzc.cpi.clientSn | 1 | Using where |
| 1 | PRIMARY | <derived2> | ref | <auto_key0> | <auto_key0> | 4 | zjzc.cpi.clientSn | 10 | NULL |
| 2 | DERIVED | ccd_int | ALL | NULL | NULL | NULL | NULL | 5999 | Using where |
| 3 | DEPENDENT SUBQUERY | ClientCashDetail | ALL | NULL | NULL | NULL | NULL | 5999 | Using where |
+----+--------------------+--------------------+--------+---------------+-------------+---------+-------------------+------+---------------------------------+
5 rows in set (0.11 sec)
mysql> show index from ClientCashDetail;
+------------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+------------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| ClientCashDetail | 0 | PRIMARY | 1 | sn | A | 5999 | NULL | NULL | | BTREE | | |
+------------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.01 sec)
create index ClientCashDetail_idx1 on ClientCashDetail(clientSn,createdTime);
DROP INDEX ClientCashDetail_idx1 ON ClientCashDetail;
加上索引后:
+----+--------------------+--------------------+--------+-----------------------+-----------------------+---------+-----------------------+------+---------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------------+--------------------+--------+-----------------------+-----------------------+---------+-----------------------+------+---------------------------------+
| 1 | PRIMARY | cpi | ALL | PRIMARY | NULL | NULL | NULL | 799 | Using temporary; Using filesort |
| 1 | PRIMARY | ClientPersonalInfo | eq_ref | PRIMARY | PRIMARY | 4 | zjzc.cpi.clientSn | 1 | Using where |
| 1 | PRIMARY | <derived2> | ref | <auto_key0> | <auto_key0> | 4 | zjzc.cpi.clientSn | 10 | NULL |
| 2 | DERIVED | ccd_int | ALL | NULL | NULL | NULL | NULL | 4958 | Using where |
| 3 | DEPENDENT SUBQUERY | ClientCashDetail | ref | ClientCashDetail_idx1 | ClientCashDetail_idx1 | 4 | zjzc.ccd_int.clientSn | 3 | Using where; Using index |
+----+--------------------+--------------------+--------+-----------------------+-----------------------+---------+-----------------------+------+---------------------------------+
5 rows in set (0.03 sec)
mysql> explain select * from ClientCashDetail;
+----+-------------+------------------+------+---------------+------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------------+------+---------------+------+---------+------+------+-------+
| 1 | SIMPLE | ClientCashDetail | ALL | NULL | NULL | NULL | NULL | 4958 | NULL |
+----+-------------+------------------+------+---------------+------+---------+------+------+-------+
1 row in set (0.00 sec)
mysql> select count(*) from ClientCashDetail;
+----------+
| count(*) |
+----------+
| 10371 |
+----------+
1 row in set (0.00 sec)
可以看出 mysql 的rows 也是假的
mysql 第一个sql优化
转载本文章为转载内容,我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题,欢迎原作者联系我们进行内容更正或删除文章。
提问和评论都可以,用心的回复会被更多人看到
评论
发布评论
相关文章
-
第一个jsp+sql
第一个jsp+sql
java jsp import title javaweb -
python pop 第一个 python第一个代码
#!usr/bin/env python#-*-coding:utf-8 -*- print("hello,world")
python pop 第一个 python