@22221cjp 2017-02-16T02:49:32.000000Z 字数 8591 阅读 662

Mysql优化

技术

Mysql的优化大致可以分为以下四个部分：

Sql优化
索引优化
数据库表结构优化
系统优化&mysql配置优化

Sql优化

sql的优化首先要知道什么sql出现了问题。

开启慢查询日志：

开启慢查询日志
show variables like 'slow_query_log' 展现慢查询数据日志
set global slow_query_log_file = 'd:/mysql/sql_log/mysql-slow.log/'设置慢查询日志存放的位置
set global log_queries_not_using_indexes = on; 将没有索引的查询展现出来
set global long_query_time = 1;将查询语句在一秒以上的查询展现在日志中

show variables like '%log';
set global log_queries_not_using_indexes = on; 将没有索引的查询展现出来;记录未使用索引的查询。
show variables like 'slow%' 可以看到慢查询日志是否开启以及慢查询日志的位置。

如何分析慢查询日志

Mysql官方的工具mysqldumpslow

Mysqldumpslow安装mysql时自带的mysql慢查询日志分析工具
但工具展示的信息还不够全面,主要是主机信息,sql以及当前sql执行的次数,消耗的时间
使用方式:
mysqldumpslow -h (可以列出参数的帮助列表) 需要分析的慢查询日志文件.

mysqldumpslow -t 3 /home/mysql/data/mysql-slow.log | more

第三方的慢查询分析工具pt-query-digest

pt-query-digest分析慢查询日志文件比mysqldumpslow更丰富:
1.显示日志的时间范围,以及总的sql数量.
2.表的统计信息sql响应时间和执行次数。
3.具体的sql

分析的结果输出的文件

pt-query-digest slow-log > slow_log.report

输出到数据库表

pt-query-digest slow.log -review \
h=127.0.0.1,D=test,p=root,P=3306,u=root,t=query_review \
--create-reviewtable \
--review-history t=hostname_slow

查看帮助

pt-query-digest -h

e.g:

>pt-query-digest -t /home/..../slow.log


Current date: Sat Jul 12 09:12:51...
Hostname: sample
Overall:9.0k total, 1.17 unique, 0.00 QPS, 0.00x concurrency
Time range: 2014-06-19 10:01:36 to 2014......
Attribute         total    min     max  avg   95%  stddev   median
==================================================================
Excecute time     .....
Lock     time
Rows     sent
Rows     examine
Query    size

...

分析慢查询日志，发现有问题的SQL

需要解决：
1.查询时间长,查询次数多
2.IO大的sql,分析Rows Examine项,扫描的行数
3.未命中索引的sql,分析Rows Examine与Rows send发送的行数的对比

使用explain优化单个SQL

使用explain穿SQL的执行计划

e.g:

mysql>explain select customer_id,first_name,last_name from customer;

|id|select_type|table |type|possible_keys|key|key_len|ref|rows|Extra|
+....+...........+.......+.......+.........+.......+...+....+....+..+
|1 |SIMPLE     |customer|ALL|NULL        |NULL...........|671|.......

explain返回各列的含义

table :显示这一行的数据是哪张表的

type:这是重要的列，显示连接使用了何种类型。从最好到最差的连接类型为
const 常数查找唯一索引或卫衣主键查找
eq_reg 范围查找主键范围查找等
ref 一个表是基于某一个表的查找
range 基于索引的范围查找
index 基于索引的扫描
ALL 全表扫描

possible_keys:显示可能应用在这张表中的索引。如果为空，没有可能的索引

key:实际使用的索引。如果为NULL，则没有使用索引

key_len:使用索引的长度。在不损失精确性的情况下，长度越短越好

ref:显示索引的哪一列被使用了，如果可能的话，是一个常数

rows:MySQL认为必须检查的用来请求数据的的行数

Extra:扩展列需要注意的返回值

  Using filesort:看到这个的时候，查询就需要优化了。MySQL需要进行
  额外的步骤来发现如何对返回的行排序。它根据连接类型以及存储排序键
  值和匹配条件的全部行的指针来排序全部行

  Using temporary:看到这个的时候，查询需要优化了。这里MySQL需要创建
  临时表来存储结果，这通常发生在对不同的列集进行order by 上，而不是
  group by 上。

count和max函数优化

count查询结果集数量
max查询某个最大的

count优化

在一条SQL中同时查出2006年和2007年电影的数量－－优化count()

错误的方式：

select count(release_year='2007' or release_year='2007') from film;
无法分开计算2006和2007年电影的数量
select count(*) from film where release_year='2006' and release_year='2007';

明显有错误，发布年份不能同时是2006和2007

有很多同学会疑问是用count(*) 好呢还是count(id)好，还是count(1)好？这两种的选择有时候执行结果是不一样的。

e.g:

mysql>create table t(id int);
mysql>insert into t(id) values(1);
mysql>insert into t(id) values(2);
mysql>insert into t(id) values(null);
mysql>select * from t;
+------------+
| id |
+------------+
| 1 |
| 2 |
| NULL|
mysql>select count(*),count(1)，count(id) from t;

结果：count(*)=3条 ; count(1)=3条 count(id)=2条

因为count(*) ,count(1)包括null值，count(id)忽略null值

最后：count的正确语句是：

select count(release_year='2006' or NULL) ,
count(release_year='2007' or NULL)
from film;

max优化

查询最后支付时间－优化max（）函数

select max(payment_date) from payment;

mysql>explain select max(payment_date) from payment \G;

这里最后加了个\G 能使输出按列打印

这个查询rows 如果IO负荷很大的话，则不是一个很好的SQL，需要优化

那怎么优化呢？

在payment_date列上建立索引
mysql>create index idx_paydate on payment(pay_date);

再次expain这条查询语句，性能明显有很大的提高，因为索引是顺序排列的
，通过索引的信息，查询很快就能知道哪条最大

子查询优化

尽量不用子查询
实在要用，试试是否可以转化为多表关联

group by优化

在实际使用中，也可以不用group by，而是先查出来，在Java代码中进行聚合

explain select actor.first_name,actor.last_name,count(*)
from sakila.film_actor inner join sakila.actor using(actor_id)
group by film_actor.actor_id;
*************************************************************
Extra:Using temporary; Using filesort

用到了临时表和文件排序

为了避免临时表盒文件排序，优化如下：

 explain select actor.first_name,actor.last_name,c.cnt from 
 sakila.actor inner join (select actor_id,count(*) as cnt from sakila.film_actor
 group by actor_id) as c using (actor_id);

但是优化也不是一成不变的，增加查询条件的时候，我们相应的需要作出调整，将条件加大子查询里面，
缩小匹配的范围

limit优化

limit常用于分页处理，时常伴随order by 从句使用，因此大多时候会使用
Filesorts这样会造成大量的IO问题，将order by的列进行索引。

select film_id,description from sakila.film order by title limit 50,5;

优化策略1:使用有索引的列或主键进行order by操作
select film_id,description from sakila.film order by film_id limit 50,5;

但是随着id越老越大，IO操作越来越多，需要进一步优化

优化策略2:记录赏赐返回的主键，在下次查询时使用主键过滤
select file_id,description from sakila.film where film_id>55 and
film_id <=60 order by film_id limit 1,5

但是这种优化有个缺点就是要求ID连续的，如果ID不连续的话，会有问题，可以创建连续自增的列来实现，这个优化方式实际基本不用

索引优化

如何选择合适的列建立索引

1.在where从句，group by 从句，order by从句,on从句中出现的列
2.索引字段越小越好数据存储以一页为单位，一页数据越多，IO数据量大，速度越慢，所以越小越好
3.离散度大的列放到联合索引的前面
select * from payment where staff_id=2 and customer_id=584;
是index(staff_id,customer_id)好，还是index(customer_id,staff_id)好？

由于customer_id的离散度更大，所以应该是用index(customer_id,staff_id)

这里有个离散度的概念，怎么理解这个术语呢？

如果一个字段的唯一值出现越多，离散度越大，用sql来查询比较就是
select count(distinct customer_id),count(distinct staff_id) from payment;

如果一个索引包括了一个查询中的所有列，则称之为覆盖索引

建立什么类型的索引

索引的类型有：

普通索引
唯一索引
全文索引
组合索引

mysql一次查询只能利用一个索引。建立什么类型的所有应该根据业务的需要。

普通索引
应该是最常用的索引，一般就是btree。

CREATE INDEX index_name ON table(column(length))

唯一索引
主键就是一个唯一索引
根据业务需要，可以创建两个列的联合唯一索引，这样来避免脏数据。

全文索引
只有MyISAM存储引擎支持。类似于搜索

CREATE FULLTEXT INDEX index_content ON article(content)

组合索引
创建多个列的联合索引，这个需要根据具体的sql语句的条件判断。如果有多个条件，将区分度高的列作为联合索引的前面，联合索引满足最左匹配原则

SELECT * FROM article WHREE title='测试' AND time=1234567890;

索引的注意事项

索引列不要有NULL值，否则索引无效
使用短索引
比如varchar(255)并不一定对整个字段加索引，可以对�前10个字符索引
使用索引列排序
like语句：'%xx%'和'xx%'区别
where条件中不要对索引列使用函数计算
如：select * from users where YEAR(adddate)<2007将不走索引

索引的维护

删除冗余的索引

索引并不是越多越好，会对插入和更新的性能有影响。
注意不要建立重复索引。
如：

create table test(
    id int not null primary key,
    name varchar(10) not null,
    title varchar(50) not null,
    unique(id),
) engine=innodb;

key和id上的索引就是重复索引。

create table test(
   id int not null primary key,
   name varchar(10) not null,
   title varchar(50) not null,
   key(name,id)
) engine=innodb;

冗余索引是指多个索引的前缀列相同，或是在联合索引中包含了主键的索引，上面
这个列子中的key(name,id)就是一个冗余索引
实际中每一个索引后面都会跟个主键，所以上面是冗余的。
怎么查找重复冗余索引？
可以使用pt-duplicate-key-checker工具检查重复及冗余索引，还能给出索引维护的建议。

删除不用的索引

如何找到不用的索引？
目前MySQL中还没有记录索引的使用情况，但是在PerconMySQL和MariaDB中
可以通过INDEX_STATISTICS表来查看哪些索引未使用，但在MySQL中目前智能通过慢
查日志配合pt-index-usage工具来进行索引使用情况的分析。

pt-index-usage \

-uroot -p '' \
mysql-slow.log

数据库和表结构优化

选择合适的数据类型

数据类型的选择，重点在于合适二字
1.使用可以存下你的数据的最小的数据类型
2.使用简单的数据类型，int要比varchar类型在mysql处理上简单。
3.尽可能的使用not null定义字段
4.尽量少用text类型，非用不可时最好考虑分表

.使用int来存储日起时间，利用from_unixtime()，unix_timestamp()两个
函数来进行转换
from_unixtime() 将int转换为时间格式 unix_timestamp()反之。

.使用bigint来存储IP地址，利用inet_aton(),inet_ntoa()两个函数转换。

表的范式化和反范式化

范式化

数据表设计三范式。一般满足第三范式即可。减少数据的冗余。

反范式化

反范式化是指为了查询效率的考虑把原本符合第三范式的表适当
的增加冗余，以达到优化查询效率的目的，反范式化是一种以空间来换取时间的操作。

表的垂直拆分

表的垂直拆分，就是把原来一个有很多列的表拆分成多张表，这解决了表的宽度问题。
通常垂直拆分可以按以下原则来进行：
1.把不常用的字段单独放到一个表
2.把大字段独立存放到一个表
3.把经常在一起使用的字段放到一起

表的水平拆分（做表分区）

表的水平拆分是为了解决单表的数据量过大的问题，水平拆分的表每一个表的结构都一样。
常用的水平拆分方法为：
1.对主键进行hash计算，如果要拆分5个表则使用mod(id,5)取出0-4个值
2.针对不同的hashID把数据存到不同的表中。

mysql支持5种分区方式：RANGE分区、LIST分区、HASH分区、LINEAR HASH分区和KEY分区

range分区

CREATE TABLE `T_part` (
    `f_id` INT DEFAULT NULL,
    `f_name` VARCHAR (20) DEFAULT NULL,
    PRIMARY KEY (`f_id`)
) ENGINE = myisam DEFAULT CHARSET = utf8 
PARTITION BY RANGE (f_id)(        -----指定分区方式
    PARTITION p0  VALUES less THAN (10),-- 分了两个区
    PARTITION p1  VALUES less THAN (20)
)

LIST分区

CREATE TABLE `T_list` (
    `f_id` INT DEFAULT NULL,
    `f_name` VARCHAR (20) DEFAULT NULL,
    PRIMARY KEY (`f_id`)
) ENGINE = myisam DEFAULT CHARSET = utf8 
PARTITION by list(f_id)
(
  PARTITION p0 VALUES in(1,2,3),  ----区间值不能重复
  PARTITION p1 VALUES in(4,5,6)
);

HASH分区：

CREATE TABLE `T_hash` (
    `f_id` INT DEFAULT NULL,
    `f_name` VARCHAR (20) DEFAULT NULL,
    PRIMARY KEY (`f_id`)
) ENGINE = myisam DEFAULT CHARSET = utf8 
PARTITION BY HASH(f_id) ---可以指定多列
PARTITIONS 4；---分区个数

其他略

系统优化&mysql配置优化

操作系统优化

数据库是基于操作系统的，目前大多数的MySQL都是安装在linux系统之上，
所以对于操作系统的一些参数配置也影响到MySQL的性能，下面就列出一些常用到
的系统配置。

网络方面的配置，要修改/etc/sysctl.conf文件

#增加tcp支持的队列书
net.ipv4.tcp_max_syn_backlog=65535
#减少断开连接时，资源的回收
net.ipv4.tcp_max_tw_buckets=8000
net.ipv4.tcp_tw_reuse = 1
net.ipv4.tcp_tw_recycle=1
net.ipv4.tcp_fin_timeout=10

打开文件数的限制，可以使用ulimit -a 查看目录的各位闲置，可以修改
/etc/security/limits.conf文件，增加以下内容以修改打开文件数量的限制

*soft nofile 65535
*hard nofile 65535

除此之外最好在MySQL服务器上关闭iptables,selinux等防火墙软件。

mysql配置优化

MySQL可以通过启动时指定配置参数和使用配置文件两种方法进行配置，在大多数情况下配置文件位于/etc/my.cnf或/etc/mysql/my.cnf；在windows系统配置文件可以位于C：/windows/my.ini文件，MySQL查找配置的顺序可以通过以下方法获得：

$:/usr/sbin/mysqld --verbose --help | grep -A 1 'Default options'

注意：如果存在多个位置存在配置文件，则后面的会覆盖前面的

常用参数说明

innodb_buffer_pool_size
非常重要的参数，用于配置innodb的缓冲池如果数据库中只有innodb表，
则推荐配置量为总内存的75%

下面是检测语句

select engine,round(sum(data_ength + index_length)/1024/1024,1) as 'Total MB',
from information_schema.tables where table_schema not in ('information_schema',
'performance_schema') group by engine;

innodb_buffer_pool_size >= Total MB

innodb_buffer_pool_instances

MySQL5.5中新增参数，可以控制缓冲池的个数，默认情况下只有一个

innodb_log_buffer_size

innodb log缓冲的大小，由于日志最长每秒钟就会刷新所以一般不用太大

innodb_flush_log_at_trx_commit

关键参数，对innodb的IO效率影响比较大，默认值为1，可以取0，1，2三个值，
一般建议设为2，但如果数据安全性要求比较高则使用默认值1.

innodb_read_io_threads
innodb_write_io_threads

以上两个参数决定了innodb读写IO进程数，默认4

innodb_file_per_table

关键参数，控制innodb中每一个表使用独立的表空间，默认为OFF，也就是所有表
都会建立在共享表空间。

innodb_stats_on_metadata

决定了MySQL在什么情况下会刷新innodb表的统计信息

Mysql优化

Sql优化

开启慢查询日志：

如何分析慢查询日志

Mysql官方的工具mysqldumpslow

第三方的慢查询分析工具pt-query-digest

分析慢查询日志，发现有问题的SQL

使用explain优化单个SQL

count和max函数优化

count优化

max优化

子查询优化

group by优化

limit优化

索引优化

如何选择合适的列建立索引

建立什么类型的索引

索引的注意事项

索引的维护

删除冗余的索引

删除不用的索引

数据库和表结构优化

选择合适的数据类型

表的范式化和反范式化

范式化

反范式化

表的垂直拆分

表的水平拆分（做表分区）

range分区

LIST分区

HASH分区：

系统优化&mysql配置优化

操作系统优化

mysql配置优化

内容目录

选择主题