MySQL学习笔记

MySQL笔记

1. 事务

1.1 事务介绍

事务是一组操作的集合，是一个不可分割的工作单位。

事务会把所有操作作为一个整体一起向系统提交或撤销操作请求，这些操作要么同时成功，要么同时失败。

• 注意：MySQL事务默认是自动提交的。每执行完一条语句，MySQL会隐式地提交

1.2 事务操作

1. 通过设置事务提交来控制事务

   select @@autocommit;
   set @@autocommit = 0; # 取消事务手动提交

2. 开启事务

   start transaction

3. 提交与回滚事务

   commit; # 提交
   rollback; # 回滚

1.3 事务四大特性

ACID

• 原子性：事务是不可分割的最小操作单元，要么全部成功，要么全部失败。
• 一致性：事务完成时，必须使所有数据都保持一致状态
• 隔离性：数据库系统提供的隔离机制，保证事务在不受外部并发操作影响的独立环境下运行。
• 持久性：事务一旦提交或回滚，它对数据库中的数据改变是永久的。

1.4 并发事务问题

• 脏读：一个事务读到另外一个事务还未提交的数据
• 不可重复读：一个事务先后读取同一条记录，但两次读取的数据不同。原因是另一个事务修改了其中的部分数据。
• 幻读：一个事务按照条件查询数据时，没有对应的数据行，但是在插入数据时，又发现这行数据已经存在。原因是另一个事务在此过程中插入了此条数据。

1.5 事务隔离级别

• 查看事务隔离级别

  select @@transaction_isolation

• 设置事务隔离级别

  set [GLOBAL|SESSION] transaction_isolation = '隔离级别'
  -- 隔离级别格式：READ-UNCOMMITTED ，READ-COMMITTED  ，REPEATABLE-READ  ，SERIALIZABLE

2. 存储引擎

2.1 MySQL体系结构

1. 连接层：完成诸如连接处理、授权认证等操作。在该层引入线程池概念，为通过认证安全接入的客户端提供线程
2. 服务层：完成绝大多数核心功能，如SQL接口，并完成缓存的查询、SQL的分析与优化、部分内置函数的执行。在该层，服务器会解析查询并创建相应的内部解析数，对其完成相应的优化：如表查询顺序、是否利用索引等。
3. 引擎层：真正负责MySQL中数据的存储和提取。
4. 存储层：将数据（redolog、undolog、数据、索引、二进制日志、错误日志、查询日志、慢查询日志等）存储在文件系统之上

2.2 存储引擎介绍

存储引擎是MySQL数据库的核心

• 存储引擎是存储数据、建立索引、更新查询数据等技术的实现方式
• 存储引擎是基于表的，而不是基于库的。在创建表时，可以为表指定存储引擎

2.2.1 InnoDB

InnoDB是一种兼顾高可靠性与高性能的通用存储引擎，在MySQL5.5之后为默认存储引擎

特点：

1. DML操作遵循ACID模型，支持事务
2. 行级锁，提高并发访问性能
3. 支持外键FOREIGN KEY约束，保证数据完整性与正确性

文件：xxx.ibd。xxx为表名，该引擎下每张表都会对应一个表空间文件：存储该表的表结构、数据与索引

逻辑存储结构

• 表空间：InnoDB存储引擎逻辑结构的最高层，ibd文件其实就是其表空间文件，其中包含多个Segment段
• 段：表空间是由各个段组成的：数据段、索引段、回滚段等。一个段中包含多个区
• 区：区是表空间的单元结构，每个区大小为1M。默认情况下，页大小为16K，即一个区中一共有64个连续的页
• 页：页是组成区的最小单元，页也是InnoDB存储引擎磁盘管理的最小单元，每个页大小默认为16KB
• 行：InnoDB存储引擎是面向行的，数据是按行存放

2.3 存储引擎对比

InnoDB与MyISAM区别：

• InnoDB支持事务；而MyISAM不支持
• InnoDB支持行锁与表锁；而MyISAM只支持表锁，不支持行锁
• InnoDB支持外键；而MyISAM不支持

3. 索引

3.1 索引概述

索引是一种帮助MySQL高效获取数据的数据结构。除数据库本身存储的数据，数据库系统还维护者满足特定查找算法的数据结构，这些数据结构通过某种方式引用指向数据，从而实现高级查找算法。

优点：

1. 提高数据检索的效率，降低数据库IO成本
2. 通过索引对数据进行排序，降低数据排序成本，降低CPU消耗

缺点：

1. 索引也需要占据空间（不过磁盘不值钱）
2. 索引大大提升了查询效率，但同时也降低了更新表的速度

3.2 索引结构

3.2.1 二叉搜索树

二叉搜索树虽然能提升查询效率，但是存在极端情况：顺序插入时会形成链表

此外：大数据量的情况下，二叉树的层级较深，检索速度慢

若采用红黑树，虽然解决了极端情况出现的链表问题，但仍然存在第二点问题。

3.2.2 B-Tree

B树是一种多路平衡查找树，相比于二叉树，B树每个节点可以有多个分支。

以一颗最大度数为5的B-Tree为例，每个节点最多可以存储4个key，5个指针

3.2.3 B+Tree

B+Tree是B-Tree的变体。其只有叶子节点存放具体的数据。

B+Tree与B-Tree的对比：

1. B 树的所有节点既存放键(key) 也存放 数据(data)，而 B+树只有叶子节点存放 key 和 data，其他内节点只存放 key。
2. B 树的叶子节点都是独立的;B+树的叶子节点有一条引用链指向与它相邻的叶子节点。
3. B 树的检索的过程相当于对范围内的每个节点的关键字做二分查找，可能还没有到达叶子节点，检索就结束了。而 B+树的检索效率就很稳定了，任何查找都是从根节点到叶子节点的过程，叶子节点的顺序检索很明显。

3.2.4 Hash

MySQL中除了支持B+Tree索引，还支持Hash索引

哈希表是键值对的集合，通过键(key)即可快速取出对应的值(value)，因此哈希表可以快速检索数据（接近 O(1)）。

但是哈希算法有个 Hash 冲突 问题，也就是说多个不同的 key 最后得到的 index 相同。通常情况下，我们常用的解决办法是 链地址法。链地址法就是将哈希冲突数据存放在链表中。就比如 JDK1.8 之前 HashMap 就是通过链地址法来解决哈希冲突的。不过，JDK1.8 以后HashMap为了减少链表过长的时候搜索时间过长引入了红黑树。

优点：Hash索引对于单条数据查询效率很高

缺点：Hash索引只能用于对等比较，不支持范围查询；无法利用索引完成排序操作

3.3 索引分类

在MySQL数据库中，索引主要分为：主键索引、唯一索引、常规索引、全文索引

根据索引的存储形式，又可分为：聚集索引、二级索引

聚集索引选取规则：

1. 如果存在主键，则主键索引就是聚集索引
2. 如果不存在主键，则使用第一个唯一索引作为聚集索引
3. 如果表没有主键或没有唯一索引，则InnoDB会自动生成一个rowid作为隐藏的聚集索引

• 聚集索引的叶子节点下存放的为这一行的数据
• 二级索引的叶子节点下存放的为该字段对应的主键值

执行 select * from user when name = 'Arm'，其中name为索引

具体过程为：

1. 由于是根据name字段进行查询，所以先根据name=’Arm’到name字段的二级索引中进行匹配查找。但是在二级索引中只能查找到 Arm 对应的主键值 10。
2. 由于查询返回的数据是*，所以此时，还需要根据主键值10，到聚集索引中查找10对应的记录，最终找到10对应的行row。
3. 最终拿到这一行的数据，直接返回即可。

回表查询：先到二级索引中查找数据，找到主键值之后，再到聚集索引中根据主键值获取数据。

3.4 索引语法

1. 创建索引

   CREATE [ UNIQUE | FULLTEXT ] INDEX index_name ON table_name ( index_col_name,... );

2. 查看索引

   SHOW INDEX FROM table_name;

3. 删除索引

   DROP INDEX index_name ON table_name;

3.5 SQL性能分析

3.5.1 SQL执行频率

通过如下命令，可以查看当前数据库的 INSERT、UPDATE、DELETE、SELECT的访问频次：

-- show [session|global] status
-- session 是查看当前会话 
-- global 是查询全局数据 
SHOW GLOBAL STATUS LIKE 'Com_______';

通过上述指令，我们可以查看到当前数据库到底是以查询为主，还是以增删改为主，从而为数据库优化提供参考依据。

• 如果是以增删改为主，我们可以考虑不对其进行索引的优化。
• 如果是以查询为主，那么就要考虑对数据库的索引进行优化了。

3.5.2 慢查询日志

慢查询日志记录了所有执行时间超过指定参数（long_query_time，单位：秒，默认10秒）的所有SQL语句的日志。

MySQL的慢查询日志默认没有开启，查看系统变量 slow_query_log。

如果要开启慢查询日志，需要在MySQL的配置文件（/etc/my.cnf）中配置如下信息：

# 开启MySQL慢日志查询开关 
slow_query_log=1 
# 设置慢日志的时间为2秒，SQL语句执行时间超过2秒，就会视为慢查询，记录慢查询日志 
long_query_time=2

3.5.3 profile详情

通过 show profiles 能够查询出每一条命令的执行时间

通过set语句在session/global级别开启profiling：

set profiling = 1;

执行完一系列业务SQL操作后，通过如下命令查询执行耗时

-- 查看每一条SQL的耗时基本情况 
show profiles; 
-- 查看指定query_id的SQL语句各个阶段的耗时情况 
show profile for query query_id; 
-- 查看指定query_id的SQL语句CPU的使用情况 
show profile cpu for query query_id;

3.5.4 explain

EXPLAIN 或者 DESC命令获取 MySQL 如何执行 SELECT 语句的信息，包括在 SELECT 语句执行过程中表如何连接和连接的顺序。

-- 直接在select语句之前加上关键字 
explain / desc EXPLAIN SELECT 字段列表 FROM 表名 WHERE 条件 ;

Explain 执行计划中各个字段的含义:

3.6 索引使用规则

4. SQL优化

4.1 插入数据

• insert优化

  • 执行批量插入：500 ~ 1000条
  • 手动开启与提交事务
  • 主键顺序插入

• 大批量插入数据

  • 如果一次性需要插入大批量数据，使用insert语句插入性能较低，此时需要MySQL提供的load指令

  

  使用如下指令，将数据脚本文件中的数据加载到表结构中

  -- 客户端连接服务端时，加上参数 -–local-infile 
  mysql –-local-infile -u root -p 
  -- 设置全局参数local_infile为1，开启从本地加载文件导入数据的开关 
  set global local_infile = 1; 
  -- 执行load指令将准备好的数据，加载到表结构中 
  load data local infile '/root/sql1.log' into table tb_user fields 
  terminated by ',' lines terminated by '\n' ;

  开启local_infile之后，在任意一个数据库中创建一张表

  CREATE TABLE `tb_user` ( 
      `id` INT(11) NOT NULL AUTO_INCREMENT, 
      `username` VARCHAR(50) NOT NULL, 
      `password` VARCHAR(50) NOT NULL, 
      `name` VARCHAR(20) NOT NULL, `birthday` DATE DEFAULT NULL, 
      `sex` CHAR(1) DEFAULT NULL, 
      PRIMARY KEY (`id`), 
      UNIQUE KEY `unique_user_username` (`username`) 
  ) ENGINE=INNODB DEFAULT CHARSET=utf8 ;

  然后在数据库上加载对应的文件

  load data local infile '/Users/xxx/Documents/load_user_100w_sort.sql' into table tb_user fields terminated by ',' lines terminated by '\n';

  

  从中可见性能提升了很多

4.2 主键优化

在InnoDB存储引擎中，表数据都是根据主键顺序组织存放的，这种存储方式的表称为索引组织表

在InnoDB引擎中，数据行是记录在逻辑结构 page 页中的，而每一个页的大小是固定的，默认16K。那也就意味着， 一个页中所存储的行也是有限的，如果插入的数据行row在该页存储不小，将会存储到下一个页中，页与页之间会通过指针连接。

• 页分裂

  页可以为空，也可以填充一半，也可以填充100%。每个页包含了2-N行数据(如果一行数据过大，会行溢出)，根据主键排列。

  • 主键顺序插入时，当前页满了之后，会插入到下一页。页与页之间用指针相连。
  • 主键乱序插入时，当插入的主键id在中间且当前页已经写满时，会将当前页的后一半数据移动到新的一页，然后再插入该主键id，此时需要重新设置页与页之间的链表指针 => 页分裂

• 页合并

  • 当删除一行记录时，实际上记录并没有被物理删除，只是记录被标记（flaged）为删除并且它的空间变得允许被其他记录声明使用。
  • 当页中删除的记录达到 MERGE_THRESHOLD（默认为页的50%），InnoDB会开始寻找最靠近的页（前或后）看看是否可以将两个页合并以优化空间使用。

索引设计原则：

1. 满足业务需求的情况下，尽量降低主键的长度。
2. 插入数据时，尽量选择顺序插入，选择使用AUTO_INCREMENT自增主键。
3. 尽量不要使用UUID做主键或者是其他自然主键，如身份证号。
4. 业务操作时，避免对主键的修改。

4.3 order by优化

MySQL的排序，有两种方式：

• Using filesort : 通过表的索引或全表扫描，读取满足条件的数据行，然后在排序缓冲区sortbuffer中完成排序操作，所有不是通过索引直接返回排序结果的排序都叫 FileSort 排序。
• Using index : 通过有序索引顺序扫描直接返回有序数据，这种情况即为 using index，不需要额外排序，操作效率高。

在优化排序操作时，尽量采用Using Index

order by优化原则

1. 根据排序字段建立合适的索引，多字段排序时，也遵循最左前缀法则
2. 尽量使用覆盖索引
3. 多字段排序, 一个升序一个降序，此时需要注意联合索引在创建时的规则（ASC/DESC）
4. 如果不可避免的出现filesort，大数据量排序时，可以适当增大排序缓冲区大小sort_buffer_size，默认256K

4.4 group by优化

在分组操作中，我们需要通过以下两点进行优化，以提升性能：

1. 在分组操作时，可以通过索引来提高效率。
2. 分组操作时，索引的使用也是满足最左前缀法则的。

4.5 limit优化

优化思路: 一般分页查询时，通过创建 覆盖索引 能够比较好地提高性能，可以通过覆盖索引加子查询形式进行优化。

explain select * from tb_sku t , (select id from tb_sku order by id limit 2000000,10) a where t.id = a.id;

4.6 count优化

当表的数据量很大时，对表执行count操作是非常耗时的

• MyISAM 引擎把一个表的总行数存在了磁盘上，因此执行 count(*) 的时候会直接返回这个数，效率很高； 但是如果是带条件的count，MyISAM也慢。
• InnoDB执行 count(*) 的时候，需要把数据一行一行地从引擎里面读出来，然后累积计数。
• 如果想大幅度提升InnoDB表的count效率，可以借助Redis手动统计计数

count用法：count(字段) < count(主键 id) < count(1) ≈ count(*)

4.7 update优化

在采用update语句进行更新数据操作时，最好将索引作为查询数据的条件。这是因为InnoDB的行锁是针对索引加锁，而不是针对记录加锁。如果索引失效，则行锁会自动升级为表锁。