mysql checking permissions 优化_MySQL查询优化

[TOC]

# 请简述项目中优化MySQL语句执行效率的方法，从哪些方面入手，SQL语句性能如何分析？

# 分析查询速度

+ 记录慢查询日志

分析查询日志，使用`pt-query-digest`工具进行分析

+ 使用`show profile`

set profiling=1; # 服务器上执行的所有语句会检测消耗的时间、存到临时表中

`show profiles # 所有的SQL语句执行记录`

`show profile for query 临时表ID # 查执行ID的SQL语句`

~~~

mysql> set profiling=1;

Query OK, 0 rows affected

mysql> select * from user;

+----+------+-----+

| id | name | age |

+----+------+-----+

| 1 | Jack | 23 |

+----+------+-----+

1 row in set

mysql> show profiles;

+----------+------------+---------------------------------------------------------------------------------------------------------------------------------+

| Query_ID | Duration | Query |

+----------+------------+---------------------------------------------------------------------------------------------------------------------------------+

| 1 | 8.675E-5 | set profiling=1 |

| 2| 0.000197 | select * from user |

+----------+------------+---------------------------------------------------------------------------------------------------------------------------------+

10 rows in set

mysql> show profile for query 2;

+----------------------+----------+

| Status | Duration |

+----------------------+----------+

| starting | 4.3E-5 |

| checking permissions | 7E-6 |

| Opening tables | 1.8E-5 |

| System lock | 1E-5 |

| init | 1.4E-5 |

| optimizing | 4E-6 |

| statistics | 3.1E-5 |

| preparing | 1E-5 |

| executing | 2E-6 |

| Sending data | 1E-5 |

| end | 3E-6 |

| query end | 2E-6 |

| closing tables | 5E-6 |

| freeing items | 3.5E-5 |

| logging slow query | 3E-6 |

| cleaning up | 2E-6 |

+----------------------+----------+

16 rows in set

~~~

+ show status

`show status`会返回一些计数器，`show global status`查看服务器级别的所有计数。

+ show processlist

观察是否有大量的线程处于不正常的状态或特征。

+ explain

分析单条的SQL语句

~~~

mysql> explain select * from user;

+----+-------------+-------+--------+---------------+------+---------+------+------+-------+

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

+----+-------------+-------+--------+---------------+------+---------+------+------+-------+

| 1 | SIMPLE | user | system | NULL | NULL | NULL | NULL | 1 | |

+----+-------------+-------+--------+---------------+------+---------+------+------+-------+

1 row in set

~~~

> explain它有一个别名`desc`，所以使用`desc select * from user;`查询到的结果与上面的一样。

# 优化查询过程中的数据访问

+ 数据太多会导致查询性能下降。

+ 确定应用程序是否在检索大量超过需要的数据，可能太多行或列。

+ 确认MySQL服务器是否在分析大量不必要的数据行

**避免使用以下SQL语句**

+ 查询不需要的记录，使用`limit`解决

+ 多表管理返回全部列，需要制定列，如A.id、B.name等

+ 总是取出全部列，`select * `会让优化器无法完成索引覆盖扫描优化

+ 重复查询相同的数据，可以使用缓存，下次直接读取缓存

**是否在扫描额外的记录**

使用`explain`来进行分析，如果发现查询需要扫描大量的数据但是只返回少数的行，可以通过如下技巧去优化：

+ 使用索引覆盖扫描，把所有的列都放在索引中。

+ 改变数据库和表的结构，修改数据表范式

+ 重写SQL语句，让优化器可以以最优的方式执行查询

# 优化长难的查询语句

使用尽可能小的查询是好的，但有时将一个大的查询分解成多个小的查询是很有必要的。

+ **切分查询**

将一个大的查询分为多个小的相同的查询

一次性删除1000万的数据比一次删除1万，暂停一会儿在执行删除1万条数据要较少更多的服务器开销。

+ **分解关联查询**

可以将一条关联语句分解成多个SQL语句执行

让缓存的效率更高

执行单个查询可以减少锁的竞争

在应用层做关联可以更容易对数据库进行拆分

# 优化特定类型的查询语句

## 优化`count()`查询

+ `count(*)`占用的`*`会忽略所有的列，直接统计所有的列数，因此不要使用`count(列名)`

+ MyISAM中`没有任何where条件的count(* )`查询速度非常快

+ `有where条件`时，MyISAM的count统计不一定比其他表引擎快

优化：

+ 可以使用`explain`查询近似值，用近似值代替`count(*)`

+ 增加汇总表

+ 使用缓存

## 优化关联查询

+ 确定on或者using子句的列上有索引；

+ 确保`group by`和`order by`中只有一个表中的列，这样MySQL才有可能使用索引；

## 优化子查询

尽量使用关联查询替代

## 优化`group by`和`distinct`

+ 使用**索引**来优化

+ 关联查询中，使用**标识列**(主键列)进行分组的效率会更高

+ 如果不需要`order by`，进行`group by`时使用`order by NULL`，MySQL不会进行文件排序

+ `with rollup`超级剧和，可以挪到应用程序处理

## 优化`limit`子句

`limit`偏移量大的时候，查询效率较低。

方案：

+ 记录上一次查询的最大ID，下次查询时直接根据ID来查询(还是使用limit查询，不过加一个where条件，id > ID)

## 优化`union`

`union all` 的效率高于`union`。