TA的每日心情 | 无聊 2024-9-19 09:07 |
---|
签到天数: 11 天 连续签到: 2 天 [LV.3]测试连长
|
一.表的优化和列类型选择
表的优化
1. 定长与变长分离
如:id int 4个字节,char(4)占4个字符长度 也是定长,time
即每一单元值占的字节数是固定的
核心且常用字段,宜建成定长,放在一张表
而varchar,text,blob这种变长字段,适合单放一张表,用主键与核心表关联
2. 常用字段和不常用字段分离
需要结合网站具体的业务来分析,分析字段的查询场景,查询频率低的字段,单拆出来
3. 在一对多需要关联统计的字段上添加冗余字段
列选择原则
1.字段类型优先级:整形 >date, time > enum, char > varchar > blob, text(无法用到内存临时表)
整形:定长,没有国家地区之分,没有字符集的差异
比如:tinyint 1, 2, 3, 4, 5 <—> char(1) a, b, c, d, e
从空间上看,都是占1个字节,但是orderby排序前者快
原因:后者需要考虑字符集与校对集(就是排序规则)
time定长,运算快,节省空间;考虑时区,写sql时不方便where createDate > ‘2018-04-19’
enum内部是用整形来储存,起到了约束值的目的
char定长,要考虑字符集和校对集
varchar变长,要考虑字符集的转换与排序时的校对集,速度慢
text,,blob无法使用内存临时表(排序等操作只能在磁盘上进行)
例:性别以utf8为例
char(1) 3个字长字节
enum(‘男’,’女’) 内部多了一个转成数字来存的过程
tinyint 定长1个字节
2.够用就行,不要慷慨,大的字段浪费内存,影响速度
例:年龄tinyint unsigned not null 可以存储255岁,足够,用int浪费了3个字节
varchar(10),,varchar(300)存储内容相同,但在联表查询时,varchar(300)要花更多内存
sql语句优化
sql语句的执行时间:a.查找—沿着索引查找,慢者可能全表扫描
b.取出—查到行后,把数据取出来(sending data)
如何查询快?
a) 查询的快—联合索引的顺序,区分度,长度
b) 取的快,索引覆盖
c) 传输的少,更少的行和列
count()优化
误区:
1. myisam的count()非常快
答:是比较快,但仅限于查询表的“所有行”比较快,因为myisam对行数进行了存储。一旦有条件的查询,速度就不再快了,尤其是where条件上没有索引
2. 假如 id<100的商家都是我们内部测试的,我们想查查真实的商家有多少?
select count(*)from lx_com where id >=100; (1000多万行用了6.x秒)
selectcount(*) from lx_com; 快
selectcount(*) from lx_com where id<100; 快
selectcount(*) from lx_com - select count(*) from lx_com where id<100; 快
select (selectcount(*) from lx_com) – (select count(*) from lx_com where id<100);
in型子查询引出的陷阱
题:在ecshop商城表中,查询6号栏目的商品。(注:6号是个大栏目)
最直观的:mysql>select goods_id, cat_id, goods_nameform goods where cat_id in (select cat_id from category where parent_id = 6);
误区:给我们的感觉是,先查到内层的6号栏目的子栏目,如 7, 8, 9, 11
然后外层cat_id in(7, 8, 9, 11)
即:先取出goods表中的所有记录,然后再取出in子句中的cat_id,最后将goods表中的所有记录挨个的与category表进行对比
原因: 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
详见:https://blog.csdn.net/freedomai/article/details/45131193
limit翻页及优化
limit offset, N 当offset非常大时,效率极低
原因是:mysql并不是跳过offset行,然后单取N行,
而是取offset+N行,返回放弃前offset行,返回N行
优化方法:
1. 从业务上解决
办法:不允许翻过100页; 以百度为例,一般翻页到70页左右
2.非要offset精确查询,还不限制分页怎么办?
分析:优化思路是 不查, 少查, 查索引, 少取列
我们现在必须要查,则只查索引,不查数据,得到id
再用id去查具体项目,这种技巧就是延迟关联
mysql>selectid, name from lx_com inner join (select id from lx_com limit 5000000, 10) as tmpon lx_com.id=tmp.id;
---------------------
|
|