mysql查询优化

查询优化
在优化MySQL时，通常需要对数据库进行分析。常见的分析手段有慢查询日志，EXPLAIN分析查询，通过定位分析性能的瓶颈，才能更好的优化数据库系统的性能。

慢查询

1.1 慢查询日志开启
在配置文件my.cnf(linux)或者my.ini(windows)中在[mysqld]一行下面加入两个配置参数
log-slow-queries=/data/mysqldata/slow-query.log
long_query_time=5

注：log-slow-queries参数为慢查询日志存放的位置，一般这个目录要有mysql的运行账号的可写权限，一般都将这个目录设置为mysql的数据存放目录；
long_query_time=5 中的5表示查询超过5秒才记录

还可以在my.cnf或者my.ini中添加log-queries-not-using-indexes参数,表示记录下没有使用索引的查询。

1.2 慢查询分析
我们可以通过打开log文件查看得知哪些sql执行效率低下
从日志中，可以发现查询时间超过5秒的sql，而小于5秒的没有出现在些日志中。

如果慢查询日志中记录内容很多，可以使用mysqldumpslow工具（MySQL客户端安装自带）来对慢查询日志进行分类汇总。mysqldumpslow对日志文件进行了分类汇总，显示汇总后摘要结果。

进入log的存放目录，运行
[root]# mysqldumpslow slow-query.log

mysqldumpslow命令

/path/mysqldumpslow -s c -t 10 /database/mysql/slow-query.log
这会输出记录次数最多的10条sql语句，其中：

-s,表示是按何种方式排序，c t l r分别是按照记录次数、时间、查询时间、返回的记录数来排序，
ac at al ar，表示相应的倒序
-t,是top n的意思，即为返回前面多少条的数据；
-g,后面可以写一个正则匹配模式，大小写不敏感

例如：
/path/mysqldumpslow -s r -t 10 /database/mysql/slow-query.log
得到返回记录集最多的10个查询
/path/mysqldumpslow -s t -t 10 -g “left join" /database/mysql/slow-query.log
得到按照时间排序的前10条里面含有左连接的查询语句

使用mysqldumpslow命令可以非常明确的得到各种我们需要的查询语句，对Mysql查询语句的监控、分析、优化是mysql优化非常重要的一步。开启慢查询日志后，由于日志记录操作，在一定程序上会占用CPU资源影响mysql的性能，但是可以阶段性开启来定位性能瓶颈。

2 EXPLAIN
在mysql中可以使用explain查看sql执行计划，用法 explain select * from products;

2.1 id
select识别符。这是select查询序列号。这个不重要。

2.2 select_type
表示select语句的类型

例如：
1)、simple
表示简单查询，其中不包含连接查询和子查询。
2)、primary
表示主查询，或者是最外面的查询语句。
3)、union
表示连接查询的第2个或者后面的查询语句

2.3 table
表示查询的表。

2.4 type
表示表的连接类型。
以下的连接类型的顺序是从最佳类型到最差类型：

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

2)、const
数据表最多只有一个匹配行，因为只匹配一行数据，所以很快

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

4)、ref
查询条件索引即不是unique，也不是primary key的情况。ref可用于=或者<或>操作符的带索引的列。

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

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

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

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

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

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

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

11)、all
对于每个来自于先前的表的行组合，进行完整的表扫描。（性能最差）

2.5 possible_keys

批出mysql能使用哪个索引在该表中找到行。
如果该列为NULL，说明没有使用索引，可以对该列创建索引来提高性能。

2.6 key

显示mysql实际决定使用的键（索引）。如果没有选择索引，键是NULL。

2.7 key_len

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

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

2.8 ref
显示使用哪个列或者常数为key一起从表中选择行。

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

2.10 extra
该列包含mysql解决查询的详细信息。

3.1 创建索引

是否创建索引，几点原则：
较频繁的作为查询条件的字段应该创建索引；
唯一性太差的字段不适合单独创建索引，即使频繁作为查询条件；
更新非常频繁的字段不适合创建索引；
不会出现在where子名中的字段不该创建索引；

索引能够极大的提高数据检索效率，也能够改善排序分组操作的性能，但是我们不能忽略的一个问题就是索引是完全独立于基础数据之外的一部分数据，更新数据会带来IO量和调整索引所致的计算量的资源消耗。

3.2 使用索引

3.2.1 使用联合索引的查询
mysql可以为多个字段创建索引，一个索引可以包括16个字段。对于联合索引，只有查询条件中使用了这些字段中的第一个字段时，索引才生效。

3.2.2 使用 OR 关键字的查询
查询语句的查询条件中只有OR关键字，且OR前后的两个条件中列都是索引时，索引才会生效，否则，索引不生效。

MyISAM和InnoDB区别：前者不支持事务，查询快

MyISAM存储优化：

4.1 禁用索引
大量插入前禁用，插入完成后再开启

禁用索引的语句：
alter table table_name disable keys

开启索引语句：
alter table table_name keys

对于空表不需要，MyISAM引擎是在导入数据后才建立索引

4.2 禁用唯一性检查

同上
禁用唯一性检查的语句：
set unique_checks=0;

开启唯一性检查的语句：
set unique_checks=1;

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

一条插入多个速度比较快。

4.4 使用load data infile

当需要批量导入数据时，使用load data infile 语句比insert语句快很多。

InnoDb

5.1 禁用唯一性检查
同上
5.2 禁用外键检查
插入数据之前执行禁止对外键的检查，数据插入完成后再恢复，可以提升插入速度。

禁用： set foreign_key_checks=0;
开启： set foreign_key_checks=1;

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

禁用： set autocommit=0;
开启： set autocommit=1;

一般开发时不用，生产时使用。

数据库结构优化
6.1 优化表结构

• 尽量将表字段定义为not null约束，这时由于在mysql中含有空值的列很难进行查询优化，null值会使索引以及索引的统计信息变得很复杂。
• 对于只包含特定类型的字段，可以使用enum、set等符合数据类型。
• 数值型字段的比较比字符串的比较效率高得多，字段类型尽量使用最小、最简单的数据类型。例如P地址可以使用int类型。
• 尽量使用tinyint、smallint、medium_int作为整数类型而非int,如果非负则加上unsigned
• varchar的长度只分配真正需要的空间
• 尽量使用timestamp而非datetime
• 单表不要有太多字段，建议在20以内
• 合理的加入冗余字段可以提高查询速度。

6.2 表拆分
6.2.1 垂直拆分
方法： 如果表字段太多，比常用的拆分到一张表，不常用的拆分到另一张表。
取的时候，分表取两次，比用join效率高
6.2.2 水平拆分
例子：对id求余，对余数进行分析，然后拆表 如果id % 3 = 0~2 折成了3张表。

水平拆分按照行进行拆分，常见的就是分库分表。以用户表为例，可以取用户ID,然后对ID取10的余数，将用户均匀的分配进这0-9这个10个表中。查找的时候也按照这种规则，又快又方便。

有些表业务关联比较强，那么可以使用按时间划分的。例如每天的数据量很大，需要每天新建一张表。这种业务类型就是需要调整插入，但是对于查询的效率不太关心。表越大，插入数据所需要索引维护的时间也就越长。

两种方式：
1、动态数据源
2、myCat数据中间件，可以用在此处。
6.3 分区
mysql主要支持4种模式的分区：range分区、list预定义列表分区,hash分区，key键值分区。

不复杂，配置就可以

6.4 读写分离

我们发现一般情况下对数据库而言都是“读多写少”，也就是对数据库读取数据的压力比较大，这样分析可以采用数据库集群的方案。其中一个是主库，负责写入数据，我们称为写库；其他都是从库，负责读取数据，我们称为读库。这样可以缓解一台服务器的访问压力。

6.5 数据库集群

如果访问量非常大，虽然使用读写分离能够缓解压力，但是一旦写操作一台服务器都不能承受了，这个时候我们就需要考虑使用多台服务器实现读写操作。

例如可以使用MyCat搭建Mysql集群，对ID求3的余数，这样可以把数据分别存到3台不同的服务器上，由MyCat负责维护集群节点的使用。