索引

索引概述

索引的本质：索引是数据结构。你可以简单理解为“排好序的快速查找数据结构”，满足特定查找算法。 这些数据结构以某种方式指向数据， 这样就可以在这些数据结构的基础上实现高级查找算法 。

  • 优点
    • （1）类似大学图书馆建书目索引，提高数据检索的效率，降低数据库的IO成本 ，这也是创建索引最主要的原因。
    • （2）通过创建唯一索引，可以保证数据库表中每一行 数据的唯一性 。
    • （3）在实现数据的 参考完整性方面，可以加速表和表之间的连接 。换句话说，对于有依赖关系的子表和父表联合查询时， 可以提高查询速度。
    • （4）在使用分组和排序子句进行数据查询时，可以显著 减少查询中分组和排序的时 间 ，降低了CPU的消耗。
  • 缺点
    • （1）创建索引和维护索引要耗费时间 ，并且随着数据量的增加，所耗费的时间也会增加。
    • （2）索引需要占磁盘空间 ，除了数据表占数据空间之 外，每一个索引还要占一定的物理空间， 存储在磁盘上 ，如果有大量的索引，索引文件就可能比数据文 件更快达到最大文件尺寸。
    • （3）虽然索引大大提高了查询速度，同时却会降低更新表的速度 。当对表 中的数据进行增加、删除和修改的时候，索引也要动态地维护，这样就降低了数据的维护速度。
    • 因此，选择使用索引时，需要综合考虑索引的优点和缺点。

索引的分类

MySQL的索引包括普通索引、唯一性索引、全文索引、单列索引、多列索引和空间索引等。

  • 从功能逻辑上说，索引主要有 4 种，分别是普通索引、唯一索引、主键索引、全文索引。
  • 按照 物理实现方式 ，索引可以分为 2 种：聚簇索引和二级索引。
    • 聚簇索引（Clustered Index）即索引结构和数据一起存放的索引（有且只有一个）
    • (B+树)的每个非叶子节点存储索引，叶子节点存储索引，下边挂着行数据。
    • 如果存在主键，主键索引就是聚集索引。
      如果不存在主键，将使用第一 个唯一(UNIQUE)索引作为聚集索引。
      如果表没有主键，或没有合适的唯一索引，则InnoDB会自动生成-个rowid作为隐藏的聚集索引。
  • 按照 作用字段个数 进行划分，分成单列索引和联合索引。

1.普通索引

  • 这是最基本的索引，它没有任何限制。

2.唯一索引

  • 与普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须一。

3.主键索引

  • 它是一种特殊的唯一索引，不允许有空值。一般是在建表的时候指定了主键，就会创建主键索引

4.全文索引

  • 是MySQL专门提供用作搜索引擎的，查询数据量较大的字符串时可以提高效率

5.单列索引

  • 在单个字段上创建的索引

6.多列(组合，联合)索引

  • 在多个字段组合上建立的索引

创建索引

1. 创建表的时候创建索引

  • 隐式创建，规定约束时会自动创建 主键，唯一，外键
1
2
3
4
5
6
7
8
9
10
CREATE TABLE dept(
dept_id INT PRIMARY KEY AUTO_INCREMENT,
dept_name VARCHAR(20)
);
CREATE TABLE emp(
emp_id INT PRIMARY KEY AUTO_INCREMENT,
emp_name VARCHAR(20) UNIQUE,
dept_id INT,
CONSTRAINT emp_dept_id_fk FOREIGN KEY(dept_id) REFERENCES dept(dept_id)
);

但是，如果显式创建表时创建索引的话，基本语法格式如下：

1
2
CREATE TABLE table_name [col_name data_type]
[UNIQUE | FULLTEXT | SPATIAL] [INDEX | KEY] [index_name] (col_name [length]) [ASC|DESC]
  • UNIQUE 、 FULLTEXT 和 SPATIAL 为可选参数，分别表示唯一索引、全文索引和空间索引；
  • INDEX 与 KEY 为同义词，两者的作用相同，用来指定创建索引；
  • index_name 指定索引的名称，为可选参数，如果不指定，那么MySQL默认col_name为索引名；
  • col_name 为需要创建索引的字段列，该列必须从数据表中定义的多个列中选择；
  • length 为可选参数，表示索引的长度，只有字符串类型的字段才能指定索引长度；
  • ASC 或 DESC 指定升序或者降序的索引值存储。

2.创建表之后创建

  • 使用ALTER TABLE语句创建索引
1
2
ALTER TABLE table_name ADD [UNIQUE | FULLTEXT | SPATIAL] [INDEX | KEY]
[index_name] (col_name[length],...) [ASC | DESC]
  • 使用CREATE INDEX创建索引
1
2
CREATE [UNIQUE | FULLTEXT | SPATIAL] INDEX index_name
ON table_name (col_name[length],...) [ASC | DESC]

3.删除索引

  • 使用ALTER TABLE删除索引
1
ALTER TABLE table_name DROP INDEX index_name;
  • 使用DROP INDEX语句删除索引
1
DROP INDEX index_name ON table_name;

提示

添加AUTO_INCREMENT约束字段的唯一索引是无法删除的

删除表中的列时，如果要删除的列为索引的组成部分，则该列也会从索引中删除。如果组成 索引的所有列都被删除，则整个索引将被删除。

MySQL8.0索引新特性

支持降序索引

  • 降序索引以降序存储键值。虽然在语法上，从MysQL 4版本开始就已经支持降序索引的语法了，但实际上该DESC定义是被忽略的，直到MysQL 8.x版本才开始真正支持降序索引(仅限于InnoDB存储引擎)。
  • MysQL在8.o版本之前创建的仍然是升序索引，使用时进行反向扫描，这大大降低了数据库的效率在某些场景下，降序索引意义重大。
  • 例如，如果一个查询，需要对多个列进行排序，且顺序要求不一致，那么使用降序索引将会避免数据库使用额外的文件排序操作，从而提高性能。

支持隐藏索引

  • 在MySQL 5.7版本及之前，只能通过显式的方式删除索引。此时，如果发现删除索引后出现错误，又只能 通过显式创建索引的方式将删除的索引创建回来。如果数据表中的数据量非常大，或者数据表本身比较大，这种操作就会消耗系统过多的资源，操作成本非常高。
  • 从MySQL 8.x开始支持 隐藏索引（invisible indexes） ，只需要将待删除的索引设置为隐藏索引，使查询优化器不再使用这个索引（即使使用force index（强制使用索引），优化器也不会使用该索引）， 确认将索引设置为隐藏索引后系统不受任何响应，就可以彻底删除索引。
  • 这种通过先将索引设置为隐藏索引，再删除索引的方式就是软删除 。

索引的设计原则

适合创建索引的11种情况

1.字段的数值有唯一性的限制

业务上具有唯一特性的字段，即使是组合字段，也必须建成唯一索引。（来源：Alibaba）

说明：不要以为唯一索引影响了 insert 速度，这个速度损耗可以忽略，但提高查找速度是明显的。

2.频繁作为 WHERE 查询条件的字段

  • 某个字段在SELECT语句的 WHERE 条件中经常被使用到，那么就需要给这个字段创建索引了。
  • 尤其是在数据量大的情况下，创建普通索引就可以大幅提升数据查询的效率。 比如student_info数据表（含100万条数据），假设我们想要查询 student_id=123110 的用户信息。

3.经常 GROUP BY 和 ORDER BY 的列

  • 索引就是让数据按照某种顺序进行存储或检索，因此当我们使用 GROUP BY 对数据进行分组查询，或者使用 ORDER BY 对数据进行排序的时候，就需要对分组或者排序的字段进行索引 。
  • 如果待排序的列有多 个，那么可以在这些列上建立组合索引 。

4.UPDATE、DELETE 的 WHERE 条件列

  • 对数据按照某个条件进行查询后再进行 UPDATE 或 DELETE 的操作，如果对 WHERE 字段创建了索引，就能大幅提升效率。
  • 原理是因为我们需要先根据 WHERE 条件列检索出来这条记录，然后再对它进行更新或 删除。
  • 如果进行更新的时候，更新的字段是非索引字段，提升的效率会更明显，这是因为非索引字段更新不需要对索引进行维护。

5.DISTINCT 字段需要创建索引

  • 有时候我们需要对某个字段进行去重，使用 DISTINCT，那么对这个字段创建索引，也会提升查询效率。

6.多表 JOIN 连接操作时，创建索引注意事项

  • 首先， 连接表的数量尽量不要超过 3 张 ，因为每增加一张表就相当于增加了一次嵌套的循环，数量级增长会非常快，严重影响查询的效率。
  • 其次， 对 WHERE 条件创建索引 ，因为 WHERE 才是对数据条件的过滤。如果在数据量非常大的情况下， 没有 WHERE 条件过滤是非常可怕的。
  • 最后， 对用于连接的字段创建索引 ，并且该字段在多张表中的类型必须一致 。比如 course_id 在 student_info 表和 course 表中都为 int(11) 类型，而不能一个为 int 另一个为 varchar 类型(索引会失效)。

7.使用列的类型小的创建索引

  • 数据类型越小，在查询时进行的比较操作越快
  • 数据类型越小，索引占用的存储空间就越少，在一个数据页内就可以放下更多的记录，从而减少磁盘I/0带来的性能损耗，也就意味着可以把更多的数据页缓存在内存中，从而加快读写效率。

8.使用字符串前缀创建索引

假设我们的字符串很长，那存储一个字符串就需要占用很大的存储空间。在我们需要为这个字符串列建立索引时，那就意味着在对应的B+树中有这么两个问题:

  • B+树索引中的记录需要把该列的完整字符串存储起来，更费时。而且字符串越长，在索引中占用的存储空间越大。
  • 如果B+树索引中索引列存储的字符串很长，那在做字符串比较时会占用更多的时间。
  • 我们可以通过截取字段的前面一部分内容建立索引，这个就叫前缀索引。这样在查找记录时虽然不能精确的定位到记录的位置，但是能定位到相应前缀所在的位置，然后根据前缀相同的记录的主键值回表查询完整的字符串值。既节约空间，又减少了字符串的比较时间，还大体能解决排序的问题。

9.区分度高(散列性高)的列适合作为索引

10使用最频繁的列放到联合索引的左侧

这样也可以较少的建立一些索引。同时，由于”最左前缀原则”，可以增加联合索引的使用率。

11.在多个字段都要创建索引的情况下，联合索引优于单值索引

在实际工作中，我们也需要注意平衡，索引的数目不是越多越好。我们需要限制每张表上的索引数量，建议单张表索引数量不超过6个。原因:

  • 每个索引都需要占用磁盘空间，索引越多，需要的磁盘空间就越大。
  • 索引会影响INSERT、DELETE、UPDATE等语句的性能，因为表中的数据更改的同时，索引也会进行调整和更新，会造成负担。
  • 优化器在选择如何优化查询时，会根据统一信息，对每一个可以用到的索引来进行评估，以生成出一个最好的执行计划，如果同时有很多个索引都可以用于查询，会增加MySQL优化器生成执行计划时间，降低查询性能。

哪些情况不适合创建索引

  • 在where中使用不到的字段，不要设置索引

  • 数据量小的表最好不要使用索引

结论：在数据表中的数据行数比较少的情况下，比如不到 1000 行，是不需要创建索引的。

  • 有大量重复数据的列上不要建立索引

举例：要在 100 万行数据中查找其中的 50 万行（比如性别为男的数据），一旦创建了索引，你需要先 访问 50 万次索引，然后再访问 50 万次数据表，这样加起来的开销比不使用索引可能还要大。

  • 避免对经常更新的表创建过多的索引
  • 不建议用无序的值作为索引

例如身份证、UUID(在索引比较时需要转为ASCII，并且插入时可能造成页分裂)、MD5、HASH、无序长字 符串等。

  • 删除不再使用或者很少使用的索引

  • 不要定义冗余或重复的索引

索引使用注意事项

全值匹配我最爱

  • 建立几个复合索引字段，最好就用上几个字段。且按照顺序来用。

最佳左前缀法则

  • 建立联合索引时，必须按照严格的顺序。
  • 要使用联合索引的话，必须先从左边考虑(必须包含左边的字段，才可以使用后面的字段)。一旦跳过某个字段，则后面的字段都没办法使用，但是前面的字段可以使用索引

主键插入顺序

  • 对于一个使用InnoDB存储引擎的表来说，在我们没有显式的创建索引时，表中的数据实际上都是存储在聚簇索引的叶子节点的。而记录又是存储在数据页中的，数据页和记录又是按照记录主键值从小到大的顺序进行排序，所以如果我们插入的记录的主键值是依次增大的话，那我们每插满一个数据页就换到下一个数据页继续插，而如果我们插入的主键值忽大忽小的话，就比较麻烦了会造成页分裂，第一页的一些记录会移动到第二页

  • 页面分裂和记录移位意味着什么?意味着:性能损耗!

  • 所以如果我们想尽量避免这样无谓的性能损耗，最好让插入的记录的主键值依次递增，这样就不会发生这样的性能损耗了。所以我们建议:让主键具有AUTO_INCREMENT，让存储引擎自己为表生成主键，而不是我们手动插入

索引失效的情况

计算，函数，类型转化(自动或者手动)导致造成索引失效

范围条件右边的列索引失效

  • 所以，建立联合字段索引时，需要把范围索引字段放在最后面，不然范围索引后面的字段则使用不到 (当然，你只更换查询语句里的顺序，肯定是不能改变效果的,优化器都会帮你修改，而索引是不会改变的)

<>和!=不等于索引失效

  • 因为在B+树中查找不等于时，只能一个个去查找，导致索引失效

is null可以使用索引，is not null不能使用索引

  • 因为is null相当于是等值，is not null相当于是不等于

  • 最好在设计数据表的时候就将字段设置为 NOT NULL约束，比如你可以将INT类型的字段，默认值设置为0。将字符类型的默认值设置为空字符串()。
    拓展:同理，在查询中使用not like 也无法使用索引，导致全表扫描。

like以通配符%开头索引失效

OR前后存在非索引的列，索引失效

  • 因为or的话，相当于你两边都要；如果你走索引的话，相当于要先走一遍索引字段，然后再全表走没有索引的字段，这样还不如全表来的更快。

数据库和表的字符集同一使用utf8mb4

  • 不同字符集会导致转换产生乱码，不同的字符集进行比较前会进行转换，造成索引失效

外连接内连接的优化

外连接

  • 如果只能使用一个索引，对被驱动表使用索引，因为连接查询实际上是遍历两个表进行查询，被驱动表通过驱动表查询的条件进行查询，此时被驱动表就可以用上索引

内连接(小表驱动大表)

  • 对于内连接来说，优化器可以选择驱动表和被驱动表
    • 只有一个索引的情况下，优先将有索引的表作为被驱动表
    • 当有两个 索引的条件下，优化器会做出抉择，数据量小的作为驱动表（因为此表要进行全遍历）

小结

  • 保证被驱动表的JOIN字段已经创建了索引
  • 需要JOIN 的字段，数据类型保持绝对一致。
  • LEFT JOIN 时，选择小表作为驱动表， 大表作为被驱动表 。减少外层循环的次数。
  • INNER JOIN 时，MySQL会自动将 小结果集的表选为驱动表 。选择相信MySQL优化策略。
  • 能够直接多表关联的尽量直接关联，不用子查询。(减少查询的趟数)
  • 不建议使用子查询，建议将子查询SQL拆开结合程序多次查询，或使用 JOIN 来代替子查询。
  • 衍生表建不了索引

子查询优化

  • MySQL从4.1版本开始支持子查询，使用子查询可以进行SELECT语句的嵌套查询，即一个SELECT查询的结果作为另一个SELECT语句的条件。 子查询可以一次性完成很多逻辑上需要多个步骤才能完成的SQL操作 。 子查询是 MySQL 的一项重要的功能，可以帮助我们通过一个 SQL 语句实现比较复杂的查询。
  • 但是，子查询的执行效率不高。原因：
    • ① 执行子查询时，MySQL需要为内层查询语句的查询结果 建立一个临时表 ，然后外层查询语句从临时表 中查询记录。查询完毕后，再撤销这些临时表 。这样会消耗过多的CPU和IO资源，产生大量的慢查询。
    • ② 子查询的结果集存储的临时表，不论是内存临时表还是磁盘临时表都不会存在索引 ，所以查询性能会受到一定的影响。
    • ③ 对于返回结果集比较大的子查询，其对查询性能的影响也就越大
  • 在MySQL中，可以使用连接（JOIN）查询来替代子查询。连接查询不需要建立临时表 ，其速度比子查询要快 ，如果查询中使用索引的话，性能就会更好。

结论：尽量不要使用NOT IN 或者 NOT EXISTS，用LEFT JOIN xxx ON xx WHERE xx IS NULL替代

Order by排序优化

在MySQL 中，支持两种排序方式，分别是FileSort和Index排序。

  • Index排序中，索引可以保证数据的有序性，不需要再进行排序，效率更高。
  • FileSort排序则一般在内存中进行排序，占用CPU较多。如果待排结果较大，会产生临时文件I/o到磁盘进行排序的情况，效率较低。

优化建议：

  • SQL 中，可以在 WHERE 子句和 ORDER BY 子句中使用索引，
    • 目的是在 WHERE 子句中避免全表扫描 。
    • 在 ORDER BY 子句 避免使用 FileSort 排序 。
    • 当然，某些情况下全表扫描，或者 FileSort 排 序不一定比索引慢。但总的来说，我们还是要避免，以提高查询效率。
  • 尽量使用 Index 完成 ORDER BY 排序。如果 WHERE 和 ORDER BY 后面是相同的列就使用单索引列； 如果不同就使用联合索引。
  • 无法使用 Index 时，需要对 FileSort 方式进行调优。

Group by优化

  • group by 使用索引的原则几乎跟order by一致 ，group by 即使没有过滤条件用到索引，也可以直接使用索引。
  • group by 先排序再分组，遵照索引建的最佳左前缀法则
  • 当无法使用索引列，增大 max_length_for_sort_data 和 sort_buffer_size 参数的设置
  • where效率高于having，能写在where限定的条件就不要写在having中了
  • 减少使用order by，和业务沟通能不排序就不排序，或将排序放到程序端去做。Order by、group by、distinct这些语句较为耗费CPU，数据库的CPU资源是极其宝贵的。
  • 包含了order by、group by、distinct这些查询的语句，where条件过滤出来的结果集请保持在1000行 以内，否则SQL会很慢。

Limit 优化分页查询

优化思路一： 在索引上完成排序分页操作，最后根据主键关联回原表查询所需要的其他列内容。

1
2
3
EXPLAIN SELECT * FROM student t,(SELECT id FROM student ORDER BY id LIMIT 2000000,10)
a
WHERE t.id = a.id;

优化思路二： 该方案适用于主键自增的表，可以把Limit 查询转换成某个位置的查询 。

1
EXPLAIN SELECT * FROM student WHERE id > 2000000 LIMIT 10;

Count 优化

Count（*）最优

覆盖索引

  • 一个索引包含了满足查询结果的数据就叫做覆盖索引(不需要回表操作)。

  • 简单说就是， 索引列+主键 包含 SELECT 到 FROM之间查询的列 。

覆盖索引的利弊

  • 好处：避免Innodb表进行索引的二次查询（回表） 2. 可以把随机IO变成顺序IO加快查询效率
  • 弊端： 索引字段的维护总是有代价的。因此，在建立冗余索引来支持覆盖索引时就需要权衡考虑了。这是业务DBA，或者称为业务数据架构师的工作。

淘宝数据库，主键如何设计的？

自增ID的问题

自增ID做主键，简单易懂，几乎所有数据库都支持自增类型，只是实现上各自有所不同而已。自增ID除 了简单，其他都是缺点，总体来看存在以下几方面的问题：

可靠性不高

存在自增ID回溯的问题，这个问题直到最新版本的MySQL 8.0才修复。

安全性不高

对外暴露的接口可以非常容易猜测对应的信息。比如：/User/1/这样的接口，可以非常容易猜测用户ID的 值为多少，总用户数量有多少，也可以非常容易地通过接口进行数据的爬取。

性能差

自增ID的性能较差，需要在数据库服务器端生成。

交互多

业务还需要额外执行一次类似 last_insert_id() 的函数才能知道刚才插入的自增值，这需要多一次的 网络交互。在海量并发的系统中，多1条SQL，就多一次性能上的开销。

局部唯一性

最重要的一点，自增ID是局部唯一，只在当前数据库实例中唯一，而不是全局唯一，在任意服务器间都 是唯一的。对于目前分布式系统来说，这简直就是噩梦。

不要选择业务字段做主键

所以，建议尽量不要用跟业务有关的字段做主键。毕竟，作为项目设计的技术人员，我们谁也无法预测 在项目的整个生命周期中，哪个业务字段会因为项目的业务需求而有重复，或者重用之类的情况出现。

经验： 刚开始使用 MySQL 时，很多人都很容易犯的错误是喜欢用业务字段做主键，想当然地认为了解业 务需求，但实际情况往往出乎意料，而更改主键设置的成本非常高。

推荐的主键设计

非核心业务 ：对应表的主键自增ID，如告警、日志、监控等信息。

核心业务 ：主键设计至少应该是全局唯一且是单调递增。全局唯一保证在各系统之间都是唯一的，单调 递增是希望插入时不影响数据库性能。 这里推荐最简单的一种主键设计：UUID。

  • UUID的特点： 全局唯一，占用36字节，数据无序，插入性能差。
  • MYSQL中UUID组成为UUID = 时间+UUID版本（16字节）- 时钟序列（4字节） - MAC地址（12字节）

为什么UUID是全局唯一的？

  • 在UUID中时间部分占用60位，存储的类似TIMESTAMP的时间戳，但表示的是从1582-10-15 00：00：00.00 到现在的100ns的计数。可以看到UUID存储的时间精度比TIMESTAMPE更高，时间维度发生重复的概率降 低到1/100ns。 时钟序列是为了避免时钟被回拨导致产生时间重复的可能性。MAC地址用于全局唯一。

为什么UUID占用36个字节？

  • UUID根据字符串进行存储，设计时还带有无用”-“字符串，因此总共需要36个字节。

为什么UUID是随机无序的呢？

  • 因为UUID的设计中，将时间低位放在最前面，而这部分的数据是一直在变化的，并且是无序。

改善UUID

  • 若将时间高低位互换，则时间就是单调递增的了，也就变得单调递增了。
  • MySQL 8.0可以更换时间低位和 时间高位的存储方式，这样UUID就是有序的UUID了。
  • MySQL 8.0还解决了UUID存在的空间占用的问题，除去了UUID字符串中无意义的”-“字符串，并且将字符串用二进制类型保存，这样存储空间降低为了16字节 。

数据库设计规范

范式

在关系型数据库中，关于数据表设计的基本原则、规则就称为范式。可以理解为，一张数据表的设计结构需要满足的某种设计标准的级别 要想设计一个结构合理的关系型数据库，必须满足一定的范式。

第一范式

  • 1NF（第一范式）是指数据库表的每一列都是不可分割的基本数据项，同一列中不能有多个值，即实体中的某个属性不能有多个值或者不能有重复的属性。
  • 在任何一个关系数据库中，第一范式（1NF）是对关系模式的基本要求，不满足第一范式（1NF）的数据库就不是关系数据库。

第二范式

  • 第二范式要求，在满足第一范式的基础上，还要满足数据表里的每一条数据记录，都是可唯一标识的。
  • 而且所有非主键字段，都必须完全依赖主键，不能只依赖主键的一部分。
  • 如果知道主键的所有属性的值，就可以检索到任何元组(行)的任何属性的任何值。(要求中的主键，其实可以拓展替换为候选键)。|

对于非主属性来说，并非完全依赖候选键。这样会产生怎样的问题呢？

假设有球员表：(球员编号, 比赛编号) → (姓名, 年龄, 比赛时间, 比赛场地，得分)

  • 数据冗余 ：如果一个球员可以参加 m 场比赛，那么球员的姓名和年龄就重复了 m-1 次。一个比赛 也可能会有 n 个球员参加，比赛的时间和地点就重复了 n-1 次。
  • 插入异常 ：如果我们想要添加一场新的比赛，但是这时还没有确定参加的球员都有谁，那么就没 法插入。
  • 删除异常 ：如果我要删除某个球员编号，如果没有单独保存比赛表的话，就会同时把比赛信息删 除掉。
  • 更新异常 ：如果我们调整了某个比赛的时间，那么数据表中所有这个比赛的时间都需要进行调 整，否则就会出现一场比赛时间不同的情况。

为了避免出现上述的情况，我们可以把球员比赛表设计为下面的三张表。

表名 属性
球员表 球员编号，姓名，年龄
比赛表 比赛编号，比赛时间，比赛场地
球员比赛关系表 球员编号，比赛编号，得分等属性

这样的话，每张数据表都符合第二范式，也就避免了异常情况的发生。

1NF 告诉我们字段属性需要是原子性的，

2NF 告诉我们一张表就是一个独立的对象，一张表只表达一个意思。

小结:第二范式(2NF)要求实体的属性完全依赖主关键字。如果存在不完全依赖，那么这个属性和主关键字的这一部分应该分离出来形成一个新的实体，新实体与元实体之间是一对多的关系。

第三范式

第三范式是在第二范式的基础上，确保数据表中的每一个非主键字段都和主键字段直接相关，也就是说，要求数据表中的所有非主键字段不能依赖于其他非主键字段。(即，不能存在非主属性A依赖于非主属性B，非主属性B依赖于主键c的情况，即存在”A一B→C””的决定关系）通俗地讲，该规则的意思是所有非主键属性之间不能有依赖关系，必须相互独立。

符合3NF后的数据模型通俗地讲，2NF和3NF通常以这句话概括：“每个非主键属性依赖于主键，依赖于整个主键，并且除了键别无他物”。

小结

关于数据表的设计，有三个范式要遵循。

(1）第一范式(1NF)，确保每列保持原子性

  • 数据库的每一列都是不可分割的原子数据项，不可再分的最小数据单元，而不能是集合、数组、记录等非原子数据项

(2)第二范式（2NF)，确保每列都和主键完全依赖

  • 尤其在复合主键的情况下，非主键部分不应该依赖于部分主键。

(3）第三范式(3NF）确保每列都和主键列直接相关，而不是间接相关

**范式的优点:**数据的标准化有助于消除数据库中的数据冗余，第三范式(3NF）通常被认为在性能、扩展性和数据完整性方面达到了最好的平衡。

**范式的缺点:**范式的使用，可能降低查询的效率。因为范式等级越高，设计出来的数据表就越多、越精细，数据的冗余度就越低，进行据查询的时候就可能需要关联多张表，这不但代价昂贵，也可能使一些索引策略无效。范式只是提出了设计的标准，实际上设计数据时，未必一定要符合这些标准。

开发中，我们会出现为了性能和读取效率违反范式化的原则，通过增加少量的冗余或重复的数据来提高据库的读性能，减少关联查询，join表的次数，实现空间换取时间的目的。因此在实际的设计过程中要理论结合实际，灵活运用。

反范式

  • 有的时候不能简单按照规范要求设计数据表，因为有的数据看似冗余，其实对业务来说十分重要。这个时候，我们就要遵循业务优先的原则，首先满足业务需求，再尽量减少冗余。
  • 如果数据库中的数据量比较大，系统的UV和PV访问频次比较高，则完全按照MysQL的三大范式设计数据表，读数据时会产生大量的关联查询，在一定程度上会影响数据库的读性能。如果我们想对查询效率进行优化，反范式优化也是一种优化思路。此时，可以通过在数据表中增加冗余字段来提高数据库的读性能。
  1. 为满足某种商业目标 , 数据库性能比规范化数据库更重要
  2. 在数据规范化的同时 , 要综合考虑数据库的性能
  3. 通过在给定的表中添加额外的字段，以大量减少需要从中搜索信息所需的时间
  4. 通过在给定的表中插入计算列，以方便查询

锁🔒

  • 全局锁，锁整个数据库，基本是在做数据备份时使用
1
2
3
4
5
-- 全局锁，整个数据库处于只读状态，其他操作均阻塞
FLUSH TABLES WITH READ LOCK

-- 释放全局锁
UNLOCK TABLES

  • 表级锁，锁具体一张表，表级锁又分为表锁、元数据锁、意向锁、自增锁。

    • 表锁（Table-level lock）用于锁定整个表，控制对表的并发访问。当一个事务获取到表级锁时，其他事务将被阻塞，无法同时对该表进行写操作或修改操作。

      • 表锁锁分为读共享锁和写独占锁，读共享对修改阻塞，对查询开放，写锁既阻塞修改也阻塞查询

    • 元数据锁（Meta Data Lock）用于保护数据库对象的元数据（如表结构、索引信息等）。当一个事务获取到元数据锁后，其他事务将无法修改该元数据，直到锁被释放。元数据锁将阻止并发事务对元数据的并行修改，防止出现不一致或损坏的元数据状态。

    • 意向锁为了避免DML在执行时，加的行锁与表锁的冲突，在InnoDB中引入了意向锁，使得表锁不用检查每行数据是否加锁，使用意向锁来减少表锁的检查。

  • 行级锁

    • 行级锁的类型有 - 记录锁，也就是仅仅把一条（行）记录锁上； - 间隙锁，锁定一个范围，但是不包含记录本身；

innodb引擎

  • 逻辑架构

    • 表，ibd文件，存储记录和索引
    • 段 分三类 数据段（b+树的叶子节点） 索引段(非叶子节点)，回滚段
    • 区 大小1m 含64个页
    • 页大小16kb 里存记录 页是最小的管理单元
    • 行 记录

  • 内存架构

    • Buffer Pool

      缓冲池是 InnoDB 缓存表和索引数据的内存区域。缓冲池允许直接从内存访问频繁使用的数据，从而加快处理速度。

    • Chang Buffer

      Chang Buffer 是一种特殊的数据结构，当这些页面不在 Buffer Pool 时，它会缓存对页面的更改。缓冲的更改可能由 Insert，Delete 或 Update 操作 (DML) 引起 ，稍后当其他读取操作将页面加载到缓冲池中时，这些更改将被合并。

    • Adaptive Hash Index

      自适应哈希索引是针对经常访问的索引页面按需构建的。

    • Log Buffer

    Log Buffer 是存储要写入磁盘上日志文件的数据的内存区域。