【数据库篇】MySQL知识点

面试

mysql能存储多少数据？

树的高度为3为例，叶子节点(第三层)的18 15…是我们存储的数据

每页存储的数据量是16K mysql默认且可以修改

show VARIABLES like 'innodb_page_size%’
换算：16384(字节)/1024=16k

以bigInt 举例子 它是8个字节 一个指针是6个字节(不要问为什么，你就信了吧)bigInt是mysql 字段类型之一

• 1.第一个图咱们来计算一下 第一页有多少指向第二层的指针(第一页和第二页只存了索引和指向下一级的指针) 16384/(6+8)≈ 1170个指针
• 每一个指针都是一个页，那么第二层有多少页？答案是1170个页，那么每个页是不 也有1170个指针指向第三层，那么第二层有多少指针了？1170*1170 = 1368900
• 第三层是存数据的，那么他能存多少条数据呢？
  每一个页咱们之前说了他能存16k大小的数据，而每一条数据大概是 1k 我们就按 1k 算，第二层有1368900个指针指向第三层，也就是 说第三层有1368900个页，每一页可以存16条数据，那么第三层可以存
  16*1368900 =21902400，两千一百九十多万条数据。

mysql如何通过索引找到数据

当你建立了其他索引的时候，比如我们对name字段建立一个辅助索引，此时mysql在底层会维护出第二颗索引树，就是你这个name索引的树，该B+树是以索引字段name为key的，在叶子节点中存储的是这个索引字段的值和该字段值对应的主键的值，你索引有几个字段，这里就存几个字段。

所以基于以上的描述，我们来说一下这种模式下的检索过程，

如果你检索的条件是主键id,那么它就走主索引树，然后在第一颗树上直接二分检索拿到整行数据，完成检索，这就是为啥主键查询快的原因，干净利索，肯定快。

当你检索的是辅助索引字段，它就走第二颗树，二分检索到值之后，同时也就找到了对应的id，然后拿着这个id去第一颗树里找到数据。

此处我们加个画外音，就是为什么通常建议你一般覆盖索引，就是你要是覆盖索引它直接就在第二课树上找到了你要的所有数据，不需要再去第一颗树了，覆盖就是你检索的内容正好是索引字段，所以在第二颗树就能找到，这种也是很不错的。但是工作中没那么多正好，覆盖索引也不是都有的，所以灵活运用才是王道。

为什么 Mysql 使用B+树？（优势）

• 节省空间（非叶子节点不存储数据，相对b tree的优势）
• 减少I/O次数（节省的空间全部存指针地址，让树变的矮胖）
• 范围查找方便（相对hash的优势）

B+树的中间节点没有数据，所以同样大小的磁盘页可以容纳更多的节点元素，这就意味着在数据量相同的情况下，B+树更加的矮胖，因此IO的次数也就较少
B+树查询必须查找到叶子节点，每一次查找都是稳定的

https://blog.csdn.net/qq_22222499/article/details/79060495

为什么InnoDB表必须有主键。并且推荐使用整型的自增主键：

• 如果设置了主键，那么InnoDB会选择主键作为聚集索引、如果没有显式定义主键，则InnoDB会选择第一个不包含有NULL值的唯一索引作为主键索引、如果也没有这样的唯一索引，则InnoDB会选择内置6字节长的ROWID作为隐含的聚集索引(ROWID随着行记录的写入而主键递增)。

• 如果表使用自增主键
  mysq innodb的叶子结点是一个有序链表，每次插入新的记录，记录就会顺序添加到当前索引节点的后续位置，主键的顺序按照数据记录的插入顺序排列，自动有序。当一页写满，就会自动开辟一个新的页

• 如果使用非自增主键（身份证号、学号、无序uuid）

  由于每次插入主键的值近似于随机，因此每次新纪录都要被插到现有索引页得中间某个位置，此时MySQL不得不为了将新记录插到合适位置而移动数据，甚至目标页面可能已经被回写到磁盘上而从缓存中清掉，此时又要从磁盘上读回来，这增加了很多开销，同时频繁的移动、分页操作造成了大量的碎片，得到了不够紧凑的索引结构（页分裂），后续不得不通过OPTIMIZE TABLE来重建表并优化填充页面。

  uuid是无序的（对比半随机（semi-random）特性的UUID会导致明显的页稀疏分布（页数量更多，相关分裂操作更多），叶子结点是一个有序链表，页分裂的概念是你插入无序索引时，需要移动数据

索引选择（字符索引与整数索引的区别）

1、tablespace中空间浪费

当然我们知道使用varchar可能会导致辅助索引比较大，因为用到varchar可能存储的字符较多，同时

而辅助索引叶子结点毕竟都存储了主键值，这样至少会多varchar数据字节数量+1(或者2) 字节- 4(int)字节空间。

2、辅助索引B+树扫描性能

由于辅助索引B+树的空间要求更大，虽然在B+树层次一般都是3层-4层，索引单值定位I/O消耗并不明显，如果涉及到

范围查询(比如PAGE_CUR_G),需要访问的块就更多，同时比如例如辅助索引的using index，需要访问的块自然

基数大的列：基数（一个列中不重复的数据）大，说明重复数据少范精准度高

列类型小：比如能使用int就不用bigint，因为数据类型越小，查询时比较速度越快，索引占的空间越小，一个数据页可存放的内容越多，能够放在缓存的数据页越多，减少i/o带来的性能损耗，加快读写效率

因为字符串的索引比较大，如果说数值型建索引后2000万条记录开始查询略显缓慢，那字符串索引可能会有700万条记录的时候就会查询略显缓慢(具体看表的情况和查询条件咯)

排序和比较规则都会根据字符码值，而不是词典顺序

深度分页及优化

为什么深度分页会变慢？

底层B+树 去扫描行数 +limit字段
开销大：扫描+回表

解决方案：

• 合理建表
• 冷热分离
• 分库分表
• 避免跳转、深度分页
• 限制最大分页数
• 缓存

分页：防止一次性加载太多数据导致内存、磁盘IO都开销过大

1.从业务维度优化：从业务上避免，最多翻到100页就不让你翻了，这种方式就是从业务上解决；

2.游标滚动方式做：在查询下一页时把上一页的行id作为参数传递给客户端程序，然后sql就改成了select * from table where id>3000000 limit 10;

3.子查询优化 （覆盖索引进行优化）

select a.*
from people a
inner join(
    select id
    from people
    order by create_time desc
    limit 5000000, 10
) b ON a.id = b.id;

主从延时

• 硬件资源瓶颈导致：网络、cpu,硬盘

• 业务量太大：分库分表

• 参考同步原理并优化

  • 原因1：从库上单线程Slave_SQL_Running可能有ddl语句和查询造成lock，导致延迟。（主库ddl时为多线程）
    优化1：【5.7后可修改 set global slave_parallel_workers=10】，用多个线程并行重放relay log【基于GTID中的commit_id&sequence_number等字段，可以保证多个从库和多线程最终的一致性】

  • 原因2：内存flush到硬盘策略（redis的aof也是如此）
    优化2：

    考虑禁用salve端binlog
    bin_log落盘机制sync_binlog=0的优化

• 架构方面

  1.业务的持久化层的实现采用分库架构，mysql服务可平行扩展，分散压力。

  2.单个库读写分离，一主多从，主写从读，分散压力。这样从库压力比主库高，保护主库。

  4.业务初期规划的时候，就要选择合适的分库、分表策略，避免单表，或者单库过大。带来额外的复制压力。从而带来主从延迟的问题。

  5.使用比主库更好的硬件设备作为slave总结，mysql压力小，延迟自然会变小。

  6.避免让数据库进行各种大量运算，要记住数据库只是用来存储数据的，让应用端多分担些压力，或者可以通过缓存、队列。

数据库写入什么时候费时间

建立索引的时候

为什么数据库索引不要太多

一般5-6个旧够了，太多就 数据在增加删除的过程中，索引重建耗性能

MySQL 主从同步延迟的原因及解决办法？

union

临时表，数据量小 需要限制查询的数量

redolog它是写在什么地方的？

char 与 varchar 区别

MySQL中的字符串有两个常用的类型：char和varchar，二者各有优势，下面我们来详细分析一

1、 char长度固定， 即每条数据占用等长字节空间；适合用在身份证号码、手机号码等定。

2、 varchar可变长度，可以设置最大长度；适合用在长度可变的属性。

3、 text不设置长度， 当不知道属性的最大长度时，适合用text。

最大长度：

• char：char(n)中的n表示字符数，最大长度是255个字符； 如果是utf8编码方式， 那么char类型占255 * 3个字节。（utf8下一个字符占用1至3个字节）

• varchar：varchar(n)中的n表示字符数，最大空间是65535个字节， 存放字符数量跟字符集有关系；
  如果是utf8编码， 那么varchar最多存65532/3 = 21844个字符。

明确的是，char的长度是不可变的，而varchar的长度是可变的，也就是说，定义一个char[10]和varchar[10],如果存进去的是‘csdn’,那么char所占的长度依然为10，除了字符‘csdn’外，后面跟六个空格，而varchar就立马把长度变为4了，取数据的时候，char类型的要用trim()去掉多余的空格，而varchar是不需要的。

尽管如此，char的存取数度还是要比varchar要快得多，因为其长度固定，方便程序的存储与查找；但是char也为此付出的是空间的代价，因为其长度固定，所以难免会有多余的空格占位符占据空间，可谓是以空间换取时间效率，而varchar是以空间效率为首位的。

再者，char的存储方式是，对英文字符（ASCII）占用1个字节，对一个汉字占用两个字节；而varchar的存储方式是，对每个英文字符占用2个字节，汉字也占用2个字节。

Mysql主从模式，主节点挂了会怎么样？

从库挂了 ：还要到主服务上拉取最新的 bin-log 进行同步。最后进行一系列设置将选中的从库变更为主库配置。

半同步复制
2010 年引入Semisynchronous Replication，5.5 可用，解决主库数据丢失问题，保证 Source 和 Replica 的最终一致性。
需要启用插件。

• 主库写入binlog日志后，会强制立即将数据同步到从库
• 从库将日志写入自己的relay log后，会返回ack给主库
• 主库接收到至少一个从库的ack后才会认为写操作完成

主从延迟严重解决方案

分库 : 将一个主库拆分，每个主库的写并发就降低了，主从延迟即可忽略不计
打开MySQL支持的并行复制，多个库并行复制，若某个库的写入并发特别高，写并发达到了2000/s，并行复制还是没意义。二八法则，很多时候比如说，就是少数的几个订单表，写入了2000/s，其他几十个表10/s。
从库开启多线程，并行读取relay log中不同库的日志，然后并行重放不同库的日志，这是库级别的并行。
重构代码 : 重构代码，插入数据后，直接更新，不查询
若确实存在必须先插入，立马要求查询，然后立马就反过来执行一些操作，对这个查询设置直连主库(不推荐，这会导致读写分离失去意义)

数据库从磁盘加载到内存经历几次复制

操作系统守护线程用来做什么？什么时候结束？

手动中断？

什么是中断？

MySQL如何比较2个字符串的大小？

如果比较2个字符串的字符集不同，MySQL会先将其转成同一个字符集再进行比较，如果2个字符集不兼容，则会出错。

Mysql5.0以后的版本会做隐式转换。还可以使用前缀和collate子句来指定字符串的字符集和校对字符集。

这里有一个通用原则可以供我们使用：

先为服务器或者数据库选择一个合理的字符集，然后根据不同的实际情况，让某个列选择自己的字符集。

原来对于数字与非数字混合的字符串，在进行大小比较的时候，

• 如果两字符串长度相等，那么两字符串就会比较相同位置的字符，比较时若字符是数字，则直接比较，

• 若字符是非数字那么会转换为ascii码进行比较，若在某位置上已经有大小之分，那么就不会再进行比较。

总结
字符串大小比较的时候，会从左向右将两个字符串第一个不相等的两个字符的ascii码的比较结果作为最终结果

为什么数据库的字段要设置not null

1.不知道是什么值与空值
2.聚合函数会忽略NULL值
3.等号表达式失效 NULL字段值必须要是is null / is not null查询
4.值运算失效 age null age+1=null
5. 避免等号失效

6.当null字段变多时导致索引失效，有可能有效也有可能无效。原理上，索引选择更加复杂，更加难以优化，

7.存储空间问题
存储空间问题，null本身并不会占用存储空间，如果该null存在的字段，就会多占用一个字符位置的空间，

左链接，左边一行数据，右边两行数据，查询返回几行？

答：返回 右边左边拼接与相等的数量

存储相关

MySQL页分裂

MySQL读取数据的最小单位为页，默认大小为16kb。

在mysql中，索引结构是个b+树，在b+树的叶子节点其实是大小为16k的数据页（data page）,数据页的顺序是按照主键进行排序的，因此像使用uuid这种没有顺序的主键时，会导致数据的重排，即会发生页分裂。

普通索引（如uuid）新增数据在索引树随机位置插入，超过数据块容量时需要分裂，但分裂空间和时间上均匀分布。

lnnoDB逻辑存储结构

MySQL所有数据都被逻辑地存放在一个空间中 ，我们称之为表空间 （ tablespace ） 。表空间又由段 （ segment ） 、区 （ extent ） 、页 ( page ） 组成。

默认情况下 InnoDB存储引擎有一个共享表空间 ibdata1 ，即所有数据都放在这个表空间内 。如果我们启用了参数innodb_file_per_table ，则每张表内的数据可以单独放到一个表空间内 。段就是表，区就是连续的几个页，页是最小单位。

mysql delete操作其实是假删除

表数据被清空后，表所占空间大小并没有变化，delete 操作并没有真正删除数据，表的空间并没有被释放。

这些被删除的记录行，只是被标记删除，是可以被复用的，下次有符合条件的记录是可以直接插入到这个被标记的位置的。

如果一个表上存在大量的数据空洞，原本只需一个数据页就保存的数据，由于被很多空洞占用了空间，不得不需要增加其他的数据页来保存数据，相应的，mysql 在查询相同数据的时候，就不得不增加磁盘 IO 操作，从而影响查询速度。

其实不仅仅是删除操作会造成数据空洞，插入和更新同样也会造成空洞，
因此，一个数据表在经过大量频繁的增删改之后，难免会产生数据空洞，浪费空间并影响查询效率，通常在生产环境中会直接表现为原本很快的查询会变得越来越慢。

对于这种情况，我们通常可以使用下面这个命令就能解决数据空洞问题。
optimize table t
这个命令的原理就是重建表，就是建立一个临时表 B，然后把表 A（存在数据空洞的表） 中的所有数据查询出来，接着把数据全部重新插入到临时表 B 中，最后再用临时表 B 替换表 A 即可，这就是重建表的过程。

varchar(32) 和 varchar(64)区别是啥？

首先，varchar 大家都知道是动态长度分配,他们占用磁盘空间是一样的。"123456"占用磁盘空间一样

但是对于内存来说，则不是。其时使用固定大小的内存块来保存值。就是使用字符类型中定义的长度，即定义的64或32个字符空间。显然，这对于排序或者临时表(这些内容都需要通过内存来实现)操作会产生比较大的不利影响。

再者，MySQL对于该字段建索引时如果没有限制索引的大小，索引长度会默认采用的该字段的长度，也就是说varchar(64)建立的索引存储大小要比varchar(32)建立索引存储大小大的多，加载索引使用的内存也更多，效率会低。

事务相关

RR和RC使用场景

引用：

MySQL的RR需要gap lock来解决幻读问题。而RC隔离级别则是允许存在不可重复读和幻读的。所以RC的并发一般要好于RR；

RR隔离级别，通过 where 条件走非索引列过滤之后，即使不符合where条件的记录，也是会加行锁。所以从锁方面来看，RC的并发应该要好于RR；可以减少一部分锁竞争，减少死锁和锁超时的概率。

索引相关

MySQL索引的更新策略

对于数据的每一次更新，MySQL并不会每次都会更新索引(针对非唯一性索引而言)，索引的更新策略是这样的：

在InnoDB中，增删改都会立刻修改主键or唯一索引，但是不会rebuild全局索引，而是对这些索引增加值(或移除值)。

对于非唯一性索引，InnoDB会进行change buffering操作。将更改排入队列，之后再在后台将其合并到索引中。甚至，为了后续物理更新更加高效，会将变更进行合并。
这种特性不需要手动开启，而是默认开启的。在MySQL5.1版本，change buffering操作仅仅适用于insert。而在MySQL5.5版本之后，change buffering操作则扩展到update和delete里。

对于change buffering，是这样定义的：
change buffer是一个特殊的数据结构。对于那些不在缓存池内的二级索引有修改时，对应的修改会被缓存在change buffer里（二级索引就是非聚集索引）。对二级索引有修改的场景包括：对数据的插入、更新和删除操作。之后如果有读操作时，会将这些二级索引页加载到缓存池里时，此时才会将change buffer里的修改与二级索引页合并。

跟聚集索引不同，二级索引往往是不唯一的，并且数据会以一种相对随机的顺序插入二级索引。类似的，删除和更新操作也会往往会影响索引树上的不相邻的二级索引页。因此采取：当被变更的二级索引页从磁盘中被读入缓存池时，才进行合并工作。这种将更改延后进行合并的操作，能够避免大量的随机磁盘读取IO操作。

这种清洗工作往往在系统处于空闲时周期进行，或者通过一段短暂的shutdown来将更新的索引页写入磁盘。这种清洗工作将一系列索引值写入磁盘块，会比来一个写一次快得多。

当存在很多被影响的行，存在很多需要更新的二级索引时，change buffer的合并工作往往会消耗几个小时。在这段时间内，磁盘IO数量激增，会导致对于磁盘的查询操作宕机。另外，change buffer的合并工作会在事务提交后进行，甚至会在服务器的关闭重启后进行。

在内存中，change buffer占用了缓冲池的一部分。在磁盘上，change buffer是系统表空间的一部分，当数据库服务器被关闭时，索引的更改将会被缓存在其中。

你可以自定义最大的change buffer的大小。详情可见：https://dev.mysql.com/doc/refman/8.0/en/innodb-change-buffer.html#innodb-change-buffer-configuration

MySQL 聚簇索引和主键索引区别

在 MySQL 中，聚簇索引（Clustered Index）和主键索引（Primary Key Index）是两种不同类型的索引。

主键索引是一种特殊的索引类型，它是用于唯一标识表中每一行记录的。
主键索引可以是聚簇索引，也可以是非聚簇索引。如果主键索引是聚簇索引，则表中的数据行按照主键值的大小顺序存储，这种方式被称为聚簇索引。

聚簇索引是一种将表中的数据行按照索引列的值进行排序的索引方式。
在聚簇索引中，索引的叶子节点存储的是数据行而不是指向数据行的指针。这样就可以通过聚簇索引快速地定位到表中的数据行，因为数据行已经和索引在一起存储了。

因此，聚簇索引和主键索引的区别在于，主键索引是一种用于唯一标识每一行记录的索引，而聚簇索引则是一种按照索引列的值对表中的数据行进行排序的索引方式。在某些情况下，主键索引也可以被用作聚簇索引，但并不总是如此。

数据库设计相关

数据库冗余字段有什么优缺点？

优点：便于查询、减少查询
缺点：业务不收拢，更新时需要更新多个地方，在同一个数据库还能控制，在多个数据库风险不可控（看业务场景、读写占比）