言十年的博客

上善若水,水善利萬物而不爭

物盡其用!人盡其才!兩全其美!


mysql的优化【总结】

##Mysql优化大的思路

顺序读取跟随机读取效率差了一个量级以上 。基准测试工具需要多台服务器测试对比才更有意义。单台服务器没有对比意义不大。

要想解决问题,就得先去观察问题在哪里。

拿上一台服务器,不是看表结构,首先从整体观察,看它的状态。往往写一个脚本观察24小时或者更久。这样才能体现出周期性的变化出来!

看周期性波动。看慢查询。等等

##awk的简洁入门

命令行中执行show status

Queries 当前已经发生了 多少次查询

Threads_connected 有几个线程过来链接

Threads_running 有几个线程正在工作(进程连接上来了也未必查询啊)

当前有多少个链接进行工作然后发生了多少次查询。使用awk分析。

awk是一个小脚本可以方便的进行数据的统计。

创建一个kids.txt,写入:

name age sex
小新 4   男
风间 5   男
妮妮 5	 女

执行 awk '{printf("%s\n",$0)}' kids.txt全部输出。

$0是当前行。$1是第一列

name age sex
小新 4   男
风间 5   男
妮妮 5	 女

awk ‘{printf(“%s\n”,$1)}’ kids.txt

name
小新
风间
妮妮

awk ‘{printf(“%s\n”,$2)}’ kids.txt

age
4
5
5

$3你懂的,$4就什么都没有了。

我们想找以开头的数据。awk '/小/{printf("%s\n",$0)}' kids.txt

小新 4   男

处理原理:会自动的帮你去循环这个文件,一行一行的读,每读来一行,把整个一行给$0,然后第一列赋给$1,第二列赋给$2,第n列赋给$n

处理过程:

While(还有一行) {
	1.读取下一行并把下一行赋给$0,各列赋给$1,$2,$N变量
}

如何处理1行数据?

答:分2部分,pattern(条件) + action(处理动作)

开头的数据所在的行打印出来。

awk '/小/{printf("%s\n",$0)}'  kids.txt
小新 4   男
mysqladmin -uroot ext | awk '/Queries/{printf("%d", $4)}'

##观察服务器周期性变化

模拟一下在网站中缓存周期性的变化,对请求周期数所带来的影响。

一天24消失,零点到三点,人总是特别少。10点14点19点20点大概会飙升。

设计实验:

总数据3w上50个并发,没秒请求500-1000次

请求结果缓存在memcache生命周期为5分钟

观察mysql连接数,没秒请求数的周期变化

实验素材:

1.index.php(随机访问3w条热数据,并存储在memcached中)

2.memcached(储存查询结果)

4.ab压力测试工具

启动memecache /usr/local/memcached/bin/memcached -u nobody -m 512 -d,分配了512M内存

查看它的进程确认下:ps aux | grep mem

启动nginx,/usr/local/nginx/sbin/nginx

./ab -c 50 200000 http;//xxx/index.php

我们需要写一个脚本,每秒钟获取mysql服务器的状态

#!/bin/bash
while true

do

mysql -uroot -e 'show processlist'

3w条数据,15秒跑完一遍

请求30万次, 70多秒

通过脚本,刷新观察 status。是否周期性故障或波动。

###观察mysql进程状态

show processlist 或开启慢查询,获取有问题的sql

mysql -uroot -e 'show processlist'

这个命令是显示当前所有连接的工作状态

#!/bin/bash
while true
do
mysql -uroot - e 'show processlist\G|grep State:|uniq -e|sort -rn'
echo '--'
sleep 1
Done

每秒刷新一下,当请求量大的情况下,多个进程有可能处于一个状态。我们可以用uniq将同样的状态合并,用sort 排序

profiling分析语句及explain

观察mysql进程状态

mysql -h 192.168.xx.xx -u root -e 'show processlist\G'|grep State:|sort|uniq -c|sort -rn

5 State: Sending data
2 State: statistics
2 State: NULL
1 State: Updating
1 State: update

值得注意的mysql进程状态

converting HEAP to MyISAM 查询结果太大时,把结果放在磁盘(反思是不是不用查那么多条?比如新闻列表20条就可以)
create tmp table 创建临时表(如group时储存在中间结果,反思是不是没有合理的使用索引,还有文件排序也是)
Copying to tmp table on disk 把内存临时表复制到磁盘
locked 被其他查询锁住
logging slow query 记录慢查询

注:把临时表内存变小,重现前

这次目的就是为了观察mysql运行时的可疑状态,准确的说是低效状态

Sending data 是指发送数据,如果查询的数量一下子返回1000条或者更多,你会发现大部分时间,都浪费到Send data上,太可惜了。

Copying to tmp table 正在往临时表上复制,而复制到临时表也很消耗资源的过程。

Sending data 发送数据,尽量避免Sending data 过长

sorting result 排序也是非常浪费资源的地方,如果在磁盘排序那更慢

show variables like '%size%';

set global tmp_table_size=1024;

set session tmp_table_size=1024;

本次会话跟全局都改小了。 set profiling = 1;

show profiles;

set names utf8;

select * from lx_com group by id%20 order by 5;这条语句语义上并没有任何意义,主要看查询后的状态。

mysql5.0以后 新增了一个很强的功能。叫profiling 可以将每条语句生成一个详细的过程。

show profiles;

列选取原则

1.字段类型优先级 整型 > date,time > enum > char, varchar > blob

原因:

整型,time 运算快(因为整数运算快,浮点就费点劲),节省空间。

Char/varchar 要考虑字符集的转换与排序时的校对集,速度慢。(a,B这两个怎么排序,字符排序这里有个重要的概念,要用校对集,到底排序的时候以谁优先,排序的时候有些麻烦,需要用校对集翻译一遍,整型 不用因为 1 2 3大小是死的)

Blob 无法使用内存临时表(一旦牵扯到某个字段有blob,肯定会取到磁盘上去建立临时表,所以不推荐)

2.够用就行,不要慷慨(如smallint,varchar(N))

原因:大的字段浪费内存,影响速度。

以 varchar(10),varchar(300)存储的内容相同,但在表联查时,varchar(300)要花更多内存

3.尽量避免用NULL

原因:NULL不利于索引,要用特殊字节来标注。在磁盘上占据的空间其实更大。

create table t3 (
	name char(1) not null default '',
	key(name)
)engine myisam charset utf8;
create table t4 (
	name char(1) ,
	key(name)
)engine myisam charset utf8;

insert into t3 values ('a'), ('');
insert into t4 values ('a'), (null);

explain select * from t3 where name='a' \G

explain select * from t4 where name='a' \G

explain select ..t3..语句看到 key_lend的值为4,为了存储null会刻意的多保存一个字节。用来区分这个null。所以,建表的时候尽量的避免用null。

把name=null的查出来,怎么查? name is null

Enum(枚举类型)列的说明:

1.enum列在内部是用整型来储存的。

2.enum列与enum列相关联速度最快

3.enum比(var)char的弱势——在碰到与char关联时,要转化,要花时间

4.优势在于,当char非常长时,enum依然是整型固定长度

5.enum与char/varchar关联,因为要转化,速度要比enum->enum_char->char要慢

但有时也这样用——就是在数据量特别大时,可以节省IO。

create table t5 (
gender enum('male','female') not null default 'male'
)engine myisam charset utf8;

insert into t5 values ('male'),('female');

select gender+0 from t5;

+----------+
| gender+0 |
+----------+
|        1 |
|        2 |
+----------+
这样使用只占4个字符,而用字符串呢?utf8的情况下,male就是12个字符,female18个字符;

###多列索引生效规则

btree索引左前缀匹配规则

索引优化策略

1.索引类型 1.1 B-tree索引

注:名叫btree索引,大的方面看,都用的平衡树,但具体的实现上,各引擎稍有不同,比如,严格的说,NDB引擎,使用的是T-tree

Myisam,innodb,默认用B-tree索引

但抽象一下–B-tree系统,可理解为排好序的快速查找结果 1.2hash索引

在memory表里,默认是hash索引,hash的理论查询时间复杂度为O(1)

疑问:既然hash的查找如此高效,为什么不都用hash索引

答:

1.hash函数计算后的结果,是随机的,如果是在磁盘上放置数据

比主键为id为例,那么随着id的增长,id对应的行,在磁盘上随机放置。

2.无法对范围查询进行优化

3.无法利用前缀索引,比如,在btree中,field列的值“helloworld”,并加索引,查询 xx=helloword自然可以利用索引,xx=hello,也可以利用索引(左前缀索引),因为hash(‘helloworld’)和hash(‘hello’),两者的关系扔为随机

4.排序也无法优化

5.必须回行,就是说,通过索引拿到数据位置,必须回到表中取数据。

2.btree索引的常见误区

2.1 在where 条件常用的列上都加上索引

例:where cat_id=3 and price>100;//查询第3个栏目,100元以上的商品。

误:cat_id 上 和 price 上都加上索引

错:只能用上cat_id 或Price索引,因为是独立的索引,同时只能用上1个(虽然在mysql5.6做了改进,允许两个索引做一定程度的合并,从一定程度上提高下运行效率,但毕竟有限,如果想让两列都发挥作用,我们使用多列索引)。

2.2在多列上建立索引后,查询那个列,索引都将发挥作用

误:多列索引上,索引发挥作用,需要满足在前缀要求。

以index(a,b,c)为例。

语句 索引是否发挥作用  
Where a=3 是,只使用了a列  
Where a=3 and b=5 是,使用了a,b列  
Where a=3 and b=5 and c=4 是,全部使用了  
Where b=3 Where c=4
Where a=3 and c=4 a列能发挥索引,c不能  
Where a=3 and b>10 and c=7 A能利用,b能利用,c不能利用  
同上,where a=3 and b like ‘xxx%’ and c=7 a能用,b能用,c不能用  

where c1=x and c2=x adn c4=x order by c3; //c3发挥作用没?答:发挥了

create table t6(
	c1 char(1) not null default '',
	c2 char(1) not null default '',
	c3 char(1) not null default '',
	c4 char(1) not null default '',
	c5 char(1) not null default '',
	index(c1,c2,c3,c4,c5)
)engine myisam charset utf8;

insert into t6 values ('a', 'b', 'c', 'd', 'e');//多插入几条,否则只有一条explain显示没有用到过索引

explain select * from t6 where c1='a' and c2='b' and c4>'a' and c3='c' \G

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t6
         type: range //使用索引的方式,范围形式的索引指的是c4,
possible_keys: c1
          key: c1
      key_len: 12 // 索引长度为12,一个utf8长度为3,四列,三四一十二四列的索引都用到了
          ref: NULL
         rows: 3
        Extra: Using where; Using index
1 row in set (0.03 sec)

using filesort 二次排序 ,c5是无序的,所以查出来还得根据索引排序

临时表的目的是,得到一个cat_id排好序的一个临时结果,然后对这个临时结果进行group,如果我沿着cat_id直接走肯定是有序的。想下将会有什么现象发生?就不用临时表了,Using temporary就不会出现了。

explain select cat_id,avg(shop_price) from ecs_goods use index(cat_id) group by cat_id \G

为什么给cat_id添加了索引,还是有Using temporary; Using filesort;?

因为执行的过程中mysql会自动的分析你的语句,是不是必须要用索引,会有一个自动优化的过程,没有用where,意味着整张表都被取出来,优先采用顺序读取,想办法避开整张表读取。

变换下语句成explain select cat_id,avg(shop_price) from ecs_goods where cat_id> 3 group by cat_id \G这样写就使用了cat_id的索引了。一旦使用了cat_id,Using temporary就不见了。

一次性的大块读取效率高(再在内存中排序),随机读取效率不高

###商城多列索引实验

我们给ecs_goods这张表中的cat_id、shop_price字段添加多列索引。

执行 select goods_id,shop_price from goods where cat_id=3 and shop price>3000;

把Profiling打开set profiling=1;(或者=on)

清除查询缓存 reset query cache;

然后 show profiles;

扫描行数减少,说明提速了。

聚簇索引,索引覆盖,innoDB与myisam的区别

###聚簇索引

innodb的次索引指向对主键的引用

myisam的次索引和主索引都指向物理行。

ps.【总结】都是非原创。向原创致敬。

###count优化

select count 在myisam下快,innodb比较慢。快是因为被系统的表给缓存过了。

###group by

###巧用变量减少查询

注意:

1.分组用于统计,而不用于筛选数据

比如:统计平均分,最高分,适合。但用于

参考资料:

分享到