Mysql优化

目录

如何思考和定位到可调优的地方？

SQL执行时间长的优化

分析查询语句：EXPLAIN

1、table

2、id

3、select_type 

 4、type*

5、possible_keys

6、key

7、key_len*

8、ref

 9、rows*

10、filtered

11、Extra*

查看 SQL 执行成本：SHOW PROFILE

---

为了提高sql语句的查询效率，我们不得不进行优化。

如何思考和定位到可调优的地方？

当我们遇到数据库调优问题的时候，该如何思考从而定位到问题呢？

整个思考和行动的流程划分成了观察（Show status）和行动（Action）两个部分。字母 S 的部分代表观察（会使用相应的分析工具），字母 A 代表的部分是行动（对应分析可以采取的行动）。

分析查询语句：EXPLAIN

先通过慢查询日志，找到那些慢查询的sql语句，使用explain对sql语句进行全面解析。

在找出慢查询的sql语句后，需要继续使用mysql提供的explain命令来查询sql语句的执行计划，查看sql语句有没有使用上索引，有没有全表扫描等。从而选择更好的索引和写出更优化的查询语句。

列名	描述
id	在一个大的查询语句中包含的SELECT关键字都对应一个唯一的id标识
select_type	SELECT关键字对应的那个查询的类型
table	查询涉及到的表名
type*	针对单表的访问方法
possible_keys	可能用到的索引
key	实际上使用的索引
key_len*	实际上使用到的索引长度，如果是三个字段构成的联合索引， 实际用到两个字段，则值为2*
ref	当使用索引列等值查询时，与索引列进行等值匹配的对象信息，与索引相比的列
rows	预估需要读取的记录条数
Extra*	一些额外的信息

1、table

explain输出记录所引用的表的名称。

在一个查询中，涉及到几个表，explain的结果就会有几条记录，当然有时候记录会多于表的个数，是因为可能在查询过程中有临时表的创建。

不论我们的查询语句有多复杂，里边儿包含了多少个表，到最后也是需要对每个表进行单表访问的，所以MySQL规定EXPLAIN语句输出的每条记录都对应着某个单表的访问方法，该条记录的table列代表着该表的表名（有时不是真实的表名字，可能是简称）。

2、id

在一个复杂的查询中，一个select关键字对应一个唯一id标识。但是有时候MySQL会自动将可以重写的sql自动重写，从而减少查询的次数，就是有可能两个select关键字，但是id就一个，这就是MySQL自动重写了sql。

小结：

• id如果相同，可以认为是一组，同一个select查询，从上往下顺序执行
• 在所有组中，id值越大，优先级越高，越先执行
• 关注点：id号每个号码，表示一趟独立的查询, 一个sql的查询趟数越少越好

id为null的可能是临时表： 

 

3、select_type 

对于一个大的复杂查询语句中，可能包含多个select关键字，每个select关键字代表了一个小查询，而select小查询又可能form多个表查出数据，每个表在explain语句计划中对应一条记录，但是id都一样。

select_type代表当前查询的查询类型，根据这个就可以知道这个小查询在大查询中的身份(比如子查询)。

名称	描述
PRIMARY	最外面的select，在有子查询的语句中， 最外层的select查询的select_type就是primary
SUBQUERY	子查询中的第一个SELECT，结果不依赖于外部查询。 子查询不依赖外部的表。
DEPENDENT SUBQUERY	子查询中的第一个SELECT，依赖于外部查询。 子查询不依赖外部的表。
SIMPLE	表示简单的select，没有union联合查询和子查询

 subquery: 

dependent subquery:

 4、type*

explain执行计划的一条记录就代表着MySQL对某个表的执行查询时的访问方法，type列就说明了访问方法是什么，是sql是否优化的较为重要的一个指标。比如，看到type是ALL，那完了，说明对当前表是全表扫描，效率很低，这时候就要进行优化了。

结果值从好到坏依次是：system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

SQL 性能优化的目标：至少要达到 range 级别，要求是 ref 级别，最好是 consts级别。（阿里巴巴开发手册要求）

我们在进行条件查询时，建议使用索引，否则将引起全表扫描，IO的开销和程序的性能都没法保证！

5、possible_keys

表示查询时，可能使用的索引，一般是where后面的条件中的字段如果有用到索引就会变成possible_keys。

6、key

查询中实际用到的索引，多个索引MySQL确定的最优索引就会变成key。

7、key_len*

实际使用到的索引长度(即：字节数)

帮你检查`是否充分的利用上了索引`，`值越大越好`,主要针对于联合索引，有一定的参考意义。

key_part1、key_part2和key_part3三个字段构成一个联合索引

8、ref

当使用索引所在的字段进行等值查询时，与索引所在列的字段进行等值匹配的对象信息。比如是又给常数const或某个字段。

 9、rows*

该列指示MySQL认为必须检查才能执行查询的行数。就是当前查询估计需要扫描表的行数。

对于InnoDB表，此数字是估计值，可能并不总是准确的。

值越小越好。

10、filtered

一般配合rows一起看，值越大越好。

11、Extra*

Extra列是用来说明一些额外的信息的，可以通过额外信息了解到Mysql到底如何执行给定的查询语句。

查看 SQL 执行成本：SHOW PROFILE

1、查看profile功能是否打开 ON打开，OFF没打开

show variables like 'profiling';

2、通过设置 profiling='ON’来开启 show profile：

mysql > set profiling = 'ON';

3、show profiles;        查看最近的sql语句。查看当前会话所产生的所有 profiles

4、show profile for query sql语句的id;        查看sql执行的每个过程所花的时间。

status字段解读:  

* Sending data (最重要的一个过程★★★★★)
  线程正在读取和处理一条SELECT语句的行，并且将数据发送至客户端。由于在此期间会执行大量   的磁盘访问（读操作），
  这个状态在一个指定查询的生命周期中经常是耗时最长的。
  这个字段才是SQL真正运行采集+相应数据的时间，而非executing；
  
  --以下按照首字母顺序依次排序
  
* After create
  这个状态当线程创建一个表（包括内部临时表）时，在这个建表功能结束时出现。即使某些错误导致建表失败，也会使用这个状态。

* Analyzing
  当计算MyISAM表索引分布时。（比如进行ANALYZE TABLE时）

* checking permissions
  这个线程检查服务器是否有具有执行该语句的所需权限。

* Checking table
  线程正在执行表检查操作。

* cleaning up
  线程处理一个命令，并正准备释放内存和重置某些状态变量。

* closing tables
  线程正在将变更的表中的数据刷新到磁盘上并正在关闭使用过的表。这应该是一个快速的操作。如果不是这样的话
  则应该检查硬盘空间是否已满或者硬盘IO是否达到瓶颈。 

* converting HEAP to MyISAM
  线程将一个内部临时表转换为磁盘上的MyISAM表。

* copy to tmp table 
  线程正在处理一个ALTER TABLE语句。这个状态发生在新的表结构已经创建之后，但是在数据被复制进入之前。

* Copying to group table
  如果一个语句有不同的ORDER BY和GROUP BY条件，数据会被复制到一个临时表中并且按组排序。

* Copying to tmp table
  线程将数据写入内存中的临时表。  正在创建临时表以存放部分查询结果

* Copying to tmp table on disk
  线程正在将数据写入磁盘中的临时表。临时表的结果集过大。所以线程将临时表由基于内存模式改为基于磁盘模式，以节省内存。
  但是这个过程会异常的缓慢！！

* Creating index
  线程正在对一个MyISAM表执行ALTER TABLE ... ENABLE KEYS语句。

* Creating sort index
  线程正在使用内部临时表处理一个SELECT 操作。

* creating table
  线程正在创建一个表，包括创建临时表。

* Creating tmp table
  线程正在创建一个临时表在内存或者磁盘上。
  如果这个表创建在内存上但是之后被转换到磁盘上，这个状态在运行Copying to tmp table on disk 的时候保持。

* deleting from main table
  线程正在执行多表删除的第一部分，只从第一个表中删除。并且保存列和偏移量用来从其他（参考）表删除。

* deleting from reference tables
  线程正在执行多表删除的第二部分，并从其他表中删除匹配的行。

* discard_or_import_tablespace
  线程正在执行ALTER TABLE ... DISCARD TABLESPACE 或 ALTER TABLE ... IMPORT TABLESPACE语句。

* end
  这个状态出现在结束时，但是在对ALTER TABLE, CREATE VIEW, DELETE, INSERT, SELECT,   或者 UPDATE 语句进行清理之前。

* executing
  该线程已开始执行一条语句。

* Execution of init_command
  线程正在执行处于init_command系统变量的值中的语句。

* freeing items
* 线程已经执行了命令。在这个状态中涉及的查询缓存可以得到一些释放。这个状态通常后面跟随cleaning up状态。

* Flushing tables
  线程正在执行FLUSH TABLES 并且等待所有线程关闭他们的表。

* FULLTEXT initialization
  服务器正在准备进行自然语言全文检索。

* init
  这个状态出现在线程初始化ALTER TABLE, DELETE, INSERT, SELECT, 或 UPDATE语句之前。
  服务器在这种状态下进行的操作，包括：刷新全日志、Innodb日志，和一些查询缓存清理操作。

* Killed
  程序对线程发送了KILL语句，并且它应该放弃下一次对KILL标记的检查。
  这个标记在每一个MySQL的主要循环中被检查，但在某些情况下，它可能需要令线程在很短的时间内死亡。
  如果这个线程被其他线程锁住了，这个KILL操作会在其他线程释放锁的瞬时执行。


* logging slow query
  这个线程正在将语句写入慢查询日志。

* NULL
  没有操作的状态。

* login
  线程连接的初始状态。直到客户端已经成功验证。

* manage keys
  服务器启用或禁用表索引。

* Opening tables, Opening table
  线程正试图打开一张表

* optimizing
  服务器执行查询的初步优化。

* preparing
 在查询优化过程中出现这个状态。

* Purging old relay logs
  线程正在移除不必要的中继日志文件。

* query end
  这个状态出现在处理一个查询之后，但是在freeing items状态之前。

* Reading from net
  服务器正在从网络阅读数据包。

* Removing duplicates
  查询正在使用SELECT DISTINCT，这种情况下MySQL不能在早期阶段优化掉一些distinct操作。
  因此，MySQL需要一个额外的阶段，在将结果发送到客户端之前删除所有重复的行。

* removing tmp table
  线程正在移除一个内置临时表，在执行一条SELECT语句之后。 如果没有临时表产生，那么这个状态不被使用。

* rename
* 线程正在重命名一张表。

* rename result table
  线程正在处理ALTER TABLE语句，创建新的表，并且重命名它来代替原有的表。

* Reopen tables
  线程获得了表锁，但是在取得表锁之后才发现该表的底层结构已经发生了变化。线程释放这个锁，关闭表，并试图重新打开该表。

* Repair by sorting
  修复代码正在使用一个分类来创建索引。

* Repair done
  线程完成一个多线程的MyISAM表的修复。

* Repair with keycache
  修复代码正在通过索引缓存一个接一个地使用创建索引。这比通过分类修复要慢很多。

* Rolling back
  线程正在回滚一个事务

* Searching rows for update
  线程正在进行第一阶段，在更新前寻找所有匹配的行。如果update正在更改用于查找相关行的索引，则必须这么做。

* setup
  线程正开始进行一个ALTER TABLE操作。

* Sorting for group
  线程正在执行一个由GROUP BY指定的排序。

* Sorting for order
  线程正在执行一个由ORDER BY指定的排序。

* Sorting index
  线程正在对索引页进行排序，为了对MyISAM表进行操作时获得更优的性能。

* Sorting result
 对于一个SELECT语句，这与创建排序索引相似，但是是对非临时表。

* statistics
  服务器计算统计去规划一个查询。如果一个线程长时间处于这个状态，这个服务器的磁盘可能在执行其他工作。

* System lock
 这个线程正在请求或者等待一个内部的或外部的系统表锁。如果这个状态是由于外部锁的请求产生的，并且你没有使用多个正在访问相同的表的mysql服务器
 
* Waiting for table level lock
  系统锁定后的下一个线程状态。线程已获得外部锁并且将请求内部表锁。

* Updating
  线程寻找更新匹配的行并进行更新。

* updating main table
  线程正在执行多表更新的第一部分，只从第一个表中更新。并且保存列和偏移量用来从其他（参考）表更新。

* updating reference tables
  线程正在执行多表更新的第二部分，并从其他表中更新匹配的行。

* User lock
  线程正在请求或等待一个GET_LOCK()调用所要求的咨询锁。对于SHOW PROFILE，这个状态意味这线程正在请求锁。（而非等待）

* User sleep
  线程调用了一个SLEEP()。

* Waiting for commit lock
  一个显式或隐式语句在提交时等待释放读锁

* Waiting for global read lock
  等待全局读锁。

* Waiting for release of readlock
  等待释放读锁。

* Waiting for tables, Waiting for table, Waiting for table flush
  线程获得一个通知，底层表结构已经发生变化，它需要重新打开表来获取新的结构。然而，重新打开表，它必须等到所有其他线程关闭这个有问题的表。
  这个通知产生通常因为另一个线程对问题表执行了FLUSH TABLES或者以下语句之一：
  FLUSH TABLES tbl_name, ALTER TABLE, RENAME TABLE, REPAIR TABLE, ANALYZE TABLE, or OPTIMIZE TABLE.

* Waiting for lock_type lock
  等待各个种类的表锁。

* Waiting on cond
 一个普通的状态，线程正在等待一个条件为真。没有特定的状态信息可用。

* Writing to net
  服务器正在写一个网络数据包。