数据库基础

数据库三范式

开发中应严格遵循数据库三范式

• 第一范式：每个列都不可以再拆分。
• 第二范式：在第一范式的基础上，非主键列完全依赖于主键，而不能是依赖于主键的一部分。
• 第三范式：在第二范式的基础上，非主键列只依赖于主键，不依赖于其他非主键。

数据库索引：

• 索引是一种特殊的文件(InnoDB数据表上的索引是表空间的一个组成部分)，它们包含着对数据表里所有记录的引用指针。
• 索引是一种数据结构。数据库索引，是数据库管理系统中一个排序的数据结构，以协助快速查询、更新数据库表中数据。索引的实现通常使用B树及其变种B+树。
• 更通俗的说，索引就相当于目录。为了方便查找书中的内容，通过对内容建立索引形成目录。索引是一个文件，它是要占据物理空间的。

创建（优先考虑where、group by使用到的字段）

• 多个条件时可以创建多列索引，比多个单列索引效率高
• 让选择性最强的索引列（能排除更多记录的列）放在前面
• 对于BLOB、TEXT 和 VARCHAR类型的列，加前缀索引
• 覆盖索引：满足查询语句中所有字段与条件，这样可以直接在索引中查询，而不用再到数据库读取了

注意

• MySql在使用like进行模糊查询的时候把%放后面，避免开头模糊查询，因为MySql在使用like查询的时候只有使用后面的%时，才会使用到索引。
• 尽量避免使用in 和not in，会导致数据库引擎放弃索引进行全表扫描，可使用exists代替
• 尽量避免使用or，会导致数据库引擎放弃索引进行全表扫描，可使用union代替
• 尽量避免进行null值的判断，会导致数据库引擎放弃索引进行全表扫描，可以给字段添加默认值0，对0值进行判断
• 尽量避免在where条件中等号的左侧进行表达式、函数操作，会导致数据库引擎放弃索引进行全表扫描，可以将表达式、函数操作移动到等号右侧
• 当数据量大时，避免使用where 1=1的条件。通常为了方便拼装查询条件，我们会默认使用该条件，数据库引擎会放弃索引进行全表扫描

索引的优点

• 可以大大加快数据的检索速度，这也是创建索引的最主要的原因。
• 通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能。

索引的缺点

• 时间方面：创建索引和维护索引要耗费时间，具体地，当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，会降低增/改/删的执行效率；
• 空间方面：索引需要占物理空间。

SQL优化

索引优化

• 优点：建立索引后，查询时不会扫描全表，而会查询索引表锁定结果。
  • 缺点：在数据库进行DML操作的时候，除了维护数据表之外，还需要维护索引表，运维成本增加。
  • 应用场景：数据量比较大，查询字段较多的情况。

查询优化

• Explain 用来分析 SELECT 查询语句，开发人员可以通过分析结果来优化查询语句。
• 只返回必要的列：最好不要使用 SELECT * 语句。
• 只返回必要的行：使用 LIMIT 语句来限制返回的数据。
• 给经常要被查询的数据设置缓存

重构查询

• 切分大查询：一个大查询如果一次性执行的话，可能一次锁住很多数据、占满整个事务日志、耗尽系统资源、阻塞很多小的但重要的查询。
• 分解大连接查询：将一个大连接查询分解成对每一个表进行一次单表查询，然后在应用程序中进行关联，这样做的好处有：
• 让缓存更高效：对于连接查询，如果其中一个表发生变化，那么整个查询缓存就无法使用。而分解后的多个查询，即使其中一个表发生变化，对其它表的查询缓存依然可以使用。
• 分解成多个单表查询，这些单表查询的缓存结果更可能被其它查询使用到，从而减少冗余记录的查询。
• 减少锁竞争。
• 在应用层进行连接，可以更容易对数据库进行拆分，从而更容易做到高性能和可伸缩。