sql 插入blob_MySQL系列——SQL优化（三）

1、explain查询执行计划

举个例子，最基础的主键查询

EXPLAIN SELECT*FROM  `subject` WHERE  id = 1

执行结果如下： 

再举个关联查询的例子

EXPLAIN SELECTa.*FROM  `subject` a  LEFT JOIN subject_role_0 b ON a.id = b.subject_id WHERE  a.id < 3

执行结果如下：

以上执行计划里面的每个参数具体是什么含义呢，请看下面的表格：

属性	说明
id	查询的序列号
select_type	查询的类型
table	输出结果集的表
rows	扫描的行数
type	连接类型，all表示采用全表扫描的方式。
possible_keys	可能使用的索引
key	实际使用的索引
key_len	索引字段的长度
ref	列与索引的比较
Extra	额外信息，比如使用了where语句，使用了join buffer等

下面是对每个属性作详细的阐述。

id

id是sql执行顺序的标识，按id从大到小的顺序执行，在id相同时，执行顺序是由上至下

select_type

select 查询的类型，主要是用于区别普通查询，联合查询，嵌套的复杂查询

类型	说明
simple	简单的select 查询，查询中不包含子查询或者union
primary	查询中若包含任何复杂的子查询，最外层查询则被标记为primary
subquery	在select或where 列表中包含了子查询
derived	在from列表中包含的子查询被标记为derived(衍生)MySQL会递归执行这些子查询，把结果放在临时表里。
union	若第二个select出现在union之后，则被标记为union，若union包含在from子句的子查询中，外层select将被标记为：derived
union result	从union表获取结果的select

table

查询的数据库的表的名称，如果没有给表指定别名，那么table值为表的名称；否则table值为你指定的别名

type

表示MySQL在表中找到所需行的方式，这是一个非常重要的参数，常见的有：all , index , range , ref , eq_ref , const , system , null 八个级别。

常用的类型有：all、index、range、 ref、eq_ref、const、system、null(从左到右，性能从差到好)

类型	说明
all	全表扫描找到匹配的行，性能最差
index	全索引扫描，从索引树找数据，比all性能好
range	只扫描指定范围的行，使用索引来匹配行，常见使用between，in，>,
ref	非唯一性索引扫描，本质上也是一种索引访问，返回所有匹配某个单独值的行。
eq_ref	唯一性索引扫描，对于每个索引键，表中有一条记录与之匹配。
const	表示通过索引一次就可以找到，const用于比较primary key 或者unique索引。因为只匹配一行数据，所以很快
system	表只有一条记录(等于系统表)，这是const类型的特列，平时不会出现
null	MySQL在优化过程中分解语句，执行时甚至不用访问表或索引，例如从一个索引列里选取最小值可以通过单独索引查找完成。

possible_keys

指出MySQL能使用哪个索引在表中找到记录，查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询使用(该查询可以利用的索引，如果没有任何索引显示 null)

key

key列显示MySQL实际决定使用的索引，必然包含在possible_keys中

key_len

显示索引中使用的字节数，可通过key_len计算查询中使用的索引长度。在不损失精确性的情况下索引长度越短越好。key_len 显示的值为索引字段的最可能长度，并非实际使用长度，即key_len是根据表定义计算而得，并不是通过表内检索出的。

ref

显示索引的哪一列或常量被用于查找索引列上的值。

rows

很重要的一个参数，根据表统计信息及索引选用情况，大致估算出找到所需的记录所需要读取的行数，值越大说明扫描的行数越多，性能越差

Extra

该列包含MySQL解决查询的详细信息,有以下几种情况：

值	说明
Using where	不用读取表中所有信息，仅通过索引就可以获取所需数据
Using temporary	表示需要使用临时表来存储结果集，常见于排序和分组查询，如group by ，order by
Using filesort	当查询中包含 order by 操作，且无法利用索引完成的排序操作称为“文件排序”
Using join buffer	在获取连接条件时没有使用索引，并且需要连接缓冲区来存储中间结果。如果出现了这个值，那应该注意，根据查询的具体情况可能需要添加索引来改进能
Impossible where	强调了where语句会导致没有符合条件的行
Select tables optimized away	这个值意味着仅通过使用索引，优化器可能仅从聚合函数结果中返回一行
No tables used	Query语句中使用from dual 或不含任何from子句

2、如何优化？

2.1 优化数据大小

以最小占用磁盘空间来设计表，这样可以减少磁盘写入和读取来实现性能的提升。较小的表通常需要较少的主内存，同时索引也比较小，便于更快的处理。

通过使用此处列出的技术，您可以获得更好的表性能并最大限度地减少存储空间：

表列

• 尽可能使用最有效(最小)的数据类型。MySQL有许多专门的类型可以节省磁盘空间和内存。例如，如果可能，请使用较小的整数类型。mediumint通常是一个更好的选择，它比int使用的列空间减少25%。

• 尽量使用NOT NULL列，它通过更好地使用索引并避免测试每个值是否为NULL来获取更快的速度。

索引

• 表的主索引应尽可能短。这使得每行的识别变得简单而有效。

• 仅创建提高查询性能所需的索引。索引适用于检索，但会降低插入和更新操作的速度。如果您主要通过搜索列的组合来访问表，请在它们上创建单个复合索引，而不是为每列创建单独的索引。索引的第一部分应该是最常用的列。如果从表中选择时总是使用多列，则索引中的第一列应该是具有最多重复的列，以获得更好的索引压缩。

• 如果长字符串列很可能在第一个字符数上有唯一的前缀，那么最好只使用MySQL支持在列的最左边部分创建索引来索引此前缀，较短的索引更快，不仅因为它们需要更少的磁盘空间，而且因为它们还会在索引缓存中为您提供更多的命中，从而减少磁盘搜索次数。

Join

• 在某些情况下，分成两个经常扫描的表可能是有益的，如果它是动态格式表，则尤其如此，并且可以使用较小的静态格式表，该表可用于在扫描表时查找相关行。

• 在具有相同数据类型的不同表中声明具有相同信息的列，可以加速连接。

• 保持列名简单，以便您可以在不同的表中使用相同的名称并简化连接查询。例如表customer，使用列名name而不是customer_name。

正常化

• 通常，尽量保持所有数据不冗余(第三范式)，尽量通过引用join子句中的ID来连接查询中的表。

• 如果速度比磁盘空间更重要，并且保留多个数据副本，那么可以创建汇总表到获得更快的速度。

2.2 优化数据类型

对于唯一的id，首选使用数字列，而不是字符串，这是因为数字比字符串占用更少的字节，传输和比较速度更快，占用的内存更少。

优化字符和字符串类型

对于字符和字符串列，请遵循以下准则：

• 比较来自不同列的值时，请尽可能声明具有相同字符集和排序规则的列，以避免在运行查询时进行字符串转换。

• 对于小于8KB的列值，请使用binary VARCHAR而不是 BLOB。

• 如果表包含字符串列(如名称和地址)，但许多查询不检索这些列，请考虑将字符串列拆分为单独的表，并在必要时使用带有外键的连接查询。可以减少了常见查询的磁盘I / O和内存使用。

优化BLOB类型

• 存储包含文本数据的大blob时，请考虑先压缩它。

• 对于具有多个列的表，要减少使用BLOB列的查询，请考虑将BLOB列拆分为单独的表，并在需要时使用连接查询引用它。