mysql优化误区_MySQL高性能优化思路-经典陷阱误区实例

一．表的优化和列类型选择

表的优化

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表全扫描, 并逐行与category表对照,看parent_id=6是否成立

即：先取出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..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;