mysql优化与sql分析

mysql优化从几个方面去分析

1、表设计

1.1 表字段尽量不要使用null，可以用0或者其他字符代替，避免where语句查询时全表查询（字段a = null 会进行全表查询）。

注意：

a、null与''空字符串是不同概念，null占用内存空间，而''不占用内存空间。

b、使用DISTINCT、GROUP BY或ORDER BY时，所有NULL值将被视为等同的。

c、使用ORDER BY时，首先将显示NULL值，如果指定了DESC按降序排列，NULL值将最后显示。

d、对于聚合（累计）函数，如COUNT()、MIN()和SUM()，将忽略NULL值。对此的例外是COUNT(*)，它将计数行而不是单独的列值。例如，下述语句产生两个计数。首先计数表中的行数，其次计数age列中的非NULL值数目：mysql> SELECT COUNT(*), COUNT(age) FROM person;   

e、对于某些列类型，MySQL将对NULL值进行特殊处理。如果将NULL插入TIMESTAMP列，将插入当前日期和时间。如果将NULL插入具有AUTO_INCREMENT属性的整数列，将插入序列中的下一个编号。 

2、索引

合理运用索引可以大大提高sql效率

注意

1. LIKE关键字匹配'%'开头的字符串,不会使用索引.
2. OR关键字的两个字段必须都是用了索引,该查询才会使用索引.
3. 使用多列索引（组合索引）必须满足最左匹配.

2.1 唯一索引

2.2 普通索引

2.3 组合索引

3、sql语句

3.1 使用exists()代替in()

3.2 使用between代替< >区间运算

4、explain分析语句

 

 

4.1 id

id为编号为select的序列号，有几个select就有几个id，且id的序号是按照select语句出现的顺序自增。

4.2 select_type

select_type表示语句是简单查询还是复杂查询，如果是复杂查询又分为三类：简单子查询、派生表（from语句中的子查询）、union 查询。

a、simple：简单查询，查询不包含子查询和union

b、复杂查询中最外层的select

c、subquery：包含在select中的子查询（不在from子句中）

d、derived：包含from子句中的子查询，mysql会将结果存放在一个临时表中，也称为派生表

用这个例子来了解 primary、subquery 和 derived 类型

 

e、union：在union中的第二个和随后的select

f、union resule：从union临时表检索结果的select

用这个例子来了解 union 和 union result 类型：

 

4.3 table列

这一列表示explain的一行正在访问哪个表。

当from子句中有子查询时，table列是<derivenN>格式，表示当前查询依赖 id=N 的查询，于是先执行 id=N 的查询。当有 union 时，UNION RESULT 的 table 列的值为 <union1,2>，1和2表示参与 union 的 select 行id。

4.4 type

这一列表示关联类型或访问类型，即mysql决定如何查找表中的行。

依次从最优到最差分别是：system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

NULL：mysql能够在优化阶段分解查询语句，在执行阶段用不着再访问表或索引。例如：在索引列中选取最小值，可以单独查找索引来完成，不需要在执行时访问表

 

• system

该表只有一行（=系统表）。这是const联接类型的特例 。

• const

该表最多具有一个匹配行，该行在查询开始时读取。因为只有一行，所以优化器的其余部分可以将这一行中列的值视为常量。 const表非常快，因为它们只能读取一次。

• eq_ref

对于先前表中的每行组合，从此表中读取一行。除了 system和 const类型，这是最好的联接类型。当连接使用索引的所有部分并且索引为a PRIMARY KEY或UNIQUE NOT NULLindex时使用。

eq_ref可以用于使用=运算符进行比较的索引列 。比较值可以是常量，也可以是使用在此表之前读取的表中列的表达式。

• ref

对于先前表中的每个行组合，将从该表中读取具有匹配索引值的所有行。ref如果联接仅使用键的最左前缀，或者如果键不是a PRIMARY KEY或 UNIQUE索引（换句话说，如果联接无法根据键值选择单个行），则使用。如果使用的键仅匹配几行，则这是一种很好的联接类型。

ref可以用于使用=或<=> 运算符进行比较的索引列 。

• fulltext

使用FULLTEXT 索引执行联接。

• ref_or_null

这种连接类型类似于 ref，但是除了MySQL会额外搜索包含NULL值的行。此联接类型优化最常用于解析子查询。在以下示例中，MySQL可以使用 ref_or_null联接进行处理ref_table：

SELECT * FROM ref_table WHERE key_column=expr OR key_column IS NULL;

• range

使用索引选择行，仅检索给定范围内的行。的key 输出行中的列指示使用哪个索引。将key_len包含已使用的时间最长的关键部分。该ref列 NULL适用于此类型。

range当一个键柱使用任何的相比于恒定可使用 =， <>， >， >=， <， <=， IS NULL， <=>， BETWEEN， LIKE，或 IN()运营商：

• index

该index联接类型是一样的 ALL，只是索引树被扫描。这发生两种方式：

• • 如果索引是查询的覆盖索引，并且可用于满足表中所需的所有数据，则仅扫描索引树。在这种情况下，Extra列显示为 Using index。仅索引扫描通常比索引扫描更快， ALL因为索引的大小通常小于表数据。
  • 使用对索引的读取执行全表扫描，以按索引顺序查找数据行。 Uses index没有出现在 Extra列中。

当查询仅使用属于单个索引一部分的列时，MySQL可以使用此联接类型。

• ALL

对来自先前表的行的每个组合进行全表扫描。如果该表是未标记的第一个表 const，则通常不好，并且在所有其他情况下通常 非常糟糕。通常，可以ALL通过添加索引来避免这种情况，这些 索引允许基于早期表中的常量值或列值从表中检索行。

 

4.5 possible_key

这一列显示查询可能使用哪些索引来查找

4.6 key

这一列显示实际采用哪个索引来优化对该表查询

4.7 key_len

这一列显示了mysql在索引里使用的字节数，通过这个值可以算出具体使用了索引中的哪些列。

举例来说，film_actor的联合索引 idx_film_actor_id 由 film_id 和 actor_id 两个int列组成，并且每个int是4字节。通过结果中的key_len=4可推断出查询使用了第一个列：film_id列来执行索引查找。

4.8 ref列

这一列是mysql估计要读取并检测的行数，注意这个不是结果集里的行数。

4.9 extra列

这一列展示的是额外信息。常见的重要值如下： 

distinct: 一旦mysql找到了与行相联合匹配的行，就不再搜索了