1 数据库设计的三大范式（原子，主键，冗余）

范式是具有最小冗余的表结构。

1.1 第一范式（1NF）：列都是不可再分割的

第一范式的目标是确保每列的原子性：如果每一列都是不可再分的最小数据单元，也称为最小的原子
单元，则满足第一范式。
image.png
具体是指在关系模型中，对于添加的一个规范要求，所有的域都应该是原子性的，即数据库表的每一列都是不可分割的原子数据项，而不能是集合，数组，记录等非原子数据项。

也就是如果实体中的某个属性有多个值时，必须拆分为不同的属性。在符合第一范式表中的每个域值只能是实体的一个属性或一个属性的一部分。简而言之，第一范式就是无重复的域。

1.2 第二范式（2NF）：每个表只描述一件事情

满足第一范式，并且表中非主键列不存在对主键的部分依赖。第二范式要求每个表只描述一件事情。
image.png
在1NF的基础上，非码属性必须完全依赖于候选码（在1NF基础上消除非主属性对主码的部分函数依赖）。
第二范式是在第一范式的基础上建立起来的，即满足第二范式必须先满足第一范式。第二范式要求数据库表中的每个实例或记录必须可以被唯一地区分。选取一个能区分每个实体的属性或属性组，作为实体的唯一标识。（主键）

例如在员工表中的身份证号码即可实现每个一员工的区分，该身份证号码即为候选键，任何一个候选键都可以被选作主键。在找不到候选键时，可额外增加属性以实现区分，如果在员工关系中，没有对其身份证号进行存储，而姓名可能会在数据库运行的某个时间重复，无法区分出实体时，设计辟如ID等不重复的编号以实现区分，被添加的编号或ID选作主键。

1.3 第三范式（3NF）：不存在对非主键列的传递依赖

满足第二范式，并且表中的列不存在对非主键列的传递依赖。比如左图中，除了主键订单编号外，顾客姓名依赖于非主键顾客编号，因此不满足第三范式。
image.png
在2NF基础上，任何非主属性不依赖于其它非主属性（在2NF基础上消除传递依赖）。
第三范式是第二范式的一个子集，即满足第三范式必须满足第二范式。简而言之，第三范式要求一个关系中不包含已在其它关系已包含的非主关键字信息。（双表共存，一表部门信息表（部门id，部门名，部门简介一一对应），一表为员工信息表，包含部门id后就不必包含部门名，部门简介，防止冗余）

例如，存在一个部门信息表，其中每个部门有部门编号（dept_id）、部门名称、部门简介等信息。那么在员工信息表中列出部门编号后就不能再将部门名称、部门简介等与部门有关的信息再加入员工信息表中。如果不存在部门信息表，则根据第三范式（3NF）也应该构建它，否则就会有大量的数据冗余。

2 SQL的执行过程（SQL的查询流程）

image.png

2.1 查询缓存

Server 如果在查询缓存中发现了这条SQL语句，就会直接将结果返回给客户端；如果没有，就进入到解析器阶段。但是因为查询缓存往往效率不高，所以在MySQL8.0 之后就抛弃了这个功能。（相同的查询操作才会命中，查询缓存命中率不高，缓存失效。）

2.2 解析器

在解析器中对SQL语句进行语法分析、语义分析

  1. 分析器先做词法分析。你输入的是由多个字符串和空格组成的一条SQL 语句，MySQL 需要识别出里面的字符串分别是什么，代表什么。MySQL 从你输入的 “select” 这个关键字识别出来，这是一个查询语句。它也要把字符串 “T” 识别成 “表名T” ，把字符串 “ID” 识别成 “列ID”。

  2. 接着，要做语法分析。根据词法分析的结果，语法分析器（比如：Bison）会根据语法规则，判断你输入的这个SQL 语句是否满足MySQL 语法。如果SQL语句正确，则会生成一个语法树。

    2.3 优化器

    在优化器中会确定SQL语句的执行路径，比如是根据全表检索，还是根据索引检索等。

    1. select * from test1 join test2 using(ID)
    2. where test1.name='zhangwei' and test2.name='mysql高级课程';
    3. 方案1：可以先从表 test1 里面取出 name='zhangwei'的记录的 ID 值，再根据 ID 值关联到表 test2，再判断 test2 里面 name的值是否等于 'mysql高级课程'。
    4. 方案2：可以先从表 test2 里面取出 name='mysql高级课程' 的记录的 ID 值，再根据 ID 值关联到 test1，再判断 test1 里面 name的值是否等于 zhangwei。
    5. 这两种执行方法的逻辑结果是一样的，但是执行的效率会有不同，而优化器的作用就是决定选择使用哪一个方案。优化器阶段完成后，这个语句的执行方案就确定下来了，然后进入执行器阶段。

    在查询优化器中，可以分为逻辑查询优化阶段和物理查询优化阶段。

    2.4 执行器

    截止到现在，还没有真正去读写真实的表，仅仅只是产出了一个执行计划。于是就进入了执行器阶段。
    在执行之前需要判断该用户是否具备权限。如果没有，就会返回权限错误。如果具备权限，就执行SQL查询并返回结果。在MySQL8.0 以下的版本，如果设置了查询缓存，这时会将查询结果进行缓存。

    3 存储引擎

    数据库存储引擎是数据库底层软件组织，数据库管理系统（DBMS）使用数据引擎进行创建、查询、
    更新和删除数据。不同的存储引擎提供不同的存储机制、索引技巧、锁定水平等功能，使用不同
    的存储引擎，还可以 获得特定的功能。现在许多不同的数据库管理系统都支持多种不同的数据引
    擎。存储引擎主要有： InnoDB, MyIsam, Memory 等。
    image.png
    查看MYSQL提供的存储引擎：show engines;
    image.png
    查看默认的存储引擎：show variables like '%storage_engine%';
    修改默认的存储引擎：set default_storage_engine=MyISAM;
    创建表时指定存储引擎：
    CREATE TABLE 表名( 建表语句; ) ENGINE = 存储引擎名称;
    修改表的存储引擎：ALTER TABLE 表名 ENGINE = 存储引擎名称;
    查看表的结构：SHOW CREATE TABLE 表名

    3.1 InnoDB和MyIASM

    区别

  3. 事务：支持；不支持；

  4. 锁机制：支持行级锁和表级锁；表级锁；
  • 表锁，即使操作一条记录也会锁住整个表，不适合高并发的操作
  • 行锁，操作时只锁某一行，不对其它行有影响，适合高并发的操作
  1. 外键：支持；不支持；
  2. 索引：聚簇；非聚簇；参考
  • 聚簇索引就是把数据全放在一起，存放在索引上，这样在遍历索引树的时候，只要找到对应的叶子节点就可以直接找到想要的数据
  • 非聚簇索引就是把数据分开存放，看上去数据好像是整个存放在内存或者磁盘中（虚拟地址），但这些数据可能分布在不同的地址，非聚簇索引的叶子结点存放相对应数据的地址，因为数据都分散存放，所以拿到地址后要去相对应的地方取数据。
  1. InnoDB必须有唯一索引（主键索引）；MyISAM可以没有；
  2. 缓存：InnoDB不仅缓存索引还要缓存真实数据，对内存要求较高 ，而且内存大小对性能有决定性的影响；MyISAM只缓存索引，不缓存真实数据；
  3. 空间使用：高；低
  4. 内存使用：高；低
  5. 集群索引：支持；不支持
  6. 全文索引：不支持；支持

相同

  1. 都支持B+树索引

  2. 都不支持哈希索引，但是InnoDB支持自适应哈希索引

    3.1 InnoDB：MySQL的默认存储引擎

    适用场景：

  3. 支持事务：用来处理大量的短期(short-lived)事务。可以确保事务的完整提交(Commit)和回滚(Rollback)。

  4. 支持行级锁：经常更新的表，适合处理多重并发的更新请求。除了增加和查询外，还需要更新、删除操作，那么，应优先选择InnoDB存储引擎。
  5. InnoDB 底层存储结构为B+树， B树的每个节点对应innodb的一个page，page大小是固定的， 一般设为 16k。其中非叶子节点只有键值，叶子节点包含完成数据。
  6. 可以从灾难中恢复（通过 bin-log 日志等）。
  7. 外键约束。只有InnoDB 支持外键。
  8. 支持自动增加列属性 auto_increment。

  9. 除非有非常特别的原因需要使用其他的存储引擎，否则应该优先考虑InnoDB引擎。

    3.2 MyIASM

  10. 不支持事务；

  11. 不支持行级锁，因此写操作，比如 INSERT(插入)或UPDATE(更新)数据时，需要锁定整个表，效率便会低一些；
  12. 读操作的速度快，而且不占用大量的内存和存储资源。在设计之初就预想数据组织成有固定长度的记录，按顺序存储的。是一种静态索引结构。
  13. 不支持外键 ；
  14. 崩溃后无法安全恢复；

4 索引

4.1 索引的作用和优缺点

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

索引是在存储引擎中实现的，不同存储引擎支持的索引不一定相同。索引是存储引擎用于快速找到数据记录的一种数据结构。进行数据查找时，首先查看查询条件是否命中某条索引，符合则通过索引查找相关数据，如果不符合则需要全表扫描，即需要一条一条地查找记录，直到找到与条件符合的记录。

优点：

  1. 提高数据的查询效率，降低数据库的IO成本，减少磁盘I/O的次数。
  2. 通过创建唯一索引，可以保证数据库表中每一行数据的唯一性。
  3. 在实现数据的参考完整性方面，可以加速表和表之间的连接。换句话说，对于有依赖关系的子表和父表联合查询时，可以提高查询速度。
  4. 在使用分组和排序子句进行数据查询时，可以显著减少查询中分组和排序的时间，降低了CPU的消耗。

缺点：

  1. 创建索引和维护索引要耗费时间，并且随着数据量的增加，所耗费的时间也会增加。
  2. 索引需要占磁盘空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，存储在磁盘上，如果有大量的索引，索引文件就可能比数据文件更快达到最大文件尺寸。
  3. 虽然索引大大提高了查询速度，同时却会降低更新表的速度。当对表中的数据进行增加、删除和修改的时候，索引也要动态地维护，这样就降低了数据的维护速度。

    4.2 B+树

    常见的查询算法：顺序查找、二分查找、二叉排序树查找、哈希散列法、平衡多路搜索树 B 树、B+树等

    1. 表的行格式

    image.pngimage.png
  • record_type ：记录头信息的一项属性，表示记录的类型，0 表示普通记录、1表示目录项记录、2 表示最小记录、3 表示最大记录。
  • next_record ：记录头信息的一项属性，表示下一条地址相对于本条记录的地址偏移量，我们用箭头来表明下一条记录是谁。
  • 各个列的值 ：表中的三个列，分别是 c1、c2 和 c3，其中 c1是主键。

    2. B+Tree的引入

    数据页：按照主键的递增顺序存放记录，且下一个数据页中记录的主键值必须大于上一个页中记录的主键值。

页分裂：在对页中的记录进行增删改操作的过程中，必须通过一些诸如记录移动的操作来始终保证：下一个数据页中用户记录的主键值必须大于上一个页中用户记录的主键值，这个过程称为页分裂。

目录项：给每个数据页建立对应的一个目录项，即目录项记录。
image.png
目录项纪录的目录页：把多个目录项按照主键的递增顺序，放在一个数据页中。
image.png
目录项记录和普通的用户记录的不同点：

  1. 目录项记录的 record_type 值是1，而普通用户记录的 record_type 值是0。
  2. 目录项记录只有主键值和页的编号两个列，而普通的用户记录的列是用户自己定义的，可能包含很多列 ，另外还有InnoDB自己添加的隐藏列。

目录项记录和普通的用户记录的相同点：两者用的都是数据页，都会为主键值生成 Page Directory 页目录，从而在按照主键值进行查找时可以使用二分法来加快查询速度。

总结：

  1. 数据页之内以及数据页之间的记录或目录项，都是按主键递增的。
  2. 页分裂：数据页之间必须保证主键递增，在insert或delete时导致记录在页之间移动的现象叫做页分裂。
  3. 数据页的编号并不是连续的，通过双向链表的方式连接。
  4. 目录项的结构：目录项对应的数据页中最小主键+目录项对应的数据页的页码

    3. B+ Tree树

image.png
根节点、内节点、叶子结点：
不论是存放用户记录的数据页，还是存放目录项记录的数据页，都把它们存放到B+树这个数据结构中了，所以也称这些数据页为节点。从图中可以看出，实际用户记录都存放在B+树的最底层的节点上，这些节点也被称为叶子节点，其余用来存放目录项的节点称为非叶子节点或内节点，其中B+树最上边的那个节点也称为根节点。

B+树的层数和存放记录数之间的关系：
一个B+树的节点其实可以分成好多层，规定最下边的那层，也就是存放用户记录的那层为第 0 层，之后依次往上加。假设所有存放用户记录的叶子节点代表的数据页最多可以存放100条用户记录，所有存放目录项记录的内节点代表的数据页最多可以存放1000条目录项记录 ，那么：

  • 如果B+树只有1层，也就是只有1个用于存放用户记录的节点，最多能存放 100 条记录。
  • 如果B+树有2层，最多能存放 1000×100=10,0000 条记录。
  • 如果B+树有3层，最多能存放 1000×1000×100=1,0000,0000 条记录。
  • 如果B+树有4层，最多能存放 1000×1000×1000×100=1000,0000,0000 条记录。W

一般情况下，我们用到的B+树都不会超过4层 ，那我们通过主键值去查找某条记录最多只需要做4个页面内的查找（查找3个目录项页和一个用户记录页），又因为在每个页面内有所谓的 Page Directory 页目录，所以在页面内也可以通过二分法实现快速定位记录。

  • B+树能够存储的记录数=数据页可存记录数 * 目录页可存记录数^(层数-1)WW

  • 树的层次越低，IO次数越少

    4. B+Tree树的注意事项

    根页面位置万年不动：
    实际上B+树的形成过程是这样的：

  • 每当为某个表创建一个B+树索引（聚簇索引不是人为创建的，默认就有）的时候，都会为这个索引创建一个根节点页面。最开始表中没有数据的时候，每个B+树索引对应的根节点中既没有用户记录，也没有目录项记录。

  • 随后向表中插入用户记录时，先把用户记录存储到这个根节点中。
  • 当根节点中的可用空间用完时继续插入记录，此时会将根节点中的所有记录复制到一个新分配的页，比如页a中，然后对这个新页进行页分裂的操作，得到另一个新页，比如页b。这时新插入的记录根据键值（也就是聚簇索引中的主键值，二级索引中对应的索引列的值）的大小就会被分配到页a或者页b中，而根节点便升级为存储目录项记录的页。

这个过程特别注意的是：一个B+树索引的根节点自诞生之日起，便不会再移动。这样只要我们对某个表建立一个
索引，那么它的根节点的页号便会被记录到某个地方，然后凡是InnoDB存储引擎需要用到这个索引的时候，都会从那个固定的地方取出根节点的页号，从而来访问这个索引。

内节点中目录项记录的唯一性：
为了保证在B+树的同一层内节点的各条目录项记录除页号这个字段以外是唯一的，所以对于非聚簇索引的内节点的目录项记录的内容实际上是由三个部分构成：索引列的值+主键值+页号。
image.png
一个数据页中最少存储2条记录

4.3 聚簇索引、非聚簇索引、联合索引

B+树索引按照物理实现方式可以分为：聚簇和非聚簇索引，也把非聚集索引称为二级索引或者辅助索引。

1. 聚簇索引

聚簇索引并不是一种单独的索引类型，而是一种数据存储方式：所有的用户记录都存储在了叶子节点，也就是
所谓的索引即数据，数据即索引。聚簇指的是数据行（记录）和相邻的键值（主键）聚簇的存储在一起。
image.png
特点：

  1. 使用主键值的递增顺序进行记录和页的排序，包括三个方面的含义：
    1. 页内的记录是按照主键的大小顺序排成一个单向链表。
    2. 各个存放用户记录的页也是根据页中用户记录的主键大小顺序排成一个双向链表。
    3. 存放目录项记录的页分为不同的层次，在同一层中的页也是根据页中目录项记录的主键大小顺序排成一个双向链表，在同一个页中也是根据目录项记录的主键大小顺序排成一个单向链表。
  2. 叶子节点存储的是完整的用户记录，即存储了所有列的值，包括隐藏列。

优点：

  1. 数据访问更快。聚簇索引将索引和数据保存在同一个B+树中，因此从聚簇索引中获取数据比非聚簇索引快。
  2. 聚簇索引对于主键的排序查找和范围查找速度非常快。
  3. 按照聚簇索引排列顺序，查询显示一定范围数据的时候，由于数据都是紧密相连，数据库不用从多个数据块中提取数据，所以节省了大量的IO操作。

缺点：

  1. 插入速度严重依赖于插入顺序 ，按照主键的顺序插入是最快的方式，否则将会出现页分裂，严重影响性能。因此，对于InnoDB表，一般都会定义一个自增的ID列为主键。
  2. 更新主键的代价很高，因为将会导致被更新的行移动。因此对于InnoDB表，一般定义主键为不可更新。
  3. 二级索引访问需要两次索引查找 ，第一次找到主键值，第二次根据主键值找到行数据。

限制：

  1. 对于MySQL数据库目前只有InnoDB数据引擎支持聚簇索引。
  2. 由于数据物理存储排序方式只能有一种，所以每个表只能有一个聚簇索引。一般情况下就是该表的主键。
  3. 如果没有定义主键，会选择非空的唯一索引代替。如果没有这样的索引，会隐式的定义一个主键来作为聚簇索引。
  4. 为了充分利用聚簇索引的聚簇的特性，所以表的主键列尽量选用有序的顺序id，而不建议用无序的id，比如UUID、MD5、HASH、字符串列作为主键无法保证数据的顺序增长。

2. 非聚簇索引

叶子节点不存储完整的用户记录。
image.png
与聚簇索引的区别：

  1. 非聚簇索引按照非主键列的递增顺序进行排序；聚簇索引按照主键的递增顺序进行排序
  2. 非聚簇索引的目录项包括目录项对应的数据页中最小非主键+目录项对应的数据页的页码；

聚簇索引的目录项包括目录项对应的数据页中最小主键+目录项对应的数据页的页码；

  1. 非聚簇索引的叶子节点只存储非主键列+主键，非完整的数据；

聚簇索引的叶子节点存储主键+全部列，包括隐藏列，即完整的数据；

  1. 一个表只能有一个聚簇索引，因为只能有一种排序存储的方式；但可以有多个非聚簇索引，也就是多个索引目录提供数据检索。
  2. 对数据进行查询时，使用聚簇索引的的查询效率高，不需要回表；但如果对数据进行插入，删除，更新等操作，聚簇索引效率会比非聚簇索引低。

非聚簇索引的回表查询：
因为非聚簇索引的叶子节点只存储非主键列+主键，非完整数据。所以根据这个以非主键列的递增顺序进行排序的非聚簇索引只能确定我们要查找记录的主键值，所以如果我们想根据非主键列的值查找到完整的用户记录的话，仍然需要到聚簇索引中再查一遍，这个过程称为回表 。也就是根据非主键列的值查询一条完整的用户记录需要使用到 2 棵B+树。

为什么还需要一次回表操作？为什么不直接把完整的用户记录放到叶子节点？
如果把完整的用户记录放到叶子节点是可以不用回表。但是太占地方了，相当于每建立一棵B+树都需要把所有的用户记录再都拷贝一遍，这就有点太浪费存储空间了。

因为这种按照非主键列建立的B+树需要一次回表操作才可以定位到完整的用户记录，所以这种B+树也被称为二级索引或辅助索引。由于使用的是某个非主键列的大小作为B+树的排序规则，所以也称这个B+树是为某个非主键列建立的索引。

非聚簇索引的存在不影响数据在聚簇索引中的组织，所以一张表可以有多个非聚簇索引。
image.png

3. 联合索引

我们也可以同时以多个列的大小作为排序规则，也就是同时为多个列建立索引。
比方说我们想让B+树按照c2和c列的大小进行排序，这个包含两层含义:

  • 先把各个记录和页按照c2列进行排序。
  • 在记录的c2列相同的情况下，采用c3列进行排序

image.png
以c2和c3列的大小为排序规则建立的B+树称为联合索引，本质上也是一个二级索引。它与分别为c2和c3列分别建立索引是不同的：

  1. 建立联合索引只会建立1棵B+树。
  2. 为c2和c3列分别建立索引会分别以c2和c3列的大小为排序规则建立2棵B+树。

    4.4 MyISAM中的索引方案

    | 索引 / 存储引擎 | MyISAM | InnoDB | | —- | —- | —- | | B+Tree | 支持 | 支持 |

InnoDB和MyISAM默认的索引是B+Tree索引；而Memory默认的索引是Hash索引。
InnoDB和MyISAM都支持B+Tree类型的索引，但是他们的实现原理不同的。

1. MyISAM中的非聚簇索引

MyISAM引擎使用B+Tree作为索引结构，叶子节点的data域存放的是数据记录的地址。
image.pngimage.png
InnoDB中聚簇索引的那棵B+树的叶子节点中已经把所有完整的用户记录都包含了，而MyISAM的索引方案虽然也使用树形结构，但是却将索引和数据分开存储︰

  • 将表中的记录按照记录的插入顺序单独存储在一个文件中，称之为数据文件。这个文件并不划分为若干个数据页，有多少记录就往这个文件中塞多少记录就成了。由于在插入数据的时候并没有刻意按照主键大小排序，所以我们并不能在这些数据上使用二分法进行查找。
  • 使用MyISAM存储引擎的表会把索引信息另外存储到一个称为索引文件的另一个文件中。MyISAM会单独为表的主键创建一个索引，只不过在索引的叶子节点中存储的不是完整的用户记录，而是主键值＋数据记录地址的组合。

    2. MyISAM 与 InnoDB 索引的对比

    image.png
    MyISAM的索引方式都是非聚簇的，InnoDB的索引方式包括聚簇索引和非聚簇索引。区别如下：
    ① 在InnoDB存储引擎中，我们只需要根据主键值对 聚簇索引 进行一次查找就能找到对应的记录，而在MyISAM 中却需要进行一次 回表 操作，意味着MyISAM中建立的索引相当于全部都是 二级索引 。
    ② InnoDB的数据文件本身就是索引文件，而MyISAM索引文件和数据文件是 分离的 ，索引文件仅保存数据记录的地址。
    ③ InnoDB的非聚簇索引data域存储相应记录 主键的值 ，而MyISAM索引记录的是 地址 。换句话说，InnoDB的所有非聚簇索引都引用主键作为data域。
    ④ MyISAM的回表操作是十分 快速 的，因为是拿着地址偏移量直接到文件中取数据的，反观InnoDB是通过获取主键之后再去聚簇索引里找记录，虽然说也不慢，但还是比不上直接用地址去访问。
    ⑤ InnoDB要求表 必须有主键 （ MyISAM可以没有 ）。如果没有显式指定，则MySQL系统会自动选择一个可以非空且唯一标识数据记录的列作为主键。如果不存在这种列，则MySQL自动为InnoDB表生成一个隐含字段作为主键，这个字段长度为6个字节，类型为长整型。

    4.5 其他类型的索引

    1. Hash

    Hash本身是一个函数，又称为散列函数，它可以帮助我们大幅提高检索数据的效率。Hash算法是通过某种确定性的算法（比如MD5、SHA1、SHA2、SHA3）将输入转变为输出。相同的输入永远可以得到相同的输出，假设输入内容有微小偏差，在输出中通常会有不同的结果。

加速查找速度的数据结构，常见的有两类:

  1. 树，例如平衡二叉搜索树，查询/插入/修改/删除的平均时间复杂度都是О(log2N);
  2. 哈希，例如HashMap，查询/插入/修改/删除的平均时间复杂度都是o(1);

采用Hash进行检索的效率非常高，基本上一次检索就可以找到数据，而B+Tree需要自顶而下依次查找，多次访问节点才能找到数据，中间需要多次I/O操作，效率上来说，Hash比B+Tree更快。

哈希函数有可能将两个不同的关键字映射到相同的位置，这叫做哈希碰撞 ，在数据库中一般采用链接法来解决。在链接法中，将散列到同一槽位的元素放在一个链表中，如下图所示：
image.png

Hash结构的效率高，那为什么不索引不用Hash而设计成树型呢？

  1. Hash仅能满足（=）（<>）和IN查询。而进行范围查询，hash类型的索引，时间复杂度会退化为O(N)；而树型索引的有序特性，依然是O(Log2N)；
  2. Hash类型的索引，数据存储是无序的，在order by时，需要重排序；
  3. 对于联合索引，Hash值是将联合索引键合并后一起来计算的，无法对单独的一个键或者几个索引键进行查询（索引失效）；
  4. 等值查询时，若索引列重复值很多时，效率会降低。因为hash冲突时，遍历每个位置的链表比较耗时。所以hash类型的索引通常不会用在重复值多的列上，比如列为年龄、性别等情况；

什么时候用Hash索引：自适应hash索引
InnoDB提供自适应hash索引，如果某个数据经常被访问，当满足一定条件时，就会将这个数据页的地址存放到Hash表中，这样下次查询的时候，就可以直接找到这个页面的位置。

MyISAM和InnoDB都不支持Hash索引，但InnoDB提供自适应Hash索引。Memory支持Hash索引。

2. B-Tree 多路平衡查找树

image.png
特点：

  • 一个节点上的指针域个数=数据域个数+1
  • 非叶子结点的数据域存储完整记录，而非只存储索引键，因此没有冗余
  • B树在插入和删除节点的时候如果导致树不平衡，就会通过自动调节节点位置来保持树的自平衡
  • 关键字集合分布在整棵树上，即叶子结点和非叶子结点都存储数据，搜索可能在非叶子结点结束。
  • 其搜索应能等价于在关键字全集中做一次二分查找

结构上的差异：

  • 有k个孩子的节点就有k个关键字。也就是孩子数量=关键字数，而B树中，孩子数量=关键字数+1.（其实说的就是指针域）
  • 非叶子节点的关键字也会同时存在子节点中，并且是在子节点中所有关键字的最大(或最小) .
  • 非叶子节点仅用于索引，不保存数据记录，跟记录有关的信息都放在叶子节点中。而B树中，非叶子节点既保存索引，也保存数据记录。
  • 所有关键字都在叶子节点出现，叶子节点构成一个有序链表，而且叶子节点本身按照关键字的大小从小到大顺序链接。

性能上的差异：

  • B+树的磁盘读写代价更低。B+树的内部节点并没有指向关键字信息的指针，因此其内部节点相对B树更小。如果把所有的同一节点的关键字存放在同一磁盘块中，那么盘块所能容纳的关键字数量也越多。一次性读入内存中需要查找的关键字也越多，相对来说IO读写次数降低。
  • B+树查询效率更稳定。B+树在访问叶子节点的时候才能找到对应的数据，B树可能结束于非叶子结点。
  • B+树查询效率更高。B+树阶数更大，深度更低，查询所需要的磁盘I/O更少。同样的磁盘页大小，B+树可以存储更多的节点关键字。
  • B+树在查询范围上效率更高。关键字都出现在B+树的叶子节点上，叶子结点之间会有指针，数据是递增的，我们可以通过指针链接进行范围查找。B树需要中序遍历才能范围查找。

4.8 索引的分类

从 功能逻辑 上说，索引主要有 4 种，分别是普通索引、唯一索引、主键索引、全文索引。
按照 物理实现方式 ，索引可以分为 2 种：聚簇索引和非聚簇索引。
按照 作用字段个数 进行划分，分成单列索引和联合索引。

1. 索引类型

主键索引：一种特殊的唯一索引，不允许有空值。
ALTER TABLE table_name ADD PRIMARY KEY
唯一索引：与普通索引类似，不同的是索引列的值必须唯一，但允许有空值。
ALTER TABLE table_name ADD UNIQUE (column)
普通索引：最基本的索引，没有任何限制。
ALTER TABLE table_name ADD INDEX index_name ( column )
全文索引：仅可用于 MyISAM 表，针对较大的数据，生成全文索引很耗时耗空间。
ALTER TABLE table_name ADD FULLTEXT ( column )
联合索引： 为了更多的提高效率可建立组合索引，遵循最左前缀原则。
ALTER TABLE table_name ADD INDEX index_name ( column1, column2, column3 )

2. 主键索引和唯一索引的区别

（1）主键一定是唯一性索引，唯一性索引并不一定主键。主键是一种约束，唯一索引是一种索引，两者在本质上是不同的。主键创建后一定包含一个唯一性索引，唯一性索引并不一定就是主键。
（2）唯一性索引列允许空值，而主键列不允许为空值。
（3）主键可以被其他表引用为外键，而唯一索引不能。
（4） 一个表最多只能创建一个主键，但可以创建多个唯一索引。
（5）主键更适合那些不容易更改的唯一标识，如自动递增列、身份证号等。
（6）在 RBO 模式下，主键的执行计划优先级要高于唯一索引。
（7）两者可以提高查询的速度。

4.6 MySQL8 索引新特性

支持降序索引、隐藏索引

4.6 索引的代价

空间上的代价
每建立一个索引都要为它建立一棵B+树，每一棵B+树的每一个节点都是一个数据页，一个页默认会占用 16KB 的存储空间，一棵很大的B+树由许多数据页组成，那就是很大的一片存储空间。

时间上的代价
每次对表中的数据进行 增、删、改 操作时，都需要去修改各个B+树索引。而且我们讲过，B+树每层节点都是按照索引列的值 从小到大的顺序排序 而组成了 双向链表 。不论是叶子节点中的记录，还是内节点中的记录（也就是不论是用户记录还是目录项记录）都是按照索引列的值从小到大的顺序而形成了一个单向链表。而增、删、改操作可能会对节点和记录的排序造成破坏，所以存储引擎需要额外的时间进行一些 记录移位 ， 页面分裂 、 页面回收 等操作来维护好节点和记录的排序。如果我们建了许多索引，每个索引对应的B+树都要进行相关的维护操作，会给性能拖后腿。

4.6 索引的设计原则

1. 哪些情况适合创建索引

  1. 字段的数值有唯一性限制。
  • 索引本身可以起到约束的作用，比如唯一索引、主键索引都是可以起到唯一性约束的，值是唯一的。因此在数据表中，如果某个字段是唯一性的，就可以直接创建唯一性索引或者主键索引，这样可以更快速地通过该索引来确定某条记录。
  • 当具有唯一性限制的字段带有唯一约束时，底层默认创建了索引，因此不需要再额外创建。
  • 业务上具有唯一特性的字段，即使是组合字段，也必须建成唯一索引。
  1. 查询、修改、删除语句中频繁作为 where 条件的字段，给这个字段创建普通索引就可以大幅提升效率。
  2. 为经常需要排序 group by、分组 order by、联合操作的字段建立索引。

  1. distinct 去重字段需要创建索引。

  2. 多表join时创建索引注意事项：

  3. 使用数据类型小的字段创建索引：如果索引的值很长，那么查询的速度会受到影响

  • 例如：对一个char(100)类型的字段进行全文检索需要的时间肯定比对char(10)类型的字段需要的时间更多
  1. 使用字符串前缀创建索引
  2. 对散列性高的字段建立索引（联合索引时把散列度高的放在前面）
  3. 使用最频繁的字段放在联合索引左侧

  4. 多个字段创建索引的情况下，联合索引更优

    2. 不适合创建索引的7种情况

  5. where条件、group by、order by用不到的字段

  6. 小表（数据小不需要索引）
  7. 有大量数据重复的字段。要尽量选择区分度高的列作为索引，区分度的公式是表示字段不重复的比例。
  8. 经常更新的表
  9. 不建议用无序值作索引
  10. 删除不再使用或者很少使用的索引
  11. 避免冗余和重复索引。只会增加维护的成本。
  • 冗余索引：对同一个列创建了多个索引，比如 index(a,b,c) 包括了 index(a)、index(a,b)、index(a,b,c)
  • 重复索引：

    3. 索引的其他原则

  1. 如果索引字段的值很长，那么查询的速度会受到影响。尽量使用值的前缀来索引。
  2. 最左前缀匹配原则
  3. 索引数目不要太多

在实际工作中，我们也需要注意平衡，索引的数目不是越多越好，越多的索引会使更新表变得很浪费时间，限制索引的数目尽量使用数据量少的索引。
因此，我们需要限制每张表上的索引数量，建议单张表索引数量不超过6个。原因：

  1. 每个索引都需要占用磁盘空间，索引越多，需要的磁盘空间就越大。
  2. 索引会影响INSERT. DELETE、 UPDATE 等语句的性能，因为表中的数据更改的同时，索引也会进行调整和更新，会造成负担。
  3. 优化器在选择如何优化查询时，会根据统一信息，对每一个可以用到的索引来进行评估，以生成出一个最好的执行计划，如果同时有很多个索引都可以用于查询，会增加MySQL优化器生成执行计划时间，降低查询性能不清楚
    1. 尽量的扩展索引，不要新建索引
    2. 索引列不能参与计算，保持列干净：带函数的查询不参与索引

      4.7 怎么优化索引，减少回表

      1. 使用覆盖索引

      如果要查询的数据列只用从索引中就能够取得，不必从数据区中读取，这时候使用的索引就叫做覆盖索引。Explain 中 Using index，表示这个语句使用了覆盖索引。

      2. 索引下推

      如果部分WHERE条件可以仅使用索引中的列进行筛选，则MYSQL就会把这部分WHEERE条件放到存储引擎筛选。然后存储引擎通过使用索引条目来筛选数据，并且只有在满足这一条件时才从表中读取行。
  • 索引下推全称叫做索引条件下推
  • 是Mysql5.6的新特性
  • 是一种在存储引擎层使用索引过滤数据的优化方式
  • 在EXPLAIN的Extra字段中出现了Using Index Condition 表示使用到了索引下推

    3. 子查询

    优化超多分页场景。查询条件放到子查询中，子查询只查主键id，然后使用子查询中确定的主键关联查询其他的属性字段。子查询使用主键id进行查询，则查询出来的记录包含了所有列值，然后利用right join将所需查询结果与子查询进行关联。
    1. select 各种字段 
    2. from table_name t1 
    3. right join 
    4. (select 主键 from table_name where 筛选条件 limit 0, 10)t2 
    5. on t1.主键 = t2.主键

    4.8 索引失效的情况

  1. 最佳左前缀法则（如果索引用了多列，检索条件从索引的最左列开始并且不跳过中间的列（1.火车头不能丢2.中间不能断）
  2. 计算、函数、类型转换导致索引失效
  3. 范围条件右边的列索引失效(a=1 and b=2 and c<3 and d=5，只有abc用到了索引)
  4. 不等于（!= or <>）索引失效
  5. is null可以用索引，is not null不可以用索引
  6. like以通配符%开头索引失效，可以结尾，中间，但不能开头
  7. OR前后存在非索引列，索引失效

5 定位慢查询

5.1 概念

是MySQL提供的一种日志记录，用来记录在MySQL中响应时间超过long_query_time的语句。long_query_time的默认值为10，意思是记录运行10秒以上的语句。默认情况下，MySQL数据库并不启动慢查询日志，需要手动来设置这个参数。
当然，如果不是调优需要的话，一般不建议启动该参数，因为开启慢查询日志会或多或少带来一定的性能影响。
慢查询日志支持将日志记录写入文件和数据库表。

5.2 参数

l slow_query_log：是否开启慢查询日志，1表示开启，0表示关闭。
l slow-query-log-file：新版（5.6及以上版本）MySQL数据库慢查询日志存储路径。可以不设置该参数，系统则会默认给一个缺省的文件host_name-slow.log
l long_query_time：慢查询阈值，当查询时间多于设定的阈值时，记录日志。
l log_queries_not_using_indexes：未使用索引的查询也被记录到慢查询日志中（可选项）。
l log_output：日志存储方式。log_output=’FILE’表示将日志存入文件，默认值是’FILE’。log_output=’TABLE’表示将日志存入数据库。

5.3 explain的参数

定位了查询慢的SQL之后，我们就可以使用EXPLAIN做针对性的分析查询语句。
MySQL中有专门负责优化SELECT语句的优化器模块，主要功能:通过计算分析系统中收集到的统计信息，为客户端请求的Query提供它认为最优的执行计划。这个执行计划展示了接下来具体执行查询的方式，比如多表连接的顺序是什么,对于每个表采用什么访问方法来具体执行查询等等。MySQL为我们提供了EXPLAIN语句来帮助我们查看某个查询语句的具体执行计划。

Id Select_type table partitions type Possible_keys
在一个查询语句中每个select都对应一个id Select对应哪个查询类型 表名 匹配分区信息 针对单表的访问方法 可能用到的索引
key Key_len ref rows filtered Extra
实际使用的索引 实际使用的索引长度 当时用索引列等值查询时，与索引列进行等值匹配的对象信息 预估需要读取的记录条数 某个表经过搜索条件过滤后剩余记录条数的百分比 额外信息








6 数据库的死锁问题原因是什么，怎么避免死锁，怎么解决死锁

死锁是指两个或多个事务在同一资源上相互占用，并请求锁定对方占用的资源，从而导致恶性循环。两个事务分别占着两个写锁。

1秒 事务A占id=1的写锁（update）
2秒
事务B占id=2的写锁
3秒 事务A想获取id=2的写锁
4秒
事务B想获取id=1的写锁

l 等待超时
l 让步，让最后一个产生死锁的事务回滚（事务结束），从而事务B可以顺利执行。通常让持有最少行级排它锁的事务进行回滚。

7 MySQL8.0的改进

1、取消缓存命中
2、支持降序索引

8 MVCC（快照读）

多版本并发控制；通过数据行的多个版本管理实现数据库的并发控制。使得在Innodb的事务隔离级别下执行一致性操作有了保证。也就是说是为了查询一些正在被另一个事务更新的行，并且可以看到它们被更新之前的值，这样查询就不用等待另一个事务释放锁。

1.8.1 undo log和redo log

1. Undo Log

Undo Log 是为了实现事务的原子性，在MySQL数据库InnoDB存储引擎中，用Undo Log来实现多版本并发控制(简称：MVCC)。每次对记录改动，都会记录一条undo日志，undo日志会被roll_pointer串成一个链表。

2. Redo Log

和Undo Log相反，Redo Log记录的是新数据的备份。在事务提交前，只要将Redo Log持久化即可，不需要将数据持久化。当系统崩溃时，虽然数据没有持久化，但是Redo Log已经持久化。系统可以根据Redo Log的内容，将所有数据恢复到最新的状态。

1.8.2 Readview

l undo log记录历史版本信息，ReadView决定当前事务能够读取哪个版本
l RR第一次读创建ReadView，RC每次select都会创建
l 已经提交的事务没有ReadView

creator_trx_id trx_ids up_limit_id low_limit_id
创建当前ReadView的事务的事务ID 生成ReadView的时刻，当前系统中活跃的读写事务的id列表 活跃事务中最小的事务id 生成ReadView的时刻，系统应该分配给下一个事务的id值；

1.8.3 innodb在RR级别下怎么解决幻读

MVCC 不能解决幻读的问题，临建锁（Next-Key Locks）就是为了解决这个问题而存在的。在可重复读（REPEATABLE READ）隔离级别下，使用MVCC + Next-Key Locks （临建锁）可以解决幻读问题。Next-Key Locks是Record Locks 和Gap Locks（锁住该记录和前面记录之间的位置）的结合，不仅锁定一个记录上的索引，也锁定索引之间的间隙。

9 悲观锁和乐观锁

悲观锁：每次拿数据的时候都会上锁，这样别人想拿这个数据就会阻塞直到他释放锁。Java中的synchronized和reentrantlock等独占锁就是悲观锁思想的体现。
乐观锁：版本号机制，时间戳机制和CAS机制

10 事务

10.1 概念

事务是由N步相互依赖的sql数据库操作语句组成的一组逻辑操作单元，使数据从一种状态变换到另一种状态。事务可由一条非常简单的SQL语句组成，也可以由一组复杂的SQL语句组成。在事务中的操作，要么都执行，要么都不执行，这就是事务的目的，也是事务模型区别于文件系统的重要特征之一。

10.2 ACID

A（atomicity），原子性。原子性指事务是一个不可分割的最小工作单位，要么全部提交，要么全部回滚。只有使事务中所有的数据库SQL操作语句都执行成功，整个事务的执行才算成功。如果事务中任何一条SQL语句执行失败或产生错误，那么整个单元将回滚，已经执行成功的SQL语句也必须撤销，所有受到影响的数据，即数据库状态将返回到事务开始以前的状态。
C（consistency），一致性。一致性指事务将数据库从一种合法状态转变为另一种合法的状态。在事务开始之前和事务结束以后，数据库的完整性约束没有被破坏。
I（isolation），隔离性。事务的隔离性要求每个读写事务的对象与其他事务的操作对象能相互分离，即一个事务内部的操作及使用的数据对并发的其他事务是隔离的，一个事务的执行不受其他事务的干扰，这通常使用锁来实现。
D（durability），持久性。持久性是指一个事务一旦提交，会永久改变数据库的数据，除非其他事务对其再进行修改。即使发生宕机等故障，数据库也能将数据恢复。持久性保证的是事务系统的高可靠性，而不是高可用性。

1.10.3 脏读，不可重复读，幻读

l 脏读（改）：一个事务读取到了另一个事务更新但未提交的临时无效的数据
l 不可重复读（改）：一个事务对同一个数据多次读取到的结果不一致。一个事务读取到另一个事务更新并已提交的数据。脏读与不可重复读的区别在于：前者读到的是其他事务未提交的数据，后者读到的是其他事务已提交的数据。
l 幻读（增删）：某一个事务对同一个表多次查询的行数不一致。一个事务读取到另外一个事务新插入并提交的数据。前提：新增数据刚好满足事务的查询条件。不可重复读与幻读的区别可以通俗的理解为：前者是数据变了，后者是数据的行数变了。

1.10.4 四种隔离级别

READ UNCOMMITTED ：读未提交。在该隔离级别，所有事务都可以看到其他未提交事务的执行结果。不能避免脏读、不可重复读、幻读。
READ COMMITTED ：读已提交。它满足了隔离的简单定义：一个事务只能看见已经提交事务所做的改变。这是大多数数据库系统的默认隔离级别（但不是MySQL默认的）。可以避免脏读，但不可重复读、幻读问题仍然存在。
REPEATABLE READ ：可重复读。事务A在读到一条数据之后，此时事务B对该数据进行了修改并提交，那么事务A再读该数据，读到的还是原来的内容。可以避免脏读、不可重复读，但幻读问题仍然存在。这是MySQL的默认隔离级别。
SERIALIZABLE ：可串行化，事务串行化执行。确保事务可以从一个表中读取相同的行。在这个事务持续期间，禁止其他事务对该表执行插入、更新和删除操作。所有的并发问题都可以避免，但性能十分低下。能避免脏读、不可重复读和幻读。

1.10.5 事务的原子性和持久性是怎么保证的

1. 原子性

实现原子性的关键，是当事务回滚时能够撤销所有已经成功执行的sql语句。InnoDB实现回滚靠的是undo log，当事务对数据库进行修改时，InnoDB会生成对应的undo log。如果事务执行失败或调用了rollback，导致事务需要回滚，便可以利用undo log中的信息将数据回滚到修改之前的样子。（对于undo日志在RR和RC级别下的不同，请移步MVCC）
undo log属于逻辑日志，它记录的是sql执行相关的信息。当发生回滚时，InnoDB会根据undo log的内容做与之前相反的工作。对于insert，回滚时会执行delete。对于delete，回滚时会执行insert。对于update，回滚时则会执行相反的update，把数据改回去。

2. 持久性

是利用了redo log。Mysql是先把磁盘上的数据加载到内存中，在内存中对数据进行修改，再刷回磁盘上。如果此时突然宕机，内存中的数据就会丢失。redo log包括两部分：一是内存中的日志缓冲(redo log buffer)，该部分日志是易失性的；二是磁盘上的重做日志文件(redo log file)，该部分日志是持久的。innodb通过force log at commit机制实现事务的持久性，即在事务提交的时候，必须先将该事务的所有事务日志写入到磁盘上的redo log file和undo log file中进行持久化。也就是说提交了两个日志文件。

11 数据库中的锁

11.1 行级锁：

特点：开销大、加锁慢，发生锁冲突的概率低、并发度高，会出现死锁。

1. InnoDB存储引擎实现了如下两种标准的行级锁：

共享锁（S Lock）：行级锁。允许事务读一行数据。
排他锁（X Lock）：行级锁。允许事务删除或更新一行数据。

2. InnoDB存储引擎有3种行锁的算法，其分别是：

Record Lock：单个行记录上的锁。Record Lock总是会去锁住索引记录，如果InnoDB存储引擎表在建立的时候没有设置任何一个索引，那么这时InnoDB存储引擎会使用隐式的主键来进行锁定。
Gap Lock：间隙锁。用于锁定一个范围，但不包含记录本身。使用范围查询时，对范围内不存在的记录加锁。它的作用一是为了防止幻读（阻止多个事务将记录插入到同一范围内，而导致幻读问题的产生），二是为了满足恢复和复制的需要。
Next-Key Lock∶锁定一个范围，并且锁定记录本身。Next-Key Lock是结合了Gap Lock和Record Lock的一种锁定算法，在Next-Key Lock算法下，InnoDB对于行的查询都是采用这种锁定算法。采用Next-Key Lock的锁定技术称为Next-Key Locking，其设计的目的是为了解决Phantom Problem（幻读）。而利用这种锁定技术，锁定的不是单个值，而是一个范围，是谓词锁（predict lock）的一种改进。

3. Gap锁

Gap锁和Next-Key锁只存在RR隔离级别下，RC隔离级别下并不使用这些锁。
Gap锁意义是什么？是为了解决幻读问题。
什么是幻读问题？一个事务中同一个SQL多次执行，结果集不同，就是多了一些记录。这违反了事务的隔离性，即当前事务能够看到其他事务的结果。Gap锁的目的就是解决这个问题。它阻塞插入意向锁，阻止不适当的记录插入，避免幻读问题。
加Gap锁或Next-Key锁的场景：思考一下通过这些锁是否可以解决幻读问题，就知道为什么要加Gap锁和Next-Key锁了。
在RC隔离级别下会给所有记录加Record锁，在RR隔离级别下，对所有记录加Next-Key锁。

4. 插入意图锁（Insert Intention Lock）：

行级锁。插入意图锁是在行插入之前通过INSERT操作设置的一种特殊间隙锁。
注意：多个事务插入同一个间隙的不同位置，他们并不会冲突。假设存在索引记录，其值分别为4和7。单独的事务分别尝试插入值5和6，在获得插入行的排他锁之前，每个事务都使用插入意图锁来锁定4和7之间的间隙，但他们不会互相阻塞。同样，不同事务请求同一个间隙的Gap锁并不会阻塞，但如果一个事务请求了Gap锁，另一个事务再请求插入意向锁，则会阻塞。

1.11.2 表级锁：

特点：开销小、加锁快，发生锁冲突的概率高、并发度低，不会出现死锁。

1. 意向锁：

InnoDB存储引擎支持多粒度锁定，这种锁定允许事务在行级上的锁和表级上的锁同时存在。为了支持在不同粒度上进行加锁操作，InnoDB存储引擎支持一种额外的锁方式，称之为意向锁。意向锁是将锁定的对象分为多个层次，意向锁意味着事务希望在更细粒度上进行加锁。
InnoDB存储引擎支持意向锁设计比较简练，其意向锁即为表级别的锁。设计目的主要是为了在一个事务中揭示下一行将被请求的锁类型，表明“某个事务正在某些行持有了锁，或者他准备去持有锁”。避免了加行级锁时先去逐行看有没有加锁。
意图锁是表级锁，标识事务稍后对表中的行做哪种类型的锁定（共享或独占）。其支持两种意向锁：意向共享锁和意向排它锁

2. 意向共享锁（IS Lock）：

l 事务想要获得一张表中某几行的共享锁，就必须先获取表的IS锁
l 在给一个数据加S锁之前Innodb先给表加IS锁，表示准备加S锁，想要读取表中的某几行数据。
l 如果事务A给数据表中某些记录上了共享锁，会自动给该数据表添加意向共享锁。

3. 意向排他锁（IX Lock）：

l 事务想要获得一张表中某几行的排他锁，就必须先获得表的IX锁
l 在给一个数据加X锁之前Innodb先给表加IX锁，表示准备加X锁，想要删除或更新表中的某几行数据
l 如果事务A给数据表中某些记录上了排他锁，会自动给该数据表添加意向排他锁。

4. 意向锁总结

l 意向锁是表级锁，是InnoDB引擎自动生成的；
l InnoDB支持多粒度锁，特定场景下，表级锁（意向锁）和行级锁共存；
l 意向锁之间是不互相排斥的；
l 意向锁和行级锁也是不互相排斥的（和第二条一个意思=> 意向锁和行锁共存）
l 表级锁和意向锁之间的排斥规则和读写锁的排斥规则是一样的
l 意向锁在保证并发性的前提下，实现了行锁和表锁共存并且满足事务隔离性的要求

5. 意图锁遵循如下协议：

在事务获取表中某行的共享锁之前，它必须首先在表上获取IS锁或更强的锁。
在事务获取表中某行的独占锁之前，它必须首先在表上获取IX锁。
意向锁只会阻塞表级别的锁（如LOCK TABLES请求的表锁），并不会阻塞行级锁（如行级X锁）。

6. 自增锁：

自增锁是事务插入到具有AUTO_INCREMENT列的表时的一种特殊表级锁。当一个事务将值插入表时，必须获取自增锁，以便获取自增列的值。innodb_autoinc_lock_mode参数可以控制auto-increment 锁定的算法。

7. 表级别的S锁、X锁：

innodb还支持表锁，LOCK TABLES … WRITE可以获取指定表的X锁。LOCK TABLES … READ可以获取指定表的S锁。

1.11.3 锁的兼容

表级意向锁与行级锁的兼容性如下图所示。兼容是指对同一记录（row）锁的兼容性情况。


 IS IX S X
IS 兼容 兼容 兼容 不兼容
IX 兼容 兼容 不兼容 不兼容
S 兼容 不兼容 兼容 不兼容
X 不兼容 不兼容 不兼容 不兼容

总结：X锁与任何的锁都不兼容，S锁与IX锁、X锁不兼容，其他情况都是兼容的

1.11.4 锁的升级

锁升级（Lock Escalation）是指将当前锁的粒度降低。举例来说，数据库可以把一个表的1000个行锁升级为一个页锁，或者将页锁升级为表锁。
InnoDB存储引擎不存在锁升级的问题。因为其不是根据每个记录来产生行锁的，相反，其根据每个事务访问的每个页对锁进行管理的，采用的是位图的方式。因此不管一个事务锁住页中一个记录还是多个记录，其开销通常都是一致的。

12 Sql注入问题

12.1 SQL注入简介

SQL注入是比较常见的网络攻击方式之一，它不是利用操作系统的BUG来实现攻击，而是针对程序员编程时的疏忽，通过SQL语句，实现无帐号登录，甚至篡改数据库。

12.2 SQL注入攻击的总体思路

1.寻找到SQL注入的位置
2.判断服务器类型和后台数据库类型
3.针对不通的服务器和数据库特点进行SQL注入攻击

12.3 SQL注入攻击实例

比如在一个登录界面，要求输入用户名和密码：
可以这样输入实现免帐号登录：
用户名：‘or 1 = 1 –
密码：
点登陆,如若没有做特殊处理,那么这个非法用户就很得意的登陆进去了.(当然现在的有些语言的数据库API已经处理了这些问题)
这是为什么呢? 下面我们分析一下：
从理论上说，后台认证程序中会有如下的SQL语句：
String sql = “select from user_table where username=
‘ “+userName+” ‘ and password=’ “+password+” ‘“;
当输入了上面的用户名和密码，上面的SQL语句变成：
SELECT FROM user_table WHERE username=
‘’or 1 = 1 — and password=’’
分析SQL语句：
条件后面username=”or 1=1 用户名等于”或1=1 那么这个条件一定会成功；
然后后面加两个-，这意味着注释，它将后面的语句注释，让他们不起作用，这样语句永远都能正确执行，用户轻易骗过系统，获取合法身份。
这还是比较温柔的，如果是执行
SELECT * FROM user_table WHERE
username=’’ ;DROP DATABASE (DB Name) —‘ and password=’’
….其后果可想而知…

1. （简单又有效的方法）PreparedStatement

采用预编译语句集，它内置了处理SQL注入的能力，只要使用它的setXXX方法传值即可。
使用好处：
(1).代码的可读性和可维护性.
(2).PreparedStatement尽最大可能提高性能.
(3).最重要的一点是极大地提高了安全性.
原理：
sql注入只对sql语句的准备(编译)过程有破坏作用
而PreparedStatement已经准备好了,执行阶段只是把输入串作为数据处理,
而不再对sql语句进行解析,准备,因此也就避免了sql注入问题.

2. 使用正则表达式过滤传入的参数

要引入的包：
import java.util.regex.;
正则表达式：
private String CHECKSQL = “^(.+)\sand\s(.+)|(.+)\sor(.+)\s$”;
判断是否匹配：
Pattern.matches(CHECKSQL,targerStr);
下面是具体的正则表达式：
检测SQL meta-characters的正则表达式：
/(\%27)|(\’)|(--)|(\%23)|(#)/ix
修正检测SQL meta-characters的正则表达式：/((\%3D)|(=))[^\n]((\%27)|(\’)|(--)|(\%3B)|(:))/i
典型的SQL 注入攻击的正则表达式：/\w*((\%27)|(\’))((\%6F)|o|(\%4F))((\%72)|r|(\%52))/ix
检测SQL注入，UNION查询关键字的正则表达式：/((\%27)|(\’))union/ix(\%27)|(\’)
检测MS SQL Server SQL注入攻击的正则表达式：
/exec(\s|+)+(s|x)p\w+/ix
等等….

3. 字符串过滤

比较通用的一个方法：
（||之间的参数可以根据自己程序的需要添加）

public static boolean sql_inj(String str)
{undefined
String inj_str = “‘|and|exec|insert|select|delete|update|
count|*|%|chr|mid|master|truncate|char|declare|;|or|-|+|,”;
String inj_stra[] = split(inj_str,”|”);
for (int i=0 ; i < inj_stra.length ; i++ )
{undefined
if (str.indexOf(inj_stra[i])>=0)
{undefined
return true;
}
}
return false;
}

4. jsp中调用该函数检查是否包函非法字符

防止SQL从URL注入：

sqlinj.java代码：
package sql_inj;
import java.net.;
import java.io.;
import java.sql.;
import java.text.;
import java.lang.String;
public class sql_inj{undefined
public static boolean sql_inj(String str)
{undefined
String inj_str = “‘|and|exec|insert|select|delete|update|
count||%|chr|mid|master|truncate|char|declare|;|or|-|+|,*”;
//这里的东西还可以自己添加_ _String[] inj_stra=inj_str.split(“\\|”);
for (int i=0 ; i < inj_stra.length ; i++ )
{undefined
if (str.indexOf(inj_stra[i])>=0)
{undefined
return true;
}
}
return false;
}
}
}

5. JSP页面判断代码：

使用javascript在客户端进行不安全字符屏蔽
功能介绍：检查是否含有”‘”,”\”,”/”
参数说明：要检查的字符串
返回值：0：是1：不是
函数名是

function check(a){
undefined
return 1;
fibdn = new Array (”‘” ,”\\”,”/”);
i=fibdn.length;
j=a.length;
for (ii=0; ii＜i; ii++)
{
for (jj=0; jj＜j; jj++)
{
temp1=a.charAt(jj);
temp2=fibdn[ii];
if (tem’; p1==temp2)
{
return 0;
}
}
}
return 1;
}

1.12.4 Mybatis是怎么防止sql注入的

select id, username, password, role from user where username=? and password=?

【底层实现原理】MyBatis在处理#{}时，会将sql中的#{}替换为?号，调用PreparedStatement的set方法来赋值 ,PreparedStatement在执行阶段只是把输入串作为数据处理,不再对sql语句进行解析,准备,因此也就避免了sql注入问题。
PreparedStatement防止SQL注入的原理：JDBC的PreparedStatement会将带’?’占位符的sql语句预先编译好，也就是SQL引擎会预先进行语法分析，产生语法树，生成执行计划。对于占位符输入的参数，无论是什么，都不会影响该SQL语句的语法结构了，因为语法分析已经完成了，即使你后面输入了这些sql命令，也不会被当成sql命令来执行了，只会被当做字符串字面值参数。所以的sql语句预编译可以防御SQL注入。而且在多次执行同一个SQL时，能够提高效率。原因是SQL已编译好，再次执行时无需再编译。