表的优化与列类型选择
表的优化:
1: 定长与变长分离
如 id int, 占4个字节, char(4) 占4个字符长度,也是定长, time
即每一单元值占的字节是固定的.
核心且常用字段,宜建成定长,放在一张表.
而varchar, text,blob,这种变长字段,适合单放一张表, 用主键与核心表关联起来.
2:常用字段和不常用字段要分离.
需要结合网站具体的业务来分析,分析字段的查询场景,查询频度低的字段,单拆出来.
3:合理添加冗余字段.
看如下BBS的效果
每个版块里,有N条帖子, 在首页,显示了版块信息,和版块下的帖子数.
这是如何做的?
boardid | boardname | postnum |
8 | 开班信息 | 2 |
9 | 每日视频及代码 | 1 |
postid | boardid | title |
123 | 8 | 论坛开张了 |
129 | 8 | 灌水 |
133 | 9 | 来一帖 |
如果board表只有前2列,则需要取出版块后,
再查post表,select count(*) from post group by board_id,得出每个版块下的帖子数.
如果有postnum字段,每发一个帖子时,对postnum字段+1;
再查询版块下的帖子数时, 只需要1条语句直接查boardid
select boradid, boardname,postnum from board;
典型的”空间换时间”
列选择原则:
1:字段类型优先级 整型 > date,time > enum,char>varchar > blob
列的特点分析:
整型: 定长,没有国家/地区之分,没有字符集的差异
time定长,运算快,节省空间. 考虑时区,写sql时不方便 where > ‘2005-10-12’;
enum: 能起来约束值的目的, 内部用整型来存储,但与char联查时,内部要经历串与值的转化
Char 定长, 考虑字符集和(排序)校对集
varchar, 不定长 要考虑字符集的转换与排序时的校对集,速度慢.
text/Blob 无法使用内存临时表
附: 关于date/time的选择,大师的明确意见
http://www.xaprb.com/blog/2014/01/30/timestamps-in-mysql/
性别: 以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不利于索引,要用特殊的字节来标注.
在磁盘上占据的空间其实更大.
实验:
可以建立2张字段相同的表,一个允许为null,一个不允许为Null,各加入1万条,查看索引文件的大小. 可以发现,为null的索引要大些.(mysql5.5里,关于null已经做了优化,大小区别已不明显)
另外: null也不便于查询,
where 列名=null;
where 列名!=null; 都查不到值,
where 列名 is null ,或is not null 才可以查询.
create table dictnn (
id int,
word varchar(14) not null default '',
key(word)
)engine myisam charset utf8;
create table dictyn (
id int,
word varchar(14),
key(word)
)engine myisam charset utf8;
alter table dictnn disable keys;
alter table dictyn disable keys;
insert into dictnn select id,if(id%2,word,'') from dict limit 10000;
insert into dictyn select id,if(id%2,word,null) from dict limit 10000;
alert table dictnn enable keys;
alter table dictyn enable keys;
Enum列的说明
1: enum列在内部是用整型来储存的
2: enum列与enum列相关联速度最快
3: enum列比(var)char 的弱势---在碰到与char关联时,要转化. 要花时间.
4: 优势在于,当char非常长时,enum依然是整型固定长度.
当查询的数据量越大时,enum的优势越明显.
5: enum与char/varchar关联 ,因为要转化,速度要比enum->enum,char->char要慢,
但有时也这样用-----就是在数据量特别大时,可以节省IO.
试验:
create table t2 (
id int,
gender enum('man','woman'),
key(gender)
)engine myisam charset utf8;
create table t3 (
id int,
gender char(5) not null default '',
key(gender)
)engine myisam charset utf8;
alter table t2 disable keys;
alter table t3 disable keys;
insert into t2 select id,if(id%2,'man','woman') from dict limit 10000;
insert into t3 select id,if(id%2,'man','woman') from dict limit 10000;
alter table t2 enable keys;
alter table t3 enable keys;
mysql> select count(*) from t2 as ta,t2 as tb where ta.gender=tb.gender
mysql> select count(*) from t3 as ta,t3 as tb where ta.gender=tb.gender
列<---->列 | 时间 |
Enum<--->enum | 10.53 |
Char<---->char | 24.65 |
Enum<---->char | 18.22 |
如果t2表的优势不明显, 加大t3的gender列 ,char(15), char(20)...
随着t3 gender列的变大,t2表优势逐渐明显.
原因----无论enum(‘manmaman’,’womanwomanwoman’) 枚举的字符多长,内部都是用整型表示, 在内存中产生的数据大小不变,而char型,却在内存中产生的数据越来越多.
总结: enum 和enum类型关联速度比较快
Enum 类型 节省了IO
索引优化策略
1:索引类型
1.1 B-tree索引
注: 名叫btree索引,大的方面看,都用的平衡树,但具体的实现上, 各引擎稍有不同,
比如,严格的说,NDB引擎,使用的是T-tree
Myisam,innodb中,默认用B-tree索引
但抽象一下---B-tree系统,可理解为”排好序的快速查找结构”.
1.2 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: 必须回行.就是说 通过索引拿到数据位置,必须回到表中取数据
2: btree索引的常见误区
2.1 在where条件常用的列上都加上索引
例: where cat_id=3 and price>100 ; //查询第3个栏目,100元以上的商品
误: cat_id上,和, price上都加上索引.
错: 只能用上cat_id或Price索引,因为是独立的索引,同时只能用上1个.
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 | 是,使用了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不能用 |
为便于理解, 假设ABC各10米长的木板, 河面宽30米.
全值索引是则木板长10米,
Like,左前缀及范围查询, 则木板长6米,
自己拼接一下,能否过河对岸,就知道索引能否利用上.
如上例中, where a=3 and b>10, and c=7,
A板长10米,A列索引发挥作用
A板正常接B板, B板索引发挥作用
B板短了,接不到C板, 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 t4 (
c1 tinyint(1) not null default 0,
c2 tinyint(1) not null default 0,
c3 tinyint(1) not null default 0,
c4 tinyint(1) not null default 0,
c5 tinyint(1) not null default 0,
index c1234(c1,c2,c3,c4)
);
insert into t4 values (1,3,5,6,7),(2,3,9,8,3),(4,3,2,7,5);
对于A:
c1=x and c2=x and c4>x and c3=x <==等价==> c1=x and c2=x and c3=x and c4>x
因此 c1,c2,c3,c4都能用上. 如下:
mysql> explain select * from t4 where c1=1 and c2=2 and c4>3 and c3=3 \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t4
type: range
possible_keys: c1234
key: c1234
key_len: 4 #可以看出c1,c2,c3,c4索引都用上
ref: NULL
rows: 1
Extra: Using where
对于B: select * from t4 where c1=1 and c2=2 and c4=3 order by c3
c1 ,c2索引用上了,在c2用到索引的基础上,c3是排好序的,因此不用额外排序.
而c4没发挥作用.
mysql> explain select * from t4 where c1=1 and c2=2 and c4=3 order by c3 \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t4
type: ref
possible_keys: c1234
key: c1234
key_len: 2
ref: const,const
rows: 1
Extra: Using where
1 row in set (0.00 sec)
mysql> explain select * from t4 where c1=1 and c2=2 and c4=3 order by c5 \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t4
type: ref
possible_keys: c1234
key: c1234
key_len: 2
ref: const,const
rows: 1
Extra: Using where; Using filesort
1 row in set (0.00 sec)
对于 C: 只用到c1索引,因为group by c3,c2的顺序无法利用c2,c3索引
mysql> explain select * from t4 where c1=1 and c4=2 group by c3,c2 \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t4
type: ref
possible_keys: c1234
key: c1234
key_len: 1 #只用到c1,因为先用c3后用c2分组,导致c2,c3索引没发挥作用
ref: const
rows: 1
Extra: Using where; Using temporary; Using filesort
1 row in set (0.00 sec)
mysql> explain select * from t4 where c1=1 and c4=2 group by c2,c3 \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t4
type: ref
possible_keys: c1234
key: c1234
key_len: 1
ref: const
rows: 1
Extra: Using where
1 row in set (0.00 sec)
D语句: C1确定的基础上,c2是有序的,C2之下C3是有序的,因此c2,c3发挥的排序的作用.
因此,没用到filesort
mysql> explain select * from t4 where c1=1 and c5=2 order by c2,c3 \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t4
type: ref
possible_keys: c1234
key: c1234
key_len: 1
ref: const
rows: 1
Extra: Using where
1 row in set (0.00 sec)
E: 这一句等价与 elect * from t4 where c1=1 and c2=3 and c5=2 order by c3;
因为c2的值既是固定的,参与排序时并不考虑
mysql> explain select * from t4 where c1=1 and c2=3 and c5=2 order by c2,c3 \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t4
type: ref
possible_keys: c1234
key: c1234
key_len: 2
ref: const,const
rows: 1
Extra: Using where
1 row in set (0.00 sec)
一道面试题:
有商品表, 有主键,goods_id, 栏目列 cat_id, 价格price
说:在价格列上已经加了索引,但按价格查询还是很慢,
问可能是什么原因,怎么解决?
答: 在实际场景中,一个电商网站的商品分类很多,直接在所有商品中,按价格查商品,是极少的,一般客户都来到分类下,然后再查.
改正: 去掉单独的Price列的索引, 加 (cat_id,price)复合索引
再查询.
聚簇索引与非聚簇索引
Myisam与innodb引擎,索引文件的异同
===============================================================
innodb的主索引文件上 直接存放该行数据,称为聚簇索引,次索引指向对主键的引用
myisam中, 主索引和次索引,都指向物理行(磁盘位置).
注意: innodb来说,
1: 主键索引 既存储索引值,又在叶子中存储行的数据
2: 如果没有主键, 则会Unique key做主键
3: 如果没有unique,则系统生成一个内部的rowid做主键.
4: 像innodb中,主键的索引结构中,既存储了主键值,又存储了行数据,这种结构称为”聚簇索引”
聚簇索引
优势: 根据主键查询条目比较少时,不用回行(数据就在主键节点下)
劣势: 如果碰到不规则数据插入时,造成频繁的页分裂.
C) 聚簇索引的页分裂过程
实验: 聚簇索引使用随机值导致页频繁分裂影响速度
过程:建立innodb表, 利用php连接mysql,
分别规则插入10000条数据,不规则插入10000条数据
观察时间的差异,体会聚簇索引,页分裂的影响.
create table t5(
id int primary key,
c1 varchar(500),
c2 varchar(500),
c3 varchar(500),
c4 varchar(500),
c5 varchar(500),
c6 varchar(500)
) engine innodb charset utf8;
create table t6(
id int primary key,
c1 varchar(500),
c2 varchar(500),
c3 varchar(500),
c4 varchar(500),
c5 varchar(500),
c6 varchar(500)
) engine innodb charset utf8;
// testinnodb.php
$time_start = microtime_float();
$str = str_repeat('hello',100);
for($i=1;$i<=10000;$i++) {
$sql = "insert into t5 values ($i,'$str' , '$str' , '$str' , '$str' , '$str' , '$str'
)";
//echo $sql;
mysql_query($sql , $conn);
}
$time_end = microtime_float();
echo 'seq insert cost' , ($time_end - $time_start) , "seconds\n";
function microtime_float()
{
list($usec, $sec) = explode(" ", microtime());
return ((float)$usec + (float)$sec);
}
// rndinnodb.php
$base = range(1,10000);
shuffle($base);
$time_start = microtime_float();
$str = str_repeat('hello',100);
foreach($base as $i) {
$sql = "insert into t6 values ($i,'$str' , '$str' , '$str' , '$str' , '$str' , '$str'
)";
//echo $sql;
mysql_query($sql , $conn);
}
$time_end = microtime_float();
echo 'rand insert cost' , ($time_end - $time_start) , "seconds\n";
function microtime_float()
{
list($usec, $sec) = explode(" ", microtime());
return ((float)$usec + (float)$sec);
}
字段数 | 混乱程度(步长) | 顺序1000条(秒数) | 乱序1000条(秒数) | 顺序写入page页数 | 乱序写入page数 |
1 | 1 | 54.365 | 53.438 | 62 | 91 |
10 | 1 | 53.413 | 62.940 | 235 | 1301 |
10 | 100 |
| 64.18 |
| 1329 |
10 | 1000 |
| 67.512 |
| 1325 |
通过上面的规律可以看出-----
1: innodb的buffer_page 很强大.
2: 聚簇索引的主键值,应尽量是连续增长的值,而不是要是随机值,
(不要用随机字符串或UUID)
否则会造成大量的页分裂与页移动.
高性能索引策略
0:对于innodb而言,因为节点下有数据文件,因此节点的分裂将会比较慢.
对于innodb的主键,尽量用整型,而且是递增的整型.
如果是无规律的数据,将会产生的页的分裂,影响速度.
索引覆盖:
索引覆盖是指 如果查询的列恰好是索引的一部分,那么查询只需要在索引文件上进行,不需要回行到磁盘再找数据.
这种查询速度非常快,称为”索引覆盖”
理想的索引
1:查询频繁 2:区分度高 3:长度小 4: 尽量能覆盖常用查询字段.
1: 索引长度直接影响索引文件的大小,影响增删改的速度,并间接影响查询速度(占用内存多).
针对列中的值,从左往右截取部分,来建索引
1: 截的越短, 重复度越高,区分度越小, 索引效果越不好
2: 截的越长, 重复度越低,区分度越高, 索引效果越好,但带来的影响也越大--增删改变慢,并间影响查询速度.
所以, 我们要在 区分度 + 长度 两者上,取得一个平衡.
惯用手法: 截取不同长度,并测试其区分度,
mysql> select count(distinct left(word,6))/count(*) from dict;
+---------------------------------------+
| count(distinct left(word,6))/count(*) |
+---------------------------------------+
| 0.9992 |
+---------------------------------------+
1 row in set (0.30 sec)
对于一般的系统应用: 区别度能达到0.1,索引的性能就可以接受.
2:对于左前缀不易区分的列 ,建立索引的技巧
1: 把列内容倒过来存储,并建立索引
2: 伪hash索引效果
同时存 url_hash列
3:多列索引
3.1 多列索引的考虑因素---
列的查询频率 , 列的区分度,
以ecshop商城为例, goods表中的cat_id,brand_id,做多列索引
从区分度看,Brand_id区分度更高,
mysql> select count(distinct cat_id) / count(*) from goods;
+-----------------------------------+
| count(distinct cat_id) / count(*) |
+-----------------------------------+
| 0.2903 |
+-----------------------------------+
1 row in set (0.00 sec)
mysql> select count(distinct brand_id) / count(*) from goods;
+-------------------------------------+
| count(distinct brand_id) / count(*) |
+-------------------------------------+
| 0.3871 |
+-------------------------------------+
1 row in set (0.00 sec)
但从 商城的实际业务业务看, 顾客一般先选大分类->小分类->品牌,
最终选择 index(cat_id,brand_id)来建立索引
有如下表(innodb引擎), sql语句在笔记中,
给定日照市,查询子地区, 且查询子地区的功能非常频繁,
如何优化索引及语句?
+------+-----------+------+
| id | name | pid |
+------+-----------+------+
| .... | .... | .... |
| 1584 | 日照市 | 1476 |
| 1586 | 东港区 | 1584 |
| 1587 | 五莲县 | 1584 |
| 1588 | 莒县 | 1584 |
+------+-----------+------+
1: 不加任何索引,自身连接查询
mysql> explain select s.id,s.name from it_area as p inner join it_area as s on p.id=s.pid where p.name='日照市' \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: p
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 3263
Extra: Using where
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: s
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 3263
Extra: Using where; Using join buffer
2 rows in set (0.00 sec)
2: 给name加索引
mysql> explain select s.id,s.name from it_area as p inner join it_area as s on p.id=s.pid where p.name='日照市' \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: p
type: ref
possible_keys: name
key: name
key_len: 93
ref: const
rows: 1
Extra: Using where
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: s
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 3243
Extra: Using where; Using join buffer
2 rows in set (0.00 sec)
3: 在Pid上也加索引
mysql> explain select s.id,s.name from it_area as p inner join it_area as s on p.id=s.pid where p.name='日照市' \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: p
type: ref
possible_keys: name
key: name
key_len: 93
ref: const
rows: 1
Extra: Using where
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: s
type: ref
possible_keys: pid
key: pid
key_len: 5
ref: big_data.p.id
rows: 4
Extra: Using where
2 rows in set (0.00 sec)
延迟关联
mysql> select * from it_area where name like '%东山%';
+------+-----------+------+
| id | name | pid |
+------+-----------+------+
| 757 | 东山区 | 751 |
| 1322 | 东山县 | 1314 |
| 2118 | 东山区 | 2116 |
| 3358 | 东山区 | 3350 |
+------+-----------+------+
4 rows in set (0.00 sec)
分析: 这句话用到了索引覆盖没有?
答: 没有,1 查询了所有列, 没有哪个索引,覆盖了所有列.
2 like %xx%”,左右都是模糊查询, name本身,都没用上索引
第2种做法:
select a.* from it_area as a inner join (select id from it_area where name like '%东山%') as t on a.id=t.id;
Show profiles; 查看效率:
| 18 | 0.00183800 | select * from it_area where name like '%东山%'
| 20 | 0.00169300 | select a.* from it_area as a inner join (select id from it_area where name like '%东山%') as t on a.id=t.id |
发现 第2种做法,虽然语句复杂,但速度却稍占优势.
第2种做法中, 内层查询,只沿着name索引层顺序走, name索引层包含了id值的.
所以,走完索引层之后,找到所有合适的id,
再通过join, 用id一次性查出所有列. 走完name列再取.
第1种做法: 沿着name的索引文件走, 走到满足的条件的索引,就取出其id,
并通过id去取数据, 边走边取.
通过id查找行的过程被延后了. --- 这种技巧,称为”延迟关联”.
索引与排序
排序可能发生2种情况:
1: 对于覆盖索引,直接在索引上查询时,就是有顺序的, using index
2: 先取出数据,形成临时表做filesort(文件排序,但文件可能在磁盘上,也可能在内存中)
我们的争取目标-----取出来的数据本身就是有序的! 利用索引来排序.
比如: goods商品表, (cat_id,shop_price)组成联合索引,
where cat_id=N order by shop_price ,可以利用索引来排序,
select goods_id,cat_id,shop_price from goods order by shop_price;
// using where,按照shop_price索引取出的结果,本身就是有序的.
select goods_id,cat_id,shop_price from goods order by click_count;
// using filesort 用到了文件排序,即取出的结果再次排序
重复索引与冗余索引
重复索引: 是指 在同1个列(如age), 或者 顺序相同的几个列(age,school), 建立了多个索引,
称为重复索引, 重复索引没有任何帮助,只会增大索引文件,拖慢更新速度, 去掉.
冗余索引:
冗余索引是指2个索引所覆盖的列有重叠, 称为冗余索引
比如 x,m,列 , 加索引 index x(x), index xm(x,m)
x,xm索引, 两者的x列重叠了, 这种情况,称为冗余索引.
甚至可以把 index mx(m,x) 索引也建立, mx, xm 也不是重复的,因为列的顺序不一样.
索引碎片与维护
在长期的数据更改过程中, 索引文件和数据文件,都将产生空洞,形成碎片.
我们可以通过一个nop操作(不产生对数据实质影响的操作), 来修改表.
比如: 表的引擎为innodb , 可以 alter table xxx engine innodb
optimize table 表名 ,也可以修复.
注意: 修复表的数据及索引碎片,就会把所有的数据文件重新整理一遍,使之对齐.
这个过程,如果表的行数比较大,也是非常耗费资源的操作.
所以,不能频繁的修复.
如果表的Update操作很频率,可以按周/月,来修复.
如果不频繁,可以更长的周期来做修复.
sql语句优化
1: sql语句的时间花在哪儿?
答: 等待时间 , 执行时间.
这两个时间并非孤立的, 如果单条语句执行的快了,对其他语句的锁定的也就少了.
所以,我们来分析如何降低执行时间.
2: sql语句的执行时间,又花在哪儿了?
答：
a: 查 ----> 沿着索引查,甚至全表扫描
b: 取 ----> 查到行后,把数据取出来(sending data)
3: sql语句的优化思路？
答: 不查, 通过业务逻辑来计算,
比如论坛的注册会员数,我们可以根据前3个月统计的每天注册数, 用程序来估算.
少查, 尽量精准数据,少取行. 我们观察新闻网站,评论内容等,一般一次性取列表 10-30条左右.
必须要查,尽量走在索引上查询行.
取时, 取尽量少的列.
比如 select * from tableA, 就取出所有列, 不建议.
比如 select * from tableA,tableB, 取出A,B表的所有列.
4: 如果定量分析查的多少行,和是否沿着索引查?
答: 用explain来分析
explain的列分析
id: 代表select 语句的编号, 如果是连接查询,表之间是平等关系, select 编号都是1,从1开始. 如果某select中有子查询,则编号递增.
mysql> explain select goods_id,goods_name from goods where goods_id in (sele
ct goods_id from goods where cat_id=4) \G
*************************** 1. row ***************************
id: 1
select_type: PRIMARY
table: goods
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 31
Extra: Using where
*************************** 2. row ***************************
id: 2
select_type: DEPENDENT SUBQUERY
table: goods
type: unique_subquery
possible_keys: PRIMARY,cat_id
key: PRIMARY
key_len: 3
ref: func
rows: 1
Extra: Using where
2 rows in set (0.00 sec)
select_type: 查询类型
table: 查询针对的表
有可能是
实际的表名 如select * from t1;
表的别名 如 select * from t2 as tmp;
derived 如from型子查询时
null 直接计算得结果,不用走表
possible_key: 可能用到的索引
注意: 系统估计可能用的几个索引,但最终,只能用1个.
key : 最终用的索引.
key_len: 使用的索引的最大长度
type列: 是指查询的方式, 非常重要,是分析”查数据过程”的重要依据
可能的值
all: 意味着从表的第1行,往后,逐行做全表扫描.,运气不好扫描到最后一行.
例: 把goods_name列上的索引去掉, 并根据goods_name来查询
mysql> explain select goods_name from goods where goods_name='诺基亚N85' \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: goods
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 31
Extra: Using where
1 row in set (0.00 sec)
index: 比all性能稍好一点，
通俗的说: all 扫描所有的数据行,相当于data_all index 扫描所有的索引节点,相当于index_all
２种情况可能出现：
１：索引覆盖的查询情况下, 能利用上索引,但是又必须全索引扫描.
mysql> explain select goods_id from goods where goods_id=1 or goods_id+1>20
\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: goods
type: index
possible_keys: PRIMARY
key: PRIMARY
key_len: 3
ref: NULL
rows: 31
Extra: Using where; Using index
1 row in set (0.00 sec)
mysql> explain select goods_id,click_count from goods where goods_id=1 or go
ods_id+1>20 \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: goods
type: ALL
possible_keys: PRIMARY
key: NULL
key_len: NULL
ref: NULL
rows: 31
Extra: Using where
1 row in set (0.00 sec)
2: 是利用索引来进行排序,但取出所有的节点
select goods_id from goods order by goods_id desc;
分析: 没有加where条件, 就得取所有索引节点,同时,又没有回行,只取索引节点.
再排序,经过所有索引节点.
mysql> explain select goods_id from goods order by goods_id asc\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: goods
type: index
possible_keys: NULL
key: PRIMARY
key_len: 3
ref: NULL
rows: 31
Extra: Using index
1 row in set (0.00 sec)
range: 意思是查询时,能根据索引做范围的扫描
mysql> explain select goods_id,goods_name,shop_price from goods where goods
id >25 \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: goods
type: range
possible_keys: PRIMARY
key: PRIMARY
key_len: 3
ref: NULL
rows: 8
Extra: Using where
1 row in set (0.00 sec)
ref 意思是指 通过索引列,可以直接引用到某些数据行
mysql> explain select goods_id,goods_name from goods where cat_id=4 \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: goods
type: ref
possible_keys: cat_id
key: cat_id
key_len: 2
ref: const
rows: 3
Extra:
1 row in set (0.00 sec)
在这个例子中,通过cat_id索引 指向N行goods数据,来查得结果.
eq_ref 是指,通过索引列,直接引用某1行数据
常见于连接查询中
mysql> explain select goods_id,shop_price from goods innert join ecs_catego
y using(cat_id) where goods_id> 25 \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: innert
type: range
possible_keys: PRIMARY,cat_id
key: PRIMARY
key_len: 3
ref: NULL
rows: 8
Extra: Using where
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: ecs_category
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 2
ref: shop.innert.cat_id
rows: 1
Extra: Using index
2 rows in set (0.00 sec)
const, system, null 这3个分别指查询优化到常量级别, 甚至不需要查找时间.
一般按照主键来查询时,易出现const,system
或者直接查询某个表达式,不经过表时, 出现NULL
mysql> explain select goods_id,goods_name,click_count from goods wher
_id=4 \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: goods
type: const
possible_keys: PRIMARY
key: PRIMARY
key_len: 3
ref: const
rows: 1
Extra:
1 row in set (0.00 sec)
mysql> explain select max(goods_id) from goods \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: NULL
type: NULL myisam表的max,min,count在表中优化过,不需要\真正查找,为NULL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: NULL
Extra: Select tables optimized away
1 row in set (0.00 sec)
ref列 指连接查询时, 表之间的字段引用关系.
mysql> explain select goods_id,cat_name,goods_name from goods inner join ec
_category using(cat_id) where ecs_category.cat_name='' \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: goods
type: ALL
possible_keys: cat_id
key: NULL
key_len: NULL
ref: NULL
rows: 31
Extra:
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: ecs_category
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 2
ref: shop. goods.cat_id
rows: 1
Extra: Using where
2 rows in set (0.00 sec)
rows : 是指估计要扫描多少行.
extra:
index: 是指用到了索引覆盖,效率非常高
using where 是指光靠索引定位不了,还得where判断一下
using temporary 是指用上了临时表, group by 与order by 不同列时,或group by ,order by 别的表的列.
using filesort : 文件排序(文件可能在磁盘,也可能在内存), (?????
select sum(shop_price) from goods group by cat_id(???? 这句话,用到了临时表和文件排序)
in 型子查询引出的陷阱
题: 在ecshop商城表中,查询6号栏目的商品, (注,6号是一个大栏目)
最直观的: mysql> select goods_id,cat_id,goods_name from goods where cat_id in (select
cat_id from ecs_category where parent_id=6);
误区: 给我们的感觉是, 先查到内层的6号栏目的子栏目,如7,8,9,11
然后外层, cat_id in (7,8,9,11)
事实: 如下图, goods表全扫描, 并逐行与category表对照,看parent_id=6是否成立
原因: mysql的查询优化器,针对In型做优化,被改成了exists的执行效果.
当goods表越大时, 查询速度越慢.
改进: 用连接查询来代替子查询
explain select goods_id,g.cat_id,g.goods_name from goods as g
inner join (select cat_id from ecs_category where parent_id=6) as t
using(cat_id) \G
内层 select cat_id from ecs_category where parent_id=6 ; 用到Parent_id索引, 返回4行
+--------+
| cat_id |
+--------+
| 7 |
| 8 |
| 9 |
| 11 |
+--------+ 形成结果,设为t
*************************** 3. row ***************************
id: 2
select_type: DERIVED
table: ecs_category
type: ref
possible_keys: parent_id
key: parent_id
key_len: 2
ref:
rows: 4
Extra:
3 rows in set (0.00 sec)
第2次查询,
t和 goods 通过 cat_id 相连,
因为cat_id在 goods表中有索引, 所以相当于用7,8,911,快速匹配上 goods的行.
*************************** 2. row ***************************
id: 1
select_type: PRIMARY
table: g
type: ref
possible_keys: cat_id
key: cat_id
key_len: 2
ref: t.cat_id
rows: 6
Extra:
第1次查询 :
是把上面2次的中间结果,直接取回.
*************************** 1. row ***************************
id: 1
select_type: PRIMARY
table: <derived2>
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 4
Extra:
exists子查询
题: 查询有商品的栏目.
按上面的理解,我们用join来操作,如下:
mysql> select c.cat_id,cat_name from ecs_category as c inner join goods as g
on c.cat_id=g.cat_id group by cat_name; (见36)
优化1: 在group时, 用带有索引的列来group, 速度会稍快一些,另外,
用int型 比 char型 分组,也要快一些.(见37)
优化2: 在group时, 我们假设只取了A表的内容,group by 的列,尽量用A表的列,
会比B表的列要快.(见38)
优化3: 从语义上去优化
select cat_id,cat_name from ecs_category where exists(select *from goods where goods.cat_id=ecs_category.cat_id) (见40)
| 36 | 0.00039075 | select c.cat_id,cat_name from ecs_category as c inner
join goods as g on c.cat_id=g.cat_id group by cat_name
|
| 37 | 0.00038675 | select c.cat_id,cat_name from ecs_category as c inner
join goods as g on c.cat_id=g.cat_id group by cat_id
|
| 38 | 0.00035650 | select c.cat_id,cat_name from ecs_category as c inner
join goods as g on c.cat_id=g.cat_id group by c.cat_id
|
| 40 | 0.00033500 | select cat_id,cat_name from ecs_category where exists
(select * from goods where goods.cat_id=ecs_category.cat_id)
|
from 型子查询:
注意::内层from语句查到的临时表, 是没有索引的.
所以: from的返回内容要尽量少.
奇技淫巧!
min/max优化 在表中,一般都是经过优化的. 如下地区表
id | area | pid |
1 | 中国 | 0 |
2 | 北京 | 1 |
... |
|
|
3115 |
| 3113 |
我们查min(id), id是主键,查Min(id)非常快.
但是,pid上没有索引, 现在要求查询3113地区的min(id);
select min(id) from it_area where pid=69;
试想 id是有顺序的,(默认索引是升续排列), 因此,如果我们沿着id的索引方向走,
那么 第1个 pid=69的索引结点,他的id就正好是最小的id.
select id from it_area use index(primary) where pid=69 limit 1;
| 12 | 0.00128100 | select min(id) from it_area where pid=69 |
| 13 | 0.00017000 | select id from it_area use index(primary) where pid=69 limit 1 |
改进后的速度虽然快,但语义已经非常不清晰,不建议这么做,仅仅是实验目的.
count() 优化
误区:
1:myisam的count()非常快
答: 是比较快,.但仅限于查询表的”所有行”比较快, 因为Myisam对行数进行了存储.
一旦有条件的查询, 速度就不再快了.尤其是where条件的列上没有索引.
2: 假如,id<100的商家都是我们内部测试的,我们想查查真实的商家有多少?
select count(*) from lx_com where id>=100; (1000多万行用了6.X秒)
小技巧:
select count(*) from lx_com; 快
select count(*) from lx_com where id<100; 快
select count(*) frol lx_com -select count(*) from lx_com where id<100; 快
select (select count(*) from lx_com) - (select count(*) from lx_com where id<100)
3: group by
注意:
1:分组用于统计,而不用于筛选数据.
比如: 统计平均分,最高分,适合, 但用于筛选重复数据,则不适合.
以及用索引来避免临时表和文件排序
2: 以A,B表连接为例 ,主要查询A表的列,
那么 group by ,order by 的列尽量相同,而且列应该显示声明为A的列
4: union优化
注意: union all 不过滤 效率提高,如非必须,请用union all
因为 union去重的代价非常高, 放在程序里去重.
limit 及翻页优化
limit offset,N, 当offset非常大时, 效率极低,
原因是mysql并不是跳过offset行,然后单取N行,
而是取offset+N行,返回放弃前offset行,返回N行.
效率较低,当offset越大时,效率越低
优化办法:
1: 从业务上去解决
办法: 不允许翻过100页
以百度为例,一般翻页到70页左右.
1:不用offset,用条件查询.
例:
mysql> select id,name from lx_com limit 5000000,10;
+---------+--------------------------------------------+
| id | name |
+---------+--------------------------------------------+
| 5554609 | 温泉县人民政府供暖中心 |
..................
| 5554618 | 温泉县邮政鸿盛公司 |
+---------+--------------------------------------------+
10 rows in set (5.33 sec)
mysql> select id,name from lx_com where id>5000000 limit 10;
+---------+--------------------------------------------------------+
| id | name |
+---------+--------------------------------------------------------+
| 5000001 | 南宁市嘉氏百货有限责任公司 |
.................
| 5000002 | 南宁市友达电线电缆有限公司 |
+---------+--------------------------------------------------------+
10 rows in set (0.00 sec)
问题: 2次的结果不一致
原因: 数据被物理删除过,有空洞.
解决: 数据不进行物理删除(可以逻辑删除).
最终在页面上显示数据时,逻辑删除的条目不显示即可.
(一般来说,大网站的数据都是不物理删除的,只做逻辑删除 ,比如 is_delete=1)
3: 非要物理删除,还要用offset精确查询,还不限制用户分页,怎么办?
分析: 优化思路是 不查,少查,查索引,少取.
我们现在必须要查,则只查索引,不查数据,得到id.
再用id去查具体条目. 这种技巧就是延迟索引.
mysql> select id,name from lx_com inner join (select id from lx_com limit 5000000,10) as tmp using(id);
+---------+-----------------------------------------------+
| id | name |
+---------+-----------------------------------------------+
| 5050425 | 陇县河北乡大谈湾小学 |
........
| 5050434 | 陇县堎底下镇水管站 |
+---------+-----------------------------------------------+
10 rows in set (1.35 sec)
173万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
