MySQL的逻辑分层，索引介绍和怎样看执行计划

    每个javaweb项目中，都离不开写sql语句，打开一个电商网站，商品的查询，用户的管理，库存的更新等，都涉及到sql语句，当一个商品的查询sql语句执行较慢，一两分钟都不能返回结果的话，直接回影响到用户的体验效果，严重的话，会流失客户，这里涉及到sql语句的优化，要想很好的进行优化，就得了解下mysql的逻辑分层。主要分为

• 连接层

        提供与客户端连接的服务

• 服务层

        1.提供用户使用的接口，如select，insert，update，delete等

        2.提供sql优化器（MySQL QUery Optimizer）

• 引擎层

        提供了各种存储数据的方式（InnoDB MyISAM）

• 存储层

        存储数据

    接下来我们说说，sql语句的解析过程，我们写sql语句时，按照select 。。。from。。。where。。。group by。。。ordery by。。。等等，实际解析顺序是from。。。on。。。join。。。where。。。group by。。。having。。。select distinct。。。ordery by limit。。。

    索引，我们可以理解为一个字典的目录页，我们想在字典中找到我们想要查的字，首先就要打开目录，按照字母的顺序，找到字所在的页，翻到那一页，同样，MySQL的索引也是同一个道理，当我们在存储数据的时候，为每一条数据做个标记，这样我们就会很快的找到它，否则我们就需要一页一页的去翻，很浪费时间以及机器的性能，索引的数据结构是B tree结构，Btree记住一个口诀，存储数据的时候，小的放左边，大的放右边。

    索引分为，单值索引，唯一索引，复合索引，还有一个就是主键索引，前三种索引需要我们手动创建，主键索引，当我们设定primary key的时候，mysql就会给我们设置主键索引，主键索引也可以理解是唯一索引，但和唯一索引的唯一区别是，主键不可以为null，而唯一索引可以为null。

    当我们优化sql就需要分析sql的执行计划，但有时mysql中服务层的sql优化器，会干扰到我们的优化，这里需要注意一下，那样看执行计划呢。

• 创建几个临时表以及准备一些数据

create table course
(
    cid int(3),
    cname varchar(20),
    tid int(3)
);

create table teacher
(
    tid int(3),
    tname varchar(20),
    tcid int(3)
);

create table teacherCard
(
    tcid int(3),
    tcdesc varchar(200)
);

insert into course values(1,'java',1);
insert into course values(2,'html',1);
insert into course values(3,'sql',2);
insert into course values(4,'web',3);
insert into teacher values(1,'tz',1);
insert into teacher values(2,'tw',2);
insert into teacher values(3,'tl',3);
insert into teacherCard values(1,'tzdesc');
insert into teacherCard values(2,'twdesc');
insert into teacherCard values(3,'tldesc');

 

• 执行sql计划

mysql> explain select t.* from teacher t , course c, teacherCard tc where t.tid=c.tid and t.tcid = tc.tcid and (c.cid = 2 or tc.tcid=3);
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                                      |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------------------+
|  1 | SIMPLE      | t     | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    3 |   100.00 | NULL                                       |
|  1 | SIMPLE      | tc    | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    3 |    33.33 | Using where; Using join buffer (hash join) |
|  1 | SIMPLE      | c     | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    4 |    25.00 | Using where; Using join buffer (hash join) |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------------------+

id:编号
select_type:查询类型
table:
partitions:
type:类型
possible_keys:预测用到的索引
key:实际使用的索引
key_len:实际使用的索引列的长度，例如varchar(8)，实际长度就是8
ref:表之间的引用
rows:通过索引查询到的数据量
filtered:
Extra:额外的信息

    简单说明一下

• id都是1，从上向下执行，数据量少的表优先查询，id越大越优先查询
• select_type

        primary包含子查询sql中的 主查询（最外层）

        subquery包含子查询中的子查询（非最外层）

        simple简单查询（不包含子查询，union等）

        derived衍生查询（查询中使用到了临时表），在from子查询，子查询中并且只有一张表，的结果，表1 union 表2 表1就是衍生查询

mysql> explain select cr.name from (select cname name from course union select cname  name from course) cr
    -> ;
+----+--------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+
| id | select_type  | table      | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra           |
+----+--------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+
|  1 | PRIMARY      | <derived2> | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    8 |   100.00 | NULL            |
|  2 | DERIVED      | course     | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    4 |   100.00 | NULL            |
|  3 | UNION        | course     | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    4 |   100.00 | NULL            |
| NULL | UNION RESULT | <union2,3> | NULL       | ALL  | NULL          | NULL | NULL    | NULL | NULL |     NULL | Using temporary |
+----+--------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+
4 rows in set, 1 warning (0.02 sec)

 

• type是索引类型

        system>const>eq_ref>ref>range>index>all

            system和const和eq_ref是理想状态，system只有一条数据的系统表，const用主键或者唯一索引查询

            eq_ref是唯一性索引查询，返回结果集中都是唯一数据

            ref是非唯一性索引查询，返回结果集中非唯一数据

            range检查指定范围的行，where后面是一个范围查询(between and, in , >, >=, <, <=等)，这里需要注意的是in有时会失效，变成无索引查询

            index查询全部索引中的数据，查询的这段就是索引字段

            all查询全部表中的数据

• possible_keys可能用到的索引
• key实际用到的索引
• key_len索引的长度，用于判断复合索引是否被完全使用
• ref当前表所参照的字段，有const，也有参照的哪个字段
• rows被索引优化查询的个数
• Extra

        using filesort 多了一次额外的查询或排序，举个例子排序和查找不是同一个字段的话，在一个是复合索引跨列的时候，例如有a1，a2，a3三个复合索引，当where条件是a1，排序是a3的时候也会出现using filesort，多出现在order by

        using temporary 也是需要性能优化的，用到了临时表，多出现在group by，举个栗子，where条件中a1，但是groupby事使用a2，这样mysql会创建一个临时表放a2的数据，然后从临时表中筛选出a2的数据

        using index索引覆盖，说明不读取原表数据，只从索引表中（B tree）查询，如果有where会出现在key中，如果没有where会出现在key和possible_keys中

        using where是数据回到原表当中查询，where条件中没有设定index的项目

        impossible where是where子句中永远为false，举个例子，where条件中同一个字段又是a，又是b的查询

        这里where条件的顺序要和复合索引的顺序要一致

    简单的入门说明，平时还需要小伙伴们多动手，多调试。