MySQL篇

WhyMysql？

NoSQL数据库四大家族

  • 列存储 Hbase
  • K-V存储 Redis
  • 图像存储 Neo4j
  • 文档存储 MongoDB

云存储OSS

海量Aerospike

Aerospike（简称AS）是一个分布式，可扩展的键值存储的NoSQL数据库。T级别大数据高并发的结构化**数据存储，**采用混合架构，索引存储在内存中，而数据可存储在机械硬盘(HDD)或固态硬盘(SSD) 上，读写操作达微妙级，99%的响应可在1毫秒内实现。

Aerospike Redis
类型 Nosql数据库 缓存
线程数 多线程 单线程
数据分片 自动处理相当于分片 提供分片算法、平衡各分片数据
数据扩容 动态增加数据卷平衡流量 需停机
数据同步 设置复制因子后可以透明的完成故障转移 手动故障转移和数据同步
载体 内存存储索引+SSD存储数据 内存

Aerospike作为一个大容量的NoSql解决方案，适合对容量要求比较大，QPS相对低一些的场景，主要用在广告行业，个性化推荐厂告是建立在了和掌握消费者独特的偏好和习性的基础之上，对消费者的购买需求做出准确的预测或引导，在合适的位置、合适的时间，以合适的形式向消费者呈现与其需求高度吻合的广告，以此来促进用户的消费行为。

image-20210103170039711

（ETL数据仓库技术）抽取（extract）、转换（transform）、加载（load）

  • 用户行为日志收集系统收集日志之后推送到ETL做数据的清洗和转换

  • 把ETL过后的数据发送到推荐引擎计算每个消费者的推荐结果，其中推荐逻辑包括规则和算法两部分

  • 收集用户最近浏览、最长停留等特征，分析商品相似性、用户相似性、相似性等算法。

  • 把推荐引擎的结果存入Aerospike集群中，并提供给广告投放引擎实时获取

    分别通过HDFS和HBASE对日志进行离线和实时的分析，然后把用户画像的标签(tag : 程序猿、宅男…)结果存入高性能的Nosql数据库Aerospike中，同时把数据备份到异地数据中心。前端广告投放请求通过决策引擎（投放引擎）向用户画像数据库中读取相应的用户画像数据，然后根据竞价算法出价进行竞价。竞价成功之后就可以展现广告了。而在竞价成功之后，具体给用户展现什么样的广告，就是有上面说的个性化推荐广告来完成的。

Aerospike Mysql
库名 Namespace Database
表名 Set Table
记录 Bin Column
字段 Record Row
索引 key 、 pk 、kv pk

图谱Neo4j

Neo4j是一个开源基于java开发的图形noSql数据库，它将结构化数据存储在图中而不是表中。它是一个嵌入式的、基于磁盘的、具备完全的事务特性的Java持久化引擎。程序数据是在一个面向对象的、灵活的网络结构下，而不是严格的表中，但具备完全的事务特性、企业级的数据库的所有好处。

一种基于图的数据结构，由节点(Node)和边(Edge)组成。其中节点即实体，由一个全局唯一的ID标示，边就是关系用于连接两个节点。通俗地讲，知识图谱就是把所有不同种类的信息，连接在一起而得到的一个关系网络。知识图谱提供了从“关系”的角度去分析问题的能力。

互联网、大数据的背景下，谷歌、百度、搜狗等搜索引擎纷纷基于该背景，创建自己的知识图Knowledge Graph（谷歌）、知心（百度）和知立方（搜狗），主要用于改进搜索质量。

自己项目主要用作好友推荐，图数据库(Graph database)指的是以图数据结构的形式来存储和查询数据的数据库。关系图谱中，关系的组织形式采用的就是图结构，所以非常适合用图库进行存储。

  • image-20210103191540372

    优势总结:

  • 性能上，使用cql查询，对长程关系的查询速度快

  • 擅于发现隐藏的关系，例如通过判断图上两点之间有没有走的通的路径，就可以发现事物间的关联

image-20210103192653004

1
2
3
4
5
6
7
// 查询三层级关系节点如下：with可以将前面查询结果作为后面查询条件
match (na:Person)-[re]-(nb:Person) where na.name="林婉儿" WITH na,re,nb match (nb:Person)- [re2:Friends]->(nc:Person) return na,re,nb,re2,nc
// 直接拼接关系节点查询
match data=(na:Person{name:"范闲"})-[re]->(nb:Person)-[re2]->(nc:Person) return data
// 使用深度运算符
显然使用以上方式比较繁琐,可变数量的关系->节点可以使用-[:TYPE*minHops..maxHops]-。
match data=(na:Person{name:"范闲"})-[*1..2]-(nb:Person) return data

文档MongoDB

MongoDB 是一个基于分布式文件存储的数据库，是非关系数据库中功能最丰富、最像关系数据库的。在高负载的情况下，通过添加更多的节点，可以保证服务器性能。由 C++ 编写，可以为 WEB 应用提供可扩展、高性能、易部署的数据存储解决方案。

image-20210103194830654

什么是BSON

{key:value,key2:value2}和Json类似，是一种二进制形式的存储格式，支持内嵌的文档对象和数组对象，但是BSON有JSON没有的一些数据类型，比如 value包括字符串,double,Array,DateBSON可以做为网络数据交换的一种存储形式,它的优点是灵活性高，但它的缺点是空间利用率不是很理想。

BSON有三个特点：轻量性、可遍历性、高效性

1
2
3
4
5
6
/* 查询 find() 方法可以传入多个键(key)，每个键(key)以逗号隔开*/
db.collection.find({key1:value1, key2:value2}).pretty()
/* 更新 $set ：设置字段值 $unset :删除指定字段 $inc：对修改的值进行自增*/
db.collection.update({where},{$set:{字段名:值}},{multi:true})
/* 删除 justOne :如果设为true，只删除一个文档，默认false，删除所有匹配条件的文档*/
db.collection.remove({where}, {justOne: <boolean>, writeConcern: <回执> } )

优点：

  • 文档结构的存储方式，能够更便捷的获取数据。

    对于一个层级式的数据结构来说，使用扁平式的，表状的结构来查询保存数据非常的困难。

  • 内置GridFS，支持大容量的存储。

    GridFS是一个出色的分布式文件系统，支持海量的数据存储，满足对大数据集的快速范围查询。

  • 性能优越

    千万级别的文档对象，近10G的数据，对有索引的ID的查询 不会比mysql慢，而对非索引字段的查询，则是全面胜出。 mysql实际无法胜任大数据量下任意字段的查询，而mongodb的查询性能实在牛逼。写入性能同样很令人满意，同样写入百万级别的数据，mongodb基本10分钟以下可以解决。

缺点：

  • 不支持事务
  • 磁盘占用空间大

MySQL 8.0 版本

1. 性能：MySQL 8.0 的速度要比 MySQL 5.7 快 2 倍。

2. NoSQL：MySQL 从 5.7 版本开始提供 NoSQL 存储功能，在 8.0 版本中nosql得到了更大的改进。

3. 窗口函数：实现若干新的查询方式。窗口函数与 SUM()、COUNT() 这种集合函数类似，但它不会将多行查询结果合并为一行，而是将结果放回多行当中，即窗口函数不需要 GROUP BY。

4. 隐藏索引：在 MySQL 8.0 中，索引可以被“隐藏”和“显示”。当对索引进行隐藏时，它不会被查询优化器所使用。我们可以使用这个特性用于性能调试，例如我们先隐藏一个索引，然后观察其对数据库的影响。如果数据库性能有所下降，说明这个索引是有用的，然后将其“恢复显示”即可；如果数据库性能看不出变化，说明这个索引是多余的，可以考虑删掉。

云存储

OSS 自建
可靠性 可用性不低于99.995% 数据设计持久性不低于99.9999999999%（12个9） 受限于硬件可靠性，易出问题，一旦出现磁盘坏道，容易出现不可逆转的数据丢失。人工数据恢复困难、耗时、耗力。
安全 服务端加密、客户端加密、防盗链、IP黑白名单等。多用户资源隔离机制，支持异地容灾机制。 需要另外购买清洗和黑洞设备。需要单独实现安全机制。
成本 多线BGP骨干网络，无带宽限制，上行流量免费。无需运维人员与托管费用，0成本运维。 单线或双线接入速度慢，有带宽限制，峰值时期需人工扩容。需专人运维，成本高。

使用步骤

1、开通服务

2、创建存储空间

3、上传文件、下载文件、删除文件

4、域名绑定、日志记录

5、根据开放接口进行鉴权访问

功能

图片编辑（裁剪、模糊、水印）

视频截图

音频转码、视频修复

CDN加速

对象存储OSS与阿里云CDN服务结合，可优化静态热点文件下载加速的场景（即同一地区大量用户同时下载同一个静态文件的场景）。可以将OSS的存储空间（Bucket）作为源站，利用阿里云CDN将源内容发布到边缘节点。当大量终端用户重复访问同一文件时，可以直接从边缘节点获取已缓存的数据，提高访问的响应速度

FastDFS

开源的轻量级分布式文件系统。它对文件进行管理，功能包括：文件存储、文件同步、文件访问（文件上传、文件下载）等，解决了大容量存储和负载均衡的问题。使用FastDFS很容易搭建一套高性能的文件服务器集群提供文件上传、下载等服务。如相册网站、视频网站等

扩展能力: 支持水平扩展，可以动态扩容；

高可用性: 一是整个文件系统的可用性，二是数据的完整和一致性；

弹性存储: 可以根据业务需要灵活地增删存储池中的资源，而不需要中断系统运行。

image-20210107221022658

特性

  • 和流行的web server无缝衔接，FastDFS已提供apache和nginx扩展模块
  • 文件ID由FastDFS生成，作为文件访问凭证，FastDFS不需要传统的name server
  • 分组存储，灵活简洁、对等结构，不存在单点
  • 文件不分块存储，上传的文件和OS文件系统中的文件一一对应
  • 中、小文件均可以很好支持，支持海量小文件存储
  • 支持相同内容的文件只保存一份，节约磁盘空间
  • 支持多块磁盘，支持单盘数据恢复
  • 支持在线扩容 支持主从文件
  • 下载文件支持多线程方式，支持断点续传

组成

  • 客户端（client）

    通过专有接口，使用TCP/IP协议与跟踪器服务器或存储节点进行数据交互。

  • 跟踪器（tracker）

    Trackerserver作用是负载均衡和调度，通过Tracker server在文件上传时可以根据策略找到文件上传的地址。Tracker在访问上起负载均衡的作用。

  • 存储节点（storage）

    Storageserver作用是文件存储，客户端上传的文件最终存储在Storage服务器上，Storage server没有实现自己的文件系统而是利用操作系统的文件系统来管理文件。存储节点中的服务器均可以随时增加或下线而不会影响线上服务。

上传

image-20210107222155291

下载

image-20210107222312338

断点续传

续传涉及到的文件大小MD5不会改变。续传流程与文件上传类似，先定位到源storage，完成完整或部分上传，再通过binlog进行同group内server文件同步。

配置优化

配置文件：tracker.conf 和 storage.conf

1
2
3
4
5
6
7
8
// FastDFS采用内存池的做法。 
// v5.04对预分配采用增量方式，tracker一次预分配 1024个，storage一次预分配256个。 
max_connections = 10240
// 根据实际需要将 max_connections 设置为一个较大的数值，比如 10240 甚至更大。
// 同时需要将一个进程允许打开的最大文件数调大
vi /etc/security/limits.conf 重启系统生效 
* soft nofile 65535 
* hard nofile 65535
1
2
3
work_threads = 4 
// 说明：为了避免CPU上下文切换的开销，以及不必要的资源消耗，不建议将本参数设置得过大。
// 公式为： work_threads + (reader_threads + writer_threads) = CPU数
1
2
3
4
5
// 对于单盘挂载方式，磁盘读写线程分 别设置为 1即可 
// 如果磁盘做了RAID，那么需要酌情加大读写线程数，这样才能最大程度地发挥磁盘性能
disk_rw_separated：磁盘读写是否分离 
disk_reader_threads：单个磁盘读线程数 
disk_writer_threads：单个磁盘写线程数

避免重复

如何避免文件重复上传 解决方案 上传成功后计算文件对应的MD5然后存入MySQL,添加文件时把文件MD5和之前存入MYSQL中的存储的信息对比 。DigestUtils.md5DigestAsHex(bytes)。

事务

1、事务4大特性

**事务4大特性：**原子性、一致性、隔离性、持久性

原⼦性： 事务是最⼩的执⾏单位，不允许分割。事务的原⼦性确保动作要么全部完成，要么全不执行

一致性： 执⾏事务前后，数据保持⼀致，多个事务对同⼀个数据读取的结果是相同的；

隔离性： 并发访问数据库时，⼀个⽤户的事务不被其他事务所⼲扰，各并发事务之间数据库是独⽴的；

持久性： ⼀个事务被提交之后。它对数据库中数据的改变是持久的，即使数据库发⽣故障也不应该对其有任何影响。

实现保证：

MySQL的存储引擎InnoDB使用重做日志保证一致性与持久性，回滚日志保证原子性，使用各种锁来保证隔离性。

2、事务隔离级别

**读未提交：**最低的隔离级别，允许读取尚未提交的数据变更，可能会导致脏读、幻读或不可重复读。

**读已提交：**允许读取并发事务已经提交的数据，可以阻⽌脏读，但是幻读或不可重复读仍有可能发⽣。

**可重复读：**同⼀字段的多次读取结果都是⼀致的，除⾮数据是被本身事务⾃⼰所修改，可以阻⽌脏读和不可重复读，会有幻读。

**串行化：**最⾼的隔离级别，完全服从ACID的隔离级别。所有的事务依次逐个执⾏，这样事务之间就完全不可能产⽣⼲扰。

隔离级别 并发问题
读未提交 可能会导致脏读、幻读或不可重复读
读已提交 可能会导致幻读或不可重复读
可重复读 可能会导致幻读
可串行化 不会产⽣⼲扰

3、默认隔离级别-RR

**默认隔离级别：**可重复读；

同⼀字段的多次读取结果都是⼀致的，除⾮数据是被本身事务⾃⼰所修改；

可重复读是有可能出现幻读的，如果要保证绝对的安全只能把隔离级别设置成SERIALIZABLE；这样所有事务都只能顺序执行，自然不会因为并发有什么影响了，但是性能会下降许多。

第二种方式，使用MVCC解决快照读幻读问题（如简单select），读取的不是最新的数据。维护一个字段作为version，这样可以控制到每次只能有一个人更新一个版本。

1
2
select id from table_xx where id = ? and version = V
update id from table_xx where id = ? and version = V+1

第三种方式，如果需要读最新的数据，可以通过GapLock+Next-KeyLock可以解决当前读幻读问题，

1
2
select id from table_xx where id > 100 for update;
select id from table_xx where id > 100 lock in share mode;

4、RR和RC使用场景

事务隔离级别RC(read commit)和RR（repeatable read）两种事务隔离级别基于多版本并发控制MVCC(multi-version concurrency control）来实现。

RC RR
实现 多条查询语句会创建多个不同的ReadView 仅需要一个版本的ReadView
粒度 语句级读一致性 事务级读一致性
准确性 每次语句执行时间点的数据 第一条语句执行时间点的数据

5、行锁，表锁，意向锁（事务隔离时用到的）

InnoDB⽀持⾏级锁(row-level locking)和表级锁,默认为⾏级锁

InnoDB按照不同的分类的锁：

共享/排它锁(Shared and Exclusive Locks)：行级别锁，

意向锁(Intention Locks)：表级别锁

间隙锁(Gap Locks)：锁定一个区间

记录锁(Record Locks)：锁定一个行记录

表级锁：（串行化）

Mysql中锁定 粒度最大的一种锁，对当前操作的整张表加锁，实现简单 ，资源消耗也比较少，加锁快，不会出现死锁 。其锁定粒度最大，触发锁冲突的概率最高，并发度最低，MyISAM和 InnoDB引擎都支持表级锁。

行级锁：（RR、RC）

Mysql中锁定 粒度最小 的一种锁，只针对当前操作的行进行加锁。 行级锁能大大减少数据库操作的冲突。其加锁粒度最小，并发度高，但加锁的开销也最大，加锁慢，会出现死锁。 InnoDB支持的行级锁，包括如下几种：

记录锁（Record Lock）: 对索引项加锁，锁定符合条件的行。其他事务不能修改和删除加锁项；

间隙锁（Gap Lock）: 对索引项之间的“间隙”加锁，锁定记录的范围，不包含索引项本身，其他事务不能在锁范围内插入数据。

Next-key Lock： 锁定索引项本身和索引范围。即Record Lock和Gap Lock的结合。可解决幻读问题。

InnoDB 支持多粒度锁（multiple granularity locking），它允许行级锁与表级锁共存，而意向锁就是其中的一种表锁。

共享锁（ shared lock, S ）锁允许持有锁读取行的事务。加锁时将自己和子节点全加S锁，父节点直到表头全加IS锁

排他锁（ exclusive lock， X ）锁允许持有锁修改行的事务。 加锁时将自己和子节点全加X锁，父节点直到表头全加IX锁

意向共享锁（intention shared lock, IS）：事务有意向对表中的某些行加共享锁（S锁）

意向排他锁（intention exclusive lock, IX）：事务有意向对表中的某些行加排他锁（X锁）

互斥性 共享锁（S） 排它锁（X） 意向共享锁IS 意向排他锁IX
共享锁（S） ✅ ❌ ✅ ❌
排它锁（X） ❌ ❌ ❌ ❌
意向共享锁IS ✅ ❌ ✅ ✅
意向排他锁IX ❌ ❌ ✅ ✅

6、MVCC多版本并发控制

MVCC是一种多版本并发控制机制，通过事务的可见性看到自己预期的数据，能降低其系统开销.（RC和RR级别工作）

InnoDB的MVCC,是通过在每行记录后面保存系统版本号(可以理解为事务的ID)，每开始一个新的事务，系统版本号就会自动递增，事务开始时刻的系统版本号会作为事务的ID。这样可以确保事务读取的行，要么是在事务开始前已经存在的，要么是事务自身插入或者修改过的，防止幻读的产生。

1.MVCC手段只适用于Msyql隔离级别中的读已提交（Read committed）和可重复读（Repeatable Read）.

2.Read uncimmitted由于存在脏读，即能读到未提交事务的数据行，所以不适用MVCC.

3.简单的select快照度不会加锁，删改及select for update等需要当前读的场景会加锁

原因是MVCC的创建版本和删除版本只要在事务提交后才会产生。客观上，mysql使用的是乐观锁的一整实现方式，就是每行都有版本号，保存时根据版本号决定是否成功。Innodb的MVCC使用到的快照存储在Undo日志中，该日志通过回滚指针把一个数据行所有快照连接起来。

版本链

在InnoDB引擎表中，它的聚簇索引记录中有两个必要的隐藏列：

trx_id

这个id用来存储的每次对某条聚簇索引记录进行修改的时候的事务id。

roll_pointer

每次对哪条聚簇索引记录有修改的时候，都会把老版本写入undo日志中。这个roll_pointer就是存了一个指针，它指向这条聚簇索引记录的上一个版本的位置，通过它来获得上一个版本的记录信息。(注意插入操作的undo日志没有这个属性，因为它没有老版本)

每次修改都会在版本链中记录。**SELECT可以去版本链中拿记录，这就实现了读-写，写-读的并发执行，**提升了系统的性能。

索引

1、Innodb和Myisam引擎

**Myisam：**支持表锁，适合读密集的场景，不支持外键，不支持事务，索引与数据在不同的文件

**Innodb：**支持行、表锁，默认为行锁，适合并发场景，支持外键，支持事务，索引与数据同一文件

2、哈希索引

哈希索引用索引列的值计算该值的hashCode，然后在hashCode相应的位置存执该值所在行数据的物理位置，因为使用散列算法，因此访问速度非常快，但是一个值只能对应一个hashCode，而且是散列的分布方式，因此哈希索引不支持范围查找和排序的功能

3、B+树索引

优点：

B+树的磁盘读写代价低，更少的查询次数，查询效率更加稳定，有利于对数据库的扫描

B+树是B树的升级版，B+树只有叶节点存放数据，其余节点用来索引。索引节点可以全部加入内存，增加查询效率，叶子节点可以做双向链表，从而提高范围查找的效率，增加的索引的范围

在大规模数据存储的时候，红黑树往往出现由于树的深度过大而造成磁盘IO读写过于频繁，进而导致效率低下的情况。所以，只要我们通过某种较好的树结构减少树的结构尽量减少树的高度，B树与B+树可以有多个子女，从几十到上千，可以降低树的高度。

磁盘预读原理：将一个节点的大小设为等于一个页，这样每个节点只需要一次I/O就可以完全载入。为了达到这个目的，在实际实现B-Tree还需要使用如下技巧：每次新建节点时，直接申请一个页的空间，这样就保证一个节点物理上也存储在一个页里，加之计算机存储分配都是按页对齐的，就实现了一个node只需一次I/O。

4、创建索引

1
2
3
4
5
6
7
8
9
10
11
CREATE  [UNIQUE | FULLTEXT]  INDEX  索引名 ON  表名(字段名) [USING 索引方法]；

说明：
UNIQUE:可选。表示索引为唯一性索引。
FULLTEXT:可选。表示索引为全文索引 加速大文本。
NORMAL：可选。加速查询操作，包括精确匹配、范围查询和排序。
SPATIAL：可选。专门用于处理地理空间数据，支持几何数据类型。
INDEX和KEY:用于指定字段为索引，两者选择其中之一就可以了，作用是一样的。
索引名:可选。给创建的索引取一个新名称。
字段名1:指定索引对应的字段的名称，该字段必须是前面定义好的字段。
注：索引方法默认使用B+TREE。

5、聚簇索引和非聚簇索引

**聚簇索引：**将数据存储与索引放到了一块，索引结构的叶子节点保存了行数据（存储的是表的主键）（主键索引）

**非聚簇索引：**将数据与索引分开存储，索引结构的叶子节点指向了数据对应的位置（辅助索引）

聚集索引和非聚集索引的区别：
1.聚集索引中的非叶子节点存储的是表的主键，非聚集索引的非叶子节点存储的是自己设置的索引字段对应的值(如果是联合索引，那就是联合索引的几个字段对应的值)
2.聚集索引的叶子节点，存储着当前表中每条记录的所有信息；非聚集索引的叶子节点，只存储当前记录对应的主键ID(也就是聚集索引的非叶子节点存储的值)

聚簇索引

在MySQL的InnoDB存储引擎中，聚簇索引是自动管理的，不需要显式创建。以下是几种情况：

  1. 使用主键创建聚簇索引：

    • 如果在表定义时创建了主键，那么该主键会自动成为聚簇索引。
    1
    2
    3
    4
    5
    CREATE TABLE my_table (
        id INT PRIMARY KEY,
        name VARCHAR(50),
        age INT
    ) ENGINE=InnoDB;

    在上面的例子中，id列是主键，因此会自动成为聚簇索引。

  2. 没有主键但有唯一非空索引：

    • 如果表中没有主键，但有一个唯一非空索引，那么这个唯一索引将成为聚簇索引。
    1
    2
    3
    4
    5
    CREATE TABLE my_table (
        id INT NOT NULL,
        name VARCHAR(50) UNIQUE NOT NULL,
        age INT
    ) ENGINE=InnoDB;

    在这个例子中，name列是唯一且非空的，因此会成为聚簇索引。

  3. 没有主键和唯一非空索引：

    • 如果表中既没有主键，也没有唯一非空索引，InnoDB会自动创建一个隐藏的聚簇索引。

非聚簇索引

非聚簇索引需要显式创建，可以在创建表时定义，也可以在表创建后添加。

  1. 在创建表时定义非聚簇索引：

    1
    2
    3
    4
    5
    6
    CREATE TABLE my_table (
        id INT PRIMARY KEY,
        name VARCHAR(50),
        age INT,
        INDEX (name)
    ) ENGINE=InnoDB;

    在这个例子中，name列上的索引是一个非聚簇索引。

  2. 在表创建后添加非聚簇索引：

    1
    CREATE INDEX idx_name ON my_table(name);

    这个命令在my_table表的name列上创建了一个非聚簇索引。

6、最左前缀问题

最左前缀原则主要使用在联合索引中，联合索引的B+Tree是按照第一个关键字进行索引排列的。

联合索引的底层是一颗B+树，只不过联合索引的B+树节点中存储的是键值。由于构建一棵B+树只能根据一个值来确定索引关系，所以数据库依赖联合索引最左的字段来构建。

采用>、<等进行匹配都会导致后面的列无法走索引，因为通过以上方式匹配到的数据是不可知的。

创建索引时选择性高的列放在前面，以提高索引的效率。

SQL查询

1、SQL语句的执行过程

查询语句：

1
select * from student  A where A.age='18' and A.name='张三';

img

结合上面的说明，我们分析下这个语句的执行流程：

①通过客户端/服务器通信协议与 MySQL 建立连接。并查询是否有权限

②Mysql8.0之前开看是否开启缓存，开启了 Query Cache 且命中完全相同的 SQL 语句，则将查询结果直接返回给客户端；

③由解析器进行语法语义解析，并生成解析树。如查询是select、表名tb_student、条件是id=’1’

④查询优化器生成执行计划。根据索引看看是否可以优化

⑤查询执行引擎执行 SQL 语句，根据存储引擎类型，得到查询结果。若开启了 Query Cache，则缓存，否则直接返回。

2、回表查询和覆盖索引

解释：

https://blog.csdn.net/CPLASF_/article/details/108799381

https://www.cnblogs.com/Courage129/p/14166775.html

普通索引（唯一索引+联合索引+全文索引）需要扫描两遍索引树

（1）先通过普通索引定位到主键值id=5；

（2）在通过聚集索引定位到行记录；

这就是所谓的回表查询，先定位主键值，再定位行记录，它的性能较扫一遍索引树更低。

覆盖索引：主键索引==聚簇索引==覆盖索引

如果where条件的列和返回的数据在一个索引中，那么不需要回查表，那么就叫覆盖索引。

实现覆盖索引：常见的方法是，将被查询的字段，建立到联合索引里去。

3、Explain及优化

参考：https://www.jianshu.com/p/8fab76bbf448

1
2
3
4
5
6
7
mysql> explain select * from staff;
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
|  1 | SIMPLE      | staff | ALL  | NULL          | 索引  | NULL    | NULL |    2 | NULL  |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
1 row in set

索引优化：

①最左前缀索引：like只用于’string%’，语句中的=和in会动态调整顺序

②唯一索引：唯一键区分度在0.1以上

③无法使用索引：!= 、is null 、 or、>< 、（5.7以后根据数量自动判定）in 、not in

④联合索引：避免select * ，查询列使用覆盖索引

1
2
SELECT uid From user Where gid = 2 order by ctime asc limit 10
ALTER TABLE user add index idx_gid_ctime_uid(gid,ctime,uid) #创建联合覆盖索引，避免回表查询

语句优化：

①CHAR vs. VARCHAR

​ CHAR：固定长度字符类型，查询效率较高，因为每个值占用的空间是固定的。适合存储定长字符串。

​ VARCHAR：可变长度字符类型，第一个字节记录数据长度（在MySQL 5.0及以下版本中，第一个或两个字节记录长度，取决于 最大长度）。存储可变长度的字符串更节省空间，但在查询时由于长度不固定，可能稍慢于CHAR。

②应该针对Explain中Rows增加索引

③group/order by字段均会涉及索引

④Limit中分页查询会随着start值增大而变缓慢，通过子查询+表连接解决

1
2
3
select * from mytbl order by id limit 100000,10  改进后的SQL语句如下：
select * from mytbl where id >= ( select id from mytbl order by id limit 100000,1 ) limit 10
select * from mytbl inner ori join (select id from mytbl order by id limit 100000,10) as tmp on tmp.id=ori.id;

⑤count会进行全表扫描，如果估算可以使用explain

⑥delete删除表时会增加大量undo和redo日志， 确定删除可使用trancate

​

表结构优化：

①单库不超过200张表

②单表不超过500w数据

③单表不超过40列

④单表索引不超过5个

数据库范式 ：

①第一范式（1NF）列不可分割

②第二范式（2NF）属性完全依赖于主键 [ 消除部分子函数依赖 ]

③第三范式（3NF）属性不依赖于其它非主属性 [ 消除传递依赖 ]

数据库范式（Normal Forms）是数据库设计中的重要概念，通过规范化规则确保数据库结构的合理性和有效性。以下是关于数据库第一范式（1NF）、第二范式（2NF）和第三范式（3NF）的详细解释：

第一范式（1NF）

定义：表中的每一列都是原子的，即每一个列值都是不可再分的基本数据项。

特点：

  • 每一列只能包含一个值，不能有重复的列。
  • 表中的每一个字段必须是单一值，不允许有表或数组类型的字段。

示例：

不符合第一范式的表：

1
2
3
4
plaintext复制代码| StudentID | Name       | Subjects        |
|-----------|------------|-----------------|
| 1         | Alice      | Math, Science   |
| 2         | Bob        | Math, History   |

符合第一范式的表：

1
2
3
4
5
6
plaintext复制代码| StudentID | Name       | Subject   |
|-----------|------------|-----------|
| 1         | Alice      | Math      |
| 1         | Alice      | Science   |
| 2         | Bob        | Math      |
| 2         | Bob        | History   |
第二范式（2NF）

定义：在满足第一范式的基础上，表中的每一个非主键列必须完全依赖于主键，不能依赖于主键的一部分。

特点：

  • 必须已经满足1NF。
  • 所有非主键列都完全依赖于主键，消除部分依赖。

示例：

不符合第二范式的表：

1
2
3
4
plaintext复制代码| StudentID | CourseID | StudentName | CourseName |
|-----------|----------|-------------|------------|
| 1         | 101      | Alice       | Math       |
| 2         | 102      | Bob         | History    |

符合第二范式的表：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
plaintext复制代码Students
| StudentID | StudentName |
|-----------|-------------|
| 1         | Alice       |
| 2         | Bob         |

Courses
| CourseID | CourseName |
|----------|------------|
| 101      | Math       |
| 102      | History    |

Enrollments
| StudentID | CourseID |
|-----------|----------|
| 1         | 101      |
| 2         | 102      |
第三范式（3NF）

定义：在满足第二范式的基础上，表中的每一个非主键列必须直接依赖于主键，而不是通过其他非主键列传递依赖于主键。

特点：

  • 必须已经满足2NF。
  • 消除传递依赖，即非主键列之间不应该有依赖关系，所有非主键列都应该直接依赖于主键。

示例：

不符合第三范式的表：

1
2
3
4
plaintext复制代码| StudentID | StudentName | Advisor | AdvisorOffice |
|-----------|-------------|---------|---------------|
| 1         | Alice       | Dr. Smith | Room 101     |
| 2         | Bob         | Dr. Jones | Room 102     |

符合第三范式的表：

1
2
3
4
5
6
7
8
9
10
11
plaintext复制代码Students
| StudentID | StudentName | AdvisorID |
|-----------|-------------|-----------|
| 1         | Alice       | 201       |
| 2         | Bob         | 202       |

Advisors
| AdvisorID | AdvisorName | AdvisorOffice |
|-----------|-------------|---------------|
| 201       | Dr. Smith   | Room 101      |
| 202       | Dr. Jones   | Room 102      |

通过以上三个范式的规范化，可以避免数据冗余，提高数据的一致性和完整性。然而，在实际应用中，有时为了性能考虑，可能会适当地进行反规范化。了解和运用这些范式能够帮助设计出更高效、易维护的数据库结构。

配置优化：

配置连接数、禁用Swap、增加内存、升级SSD硬盘

4、JOIN查询

img

left join(左联接) 返回包括左表中的所有记录和右表中关联字段相等的记录（返回左表中的所有行，无论右表中是否有匹配的行。如果右表没有匹配的行，右表的对应列将返回 NULL。）

right join(右联接) 返回包括右表中的所有记录和左表中关联字段相等的记录（返回右表中的所有行，无论左表中是否有匹配的行。如果左表没有匹配的行，左表的对应列将返回 NULL。）

inner join(等值连接) 只返回两个表中关联字段相等的行

集群

1、主从复制过程

MySQl主从复制：

  • 原理：将主服务器的binlog日志复制到从服务器上执行一遍，达到主从数据的一致状态。
  • 过程：从库开启一个I/O线程，向主库请求Binlog日志。主节点开启一个binlog dump线程，检查自己的二进制日志，并发送给从节点；从库将接收到的数据保存到中继日志（Relay log）中，另外开启一个SQL线程，把Relay中的操作在自身机器上执行一遍
  • 优点
    • 作为备用数据库，并且不影响业务
    • 可做读写分离，一个写库，一个或多个读库，在不同的服务器上，充分发挥服务器和数据库的性能，但要保证数据的一致性

**binlog记录格式：**statement、row、mixed

基于语句statement的复制、基于行row的复制、基于语句和行（mix）的复制。其中基于row的复制方式更能保证主从库数据的一致性，但日志量较大，在设置时考虑磁盘的空间问题

2、数据一致性问题及解决方案

“主从复制有延时”，这个延时期间读取从库，可能读到不一致的数据。

缓存记录写key法：

在cache里记录哪些记录发生过的写请求，来路由读主库还是读从库

异步复制：

在异步复制中，主库执行完操作后，写入binlog日志后，就返回客户端，这一动作就结束了，并不会验证从库有没有收到，完不完整，所以这样可能会造成数据的不一致。

半同步复制：

当主库每提交一个事务后，不会立即返回，而是等待其中一个从库接收到Binlog并成功写入Relay-log中才返回客户端，通过一份在主库的Binlog，另一份在其中一个从库的Relay-log，可以保证了数据的安全性和一致性。

全同步复制：

指当主库执行完一个事务，所有的从库都执行了该事务才返回给客户端。因为需要等待所有从库执行完该事务才能返回，所以全同步复制的性能必然会收到严重的影响。

3、集群架构

Keepalived + VIP + MySQL 主从/双主

当写节点 Master db1 出现故障时，由 MMM Monitor 或 Keepalived 触发切换脚本，将 VIP 漂移到可用的 Master db2 上。当出现网络抖动或网络分区时，MMM Monitor 会误判，严重时来回切换写 VIP 导致集群双写，当数据复制延迟时，应用程序会出现数据错乱或数据冲突的故障。有效避免单点失效的架构就是采用共享存储，单点故障切换可以通过分布式哨兵系统监控。

img

**架构选型：**MMM 集群 -> MHA集群 -> MHA+Arksentinel。

img

4、故障转移和恢复

转移方式及恢复方法

1
1. 虚拟IP或DNS服务 （Keepalived +VIP/DNS  和 MMM 架构）

问题：在虚拟 IP 运维过程中，刷新ARP过程中有时会出现一个 VIP 绑定在多台服务器同时提供连接的问题。这也是为什么要避免使用 Keepalived+VIP 和 MMM 架构的原因之一，因为它处理不了这类问题而导致集群多点写入。

1
2. 提升备库为主库（MHA、QMHA）

尝试将原 Master 设置 read_only 为 on，避免集群多点写入。借助 binlog server 保留 Master 的 Binlog；当出现数据延迟时，再提升 Slave 为新 Master 之前需要进行数据补齐，否则会丢失数据。

面试题

分库分表

如何进行分库分表

分表用户id进行分表，每个表控制在300万数据。

分库根据业务场景和地域分库，每个库并发不超过2000

Sharding-jdbc 这种 client 层方案的优点在于不用部署，运维成本低，不需要代理层的二次转发请求，性能很高，但是各个系统都需要耦合 Sharding-jdbc 的依赖，升级比较麻烦

Mycat 这种 proxy 层方案的缺点在于需要部署，自己运维一套中间件，运维成本高，但是好处在于对于各个项目是透明的，如果遇到升级之类的都是自己中间件那里搞就行了

水平拆分：一个表放到多个库，分担高并发，加快查询速度

  • id保证业务在关联多张表时可以在同一库上操作
  • range方便扩容和数据统计
  • hash可以使得数据更加平均

垂直拆分：一个表拆成多个表，可以将一些冷数据拆分到冗余库中

不是写瓶颈优先进行分表

  • 分库数据间的数据无法再通过数据库直接查询了。会产生深分页的问题
  • 分库越多，出现问题的可能性越大，维护成本也变得更高。
  • 分库后无法保障跨库间事务，只能借助其他中间件实现最终一致性。

分库首先需考虑满足业务最核心的场景：

1、订单数据按用户分库，可以提升用户的全流程体验

2、超级客户导致数据倾斜可以使用最细粒度唯一标识进行hash拆分

3、按照最细粒度如订单号拆分以后，数据库就无法进行单库排重了

三个问题：

  • 富查询：采用分库分表之后，如何满足跨越分库的查询？使用ES的宽表

    借助分库网关+分库业务虽然能够实现多维度查询的能力，但整体上性能不佳且对正常的写入请求有一定的影响。业界应对多维度实时查询的最常见方式便是借助 ElasticSearch

  • 数据倾斜：数据分库基础上再进行分表

  • 分布式事务：跨多库的修改及多个微服务间的写操作导致的分布式事务问题？

  • 深分页问题：按游标查询，或者叫每次查询都带上上一次查询经过排序后的最大 ID

查看当前线程操作

1
2
3
4
法一
SHOW PROCESSLIST;
法二
SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST;

完整查询

1
SHOW FULL PROCESSLIST;

终止查询：

  • 仅终止查询但不终止连接：

    1
    KILL QUERY Id;

终止线程：

  • 终止整个线程，包括连接：

    1
    KILL Id;

表级锁和行级锁了解吗？有什么区别？

  1. 表级锁：粒度大，消耗资源少，加锁快，不会出现死锁，针对非索引字段，但是触发锁冲突概率高且并发下效率低。innodb和myisam都支持。
  2. 行级锁：粒度小，消耗资源大，加锁慢，会出现死锁，针对索引字段，但是并发下效率高，innodb支持

行级锁的使用有什么注意事项？

只针对索引字段生效，所以当update，delete时where子句没有命中索引的话会失效而会使用表级锁。

InnDB有那些行级锁

  1. 记录锁（record lock）：只锁住当前记录
  2. 间隙锁（gap lock）：锁住一个范围，不包括当前记录
  3. 临键锁 (next-key lock)：Record Lock+Gap Lock。锁住一个范围，包括当前记录。主要是为了解决幻读问题。记录锁只能锁住已经存在的记录，为了避免插入新记录，需要依赖间隙锁。

在InnoDB默认的隔离级别（不可重复读）下，会有幻读的问题。因此当执行update，delete语句时，如果where子句不是命中唯一索引的话，会使用next-key lock方式。防止其他事务修改该记录，能保证当前事务执行时不会产生幻读问题。

image-20240722232804342

共享锁和排他锁呢？

不论是表级锁还是行级锁，都存在共享锁（Share Lock，S 锁）和排他锁（Exclusive Lock，X 锁）这两类

  • 共享锁：读锁S锁，事务在读取记录时会获取共享锁，允许多个事务同时获取（锁兼容）
  • 排他锁：写锁X锁。事务写记录时获取，不允许事务同时获取（锁不兼容）

排他锁与任何的锁都不兼容，共享锁仅和共享锁兼容。

S 锁 X 锁
S 锁 不冲突 冲突
X 锁 冲突 冲突

由于 MVCC（多版本并发控制） 的存在，对于一般的 SELECT 语句，InnoDB 不会加任何锁。不过， 你可以通过以下语句显式加共享锁或排他锁。

1
2
3
4
5
6
# 共享锁 可以在 MySQL 5.7 和 MySQL 8.0 中使用
SELECT ... LOCK IN SHARE MODE;
# 共享锁 可以在 MySQL 8.0 中使用
SELECT ... FOR SHARE;
# 排他锁
SELECT ... FOR UPDATE;

意向锁有什么作用?

在并发下，如果有事务想要获取表级锁需要去判断该表是否有行级锁（事务一想要加表级锁并获取排他锁（写），而表中已经有事务二对一些行加了行级锁并获取了排他锁，这样事务一就会阻塞了）。需要一行行排查，效率底下。为了实现多粒度锁机制，所以有了意向锁，用来协调表锁和行锁。

意向锁是表锁：

  • 意向共享锁（IS）：当事务想对一些行加共享锁时，会对表加意向共享锁
  • 意向排他锁（IX）：当事务想对一些行加排他锁时，会对表加意向排他锁

意向共享锁和意向排他锁两两互相兼容

意向锁之间是互相兼容的。（不同事务锁住不同行就有会多个IS或者IX）

IS 锁 IX 锁
IS 锁 兼容 兼容
IX 锁 兼容 兼容

意向锁和共享锁和排它锁互斥（表级别的共享锁排他锁）

IS 锁 IX 锁
S 锁 兼容 互斥
X 锁 互斥 互斥

多个人同时往表里插数据，数据库表主键是自增，能正常执行吗？

可以的，在多用户并发插入数据的情况下，MySQL InnoDB使用自增锁（auto-increment lock）来确保自增主键的唯一性。自增锁有两种模式：

传统模式（Traditional Mode）：

  • 自增锁是在每次插入新行时获取，并在插入操作完成后立即释放。这确保了自增列值的连续性和唯一性。传统模式下，所有并发插入都会被串行化，以确保自增值的正确性。

互斥锁模式（Interleaved Mode）：

  • 自MySQL 5.1.22起，引入了互斥锁模式。它使用轻量级的互斥锁（mutex），允许多个事务同时分配自增值，而不需要完全串行化。这提高了并发插入的性能。互斥锁模式下，自增值可能不连续，但仍然是唯一的。