数据库--sql优化问题(百万级数据优化方案)

sql优化问题(百万级数据优化方案)

一、sql数据库优化方案

1、索引
2、分库分表分区
3、数据库引擎
4、预处理
5、读写分离

1、索引，建立索引是数据库优化各种方案之中成本最低，见效最快的解决方案，一般来讲，数据库规模在几十万和几百万级别的时候见效最快，即便是有不太复杂的表关联，也能大幅度提高sql的运行效率，这个在我们以前的项目应用中，有非常深刻的体会，本来耗时50s的sql，在增加索引后可以提升到1-2s，而且不需要有代码改动，成本低廉，见效明显

建立索引需要注意的地方

a、索引一般加在查询条件的关键字上，如果有多个查询条件关键字，还可以添加组合索引，写sql的时候需要注意，索引字段和sql字段需要保持一致，否则索引会无效，比如

简单粗暴一点儿，我直接使用我们主数据数据库(测试库)中的md_house_property_info表中的source_house_code_no字段，这个字段在数据库中被定义为了varchar类型，定义了多个索引，都包含了source_house_code_no字段

大家看，source_house_code_no我写成varchar类型的时候，是可以走索引的

当我写成数字的时候，sql能够正确执行，但是却没有命中索引

大家再细心一点儿会发现，我这里面有个possiable_keys，这个是指的可能命中的索引，此处出现了两个，但是数据库引擎会选择最优的一个idx_source_house_code_no,这个过程我在开始有介绍了

2、分库分表分区

分库，可以按照业务分库，分流数据库并发压力，使数据库表更加有条理性，最起码更加好找吧，我们当时是把查询库和系统库(增删改比较频繁的表)分开了，这样如果有大查询，不影响系统库

分表，刚才说了，索引适合应对百万级别的数据量，千万级别数据量使用的好，勉强也能凑合，但如果是上亿级别的数据量，索引就无能为力了，因为单索引文件可能就已经上百兆或者更多了，那么，轮到我们的分表分区登场了

分表的方法有很多种

a、如果这个业务是有流程的，那么我们通常会设计一个历史表或者归档表，用来存放历史数据，这样能保证实时数据效率比较高

b、针对某一张大表，可以根据查询条件分成多张表，比如时间，我们可以将半个月或者10天的数据放到一张表里(看具体数据量，个人认为3000W是个上限，最好控制到百万级别)，每过10天，我们就自动创建一张数据库表，然后将数据插入，如此，按照时间查询，就要先定位去那种表中去取数，这样，效率能够得到大幅度提升，当然，这么解决也有问题，比如跨表，需要union多张表，而且跨表没法支持索引

c、上面的方法是我们直接通过程序和数据库实现的最原始的分表解决方案，现在市面上有一些成熟的软件如mycat，也是支持分表的，我们之前从事的公司有个专门做分布式数据库的，这些产品出现跨表，可以不使用程序union了，而且还是使索引生效，但是需要对产品有一定的掌握

d、一般来讲，数据库中的大表毕竟只是一少部分，仅需要对这少部分大表进行分表就可以了，没必要小表也进行分表，增加维护开发难度

分区

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

3、数据库引擎

也是偶尔听一个dba同事提到的，有一次我跟dba同事抱怨，我的数据库查询太慢，有没有好的优化方法，他一开始就问，数据量多大，有没有索引，使用的什么数据库引擎，这时我才意识到原来数据库引擎也算是一种优化方案

mysql比较常用的数据库引擎有两种，一种是innodb、一种是myisam

我当时做过一个千万级数据量复杂sql测试，myisam的效率大概能够比innodb快1-2倍，虽然效率提升不是很明显，但是也有提升，后来查过一些资料，说之所以mysiam快，是因为他的数据存储结构、索引存储结构和innodb不一样的，mysiam的索引结构是在内存中存的

当然，mysiam也有弱点，那就是他是表级锁，而innodb是行级锁，所以，mysiam适用于一次插入，多次查询的表，或者是读写分离中的读库中的表，而对于修改插入删除操作比较频繁的表，就很不合适了

4、预处理

一般来说，实时数据(当天的数据)还是比较有限的，真正数据量比较大的是历史数据，基于大表历史数据的查询，如果再涉及一些大表关联，这种sql是非常难以优化的

a、实时数据(当天数据)

通过对对业务的抽象，可以放在缓存里面，提升系统运行效率

b、历史数据，大数据表历史数据且有表关联，通过常规sql难以优化，但是该数据通常有个共性，就是第二天去查询前一天的数据做分析报表，也就是说对时效性要求不高，这种情况的解决方案是预处理

做法是将这些复杂表关联sql写成个定时任务在半夜执行，将执行的结果存入到一张结果表中，第二天直接查询结果表,如此，效率能得到十分明显提升

c、和b类似，可以将表关联结果存入solr或者elastisearch中，以此提升效率，目前我们的项目就是如此处理

b、不要在查询=前面使用函数，否则会导致索引不生效，举个栗子，where str=substring(“hello world”,6,8),这样是可以走索引的，但是 where substring(str,6,8)=“hello world” 是不会命中索引的

c、建立索引的字段要区分度比较高，比如user表中有一个性别字段，性别字段无非男女两种值，区分度不好，建立索引效果不好，要选择区分度高的字段

d、建立组合索引，可以持续提升sql运行效率，但是也不要盲目，同样的要注意区分度，如果区分度不够高，就不要加了，多个字段，尽可能把区分度高的字段放在前面，另外，还要注意索引长度，这个索引要同时兼顾索引长度和区分度的平衡

e、索引会大幅提升查询效率，但是也会损耗查询后修改效率，要注意兼顾平衡，使用在一次插入，多次查询的表上效果最好，同时要注意的是，组合索引会不可避免的增加索引长度，会增加索引存储空间，注意索引长度和区分度平衡

f、后来因为工作需要，意外发现mysql居然支持全文索引，没测试过效率，正常使用全文索引都是使用 lunce，以及在其之上的solr和现在正火的elastisearch，后面可以单独来说.

5、读写分离

在数据库并发大的情况下，最好的做法就是进行横向扩展，增加机器，以提升抗并发能力，而且还兼有数据备份功能

---

二.SQL语句具体操作

1.对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引。

2.应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描，如：

select id from t where num is null

可以在num上设置默认值0，确保表中num列没有null值，然后这样查询：

select id from t where num=0

3.应尽量避免在 where 子句中使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描。

4.应尽量避免在 where 子句中使用 or 来连接条件，否则将导致引擎放弃使用索引而进行全表扫描，如：

select id from t where num=10 or num=20

可以这样查询：

select id from t where num=10

union all

select id from t where num=20

5.in 和 not in 也要慎用，否则会导致全表扫描，如：

select id from t where num in(1,2,3)

对于连续的数值，能用 between 就不要用 in 了：

select id from t where num between 1 and 3

6.下面的查询也将导致全表扫描：

select id from t where name like ‘%abc%’

7.应尽量避免在 where 子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描。如：

select id from t where num/2=100

应改为:

select id from t where num=100*2

8.应尽量避免在where子句中对字段进行函数操作，这将导致引擎放弃使用索引而进行全表扫描。如：

select id from t where substring(name,1,3)=‘abc’–name以abc开头的id

应改为:

select id from t where name like ‘abc%’

9.不要在 where 子句中的“=”左边进行函数、算术运算或其他表达式运算，否则系统将可能无法正确使用索引。

10.在使用索引字段作为条件时，如果该索引是复合索引，那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引，

否则该索引将不会被使用，并且应尽可能的让字段顺序与索引顺序相一致。

11.不要写一些没有意义的查询，如需要生成一个空表结构：

select col1,col2 into #t from t where 1=0

这类代码不会返回任何结果集，但是会消耗系统资源的，应改成这样：

create table #t(…)

12.很多时候用 exists 代替 in 是一个好的选择：

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)

13.并不是所有索引对查询都有效，SQL是根据表中数据来进行查询优化的，当索引列有大量数据重复时，SQL查询可能不会去利用索引，

如一表中有字段sex，male、female几乎各一半，那么即使在sex上建了索引也对查询效率起不了作用。

14.索引并不是越多越好，索引固然可以提高相应的 select 的效率，但同时也降低了 insert 及 update 的效率，

因为 insert 或 update 时有可能会重建索引，所以怎样建索引需要慎重考虑，视具体情况而定。

一个表的索引数最好不要超过6个，若太多则应考虑一些不常使用到的列上建的索引是否有必要。

15.尽量使用数字型字段，若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，并会增加存储开销。

这是因为引擎在处理查询和连接时会逐个比较字符串中每一个字符，而对于数字型而言只需要比较一次就够了。

16.尽可能的使用 varchar 代替 char ，因为首先变长字段存储空间小，可以节省存储空间，

其次对于查询来说，在一个相对较小的字段内搜索效率显然要高些。

17.任何地方都不要使用 select * from t ，用具体的字段列表代替“*”，不要返回用不到的任何字段。

18.避免频繁创建和删除临时表，以减少系统表资源的消耗。

19.临时表并不是不可使用，适当地使用它们可以使某些例程更有效，例如，当需要重复引用大型表或常用表中的某个数据集时。但是，对于一次性事件，最好使用导出表。

20.在新建临时表时，如果一次性插入数据量很大，那么可以使用 select into 代替 create table，避免造成大量 log ，

以提高速度;如果数据量不大，为了缓和系统表的资源，应先create table，然后insert。

21.如果使用到了临时表，在存储过程的最后务必将所有的临时表显式删除，先 truncate table ，然后 drop table ，这样可以避免系统表的较长时间锁定。

22.尽量避免使用游标，因为游标的效率较差，如果游标操作的数据超过1万行，那么就应该考虑改写。

23.使用基于游标的方法或临时表方法之前，应先寻找基于集的解决方案来解决问题，基于集的方法通常更有效。

24.与临时表一样，游标并不是不可使用。对小型数据集使用 FAST_FORWARD 游标通常要优于其他逐行处理方法，尤其是在必须引用几个表才能获得所需的数据时。

在结果集中包括“合计”的例程通常要比使用游标执行的速度快。如果开发时间允许，基于游标的方法和基于集的方法都可以尝试一下，看哪一种方法的效果更好。

25.尽量避免大事务操作，提高系统并发能力。

26.尽量避免向客户端返回大数据量，若数据量过大，应该考虑相应需求是否合理。

---

参考文献：https://blog.csdn.net/a13804947436/article/details/84782580

注：以上内容仅提供参考和交流，请勿用于商业用途，如有侵权联系本人删除！

---

持续更新中…

如有对思路不清晰或有更好的解决思路，欢迎与本人交流，QQ群：273557553
你遇到的问题是小编创作灵感的来源！

---