MySQL

drop delete 和truncate区别

  • (1) Drop是直接删掉表以及所有数据，将表所占空间全部释放掉。
    (2) Delete操作不会返回数据所占空间，可以与where一起使用，不指定where则默认删除表中所有记录。
    (3) Truncate会删掉表中所有记录，且返回数据所占空间。
    (4) Drop和truncate是DDL语句，隐式提交，不能回滚。Delete是DML语句，可以提交。
    (5）运行速度 drop>truncate>delete

in和exists两者的区别和性能影响

  • ① Mysql中的in把外表和内表做hash连接，而exists语句是对外表做loop循环，每次loop循环再对内表进行查询。
  • ② 子表数据量比外表数据量少，使用in。
  • ③ 子表数据量比外表数据量大，使用exists。
  • ④ 子表与外表数据量大小差不多，用in与exists的效率相差不大。
  • ⑤ Not in内外表都要进行全表扫描，not exists的子查询依然能用到表上索引。

MySQL分库分表

  • 按时间分表，针对有较强时效性的数据，如微博发送记录，微信消息记录等。将6个月前或一年前的数据切出去放在另外一张表。
  • 按区间范围分表。一般在有严格的自增id需求上，比如按照id水平分表。
  • Hash取模，对用户id进行hash取模，分配到不同数据库上。

MySQL分库分表后面临的问题

  • 分库分表后的事务变成分布式事务了。
  • 跨库join,无法join位于不同分库的表。解决办法：字段冗余或建立一个全局表，将基础数据所有库都拷贝一份。

MySQL中的死锁

  • 当事务试图以不同顺序锁定资源时，就可能产生死锁。InnoDB存储引擎能检测到死锁的循环依赖并立即返回一个错误。
  • 例子： 事务A先对id=1这行数据加锁， 事务B对id=2这行数据加锁 事务A再对id=2这行数据加锁 事务B再对id=1这行数据加锁，此时产生了死锁，innodb会自动回滚事务B.
  • InnoDB目前处理死锁的方法是，将持有最少排他锁的事务进行回滚。所以事务型应用程序在设计时必须考虑如何处理死锁，多数情况下只需要重新执行因死锁回滚的事务即可。
  • 若涉及到外部锁或表锁的情况下，innodb不能完全自动检测到死锁，需要通过设置锁等待超时参数来解决。

InnoDB锁机制

  • (1) 共享/排他锁（行锁）

    • ① 共享锁（S锁）即读锁，允许其他事务可以读，可以加S锁，但不能加X锁，直到S锁释放。
    • ② 排他锁（X锁），又称写锁，允许其他事务不加锁的读，但不允许加S锁或X锁，直到X锁释放。
    • ③ Innodb中update,delete,insert都会自动给涉及数据加上排他锁，select默认不会加任何锁类型。
    • 只有S锁与S锁是兼容的，其他组合都是不兼容的。
    • select … for update 对读取的行记录加一个X锁，其他事务想在这些行加任何锁都会被阻塞。
    • select lock in share mode 对读取的行记录加S锁，其他事务可以向被锁定的行记录加S锁，但对于加X锁会被阻塞。

  • (2) 意向锁（表锁）

    • ① 意向锁指未来的某个时刻，事务可能要加S/X锁了，先提前声明一个意向。意向锁是一个表级别的锁。
    • ② 意向锁又可以分为意向共享锁和意向排他锁。前者表示事务意向对表中的某些行加S锁。后者表示事务有意向对表中的某些行加X锁。

  • (3) 间隙锁(Gap Locks)

    • ① 当使用范围条件去检索数据时，InnoDB会给复合条件的已有数据的索引项加锁。对于键值在条件范围内，但并不存在的记录叫做间隙，InnoDB也会对这个间隙加锁。
    • ② 可以防止幻读。

  • (4) 记录锁（Record Locks)

    • ① 为某行记录加锁，封锁该行的索引记录，加锁必须在唯一索引列或主键列。
    • ② 同时查询语句必须为精准匹配（=）不能为> < like等，否则会退化为临键锁。

  • (5) 临键锁（Next-Key Locks)

    • ① 一种特殊的间隙锁，每个数据行的非唯一索引列都会存在一把临键锁，当某个事务持有该数据行的临键锁时，会锁住一段左开右闭的区间的数据。在唯一索引列上不存在临键锁，会降级为记录锁，仅锁住索引本身，不是范围。
    • ② 根据非唯一索引对记录行进行UPDATE\FOR UPDATE\LOCK IN SHARE MODE操作时，Innodb会获取该记录行的临键锁，并同时获取该记录行下一个区间的间隙锁。

  • (6) 插入意向锁(Insert Intention Locks)

    • ① 是间隙锁的一种，专门针对insert操作的，多个事务在同一个索引，同一个范围区间插入记录时，如果插入的位置不冲突，不会阻塞彼此。

  • (7)自增锁（Auto-inc Locks)

    • ① 一种特殊的表级锁，专门针对事务插入AUTO_INCREMENT类型的列。如果一个事务正往表中插入记录，所有其他事务的插入必须等待，以便第一个事务插入的行是连续的主键值。

SQL语句执行顺序

  • select distinct from xxx join xxx on
  • where
  • group by
  • having
  • order by
  • limit m,n 从m+1开始选择n条

SQL注入

  • ① #相当于对数据加上双引号，$相当于直接显示数据。

  • ② #的优势就在于它能很大程度的防止sql注入，而$则不行。

  • ③ Mybatis在处理#{}时,会将sql中的#替换为?,调用PreparedStatement的set方法来赋值.

  • ④ Mybatis在处理MySQL - 图1{}替换为变量的值.

  • 例子：

    1. -- #{} 可以防止sql注入，因为它可以把传入的参数变为字符串
    2. SELECT * FROM sb_user WHERE user_name=" ' or 1=1 -- " AND user_pwd="0192023a7bbd73250516f069df18b500";
    3. -- ${} 不能防止sql注入，因为它是直接替换的
    4. SELECT * FROM sb_user WHERE user_name=' ' OR 1=1 -- ' AND user_pwd='0192023a7bbd73250516f069df18b500';

数据库内连接和外连接的区别

  • 内连接指连接结果仅包含符合连接条件的行，两张表都要符合连接条件
  • 外连接结果不仅包含符合连接条件的行同时也包含自身不符合条件的行。包括左外连接、右外连接和全外连接。

数据库隔离级别

  • (1) Read uncommitted读未提交，总是读取记录的最新版本数据，无论该版本是否已经提交。可能会导致脏读、不可重复读、幻读。
    (2) read committed 读已提交，事务中能看到其他事务已提交的修改。大多数数据库的默认隔离级别。可能会导致不可重复读、幻读。
    (3) repeatable read可重复读，对读取到的记录加锁，保证对记录多次读取结果都是一样的，可能会导致幻读。Mysql 的innoDB依靠临界锁，在可重复读的级别下也可以避免幻读，是InnoDB的默认隔离级别。
    (4) Serializable 可串行化 最高的隔离级别，所有事务依次逐个执行，这样事务之间不可能产生干扰，可以防止脏读、不可重复读和幻读。InnoDB 存储引擎在分布式事务的情况下⼀般会⽤到 SERIALIZABLE(可串⾏化) 隔离级别。

count(*), count(1) 和count(列名)的区别

  • (1) Count()统计表中的所有记录数，包括字段为null的记录，相当于行数。
    (2) Count(1)统计表中的所有记录数，包括字段为null的记录，用1代表行。
    (3) Count(列名）统计该字段在表中出现的次数，忽略字段为null的记录。
    (4) 执行效率上：
    ① 列名为主键，count(列名)会比count(1)快
    ② 列名不为主键，count(1)会比count(列名)快
    ③ 如果表多个列并且没有主键，则 count（1） 的执行效率优于 count（    ）
    ④ 如果表只有一个字段，则 select count（*）最优

MVCC

  • MVCC（multi-version Concurrency Control) 多版本并发控制， MVCC的最大好处是读不加锁，读写不冲突。基于乐观锁的实现机制，事务对数据库的任何修改的提交都不会覆盖之前的数据，而是产生一个新版本与老版本共存，使得读取时可以不加锁。在Mysql的InnoDB引擎中就是指在读已提交和可重复读这两种隔离级别下的事务对SELECT操作会访问版本链中记录的过程。
  • MVCC在mysql的InnoDB引擎中的实现方式是只在数据库中保存最新版本的数据，但是会在使用undo时动态重构旧版本数据。InnoDB的MVCC实现是通过保存数据在某个时间点的快照来实现的，一个事务不管其执行多长时间，其内部看到的数据是一致的，也就是事务在执行过程中不会相互影响。
  • 版本链：事务id: 存储每次对某条聚簇索引记录进行修改的事务id。回滚指针，指向这条聚簇索引记录的上一个版本位置。
  • 已提交读隔离级别下的事务在每次查询的开始都会生成一个独立的ReadView,而可重复读隔离级别则在第一次读的时候生成一个ReadView，之后的读都复用之前的ReadView。

  • 在MVCC并发控制中，读操作可以分为两类:当前读和快照读。

    • ① 快照读：读取的是记录中的可见版本，不用加锁。
    • ② 当前读：读取的是记录的最新版本，并且当前读返回的记录都会加锁。

InnoDB中的MVCC

  • (1) MVCC会给每行数据添加两个隐藏列：创建版本号和删除版本号。MVCC会给每行数据添加两个隐藏列：创建版本号和删除版本号。每个事务在修改时，先拷贝原始数据行，然后进行修改，各个事务之间没有干扰。保存时比较版本号，如果成功则覆盖原纪录，失败则放弃。
    (2) 插入：将新增行的创建版本号设置为插入时的事务id,删除版本号默认为null.
    (3) 删除：会给行数据的删除版本号设置一个当前事务的id值。
    (4) 修改：新增一行数据，再把原先数据的删除版本号设置为当前事务id值，再修改拷贝的数据的创建版本号为当前事务id.
    (5) 查询：保证该行数据的数据版本号小于当前事务ID，并且删除版本号必须为null或大于当前事务ID值。这样可以保证读取之前事务是存在的。

MVCC是通过什么实现多版本控制的？

  • Undolog。 Innodb将行记录快照保存在undo log里面，通过回滚指针将数据行记录串联成一个链表。如果要找到某个版本的历史快照，可以通过遍历回滚指针的方式查找。

InnoDB如何解决幻读？

  • (1) 在可重复读的隔离级别下，innodb使用MVCC和next-key locks解决幻读。
    (2) 临键锁锁住的是当前记录以及查找范围，避免范围间插入记录，影响记录行数。
    (3) 如果是当前读的情况，比如加锁的select或update,delete等语句，在PR的隔离级别下，数据库会使用临界锁来锁住本条记录以及索引区间。
    (4) 如果是快照读的情况，比如普通的select语句，在PR隔离级别下，解决幻读的手段是MVCC。多次读取的数据都是一样的。

  • 如果是RC的隔离级别, MVCC解决不了幻读和不可重复读，每次读都会读它自己刷新的快照版本。

Undo log 和 Redo log

  • (1) Undo log保证事务的原子性和普通select的快照读。当事务开启的时候会拷贝当前数据到undo log中，此时其他事务中的select直接从undo log中获取。若事务处理过程中出现错误或事务回滚可根据undo log恢复原始数据。

  • redolog记录数据修改后的值，用于持久化到磁盘中。redo log 包括两部分，一部分是内存中的redo buffer，另外一部分是磁盘上的redo log日志文件。redo log记录的是数据页更新内容，是物理日志。

  • (2) Redo log保证了事务的持久性。事务开始后，只要开始改变数据信息就会持续写入到redo buffer中，再持久化到redo log中。在数据库发生意外故障时，尚有修改的数据未写入磁盘，在重启mysql服务时，根据redo log进行重做，从而达到未入磁盘数据进行持久化。

  • (3) Redo buffer 持久化到Redo log的策略有三种：
    ① 取值 0 每秒一次进行提交持久化[可能丢失一秒内 的事务数据]
    ② 取值 1 默认值，每次事务提交执行Redo buffer —> Redo log OS cache —>flush cache to disk [最安全，性能最差的方式]
    ③ 取值 2 每次事务提交到系统缓存OS cache，再每一秒从系统缓存中执行持久化操作

Redo log 和Binlog的区别

  • (1) Redo log 是InnoDB引擎特有的，binlog属于服务层。数据库使用别的引擎也可以通过binlog达到一致性要求。
    (2) Redo log是物理日志，记录该数据页更新内容，bin log是逻辑日志，记录更新数据的原始逻辑。
    (3) Redo log是循环写，日志空间大小固定。Binlog是追加写，写完后会更换下一个文件，不会覆盖。
    (4) Binlog可以作为恢复数据使用，主从复制搭建。redo log作为异常宕机后的数据恢复使用。

myisam和innodb区别

  • (1) InnoDB支持事务，myISAM不支持事务。
    (2) InnoDB支持外键，而MyISAM不支持
    (3) InnoDB 是聚集索引，MyISAM 是非聚集索引。因此 InnoDB 必须要有主键，通过主键索引效率很高。但是辅助索引需要两次查询，先查询到主键，然后再通过主键查询到数据。因此，主键不应该过大，因为主键太大，其他索引也都会很大。而 MyISAM 是非聚集索引，数据文件是分离的，索引保存的是数据文件的指针。主键索引和辅助索引是独立的。
    (4) InnoDB 不保存表的具体行数，执行 select count(*) from table 时需要全表扫描。而MyISAM 用一个变量保存了整个表的行数，执行上述语句时只需要读出该变量即可，速度很快。
    (5) InnoDB 最小锁粒度是行锁，MyISAM 最小锁粒度是表锁。一个更新语句会锁住整张表，导致其他查询和更新都会被阻塞，因此并发访问受限。是 MySQL 将默认存储引擎从 MyISAM 变成 InnoDB 的重要原因之一；

为什么myisam的查询速度要比innoDB快一些呢

  • (1) Innodb在做select的时候，要维护的东西比myisam引擎多很多，比如
    ① innodb要缓存数据块，myisam只缓存索引块，
    ② Innodb寻址要映射到块再到行，而myisam记录的直接是数据地址
    ③ Innodb还要维护MVCC一致。

myisam和innodb的应用场景

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

两阶段锁协议 2PL

  • ① 锁操作分为两个阶段：加锁阶段和解锁阶段，保证加锁阶段和解锁阶段不相交。
    ② 在一个事务中不管有多少条增删改，在commit()之后进入解锁阶段才会全部解锁。

更新语句是如何加锁的?

(1) id主键+read committed或read repeatable, 直接加X锁。

(2) Id不是主键，是唯一索引，在read committed或或read repeatable级别下，需要加两个X锁，一个锁id的记录，一个锁聚集索引上的记录。

(3) Id不是唯一索引+read committed， 若id列上有非唯一索引，那么对应的所有满足SQL查询条件的记录都要加锁，同时这些记录在主键索引上也要加锁。

(4) Id无索引+read committed。mysql会走聚集索引进行全表扫描过滤，每条记录无论是否满足条件都会加X锁。为了效率，mysql在扫描过程中若记录不满足过滤条件则会进行解锁操作，这违背了2PL协议。

(5) Id不唯一索引+read repeatable,首先通过id索引定位到第一条满足条件的记录，给记录加上X锁，并且给GAP加上Gap锁，然后在主键聚集索引上满足相同条件的记录加上X锁，然后返回。

(6) Id无索引+read repeatable,如果要进行全表扫描的当前读，那么会锁上表上的所有记录并且给所有GAP都加上Gap锁，杜绝所有增删改操作。在mysql中可以触发semi-consistent read来缓解开销与并发影响。

事务

  • (1) 事务是逻辑上的一组操作，要么都执行，要么都不执行。
    (2) 事务有ACID四大特性。原子性，一致性，隔离性和持久性。
    (3) 事务有四种隔离级别，读未提交，读已提交，可重复读，序列化。

事务的ACID四个特性

  • (1)原子性（Atomicity) 事务是最小的执行单位，不允许分割。事务的原子性确保动作要么全部完成，要么完全不起作用。undolog
    (2) 一致性（Consistency) 事务必须是使数据库从一个一致性状态变到另一个一致性状态。
    (3) 隔离性（Isolation)：一个事务的执行不能被其他事务干扰。即一个事务内部的操作及使用的数据对并发的其他事务是隔离的，并发执行的各个事务之间不能互相干扰。 MVCC
    (4) 持久性(Durability)：持久性也称永久性，指一个事务一旦提交，它对数据库中数据的改变就应该是永久性的。接下来的其他操作或故障不应该对其有任何影响。redolog

并发事务带来哪些问题?

  • (1) 脏读：一个事务读取了另一个事务未提交的数据。
    (2) 丢失修改:一个事务的更新覆盖了另一个事务的更新。
    (3) 不可重复读: 一个事务两次读取同一个数据，两次读取的数据不一致。
    (4)幻读: 一个事务两次读取一个范围的记录数，两次读取的记录数不一致。
    (5) 不可重复读和幻读区别：不可重复读关注某条记录的值被修改，导致两次读取结果不一样。而幻读是新增或删除一些数据记录，导致数据记录条数不一样了。

数据库范式

  • (1) 第一范式
    第一范式（1NF）是指数据库表的数据库表中的所有字段值都是不可分解的原子值。
    (2) 第二范式
    第二范式（2NF)需要确保数据库表中的每一列都和主键相关。也就是说在一个数据库表中，一个表中只能保存一种数据，不可以把多种数据保存在同一张数据库表中。
    (3) 第三范式
    第三范式（3NF）确保数据表中的每一列数据都和主键直接相关，不存在传递依赖。（限制1对多的关系)

mysql如何执行一条sql语句的

MySQL - 图2

  • (1) MySQL内部可以分为服务层和存储引擎层两部分：
    ① 服务层包括连接器、查询缓存、分析器、优化 器、执行器等，涵盖MySQL的大多数核心服务功能，以及所有的内置函数，所有跨存储引擎的功能都在这一层实现，比如存储过程、触发器、视图等。
    ② 存储引擎层负责数据的存储和提取。其架构模式是插件式的，支持InnoDB、MyISAM、Memory等多个存储引擎。现在最常用的存储引擎是InnoDB，它从MySQL 5.5.5版本开始成为了默认的存储引擎。

  • (2) Server层按顺序执行sql的步骤为：
    ① 客户端请求->连接器（验证用户身份，给予权限）
    ② 查询缓存（存在缓存则直接返回，不存在则执行后续操作）mysql的查询缓存效率不高，只要表中数据有更新就会清空缓存。所以缓存适合查询静态表，很长时间才更新一次。
    ③ 分析器（对SQL进行词法分析和语法分析操作）
    ④ 优化器（主要对执行的sql优化选择最优的执行方案方法）
    ⑤ 执行器（执行时会先看用户是否有执行权限，有才去使用这个引擎提供的接口）
    ⑥ 去引擎层获取数据返回（如果开启查询缓存则会缓存查询结果）

一条更新语句的执行过程

  • (1) 执行器先找引擎取 ID=2 这一行。ID 是主键，引擎直接用树搜索找到这一行。如果 ID=2 这一行所在的数据页本来就在内存中，就直接返回给执行器；否则，需要先从磁盘读入内存，然后再返回。
    (2) 执行器拿到引擎给的行数据，修改内容，得到新的一行数据，再调用引擎接口写入这行新数据。
    (3) 引擎将这行新数据更新到内存中，同时将这个更新操作记录到 redo log 里面，此时 redo log 处于 prepare 状态。然后告知执行器执行完成了，随时可以提交事务。
    (4) 执行器生成这个操作的 bin log，并把 bin log 写入磁盘。
    (5) 执行器调用引擎的提交事务接口，引擎把刚刚写入的 redo log 改成提交状态，更新完成。

char和vachar区别

  • Char定长，varchar变长。如果实际存储的长度低于括号中填写的长度，char会用空格进行补全。
  • Char最多能存放字符个数为255和编码无关，而varchar最多能存放65532个字符，最大有效长度由最大行大小和使用的字符集来确定。整体最大长度为65532字节。
  • char在取值的时候会把存值后面的空格去除掉，varchar 如果后面有空格则会保留；
  • 不同字符集字符和字节换算是不同的。拉丁字符latin换算规律1字符=1字节，utf8是1字符3字节，gbk是1字符2字节。若var(8)，对于utf8来说，占用字节为8*3=24. varchar(10)，但实际存储了3个字符，那么容量就是实际存储字符占用的字节数。

MySQL索引

为什么使用索引?

  • (1) 通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。
    (2) 可以大大加快数据的检索速度，这也是创建索引的最主要的原因。
    (3) 帮助服务器避免排序和临时表
    (4) 将随机IO变为顺序IO。
    (5) 可以加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义。

为什么不对表的每一列创建一个索引?

  • (1) 当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，这样就降低了数据的维护速度。
    (2) 索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果要建立簇索引，那么需要的空间就会更大。
    (3) 创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加.

什么情况下不适合建立索引？

  • 避免对经常更新的表做更多索引
  • 数据量少的表尽量不适用索引
  • 对区分度不高的字段不需要建立索引，比如性别

索引类型

  • (1) 普通索引 INDEX
    (2) 唯一索引 UNIQUE
    (3) 主键索引 PRIMARY KEY
    (4) 组合索引 ADD INDEX指多个字段上创建的索引，只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用。
    (5) 全文索引 FULLTEXT 主要用来查找文本中的关键字，而不是直接与索引中的值比较

mysql中索引的数据结构

  • Mysql索引使用的数据结构有BTee索引和哈希索引。在绝大多数需求为单条记录查询时，可以选择哈希索引，性能最快。其余场景选择BTee索引。
    BTree索引，Mysql的BTree索引使用的是B树中的B+Tree但对于主要的两种存储引擎（MyISAM和InnoDB）的实现方式是不同的.

hash索引的局限性

  • 哈希索引没办法利用索引完成排序
  • 存在大量重复键值情况下，哈希索引的效率低——>哈希碰撞
  • 不支持范围查询
  • 对于hash索引，innodb是自适应哈希索引的（hash索引的创建由InnoDB存储引擎引擎自动优化创建，我们干预不了）

聚集索引

  • innoDB存储引擎表是索引组织表，即表中数据按照主键顺序存放。而聚集索引就是按照每张表的主键构造一棵B+树，并且叶节点存放整张表的行记录数据，因此也让聚集索引的叶节点成为数据页。同B+树一样，每个数据页都通过一个双向链表来进行链接。
  • 对于主键索引的添加或者删除操作，mysql数据库先创建一张新的临时表，然后把数据导入临时表，删除原表，再把临时表重命名为原来的表名。
  • 对于辅助索引的创建，innodb引擎会对表加上一个S锁，在创建的过程中不需要重建表。

myisam和innodb实现b+树索引方式的区别

  • (1) MyISAM，B+Tree叶节点的data域存放的是数据记录的地址，在索引检索的时候，首先按照B+Tree搜索算法搜索索引，如果指定的key存在，则取出其data域的值，然后以data域的值为地址读取相应的数据记录，这被称为“非聚簇索引”。
  • (2) nnoDB，其数据文件本身就是索引文件，相比MyISAM，索引文件和数据文件是分离的，其表数据文件本身就是按B+Tree组织的一个索引结构，树的节点data域保存了完整的数据记录，这个索引的key是数据表的主键，因此InnoDB表数据文件本身就是主索引。这被称为“聚簇索引”或者聚集索引，而其余的索引都作为辅助索引，辅助索引的data域存储相应记录主键的值而不是地址，这也是和MyISAM不同的地方，在根据主索引搜索时，直接找到key所在的节点即可取出数据；在根据辅助索引查找时，则需要先取出主键的值，在走一遍主索引。因此，在设计表的时候，不建议使用过长的字段为主键，也不建议使用非单调的字段作为主键，这样会造成主索引频繁分裂。

innodb索引的查找过程

MySQL - 图3

辅助索引（二级索引）

  • (1) 先通过辅助索引得到主键id,然后遍历主键索引的b+树，得到对应数据，这个过程叫做回表。

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

  • (1) 唯一索引列可以为空，主键索引列不能为空
    (2) 一个表只能创建一个主键索引，但可以创建多个唯一索引。
    (3) 主键索引可以做外键，唯一索引不能。

为什么使用B+树作为索引？

  • ① 索引很大，不可能全部存储在内存中，因此索引往往以文件的形式存储在磁盘。
    ② 索引结构的节点被设计为一个页的大小，提升查找速度的关键在于尽可能少的磁盘I/0次数。
    ③ 磁盘IO的次数取决于树的高度，每个节点中的key个数越多，树的高度越小，需要IO的次数越少。一般来说B+树比B树更快，因为它的非叶节点不存储数据，可以存储更多的key。
    ④ 由于B+树的非叶节点不存储数据，因此所有的查询都要到叶子节点，而叶子节点的高度是相同的，因此所有数据查询速度是一致的。B树的查询不如B+树稳定，因为它的非叶节点也存储了数据。
    ⑤ B+树添加了相邻叶子节点的指针，所以支持范围查找，但B树不支持。

介绍一下B+树

  • B+树是一棵多路平衡的二叉查找树，左右子树高度差不超过1。B+树具有两种类型的结点，索引结点和叶子结点，索引结点不存储数据只存储索引，数据全部保存在叶子结点中。
  • 索引结点的key都按照从小到大的顺序排列，每个key的左子树中的所有key都小于它，而右子树中的所有key都大于它。叶子结点中的记录也按照key的大小排序。
  • 所有叶子结点都位于同一层。每个叶子结点都存有相邻叶子结点的指针。
  • 数据的增删改操作的时间复杂度是O(logN)

B树和B+树的区别

  • (1) B+树的中间节点不保存数据，是纯索引。但B树的中间节点是保存数据和索引的。

  • (2) 对于范围查找来说，B+树只需要遍历叶子节点链表即可。B树却需要重复地进行中序遍历。

聚集索引和非聚集索引的区别

  • 聚集索引的数据的物理存放顺序与索引顺序是一致的.叶子节点存放的是整行数据,直接通过聚集索引的键值找到某行.
  • 非聚簇索引的数据的物理存放顺序和索引顺序无关,叶子节点存放的是数据文件的指针。非聚集索引需要两次查询,先查询到主键,再通过主键查询到数据。
  • 聚集索引的优点是查询速度快,缺点是对表进行修改速度较慢，这是为了保持表中的记录的物理顺序与索引的顺序一致，而把记录插入到数据页的相应位置，必须在数据页中进行数据重排，降低了执行速度。
  • InnoDB引擎使用聚集索引,MyISAM引擎使用非聚集索引.
  • 每张表只能拥有一个聚集索引或者一个非聚集索引.

mysql 日志

MySQL中一般有以下几种日志：

日志类型 写入日志的信息
错误日志 记录在启动，运行或停止mysqld时遇到的问题
通用查询日志 记录建立的客户端连接和执行的语句
二进制日志 记录更改数据的语句
中继日志 从复制主服务器接收的数据更改
慢查询日志 记录所有执行时间超过 long_query_time 秒的所有查询或不使用索引的查询
DDL日志（元数据日志） 元数据操作由DDL语句执行

慢查询日志

  • 设置一个阈值，将运行时间超过该值的所有SQL语句都记录到慢查询日志文件中。该阈值可以通过参数long_query_time来设置，默认为10秒。默认情况下，mysql数据库并不启动慢查询日志，需要手动将这个参数设置为ON。
  • 另外一个和慢查询日志有关的参数是log_queries_not_using_indexes，如果运行的SQL语句没有使用索引，则MySQL数据库同样会将这条语句记录到慢查询日志文件。

二进制日志

  • 二进制日志记录了对数据库执行更改的所有操作，不包括select和show这类操作。二进制日志主要作用是恢复和复制。

    • mysql主从复制
    • 数据恢复 通过使用mysqlbiglog工具来恢复数据
  • 三种格式：statement、row和mixed
  • statement记录的是日志的逻辑SQL语句。
  • row格式记录表的行更改情况。

  • mixed格式，默认情况下采用statement格式进行二进制日志文件记录，但一些情况会使用ROW格式：

    • 使用了临时表
    • 使用了用户定义函数

Explain

  • Explain+SQL语句，使用explain关键字可以模拟优化器执行sql查询语句，分析查询语句的性能瓶颈。

  • Explain几个重要的字段：

    1. id: select查询的序列号，id相同执行顺序从上至下，id不同，子查询的id序号会递增，越先被执行。

    2. select_type:查询类型，用于区别普通查询，联合查询，子查询等复杂查询。

    3. type 显示查询使用了哪种类型。查询级别最好达到ref。

    4. Possible_keys 表示可能用在这张表中的索引。

    5. key表示实际使用的索引。

    6. rows 扫描出的行数

  • select_type字段： | 类型 | 说明 | | —- | —- | | SIMPLE | 简单表，不使用表连接或子查询 | | PRIMARY | 主查询，即外层的查询 | | UNION | UNION中的第二个或者后面的查询语句 | | SUBQUERY | 子查询中的第一个 |

  • type字段：描述数据库引擎查找表的一种方式。

    • all:全表扫描 select * from table;
    • index:另一种方式的全表扫描，只不过是按照索引的顺序。mysql会遍历整个索引来查询匹配行，并不会扫描表。 select id from table;
    • range指的是有范围的索引扫描。常用于<,<=,>,>=，between等操作，比较字段需要加索引，否则会退化为全表扫描。
    • ref表示使用了索引，但不是唯一索引。一般还用在关联查询中，表关联查询时必定会有一张表进行全表扫描，此表一定是几张表中记录行数最少的表。然后再通过非唯一索引寻找其他关联表中的匹配行。
    • eq_ref 表示查找结果集只有一个（使用了索引或唯一索引）。
    • const表示 通过索引一次就找到了。
    • system是表只有一行记录。

  • extra字段：

    • using filesort 表示mysql需要额外的排序操作，不能通过索引顺序达到排序效果。
    • using index覆盖索引扫描，表示查询在索引树中就可查找所需数据，不用扫描表数据文件。
    • using where表示进行了回表查询
    • using index condition 表示进行了ICP优化。

ICP是什么？

  • index condition pushdown 索引条件下推。ICP能够减少在使用二级索引过滤where条件时的回表次数 。

  • where过滤条件根据索引使用情况分为了三种：

    • index key 根据索引来确定扫描的范围
    • index filter 索引来进行where条件过滤—-> ICP技术
    • table filter where条件不能使用索引进行处理的，只能回表进行过滤。

  • 例子： 比如建立了联合索引（name,age)， 寻找姓陈且年龄=20的用户：

    • 未使用ICP：（忽略age字段，直接通过name进行查询）
      MySQL - 图4

    • 使用ICP，在索引内部就判断了age字段，减少了回表次数
      MySQL - 图5

索引优化方法

  1. (1) 遵循联合索引最左匹配原则，只用用到了创建索引的第一个字段才会使用索引。
  3. (2) 不要在列上使用函数或进行运算，会导致索引失效。
  5. (3) 负向条件查询不能使用索引，比如！,not null,not exists,not in.
  7. (4) 尽量使用覆盖索引，即查询字段全部是索引字段，就避免回表。
  9. (5) 避免强制类型转换，比如字符串不加单引号会导致索引失效。
  11. (6) 范围列可以用到索引，但范围列后面的列无法使用索引。
  13. (7) 更新频繁，数据区分度不高的字段上不宜建立索引。
  15. (8) 避免使用or来连接，除非or条件中的每个列都加上索引。
  17. (9) 前导模糊查询不能使用索引，非前导查询可以。

什么是回表？

  • 回到主键索引树搜索的过程称为回表。比如查询语句中有一个主键索引id还有一个普通索引name,查询条件是根据name查询，那么先在name索引上找到数据，得到id,最后去主键索引树，找到id对应的值。
  • 可以使用覆盖索引避免回表

什么是覆盖索引?

  • Select *是查询所有字段，需要回表。而如果只查询id，则不用回表，因为name索引上已经有了。
  • 一般select后面带的都是索引列，那么用的就是覆盖索引。

联合索引

  • 联合索引还是一棵B+树，不同的是联合索引的键值数量大于等于2，数据按照（a,b,c)的顺序进行存放。
  • (1) 对多个字段同时建立的索引，有顺序之分。
  • (2) （a,b,c) 相当于建立了（a) (a,b) (a,b,c) 三个索引
  • (3) 使用时需要注意避免索引失效问题，其实是最左匹配原则。只有使用了建立索引时的第一个字段去查找才是走了索引的，只用后面的字段找会让索引失效。
  • (4) 好处： 一个联合索引相当于多个索引。索引列越多，通过索引筛选出的数据越少。覆盖索引，mysql可以直接通过遍历索引得到数据，而无需回表，减少了io操作。

最左匹配原则

  • 建立组合索引(a,b,c)
  • 如果查询条件只用了(a,c),由于中间缺失了b，故只用到了a索引。
  • 如果查询条件没有a,那么不走索引
  • 如果查询的时候使用范围查询，并且是最左前缀，那么范围列后面的列无法用到索引。

MySQL主从复制过程以及优缺点

  • (1) 主从复制过程
    ① 主库db的更新事件(update、insert、delete)被写到binlog
    ② 主库创建一个binlog dump thread，把binlog的内容发送到从库
    ③ 从库启动并发起连接，连接到主库
    ④ 从库启动之后，创建一个I/O线程，读取主库传过来的binlog内容并写入到中继日志relay log
    ⑤ 从库启动之后，创建一个SQL线程，从relay log里面读取到更新事件并执行。

  • (2) 优缺点
    ① 从库同步主库数据过程是串行化的，也就是说在主库上并行的操作在从库上会串行执行。由于从库从主库拷贝日志以及串行执行SQL的特点，在高并发场景下，从库的数据一定会比主库慢一些，是有延时的。有可能出现刚写入主库的数据在从库中读取不到的情况，要过几十或者几百毫秒才能读取到。
    ② 如果主库突然宕机，假设数据还没同步到从库，那么有些数据可能会丢失。

  • (3) 解决办法
    ① 半同步复制和并行复制。半同步复制用来解决数据丢失的问题，并行复制用来解决从库复制延迟的问题。
    ② 半同步复制：事务在主库写完binlog后需要从库返回一个已接受，才返回给客户端；确保事务提交后binlog至少传输到一个从库，性能有一定降低。若网络异常或者从库宕机，那么主库会阻塞。
    ③ 并行复制是将事务进行分组，如果事务能够同时提交成功，证明它们之间没有冲突，因此可以在slave上并行执行。通过在主机上的二进制日志中添加组提交信息，可以让salve并行地安全执行事务.

  • (4) 好处
    ① 数据备份，保证数据安全。
    ② 读写分离，提高并发度，主库负责写，从库负责读
    ③ 提升IO性能，多库的存储有效降低磁盘IO访问的频率，提高了单个设备的IO性能。

分布式数据库中间件

  • Mycat是基于阿里 Cobar 演变而来的一款开源分布式数据库中间件，是一个实现了 MySQL 协议的 Server。前端用户可以把它看做是一个数据库代理，用 MySQL 客户端工具和命令行访问；而其后端可以用 MySQL 原生（Native）协议与多个 MySQL 服务器通信，也可以用 JDBC 协议与大多数主流数据库服务器通信。
  • Apache ShardingSphere是一套开源的分布式数据库中间件解决方案组成的生态圈，它由 Sharding-JDBC、Sharding-Proxy 和 Sharding-Sidecar（规划中）这 3 款相互独立，却又能够混合部署配合使用的产品组成。它们均提供标准化的数据分片、分布式事务和数据库治理功能，可适用于如 Java 同构、异构语言、云原生等各种多样化的应用场景。