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