SQL规范与优化

是良辰

已于 2023-02-02 17:13:36 修改

阅读量747

点赞数

分类专栏：数据库调优文章标签： sql 数据库 java

于 2022-09-23 16:45:08 首次发布

本文链接：https://blog.csdn.net/luostudent/article/details/127011118

版权

数据库同时被 2 个专栏收录

5 篇文章 0 订阅

订阅专栏

调优

3 篇文章 0 订阅

订阅专栏

一 SQL设计规范

1 数据库设计思想

基本思想：OLTP数据库只做数据“存与取”，尽量小而简单。

小：字段类型选择小的，事务要避免大的
简单：能在应用实现的，不要放到数据库

2 schema设计

2.1 字段类型选择

2.2 必备字段

每个表含以下字段：

`id` bigint(18) NOT NULL AUTO_INCREMENT COMMENT '主键id',
 ....
`creator_id` varchar(32) NOT NULL DEFAULT '' 表必须包COMMENT '创建人id',
`modifier_id` varchar(32) NOT NULL DEFAULT '' COMMENT '修改人id',
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
`modify_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
`is_delete` tinyint(2) NOT NULL DEFAULT '0' COMMENT '删除标识：0未删除1已删除',

说明：

主键id：独立的自增的bigint，与业务无关
创建人id：记录创建人
修改人id：记录修改人
创建时间：程序维护、不修改，建索引
更新时间：数据维护删除标识：软删除

2.3 主键使用自增int/bigint

主键id：独立的自增的bigint，与业务无关，禁止修改

避免插入数据造成数据分页
提高二级索引的性能，二级索引包含主键
大表一定要bigint，防止int不够用（大约21亿左右），溢出后会导致报错。

为什么不用UUID？

uuid是无序的，插入数据时，页的位置会发生变化，页分裂，速度慢
uuid占的空间大，并且innodb中，二级索引包含主键的值，那么每个索引的空间也都会增大，占的空间大，需要读数据时io次数就多

2.4 禁止使用default NULL

所有字段均定义为NOT NULL：

字符为default ‘’
数值为default 0
时间为default 0000-00-00 00:00:00

为什么不用Null

Conceptually, NULL means “a missing unknown value” and it is treated somewhat differently from other values. 严格来说，NULL是一种状态，而不是一个值。

存储空间优化
查询效率优化
联表查询问题
count(NULL column)
子查询 NOT IN

2.5 禁止使用存储过程等

禁用procedure、function、trigger、views、event、外键约束。推荐都在程序端实现，应用横向扩展容易。

避免一些bug
消耗数据库更多的计算资源
降低数据库实例可扩展性
增加数据库复杂度
增加数据库自动化运维难度

例如：外键约束容易导致数据批量同步出错，存储过程的存在可能会导致拆库迁移有问题。

3 索引设计规范

索引名称必须使用小写
非唯一索引建议使用“idx_表缩写名称_字段缩写名称”进行命名。
唯一索引建议使用“uk_表缩写名称_字段缩写名称”进行命名。
索引字段的顺序需要考虑字段值去重之后的个数（基数），个数多的放在前面
使用EXPLAIN判断SQL语句是否合理使用索引，尽量避免extra列出现：Using File Sort，Using Temporary。
UPDATE、DELETE语句需要根据WHERE条件添加索引。
单个表上的索引个数不能超过7个。
合理创建联合索引（避免冗余），(a,b,c) 相当于 (a) 、(a,b) 、(a,b,c)。
合理利用覆盖索引。比如SELECT email,uid FROM user_email WHERE uid=xx，如果uid不是主键，适当时候可以将索引添加为index(uid,email)，以获得性能提升。

SQL规范：访问量很大的SQL不要在数据库里做排序

禁止在DB里排序，请在web server上排序（对并发量特别大的SQL），web server有上百台，而db仅仅只有个位数的数量，排序都在db，会把db压垮。
禁止在where子句中对字段施加函数，如to_date(add_time)>xxxxx, 应改为: add_time >= unix_timestamp(date_add(str_to_date('20220227','%Y%m%d'),interval - 29 day))
禁止使用%做前缀模糊查询，例如LIKE “%weibo”，无法使用索引。
禁止使用select *，兼容性差，使用更多的网络流量，不能使用覆盖索引，必须回表
禁止3个以上的表join，join的字段数据类型必须绝对一致
in列表元素不能超过1000个

二慢查询优化

什么是慢查询，慢查询的定义是啥？

执行时间超过1s的语句（参数long_query_time），全表扫描超过1w行的语句（参数log_queries_not_using_indexes、min_examined_row_limit）。

1 没加索引，索引不生效

加索引的两种语法：
CREATE INDEX idx_name ON table(column1,column2);
alter table table_name add index idx_name(column1,column2);

区别：
create 一次只能创建一个索引；
alter 一次可以添加多个索引。

索引不生效案例1

有表：

CREATE TABLE tb_test (
	id BIGINT auto_increment NOT NULL COMMENT '主键id',
	test_name varchar(20) DEFAULT '' NOT NULL COMMENT '名称',
	`creator_id` varchar(32) NOT NULL DEFAULT '' COMMENT '创建人id',
    `modifier_id` varchar(32) NOT NULL DEFAULT '' COMMENT '修改人id',
    `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    `modify_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    `is_delete` tinyint(2) NOT NULL DEFAULT '0' COMMENT '删除标识：0未删除1已删除',
     PRIMARY KEY (`id`)
)
ENGINE=InnoDB
DEFAULT CHARSET=utf8mb4
COLLATE=utf8mb4_general_ci;

查询条件带or导致索引失效

SELECT
   *
FROM
   tb_test 
WHERE
   1 = 1
   AND test_name = 'xxx'
   AND(
     creator_id = '123456'
     Or modifier_id = '123456'
   )
   AND create_time >= '2022-04-29 00:00:00'
   AND '2022-05-05 23:59:59' >= create_time

explain 查看执行计划，可知 type = ALL

解决方案1

在or的两个字段上分别建索引，mysql可以使用index merge的功能，可以检索多个范围扫描并将结果合并。

index merge 如果在WHERE语句中，存在多层嵌套的AND/OR，MySQL可能不会选择最优的方案。

解决方案2

将or改写成union，注意去重；

SELECT
   *
FROM
   tb_test 
WHERE
   1 = 1
   AND test_name = 'xxx'
   AND creator_id = '123456'
   AND create_time >= '2022-04-29 00:00:00'
   AND '2022-05-05 23:59:59' >= create_time 
union
SELECT
   *
FROM
   tb_test 
WHERE
   1 = 1
   AND test_name = 'xxx'
   AND modifier_id = '123456'
   AND create_time >= '2022-04-29 00:00:00'
   AND '2022-05-05 23:59:59' >= create_time

该方法要注意去重，不然结果会有偏差。

解决方案3

使用覆盖索引

覆盖索引会使索引比较臃肿，增加了dml的开销，非特别情况，不建议使用该方法。

当or条件的两个字段索引选择率不高的情况下，可以考虑覆盖索引。

索引不生效案例2

## 在查询字段上加函数会导致索引失效
select * from tb_test where left(test_name,length('张三')) = '张三'

## 改写为左匹配查询可以使用到索引
select * from tb_test where test_nam like '张三%'

索引不生效案例3

in元素过多可能会导致索引失效，超过500个可能就会失效。

解决方案1

in元素超过500个，建议分组，结果用union all合并

实际过程中有测试原sql执行需要2.35s，通过union all 方法执行时间为0.67s。

解决方案2

调整eq_range_index_dive_limit参数的值，默认是200

设置会话级别的参数后，执行时间为0.66s。

扩展：

index dive: 到index中即利用索引完成元组数的估算，速度慢,但能得到精确的值
index statistics: 使用索引的统计数值,进行估算，速度快,但得到的值未必精确,例如索引统计信息计算出每个等值影响100条数据，那么IN条件中包含5个等值则影响5*100条记录

强制索引

已经创建索引，收集统计信息，而且索引选择率更好的情况下就是走错索引。可以使用OPTIMIZER_TRACE方法来查看mysql走每个索引，使用各种连接顺序的一个分析情况。

官网的一个例子： http://dev.mysql.com/doc/internals/en/tracing-example.html

走错索引应该怎么解决：

1、使用force index 强行选择某个索引；
2、修改你的SQl，引导它使用我们期望的索引；
3、优化你的业务逻辑；
4、优化你的索引，新建一个更合适的索引，或者删除误用的索引（慎用）。

强制索引方法

mysql 5.7支持query_rewrite的功能，可以直接在数据库层支持sql改写，而不用再应用侧修改sql。改写后的执行计划跟之前的一样，唯一的区别就是执行完sql之后会有个warning告警。

limit深度分页

select productid,categoryid,deptid from product where checktime > '2020-01-01' limit 200000,10;

sql流程：

通过普通二级索引树idx_checktime，过滤checktime条件，找到满足条件的主键id。
通过主键id，回到id主键索引树，找到满足记录的行，然后取出需要展示的列（回表过程）。
扫描满足条件的200010行，然后扔掉前200000行，返回。

解决方法：减少回表的次数，降低io

## 原sql：
select productid,categoryid,deptid from product where checktime > '2020-01-01' limit 200000,10;
## 改写后的sql：
select  a.productid,a.categoryid,a.deptid from product a INNER JOIN( select c.productid from product c where c.checktime > '2020-01-01' limit 200000,10)b on a.productid=b.productid;

优化思路：先通过idx_checktime二级索引树查询到满足条件的主键ID，再与原表通过主键ID内连接，这样后面直接走了主键索引了，同时也减少了回表。

原sql执行0.83s，优化后的sql自行0.07s，效果相差10倍。

多表join

数据库有个规范约定就是：尽量不要有超过3个以上的表连接。

MySQL中，join的执行算法，分别是：Simple Nested-Loop Join、Index Nested-Loop Join、Block Nested-Loop Join。优先级：Index Nested-LoopJoin > Block Nested-Loop Join > Simple Nested-Loop Join

Index Nested-Loop Join：索引进行连接的算法，索引是基于内层表的，通过外层表匹配条件直接与内层表索引进行匹配，避免和内层表的每条记录进行比较，从而利用索引的查询减少了对内层表的匹配次数。
Block Nested-Loop Join：把参与查询的列缓存到Join Buffer 里，然后拿join buffer里的数据批量与内层表的数据进行匹配，从而减少了内层循环的次数（遍历一次内层表就可以批量匹配一次Join Buffer里面的外层表数据）

join过多的问题：

1、过多的表连接，会大大增加SQL复杂度。
2、如果使用小表来做驱动表，匹配的数据量比较小或者join_buffer设置的比较大，速度也不会太慢。
但是，如果join的数据量比较大时，mysql会采用在硬盘上创建临时表的方式进行多张表的关联匹配，这种显然效率就极低。

多表join优化思路

匹配次数 = 外层表行数 * 内层表行数

1、用小表驱动大表，减少外层循环的连接，从而减少内层循环次数；
2、join字段都加上索引，争取使用INLJ，减少内层表的循环次数；
3、增大join buffer size的大小：缓存数据越多，内层表循环次数越少；
4、减少不必要字段查询：
（1）用INLJ时，如果可以用覆盖索引，减少回表次数。
（2）用BNLJ时，查询字段越少，join buffer 缓存的数据就越多，内层表循环次数越少。

案例1

难点：多表join，模糊查询
解决：先只取该字段+主键的值，查询出来的结果再与自身表做关联取出其他字段的值。
效果对比：优化前2.87s，优化后0.82s，性能提升了3倍。

案例2

更改业务逻辑，去掉多余的连接

案例3

返回数据量太大，更改业务逻辑，比如改为分页

子查询案例

SELECT  coupon_name,  grant_record_id  FROM  coupon_grant_record  WHERE  grant_record_id IN ( SELECT max( grant_record_id )  FROM  coupon_grant_record  WHERE  use_state IN (0,1)  AND valid_end_date > now()  AND TIMESTAMPDIFF( HOUR, now(), valid_end_date ) <= 24 GROUP BY grant_record_id );   ---字段grant_record_id为主键

由于子查询的结果是未知的，不能作为外层索引的判断条件，由此导致外层直接全表扫描。

子查询优化

将子查询中的结果作为结果集，通过主键与表连接，这样就能用到主键索引。
原来执行需要93s，改写成join后执行需要3s

单表数据量太大

1、单表数据量太大为什么会变慢？

MySQL 为了提高性能，会将表的索引数据装载到内存中。InnoDB buffer size 足够的情况下，其能完成全加载进内存，查询不会有问题。但是，当单表数据量好几千万或者上亿时，导致内存无法存储其索引，使得之后的 SQL 查询会产生磁盘 IO，从而导致性能下降。性能之所以会变差，是因为维护索引的B+树结构层级变得更高了，查询一条数据时，需要经历的磁盘IO变多，因此查询性能变慢。

解决方案

1、事务优化
sql语句尽量简化，最好是基于主键索引查询，尽可能的避免多表复杂连接，olap事务在从库查询
2、表结构优化
将大表改造成分区表，查询条件都要带分区字段查询
3、加缓存
读多写少的场景，采用redis缓存，将读请求转到缓存层面
4、业务层优化
业务拆分，将结合业务拆分为独立业务，把历史数据分离出来
5、垂直拆分
6、水平拆分
7、架构调整
使用兼容mysql协议的newdb,如：tidb、oceanbase