MySQL

最新推荐文章于 2024-10-02 12:42:27 发布

码农辛

最新推荐文章于 2024-10-02 12:42:27 发布

阅读量150

点赞数 1

文章标签： mysql 索引 java

本文链接：https://blog.csdn.net/qq_40745357/article/details/116357334

版权

MySQL

1. 索引

1.1 为什么MySQL使用B+树？

hash：等值查询很快，但无法范围查询。

二叉树：当数据量过大时二叉树的深度会过深。导致查询可能需要多次IO导致速度变慢。

B树：所有节点中都有数据，过大的数据会使得节点中的索引信息变少，无法存储大规模数据。

B+树：只有叶子节点中有数据，其他节点中只有索引信息，可以存储更多条记录。并且叶子节点由双向链表连接，可以很快地做范围查询。

1.2 索引匹配方式

现有表staffs

有字段（id，name，age，pos，add_time）

有组合索引（name，age，pos）

全值匹配：和组合索引中所有的列进行匹配：explain select * from staffs where name=‘张三’ and age=23 and pos =‘dev’;
匹配最左前缀：和组合索引中的前几列进行匹配：（explain select * from staffs where name=‘张三’ and age=23;）会使用索引。（explain select * from staffs where name=‘张三’ and pos =‘dev’;）中的pos='dev’无法使用索引。
匹配列前缀：可以匹配某一列的值的开头部份。（explain select * from staffs where name like ‘张%’;）可以使用索引。（explain select * from staffs where name like ‘%三’;）无法使用索引，可以考虑使用倒排索引。
匹配范围值：可以查询某一范围的值（explain select * from staffs where name > ‘a’ and name < ‘b’;）可以使用索引。
精确匹配某一列并范围匹配另外一列：（explain select * from staffs where name = ‘a’ and age > 10;）可以使用索引。（explain select * from staffs where name = ‘a’ and age > 10 and pos =‘b’;）范围匹配及之前可以使用索引，之后无法使用索引。（explain select * from staffs where name = ‘a’ and pos =‘b’ and age > 10;）无意义，与之前一样，先前所说的顺序是组合索引的顺序，而不是where后面条件的顺序。
只访问索引的查询：explain select name, age, pos from staffs where name=‘张三’ and age=23 and pos =‘dev’;

1.3 优化细节

当使用索引列进行查询时尽量不要使用表达式，把计算放到业务层而不是数据库层。可以用（select * from staffs where age=23-1;）而不要用（select * from staffs where age+1=23;）
尽量使用主键查询而不是，其他索引，因为其他索引需要回表。
使用前缀索引。当索引字段过长时，可以考虑使用该字段的一部分作为索引。
使用索引排序。当排序时使用索引列而条件中没有索引列（explain select * from staffs order by name;）时不使用索引。当条件使用索引列并且排序也使用索引列时（explain select * from staffs where name > ‘’ order by name;）才会走索引。排序条件必须全为升序或全为降序才能使用索引，既有升序又有降序无法使用索引。
关于or：当使用单列索引的时候，使用or会走索引。使用组合索引时，若表中所有列均在组合索引中，则走索引，否则不走索引。
强制类型转换会导致全表扫描。（explain select name, age, pos from staffs where name=‘123’;）可以使用索引。（explain select name, age, pos from staffs where name=123;）不会使用索引。
更新十分频繁，数据区分度低的字段不宜建立索引。
能使用limit的时候尽量使用limit。这样可以减小IO操作。

1.4 B+树

B+树有一个度（degree）的概念。当节点中的数据等于度时就会进行分裂，即每一个节点中的数据至多为d-1。节点有两种，叶子节点和索引节点。索引节点中不存数据，只是为了帮助找到叶子节点。叶子节点之间用双向链表连接。索引节点中会有key，key的左边指向小于该key的key，右边指向大于等于该key的key。

1）插入：

如果当前树为空树，创建一个叶子结点，然后将记录插入其中，此时这个叶子结点就是根结点，插入操作完成。如果不是空树根据key值找到目标叶子结点，向这个叶子结点插入记录。插入后，若当前结点key的个数小于等于d-1，插入结束。否则将这个叶子结点分裂成左右两个叶子结点，左叶子结点包含前d/2个记录，右结点包含剩下的记录，将右节点中第1个key进位到父结点中（父节点中有此key子节点中也有此key）。进位之后若该节点（被进位的节点）key的个数小于等于d-1则插入结束。否则，将这个索引类型结点分裂成两个索引结点，左索引结点包含前(d-1)/2个key，将下一个key进位到父结点中（此时父索引节点中有此key，而子索引节点中没有此key），右结点包含剩下key，进位到父结点的key左孩子指向左结点, 进位到父结点的key右孩子指向右结点。若新索引节点中的key等于d，继续分裂向上进位。

2）删除：

以后再写。。。

2. 事务的四个特征（ACID）

事务具有四个特征：原子性（ Atomicity ）、一致性（ Consistency ）、隔离性（ Isolation ）和持续性（ Durability ）。这四个特性简称为 ACID 特性。

原子性。事务是数据库的逻辑工作单位，事务中包含的各操作要么都做，要么都不做
一致性。事务执行的结果必须是使数据库从一个一致性状态变到另一个一致性状态。因此当数据库只包含成功事务提交的结果时，就说数据库处于一致性状态。如果数据库系统运行中发生故障，有些事务尚未完成就被迫中断，这些未完成事务对数据库所做的修改有一部分已写入物理数据库，这时数据库就处于一种不正确的状态，或者说是不一致的状态。
隔离性。一个事务的执行不能其它事务干扰。即一个事务内部的操作及使用的数据对其它并发事务是隔离的，并发执行的各个事务之间不能互相干扰。
持续性。也称永久性，指一个事务一旦提交，它对数据库中的数据的改变就应该是永久性的。接下来的其它操作或故障不应该对其执行结果有任何影响。

3. 隔离级别

Read Uncommitted（读未提交）：所有事务都可以看到其他未提交事务的执行结果。存在问题：脏读、不可重复读、幻读、丢失更新。
Read Committed（读已提交）：一个事务只能看见已经提交事务所做的改变。存在问题：不可重复读、幻读、丢失更新
Repeatable Read（可重复读）：这是MySQL的默认事务隔离级别，它确保同一事务的多个实例在并发读取数据时，会看到同样的数据行。存在问题：幻读、丢失更新。
Serializable（可串行化）：这是最高的隔离级别，它通过串行化事务，解决幻读问题。但是效率低。