数据库高级——数据库优化1

怼烎@ 2022-12-22 11:19 158阅读 0赞

### 数据库高级——数据库优化1 ###

*  数据库优化
 *   *  优化方式
     *   *  索引
         *  分库分表分区
         *  预处理
         *  sql语句优化
         *  读写分离

# 数据库优化 #

随着系统规模不断增加，数据量和并发量不断增大，整个系统架构最先受到冲击形成瓶颈的必然是数据库；因此数据优化是每一个程序员都需要了解和掌握的技能之一

**SQL的执行过程**  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxNTEwNTUx_size_16_color_FFFFFF_t_70_pic_center]

当你在sql客户端（命令行或者管理工具的图形化界面）提交一条语句后，sql解析引擎会启动，sql引擎回去解析优化这条sql（比如自动类型转化处理，帮你选择最优的索引），数据库引擎拿着优化后的sql命令去硬盘中查找数据，然后将查找数据返回（如果此时返回的结果集过大，会造成数据库IO繁忙，会大大影响sql效率，所以一般使用分页的原因就在此）

## 优化方式 ##

### 索引 ###

*  建立索引是数据库优化的各种方案中成本最低的，见效最快的解决方法，一般来讲，当数据库数据在几十万到几百万的数据之间时，见效最快，即便有不太复杂的表关联，也能够大幅度提高sql运行效率

**建立索引需要注意的地方**

1.  索引一般加在查询条件的关键字上，如果有多个查询条件关键字，可以添加组合索引；写sql是时候需要注意 **`索引字段要和sql字段保持一致，否则会索引失效`**
2.  不要在查询=前面使用函数，否则会导致索引失效
3.  建立索引的字段要区分度比较高，比如性别字段，只有男或女两种值，区分度不高，建立索引效果不好
4.  建立组合索引，可以持续提高sql执行效率，但也不能盲目使用，同样要注意区分度，区分度不够就尽量不要加，**多个字段，尽量把区分度高的字段放前面；** 另外还要注意索引长度，需要考虑索引长度和区分度的平衡
5.  索引会大幅提升查询效率,但是也会损耗查询后修改效率，要注意兼顾平衡，使用在一次插入， 多次查询的表上效果最好，同时要注意的是，组合索引会不可避免的增加索引长度，会增加索弓|存储空间，注意索引长度和区分度平衡
6.  mysql支持全文索引

### 分库分表分区 ###

分库：可以按照业务进行分库，分流数据库的并发压力，使数据库更加具有条理性  
分表：索引适用于百万级别的数据量，千万级别数据量，使用的好也勉强能够使用，但是对于上亿级别的数据量，索引就无能为力了，因为单索引文件可能就可能上百兆了，或者更多，所以此时就需要使用其他优化方式——分表  
**分表方式**

*  如果这个业务是有流程的,那么我们通常会设计一个历史表或者归档表,来存放历史数据,这样能保证实时数据效率比较高
 *  针对某一张大表， 可以根据查询条件分成多张表，比如时间，我们可以将半个月或者10天的数据放到一张表里(看具体数据量,个人认为3000W是个上限，最好控制到百万级别)，每过10天,我们就自动创建一张数据库表, 然后将数据插入，如此，按照时间查询，就要先定位去那种表中去取数，这样，效率能够得到大幅度提升，当然，这么解决也有问题，比如跨表，需要union多张表，且跨表没法支持索引
 *  上面的方法是我们直接通过程序和数据库实现的最原始的分表解决方案， 现在市面上有一些成熟的软件如mycat， 也是支持分表的,我们之前从事的公司有个专门做分布式数据库的，这些产品出现跨表，可以不使用程序union了 ，而且还是使索引生效，但是需要对产品有一定的掌握
 *  般来讲,数据库中的大表毕竟只是一少部分, 仅需要对这少部分大表进行分表就可以了，没必要小表也进行分表,增加维护开发难度
 *  分表分为垂直拆分和水平拆分：
    
    垂直拆分：把原来的一个很多字段的表拆分多个表，解决表的宽度问题。你可以把不常用的字段单独放到一个表中，也可以把大字段独立放一个表中，或者把关联密切的字段放一个表中。
    
    水平拆分：把原来一个表拆分成多个表，每个表的结构都一样，解决单表数据量大的问题。

**分区**  
分区的实现道理和分表一样, 也是将相应规则的数据放在一起，唯一不同的是分区 你只需要设定好分区规则，插入的数据会被自动插入到指定的区里，当然查询的时候也能很快查询到需要区，相当于是分表对外透明了，出现跨表数据库自动帮我们合并做了处理，使用起来比分表更加方便，但是分区也有自己2的问题，每一个数据库表的并发访问是有上限的,也就是说，分表能够抗高并发，而分区不能，如何选择，要考虑实际情况

### 预处理 ###

一般来说，实时数据（当天的数据，不包括双十一这种大型活动的时候）比较有限，真正数据量比较大的是历史数据，基于大表历史数据查询，如果还涉及大表的关联，sql是非常难优化的，所以需要进行与预处理

1.  实时数据（当天数据）：通过对业务的抽象，可以放到缓存里面，提升系统运行效率
2.  历史数据，大数据表且存在大表关联，，通常常规的sql很难进行优化，但通常这类数据对实时性要求不高，这就可以使用预处理解决

*  将这些复杂表关联的sql写成一个定时任务在半夜执行，将执行结果存到一张结果表中，第二天直接查询结果表

1.  和2类似，可以将表关联结果存入solr或者elastisearch

### sql语句优化 ###

1.  对查询进行优化，要尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引。
2.  应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描（**最好不要给数据库留NULL，尽可能的使用 NOT NULL填充数据库.**）
3.  应尽量避免在 where 子句中使用 != 或 <> 操作符，否则将引擎放弃使用索引而进行全表扫描。
4.  应尽量避免在 where 子句中使用 or 来连接条件，**如果一个字段有索引，一个字段没有索引，将导致引擎放弃使用索引而进行全表扫描**
5.  in 和 not in 也要慎用，否则会导致全表扫描；**对于连续的数值，能用 between 就不要用 in 了；很多时候用 exists 代替 in 是一个好的选择**

select id from t where num in(1,2,3)
    select id from t where num between 1 and 3
    
    select num from a where num in(select num from b)
    select num from a where exists(select 1 from b where num=a.num)

1.  尽量避免like查询：`like '%string%'`不支持索引，但是`like 'string%`支持索引
2.  如果在 where 子句中使用参数，也会导致全表扫描。因为SQL只有在运行时才会解析局部变量，但优化程序不能将访问计划的选择推迟到运行时；它必须在编译时进行选择。然而，如果在编译时建立访问计划，变量的值还是未知的，因而无法作为索引选择的输入项。
3.  应尽量避免在 **where 子句中对字段进行表达式操作**，这将导致引擎放弃使用索引而进行全表扫描。
4.  应尽量避免在where子句中对字段进行函数操作，这将导致引擎放弃使用索引而进行全表扫描
5.  在使用索引字段作为条件时，如果该索引是复合索引，那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引，否则该索引将不会被使用，并且应尽可能的让字段顺序与索引顺序相一致。
6.  Update 语句，如果只更改1、2个字段，不要Update全部字段，否则频繁调用会引起明显的性能消耗，同时带来大量日志
7.  对于多张大数据量（这里几百条就算大了）的表JOIN，要先分页再JOIN，否则逻辑读会很高，性能很差
8.  select count(\*) from table；这样不带任何条件的count会引起全表扫描，并且没有任何业务意义，是一定要杜绝的。
9.  索引并不是越多越好，索引固然可以提高相应的 select 的效率，但同时也降低了 insert 及 update 的效率，因为 insert 或 update 时有可能会重建索引，所以怎样建索引需要慎重考虑，视具体情况而定。一个表的索引数最好不要超过6个，若太多则应考虑一些不常使用到的列上建的索引是否有 必要。
10. 尽可能的使用 `varchar/nvarchar` 代替`char/nchar` ，因为首先变长字段存储空间小，可以节省存储空间，其次对于查询来说，在一个相对较小的字段内搜索效率显然要高些。
11. 任何地方都不要使用 select \* from t ，用具体的字段列表代替“\*”，不要返回用不到的任何字段。

### 读写分离 ###

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxNTEwNTUx_size_16_color_FFFFFF_t_70_pic_center]: /images/20221120/8a99cd9754f646298e756a98ddd5371e.png