MySQL索引、事务学习

男娘i 2022-12-03 12:57 90阅读 0赞

### 文章目录 ###

*  MySQL三范式
 *  数据类型
 *  引擎
 *  索引
 *   *  索引的定义
     *  索引的优缺点
     *  索引类型
     *  索引使用场景
     *  索引覆盖：
     *  索引的数据结构（b树、hash）
     *  索引的基本原理
     *  索引算法
     *  索引设计的原则
     *  创建索引的原则
     *  联合索引，为什么需要注意联合索引的顺序。
     *  数据库索引不使用其他数据结构的原因
 *  事务
 *   *  事务四大特性（ACID）
     *  脏读、不可重复读、幻读
     *  事务的隔离级别

本篇文章根据 [ThinkWon][]博客 [MySQL面试题][MySQL]学习

# MySQL三范式 #

**第一范式：** 字段不可再分。  
**第二范式：** 在一范式的基础上，要有主键，且非主键列要完全依赖主键，不能依赖主键的一部分。  
**第三范式：** 在二范式基础上，不可有传递依赖，非主键字段直接依赖主键。

# 数据类型 #

**整数类型**

<table> 
 <thead> 
  <tr> 
   <th>tinyIny</th> 
   <th>smalint</th> 
   <th>mediumint</th> 
   <th>int(integer)</th> 
   <th>bigint</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>1字节</td> 
   <td>2字节</td> 
   <td>3字节</td> 
   <td>4字节</td> 
   <td>8字节</td> 
  </tr> 
  <tr> 
   <td>8位</td> 
   <td>16位</td> 
   <td>24位</td> 
   <td>32位</td> 
   <td>64位</td> 
  </tr> 
 </tbody> 
</table>

> 任何证书都可加`unsigned`属性，表示数据无符号，非负整数；`unsiged zerofill`属性，不足位数左补0；整数类型长度在大多数场景无意义，不会限制值的合法范围。

**实数类型**

> `float` `double` `decimal`  
> `decimal`可以用于存储比`bigint`还大的整数，能存储精确的小数而`float`和`double`是有取值范围的。支持使用浮点近似计算，计算时`float`和`double`相比`decimal`效率更高一些，`decimal`相当于用字符串进行处理

**字符串类型**

> `varchar` `char` `text` `blob`  
> `varchar`用于存储可变长字符串，比定长类型更节省空间；  
> `varchar`用额外1或2个字节存储字符串长度，列长度小于255字节用1个字节表示否则使用2个字节表示；  
> `varchar`存储内容超出设置长度时内容会被截断；  
> `char`是定长的，根据定义长度分配空间；  
> `char`适合存储很短的字符串，或所有值接近同一长度的字符串；  
> `char`超出长度同样内容会被截断；
> 
> --------------------
> 
> **使用策略**  
> 对于经常变更的数据来说，`char`比`varchar`更好，`char`不容易产生碎片对于非常短的列来说`char`比`varchar`在存储空间上更有效率；使用时要注意只分配需要的空间，更长的列排序时会消耗更多内存，尽量避免使用`TEXT/BLOB`类型，查询时会使用临时表导致严重的性能开销。

**枚举类型**

> `ENUM`  
> 把不重复的数据存储为一个预定义的集合。有时可以使用`ENUM`代替常用的字符串类型。  
> `ENUM`存储非常紧凑，会把列表值压缩到一个或两个字节  
> `ENUM`在内部存储时，其实存的是整数

**日期和时间类型**

> 尽量使用`timestamp`，空间效率高于`datetime`  
> 用整数保存时间通常不方便处理，如果需要存储微妙，可以使用`bigint`存储

# 引擎 #

> **MySQL存储引擎MyISAM与InnoDB区别**

**存储引擎：MySQL中的数据、索引以及其他对象是如何存储的，是一套文件系统的实现。**  
**Innodb引擎：** Innodb引擎提供了对数据库ACID事务的支持。并且还提供了行级锁和外键的约束。它的设计目标就是处理大数据容量的数据库系统。  
\*\*MyISAM引擎(原本MySQL的默认引擎)：\*\*不提供事务的支持，也不支持行级锁和外键。

> **MyISAM索引与InnoDB索引的区别**
> 
>  *  InnoDB索引是聚簇索引，MyISAM索引是非聚簇索引。
>  *  InnoDB的主键索引的叶子节点存储着行数据，因此主键索引非常高效
>  *  MyISAM索引的叶子节点存储的是行数据地址，需要在寻址一次才能得到数据。
>  *  InnoDB非主键索引的叶子节点存储的是主键和其他带索引的列数据，因此查询时做到覆盖索引会非常高效。

**InnoDB引擎的4大特性**  
1、插入缓存  
2、二次写  
3、自适应哈希索引  
4、预读  
**存储引擎选择**  
**如果没有特别的需求，使用默认的InnoDB**  
**MyISAM：** 以读写插入为主的应用程序，比如博客系统、新闻门户网站。  
**InnoDB：** 更新(删除)操作频率也高，或者要保证数据的完整性；并发量高，支持事务和外键。比如OA自动化办公系统。

# 索引 #

## 索引的定义 ##

*  **索引是一种特殊的文件**(InnoDB数据表上的索引是表空间的一个组成部分)，它们**包含着对数据表里所有记录的引用指针**
 *  **索引是一种数据结构**，数据库索引，**是数据库管理系统中一个排序的数据结构，** 目的**协助快速查询，更新数据库表中的数据。** 索引的实习通常**使用B树及其变种B+树**
 *  更通俗说，索引就相当于**目录**，方便查找内容，通过对内容建立索引形成目录。**索引是一个文件，它是要占物理空间(所以不是越多越好)**

## 索引的优缺点 ##

**优点：**

*  加快数据的检索速度，创建索引主要原因。
 *  查询过程中，优化隐藏器，提高系统的性能。

**缺点：**

*  时间方面：创建索引和维护索引要耗费时间；对表中数据进行增、删、改时，索引也要动态维护，降低增、删、改效率。
 *  空间方面：索引需要物理空间。

## 索引类型 ##

**主键索引：** 数据列不允许重复(数据不重复)，不允许为NULL，一个表只能有一个主键。  
**唯一索引：** 数据列不允许重复，允许为NULL，一个表允许多个列创建唯一索引。

*  可以通过`alter table table_name add unique (column);`创建唯一索引。
 *  可以通过`alter table table_name add unique (column1, column2);`创建唯一组合索引。

**普通索引：** 基本的索引类型，没有唯一性的限制，允许为NULL值。

*  可以通过`alter table table_name add index index_name (column);`创建普通索引。
 *  可以通过`alter table table_name add index index_name (column1,column2);`创建组合索引。

**全文索引：** 是目前搜索引擎使用的一种关键技术。

*  可以通过`alter table table_name add fulltext (column);`创建全文索引。

## 索引使用场景 ##

**where（条件查询时）**

> 根据某字段查询时，该字段建立索引查询比不建立索引查询，查询的效率会高很多(数据越高越明显)  
> 当条件查询的字段有多个索引时，最终会选一个较优的作为检索的依据。

**order by（排序）**

> 当使用order by将查询结果按某字段排序时，如果该字段没有建立索引，那么执行计划会将查询出的所有数据使用外部排序（将数据从硬盘分批读取到内存，使用内部排序，最后合并排序的结果）这个操作是很影响性能的。  
> 如果对按照某个字段排序该字段建立了索引，由于索引本身有序因此可以直接按照索引的顺序和映射关系逐条取出数据即可。而且如果需要分页，只需取出索引表某个范围内对应的数据。而不像未建立索引要先取出所有数据再排序，再返回某个范围内的数据。

**jojn（连接）**

> 对join语句匹配关系（on）涉及的字段建立索引能够提高效率。

## 索引覆盖： ##

> 如果要查询的字段都建立过索引，那么引擎会直接在索引表中查询而不会访问原始数据（否则只要有一个字段没有建立索引就会做全表扫描），这就叫做索引覆盖。因此我们需要尽可能的在select后只写必要的查询字段，以增加索引覆盖的几率。  
> **注意：** 不要想着为每个字段建立索引 ，因为优先使用索引的优势就在于索引的体积小。

## 索引的数据结构（b树、hash） ##

索引数据结构和具体存储引擎的实现有关，在MySQL中使用较多的索引有Hash索引、B+树索引等，InnoDB引擎默认为B+树索引。

**Hash索引**

> Hash索引底层数据结构就是哈希表，因此在绝大多数需求为单条记录查询的时候，可以选择哈希索引，查询性能最快。

--------------------

> 类似于数据结构中简单实现的HASH表（散列表）一样，当我们在MySQL中用哈希索引时，主要通过Hash算法（常见的Hash算法有直接定址法、平方取中法、折叠法、除数取余法、随机数法）将数据库字段数据转换成定长的Hash值，与这条数据的行指针一并存入Hash表的对应位置；如果发生Hash碰撞（两个不同关键字的Hash值相同）则对应Hash键下以链表形式存储。

**B树索引**

> 查询方式：  
> **主键索引区：** Pi（关联保存的时数据地址）按主键查询。  
> **普通索引区：** Si（关联的id的地址，然后再到达上面的地址）所以按主键查询，速度最快。

**B+树性质**

>  *  n颗子树的节点包含n个关键字，不用来保存数据而是保存数据的索引。
>  *  所有的叶子结点中包含了全部关键字的信息，及指向含这些关键字记录的指针，且叶子结点本身依关键字的大小自小而大顺序链接
>  *  所有的非终端结点可以看成是索引部分，结点中仅含其子树中的最大（或最小）关键字。
>  *  B+树中，数据对象的插入和删除仅在叶节点上进行。
>  *  B+树有2个头指针，一个是树的的根节点，一个是最小关键码的叶节点。

## 索引的基本原理 ##

> 索引是用来快速地寻找那些具有特定值的记录。如果没有索引，一般来说执行查询时遍历整张表。  
> **索引原理：就是把无序的数据变成有序的查询**  
> 1、把创建了索引的内容进行排序  
> 2、对排序结果生成倒排表  
> 3、在倒排表内容上拼上数据地址链  
> 4、在查询的时候，先拿到倒排表内容，再取出数据地址链，从而拿到具体数据。

## 索引算法 ##

> **BTree**是常用的MySQL数据库索引算法，也是MySQL默认的算法；因为它不仅可以被用在`=`,`>`,`>=`,`<`,`<=`和`between`这些比较操作符上，而且还可以用于`like`操作符，只要它的查询条件是一个不以通配符开头的常量。
> 
>     --只要它的查询条件是一个不以通配符开头的常量；
>     select * from user where name like 'jack%';
>     --如果以通配符开头，或者没有使用常量，则不会使用索引；
>     select * from user where name like '%jack';
> 
> **Hash算法**  
> Hash索引只能用于对等比较，由于是一次定位数据，所以检索效率远高于BTree索引

## 索引设计的原则 ##

>  *  适合索引的列是出现在where子句中的列，或者连接子句中指定的列
>  *  基数较小的类，索引效果较差，没有必要在此列建立索引
>  *  使用短索引，如果对长字符串列进行索引，应该指定一个前缀长度，这样能够节省大量索引空间
>  *  不要过度索引。索引需要额外的磁盘空间，并降低写操作的性能。在修改表内容的时候，索引会进行更新甚至重构，索引列越多，这个时间就会越长。所以只保持需要的索引有利于查询即可。

## 创建索引的原则 ##

>  *  最左前缀匹配原则，组合索引非常重要的原则，MySQL会一直向右匹配直到遇到范围查询（`>`、`<`、`between`、`like`）就停止匹配，比如`a=1 and b=2 c>3 and d=4` 如果建立`(a,b,c,d)` 顺序的索引，d是用不到索引的，如果建立`(a,b,d,c)` 的索引则都可以用到，a,b,d 的顺序是可以任意调整的。
>  *  较频繁作为查询条件的字段才去创建索引
>  *  更新频繁字段不适合创建索引
>  *  若是不能有效区分数据的列不适合做索引（如：性别，男、女、未知，最多也就三种，区分度太低）
>  *  尽量的扩展索引，不要新建索引。比如表中已经有`a`的索引，现在要加`(a,b)` 的索引，那么只需要修改原来的索引即可。
>  *  定义有外键的数据列一定要建立索引
>  *  对于那些查询中很少涉及的列，重复值比较多的列不要建立索引
>  *  对于定义为text、image 和 bit 的数据类型的列不要建立索引

## 联合索引，为什么需要注意联合索引的顺序。 ##

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

**具体原因：**

> MySQL使用索引时需要索引有序，假设建立了`(name,age,school)`的联合索引，那么索引的排序为：先按照 name 排序，如果 name 相同则按照 age 排序，如果 age 值也相等，则按照 school 进行排序。  
> 当进行查询时，此时索引仅仅按照 name 严格有序，因此必须首先使用 name 字段进行等值查询，之后对于匹配到的列而言，其按照 age 字段严格有序，此时可以使用 age 字段用做索引查找，以此类推。因此在建立联合索引的时候应注意索引列的顺序，一般情况下，将查询需求频繁或者字段选择性高的列放在前面。此外可以根据特例的查询或者表结构进行单独的调整。

## 数据库索引不使用其他数据结构的原因 ##

*  **不使用Hash索引原因**
    
    > 使用Hash索引的话排序会根据Hash值排序但存储的值可能还是无序的。且如果有Hash冲突查询会比较慢
 *  **不使用平衡二叉树(AVL树)原因**
    
    >  *  树越高查找速度越慢
    >  *  当进行范围查找时会进行回旋查找（如：大于5）
 *  **不使用B树原因**
    
    > 虽然说B树的存储结构很好的解决了树高的问题，但进行范围查找时还是会进行回旋查找。

# 事务 #

## 事务四大特性（ACID） ##

>  *  原子性 —— 不可分割
>  *  隔离性 —— 一个事务不被另一个事务影响
>  *  持久性 —— 持久到数据库
>  *  一致性 —— 操作后数据与操作前一致

## 脏读、不可重复读、幻读 ##

**脏读：** 某个事务已更新一份数据，另一个事务在此时读取了同一份数据，由于某些原因，前一个RollBack了操作，则后一个事务所读取的数据就会是不正确的。一个事务读取了另一个事务未提交的数据

**不可重复读：** 在一个事务的两次查询之中数据不一致，这可能时两次查询过程中间插入了一个事务更新的原有的数据。

**幻读：** 在一个事务中的两次查询中数据笔数不一致，例如：有一个事务查询了几行数据，而另一个事务却在此时插入了新的几行数据，先前的事务在接下来的查询中，就会发现有几行数据是它先前所没有的。

## 事务的隔离级别 ##

*  **读取未提交：** 最低的隔离级别，允许读取尚未提交的变更，可能会导致脏读、幻读或不可重复读
 *  **读取已提交：** 允许读取并发事务已经提交的数据，可以阻止脏读，但是幻读或不可重复读仍有可能发生
 *  **可重复读：** 对同一字段的多次读取结果都是一致的，除非数据是被本身事务自己所修改，可以阻止脏读和不可重复读，但幻读仍有可能发生
 *  **可串行化：** 最高的隔离级别，完全服从ACID的隔离级别。所有的事务依次逐个执行，这样事务之间就完全不可能产生干扰，也就是说，该级别可以防止脏读、不可重复读以及幻读

**注意：** MySQL默认采用`可重复读`隔离级别；oracle默认采用`读取已提交`隔离级别

[ThinkWon]: https://blog.csdn.net/ThinkWon
[MySQL]: https://blog.csdn.net/ThinkWon/article/details/104778621