mysql 底层索引结构、sql优化

索引:

索引是帮助 mysql 高效获取数据的 排好序 的 数据结构

索引的数据结构:

• 二叉树
• 红黑树
• Hash表
• B - Tree

思考一下如果以下数据机构使用不同的索引类型有哪些优缺点?

如果不建立索引的情况下,我们搜索 select * from table where col2 = 89 , 在没加索引的情况下其实是逐行查找,每查找一次就做会和磁盘一次 I/O 交互 6次 I/O 交互才能找到 。

二叉树:

如果我们根据二叉树的数据结构来寻找 col2 = 89 的话, (二分法,左边的元素比右边的元素小) 3次 I/O (第一次34,第二次89,第三次从磁盘中加载89所在的索引指向)。

优点:

二叉树是一种比顺序结构更加高效地查找目标元素的结构，它可以从第一个父节点开始跟目标元素值比较，如果相等则返回当前节点，如果目标元素值小于当前节点，则移动到左侧子节点进行比较，大于的情况则移动到右侧子节点进行比较，反复进行操作最终移动到目标元素节点位置。

缺点:

在特殊的情况下会形成链表结果的数据结构,(如下) 其实和全表扫描一样。

红黑树:

优点:

红黑树也叫平衡二叉树，它不仅继承了二叉树的优点，而且解决了上面二叉树遇到的自增整形索引的问题，从下面的动态图中可以看出红黑树会走动对结构进行调整，始终保证左子节点数 < 父节点数 < 右子节点数的规则。

缺点:

在数据量大的时候，深度也很大。从图中可以看出每个父节点只能存在两个子节点，如果我们有很多数据，那么树的深度依然会很大，可能就会超过十几二十层以上，对我们的磁盘寻址不利，依然会花费很多时间查找。(我们希望控制树的高度 h 矮一些)

Hash:

优点:

对数据进行Hash（散列）运算，然后将哈希结果作为文件指针可以从索引文件中获得数据的文件指针，再到数据文件中获取到数据，按照这样的设计，我们在查找where Col2 = 22的记录时只需要对22做哈希运算得到该索引所对应那行数据的文件指针(精确定位)，从而在MySQL的数据文件中定位到目标记录，查询效率非常高。

缺点:mysql索引为什么要自增,这里也可以设置成Hash

无法解决范围查询（Range）的场景，比如 select *  from table where id >10；因此Hash这种索引结构只能针对字段名=目标值的场景使用，不适合范围查询的场景。

B-Tree:

 既然红黑树存在缺点，那么我们可以在红黑树的基础上构思一种新的储存结构。既然觉得树的深度太长，就只需要适当地增加每个树节点能存储的数据个数即可，但是数据个数也必须要设定一个合理的阈值，不然一个节点数据个数过多会产生多余的消耗。

优点:

BTree的结构可以弥补红黑树的缺点，解决数据量过大时整棵树的深度过长的问题。相同数量的数据只需要更少的层，相同深度的树可以存储更多的数据，查找的效率自然会更高。

缺点：

在查询单条数据是非常快的。但如果范围查的话，BTree结构每次都要从根节点查询一遍，效率会有所降低，因此在实际应用中采用的是另一种BTree的变种B+Tree（B+树）。

B+Tree :

操作系统储存数据的最小单位是页（page）, 而mysql一般给我们分配的是 16KB的大小。

 使用  B+ Tree 树 高度h为3的的情况下,大概可以存放 2千万的数据 。

常见的数据库存储引擎分为 :

1. MySAM 数据文件和索引文件是分离的 (非聚集) xxx.MYD是数据文件, xxx.MYI是索引文件。
2. InnoDB 索引实现 (聚集)  xxx.idb 数据和索引都放在这里面，xxx.frm是数据结构(MySAM和InnoDB 都存在这个文件)。

联合索引:

MySQL可以使用多个字段同时建立一个索引，叫做联合索引。在联合索引中，如果想要命中索引，需要按照建立索引时的字段顺序挨个使用，否则无法命中索引。

• 最左前缀原则。
• like以通配符开头（’%xxx…’）索引失效。
• 索引列上操作、计算、函数、自动或手动转换 都会导致索引失效。
• 字符串不加单引号索引失效  。

• mysql在使用不等于（！=或者<>）的时候无法使用索引会导致全表扫描。

创建索引时需要注意什么？

• 非空字段：应该指定列为NOT NULL，除非你想存储NULL。在mysql中，含有空值的列很难进行查询优化，因为它们使得索引、索引的统计信息以及比较运算更加复杂。你应该用一个特殊的值或者一个空串代替空值。
• 取值离散大的字段：变量各个取值之间的差异程度大的列放到联合索引的前面，可以通过count()函数查看字段的差异值，返回值越大说明字段的唯一值越多字段的离散程度高。
• 索引字段越小越好：数据库的数据存储以页为单位一页存储的数据越多一次IO操作获取的数据越大效率越高。

mysql查询是否回表?

非聚簇索引情况下是需要根据索引指向再去数据文件根据索引地址搜索,需要回表。

InnoDB存储引擎的情况下当查询使用聚簇索引时，在对应的叶子节点，可以获取到整行数据，因此不用再次进行回表查询。

什么是聚簇索引？何时使用聚簇索引与非聚簇索引

• 聚簇索引：将数据存储与索引放到了一块，找到索引也就找到了数据
• 非聚簇索引：将数据存储于索引分开结构，索引结构的叶子节点指向了数据的对应行，myisam通过key_buffer把索引先缓存到内存中，当需要访问数据时（通过索引访问数据），在内存中直接搜索索引，然后通过索引找到磁盘相应数据，这也就是为什么索引不在key buffer命中时，速度慢的原因
   

非聚簇索引一定会回表查询吗？

不一定，这涉及到查询语句所要求的字段是否全部命中了索引，如果全部命中了索引，那么就不必再进行回表查询。

假设我们在员工表的年龄上建立了索引，那么当进行 select age from table where age < 20 的查询时，在索引的叶子节点上，已经包含了age信息，不会再次进行回表查询。

数据库事务：

事务是一个不可分割的数据库操作序列，也是数据库并发控制的基本单位，其执行的结果必须使数据库从一种一致性状态变到另一种一致性状态。事务是逻辑上的一组操作，要么都执行，要么都不执行。

事务的四大特性：

1. 原子性
2. 持久性
3. 一致性
4. 隔离性

事务的隔离级别：

隔离级别	脏读	不可重复读	幻读
READ-UNCOMMITTED	✓	✓	✓
READ-COMMITTED	✗	✓	✓
REPEATABLE-READ	✗	✗	✓
SERIALIZABLE	✗	✗	✗

数据库的乐观锁和悲观锁

悲观锁：适用于多写场景

见名知意，假定会发生并发冲突，屏蔽一切可能违反数据完整性的操作。在查询完数据的时候就把事务锁起来，直到提交事务。

乐观锁：适用于多读场景

见名知意，假设不会发生并发冲突，只在提交操作时检查是否违反数据完整性。在修改数据的时候把事务锁起来，通过version的方式来进行锁定。实现方式：乐一般会使用版本号机制或CAS算法实现。

锁的粒度：

行锁，表锁，页锁。

MyISAM和InnoDB存储引擎使用的锁：

• MyISAM采用表级锁(table-level locking)。
• InnoDB支持行级锁(row-level locking)和表级锁，默认为行级锁。

锁的种类

大家感兴趣可以看下： https://blog.csdn.net/u013276277/article/details/72511360

为什么要优化数据库：

• 系统的吞吐量瓶颈受到数据库的访问速度上的限制
• 随着应用程序的运行，数据库的中的数据会越来越多，处理时间会相应变慢
• 数据是存放在磁盘上的，数据量越多，I/O交互和读写速度受到影响

优化原则：减少系统瓶颈，减少资源占用，增加系统的反应速度。

数据库CPU飙升如何解决：

先用操作系统命令 top 命令观察是不是 mysqld 占用导致的，如果不是，找出占用高的进程，并进行相关处理。

如果是 mysqld 造成的， show processlist，看看里面跑的 session 情况，是不是有消耗资源的 sql 在运行。找出消耗高的 sql，看看执行计划是否准确， index 是否缺失，或者实在是数据量太大造成。

一般来说，肯定要 kill 掉这些线程(同时观察 cpu 使用率是否下降)，等进行相应的调整(比如说加索引、改 sql、改内存参数)之后，再重新跑这些 SQL。

大数据量，大表数据库优化：

1. 限定数据的范围： 务必禁止不带任何限制数据范围条件的查询语句。比如：我们当用户在查询订单历史的时候，我们可以控制一个时间范围内。
2. 读/写分离： 经典的数据库拆分方案，主库负责写，从库负责读。
3. 缓存： 使用MySQL的缓存，或者考虑使用应用级别的缓存。
4. 还有就是通过分库分表的方式进行优化，主要有垂直分表和水平分表。

垂直区分：

根据数据库里面数据表的相关性进行拆分。 可分为查询常用信息，和不常用信息。或者基本信息和行为信息或者其他维度拆分。

垂直拆分的优点： 可以使得行数据变小，可以简化表的结构，易于维护。

垂直拆分的缺点： 主键会出现冗余，需要管理冗余列，并会引起Join操作，可以通过在应用层进行Join来解决。此外，垂直分区会让事务变得更加复杂。

水平区分：

水平拆分是指数据表行的拆分，表的行数超过2000万行时，就会变慢，这时可以把一张的表的数据拆成多张表来存放。

水平拆分优点：

应用端改造小，可以避免单一表数据量过大对性能造成影响。

水平拆分缺点：

给应用增加复杂度，通常查询时需要多个表名，查询所有数据都需UNION操作。

在许多数据库应用中，这种复杂度会超过它带来的优点，查询时会增加读一个索引层的磁盘次数。

MySQL的主从复制原理和作用

MySQL主从复制工作原理

主：binlog线程——记录下所有改变了数据库数据的语句，放进master上的binlog中。

从：io线程——在使用start slave 之后，负责从master上拉取 binlog 内容，放进自己的relay log中。

从：sql执行线程——执行relay log中的语句。

主从复制的作用：

1. 主数据库出现问题，可以切换到从数据库。
2. 可以进行数据库层面的读写分离。
3. 可以在从数据库上进行日常备份
4. 负载均衡，降低单个服务器的压力
5. 高可用和故障切换，帮助应用程序避免单点失败

不懂得大家可以看下B站 这位老师讲的很好: https://www.bilibili.com/video/BV1Zr4y1Q7Vv?p=4