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表中的所有记录，然后再取出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;
---------------------