• 1. MySQL索引
  • 1.1. 作用
  • 1.2. 分类
  • 1.3. 使用
    • 1.3.1. 创建
    • 1.3.2. 查看
    • 1.3.3. 删除
  • 1.4. 原理
    • 1.4.1. 索引数据结构
      • 1.4.1.1. hash表
      • 1.4.1.2. B树和B+树
      • 1.4.1.3. MySQL索引数据结构
• 2. MySQL事务
  • 2.1. 定义
  • 2.2. 操作
  • 2.3. 特性——ACID
  • 2.4. 事务隔离级别
    • 2.4.1. read uncommitted
    • 2.4.2. read committed
    • 2.4.3. repeatable read（InnoDB默认隔离级别）
    • 2.4.4. Serializable
    • 2.4.5. 并发事务可能引发的问题
      • 2.4.5.1. 脏读
      • 2.4.5.2. 不可重复读
      • 2.4.5.3. 幻读
      • 2.4.5.4. 不同的隔离级别产生的并发事务问题
• 3. MySQL-锁
  • 3.1. 分类
    • 3.1.1. InnoDB锁
      • 3.1.1.1. 表级锁
      • 3.1.1.2. 行级锁
• 4. MySQL日志
  • 4.1. 错误日志
  • 4.2. 二进制日志
    • 4.2.1. 二进制日志格式
  • 4.3. 查询日志
  • 4.4. 缓慢查询日志

1. MySQL索引

1.1. 作用

索引用于快速定位数据，而不必在每次访问数据库表时搜索数据库表中的每一行。可以使用数据库表的一个或多个列创建索引，为快速随机查找和有序记录的高效访问提供基础。

1.2. 分类

• 单列索引
  1. 普通索引
  2. 唯一索引
  3. 主键索引
• 组合索引
• 全文索引
• 空间索引

1.3. 使用

1.3.1. 创建

• 建表时创建

    CREATE TABLE table_name (
    ...
    index_type index_name名(column_list) 
    );
  

• 建表后创建

    ```sql
    -- 方法1
    create index index_name on table_name(column_list); 
  
    -- 方法2
    alter table table_name add index_name 索引名(column_list);
    ```
  

1.3.2. 查看

查看数据库所有索引状态：

SELECT *FROM mysql.innodb_index_stats I WHERE I.database_name='database_name';

查看某个表的索引：SHOW INDEX FROM table_name;

1.3.3. 删除

删除某表的某个索引：
DROP INDEX NAME ON student;或 ALTER TABLE table_name DROP INDEX index_name;

1.4. 原理

一般来讲，索引本身数据量也很大，不可能全部放在内存中，因此索引常常以索引文件的形式存放在硬盘中，但文件I/O是很消耗资源的工作，因此，评价一个数据结构作为索引结构的好坏的一个重要指标就是磁盘I/O次数。

1.4.1. 索引数据结构

1.4.1.1. hash表

• 优点：定位数据速度很快
• 缺点：散列表中的值无序，不能进行范围查找，比如对于“where a>?”这种条件的查找。

1.4.1.2. B树和B+树

B树和B+树的主要区别：

• B+树的叶子结点存储key和value，非叶子结点只存储key，B树的所有节点都存储key和value
• B+树的叶子结点通过指针相连，B树没有。

degree为3的，B树和B+树的结构如下所示：
B树：

B+树：

1.4.1.3. MySQL索引数据结构

MySQL索引使用B+Tree作为数据结构，并且MyISAM和InnoDB的索引有区别：

• 对于MyISAM，B+树的叶子结点存储的是指向真实数据的指针。
• 对于InnoDB，B+树的叶子结点存储的就是真实数据。

2. MySQL事务

2.1. 定义

数据库事务是 DBMS 中的一个逻辑处理单元，它需要一个或多个数据库访问操作。在 DBMS 中，开始和结束事务语句之间的所有类型的数据库访问操作都被认为是一个单一的逻辑事务。在事务处理期间，数据库不一致。只有在提交数据库之后，状态才会从一个一致状态更改为另一个一致状态。

事务是数据库系统区别于文件系统的一个重要特性之一。

2.2. 操作

• 开启事务：BEGIN或START TRANSACTION
• 提交事务：COMMIT
• 回滚：ROLLBACK

关闭/开启事务自动提交：SET autocommit=0/SET autocommit=1

2.3. 特性——ACID

ACID：

1. 原子性（Atomic）：事务不可分割，事务中的任务要么全部成功，要么全部失败。
2. 一致性（consistently）：事务将数据库从一个一致状态迁移到另一个一致状态。
3. 隔离性（isolation）：不同的事务之间相互隔离，互不干扰。
4. 持久性（durability）：事务提交后，数据库状态的修改是永久性的。

2.4. 事务隔离级别

事物的隔离级别由低到高一共有4种：READ UNCOMMITED、READ COMMITED、REPEATABLE RAED、Serializable；

设置事务的隔离级别：SET SESSION TRANSACTION ISOLATION LEVEL 隔离级别;

2.4.1. read uncommitted

事务可以读取到其他事务没有提交的数据。

2.4.2. read committed

一个事务必须等其他事务提交才能读取数据

2.4.3. repeatable read（InnoDB默认隔离级别）

同一个事务内不会读取到其他事务已经提交的数据。

2.4.4. Serializable

处于这个隔离级别的事务都是串行化执行，同一时刻只能有一个事务正在执行。

2.4.5. 并发事务可能引发的问题

2.4.5.1. 脏读

脏读：事务T1读取到事务T2没有提交的数据

2.4.5.2. 不可重复读

不可重复读：事务T1在执行期间多次读取同一行的数据，在多次读取的间隔之间，事务T2对数据进行了修改，导致T1多次读取同一行的数据结果不一致。

2.4.5.3. 幻读

幻读：同一事务内，由选择操作获得的结果集表示的数据状态不能支持后续的业务操作。例如：选择记录不存在，可以插入。但是，当插入时，发现记录已经存在并且无法插入。

对幻读的错误理解：同一事务内同一条选择语句获得的结果不一致，比如第一次选择操作结果集为10条数据，第二次选择操作结果集变成了11条。事实上，这属于不可重复读。这种情况只会出现在R-U(read uncommitted)和R-C级别，不会出现在InnoDB默认的RR级别。

注意：如果事务A对事务B插入的数据进行了覆盖（插入或更新），下次读取会将数据读出来，因为那条记录的DB_TRX_ID会更新为当前事务的ID。

2.4.5.4. 不同的隔离级别产生的并发事务问题

3. MySQL-锁

3.1. 分类

• 按锁粒度分
  1. 表级锁：对操作的整个表加锁。开销小，加锁快，不会出现死锁；锁粒度大，发生锁冲突概率高，并发度低。
  2. 行级锁：对操作的行加锁。开销大，加锁慢，可能出现死锁；锁粒度小，发生锁冲突概率低，并发度高。
• 按操作类型分
  1. 读锁（共享锁）：对于要操作的数据，加了读锁后，可以加多个读锁，但不允许再加写锁。即多个读操作可以同时执行，但不允许写操作。
  2. 写锁（排它锁）：对于要操作的数据，加了写锁后，和其他的读锁和写锁互斥，即其他的读写操作都不允许。

3.1.1. InnoDB锁

3.1.1.1. 表级锁

表级锁在table上加锁，锁粒度大，发生锁冲突的概率高，并发度低。
对于表级锁，主要分为以下三类：

1. 表锁
2. 元数据锁，Meta Data Lock，MDL
3. 意向锁

3.1.1.2. 行级锁

行级锁锁住数据行，锁粒度小，发生锁冲突的概率低，并发度高。

行级锁通过对索引项加锁实现，这意味着只有在通过索引更新数据时才会加行锁，否则对整张表加锁。

行级锁有3种：

1. 记录锁（Record Lock）：锁定单个记录 记录锁又分为：
   • 共享锁（S锁）
   • 排它锁（X锁）
2. 间隙锁（Gap Lock）：锁定记录间的间隙（不包含记录），确保记录之间的间隙不变，防止其他事务进行insert操作。
3. 临键锁（Next-key Lock）：行锁和间隙锁的组合。

4. MySQL日志

MySQL维护管理员依赖的一系列日志文件。主要的日志文件有以下 几种。

4.1. 错误日志

它包含启动和关闭问题以及任意关键错误的细节。此日志通常名为hostname.err，位于data目录中。此日志名可用–log-error命令行选项更改。

MySQL错误日志默认开启。

4.2. 二进制日志

它记录更新过数据（或者可能更新过数据）的所有语句。此日志通常名为hostname-bin，位于data目录内。此名字可以用–log-bin命令行选项更改。注意，这个日志文件是MySQL 5中添加的，以前的MySQL版本中使用的是更新日志。

MySQL8以后二进制日志默认开启，之前的低版本需要通过配置文件开启。 my.ini或my.cnf：

[mysqld]
log-bin=dir/[filename]

其中，dir 参数指定二进制文件的存储路径；filename 参数指定二进制文件的文件名，其形式为 filename.number，number 的形式为 000001、000002 等。

每次发生以下事件时，服务器都会在该系列中创建一个新的二进制日志文件:

• 服务器启动或重新启动
• 服务器刷新日志。
• 当前日志文件的大小达到 max _ binlog _ size。

4.2.1. 二进制日志格式

mysql二进制日志有3种格式可供选择:

• STATEMENT：基于语句的日志记录。
• ROW：基于行的日志记录。在基于行的日志记录中，源将事件写入二进制日志，以指示单个表行如何受到影响。因此，表必须始终使用主键，以确保能够有效地识别行。
• MIXED：混合日志记录。对于混合日志记录，默认情况下使用基于语句的日志记录，但是在某些情况下，日志模式会自动切换到基于行的日志记录。

4.3. 查询日志

它记录所有MySQL活动，在诊断问题时非常有用。此日志文件可能会很快地变得非常大，因此不应该长期使用它。此日志通常名为hostname.log，位于data目录中。此名字可以用 –log命令行选项更改。

4.4. 缓慢查询日志

顾名思义，此日志记录执行缓慢的任何查询。这个日志在确定数据库何处需要优化很有用。此日志通常名为hostname-slow.log，位于data目录中。此名字可以用–log-slow-queries命令行选项更改。

换慢查询日志默认是关闭的，通过在服务器启动配置文件中配置slow_query_log = ON开启。 缓慢查询有个时间变量：long_query_time，默认为10s，也可以通过配置文件修改。当查询时间超过这个时间值，就会被写入慢查询日志文件。

在使用日志时，可用FLUSHLOGS语句来刷新和重新开始所有日志文 件。