常见数据库优化技巧总结

分手后的思念是犯贱 2023-10-18 14:16 142阅读 0赞

#### 一、索引 ####

本文以mySQL为例。

使用索引是最常见的优化方式，在千万级数据库以下，索引能大幅度提升查询效率。  
这里介绍一下索引使用的几个注意点。  
先介绍一下几种常见的索引种类。

`Primary key` 主键索引（所有的主键都是主键索引，唯一，不为空）

`Unique` 唯一索引（设置为该索引后唯一）

`Index` 普通索引（仅表示该字段设置为索引）

`Index` (id,username) 联合索引（将多个字段设置为索引，同时查找这几个字段时触发索引）

`Full text` 全文索引

##### 1、索引有“最左匹配原则” #####

在模糊查询中，如果出现左模糊和全模糊，则不会命中索引。  
即左边有‘%’时，不命中索引。

//命中索引
    SELECT id FROM user WHERE username LIKE "aa%"；
    //未命中索引
    SELECT id FROM user WHERE username LIKE "%aa"；
    //未命中索引
    SELECT id FROM user WHERE username LIKE "%aa%"；

在联合索引中，左边的字段命中索引。  
我们假设只设置了`Index(id,username,iphone)`的联合索引。  
如果查询条件只出现了左边的的字段一样可以命中索引。  
但如果左边的字段没出现，却出现了右边的字段则没有命中索引。

//名中索引
    SELECT * FROM user WHERE id='001' AND username='tom' AND iphone='110'；
    //名中索引
    SELECT * FROM user WHERE id='001' AND username='tom'；
    //名中索引
    SELECT * FROM user WHERE id='001'；
    //未命中索引
    SELECT * FROM user WHERE username='tom' AND iphone='110'；
    //未命中索引
    SELECT * FROM user WHERE id='001' AND iphone='110'；
    //未命中索引
    SELECT * FROM user WHERE iphone='110'；

##### 2、其他无法命中索引的操作 #####

WHERE子句中对字段进行表达式操作，函数操作等操作时无法名中索引

//未名中索引
    SELECT id FROM user WHERE number/2 = 100;
    //未名中索引
    SELECT id FROM user WHERE substring(name,1,3) = 'abc';
    //将运算放在‘=’右边后，命中索引
    SELECT id FROM user WHERE number = 100*2;

使用OR连接条件时，若其中一个条件未设置索引则不会命中索引。

//如果id和phone中有一个未设置索引，则不命中索引。
    SELECT * FROM user WHERE id='001' OR phone='110'；

判断一个字段是否不为NULL时，不会命中索引。  
判断一个字段是否为NULL时，命中索引

//未命中索引
    SELECT id FROM user WHERE phone IS NOT NULL;
    //命中索引
    SELECT id FROM user WHERE phone IS NULL;

使用NOT IN时不会命中索引

//未命中索引
    SELECT * FROM user WHERE id NOT IN(1,2,3);
    //命中索引
    SELECT * FROM user WHERE id IN(1,2,3);

索引并不是越多越好，索引固然可以提高相应的 select 的效率，但同时也降低了 insert 及 update 的效率，因为 insert 或 update 时有可能会重建索引。  
推荐一篇不错的数据库优化博客。  
[数据库SQL优化大总结1之- 百万级数据库优化方案][SQL_1_-]

#### 二、exists和in的选择 ####

经常听到一些说法，说in的效率没有exists高，应该用exists替换掉in子句。但实际上并不是这样的，我们应该根据实际情况进行选择。  
我们假设一个情况，一张学生表student和一张寝室表dorm。  
假设我们要查找所有寝室在A区的学生id,这里section字段未设置索引。

select id from student where dorm_id in (select id from dorm where section='A');
    select id from student where exists(select null from dorm where id=dorm_id and section='A')；

看上面两个sql语句，做同一件事，一个使用的是in一个使用exists。  
第一条语句中，外表可以使用了dorm \_id的索引，内表没使用索引。  
第二条语句中，外表没有使用索引，内表可以使用id的索引。  
因此，当内表很大时，第一条语句没有使用索引效率低下，第二条语句使用索引就可以提高效率。  
而当内表不大，反而外表很大时，第一条语句就具有优势。  
所有，使用exists时，就是在内表使用索引。使用in时，就是在外表使用索引。  
总结：**IN适合于外表大而内表小的情况；EXISTS适合于外表小而内表大的情况。**  
详细请看这篇博客。  
[Exists和in的效率哪个高][Exists_in]

#### 三、分库分表 ####

当数据库数据达到千万级开始，索引的作用就越来越差了，为了提高效率，程序员们想到了拆分数据的方法。

##### 1、水平分表 #####

当一个表数据过多时，查询效率自然变慢，为了提高查询效率我们可以将表A拆分成表A1,A2,A3…字段都是一样的，将数据以一定规律均匀分布在多个表中，提高数据库操作效率。

##### 2、垂直分表 #####

垂直分表是指，有些表字段很多，我们可以将一些不常用的，比较长的字段拆分出来，做成一个新表，即‘大表拆小表’，便于开发与维护。

分表说到底还是在一个数据库中操作，没有解决数据库连接不够的问题，数据库瓶颈依然存在，因此我们进行分库操作。

##### 3、水平分库 #####

水平分库，就是不将所有的表放在一个数据库中，比如好友模块的表放在一个数据库中，另一些交易模块的表放在另一个数据库中。这样就实现了高内聚低耦合，现如今的微服务框架就是水平分库的应用。

##### 4、垂直分库 #####

垂直分库其实就是集群的概念，将数据库A拆分成A1,A2,A3…里面的构造都是一样的，只把数据均匀分散在多个数据库中，以提高数据库操作效率。

同时分库分表也会带来很多新的问题，比如说事务变得更加复杂，全局主键避免重复等等。

详细请看这篇博客。  
[数据库分库分表思路][Link 1]  
（未完待续）

[SQL_1_-]: https://mp.weixin.qq.com/s?__biz=MzIxMjg4NDU1NA==&mid=2247483684&idx=1&sn=f5abc60e696b2063e43cd9ccb40df101&chksm=97be0c01a0c98517029ff9aa280b398ab5c81fa1fcfe0e746222a3bfe75396d9eea1e249af38&mpshare=1&scene=1&srcid=0606XGHeBS4RBZloVv786wBY#rd
[Exists_in]: https://blog.csdn.net/u011627980/article/details/50370566
[Link 1]: https://www.cnblogs.com/butterfly100/p/9034281.html