ToB企服应用市场:ToB评测及商务社交产业平台

标题: 一天吃透MySQL面试八股文 [打印本页]
作者: 干翻全岛蛙蛙    时间: 2023-12-11 05:12
标题: 一天吃透MySQL面试八股文
目录
内容摘自我的学习网站：topjavaer.cn
什么是MySQL

MySQL是一个关系型数据库，它采用表的形式来存储数据。你可以理解成是Excel表格，既然是表的形式存储数据，就有表结构（行和列）。行代表每一行数据，列代表该行中的每个值。列上的值是有数据类型的，比如：整数、字符串、日期等等。
数据库的三大范式

第一范式1NF
确保数据库表字段的原子性。
比如字段 userInfo: 广东省 10086' ，依照第一范式必须拆分成 userInfo: 广东省  userTel: 10086两个字段。
第二范式2NF
首先要满足第一范式，另外包含两部分内容，一是表必须有一个主键；二是非主键列必须完全依赖于主键，而不能只依赖于主键的一部分。
举个例子。假定选课关系表为student_course(student_no, student_name, age, course_name, grade, credit)，主键为(student_no, course_name)。其中学分完全依赖于课程名称，姓名年龄完全依赖学号，不符合第二范式，会导致数据冗余（学生选n门课，姓名年龄有n条记录）、插入异常（插入一门新课，因为没有学号，无法保存新课记录）等问题。
应该拆分成三个表：学生：student(stuent_no, student_name, 年龄)；课程：course(course_name, credit)；选课关系：student_course_relation(student_no, course_name, grade)。
第三范式3NF
首先要满足第二范式，另外非主键列必须直接依赖于主键，不能存在传递依赖。即不能存在：非主键列 A 依赖于非主键列 B，非主键列 B 依赖于主键的情况。
假定学生关系表为Student(student_no, student_name, age, academy_id, academy_telephone)，主键为"学号"，其中学院id依赖于学号，而学院地点和学院电话依赖于学院id，存在传递依赖，不符合第三范式。
可以把学生关系表分为如下两个表：学生：(student_no, student_name, age, academy_id)；学院：(academy_id, academy_telephone)。
2NF和3NF的区别？
首先分享一份大彬精心整理的大厂面试手册，包含计算机基础、Java基础、多线程、JVM、数据库、Redis、Spring、Mybatis、SpringMVC、SpringBoot、分布式、微服务、设计模式、架构、校招社招分享等高频面试题，非常实用，有小伙伴靠着这份手册拿过字节offer~


需要的小伙伴可以自行下载：
http://mp.weixin.qq.com/s?__biz=Mzg2OTY1NzY0MQ==&mid=2247485445&idx=1&sn=1c6e224b9bb3da457f5ee03894493dbc&chksm=ce98f543f9ef7c55325e3bf336607a370935a6c78dbb68cf86e59f5d68f4c51d175365a189f8#rd
事务的四大特性？

事务特性ACID：原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）、持久性（Durability）。
事务隔离级别有哪些？

先了解下几个概念：脏读、不可重复读、幻读。
不可重复读和脏读的区别是，脏读是某一事务读取了另一个事务未提交的脏数据，而不可重复读则是读取了前一事务提交的数据。
事务隔离就是为了解决上面提到的脏读、不可重复读、幻读这几个问题。
MySQL数据库为我们提供的四种隔离级别：
查看隔离级别：
  1. select @@transaction_isolation;
复制代码
设置隔离级别：
  1. set session transaction isolation level read uncommitted;
复制代码
生产环境数据库一般用的什么隔离级别呢？

生产环境大多使用RC。为什么不是RR呢？
可重复读(Repeatable Read)，简称为RR
读已提交(Read Commited)，简称为RC
缘由一：在RR隔离级别下，存在间隙锁，导致出现死锁的几率比RC大的多！
缘由二：在RR隔离级别下，条件列未命中索引会锁表！而在RC隔离级别下，只锁行!
也就是说，RC的并发性高于RR。
并且大部分场景下，不可重复读问题是可以接受的。毕竟数据都已经提交了，读出来本身就没有太大问题！
链接（可点击）：很全面的Java面试网站
编码和字符集的关系

我们平时可以在编辑器上输入各种中文英文字母，但这些都是给人读的，不是给计算机读的，其实计算机真正保存和传输数据都是以二进制0101的格式进行的。
那么就需要有一个规则，把中文和英文字母转化为二进制。其中d对应十六进制下的64，它可以转换为01二进制的格式。于是字母和数字就这样一一对应起来了，这就是ASCII编码格式。
它用一个字节，也就是8位来标识字符，基础符号有128个，扩展符号也是128个。也就只能表示下英文字母和数字。
这明显不够用。于是，为了标识中文，出现了GB2312的编码格式。为了标识希腊语，出现了greek编码格式，为了标识俄语，整了cp866编码格式。
为了统一它们，于是出现了Unicode编码格式，它用了2~4个字节来表示字符，这样理论上所有符号都能被收录进去，并且它还完全兼容ASCII的编码，也就是说，同样是字母d，在ASCII用64表示，在Unicode里还是用64来表示。
但不同的地方是ASCII编码用1个字节来表示，而Unicode用则两个字节来表示。
同样都是字母d，unicode比ascii多使用了一个字节，如下：
  1. D   ASCII:           01100100
  2. D Unicode:  00000000 01100100
复制代码
可以看到，上面的unicode编码，前面的都是0，其实用不上，但还占了个字节，有点浪费。如果我们能做到该隐藏时隐藏，这样就能省下不少空间，按这个思路，就是就有了UTF-8编码。
总结一下，按照一定规则把符号和二进制码对应起来，这就是编码。而把n多这种已经编码的字符聚在一起，就是我们常说的字符集。
比如utf-8字符集就是所有utf-8编码格式的字符的合集。
想看下mysql支持哪些字符集。可以执行 show charset;
utf8和utf8mb4的区别

上面提到utf-8是在unicode的基础上做的优化，既然unicode有办法表示所有字符，那utf-8也一样可以表示所有字符，为了避免混淆，我在后面叫它大utf8。
mysql支持的字符集中有utf8和utf8mb4。
先说utf8mb4编码，mb4就是most bytes 4的意思，从上图最右边的Maxlen可以看到，它最大支持用4个字节来表示字符，它几乎可以用来表示目前已知的所有的字符。
再说mysql字符集里的utf8，它是数据库的默认字符集。但注意，此utf8非彼utf8，我们叫它小utf8字符集。为什么这么说，因为从Maxlen可以看出，它最多支持用3个字节去表示字符，按utf8mb4的命名方式，准确点应该叫它utf8mb3。
utf8 就像是阉割版的utf8mb4，只支持部分字符。比如emoji表情，它就不支持。
而mysql支持的字符集里，第三列，collation，它是指字符集的比较规则。
比如，"debug"和"Debug"是同一个单词，但它们大小写不同，该不该判为同一个单词呢。
这时候就需要用到collation了。
通过SHOW COLLATION WHERE Charset = 'utf8mb4';可以查看到utf8mb4下支持什么比较规则。

如果collation = utf8mb4_general_ci，是指使用utf8mb4字符集的前提下，挨个字符进行比较（general），并且不区分大小写（_ci，case insensitice）。
这种情况下，"debug"和"Debug"是同一个单词。
如果改成collation=utf8mb4_bin，就是指挨个比较二进制位大小。
于是"debug"和"Debug"就不是同一个单词。
那utf8mb4对比utf8有什么劣势吗？
我们知道数据库表里，字段类型如果是char(2)的话，里面的2是指字符个数，也就是说不管这张表用的是什么编码的字符集，都能放上2个字符。
而char又是固定长度，为了能放下2个utf8mb4的字符，char会默认保留2*4（maxlen=4）= 8个字节的空间。
如果是utf8mb3，则会默认保留 2 * 3 (maxlen=3) = 6个字节的空间。也就是说，在这种情况下，utf8mb4会比utf8mb3多使用一些空间。
索引

什么是索引？

索引是存储引擎用于提高数据库表的访问速度的一种数据结构。它可以比作一本字典的目录，可以帮你快速找到对应的记录。
索引一般存储在磁盘的文件中，它是占用物理空间的。
索引的优缺点？

优点：
缺点：
给大家分享一个Github仓库，上面有大彬整理的300多本经典的计算机书籍PDF，包括C语言、C++、Java、Python、前端、数据库、操作系统、计算机网络、数据结构和算法、机器学习、编程人生等，可以star一下，下次找书直接在上面搜索，仓库持续更新中！Github地址

索引的作用？

数据是存储在磁盘上的，查询数据时，如果没有索引，会加载所有的数据到内存，依次进行检索，读取磁盘次数较多。有了索引，就不需要加载所有数据，因为B+树的高度一般在2-4层，最多只需要读取2-4次磁盘，查询速度大大提升。
什么情况下需要建索引？

什么情况下不建索引？

索引的数据结构

索引的数据结构主要有B+树和哈希表，对应的索引分别为B+树索引和哈希索引。InnoDB引擎的索引类型有B+树索引和哈希索引，默认的索引类型为B+树索引。
B+树索引
B+ 树是基于B 树和叶子节点顺序访问指针进行实现，它具有B树的平衡性，并且通过顺序访问指针来提高区间查询的性能。
在 B+ 树中，节点中的 key 从左到右递增排列，如果某个指针的左右相邻 key 分别是 keyi 和 keyi+1，则该指针指向节点的所有 key 大于等于 keyi 且小于等于 keyi+1。

进行查找操作时，首先在根节点进行二分查找，找到key所在的指针，然后递归地在指针所指向的节点进行查找。直到查找到叶子节点，然后在叶子节点上进行二分查找，找出key所对应的数据项。
MySQL 数据库使用最多的索引类型是BTREE索引，底层基于B+树数据结构来实现。
  1. mysql> show index from blog\G;
  2. *************************** 1. row ***************************
  3.         Table: blog
  4.    Non_unique: 0
  5.      Key_name: PRIMARY
  6. Seq_in_index: 1
  7.   Column_name: blog_id
  8.     Collation: A
  9.   Cardinality: 4
  10.      Sub_part: NULL
  11.        Packed: NULL
  12.          Null:
  13.    Index_type: BTREE
  14.       Comment:
  15. Index_comment:
  16.       Visible: YES
  17.    Expression: NULL
复制代码
哈希索引
哈希索引是基于哈希表实现的，对于每一行数据，存储引擎会对索引列进行哈希计算得到哈希码，并且哈希算法要尽量保证不同的列值计算出的哈希码值是不同的，将哈希码的值作为哈希表的key值，将指向数据行的指针作为哈希表的value值。这样查找一个数据的时间复杂度就是O(1)，一般多用于精确查找。
Hash索引和B+树索引的区别？

为什么B+树比B树更适合实现数据库索引？

索引有什么分类？

1、主键索引：名为primary的唯一非空索引，不允许有空值。
2、唯一索引：索引列中的值必须是唯一的，但是允许为空值。唯一索引和主键索引的区别是：唯一索引字段可以为null且可以存在多个null值，而主键索引字段不可以为null。唯一索引的用途：唯一标识数据库表中的每条记录，主要是用来防止数据重复插入。创建唯一索引的SQL语句如下：
  1. ALTER TABLE table_name
  2. ADD CONSTRAINT constraint_name UNIQUE KEY(column_1,column_2,...);
复制代码
3、组合索引：在表中的多个字段组合上创建的索引，只有在查询条件中使用了这些字段的左边字段时，索引才会被使用，使用组合索引时需遵循最左前缀原则。
4、全文索引：只能在CHAR、VARCHAR和TEXT类型字段上使用全文索引。
5、普通索引：普通索引是最基本的索引，它没有任何限制，值可以为空。
什么是最左匹配原则？

如果 SQL 语句中用到了组合索引中的最左边的索引，那么这条 SQL 语句就可以利用这个组合索引去进行匹配。当遇到范围查询(>、 3 and d = 4，那么a、b和c三个字段能用到索引，而d无法使用索引。因为遇到了范围查询。
如下图，对(a, b) 建立索引，a 在索引树中是全局有序的，而 b 是全局无序，局部有序（当a相等时，会根据b进行排序）。直接执行b = 2这种查询条件无法使用索引。

当a的值确定的时候，b是有序的。例如a = 1时，b值为1，2是有序的状态。当a = 2时候，b的值为1，4也是有序状态。 当执行a = 1 and b = 2时a和b字段能用到索引。而执行a > 1 and b = 2时，a字段能用到索引，b字段用不到索引。因为a的值此时是一个范围，不是固定的，在这个范围内b值不是有序的，因此b字段无法使用索引。
什么是聚集索引？

InnoDB使用表的主键构造主键索引树，同时叶子节点中存放的即为整张表的记录数据。聚集索引叶子节点的存储是逻辑上连续的，使用双向链表连接，叶子节点按照主键的顺序排序，因此对于主键的排序查找和范围查找速度比较快。
聚集索引的叶子节点就是整张表的行记录。InnoDB 主键使用的是聚簇索引。聚集索引要比非聚集索引查询效率高很多。
对于InnoDB来说，聚集索引一般是表中的主键索引，如果表中没有显示指定主键，则会选择表中的第一个不允许为NULL的唯一索引。如果没有主键也没有合适的唯一索引，那么InnoDB内部会生成一个隐藏的主键作为聚集索引，这个隐藏的主键长度为6个字节，它的值会随着数据的插入自增。
什么是覆盖索引？

select的数据列只用从索引中就能够取得，不需要回表进行二次查询，也就是说查询列要被所使用的索引覆盖。对于innodb表的二级索引，如果索引能覆盖到查询的列，那么就可以避免对主键索引的二次查询。
不是所有类型的索引都可以成为覆盖索引。覆盖索引要存储索引列的值，而哈希索引、全文索引不存储索引列的值，所以MySQL使用b+树索引做覆盖索引。
对于使用了覆盖索引的查询，在查询前面使用explain，输出的extra列会显示为using index。
比如user_like 用户点赞表，组合索引为(user_id, blog_id)，user_id和blog_id都不为null。
  1. explain select blog_id from user_like where user_id = 13;
复制代码
explain结果的Extra列为Using index，查询的列被索引覆盖，并且where筛选条件符合最左前缀原则，通过索引查找就能直接找到符合条件的数据，不需要回表查询数据。
  1. explain select user_id from user_like where blog_id = 1;
复制代码
explain结果的Extra列为Using where; Using index， 查询的列被索引覆盖，where筛选条件不符合最左前缀原则，无法通过索引查找找到符合条件的数据，但可以通过索引扫描找到符合条件的数据，也不需要回表查询数据。

索引的设计原则？

索引什么时候会失效？

导致索引失效的情况：
什么是前缀索引？

有时需要在很长的字符列上创建索引，这会造成索引特别大且慢。使用前缀索引可以避免这个问题。
前缀索引是指对文本或者字符串的前几个字符建立索引，这样索引的长度更短，查询速度更快。
创建前缀索引的关键在于选择足够长的前缀以保证较高的索引选择性。索引选择性越高查询效率就越高，因为选择性高的索引可以让MySQL在查找时过滤掉更多的数据行。
建立前缀索引的方式：
  1. // email列创建前缀索引
  2. ALTER TABLE table_name ADD KEY(column_name(prefix_length));
复制代码
索引下推

参考我的另一篇文章：图解索引下推！
常见的存储引擎有哪些？

MySQL中常用的四种存储引擎分别是： MyISAM、InnoDB、MEMORY、ARCHIVE。MySQL 5.5版本后默认的存储引擎为InnoDB。
InnoDB存储引擎
InnoDB是MySQL默认的事务型存储引擎，使用最广泛，基于聚簇索引建立的。InnoDB内部做了很多优化，如能够自动在内存中创建自适应hash索引，以加速读操作。
优点：支持事务和崩溃修复能力；引入了行级锁和外键约束。
缺点：占用的数据空间相对较大。
适用场景：需要事务支持，并且有较高的并发读写频率。
MyISAM存储引擎
数据以紧密格式存储。对于只读数据，或者表比较小、可以容忍修复操作，可以使用MyISAM引擎。MyISAM会将表存储在两个文件中，数据文件.MYD和索引文件.MYI。
优点：访问速度快。
缺点：MyISAM不支持事务和行级锁，不支持崩溃后的安全恢复，也不支持外键。
适用场景：对事务完整性没有要求；表的数据都会只读的。
MEMORY存储引擎
MEMORY引擎将数据全部放在内存中，访问速度较快，但是一旦系统奔溃的话，数据都会丢失。
MEMORY引擎默认使用哈希索引，将键的哈希值和指向数据行的指针保存在哈希索引中。
优点：访问速度较快。
缺点：
ARCHIVE存储引擎
ARCHIVE存储引擎非常适合存储大量独立的、作为历史记录的数据。ARCHIVE提供了压缩功能，拥有高效的插入速度，但是这种引擎不支持索引，所以查询性能较差。
MyISAM和InnoDB的区别？

MySQL有哪些锁？

按锁粒度分类，有行级锁、表级锁和页级锁。
按锁级别分类，有共享锁、排他锁和意向锁。
意向共享锁（IS）：表示事务准备给数据行加入共享锁，也就是说一个数据行加共享锁前必须先取得该表的IS锁；
意向排他锁（IX）：类似上面，表示事务准备给数据行加入排他锁，说明事务在一个数据行加排他锁前必须先取得该表的IX锁。
意向锁是 InnoDB 自动加的，不需要用户干预。
对于INSERT、UPDATE和DELETE，InnoDB 会自动给涉及的数据加排他锁；对于一般的SELECT语句，InnoDB 不会加任何锁，事务可以通过以下语句显式加共享锁或排他锁。
共享锁：SELECT … LOCK IN SHARE MODE;
排他锁：SELECT … FOR UPDATE;
MVCC 实现原理？

MVCC(Multiversion concurrency control) 就是同一份数据保留多版本的一种方式，进而实现并发控制。在查询的时候，通过read view和版本链找到对应版本的数据。
作用：提升并发性能。对于高并发场景，MVCC比行级锁开销更小。
MVCC 实现原理如下：
MVCC 的实现依赖于版本链，版本链是通过表的三个隐藏字段实现。
每条表记录大概是这样的：

使用事务更新行记录的时候，就会生成版本链，执行过程如下：
下面举个例子方便大家理解。
1、初始数据如下，其中DB_ROW_ID和DB_ROLL_PTR为空。

2、事务A对该行数据做了修改，将age修改为12，效果如下：

3、之后事务B也对该行记录做了修改，将age修改为8，效果如下：

4、此时undo log有两行记录，并且通过回滚指针连在一起。
接下来了解下read view的概念。
read view可以理解成将数据在每个时刻的状态拍成“照片”记录下来。在获取某时刻t的数据时，到t时间点拍的“照片”上取数据。
在read view内部维护一个活跃事务链表，表示生成read view的时候还在活跃的事务。这个链表包含在创建read view之前还未提交的事务，不包含创建read view之后提交的事务。
不同隔离级别创建read view的时机不同。
read view的记录筛选方式
前提：DATA_TRX_ID 表示每个数据行的最新的事务ID；up_limit_id表示当前快照中的最先开始的事务；low_limit_id表示当前快照中的最慢开始的事务，即最后一个事务。

<ul>如果DATA_TRX_ID < up_limit_id：说明在创建read view时，修改该数据行的事务已提交，该版本的记录可被当前事务读取到。
如果DATA_TRX_ID >= low_limit_id：说明当前版本的记录的事务是在创建read view之后生成的，该版本的数据行不可以被当前事务访问。此时需要通过版本链找到上一个版本，然后重新判断该版本的记录对当前事务的可见性。
如果up_limit_id  truncate > delete。
</ol>having和where区别？

为什么要做主从同步？

什么是MySQL主从同步？

主从同步使得数据可以从一个数据库服务器复制到其他服务器上，在复制数据时，一个服务器充当主服务器（master），其余的服务器充当从服务器（slave）。
因为复制是异步进行的，所以从服务器不需要一直连接着主服务器，从服务器甚至可以通过拨号断断续续地连接主服务器。通过配置文件，可以指定复制所有的数据库，某个数据库，甚至是某个数据库上的某个表。
乐观锁和悲观锁是什么？

数据库中的并发控制是确保在多个事务同时存取数据库中同一数据时不破坏事务的隔离性和统一性以及数据库的统一性。乐观锁和悲观锁是并发控制主要采用的技术手段。
用过processlist吗？

show processlist 或 show full processlist 可以查看当前 MySQL 是否有压力，正在运行的SQL，有没有慢SQL正在执行。返回参数如下：
MySQL查询 limit 1000,10 和limit 10 速度一样快吗？

两种查询方式。对应 limit offset, size 和 limit size 两种方式。
而其实 limit size ，相当于  limit 0, size。也就是从0开始取size条数据。
也就是说，两种方式的区别在于offset是否为0。
先来看下limit sql的内部执行逻辑。
MySQL内部分为server层和存储引擎层。一般情况下存储引擎都用innodb。
server层有很多模块，其中需要关注的是执行器是用于跟存储引擎打交道的组件。
执行器可以通过调用存储引擎提供的接口，将一行行数据取出，当这些数据完全符合要求（比如满足其他where条件），则会放到结果集中，最后返回给调用mysql的客户端。
以主键索引的limit执行过程为例：
执行select * from xxx order by id limit 0, 10;，select后面带的是星号，也就是要求获得行数据的所有字段信息。
server层会调用innodb的接口，在innodb里的主键索引中获取到第0到10条完整行数据，依次返回给server层，并放到server层的结果集中，返回给客户端。
把offset搞大点，比如执行的是：select * from xxx order by id limit 500000, 10;
server层会调用innodb的接口，由于这次的offset=500000，会在innodb里的主键索引中获取到第0到（500000 + 10）条完整行数据，返回给server层之后根据offset的值挨个抛弃，最后只留下最后面的size条，也就是10条数据，放到server层的结果集中，返回给客户端。
可以看出，当offset非0时，server层会从引擎层获取到很多无用的数据，而获取的这些无用数据都是要耗时的。
因此，mysql查询中 limit 1000,10 会比 limit 10 更慢。原因是 limit 1000,10 会取出1000+10条数据，并抛弃前1000条，这部分耗时更大。
高度为3的B+树，可以存放多少数据？

InnoDB存储引擎有自己的最小储存单元——页（Page）。
查询InnoDB页大小的命令如下：
  1. insert into user(user_name, user_password, user_mail, user_state) values('tyson', 'a', 'a', 0);
复制代码
可以看出 innodb 默认的一页大小为 16384B = 16384/1024 = 16kb。
在MySQL中，B+树一个节点的大小设为一页或页的倍数最为合适。因为如果一个节点的大小 < 1页，那么读取这个节点的时候其实读取的还是一页，这样就造成了资源的浪费。
B+树中非叶子节点存的是key + 指针；叶子节点存的是数据行。
对于叶子节点，如果一行数据大小为1k，那么一页就能存16条数据。
对于非叶子节点，如果key使用的是bigint，则为8字节，指针在MySQL中为6字节，一共是14字节，则16k能存放 16 * 1024 / 14 = 1170 个索引指针。
于是可以算出，对于一颗高度为2的B+树，根节点存储索引指针节点，那么它有1170个叶子节点存储数据，每个叶子节点可以存储16条数据，一共 1170 x 16 = 18720 条数据。而对于高度为3的B+树，就可以存放 1170 x 1170 x 16 = 21902400 条数据（两千多万条数据），也就是对于两千多万条的数据，我们只需要高度为3的B+树就可以完成，通过主键查询只需要3次IO操作就能查到对应数据。
所以在 InnoDB 中B+树高度一般为3层时，就能满足千万级的数据存储。
深分页怎么优化？

还是以上面的SQL为空：select * from xxx order by id limit 500000, 10;
方法一：
从上面的分析可以看出，当offset非常大时，server层会从引擎层获取到很多无用的数据，而当select后面是*号时，就需要拷贝完整的行信息，拷贝完整数据相比只拷贝行数据里的其中一两个列字段更耗费时间。
因为前面的offset条数据最后都是不要的，没有必要拷贝完整字段，所以可以将sql语句修改成：
  1. update user set user_name = 'a';
复制代码
先执行子查询 select id from xxx by id limit 500000, 1, 这个操作，其实也是将在innodb中的主键索引中获取到500000+1条数据，然后server层会抛弃前500000条，只保留最后一条数据的id。
但不同的地方在于，在返回server层的过程中，只会拷贝数据行内的id这一列，而不会拷贝数据行的所有列，当数据量较大时，这部分的耗时还是比较明显的。
在拿到了上面的id之后，假设这个id正好等于500000，那sql就变成了
  1. select * from table where id<6 lock in share mode;--共享锁
  2. select * from table where id<6 for update;--排他锁
复制代码
这样innodb再走一次主键索引，通过B+树快速定位到id=500000的行数据，时间复杂度是lg(n)，然后向后取10条数据。
方法二：
将所有的数据根据id主键进行排序，然后分批次取，将当前批次的最大id作为下次筛选的条件进行查询。
  1. CREATE TABLE test_range_partition(
  2.        id INT auto_increment,
  3.        createdate DATETIME,
  4.        primary key (id,createdate)
  5.    )
  6.    PARTITION BY RANGE (TO_DAYS(createdate) ) (
  7.       PARTITION p201801 VALUES LESS THAN ( TO_DAYS('20180201') ),
  8.       PARTITION p201802 VALUES LESS THAN ( TO_DAYS('20180301') ),
  9.       PARTITION p201803 VALUES LESS THAN ( TO_DAYS('20180401') ),
  10.       PARTITION p201804 VALUES LESS THAN ( TO_DAYS('20180501') ),
  11.       PARTITION p201805 VALUES LESS THAN ( TO_DAYS('20180601') ),
  12.       PARTITION p201806 VALUES LESS THAN ( TO_DAYS('20180701') ),
  13.       PARTITION p201807 VALUES LESS THAN ( TO_DAYS('20180801') ),
  14.       PARTITION p201808 VALUES LESS THAN ( TO_DAYS('20180901') ),
  15.       PARTITION p201809 VALUES LESS THAN ( TO_DAYS('20181001') ),
  16.       PARTITION p201810 VALUES LESS THAN ( TO_DAYS('20181101') ),
  17.       PARTITION p201811 VALUES LESS THAN ( TO_DAYS('20181201') ),
  18.       PARTITION p201812 VALUES LESS THAN ( TO_DAYS('20190101') )
  19.    );
复制代码
通过主键索引，每次定位到start_id的位置，然后往后遍历10个数据，这样不管数据多大，查询性能都较为稳定。
大表查询慢怎么优化？

某个表有近千万数据，查询比较慢，如何优化？
当MySQL单表记录数过大时，数据库的性能会明显下降，一些常见的优化措施如下：
MySQL单表多大进行分库分表？

目前主流的有两种说法：
事实上，这个数值和实际记录的条数无关，而与 MySQL 的配置以及机器的硬件有关。因为MySQL为了提高性能，会将表的索引装载到内存中。在InnoDB buffer size 足够的情况下，其能完成全加载进内存，查询不会有问题。但是，当单表数据库到达某个量级的上限时，导致内存无法存储其索引，使得之后的 SQL 查询会产生磁盘 IO，从而导致性能下降。当然，这个还有具体的表结构的设计有关，最终导致的问题都是内存限制。
因此，对于分库分表，需要结合实际需求，不宜过度设计，在项目一开始不采用分库与分表设计，而是随着业务的增长，在无法继续优化的情况下，再考虑分库与分表提高系统的性能。对此，阿里巴巴《Java 开发手册》补充到：如果预计三年后的数据量根本达不到这个级别，请不要在创建表时就分库分表。
至于MySQL单表多大进行分库分表，应当根据机器资源进行评估。
说说count(1)、count(*)和count(字段名)的区别

嗯，先说说count(1) and count(字段名)的区别。
两者的主要区别是
接下来看看三者之间的区别。
执行效果上：
执行效率上：
MySQL中DATETIME 和 TIMESTAMP有什么区别？

嗯，TIMESTAMP和DATETIME都可以用来存储时间，它们主要有以下区别：
1.表示范围
TIMESTAMP支持的时间范围比DATATIME要小，容易出现超出的情况。
2.空间占用
3.存入时间是否会自动转换
TIMESTAMP类型在默认情况下，insert、update 数据时，TIMESTAMP列会自动以当前时间（CURRENT_TIMESTAMP）填充/更新。DATETIME则不会做任何转换，也不会检测时区，你给什么数据，它存什么数据。
4.TIMESTAMP比较受时区timezone的影响以及MYSQL版本和服务器的SQL MODE的影响。因为TIMESTAMP存的是时间戳，在不同的时区得出的时间不一致。
5.如果存进NULL，两者实际存储的值不同。
说说为什么不建议用外键？

外键是一种约束，这个约束的存在，会保证表间数据的关系始终完整。外键的存在，并非全然没有优点。
外键可以保证数据的完整性和一致性，级联操作方便。而且使用外键可以将数据完整性判断托付给了数据库完成，减少了程序的代码量。
虽然外键能够保证数据的完整性，但是会给系统带来很多缺陷。
1、并发问题。在使用外键的情况下，每次修改数据都需要去另外一个表检查数据，需要获取额外的锁。若是在高并发大流量事务场景，使用外键更容易造成死锁。
2、扩展性问题。比如从MySQL迁移到Oracle，外键依赖于数据库本身的特性，做迁移可能不方便。
3、不利于分库分表。在水平拆分和分库的情况下，外键是无法生效的。将数据间关系的维护，放入应用程序中，为将来的分库分表省去很多的麻烦。
使用自增主键有什么好处？

自增主键可以让主键索引尽量地保持递增顺序插入，避免了页分裂，因此索引更紧凑，在查询的时候，效率也就更高。
InnoDB的自增值为什么不能回收利用？

主要为了提升插入数据的效率和并行度。
假设有两个并行执行的事务，在申请自增值的时候，为了避免两个事务申请到相同的自增 id，肯定要加锁，然后顺序申请。
假设事务 A 申请到了 id=2， 事务 B 申请到 id=3，那么这时候表 t 的自增值是 4，之后继续执行。
事务 B 正确提交了，但事务 A 出现了唯一键冲突。
如果允许事务 A 把自增 id 回退，也就是把表 t 的当前自增值改回 2，那么就会出现这样的情况：表里面已经有 id=3 的行，而当前的自增 id 值是 2。
接下来，继续执行的其他事务就会申请到 id=2，然后再申请到 id=3。这时，就会出现插入语句报错“主键冲突”。
而为了解决这个主键冲突，有两种方法：
可见，这两个方法都会导致性能问题。
因此，InnoDB 放弃了“允许自增 id 回退”这个设计，语句执行失败也不回退自增 id。
自增主键保存在什么地方？

不同的引擎对于自增值的保存策略不同：
自增主键一定是连续的吗？

不一定，有几种情况会导致自增主键不连续。
1、唯一键冲突导致自增主键不连续。当我们向一个自增主键的InnoDB表中插入数据的时候，如果违反表中定义的唯一索引的唯一约束，会导致插入数据失败。此时表的自增主键的键值是会向后加1滚动的。下次再次插入数据的时候，就不能再使用上次因插入数据失败而滚动生成的键值了，必须使用新滚动生成的键值。
2、事务回滚导致自增主键不连续。当我们向一个自增主键的InnoDB表中插入数据的时候，如果显式开启了事务，然后因为某种原因最后回滚了事务，此时表的自增值也会发生滚动，而接下里新插入的数据，也将不能使用滚动过的自增值，而是需要重新申请一个新的自增值。
3、批量插入导致自增值不连续。MySQL有一个批量申请自增id的策略：
如果下一个事务再次插入数据的时候，则会基于上一个事务申请后的自增值基础上再申请。此时就出现自增值不连续的情况出现。
4、自增步长不是1，也会导致自增主键不连续。
MySQL数据如何同步到Redis缓存？

参考：https://cloud.tencent.com/developer/article/1805755
有两种方案：
1、通过MySQL自动同步刷新Redis，MySQL触发器+UDF函数实现。
过程大致如下：
2、解析MySQL的binlog，实现将数据库中的数据同步到Redis。可以通过canal实现。canal是阿里巴巴旗下的一款开源项目，基于数据库增量日志解析，提供增量数据订阅&消费。
canal的原理如下：
为什么阿里Java手册禁止使用存储过程？

先看看什么是存储过程。
存储过程是在大型数据库系统中，一组为了完成特定功能的SQL 语句集，它存储在数据库中，一次编译后永久有效，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。
存储过程主要有以下几个缺点。
存储MD5值应该用VARCHAR还是用CHAR？

首先说说CHAR和VARCHAR的区别：
1、存储长度：
CHAR类型的长度是固定的
当我们当定义CHAR(10)，输入的值是"abc"，但是它占用的空间一样是10个字节，会包含7个空字节。当输入的字符长度超过指定的数时，CHAR会截取超出的字符。而且，当存储为CHAR的时候，MySQL会自动删除输入字符串末尾的空格。
VARCHAR的长度是可变的
比如VARCHAR(10)，然后输入abc三个字符，那么实际存储大小为3个字节。
除此之外，VARCHAR还会保留1个或2个额外的字节来记录字符串的实际长度。如果定义的最大长度小于等于255个字节，那么，就会预留1个字节；如果定义的最大长度大于255个字节，那么就会预留2个字节。
2、存储效率
CHAR类型每次修改后的数据长度不变，效率更高。
VARCHAR每次修改的数据要更新数据长度，效率更低。
3、存储空间
CHAR存储空间是初始的预计长度字符串再加上一个记录字符串长度的字节，可能会存在多余的空间。
VARCHAR存储空间的时候是实际字符串再加上一个记录字符串长度的字节，占用空间较小。
根据以上的分析，由于MD5是一个定长的值，所以MD5值适合使用CHAR存储。对于固定长度的非常短的列，CHAR比VARCHAR效率也更高。
最后给大家分享200多本计算机经典书籍PDF电子书，包括C语言、C++、Java、Python、前端、数据库、操作系统、计算机网络、数据结构和算法、机器学习、编程人生等，感兴趣的小伙伴可以自取：


https://mp.weixin.qq.com/s?__biz=Mzg2OTY1NzY0MQ==&mid=2247486208&idx=1&sn=dbeedf47c50b1be67b2ef31a901b8b56&chksm=ce98f646f9ef7f506a1f7d72fc9384ba1b518072b44d157f657a8d5495a1c78c3e5de0b41efd&token=1652861108&lang=zh_CN#rd

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！



欢迎光临 ToB企服应用市场:ToB评测及商务社交产业平台 (https://dis.qidao123.com/) Powered by Discuz! X3.4