sql 数据库的优化【经验贴】

所谓数据库的优化，就是要实现： 执行sql语句时要 耗最小的性能、最短的时间

1.正确创建索引

对于那些查改频繁且数据量较大的表（>50万条数据的表）
索引的创建，可以极大的优化sql的执行效率，是我们优化数据库最常用的方法。
除了要了解那些常见的索引分类：主键索引、唯一索引、聚集索引、非聚集索引

这里我主要想讲一下 联合索引(或者叫多列索引) ：

应对场景：在实际工作中，对于一个承载着业务核心的表，它除了数据条数多之外，而且会有很多列。 对于这样核心的表，我们又频繁的有查询修改操作。查询的时候 往往是多个列在一起构成筛选条件,所以针对这种情况我们就需要创建合理的 联合索引。

这里通过创建一个用户信息表来讲解，表名为userbase 表信息如下：

创建一个联合索引 ，由 age,score,sex 组成
注意他们的前后顺序 ：age 是1 ，score是2 ，sex是3

执行不同条件查询及结果：
语句：select * from userbase where age =10 and score=98 and sex=‘男’ (顺序：1、2、3)
结果：使用了索引

语句：select * from userbase where age =10 and score=98 (顺序：1、2)
结果：使用了索引

语句：select * from userbase where age =10 and sex=‘男’ (顺序：1、3)
结果：使用了索引

语句：select * from userbase where age =10 (顺序：1)
结果：使用了索引

语句：select * from userbase where score=98 and sex=‘男’ (顺序：2、3)
结果：未使用索引

语句：select * from userbase where score=98 (顺序：2)
结果：未使用索引

语句：select * from userbase where sex=‘男’ (顺序：3)
结果：未使用索引

得出结论：
这就是联合索引的最左原则： 以最左边的那列为中心，只要它参与了，就可以让索引起到作用，否则就不起作用。
我们知道了最左原则，就可以避免创建过多而无用的索引。索引的创建，会占用硬盘大量的空间，所以合理的创建索引，不但可以让我们查询效率提高，也能减少硬盘空间。

2.建表时合理选择数据类型

在创建表初期，要使用合理的数据类型，让表占用空间更小，效率更高。业务后期表可能很大，上千万的数据，那是就能体会到，合理使用数据类型后带来的高效提现。
首先我们熟悉数据类型，以及它们的范围。

更多数据类型：
https://www.w3school.com.cn/sql/sql_datatypes.asp

3.不滥用事务

不同的数据库，有自己默认的事务类型。
我们在做开发时，因为某些关键业务比如涉及到金钱的转账等，必须用事务防止出现脏数据(或脏读现象)，保证数据的准确性。 但是这样的情况下，必定有损执行性能，在一些不必要的业务中，建议取消事务。
如我要查询某个结果，这个结果即使出现脏读，也不影响我们业务。就可以取消事务，来提高整体效率。
这里以sql server为例：

4.使用定时作业去优化结构

在业务中通过埋点，我们可能需要保存用户的行为数据。这是巨大的统计量，每天可能产生上千万条数据，我们除了要保存这上千万条数据到一个表中。
为了方便不同维度的查询，我们需要聚合这千万数据。可以设置每一个小时聚合一次，或者每天聚合一次。
聚合操作的执行，就可以由sql的定时作业完成。
在sql server中：

5.避免不必要的数据库连接

如果要执行100行sql语句，如果可以一次执行，那就在一次数据库连接，一起执行100条。而不是通过100次数据库连接，每次连接执行一条。较少数据库连接时产生的性能消耗。

在java项目中，我们会这么写：

6.学会使用sql分析工具(profiler)

在数据库出现异常时。我们需要定位查询原因。可以使用sql分析工具来跟踪定位查到，是哪一条sql语句导致的问题，导致的是什么问题。都可以查询到。

sql server profiler 截图示例：