Mysql优化

Mysql优化可以从 查询、数据库结构、插入数据优化、Mysql服务器配置 入手。

一、查询优化：

1.使用explain查看执行计划

 结果说明:

1)id    select识别符

2)select_type    表示SELECT语句的类型

       SIMPLE     表示简单查询，其中不包含连接查询和子查询。

    PRIMARY    表示主查询，或者是最外面的查询语句。

    UNION    表示连接查询的第2个或后面的查询语句。

    DEPENDENT UNION    UNION中的第二个或后面的SELECT语句，取决于外面的查询。

    UNION RESULT    连接查询的结果。

    SUBQUERY    子查询中的第1个SELECT语句。

    DEPENDENT SUBQUERY    子查询中的第1个SELECT语句，取决于外面的查询。

    DERIVED    SELECTFROM 子句的子查询。

3)table    表示要查询的表

4)type（重要）    表示表的连接类型

    从最佳类型到最差类型

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

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

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

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

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

   以上五种是连接使用索引较理想的情况:

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

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

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

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

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

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

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

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

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

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

8)ref    显示使用哪个列或常数与key一起从表中选择行。

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

10)extra    该列包含MySQL解决查询的详细信息

使用索引需要注意

1)like '%张%'

%不在匹配字符的第一个位置，索引才生效。

2)使用联合索引查询时，一个索引包含多个字段，查询条件使用了这个索引这些字段的第一个字段时，索引才生效。

3）or 关键字查询时 or 两边都是索引，索引才生效

子查询优化

mysql 支持子查询完成复杂逻辑

但是执行子查询时，需要创建临时表，查询完毕后删除，所以子查询的速度会受到影响。

可以使用join代替子查询，连接查询不需要创建临时表，比子查询要快。

总结:

    mysql查询时，避免使用子查询（子查询会创建临时表，查询完毕后删除临时表，影响查询效率），使用join代替子查询提高效率。

    为表建立合适的索引，利用好索引，优化查询，避免全表扫描。对于单表或者关联查询，尽量使用主键索引或者唯一索引。

    用exlpain查看执行计划，避免有些没有使用到索引的情况，比如like '%a%'  %在第一个位置，联合索引时要使用第一个字段才会使索引生效，or关键字两边都是索引字段才会使索引生效。

二、数据库优化

需要考虑数据冗余、查询和跟新的速度、字段数据类型是否合理等多方面内容。

1)将字段多的表分解成几个表。

  一张表数据量大时大量的字段会导致查询效率变低，对于字段非常多的表，但是表里的有些字段是低频使用的字段，可以分离出来形成新表。

2）增加中间表

    对于经常要使用联合查询的表，可以建立中间表提高查询效率。

    通过建立中间表把需要联合查询的数据插入的中间表，把联合查询转换为对中间表的查询。

3）合理增加冗余字段

    表与表之间联合查询情况多，会导致查询效率低，可以增加适当的冗余字段提高查询效率。

三、插入数据优化

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

    对于两种存储引擎,插入优化的方案不一样。MyISAM是mysql的默认存储引擎，基于传统的ISAM类型，支持全文搜索，但是不是事务安全的，而且不支持外键。InnoDB是事务型引擎,支持回滚、事务崩溃恢复、并发。行级锁（InnoDB行级锁并不是绝对的，如果执行sql语句时，不能确定扫描范围，如like同样会锁全表）

    区别：

    MyISAM是非事务安全的、InnoDB是事务安全的。

    MyISAM锁表粒度是全表、InnoDB支持行级锁。

    MyISAM支持全文索引,InnoDB不支持全文索引。

    MyISAM相对简单,效率上优于InnoDB，小型应用可以考虑使用。

    MyISAM保存成文件形式,跨平台转移数据比较方便。

    InnoDB表比MyISAM更安全，可以在数据不丢失的情况下，切换非事务表到事务表。

应用场景:

    MyISAM管理非事务表。提供高速的存储和检索，以及全文搜索能力。如果需要执行大量的select语句，那MyISAM是更好的选择。

    InnoDB使用与事务型应用程序。如果应用中存在大量的insert和update操作，应该使用innodb，可以提高数据库的并发能力。

MyISAM

1)禁用索引，插入大量数据时简历索引会消耗大量时间，数据插入完毕后再开启索引。 

禁用索引的语句：

ALTER TABLE table_name DISABLE KEYS

开启索引语句：

ALTER TABLE table_name ENABLE KEYS

 2）禁用唯一性检查，唯一性检查会降低插入记录的速度，可以在插入前禁用唯一性检查，插入后再开启。 

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

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

3）批量插入数据

插入数据时，可以使用一条INSERT语句插入一条数据，也可以插入多条数据。

4）使用load data in file

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

InnoDB

1)禁用唯一性检查，唯一性检查会降低插入记录的速度，可以在插入前禁用唯一性检查，插入后再开启。 

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

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

2)禁用外键检查，数据插入完毕后恢复外键检查。

禁用：SET foreign_key_checks = 0;

开启：SET foreign_key_checks = 1;

3）禁止自动提交

插入数据数据之前禁止自动提交，插入完毕后恢复。

禁用：SET autocommit = 0;

开启：SET autocommit = 1;

四、服务器优化

1.优化服务器硬件

从以下几方面考虑

1)配置较大的内存。内存io比硬盘快很多，增加系统的缓冲区容量，让数据再缓冲区停留更久，减少io。

2）配置高速磁盘，如ssd。

3)合理分配磁盘的io，分散到多个设备，减少资源竞争，提高并行能力。

4）配置多核处理器，mysql是多线程数据库，多处理器可以提高多线程能力。

2.优化mysql的参数

通过优化MySQL的参数可以提高资源利用率，从而达到提高MySQL服务器性能的目的。

MySQL的配置参数都在my.conf或者my.ini文件的[mysqld]组中，常用的参数如下：

key_buffer_size:表示索引缓冲区的大小。

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

query_cache_size:表示查询缓冲区的大小。

总结：从查询优化、数据库结构、插入数据优化、服务器优化4个方面。

查询优化：避免使用子查询（子查询建立临时表，用join）、建立合适索引（尽量使用唯一、主键）、注意使用不到索引的情况（like %在第一个字符、联合未使用第一个字段、or两边有非索引）。

数据库结构：考虑冗余字段、查询（更新）速度、字段数据类型。大表拆成多个表（低频字段）、建立中间表（经常使用的联合查询）、适当引入冗余字段（高频查询）。

插入数据优化：根据两种引擎，MyISAM 禁用索引检查、禁用唯一性检查、批量插入数据、load data infile比insert快；InnoDB引擎禁用唯一性检查、禁止外键检查、禁止事务提交。

mysq服务器优化：硬件和参数两方面，硬件：增加内存、使用高速磁盘ssd、合理分配磁盘io、配置多核处理器。参数设置，key_buffer_size（索引缓冲区大小）、table_cache(同时打开表的数量)、query_buffer_size(查询缓冲区大小)。