mysql选择第一个_mysql 第一个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 | | ref | | | 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 | | ref | | | 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 也是假的