MySQL优化一方面是找出系统的瓶颈，提高MySQL数据库整体的性能；另一方面需要合理的结构设计和参数调整，以提高用户操作响应的速度；同时还要尽可能节省系统资源，以便系统可以提供更大负荷非服务

　　MySQL常见的瓶颈：

• CPU：读写数据

• IO:写入数据大于内存时

• 服务器

一、优化查询

1.1、分析查询语句

explain [extended] select select_options

expalian查询结果字段解释

• id：select标识符，select的查询序列号

• select_type:表示select语句的类型。

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

  • PRIMARY：主查询，或者是最外层的查询

  • UNION：连接查询的第2个，或者后面的查询语句**

  • DEPENDENT** UNION：连接查询中的第2个或者后面的SELECE语句，取决于外面的查询

  • UNION RESULT：连接查询的结果

  • SUBQUERY：子查询中的第一个SELECT语句

  • DEPENDENT SUBQUERY：子查询中的第一个SELECT,取决于外面的查询

  • DERIVED：导出表的SELECT

• table：查询的表

• type：连接的类，下面是从最佳到最差,一般来说达到range级别，最好达到ref级别

system > const > eq_rf > ref > range > index > ALL

• • system：表是仅有一行记录的表

  • const：数据表最多只有一个匹配行，它将在查询开始被读取，并在余下的查询优化中作为常量对待。const表的查询速度很快，因为它们读取一次。const用于使用常数值比较PRIMARY KEY或UNIQUE 索引的所有部分的场合。

  • eq_ref: 唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一性扫描

  • ref：非唯一性索引扫描，返回匹配某个单独值的所有行，本质也是一种索引访问，它返回所有匹配某个值的行，然而，他可能会找到多个符合条件的行，所以他应该属于查找和扫描的混合体

  • Range: 只检索给定返回的行，使用一个索引来选择行，key列显示使用了那个索引，一般就是你的where语句中出现 between、<、> 、in等查询这种返回扫描索引扫描比全表扫描要好，因为只需要开始于索引的某一个点，而结束语句另一个点，不用扫描全部索引

  • Index: FUll Index Scan 与ALL区别为index类型只遍历索引树，这通常比All快，因为索引文件通常比数据文件小(也就是说虽然all和index都是读全表，但是index是从索引中读取的，而all是从硬盘中读的)

  • All: full Table scan 将遍历全表以找到匹配的行。

• possible_keys:显示可能应用在这张表中的索引，一个或多个，查询涉及到的字段上若存在索引，则索引被列出，但不一定被查询实际使用

• key：查询实际使用到的索引.

• key_len:表示选择的索引字段按字节计算的长度,越短越好

• rows：显示MySQL在表中进行查询时必须检查的行数

• Extra：表示MySQL在处理查询时的详细信息

  • Using filesort :mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。(文件内排序)

  • Using temporary：使用临时表保1存中间结果，MySQL在对查询结果排序时使用临时表，常见于排序order by和分组查询 group by和分组查询

  • using index:表示相应的select操作中使用了覆盖索引，避免访问了表的数据行，效率不错，如果同时出现using where 表明索引被用来执行索引键值的查找如果没有同时出现using where 表明索引用来读取数据而非执行查找动作

  • using where:表示使用where过滤

  • using join buffer 使用连接缓存

  • impossible where where子句的值总是false，不能用来获取任何元组

  • select tables optized away:在没有GROUP BY子句的情况下，基于索引优化MIN/MAX操作或者对象MyISAM存储引擎优化COUNT(*)操作。不必等到执行阶段在运行计算查询执行计划生成的阶段即可完成优化

  • distinct:优化distinct操作。在找到第一个匹配的元组后即停止找同样值的操作

覆盖索引：

理解方式1：就是select的数据列只用从索引中就能取得，不比读取数据行，MySQL可以利用索引返回select列表中的字段，而不必根据索引再次读取数据文件，换句话说查询列要被所创建的索引覆盖。

DESCRIBE SELECT select_options和EXPLAIN用法一样

二、索引使用原则

1.全值匹配
2.最佳左前缀法则：最左边的索引字段不能丢失
3.不在索引列上左任何操作，
4.存储引擎不能使用索引中范围条件右边的列（返回之后全失效）
5.尽量使用覆盖索引，减少select *
6.mysql在使用不等于（!= 或者<>）的时候无法使用索引，导致全表扫描
7.is null,is not null 也无法使用索引
8.like已统配符开头（‘%abc‘）所有失效，全表扫描
9.字符串不加单引号，索引失效
10.少用or，用它连接时索引失效。

总结：假设index(a,b,c)

2.1、使用索引查询

索引并不起作用场景。

1. 使用LIKE关键字的查询语句 如果匹配字符串的第一个字符为“%”,索引不会起作用

2. 使用多列索引的查询语句 MySQL可以创建多字段的索引。一个索引可以包括16个字段，对于多列索引，只有查询条件中使 用这些字段中第一个字段时，索引才会被使用。

3. 适用OR关键字的查询语句 OR前后的两个条件中的列都是索引时，查询中才使用索引。否则使用不到索引

2.2、优化子查询

　　子查询虽然可以使查询语句灵活，但是执行效率不高。执行子查询时，MySQL需要为内层查询语句的查询结果建立一个临时表，因此，子查询的速度会受到一定的影响。 连接查询不需要建立临时表，其速度比子查询更快，如果查询中使用索引的话，性能会更好。

三、优化数据库结构

3.1、将字段很多的表分解成多个表

对于字段多的表，如果有些字段的使用频率很低，可以将这些字段分离出来形成新的表

3.2、增加中间表

对于需要经常联合查询的表，可以建立中间表以提高查询效率，通过建立中间表，把需要经常联合查询的数据插入到中间表中，然后将原来的联合查询改为对中间表的查询 。

3.3、增加冗余字段

3.4、优化插入记录的速度

插入数据时，影响插入速度的主要是索引、唯一性效验、一次插入记录条数等

禁用索引

• 对于非空表，插入记录时，MySQL会根据表的索引对插入的记录建立索引。如果插入大量数据，建立索引会影响插入记录的速度。我们可以在插入记录之前禁用索引， 数据插入完毕之后开启索引

-- 禁用索引的语法：
ALTER TABLE table_name DISABLE KEYS

-- 重新开启索引的语法
ALTER TABLE table_name ENABLE KEYS;

对于空表批量导入数据，则不需要进行此操作，因为MyISAM引擎的表是在导入数据之后建立索引 。

禁用唯一性检查

插入数据时，MySQL会对插入的记录进行唯一性效验。这种唯一性校验也会降低插入记录的速度。

-- 禁用语法：
SET UNIQUE_CHECKS=0

-- 开启语法：
SET UNIQUE_CHECKS=1;

使用LOAD DATE INFILE 批量导入

--  对于InnoDB引擎的表：
-- 1.禁止唯一性检查
-- 2.禁止外键检查
        SET foreign_key_checks=0;
        SET foreign_key_checks=1;
-- 3.禁止自动提交
        SET autocommit =0;
        set autocommit=1;

2.5、分析表、检查表和优化表

• 分析表主要是分析关键字的分布

• 查检表主要是检查表是否存在错误

• 优化表主要是消除删除或者更新造成的空间浪费

(1)、分析表

ANALYZE  [local|No_WRITE_TO_BINLOG]  TABLE  tab_name [,tab_name] ...

• local:是关键字，No_WRITE_TO_BINLOG：关键字别名，二者都是执行过程中不写入二进制日志ANALYZE TABLE分析表的过程中，数据库系统会自动对表加一个只读锁。不能插入数据。

• ANALYZE TABLE语句能够分析InnoDB、DBD和MyISAM类型的表 。

(2)、检查表

　　MySQL中使用CHECK TABLE语句来检查表。CHECK TABLE语句能够检查InnoDB和MyISAM类型的表是否存在错误，对于MyISAM类型的表，CHECK TABLE语句还有更新关键字统计数据。而且，CHECK TABLE也可以检查视图是否有错误。

-- 语法
CHECK TABLE tbl_name [,tab_name] ....[option]...

option={QUICK|FAST|MEDIUM|EXTENDED|CHANGED}

• QUICK:不扫描行，不检查错误的连接

• FAST：只检查没有被正确关闭的表

• CHANGED：只检查上次检查后被更改的表和没有别正确关闭的表

• MEDIUM：扫描行，已验证被删除的连接是有效的。也可以计算各行的关键字效验和，并使用计算出的校验和验证这一点

• EXTENDED:对每行的所有关键字践行一个全面的关键字查找，这可以确保表示100%一致的。耗时长.

(3)、优化表

optimize TABLE语句来优化表，但是只能优化表中VARCHAR、BLOB或者TEXT类型的字段。

-- 语法：
OPTIMIZE  [LOCAL|No_WRITE_TO_BINLOG] TABLE tab_name [,tab_name] ...

四、优化MySQL服务器

一方面是对硬件的优化，另一方面是对MySQL服务器参数进行优化。

4.1、优化服务器硬件

• 配置较大的内存。通过增加系统的缓冲区容量，使数据在内存停留的时间更长，以减少磁盘I\O

• 配置高速磁盘系统，以减少读盘的等待时间，提高响应速度

• 合理分布磁盘I/O，把磁盘分散在多个设备上，以减少资源竞争，提高并行操作能力

• 配置多处理器，MySQL是多线程的数据库。多处理器可同时执行多个线程

4.2、优化参数

• key_buffer_size:表示索引缓冲区的大小，索引缓冲区所有的线程共享。增加索引缓冲区可以得到更好处理的索引

• table_cache:表示同时打开表的个数

• query_cache_size:表示查询缓冲区的大小，该参数需要query_cache_type配合使用当query_cache_type 值是0时，所有的查询都不使用查询缓冲区。但是query_cache_type=0并不会导致MySQL释放query_cache_size所配置的缓冲区内存。当query_cache_type=1时，所有的查询都将使用查询缓冲区，除非在查询语句中指定 SOL_NO_CACHE，如SELECT NO_SQL_CACHE * from table.当query_cache_type=2时，只有在查询语句中使用SQL_CACHE关键字，查询才会使用查询缓冲区，使用查询缓冲区可以提高查询速度，这种方式只适应于修改操作少且经常执行相同的查询操作的情况

• sort_buffer_size：表示排序缓冲区的大小，这个值越大，进行拍新的速度越快

• read_buffer_size：表示线程连续扫描时为扫描每个表分配的缓冲区大小

• read_rnd_buffer_size：表示为每个线程保留的缓冲区大小。主要用于存储安特定存出顺序读取出来的记录

• innodb_buffer_pool_size：表示innodb类型的表和索引的最大缓存。这个值越大，查询的速度就会越快。

• max_connections：数据库最大连接数

• InnoDB_flush_log_at_trx_commit:表示何时将缓冲区的数据写入日志文件，并且将日志文件写入磁盘，该参数有3个值：0、1、2。0：表示每隔一秒将数据写入日志文件并将日志文件写入磁盘。值为1时，表示每次提交事务时将数据写入日志文件并将日志文件写入磁盘。值为2时，表示每次提交事务时将数据写入日志文件，每个1秒将日志文件写入磁盘，默认值为1

• back_log:表示对到来的TCP/IP连接的侦听队列的大小

• interactive_timeout:表示服务器在关闭连接前等待的秒数

• thread_cache_size：表示可以复用的线程的数量。如果有很多新的线程，为了提高性能可以增大该参数的值。

• `sort_buffer_size``：表示每个需要进行排序的线程分配的缓冲区的大小。增加这个参数的值可以提高ORDER BY或者GROUP BY 操作的速度，默认值为2M。

十六、性能优化

原文：https://www.cnblogs.com/jdy1022/p/14028587.html