MySQL

基本概念

• 主键、外键、唯一键、自增主键
• 数据库范式
• 内连接、左右外连接
• 存储过程
• 触发器
• 视图和游标
• SQL手写和执行顺序
• 二进制文件binlog
• drop、truncate、delete区别
• like %和-的区别
• count(*)、count(1)、count(column)的区别

索引及优化

• 索引
• 聚集索引和非聚集索引
• 优化

事务

• 事务的四大特性ACID
• 事务的并发
• 事务的隔离级别

存储引擎

• MyISAM和InnoDB区别
• MySQL主从复制

数据库锁

• mysql中锁的级别
• 乐观锁和悲观锁
• 行锁怎么实现
• 间隙锁

主键 外键 唯一键 自增主键

• 主键 PRIMARY KEY
  • 数据库表中对储存数据对象予以唯一和完整标识的数据列或属性的组合。一个数据列只能有一个主键，且主键的取值不能缺失，即不能为空值（NULL）。
• 外键 FOREIGN KEY
  • 在一个表中存在的另一个表的KEY(可以是另外一个表的主键或唯一键)称此表的外键。
• 唯一键 UNIQE
  • 保证字段的唯一性，可以为空
• 自增主键 AUTO_INCREMENT
  • 自增列每增加一行自动增量，每个表只能有一个自增列，
  • 一般将主键设置为自增列，又叫自增主键
  • 只能是数值型，delete删除自增列后，若再次插入，从断开处插入
  • truncate删除后，再次插入从1开始

数据库范式

范式之间的关系，第一范式包含第二范式，第二范式包含第三范式。这里只举反例，因为实际的数据在设计数据库表的时候，范式都是相对的。

• 第一范式 1NF
  • 确保每列保持原子性，所有字段值都是不可分解的原子值
  • 比如，学生包括学号、姓名；地址包括省份，城市。若在使用中经常访问学号，城市等字段，则建表时，不能把学生、地址作为字段，若将其作为字段，则不满足第一范式。
  • 将学号、城市等作为字段，符合第一范式（相对来讲，因为城市还是可以可分的，只是平时访问的多，也不会访问城市以下的字段）
• 第二范式 2NF
  • **需要确保数据库表中的每一列都和主键相关，而不能只与主键的某一部分相关（主要针对联合主键而言）；**或者说每一个非主属性都完全函数依赖与任何一个主键
  • 比如，学号，课程号，学院，成绩建一个数据库表。此时（学号，课程号）为主键，也称联合主键，此时只能当学号，课程号两者都确定的情况下，才能确定成绩。但学院只与学号有关，因此部分依赖与联合主键，这种情况不符合第二范式。
• 第三范式 3NF
  • **数据表中的每一列数据都和主键直接相关，而不能间接相关；**或每一个非主属性既不传递依赖主键，也不部分依赖主键。
  • 订单表【Order】（OrderID，OrderDate，CustomerID，CustomerName，CustomerAddr，CustomerCity）主键是（OrderID），CustomerName，CustomerAddr，CustomerCity 直接依赖的是 CustomerID（非主键列），而不是直接依赖于主键，它是通过传递才依赖于主键，所以不符合3NF。

内连接、左右外连接

• 内连接(inner join): 只连接匹配的行
• 左外连接(left join): 包含左边表的全部行（不管右边的表中是否存在与它们匹配的行），以及右边表中全部匹配的行
• 右外连接(right join): 包含右边表的全部行（不管左边的表中是否存在与它们匹配的行），以及左边表中全部匹配的行

存储过程

存储过程是是事先经过编译，并存储在数据库中的一段SQL语句的集合。 通俗讲，存储过程是由一些SQL语句组成的代码块，这些SQL语句代码像一个方法一样实现一些功能（对单表或多表的增删改查）。

优点

• 存储过程只在创建时进行编译，以后每次执行存储过程都不需再重新编译，而一般 SQL语句每执行一次就编译一次，所以使用存储过程可提高数据库执行效率；
• 当SQL语句有变动时，可以只修改数据库中的存储过程而不必修改代码；
• 通过存储过程能够使**没有权限的用户在控制之下**间接地存取数据库，从而确保数据的安全。

触发器

触发器是一种特殊的存储过程，主要是通过事件来触发而被执行的。如，某表上的触发器上包含对另一个表的数据操作，售出一件商品，仓库库存-1

视图和游标

• 视图
  • 一种虚拟的表，具有和物理表相同的功能。可以对视图进行增，改，查，操作，试图通常是有一个表或者多个表的行或列的子集。对视图的修改会影响基本表。它使得我们获取数据更容易，相比多表查询。
• 游标
  • 对查询出来的结果集作为一个单元来有效的处理。游标可以定在该单元中的特定行，从结果集的当前行检索一行或多行。可以对结果集当前行做修改。一般不使用游标，但是需要逐条处理数据的时候，游标显得十分重要。

SQL手写和执行顺序

• 手写

  • select
  • from
  • join
  • on
  • where
  • group by
  • having
  • order by
  • limit

• 执行顺序

  • from
  • on
  • join, where
  • group by
  • having
  • select
  • order by
  • limit

二进制文件binlog

用来记录对mysql数据更新或潜在发生更新的SQL语句，并以”事务”的形式保存在磁盘中

作用

• 复制
  • 用于主从复制，读写分离，Master把它的二进制日志传递给slaves并回放(在slave上执行一遍)来达到master-slave数据一致的目的
• 恢复
  • 让mysql将保存在binlog日志中指定段落区间的sql语句逐个重新执行一次

drop truncate delete区别

• drop
  • 直接删除表
• truncate
  • 清空整个表数据，并不删除表
  • 若删除自增列，再次插入从1开始
• delete
  • 可以通过where删除某一行的数据
  • 删除自增列，再次插入从断点开始

like %和-的区别

%不限制通配的个数，-仅仅是一个字符

count(*)、count(1)、count(column)的区别

• count(*)对行的数目进行计算,包含NULL
• count(1)这个用法和count(*)的结果是一样的，如果表没有主键,那么count(1)比count(*)快
• count(column)对特定的列的值具有的行数进行计算,不包含NULL值。
• 任何时候count(*)都是最优选择

索引

概念

索引是对数据进行排序并快速查找的数据结构，主要功能是排序和查找

数据结构

B/B+树，hash索引，存储引擎MyISAM和InnoDB使用的B/B+树，MEMORY/Heap有hash和B/B+，默认是hash

• B/B+树，hash索引的区别

  • hash适用于等值查找的情况，不能进行范围查找；hash索引在任何时候都不能避免表扫描
  • hash当有大量重复键值的情况，由于hash冲突，性能并不一定就会比B-Tree索引高

• 为什么B+树比B树更适合文件系统索引

  • B+树只要遍历叶子节点就可以实现整棵树的遍历，而且在数据库中基于范围的查询是非常频繁的，而B树只能中序遍历所有节点，效率太低
  • B+树的非叶子结点中只存放关键字的信息，没有存放关键字具体信息，因此结点更小。如果把所有同一内部结点的关键字存放在同一盘块中，那么盘块所能容纳的关键字数量也越多。一次性读入内存中的需要查找的关键字也就越多，相对来说IO读写次数也就降低了；
  • 由于内部结点并不是最终指向文件内容的结点，而只是叶子结点中关键字的索引，所以，任何关键字的查找必须走一条从根结点到叶子结点的路。所有关键字查询的路径长度相同，导致每一个数据的查询效率相当

索引分类

• 单值索引
  • 一个索引值包含单个列
• 复合索引
  • 索引包含多个列
• 唯一索引
  • 索引列必须唯一 ，一般只是为了防止重复
  • 主键索引是唯一索引的特殊类型,主键自动建立索引，主键索引可以既防止重复，又提高访问速度
• 非唯一索引
  • 普通索引，为了提高访问速度，索引中的值可重复

什么样的字段适合创建索引

• 主键自动建立唯一索引
• 经常作查询选择的字段
• 经常作表连接的字段，比如外键
• 经常出现在order by, group by, distinct 后面排序或分组的字段

哪些不适合建立索引

• 数据记录太少的表 <300w
• 经常增删改的表
• 数据重复且分布平均的表字段，如性别和国籍

索引的缺点

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

最左前缀原则

• 在创建联合索引的时候，需要做联合索引多个字段之间顺序你们是如何选择的
  • 把最频繁，识别度最高的放在最前面，因为最左前缀原则
• 最左前缀原则：顾名思义，就是最左优先，上例中我们创建了lname_fname_age多列索引,相当于创建了(lname)单列索引，(lname,fname)组合索引以及(lname,fname,age)组合索引。

聚集索引和非聚集索引

聚集索引和非聚集索引的根本区别是表记录的排列顺序和与索引的排列顺序是否一致。聚集索引的逻辑顺序和物理顺序相同，非聚集索引则不同。

B+树叶子结点可以存哪些数据

• 聚集索引的叶节点就是数据节点，而非聚集索引的叶节点仍然是索引节点，只不过其包含一个指向对应数据块的指针
• 叶子结点存储的是主键整行数据的是聚簇索引，叶子结点存储的仅仅是主键，然后通过指针指向具体信息的是非聚簇索引。

优化

索引优化

• 全值匹配我最爱
  • 创建索引后，尽量在where筛选条件中使用索引列
• 最左前缀要遵守
  • 建立索引，将最常用的放在最左侧
• 带头大哥不能死
  • 查询从索引最左列开始 ，否则全部**失效**
• 中间兄弟不能断
  • 不能跳过索引中的列，否则部分**失效**
• 索引列上少计算
  • 不在索引列上做任何操作，比如计算或类型转换，有些VARCHAR不写引号，sql会默认转换，但索引**失效**
• 范围之后全失效
  • 用<或>时，当前索引不失效，范围条件右边的列**失效**
• LIKE百分写最右
  • LIKE百分号不要写在最左侧，否则索引**失效**
• 覆盖索引不写*
  • 当查询索引列时，select后面不写*，而是写索引列
• 不等空值还有or
  • 尽量不使用！=或<>符号，不使用is NULL或is not NULL，少用or，否则索引**失效**
• VARCHAR引号不能丢
  • 字符串不用单引号会**失效**，相当于索引列做自动类型转换

查询优化

• 慢查询步骤和过程
  • 开启慢查询
    • set slow_query_log = 1慢查询开启状态。
    • slow_query_log_file 慢查询日志存放的位置（这个目录需要MySQL的运行帐号的可写权限，一般设置为MySQL的数据存放目录）。
  • 设置超时阈值，比如5s或10s，超时的sql语句会放在慢日志中
    • long_query_time 查询超过多少秒才记录。
  • cat查看慢日志
  • 用explain分析慢sql语句
• explain优化，实际上模拟优化器执行sql语句，查看mysql如何执行你的sql语句
  • table：显示这一行的数据是关于哪张表的
  • type：这是重要的列，显示连接使用了何种类型。从最好到最差的连接类型为const、eq_ref、ref、range、index和ALL，一般来说到range不错，最好到ref
    • all:full table scan ;MySQL将遍历全表以找到匹配的行；
    • index: index scan; index 和 all的区别在于index类型只遍历索引；
    • range：索引范围扫描，对索引的扫描开始于某一点，返回匹配值的行，常见与between ，等查询；
    • ref：非唯一性索引扫描，返回匹配某个单独值的所有行，常见于使用非唯一索引即唯一索引的非唯一前缀进行查找；
    • eq_ref：唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配，常用于主键或者唯一索引扫描；
    • const，system：当MySQL对某查询某部分进行优化，并转为一个常量时，使用这些访问类型。如果将主键置于where列表中，MySQL就能将该查询转化为一个常量。
  • possible_keys：显示可能应用在这张表中的索引。如果为空，没有可能的索引。可以为相关的域从WHERE语句中选择一个合适的语句
  • key： 实际使用的索引。如果为NULL，则没有使用索引。很少的情况下，MySQL会选择优化不足的索引。这种情况下，可以在SELECT语句中使用USE INDEX（indexname）来强制使用一个索引或者用IGNORE INDEX（indexname）来强制MySQL忽略索引
  • key_len：使用的索引的长度。在不损失精确性的情况下，长度越短越好
  • rows：MySQL认为必须检查的用来返回请求数据的行数
  • Extra：关于MySQL如何解析查询的额外信息
    • Using temporary和Using filesort，意思MySQL根本不能使用索引，结果是检索会很慢。
    • Using index表示不错的信息，使用了覆盖索引
• 根据explain的结果，进行索引优化

事务的四大特性ACID

事务

事务是用户自定义的一个数据库操作序列，这些操作要么全做，要么全不做，是一个不可分割的工作单位。在关系数据库中，事务可以是一条SQL语句，也可以是一组SQL语句。

四大特性ACID

事务具有4个基本特征，分别是：原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）、持久性（Duration），简称ACID。

• 原子性
  • 事务所包含的一系列数据库操作要么全部成功执行，要么全部回滚
  • 事务的操作如果成功就必须要完全应用到数据库，如果操作失败则不能对数据库有任何影响
• 一致性
  • 事务必须使数据库从一个一致性状态变换到另一个一致性状态
  • 一个事务执行之前和执行之后都必须处于一致性状态。
• 隔离性
  • 并发执行的事务之间不能相互影响
  • 当多个用户并发访问数据库时，比如操作同一张表时，数据库为每一个用户开启的事务，不能被其他事务的操作所干扰
• 持久性
  • 指一个事务一旦被提交了，那么对数据库中的数据的改变就是永久性的
  • 即便是在数据库系统遇到故障的情况下也不会丢失提交事务的操作。

事务的并发

从理论上来说, 事务应该彼此完全隔离, 以避免并发事务所导致的问题，然而, 那样会对性能产生极大的影响, 因为事务必须按顺序运行， 在实际开发中, 为了提升性能, 事务会以较低的隔离级别运行，这样会带来一些问题。

• 脏读
  • 一个事务读取了另一个事务未提交的数据；
  • 事务A读取事务B更新的数据，然后事务B回滚，此时事务A读到的是脏数据
• 不可重复读
  • 不可重复读的**重点是修改**，同样条件下两次读取结果不同，也就是说，被读取的数据可以被其它事务修改；
  • 事务 A 多次读取同一数据，事务 B 在事务A多次读取的过程中，对数据作了更新并提交，导致事务A多次读取同一数据时，结果因此本事务先后两次读到的数据结果会不一致
• 幻读
  • 幻读的**重点在于新增或者删除**，同样条件下两次读出来的记录数不一样。
  • 事务A统计表中的数据，此时事务B想表中添加或删除了数据，当事务A再次统计表中的数据时，发现两次的记录不一样。
• 不可重复读和幻读的区别
  • 不可重复读侧重于修改，幻读侧重于新增或删除。
  • 解决不可重复读的问题只需锁住满足条件的行，解决幻读需要锁表。

事务的隔离级别

• 读未提交。最低的隔离级别，会产生脏读，不可重复读，幻读问题
• 读提交。会产生不可重复读，幻读问题
• 可重复读。会产生幻读问题
• 串行化。最高的隔离级别，在这个隔离级别下，不会产生任何异常。并发的事务，就像事务是在一个个按照顺序执行一样

Mysql中默认的事务隔离级别

可重复读的事务本身是会发生幻读，但MySQL中默认事务隔离级别是“可重复读”，不会发生脏读，可重复读和幻读的情况，因为可以通过Next-Key进行控制，Next-Key锁是行锁和GAP（间隙锁）的合并

• 事务隔离级别：未提交读时，写数据只会锁住相应的行。
• 事务隔离级别为：可重复读时，写数据会锁住整张表。
• 事务隔离级别为：串行化时，读写数据都会锁住整张表。

MyISAM和InnoDB区别

在MySQL 5.5之前，MyISAM是mysql的默认数据库引擎，之后的版本是InnoDB。

InnoDB

• 支持事务
• 有行级锁定和外键约束
• 不支持FULLTEXT类型的索引
• 没有保存表的行数
• 叶子结点是聚集索引

MyISAM

• 不支持事务
• 不支持行锁和外键，因此当INSERT或UPDATE数据时即写操作需要锁定整个表，效率便会低一些
• 叶子结点是非聚集索引

区别

• 事务
  • InnoDB支持，MyISAM不支持
• 行数
  • InnoDB没有保存表的行数，MyISAM保存了表的行数，可以直接读取
• 索引存储
  • InnoDB是聚集索引，MyISAM是非聚集索引
• 外键
  • InnoDB支持，MyISAM不支持
• 锁
  • InnoDB支持行锁，表锁。行锁可以提高多用户并发操作，但InnoDB的行锁，只是在WHERE的主键是有效的，非主键的WHERE都会锁全表的
  • MyISAM支持表锁

MySQL主从复制

原理

• 主从复制需要三个线程来完成
  • 主库中有一个binary-log dump线程，当从库连接主库时，主库会创建该线程，用于给从库发送二进制文件信息，其中读取二进制文件信息的时候会加锁
  • 从库中有两个线程，一个是从节点I/O线程，一个是SQL线程。其中从节点I/O线程接收主节点线程发来的二进制文件信息，存入到中继日志文件（relay-log），SQL线程负责执行中继日志文件中的SQL操作
• Master将数据改变记录到二进制日志(binary log)中
• Slave上面的IO进程连接上Master，并请求从指定日志文件的指定位置（或者从最开始的日志）之后的日志内容
• Master接收到来自Slave的IO进程的请求
  • 负责复制的IO进程会根据请求信息读取日志指定位置之后的日志信息，返回给Slave的IO进程。
  • 返回信息中除了日志所包含的信息之外，还包括本次返回的信息已经到Master端的bin-log文件的名称以及bin-log的位置
• Slave的IO进程接收到信息后，将接收到的日志内容依次添加到Slave端的relay-log文件的最末端，并将读取到的Master端的bin-log的文件名和位置记录到master-info文件中，以便在下一次读取的时候能够清楚的告诉Master从某个bin-log的哪个位置开始往后的日志内容
• Slave的Sql进程检测到relay-log中新增加了内容后，会马上解析relay-log的内容成为在Master端真实执行时候的那些可执行的内容，并在自身执行，从而实现主从数据的一致

读写分离

基于主从复制架构，简单来说，就搞一个主库，挂多个从库，然后我们就单单只是写主库，然后主库会自动把数据给同步到从库上去。

三种复制方式

• 同步
  • master的变化，必须等待slave-1,slave-2,…,slave-n完成后才能返回。 这样，显然不可取，也不是MySQL复制的默认设置。比如，在WEB前端页面上，用户增加了条记录，需要等待很长时间。
• 异步
  • master只需要完成自己的数据库操作即可。至于slaves是否收到二进制日志，是否完成操作，不用关心,MySQL的默认设置，这样可能会丢失数据

• 半同步

  • master一般至少有两个slave，主库写入 binlog 日志之后，就会将强制此时立即将数据同步到从库，从库将日志写入自己本地的 relay log 之后，接着会返回一个 ack 给主库，主库接收到至少一个从库的 ack 之后才会认为写操作完成了。

• 注意

  • 从库同步主库数据的过程是串行化的，也就是说主库上并行的操作，在从库上会串行执行。
    • 由于从库从主库拷贝日志以及串行执行SQL 的特点，在高并发场景下，从库的数据一定会比主库慢一些，是有延时的
    • 经常出现，刚写入主库的数据可能是读不到的，要过几十毫秒，甚至几百毫秒才能读取到。
  • 解决思路
    • 从库中开启多线程并行操作

mysql中的锁

读写锁

• 读锁会阻塞写，但不会阻塞读
• 写锁会阻塞读和写

行页表锁

• 对表中的记录加锁，叫做记录锁，又称行锁，行锁只锁定一行，偏写
• 表锁会锁定整个表，偏读
• 页锁在行锁和表锁之间

InnoDB加锁

• 对于UPDATE、DELETE和INSERT语句，InnoDB会自动给涉及及数据集加排他锁
• 对于普通SELECT语句，InnoDB不会加任何锁
• 事务可以通过以下语句显示给记录集加共享锁或排锁。

//共享锁（Ｓ）
SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE
//排他锁（X）
SELECT * FROM table_name WHERE ... FOR UPDATE

乐观锁和悲观锁

悲观锁

先获取锁，再进行业务操作，悲观的认为所有的操作均会导致并发安全问题，因此要先确保获取锁成功再进行业务操作。select ... for update 实现悲观锁

乐观锁

先进行业务操作，再获取锁，一般的做法是在需要锁的数据上增加一个版本号，或者时间戳，提交更新时，时间戳或版本号必须大于当前版本才提交更新

SELECT data AS old_data, version AS old_version FROM …;
//根据获取的数据进行业务操作，得到new_data和new_version
UPDATE SET data = new_data, version = new_version WHERE version = old_version
if (updated row > 0) {
    // 乐观锁获取成功，操作完成
} else {
    // 乐观锁获取失败，回滚并重试
}

使用场景

一般情况下，读多写少更适合用乐观锁，读少写多更适合用悲观锁。乐观锁在不发生取锁失败的情况下开销比悲观锁小，但是一旦发生失败回滚开销则比较大，因此适合用在取锁失败概率比较小的场景，可以提升系统并发性能。

行锁怎么实现

• 显示系统上行锁的争夺情况show status like 'innodb_row_lock%';
• 只有通过索引条件检索数据，InnoDB才使用行级锁，否则，InnoDB将使用表锁

//显式锁定
set auoto_commit = 0;
begin;
//加索引
 ALTER TABLE account ADD INDEX index_user_id (user_id);
//检索索引
SELECT * FROM account WHERE user_id = 1 FOR UPDATE
commit;

间隙锁

概念

• 当我们使用范围条件而不是相等条件查询数据时，InnoDB会把符合条件的数据记录索引项都加锁，此时键值在条件范围内，但并不存在的数据记录称为间隙。
• 比如检索1-3之间的数据，但表中并没有数据2，此时InnoDB会对这个间隙也加锁
• 若范围检索不提交，另一个对间隙进行操作（比如添加2）会被阻塞，无法插入数据，导致性能变差
• 在使用范围条件检索并锁定记录时，InnoDB这种加锁机制会阻塞符合条件范围内键值的并发插入，这往往会造成严重的锁等待

作用

• 间隙锁的主要作用是为了防止出现幻读

解除间隙锁

• 修改系统参数innodb_locks_unsafe_for_binlog=on就可以关闭改间隙锁机制，该值默认为off
• 修改事务隔离级别为read-committed也可以避免间隙锁