MySQL进阶篇——sql优化

优化很多是基于索引的，结合上一篇中的性能分析。

1、insert优化

--批量插入 insert into tb_test values(1,'tom'),(2,'cat'),(3,'jerry'); --手动事务提交 start transaction; insert into...; insert into...; commit; --主键顺序插入（性能高于乱序插入） --大批量数据插入load（insert性能较低） mysql --local-infile -u root -p --连接mysql时加载本地文件的参数 set global local_infile=1; --设置全局参数，开启本地导入 --载入文件地址，表，字段间分隔，行间分隔 load data local infile '/root/sql1.log' into table 'tb_user' fields terminated by ',' lines terminated by '\n';

2、主键优化

InnoDB存储引擎，表数据根据主键顺序存放，称为索引组织表。

主键乱序插入id=50（页分裂）

页合并：页中删除记录达到merge_threshold（默认页的50%），InnoDB会开始寻找前后页，是否可以合并以优化空间。

主键设计原则

尽量降低主键长度（节省二级索引空间）；

插入数据时，尽量选择顺序插入（防止页分裂），使用auto_increment自增主键；

尽量不要使用UUID做主键或者其他自然主键，如身份证号（这些都是无序的且长度长）；

尽量避免对主键的修改（代价大，需要改索引结构）；

3、order by优化

Using filesort：通过表的索引或全表扫描，读取满足条件的数据行，然后在排序缓冲区sort buffer中完成排序操作，所有不是通过索引直接返回排序结果的排序都叫FileSort排序。

Using index：通过有序索引顺序扫描直接返回有序数据，这种情况即为using index，不需要额外排序，操作效率高。

写在extra中：Using filesort（尽量避免发生） / Using index

create index idx_user_age_phone on tb_user(age,phone); explain select id,age,phone from tb_user order by age,phone; --结果中extra会显示Using index（性能高） order by age desc,phone desc; --desc倒序排序时，extra会出现backward index scan反向扫描索引; Using index order by age asc, phone desc; --extra会出现Using index;Using filesort(性能低) --解决方法 create index idx_user_age_pho_ad on tb_user(age asc,phone desc); --extra只会出现Using index

1、根据排序字段建立合适的索引，多字段排序时遵循最左前缀法则；

2、尽量使用覆盖索引；

3、多字段排序，一升序一降序，需要注意联合索引在创建时的规则；

4、如果不可避免出现filesort，大量数据排序时，可以适当增大排序缓冲区大小sort_buffer_size(默认256K)；

4、group by优化

explain select profession,count(*) from tb_user group by profession; --type=all, extra=using temporary临时表性能很低 --创建联合索引 create index idx_user_pro_age_sta on tb_user(profession,age,status); --extra变成using index性能优化 --第二种情况 explain select age,count(*) from tb_user group by age; --extra变成using index;using temporary explain select age,count(*) from tb_user where profession='math' group by age; --extra只有using index

group by分组操作时，建立索引提高效率，尽量避免extra出现using temporary

group by分组操作时，索引的使用也满足最左前缀法则的

5、limit优化

limit x,n --表示从x+1行开始返回n行 select * from tb_sku limit 2000000,10; --大数据量情况下，x很大时性能很低，全部回表 --优化方法：子查询覆盖索引，仅回表10条数据 select s.* from (select id from tb_sku order by id limit 2000000,10) a left join tb_sku s on a.id=s.id;

6、count优化

select count(*) from tb_sku;

大数据量情况下比较耗时，因为InnoDB需要读取每一行；

优化思路：自己计数，插入数据时参数自加1；

性能比较：count(*)=count(1)>count(主键)>count(字段)

count(主键)：遍历整张表取值，直接累加；

count(字段) 没有not null约束：遍历整张表取值，服务层判断是否null，累加；

count(字段) 有not null约束：遍历整张表取值，直接累加；

count(*)：专门优化，不取值直接累加；

7、update优化

更新数据时要根据索引（主键id等），此时事务是行锁

update course set name=’java’ where id=1;

若更新数据时根据name无索引，会产生表锁

update course set name=’java’ where name=’php’;