MySQL高级--数据库优化方案

数据库优化方案

（一）选取最适用的字段约束（面试重点）

MySQL可以很好的支持大数据量的存取，但是一般说来，数据库中的表越小，在它上面执行的查询也就会越快。因此，在创建表的时候，为了获得更好的性能，我们可以将表中字段的宽度设得尽可能小。

比如，应该尽量把字段设置为NOT NULL，这样在将来执行查询的时候，数据库不用去比较NULL值。对于某些文本字段，例如“省份”或者“性别”，我们可以将它们定义为ENUM类型。

gender ENUM('0', '1')

（二）使用连接（JOIN）来代替子查询(Sub-Queries)

这个技术可以使用SELECT语句来创建一个单列的查询结果，然后把这个结果作为过滤条件用在另一个查询中。连接（JOIN）之所以更有效率一些，是因为MySQL不需要在内存中创建临时表来完成这个逻辑上的需要两个步骤的查询工作。

（三）使用事务保证数据的安全

通常情况，需要用到一系列的语句来完成某种工作。但是在这种情况下，当这个语句块中的某一条语句运行出错的时候，整个语句块的操作就会变得不确定起来。设想一下，要把某个数据同时插入两个相关联的表中，可能会出现这样的情况：第一个表中成功更新后，数据库突然出现意外状况，造成第二个表中的操作没有完成，这样，就会造成数据的不完整，甚至会破坏数据库中的数据。要避免这种情况，就应该使用事务。事务的另一个重要作用是当多个用户同时使用相同的数据源时，它可以利用锁定数据库的方法来为用户提供一种安全的访问方式，这样可以保证用户的操作不被其它的用户所干扰。

（四）使用索引（面试重点）

应该考虑在where及order by 涉及的列上建立索引

建立索引不是建的越多越好，原则是：

1. 多数查询经常使用的列；
2. 很少进行修改操作的列；
3. 索引需要建立在数据差异化大的列上

（五）优化的查询语句（面试重点）

绝大多数情况下，使用索引可以提高查询的速度，但如果SQL语句使用不恰当的话，索引将无法发挥它应有的作用。

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

• 应尽量避免在 where 子句中使用 or 来连接条件，如果一个字段有索引，一个字段没有索引，将导致引擎放弃使用索引而进行全表扫描

• in 和 not in 也要慎用，否则会导致全表扫描

• like语句优化：不要在关键词前加% 比如‘%abc%’ 应该‘abc%’

• 任何地方都不要用 select * from table ，用具体的字段列表替换"*"，不要返回用不到的字段

• 使用字段时避免类型转换，会导致mysql无法使用索引

• Inner join 和 left join、right join、子查询

  • 能用inner join连接尽量使用inner join连接
  • 子查询的性能又比外连接性能慢，尽量用外连接来替换子查询

• 当我们的查询条件有多个时，我们的 把有索引的字段放在前面，索引使用最左原则