mysql

Mysql

索引的数据结构：用的是B+树。
二叉树：树的高度不受控
红黑树：树的高度不受控制

数据库引擎是形容数据库表的。

innoDB的主键索引结构叶子节点存储的是主键值和记录。非主键索引结构的叶子节点存储的索引值和主键值。
使用Innodb索引，要求表的数据用B+树来组织，索引要求数据库表必须有主键。如果没有指明主键，系统会从数据库表的第一列开始查找能建唯一索引的列来建创建唯一索引结构来存储数据，如果找不到这样的列，数据库管理系统会生成一个隐藏列来保证该列的唯一性，以该列来做唯一索引保存数据。索引为了节约数据库资源，最好自建主键。
整形做排序性能会比其他类型更好。
自增：非自增的节点，会导致B+树的节点发生分裂，

---

1、B树和B+树的区别：

1. B树的特点：

• 节点排序
• 一个节点可以存储多个元素

2. B+树的特点

• 拥有B树的特点
• 叶子节点之间有指针
• 非叶子节点上的元素在叶子节点上都有冗余，也就是叶子节点存储了所有元素，并且排好序。
  在mysql中，一个innodb页就是一个B+树节点，一个innodb页默认16kb,所以一般情况下一颗两层的B+树可以存储2000W左右的数据，然后通过B+树叶子节点存储了所有用户数据并且进行了排序，叶子节点之间有指针，很好的支持全表扫描，范围查找等sql语句。

2、联合索引：

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-3epQgnh0-1652740787957)(_v_images/20220425150742674_15479.png)]
唯一索引：允许null
主键索引：特殊的唯一索引，不允许null

最左前缀原则：

3、mysql锁有哪些，如何理解

3.1 、从数据操作类型划分

• 读锁
• 写锁

3.2 、从数据库操作粒度划分

3.2.1 表级锁：

- 表级别的S锁、X锁
- 意向锁：
	- 意向共享锁：事务有意向对表中的某些行加共享锁。
	- 意向排他锁：事务有意向对表中的某些行加排他锁。
- 自增锁： 主键自增时，插入语句会阻塞。Auto-inc锁是当向使用含有AUTO_INCREMENT列的表中插入数据时需要获取的一种特殊的表级锁。
	一个事务在持有Auto-inc锁的过程中，其他事务的插入语句都要被阻塞。
- 元数据锁： 当对一个表做增删改查操作的时候，加MDL(元数据锁)读锁;当要对表结构变更操作的时候，加MDL写锁。（这个锁自动添加）。

3.2.2 行锁：

- 记录锁：
- 间隙锁：为了防止插入幻影记录而提出的。
- 临键锁：即可锁住某条记录，又想阻止在该记录之前的间隙插入新纪录。
- 插入意向锁：innoDB规定事务在等待的时候也需要在内存中生成一个锁结构，表明事务想在某个间隙插入新记录，但是现在正在等待。

3.2.3 页锁：

每个层级锁的数量是限制的，因为锁会占用内存空间，锁空间的大小是有限制的，当某个层级的数量超过了这个层级的阈值，
就会进行锁升级，也就是用更大粒度的锁代替多个小粒度的锁。

3.3、对待锁的态度：

• 悲观锁：select … fro update 是mysql中的悲观锁。在语句执行过程中，所有扫描到的行都会被加锁，因此在mysql中用的悲观锁必须确定使用了索引，
  而不是全表扫描，否则会把整个表锁住。
• 乐观锁：不采用数据库自身的锁机制来实现，而是通过程序实现。乐观锁多使用多读的应用类型，这样可以提高吞吐量。

3.4 全局锁

就是对真个数据库加锁，当你需要让整个库处于只读状态时，可以使用这个命令Flush table with read lock，之后其他线程的以下语句都会被阻塞：数据库更新（数据的增删改）、数据定义语句和更新类事务的提交。典型的使用场景是做全局逻辑备份。

3.5 死锁

两个事务都持有对方需要的锁，并且在等待对方释放，且双方都不会释放自己的锁。

3.5.1 如何解决死锁：

方式1：等待，直到超时，即当两个事务相互等待时，当一个事务等待时间超过设置的阈值时，就将其回滚，另外事务继续进行。这种方法简单，在innodb中
参数innodb_lock_wait_timeout用来设置超时时间。
缺点：对于在线服务来说，这个等待时间往往是无法接受的，如果将超时时间设置的短些，又容易误伤正常的锁等待。
方式2： 使用死锁检测进行死锁处理。innodb提供了wait-for graph算法来主动进行死锁检测，每当加锁请求无法立即满足需要并进入等待时，awit-for graph算法都会被触发。一旦检测到有死锁，这时候innodb存储引擎回选择回滚undo量最小的事务，让其他事务继续执行。 （innodb_deadlock_detect=on）表示开启这个逻辑。
缺点：每个新的被阻塞线程，都要判断是不是由于自己的加入导致了死锁，这个操作时间复杂度时O(n),如果并发更新同一行的线程数很多，
这样在innodb内部将会有大量的死锁检测工作。可以通过在中间件中实现对相同行的更新，让线程在进入引擎前排队，对少对引擎的并发访问数量。
也可以通过将一行数据改成逻辑上的多行来减少锁冲突。
如何避免死锁：

• 合理设计索引，使业务SQL尽可能通过索引定位更少的行，减少锁竞争
• 调整业务逻辑SQL的执行顺序，避免update/delete长时间持有锁的SQL在事务前面。
• 将大事务拆成小事务
• 适当降低隔离级别

4、Mysql慢查询该如何优化？

1. 是否走了索引，如果没有则优化SQL,利用索引
2. 检查所利用的索引，是否为最优索引
3. 检查所查字段是否都是必须的，是否查询了过多字段。查出了多余数据
4. 检查表中的数据是否过多，是否应该进行分库分表
5. 检查数据库实例所在机器的性能配置，是否太低，是否可以适当增加资源

5、Explain语句结果中各个字段分别表示什么

1、id:查询语句中每出现一个select关键字，mysql就会为他分配一个唯一的id值
2、table: 表名
3、select_type:
查询的类型，主要用于区别普通查询、联合查询、子查询等复杂查询。

• SIMPLE: 简单查询，查询不包含子查询或者UNION。
• PRIMARY: 查询中若包含任何复杂的子部分，最外层查询则被标记为PRIMARY。
• SUBQUERY: 在select或者where列表中包含了子查询
• DERIVED: derived(衍生)，在from列表中包含的子查询会被标记为derived
• UNION: 若第二个select出现在union之后，则被标记为union。
  若union包含在from子句的子查询中，外层select将被标记为：derived
• UNION RESULT: 从union表获取结果的select。

4、type:
常见的访问类型,

• ALL: 全表扫描。
• index：full index scan, index与all区别为index类型只遍历索引树。这通常比all快，
  这因为索引文件通常比数据文件小
• range: 只检索给定范围的行
• ref：非唯一索引扫描，返回匹配某个单独值的所有行。
• eq_ref: 唯一索引扫描，对于每个索引键，表中只有一条记录与之匹配。
• const: 通过索引一次就找到了，const用于主键索引或者唯一索引，因为只匹配一行数据。 如将主键置于where列表中。
• system：表只有一行记录，这是const的特例，基本不会出现。
• NULL:

5、ref:
显示索引的哪一列被使用了，如果可能的话，是一个常数。哪些列或常数被用于查找索引列上的值。
6、possible_keys: 可能用到的索引
7、key:实际上使用到的索引
8、key_len：实际使用到的索引长度
9、rows:预估的需要读取的记录行数
10、filtered: 某个表经过搜索条件过滤后剩余记录条数的百分比

6、索引覆盖：

索引覆盖就是一个sql在执行时，可以利用索引来快速查找，并且在此sql所要查询的字段在当前对应的字段中都包含了，那么就表示此sql走完索引后不用回表了，所需要的字段都在当前索引的叶子节点上存在，可以直接作为结果返回了。

7、最左前缀原则

联合索引就是按照第一列进行排序，然后第一列排好序的基础上再对第二列进行排序，以此类推。如果没有第一列直接访问第二列，第二列肯定是无序的，直接访问后面的列就用不到索引了。
https://www.cnblogs.com/yeyuzhuanjia/p/16267031.html

8、Innodb是如何实现事务的

innodb通过buffer pool、LogBuffer、RedoLog、UndoLog来实现事务，以一个update语句为例

1. innodb在收到一个update语句后，会根据条件找到数据所在的叶，并将该叶缓存在buffer pool中
2. 执行update语句，修改buffer pool中的数据，也就是内存中的数据
3. 针对update语句，生成一个RedoLog日志，并存入LogBuffer中
4. 针对update语句，生成undoLog日志，用于事务回滚
5. 如果事务提交，那么则把RedoLog对象进行持久化，后续还有其他机制将Buffer pool中锁修改的数据页持久化到磁盘
6. 如果事务回滚，则利用undoLog日志进行回滚

9、Redis 和 Mysql 如何保证数据一致性

延时双删，步骤是：先删除redis缓存数据，再更新mysql,然后再删除redis中的数据，这样就算在更新mysql时，有其他线程读取了mysql，把老数据读取到了Redis中，那么也会被删除，从而保证数据一致。

10、索引设计的原则

• 一般等到主体业务功能开发完毕，把涉及到该表相关sql都要拿出来分析之后再建立索引。
• 联合索引尽量覆盖条件
  让每一个联合索引都尽量去包含sql语句里的where、order by、group by的字段，还要确保这些联合索引的字段顺序尽量满足sql查询的最左前缀原则。
• 不要在值比较少的字段上建立索引
  比如一张表有几百万行记录，其中有性别字段， 其值只有男或女， 如果对这种字段建立索引的话，索引树里只包含男女两种值，没 法进行快速的二分查找，用索引就没有太大的意义。 一般建立索引，尽量使用值比较多的字段，这样才能发挥出B+树快速二分查 找的优势。
• 长字符串可以采用前缀索引
  比如varchar(255)，可以针对这个字段的前20个字符建立索引，也就是对这个字段里的每个值的前20个字符放在索引树里，类似于 KEY index(name(20),age,position)。 此时你在where条件里搜索的时候，如果是根据name字段来搜索，那么此时就会先到索引树里根据name字段的前20个字符去搜索，定位到之后前20个字符的前缀匹配的部分数据之后，再回到聚簇索引提取出来 完整name字段值进行比对.
• where与order by冲突时优先where
  大多数情况基于索引进行where筛选往往可以最快速度筛选出你要的少部分数据，然后做排序的成本可能会小很多

11、ACID是靠什么保证的

• A，原子性：是由undo log日志保证，它记录里需要回滚的日志信息，事务回滚时，撤销已经执行成功的sql；
• I，隔离性：由MVCC（多版本并发控制）保证；
• D: 持久性，是由内存加redolog来保证的，mysql修改数据同时，在内存和redolog记录这次操作，宕机的时候可以从redolog中恢复。

12、MVCC

mvcc 多版本并发控制：主要是为了提高数据库并发性能，用更好的方式去处理读-写冲突，做到即使有读写冲突，也能做到不加锁，非阻塞并发读，这个读指的就是快照读，而非当前读。
隐藏字段trId+undolog版本链+readView

13、 脏读、幻读

脏读：无效数据的读取
幻读：幻读指的是一个事务在前后两次查询同一个范围的时候，后一次查询看到了前一次查询没有看到的行。

14、 索引的优缺点

优点：

• 提高数据检索效率，降低数据库的ＩＯ成本
• 通过创建唯一索引，可以保证数据库表中数据的唯一性
• 加速表与表之间的连接
• 减少查询中分组和排序的时间
  缺点：
  －　创建索引和维护索引要耗费时间、并且随着数据量的增大，所耗时间也会增加
  －　索引需要占用磁盘空间

15、主从复制

15.1 作用

• 实现读写分离，提高性能，master作为写库，slave作为读库
• 数据备份
• 高可用：当服务器出现故障或者宕机的情况下，可以切换到从服务器，保证系统的正常运行

15.2 主从复制原理

slave 会从master读取binlog日志，来实现数据同步。
三个线程：

1. 二进制日志转储线程：是一个主库线程，当从库线程链接时，主库可以将二进制日志发送给从库–todo
2. 从库的io线程：链接主库，读取binlog的更新部分，并拷贝到本地的中继日志relayLog
3. 从库的SQL线程，读取relayLog，更新数据库。

15.3 数据一致性问题

1. 异步复制：不等待从库的结果返回，一致性最差
2. 半同步复制：保证一个从库写入成功，主库写入事务才算完成
3. 组复制：MGR，将做个从库组成一个复制组，有N/2+1个同意，则commit

4. 15.4 数据库的多主部署，依赖mycat（todo）