|NO.Z.00152|——————————|BigDataEnd|——|Java&MySQL.高级.V24|——|MySQL.v25|EXPLAIN_rows字段介绍|

一、EXPLAN_rows字段介绍

### --- rows 介绍
——>        表示MySQL根据表统计信息及索引选用情况，估算的找到所需的记录所需要读取的行数；
——>        越少越好使用like 查询,会产生全表扫描, L2中有3条记录,就需要读取3条记录进行查找

EXPLAIN SELECT * FROM L1,L2 WHERE L1.id = L2.id AND L2.title LIKE '%la%';
mysql> EXPLAIN SELECT * FROM L1,L2 WHERE L1.id = L2.id AND L2.title LIKE '%la%';
+----+-------------+-------+------------+--------+---------------+---------+---------+--------------------+------+----------+-------------+
| id | select_type | table | partitions | type   | possible_keys | key     | key_len | ref                | rows | filtered | Extra       |
+----+-------------+-------+------------+--------+---------------+---------+---------+--------------------+------+----------+-------------+
|  1 | SIMPLE      | L1    | NULL       | ALL    | PRIMARY       | NULL    | NULL    | NULL               |    1 |   100.00 | NULL        |
|  1 | SIMPLE      | L2    | NULL       | eq_ref | PRIMARY       | PRIMARY | 4       | test_explain.L1.id |    1 |   100.00 | Using where |
+----+-------------+-------+------------+--------+---------------+---------+---------+--------------------+------+----------+-------------+

### --- 如果使用等值查询, 则可以直接找到要查询的记录,返回即可,所以只需要读取一条

EXPLAIN SELECT * FROM L1,L2 WHERE L1.id = L2.id AND L2.title = 'yanqi03';
mysql> EXPLAIN SELECT * FROM L1,L2 WHERE L1.id = L2.id AND L2.title = 'yanqi03';
+----+-------------+-------+------------+--------+-------------------+---------+---------+--------------------+------+----------+-------------+
| id | select_type | table | partitions | type   | possible_keys     | key     | key_len | ref                | rows | filtered | Extra       |
+----+-------------+-------+------------+--------+-------------------+---------+---------+--------------------+------+----------+-------------+
|  1 | SIMPLE      | L1    | NULL       | ALL    | PRIMARY           | NULL    | NULL    | NULL               |    1 |   100.00 | NULL        |
|  1 | SIMPLE      | L2    | NULL       | eq_ref | PRIMARY,idx_title | PRIMARY | 4       | test_explain.L1.id |    1 |   100.00 | Using where |
+----+-------------+-------+------------+--------+-------------------+---------+---------+--------------------+------+----------+-------------+

### --- 总结: 当我们需要优化一个SQL语句的时候，我们需要知道该SQL的执行计划，
~~~     比如是全表扫描，还是索引扫描； 使用explain 关键字可以模拟优化器执行sql 语句，
~~~     从而知道mysql 是如何处理sql 语句的,方便我们开发人员有针对性的对SQL进行优化.

——>        表的读取顺序。（对应id）
——>        数据读取操作的操作类型。（对应select_type）
——>        哪些索引可以使用。（对应possible_keys）
——>        哪些索引被实际使用。（对应key）
——>        每张表有多少行被优化器查询。（对应rows）
——>        评估sql的质量与效率 (对应type)

---

 

 

 

 

 

 

 

 

 

---

Walter Savage Landor:strove with none,for none was worth my strife.Nature I loved and, next to Nature, Art:I warm'd both hands before the fire of life.It sinks, and I am ready to depart

                                                                                                                                                   ——W.S.Landor

---