MySQL优化三大方向

 ① 优化MySQL所在服务器内核(此优化一般由运维人员完成)。


 ② 对MySQL配置参数进行优化（my.cnf）此优化需要进行压力测试来进行参数调整。


 ③ 对SQL语句以及表优化。

MySQL参数优化

 1:MySQL 默认的最大连接数为 100，可以在 mysql 客户端使用以下命令查看
mysql> show variables like 'max_connections';
2:查看当前访问Mysql的线程
mysql> show processlist;
3:设置最大连接数
mysql>set globle max_connections = 5000;
最大可设置16384,超过没用
4:查看当前被使用的connections
mysql>show globle status like 'max_user_connections'


对MySQL语句性能优化的16条经验

 ① 为查询缓存优化查询


 ② EXPLAIN 我们的SELECT查询(可以查看执行的行数)


 ③ 当只要一行数据时使用LIMIT 1


 ④ 为搜索字段建立索引


 ⑤ 在Join表的时候使用相当类型的列，并将其索引


 ⑥ 千万不要 ORDER BY RAND  ()


 ⑦ 避免SELECT *


 ⑧ 永远为每张表设置一个ID


 ⑨ 可以使用ENUM 而不要VARCHAR


 ⑩ 尽可能的使用NOT NULL


 ⑪ 固定长度的表会更快


 ⑫ 垂直分割


 ⑬ 拆分打的DELETE或INSERT语句


 ⑭ 越小的列会越快


 ⑮ 选择正确的存储引擎


 ⑯ 小心 "永久链接"


 具体描述如下：


 (一) 使用查询缓存优化查询


 大多数的MySQL服务器都开启了查询缓存。这是提高性能最有效的方法之一，而且这是被MySQL引擎处理的。当有很多相同的查询被执行了多次的时候，这些查询结果会被放入一个缓存中，这样后续的相同查询就不用操作而直接访问缓存结果了。


 这里最主要的问题是，对于我们程序员来说，这个事情是很容易被忽略的。因为我们某些查询语句会让MySQL不使用缓存，示例如下：


 1：SELECT username FROM user WHERE    signup_date >= CURDATE()
 
2：SELECT username FROM user WHERE    signup_date >= '2014-06-24‘
 
上面两条SQL语句的差别就是 CURDATE() ，MySQL的查询缓存对这个函数不起作用。所以，像 NOW() 和 RAND() 或是其它的诸如此类的SQL函数都不会开启查询缓存，因为这些函数的返回是会不定的易变的。所以，你所需要的就是用一个变量来代替MySQL的函数，从而开启缓存。
 



 (二) 使用EXPLAIN关键字检测查询


 使用EXPLAIN关键字可以使我们知道MySQL是如何处理SQL语句的，这样可以帮助我们分析我们的查询语句或是表结构的性能瓶颈；EXPLAIN的查询结果还会告诉我们索引主键是如何被利用的，数据表是如何被被搜索或排序的....等等。语法格式是：EXPLAIN +SELECT语句;


 
 



 
 



 我们可以看到，前一个结果显示搜索了 7883 行，而后一个只是搜索了两个表的 9 和 16 行。查看rows列可以让我们找到潜在的性能问题。 
 



 (三)当只要一行数据时使用LIMIT 1


 加上LIMIT 1可以增加性能。MySQL数据库引擎会在查找到一条数据后停止搜索，而不是继续往后查询下一条符合条件的数据记录。


 (四)为搜索字段建立索引


 索引不一定就是给主键或者是唯一的字段，如果在表中，有某个字段经常用来做搜索，需要将其建立索引。


 索引的有关操作如下：


 1.创建索引


 在执行CREATE TABLE语句时可以创建索引，也可以单独用CREATE INDEX或ALTER TABLE来为表增加索引。
 



 1.1> ALTER TABLE


 ALTER TABLE 用来创建普通索引、唯一索引、主键索引和全文索引


 ALTER TABLE table_name ADD INDEX index_name (column_list);


 

  ALTER TABLE table_name ADD UNIQUE (column_list);
 
 

  

   ALTER TABLE table_name ADD PRIMARY KEY (column_list);
  
  

   

    ALTER TABLE table_name ADD FULLTEXT (column_list);
   
   

    其中table_name是要增加索引名的表名，column_list指出对哪些列列进行索引，多列时各列之间使用半角逗号隔开。索引名index_name是可选的，如果不指定索引名称，MySQL将根据第一个索引列自动指定索引名称，另外，ALTER TABLE允许在单个语句中更改多个表，因此可以在同时创建多个索引。
   
   

    1.2> CREATE INDEX
   
   

    CREATE INDEX可对表增加普通索引或UNIQUE索引以及全文索引，但是不可以对表增加主键索引
   
   

    CREATE INDEX index_name ON table_name (column_list);
   
   

    

     CREATE UNIQUE index_name ON table_name (column_list);
    
    

     CREATE FULLTEXT index_name ON table_name (column_list);
    
    

     table_name、index_name和column_list具有与ALTER TABLE语句中相同的含义，索引名必须指定。另外，不能用CREATE INDEX语句创建PRIMARY KEY索引。
    
   
  
 


 2.索引类型


 普通索引INDEX：适用于name、email等一般属性


 唯一索引UNIQUE：与普通索引类似，不同的是唯一索引要求索引字段值在表中是唯一的，这一点和主键索引类似，但是不同的是，唯一索引允许有空值。唯一索引一般适用于身份证号码、用户账号等不允许有重复的属性字段上。


 主键索引：其实就是主键，一般在建表时就指定了，不需要额外添加。


 全文检索：只适用于VARCHAR和Text类型的字段。


 注意：全文索引和普通索引是有很大区别的，如果建立的是普通索引，一般会使用like进行模糊查询，只会对查询内容前一部分有效，即只对前面不使用通配符的查询有效，如果前后都有通配符，普通索引将不会起作用。对于全文索引而言在查询时有自己独特的匹配方式，
 例如我们在对一篇文章的标题和内容进行全文索引时：


 ALTER TABLE article ADD FULLTEXT ('title', 'content'); 在进行检索时就需要使用如下的语法进行检索：


 SELECT * FROM article WHERE MATCH('title', 'content') AGAINST ('查询字符串');


 在使用全文检索时的注意事项：


 MySql自带的
 全文索引只能用于数据库引擎为MYISAM的数据表，如果是其他数据引擎，则全文索引不会生效。此外，
 MySql自带的全文索引只能对英文进行全文检索，目前无法对中文进行全文检索。如果需要对包含中文在内的文本数据进行全文检索，我们需要采用Sphinx（斯芬克斯）/Coreseek技术来处理中文。另外使用MySql自带的全文索引时，
 如果查询字符串的长度过短将无法得到期望的搜索结果。MySql全文索引所能找到的词默认最小长度为4个字符。另外，如果查询的字符串包含停止词，那么该停止词将会被忽略。
 



 3.组合索引


 组合索引又称多列索引，就是建立索引时指定多个字段属性。有点类似于字典目录，比如查询 'guo' 这个拼音的字时，首先查找g字母，然后在g的检索范围内查询第二个字母为u的列表，最后在u的范围内查找最后一个字母为o的字。比如组合索引(a,b,c)，abc都是排好序的，在任意一段a的下面b都是排好序的，任何一段b下面c都是排好序的


 组合索引的生效原则是  从前往后依次使用生效，如果中间某个索引没有使用，那么断点前面的索引部分起作用，断点后面的索引没有起作用；
 



 造成断点的原因：


 前边的任意一个索引没有参与查询，后边的全部不生效。


 前边的任意一个索引字段参与的是范围查询，后面的不会生效。


 断点跟索引字字段在SQL语句中的位置前后无关，只与是否存在有关。在网上找到了很好的示例：


 比如：


 
where a=3 and b=45 and c=5 .... #这种三个索引顺序使用中间没有断点，全部发挥作用；
where a=3 and c=5... #这种情况下b就是断点，a发挥了效果，c没有效果
where b=3 and c=4... #这种情况下a就是断点，在a后面的索引都没有发挥作用，这种写法联合索引没有发挥任何效果；
where b=45 and a=3 and c=5 .... #这个跟第一个一样，全部发挥作用，abc只要用上了就行，跟写的顺序无关
（a,b,c） 三个列上加了联合索引（是联合索引 不是在每个列上单独加索引）而是建立了a,(a,b),(a,b,c)三个索引，另外(a,b,c)多列索引和 (a,c,b)是不一样的。
 
具体实例可以说明：


 
(0) select * from mytable where a=3 and b=5 and c=4;
#abc三个索引都在where条件里面用到了，而且都发挥了作用
(1) select * from mytable where  c=4 and b=6 and a=3;
#这条语句为了说明 组合索引与在SQL中的位置先后无关，where里面的条件顺序在查询之前会被mysql自动优化，效果跟上一句一样
(2) select * from mytable where a=3 and c=7;
#a用到索引，b没有用，所以c是没有用到索引效果的
(3) select * from mytable where a=3 and b>7 and c=3;
#a用到了，b也用到了，c没有用到，这个地方b是范围值，也算断点，只不过自身用到了索引
(4) select * from mytable where b=3 and c=4;
#因为a索引没有使用，所以这里 bc都没有用上索引效果
(5) select * from mytable where a>4 and b=7 and c=9;
#a用到了  b没有使用，c没有使用
(6) select * from mytable where a=3 order by b;
#a用到了索引，b在结果排序中也用到了索引的效果，前面说了，a下面任意一段的b是排好序的
(7) select * from mytable where a=3 order by c;
#a用到了索引，但是这个地方c没有发挥排序效果，因为中间断点了，使用 explain 可以看到 filesort
(8) select * from mytable where b=3 order by a;
#b没有用到索引，排序中a也没有发挥索引效果
 
 

  注意：在查询时，MYSQL只能使用一个索引，如果建立的是多个单列的普通索引，在查询时会根据查询的索引字段，从中选择一个限制最严格的单例索引进行查询。别的索引都不会生效。
 
4.
 查看索引


 mysql> show index from tblname;
mysql> show keys from tblname;



 5.删除索引


 删除索引的mysql格式 :DORP INDEX IndexName ON tab_name；
 



 注意：不能使用索引的情况 


 对于普通索引而言 在使用like进行通配符模糊查询时,如果首尾之间都使用了通配符，索引时无效的。


 假设查询内容的关键词为'abc'


 SELECT * FROM tab_name WHERE index_column LIKE  'abc%';  #索引是有效的


 
 

  SELECT * FROM tab_name WHERE index_column LIKE  '%abc';  #索引是无效的
 
 

  

   SELECT * FROM tab_name WHERE index_column LIKE  '%cba';  #索引是有效的
  
 


 

  SELECT * FROM tab_name WHERE index_column LIKE  '%abc%';  #索引是无效的
 
 

  当检索的字段内容比较大而且检索内容前后部分都不确定的情况下，可以改为全文索引，并使用特定的检索方式。
 
 

  (五)在join表的时候使用相当类型的列，并将其索引
 
如果在程序中有很多JOIN查询，应该保证两个表中join的字段时被建立过索引的。这样MySQL颞部会启动优化JOIN的SQL语句的机制。
 注意：这些被用来JOIN的字段，应该是相同类型的。
 例如：如果要把 DECIMAL 字段和一个 INT 字段Join在一起，MySQL就无法使用它们的索引。对于那些STRING类型，还需要有相同的字符集才行。（两个表的字符集有可能不一样）  


 例如：


 SELECT company_name FROM users LEFT JOIN companies ON (users.state = companies.state) WHERE users.id = “user_id”
两个 state 字段应该是被建过索引的，而且应该是相当的类型，相同的字符集。



 (六)切记不要使用ORDER BY RAND()


 如果你真的想把返回的数据行打乱了，你有N种方法可以达到这个目的。这样使用只让你的数据库的性能呈指数级的下降。这里的问题是：MySQL会不得不去执行RAND()函数（很耗CPU时间），而且这是为了每一行记录去记行，然后再对其排序。就算是你用了Limit 1也无济于事（因为要排序） 



 (七)避免使用SELECT *


 从数据库里读出越多的数据，那么查询就会变得越慢。并且，如果我们的数据库服务器和WEB服务器是两台独立的服务器的话，这还会增加网络传输的负载。 所以，我们应该养成一个需要什么就取什么的好的习惯。
Hibernate性能方面就会差，它不用*，但它将整个表的所有字段全查出来 
优点：开发速度快



 (八)永远为每张表设置一个ID主键


 我们应该为数据库里的每张表都设置一个ID做为其主键，而且最好的是一个INT型的（推荐使用UNSIGNED），并设置上自动增加的 AUTO_INCREMENT标志。 就算是我们 users 表有一个主键叫 “email”的字段，我们也别让它成为主键。使用 VARCHAR 类型来当主键会使用得性能下降。另外，在我们的程序中，我们应该使用表的ID来构造我们的数据结构。 而且，在MySQL数据引擎下，还有一些操作需要使用主键，在这些情况下，主键的性能和设置变得非常重要，比如，集群，分区…… 在这里，只有一个情况是例外，那就是“关联表”的“外键”，也就是说，这个表的主键，通过若干个别的表的主键构成。我们把这个情况叫做“外键”。比如：有一个“学生表”有学生的ID，有一个“课程表”有课程ID，那么，“成绩表”就是“关联表”了，其关联了学生表和课程表，在成绩表中，学生ID和课程ID叫“外键”其共同组成主键。 



 (九)使用ENUM而不是VARCHAR


 ENUM 类型是非常快和紧凑的。在实际上，其保存的是 TINYINT，但其外表上显示为字符串。这样一来，用这个字段来做一些选项列表变得相当的完美。 如果我们有一个字段，比如“性别”，“国家”，“民族”，“状态”或“部门”，我们知道这些字段的取值是有限而且固定的，那么，我们应该使用 ENUM 而不是 VARCHAR。



 (十)尽可能的不要赋值为NULL


 如果不是特殊情况，尽可能的不要使用NULL。在MYSQL中对于INT类型而言，EMPTY是0，而NULL是空值。而在Oracle中 NULL和EMPTY的字符串是一样的。NULL也需要占用存储空间，并且会使我们的程序判断时更加复杂。现实情况是很复杂的，依然会有些情况下，我们需要使用NULL值。 下面摘自MySQL自己的文档： “NULL columns require additional space in the row to record whether their values are NULL. For MyISAM tables, each NULL column takes one bit extra, rounded up to the nearest byte.” 


 (十一) 固定长度的表会更快


 如果表中的所有字段都是“固定长度”的，整个表会被认为是 “static” 或 “fixed-length”。 例如，表中没有如下类型的字段： VARCHAR，TEXT，BLOB。只要我们包括了其中一个这些字段，那么这个表就不是“固定长度静态表”了，这样，MySQL 引擎会用另一种方法来处理。 固定长度的表会提高性能，因为MySQL搜寻得会更快一些，因为这些固定的长度是很容易计算下一个数据的偏移量的，所以读取的自然也会很快。而如果字段不是定长的，那么，每一次要找下一条的话，需要程序找到主键。 并且，固定长度的表也更容易被缓存和重建。不过，唯一的副作用是，固定长度的字段会浪费一些空间，因为定长的字段无论我们用不用，他都是要分配那么多的空间。另外在取出值的时候要使用trim去除空格 



 (十二)垂直分割


 “垂直分割”是一种把数据库中的表按列变成几张表的方法，这样可以降低表的复杂度和字段的数目，从而达到优化的目的。



 (十三)拆分大的DELETE或INSERT


 如果我们需要在一个在线的网站上去执行一个大的 DELETE 或 INSERT 查询，我们需要非常小心，要避免我们的操作让我们的整个网站停止相应。因为这两个操作是会锁表的，表一锁住了，别的操作都进不来了。Apache 会有很多的子进程或线程。所以，其工作起来相当有效率，而我们的服务器也不希望有太多的子进程，线程和数据库链接，这是极大的占服务器资源的事情，尤其是内存。如果我们把我们的表锁上一段时间，比如30秒钟，那么对于一个有很高访问量的站点来说，这30秒所积累的访问进程/线程，数据库链接，打开的文件数，可能不仅仅会让我们的WEB服务Crash，还可能会让我们的整台服务器马上掛了。所以在使用时使用LIMIT 控制数量操作记录的数量。



 (十四)越小的列会越快  
对于大多数的数据库引擎来说，硬盘操作可能是最重大的瓶颈。所以，把我们的数据变得紧凑会对这种情况非常有帮助，因为这减少了对硬盘的访问。 参看 MySQL 的文档 Storage Requirements 查看所有的数据类型。 如果一个表只会有几列罢了（比如说字典表，配置表），那么，我们就没有理由使用 INT 来做主键，使用 MEDIUMINT, SMALLINT 或是更小的 TINYINT 会更经济一些。如果我们不需要记录时间，使用 DATE 要比 DATETIME 好得多。 



 (十五)选择正确的存储引擎


 在MYSQL中有两个存储引擎MyISAM和InnoDB,每个引擎都有利有弊。


 MyISAM适合于一些需要大量查询的应用，但是对于大量写操作的支持不是很好。甚至一个update语句就会进行锁表操作，这时读取这张表的所有进程都无法进行操作直至写操作完成。另外MyISAM对于SELECT  COUNT(*)这类的计算是超快无比的。InnoDB 的趋势会是一个非常复杂的存储引擎，对于一些小的应用，它会比 MyISAM 还慢。它支持“行锁” ，于是在写操作比较多的时候，会更优秀。并且，他还支持更多的高级应用，比如：事务。


 MyISAM是MYSQL5.5版本以前默认的存储引擎，基于传统的ISAM类型，支持B-Tree，全文检索，但是不是事务安全的，而且不支持外键。不具有原子性。支持锁表。


 InnoDB是事务型引擎，支持ACID事务(实现4种事务隔离机制)、回滚、崩溃恢复能力、行锁。以及提供与Oracle一致的不加锁的读取方式。InnoDB存储它的表和索引在一个表空间中，表空间可以包含多个文件。


 MyISAM和InnoDB比较，如下图所示：


 



 对于Linux版本的MYSQL  配置文件在 /etc/my.cnf中


 



 在5.5之后默认的存储引擎是INNODB


 可以单独进行修改也可以在创建表时修改：


 ALTER TABLE tab_name ENGINE INNODB；


 (十六)小心永久链接


 “永久链接”的目的是用来减少重新创建MySQL链接的次数。当一个链接被创建了，它会永远处在连接的状态，就算是数据库操作已经结束了。而且，自从我们的Apache开始重用它的子进程后——也就是说，下一次的HTTP请求会重用Apache的子进程，并重用相同的 MySQL 链接。 
而且，Apache 运行在极端并行的环境中，会创建很多很多的了进程。这就是为什么这种“永久链接”的机制工作地不好的原因。在我们决定要使用“永久链接”之前，我们需要好好地考虑一下我们的整个系统的架构。



 



 

影响数据库性能的常见因素如下：
 （1）磁盘IO；
 （2）网卡流量；
 （3）服务器硬件；
 （4）SQL查询速度。
 
下面介绍几个mysql 优化的工具，可以使用它们对MySQL进行检查，生成awr报告，从整体上把握数据库的性能情况。
 
一、MySQLTuner.pl
 
MySQLTuner是MySQL一个常用的数据库性能诊断工具，主要检查参数设置的合理性，包括日志文件、存储引擎、安全建议及性能分析。针对潜在的问题，给出改进的建议，帮助进行MySQL优化。MySQLTuner支持MySQL / MariaDB / Percona Server的约300个指标。
 
项目地址：https://github.com/major/MySQLTuner-perl
 
1.1 下载
 
wget https://raw.githubusercontent.com/major/MySQLTuner-perl/master/mysqltuner.pl
 
1.2 使用
 
[root@localhost ~]# ./mysqltuner.pl --socket /var/lib/mysql/mysql.sock
 >> MySQLTuner 1.7.4 - Major Hayden <major@mhtx.net>
 >> Bug reports, feature requests, and downloads at http://mysqltuner.com/
 >> Run with '--help' for additional options and output filtering
[--] Skipped version check for MySQLTuner script
Please enter your MySQL administrative login: root
Please enter your MySQL administrative password: [OK] Currently running supported MySQL version 5.7.23
[OK] Operating on 64-bit architecture
 
1.3、报告分析
 （1）重要关注[!!]（中括号有叹号的项）例如[!!] Maximum possible memory usage: 4.8G (244.13% of installed RAM)，表示内存已经严重用超了。
 
 （2）关注最后给的建议“Recommendations ”。
 
 
二、tuning-primer
 
tuning-primer针于mysql的整体进行一个体检，对潜在的问题，给出优化的建议。
 
项目地址：https://github.com/BMDan/tuning-primer.sh
 
目前，支持检测和优化建议的内容如下：
 （1）慢查询日志
 （2）最大连接数
 （3）工人线程
 （4）密钥缓冲区【仅限MyISAM】
 （5）查询缓存
 （6）排序缓冲区
 （7）加盟
 （8）临时表
 （9）表(开放和定义)缓存
 （10）表锁定
 （11）表扫描(read_buffer)【仅限MyISAM】
 （12）InnoDB状态
 
2.1 下载
 
wget https://launchpad.net/mysql-tuning-primer/trunk/1.6-r1/+download/tuning-primer.sh
 
2.2 使用
 
[root@localhost ~]# [root@localhost dba]# ./tuning-primer.sh

 -- MYSQL PERFORMANCE TUNING PRIMER --
 - By: Matthew Montgomery -
 
2.3 报告分析
 重点查看有红色告警的选项，根据建议结合自己系统的实际情况进行修改，例如：
 
 三、pt-variable-advisor
 pt-variable-advisor 可以分析MySQL变量，并就可能出现的问题提出建议。
 
3.1 安装
 https://www.percona.com/downloads/percona-toolkit/LATEST/
 
[root@localhost ~]#wget https://www.percona.com/downloads/percona-toolkit/3.0.13/binary/redhat/7/x86_64/percona-toolkit-3.0.13-re85ce15-el7-x86_64-bundle.tar

[root@localhost ~]#yum install percona-toolkit-3.0.13-1.el7.x86_64.rpm
 
3.2 使用
 pt-variable-advisor是pt工具集的一个子工具，主要用来诊断参数设置是否合理。
 
[root@localhost ~]# pt-variable-advisor localhost --socket /var/lib/mysql/mysql.sock
 
3.3 报告分析
 重点关注有WARN的信息的条目，例如：
 
 
四、pt-qurey-digest
 
pt-query-digest 主要功能是从日志、进程列表和tcpdump分析MySQL查询。
 
4.1安装
 https://www.percona.com/downloads/percona-toolkit/LATEST/
 
[root@localhost ~]#wget https://www.percona.com/downloads/percona-toolkit/3.0.13/binary/redhat/7/x86_64/percona-toolkit-3.0.13-re85ce15-el7-x86_64-bundle.tar

[root@localhost ~]#yum install percona-toolkit-3.0.13-1.el7.x86_64.rpm
 
4.2使用
 pt-query-digest主要用来分析mysql的慢日志，与mysqldumpshow工具相比，py-query_digest 工具的分析结果更具体，更完善。
 
[root@localhost ~]# pt-query-digest /var/lib/mysql/slowtest-slow.log
 
4.3 常见用法分析
 （1）直接分析慢查询文件:
 
pt-query-digest /var/lib/mysql/slowtest-slow.log > slow_report.log
 
（2）分析最近12小时内的查询：
 
pt-query-digest --since=12h /var/lib/mysql/slowtest-slow.log > slow_report2.log
 
（3）分析指定时间范围内的查询：
 
pt-query-digest /var/lib/mysql/slowtest-slow.log --since '2017-01-07 09:30:00' --until '2017-01-07 10:00:00'> > slow_report3.log
 
（4）分析指含有select语句的慢查询
 
pt-query-digest --filter '$event->{fingerprint} =~ m/^select/i' /var/lib/mysql/slowtest-slow.log> slow_report4.log
 
（5）针对某个用户的慢查询
 
pt-query-digest --filter '($event->{user} || "") =~ m/^root/i' /var/lib/mysql/slowtest-slow.log> slow_report5.log
 
（6）查询所有所有的全表扫描或full join的慢查询
 
pt-query-digest --filter '(($event->{Full_scan} || "") eq "yes") ||(($event->{Full_join} || "") eq "yes")' /var/lib/mysql/slowtest-slow.log> slow_report6.log
 
4.4 报告分析
 第一部分：总体统计结果 Overall：总共有多少条查询 Time range：查询执行的时间范围 unique：唯一查询数量，即对查询条件进行参数化以后，总共有多少个不同的查询 total：总计 min：最小 max：最大 avg：平均 95%：把所有值从小到大排列，位置位于95%的那个数，这个数一般最具有参考价值 median：中位数，把所有值从小到大排列，位置位于中间那个数。
 
第二部分：查询分组统计结果 Rank：所有语句的排名，默认按查询时间降序排列，通过–order-by指定 Query ID：语句的ID，（去掉多余空格和文本字符，计算hash值） Response：总的响应时间 time：该查询在本次分析中总的时间占比 calls：执行次数，即本次分析总共有多少条这种类型的查询语句 R/Call：平均每次执行的响应时间 V/M：响应时间Variance-to-mean的比率 Item：查询对象。
 
第三部分：每一种查询的详细统计结果 ID：查询的ID号，和上图的Query ID对应 Databases：数据库名 Users：各个用户执行的次数（占比） Query_time distribution ：查询时间分布, 长短体现区间占比。Tables：查询中涉及到的表 Explain：SQL语句。
 
参考：https://mp.weixin.qq.com/s/dqxh1bIn5XlPQERAhQZ4xA

想了很久要不要发一篇关于MySql优化的文章最后还是决定把它写出来。以输出倒逼自己复习与输入。以下内容大都参考了《高性能MySQL》一书也好把一些的章节做一个总结。全文的聊到的三个主要能容是：
 
MySql的特点与存储引擎
 
MySql高性能索引
 
MaySql的数据类型优化与查询性能优化
 
MySql的特点与存储引擎
 
MySql的总体架构图
 
用户执行Mysql的流程图：
 
 
上层的一些服务并不是Mysql特有的，比如说Mysql的服务器会启动连接池然后对客户端做连接处理授权认证安全等工作。
 
中间的架构包括查询解析、分析、优化和缓存等跨存储引擎的过程。
 
底层是存储引擎，负责Mysql数据的存储和提取，但是一般的存储引擎是不会去解析SQL语句的（但是InnoDB可以去解析外键的定义），不同的存储引擎之间是不会通信的而只是单纯的处理上层服务器的请求。
 
 
 
数据库中的锁
 
按照互斥性来说
 
共享锁（之间不互斥），排他锁（与其他任意锁都互斥）。在一般情况下读锁是共享锁，写锁是排他锁。需要注意的是，写锁的优先级比较高一个写锁的请求可能被插入到读锁队列的前面反之不然。
 
按照粒度来说
 
一般有表锁（粗粒度效率低）与行锁（细粒度并发效率高）。所谓的锁的策略就是在锁的开销与数据的安全性之间寻找一个平衡。
 
死锁
 
就是多个事务在同一资源上相互占用，并锁定对方占用的资源从而导致的恶性循环的现象。好在Mysql会自动检测和处理死锁。InnoDB的处理策略是将持有最少行级排他锁的事物回滚。
 
事务（略）
 
这里只强调一点就是，没有100%的持久化，否则备份是不会增加持久性的。
 
另一种高并发策略——MVCC
 
Mysql大多数的存储引擎并不是简单是实现了行级锁，他们同时实现了MVCC（多版本并发控制）。MVCC可以认为是行级锁的一个变种，但是它在很多的情况下都避免了加锁（只有在写入操作的时候才加锁）。MVCC是通过保存数据在某一个时间点的快照来实现的，也就是不管什么时候每个事物看到的数据都是一致的。也就是可能存在不同的事务在同一时刻看到的数据是不同的。别着急我们慢慢来。
 
MVCC的原理就是在数据的每一行后面增加两个列，一列保存的是创建时间另一列保存的是删除时间（准确的说是系统的版本号）。下面我们以REPEATABLE READ的隔离级别举个例子。
 
 
 
SELECT
 
首先是查询，当一个事务开启是能够查到的数据得满足两个条件
 
创建时间在当前事务开启之前（包含当前事务）
删除时间在当前事务之后
只有同时满足这两个条件的行才能够被当前的事务所查到。
 
INSERT
 
当当前的事务新增一条数据的时候，会将当前的版本号分配给当前新增的每一行。
 
 
 
DELETE
 
会将当前的版本号分配给删除的每一行作为删除时间。
 
UPDATE
 
会新插入一条数据版本号为当前的事务版本号，旧的那一条数据会设置当前事物的版本号为删除时间。
 
 
 
总结：增加了这样的两个版本号，会使得效率大大提高但是会花掉空间的成本。
 
 
 
InnoDB与MyISAM存储引擎的区别
 
 
比较项目
 
 
InnoDB
 
 
MyISAM
 
 
数据存储
 
 
存在表空间（是由InnoDB管理的黑盒子由一系列数据文件组成）
 
 
存储在数据文件与索引文件中
 
 
事务
 
 
支持事务
 
 
不支持事务
 
 
行级锁
 
 
支持行级锁
 
 
不支持行级锁（但是支持表级索）
 
 
全文检索
 
 
不支持全文检索
 
 
支持全文检索
 
 
外键
 
 
支持外键
 
 
不支持外键
 
 
 
Momory引擎
 
所有的数据都保留在内存中，不需要进行磁盘的IO所以读取的速度很快。但是一旦关机的话表的结构会保留但是数据就会丢失。
 
Memory表支持Hash索引，因此查找速度很快。即便如此它还是不能代替基于磁盘的引擎因为它只是支持表锁所以并发写入的效率不高。同时它不支持TEXT与BLOB数据类型，而且没有varchar类型（即使写了varchar也会自动转换成char类型）所以会造成内存的浪费。
 
 
 
总结：默认使用InnoDB存储引擎。例如：即使是需要全文索引我们也可以通过InnoDB与Sphinx的组合，而不是直接使用MyISAM。
 
 
 
创建高性能的索引
 
索引的概念就不多说了，这种数据结构可以大幅度的减少我们查找数据的时间。从架构上来说它是在存储引擎层面实现的。
 
B-Tree与B+Tree索引
 
先上图：
 
 
 
（图片来自csdn gitchat）
 
从图中我们可以看到数据的特点。所有真实的数据都是排好序的在B-Tree的叶子节点上，这些节点到根的距离是一样的。当我们要查找一个具体的数据的时候，首先会从根节点开始找每一次都会根据大小选择一个分支，这样的话大大减少了查找的次数。从此再也不需要全表的扫描了。同时B-Tree的数据是按照顺序来存放的对顺序值的查找（对索引列进行OrderBy操作）也是非常快的。这样的话避免了很多随机的IO。
 
这里需要注意的是如果存在多个索引数据库的排序规则。按照create 表的时候的索引出现的先后顺序来排序的。如果前一个指标一样那么则使用后一个指标来排序。同时如果有多列索引的话那么我们只能够从最左列开始查找。不能跳过索引的列，所以对于列的先后顺序也是我们优化的一个指标。
 
最后，B+Tree比B-Tree多了一个节点之间的顺序扫描。
 
 
 
哈希索引
 
哈希索引的底层是Hash表，每查一个值之前首先计算它的hashcode然后找到对应数据的指针，最后根据指针去找到真实数据。它有以下几点注意事项：
 
只能使用精确查询，这个与hash表的特点有关。
它不是按照索引值的顺序来存储的所以不能用于排序。
只能有等值查询不能查询>  < 等关系运算。
Hash冲突时会遍历对应Hash码的对应的链表的所有节点，而且维护成本高每删除一条记录时就会修改对应的Hash表。
自适应Hash索引
 
在innodb中如果有些索引使用的非常频繁的时候，就可以考虑使用自适应Hash索引。它对查询的性能的提升是非常明显的。
 
它的设计原理非常简单，就是让一个字段的Hash值作为索引，然后再使用B+Tree来进行查询。从这里我们可以看出这与真正的Hash索引是不同的，真正执行查询的是B+Tree，我们只是在查询之前计算了一次Hash值。这样我们可以做出以下的优化，案例如下：
 
 
Select id from url where url = “http://www.mysql.com”;
 
 
 
Select id from url where
 
url = “http://www.mysql.com” 
 
and url_crc=crc32(“http://www.mysql.com”);
 
 
 
其中crc32就是我们所指定的Hash函数。如果以它为索引，那么Mysql的优化器就会选择性能高的url_crc来实现查找。即使出现了hash冲突（也就是同一个Hash值会对应不同的url）我们也早已过滤掉了大部分的无用信息而不再像从前一样进行逐个比较了。当然自适应Hash索引也有自己的优点就是需要维护Hash索引，而且要处理Hash冲突尤其是在数据量较大的情况下。这里给出一个处理Hash冲突的建议就是使用多个where条件来保证查询的准确性。
 
小节：索引的优点
 
索引大大减少了服务器需要扫描的数据量。
索引可以避免服务器排序和建立临时表。
索引可以将随机的IO变成顺序的IO。
 
 
高性能索引的策略
 
独立的列
 
独立的列是指，索引列不能成为表达式或者参数的一部分否则就会失效。比如说下面的索引列就没有发挥作用。
 
 
比如actor_id列为索引列那么
 
Select actor_id from mytable where actor_id + 1 = 5;
 
 
 
前缀索引和索引的选择性
 
在数据量大索引的字段过长的时候，索引本身也会占用较大的空间。这样会使得索引变得大而且慢，所以我们有时候使用索引的前缀来代替索引以此来减短索引列的长度减少索引所占的空间。
 
这里不得不提出一个概念就是索引的选择性，它是指不重复的索引值与记录总数的比值。索引的选择性越高则查询性能越高，因为在查询时会过滤掉更多的行。唯一索引的选择性是1，而大多数的索引往往是一对多的。与此同时在数据库上有这样的一个事实，使用的索引前缀越短则索引的选择性广义的单调递减，使用的索引越长则索引的选择性广义单调递增。这就出现了一个高选择性与短前缀之间的一种平衡。我们的策略是尽可能的选择一个临界值的长度一旦超过这个长度索引的选择性会提升的很慢。那么这个时候我们的前缀索引是性价比最高的。
 
选择合适的索引列顺序
 
当有多列索引时，索引列的顺序极大的影响着我们的查询效率。通常情况下（不考虑排序和分组的情况下）我们把高选择性的索引列放在最前面，以保证在查询刚开始的时候就可以过滤大部分的数据。实务上我们需要根据经验来调整索引的顺序，比如说把IO的优化放在第一位，甚至是修改程序代码来达到查询的优化。
 
举个栗子：
 
查找一个名字叫“主流7”，而且在15软件G2班的同学的信息。
 
已知名字叫主流7的人有10000条，而15软件G2班只有30人。这个时候我们优先查出15软件G2班的人然后再去找名字叫主流7的人的效率要远远高于，找出所有名字叫主流7的人然后再看那个是属于15软件G2班的人的效率。
 
聚簇索引
 
聚簇索引不是一种单独的数据存储类型而是一种数据的存储方式，。所谓聚簇就是数据和相邻的键值紧凑的存放在一起（也就是数据怎么存放索引就怎么建立）所以一个表只能够有一个聚簇索引，因为它的数据只能以一种方式来存储。（聚簇索引的定义还待考究）。
 
 
 
聚簇索引与非聚簇索引（二级索引）
 
聚簇索引与非聚簇索引的具体实现与底层的存储引擎相关。下面分别以MySQL中的两大主流引擎来展开讨论。
 
InnoDB
 
InnoDB的聚簇索引就是表本身，它由表的数据与B+Tree索引组成。
 
其中内部节点中包括索引列和指向下一节点的指针，而叶子节点包括表所有列上的数据，比如主键列，MVCC列，回滚列其它列等等。所以我们在查询数据时直接就能够查找到我们想要的数据。
 
InnoDB的二级索引（非聚簇索引）
 
内部结点中包含索引列与指向下个节点的指针，而叶子节点则包括索引列和主键值。这也就造成了如果我们想要通过二级索引去查询一条数据的啥时候需要两轮的查询，第一轮是通过B+Tree查找到主键值，第二轮就是拿着主键值再经过一次B+Tree的查找找到真实的值。
 
MyISAM
 
MyISAM的索引聚簇索引和非聚簇索引原理相同，就是内部结点都包含有索引列和指向下一个节点的指针，在叶子节点中包含的是行号，指向实际的物理地址。
 
 
 
避免随机的聚簇索引
 
当表中没有什么数据需要聚集时，我们一般可以使用一个代理主键（与业务无关的键）去作为聚簇索引比如说一列自增的键。自增的键有一个好处就是在增加一条记录的时候会按照顺序插在最后面这样的话会非常节省资源因为它既避免了页分裂同时也避免了由页分裂而产生的内存碎片化。
 
另一方面，随机的聚簇索引（特别是对于一些IO密集的应用）使用随机的聚簇索引是很糟糕的，它使得聚簇索引的插入变得完全随机，使得数据完全没有聚集特性。下面具体说说随机的聚簇索引的缺点：
 
将要写入的目标页有可能已经不在内存中（或者是缓存中了），所以在写入时首先需要将目标页从硬盘读到内存中。这将导致大量的随机IO。
因为写入时乱序的，所以InnoDB不得不频繁的做页分裂操作，这将会大量的移动数据。
由于页分裂，所以会导致页变得稀疏并且不规则的填充，最终数据会有碎片。
综上我们要避免随机的聚簇索引。顺便自增的索引在高并发的时候也会产生线程安全问题，所以我们可以使用innodb_autoinc_lock_mode参数配置来保证自增的原子性。
 
小节
 
简单说一下InnoDB的聚簇索引的特点：
 
通过一个指标把与这个指标相关联的数据聚集起来，当查询与这个指标相关的数据时能够避免多次IO带来的效率底下。但是，当数据都在内存中进行运算时这时也就不会有
IO带来的效率问题了。
 
查询速度快，不像二级索引一样需要两轮查找。
索引的维护成本高，更新聚簇索引插入新的行可能会造成如果按照索引的顺序插入还好但是如果一旦在中间插入新的行就会导致页分裂的问题这样既使得更新的效率低下，又会造成内存的浪费。
覆盖索引
 
如果一个索引中包含我们想要查的那一列的值，我们就说这个索引就是覆盖索引。当我们需要查询被覆盖的列的值的时候可以直接查询覆盖索引中的值而无需回到表中查询数据。这样我们就可以说这次查询的表中的数据被索引中的数据给覆盖了。下面介绍它的一些优点：
 
可以配合InnoDB的二级索引来实现单次查询，如果二级索引能够覆盖查询就能够办到。
因为索引是按照顺序的（或者是局部有序）那么对于当前列值的IO密集型范围查询会大大减少随机IO。
索引的条目通常远小于数据行的大小，如果能直接从索引中获取数据那么会极大地减少数据的访问。减少了服务器的负载压力。
不是所有的引擎都支持覆盖索引，MySQL只支持基于B-Tree的覆盖索引。有的查询是是不能够使用覆盖索引的，比如说非前缀的like模糊查询。还有的查询他们要查询表中的很多列没有一个所以是可以覆盖很多列的所以只能够先利用覆盖索引过滤掉大多数的数据行，然后再在这个基础上进行查询，我们把这种查询方式叫做延迟关联。
 
 
 
数据类型优化与查询优化的一些经验法则
 
数据类型优化
 
MySQL支持的数据类型是很多的，选择好合适的数据类型对于节省内存和提升性能意义重大。下面介绍几点经验法则：
 
如果能够正常存储数据那么一般使用更小的数据类型。
尽量避免null值，null值可以是任何数据类型的默认值，但是null使得索引索引统计和值的比较都变得复杂。
TIMESTAMP与DATATIME的选择，TIMESTAMP的时间范围较DATATIME小的多，但是
TIMESTAMP占用的内存是DATATIME的一半并且能够随着时区不断地变化。
 
整数类型有可选的unsigned属性，如果我们的数值无需负数的话那么推荐使用这种类型，举例来说TINYINT UNSIGNED与TINYINT的存储范围都是256但是前者是0~255，后者是-128~127。
DECIMAL类型是一种存储方式，它可以用于精确计算。在计算过程中它会转化成double类型，但是DECIMAL的精确计算代价很高。为了减少这种高消耗我们可以在保留精度的条件下使用BIGINT来存储数据。举例来说，比如要保留6位小数，那么我们在存储数据的时候就可以给每一个数乘以100万，然后在取数据的时候在给每一个数除以100万。以减少代价。
Varchar与char。它们的存储方式与存储引擎相关，下面以InnoDB或MyISAM为例。
Varchar长度可变一般来说是节省空间的，但是如果使用ROW_FORMAT=FIXED参数创建表的话那么varchar也是固定的长度。Varchar有一个特点就是需要在存储空间末尾开辟一到两个字节记录字符串的长度。当字符串长度的方差大的时候（比如说使用了UTF-8这样的字符集）那么推荐使用varchar。
 
Char是定长的数据类型，非常适合存储定长的数据比如说MD5码。对于非常短的列也有非常高的效率，比如说表示一个只有true/false的值char(1)的效率要高于varchar(1)因为varchar(1)后面会附加一个记录长度的额外字节。
 
当存储IP地址时使用无符号整数，因为IP地址本来就是32位的无符号整数。使用
INET_ATON()与INET_NTOA()完成他们之间的转换。
 
查询优化
 
搞清楚查询的生命周期对于优化查询是十分必要的，如果把查询看作是一个任务那么它就是由很多的子任务所构成。查询的生命周期大致可以分为这样的几个阶段：从客户端到服务器，然后在服务器上解析，生成执行计划、执行最后返回结果给客户端。这里我再次贴上这张图
 
 
其中执行阶段可以认为是最重要的阶段这一阶段包括大量的检索数据到存储引擎的调用以及调用后的数据处理，包括排序，分组等。除此之外查询还要在不同的地方花费时间，包括网络，CPU计算，生成统计信息和执行计划，锁等待等等。
 
优化数据访问
 
减少请求无用的数据，比如说我们查询了100条数据但是最后只在得到的结果中取了其中的10条（在应用程序中完成的）。这样不仅仅是耗费了大量的计算机资源也耗费了大量的网络资源。所以在这种情况下我们尽早使用limit语句来过滤或者是使用索引来过滤。
 
重构查询方式
 
一般情况下我们倾向于使用尽可能少的SQL语句去完成查询操作，这样能够减少发送查询的次数。但是有时候我们需要将大的查询拆分成一些小的查询。比如说在删除大量的数据时数据库（假设数据库支持锁）会将大量的数据锁住这样的话其它的语句就会在队列中等待。这时候将删除的任务分为几波就可以更加高效。
 
有时候我们也需要将查询的连接放在应用中，将一个连接查询拆分成几个小的查询。这样带来的的好处就是避免了几张表连接时占用的大量内存，而且更加有利于利用缓存（如果关联的表中某个表发生了改变将无法使用缓存，但是当拆开之后某个表发生了细微的变换之后还可以使用缓存）。最后拆开之后还可以减少锁的竞争。
 
优化特定类型的查询
 
这里仅仅介绍几个经验的法则：
 
当统计行数的时候尽量使用count(*)，当我们以某一列为统计指标的时候，如果碰到null的时候就会不把这一行统计在内。而在MySQL的底层也会将count(col)优化成count(*)。
要尽可能的保证GROUP BY和ORDERBY语句中只包含一个列，这样MySQL才有可能使用索引来优化查询。
要时时注意子查询，子查询构成的虚表是没有任何索引的。
优化LIMIT分页，在一般情况下我们会使用LIMIT加上偏移量来搞定，这时如果有合适的索引的话效率一般不会错，但是如果只是一味的使用LIMIT语句的话，比如有1000,20这样的语句的话前面的需要查询1020条记录然后抛弃前面的1000条数据，这个代价是非常高的。一般的做法是尽可能的使用覆盖索引（里面可以包含我们想要的值）做延迟关联这样的效率就会很高。举个例子：以下代码使用方案2
 
select film_id,description, from sakila.film order by title limit 50,5;
 
 
Select film.film_id,film.description
 
from sakila.film
 
inner join(
 
Select film_id from sakila.film order by title limit 50,5
 
) as lim using(film_id);
 
 
 
至此我的有关于数据库的优化的知识点输出完毕。同时也希望能够帮助到你。最后，欢迎批评。

MySQL的万能嵌套循环并不是对每种查询都是最优的。不过MySQL查询优化器只对少部分查询不适用，而且我们往往可以通过改写查询让MySQL高效的完成工作。
 
1 关联子查询
 
MySQL的子查询实现的非常糟糕。最糟糕的一类查询时where条件中包含in()的子查询语句。因为MySQL对in()列表中的选项有专门的优化策略，一般会认为MySQL会先执行子查询返回所有in()子句中查询的值。一般来说，in()列表查询速度很快，所以我们会以为sql会这样执行
 
select * from tast_user where id in (select id from user where name like '王%');
我们以为这个sql会解析成下面的形式
select * from tast_user where id in (1,2,3,4,5);
实际上MySQL是这样解析的
select * from tast_user where exists 
(select id from user where name like '王%' and tast_user.id = user.id);
 
MySQL会将相关的外层表压缩到子查询中，它认为这样可以更高效的查找到数据行。
 
这时候由于子查询用到了外部表中的id字段所以子查询无法先执行。通过explin可以看到，MySQL先选择对tast_user表进行全表扫描，然后根据返回的id逐个执行子查询。如果外层是一个很大的表，那么这个查询的性能会非常糟糕。当然我们可以优化这个表的写法：
 
select tast_user.* from tast_user inner join user using(tast_user.id) where user.name like '王%'
 
另一个优化的办法就是使用group_concat()在in中构造一个由逗号分隔的列表。有时这比上面使用关联改写更快。因为使用in()加子查询，性能通常会非常糟糕。所以通常建议使用exists()等效的改写查询来获取更好的效率。
 
如何书写更好的子查询就不在介绍了，因为现在基本都要求拆分成单表查询了，有兴趣的话可以自行去了解下。
 
2 UNION的限制
 
有时，MySQL无法将限制条件从外层下推导内层，这使得原本能够限制部分返回结果的条件无法应用到内层查询的优化上。
 
如果希望union的各个子句能够根据limit只取部分结果集，或者希望能够先排好序在合并结果集的话，就需要在union的各个子句中分别使用这些子句。例如，想将两个子查询结果联合起来，然后在取前20条，那么MySQL会将两个表都存放到一个临时表中，然后在去除前20行。
 
(select first_name,last_name from actor order by last_name) union all
(select first_name,last_name from customer order by  last_name) limit 20;
 
这条查询会将actor中的记录和customer表中的记录全部取出来放在一个临时表中，然后在取前20条，可以通过在两个子查询中分别加上一个limit 20来减少临时表中的数据。
 
现在中间的临时表只会包含40条记录了，处于性能考虑之外，这里还需要注意一点：从临时表中取出数据的顺序并不是一定，所以如果想获得正确的顺序，还需要在加上一个全局的order by操作
 
3 索引合并优化
 
前面文章中已经提到过，MySQL能够访问单个表的多个索引以合并和交叉过滤的方式来定位需要查找的行。
 
4 等值传递
 
某些时候，等值传递会带来一些意想不到的额外消耗。例如，有一个非常大的in()列表，而MySQL优化器发现存在where/on或using的子句，将这个列表的值和另一个表的某个列相关联。
 
那么优化器会将in()列表都赋值应用到关联的各个表中。通常，因为各个表新增了过滤条件，优化器可以更高效的从存储引擎过滤记录。但是如果这个列表非常大，则会导致优化和执行都会变慢。
 
5 并行执行
 
MySQL无法利用多核特性来并行执行查询。很多其他的关系型数据库鞥能够提供这个特性，但MySQL做不到。这里特别指出是想提醒大家不要花时间去尝试寻找并行执行查询的方法。
 
6 哈希关联
 
在2013年MySQL并不执行哈希关联，MySQL的所有关联都是嵌套循环关联。不过可以通过建立一个哈希索引来曲线实现哈希关联如果使用的是Memory引擎，则索引都是哈希索引，所以关联的时候也类似于哈希关联。另外MariaDB已经实现了哈希关联。
 
7 松散索引扫描
 
由于历史原因，MySQL并不支持松散索引扫描，也就无法按照不连续的方式扫描一个索引。通常，MySQL的索引扫描需要先定义一个起点和重点，即使需要的数据只是这段索引中很少的几个，MySQL仍需要扫描这段索引中每个条目。
 
例：现有索引（a,b）
 
select * from table where b between 2 and 3;
 
因为索引的前导字段是a，但是在查询中只指定了字段b，MySQL无法使用这个索引，从而只能通过全表扫描找到匹配的行。
 
MySQL全表扫描：
 
 
了解索引的物理结构的话，不难发现还可以有一个更快的办法执行上面的查询。索引的物理结构不是存储引擎的API使得可以先扫描a列第一个值对应的b列的范围，然后在跳到a列第二个不同值扫描对应的b列的范围
 
 
这时就无需在使用where子句过滤，因为松散索引扫描已经跳过了所有不需要的记录。
 
上面是一个简单的例子，处理松散索引扫描，新增一个合适的索引当然也可以优化上述查询。但对于某些场景，增加索引是没用的，例如，对于第一个索引列是范围条件，第二个索引列是等值提交建查询，靠增加索引就无法解决问题。
 
MySQL5.6之后，关于松散索引扫描的一些限制将会通过索引条件吓退的分行是解决。
 
8 最大值和最小值优化
 
对于MIN()和MAX()查询，MySQL的优化做的并不好，例：
 
select min(actor_id) from actor where first_name = 'wang'
 
因为在first_name字段上并没有索引，因此MySQL将会进行一次全表扫描。如果MySQL能够进行主键扫描，那么理论上，当MySQL读到第一个太满足条件的记录的时候就是我们需要的最小值了，因为主键是严哥按照actor_id字段的大小排序的。但是MySSQL这时只会做全表扫描，我们可以通过show status的全表扫描计数器来验证这一点。一个区县优化办法就是移除min()函数，然后使用limit 1来查询。
 
这个策略可以让MySQL扫描尽可能少的记录数。这个例子告诉我们有时候为了获得更高的性能，就得放弃一些原则。
 
9 在同一个表上查询和更新
 
MySQL不允许对同一张表同时进行查询和更新。这并不是优化器的限制，如果清楚MySQL是如何执行查询的，就可以避免这种情况。例：
 
update table set cnt = (select count(*) from table as tb where tb.type = table.type);
 
这个sql虽然符合标准单无法执行，我们可以通过使用生成表的形式绕过上面的限制，因为MySQL只会把这个表当做一个临时表来处理。
 
update table inner join
(select type,count(*) as cnt from table group by type) as tb using(type) 
set table.cnt = tb.cnt;
 
实际上这执行了两个查询：一个是子查询中的select语句，另一个是夺标关联update，只是关联的表时一个临时表。子查询会在update语句打开表之前就完成，所以会正常执行。
 
10 查询优化器的提示（hint）
 
如果对优化器选择的执行计划不满意，可以使用优化器提供的几个提示（hint）来控制最终的执行计划。下面将列举一些常见的提示，并简单的给出什么时候使用该提示。通过在查询中加入响应的提示，就可以控制该查询的执行计划。
 
① HIGH_PRIORITY 和 LOW_PRIORITY
 
这个提示告诉MySQL，当多个语句同时访问某一表的时候，哪些语句的优先级相对高些，哪些语句优先级相对低些。
 
HIGH_PRIORITY用于select语句的时候，MySQL会将此select语句重新调度到所有正在表锁以便修改数据的语句之前。实际上MySQL是将其放在表的队列的最前面，而不是按照常规顺序等待。HIGH_PRIORITY还可以用于insert语句，其效果只是简单的体校了全局LOW_PRIORITY设置对该语句的影响。
 
LOW_PRIORITY则正好相反，它会让语句一直处于等待状态，只要在队列中有对同一表的访问，就会一直在队尾等待。在CRUD语句中都可以使用。
 
这两个提示只对使用表锁的存储引擎有效，不能在InnoDB或其他有细粒度所机制和并发控制的引擎中使用。在MyISAM中也要慎用，因为这两个提示会导致并发插入被禁用，可能会严重降低性能。
 
HIGH_PRIORITY和LOW_PRIORITY其实只是简单的控制了MySQL访问某个数据表的队列顺序。
 
② DELAYED
 
这个提示对insert和replace有效。MySSQL会将使用该提示的语句立即返回给客户端，并将插入的行数据放入缓冲区，然后在表空闲时批量将数据写入。日志型系统使用这样的提示非常有效，或者是其他需要写入大量数据但是客户端却不需要等待单条语句完成I/O的应用。这个用法有一些限制。并不是所有的存储引擎都支持，并且该提示会导致函数last_insert_id()无法正常工作。
 
③ STRAIGHT_JOIN
 
这个提示可以防止在select语句的select关键字之后，也可以防止在任何两个关联表的名字之间。第一个用法是让查询中所有的表按照在语句中出现的顺序进行关联。第二个用法则是固定其前后两个表的关联顺序。
 
当MySQL没能选择正确的关联顺序的时候，或者由于可能的顺序太多导致MySQL无法评估所有的关联顺序的时候，STRAIGHT_JOIN都会很有用，在MySQL可能会发给大量时间在statistics状态时，加上这个提示则会大大减少优化器的搜索空间
 
④ SQL_SMALLRESULT和SQL_BIG_RESULT
 
这个两个提示只对select语句有效。他们告诉优化器对group by或者distinct查询如何使用临时表及排序。SQL_SMALL_RESULT告诉优化器结果集会很小，可以将结果集放在内存中的索引临时表，以避免排序操作。如果是SQL_BIG_RESULT，则会告诉优化器结果集可能会非常大，建议使用磁盘临时表做排序操作。
 
⑤ SQL_BUFFER_RESULT
 
这个提示告诉优化器将查询结果放入一个临时表，然后尽可能快速释放表锁。这和前面提到的由客户端缓存结果不同。当你无法使用客户端缓存的时候，使用服务器端的缓存通常很有效。好处是无需在客户端上消耗过多内存，还能尽快释放表锁。代价是服务器端将需要更多的内存。
 
⑥ SQL_CACHE和SQL_NO_CACHE
 
这个提示告诉MySQL这个结果集是否应该放入查询缓存中。
 
⑦ SQL_CALC_FOUND_ROWS
 
严哥来说，这并不是一个优化器提示。它不会告诉优化器任何关于执行计划的东西。它会让MySQL返回的结果集包含更多的信息。查询中加上该提示MySQL会计算limit子句之后这个查询要返回的结果集总数，而实际上值返回limit要求的结果集。可以通过函数found_row()获得这个值。慎用，后面会说明为什么。
 
⑧ FOR UPDATE和LOCK IN SHARE MODE
 
这也不是真正的优化器提示。这两个提示主要控制select语句的锁机制，但只对实现了行级锁的存储引擎有效。使用该提示会对符合查询条件的数据行加锁。对于insert/select语句是不需要这两个提示的因为5.0以后会默认给这些记录加上读锁。
 
唯一内置的支持这两个提示的引擎就是InnoDB，可以禁用该默认行为。另外需要记住的是，这两个提示会让某些优化无法正常使用，例如索引覆盖扫描。InnoDB不能在不访问主键的情况下排他的锁定行，因为行的版本信息保存在主键中。
 
如果这两个提示被经常滥用，很容易早晨服务器的锁争用问题。
 
⑨ USE INDEX、IGNORE INDEX和FORCE INDEX
 
这几个提示会告诉优化器使用或者不使用那些索引来查询记录。
 
在5.0版本以后新增了一些参数来控制优化器的行为：
 
① optimizer_search_depth
 
这个参数控制优化器在穷举执行计划时的限度。如果查询长时间处于statistics状态，那么可以考虑调低此参数。
 
② optimizer_prune_level
 
该参数默认是打开的，这让优化器会根据需要扫描的行数来决定是否跳过某些执行计划。
 
③optimizer_switch
 
这个变量包含了一些开启/关闭优化器特性的标志位。
 
前面两个参数时用来控制优化器可以走的一些捷径。这些捷径可以让优化器在处理非常复杂的SQL语句时，可以更高效，但也可能让优化器错过一些真正最优的执行计划，所以慎用。
 
修改优化器提示可能在MySQL更新后让新版的优化策略失效，所以一定要谨慎。