MYSQL必知必会

1. 基础

• 0.1 关联 inner/left/right/full join
  • inner join 典型的连接运算，使用像=或<> 之类的比较运算符
  • left outer join 左外连接，以左表为主表，右表为辅表（没有的显示null）
  • right outer join 右外连接，以右表为主表（显示全部），左表为辅表（没有的显示null）
  • full join 全外连接，将所有数据都展示出来
  • union/union all union 不允许有重复值，union all 可以有
• 0.2 数据库的三大范式
  • 第一范式，确保数据表字段的原子性
  • 第二范式，表必须有一个主键；非主键列，必须完全依赖于主键，而不能部分依赖主键
  • 第三范式，非主键列，必须直接依赖于主键，不能传递依赖

1. 索引

• 1.1 什么是索引
  • 索引是存储引擎用于提高查询数据库表访问速度的一种数据结构
• 1.2 索引的优缺点？
  • 优点：提高查询数据库表的速度；加快表与表的连接
  • 缺点：占用物理空间，不适用频繁增删改的场景，会降低表的增删改的效率，因为需要进行动态维护索引，导致时间变长
• 1.3 索引的作用？
  • 提高查询速度
  • 数据存储在磁盘上，查询数据是，如果没有索引，会加载所有数据到内存，依次进行检索，读取磁盘次数较多。有了索引，就不需要加载所有数据，因为B+树一般在2～4层，最多只用读取2～4层的磁盘，查询速度会大大提什
• 1.4 索引的数据结构
  • B+ 树 ，默认的索引类型为B+树索引
    • B+树是基于B树 和叶子结点 顺序访问指针进行实现，它具有B树的平衡性，也能通过顺序访问指针提高区间查询性能
    • 在B+树中，结点中的key从左到右递增排列，如果某个指针的左右相邻key分别是keyi和keyi+1，则该指针指向结点的所有key大于等于keyi且小于等于keyi+1
    • Mysql 数据库使用最多为B树 索引，底层用的是B+树数据结构来实现
  • 哈希表
    • 哈希索引是基于哈希表实现的
    • 对于每一行数据，存储引擎会对索引列进行哈希计算得到哈希码
    • 对于不同的索引列，会计算出不同哈希码的值作为哈希表的key值
    • 将指向数据行的指针作为哈希表的value值，这样查找一个数据的时间复杂度就是O（1），一般多用于精确查找
  • 两者区别
    • 哈希索引不支持排序，因为哈希表是无序的
    • 哈希表不支持范围查找
    • 哈希索引不支持模糊查找及多列索引的最左前缀匹配
    • 因为哈希表会存在哈希冲突，所以哈希索引的性能不稳定，而B+树是性能相对稳定的，每次都是从根节点查询到叶子结点
  • 为什么B+树比B树更适合数据库索引
    • B+树的数据存储在叶子结点中，叶子结点均为索引，方便扫库，只需扫一遍叶子结点
    • 但B树因为其分支结点同样存储数据，我们要找到具体的数据，需要进行一次中序遍历按序来扫
    • 所以B+树更加适合在区间查询的情况，而在数据库中基于范围的查询是非常频繁，所以通常B+树适合做数据库索引
    • B+树的结点只存储索引key值，具体信息的地址存在叶子结点的地址中
    • 这就使以页为单位的索引中可以存放更多的结点，减少更多的I/O支出
    • B+树的查询效率更加稳定，任何关键字的查找必须走一条从根结点到叶子结点的路。所有关键字查询的路径长度相同，导致每一个数据的查询效率相当
• 1.5 索引的分类
  • 主键索引 主键名为primary的唯一非空索引，不能为空值
  • 唯一索引 唯一索引的列可以为null且可以存在多个null值。唯一索引的用途：唯一标识数据库表中的每条记录，主要是用来防止数据重复插入
  • 全文索引：只有在MyISAM引擎上才能使用，只能在CHAR、VARCHAR和TEXT类型字段上使用全文索引
  • 组合索引：在表中多个字段组合上创建的索引，只有在查询条件中使用了这些字段的左边字段时，索引才会被使用，使用组合索引时需遵循最左前缀原则
• 1.6 索引的设计原则
  • 尽量使用短索引，目的是小的索引设计到的磁盘I/O较少，查询速度更快
  • 索引不是越多越好，因为占用额外的物理空间，维护也需要花费时间
  • 利用最左前缀原则
• 1.7 索引的失效原则
  • 对于组合索引，不是使用组合索引最左边的字段，则不会使用索引
  • 以%开头的like查询如%abc，无法使用索引；非%开头的like查询如abc%，相当于范围查询，会使用索引
  • 查询条件中列类型是字符串，没有使用引号，可能会因为类型不同发生隐式转换，使索引失效，例：where col=a
  • 判断索引列是否不等于某个值时，例：where col!=123
  • 对索引列进行运算，查询条件使用or连接，也会导致索引失效，例：where col_a=123 or col_b=456
• 1.8 哪些场景 能 建立索引
  • 经常用于查询的字段
  • 经常用于连接的字段建立索引，可以加快连接的速度
  • 经常需要排序的字段建立索引，因为索引已经排好序，可以加快排序查询速度
• 1.9 哪些场景 不能 建立索引
  • where 条件中用不到的字段不适合建立索引
  • 表记录较少
  • 需要经常增删改
  • 参与列计算的列不适合建索引
  • 区分度不高的字段不适合建立索引，如性别
• 1.10 什么是最左匹配原则？
  • 如果 SQL 语句中用到了组合索引中的最左边的索引，那么这条 SQL 语句就可以利用这个组合索引去进行匹配。

• 当遇到范围查询(>、
• 对(a,b,c)建立索引，查询条件使用 a/ab/abc 会走索引，使用 bc 不会走索引。
• 对(a,b,c,d)建立索引，查询条件为a = 1 and b = 2 and c > 3 and d = 4，那么a、b和c三个字段能用到索引，而d无法使用索引。因为遇到了范围查询。

• 1.11 什么是聚集索引？
  • innoDB 使用表的主键构造主键索引树，同时叶子结点中存放的即为整张表的记录数据。聚集索引叶子结点的存储是逻辑上连续的，使用双向链表连接，叶子结点按照主键的顺序排序，因此对于主键的排序查找和范围查找速度比较快
  • 聚集索引的叶子结点就是整张表的行记录。InnoDB 主键使用的是聚簇索引。聚集索引要比非聚集索引查询效率高很多
  • 对于InnoDB来说，聚集索引一般是表中的主键索引，如果表中没有指定主键或索引那么InnoDB内部会生成一个隐藏的逐渐作为聚集索引，这个隐藏的主键长度为6个字节，它的值会随着数据的插入自增
• 1.12 什么是覆盖索引？
  • select 的数据列只用从索引中就能够取得，不需要回表进行二次查询，也就是说查询列要被所使用的索引覆盖。对于innodb表的二级索引，如果索引能覆盖到查询的列，那么就可以避免对主键索引的二次查询
  • 不是所有类型的索引都可以成为覆盖索引。覆盖索引要存储索引列的值，而哈希索引、全文索引不存储索引列的值，所以Mysql使用B+树索引做覆盖索引
  • 对于使用了覆盖索引的查询，在查询前面使用explain，输出的extra列会显示为using index

• 1.13 什么是前缀索引？
  • 有时需要在很长的字符列上创建索引，这会造成索引特别大且慢。使用前缀索引可以避免这个问题
  • 前缀索引是指对文本或者字符串的前几个字符建立索引，这样的索引长度更短，查询速度更快
  • 创建前缀索引的关键在于选择足够长的前缀，以保证较高的索引选择性。索引选择性越高查询效率也就越高，因为选择性高的索引可以让Mysql在查找过滤时过滤掉更多的数据行
  • 建立前缀索引的语句：ALTER TABLE table_name ADD KEY(column_name(prefix_length));
• 1.14 什么是分库分表？
  • 数据库的存储资源是有限的，负载能力也是有限的，数据的大量积累肯定会导致其处理数据的能力下降
  • 数据量越多，那么多数据的增删改查的开销也会越来越大，故为了解决这两种情况，引出了分库分表
  • 分库即单个数据库拆分成多个数据库，分表即单表拆成多表
  • 好处/目的：减小数据库的负担缩短查询时间；常见的数据切分的方式为垂直切分和水平切分
• 1.15 什么是分区表？
  • 分区表是一个独立的逻辑表，底层有多个物理字表组成
  • 当查询条件的数据分布在某一个分区的时候，查询引擎查询引擎只会去某一个分区查询，而不遍历整个表。在管理上，如果需要删除一个分区的数据，只需要删除对应的分区即可。
  • 分区表类型：范围分区/list分区/hash 分区
  • 分区可能带来的问题：
    • 打开和锁住所有底层表的成本很高
    • 维护分区的成本可能很高
    • 所有分区必须使用相同的存储引擎

1. 锁

• 2.1 共享锁和排他锁是什么
  • 共享锁就是只读不写
  • 当没有其他线程对查询结果集中的任何一行使用排他锁时，可以成功申请共享锁，否则会被阻塞。其他线程也可以读取使用了共享锁的表，而且这些线程读取的是同一个版本的数据，eg：SELECT ... LOCK IN SHARE MODE;
  • 排他锁就是写锁
  • 在查询语句后面增加for update，mysql会对查询结果中的每行都加排他锁 eg：SELECT ... FOR UPDATE;
• 2.2 乐观锁和悲观锁是什么
  • 乐观锁，就是比较乐观的锁，总是假设最好的情况，每次去拿数据的时候都认为别人不会修改，所以不会上锁，但是在更新的时候会判断一下，在此期间别人有没有去更新这个数据，可以使用版本号机制和CAS算法实现。适用于多读的场景
  • 悲观锁，总是假设最坏的情况，每次去拿数据时，都认为别人会修改，所以拿数据的时候都会上锁，这样别人想拿这个数据就会阻塞知道他拿到锁。

1. 事务

• 3.1 事务四大特性
  • ACID 原子性，一致性，隔离性，持久性
• 3.2 事务隔离级别有哪些
  • 问题
    • 脏读
    • 幻读
    • 不可重读
  • 区别
    • 不可重读和脏读的区别：脏读是某一事物读取了另一个事物未提交的脏数据，而不可重复度则是读取了前一个事物提交的数据
    • 不可重读和幻读的区别：都是读取了另一条已经提交的食物，不同的是不可重读的重点是修改，幻读的重点是新增或删除
  • 事务隔离就是为了解决👆的问题
    • 串行化：通过排序解决幻读的问题
    • 可重复读：默认的事务隔离级别，解决了不可重复读的问题
    • 读已提交：一个事务只能看见已提交事务所做的改变，可避免脏读的发生
    • 读未提交：所有食物都可以看到其他未提交事务的执行结果

1. 关键词

• 4.1 having 和 where区别？
  • 两者作用的对象不同，where的对象为表和视图；having的对象为组
  • having 结合group by 使用 分组后过滤
  • where 不能结合group by 使用 分组前过滤
• 4.2 exist 和 in的区别？
  • exist 用于对外表记录做筛选
  • in 用于先把后边的语句查出来放到临时表中，然后遍历临时表，将临时表的每一行，带入外查询去查找
  • 适用场景
    • 子查询的表比较大的时候，使用exist可以有效减少总的循环次数来提升速度
    • 外查询比较大的时候，使用in可以有效减少对外查询表循环遍历来提升速度
• 4.3 truncate、delete与drop区别？
  • truncate 删除表数据(DDL语句)
  • detele 删除表数据
  • drop 删除数据和结构(DDL语句)
  • 一般来说，执行速度 drop>truncate>delete
• 4.4 bin log/redo log/undo log 有什么区别？
  • 二进制日志，用于恢复和同步数据库
  • 重做日志，是用来记录innodb存储引擎的事务日志，不管事务是否提交都会记录下来，用于数据回复。当数据库发生故障时，innoDB存储引擎会使用redo log恢复到发生故障前的时刻，以此来保障数据的完整性
  • 回滚日志，当进行数据修改时还会记录undo log，undo log 用于数据的撤回操作，可以实现事务回滚
• 4.5 int(10)和char(10)的区别？
  • int10 为显示数据的长度
  • char 10 为存储数据的长度
• 4.6 preparedStatement和statement的区别？
  • 任何时候使用preparedStatement而不是statement
  • preparedStatement预编译，防止sql注入
  • preparedStatement多次使用可提高效率
• 4.7 数据库查询语言 DQL/DML/DCL 区别？
  • 含义解释
    • DQL 即data query language 数据查询语言，由select from where子句组成
    • DML 即data manipulation language 数据操纵语言 ，包含insert update delete
    • DCL 即data control languuage 数据控制语言，是用来设置或者更改数据库用户或角色权限的语句，常见的语句有grant deny revoke
    • DDL 即data definition language 数据定义语言，用来创建数据库的各种对象--表、视图、索引、如：create table view index等 不能rollback

1. MySQL 底层原理

• 5.1 查询执行流程
  • 权限校验、查询缓存、分析器、优化器、权限校验、执行器、引擎

select * from user where id > 1 and name = 'rainbowzhouj'; ############ 首先检查权限，没有权限则返回错误； MySQL8.0以前会查询缓存，缓存命中则直接返回，没有则执行下一步； 词法分析和语法分析。提取表名、查询条件，检查语法是否有错误； 两种执行方案，先查 id > 1 还是 name = 'rainbowzhouj'，优化器根据自己的优化算法选择执行效率最好的方案； 校验权限，有权限就调用数据库引擎接口，返回引擎的执行结果。

• 5.2 更新执行过程
  • 分析器、权限校验、执行器、引擎、redo log 、bin log （prepare 状态）、redo log （commit状态）

update user set name = 'rainbowzhouj' where id = 1; ################ 先查询到 id 为1的记录，有缓存会使用缓存。 拿到查询结果，将 name 更新为rainbowzhouj，然后调用引擎接口，写入更新数据，innodb 引擎将数据保存在内存中，同时记录redo log，此时redo log进入 prepare状态。 执行器收到通知后记录binlog，然后调用引擎接口，提交redo log为commit状态。 更新完成。

• 5.3 MySQL架构
  • mysql主要分为：server层和存储引擎层
    • server层含连接器、查询缓存、分析器、优化器、执行器等
    • 存储引擎层主要负责数据的存储和读取

1. 引擎

• 6.1 MyISAM
  • MyISAM基于ISAM存储引擎，并对其进行扩展。它是在Web、数据仓储和其他应用环境下最常使用的存储引擎之一。
  • MyISAM拥有较高的插入、查询速度，但不支持事务。
  • MyISAM表格可以被压缩，而且它们支持全文搜索。不支持事务，而且也不支持外键。如果事物回滚将造成不完全回滚，不具有原子性。在进行updata时进行表锁，并发量相对较小。如果执行大量的SELECT，MyISAM是更好的选择。
  • MyISAM的索引和数据是分开的，并且索引是有压缩的，内存使用率就对应提高了不少。能加载更多索引，而Innodb是索引和数据是紧密捆绑的，没有使用压缩从而会造成Innodb比MyISAM体积庞大。
  • MyISAM缓存在内存的是索引，不是数据。而InnoDB缓存在内存的是数据，相对来说，服务器内存越大，InnoDB发挥的优势越大。

• 6.2 InnoDB
  • InnoDB是事务型数据库的首选引擎，支持事务安全表（ACID），支持行锁定和外键，InnoDB是默认的MySQL引擎（5.5版本开始后，之前是MyISAM）。
  • InnoDB 采用MVCC（多版本并发控制）来支持高并发，并实现了四个标准的隔离级别。其默认级别是REPEATABLE READ（可重复读），并且通过间隙锁（next-key locking）策略防止幻读的出现。间隙锁是的 InnoDB 不仅仅锁定查询涉及的行，还会对索引中的间隙进行锁定，以防止幻影行的插入。
  • InnoDB 表是基于聚簇索引建立的。InnoDB 的索引结构和 MySQL 的其他存储引擎有很大不同，聚簇索引对主键查询有很高的性能。不过它的二级索引（secondary index，非主键索引）中必须包含主键列，所以如果主键很大的话，其他的所有索引都会很大。因此，若表上的索引较多的话，主键应当尽可能的小。
  • InnoDB不创建目录，使用InnoDB时，MySQL将在MySQL数据目录下创建一个名为ibdata1的10MB大小的自动扩展数据文件，以及两个名为ib_logfile0和ib_logfile1的5MB大小的日志文件。

• 6.3 MEMORY
  • 使用MySQL Memory存储引擎的出发点是速度。为得到最快的响应时间，采用的逻辑存储介质是系统内存。虽然在内存中存储表数据确实会提供很高的性能，但当mysqld守护进程崩溃时，所有的Memory数据都会丢失。获得速度的同时也带来了一些缺陷。它要求存储在Memory数据表里的数据使用的是长度不变的格式，这意味着不能使用BLOB和TEXT这样的长度可变的数据类型，VARCHAR是一种长度可变的类型，但因为它在MySQL内部当做长度固定不变的CHAR类型，所以可以使用
• 6.4 MERGE
  • MERGE存储引擎是一组MyISAM表的组合，这些MyISAM表结构必须完全相同，尽管其使用不如其它引擎突出，但是在某些情况下非常有用。
  • 说白了，Merge表就是几个相同MyISAM表的聚合器；Merge表中并没有数据，对Merge类型的表可以进行查询、更新、删除操作，这些操作实际上是对内部的MyISAM表进行操作。
  • 主要应用于服务器日志这种信息，一般常用的存储策略是将数据分成很多表，每个名称与特定的时间端相关。例如：可以用12个相同的表来存储服务器日志数据，每个表用对应各个月份的名字来命名。当有必要基于所有12个日志表的数据来生成报表，这意味着需要编写并更新多表查询，以反映这些表中的信息。与其编写这些可能出现错误的查询，不如将这些表合并起来使用一条查询，之后再删除Merge表，而不影响原来的数据，删除Merge表只是删除Merge表的定义，对内部的表没有任何影响

• 6.5 Archive
  • Archive是归档的意思，在归档之后很多的高级功能就不再支持了，仅仅支持最基本的插入和查询两种功能。在MySQL 5.5版以前，Archive是不支持索引，但是在MySQL 5.5以后的版本中就开始支持索引了。Archive拥有很好的压缩机制，它使用zlib压缩库，在记录被请求时会实时压缩，所以它经常被用来当做仓库使用。
• 6.6 引擎选择

不同的存储引擎都有各自的特点，以适应不同的需求，如上表所示。

各自使用场景：

• InnoDB：如果要提供提交、回滚、崩溃恢复能力的事物安全（ACID兼容）能力，并要求实现并发控制，InnoDB是一个好的选择。
• MyISAM：如果数据表主要用来插入和查询记录，则MyISAM引擎能提供较高的处理效率。并且，如果你的应用程序对查询性能要求较高，就要使用MYISAM了。MYISAM索引和数据是分开的，而且其索引是压缩的，可以更好地利用内存。所以它的查询性能明显优于INNODB。压缩后的索引也能节约一些磁盘空间。MYISAM拥有全文索引的功能，这可以极大地优化LIKE查询的效率。
• Archive：如果只有INSERT和SELECT操作，可以选择Archive，Archive支持高并发的插入操作，但是本身不是事务安全的。Archive非常适合存储归档数据，如记录日志信息可以使用Archive。
• MERGE：对日志的一些综合操作，通常使用的是MERGE存储引擎。
• Memory：目标数据较小，而且被非常频繁地访问。
  • 1）在内存中存放数据，所以会造成内存的使用，可以通过参数max_heap_table_size控制Memory表的大小，设置此参数，就可以限制Memory表的最大大小
  • 2）如果数据是临时的，而且要求必须立即可用，那么就可以存放在内存表中。
  • 3）存储在Memory表中的数据如果突然丢失，不会对应用服务产生实质的负面影响。
  • 4）如果只是临时存放数据，数据量不大，并且不需要较高的数据安全性，可以选择将数据保存在内存中的Memory引擎，MySQL中使用该引擎作为临时表，存放查询的中间结果。