MySQL写出高效SQL

时间:2023-03-08 16:06:18
MySQL写出高效SQL

mysql设计标准
事务处理标准
索引使用标准
约束设计
sql语句标准

怎么写出高效SQL
清晰无误的了知业务需求
满足业务需求,不做无用功
知道表数据量和索引基本情况
知道完成SQL需要扫描的数据量级
SQL执行计划OK?SQL性能达到要求?
调整索引和SQL,优化SQL

IN子查询容易导致问题,禁止使用,需改成join
选择正确的驱动表:关键的第一步
错误使用Left Join
left join的表在where中出现,如
Table1 left join Table2 on Table1.C1=Table2.c1
where Table2.column1=XXX
实际上已经变成了inner join
Leftjoin会限制join顺序,而且where条件只能在join后过滤,影响性能

group by常见处理
利用index实现group by,避免使用临时表
group by是否必要?
提升group by性能的一些方法
--group by col1,col2 order by null/order by col1,col2
order by常见处理
利用索引来实现排序功能?
排序字段是否在驱动表上?
order by的几种优化方法
--using index/using filesort(两次扫描/一次扫描(max_length_for_sort_data/query))
set optimizer_trace='enabled=on';
set @@global.show_compatibility_56=ON;#5.7`performance_schema`.session_status
select varable_value into @a from `performance_schema`.session_status where variable_name='Innodb_rows_read';
#select city,name,age from t where city='chengdu' order by limit 1000;
select * from zabbix.events order by eventid limit 10
select * from information_schema.OPTIMIZER_TRACE;
select varable_value into @b from `performance_schema`.session_status where variable_name='Innodb_rows_read';
select @b-@a; #扫描的行数
SET optimizer_trace="enabled=off";
MySQL的排序优化
应尽可能避免排序或者尽可能避免对大量数据进行排序。如果需要排序的数据量小于排序缓冲区,MySQL使用内存进行“快速排序”操作
如果内存不够排序,那么MySQL会先将数据分块,对每个独立的块使用“快速排序”进行排序,并将各个块的排序结果存放在磁盘上,然后将各个排序的块进行合并,最后返回排序结果
单次传输排序(新版),一次读取出所有需要的或SQL查询指定的列,然后根据排序列,排序,直接返回排序后的结果。顺序I/O,缺点:如果列多,额外占用空间
在关联查询的时候如果需要排序,MySQL会分两种情况来处理这样的文件排序。如果ORDER BY子句的所有列都来自关联的第一个表

那么MySQL在关联处理第一个表时就进行文件排序。如果是这样那么在MySQL的EXPLAIN结果中可以看到Extra字段会有Using filesort。
除此之外的所有情况,MySQL都会将关联的结果存放在一个临时表中,然后在所有的关联都结束后,再进行文件排序。这种情况下Extra字段可以看到Using temporary;Using filesort。
如果查询中有LIMIT的话,LIMIT也会在排序之后应用,所以即使需要返回较少的数据,临时表和需要排序的数据量仍然会非常大

分页SQL优化
分页SQL可能包含order by,group by和多表join等操作,可以利用前面的优化手段
后台分页取数SQL可以用cursor替换,分批fetch;使用分页SQL多次取,性能差,数据一致性没保证

并发设置
并发不是越大越好,设置满足要求的最小并发数
高并发时MySQL处理能力会因为锁竞争、上下文切换等问题而下降
测出系统处理能力和并发数的曲线关系,知道系统性能拐点位置

子查询分类
Scalar Subquery
在select子句中标量子查询只能返回一个值
标量子查询可以分成depend和independ,independ子查询只需查询一次,depeng子查询需外
部查询返回的每条记录执行一次
在where子句中的标量子查询
= 和 in的区别
使用row(...)函数进行多列比较的问题

Nested Subquery
在from子句中的子查询
关联字段要有索引
主要形式有:in,not in,exists,not exitsts
标准版,转化成DEPENDENT SUBQUERY,即外部查询先执行,再执行子查询
多个Nested Subquery子查询时,一般按在查询出现先后次序,后出现的先执行
Mariadb版本可以转化成inline view,再join

Inline View
出现在from子句中的子查询
考虑join次序,查询条件能否推进Inline View (merge)
inline view作为驱动表?
inline view产生临时表?
inline view产生的临时表支持索引?

STRAIGHT_JOIN」强制连接顺序

-sql规范:
--禁用 select *,禁止dml,select语句缺少where
--禁止任何对where字段的计算,函数等操作DATE_FORMAT(gmt_create,'%Y%m%d%H%i%s')='2018061522300'
--jon的关联字段 数据类型一致,要有索引
--group by,order by 最好都能选择驱动表的列或者只选择一个表上的列
--不要使用 count(列名)或 count(常量)来替代 count(*)
--使用 ISNULL()来判断是否为 NULL 值

----
1、所有的InnoDB表都设计一个无业务用途的自增列做主键,对于绝大多数场景都是如此,真正纯只读用InnoDB表的并不多,真如此的话还不如用TokuDB来得划算;
2、字段长度满足需求前提下,尽可能选择长度小的。此外,字段属性尽量都加上NOT NULL约束,可一定程度提高性能;
3、尽可能不使用TEXT/BLOB类型,确实需要的话,建议拆分到子表中,不要和主表放在一起,避免SELECT * 的时候读性能太差。
4、读取数据时,只选取所需要的列,不要每次都SELECT *,避免产生严重的随机读问题,尤其是读到一些TEXT/BLOB列;
5、对一个VARCHAR(N)列创建索引时,通常取其50%(甚至更小)左右长度创建前缀索引就足以满足80%以上的查询需求了,没必要创建整列的全长度索引;
6、通常情况下,子查询的性能比较差,建议改造成JOIN写法;
7、多表联接查询时,关联字段类型尽量一致,并且都要有索引;
8、多表连接查询时,把结果集小的表(注意,这里是指过滤后的结果集,不一定是全表数据量小的)作为驱动表;
9、多表联接并且有排序时,排序字段必须是驱动表里的,否则排序列无法用到索引;
10、多用复合索引,少用多个独立索引,尤其是一些基数(Cardinality)太小(比如说,该列的唯一值总数少于255)的列就不要创建独立索引了;
11、类似分页功能的SQL,建议先用主键关联,然后返回结果集,效率会高很多;
关于MySQL的管理维护的其他建议有:
1、通常地,单表物理大小不超过10GB,单表行数不超过1亿条,行平均长度不超过8KB,如果机器性能足够,这些数据量MySQL是完全能处理的过来的,不用担心性能问题,这么建议主要是考虑ONLINE DDL的代价较高;
2、不用太担心mysqld进程占用太多内存,只要不发生OOM kill和用到大量的SWAP都还好;
3、在以往,单机上跑多实例的目的是能最大化利用计算资源,如果单实例已经能耗尽大部分计算资源的话,就没必要再跑多实例了;
4、定期使用pt-duplicate-key-checker检查并删除重复的索引。定期使用pt-index-usage工具检查并删除使用频率很低的索引;
5、定期采集slow query log,用pt-query-digest工具进行分析,可结合Anemometer系统进行slow query管理以便分析slow query并进行后续优化工作;
6、可使用pt-kill杀掉超长时间的SQL请求,Percona版本中有个选项 innodb_kill_idle_transaction 也可实现该功能;
7、使用pt-online-schema-change来完成大表的ONLINE DDL需求;
8、定期使用pt-table-checksum、pt-table-sync来检查并修复mysql主从复制的数据差异;
----