mysql5.6性能优化

1、何为优化

合理的安排系统资源、调整系统参数使mysql运行的更快、更节省资源。

优化包含：查询优化、更新、mysql服务器等各个方面。

原则：减少系统瓶颈、减少资源占用，增加系统反应速度。

2、mysql性能参数

         show status；查看所有的参数，show status like  ‘Slow_queries’查询其中一个参数；

    常用的参数：

                • Slow_queries  慢查询次数

                • Com_(CRUD) 操作的次数

                • Uptime  上线时间

3、查询优化

    1、执行计划 EXPLAIN 查看SQL的执行计划。

               用法：EXPLAIN SELECT * FROM TABLE;

            

      2、执行计划说明

             id： selct识别符，select查询序列号。不重要。

             select_type: 表示select语句的类型。

                     有以下值：

SIMPLE	表示简单查询，其中不包含连接查询和子查询。
PRIMARY	表示主查询，或者最外边的查询
UNION	表示连接查询的第2个或后面的查询语句。
DEPENDENT UNION	UNION中的第二个或后面的SELECT语句，取决于外面的查询。
UNION RESULT	连接查询的结果
SUBQUERY	子查询中的第1个SELECT语句。
DEPENDENT SUBQUERY	子查询中的第1个SELECT语句，取决于外面的查询。
DERIVED	SELECT(FROM 子句的子查询)。

               table:          查询的库表名。

              type（重要）：    表示表的连接类型，性能优化的主要参考。

                以下连接类型的顺序是从最佳类型到最差类型：

     system：表仅有一行，这是const类型的特例，平时不出现，可以忽略不计。

     const：数据表最多只有一个匹配行，因为只匹配一行数据，所以很快。常用于主键查询或者UNIQUE索引查询。可以理解const是最优的。

      eq_ref:  

    mysql手册是这样说的:"对于每个来自于前面的表的行组合，从该表中读取一行。这可能是最好的联接类型，除了const类 型。它用在一个索引的所有部分被联接使用并且索引是UNIQUE或PRIMARY KEY"。eq_ref可以用于使用=比较带索引的列。

                

            ref:

    查询条件索引既不是UNIQUE也不是PRIMARY KEY的情况。ref可用于=或<或>操作符的带索引的列。

            ref_or_null:

        该联接类型如同ref，但是添加了MySQL可以专门搜索包含NULL值的行。在解决子查询中经常使用该联接类型的优化。

    上面这五种情况都是很理想的索引使用情况。

        1、 index_merge

    该联接类型表示使用了索引合并优化方法。在这种情况下，key列包含了使用的索引的清单，key_len包含了使用的索引的最长的关键元素。

        2、 unique_subquery
    该类型替换了下面形式的IN子查询的ref: value IN (SELECT primary_key FROM single_table WHERE some_expr)
unique_subquery是一个索引查找函数,可以完全替换子查询,效率更高。

        3、 index_subquery
    该联接类型类似于unique_subquery。可以替换IN子查询,但只适合下列形式的子查询中的非唯一索引: value IN (SELECT key_column FROM single_table WHERE some_expr)

        4、 range
    只检索给定范围的行,使用一个索引来选择行。

        5、 index
    该联接类型与ALL相同,除了只有索引树被扫描。这通常比ALL快,因为索引文件通常比数据文件小。

        6、 ALL
    对于每个来自于先前的表的行组合,进行完整的表扫描。（性能最差）

        

 possible_keys:  

    指出MySQL能使用哪个索引在该表中找到行。

    如果该列为NULL，说明没有使用索引，可以对该列创建索引来提高性能。

key

显示MySQL实际决定使用的键(索引)。如果没有选择索引,键是NULL。

可以强制使用索引或者忽略索引：

key_len：

显示MySQL决定使用的键长度。如果键是NULL,则长度为NULL。

注意：key_len是确定了MySQL将实际使用的索引长度。

rows

显示MySQL认为它执行查询时必须检查的行数。

Extra：

包含mysql解决查询的详细信息；

3、使用索引需要注意的地方

        

      模糊查询，模糊前置会导致索引失效。

      联合索引只有查询条件中使用了第一个字段，索引才会生效。

      OR关键字前后都是索引字段，索引才会生效，否则不生效。

 4、子查询优化

        

MySQL从4.1版本开始支持子查询，使用子查询进行SELECT语句嵌套查询，可以一次完成很多逻辑上需要多个步骤才能完成的SQL操作。

子查询虽然很灵活，但是执行效率并不高。

执行子查询时，MYSQL需要创建临时表，查询完毕后再删除这些临时表，所以，子查询的速度会受到一定的影响。

优化：

可以使用连接查询（JOIN）代替子查询，连接查询时不需要建立临时表，其速度比子查询快。

4、存储引擎

Mysql在V5.1之前默认存储引擎是MyISAM；在此之后默认存储引擎是InnoDB

插入数据优化：

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

MYISAM：

1.1.1. 禁用索引

对于非空表，插入记录时，MySQL会根据表的索引对插入的记录建立索引。如果插入大量数据，建立索引会降低插入数据速度。

为了解决这个问题，可以在批量插入数据之前禁用索引，数据插入完成后再开启索引。

禁用索引的语句：

ALTER TABLE table_name DISABLE KEYS

开启索引语句：

ALTER TABLE table_name ENABLE KEYS

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

1.1.2. 禁用唯一性检查

唯一性校验会降低插入记录的速度，可以在插入记录之前禁用唯一性检查，插入数据完成后再开启。

禁用唯一性检查的语句：SET UNIQUE_CHECKS = 0;

开启唯一性检查的语句：SET UNIQUE_CHECKS = 1;

1.1.3. 批量插入数据

1.1.4. 使用LOAD DATA INFILE

当需要批量导入数据时，使用LOAD DATA INFILE语句比INSERT语句插入速度快很多

做大量数据导入的时候使用。

innoDB

1.1.1. 禁用唯一性检查

用法和MyISAM一样。

1.1.2. 禁用外键检查

插入数据之前执行禁止对外键的检查，数据插入完成后再恢复，可以提供插入速度。

禁用：SET foreign_key_checks = 0;

开启：SET foreign_key_checks = 1;

1.1.3. 禁止自动提交

插入数据之前执行禁止事务的自动提交，数据插入完成后再恢复，可以提高插入速度。

禁用：SET autocommit = 0;

开启：SET autocommit = 1;