MySQL数据库优化学习笔记---------explain字段解释以及如何优化

一.环境配置

这里有三个表，course表，teacher表和teacherCard表

mysql> select * from course;
+------+-------+------+
| cid  | cname | tid  |
+------+-------+------+
|    1 | java  |    1 |
|    2 | html  |    1 |
|    3 | sql   |    2 |
|    4 | web   |    3 |
+------+-------+------+
4 rows in set (0.00 sec)

mysql> select * from teacher;
+------+-------+------+
| tid  | tname | tcid |
+------+-------+------+
|    1 | tz    |    1 |
|    2 | tw    |    2 |
|    3 | tl    |    3 |
+------+-------+------+
3 rows in set (0.00 sec)

mysql> select * from teacherCard;
+------+--------+
| tcid | tcdesc |
+------+--------+
|    1 | tzdesc |
|    2 | twdesc |
|    3 | tldesc |
+------+--------+
3 rows in set (0.00 sec)

现在问题是：查询课程编号为2 或 教师证编号为3的老师信息

二.编写SQL语句

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)

mysql> 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);
+------+-------+------+
| tid  | tname | tcid |
+------+-------+------+
|    1 | tz    |    1 |
|    3 | tl    |    3 |
+------+-------+------+
2 rows in set (0.00 sec)

成功查询到结果，下面用explain查看

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 | type | possible_keys | key  | key_len | ref  | rows | Extra                          |
+----+-------------+-------+------+---------------+------+---------+------+------+--------------------------------+
|  1 | SIMPLE      | t     | ALL  | NULL          | NULL | NULL    | NULL |    3 |                                |
|  1 | SIMPLE      | tc    | ALL  | NULL          | NULL | NULL    | NULL |    3 | Using where; Using join buffer |
|  1 | SIMPLE      | c     | ALL  | NULL          | NULL | NULL    | NULL |    4 | Using where; Using join buffer |
+----+-------------+-------+------+---------------+------+---------+------+------+--------------------------------+
3 rows in set (0.00 sec)

• id:id值相同，从上往下顺序执行；若id值不同，则大的先查询
• table:先执行t 再执行tc 最后执行c 这个顺序不是固定的，改变的依据是笛卡尔积：第一个表三行数据，第二个表三行数据，第三个表四行数据，那么笛卡尔积总和为3X3X4=36是不变的，但是如果顺序的改变能让中间的结果发生改变，比如3 X 3 = 9 9X4 = 36 3X4=12 12X3 = 36 分别是334 和 343 这时中间一个是12 一个是9 ，就会选择相对来说中间结果小的那个顺序。

• select_type 查询类型:
• PRIMARY:包含子查询SQL中的 主查询 （最外层） SUBQUERY：包含子查询SQL中的 子查询 （非最外层）simple:简单查询（不包含子查询、union） derived:衍生查询(使用到了临时表)
• type:索引类型、类型:
  • system（忽略）: 只有一条数据的系统表 ；或 衍生表只有一条数据的主查询
  • const:仅仅能查到一条数据的SQL ,用于Primary key 或unique索引 （类型 与索引类型有关）
  • eq_ref:唯一性索引：对于每个索引键的查询，返回匹配唯一行数据（有且只有1个，不能多 、不能0）
  • ref：非唯一性索引，对于每个索引键的查询，返回匹配的所有行（0，多）这种情况是我们优化最为常见的
  • range：检索指定范围的行 ,where后面是一个范围查询(between ,> < >=, 特殊:in有时候会失效 ，从而转为 无索引all)
  • index：查询全部索引中数据 即查询的结果是索引列的全部数据
  • all：查询全部表中的数据
• possible_keys ：可能用到的索引，是一种预测，不准。
• key ：实际使用到的索引
• key_len ：索引的长度 注意其计算，比如索引是char(20) 编码为utf-8格式 此格式一个字符占三个字节 所以长度为60 假如能为null 那么就是61 ；如果是varchar可变长度，那么最终结果是63
• ref : 注意与type中的ref值区分。作用： 指明当前表所 参照的 字段。
• rows: 被索引优化查询的 数据个数 (实际通过索引而查询到的 数据个数)
• Extra：
  • (i).using filesort ： 性能消耗大；需要“额外”的一次排序（查询） 。常见于 order by 语句中。对于单索引， 如果排序和查找是同一个字段，则不会出现using filesort；如果排序和查找不是同一个字段，则会出现using filesort；避免： where哪些字段，就order by那些字段
  • (ii). using temporary:性能损耗大 ，用到了临时表。一般出现在group by 语句中。查询那些列，就根据那些列 group by ，这样就可以避免
  • (iii). using index :性能提升; 索引覆盖（覆盖索引）。原因：不读取原文件，只从索引文件中获取数据 （不需要回表查询）只要使用到的列 全部都在索引中，就是索引覆盖using index
  • (iii).using where （需要回表查询）
    假设age是索引列
    查询语句select age,name from …where age =…,此语句中必须回原表查Name （name不是索引列） ，因此会显示using where