首页 / 更多教程 / 写SQL经验，多次加班教训总结
写SQL经验，多次加班教训总结
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了写SQL经验，多次加班教训总结，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含5546字，纯文字阅读大概需要8分钟。
内容图文
转：
写SQL经验，多次加班教训总结
下面的是SQL要注意的问题，有些是我踩过的坑，有些是同事踩的。大家看的时候可能没啥感觉，等到不小心搞出问题后会想起来的哈哈哈
文章目录
select 子语句
(1) GROUP_CONCAT
谨慎考虑使用
这方法是分组的形式将相同的行进行字符串聚合，字符之间默认以逗号间隔，有点耗时的，用了时候考虑一下要聚合的行数，如果行数多的话慎用。有同事之前SQL超时就因为这个
至于它的用处，举个例子：
要把全校的同学以班级为单位分班
select
class as 班级 ,
GROUP_CONCAT(studentName) as 学生名单
from student
group by class
| 班级 | 学生名单 |
|---|---|
| 1班 | 小明，小白，小黄 |
| 2班 | 小猪，二狗子 |
(2) 内查询
我不知道怎么称呼，我也没见过有人这么写过，长这样子：
select
studentName as 学生姓名,
(select grade from student_grade where class = '1班') as 成绩
from
student_info
where
class = '1班'
查询 1班的所有学生姓名和成绩 (其实SQL没写对)
假设1班有50个人
根据SQL的执行顺序：先from -> where ->select，我们每查一个同学的姓名，就去查一次全班的成绩(即select grade就执行1次)；
一句话，外查询的SQL返回多少行(50个studentName 共50行)，内查询就查几次，一共多查了50次成绩单，如果外查询返回了50万行呢
建议内查询少用
from子语句
(1) join尽量别连接数据量大的表
join需要用到临时表内存，会生成一张临时表，对于几百万数据量的大表，用 left join 就得谨慎点 了：
错误示范：
select .. from 600万行的表 left join 1000行的表 on ...
对于 left join，不管on后面的条件是什么，都会生成一个600万行的临时表，你的数据库的临时表内存八成会爆掉的哦
因为left join导致左边的大表几乎是被全表扫描了，那么索引大概率也不会生效了。
left join + where 是说在生成临时表后，用where的条件对临时表进行过滤删减
where 子语句要保证索引不失效
(1) 索引列运算(少点)
错误示范：
# (1) DATE_FORMAT会导致日期索引失效,假设create_time是索引
select record_info as 日志内容 from record_log where DATE_FORMAT(create_time,'%Y-%m-%d')='2021-02-23'
# (2) age是int 型, 且作为索引列, 参与了算术运算, 导致索引失效
'age' int(11) Not NULL DEFAULT 0 COMMENT '年龄'
select studentName as 学生姓名 from student_info where age+1 = 18
第一条SQL的DATE_FORMAT会导致日期索引失效，因为数据库会对create_time进行逐行计算后再比对，那基本是全表扫描
第二条SQL对索引列age进行运算，会导致索引失效
(2)避免自动数据类型转换
表里的字段是什么类型，传入的参数就给什么类型的
错误示范：
# age是字符型, 且为索引
'age' varchar(6) Not NULL DEFAULT '0' COMMENT '年龄'
alter table student_info add index index_age('age')
select studentName as 学生姓名 from student_info where age = 18
age作为索引列
- 表定义的是字符型的age：传入int型的18会 索引失效，万一数据表的age有 “18.0”、"18"这两种情况呢，这样的话表里的字符型age就没法和传入的int型 18直接比较了，最终导致age被逐行转换成int类型 后再与18比较
(3) 带上分库键
如果项目的数据库有分库分表的话，查询的时候尽量带上分库键，它能将SQL语句分发到指定的库表去执行，我们这的DRDS是这样子的
如果不带分库键(拆分键)的话，会导致SQL语句在全库都进行扫描执行，很慢的
(4)复合索引的顺序
创建复合索引
alter table student_info add index index_collection('age','studentName','sex')
只有以下三种where语句的顺序会走复合索引：
- age，studentName，sex
- age，studentName
- age
另外，第一索引列age涉及 “ >，<，between and ”，就停用索引了。
因为涉及范围查询 时，B+树的索引查找是 “ 直接从左到右的方式遍历叶子节点组成的链表 ”，而不是从根节点从上往下查找了
group by和order by带索引很快的
group by和order by后面带索引能降低开销
表结构定义
(1) 少点Default NULL
特别是索引列的定义为Default NULL的话会很影响稳定性
列的定义最好有个默认值，即NOT NULL DEFAULT VALUE
(2) 字段的定义给个注释COMMENT
特别是表明 状态类型 的字段，要给出所有状态的数值和对应的含义，例如：
`state` int(11) NOT NULL DEFAULT 0 COMMENT'0 进行中, 1 完成, 2 失效'
(3) 少用text类型
text是长文本的数据类型，mysql服务器将text数据传回客户端需要消耗大量的网络带宽，而服务器查询的时候将text数据从磁盘加载到内存又需要大量的IO带宽。
因此，在定义字段的数据类型时，text一般只适用于无法确定数据的长度的时候使用，其他情况慎用。
无法确定数据长度的情况一般是存储json串的时候，但是我们也不是用text，而是用json数据类型，因为如果要改json串的内容的话，对json数据类型修改比text方便得多
索引设置
(1) 索引的区分度要高
什么是区分度高？就是索引列的字段值最好都不一样，重复的越少，区分度越高。主键就是区分度最好的，每一行都是不重复的值，B+树很容易就将所有的行划分开来。
那种 区分度低的列最好别上索引，比如性别sex，无非就是男或女，会有很多数据行重复，想象一下：
如果要查出 成绩高于90分的男生是谁，一共50个人，有49个男生…
在成绩表grade 将性别sex设置为索引
alter table grade add index index_sex('sex')
select studentName from grade where sex='男' and grade>90
预想的执行情况是把49个男生拿出来，再一个个对比成绩，这跟没上索引差不多…
因为 索引sex=‘男’ 出现的频率过高，估计MySQL的执行策略会变成全表扫描也说不定呢
然而我司就有这种问题，咱也不敢说什么。。。
(2) 作为索引的字段的数据长度最好小一点
B+树的结点要存储索引列的每一个值，如果索引列的每个值都很大的话，MySQL会在把索引加载进入内存的过程中消耗大量的IO带宽
另外，索引字段的数据长度小的话，占用的内存就小，你知道的，innodb_buffer_pool_size设置的缓存索引的空间是有限的。 索引字段的数据长度越小，内存就能够容纳的更多的键值，提高1次就能查找到目标值的几率。1次找不到，就得从磁盘再次加载其他索引去查
能用int类型，就尽量不要用BigInt
主键更应该让数据长度小一点，每个二级索引(除聚簇索引外的索引)都会存放索引字段(除了主键外的其他字段)+对应的主键，主键太长，那每个二级索引占用的内存越大
其他的就不知道了，写不动了。。。
转：
写SQL经验，多次加班教训总结
内容总结
以上是互联网集市为您收集整理的写SQL经验，多次加班教训总结全部内容，希望文章能够帮你解决写SQL经验，多次加班教训总结所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。