文章目录
- MySQL 的内连接、左连接、右连接有什么区别？
- Hash算法
- 索引的基本原理
- 百万级别或以上的数据如何删除
- 数据库为什么使用B+树而不是B树
- 什么是聚簇索引？何时使用聚簇索引与非聚簇索引
- 非聚簇索引一定会回表查询吗？
- 行级锁，表级锁和页级锁对比
- 什么是死锁？怎么解决？
- sql约束:
- SQL的生命周期？
- 大表数据查询，怎么优化:
- 超大叶分页处理:
- 慢查询日志:
- 所以优化也是针对这三个方向来的:
- 优化查询过程中的数据访问
- 数据库优化
- 数据库结构优化:
- 当MySQL单表记录数过大时，数据库的CRUD性能会明显下降，一些常见的优化措施如下：
- MySQL的复制原理以及流程
- 主从复制的作用
- MySQL主从复制解决的问题
- MySQL主从复制工作原理
- 基本原理流程，3个线程以及之间的关联
- 复制过程
- 逻辑区分
- 1) 普通索引
- 例 1
- 2) 唯一索引
- 例 2
- 3) 主键索引
- 4) 联合索引
- 5) 全文索引
- 例 4
着色标记技术,利用指针中多余的空位来进行标记并且使用了读屏障来解决GC线性和程序
线性来保证并发带来的问题,从而避免l全局停顿
【MySQL笔记】正确的理解MySQL的乐观锁，悲观锁与MVCC
【MySQL笔记】正确的理解MySQL的MVCC及实现原理
Java面试常考的 BIO，NIO，AIO 总结
MySQL 的内连接、左连接、右连接有什么区别？
摘取一下SQL大腿群里同学的回复吧：
例：ab两表关联，a表数据有，b表关联数据没有，a表的就不要显示或显示null的问题；几个连接怎么使用，总结一下：
左链接取A集合，右链接取B集合，full join取并集，inner join 取交集。
myisam:
- .frm: 存储表定义
- .myd(MYData):存储数据
- .MYI(MYindex):存储引擎
MyISAM的数据文件和索引文件是分开存储的。
MyISAM使用B+树构建索引树时，叶子节点中存储的键值为索引列的值，数据为索引所在行的磁盘地址
MyISAM的主键索引和二级索引叶子节点存放的都是列值与行号的组合，叶子节点中保存的是数据的物理地址
innodb:
- .frm:存储表定义
- .idb:存储数据和索引,在同一个文件中
每个InnoDB表都有一个聚簇索引 ，聚簇索引使用B+树构建，叶子节点存储的数据是整行记录。一般情况下，聚簇索引等同于主键索引，当一个表没有创建主键索引时，InnoDB会自动创建一个ROWID字段来构建聚簇索引。InnoDB创建索引的具体规则如下：
在表上定义主键PRIMARY KEY，InnoDB将主键索引用作聚簇索引。
如果表没有定义主键，InnoDB会选择第一个不为NULL的唯一索引列用作聚簇索引。
如果以上两个都没有，InnoDB 会使用一个6 字节长整型的隐式字段 ROWID字段构建聚簇索引。该ROWID字段会在插入新行时自动递增。
Hash算法
（常见的Hash算法有直接定址法、平方取中法、折叠法、除数取余法、随机数法），
1.开放定址法（线性探测再散列，二次探测再散列，伪随机探测再散列）
2.再哈希法
3.链地址法(Java hashmap就是这么做的)
4.建立一个公共溢出区
索引的基本原理
快速的进行数据的定位 ,找到那些具有特定值的记录,如果没有执行就回执行查询增长表
- 把创建了索引的列的内容进行排序
- 对排序结果生成倒排表
- 在倒排表内容上拼上数据地址链
- 在查询的时候，先拿到倒排表内容，再取出数据地址链，从而拿到具体数据
百万级别或以上的数据如何删除
关于索引：由于索引需要额外的维护成本，因为索引文件是单独存在的文件,所以当我们对数据的增加,修改,删除,都会产生额外的对索引文件的操作,这些操作需要消耗额外的IO,会降低增/改/删的执行效率。所以，在我们删除数据库百万级别数据的时候，查询MySQL官方手册得知删除数据的速度和创建的索引数量是成正比的。
所以我们想要删除百万数据的时候可以先删除索引（此时大概耗时三分多钟）
然后删除其中无用数据（此过程需要不到两分钟）
删除完成后重新创建索引(此时数据较少了)创建索引也非常快，约十分钟左右。
与之前的直接删除绝对是要快速很多，更别说万一删除中断,一切删除会回滚。那更是坑了
数据库为什么使用B+树而不是B树
- B树只适合随机检索，而B+树同时支持随机检索和顺序检索；
- B+树空间利用率更高，可减少I/O次数，磁盘读写代价更低。
- B+树的查询效率更加稳定。B树搜索有可能会在非叶子结点结束
- B-树在提高了磁盘IO性能的同时并没有解决元素遍历的效率低下的问题。B+树的叶子节点使用指针顺序连接在一起，只要遍历叶子节点就可以实现整棵树的遍历。
- 增删文件（节点）时，效率更高。
什么是聚簇索引？何时使用聚簇索引与非聚簇索引
聚簇索引：将数据存储与索引放到了一块，找到索引也就找到了数据
非聚簇索引：将数据存储于索引分开结构，索引结构的叶子节点指向了数据的对应行，myisam通过key_buffer把索引先缓存到内存中，当需要访问数据时（通过索引访问数据），在内存中直接搜索索引，然后通过索引找到磁盘相应数据，这也就是为什么索引不在key buffer命中时，速度慢的原因
非聚簇索引一定会回表查询吗？
不一定: 涉及到查询语句要求的字段全部命中索引,如果全部中了 索引 那就不在进行回表
行级锁，表级锁和页级锁对比
行级锁 行级锁是Mysql中锁定粒度最细的一种锁，表示只针对当前操作的行进行加锁。行级锁能大大减少数据库操作的冲突。其加锁粒度最小，但加锁的开销也最大。行级锁分为共享锁 和 排他锁。
特点：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。
表级锁 表级锁是MySQL中锁定粒度最大的一种锁，表示对当前操作的整张表加锁，它实现简单，资源消耗较少，被大部分MySQL引擎支持。最常使用的MYISAM与INNODB都支持表级锁定。表级锁定分为表共享读锁（共享锁）与表独占写锁（排他锁）。
特点：开销小，加锁快；不会出现死锁；锁定粒度大，发出锁冲突的概率最高，并发度最低。
页级锁 页级锁是MySQL中锁定粒度介于行级锁和表级锁中间的一种锁。表级锁速度快，但冲突多，行级冲突少，但速度慢。所以取了折衷的页级，一次锁定相邻的一组记录。
特点：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般
MySQL中InnoDB引擎的行锁是怎么实现的？
答：InnoDB是基于索引来完成行锁例: select * from tab_with_index where id = 1 for update;
for update 可以根据条件来完成行锁锁定，并且 id 是有索引键的列，如果 id 不是索引键那么InnoDB将完成表锁，并发将无从谈起
什么是死锁？怎么解决？
死锁是指两个或多个事务在同一资源上相互占用，并请求锁定对方的资源，从而导致恶性循环的现象。
常见的解决死锁的方法
1、如果不同程序会并发存取多个表，尽量约定以相同的顺序访问表，可以大大降低死锁机会。
2、在同一个事务中，尽可能做到一次锁定所需要的所有资源，减少死锁产生概率；
3、对于非常容易产生死锁的业务部分，可以尝试使用升级锁定颗粒度，通过表级锁定来减少死锁产生的概率；
=乐观锁适用于写比较少的情况下（多读场景），即冲突真的很少发生的时候，这样可以省去了锁的开销，加大了系统的整个吞吐量。
但如果是多写的情况，一般会经常产生冲突，这就会导致上层应用会不断的进行retry，这样反倒是降低了性能，所以一般多写的场景下用悲观锁就比较合适。
jdk动态代理和cglib动态代理的区别及分析
红黑树是一个更高效的检索二叉树，有如下特点：
- 每个节点只能是红色或者黑色
- 根节点永远是黑色的
- 所有的叶子的子节点都是空节点，并且都是黑色的
- 每个红色节点的两个子节点都是黑色的（不会有两个连续的红色节点）
- 从任一个节点到其子树中每个叶子节点的路径都包含相同数量的黑色节点（叶子节点到根节点的黑色节点数量每条路径都相同）
sql约束:
- not null 用于控制字段的类容
- unique 控制字段不能重复
- PRIMARY KEY: 也是用于控件字段内容不能重复，但它在一个表只允许出现一个
- FOREIGN KEY: 用于预防破坏表之间连接的动作，也能防止非法数据插入外键列，因为它必须是它指向的那个表中的值之一。
- CHECK: 用于控制字段的值范围。
子查询:
- 条件查询
- 嵌套查询
SQL的生命周期？
- 应用服务器与数据库服务器建立一个连接
- 数据库进程拿到请求sql
- 解析并生成执行计划，执行
- 读取数据到内存并进行逻辑处理
- 通过步骤一的连接，发送结果到客户端
- 关掉连接，释放资源
大表数据查询，怎么优化:
- sql:优化: 优化shema sql语句+ 优化
- 第二缓存: redis
- 主从复制: 读写分离
- 垂直分表:根据你模块的耦合度，将一个大的系统分为多个小的系统，也就是分布式系统；
- 水平切分针对数据量大的表，这一步最麻烦，最能考验技术水平，要选择一个合理的sharding key, 为了有好的查询效率，表结构也要改动，做一定的冗余，应用也要改，sql中尽量带sharding key，将数据定位到限定的表上去查，而不是扫描全部的表；
超大叶分页处理:
- 解决超大分页,其实主要是靠缓存,可预测性的提前查到内容,缓存至redis等k-V数据库中,直接返回即可
- 数据库层面 sql语句处理-----子查询
慢查询日志:
配置:slow_query_log
所以优化也是针对这三个方向来的:
- 语句 ,可能是可能是查询了多余的行并且抛弃掉了，可能是加载了许多结果中并不需要的列，对语句进行分析以及重写
- 分析语句的执行计划，然后获得其使用索引的情况，之后修改语句或者修改索引，使得语句可以尽可能的命中索引。
explain - 如果对语句的优化已经无法进行，可以考虑表中的数据量是否太大，如果是的话可以进行横向或者纵向的分表
优化查询过程中的数据访问
- 访问数据太多导致查询性能下降
- 确定应用程序是否在检索大量超过需要的数据，可能是太多行或列
- 确认MySQL服务器是否在分析大量不必要的数据行
- 避免犯如下SQL语句错误
- 查询不需要的数据。解决办法：使用limit解决
- 多表关联返回全部列。解决办法：指定列名
- 总是返回全部列。解决办法：避免使用SELECT *
- 重复查询相同的数据。解决办法：可以缓存数据，下次直接读取缓存
- 是否在扫描额外的记录。解决办法：
- 使用explain进行分析，如果发现查询需要扫描大量的数据，但只返回少数的行，可以通过如下技巧去优化：
- 使用索引覆盖扫描，把所有的列都放到索引中，这样存储引擎不需要回表获取对应行就可以返回结果。
- 改变数据库和表的结构，修改数据表范式
- 重写SQL语句，让优化器可以以更优的方式执行查询
数据库优化
为什么要优化
- 系统的吞吐量瓶颈往往出现在数据库的访问速度上
- 随着应用程序的运行，数据库的中的数据会越来越多，处理时间会相应变慢
- 数据是存放在磁盘上的，读写速度无法和内存相比
优化原则：减少系统瓶颈，减少资源占用，增加系统的反应速度。
数据库结构优化:
数据冗余,查询和更新速度字段的数据结构是否合理等方面
将字段很多的表分解成多个表
对于字段较多的表，如果有些字段的使用频率很低，可以将这些字段分离出来形成新表。因为当一个表的数据量很大时，会由于使用频率低的字段的存在而变慢。
增加中间表
对于需要经常联合查询的表，可以建立中间表以提高查询效率。
通过建立中间表，将需要通过联合查询的数据插入到中间表中，然后将原来的联合查询改为对中间表的查询。
增加冗余字段
设计数据表时应尽量遵循范式理论的规约，尽可能的减少冗余字段，让数据库设计看起来精致、优雅。但是，合理的加入冗余字段可以提高查询速度。
表的规范化程度越高，表和表之间的关系越多，需要连接查询的情况也就越多，性能也就越差。
冗余字段的值在一个表中修改了 要想办法在其他表中更新,负责出现了数据不一致
当MySQL单表记录数过大时，数据库的CRUD性能会明显下降，一些常见的优化措施如下：
- 限定数据的范围: 务必禁止不带任何限制数据范围条件的查询语句。比如：我们当用户在查询订单历史的时候，我们可以控制在一个月的范围内
- 读写分离: 数据库拆分方案,，主库负责写，从库负责读；
- 缓存： 使用MySQL的缓存，另外对重量级、更新少的数据可以考虑使用应用级别的缓存；
- 还有就是通过分库分表的方式进行优化，主要有垂直分表和水平分表
【MySQL笔记】正确的理解MySQL的乐观锁，悲观锁与MVCC
MySQL的复制原理以及流程
主从复制：将主数据库中的DDL和DML操作通过二进制日志（BINLOG）传输到从数据库上，然后将这些日志重新执行（重做）；从而使得从数据库的数据与主数据库保持一致。
主从复制的作用
主数据库出现问题，可以切换到从数据库。
可以进行数据库层面的读写分离。
可以在从数据库上进行日常备份。
MySQL主从复制解决的问题
数据分布：随意开始或停止复制，并在不同地理位置分布数据备份
负载均衡：降低单个服务器的压力
高可用和故障切换：帮助应用程序避免单点失败
升级测试：可以用更高版本的MySQL作为从库
MySQL主从复制工作原理
在主库上把数据更高记录到二进制日志
从库将主库的日志复制到自己的中继日志
从库读取中继日志的事件，将其重放到从库数据中
基本原理流程，3个线程以及之间的关联
主：binlog线程——记录下所有改变了数据库数据的语句，放进master上的binlog中；
从：io线程——在使用start slave 之后，负责从master上拉取 binlog 内容，放进自己的relay log中；
从：sql执行线程——执行relay log中的语句；
复制过程
Binary log：主数据库的二进制日志
Relay log：从服务器的中继日志
第一步：master在每个事务更新数据完成之前，将该操作记录串行地写入到binlog文件中。
第二步：salve开启一个I/O Thread，该线程在master打开一个普通连接，主要工作是binlog dump process。如果读取的进度已经跟上了master，就进入睡眠状态并等待master产生新的事件。I/O线程最终的目的是将这些事件写入到中继日志中。
第三步：SQL Thread会读取中继日志，并顺序执行该日志中的SQL事件，从而与主数据库中的数据保持一致。
逻辑区分
根据索引的具体用途，MySQL 中的索引在逻辑上分为以下 5 类：
1) 普通索引
普通索引是 MySQL 中最基本的索引类型，它没有任何限制，唯一任务就是加快系统对数据的访问速度。
普通索引允许在定义索引的列中插入重复值和空值。
创建普通索引时，通常使用的关键字是 INDEX 或 KEY。
例 1
下面在 tb_student 表中的 id 字段上建立名为 index_id 的索引。
CREATE INDEX index_id ON tb_student(id);
2) 唯一索引
唯一索引与普通索引类似，不同的是创建唯一性索引的目的不是为了提高访问速度，而是为了避免数据出现重复。
唯一索引列的值必须唯一，允许有空值。如果是组合索引，则列值的组合必须唯一。
创建唯一索引通常使用 UNIQUE 关键字。
例 2
下面在 tb_student 表中的 id 字段上建立名为 index_id 的索引，SQL 语句如下：
CREATE UNIQUE INDEX index_id ON tb_student(id);
其中，id 字段可以有唯一性约束，也可以没有。
3) 主键索引
顾名思义，主键索引就是专门为主键字段创建的索引，也属于索引的一种。
主键索引是一种特殊的唯一索引，不允许值重复或者值为空。
创建主键索引通常使用 PRIMARY KEY 关键字。不能使用 CREATE INDEX 语句创建主键索引。
4) 联合索引
联合索引，在建立索引的时候，尽量在多个单列索引上判断下是否可以使用联合索引。联合索引的使用不仅可以节省空间，还可以更容易的使用到索引覆盖。
联合索引的创建原则，在创建联合索引的时候因该把频繁使用的列、区分度高的列放在前面，频繁使用代表索引利用率高，区分度高代表筛选粒度大，这些都是在索引创建的需要考虑到的优化场景，也可以在常需要作为查询返回的字段上增加到联合索引中，如果在联合索引上增加一个字段而使用到了覆盖索引，那我建议这种情况下使用联合索引
联合索引的使用
- 考虑当前是否已经存在多个可以合并的单列索引，如果有，那么将当前多个单列索引创建为一个联合索引。
- 当前索引存在频繁使用作为返回字段的列，这个时候就可以考虑当前列是否可以加入到当前已经存在索引上，使其查询语句可以使用到覆盖索引
5) 全文索引
全文索引主要用来查找文本中的关键字，只能在 CHAR、VARCHAR 或 TEXT 类型的列上创建。在 MySQL 中只有 MyISAM 存储引擎支持全文索引。
全文索引允许在索引列中插入重复值和空值。
不过对于大容量的数据表，生成全文索引非常消耗时间和硬盘空间。
创建全文索引使用 FULLTEXT 关键字。
例 4
在 tb_student 表中的 info 字段上建立名为 index_info 的全文索引，SQL 语句如下：
CREATE FULLTEXT INDEX index_info ON tb_student(info);
其中，index_info 的存储引擎必须是 MyISAM，info 字段必须是 CHAR、VARCHAR 和 TEXT 等类型。
对MySQL来说，逻辑备份日志（binlog）、重做日志（redolog）、回滚日志（undolog）、锁技术 + MVCC就是MySQL实现事务的基础。
原子性：通过undolog来实现。
持久性：通过binlog、redolog来实现。
隔离性：通过(读写锁+MVCC)来实现。
一致性：MySQL通过原子性，持久性，隔离性最终实现（或者说定义）数据一致性。
- 按照锁的粒度划分：行锁、表锁、页锁
- 按照锁的使用方式划分：共享锁、排它锁（悲观锁的一种实现）
- 还有两种思想上的锁：悲观锁、乐观锁。
- InnoDB中有几种行级锁类型：Record Lock、Gap Lock、Next-key Lock
- Record Lock：在索引记录上加锁
- Gap Lock：间隙锁
- Next-key Lock：Record Lock+Gap Lock