MySQL调优总结

一、为什么需要SQL优化?

在系统开发初期，由于数据库中数据较少，对于SQL语句，复杂的视图的编写等看不出SQL语句的各种写法的性能优劣，但是随着应用的持续开发，系统交付实际应用之后，随着数据量的增加，系统的响应速度很慢，这就需要系统解决了，而最重要的一个优化就是SQL优化。对于海量数据，优质的SQL与劣质的SQL之间的速度差距可以达到上百倍，对于一个系统，不仅仅是只是实现其功能，还要写出高质量的SQL语句，提高系统的可用性。SQL优化，可以体现在如下的几个方法。

二、SQL优化-SQL语句优化

2.1 慢查日志开启

慢查询日志 slow_query_log，是用来记录查询比较慢的sql语句，通过查询日志来查找哪条sql语句比较慢，对比较慢的sql可以进行优化 。

1. 登录数据库；

show global variables like '%slow%'; //查看慢查日志存放路径。

1. 开启慢查询日志

set global slow_query_log=on;

3.查看慢查日志的设置时间，是否是自己需要的。

show global variables like '%long%';

1. 默认设置的是10秒，如果不是自己需要的时间，修改慢查询时间，只要超过了设置的时间，查询的日志就会添加到慢查日志文件中，本次设置超时时间为1s,将超过1s的SQL写入慢查日志中。

set global long_query_time=1;

5.数据准备，大数据或者复杂SQL查询；

6.查看日志：打开日志（打开慢查日志文件，）

慢查日志内容：
# Time: 181202 10:43:31   //执行时间
# User@Host: qiulin[qiulin] @ localhost [127.0.0.1]  //使用的机器
# Query_time: 0.811781  Lock_time: 0.000000 Rows_sent: 160655  //执行时间 Rows_examined: 160668 //扫描行数
SET timestamp=1543718611; 
select * from tb_content where title like '%小%';  //执行的语句
​

7.执行计划打印，就是在sql前面加上explain关键字

全表扫描，描述extra中，表示只使用了where条件，没有其他什么索引之类的。这里只是简单如何使用慢查询，这条语句没法优化。

explain:查询语句，返回各列含义
table：查询的那张表
type:显示使用何种类型。const(常量) > eq_req(范围) > ref（连接） > range(索引范围) >index(索引扫描) > all（全表）
prossible_key:可能应用在这张表的索引
extra:使用的条件

2.2 常见SQL语句优化

1. count() 和 max()优化

尽量使用索引列区max()，count();当数据量很大时，max()需要扫描某列所有值比较，当建立索引时，只需扫描最后一行，大大减少了查询时间。

count（）优化，首先区分count(*),count（列名）、count(1);
count(*),count(1) 查询的结果统计包含null值
count（id）忽略null值。

2. 子查询优化

通常情况下，需要将子查询优化为join查询，但在优化时需要注意关联是否有一对多的关系(会出现数据的重复-distinct去重),需要注意重复数据。因为：连接查询不需要建立临时表，因此其速度比子查询快。

SELECT * FROM tb_order WHERE user_id =(SELECT id FROM tb_user WHERE username ='tom123' ) 
​
SELECT * FROM tb_order b JOIN tb_user u ON b.user_id = u.id WHERE u.username='tom123'  
​

3. order by,group by优化

尽量使用同一张表的列，group by ，where 条件尽量使用索引。

1.order by优化
实现方式：
1. 根据索引字段排序，利用索引取出的数据已经是排好序的，直接返回给客户端；
2. 没有用到索引，将取出的数据进行一次排序操作后返回给客户端。
EXPLAIN SELECT m.id,m.subject,c.content FROM group_message m,group_message_content c WHERE m.group_id = 1 AND m.id = c.group_msg_id ORDER BY m.user_id\G;
create_time对query进行了优化，它会按照user_id上的索引顺序来访问数据，这样获取的数据已经是排好序的。
这种利用索引实现数据排序的方法是 MySQL 中实现结果集排序的最佳做法，利用已有的索引避免实际的排序计算所带来的资源消耗。
​

==order By 关键字优化
1.尽量使用index方式排序，避免使用Filesort方式排序。
2.Mysql支持两种排序方式，Filesort和Index，Index效率高，
它指Mysql扫描索引本身完成排序。FileSort排序方式效率低
==order by满足两种情况，会使用index排序
1.order by 语句使用索引最左前列
2.使用where子句和order By子句条件满足索引最左前列。

group by的实现过程除了要使用排序操作外，还要进行分组操作，如果使用到一些聚合函数，还要进行相应的聚合计算。
group by优化：
1. 尽可能利用索引并且是松散索引来完成group by操作，这的依靠调整索引或者调整query来实现；
2. 当无法利用索引的时候，必须要提供足够的sort_buffer_size来供mysql完成排序操作，之前介绍过，不然mysql会将需要排序的字段进行分段排序，会影响性能。除此之外尽量不要对大结果集进行group by操作，因为一旦数据量超过系统最大临时表大小时，mysql会将临时表里的数据copy到磁盘上然后再进行操作，性能会成数量级的下降。

group by 实质是先排序后进行分组，遵照索引建的最佳左前缀。
Where高于having，能写在where限定的条件就不要去having限定了。

4.limit 优化(数据量大的情况下，会产生大量IO)，避免过多扫描，使用有索引的列或者主键进行order by操作。

三、SQL优化-索引优化

3.1 索引的概念

索引是一种特殊的文件(InnoDB数据表上的索引是表空间的一个组成部分)，它们包含着对数据表里所有记录的引用指针。更通俗的说，数据库索引好比是一本书前面的目录，能加快数据库的查询速度。

索引分为聚簇索引和非聚簇索引两种，聚簇索引是按照数据存放的物理位置为顺序的，而非聚簇索引就不一样了；聚簇索引能提高多行检索的速度，而非聚簇索引对于单行的检索很快。

3.2 索引的类型

3.2.1  普通索引

这是最基本的索引，它没有任何限制，比如上文中为title字段创建的索引就是一个普通索引，MyIASM中默认的BTREE类型的索引，也是我们大多数情况下用到的索引。

1.创建索引
create index index_name on table (column(length))
2.修改索引
alter table table_name add index index_name on (column(length))
3.删除索引
drop index index_name on table

3.2.2 唯一索引

与普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值（注意和主键不同）。如果是组合索引，则列值的组合必须唯一，创建方法和普通索引类似。

1.创建唯一索引
    CREATE UNIQUE INDEX indexName ON table(column(length))
2.修改表结构
    ALTER TABLE table_name ADD UNIQUE indexName ON (column(length))
​

3.2.3 全文索引（FULLTEXT）

MySQL从3.23.23版开始支持全文索引和全文检索，FULLTEXT索引仅可用于 MyISAM 表；他们可以从CHAR、VARCHAR或TEXT列中作为CREATE TABLE语句的一部分被创建，或是随后使用ALTER TABLE 或CREATE INDEX被添加。

1.创建表的时候添加全文索引
create table 'tb_item'(
    'id' int(11) not null auto_increment,
    'title' char(255) CHARACTER not null,
    'content' text ,
    'time' int(10) ,
    primary key('id'),
    fulltext(content)
);
2.修改表结构添加全文索引
    ALTER TABLE tb_item ADD FULLTEXT index_content(content)
3.直接创建索引
     CREATE FULLTEXT INDEX index_content ON tb_item(content)    

3.2.4 聚集索引（最左前缀）

平时用的SQL查询语句一般都有比较多的限制条件，所以为了进一步榨取MySQL的效率，就要考虑建立组合索引。例如上表中针对title和time建立一个组合索引：ALTER TABLE tb_item ADD INDEX index_titme_time (title(50),time(10))。建立这样的组合索引，其实是相当于分别建立了下面两组组合索引：

–title,time

–title

3.3 索引的使用。

使用索引的好处就是加快查询速度，但过多的使用索引将会造成滥用。因此索引也会有它的缺点：虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。一张表上索引越多，在数据增删改的时候越慢，因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件。建立索引会占用磁盘空间的索引文件。一般情况这个问题不太严重，但如果你在一个大表上创建了多种组合索引，索引文件的会膨胀很快。

3.3.1 合适的地方添加索引

1. 在where 从句，group by 从句，order by 从句，on从句中出现的列。

2. 索引字段越小越好

3. 离散度大的列放在聚集索引的前面。（唯一值越多，离散度越高。可用count（distinct ）判断）

3.3.2 索引优化

1.使用聚集索引或非聚集索引正确时机

动作描述	使用聚集索引	使用非聚集索引
列经常被分组排序	使用	1用
返回某范围内的数据	使用	不使用
一个或极少不同值	不使用	不使用
小数目的不同值	使用	不使用
大数目的不同值	不使用	使用
频繁更新的列	不使用	使用
外键列	使用	使用
主键列	使用	使用
频繁修改索引列	不使用	使用

2. 索引不会包含有NULL值的列

只要列中包含有NULL值都将不会被包含在索引中，复合索引中只要有一列含有NULL值，那么这一列对于此复合索引就是无效的。所以我们在数据库设计时不要让字段的默认值为NULL。

3.使用短索引

对串列进行索引，如果可能应该指定一个前缀长度。例如，如果有一个CHAR(255)的列，如果在前10个或20个字符内，多数值是惟一的，那么就不要对整个列进行索引。短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作。

4. 索引列排序

MySQL查询只使用一个索引，因此如果where子句中已经使用了索引的话，那么order by中的列是不会使用索引的。因此数据库默认排序可以符合要求的情况下不要使用排序操作；尽量不要包含多个列的排序，如果需要最好给这些列创建复合索引。

5. like语句操作

一般情况下不鼓励使用like操作，如果非使用不可，如何使用也是一个问题。like “%aaa%” 不会使用索引而like “aaa%”可以使用索引。

6.不要在列上进行运算

例如：select * from tb_item where YEAR(from_unixTime(time))<2007，将在每个行上进行运算，这将导致索引失效而进行全表扫描，因此我们可以改成：select * from tb_item where time<unix_timestamp(’2007-01-01′)。

7.选择合适的存储引擎

Myisam 适合一些需要大量查询的应用，但对于大量写操作并不友好，因为它用到的是表级锁，在更新的时候，整张表都会被锁起来。SELECT COUNT(*) 计算很快。InnoDB :对于一些较小的应用，比Myisam慢，行级锁，多用于写操作较多的时候，支持事务。

最后总结一下，MySQL只对一下操作符才使用索引：<,<=,=,>,>=,between,in,以及某些时候的like(不以通配符%或_开头的情形)。而理论上每张表里面最多可创建16个索引，不过除非是数据量真的很多，否则过多的使用索引也不是那么好玩的，比如我刚才针对text类型的字段创建索引的时候，系统差点就卡死了。

3.2 删除冗余的索引

1.冗余和重复索引

　　mysql允许在相同列上创建多个索引，无论是有意还是无意，mysql需要单独维护重复的索引，并且优化器在优化查询的时候也需要逐个地进行考虑，这会影响性能。重复索引是指的在相同的列上按照相同的顺序创建的相同类型的索引，应该避免这样创建重复所以，发现以后也应该立即删除。但，在相同的列上创建不同类型的索引来满足不同的查询需求是可以的。

冗余索引和重复索引有一些不同，如果创建了索引（a,b），再创建索引（a）就是冗余索引，因为这只是前面一个索引的前缀索引，因此（a,b）也可以当作(a)来使用，但是（b,a）就不是冗余索引，索引(b)也不是，因为b不是索引（a,b）的最左前缀列，另外，其他不同类型的索引在相同列上创建（如哈希索引和全文索引）不会是btree索引的冗余索引。

2. 冗余索引清理规则

1. 第一个索引是第二个索引的左前缀索引, 删除第一个索引，创建了索引（a,b），删除索引（a）。

2. 有主键, 用不到唯一索引, 删除唯一索引。

3. 有主键, 用不到普通索引, 删除普通索引。

4. 有唯一索引, 用不到普通索引, 删除普通索引。

5. 有联合主键索引, 用不到普通组合索引, 删除普通组合索引。

6. 索引重复, 删除其中一个索引。

四、SQL优化-数据表及数据结构优化

4.1 选择合适的数据类型

1. 使用可以存下你的数据的最小的数据类型。

选择合适的标识符是非常重要的。选择时不仅应该考虑存储类型，而且应该考虑MySQL是怎样进行运算和比较的。一旦选定数据类型，应该保证所有相关的表都使用相同的数据类型。(1) 整型：通常是作为标识符的最好选择，因为可以更快的处理，而且可以设置为AUTO_INCREMENT。

(2) 字符串：尽量避免使用字符串作为标识符，它们消耗更好的空间，处理起来也较慢。而且，通常来说，字符串都是随机的，所以它们在索引中的位置也是随机的，这会导致页面分裂、随机访问磁盘，聚簇索引分裂（对于使用聚簇索引的存储引擎）。

2.使用简单的数据类型。Int要不varchar类型在MySQL上处理简单。

eg1：用int 存储日期时间，可以使用
       Unix_timestamp(时间字符串) 将字符串转int 类型
       from_unixTime(int) 将整数转成字符串格式
eg2: 用bigint存储ip地址。
       inet_aton(ip字符串) 将ip地址转为int类型
       inet_ntoa(int数据) 将整数型ip转字符串

3.尽可能使用not null 定义字段，设置默认值。

尽量避免NULL：应该指定列为NOT NULL，除非你想存储NULL。在MySQL中，含有空值的列很难进行查询优化，因为它们使得索引、索引的统计信息以及比较运算更加复杂。你应该用0、一个特殊的值或者一个空串代替空值。

4.尽量减少text类型，非用不可时最好考虑分表。

char:  char不用多说了，它是定长格式的，但是长度范围是0~255. 当你想要储存一个长度不足255的字符时，MySQL会用空格来填充剩下的字符。因此在读取数据时，char类型的数据要进行处理，把后面的空格去除。

varchar:  关于varchar，有的说最大长度是255，也有的说是65535，查阅很多资料后发现是这样的：varchar类型在5.0.3以下的版本中的最大长度限制为255，而在5.0.3及以上的版本中，varchar数据类型的长度支持到了65535，也就是说可以存放65532个字节（注意是字节而不是字符）的数据 。

text:与char和varchar不同的是，text不可以有默认值，其最大长度是2的16次方-1

5.分库分表

单表的字段控制在20以内为最佳，而且字段尽量短小但是含义清晰。大字段或者过多的字段都会影响执行效率。

按照具体的业务需求，合理的设计数据库和表，将数据合理分开存储。单表数量最好不超过500万记录。一个数据中表数量最好不多于300。

五、SQL优化-系统配置优化