mysql性能优化学习与实战-2

mysql性能分析

不论进行什么优化，观察系统运行情况为第一步

定时分析process

没有压测的情况下

[root@centos1 ~]# mysql -uroot -p123456 -e 'show processlist \G' *************************** 1. row ***************************     Id: 1   User: root   Host: localhost     db: NULLCommand: Query   Time: 0  State: NULL   Info: show processlist--只关心State这一数据信息[root@centos1 ~]# mysql -uroot -p123456 -e 'show processlist \G' \> | grep State \> | sort -rn \> | uniq  State: NULL

编写脚本定时分析mysql的process状态

#!/bin/bashwhile truedomysql -uroot -p123456 -e 'show processlist \G' | grep State | sort -rn | uniq >> proce.txt --结果保存usleep 100000 --0.1S观察一次done

下面运行此脚本，然后运行压测程序，见实战1，分析结果

[root@centos1 shell]# cat proce.txt | sort -r | uniq -c    104   State: Writing to net     116   State: Updating --线程正在寻找要更新的行和更新他们。     50   State: update --修改数据    353   State: statistics --服务器计算统计数据,以制定一个查询执行计划。 如果一个线程处于这种状态很长一段时间,服务器可能是磁盘绑定执行其他工作。     18   State: Sorting result --对结果进行排序，未建立索引的列      3   State: Sending data --线程处理一个SELECT语句行,将数据发送到客户端。 因为在这个状态发生的操作往往执行大量的磁盘访问(读取),它往往是在一个给定的查询的生命周期最长的运行状态。      1   State: Opening tables   1022   State: NULL --这种状态是用于SHOW PROCESSLIST状态。      1   State: login --连接线程的初始状态,直到客户端已成功通过身份验证。      3   State: Copying to tmp table --将数据放入临时表；如果group by 的列没有索引,必产生内部临时表,如果order by 与group by为不同列时,或多表联查时order by ,group by 包含的列不是第一张表的列,将会产生临时表      1   State: removing tmp table --线程是消除处理后,内部的SELECT表SELECT语句。 如果没有创建临时表,这种状态是不使用。      2   State: optimizing --系统进行查询语句优化      2   State: Creating tmp table --创建临时表      15   State: Creating sort index--线程正在处理一个SELECT就是使用内部临时表解决。      3   State: cleaning up --线程处理一个命令,并正准备以释放内存和重置某些状态变量。      6   State: 

耗时的地方发送数据给客户端、数据统计、创建临时表、排序
我们应该避免大批量数据传输，数据统计、排序优先考虑组合索引
如下，500W的数据

mysql> set profiling=on; mysql> select max(k) from sbtest;+--------+| max(k) |+--------+|     14 |+--------+1 row in set (6.06 sec)mysql> show profile;             +----------------------+----------+| Status               | Duration |+----------------------+----------+| starting             | 0.004926 || checking permissions | 0.000094 || Opening tables       | 0.000030 || System lock          | 0.000017 || init                 | 0.001631 || optimizing           | 0.000024 || statistics           | 0.000020 || preparing            | 0.000013 || executing            | 0.000020 || Sending data         | 6.047464 || end                  | 0.000010 || query end            | 0.000004 || closing tables       | 0.000006 || freeing items        | 0.000116 || logging slow query   | 0.000003 || cleaning up          | 0.000002 |+----------------------+----------+16 rows in set (0.00 sec)

mysql> explain select * from sbtest group by id%20 order by pad \G; *************************** 1. row ***************************           id: 1  select_type: SIMPLE --简单查询        table: sbtest         type: ALL --全表扫描，如果用了索引就是indexpossible_keys: NULL --可用的索引          key: NULL --实际用到的索引      key_len: NULL          ref: NULL         rows: 5000071 --检索行数        Extra: Using temporary; Using filesort --用了临时表，用了文件排序1 row in set (0.00 sec)

分析：使用了临时表是因为，虽然ID是已经排好序的，但是id%20并未排序，首先得计算它，把计算结果保存，然后才能分组，filesort是因为pad没有索引，需要进行排序

mysql> show profile;+----------------------+----------+| Status               | Duration |+----------------------+----------+| starting             | 0.000284 || checking permissions | 0.000016 || Opening tables       | 0.000089 || System lock          | 0.000022 || init                 | 0.000037 || optimizing           | 0.000006 || statistics           | 0.000023 || preparing            | 0.000013 || Creating tmp table   | 0.000058 || executing            | 0.000004 || Copying to tmp table | 6.667907 |--主要耗时| Sorting result       | 0.004117 || Sending data         | 0.001231 |--本地，所以此处耗时少| end                  | 0.000003 || removing tmp table   | 0.000005 || end                  | 0.000002 || query end            | 0.000003 || closing tables       | 0.000023 || freeing items        | 0.001878 || logging slow query   | 0.000002 || cleaning up          | 0.000002 |+----------------------+----------+21 rows in set (0.00 sec)

mysql创建索引语法

ALTER TABLE table_name ADD INDEX index_name (column_list)ALTER TABLE table_name ADD UNIQUE (column_list)ALTER TABLE table_name ADD PRIMARY KEY (column_list)

mysql列类型选取原则

列选择原则:
1:字段类型优先级 整型 > date,time > enum,char>varchar > blob
列的特点分析:
整型: 定长,没有国家/地区之分,没有字符集的差异
time定长,运算快,节省空间. 考虑时区,写sql时不方便 where > ‘2005-10-12’;
enum: 能起来约束值的目的, 内部用整型来存储,但与char联查时,内部要经历串与值的转化
Char 定长, 考虑字符集和(排序)校对集
varchar, 不定长 要考虑字符集的转换与排序时的校对集,速度慢.
text/Blob 无法使用内存临时表

附: 关于date/time的选择,大师的明确意见mysql-date-type

性别: 以utf8为例
char(1) , 3个字长字节
enum(‘男’,’女’); // 内部转成数字来存,多了一个转换过程
tinyint() , // 0 1 2 // 定长1个字节.

2: 够用就行,不要慷慨 (如smallint,varchar(N))
原因: 大的字段浪费内存,影响速度,
以年龄为例 tinyint unsigned not null ,可以存储255岁,足够. 用int浪费了3个字节
以varchar(10) ,varchar(300)存储的内容相同, 但在表联查时,varchar(300)要花更多内存

3: 尽量避免用NULL()
原因: NULL不利于索引,要用特殊的字节来标注.
在磁盘上占据的空间其实更大.

Enum列的说明
1: enum列在内部是用整型来储存的
2: enum列与enum列相关联速度最快
3: enum列比(var)char 的弱势—在碰到与char关联时,要转化. 要花时间.
4: 优势在于,当char非常长时,enum依然是整型固定长度.
当查询的数据量越大时,enum的优势越明显.
5: enum与char/varchar关联 ,因为要转化,速度要比enum->enum,char->char要慢,
但有时也这样用—–就是在数据量特别大时,可以节省IO.
原因—-无论enum(‘manmaman’,’womanwomanwoman’) 枚举的字符多长,内部都是用整型表示, 在内存中产生的数据大小不变,而char型,却在内存中产生的数据越来越多.

mysql索引优化策略

索引类型

B-tree索引

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

但抽象一下—B-tree系统,可理解为”排好序的快速查找结构”.

hash索引

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

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

1. hash函数计算后的结果,是随机的,如果是在磁盘上放置数据,比主键为id为例, 那么随着id的增长, id对应的行,在磁盘上随机放置.
2. 不法对范围查询进行优化.
3. 无法利用前缀索引. 比如 在btree中, field列的值“hellopworld”,并加索引查询 xx=helloword,自然可以利用索引, xx=hello,也可以利用索引. (左前缀索引)因为hash(‘helloword’),和hash(‘hello’),两者的关系仍为随机
4. 排序也无法优化.
5. 必须回行.就是说 通过索引拿到数据位置,必须回到表中取数据

btree索引的常见误区

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

例: where cat_id=3 and price>100 ; //查询第3个栏目,100元以上的商品
误: cat_id上,和,price上都加上索引.
错: 只能用上cat_id或Price索引,因为是独立的索引,同时只能用上1个.

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

误: 多列索引上,索引发挥作用,需要满足左前缀要求.
以 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 是,使用了abc
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 ‘xxxx%’and c=7 A能用,B能用,C不能用

案例

假设某个表有一个联合索引（c1,c2,c3,c4）一下——只能使用该联合索引的c1,c2,c3部分
A where c1=x and c2=x and c4>x and c3=x
B where c1=x and c2=x and c4=x order by c3
C where c1=x and c4= x group by c3,c2
D where c1=x and c5=x order by c2,c3
E where c1=x and c2=x and c5=? order by c2,c3

create table t1(    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 '',    key(c1,c2,c3,c4))engine myisam charset utf8;insert into t1 values('a','b','c','d','e');insert into t1 values('A','B','C','D','E');insert into t1 values('a','B','c','D','E');insert into t1 values('A','b','C','d','E');mysql> explain select * from t1\G;*************************** 1. row ***************************           id: 1  select_type: SIMPLE        table: t1   partitions: NULL         type: ALLpossible_keys: NULL          key: NULL      key_len: NULL          ref: NULL         rows: 4     filtered: 100.00        Extra: NULL1 row in set, 1 warning (0.00 sec)mysql> explain select * from t1 where c1='a' and c2='b' and c4>'a' and c3='c' \G;*************************** 1. row ***************************           id: 1  select_type: SIMPLE        table: t1   partitions: NULL         type: rangepossible_keys: c1          key: c1      key_len: 12          ref: NULL         rows: 3     filtered: 100.00        Extra: Using index condition1 row in set, 1 warning (0.02 sec)mysql> explain select * from t1 where c1='a' and c2='b' and c4='a' order by c3 \G;*************************** 1. row ***************************           id: 1  select_type: SIMPLE        table: t1   partitions: NULL         type: refpossible_keys: c1          key: c1      key_len: 6          ref: const,const         rows: 3     filtered: 25.00        Extra: Using index condition1 row in set, 1 warning (0.00 sec)mysql> explain select * from t1 where c1='a' and c2='b' and c4='a' order by c5 \G;*************************** 1. row ***************************           id: 1  select_type: SIMPLE        table: t1   partitions: NULL         type: refpossible_keys: c1          key: c1      key_len: 6          ref: const,const         rows: 3     filtered: 25.00        Extra: Using index condition; Using filesort--c5无序，需要经历一次排序1 row in set, 1 warning (0.00 sec)mysql> explain select * from t1 where c1='a' and c5='e' order by c2,c3 \G;                *************************** 1. row ***************************           id: 1  select_type: SIMPLE        table: t1   partitions: NULL         type: refpossible_keys: c1          key: c1      key_len: 3          ref: const         rows: 3     filtered: 25.00        Extra: Using index condition; Using where1 row in set, 1 warning (0.00 sec)mysql> explain select * from t1 where c1='a' and c5='e' order by c3,c2 \G;*************************** 1. row ***************************           id: 1  select_type: SIMPLE        table: t1   partitions: NULL         type: refpossible_keys: c1          key: c1      key_len: 3          ref: const         rows: 3     filtered: 25.00        Extra: Using index condition; Using where; Using filesort1 row in set, 1 warning (0.00 sec)mysql> explain select c3,c2 from t1 where c1='a' and c4='b' group by c2,c3 \G; *************************** 1. row ***************************           id: 1  select_type: SIMPLE        table: t1   partitions: NULL         type: refpossible_keys: c1          key: c1      key_len: 3          ref: const         rows: 3     filtered: 25.00        Extra: Using where; Using index1 row in set, 1 warning (0.00 sec)mysql> explain select c3,c2 from t1 where c1='a' and c4='b' group by c2,c3 \G;*************************** 1. row ***************************           id: 1  select_type: SIMPLE        table: t1   partitions: NULL         type: refpossible_keys: c1          key: c1      key_len: 3          ref: const         rows: 3     filtered: 25.00        Extra: Using where; Using index1 row in set, 1 warning (0.00 sec)mysql> explain select c3,c2 from t1 where c1='a' and c4='b' group by c3,c2 \G;*************************** 1. row ***************************           id: 1  select_type: SIMPLE        table: t1   partitions: NULL         type: refpossible_keys: c1          key: c1      key_len: 3          ref: const         rows: 3     filtered: 25.00        Extra: Using where; Using index; Using temporary; Using filesort1 row in set, 1 warning (0.00 sec)mysql> explain select c3,c2 from t1 where c1='a' and c2='b'and c5='e' order by c3,c2 \G;      *************************** 1. row ***************************           id: 1  select_type: SIMPLE        table: t1   partitions: NULL         type: refpossible_keys: c1          key: c1      key_len: 6          ref: const,const         rows: 3     filtered: 25.00        Extra: Using index condition; Using where1 row in set, 1 warning (0.00 sec)mysql> explain select c3,c2 from t1 where c1='a' and c5='e' order by c3,c2 \G;          *************************** 1. row ***************************           id: 1  select_type: SIMPLE        table: t1   partitions: NULL         type: refpossible_keys: c1          key: c1      key_len: 3          ref: const         rows: 3     filtered: 25.00        Extra: Using index condition; Using where; Using filesort1 row in set, 1 warning (0.01 sec)

一道面试题:
有商品表, 有主键,goods_id, 栏目列 cat_id, 价格price
说:在价格列上已经加了索引,但按价格查询还是很慢,
问可能是什么原因,怎么解决?

答: 在实际场景中,一个电商网站的商品分类很多,直接在所有商品中,按价格查商品,是极少的,一般客户都来到分类下,然后再查. 改正:
去掉单独的Price列的索引, 加 (cat_id,price)复合索引 再查询.

聚簇索引/索引覆盖

create table a(
id varchar(64) primary key,
ver int,
…
)
在ID，ver上有联合索引
1000条数据
为什么select id from a order by id比select id from a order by id,ver慢

innodb的主索引文件上 直接存放该行数据,称为聚簇索引,次索引指向对主键的引用
myisam中, 主索引和次索引,都指向物理行(磁盘位置).

注意: innodb来说,
1: 主键索引 既存储索引值,又在叶子中存储行的数据
2: 如果没有主键, 则会Unique key做主键
3: 如果没有unique,则系统生成一个内部的rowid做主键.
4: 像innodb中,主键的索引结构中,既存储了主键值,又存储了行数据,这种结构称为”聚簇索引”

聚簇索引
优势: 根据主键查询条目比较少时,不用回行(数据就在主键节点下)
劣势: 如果碰到不规则数据插入时,造成频繁的页分裂.

1. innodb的buffer_page 很强大.
2. 聚簇索引的主键值,应尽量是连续增长的值,而不是要是随机值, (不要用随机字符串或UUID)，否则会造成大量的页分裂与页移动。
3. 高性能索引策略，对于innodb而言,因为节点下有数据文件,因此节点的分裂将会比较慢。对于innodb的主键,尽量用整型,而且是递增的整型。如果是无规律的数据,将会产生的页的分裂,影响速度.

索引覆盖:
索引覆盖是指 如果查询的列恰好是索引的一部分,那么查询只需要在索引文件上进行,不需要回行到磁盘再找数据。这种查询速度非常快,称为”索引覆盖”

案例分析：
1、innodb引擎
2、多个比较长的列
3、聚簇索引，导致沿着ID排序，要跨越好多小文件
4、比较长的列，块比较多
如果没有大字段，也基本上没有差别

附录

mysql innodb索引存储介绍