1、慢sql优化问题
- 分页查询中的慢查询优化问题：
select * from emp where ename='svZLER' limit 10000, 10; // 这条语句消耗51s
select * from emp where id in (select id from emp where ename='svZLER') limit 10000, 10; // 这条语句消耗44s
select * from emp inner join (select id from emp where ename='svZLER' limit 10000, 10) b using(id); // 这条语句消耗37s
- 优化group by语句：
// 低效：
select job, avg(sal) from tdemo group by job having job='manager';
// 高效：
select job, avg(sal) from tdemo where job='manager' group by job;
- 范围查询优化：
- 如果使用单键trade_date_time索引，从索引找到trade_date_time在区间内的id，对这些id进行排序（因为很多相邻的主键可能一次I/O就能取到，回表查询筛选出ore_code等于指定值的记录
- 使用联合索引(trade_date_time, org_code)和(org_code, trade_date_time)，范围查询直接找到最小、最大值，然后进行链表遍历，因为MySQL5.6引入了索引下推，虽然org_code无法用到索引树，但是回表时可以过滤主键id
- 总结：对于这个案例，索引效果(org_code, trade_date_time) > (trade_date_time, org_code) > trade_date_time
select *
from statement
where org_code='1012'
and trade_date_time >= '2019-05-01 00:00:00'
and trade_date_time <= '2020-05-01 00:00:00'
- 优化order by查询：
select id, ..., creator, modifier, create_time, update_time
from statement
where (account_number='XX' and create_time>='2022-04-24 05:03:04'
and create_time<='2022-04-26 05:03:04' and dc_flag='C')
order by trade_date_time desc, id desc
limit 0, 1000
- SQL语句优化：
// 1、尽量避免使用子查询
select * from t1 where id (select id from t2 where name = 'chackca');
// 问题：5.5版本中，内部执行计划是先查外表在匹配内表，当外表数据很大时，查询速度会很慢；
// 尽管5.6版本中会优化成join连接的方式查询，但是对于update/delete等操作不会进行优化
// 2、使用in代替or
select * from t where id = 10 or id = 20 or id = 30; // 低效版本
select * from t where id in (10, 20, 30); // 优化版本
// 问题：采用in的方法时会将in的常量存入一个数组中，然后对数组排序，将数据二分查找的方式查询是否在其中，
// 而or的方式是对每一个值进行一次遍历确定是否在其中
// in时间复杂度是 O(log(n))，or时间复杂度是 O(n)
// 3、采用limit m,n分页查询时注意m的大小
select id, name from t limit 866613, 20
// 问题：对于limit m,n的分页查询，越往后面翻页（m很大）SQL耗时会越长，对于这种情况应该先取主键id，然后通过主键id跟原表进行join关联查询
// 因为MySQL并不是跳过offset行，而是取offset+n行，然后舍弃掉前offset行，返回n行
// 4、禁止不必要的order by排序
select goods_id, count(*) from t group by goods_id order by null;
// 问题：默认情况下，MySQL会对所有的group by的字段进行排序，想要避免的话，可以使用order by null禁止排序
// 5、总和查询禁止排重可以使用union all
// 问题：union和union all区别是前者需要将结果进行唯一性过滤，会涉及到排序，后者则不会
// 6、将多次插入换成批量insert插入
insert into t(id, name) values(1, 'aa'),(2, 'bb'), (3, 'cc');
// 7、只返回必要的列，具体字段代替select *
// 问题：select *会增加很多不必要的消耗
// 8、区分in和exists
select * from t1 where id in (select id from t2);
select * from t1 where exists(select * from t2 where t2.id=t1.id);
// 问题：上边的两条语句是等价的，但是exists是先查询外层表，in是先执行子查询
// in适合外表大但是内表小的情况；exists适合外表小但是内表大的情况
// 9、尽量使用数字型字段比较
// 问题：只包含数值信息的字段尽量不要设计为字符型，会降低查询和连接性能，处理字符时会逐个比较字符串每个字符，数值型只需比较一次
2、索引问题
-
索引失效情况
- 不满足最左匹配原则
- 在索引列进行计算
- 字符串类型必须带""才能使用索引
- 字段是int型，但是用字符串进行查询时可以使用索引
- 存储引擎不能使用范围查询右边的列
select * from username='123' and age>20 and phone='13987654321，不会用phone字段 - 尽量使用覆盖索引（索引列和查询列一致）
- 使用
!=、<>、not in、not exists、not like会导致全表扫描，因为无法判断去B+树的左还是右查询 - is null，is not null也不能使用索引
- like通配符开头
%abc，会导致索引失效进而全表扫描 - 少使用or，如果or的左端为索引列右端不是索引列，就会全表扫描，除非两边都是索引列才会走索引查询
- 使用前缀索引，可以提高查询性能并减少索引文件开销，缺点是不能使用group by，也不能使用覆盖索引
-
索引创建原则：
- 数据有唯一性限制的使用唯一索引
- 频繁作为where条件的字段
- 经常使用group by和order by的字段，既有group by又有order by建议使用联合索引
- 经常作为update或delete条件的字段
- 经常需要distinct的字段
- 多表连接时的字段创建索引，且连接表数量最好不要超过三个
- 尽量使用数据类型小的字段，索引也会占用空间
- 对字符串创建索引时建议使用字符串的前缀，节省空间
- 使用区分度高的字段做索引
- 在多个字段需要创建索引时，联合索引优于单值索引，使用最频繁字段作为最左侧
-
不需要使用索引的情况：
- where条件用不到的字段
- 数据量小的时候不需要索引
- 有大量重复数据的列不需要索引
- 避免在经常更新的字段创建索引
- 不建议主键使用无序的值作为索引，比如uuid
- 不要定义冗余的索引
-
索引下推：
// 有索引（name, age）
select * from user where name like "张%" and age=10;
// 这句话因为前边的like已经是范围查询了，所以后边的age是不会走索引的
// 索引下推的目的是为了减少回表的次数，即减少IO次数，对于聚簇索引，数据和索引在一起，不存在回表的说法
MySQL5.6版本之前的情况：（多次回表查询）
MySQL5.6版本之后：（只需要回表查询正确的数据，错误的age已经被筛掉）
