Sql 优化，调优

一些简单的sql语句优化

1、所有的查询，尽量避免全表扫描，选择性在常用字段上创建索引。
2、避免在where 中写函数，或者进行表达式操作,这将导致引擎放弃使用索引 ,进行全表扫。
比如：

select  s_name from spring where substring(s_name,0,1) = '李'

优化：

select s_name from spring where name like '李%'

3、%号在左侧也会导致全表扫描
例：

select name as name, card_no as cardNo from mad_student where name like '%李'

4、尽量不要是用 * 号进行查询，可以将需要查询表的字段写上，即使是表中所有的字段都要查询，在数据量比较大的表中，字段全写上也是比 * 号查询速度要快。
比如：

select s_id,s_name from spring（1）select s_id,s_name from spring（2）
执行速度:（1）>（2）  

5、表连接尽量写在where之前。

6、数值类型的字段不要使用字符串类型，如果使用字符串类型的话，会降低查询效率，并增加存储开销。因为在引擎处理表连接和查询的时候，会将字段中的每个字符逐个比较一遍。如果只是数字的话，只要比较一次就够了。

7、使用临时表，来缓存中间数据。这样可以避免程序多次扫描表，降低查询成本，减少了阻塞，大大的提高了高并发的性能。

8、尽量少使用not in 关键字，这个关键字会使引擎放弃索引，进行全表扫描。
比如：

select id from student where name not in (
select id from spring where s_name = ("李"))

有的时候，使用exists代替in也是一个很不错的选择：
优化方式：

select id from student where exists(select 1 from spring where s_name = ("李"))

更新 2022-6-23 14:54:48
复盘后发现百度上说：
not in或者!=会导致索引失效并不是绝对的 对于数据较为均匀的场景是会失效的 但是如果业务数据严重不均的字段加了索引的话是不一定失效的 mysql自己会做判断 并不是绝对判定不使用索引 比如表A性别列有男10000条女20条，当sex!=’男‘是可以使用索引的 同样的如果你sex='男’反而不会使用索引 mysql自己会选择最优的检索方式。
以上供大家参考。
9、对于连续的数值，能用 between 就不要用 in ：
比如：

select s_name from student where s_id in (1,2,3) 

优化：

select s_name from student where s_id between 1 AND 3

10、尽量避免使用!=或<>操作符。在where语句中使用!=或<>，引擎将放弃使用索引而进行全表扫描，参考序号 8 的补充。

11、超过三个表不建议使用join，需要join的字段，数据类型绝对要一致；当多表关联查询的时候，保证关联的字段要有索引。
说明：总而言之，就是多表连接，数量大查询慢，跑不动！
原文链接：https://blog.csdn.net/chenssy/article/details/102927638

12、不要使用count(列明)或者count(常量)来代替count(星号),count(*)是标准的统计函数的语法，跟数据库无关，跟NULL和非NULL无关。
说明：
count(星号)会统计值为NULL的行，而count(列名)不会统计此列为NULL值的行。
注意count()函数的作用，他是用来干嘛的，count()是标准的统计语言。

13、另外一个union关键字，我个人非常建议大家学习一下。
UNION 操作符用于合并两个或多个 SELECT 语句的结果集。

select子句输出的列数必须是相等的
select子句输出的数据类型是兼容的

union和union ALL的作用基本是等效的。区别在于union只显示唯一的数据，去重。
而 union ALL则是显示所有数据。

---

这里我写一写数据库表的命名规则吧，都是我最近在看的阿里巴巴Java开发手册中的一些规范。

1、表名、字段名必须统一使用小写字母或数字，禁止出现数字开头以及两个下划线中间就一个数字_8_。
因为MySQL在windows系统下默认是不区分大小写的，而在Linux系统下默认是区分大小写的。
正例：

stundent_name,baidu_yunpan

反例

StudentName,BaiduYunPan

2、在表达是否概念的字段，必须使用is_xxx的方式，而在实体类pojo中，所有boolean类型的变量，前面都不要加is前缀，因为boolean变量生成的set与get方法使用is开头。
举个栗子：

当RPC框架反向解析的时候，以为对应的属性就是Success，导致属性找不到，会引起RPC框架反序列化异常。

3、表中索引的命名规则：

1. 主键索引名：pk_xxx。
2. 唯一索引名：uk_xxx。
3. 普通索引名：idx_xx。

说明：pk_即primary key；uk_即unique key;idx_即index的简称

3、存储小数类型时，不要使用double与float。这哥俩儿存在精度损失问题，说白了就是存在出现误差的情况，在比较值的时候可能拿不到正确的结果。建议使用decimal，如果超出decimal的范围，将整数与小数分开存储。
如果使用double与float类型，要在数据库中设置小数的位数，要求小数位相同。

4、字符类型，如果字段数据几乎相等，建议使用char定义长字符串类型。比如手机号，位数都在11位；这里就可以使用char类型。

5、varchar是可变长度字符串，不预先分配存储空间，长度不要超过5000，如果超过这个值，建议独立一张表，定义该字段为text，用主键来对应。这样在查询其他字段时，全表扫描等，不会影响其他字段的索引效率。

6、这个我觉得很关键奥，小伙伴留意一下，一个表必备的三个段：id，create_time，update_time。
说明：

其中id必须为主键，单表自增、步长为1、数据类型为unsigned bigint。
create_time 与 update_time均为date_time类型。一个是数据创建时间，一个是修改时间。大家都懂哈哈。

**为什么表的主键的数据类型一定要使用 bigint unsigned呢？**unsigned表示无符号。
说明：
简单了解一下就好哈~ ~ ~
原文链接：https://blog.csdn.net/nrsc272420199/article/details/102877399
7、当单表行数超过500w行或者单表容量超过2GB时，才建议分库分表。如果预计三年后的数据量仍然无法达到这个级别，请不要在创建表时就分库分表。

8、每个字段设置合适的存储长度，不但可以节约数据库表空间和索引存储，更重要的是能够提升查询速度。