数据库调优学习笔记

前言

今天突然学完了mysql面试之后，突然发现数据库调优不是很懂，学习之后打算写一篇学习笔记可以和大家交流一下。

1.建立索引

为了避免全表扫描，首先应该考虑在where和order by涉及的列上建立索引，当发现检索速度过慢，首先想到的是创建索引。（ps：一个表的索引数最好不要大于6个，因为索引并不是越多越好，索引虽然可以提高select的速度，可是同时也降低了insert和update的效率，降低效率的原因是insert和update有可能会重建索引）

2.用union all来代替union

当使用union进行两个结果集的合并时，即使两个结果集没有重复的记录，它仍然会尝试合并，然后在输出最终结果前进行排序。因此当我们确定检索结果中没有重复记录的时候，应该用union all，这样子效率会更高。

3.尽量用一条sql语句来代替多条sql语句

每次执行sql的时候都要建立网络连接，进行权限校验，进行sql语句的查询优化，发送执行结果这个过程会反复进行，因此我们用一条sql语句来代替多条sql语句就可以尽量避免这个重复过程。

4.select查询语句中用具体的字段来代替*

任何地方都不要使用select * from table，用具体的字段列表来代替“*”，不要返回一些用不到的字段，这样子同样非常消耗效率。

5.尽量避免在索引上进行计算

在where字段中，如果索引是计算或函数的一部分的话，数据库的优化器将不会使用索引而是使用全表查询、函数。
效率低：

SELECT * FROM `stu` where salary * 50 > 2500

效率高：

SELECT * FROM `stu` where salary > 2500/50

6.insert语句优化

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

7.用where代替having

避免使用having字句，因为having只会在检索出所有记录之后才对结果集进行过滤，而where是在聚合前筛选记录，如果能通过where字句限制记录的数目，那就能减少这方面的开销。

8.修改where的连接顺序

数据库一般采用自下而上的顺序解析where字句，根据这个原理表连接最好卸载其他where条件之前，那样子可以过滤掉最大数量记录。

9.使用预编译查询

数据库通常是根据用户的输入来动态执行SQL，这时应该尽量使用参数化sql，这样子不仅避免了sql注入漏洞攻击，最重要的是数据库会对这些参数化sql进行预编译，这样第一次执行的时候数据库会对这个sql语句进行查询优化并且执行预编译，这样子下次再使用这个sql的时候就直接使用预编译的结果。

10.使用表的别名

当在sql语句中链接了多个表的时候，可以使用表的别名并把别名前缀于每个列名上，这样子可以减少解析的时间并减少由列名歧义引起的语法错误。

11.使用char来代替varchar

1.varchar的存储空间小，可以节省存储空间。
2.字段较小的字段搜索效率高些。

12.尽量避免使用游标

尽量避免向客户端返回大数据量，因为游标效率较差，如果数据量过大，应该考虑相应需求是否合理。

13.尽量避免使用in和not in

in和not in都会导致全表扫描，因此慎用in和not in。

14.应尽量避免在 where 子句中对字段进行 null 值判断

1.不要以为null不消耗空间，如果在cahr型字段建立的时候，空间就固定了，怎么样都会占用空间。
2.容易导致引擎放弃使用索引而进行全表扫描。
3.我们可以在字段上设置默认值为0，确保表中这个字段没有null值

15.避免使用！=或<>操作符

很容易导致全表扫描

16.分库分表分区

分库：分库是根据业务不同把相关的表切分到不同的数据库中
分表：分表分为垂直拆分和水平拆分：
垂直拆分：把原来的一个很多字段的表拆分多个表，解决表的宽度问题。你可以把不常用的字段单独放到一个表中，也可以把大字段独立放一个表中，或者把关联密切的字段放一个表中。
水平拆分：把原来一个表拆分成多个表，每个表的结构都一样，解决单表数据量大的问题。
分区：分区就是把一张表的数据根据表结构中的字段（如name、age、address）分成多个区块，这些区块可以在一个磁盘上，也可以在不同的磁盘上，分区后，表面上还是一张表，但数据散列在多个位置，这样一来，多块硬盘同时处理不同的请求，从而提高磁盘I/O读写性能，实现比较简单。