MySql

没有代码的程序

已于 2023-05-05 15:23:00 修改

阅读量133

点赞数

文章标签： mysql 求职招聘面试

于 2023-05-03 15:13:51 首次发布

本文链接：https://blog.csdn.net/weixin_42241527/article/details/130472703

版权

MySql索引（知识点笔记）

索引介绍

索引是一种快速查询和检索数据的数据结构。
常见的索引结构：B树，B+树，Hash，红黑树

索引的优缺点：

优点：
- 大大加快数据的检索速度，降低数据库的IO成本
- 通过索引对数据进行排序，可以降低cpu消耗（在使用分组和排序子句进行数据查询时，可以显著减少查询中分组和排序的时间）
- 通过创建唯一索引，可以保证数据库表中每一行数据的唯一性
缺点：
- 创建索引和维护索引需要耗费很多时间。当对表中的数据进行增删改的时候，如果数据有索引，那么索引也需要动态修改。
- 存储索引也需要一定的空间。

索引的实现原理

B+树索引

BTree+树的非叶子节点只存储了索引信息，这样的话可以才存储更多的索引key，降低了BTree+树的高度，叶子节点存放数据，而且叶子节点用链表连接起来。每一次查询都要从根节点进行查询到叶子节点，这样每一次查询的效率都是稳定的，同时叶子节点有指向相邻节点的指针，提高了范围查找的效率。

Hash索引

Hash索引底层是用哈希表实现的。哈希表是键值对的组合，通过键（特定哈希算法）可以快速查询值，时间接近0（1）。
哈希冲突：如果两个(或多个)键值，映射到一个相同的槽位上，他们就产生了hash冲突。最常见的解决Hash冲突的方法是链地址法，将哈希冲突的数据存放在链表中
特点：
◦ Hash索引只能用于精确匹配索引列的查询(=，in)，不支持范围查询（between，>，< ，…）
◦ 无法利用索引完成排序操作
◦ 查询效率高，通常(不存在hash冲突的情况)只需要一次检索就可以了，效率通常要高于B+tree索引

R-tree(空间索引）:

空间索引是MyISAM引擎的一个特殊索引类型，主要用于地理空间数据类型，通常使用较少

全文索引

是一种通过建立倒排索引,快速匹配文档的方式。

InnoDB和MyISAM实现索引的区别

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

其他结构为什么不能做为索引：

二叉树作为索引结构的缺点
- 顺序插入时，会形成一个链表，查询性能大大降低
- 大数据量情况下，层级较深，检索速度慢
红黑树也是一颗二叉树，所以也会存在一个缺点
◦ 大数据量情况下，层级较深，检索速度慢
对于B-tree，无论是叶子节点还是非叶子节点，都会保存数据，这样导致一页中存储的键值减少，指针跟着减少，要同样保存大量数据，只能增加树的高度，导致性能降低
B+树只有叶子节点存放索引和数据，非叶子节点只存放索引。B树的叶子节点是独立的，B+树的叶子节点使用链表相连。

索引分类

按照底层存储的方式划分：
- 聚集索引
  - 索引结构和数据一起存放的索引，InnoDB 中的主键索引就属于聚簇索引
- 非聚集索引
  - 索引结构和数据分开存放的索引，二级索引(辅助索引)就属于非聚簇索引。MySQL 的 MyISAM 引擎，不管主键还是非主键，使用的都是非聚簇索引
按照应用维度划分:
- 主键索引：加速查询、列值唯一、表中只能有一个
- 普通索引：仅仅加速查询
- 唯一索引：加速查询+列值唯一（可以有null）
- 覆盖索引：索引包含所有需要查询的字段的值
- 联合索引：多列值组成一个索引，专门用于组合搜索
- 全文索引：对文本的内容进行分词，进行搜索。目前只有 CHAR、VARCHAR，TEXT列上可以创建全文索引
主键索引
- 主键列所使用的索引就是主键索引
- InnoDB中，当没有指定主键时，InnoDB会自动检查表中是否有唯一索引且不允许存在null值得字段，如果有默认该字段为主键。如果没有，InnoDB自动创建一个自增主键。
二级索引
- 又称为辅助索引，二级索引得叶子节点存放的数据是主键。
唯一索引，普通索引，前缀索引属于二级索引
聚集索引和非聚集索引
- 聚簇索引（Clustered Index）即索引结构和数据一起存放的索引，并不是一种单独的索引类型。InnoDB 中的主键索引就属于聚簇索引
- 优缺点：
  - 优点：
    查询速度非常快：聚簇索引的查询速度非常的快，因为整个 B+树本身就是一颗多叉平衡树，叶子节点也都是有序的，定位到索引的节点，就相当于定位到了数据。相比于非聚簇索引，聚簇索引少了一次读取数据的 IO 操作
    对排序查找和范围查找优化：聚簇索引对于主键的排序查找和范围查找速度非常快
  - 缺点：
    依赖于有序的数据：因为 B+树是多路平衡树，如果索引的数据不是有序的，那么就需要在插入时排序，如果数据是整型还好，否则类似于字符串或 UUID 这种又长又难比较的数据，插入或查找的速度肯定比较慢
    更新代价大：如果对索引列的数据被修改时，那么对应的索引也将会被修改，而且聚簇索引的叶子节点还存放着数据，修改代价肯定是较大的，所以对于主键索引来说，主键一般都是不可被修改的
- 非聚簇索引：索引结构和数据分开存放的索引，并不是一种单独的索引类型。二级索引(辅助索引)就属于非聚簇索引。MySQL 的 MyISAM 引擎，不管主键还是非主键，使用的都是非聚簇索引
- 优缺点：
  - 优点：
    更新代价小：非聚簇索引的叶子节点是不存放数据的
  - 缺点：
    依赖于有序的数据：跟聚簇索引一样，非聚簇索引也依赖于有序的数据
    可能会二次查询(回表)：这应该是非聚簇索引最大的缺点了。当查到索引对应的指针或主键后，可能还需要根据指针或主键再到数据文件或表中查询

B+树的插入及查询操作

B+树插入操作
 查询单个元素流程

联合索引的建立规则

将查询需求频繁或者字段选择性高的列放在前面
索引的复用，可以少维护一些索引(a)->(a,b)

最左前缀法则

如果索引了多列（联合索引），要遵守最左前缀法则。最左前缀法则指的是查询从索引的最左列开始，并且不跳过索引中的列。如果跳跃某一列，索引将会部分失效(后面的字段索引失效)
最左前缀法则只要查询语句有第一列索引即可，而不是一定要在最左边。

索引失效场景

以“%”开头的like语句，索引无效，后缀“%”不影响
or语句前后没有同时使用索引
列类型是字符串，一定要在条件中将数据用引号引用，否则失效（隐式转换）
如果mysql估计使用全表扫描比索引快，则不用索引（键值少，重复数据多）
组合索引要遵守最左前缀原则——不使用第一列索引失效
在索引字段上使用not，<>，！= （对它处理是全表扫描）
对索引字段进行计算操作，字段使用函数也会失效
is null

索引设计原则

针对数据量大，且查询频繁的表建立索引
常作为查询条件的字段建立索引
尽量选择区分度高的列，尽量建立唯一索引。
针对字符串类型的字段，如果字段比较长，可以建立前缀索引。
尽量使用联合索引，减少单列索引。是为了覆盖索引减少回表
要控制索引的数量，索引并不是多多益善，索引越多，维护索引结构的代价也就越大，会影响增删改的效率

索引下推

索引下推详解
 联合索引、覆盖索引、索引下推详解

锁

先来说说锁这个概念。锁是计算机协调多个进程并发访问某一资源得机制。在数据库中，除了传统得CPU、I/O争用外，数据也是供许多用户共享得的资源。保证数据的一致性和有效性就是数据库必须要解决的一个问题。
MySql中的锁按照锁的粒度分为全局锁，表级锁和行级锁。MyISAM仅仅支持表级锁，一锁
就是锁住整张表，这在并发的情况下性能很差。InnoDB支持行级锁，仅需要对相关的记录上
锁，所以对于并发写入来说，InnoDB的性能更好。

全局锁：

全局锁就是对整个数据库实例加锁，加锁后整个实例就处于只读状态，后续的DML的写语句，DDL语句，已经更新操作的事务提交语句都将被阻塞
使用场景是做全库的逻辑备份，对所有的表进行锁定，从而获取一致性视图，保证数据的完整性
特点：
- 如果在主库上备份，那么在备份期间都不能执行更新，业务基本上就得停摆。
- 如果在从库上备份，那么在备份期间从库不能执行主库同步过来的二进制日志（binlog），会导致主从延迟。

表级锁：

表级锁，每次操作锁住整张表。锁定粒度大，发生锁冲突的概率最高，并发度最低。应用在MyISAM、InnoDB、BDB等存储引擎中。
分类：表锁、元数据锁、意向锁
表锁：
- 表共享读锁
  - 客户端一对指定表加了共享读锁，不会影响客户端二得读，但会影响客户端二得写
- 表独占写锁
  - 客户端一，对指定表加了写锁，会阻塞右侧客户端的读和写。
元数据锁：
- 加锁过程是系统自动控制，无需显式使用，在访问一张表的时候会自动加上。MDL锁主要作用是维护表元数据的数据一致性，在表上有活动事务的时候，不可以对元数据进行写入操作。为了避免DML与DDL冲突，保证读写的正确性
意向锁：
- 如果需要用到表锁的话，为了避免表锁和行锁得冲突，如何判断表中的记录没有行锁呢，一行一行遍历肯定是不行，性能太差。我们可以使用意向锁来快速判断是否可以对某个表使用表锁
- 场景：客户端一，在执行DML操作时，会对涉及的行加行锁，同时也会对该表加上意向锁。而其他客户端，在对这张表加表锁的时候，会根据该表上所加的意向锁来判定是否可以成功加表锁，而不用逐行判断行锁情况了。
- 意向共享锁(IS): 由语句select … lock in share mode添加。与表锁共享锁(read)兼容，与表锁排他锁(write)互斥。
- 意向排他锁(IX): 由insert、update、delete、select…for update添加。与表锁共享锁(read)及排他锁(write)都互斥，意向锁之间不会互斥。
- 一旦事务提交了，意向共享锁、意向排他锁，都会自动释放。

行级锁

行级锁，每次操作锁住对应的行数据。锁定粒度最小，发生锁冲突的概率最低，并发度最高。应用在InnoDB存储引擎中
InnoDB的数据是基于索引组织的，行锁是通过对索引上的索引项加锁来实现的，而不是对记录加的锁。
行锁：
- 锁定单个行记录的锁，防止其他事务对此行进行update和delete。在RC、RR隔离级别下都支持
- 共享锁：允许一个事务去读一行，阻止其他事务获得相同数据集的排它锁
- 排他锁：允许获取排他锁的事务更新数据，阻止其他事务获得相同数据集的共享锁和排他锁。
间隙锁：
- 锁定索引记录间隙（不含该记录），确保索引记录间隙不变，防止其他事务在这个间隙进行insert，产生幻读。在RR隔离级别下都支持。
临键锁：
- 行锁和间隙锁组合，同时锁住数据，并锁住数据前面的间隙Gap。在RR隔离级别下支持

表级锁和行级锁对比：

表级锁：MySQL 中锁定粒度最大的一种锁（全局锁除外），是针对非索引字段加的锁，对当前操作的整张表加锁，实现简单，资源消耗也比较少，加锁快，不会出现死锁。不过，触发锁冲突的概率最高，高并发下效率极低。表级锁和存储引擎无关，MyISAM 和 InnoDB 引擎都支持表级锁。
行级锁：MySQL 中锁定粒度最小的一种锁，是针对索引字段加的锁，只针对当前操作的行记录进行加锁。行级锁能大大减少数据库操作的冲突。其加锁粒度最小，并发度高，但加锁的开销也最大，加锁慢，会出现死锁。行级锁和存储引擎有关，是在存储引擎层面实现的

事务

事务是逻辑上的一组操作，要么都执行，要么都不执行。
ACID
- 原子性：事务是最小的执行单位，不允许分割。原子性确保事务要么全部完成，要么全都不起作用。
- 一致性：执行事务前后，数据保持一致。
- 隔离性：并发访问数据库时，一个用户的事务不被其他事务所干扰。
- 持久性：一个事务被提交后，其对数据库中数据的改变是持久的。

并发事务带来的问题

脏读

事务A读取某条数据并对数据进行了修改，但是事务A并没有提交而是回滚，导致数据的修改没有提交到数据库，数据值没有修改。而事务B在事务A 对数据进行修改后读取到了修改后的数据，那么事务B读取到的就是脏数据。（破坏隔离性）

场景：例如：事务 1 读取某表中的数据 A=20，事务 1 修改 A=A-1，事务 2 读取到 A = 19,事务 1 回滚导致对 A 的修改并未提交到数据库， A 的值还是 20。

不可重复读（破坏一致性，update和delete）

指在一个事务内多次读同一数据。在这个事务还没有结束时，另一个事务也访问该数据。那么，在第一个事务中的两次读数据之间，由于第二个事务的修改导致第一个事务两次读取的数据可能不太一样。这就发生了在一个事务内两次读到的数据是不一样的情况，因此称为不可重复读。

场景：事务 1 读取某表中的数据 A=20，事务 2 也读取 A=20，事务 1 修改 A=A-1，事务 2 再次读取 A =19，此时读取的结果和第一次读取的结果不同

幻读：（破坏一致性，insert）

幻读与不可重复读类似。它发生在一个事务读取了几行数据，接着另一个并发事务插入了一些数据时。在随后的查询中，第一个事务就会发现多了一些原本不存在的记录，就好像发生了幻觉一样，所以称为幻读。

场景：事务 2 读取某个范围的数据，事务 1 在这个范围插入了新的数据，事务 2 再次读取这个范围的数据发现相比于第一次读取的结果多了新的数据

不可重复读和幻读的区别

不可重复读的重点是内容修改或者记录减少比如多次读取一条记录发现其中某些记录的值被修改；
幻读的重点在于记录新增比如多次执行同一条查询语句（DQL）时，发现查到的记录增加了。
幻读其实可以看作是不可重复读的一种特殊情况，单独把区分幻读的原因主要是解决幻读和不可重复读的方案不一样。

💡解决方案

执行delete和update操作的时候，可以直接对记录加锁，保证事务安全。而执行insert操作的时候，由于记录锁（Record Lock）只能锁住已经存在的记录，为了避免插入新记录，需要依赖间隙锁（Gap Lock）。

并发事务的控制方式有哪些？

锁：悲观控制的模式
◦ 锁控制模式下通过锁来显示控制共享资源而不是通过调度手段，MySQL中主要通过读写锁来实现并发控制
◦ 共享锁（S锁）：又称读锁，事务在读取记录的时候获取共享锁，允许多个事务同时获取（锁兼容）。
◦ 排他锁（X锁）：又称写锁/独占锁，事务在修改记录的时候获取排他锁，不允许多个事务同时获取。如果一个记录已经被加了排他锁，那其他事务不能再对这条记录加任何类型的锁（锁不兼容）
MVCC：乐观控制的模式
- 对一份数据会存储多个版本，通过事务的可见性来保证事务能看到自己应该看到的版本。通常会有一个全局的版本分配器来为每一行数据设置版本号，版本号是唯一的
- MVCC 在 MySQL 中实现所依赖的手段主要是: 隐藏字段、read view、undo log
  - undo log : undo log 用于记录某行数据的多个版本的数据
  - read view 和隐藏字段 : 用来判断当前版本数据的可见性

SQL标准定义了哪些事务隔离级别？

为了权衡【隔离】和【并发】的矛盾，ISO定义了四个事务的隔离级别，每个级别的隔离程度不同，允许的副作用也不通
READ-UNCOMMITTED(读取未提交) 最低的隔离级别，允许读取尚未提交的数据变更，可能会导致脏读、幻读或不可重复读。
READ-COMMITTED(读取已提交) 允许读取并发事务已经提交的数据，可以阻止脏读，但是幻读或不可重复读仍有可能发生
• REPEATABLE-READ(可重复读) 对同一字段的多次读取结果都是一致的，除非数据是被本身事务自己所修改，可以阻止脏读和不可重复读，但幻读仍有可能发生
• SERIALIZABLE(可串行化): 最高的隔离级别，完全服从 ACID 的隔离级别。所有的事务依次逐个执行，这样事务之间就完全不可能产生干扰，也就是说，该级别可以防止脏读、不可重复读以及幻读。

MySQL隔离级别的实现方式

MySQL 的隔离级别基于锁和 MVCC 机制共同实现的
锁：
MVCC:生成一个数据快照，并用这个快照来提供一定级别的一致性数据读取，也称为多版本的数据控制。
- 实际就是CAS版本控制和数据读写分离的思想
- 主要作用于RC和RR级别
SERIALIZABLE 隔离级别是通过锁来实现的，READ-COMMITTED 和 REPEATABLE-READ 隔离级别是基于 MVCC 实现的。不过， SERIALIZABLE 之外的其他隔离级别可能也需要用到锁机制，就比如 REPEATABLE-READ 在当前读情况下需要使用加锁读来保证不会出现幻读

存储引擎

存储引擎就是存储数据、建立索引、更新/查询数据等技术的实现方式。存储引擎是基于表的，而不是基于库的，所以存储引擎也可被称为表类型。我们可以在创建表的时候，来指定选择的存储引擎，如果没有指定将自动选择默认的存储引擎

InnoDB

MySQL5.5之后默认的存储引擎
遵循ACID模型，支持事务
行级锁，提高并发访问性能
支持外键

MyISAM

不支持事务，不支持外锁
支持表锁，不支持行锁
访问速度快

Memory

表数据是存储在内存中的，由于受到硬件问题、或断电问题的影响，只能将这些表作为临时表或缓存使用
默认哈希索引
文件：xxx.sdi：存储表结构信息
存储引擎选择

MySQL的InnoDB引擎如何实现事务的四大特性

redo log

• 重做日志，记录的是事务提交时数据页的物理修改，是用来实现事务的持久性

该日志文件由两部分组成：重做日志缓冲（redo log buffer）以及重做日志文件（redo logfile）,前者是在内存中，后者在磁盘中。当事务提交之后会把所有修改信息都存到该日志文件中, 用于在刷新脏页到磁盘,发生错误时, 进行数据恢复使用。

场景：
我们知道，在InnoDB引擎中的内存结构中，主要的内存区域就是缓冲池，在缓冲池中缓存了很多的数据页。当我们在一个事务中，执行多个增删改的操作时，InnoDB引擎会先操作缓冲池中的数据，如果缓冲区没有对应的数据，会通过后台线程将磁盘中的数据加载出来，存放在缓冲区中，然后将缓冲池中的数据修改，修改后的数据页我们称为脏页。而脏页则会在一定的时机，通过后台线程刷新到磁盘中，从而保证缓冲区与磁盘的数据一致。而缓冲区的脏页数据并不是实时刷新的，而是一段时间之后将缓冲区的数据刷新到磁盘中，假如刷新到磁盘的过程出错了，而提示给用户事务提交成功，而数据却没有持久化下来，这就出现问题了，没有保证事务的持久性
那么，如何解决上述的问题呢？在InnoDB中提供了一份日志 redo log，接下来我们再来分析一下，通过redolog如何解决这个问题。
有了redolog之后，当对缓冲区的数据进行增删改之后，会首先将操作的数据页的变化，记录在redo log buffer中。在事务提交时，会将redo log buffer中的数据刷新到redo log磁盘文件中。过一段时间之后，如果刷新缓冲区的脏页到磁盘时，发生错误，此时就可以借助于redo log进行数据恢复，这样就保证了事务的持久性。而如果脏页成功刷新到磁盘或或者涉及到的数据已经落盘，此时redolog就没有作用了，就可以删除了，所以存在的两个redolog文件是循环写的。
那为什么每一次提交事务，要刷新redo log 到磁盘中呢，而不是直接将buffer pool中的脏页刷新到磁盘呢 ?因为在业务操作中，我们操作数据一般都是随机读写磁盘的，而不是顺序读写磁盘。而redo log在往磁盘文件中写入数据，由于是日志文件，所以都是顺序写的。顺序写的效率，要远大于随机写。这种先写日志的方式，称之为 WAL（Write-Ahead Logging）
undo log（原子性）

回滚日志，

用于记录数据被修改前的信息 , 作用包含两个 : 提供回滚(保证事务的原子性) 和MVCC(多版本并发控制)
• undo log和redo log记录物理日志不一样，它是逻辑日志。可以认为当delete一条记录时，undolog中会记录一条对应的insert记录，反之亦然，当update一条记录时，它记录一条对应相反的update记录。当执行rollback时，就可以从undo log中的逻辑记录读取到相应的内容并进行回滚。
• Undo log销毁：undo log在事务执行时产生，事务提交时，并不会立即删除undo log，因为这些日志可能还用于MVCC。
• Undo log存储：undo log采用段的方式进行管理和记录，存放在前面介绍的 rollback segment回滚段中，内部包含1024个undo log segment

MVCC

当前读
- 读取的是记录的最新版本，读取时还要保证其他并发事务不能修改当前记录，会对读取的记录进行加锁
- 对于我们日常的操作，如：select … lock in share mode(共享锁)，select …for update、update、insert、delete(排他锁)都是一种当前读。
快照读
- 简单的select（不加锁）就是快照读，快照读，读取的是记录数据的可见版本，有可能是历史数据，不加锁，是非阻塞读

日志

错误日志

记录了当mysqlld启动和停止时，以及服务器在运行过程中发生任何严重错误时的相关信息。
默认开启，存放在var/log目录下

二进制日志

记录了所有数据操纵语言（DML）和数据定义语言(DDL)，但不包含查询语句
作用：
- 灾难时的数据恢复
- MySql的主从复制。MySql 8版本中，默认开始二进制日志

查询日志

查询日志记录了客户端的所有操作语句，而二进制日志不包含数据查询语句。
默认未开启。

慢查询日志

记录执行时间超过规定参数long_query_time 设置值并且扫描记录数不小于 min_examined_row_limit 的所有的SQL语句的日志，默认未开启

主从复制

概述:

主从复制是指将主数据库的 DDL 和 DML 操作通过二进制日志传到从库服务器中，然后在从库上对这些日志重新执行（也叫重做），从而使得从库和主库的数据保持同步。
流程
1、主库将数据库中数据的变化写入到 binlog
2、从库连接主库
3、从库会创建一个 I/O 线程向主库请求更新的 binlog
4、主库会创建一个 binlog dump 线程来发送 binlog ，从库中的 I/O 线程负责接收
5、从库的 I/O 线程将接收的 binlog 写入到 relay log（从库的中继日志）中。
6、从库的 SQL 线程读取 relay log 同步数据本地（也就是再执行一遍 SQL ）。
MySQL 复制的优点主要包含以下三个方面:
- 主库出现问题，可以快速切换到从库提供服务
- 实现读写分离，降低主库的访问压力
- 可以在从库中执行备份，以避免备份期间影响主库服务
复制的工作原理如上图所示，其中从服务器有2个线程，一个是I/O线程，负责读取主服务器的二进制日志，并将其保存为中继日志；另一个是SQL线程，复制执行中继日志

分库分表

介绍
随着互联网的发展，，应用系统的数据量也是成指数式增长，若采用单数据库进行数据存储，存在以下性能瓶颈：
- IO瓶颈：热点数据太多，数据库缓存不足，产生大量磁盘IO，效率较低。请求数据太多，带宽不够，网络IO瓶颈。
- CPU瓶颈：排序、分组、连接查询、聚合统计等SQL会耗费大量的CPU资源，请求数太多，CPU出现瓶颈。
为了解决以上问题，需要对数据库进行分库分表

分库分表的核心思想是将数据分散存储。使得单一数据库/表的数据量变小来缓解单一数据库的性能问题，从而达到提升数据库性能的目的
拆分策略：（水平拆分和垂直拆分）
垂直分库 ：以表为依据，根据业务将不同表拆分到不同库中。
- 特点：
- 每个库的表结构不一样
- 每个库的数据也不一样
- 所有库的并集是全量数据

垂直分表：以字段为依据，根据字段属性将不同字段拆分到不同表中。
- 特点：
  每个表的结构都不一样
- 每个表的数据也不一样
- 所有表的并集是全量数据
水平分库：以字段为依据，按照一定策略，将一个库的数据拆分到多个库中
- 特点：
- 每个库中的表结构是一样的
- 每个库的数据都不一样
- 所有库的并集是全量数据
水平分表：以字段为依据，按照一定策略，将一个表的数据拆分到多个表中
- 特点：
- 每个表中的数据结构是一样的
- 每个表的数据不一样
- 所有表的并集是全量数据

读写分离

是把对数据库的读和写操作分开,将数据库的读写操作分散到不同的数据库节点上。主数据库提供写操作，从数据库提供读操作，这样能有效地减轻单台数据库的压力。

面试题目

关系型数据库和非关系型数据库的区别

关系型数据库的优点
- 容易理解。因为它采用了关系模型来组织数据。
- 可以保持数据的一致性。
- 数据更新的开销比较小。
- 支持复杂查询（带where子句的查询）
非关系型数据库的优点
- 不需要经过SQL层的解析，读写效率高。
- 基于键值对，数据的扩展性很好。
- 可以支持多种类型数据的存储，如图片，文档等等。

什么是ORM？Mybatis

对象关系映射（Object Relational Mapping，简称ORM）模式是一种为了解决面向对象与关系数据库存在的互不匹配的现象的技术 。ORM框架是连接数据库的桥梁，只要提供了持久化类与表的映射关系，ORM框架在运行时就能参照映射文件的信息，把对象持久化到数据库中。
优点
- 提高开发效率，降低开发成本
- 是开发更加对象化
- 可移植
- 可以方便的引入数据缓存之类的附加功能
缺点
- 自动化进行关系数据库的映射需要消耗系统性能（其实还好）
- 在处理多表联查、where条件复杂之类的查询时，ORM的语法会变得复杂

如何判断一个索引创建的是否合理？

建议按照以下原则来设计索引：

避免对经常更新的表进行过多的索引，并且索引中的列要尽可能少。应该经常用于查询的字段创建索引，但要避免添加不必要的字段。
数量较小的表不要使用索引，因为查询的效率可能不如全局扫描。
在条件表达式中经常用到的不同值较多的列上建立索引，在不同值很少的列上不要建立索引。比如在学生表的“性别”字段上只有“男”与“女”两个不同值，因此就无须建立索引，如果建立索引不但不会提高查询效率，反而会严重降低数据更新速度
当唯一性是某种数据本身的特征时，指定唯一索引。使用唯一索引需能确保定义的列的数据完整性，以提高查询速度
在频繁进行排序或分组（即进行group by或order by操作）的列上建立索引，如果待排序的列有多个，可以在这些列上建立组合索引。

Count函数

执行效果上
- count(*)包括了所有的列，相当于行数，在统计结果的时候，不会忽略列值为NULL
- count(1)包括了忽略所有列，用1代表代码行，在统计结果的时候，不会忽略列值为NULL
- count(列名)只包括列名那一列，在统计结果的时候，会忽略列值为空（这里的空不是只空字符串或者0，而是表示null）的计数，即某个字段值为NULL时，不统计。
执行效率上
- 列名为主键，count(列名)会比count(1)快
- 列名不为主键，count(1)会比count(列名)快
- 如果表多个列并且没有主键，则 count（1）的执行效率优于 count（*）
- 如果有主键，则 select count（主键）的执行效率是最优的
- 如果表只有一个字段，则 select count（ *）最优。

count(可空字段) < count(非空字段) = count(主键 id) < count(1) ≈ count(*)

数据库中的三大范式？

第一范式：字段具有原子性，不可再分（字段单一职责）
第二范式：满足第一范式，每行应该被唯一区分，加一列存放每行的唯一标识符，称为主键（都要依赖主键）
第三范式：满足一二范式，且一个表不能包含其他表已存在的非主键信息（不间接依赖-不存在其他表的非主键信息）
范式优点：
◦ 范式化、重复数据少。更新快、修改少
缺点：
◦ 因为一个表不存在冗余重复数据，查询可能造成很多关联，效率变低，可能使一些索引策略无效，范式化将列存在不同表中，这些列若在同一个表中可以是一个索引

char和varchar的区别

char 是固定长度的，varchar 是可变长度的，也就是说，定义一个CHAR[10]和VARCHAR[10],如果存进去的是‘ABCD’, 那么CHAR所占的长度依然为10，除了字符‘ABCD’外，后面跟六个空格，而VARCHAR的长度变为4了，取数据的时候，CHAR类型的要用trim()去掉多余的空格，而VARCHAR类型是不需要的。
CHAR的存取速度要比VARCHAR快得多，因为其长度固定，方便程序的存储与查找；但是CHAR为此付出的是空间的代价，因为其长度固定，所以难免会有多余的空格占位符占据空间，可以说是以空间换取时间效率，而VARCHAR则是以空间效率为首位的。VARCHAR需要使用1或2个额外字节记录字符串的长度
CHAR的存储方式是，一个英文字符（ASCII）占用1个字节，一个汉字占用两个字节；而VARCHAR的存储方式是，一个英文字符占用2个字节，一个汉字也占用2个字节。

varchar(255)存多少个汉字

在字符集为UTF-8的情况下：

MySQL | version < 4.1：

VARCHAR以字节为单位存储，假设全部为常用汉字，则VARCHAR(255)共可存放约85个汉字。

MySQL | version >= 4.1：

VARCHAR以字符为单位存储，假设全部为常用汉字，则VARCHAR(255)可以存放255个汉字。

数据库设计或者开发功能中，要考虑什么细节

数据库和表的字符集统一使用 UTF8
所有表和字段都需要添加注释
尽量做到冷热数据分离,减小表的宽度
尽可能把所有列定义为 NOT NUL
禁止在数据库中存储图片,文件等大的二进制数据
优先选择符合存储需要的最小的数据类型
避免使用 TEXT,BLOB 数据类型，最常见的 TEXT 类型可以存储 64k 的数据，可以将text数据单独成表

InnoDB和MyISAM的区别

InnoDB聚簇索引，MyISAM非聚簇索引
InnoDB支持事务、外键、行锁表锁，MyISAM只支持表锁
select count(*)
MyISAM查询更优，InnoDB更新更优
MyISAM支持全文索引，InnoDB5.6后支持

一个表中没有创建索引，还会创建B+树吗

会，row_id隐藏字段自动创建索引

Sql语句

-创建表

CREAT TABLE 'table_name'{
	'ID' INT UNSIGNED AUTO_INCREMENT,
	'字段1' varchar(100) NOT NULL,
}ENGINE = InnoDB DEFUALT CHAREST = utf-8

学生成绩表（姓名，科目，成绩），查询每门课成绩都>80分的学生姓名。

select name from table group by name having min(score) > 80;
select name from table group by name having count(score) = sum(case when score > 80 then 1 else 0 end)

添加索引

ALTER TABLE `table_name` ADD PRIMARY KEY ( `column` )//主键索引
ALTER TABLE `table_name` ADD UNIQUE ( `column` )//唯一索引
ALTER TABLE `table_name` ADD INDEX index_name ( `column` )
ALTER TABLE `table_name` ADD INDEX index_name ( `column1`, `column2`, `column3` )//联合索引
ALTER TABLE <表名> ADD INDEX (<字段>);

sql注入

SQL注入即是指 web应用程序对用户输入数据的合法性没有判断或过滤不严，攻击者可以在web应用程序中事先定义好的查询语句的结尾上添加额外的 SQL语句，在管理员不知情的情况下实现非法操作，以此来实现欺骗数据库服务器执行非授权的任意查询，从而进一步得到相应的数据信息
如何阻止
- 过滤用户输入参数中的特殊字符，从而降低被 SQL 注入入的风险。
- 禁止通过字符拼接的 SQL 语句，严恪使用参数绑定传入的 SQL 参数。
- 合理使用数据库访问框架提供的防注入机制。比如 MyBatis 提供的＃｛｝绑定参数，从而防止 SQL注入。同时谨慎使用${ } , $｛｝相当于使用字符拼接 SQL。

数据库sql调优的几种方式

数据库调优方式

Sql语句的执行顺序

在这里插入图片描述

FROM：对SQL语句执行查询时，首先对关键字两边的表以笛卡尔积的形式执行连接，并产生一个虚表V1。虚表就是视图，数据会来自多张表的执行结果。
ON：对FROM连接的结果进行ON过滤,并创建虚表V2
JOIN：将ON过滤后的左表添加进来，并创建新的虚拟表V3
WHERE：对虚拟表V3进行WHERE筛选，创建虚拟表V4
GROUP BY：对V4中的记录进行分组操作，创建虚拟表V5
HAVING：对V5进行过滤，创建虚拟表V6
SELECT：将V6中的结果按照SELECT进行筛选，创建虚拟表V7
DISTINCT：对V7表中的结果进行去重操作，创建虚拟表V8，如果使用了GROUP BY子句则无需使用DISTINCT，因为分组的时候是将列中唯一的值分成一组，并且每组只返回一行记录，所以所有的记录都h是不同的。
ORDER BY：对V8表中的结果进行排序。

如何优化SQL查询

使用索引：
如果查询时没有使用索引，查询语句将扫描表中的所有记录。在数据量大的情况下，这样查询的速度会很慢。如果使用索引进行查询，查询语句可以根据索引快速定位到待查询记录，从而减少查询的记录数，达到提高查询速度的目的。
优化子查询：
使用子查询可以进行SELECT语句的嵌套查询，即一个SELECT查询的结果作为另一个SELECT语句的条件。子查询可以一次性完成很多逻辑上需要多个步骤才能完成的SQL操作。
子查询虽然可以使查询语句很灵活，但执行效率不高。执行子查询时，MySQL需要为内层查询语句的查询结果建立一个临时表。然后外层查询语句从临时表中查询记录。查询完毕后，再撤销这些临时表。因此，子查询的速度会受到一定的影响。如果查询的数据量比较大，这种影响就会随之增大。
在MySQL中，可以使用连接（JOIN）查询来替代子查询。连接查询不需要建立临时表，其速度比子查询要快，如果查询中使用索引，性能会更好。

Mysql删除数据

Delete用来删除表的全部或者一部分数据行，执行delete之后，用户需要提交(commmit)或者回滚(rollback)来执行删除或者撤销删除，会触发这个表上所有的delete触发器
Truncate删除表中的所有数据，这个操作不能回滚，也不会触发这个表上的触发器，TRUNCATE比delete更快，占用的空间更小
Drop命令从数据库中删除表，所有的数据行，索引和权限也会被删除，所有的DML触发器也不会被触发，这个命令也不能回滚。

Truncate和Delete的区别

DELETE 是 DML 类型的语句；TRUNCATE 是 DDL 类型的语句。它们都用来清空表中的数据。
DELETE 是逐行一条一条删除记录的；TRUNCATE 则是直接删除原来的表，再重新创建一个一模一样的新表，而不是逐行删除表中的数据，执行数据比 DELETE 快。因此需要删除表中全部的数据行时，尽量使用 TRUNCATE 语句，可以缩短执行时间。
DELETE 删除数据后，配合事件回滚可以找回数据；TRUNCATE 不支持事务的回滚，数据删除后无法找回。
DELETE 删除数据后，系统不会重新设置自增字段的计数器；TRUNCATE 清空表记录后，系统会重新设置自增字段的计数器。
DELETE 的使用范围更广，因为它可以通过 WHERE 子句指定条件来删除部分数据；而 TRUNCATE 不支持 WHERE 子句，只能删除整体。
DELETE 会返回删除数据的行数，但是 TRUNCATE 只会返回 0，没有任何意义。

Mysql如何优化

针对查询，我们可以通过使用索引、使用连接代替子查询的方式来提高查询速度。
针对慢查询，我们可以通过分析慢查询日志，来发现引起慢查询的原因，从而有针对性的进行优化。
针对插入，我们可以通过禁用索引、禁用检查等方式来提高插入速度，在插入之后再启用索引和检查。
针对数据库结构，我们可以通过将字段很多的表拆分成多张表、增加中间表、增加冗余字段等方式进行优化。

explain关注什么？

在这里插入图片描述
重点要关注如下几列：

列名	备注
type	本次查询表联接类型，从这里可以看到本次查询大概的效率。
key	最终选择的索引，如果没有索引的话，本次查询效率通常很差。
key_len	本次查询用于结果过滤的索引实际长度。
rows	预计需要扫描的记录数，预计需要扫描的记录数越小越好。
Extra	额外附加信息，主要确认是否出现 Using filesort、Using temporary 这两种情况。

Extra列需要注意以下的几种情况：

关键字	备注
Using filesort	将用外部排序而不是按照索引顺序排列结果，数据较少时从内存排序，否则需要在磁盘完成排序，代价非常高，需要添加合适的索引。
Using temporary	需要创建一个临时表来存储结果，这通常发生在对没有索引的列进行GROUP BY时，或者ORDER BY里的列不都在索引里，需要添加合适的索引。
Using index	表示MySQL使用覆盖索引避免全表扫描，不需要再到表中进行二次查找数据，这是比较好的结果之一。注意不要和type中的index类型混淆。
Using where	通常是进行了全表/全索引扫描后再用WHERE子句完成结果过滤，需要添加合适的索引。
Impossible WHERE	对Where子句判断的结果总是false而不能选择任何数据，例如where 1=0，无需过多关注。
Select tables optimized away	使用某些聚合函数来访问存在索引的某个字段时，优化器会通过索引直接一次定位到所需要的数据行完成整个查询，例如MIN()\MAX()，这种也是比较好的结果之一。

表中有千万条数据，该怎么办

建议按照如下顺序进行优化：

优化SQL和索引；
增加缓存，如memcached、redis；
读写分离，可以采用主从复制，也可以采用主主复制；
使用MySQL自带的分区表，这对应用是透明的，无需改代码，但SQL语句是要针对分区表做优化的；
做垂直拆分，即根据模块的耦合度，将一个大的系统分为多个小的系统；
做水平拆分，要选择一个合理的sharding key，为了有好的查询效率，表结构也要改动，做一定的冗余，应用也要改，sql中尽
带sharding key，将数据定位到限定的表上去查，而不是扫描全部的表。

Mysql的慢查询优化有了解嘛

优化MySQL的慢查询，可以按照如下步骤进行：

开启慢查询日志：
MySQL中慢查询日志默认是关闭的，可以通过配置文件my.ini或者my.cnf中的log-slow-queries选项打开，也可以在MySQL服务启动的时候使用–log-slow-queries[=file_name]启动慢查询日志。
启动慢查询日志时，需要在my.ini或者my.cnf文件中配置long_query_time选项指定记录阈值，如果某条查询语句的查询时间超过了这个值，这个查询过程将被记录到慢查询日志文件中。
分析慢查询日志：
直接分析mysql慢查询日志，利用explain关键字可以模拟优化器执行SQL查询语句，来分析sql慢查询语句。
常见慢查询优化：
- 索引没起作用的情况
  在使用LIKE关键字进行查询的查询语句中，如果匹配字符串的第一个字符为“%”，索引不会起作用。只有“%”不在第一个位置，索引才会起作用。
  MySQL可以为多个字段创建索引。一个索引可以包括16个字段。对于多列索引，只有查询条件中使用了这些字段中的第1个字段时索引才会被使用。
  查询语句的查询条件中只有OR关键字，且OR前后的两个条件中的列都是索引时，查询中才使用索引。否则，查询将不使用索引。
- 优化数据库结构
  对于字段比较多的表，如果有些字段的使用频率很低，可以将这些字段分离出来形成新表。因为当一个表的数据量很大时，会由于使用频率低的字段的存在而变慢。
  对于需要经常联合查询的表，可以建立中间表以提高查询效率。通过建立中间表，把需要经常联合查询的数据插入到中间表中，然后将原来的联合查询改为对中间表的查询，以此来提高查询效率。
- 分解关联查询
  很多高性能的应用都会对关联查询进行分解，就是可以对每一个表进行一次单表查询，然后将查询结果在应用程序中进行关联，很多场景下这样会更高效。
- 优化LIMIT分页
  当偏移量非常大的时候，例如可能是limit 10000,20这样的查询，这是mysql需要查询10020条然后只返回最后20条，前面的10000条记录都将被舍弃，这样的代价很高。优化此类查询的一个最简单的方法是尽可能的使用索引覆盖扫描，而不是查询所有的列。然后根据需要做一次关联操作再返回所需的列。对于偏移量很大的时候这样做的效率会得到很大提升。

mysql查询语句加锁

lock in share mode 加入共享锁
for update 加入排他锁

百万级数据如何删除

删除数据的速度和创建的索引数量是成正比的。先删索引，再删无用数据，再创建索引

普通索引和唯一索引怎么选择

查询比较
- 查询会以页为单位将数据页加载进内存，不需要一条记录一条记录读取磁盘。然后唯一索引根据条件查询到记录时就返回结果，普通索引查到第一条记录往后遍历直到不满足条件，由于都在内存中，不需要磁盘读取那么大开销，带来的额外查询开销忽略不计，所以查询性能几乎一致
更新比较
- 唯一索引由于更新时要检查唯一性，所以需要将数据页先加载进内存才能判断，此时直接操作内存，不需要操作change buffer
- 补充：普通索引若数据再内存中直接内存中更新，否则会将更新操作先记录到channge buffer中，等下一次查询将数据读到内存中再进行change buffer里相关更新操作后将数据返回，这样一来，再写多读少的情况下就减少了磁盘IO，若写完就马上查询，就大可不必用change buffer，不但没提高多少效率还造成维护change buffer额外消耗
- 将change buffer的操作对应到原始数据页的操作称为merge（可以查询来时读到内存再修改数据，后台线程也会merge，数据库正常关闭也会merge）
适合场景
- 写多读少，选用普通索引更好，可以利用change buffer进行性能优化减少磁盘IO，将更新操作记录到change bufer，等查询来了将数据读到内存再进行修改.

MySql的底层结构

一条sql语句的执行流程

在这里插入图片描述

1、首先由连接器进行身份验证，权限管理
2、若开启了缓存，会检查缓存是否有该sql对应结果（缓存存储形式key-vlaue，key是执行的sql，value是对应的值）若开启缓存又有该sql的映射，将结果直接返回；
3、分析器进行词法语法分析
4、优化器会生成执行计划、选择索引等操作，选取最优执行方案
5、然后来到执行器，打开表调用存储引擎接口，逐行判断是否满足查询条件，满足放到结果集，最终返回给客户端；若用到索引，筛选行也会根据索引筛选。

两阶段提交（一条更新语句怎么执行？）

在这里插入图片描述

1、引擎先根据筛选条件筛选对应的行返回给执行器（若对应的行在内存直接返回，否则先去磁盘读取再返回）

2、执行器执行相关更新操作然后调用引擎接口写回更新后数据

3、引擎将新数据更新到内存，将更新操作记录到redolog，redolog处于prepare，告知执行器执行完，可提交事务

4、执行器生成该操作的binlog 并将binlog写入磁盘

5、执行器调用引擎事务提交接口，引擎把刚写入的redolog改为commit状态，更新完成。

没有代码的程序

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
1
评论
MySql

为了权衡【隔离】和【并发】的矛盾，ISO定义了四个事务的隔离级别，每个级别的隔离程度不同，允许的副作用也不通READ-UNCOMMITTED(读取未提交)最低的隔离级别，允许读取尚未提交的数据变更，可能会导致脏读、幻读或不可重复读。READ-COMMITTED(读取已提交)允许读取并发事务已经提交的数据，可以阻止脏读，但是幻读或不可重复读仍有可能发生•REPEATABLE-READ(可重复读)
复制链接

扫一扫