mysql数据库的优化整理之查询优化

作为程序员，天天都要和数据库打交道，而且以mysql居多，而数据库的优化，也是一个老生常谈的话题。今天我就整理一下mysql数据库的优化方式。

开始：

首先是查询优化： 

一条查询语句在数据库中执行，如果该条语句的执行时间比较长，那么就会被数据库记录为慢查询，那么我们怎么来查看我们写的哪些语句是慢查询的呢？我们可以在数据库中查看mysql的性能参数。

查看mysql性能参数的命令：SHOW STATUS 可以查看，这里有几百条参数。。。

比如一条查询sql超过我们制定的时间，会被记录为慢查询，并记录下这条sql制定查询慢查询的方式：

SHOW STATUS LIKE 'Slow_queries'

因为我这里没有符合条件的，所以没有值。你可以根据具体情况，到mysql的执行日志中找到语句并进行语句的优化。

查看C/R/U/D的执行次数(只配一图)

show status like 'Com_insert'
show status like 'Com_select'
show status like 'Com_update'
show status like 'Com_delete'

EXPLAIN 可以查看sql执行的过程，通过了解过程，我们进行相应的优化

EXPLAIN SELECT * FROM BOOK;

select_type：查询的方式

1：SIMPLE 简单的查询，不包括连接查询和子查询

2：PRIMARY 表示主查询，或者最外面的查询

3：UNION 表示连接查询，第二个或后面的查询

4：DEPENDENT UNION 表示UNION中第二个或者后面的SELECT语句，取决于外面的查询

5：UNION RESULT 表示连接查询的结果

6：SUBQUERY 表示子查询的第一个查询语句

7：ENPENDENT SUBQUERY 表示子查询的第一个查询语句，取决于外面的查询

8：DERIVED 表示SELECT(FROM 子句的子查询）

  而我们需要注意的是SUBQUERY，一旦出现这个，我们就要注意考虑对查询进行优化了！！！

table：表示查询的表

type：表示表的连接类型（重要）

从最佳类型到最差类型：

1：system 表示表仅有一行，这是const类型的特列，平时不会出现，可以忽略

2：const 表示数据表只有一个匹配行，因为只匹配一行数据，所以很快，常用于PRIMARY KEY 或者 UNIQUE 的索引查询，可以认为const是最优的

3：eq_ref mysql手册是这样说的：对于每个来自前面的表的行组合，从该表中读取一行，这可能是最好的连接类型，除了const。它用在一个索引的所有部分被连接使用并且索引时UNIQUE 或 PRIMARY KEY ，eq_ref 可以用于使用 =带索引的列。

4：ref 查询条件索引既不是UNIQUE 也不是 PRIMARY KEY 的情况，ref可以用于 =或<或>操作符的带索引的列

5：ref_or_null  该连接类型如同ref，但是添加了mysql可以专门搜索包含null值得行，在解决子查询中常使用该类型的优化。

以上5条都是可以接收的，下面的情况就需要我们考虑去优化了

6：index_merge 该连接类型表示使用了索引合并优化方法。在这种情况下，key列包含了使用的索引的清单，key_len包含了使用的索引的最长的关键元素。

7：unique_subquery 该类型替换了下面的形式的in子查询的ref: value in(select primery_key from single_table where some_expr),unique_subquery 是一个索引查询函数，可以完全替换子查询，效率更高

8：index_subquery 该连接类型类似于unique_subquery。可以替换in子查询，但是只适合下列形式的子查询中的非唯一索引：value in(select key_column from single_table where some_expr)

9：renge 只检索给定范围的行，使用一个索引来择行。

10：index 该连接类型与ALL相同，除了只有索引树被扫描。这通常比All块，因为索引文件通常比数据文件小。

11：All 对于每个来自于先前的表的行组合，进行完整的表扫描。（性能最差）

possible_keys

指出mysql能使用哪个索引在该表中找到行。

如果该列为null，说明没有使用索引，可以对该列长江索引来提高性能。

key

显示mysql实际决定使用的键（索引）。如果没有选择索引，键是null。

可以强制使用索引或者忽略索引：

key_len

显示mysql决定使用的键的长度，如果键是null，则长度为null

注意：key_len 是确定了mysql将实际使用的索引长度。

ref

显示使用哪个列域或常数与key仪器从表中选择行。

rows

显示mysql认为它执行查询时必须检查的行数。

Extra

该列包含mysql解决查询的详细信息：

Distinct:mysql 发现第一个匹配行后，停止位当前的行组合搜索更多的行

Not exists：mysql 能够对查询进行LEFT JOIN 优化，发现1个匹配LEFT JOIN 标准的行后，不在为前面的行组合在该表内检查更多的行。

range checked for each record(index map:#) ：mysql没有发现好的可以使用的索引，单发现如果来自前面的表的列值已知，可能索引可以使用。

Using filesort：mysql需要额外的一次传递，以找出如何安排顺序检索行。

using index:从只使用索引树种的信息而不需要进一步搜索读取实际的行来检索表中的列信息。

Using temporary : 为了解决查询，mysql需要创建一个临时表来容纳结果。

Using where：where子语句用于限制哪一个行匹配下一个表或发送到客户。

using sort_union(....),Using union(...),Using intersect(...)这些函数说明如何为index——merge连接类型合并索引扫描。

Using index for group-by :类似于访问表的Using index方式，using index for group-by 表示mysql发现一个索引，可以用来查询group by 或 distinct 查询的说有列，而不要额外搜索硬盘访问实际的表。

使用索引查询注意事项:

1.使用like 查询时，第一个字符是%时，索引不生效

2.使用联合索引查询，mysql可以为多个字段创建索引，一个索引可以包括16个字段，对于联合索引，只有查询条件中使用了这些字段中的第一个字段，索引才生效

3.使用or关键字的查询，查询语句中   只有or关键字，并且or的前后两个条件中的列都是索引时   ，索引才生效。

子查询优化

使用连接查询（join）代替子查询，连接查询时不需要建立临时表，速度更快。