mysql+explain实例分析

－－－－－－－－－－－－－－－－－－explain

gll_products索引：
class_sn       INDEX       250   sn_class
                                        sn_no



1 EXPLAIN SELECT * FROM `gll_products` where `sn_class` =4;




| id | select_type | table        | type | possible_keys | key      | key_len | ref   | rows | Extra |
+----+-------------+--------------+------+---------------+----------+---------+-------+------+-------+
|  1 | SIMPLE      | gll_products | ref  | class_sn      | class_sn | 4       | const |   12 |       |
+----+-------------+--------------+------+---------------+----------+---------+-------+------+-------+


详细的诠释

id: SELECT识别符。这是SELECT的查询序列号。
select_type: 可以为以下任何一种
    SIMPLE: 简单SELECT(不使用UNION或子查询)
    PRIMARY: 最外面的SELECT
    UNION: UNION中的第二个或后面的SELECT语句
    DEPENDENT UNION: 子查询中的第一个SELECT，取决于外面的查询
    DERIVED: 导出表的SELECT(FROM子句的子查询)


table 显示这一行的数据是关于哪张表的
type 这是重要的列，显示连接使用了何种联接类型。从最好到最差的连接类型为system, cons,eq_ref, ref, ref_or_null, index_merge, unique_subquery, index_subquery,range、index和ALL

possible_keys 显示可能应用在这张表中的索引 。如果为NULL，没有可能的索引。可以为相关的域从WHERE语句中选择一个合适的语句
key 实际使用的索引 。如果为NULL，则没有使用索引。很少的情况下，MYSQL会选择优化不足的索引。这种情况下，可以在SELECT语句中使用USE INDEX（indexname）来强制使用一个索引或者用IGNORE INDEX（indexname）来强制MYSQL忽略索引
key_len 使用的索引的长度。在不损失精确性的情况下，长度越短越好
ref 显示索引的哪一列被使用了，如果可能的话，是一个常数
rows MYSQL认为必须检查的用来返回请求数据的行数
Extra 关于MYSQL如何解析查询的额外信息。将在表4.3中讨论，但这里可以看到的坏的例子是Using temporary和Using filesort，意思MYSQL根本不能使用索引，结果是检索会很慢

extra 该列包含MySQL解决查询的详细信息。
    Distinct: 一旦MYSQL找到了与行相联合匹配的行，就不再搜索了
    Not exists: MYSQL优化了LEFT JOIN，一旦它找到了匹配LEFT JOIN标准的行，就不再搜索了
    Range checked for each:    Record（index map:#）没有找到理想的索引，因此对于从前面表中来的每一个行组合，MYSQL检查使用哪个索引，并用它来从表中返回行。这是使用索引的最慢的连接之一
    Using filesort: 看到这个的时候，查询就需要优化了。MYSQL需要进行额外的步骤来发现如何对返回的行 排序。它根据连接类型以及存储排序键值和匹配条件的全部行的行指针来排序全部行
    Using index: 列数据是从仅仅使用了索引中的信息而没有读取实际的行动的表返回的，这发生在对表的全 部的请求列都是同一个索引的部分的时候
    Using temporary: 看到这个的时候，查询需要优化了。这里，MYSQL需要创建一个临时表来存储结果，这通常发生在对不同的列集进行ORDER BY上，而不是GROUP BY上
   Using where: WHERE子句用于限制哪一个行匹配下一个表或发送到客户。除非你专门从表中索取或检查 所有行，如果Extra值不为Using where并且表联接类型为ALL或index，查询可能会有一些错误




2 EXPLAIN SELECT * FROM `gll_products` where `sn_class` =4 and sn_no='A5100-4';



| id | select_type | table        | type | possible_keys | key      | key_len | ref         | rows | Extra       |
+----+-------------+--------------+------+---------------+----------+---------+-------------+------+-------------+
|  1 | SIMPLE      | gll_products | ref  | class_sn      | class_sn | 771     | const,const |    1 | Using where |
+----+-------------+--------------+------+---------------+----------+---------+-


对比1 语句，发现key_len变大了许多



－－－－－－－－－－－－－－－－－－－－gll_pro_ru索引：
class_ctime_no       INDEX       538                    sn_class
                                                             sn_no
                                                             ctime

3 EXPLAIN SELECT * FROM `gll_pro_ru` WHERE sn_class=2 and ctime > '2009-04-01';

 id | select_type | table      | type | possible_keys  | key            | key_len | ref   | rows | Extra       |
+----+-------------+------------+------+----------------+----------------+---------+-------+------+-------------+
|  1 | SIMPLE      | gll_pro_ru | ref  | class_ctime_no | class_ctime_no | 4       | const |  114 | Using where |



EXPLAIN SELECT * FROM `gll_pro_ru` WHERE sn_class=2;


 id | select_type | table      | type | possible_keys  | key            | key_len | ref   | rows | Extra |
+----+-------------+------------+------+----------------+----------------+---------+-------+------+-------+
|  1 | SIMPLE      | gll_pro_ru | ref  | class_ctime_no | class_ctime_no | 4       | const |  114 |       |

 

 

 

---------------------------------------------------------------实例分析1:索引对应的SQL【详细分析见附件：CU提问和解答过程】

http://bbs3.chinaunix.net/viewthread.php?tid=1534941

 

Index_U_Id:  `type`, `ischecked`, `u_id`，
and连接的条件的顺序并不影响索引使用的判断。
索引列的顺序会影响索引使用的判断

---------------以下语句可以用到索引


explain SELECT `ios_seeds`.* FROM `ios_seeds` WHERE (ischecked =1 AND type=01 ) ORDER BY `id` ASC ;
explain SELECT `ios_seeds`.* FROM `ios_seeds` WHERE (type=01 AND ischecked =1) ORDER BY `id` ASC ;

explain SELECT `ios_seeds`.* FROM `ios_seeds` WHERE (u_id=14 AND type=01 ) ORDER BY `id` ASC ;
explain SELECT `ios_seeds`.* FROM `ios_seeds` WHERE (type=01 AND u_id=14) ORDER BY `id` ASC ;

explain SELECT `ios_seeds`.* FROM `ios_seeds` WHERE type=01 ORDER BY `id` ASC ;


explain SELECT `ios_seeds`.* FROM `ios_seeds` WHERE (u_id=14 AND ischecked =1 AND type=01) ORDER BY `id` ASC ;
explain SELECT `ios_seeds`.* FROM `ios_seeds` WHERE (u_id=14 AND type=01 AND ischecked =1 ) ORDER BY `id` ASC ;
explain SELECT `ios_seeds`.* FROM `ios_seeds` WHERE (type=01 AND ischecked =1 AND u_id=14) ORDER BY `id` ASC ;
explain SELECT `ios_seeds`.* FROM `ios_seeds` WHERE (type=01 AND u_id=14 AND ischecked =1) ORDER BY `id` ASC ;
explain SELECT `ios_seeds`.* FROM `ios_seeds` WHERE (ischecked =1 AND u_id=14 AND type=01 ) ORDER BY `id` ASC ;
explain SELECT `ios_seeds`.* FROM `ios_seeds` WHERE (ischecked =1 AND type=01 AND u_id=14) ORDER BY `id` ASC ;


------------以下语句没有用到索引。

explain SELECT `ios_seeds`.* FROM `ios_seeds` WHERE (u_id=14 AND ischecked =1 ) ORDER BY `id` ASC ;
explain SELECT `ios_seeds`.* FROM `ios_seeds` WHERE (ischecked =1 AND u_id=14 ) ORDER BY `id` ASC ;
explain SELECT `ios_seeds`.* FROM `ios_seeds` WHERE u_id=14 ORDER BY `id` ASC ;
explain SELECT `ios_seeds`.* FROM `ios_seeds` WHERE ischecked =1 ORDER BY `id` ASC ;


我又针对这些没有用到索引的语句增加了两个索引，以确保所用可能的SQL语句都能用到索引：

Index_U_Id2:  `u_id`, `ischecked` ：
explain SELECT `ios_seeds`.* FROM `ios_seeds` WHERE (u_id=14 AND ischecked =1 ) ORDER BY `id` ASC ;
explain SELECT `ios_seeds`.* FROM `ios_seeds` WHERE (ischecked =1 AND u_id=14 ) ORDER BY `id` ASC ;
explain SELECT `ios_seeds`.* FROM `ios_seeds` WHERE u_id=14 ORDER BY `id` ASC ;

Index_Check:  `ischecked` ：
explain SELECT `ios_seeds`.* FROM `ios_seeds` WHERE ischecked =1 ORDER BY `id` ASC ;


这样做合理吗？是不是索引太多了？

 

 

是否合理就要看你表中的数据是怎样的了。
你新列出的那些语句是否在应用中经常用到？
此外ischecked =1 此类的条件能排除掉多少记录也是比较关键的。

例如你ischecked =1 的记录有10000条
而ischecked =0 的记录只有100条
索引并不能排除大部分的数据，那么还不如不加这样的索引。因为索引的读取和载入都是需要耗费资源的。

 

 

 

 

 

 

---------------------------------------------------------------实例分析2:为什么没有使用到索引

http://bbs3.chinaunix.net/viewthread.php?tid=1541527

 

mysql> show index from gll_sale;
+----------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table    | Non_unique | Key_name  | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+----------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| gll_sale |          0 | PRIMARY   |            1 | id          | A         |         196 |     NULL | NULL   |      | BTREE      |         |
| gll_sale |          1 | Index_All |            1 | uid         | A         |           1 |     NULL | NULL   |      | BTREE      |         |
+----------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
2 rows in set (0.01 sec)

mysql> explain select * from gll_sale  WHERE uid = 22 ;
+----+-------------+----------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table    | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+----------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | gll_sale | ALL  | Index_All     | NULL | NULL     | NULL |  147 | Using where |
+----+-------------+----------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

 

你uid的Cardinality为1。说明你表中uid的值都为22。
mysql判定即使使用index也无法排除大部分数据，因此执行计划中没有使用索引。

跟你的uid多设置些不同的值，mysql就会用index_all了


Cardinality越大表示索引候选分得越细