MySQL数据库优化总结

在说mysql优化之前，我们首先要知道的一个事情，就是系统优化顺序是怎么样的？如下图：

架构调优：在系统设计时首先需要充分考虑业务的实际情况，是否可以把不适合数据库做的事情放到数据仓库、搜索引擎或者缓存中去做；然后考虑写的并发量有多大，是否需要采用分布式；最后考虑读的压力是否很大，是否需要读写分离。对于核心应用或者金融类的应用，需要额外考虑数据安全因素，数据是否不允许丢失，是否需要采用Galera或者MGR等集群方案。

MySQL调优：需要确认业务表结构设计是否合理，SQL语句优化是否足够，该添加的索引是否都添加了，是否可以剔除多余的索引，数据库的参数优化是否足够

系统、硬件优化：系统瓶颈在哪里，哪些系统参数需要调整优化，进程资源限制是否提到足够高；在硬件方面是否需要更换为具有更高 IO 性能的存储硬件，是否需要升级内存、CPU、网络等。如果在设计之初架构就不合理，比如没有进行读写分离，那么后期的 MySQL 和硬件、系统优化的成本就会很高，并且还不一定能最终解决问题。如果业务性能的瓶颈是由于索引等 MySQL 层的优化不够导致的，那么即使配置再高性能的 IO 存储硬件或者 CPU 也无法支撑业务的全表扫描

归根结底，MySQL 本身调优方式：

1. 表的设计合理化（符合 3NF）表的设计是否合理（是否符合3NF，即原子性，惟一性约束，字段冗余性的约束）表的设计合理化（符合 3NF）
2. 数据类型是否合理
3. 索引的优化
4. SQL 优化（慢查询）
5. 表优化（分库分表，读写分离）

这里主要讲解索引的优化，SQL 语句的优化，表的优化。

SQL 优化（慢查询）

慢查询是啥

慢查询一般指慢查询日志。慢查询日志是指MySQL 记录所有执行超过 long_query_time 参数设定的时间阈值的 SQL 语句的时候，就会被记录到叫slow_query_log_file的日志中。

为什么会有慢查询的存在

存在慢查询的原因：

1. 查询不需要的记录。即取出很多不需要的数据

2. 总是取出全部列。即取出很多不需要的列数据

3. 重复查询相同的数据。即重复取相同的数据，这种情况可以使用缓存

慢查询配置

慢查询可以通过下面参数来控制：

1. slow_query_log：慢查询日志是否打开，默认是关闭的，如开启慢查询日志功能，会影响MYSQL性能，所以建议只到调试阶段打开

2. long_query_time：sql语句运行时间超过该值，则会把SQL 语句记录到慢查询日志中。默认值为10（s）

3. log_queries_not_using_indexes：参数设置为NO，可以捕获所有没使用索引的SQL语句

4. slow_query_log_file：慢查询日志存放的路径及文件，默认和数据文件放一起。

上面这些参数我们可以在MYSQL配置文件（my.cnf），如果不知道该配置文件在哪里，可以通过下面命令获得：

/usr/sbin/mysqld --verbose --help|grep -A 1 'Default options'

上面这些参数还可以通过连接MYSQL，然后使用“show VARIABLES like '%*%';” 查看参数值，然后通过 set global 进行修改。（*代表这些参数）

慢查询日志分析

一般不会产生有找到慢查询日志文件，打开一看如下：

​

 格式解析：

Time，查询执行时间
User@Host: root[root] @ localhost [::1] Id: 59，用户名 、用户的 IP 信息、线程 ID 号
Query_time，执行花费的时长，单位：毫秒
Lock_time，执行获得锁的时长
Rows_sent，获得的结果行数
Rows_examined，扫描的数据行数
SET timestamp，SQL 执行的具体时间

从慢查询日志格式我们看出，如果要快速定位到需要优化的SQL，是比较困难的。因此，mysql给我们提供了mysqldumpslow帮助我们自动化分析慢查询日志。

mysqldumpslow常用参数：

 r：返回记录

 t： 查询时间

 c： 访问次数

 -t：即为返回前面多少条的数据

 -s： 是表示按照何种方式排序

 -g：后边搭配一个正则匹配模式，大小写不敏感的

  

分析例子如下：

  

Explain 执行计划

从慢查询日志我们知道那么SQL需要优化，那么我们怎么优化呢？则需要分析。Explain关键字给我们提供了分析过程，分析出查询语句或是表结构的性能瓶颈，总的来说通过 EXPLAIN 我们可以知道：

 表的读取顺序；

 数据读取操作的操作类型；

 哪些索引可以使用；

 哪些索引被实际使用；

 表之间的引用；

 每张表有多少行

如下面例子：

  

关键字说明：

id： 在一个大的查询语句中每个 SELECT 关键字都对应一个唯一的 id；如果 explain 中的有多个 id 对应的数据项，则倒叙进行执行。即id越大的，越先执行分析； 数字编号相同，从上到下进行分析

select_type：SELECT 关键字对应的那个查询的类型，即select_type 的属性；

table：表名partitions，匹配的分区信息；
type：针对单表的访问方法，具体值看type访问方法/访问类型；
possible_keys：可能用到的索引；
key：实际上使用的索引；
key_len：实际使用到的索引长度 ；
ref：当使用索引列等值查询时，与索引列进行等值匹配的对象信息 ；
rows：预估的需要读取的记录条数 ；
filtered：某个表经过搜索条件过滤后剩余记录条数的百分比 ；
Extra：—些额外的信息 。可以参考EXPLAIN Output Format

select_type 的属性：

SIMPLE：简单的 select 查询，不使用 union 及子查询；

PRIMARY：最外层的 select 查询；

UNION：UNION 中的第二个或随后的 select 查询，不 依赖于外部查询的结果集；

UNION RESULT：UNION 结果集；

SUBQUERY：子查询中的第一个 select 查询，不依赖于外部查询的结果集；

DEPENDENT UNION：UNION 中的第二个或随后的 select 查询，依赖于外部查询的结果集；

DEPENDENT SUBQUERY：子查询中的第一个 select 查询，依赖于外部查询的结果集；

DERIVED： 用于 from 子句里有子查询的情况。 MySQL 会递归执行这些子查询，把结果放在临时表里；

MATERIALIZED：物化子查询，即子查询的结果通常缓存在内存或临时表中；

UNCACHEABLE SUBQUERY：结果集不能被缓存的子查询，必须重新为外层查询的每一行进行评估，出现极少。

UNCACHEABLE UNION：UNION 中的第二个或随后的select 查询，属于不可缓存的子查询，出现极少。

type访问方法/访问类型：

system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

system：当表中只有一条记录并且该表使用的存储引擎的统计数据是精确的，system 是 const 类型的特例

const：就是当我们根据主键或者唯一级索引列与常数进行等值匹配时，对单表的访问方法就是 const。因为只匹配一行数据，所以很快。

eq_ref：在连接查询时，如果被驱动表是通过主键或者唯一二级索引列等值匹配的方式进行访问的（如果该主键或者唯一二级索引是联合索引的话，所有的索引列都必须进行等值比较），则对该被驱动表的访问方法就是 eq_ref。比如A 表和 B 表 join 连接查询，如果通过 A 表的结果集作为循环基础数据，然后一条一条地通过该结果集中的数据作为过滤条件到 B 表中查询数据，然后合并结果。那么我们称 A 表为驱动表，B 表为被驱动表。

ref：当通过普通的二级索引列与常量进行等值匹配时来查询某个表，那么对该表的访问方法就可能是 ref

ref_or_null：有时候我们不仅想找出某个二级索引列的值等于某个常数的记录，还想把该列的值为NULL的记录也找出来

index_merge：一般情况下对于某个表的查询只能使用到一个索引，在某些场景下可以使用索引合并的方式来执行查询

unique_subquery：该类型和 eq_ref 类似，但是使用了 IN 查询，且子查询是主键或者唯一索引

index_subquery：和 unique_subquery 类似，只是子查询使用的是非唯一索引

range：如果使用索引获取某些范围区间的记录，那么就可能使用到 range 访问方法，一般就是在你的 where 语句中出现了 >、>=、<、<=、IS NULL、<=>、BETWEEN、LIKE、IN() 等的查询

index：当我们可以使用索引覆盖，但需要扫描全部的索引记录时，该表的访问方法就是 index

ALL：全表扫描，遍历全表以找到匹配的行，性能最差

SQL常见的优化方案

1、对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引。

2、应尽量避免在 where 子句中使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描。

3、应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描，如：
select id from t where num is null
可以在num上设置默认值0，确保表中num列没有null值，然后这样查询：
select id from t where num=0

4、应尽量避免在 where 子句中使用 or 来连接条件，否则将导致引擎放弃使用索引而进行全表扫描，如：
select id from t where num=10 or num=20
可以这样查询：
select id from t where num=10
union all
select id from t where num=20

5、下面的查询也将导致全表扫描：
select id from t where name like ‘%abc%’
若要提高效率，可以考虑全文检索。

6、in 和 not in 也要慎用，否则会导致全表扫描，如：
select id from t where num in(1,2,3)
对于连续的数值，能用 between 就不要用 in 了：
select id from t where num between 1 and 3

7、如果在 where 子句中使用参数，也会导致全表扫描。因为SQL只有在运行时才会解析局部变量，但优化程序不能将访问计划的选择推迟到运行时；它必须在编译时进行选择。然而，如果在编译时建立访问计划，变量的值还是未知的，因而无法作为索引选择的输入项。如下面语句将进行全表扫描：
select id from t where num=@num
可以改为强制查询使用索引：
select id from t with(index(索引名)) where num=@num

8、应尽量避免在 where 子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描。如：
select id from t where num/2=100
应改为:
select id from t where num=100*2

9、应尽量避免在where子句中对字段进行函数操作，这将导致引擎放弃使用索引而进行全表扫描。如：
select id from t where substring(name,1,3)=‘abc’–name以abc开头的id
select id from t where datediff(day,createdate,‘2005-11-30’)=0–'2005-11-30’生成的id
应改为:
select id from t where name like ‘abc%’
select id from t where createdate>=‘2005-11-30’ and createdate<‘2005-12-1’

10、不要在 where 子句中的“=”左边进行函数、算术运算或其他表达式运算，否则系统将可能无法正确使用索引。

11、在使用索引字段作为条件时，如果该索引是复合索引，那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引，否则该索引将不会被使用，并且应尽可能的让字段顺序与索引顺序相一致。

12、不要写一些没有意义的查询，如需要生成一个空表结构：
select col1,col2 into #t from t where 1=0
这类代码不会返回任何结果集，但是会消耗系统资源的，应改成这样：
create table #t(…)

13、很多时候用 exists 代替 in 是一个好的选择：
select num from a where num in(select num from b)
用下面的语句替换：
select num from a where exists(select 1 from b where num=a.num)

14、并不是所有索引对查询都有效，SQL是根据表中数据来进行查询优化的，当索引列有大量数据重复时，SQL查询可能不会去利用索引，如一表中有字段sex，male、female几乎各一半，那么即使在sex上建了索引也对查询效率起不了作用。

15、索引并不是越多越好，索引固然可以提高相应的 select 的效率，但同时也降低了 insert 及 update 的效率，因为 insert 或 update 时有可能会重建索引，所以怎样建索引需要慎重考虑，视具体情况而定。一个表的索引数最好不要超过6个，若太多则应考虑一些不常使用到的列上建的索引是否有必要。

16、应尽可能的避免更新 clustered 索引数据列，因为 clustered 索引数据列的顺序就是表记录的物理存储顺序，一旦该列值改变将导致整个表记录的顺序的调整，会耗费相当大的资源。若应用系统需要频繁更新 clustered 索引数据列，那么需要考虑是否应将该索引建为 clustered 索引。

17、尽量使用数字型字段，若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，并会增加存储开销。这是因为引擎在处理查询和连接时会逐个比较字符串中每一个字符，而对于数字型而言只需要比较一次就够了。

18、尽可能的使用char/nchar 代替 varchar/nvarchar ，因为首先变长字段存储空间小，可以节省存储空间，其次对于查询来说，在一个相对较小的字段内搜索效率显然要高些。

19、任何地方都不要使用 select * from t ，用具体的字段列表代替“*”，不要返回用不到的任何字段。

20、尽量使用表变量来代替临时表。如果表变量包含大量数据，请注意索引非常有限（只有主键索引）。

21、避免频繁创建和删除临时表，以减少系统表资源的消耗。

22、临时表并不是不可使用，适当地使用它们可以使某些例程更有效，例如，当需要重复引用大型表或常用表中的某个数据集时。但是，对于一次性事件，最好使用导出表。

23、在新建临时表时，如果一次性插入数据量很大，那么可以使用 select into 代替 create table，避免造成大量 log ，以提高速度；如果数据量不大，为了缓和系统表的资源，应先create table，然后insert。

24、如果使用到了临时表，在存储过程的最后务必将所有的临时表显式删除，先 truncate table ，然后 drop table ，这样可以避免系统表的较长时间锁定。

25、尽量避免使用游标，因为游标的效率较差，如果游标操作的数据超过1万行，那么就应该考虑改写。

26、使用基于游标的方法或临时表方法之前，应先寻找基于集的解决方案来解决问题，基于集的方法通常更有效。

27、与临时表一样，游标并不是不可使用。对小型数据集使用 FAST_FORWARD 游标通常要优于其他逐行处理方法，尤其是在必须引用几个表才能获得所需的数据时。在结果集中包括“合计”的例程通常要比使用游标执行的速度快。如果开发时间允许，基于游标的方法和基于集的方法都可以尝试一下，看哪一种方法的效果更好。

28、在所有的存储过程和触发器的开始处设置 SET NOCOUNT ON ，在结束时设置 SET NOCOUNT OFF 。无需在执行存储过程和触发器的每个语句后向客户端发送 DONE_IN_PROC 消息。

29、尽量避免向客户端返回大数据量，若数据量过大，应该考虑相应需求是否合理。

30、尽量避免大事务操作，提高系统并发能力 

31、优化LIMIT。我们通常会使用 LIMIT 加上偏移量的办法实现，当偏移量非常大的时候，例如：

 select * from table limit 10000,10;

这个查询数据量很大，即10010 条数据，然后要最后10条，代价很高。如果我们这样写：

SELECT * FROM (select id from table limit 10000,10) b,table a where a.id = b.id;

它会先查询翻页中需要的 N 条数据的主键值，然后根据主键值回表查询所需要的 N 条数据，在此过程中查询 N 条数据的主键 id 在索引中完成，所以效率会高一些

SQL优化方案总结

索引的优化

索引的优化主要通过下面的方法进行优化：

前缀索引优化

覆盖索引优化

主键索引最好是自增的

防止索引失效

前缀索引优化

前缀索引就是在某个字段的前几个字符建立索引。它可以减小索引字段大小，可以增加一个索引页中存储的索引值，有效提高索引的查询速度。但是前缀索引有一定的局限性，例如:

order by 就无法使用前缀索引

无法把前缀索引用作覆盖索引

覆盖索引优化

覆盖索引就是在二级索引B+Tree  的叶子节点上都能找得到的那些记录数据，不需要通过通过聚簇索引进行回表。所以，使用覆盖索引的好处就是，不需要查询出包含整行记录的所有信息，也就减少了大量的 I/O 操作。

主键索引最好是自增的

为什么主键索引最好是自增的呢？

InnoDB 创建主键索引默认为聚簇索引，数据被存放在了 B+Tree 的叶子节点上。也就是说，同一个叶子节点内的各个数据是按主键顺序存放的，因此，每当有一条新的数据插入时，数据库会根据主键将其插入到对应的叶子节点中。

如果我们使用自增主键，那么每次插入的新数据就会按顺序添加到当前索引节点的位置，不需要移动已有的数据，当页面写满，就会自动开辟一个新页面。因为不需要重新移动数据，因此这种插入数据的方法效率非常高。

如果我们使用非自增主键，由于每次插入主键的索引值都是随机的，因此每次插入新的数据时，就可能会插入到现有数据页中间的某个位置，这将不得不移动其它数据来满足新数据的插入，甚至需要从一个页面复制数据到另外一个页面，我们通常将这种情况称为页分裂。页分裂还有可能会造成大量的内存碎片，导致索引结构不紧凑，从而影响查询效率。

举个例子，假设某个数据页中的数据是1、3、5、9，且数据页满了，现在准备插入一个数据7，则需要把数据页分割为两个数据页：

innodb页分裂

出现页分裂时，需要将一个页的记录移动到另外一个页，性能会受到影响，同时页空间的利用率下降，造成存储空间的浪费。

而如果记录是顺序插入的，例如插入数据11，则只需开辟新的数据页，也就不会发生页分裂：

开辟新数据页

因此，在使用 InnoDB 存储引擎时，如果没有特别的业务需求，建议使用自增字段作为主键

防止索引失效

 并不是建立了索引就会用上索引，所以我们要清楚索引失效的情况，如下：

• 当我们使用左或者左右模糊匹配的时候，也就是 like %xx 或者 like %xx%这两种方式都会造成索引失效；

• 当我们在查询条件中对索引列做了计算、函数、类型转换操作，这些情况下索引失效是因为查询过程需要扫描整个索引并回表，代价高于直接全表扫描，所以优化最终选择走全表扫描。

• 联合索引要能正确使用需要遵循最左匹配原则，也就是按照最左优先的方式进行索引的匹配，否则就会导致索引失效。

• 在 WHERE 子句中，如果在 OR 前的条件列是索引列，而在 OR 后的条件列不是索引列，那么索引会失效。

• 为了更好的利用索引，索引列要设置为 NOT NULL 约束

sql语句的优化

• 查询时，能不要*就不用*，尽量写全字段名
• 大部分情况连接效率远大于子查询
• 多使用explain和profile分析查询语句
• 查看慢查询日志，找出执行时间长的sql语句优化
• 多表连接时，尽量小表驱动大表，即小表 join 大表
• 在千万级分页时使用limit
• 对于经常使用的查询，可以开启缓存

表的优化

• 表的字段尽可能用NOT NULL
• 字段长度固定的表查询会更快
• 把数据库的大表按时间或一些标志分成小表
• 将表分区
• 水平切分和垂直切分，即（分库分表，读写分离）

MySQL数据库配置优化

数据库配置优化

  公共参数默认值：

max_connections = 151

#同时处理最大连接数，推荐设置最大连接数是上限连接数的80%左右   

sort_buffer_size = 2M

#查询排序时缓冲区大小，只对order by和group by起作用，可增大此值为16M

open_files_limit = 1024 

#打开文件数限制，如果show global status like 'open_files'查看的值等于或者大于open_files_limit值时，程序会无法连接数据库或卡死

MyISAM参数默认值：

key_buffer_size = 16M

#索引缓存区大小，一般设置物理内存的30-40%

read_buffer_size = 128K  

#读操作缓冲区大小，推荐设置16M或32M

query_cache_type = ON

#打开查询缓存功能

query_cache_limit = 1M  

#查询缓存限制，只有1M以下查询结果才会被缓存，以免结果数据较大把缓存池覆盖

query_cache_size = 16M  

#查看缓冲区大小，用于缓存SELECT查询结果，下一次有同样SELECT查询将直接从缓存池返回结果，可适当成倍增加此值

  InnoDB参数默认值： 

innodb_buffer_pool_size = 128M

#索引和数据缓冲区大小，一般设置物理内存的60%-70%

innodb_buffer_pool_instances = 1    

#缓冲池实例个数，推荐设置4个或8个

innodb_flush_log_at_trx_commit = 1  

#关键参数，0代表大约每秒写入到日志并同步到磁盘，数据库故障会丢失1秒左右事务数据。1为每执行一条SQL后写入到日志并同步到磁盘，I/O开销大，执行完SQL要等待日志读写，效率低。2代表只把日志写入到系统缓存区，再每秒同步到磁盘，效率很高，如果服务器故障，才会丢失事务数据。对数据安全性要求不是很高的推荐设置2，性能高，修改后效果明显。

innodb_file_per_table = OFF  

#默认是共享表空间，共享表空间idbdata文件不断增大，影响一定的I/O性能。推荐开启独立表空间模式，每个表的索引和数据都存在自己独立的表空间中，可以实现单表在不同数据库中移动。

innodb_log_buffer_size = 8M  

#日志缓冲区大小，由于日志最长每秒钟刷新一次，所以一般不用超过16M

系统内核参数优化

 大多数MySQL都部署在linux系统上，所以操作系统的一些参数也会影响到MySQL性能，以下对linux内核进行适当优化

net.ipv4.tcp_fin_timeout = 30

#TIME_WAIT超时时间，默认是60s

net.ipv4.tcp_tw_reuse = 1    

#1表示开启复用，允许TIME_WAIT socket重新用于新的TCP连接，0表示关闭

net.ipv4.tcp_tw_recycle = 1  

#1表示开启TIME_WAIT socket快速回收，0表示关闭

net.ipv4.tcp_max_tw_buckets = 4096   

#系统保持TIME_WAIT socket最大数量，如果超出这个数，系统将随机清除一些TIME_WAIT并打印警告信息

net.ipv4.tcp_max_syn_backlog = 4096

#进入SYN队列最大长度，加大队列长度可容纳更多的等待连接

总结 

这篇文章主要从慢查询日志，慢查询日志分析，explain分析SQL，SQL常见优化方法，索引优化，表优化，数据和系统内核参数配置等多方面分析mysql的优化。希望能帮助到读者，感谢阅读~