mysql 第一个sql优化

<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 也是假的