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;
---------------------
页:
[1]
