SQL优化

SQL优化

​ 执行子查询时，MySql需要建立临时表，查询完毕之后再删除这些临时表，所以，子查询的速度会受到一定的影响。

优化：可以使用连接查询 join 代替子查询，连接查询时不需要建立临时表，其速度比子查询快。

数据库结构优化

• 将字段很多的表分解成多个表，对于字段较多的表，如果有些字段的使用频率很低，可以将这些字段分离出来形成新表。因为一个表的数据量很大的时候，会由于使用频率低的字段存在而变慢

• 增加中间表

  ​ 对于需要经常联合查询的表，可以建立中间表，可以提高查询效率。

  ​ 通过建立中间表，将需要通过联合查询的数据插入到中间表中，然后将原来的联合查询改为中间表查询。

• 增加冗余的字段
  设计数据表的时候尽量遵循范式理论的规约，尽可能的减少冗余字段，让数据库设计看起来很精致，优雅。但是合理的加入冗余字段可以提高查询速度。

  对于冗余字段的设计，我们要根据不同的业务进行区分，一般来说对于数据改动比较少，而查询多的场景会使用冗余。
  例如:

  用户名称字段name本来属于表user，表示”用户昵称”的字段就唯一的只应该属于user表的”name”字段，当用户要修改昵称的时候，程序就只需要修改 user.name字段。但是在其他数据表(比如订单orders表)里只存储了用户的ID，我要通过这个ID值得到用户昵称该怎么办呢？一个普遍的解决方法是通过联接(join)查询通过ID这个唯一条件联接两个表，从而取到用户的昵称。

  这样确实是没问题，我也一直觉得这样是最好的方案，扩展方便，当要更新用户信息时，程序中要修改的地方很少，但是随着数据库里数据不断增加，百万，千万，同时，用户表的数据肯定也在不断的增加的，它可能是十万，百万。这个时候，你会发现两个表通过联接来取数据就显得相当费力了，可能你只需要取一个name这个用户昵称属性，你就不得不去联接查询一下那个已经几十万的用户表进行检索，其速度自然变得非常慢。
  所以我们这个时候可以将用户的name字段保存到订单表中，这样当我们查询订单列表的时候，对于用户名称我们就可以不用联接查询，但是冗余字段的值在一个表中修改了，就要想办法在其他的表中更新，否则将会导致数据不一致的问题。所以也增加了维护的开销。

插入数据的优化

插入数据的时候，影响插入速度的主要是索引，唯一性校验，一次插入多条数据等。

插入数据的优化，不同的存储引擎优化手段不一样，在MySql中常用的存储引擎有MyISAM和InnoDB

两者的区别主要有：MyISAM是MySQL5.5之前默认的搜索引擎，目前MySQL的默认搜索引擎是InnoDB，基于传统ISAM类型，支持全文搜索，但不是事务安全的，而且不支持外键，每张MyISAM表存放在三个文件中。

InnoDB是事务型引擎，支持回滚，多版本并发控制，ACID事务，支持行级锁定

主要区别

• MyISAM是非事务安全的，而InnoDB是事务安全的。
• MyISAM锁的粒度是表级，而InnoDB支持行级锁定。
• MyISAM支持全文类型索引，而InnoDB不支持全文索引。
• MyISAM相对简单，在执行效率上要优于InnoDB，小型应用可以考虑使用MyISAM。
• MyISAM表是保存文件的形式，在跨平台的数据转移中使用MyISAM存储会省去不少的麻烦。
• InnoDB表比MyISAM表更安全，可以保证数据不会丢失的情况下，切换非事务表到事务表。

禁用索引

​ 对于非空表，插入记录时，MySQL会根据表的索引对插入的记录建立索引。如果插入大量数据，建立索引会降低插入数据速度。

​ 为了解决这个问题，可以在批量插入数据之前禁用索引，数据插入完成后再开启索引。

• 禁用索引的语句：

ALTER TABLE table_name DISABLE KEYS

• 开启索引语句：

ALTER TABLE table_name ENABLE KEYS

对于空表批量插入数据，则不需要进行操作，因为MyISAM引擎的表是在导入数据后才建立索引。

禁用唯一性检查

• 唯一性校验会降低插入记录的速度，可以在插入记录之前禁用唯一性检查，插入数据完成后再开启。
• 禁用唯一性检查的语句：SET UNIQUE_CHECKS = 0;
• 开启唯一性检查的语句：SET UNIQUE_CHECKS = 1;

索引失效的场景

• 使用like关键字的查询

  在使用like关键字进行查询的查询语句中，如果匹配第一个字符串为%，索引不起作用，只有%不在第一个位置，索引才会起作用。

• 使用联合查询索引的查询

  MySQL可以为多个字段创建索引，一个索引可以包括16个字段。对于联合索引，只有查询条件中使用了这些字段中第一个字段时，索引才会生效。

• 使用OR关键字的查询

  查询语句的查询条件中只有OR关键字，并且OR前后的两个条件中的列都是索引时，索引才会生效，否则，索引不生效

数据库索引新建原则有那些？

1. 具有唯一性的属性必须增加唯一约束，与唯一索引
2. 经常出现在where子句中查询条件需要增加索引
3. 如果属性是大的文本不要增加索引
4. 字符串类型的索引需要指定长度，尽量使用前缀来早增加索引
5. 经常会被join联合查询的外键需要增加索引
6. 一张表不要增加太多的索引
7. 索引应该加在区分度比较高的属性上面
8. 不经常使用，或者很少使用的索引要删除掉

使用唯一索引会锁表