数据库

通常在开发中常用的数据库Mysql开始，MySQL是一个关系型数据库管理系统，由瑞典MySQL AB 公司开发，目前属于 Oracle 旗下公司。MySQL 最流行的关系型数据库管理系统，在应用方面MySQL是最好的 RDBMS (Relational Database Management System，关系数据库管理系统) 应用软件之一。

MySQL基础:

MySQL的多存储引擎架构

MySQL作为一个大型的网络程序、数据管理系统，架构非常复杂。

MySQL的数据类型

  1. 整型
类型 存储 存储 最小值 最大值
byte bit signed signed
TINYINT 1 8 -27 = -128 27-1 = 127
SMALLINT 2 16
MEDIUMINT 3 24
INT 4 32 -231 = -2147483648 231-1 = 2147483647
BIGINT 8 64

TINYINT, SMALLINT, MEDIUMINT, INT, BIGINT 分别使用 8, 16, 24, 32, 64 位存储空间，一般情况下越小的列越好。

INT(11) 中的数字只是规定了交互工具显示字符的个数，对于存储和计算来说是没有意义的。

  1. 浮点数

FLOAT 和 DOUBLE 为浮点类型，DECIMAL 为高精度小数类型。CPU 原生支持浮点运算，但是不支持 DECIMAl 类型的计算，因此 DECIMAL 的计算比浮点类型需要更高的代价。

FLOAT、DOUBLE 和 DECIMAL 都可以指定列宽，例如 DECIMAL(18, 9) 表示总共 18 位，取 9 位存储小数部分，剩下 9 位存储整数部分。

  1. 字符串

MySQL中主要有 CHAR 和 VARCHAR 两种字符串类型，一种是定长的，一种是变长的。

VARCHAR 这种变长类型能够节省空间，因为只需要存储必要的内容。但是在执行 UPDATE 时可能会使行变得比原来长，当超出一个页所能容纳的大小时，就要执行额外的操作。MyISAM 会将行拆成不同的片段存储，而 InnoDB 则需要分裂页来使行放进页内。

VARCHAR 会保留字符串末尾的空格，而 CHAR 会删除。

  1. 时间和日期

MySQL中提供了两种相似的日期时间类型：DATATIME 和 TIMESTAMP。

  • DATATIME

能够保存从 1001 年到 9999 年的日期和时间，精度为秒，使用 8 字节的存储空间。

它与时区无关。

默认情况下，MySQL 以一种可排序的、无歧义的格式显示 DATATIME 值，例如“2008-01-16 22:37:08”，这是 ANSI 标准定义的日期和时间表示方法。

  • TIMESTAMP

和 UNIX 时间戳相同，保存从 1970 年 1 月 1 日午夜（格林威治时间）以来的秒数，使用 4 个字节，只能表示从 1970 年 到 2038 年。

它和时区有关，也就是说一个时间戳在不同的时区所代表的具体时间是不同的。

MySQL 提供了 FROM_UNIXTIME() 函数把 UNIX 时间戳转换为日期，并提供了 UNIX_TIMESTAMP() 函数把日期转换为 UNIX 时间戳。

默认情况下，如果插入时没有指定 TIMESTAMP 列的值，会将这个值设置为当前时间。

应该尽量使用TIMESTAMP，因为它比DATETIME 空间效率更高。

数据库的事务

数据库的事务指的是满足数据库的 ACID 特性的一组操作，可以通过 Commit 提交一个事务，也可以使用 Rollback 进行回滚。

MySQL 中默认采用自动提交(AUTOCOMMIT)模式。如果不显式使用 START TRANSACTION 语句来开始一个事务，那么每个查询都会被当做一个事务自动提交。

数据库ACID

  1. 原子性（Atomicity）

原子性是指事务是一个不可分割的工作单位，事务中的操作要么全部成功，要么全部失败。比如在同一个事务中的SQL语句，要么全部执行成功，要么全部执行失败。
回滚可以用日志来实现，日志记录着事务所执行的修改操作，在回滚时反向执行这些修改操作即可。

  1. 一致性（Consistency）

事务必须使数据库从一个一致性状态变换到另外一个一致性状态。以转账为例子，A向B转账，假设转账之前这两个用户的钱加起来总共是100，那么A向B转账之后，不管这两个账户怎么转，A用户的钱和B用户的钱加起来的总额还是100，这个就是事务的一致性。

  1. 隔离性（Isolation）

隔离性是当多个用户并发访问数据库时，比如操作同一张表时，数据库为每一个用户开启的事务，不能被其他事务的操作所干扰，多个并发事务之间要相互隔离。

即要达到这么一种效果：对于任意两个并发的事务 T1 和 T2，在事务 T1 看来，T2 要么在 T1 开始之前就已经结束，要么在 T1 结束之后才开始，这样每个事务都感觉不到有其他事务在并发地执行。

  1. 持久性（Durability）

一旦事务提交，则其所做的修改将会永远保存到数据库中。即使系统发生崩溃，事务执行的结果也不能丢失。

可以通过数据库备份和恢复来实现，在系统发生奔溃时，使用备份的数据库进行数据恢复。

事务的 ACID 特性概念简单，但不是很好理解，主要是因为这几个特性不是一种平级关系：

  • 只有满足一致性，事务的执行结果才是正确的。
  • 在无并发的情况下，事务串行执行，隔离性一定能够满足。此时只要能满足原子性，就一定能满足一致性。
  • 在并发的情况下，多个事务并发执行，事务不仅要满足原子性，还需要满足隔离性，才能满足一致性。
  • 事务满足持久化是为了能应对数据库奔溃的情况。

数据库中的范式

满足最低要求的范式是第一范式（1NF）。在第一范式的基础上进一步满足更多规范要求的称为第二范式（2NF），其余范式以次类推。一般说来，数据库只需满足第三范式 (3NF）就可以了。范式的包含关系。一个数据库设计如果符合第二范式，一定也符合第一范式。如果符合第三范式，一定也符合第二范式…

  • 1NF：属性不可分.
  • 2NF：属性完全依赖于主键 [消除部分子函数依赖].
  • 3NF：属性不依赖于其它非主属性 [消除传递依赖].
  • BCNF（巴斯-科德范式）：在1NF基础上，任何非主属性不能对主键子集依赖[在3NF基础上消除对主码子集的依赖].
  • 4NF：要求把同一表内的多对多关系删除.
  • 5NF（完美范式）：从最终结构重新建立原始结构.

范式理论是为了解决以上提到四种异常。高级别范式的依赖于低级别的范式，1NF 是最低级别的范式。

  1. 第一范式 (1NF)

属性不可分。

  1. 第二范式 (2NF)

每个非主属性完全函数依赖于键码。

可以通过分解来满足。

分解前

Sno Sname Sdept Mname Cname Grade
1 学生-1 学院-1 院长-1 课程-1 60
2 学生-2 学院-2 院长-2 课程-2 90
2 学生-2 学院-2 院长-2 课程-1 100
3 学生-3 学院-2 院长-2 课程-2 95

以上学生课程关系中，{Sno, Cname} 为键码，有如下函数依赖：

  • Sno -> Sname, Sdept
  • Sdept -> Mname
  • Sno, Cname-> Grade

Grade 完全函数依赖于键码，它没有任何冗余数据，每个学生的每门课都有特定的成绩。

Sname, Sdept 和 Mname 都部分依赖于键码，当一个学生选修了多门课时，这些数据就会出现多次，造成大量冗余数据。

分解后

关系-1

Sno Sname Sdept Mname
1 学生-1 学院-1 院长-1
2 学生-2 学院-2 院长-2
3 学生-3 学院-2 院长-2

有以下函数依赖：

  • Sno -> Sname, Sdept
  • Sdept -> Mname

关系-2

Sno Cname Grade
1 课程-1 90
2 课程-2 80
2 课程-1 100
3 课程-2 95

有以下函数依赖：

  • Sno, Cname -> Grade
  1. 第三范式 (3NF)

非主属性不传递函数依赖于键码。

上面的 关系-1 中存在以下传递函数依赖：

  • Sno -> Sdept -> Mname

可以进行以下分解：

关系-11

Sno Sname Sdept
1 学生-1 学院-1
2 学生-2 学院-2
3 学生-3 学院-2

关系-12

Sdept Mname
学院-1 院长-1
学院-2 院长-2

并发一致性问题

  1. 丢失修改

T1 和 T2 两个事务都对一个数据进行修改，T1 先修改，T2 随后修改，T2 的修改覆盖了 T1 的修改。

  1. 脏数据读取
    （针对未提交数据）如果一个事务中对数据进行了更新，但事务还没有提交，另一个事务可以 “看到” 该事务没有提交的更新结果，这样造成的问题就是，如果第一个事务回滚，那么，第二个事务在此之前所 “看到” 的数据就是一笔脏数据。 （脏读又称无效数据读出。一个事务读取另外一个事务还没有提交的数据叫脏读。 ）

应用示例：

Jame的原工资为 1000, 财务人员将Jame的工资改为了 8000 (但未提交事务)

Jame读取自己的工资，发现自己的工资变为了 8000，欢天喜地！

而财务发现操作有误，回滚了事务，Jame的工资又变为了1000

像这样的事件，Jame记取的工资数8000就是一个脏数据。

解决办法：

把数据库的事务隔离级别调整到 READ_COMMITTED

图解：

1 修改一个数据，T2 随后读取这个数据。如果 T1 撤销了这次修改，那么 T2 读取的数据是脏数据。

  1. 不可重复读

是指在一个事务内，多次读同一数据。在这个事务还没有结束时，另外一个事务也访问该同一数据。那么，在第一个事务中的两次读数据之间，由于第二个事务的修改，那么第一个事务两次读到的的数据可能是不一样的。这样在一个事务内两次读到的数据是不一样的，因此称为是不可重复读。（同时操作，事务1分别读取事务2操作时和提交后的数据，读取的记录内容不一致。不可重复读是指在同一个事务内，两个相同的查询返回了不同的结果。 ）

应用示例：

#在事务1中，Jame读取了自己的工资为1000，操作并没有完成

con1 = getConnection();  
select salary from employee empId ="Mary"; 

#在事务2中，这时财务人员修改了Jame的工资为 2000，并提交了事务.

con2 = getConnection();  
update employee set salary = 2000;  
con2.commit();  

#在事务1中，Jame再次读取自己的工资时，工资变为了2000

#con1  
select salary from employee empId ="Mary";

在一个事务中前后两次读取的结果并不致，导致了不可重复读。

解决办法：

如果只有在修改事务完全提交之后才可以读取数据，则可以避免该问题。把数据库的事务隔离级别调整到REPEATABLE_READ

T2 读取一个数据，T1 对该数据做了修改。如果 T2 再次读取这个数据，此时读取的结果和第一次读取的结果不同。

  1. 幻读

事务 T1 读取一条指定的 Where 子句所返回的结果集，然后 T2 事务新插入一行记录，这行记录恰好可以满足T1 所使用的查询条件。然后 T1 再次对表进行检索，但又看到了 T2 插入的数据。 （和可重复读类似，但是事务 T2 的数据操作仅仅是插入和删除，不是修改数据，读取的记录数量前后不一致）.

幻读的重点在于新增或者删除 (数据条数变化).

同样的条件，第1次和第2次读出来的记录数不一样.

应用示例:

目前工资为1000的员工有10人

#事务1，读取所有工资为 1000 的员工（共读取 10 条记录 ）

con1 = getConnection();  
Select * from employee where salary =1000;  

#这时另一个事务向 employee 表插入了一条员工记录，工资也为 1000

con2 = getConnection();  
Insert into employee(empId,salary) values("Lili",1000);  
con2.commit();  

#事务1再次读取所有工资为 1000的 员工（共读取到了 11 条记录，这就像产生了幻读）

#con1  
select * from employee where salary =1000;

解决办法：

如果在操作事务完成数据处理之前，任何其他事务都不可以添加新数据，则可避免该问题。把数据库的事务隔离级别调整到 SERIALIZABLE_READ

图解：

T1 读取某个范围的数据，T2 在这个范围内插入新的数据，T1 再次读取这个范围的数据，此时读取的结果和和第一次读取的结果不同。

事务隔离级别

  1. 串行化 (Serializable)

所有事务一个接着一个的执行，这样可以避免幻读 (phantom read)，对于基于锁来实现并发控制的数据库来说，串行化要求在执行范围查询的时候，需要获取范围锁，如果不是基于锁实现并发控制的数据库，则检查到有违反串行操作的事务时，需回滚该事务。

  1. 可重复读 (Repeated Read)

所有被 Select 获取的数据都不能被修改，这样就可以避免一个事务前后读取数据不一致的情况。但是却没有办法控制幻读，因为这个时候其他事务不能更改所选的数据，但是可以增加数据，即前一个事务有读锁但是没有范围锁，为什么叫做可重复读等级呢？那是因为该等级解决了下面的不可重复读问题。

注意：现在主流数据库都使用 MVCC 并发控制，使用之后RR（可重复读）隔离级别下是不会出现幻读的现象。

  1. 读已提交 (Read Committed)

被读取的数据可以被其他事务修改，这样可能导致不可重复读。也就是说，事务读取的时候获取读锁，但是在读完之后立即释放(不需要等事务结束)，而写锁则是事务提交之后才释放，释放读锁之后，就可能被其他事务修改数据。该等级也是 SQL Server 默认的隔离等级。

  1. 读未提交 (Read Uncommitted)

最低的隔离等级，允许其他事务看到没有提交的数据，会导致脏读。

总结

  • 四个级别逐渐增强，每个级别解决一个问题，每个级别解决一个问题，事务级别遇到，性能越差，大多数环境(Read committed 就可以用了)
隔离级别 脏读 不可重复读 幻影读
未提交读 √ √ √
提交读 × √ √
可重复读 × × √
可串行化 × × ×

存储引擎

MySQL中提供了多个存储引擎，包括处理事务安全表的引擎和处理非事务安全表的引擎。在 MySQL 中，不需要在整个服务器中使用同一种存储引擎，针对具体的要求，可以对每一个表使用不同的存储引擎。

MySQL 中的数据用各种不同的技术存储在文件（或者内存）中。这些技术中的每一种技术都使用不同的存储机制、索引技巧、锁定水平并且最终提供广泛的不同的功能和能力。通过选择不同的技术，你能够获得额外的速度或者功能，从而改善你的应用的整体功能。存储引擎说白了就是如何存储数据、如何为存储的数据建立索引和如何更新、查询数据等技术的实现方法。

通常，如果你在研究大量的临时数据，你也许需要使用内存存储引擎。内存存储引擎能够在内存中存储所有的表格数据。又或者，你也许需要一个支持事务处理的数据库（以确保事务处理不成功时数据的回退能力）。

在MySQL中有很多存储引擎，每种存储引擎大相径庭，那么又改如何选择呢？

MySQL 5.5 以前的默认存储引擎是 MyISAM, MySQL 5.5 之后的默认存储引擎是 InnoDB

不同存储引起都有各自的特点，为适应不同的需求，需要选择不同的存储引擎，所以首先考虑这些存储引擎各自的功能和兼容。

1. MyISAM

MySQL 5.5 版本之前的默认存储引擎，在 5.0 以前最大表存储空间最大 4G，5.0 以后最大 256TB。

Myisam 存储引擎由 .myd（数据）和 .myi（索引文件）组成，.frm文件存储表结构（所以存储引擎都有）

特性

  • 并发性和锁级别 （对于读写混合的操作不好，为表级锁，写入和读互斥）
  • 表损坏修复
  • Myisam 表支持的索引类型（全文索引）
  • Myisam 支持表压缩（压缩后，此表为只读，不可以写入。使用 myisampack 压缩）

应用场景

  • 没有事务
  • 只读类应用（插入不频繁，查询非常频繁）
  • 空间类应用（唯一支持空间函数的引擎）
  • 做很多 count 的计算
2. InnoDB

MySQL 5.5 及之后版本的默认存储引擎

特性

  • InnoDB为事务性存储引擎
  • 完全支持事物的 ACID 特性
  • Redo log （实现事务的持久性） 和 Undo log（为了实现事务的原子性，存储未完成事务log，用于回滚）
  • InnoDB支持行级锁
  • 行级锁可以最大程度的支持并发
  • 行级锁是由存储引擎层实现的

应用场景

  • 可靠性要求比较高，或者要求事务
  • 表更新和查询都相当的频繁，并且行锁定的机会比较大的情况。
3. CSV

文件系统存储特点

  • 数据以文本方式存储在文件中
  • .csv文件存储表内容
  • .csm文件存储表的元数据，如表状态和数据量
  • .frm存储表的结构

CSV存储引擎特点

  • 以 CSV 格式进行数据存储
  • 所有列必须都是不能为 NULL
  • 不支持索引
  • 可以对数据文件直接编辑（其他引擎是二进制存储，不可编辑）

引用场景

  • 作为数据交换的中间表
4. Archive

特性

  • 以 zlib 对表数据进行压缩，磁盘 I/O 更少
  • 数据存储在ARZ为后缀的文件中（表文件为 a.arz，a.frm）
  • 只支持 insert 和 select 操作（不可以 delete 和 update，会提示没有这个功能）
  • 只允许在自增ID列上加索引

应用场景

  • 日志和数据采集类应用
5. Memory

特性:

  • 也称为 HEAP 存储引擎，所以数据保存在内存中（数据库重启后会导致数据丢失）
  • 支持 HASH 索引（等值查找应选择 HASH）和 BTree 索引（范围查找应选择）
  • 所有字段都为固定长度，varchar(10) == char(10)
  • 不支持 BLOG 和 TEXT 等大字段
  • Memory 存储使用表级锁（性能可能不如 innodb）
  • 最大大小由 max_heap_table_size 参数决定
  • Memory存储引擎默认表大小只有 16M，可以通过调整 max_heap_table_size 参数

应用场景:

  • 用于查找或是映射表，例如右边和地区的对应表
  • 用于保存数据分析中产生的中间表
  • 用于缓存周期性聚合数据的结果表

注意： Memory 数据易丢失，所以要求数据可再生

6. Federated

特性

  • 提供了访问远程 MySQL 服务器上表的方法
  • 本地不存储数据，数据全部放在远程服务器上

使用 Federated,默认是禁止的。如果需要启用，需要在启动时增加Federated参数

独立表空间和系统表空间应该如何抉择？

可以比较下两者之间的差别进行选择：

  • 系统表空间：无法简单的收缩大小（这很恐怖，会导致 ibdata1 一直增大，即使删除了数据也不会变小）
  • 独立表空间：可以通过 optimize table 命令收缩系统文件
  • 系统表空间：会产生I/O瓶颈（因为只有一个文件）
  • 独立表空间：可以向多个文件刷新数据

注意：对于Innodb引擎使用独立表空间（mysql5.6版本以后默认是独立表空间）

系统表转移为独立表的步骤（非常繁琐）

  • 使用 mysqldump 导出所有数据库表数据

  • 停止 mysql 服务，修改参数，并且删除Innodb相关文件

  • 重启 mysql 服务，重建mysql系统表空间

  • 重新导入数据

  • 问：如何选择存储引擎，需要考虑以下的条件：

  • 是否需要事务

  • 是否可以热备份

  • 崩溃恢复

  • 存储引擎的特有特性

注意:不要混合使用存储引擎

推荐：Innodb

  • MyISAM和InnoDB引擎的区别?

两者之间的区别：

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

应用场景：

  • MyISAM 管理非事务表。它提供高速存储和检索，以及全文搜索能力。如果应用中需要执行大量的 SELECT 查询，那么 MyISAM 是更好的选择。
  • InnoDB 用于事务处理应用程序，具有众多特性，包括 ACID 事务支持。如果应用中需要执行大量的 INSERT或 UPDATE 操作，则应该使用 InnoDB，这样可以提高多用户并发操作的性能。

MySQL的索引

索引使用的场景

索引能够轻易将查询性能提升几个数量级。

  • 对于非常小的表、大部分情况下简单的全表扫描比建立索引更高效。
  • 对于中到大型的表，索引就非常有效。
  • 但是对于特大型的表，建立和维护索引的代价将会随之增长。这种情况下，需要用到一种技术可以直接区分出需要查询的一组数据，而不是一条记录一条记录地匹配，例如可以使用分区技术。

索引是在存储引擎层实现的，而不是在服务器层实现的，所以不同存储引擎具有不同的索引类型和实现。

索引分类
特性 说明 InnoDB MyISAM MEMORY
B树索引 (B-tree indexes) 自增ID物理连续性更高，
二叉树，红黑树高度不可控		 √ √ √
R树索引 (R-tree indexes) 空间索引 √
哈希索引 (Hash indexes) 无法做范围查询 √ √
全文索引 (Full-text indexes) √ √
B Tree 原理

B-tree（多路搜索树，并不是二叉的）是一种常见的数据结构,也就是我们常说的B树。使用B-tree结构可以显著减少定位记录时所经历的中间过程，从而加快存取速度。按照翻译，B 通常认为是Balance的简称。这个数据结构一般用于数据库的索引，综合效率较高。

B-Tree:

定义一条数据记录为一个二元组 [key, data]，B-Tree 是满足下列条件的数据结构：

  • 所有叶节点具有相同的深度，也就是说 B-Tree 是平衡的；
  • 一个节点中的 key 从左到右非递减排列；
  • 如果某个指针的左右相邻 key 分别是 keyi 和 keyi+1，且不为 null，则该指针指向节点的（所有 key ≥ keyi） 且（key ≤ keyi+1）。

查找算法：首先在根节点进行二分查找，如果找到则返回对应节点的 data，否则在相应区间的指针指向的节点递归进行查找。

由于插入删除新的数据记录会破坏 B-Tree 的性质，因此在插入删除时，需要对树进行一个分裂、合并、旋转等操作以保持 B-Tree 性质。

B+Tree:

与 B-Tree 相比，B+Tree 有以下不同点：

  • 每个节点的指针上限为 2d 而不是 2d+1（d 为节点的出度）；
  • 内节点不存储 data，只存储 key；
  • 叶子节点不存储指针。

顺序访问指针:

一般在数据库系统或文件系统中使用的 B+Tree 结构都在经典 B+Tree 基础上进行了优化，在叶子节点增加了顺序访问指针，做这个优化的目的是为了提高区间访问的性能。

优势:

红黑树等平衡树也可以用来实现索引，但是文件系统及数据库系统普遍采用 B Tree 作为索引结构，主要有以下两个原因：

  • 更少的检索次数

平衡树检索数据的时间复杂度等于树高 h，而树高大致为 O(h)=O(logdN)，其中 d 为每个节点的出度。

红黑树的出度为 2，而 B Tree 的出度一般都非常大。红黑树的树高 h 很明显比 B Tree 大非常多，因此检索的次数也就更多。

B+Tree 相比于 B-Tree 更适合外存索引，因为 B+Tree 内节点去掉了 data 域，因此可以拥有更大的出度，检索效率会更高。

  • 利用计算机预读特性

为了减少磁盘 I/O，磁盘往往不是严格按需读取，而是每次都会预读。这样做的理论依据是计算机科学中著名的局部性原理：当一个数据被用到时，其附近的数据也通常会马上被使用。预读过程中，磁盘进行顺序读取，顺序读取不需要进行磁盘寻道，并且只需要很短的旋转时间，因此速度会非常快。

操作系统一般将内存和磁盘分割成固态大小的块，每一块称为一页，内存与磁盘以页为单位交换数据。数据库系统将索引的一个节点的大小设置为页的大小，使得一次 I/O 就能完全载入一个节点，并且可以利用预读特性，相邻的节点也能够被预先载入。

参考：MySQL 索引背后的数据结构及算法原理

B+Tree 索引

B+Tree 索引是大多数 MySQL 存储引擎的默认索引类型。

因为不再需要进行全表扫描，只需要对树进行搜索即可，因此查找速度快很多。除了用于查找，还可以用于排序和分组。

可以指定多个列作为索引列，多个索引列共同组成键。

B+Tree 索引适用于全键值、键值范围和键前缀查找，其中键前缀查找只适用于最左前缀查找。

如果不是按照索引列的顺序进行查找，则无法使用索引。

InnoDB 的 B+Tree 索引分为主索引和辅助索引。

主索引的叶子节点 data 域记录着完整的数据记录，这种索引方式被称为聚簇索引。因为无法把数据行存放在两个不同的地方，所以一个表只能有一个聚簇索引。

辅助索引的叶子节点的 data域记录着主键的值，因此在使用辅助索引进行查找时，需要先查找到主键值，然后再到主索引中进行查找。

哈希索引

InnoDB 引擎有一个特殊的功能叫 “自适应哈希索引”，当某个索引值被使用的非常频繁时，会在 B+Tree 索引之上再创建一个哈希索引，这样就让 B+Tree 索引具有哈希索引的一些优点，比如快速的哈希查找。

哈希索引能以 O(1) 时间进行查找，但是失去了有序性，它具有以下限制：

  • 无法用于排序与分组；
  • 只支持精确查找，无法用于部分查找和范围查找；
全文索引

MyISAM 存储引擎支持全文索引，用于查找文本中的关键词，而不是直接比较是否相等。查找条件使用 MATCH AGAINST，而不是普通的 WHERE。

全文索引一般使用倒排索引实现，它记录着关键词到其所在文档的映射。

InnoDB 存储引擎在 MySQL 5.6.4 版本中也开始支持全文索引。

空间数据索引（R-Tree）

MyISAM 存储引擎支持空间数据索引，可以用于地理数据存储。空间数据索引会从所有维度来索引数据，可以有效地使用任意维度来进行组合查询。

必须使用 GIS 相关的函数来维护数据。

索引的特点
  • 可以加快数据库的检索速度
  • 降低数据库插入、修改、删除等维护的速度
  • 只能创建在表上，不能创建到视图上
  • 既可以直接创建又可以间接创建
  • 可以在优化隐藏中使用索引
  • 使用查询处理器执行SQL语句，在一个表上，一次只能使用一个索引
索引的优点和缺点

索引的优点:

  • 可以创建唯一性索引，保证数据库表中每一行数据的唯一性
  • 大大加快数据的检索速度，这是创建索引的最主要的原因
  • 加速数据库表之间的连接，特别是在实现数据的参考完整性方面特别有意义
  • 在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间
  • 通过使用索引，可以在查询中使用优化隐藏器，提高系统的性能

索引的缺点:

  • 可以创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加
  • 索引需要占用物理空间，除了数据表占用数据空间之外，每一个索引还要占一定的物理空间，如果建立聚簇索引，那么需要的空间就会更大
  • 当对表中的数据进行增加、删除和修改的时候，索引也需要维护，降低数据维护的速度
索引失效的情况
  • 如果MySQL估计使用全表扫描比使用索引快，则不适用索引。

如果列key均匀分布在1和100之间，下面的查询使用索引就不是很好：select * from table_name where key>1 and key<90;

  • 如果条件中有or，即使其中有条件带索引也不会使用

使用or的查询语句，如果在key1上有索引而在key2上没有索引，则该查询也不会走索引。

> select * from table_name where key1='a' or key2='b'
  • 复合索引，如果索引列不是复合索引的第一部分，则不使用索引（即不符合最左前缀）

复合索引为(key1,key2),则查询而且不会使用索引。

> select * from table_name where key2='b'
  • 如果like是以 % 开始的，则该列上的索引不会被使用。

使用like的查询语句，即使key1上存在索引，也不会被使用如果列类型是字符串，那一定要在条件中使用引号引起来，否则不会使用索引。

> select * from table_name where key1 like '%a'
  • 如果列为字符串，则where条件中必须将字符常量值加引号，否则即使该列上存在索引，也不会被使用。

使用where的查询语句,key1列保存的是字符串，即使key1上有索引，也不会被使用。

select * from table_name where key1=1;
  • 如果使用MEMORY/HEAP表，并且where条件中不使用“=”进行索引列，那么不会用到索引，head表只有在“=”的条件下才会使用索引

适合建立索引的情况

下面的查询适合建立索引：

  • 如果SQL查询中出现在关键字order by、group by、distinct后面的字段，建立索引。
  • 在union等集合操作的结果集字段上，建立索引。其建立索引的目的同上。
  • 经常用作查询选择 where 后的字段，建立索引。
  • 经常用作表连接 join 的属性上，建立索引。
  • 考虑使用索引覆盖。对数据很少被更新的表，如果用户经常只查询其中的几个字段，可以考虑在这几个字段上建立索引，从而将表的扫描改变为索引的扫描。
为何选择用B+树做索引而不用B-树或红黑树

B+ 树只有叶节点存放数据，其余节点用来索引，而 B- 树是每个索引节点都会有 Data 域。所以从 InooDB 的角度来看，B+ 树是用来充当索引的，一般来说索引非常大，尤其是关系性数据库这种数据量大的索引能达到亿级别，所以为了减少内存的占用，索引也会被存储在磁盘上。

MySQL如何衡量查询效率呢？

主要是通过磁盘 IO 次数。

  • B- 树 / B+ 树 的特点就是每层节点数目非常多，层数很少，目的就是为了就少磁盘 IO 次数，但是 B- 树的每个节点都有 data 域（指针），这无疑增大了节点大小，说白了增加了磁盘 IO 次数（磁盘 IO 一次读出的数据量大小是固定的，单个数据变大，每次读出的就少，IO 次数增多，一次 IO 多耗时），而 B+ 树除了叶子节点其它节点并不存储数据，节点小，磁盘 IO 次数就少。

  • B+ 树所有的 Data 域在叶子节点，一般来说都会进行一个优化，就是将所有的叶子节点用指针串起来。这样遍历叶子节点就能获得全部数据，这样就能进行区间访问啦。在数据库中基于范围的查询是非常频繁的，而 B 树不支持这样的遍历操作。

B 树和红黑树之间的区别:

  • AVL 树和红黑树基本都是存储在内存中才会使用的数据结构。在大规模数据存储的时候，红黑树往往出现由于树的深度过大而造成磁盘 IO 读写过于频繁，进而导致效率低下的情况。之所以会出现这样的情况，是由于我们要获取磁盘上数据，需要先通过磁盘移动臂移动到数据所在的柱面，然后找到指定盘面，接着旋转盘面找到数据所在的磁道，最后对数据进行读写。磁盘IO代价主要花费在查找所需的柱面上，树的深度过大会造成磁盘IO频繁读写。根据磁盘查找存取的次数往往由树的高度所决定，所以，只要我们通过某种较好的树结构减少树的结构尽量减少树的高度，B树可以有多个子女，从几十到上千，可以降低树的高度。

  • 数据库系统的设计者巧妙利用了磁盘预读原理，将一个节点的大小设为等于一个页，这样每个节点只需要一次 I/O 就可以完全载入。为了达到这个目的，在实际实现 B-Tree 还需要在每次新建节点时，申请一个页的空间，这样就保证一个节点物理上也存储在一个页里，加之计算机存储分配都是按页对齐的，这样就实现了一个 node 只需一次 I/O。

联合索引

  1. 什么是联合索引

两个或更多个列上的索引被称作联合索引，联合索引又叫复合索引。对于复合索引：Mysql 从左到右的使用索引中的字段，一个查询可以只使用索引中的一部份，但只能是最左侧部分。

例如索引是key index (a,b,c)，可以支持[a]、[a,b]、[a,b,c] 3种组合进行查找，但不支 [b,c] 进行查找。当最左侧字段是常量引用时，索引就十分有效。

  1. 命名规则
  • 对于需要加索引的字段，要在 where 条件中
  • 数据量少的字段不需要加索引
  • 如果 where 条件中是OR关系，加索引不起作用
  • 符合最左原则
  1. 创建索引

在执行 CREATE TABLE 语句时可以创建索引，也可以单独用 CREATE INDEX 或 ALTER TABLE 来为表增加索引。

  • ALTER TABLE

ALTER TABLE 用来创建普通索引、UNIQUE 索引或 PRIMARY KEY 索引。

 ALTER TABLE table_name ADD INDEX index_name (column_list)
 ALTER TABLE table_name ADD UNIQUE (column_list)
 ALTER TABLE table_name ADD PRIMARY KEY (column_list)

其中 table_name 是要增加索引的表名，column_list 指出对哪些列进行索引，多列时各列之间用逗号分隔。索引名 index_name 可选，缺省时，MySQL将根据第一个索引列赋一个名称。另外，ALTER TABLE 允许在单个语句中更改多个表，因此可以在同时创建多个索引。

  • CREATE INDEX

CREATE INDEX 可对表增加普通索引或 UNIQUE 索引。

 CREATE INDEX index_name ON table_name (column_list)
 CREATE UNIQUE INDEX index_name ON table_name (column_list)

table_name、index_name 和 column_list 具有与 ALTER TABLE 语句中相同的含义，索引名不可选。另外，不能用 CREATE INDEX 语句创建 PRIMARY KEY 索引。

  1. 索引类型

在创建索引时，可以规定索引能否包含重复值。如果不包含，则索引应该创建为 PRIMARY KEY 或 UNIQUE 索引。对于单列惟一性索引，这保证单列不包含重复的值。对于多列惟一性索引，保证多个值的组合不重复。 PRIMARY KEY 索引和 UNIQUE 索引非常类似。

事实上，PRIMARY KEY 索引仅是一个具有名称 PRIMARY 的 UNIQUE 索引。这表示一个表只能包含一个 PRIMARY KEY，因为一个表中不可能具有两个同名的索引。 下面的SQL语句对 students 表在 sid 上添加 PRIMARY KEY 索引。 ​ ALTER TABLE students ADD PRIMARY KEY (sid)。

  1. 删除索引

可以利用 ALTER TABLE 或 DROP INDEX 语句来删除索引。类似于 CREATE INDEX 语句，DROP INDEX 可以在 ALTER TABLE 内部作为一条语句处理。

 DROP INDEX index_name ON talbe_name
 ALTER TABLE table_name DROP INDEX index_name
 ALTER TABLE table_name DROP PRIMARY KEY

其中，前两条语句是等价的，删除掉 table_name 中的索引 index_name。

第3条语句只在删除 PRIMARY KEY 索引时使用，因为一个表只可能有一个 PRIMARY KEY 索引，因此不需要指定索引名。如果没有创建 PRIMARY KEY 索引，但表具有一个或多个 UNIQUE 索引，则 MySQL 将删除第一个 UNIQUE 索引。

如果从表中删除了某列，则索引会受到影响。对于多列组合的索引，如果删除其中的某列，则该列也会从索引中删除。如果删除组成索引的所有列，则整个索引将被删除。

  1. 在什么情况下使用索引
  • 通常为了快速查找匹配WHERE条件的行。
  • 通常为了从考虑的条件中消除行。
  • 如果表有一个multiple-column索引，任何一个索引的最左前缀可以通过使用优化器来查找行。
  • 查询中与其它表关联的字，字段常常建立了外键关系
  • 查询中统计或分组统计的字段
select max(hbs_bh) from zl_yhjbqk
select qc_bh,count(*) from zl_yhjbqk group by qc_bh
  1. 注意事项
  • 创建索引

对于查询占主要的应用来说，索引显得尤为重要。很多时候性能问题很简单的就是因为我们忘了添加索引而造成的，或者说没有添加更为有效的索引导致。如果不加
索引的话，那么查找任何哪怕只是一条特定的数据都会进行一次全表扫描，如果一张表的数据量很大而符合条件的结果又很少，那么不加索引会引起致命的性能下降。但是也不是什么情况都非得建索引不可，比如性别可能就只有两个值，建索引不仅没什么优势，还会影响到更新速度，这被称为过度索引。

  • 复合索引

比如有一条语句是这样的：select * from users where area=’shanghai’ and age=17;

如果我们是在area和age上分别创建单个索引的话，由于mysql查询每次只能使用一个索引，所以虽然这样已经相对不做索引时全表扫描提高了很多效率，但是如果在area、age两列上创建复合索引的话将带来更高的效率。如果我们创建了(area, age,salary)的复合索引，那么其实相当于创建了(area,age,salary)、(area,age)、(area)三个索引，这被称为最佳左前缀特性。

因此我们在创建复合索引时应该将最常用作限制条件的列放在最左边，依次递减。

  • 索引不会包含有NULL值的列

只要列中包含有NULL值都将不会被包含在索引中，复合索引中只要有一列含有NULL值，那么这一列对于此复合索引就是无效的。所以我们在数据库设计时不要让字段的默认值为NULL。

  • 使用短索引

对串列进行索引，如果可能应该指定一个前缀长度。例如，如果有一个CHAR(255)的 列，如果在前10 个或20 个字符内，多数值是惟一的，那么就不要对整个列进行索引。短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作。

  • 排序的索引问题

mysql查询只使用一个索引，因此如果where子句中已经使用了索引的话，那么order by中的列是不会使用索引的。因此数据库默认排序可以符合要求的情况下不要使用排序操作；尽量不要包含多个列的排序，如果需要最好给这些列创建复合索引。

  • like语句操作

一般情况下不鼓励使用like操作，如果必须要使用，考虑如何使用也是一个问题。like “%name%” 不会使用索引，而like “name%”可以使用索引。

  • 不要在列上进行运算

  • 不使用NOT IN操作

NOT IN操作不会使用索引将进行全表扫描。NOT IN可以用NOT EXISTS代替

主键外键和索引的区别

定义 作用 个数
主键 唯一标识一条记录，不能有重复的，不允许为空 用来保证数据完整性 主键只能有一个
外键 表的外键是另一表的主键，外键可以有重复的，可以是空值 用来和其他表建立联系用的 一个表可以有多个外键
索引 该字段没有重复值，但可以有一个空值 是提高查询排序的速度 一个表可以有多个惟一索引

聚集索引与非聚集索引

聚集索引一定是唯一索引。但唯一索引不一定是聚集索引。

聚集索引，在索引页里直接存放数据，而非聚集索引在索引页里存放的是索引，这些索引指向专门的数据页的数据。

数据库中的分页查询

Mysql 的 limit 用法：

SELECT * FROM table LIMIT [offset,] rows | rows OFFSET offset

LIMIT 接受一个或两个数字参数。参数必须是一个整数常量。如果给定两个参数，第一个参数指定第一个返回记录行的偏移量，第二个参数指定返回记录行的最大数目。初始记录行的偏移量是 0(而不是 1) 。

最基本的分页方式：SELECT … FROM … WHERE … ORDER BY … LIMIT …

应用示例:

SELECT * FROM `video` ORDER BY `createTime`  desc LIMIT  10;

根据时间查询最近的10条数据.(降序排列)。

数据库中最大的连接数参数意义

数据库中最大的连接数参数意义:

  • SetMaxOpenConns用于设置最大打开的连接数，默认值为0表示不限制。
  • SetMaxIdleConns用于设置闲置的连接数。
  • SetConnMaxLifetime用于设置连接超时

设置最大的连接数，可以避免并发太高导致连接mysql出现too many connections的错误。设置闲置的连接数则当开启的一个连接使用完成后可以放在池里等候下一次使用。

通常设置连接连接超时是500秒:

db.SetConnMaxLifetime(time.Second * 500) //设置连接超时500秒

Redis数据库

Redis是一个速度非常快的key-value非关系型数据库，可以存储键(key)与5种不同类型的值（value）之间的映射，可以将存储在内存中的键值对数据持久化到硬盘中。和 Memcached 类似，它支持存储的 value 类型相对更多，包括string(字符串)、list(链表)、set(集合)、zset(sorted set –有序集合)和 hash（哈希类型）。这些数据类型都支持 push/pop、add/remove 及取交集并集和差集及更丰富的操作，而且这些操作都是原子性的。

Redis数据库与 Memcached 相比的不同:

  • 两者都可用于存储键值映射，彼此性能也相差无几
  • Redis 能够自动以两种不同的方式将数据写入硬盘
  • Redis 除了能存储普通的字符串键之外，还可以存储其他4种数据结构，memcached 只能存储字符串键
  • Redis 既能用作主数据库，由可以作为其他存储系统的辅助数据库

Redis应用场景:

  • 缓存、任务队列、应用排行榜、网站访问统计、数据过期处理、分布式集群架构中的session分离。

Redis特点：

  • 高并发读写,读的速度是 110000次/s（11 W次/s）,写的速度是81000次/s（8.1W 次/s）

  • 海量数据的高效存储和访问

  • 高可扩展性和高可用性

  • Redis命令大全

Redis的数据结构：

  • STRING：可以是字符串、整数或者浮点数
  • LIST：一个链表，链表上的每个节点都包含了一个字符串
  • SET：包含字符串的无序收集器（unordered collection），并且被包含的每个字符串都是独一无二、各不相同的
  • HAST：包含键值对的无序散列表
  • ZSET：字符串成员（member）与浮点数分值（score）之间的有序映射，元素的排列顺序由分值的大小决定

分库分表

简单来说，数据的切分就是通过某种特定的条件，将我们存放在同一个数据库中的数据分散存放到多个数据库（主机）中，以达到分散单台设备负载的效果，即分库分表。

数据的切分根据其切分规则的类型，可以分为如下两种切分模式。

  • 垂直（纵向）切分：把单一的表拆分成多个表，并分散到不同的数据库（主机）上。
  • 水平（横向）切分：根据表中数据的逻辑关系，将同一个表中的数据按照某种条件拆分到多台数据库（主机）上。
1. 垂直切分

垂直切分是将一张表按列切分成多个表，通常是按照列的关系密集程度进行切分，也可以利用垂直切分将经常被使用的列和不经常被使用的列切分到不同的表中。

在数据库的层面使用垂直切分将按数据库中表的密集程度部署到不同的库中，例如将原来的电商数据库垂直切分成商品数据库 payDB、用户数据库 userBD 等。

垂直切分的优点:

  1. 拆分后业务清晰，拆分规则明确
  2. 系统之间进行整合或扩展很容易
  3. 按照成本、应用的等级、应用的类型等将表放到不同的机器上，便于管理
  4. 便于实现动静分离、冷热分离的数据库表的设计模式
  5. 数据维护简单

垂直切分的缺点:

  1. 部分业务表无法关联（Join），只能通过接口方式解决，提高了系统的复杂度
  2. 受每种业务的不同限制，存在单库性能瓶颈，不易进行数据扩展和提升性能
  3. 事务处理复杂
2. 水平切分

水平切分又称为 Sharding，它是将同一个表中的记录拆分到多个结构相同的表中。

当一个表的数据不断增多时，Sharding 是必然的选择，它可以将数据分布到集群的不同节点上，从而缓存单个数据库的压力。

水平切分的优点:

  1. 单库单表的数据保持在一定的量级，有助于性能的提高
  2. 切分的表的结构相同，应用层改造较少，只需要增加路由规则即可
  3. 提高了系统的稳定性和负载能力

水平切分的缺点:

  1. 切分后，数据是分散的，很难利用数据库的Join操作，跨库Join性能较差
  2. 拆分规则难以抽象
  3. 分片事务的一致性难以解决
  4. 数据扩容的难度和维护量极大

垂直切分和水平切分的共同点:

  1. 存在分布式事务的问题
  2. 存在跨节点Join的问题
  3. 存在跨节点合并排序、分页的问题
  4. 存在多数据源管理的问题

3. Sharding策略和解决方案

Sharding策略为：

  • 哈希取模：hash(key) % NUM_DB,可以按照 userId mod 64.将数据分布在64个服务器上。

  • 范围：可以是 ID 范围也可以是时间范围，可以根据每台服务器计划存放一个亿的数据,先将数据写入服务器A.一旦服务器A写满,则将数据写入服务器B,以此类推. 这种方式的好处是扩展方便.数据在各个服务器上分布均匀。

  • 映射表：使用单独的一个数据库来存储映射关系。

主要的事物问题:

使用分布式事务来解决，比如 XA 接口。

JOIN：

可以将原来的 JOIN 查询分解成多个单表查询，然后在用户程序中进行 JOIN。

ID 唯一性：

  • 使用全局唯一 ID：GUID。
  • 为每个分片指定一个 ID 范围。
  • 分布式 ID 生成器 (如 Twitter 的 Snowflake 算法)。

主从复制与读写分离

主从复制:

主从复制主要涉及三个线程：binlog 线程、I/O 线程和 SQL 线程。

  • binlog 线程 ：负责将主服务器上的数据更改写入二进制文件（binlog）中。
  • I/O 线程 ：负责从主服务器上读取二进制日志文件，并写入从服务器的中继日志中。
  • SQL 线程 ：负责读取中继日志并重放其中的 SQL 语句。

读写分离:

主服务器用来处理写操作以及实时性要求比较高的读操作，而从服务器用来处理读操作。

读写分离常用代理方式来实现，代理服务器接收应用层传来的读写请求，然后决定转发到哪个服务器。

MySQL 读写分离能提高性能的原因在于：

  • 主从服务器负责各自的读和写，极大程度缓解了锁的争用；
  • 从服务器可以配置 MyISAM 引擎，提升查询性能以及节约系统开销；
  • 增加冗余，提高可用性。

查询性能优化

  1. 使用 Explain 进行分析

Explain 用来分析 SELECT 查询语句，开发人员可以通过分析 Explain 结果来优化查询语句。

字段有：

  • select_type : 查询类型，有简单查询、联合查询、子查询等
  • key : 使用的索引
  • rows : 扫描的行数
mysql> explain select * from user_info where id = 3\G
*************************** 1. row ***************************
           id: 3
  select_type: SIMPLE
        table: user_info
   partitions: NULL
         type: const
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 8
          ref: const
         rows: 1
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.00 sec)
  1. 优化数据访问

减少请求的数据量:

  • 只返回必要的列

最好不要使用 SELECT * 语句。

  • 只返回必要的行

使用 WHERE 语句进行查询过滤，有时候也需要使用 LIMIT 语句来限制返回的数据。

  • 缓存重复查询的数据

使用缓存可以避免在数据库中进行查询，特别要查询的数据经常被重复查询，缓存可以带来的查询性能提升将会是非常明显的。

  • 减少服务器端扫描的行数

对于查询来说，其中最有效的方式是使用索引来覆盖查询。

  1. 重构查询方式
  • 切分大查询

一个大查询如果一次性执行的话，可能一次锁住很多数据、占满整个事务日志、耗尽系统资源、阻塞很多小的但重要的查询。

DELEFT FROM info WHERE create < DATE_SUB(NOW(), INTERVAL 3 MONTH);
rows_affected = 0
do {
    rows_affected = do_query(
    "DELETE FROM info WHERE create  < DATE_SUB(NOW(), INTERVAL 3 MONTH) LIMIT 10000")
} while rows_affected > 0

  • 分解大连接查询
    将一个大连接查询（JOIN）分解成对每一个表进行一次单表查询，然后将结果在应用程序中进行关联，这样做的好处有：

  • 让缓存更高效。对于连接查询，如果其中一个表发生变化，那么整个查询缓存就无法使用。而分解后的多个查询，即使其中一个表发生变化，对其它表的查询缓存依然可以使用。

  • 分解成多个单表查询，这些单表查询的缓存结果更可能被其它查询使用到，从而减少冗余记录的查询。

  • 减少锁竞争；

  • 在应用层进行连接，可以更容易对数据库进行拆分，从而更容易做到高性能和可扩展。

  • 查询本身效率也可能会有所提升。例如下面的例子中，使用 IN() 代替连接查询，可以让 MySQL 按照 ID 顺序进行查询，这可能比随机的连接要更高效。

SELECT * FROM vido
JOIN video_post ON video_post.video_id=video.id
JOIN post ON video_post.post_id=post.id
WHERE video.video='mysql';
SELECT * FROM video WHERE video='mysql';
SELECT * FROM video_post WHERE video_id=1234;
SELECT * FROM post WHERE post.id IN (123,456,567,9098,8904);

锁类型

MySQL的引擎InnoDB的加锁问题，一直是一个面试中常问的话题。例如，数据库如果有高并发请求，如何保证数据完整性？产生死锁问题如何排查并解决？在工作过程中，也会经常用到，乐观锁，排它锁等。

注意：MySQL 是一个支持插件式存储引擎的数据库系统。接下来我们的分析，都是基于 InnoDB 存储引擎，其他引擎的表现，会有较大的区别。

  • 版本查看
    select version();
  • 存储引擎查看

MySQL 给开发者提供了查询存储引擎的功能,可以通过下面的命令查看：

SHOW ENGINES
  • 乐观锁

通过数据版本（Version）记录机制实现，这是乐观锁最常用的一种实现方式。什么是数据版本？即为数据增加一个版本标识，一般是通过为数据库表增加一个数字类型的 “version” 字段来实现。当读取数据时，将version字段的值一同读出，数据每更新一次，对此version值加1。当我们提交更新的时候，判断数据库表对应记录的当前版本信息与第一次取出来的version值进行比对，如果数据库表当前版本号与第一次取出来的version值相等，则予以更新，否则认为是过期数据。

应用示例:

  1. 数据库表设计
select id,value,version from TABLE where id=#{id}
  1. 每次更新表中的value字段时，为了防止发生冲突，需要这样操作
    update TABLE
set value=2,version=version+1
where id=#{id} and version=#{version};
  • 悲观锁

与乐观锁相对应的就是悲观锁了。悲观锁就是在操作数据时，认为此操作会出现数据冲突，所以在进行每次操作时都要通过获取锁才能进行对相同数据的操作，这点跟 Java 中的 synchronized 很相似，所以悲观锁需要耗费较多的时间。另外与乐观锁相对应的，悲观锁是由数据库自己实现了的，要用的时候，我们直接调用数据库的相关语句就可以了。

说到这里，由悲观锁涉及到的另外两个锁概念就出来了，它们就是共享锁与排它锁。共享锁和排它锁是悲观锁的不同的实现，它俩都属于悲观锁的范畴。

应用示例:

排它锁:

要使用悲观锁，我们必须关闭 mysql 数据库的自动提交属性，因为 MySQL 默认使用 autocommit 模式，也就是说，当你执行一个更新操作后，MySQL 会立刻将结果进行提交。

我们可以使用命令设置 MySQL 为非 autocommit 模式：

set autocommit=0;
# 设置完autocommit后，我们就可以执行我们的正常业务了。具体如下：

# 1. 开始事务 (三者选一就可以)
begin; / begin work; / start transaction;

# 2. 查询表信息
select status from TABLE where id=1 for update;

# 3. 插入一条数据
insert into TABLE (id,value) values (2,2);

# 4. 修改数据为
update TABLE set value=2 where id=1;

# 5. 提交事务
commit;/commit work;
  • 共享锁

共享锁又称读锁（read lock），是读取操作创建的锁。其他用户可以并发读取数据，但任何事务都不能对数据进行修改（获取数据上的排他锁），直到已释放所有共享锁。

如果事务 T 对数据 A 加上共享锁后，则其他事务只能对 A 再加共享锁，不能加排他锁。获得共享锁的事务只能读数据，不能修改数据。

#三者选一就可以
begin; / begin work; / start transaction;

SELECT * from TABLE where id = 1  lock in share mode;

然后在另一个查询窗口中，对 id 为 1 的数据进行更新：

update TABLE set name="https://www.youtube.com" where id =1;

此时，操作界面进入了卡顿状态，过了超时间，提示错误信息。如果在超时前，执行 commit，此更新语句就会成功。

[SQL]update  test_one set name="https://www.youtube.com" where id =1;
[Err] 1205 - Lock wait timeout exceeded; try restarting transaction

加上共享锁后，也提示错误信息:

update test_one set name="www.souyunku.com" where id =1 lock in share mode;
[SQL]update  test_one set name="https://www.youtube.com" where id =1 lock in share mode;
[Err] 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'lock in share mode' at line 1

在查询语句后面增加 lock in share mode，MySQL 会对查询结果中的每行都加共享锁，当没有其他线程对查询结果集中的任何一行使用排他锁时，可以成功申请共享锁，否则会被阻塞。其他线程也可以读取使用了共享锁的表，而且这些线程读取的是同一个版本的数据。

加上共享锁后，对于 update,insert,delete 语句会自动加排它锁。

  • 排它锁

排他锁(exclusive lock)也叫又称写锁(writer lock)。

排它锁是悲观锁的一种实现，在上面悲观锁也介绍过。

若在一个事务上对数据对象 A 加上 X 锁，该事务可以读 A 也可以修改 A，其他事务不能再对 A 加任何锁，直到该事物释放 A 上的锁。这保证了其他事务在该事物 释放 A 上的锁之前不能再读取和修改 A。排它锁会阻塞所有的排它锁和共享锁。

读取为什么要加读锁呢：防止数据在被读取的时候被别的线程加上写锁

使用方式：在需要执行的语句后面加上 for update 就可以了

  • 行锁

行锁又分共享锁和排他锁,由字面意思理解，就是给某一行加上锁，也就是一条记录加上锁。

注意：行级锁都是基于索引的，如果一条SQL语句用不到索引是不会使用行级锁的，会使用表级锁。

共享锁：

共享锁又叫做读锁，所有的事务只能对其进行读操作不能写操作，加上共享锁后在事务结束之前其他事务只能再加共享锁，除此之外其他任何类型的锁都不能再加了。

#结果集的数据都会加共享锁
SELECT * from TABLE where id = "1"  lock in share mode;

排他锁：

若某个事物对某一行加上了排他锁，只能这个事务对其进行读写，在此事务结束之前，其他事务不能对其进行加任何锁，其他进程可以读取，不能进行写操作，需等待其释放。

select status from TABLE where id=1 for update;

由于对于表中 id 字段为主键，就也相当于索引。执行加锁时，会将 id 这个索引为 1 的记录加上锁，那么这个锁就是行锁。

  • 表锁

如何加表锁?

通常在引擎Innodb下的行锁是在有索引的情况下,没有索引的表是锁定全表的。

Innodb中的行锁与表锁：

前在 Innodb引擎中既支持行锁也支持表锁，那么什么时候会锁住整张表，什么时候或只锁住一行呢？ 只有通过索引条件检索数据，InnoDB 才使用行级锁，否则，InnoDB 将使用表锁！

在实际应用中，要特别注意 InnoDB 行锁的这一特性，不然的话，可能导致大量的锁冲突，从而影响并发性能。

行级锁都是基于索引的，如果一条 SQL 语句用不到索引是不会使用行级锁的，会使用表级锁。行级锁的缺点是：由于需要请求大量的锁资源，所以速度慢，内存消耗大。

  • 死锁

死锁（Deadlock） 所谓死锁：是指两个或两个以上的进程在执行过程中，因争夺资源而造成的一种互相等待的现象，若无外力作用，它们都将无法推进下去。此时称系统处于死锁状态或系统产生了死锁，这些永远在互相等待的进程称为死锁进程。由于资源占用是互斥的，当某个进程提出申请资源后，使得有关进程在无外力协助下，永远分配不到必需的资源而无法继续运行，这就产生了一种特殊现象死锁。

解除正在死锁的状态有两种方法：

第一种：

  1. 查询是否锁表
    show OPEN TABLES where In_use > 0;
  2. 查询进程（如果您有SUPER权限，您可以看到所有线程。否则，您只能看到您自己的线程）
    show processlist
  3. 杀死进程id（就是上面命令的id列）
    kill id

第二种：

  1. 查看当前的事务
    SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX;
  2. 查看当前锁定的事务
    SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;
  3. 查看当前等锁的事务
    SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;
    杀死进程
    kill PID
    如果系统资源充足，进程的资源请求都能够得到满足，死锁出现的可能性就很低，否则就会因争夺有限的资源而陷入死锁。其次，进程运行推进顺序与速度不同，也可能产生死锁。 产生死锁的四个必要条件：

  • 互斥条件：一个资源每次只能被一个进程使用。

  • 请求与保持条件：一个进程因请求资源而阻塞时，对已获得的资源保持不放。

  • 不剥夺条件：进程已获得的资源，在末使用完之前，不能强行剥夺。

  • 循环等待条件：若干进程之间形成一种头尾相接的循环等待资源关系。

虽然不能完全避免死锁，但可以使死锁的数量减至最少。将死锁减至最少可以增加事务的吞吐量并减少系统开销，因为只有很少的事务回滚，而回滚会取消事务执行的所有工作。由于死锁时回滚而由应用程序重新提交。

我们可以通过下面的方法优化最大限度地降低死锁:

  • 按同一顺序访问对象
  • 避免事务中的用户交互
  • 保持事务简短并在一个批处理中
  • 使用低隔离级别
  • 使用绑定连接

参考资料

最后编辑: kuteng  文档更新时间: 2021-09-14 14:46   作者：kuteng