MySQL之SQL优化详解（二）
- MySQL之SQL优化详解（二）
- 1. SQL的执行顺序
- 2. 七种join
- 3. 索引
- 4. 性能分析Explain
- （1）id：select查询的序列号,包含一组数字，表示查询中执行select子句或操作表的顺序
- （2） select_type：查询的类型，主要是用于区分 普通查询、联合查询、子查询等的复杂查询
- （3） table：显示这一行的数据是关于哪张表的
- （4） type：访问类型排列，显示查询使用了何种类型
- （5） possible_key：显示可能应用在这张表的索引，一个或多个。(但不一定被实际应用)
- （6） key：实际使用的索引，如果为null，则没有使用索引。
- （7） key_len：表示索引中使用的字节数
- （8） ref：显示索引的哪一列被使用，如果可能的话，是一个常数，哪些列或常量被用于查找索引列上的值
- （9）rows：根据表统计信息及索引选用情况，大致估算出找到所需的记录所需要读取的行数
- （10）extra：包含不适合在其他列中显示但十分重要的额外信息
MySQL之SQL优化详解（二）
1. SQL的执行顺序
1.1 手写顺序
1.2 机读顺序
2. 七种join
3. 索引
3.1 索引初探
-
是什么： 排好序的快速查找数据结构
-
两个主要的索引结构： B+tree 索引和哈希索引。
-
如何建： 1. ALTER TABLE table_name ADD INDEX index_name (column_list); 2. CREATE INDEX index_name ON table_name (column_list);
优点：类似大学图书馆建书目索引，提高了检索效率，降低了数据库IO，同时还可以通过索引进行排序，降低数据排序的成本，降低了CPU的消耗
缺点： 虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行 insert、update和delete，因为更新表时不仅要保存数据，还要保存一下索引文件每次更新添加了索引列的字段。
3.2 索引分类
- 主键索引：主键是一种唯一性索引，但它必须指定为
PRIMARY KEY，每个表只能有一个主键
ALTER TABLE table_name ADD PRIMARY KEY (column_list)
- 唯一索引：索引列的所有值都只能出现一次，即必须 唯一，值可以为 空
ALTER TABLE table_name ADD UNIQUE (column_list)
- 普通索引：基本的索引类型，值可以为空，没有唯一性的限制
ALTER TABLE table_name ADD INDEX index_name (column_list);
- 全文索引： 全文索引的索引类型为 FULLTEXT，全文索引只能创建在CHAR、VARCHAR、TEXT类型的字段上。查询数据量较大的字符串类型字段时，使用全文索引可以提高查询速度
ALTER TABLE table_name ADD FULLTEXT INDEX index_name(column_list);
3.3 建与不建
对于MySQL的索引创建，我们经常有疑虑，那么什么时候该建什么时候不该建呢？
哪些情况需要创建索引
-
主键自动创建唯一索引
-
频繁作为查询条件的字段应该创建索引
-
查询中与其它表关联的字段，外键关系建立索引
-
查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度
-
查询中统计或者分组字段
哪些情况不需要建索引
- 频繁更新的字段不适合创建索引
- where 条件用不到的字段不适合创建索引
- 注意，如果某个数据列包含许多重复的内容，为它建立索引就没有太大的实际效果
4. 性能分析Explain
Explain 简称执行计划，使用 Explain 关键字可以模拟优化器执行SQL查询语句
用法：explain + SQL
（1）id：select查询的序列号,包含一组数字，表示查询中执行select子句或操作表的顺序
①、id 相同执行顺序由上至下
②、id 不同，如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行
③、id相同不相同
注：id如果相同，可以认为是一组，从上往下顺序执行；在所有组中，id值越大，优先级越高，越先执行
（2） select_type：查询的类型，主要是用于区分 普通查询、联合查询、子查询等的复杂查询
- simple：简单的
select查询,查询中不包含子查询或者union - primary：查询中若包含任何复杂的子部分，最外层查询则被标记为
- subquery：在
select或where列表中包含了子查询 - derived：在
from列表中包含的子查询被标记为 derived(衍生) - union：若第二个
select出现在之后，则被标记为union（若union包含from子句的子查询中,外层select将被标记为：derived） - union result：从
union表获取结果的select
（3） table：显示这一行的数据是关于哪张表的
（4） type：访问类型排列，显示查询使用了何种类型
从好到坏，system > const > eq_ref > ref > range > index > all
- system：表只有一行记录（等于系统表），这是const 类型的特列，平时不会出现，这个也可以忽略不计
- const：表示通过索引一次就找到了，const 用于比较 primary key或者unique索引
- eq_ref：唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描
- ref：非唯一性索引扫描，返回匹配某个单独值的所有行，本质上也是一种索引访问
- range：只检索给定范围的行,使用一个索引来选择行。key 列显示使用了哪个索引，一般就是在你的
where语句中出现了 between、<、>、in等的查询 - index：Full Index Scan，index与 all 区别为 index 类型只遍历索引树
- all：Full Table Scan，将遍历全表以找到匹配的行
（5） possible_key：显示可能应用在这张表的索引，一个或多个。(但不一定被实际应用)
（6） key：实际使用的索引，如果为null，则没有使用索引。
查询中若使用了覆盖索引，则该索引与查询的select字段重叠
（7） key_len：表示索引中使用的字节数
（8） ref：显示索引的哪一列被使用，如果可能的话，是一个常数，哪些列或常量被用于查找索引列上的值
注：由key_len可知t1表的idx_col1_col2被充分使用，col1匹配t2表的col1，col2匹配了一个常量，即 'ac'
（9）rows：根据表统计信息及索引选用情况，大致估算出找到所需的记录所需要读取的行数
（10）extra：包含不适合在其他列中显示但十分重要的额外信息
- Using filesort (劣)： mysql 会对数据使用一个外部的索引排序（文件排序），而不是照表内的索引顺序进行读取
- Using temporary (劣)：使了用临时表保存中间结果,MySQL在对查询结果排序时使用临时表。常见于排序
order by和分组查询group by
- Using index (优)：表示相应的
select操作中使用了覆盖索引(Covering Index)，避免访问了表的数据行，效率不错！ - Using where：表明使用了
where过滤 - Using join buffer：表明使用了连接缓存
- impossible where：
where子句的值总是false，不能用来获取任何数据
- select tables optimized away：
select操作已经优化到不能再优化了（MySQL根本没有遍历表或索引就返回数据了 - distinct：在select部分使用了
distinc关键字
心法： 针对explain命令生成的执行计划，这里有一个查看心法。我们可以先从查询类型type列开始查看，如果出现all关键字，后面的内容就都可以不用看了，代表全表扫描。再看key列，看是否使用了索引,null代表没有使用索引。然后看rows列，该列用来表示在SQL执行过程中被扫描的行数，该数值越大，意味着需要扫描的行数越多，相应的耗时越长，最后看Extra列，在这列中要观察是否有Using filesort 或者Using temporary 这样的关键字出现，这些是很影响数据库性能的。
