Java SQL优化

叼着烟吻着你的香

于 2024-08-12 17:39:22 发布

阅读量971

点赞数 19

文章标签： sql java 数据库

本文链接：https://blog.csdn.net/qq_36661375/article/details/141126402

版权

SQL优化的核心在于降低查询的执行时间和资源消耗，系统的稳定。
SQL优化：
1、SQL语句的优化
2、SQL索引的优化（关键）
3、SQL数据表优化
4、SQL数据库优化

SQL语句的优化

1、不要用*，可能用到覆盖索引，减少回表，提高查询效率。
SELECT * FROM user;
SELECT id,username FROM user;

2、不要在where子句中使用or来连接条件，会使索引失效，从而全表扫描；要么使用UNION ALL或者分开写SQL。
SELECT * FROM user WHERE id=1 OR salary=5000;
SELECT * FROM user WHERE id=1
UNION ALL
SELECT*FROMuserWHEREsalary=5000;

3、尽量使用数值替代字符串类型
	主键（id）：primary key优先使用数值类型int，tinyint
	性别（sex）：0代表女，1代表男；数据库没有布尔类型，mysql推荐使用tinyint
	因为引擎在处理查询和连接时会逐个比较字符串中每一个字符；
	而对于数字型而言只需要比较一次就够了；
	字符会降低查询和连接的性能，并会增加存储开销。

4、使用varchar代替char
addr char(100) DEFAULT NULL COMMENT'地址';
addr varchar(100) DEFAULT NULL COMMENT '地址';
varchar字段按数据内容实际长度存储，存储空间小，可以节省存储空间；
char按声明大小存储，不足补空格；
对于查询来说，在一个相对较小的字段内搜索，效率更高；

5、where中使用默认值代替null
SELECT * FROM user WHERE age IS NOT NULL;
SELECT * FROM user WHERE age>0;
SELECT * FROM user WHERE salary!=5000;
SELECT * FROM user WHERE salary<>5000;
并不是说使用了is null或者 is not null就会不走索引了，这个跟mysql版本以及查询成本都有关；
如果mysql优化器发现，走索引比不走索引成本还要高，就会放弃索引，这些条件 !=，<>，is null，is not null经常被认为让索引失效；
其实是因为一般情况下，查询的成本高，优化器自动放弃索引的；
如果把null值，换成默认值，很多时候让走索引成为可能，同时，表达意思也相对清晰一点；

6、inner join 、left join、right join，优先使用inner join
inner join 内连接，只保留两张表中完全匹配的结果集；返回的行数比较少，所以性能相对会好一点；
left join会返回左表所有的行，即使在右表中没有匹配的记录；
所以左边表数据结果尽量小，条件尽量放到左边处理，意味着返回的行数可能比较少；
这是mysql优化原则，就是小表驱动大表，小的数据集驱动大的数据集，从而让性能更优；
right join会返回右表所有的行，即使在左表中没有匹配的记录；优化同了left join

7、提高group by语句的效率
先分组，再过滤（不推荐）
select job,avg（salary）from employee group by job having job='develop' or job='test';
先过滤，后分组
select job,avg（salary）from employee where job='develop' or job='test' group by job;
可以在执行到该语句前，把不需要的记录过滤掉

8、清空表时优先使用truncate
truncate table在功能上与不带 where子句的 delete语句相同：二者均删除表中的全部行。但 truncate table比 delete速度快，且使用的系统和事务日志资源少。
delete语句每次删除一行，并在事务日志中为所删除的每行记录一项。truncate table通过释放存储表数据所用的数据页来删除数据，并且只在事务日志中记录页的释放。
truncate table删除表中的所有行，但表结构及其列、约束、索引等保持不变。新行标识所用的计数值重置为该列的种子。如果想保留标识计数值，请改用 DELETE。如果要删除表定义及其数据，请使用 drop table语句。
对于由 foreign key约束引用的表，不能使用 truncate table，而应使用不带 where子句的 DELETE 语句。由于 truncate table不记录在日志中，所以它不能激活触发器。
truncate table不能用于参与了索引视图的表。

9、批量插入性能提升
INSERT INTO user(id,username)VALUES(1,'张三');
INSERT INTO user(id,username)VALUES(2,'李四');
INSERT INTO user(id,username)VALUES(1,'张三'),(2,'李四');
默认新增SQL有事务控制，导致每条都需要事务开启和事务提交，而批量处理是一次事务开启和提交，效率提升明显，达到一定量级，效果显著，平时看不出来。

10、索引不宜太多，一般5个以内
索引并不是越多越好，虽其提高了查询的效率，但却会降低插入和更新的效率；
索引可以理解为一个就是一张表，其可以存储数据，其数据就要占空间；
索引表的数据是排序的，排序也是要花时间的；
insert或update时有可能会重建索引，如果数据量巨大，重建将进行记录的重新排序，所以建索引需要慎重考虑，视具体情况来定；
一个表的索引数最好不要超过5个，若太多需要考虑一些索引是否有存在的必要；

11、不要在索引列上使用内置函数
SELECT * FROM user WHERE DATE_ADD(birthday,INTERVAL7DAY)>=NOW;
SELECT * FROM user WHERE birthday>=DATE_ADD(NOW,INTERVAL7DAY);
不要在子句中的“=”左边进行函数、算术运算或其他表达式运算，否则索引失效。

12、复合索引最左特性
创建复合索引
ALTERTABLEemployeeADDINDEXidx_name_salary(name,salary)
满足复合索引的最左特性，哪怕只是部分，复合索引生效
SELECT*FROMemployeeWHERENAME='编程'
没有出现左边的字段，则不满足最左特性，索引失效
SELECT*FROMemployeeWHEREsalary=5000
复合索引全使用，按左侧顺序出现 name,salary，索引生效
SELECT*FROMemployeeWHERENAME='编程'ANDsalary=5000
虽然违背了最左特性，但MySQL执行SQL时会进行优化，底层进行颠倒优化
SELECT*FROMemployeeWHEREsalary=5000ANDNAME='编程'
复合索引也称为联合索引，当我们创建一个联合索引的时候，如(k1,k2,k3)，相当于创建了（k1）、(k1,k2)和(k1,k2,k3)三个索引，这就是最左匹配原则。联合索引不满足最左原则，索引一般会失效。

13、优化like语句
模糊查询，程序员最喜欢的就是使用like，但是like很可能让你的索引失效。
select * from citys where name like'%大连'(不使用索引)
select * from citys where name like'%大连%'(不使用索引)
select * from citys where name like '大连%'(使用索引)。
首先尽量避免模糊查询，如果必须使用，不采用全模糊查询，也应尽量采用右模糊查询， 即like ‘…%’，是会使用索引的；
左模糊like ‘%...’无法直接使用索引，但可以利用reverse + function index的形式，变化成 like ‘…%’；
全模糊查询是无法优化的，一定要使用的话建议使用搜索引擎。

14、count(*)推荐使用
count(*) ：它会获取所有行的数据，不做任何处理，行数加1。
count(1)：它会获取所有行的数据，每行固定值1，也是行数加1。
count(id)：id代表主键，它需要从所有行的数据中解析出id字段，其中id肯定都不为NULL，行数加1。
count(普通索引列)：它需要从所有行的数据中解析出普通索引列，然后判断是否为NULL，如果不是NULL，则行数+1。
count(未加索引列)：它会全表扫描获取所有数据，解析中未加索引列，然后判断是否为NULL，如果不是NULL，则行数+1。
由此，最后count的性能从高到低是：
count(*) ≈ count(1) > count(id) > count(普通索引列) > count(未加索引列)
可以为对应表key_len较小的列建立二级索引，以优化count(*)执行效率

**15、 慎用 IN 和 NOT IN**
IN 和 NOT IN 也要慎用，否则会导致全表扫描。对于连续的数值，能用 BETWEEN 就不要用 IN
select id from t where num between 1 and 3;
**注意：**
select id from t where num not in (1,2,null);
如果查询集合中有null，不适合用not in，容易出错（查询结果为空）。
为什么not in (1,2,null)会出错呢？
因为not in (1,2,null) 等价于x!=1 and x!=2 and x!=null ，**而在sql中，对于任意x，x!=null永远为false**，所以整体结果永远为false，所以查询结果永远为空。
**解决方法**
（1）仍使用not in ，但修改sql语句。
将in后的查询结果中的null过滤掉。
select name from a where name not in (
 select name from b where name is not null
);
（2）使用not exists
共有的不要了，要独有的。
select * from a 
where not exists(
     select 1 from b where a.col = b.col
);

16、选择 exists 代替 in
**in**先查询子查询的表，然后将内表和外表做一个笛卡尔积，然后按照条件进行筛选。子查询为驱动表
当在两表查询的时候， 外表是大表（数据量多），内表是小表（数据量少），把外表(A)和内表(B)做hash连接，，先查询内表，再把内表结果与外表匹配，对外表使用索引（外表效率高），而内表都需要查询，不可避免，故外表大的使用in，可加快效率。
**exists**先执行主查询，再根据主查询的结果，执行子查询。主查询为驱动表
当在两表查询的时候， 外表是小表（数据量少），内表是大表（数据量多）， 对外表做loop循环，每次loop循环再对内表进行查询，对内表的查询可使用索引（内表效率高），而外表大都需要遍历，不可避免（尽量用小表），故内表大的使用exists，可加快效率；
当子查询的结果较小，且主查询的表较大且有索引时，应用in。
当主查询的结果较小，而子查询的表较大且有索引时，应用exists。
SQL必须遵循“小表驱动大表“的原则
表A(小表)，表B(大表)
select * from A where id in(select id from B)　　-->效率低，用到了A表上id列的索引；
select * from A where exists(select id from B where id =A.id )　　-->效率高，用到了B表上id列的索引。
select * from B where id in(select id from A)　　-->效率高，用到了B表上id列的索引
select * from B where exists(select id from A where id=B.id)　　-->效率低，用到了A表上id列的索引。

17、where后面的字段，留意其数据类型的隐式转换。
SELECT *FROM user WHERE NAME=110;
因为不加单引号时，是字符串跟数字的比较，它们类型不匹配；
MySQL会做隐式的类型转换，把它们转换为数值类型再做比较；

18、为 WHERE 及 ORDER BY 涉及的列上建立索引
对查询进行优化，应尽量避免全表扫描，首先应考虑在 WHERE 及 ORDER BY 涉及的列上建立索引

19、选择重复值较低的字段建索引
在创建索引时，一定要选择重复值较低的字段。离散型非常的差，优化器可能直接就选择不走索引了，因为优化器可能认为，走索引和全表扫描差不多。值分布很稀少的字段不适合建索引，例如"性别"这种只有两三个值的字段不适合做索引。

20、join使用问题
尽量不要使用子查询，表设计好很关键

21、asc和desc混用，导致索引失效
select * from_t where a=1 order by b desc, c asc;

22、在新建临时表时，如果一次性插入数据量很大，那么可以使用 select into 代替 create table，避免造成大量 log ， 以提高速度；
如果数据量不大，为了缓和系统表的资源，应先create table，然后insert。

SQL索引的优化-执行计划EXPLAIN
EXPLAIN可以分析SQL问题，写出更优质的SQL查询语句。使用方法，在select语句前加上EXPLAIN即可
在这里插入图片描述
EXPLAIN返回参数（重点关注加粗）：
（1）id：执行计划中每个操作的唯一标识，多表联查的时候会出现多条数据id是一样的
（2）select_type：查询类型，常见类型有：SIMPLE、PRIMARY、UNION、SUBQUERY
（3）table：涉及到的表
（4）partitions：涉及到的分区
（5）type：查询时使用到的索引类型：优>差：system>const>eq_ref>ref>range>index>ALL
system：系统表
const：常数索引，通常情况下，如果将一个主键放置到where后面作为条件查询，mysql优化器就能把这次查询优化转化为一个常量
eq_ref:唯一索引，使用primary key 或者unique key 索引的所有部分，只扫描索引树种的一个一个匹配行，返回唯一一条数据
ref：非唯一索引，而是使用普通索引或者唯一索引的部分前缀，只扫描索引树种一部分数据来查找匹配行，可能返回多条数据
range：范围索引，只扫描索引树种的一个范围来查找匹配的行
index：全索引扫描，使用某个二级索引，遍历索引树
ALL：全表扫描
（6）possible_keys：可能被查询优化器使用到的索引
（7）key：查询优化器选择的索引，会显示实际使用到的索引名称
（8）key_len：索引长度
（9）ref：索引的那一列被使用
（10）rows：估计要读取并检测的行数
（11）filtered：按表条件过滤的行百分比
（12）Extra：额外信息
Using where：表示使用了where过滤，过滤条件字段无索引。
Using index：使用了覆盖索引,只需要扫描索引，无需回表检索（推荐）
using index condition：查询的列被索引覆盖，where筛选条件是索引列之一，确实命中了索引，但不是所有的列数据都在索引树上
Using temporary：创建了临时表用来存储结果，排序或者分组的情况下，需要创建索引（典型：group by和order by同时存在，且作用于不同的字段时，就会建立临时表）
Using filesort：表示（order by列）没有使用索引的排序，数据较小时从内存排序，否则从磁盘完成，需要创建索引
Using join buffer (Block Nested Loop)：需要进行嵌套循环计算，这类SQL语句性能往往也较低，需要进行优化。典型的，两个关联表join，关联字段均未建立索引，就会出现这种情况。常见的优化方案是，在关联字段上添加索引，避免每次嵌套循环计算。

SQL数据库、表优化
分库分表是数据库设计中的一个重要策略，用于处理单个数据库实例无法处理的大量数据和高并发的情况。
1、分表
在这里插入图片描述
（1）纵向分表
就是把一张表中的字段，切分到多张表。
分表理由：表的字段太多，根据数据的活跃度进行分离（因为不同活跃的数据，处理方式是不同的）
案例：
对于一个博客系统，文章标题，作者，分类，创建时间等，是变化频率慢，查询次数多，而且最好有很好的实时性的数据，我们把它叫做冷数据。而博客的浏览量，回复数等，类似的统计信息，或者别的变化频率比较高的数据，我们把它叫做活跃数据。所以，在进行数据库结构设计的时候，就应该考虑分表，首先是纵向分表的处理。
纵向分表后：
首先存储引擎的使用不同，冷数据使用MyIsam 可以有更好的查询数据。活跃数据，可以使用Innodb ,可以有更好的更新速度。
其次，对冷数据进行更多的从库配置，因为更多的操作是查询，这样来加快查询速度。对热数据，可以相对有更多的主库的横向分表处理。
其实，对于一些特殊的活跃数据，也可以考虑使用memcache ,redis之类的缓存，等累计到一定量再去更新数据库。或者mongodb一类的nosql 数据库。
（2）横向分表
就是是把大的单表结构，横向切割为同样结构的多表，如，用户信息表，user_1,user_2 等。表结构是完全一样，但是，根据某些特定的规则来划分的表，如根据用户ID来取模划分。
分表理由：根据数据量的规模来划分，保证单表的容量不会太大，从而来保证单表的查询等处理能力。
案例：
对于一个博客系统。当博客的量达到很大时候，就应该采取横向分割来降低每个单表的压力，来提升性能。例如博客的冷数据表，假如分为100个表，当同时有100万个用户在浏览时，如果是单表的话，会进行100万次请求，而现在分表后，就可能是每个表进行1万个数据的请求（因为，不可能绝对的平均，只是假设），这样压力就降低了很多很多。
2、分库
同分表策略
在这里插入图片描述
3、分库分表
分表+分库

4、分库分表采用的策略
（1） Range 范围
按某个字段的数据区间来进行切分。
比如：user表按照 user_id 的数据范围切分成多张表，每 1000万条数据存放一张表，切分后的表可以放到同一个数据库，也可以放到不同的数据库，示例图如下
在这里插入图片描述
优点：
方便扩容，每次数据量达到 range值就新加一张表，可以通过代码实现自动化扩容；
缺点：
存在写偏移，可能有热点问题；
用户注册场景：user表，因为新注册的用户数据都是写新表，通常来说新用户的活跃度高，所以读写流量全部集中在最新的 user表，因此，新表可能存在热点问题。
（2）hash切分
通过对分表键 key 进行一定的运算（通常有取余、取模运算，比如：key % m，key / m，hash(key)/m 等等），通过运算结果来决定路由的库和表。目前大多数互联网公司主要采用该方法。
比如：user表信息，根据 user_id 对 10 取余，这样就可以通过 user_id 尾号 hash 到 user_0 到 user_9 10张表中：
在这里插入图片描述
优点：
数据分片比较均匀，大大降低热点问题；
缺点：
hash 算法选择不合理，后期扩容可能需要迁移数据；
数据被切分到不同的库和表中，可能存在跨节点查询和分页等问题；
（3）映射表
映射表其实是 Range范围和 hash切分的混合模式，将分表键和数据库的映射关系记录在一个单独的表（表的形式可以是数据库表，文件或者配置中心）。
某社区电商下单场景，因为全国仓库的数量有限，所以分库直接使用了仓编编码-数据库映射表（后期新增加仓库，只要在表中增加映射关系），为了保证履约的时效性，用户下单时，商城端会选择最近的仓库，服务器在映射表中根据仓库编码查询并路由到对应的数据库，最后在库中进行 order表的操作，交互如下图：
在这里插入图片描述
优点：
可以灵活设置路由规则；
缺点：
方案比较复杂；
映射表可能也会随着业务量的增大，同样需要分库分表，带来更多的问题；
5、分库分表的问题
（1）调试和维护难度
单库单表，可以很直观在表中查看数据，分库分表后，需要先根据 key找到库和表，这样在一定意义上增加了开发人员定位问题的难度，再因为库和表的增多，维护难度自然也上去了（公司有DBA可以交给他们）。
（2）分布式ID
单库单表，可以直接使用表自增主键保证全局唯一性，分库分表后，需要自己维护全局唯一的ID，常用的算法有：UUID、号段模式（数据库生成全局ID）、雪花算法。
对于公司内部没有分布式ID相关实现的，可以使用或借鉴美团开源的Leaf ，该框架提供了雪花算法和号段模式两种方案。
（3）分布式事务
业务划分的时候规避分布式事务；
使用专业的的分布式框架，比如阿里开源的 Seata、Redission redis分布式锁；
（4）跨库关联/分页/排序
单库单表可以直接使用 MySQL limit 特性实现分页，分库分表后，可能会出现分页问题，解决方案有三种：
选择合适的分表字段，规避绝大部分高频查询场景出现跨库；
使用专业的分布式框架，比如开源框架：ElasticSearch；
业务代码中分别查询，然后组装数据；
（5）分库分表工具
客户端模式
客户端模式是指在客户端实现直连数据库，客户端通常是通过一些封装好的 jar来实现，常见的开源中间件有：Apache的Sharding-JDBC、淘宝的TDDL、美图的Zebra。
在这里插入图片描述
代理模式
代理模式是指需要单独部署服务，客户端连接代理服务，由代理服务再和数据库交互。
常见的开源中间件有：Apache的 Sharding-Proxy、阿里的 cobar、国产的 MyCat、360的 Atlas、 google的 vitess，它是基于 zookeeper，通过 RPC方式进行数据管理。
在这里插入图片描述
总结
两种方案的核心思想都是类似的，都是将分库分表的逻辑进行抽象封装，业务无需关注分库分表的实现细节，只需按照规则进行简单的配置和开发，就能正常的使用分库分表。
客户端模式比较轻量，性能也会比较好；代理模式需要部署额外的服务器，所以对于该服务器的稳定性和性能等都需要保障。