锐客网

  1. 首页 > it技术 > >

MySQL|MySQL 学习-进阶

MySQL学习-进阶

MySQL高级学习
一、MySQL 事务 1.1.事务的概念

  • 一条或多条 SQL 语句组成一个执行单元，其特点是这个单元要么同时成功要么同时失败，单元中的每条 SQL 语句都相互依赖，形成一个整体，如果某条 SQL 语句执行失败或者出现错误，那么整个单元就会回滚，撤回到事务最初的状态，如果单元中所有的 SQL 语句都执行成功，则事务就顺利执行。
1.2.事务的数据准备
-- 创建db10数据库 CREATE DATABASE db10; -- 使用db10数据库 USE db10; -- 创建账户表 CREATE TABLE account( id INT PRIMARY KEY AUTO_INCREMENT, -- 账户id NAME VARCHAR(20),-- 账户名称 money DOUBLE-- 账户余额 ); -- 添加数据 INSERT INTO account VALUES (NULL,'张三',1000),(NULL,'李四',1000);

1.3.未管理事务演示
-- 张三给李四转账500元 -- 1.张三账户-500 UPDATE account SET money=money-500 WHERE NAME='张三'; -- 2.李四账户+500 出错了... UPDATE account SET money=money+500 WHERE NAME='李四'; -- 该场景下，这两条sql语句要么同时成功，要么同时失败。就需要被事务所管理！

1.4.管理事务演示
  • 操作事务的三个步骤
    1. 开启事务：记录回滚点，并通知服务器，将要执行一组操作，要么同时成功、要么同时失败
    2. 执行sql语句：执行具体的一条或多条sql语句
    3. 结束事务(提交|回滚)
      • 提交：没出现问题，数据进行更新
      • 回滚：出现问题，数据恢复到开启事务时的状态
  • 开启事务
-- 标准语法 START TRANSACTION;

  • 回滚事务
-- 标准语法 ROLLBACK;

  • 提交事务
-- 标准语法 COMMIT;

  • 管理事务演示
-- 开启事务 START TRANSACTION; -- 张三给李四转账500元 -- 1.张三账户-500 UPDATE account SET money=money-500 WHERE NAME='张三'; -- 2.李四账户+500 -- 出错了... UPDATE account SET money=money+500 WHERE NAME='李四'; -- 回滚事务(出现问题) ROLLBACK; -- 提交事务(没出现问题) COMMIT;

1.5.事务的提交方式
  • 提交方式
    • 自动提交(MySQL默认为自动提交)
    • 手动提交
  • 修改提交方式
    • 查看提交方式
    -- 标准语法 SELECT @@AUTOCOMMIT; -- 1代表自动提交0代表手动提交

    • 修改提交方式
    -- 标准语法 SET @@AUTOCOMMIT=数字; -- 修改为手动提交 SET @@AUTOCOMMIT=0; -- 查看提交方式 SELECT @@AUTOCOMMIT;

1.6.事务的四大特征(ACID)
  • 原子性(atomicity)
    • 原子性是指事务包含的所有操作要么全部成功，要么全部失败回滚，因此事务的操作如果成功就必须要完全应用到数据库，如果操作失败则不能对数据库有任何影响
  • 一致性(consistency)
    • 一致性是指事务必须使数据库从一个一致性状态变换到另一个一致性状态，也就是说一个事务执行之前和执行之后都必须处于一致性状态
    • 拿转账来说，假设张三和李四两者的钱加起来一共是2000，那么不管A和B之间如何转账，转几次账，事务结束后两个用户的钱相加起来应该还得是2000，这就是事务的一致性
  • 隔离性(isolcation)
    • 隔离性是当多个用户并发访问数据库时，比如操作同一张表时，数据库为每一个用户开启的事务，不能被其他事务的操作所干扰，多个并发事务之间要相互隔离
  • 持久性(durability)
    • 持久性是指一个事务一旦被提交了，那么对数据库中的数据的改变就是永久性的，即便是在数据库系统遇到故障的情况下也不会丢失提交事务的操作
1.7.事务的隔离级别
  • 隔离级别的概念
    • 多个客户端操作时 ,各个客户端的事务之间应该是隔离的，相互独立的 , 不受影响的。
    • 而如果多个事务操作同一批数据时，则需要设置不同的隔离级别 , 否则就会产生问题 。
    • 我们先来了解一下四种隔离级别的名称 , 再来看看可能出现的问题
  • 四种隔离级别
1 读未提交 read uncommitted
2 读已提交 read committed
3 可重复读 repeatable read
4 串行化 serializable
  • 可能引发的问题
问题 现象
脏读 是指在一个事务处理过程中读取了另一个未提交的事务中的数据 , 导致两次查询结果不一致
不可重复读 是指在一个事务处理过程中读取了另一个事务中修改并已提交的数据, 导致两次查询结果不一致
幻读 select 某记录是否存在，不存在，准备插入此记录，但执行 insert 时发现此记录已存在，无法插入。或不存在执行delete删除，却发现删除成功
  • 查询数据库隔离级别
-- 标准语法 SELECT @@TX_ISOLATION;

  • 修改数据库隔离级别
-- 标准语法 SET GLOBAL TRANSACTION ISOLATION LEVEL 级别字符串; -- 修改数据库隔离级别为read uncommitted SET GLOBAL TRANSACTION ISOLATION LEVEL read uncommitted; -- 查看隔离级别 SELECT @@TX_ISOLATION; -- 修改后需要断开连接重新开

1.8.事务隔离级别演示
  • 脏读的问题
    • 窗口1
    -- 查询账户表 select * from account; -- 设置隔离级别为read uncommitted set global transaction isolation level read uncommitted; -- 开启事务 start transaction; -- 转账 update account set money = money - 500 where id = 1; update account set money = money + 500 where id = 2; -- 窗口2查询转账结果 ,出现脏读(查询到其他事务未提交的数据)-- 窗口2查看转账结果后，执行回滚 rollback;

    • 窗口2
    -- 查询隔离级别 select @@tx_isolation; -- 开启事务 start transaction; -- 查询账户表 select * from account;

  • 解决脏读的问题和演示不可重复读的问题
    • 窗口1
    -- 设置隔离级别为read committed set global transaction isolation level read committed; -- 开启事务 start transaction; -- 转账 update account set money = money - 500 where id = 1; update account set money = money + 500 where id = 2; -- 窗口2查看转账结果，并没有发生变化(脏读问题被解决了)-- 执行提交事务。 commit; -- 窗口2查看转账结果，数据发生了变化(出现了不可重复读的问题，读取到其他事务已提交的数据)

    • 窗口2
    -- 查询隔离级别 select @@tx_isolation; -- 开启事务 start transaction; -- 查询账户表 select * from account;

  • 解决不可重复读的问题
    • 窗口1
    -- 设置隔离级别为repeatable read set global transaction isolation level repeatable read; -- 开启事务 start transaction; -- 转账 update account set money = money - 500 where id = 1; update account set money = money + 500 where id = 2; -- 窗口2查看转账结果，并没有发生变化-- 执行提交事务 commit; -- 这个时候窗口2只要还在上次事务中，看到的结果都是相同的。只有窗口2结束事务，才能看到变化(不可重复读的问题被解决)

    • 窗口2
    -- 查询隔离级别 select @@tx_isolation; -- 开启事务 start transaction; -- 查询账户表 select * from account; -- 提交事务 commit; -- 查询账户表 select * from account;

  • 幻读的问题和解决
    • 窗口1
    -- 设置隔离级别为repeatable read set global transaction isolation level repeatable read; -- 开启事务 start transaction; -- 添加一条记录 INSERT INTO account VALUES (3,'王五',1500); -- 查询账户表，本窗口可以查看到id为3的结果 SELECT * FROM account; -- 提交事务 COMMIT;

    • 窗口2
    -- 查询隔离级别 select @@tx_isolation; -- 开启事务 start transaction; -- 查询账户表，查询不到新添加的id为3的记录 select * from account; -- 添加id为3的一条数据，发现添加失败。出现了幻读 INSERT INTO account VALUES (3,'测试',200); -- 提交事务 COMMIT; -- 查询账户表，查询到了新添加的id为3的记录 select * from account;

    • 解决幻读的问题
    /* 窗口1 */ -- 设置隔离级别为serializable set global transaction isolation level serializable; -- 开启事务 start transaction; -- 添加一条记录 INSERT INTO account VALUES (4,'赵六',1600); -- 查询账户表，本窗口可以查看到id为4的结果 SELECT * FROM account; -- 提交事务 COMMIT; /* 窗口2 */ -- 查询隔离级别 select @@tx_isolation; -- 开启事务 start transaction; -- 查询账户表，发现查询语句无法执行，数据表被锁住！只有窗口1提交事务后，才可以继续操作 select * from account; -- 添加id为4的一条数据，发现已经存在了，就不会再添加了！幻读的问题被解决 INSERT INTO account VALUES (4,'测试',200); -- 提交事务 COMMIT;

1.9.隔离级别总结
隔离级别 名称 出现脏读 出现不可重复读 出现幻读 数据库默认隔离级别
1 read uncommitted 读未提交 是 是 是
2 read committed 读已提交 否 是 是 Oracle / SQL Server
3 repeatable read 可重复读 否 否 是 MySQL
4 **serializable ** 串行化 否 否 否
注意：隔离级别从小到大安全性越来越高，但是效率越来越低 , 所以不建议使用READ UNCOMMITTED 和 SERIALIZABLE 隔离级别.
1.10.事务的总结
  • 一条或多条 SQL 语句组成一个执行单元，其特点是这个单元要么同时成功要么同时失败。例如转账操作
  • 开启事务：start transaction;
  • 回滚事务：rollback;
  • 提交事务：commit;
  • 事务四大特征
    • 原子性
    • 持久性
    • 隔离性
    • 一致性
  • 事务的隔离级别
    • read uncommitted(读未提交)
    • read committed (读已提交)
    • repeatable read (可重复读)
    • serializable (串行化)
二、MySQL 存储引擎 2.1、MySQL存储引擎的概念
  • MySQL存储引擎的概念
    • MySQL数据库使用不同的机制存取表文件 , 机制的差别在于不同的存储方式、索引技巧、锁定水平以及广泛的不同的功能和能力，在MySQL中 , 将这些不同的技术及配套的功能称为存储引擎
    • 在关系型数据库中数据的存储是以表的形式存进行储的，所以存储引擎也可以称为表类型（即存储和操作此表的类型）。
    • Oracle , SqlServer等数据库只有一种存储引擎 , 而MySQL针对不同的需求, 配置MySQL的不同的存储引擎 , 就会让数据库采取了不同的处理数据的方式和扩展功能。
    • 通过选择不同的引擎 ,能够获取最佳的方案 , 也能够获得额外的速度或者功能，提高程序的整体效果。所以了解引擎的特性 , 才能贴合我们的需求 , 更好的发挥数据库的性能。
  • MySQL支持的存储引擎
    • MySQL5.7支持的引擎包括：InnoDB、MyISAM、MEMORY、Archive、Federate、CSV、BLACKHOLE等
    • 其中较为常用的有三种：InnoDB、MyISAM、MEMORY
2.2常用引擎的特性对比
  • 常用的存储引擎
    • MyISAM存储引擎
      • 访问快,不支持事务和外键。表结构保存在.frm文件中，表数据保存在.MYD文件中，索引保存在.MYI文件中。
    • InnoDB存储引擎(MySQL5.5版本后默认的存储引擎)
      • 支持事务 ,占用磁盘空间大 ,支持并发控制。表结构保存在.frm文件中，如果是共享表空间，数据和索引保存在 innodb_data_home_dir 和 innodb_data_file_path定义的表空间中，可以是多个文件。如果是多表空间存储，每个表的数据和索引单独保存在 .ibd 中。
    • MEMORY存储引擎
      • 内存存储 , 速度快 ,不安全 ,适合小量快速访问的数据。表结构保存在.frm中。
  • 特性对比
特性 MyISAM InnoDB MEMORY
存储限制 有(平台对文件系统大小的限制) 64TB 有(平台的内存限制)
事务安全 不支持 支持 不支持
锁机制 表锁 表锁/行锁 表锁
B+Tree索引 支持 支持 支持
哈希索引 不支持 不支持 支持
全文索引 支持 支持 不支持
集群索引 不支持 支持 不支持
数据索引 不支持 支持 支持
数据缓存 不支持 支持 N/A
索引缓存 支持 支持 N/A
数据可压缩 支持 不支持 不支持
空间使用 低 高 N/A
内存使用 低 高 中等
批量插入速度 高 低 高
外键 不支持 支持 不支持
2.3、引擎的操作
  • 查询数据库支持的引擎
-- 标准语法 SHOW ENGINES; -- 查询数据库支持的存储引擎 SHOW ENGINES;

-- 表含义: - support : 指服务器是否支持该存储引擎 - transactions : 指存储引擎是否支持事务 - XA : 指存储引擎是否支持分布式事务处理 - Savepoints : 指存储引擎是否支持保存点

  • 查询某个数据库中所有数据表的引擎
-- 标准语法 SHOW TABLE STATUS FROM 数据库名称; -- 查看db9数据库所有表的存储引擎 SHOW TABLE STATUS FROM db9;

  • 查询某个数据库中某个数据表的引擎
-- 标准语法 SHOW TABLE STATUS FROM 数据库名称 WHERE NAME = '数据表名称'; -- 查看db9数据库中stu_score表的存储引擎 SHOW TABLE STATUS FROM db9 WHERE NAME = 'stu_score';

  • 创建数据表，指定存储引擎
-- 标准语法 CREATE TABLE 表名( 列名,数据类型, ... )ENGINE = 引擎名称; -- 创建db11数据库 CREATE DATABASE db11; -- 使用db11数据库 USE db11; -- 创建engine_test表，指定存储引擎为MyISAM CREATE TABLE engine_test( id INT PRIMARY KEY AUTO_INCREMENT, NAME VARCHAR(10) )ENGINE = MYISAM; -- 查询engine_test表的引擎 SHOW TABLE STATUS FROM db11 WHERE NAME = 'engine_test';

  • 修改表的存储引擎
-- 标准语法 ALTER TABLE 表名 ENGINE = 引擎名称; -- 修改engine_test表的引擎为InnoDB ALTER TABLE engine_test ENGINE = INNODB; -- 查询engine_test表的引擎 SHOW TABLE STATUS FROM db11 WHERE NAME = 'engine_test';

2.4 总结：引擎的选择
  • MyISAM ：由于MyISAM不支持事务、不支持外键、支持全文检索和表级锁定，读写相互阻塞，读取速度快，节约资源，所以如果应用是以查询操作和插入操作为主，只有很少的更新和删除操作，并且对事务的完整性、并发性要求不是很高，那么选择这个存储引擎是非常合适的。
  • InnoDB : 是MySQL的默认存储引擎， 由于InnoDB支持事务、支持外键、行级锁定 ，支持所有辅助索引(5.5.5后不支持全文检索)，高缓存，所以用于对事务的完整性有比较高的要求，在并发条件下要求数据的一致性，读写频繁的操作，那么InnoDB存储引擎是比较合适的选择，比如BBS、计费系统、充值转账等
  • MEMORY：将所有数据保存在RAM中，在需要快速定位记录和其他类似数据环境下，可以提供更快的访问。MEMORY的缺陷就是对表的大小有限制，太大的表无法缓存在内存中，其次是要确保表的数据可以恢复，数据库异常终止后表中的数据是可以恢复的。MEMORY表通常用于更新不太频繁的小表，用以快速得到访问结果。
  • 总结：针对不同的需求场景，来选择最适合的存储引擎即可！如果不确定、则使用数据库默认的存储引擎！
三、MySQL 索引 3.1、索引概念 1、索引定义
  • MySQL数据库中的索引：是帮助MySQL高效获取数据的一种数据结构！所以，索引的本质就是数据结构。
  • 数据本身之外，数据库还维护着一个满足特定查找算法的数据结构，这些数据结构以某种方式指向数据，这样就可以在这些数据结构的基础上实现高级查找算法，这种数据结构就是索引。下图是一种可能索引方式的示例图：
MySQL|MySQL 学习-进阶
文章图片

  • 一般来说索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储在磁盘上。
2、索引优劣
  • 优势
    • 提高数据检索效率，减低磁盘I/O次数
    • 通过索引列队数据进程排序，降低数据排序成本，降低 CPU 的消耗
  • 劣势
  • 占用磁盘空间
  • 对表进行 INSERT 、UPDATE 和 DELETE 时，MySQL 不仅要保存数据，还要保存索引，降低了 MySQL 效率
  • 文件每次更新添加了索引的字段，都会调整因为更新所带来的键值变化后的所有信息
3、性能下降 分析
  • 语书写问题
  • 索引失效
  • 关联查询太多 join （设计缺陷或不得已的需求）
  • 服务器调优及各个参数设置（缓冲、线程数等）
3.2、索引分类 1、功能分类
  • 普通索引：最基本的索引，它没有任何限制
  • 主键索引：一种特殊的唯一索引，不允许有空值。一般在建表的同时会创建主键索引
  • 唯一索引：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值组合必须唯一
  • 组合索引：顾名思义，就是将单列索引进行组合
  • 全文索引：快熟匹配全部文档的方式，InnoDB 引擎5.6版本后才支持全文索引，MEMORY 引擎不支持
  • 外键索引：只有 InnoDB 引擎支持外键索引，用来保证数据的一致性、完整性和实现级联操作（五大索引不包括它）
2、按数据结构分类
  • B+Tree索引 ：MySQL使用最频繁的一个索引数据结构，是InnoDB和MyISAM存储引擎默认的索引类型。
  • Hash索引 : MySQL中Memory存储引擎默认支持的索引类型。
3.3、索引相关命令
  • 数据准备
-- 创建db12数据库 CREATE DATABASE db12; -- 使用db12数据库 USE db12; -- 创建student表 CREATE TABLE student( id INT PRIMARY KEY AUTO_INCREMENT, NAME VARCHAR(10), age INT, score INT ); -- 添加数据 INSERT INTO student VALUES (NULL,'张三',23,98),(NULL,'李四',24,95), (NULL,'王五',25,96),(NULL,'赵六',26,94),(NULL,'周七',27,99);

  • 创建索引
    • 注意：如果一个表中有一列是主键，那么就会默认为其创建主键索引！(主键列不需要单独创建索引)
-- 标准语法 CREATE [UNIQUE|FULLTEXT] INDEX 索引名称 [USING 索引类型]-- 默认是B+TREE ON 表名(列名...); -- 为student表中姓名列创建一个普通索引 CREATE INDEX idx_name ON student(NAME); -- 为student表中年龄列创建一个唯一索引 CREATE UNIQUE INDEX idx_age ON student(age);

  • 查看索引
-- 标准语法 SHOW INDEX FROM 表名; -- 查看student表中的索引 SHOW INDEX FROM student;

  • alter语句添加索引
-- 普通索引 ALTER TABLE 表名 ADD INDEX 索引名称(列名); -- 组合索引 ALTER TABLE 表名 ADD INDEX 索引名称(列名1,列名2,...); -- 主键索引 ALTER TABLE 表名 ADD PRIMARY KEY(主键列名); -- 外键索引(添加外键约束，就是外键索引) ALTER TABLE 表名 ADD CONSTRAINT 外键名 FOREIGN KEY (本表外键列名) REFERENCES 主表名(主键列名); -- 唯一索引 ALTER TABLE 表名 ADD UNIQUE 索引名称(列名); -- 全文索引(mysql只支持文本类型) ALTER TABLE 表名 ADD FULLTEXT 索引名称(列名); -- 为student表中name列添加全文索引 ALTER TABLE student ADD FULLTEXT idx_fulltext_name(name); -- 查看student表中的索引 SHOW INDEX FROM student;

  • 删除索引
-- 标准语法 DROP INDEX 索引名称 ON 表名; -- 删除student表中的idx_score索引 DROP INDEX idx_score ON student; -- 查看student表中的索引 SHOW INDEX FROM student;

3.4、索引的实现原则
  • 索引是在MySQL的存储引擎中实现的，所以每种存储引擎的索引不一定完全相同，也不是所有的引擎支持所有的索引类型。这里我们主要介绍InnoDB引擎的实现的B+Tree索引。
  • B+Tree是一种树型数据结构，是B-Tree的变种。通常使用在数据库和操作系统中的文件系统，特点是能够保持数据稳定有序。我们逐步的来了解一下。
1、磁盘存储
  • 系统从磁盘读取数据到内存时是以磁盘块（block）为基本单位的
  • 位于同一个磁盘块中的数据会被一次性读取出来，而不是需要什么取什么。
  • InnoDB存储引擎中有页（Page）的概念，页是其磁盘管理的最小单位。InnoDB存储引擎中默认每个页的大小为16KB。
  • InnoDB引擎将若干个地址连接磁盘块，以此来达到页的大小16KB，在查询数据时如果一个页中的每条数据都能有助于定位数据记录的位置，这将会减少磁盘I/O次数，提高查询效率。
2BTree
  • BTree结构的数据可以让系统高效的找到数据所在的磁盘块。为了描述BTree，首先定义一条记录为一个二元组[key, data] ，key为记录的键值，对应表中的主键值，data为一行记录中除主键外的数据。对于不同的记录，key值互不相同。BTree中的每个节点根据实际情况可以包含大量的关键字信息和分支，如下图所示为一个3阶的BTree：
MySQL|MySQL 学习-进阶
文章图片

  • 根据图中结构显示，每个节点占用一个盘块的磁盘空间，一个节点上有两个升序排序的关键字和三个指向子树根节点的指针，指针存储的是子节点所在磁盘块的地址。两个关键词划分成的三个范围域对应三个指针指向的子树的数据的范围域。以根节点为例，关键字为17和35，P1指针指向的子树的数据范围为小于17，P2指针指向的子树的数据范围为17~35，P3指针指向的子树的数据范围为大于35。
查找顺序：
模拟查找15的过程 : 1.根节点找到磁盘块1，读入内存。【磁盘I/O操作第1次】 比较关键字15在区间（<17），找到磁盘块1的指针P1。 2.P1指针找到磁盘块2，读入内存。【磁盘I/O操作第2次】 比较关键字15在区间（>12），找到磁盘块2的指针P3。 3.P3指针找到磁盘块7，读入内存。【磁盘I/O操作第3次】 在磁盘块7中找到关键字15。 -- 分析上面过程，发现需要3次磁盘I/O操作，和3次内存查找操作。 -- 由于内存中的关键字是一个有序表结构，可以利用二分法查找提高效率。而3次磁盘I/O操作是影响整个BTree查找效率的决定因素。BTree使用较少的节点个数，使每次磁盘I/O取到内存的数据都发挥了作用，从而提高了查询效率。

3、B+Tree
  • B+Tree是在BTree基础上的一种优化，使其更适合实现外存储索引结构，InnoDB存储引擎就是用B+Tree实现其索引结构。
  • 从上一节中的BTree结构图中可以看到每个节点中不仅包含数据的key值，还有data值。而每一个页的存储空间是有限的，如果data数据较大时将会导致每个节点（即一个页）能存储的key的数量很小，当存储的数据量很大时同样会导致B-Tree的深度较大，增大查询时的磁盘I/O次数，进而影响查询效率。在B+Tree中，所有数据记录节点都是按照键值大小顺序存放在同一层的叶子节点上，而非叶子节点上只存储key值信息，这样可以大大加大每个节点存储的key值数量，降低B+Tree的高度。
  • B+Tree相对于BTree区别：
    • 非叶子节点只存储键值信息。
    • 所有叶子节点之间都有一个连接指针。
    • 数据记录都存放在叶子节点中。
  • 将上一节中的BTree优化，由于B+Tree的非叶子节点只存储键值信息，假设每个磁盘块能存储4个键值及指针信息，则变成B+Tree后其结构如下图所示：
MySQL|MySQL 学习-进阶
文章图片

通常在B+Tree上有两个头指针，一个指向根节点，另一个指向关键字最小的叶子节点，而且所有叶子节点（即数据节点）之间是一种链式环结构。因此可以对B+Tree进行两种查找运算：
  • 【有范围】对于主键的范围查找和分页查找
  • 【有顺序】从根节点开始，进行随机查找
实际情况中每个节点可能不能填充满，因此在数据库中，B+Tree的高度一般都在24层。MySQL的InnoDB存储引擎在设计时是将根节点常驻内存的，也就是说查找某一键值的行记录时最多只需要13次磁盘I/O操作。
3.5、索引的设计原则 1、哪些情况需要建立索引
  • 主键自动建立唯一索引
  • 频繁作为查询条件的字段应该创建索引
  • 查询中与其它表关联的字段，外键关系建立索引
  • 频繁更新的字段不适合创建索引，因为每次更新不单单是更新了记录，还会更新索引，加重IO负担
  • where条件里用不到的字段不创建索引
  • 单键/组合索引的选择问题，who？（在高并发下倾向创建组合索引）
  • 查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度
  • 查询中统计或者分组字段
2、哪些情况不需要建立索引
  • 表记录太少
  • 经常增删改的表
Why：
  • 提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件。
  • 数据重复且分布平均的表字段，因此应该只为最经常查询和最经常排序的数据列建立索引。注意，如果某个数据列包含许多重复的内容，为它建立索引就没有太大的实际效果。
四、MySQL 性能/索引分析 4.1、Explain 4.1.1、Explain 简介
  • 使用 Explain 关键字可以模拟优化器执行 SQL 查询语句，从而知道 MySQL 是如何处理你的 SQL 语句。
  • 使用方式：Explain + SQL 语句
  • 作用：
    • 表的读取顺序
    • 数据读取操作的操作类型
    • 哪些索引可以被使用
    • 哪些索引被实际使用
    • 表之间的应用
    • 每张表有多少行被优化器查询
4.1.2、Explain 字段详解
MySQL|MySQL 学习-进阶
文章图片

1、id
select 查询的序列号，包含一组数字，表示查询中执行 select 子句或操作表的顺序
  • id 相同，执行顺序由上至下
  • id 不同，如果是子查询，id 的序号会递增，id 值越大优先级越高，越先被执行
  • id 相同不同，同时存在
2、select_type
  • 种类
MySQL|MySQL 学习-进阶
文章图片

  • 查询类型：主要是用于区别普通查询、联合查询、子查询等的复杂查询
  • SIMPLE：简单的 select 查询，查询中不包含子查询或 UNION
  • PRIMARY：查询中包含任何复杂的子部分，最外层查询则被标记为 PRIMARY
  • SUBQUERY：在 FROM 列表中包含的子查询被标记为 DERIVED（衍生），MySQL 会递归执行这些子查询，把结果放在临时表里
  • DERIVED：在FROM列表中包含的子查询被标记为DERIVED（衍生）。MySQL会递归执行这些子查询，把结果放在临时表里。
  • UNION：若第二个SELECT出现在UNION之后，则被标记为UNION；若UNION包含在FROM子句的子查询中，外层SELECT将被标记为：DERIVED。
  • UNION RESULT：从UNION表中获取结果的SELECT。
3、table
  • 显示这一行数据是来自哪张表
4、type
  • 类型
MySQL|MySQL 学习-进阶
文章图片

  • 访问类型排序
    • type显示的是访问类型，是较为重要的一个指标，结果值从最好到最坏依次是：
      system>const>eq_ref>ref>fulltext>ref_or_null>index_merge>unique_subquery>index_subquery>range>index>All
    • 显示查询使用了何种类型，从最好到最差依此是：
      system>const>eq_ref>ref>range>index>All
    • system：表只有一行记录（等于系统表），这是const类型的特例，平时不会出现，这个也可以忽略不计。
    • const：表示通过索引一次就找到了，const用于比较primary key或则unique索引。因为只匹配一行数据，所以很快。如将主键置于where列表中，MySQL就能将该查询转换为一个常量。
    • eq_ref：唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描。
    • ref：非唯一性索引扫描，返回匹配某个单独值的所有行。本质上也是一种索引访问，它返回所有匹配某个单独值的行，然而，它可能会找到多个符合条件的行，所以它应该属于查找和扫描的混合体。
    • range：只检索给定范围的行，使用一个索引来选择行。key列显示使用了哪个索引。一般就是在你的where语句中出现了between、<、>、in等的查询。这种范围扫描索引扫描比全表扫描要好，因为它只需要开始于索引的某一点，而结束于另一点，不会扫描全部索引。
    • index：Full Index Scan，index与All区别为index类型只遍历索引树。这通常比All快，因为索引文件通常比数据文件小。（也就是说虽然all和index都是读全表，但index是从索引中读取的，而all是从硬盘中读的）
    • all：Full Table Scan，将遍历全表以找到匹配的行。
    • 一般来说，得保证查询至少达到range级别，最好能达到ref。
5、possible_keys
  • 显示可能应用在这张表中的索引，一个或多个。查询涉及到的字段上若存在索引，则该索引将被列出。但不一定被查询实际使用。
6、key
  • 实际使用的索引。如果为NULL，则没有使用索引。查询中若使用了覆盖索引，则该索引仅出现在key列表中，不会出现在possible_keys列表中。（覆盖索引：查询的字段与建立的复合索引的个数一一吻合）
7、key_len
  • 表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度。在不损失精确性的情况下，长度越短越好。key_len显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出的。
8、ref
  • 显示索引的哪一列被使用了，如果可能的话，是一个常数。哪些列或常量被用于查找索引列上的值。查询中与其它表关联的字段，外键关系建立索引。
9、rows
  • 据表统计信息及索引选用情况，大致估算出找到所需的记录所需要读取的行数。
10、Extra
  • 包含不适合在其他列中显示但十分重要的额外信息。
    • Using filesort：说明mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。MySQL中无法利用索引完成的排序操作成为“文件排序”。
    • Using temporary：使用了临时表保存中间结果，MySQL在对查询结果排序时使用临时表。常见于排序order by和分组查询group by。
    • Using index：表示相应的select操作中使用了覆盖索引（Covering Index），避免访问了表的数据行，效率不错！如果同时出现using where，表明索引被用来执行索引键值的查找；如果没有同时出现using where，表明索引用来读取数据而非执行查找动作。
      覆盖索引：
    MySQL|MySQL 学习-进阶
    文章图片

    • Using where：表明使用了where过滤。
    • Using join buffer：使用了连接缓存。
    • impossible where：where子句的值总是false，不能用来获取任何元组。（查询语句中where的条件不可能被满足，恒为False）
    • select tables optimized away：在没有GROUPBY子句的情况下，基于索引优化MIN/MAX操作或者对于MyISAM存储引擎优化COUNT(*)操作，不必等到执行阶段再进行计算，查询执行计划生成的阶段即完成优化。
    • distinct：优化distinct操作，在找到第一匹配的元组后即停止找相同值的动作。
4.2、索引优化 1、Join 语法优化
  • 尽可能减少 Join 语句中的 NestedLoop 的循环总次数，“永远用小结果集驱动大”
  • 优先优化 NestedLoop 的内层循环
  • 保证 Join 语句中被驱动表上 Join 条件字段已经被索引
  • 当无法保证被驱动表的 Join 条件字段被索引且内存资源充足的前提下，可以用 JoinBuffer 的设置
2、索引失效（应该避免）
  • 全值匹配我最爱：建立的索引按照固有的顺序全部用
  • 最佳左前缀法则：如果多列建立了组合索引 ，要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列
  • 不在索引列上做任何操作（计算、函数、类型转换），会导致索引失效而转向全表扫描
  • 存储引擎不能使用索引中范围条件右边的列：就是组合索引中，中间的索引用了范围判断，其右边的索引会失效
  • 尽量使用覆盖索引（只访问索引的查询（索引列和查询列一致）），避免使用 select *
  • MySQL 在使用不等于（！= 或 <>）的时候无法使用索引，会导致全表扫描
  • is null，is not null 也会导致索引无法使用
  • like 以通配符开头（%a）MySQL 索引失效，导致变成全表扫描
    • like % 加 右边
    • 利用覆盖索引解决两边%的优化问题
  • 字符串不加单引号索引失效（在列上做了类型转换）
  • VARCHAR类型绝对不能失去单引号
  • 少用or，用它来连接时会索引失效
MySQL|MySQL 学习-进阶
文章图片

优化总结口诀：
全值匹配我最爱，最左前缀要遵守；
带头大哥不能死，中间兄弟不能断；
索引列上少计算，范围之后全失效；
LIKE百分写最右，覆盖索引不写星；
【MySQL|MySQL 学习-进阶】不等空值还有or，索引失效要少用；
VAR引号不可丢，SQL高级也不难！
五、MySQL 锁 5.1、MySQL 锁概念 1、锁机制
  • 锁机制 : 数据库为了保证数据的一致性，而使用各种共享的资源在被并发访问时变得有序所设计的一种规则。
  • 锁是计算机协调多个进程并发访问某一资源的机制。
2、锁分类
  • 按操作分类：
    • 共享锁：也叫读锁。针对同一份数据，多个事务读取操作可以同时加锁而不互相影响 ，但是不能修改数据记录。
    • 排他锁：也叫写锁。当前的操作没有完成前,会阻断其他操作的读取和写入
  • 按粒度分类：
    • 表级锁：操作时，会锁定整个表。开销小，加锁快；不会出现死锁；锁定力度大，发生锁冲突概率高，并发度最低。偏向于MyISAM存储引擎！
    • 行级锁：操作时，会锁定当前操作行。开销大，加锁慢；会出现死锁；锁定粒度小，发生锁冲突的概率低，并发度高。偏向于InnoDB存储引擎！
    • 页级锁：锁的粒度、发生冲突的概率和加锁的开销介于表锁和行锁之间，会出现死锁，并发性能一般。
  • 按使用方式分类：
    • 悲观锁：每次查询数据时都认为别人会修改，很悲观，所以查询时加锁。
    • 乐观锁：每次查询数据时都认为别人不会修改，很乐观，但是更新时会判断一下在此期间别人有没有去更新这个数据
  • 不同存储引擎支持的锁
存储引擎 表级锁 行级锁 页级锁
MyISAM 支持 不支持 不支持
InnoDB 支持 支持 不支持
MEMORY 支持 不支持 不支持
BDB 支持 不支持 支持
5.2、InnoDB 锁（行锁）
  • 数据准备
-- 创建db13数据库 CREATE DATABASE db13; -- 使用db13数据库 USE db13; -- 创建student表 CREATE TABLE student( id INT PRIMARY KEY AUTO_INCREMENT, NAME VARCHAR(10), age INT, score INT ); -- 添加数据 INSERT INTO student VALUES (NULL,'张三',23,99),(NULL,'李四',24,95), (NULL,'王五',25,98),(NULL,'赵六',26,97);

  • 共享锁
-- 标准语法 SELECT语句 LOCK IN SHARE MODE;

-- 窗口1 /* 共享锁：数据可以被多个事务查询，但是不能修改 */ -- 开启事务 START TRANSACTION; -- 查询id为1的数据记录。加入共享锁 SELECT * FROM student WHERE id=1 LOCK IN SHARE MODE; -- 查询分数为99分的数据记录。加入共享锁 SELECT * FROM student WHERE score=99 LOCK IN SHARE MODE; -- 提交事务 COMMIT;

-- 窗口2 -- 开启事务 START TRANSACTION; -- 查询id为1的数据记录(普通查询，可以查询) SELECT * FROM student WHERE id=1; -- 查询id为1的数据记录，并加入共享锁(可以查询。共享锁和共享锁兼容) SELECT * FROM student WHERE id=1 LOCK IN SHARE MODE; -- 修改id为1的姓名为张三三(不能修改，会出现锁的情况。只有窗口1提交事务后，才能修改成功) UPDATE student SET NAME='张三三' WHERE id = 1; -- 修改id为2的姓名为李四四(修改成功，InnoDB引擎默认是行锁) UPDATE student SET NAME='李四四' WHERE id = 2; -- 修改id为3的姓名为王五五(注意：InnoDB引擎如果不采用带索引的列。则会提升为表锁) UPDATE student SET NAME='王五五' WHERE id = 3; -- 提交事务 COMMIT;

  • 排他锁
-- 标准语法 SELECT语句 FOR UPDATE;

-- 窗口1 /* 排他锁：加锁的数据，不能被其他事务加锁查询或修改 */ -- 开启事务 START TRANSACTION; -- 查询id为1的数据记录，并加入排他锁 SELECT * FROM student WHERE id=1 FOR UPDATE; -- 提交事务 COMMIT;

-- 窗口2 -- 开启事务 START TRANSACTION; -- 查询id为1的数据记录(普通查询没问题) SELECT * FROM student WHERE id=1; -- 查询id为1的数据记录，并加入共享锁(不能查询。因为排他锁不能和其他锁共存) SELECT * FROM student WHERE id=1 LOCK IN SHARE MODE; -- 查询id为1的数据记录，并加入排他锁(不能查询。因为排他锁不能和其他锁共存) SELECT * FROM student WHERE id=1 FOR UPDATE; -- 修改id为1的姓名为张三(不能修改，会出现锁的情况。只有窗口1提交事务后，才能修改成功) UPDATE student SET NAME='张三' WHERE id=1; -- 提交事务 COMMIT;

  • 注意：锁的兼容性
    • 共享锁和共享锁 兼容
    • 共享锁和排他锁 冲突
    • 排他锁和排他锁 冲突
    • 排他锁和共享锁 冲突
    • InnoDB引擎如果不采用带索引的列。则会提升为表锁。
    • 间隙锁：当我们用范围条件而不是相等条件索引数据，并请求共享或排他锁时，InnoDB会给符合条件的已有数据记录的索引项加锁；对于键值在条件范围内但并不存在的记录，叫做“间隙（GAP）”。InnoDB也会对这个“间隙”加锁，这种锁机制就是所谓的间隙锁（Next-Key锁）。
    • 危害：
      • 因为Query执行过程中通过范围查找的话，会锁定整个范围内所有的索引键值，即使这个键值并不存在。
      • 间隙锁有一个比较致命的弱点，就是当锁定一个范围键值之后，即使某些不存在的键值也会被无辜的锁定，而造成在锁定的时候无法插入锁定键值范围内的任何数据。在某些场景下这可能会对性能造成很大的危害。
  • 总结：
    • 添加共享锁：SELECT 语句 LOCK IN SHARE MODE;
    • 添加排他锁：SELECT 语法 FOR UPDATE；
5.3、MyISAM 锁（表锁）
  • 数据准备
-- 创建product表 CREATE TABLE product( id INT PRIMARY KEY AUTO_INCREMENT, NAME VARCHAR(20), price INT )ENGINE = MYISAM; -- 指定存储引擎为MyISAM-- 添加数据 INSERT INTO product VALUES (NULL,'华为手机',4999),(NULL,'小米手机',2999), (NULL,'苹果',8999),(NULL,'中兴',1999);

  • 读锁
-- 标准语法 -- 加锁 LOCK TABLE 表名 READ; -- 解锁(将当前会话所有的表进行解锁) UNLOCK TABLES;

-- 窗口1 /* 读锁：所有连接只能读取数据，不能修改 */ -- 为product表加入读锁 LOCK TABLE product READ; -- 查询product表(查询成功) SELECT * FROM product; -- 修改华为手机的价格为5999(修改失败) UPDATE product SET price=5999 WHERE id=1; -- 解锁 UNLOCK TABLES;

-- 窗口2 -- 查询product表(查询成功) SELECT * FROM product; -- 修改华为手机的价格为5999(不能修改，窗口1解锁后才能修改成功) UPDATE product SET price=5999 WHERE id=1;

  • 写锁
-- 标准语法 -- 加锁 LOCK TABLE 表名 WRITE; -- 解锁(将当前会话所有的表进行解锁) UNLOCK TABLES;

-- 窗口1 /* 写锁：其他连接不能查询和修改数据 */ -- 为product表添加写锁 LOCK TABLE product WRITE; -- 查询product表(查询成功) SELECT * FROM product; -- 修改小米手机的金额为3999(修改成功) UPDATE product SET price=3999 WHERE id=2; -- 解锁 UNLOCK TABLES;

-- 窗口2 -- 查询product表(不能查询。只有窗口1解锁后才能查询成功) SELECT * FROM product; -- 修改小米手机的金额为2999(不能修改。只有窗口1解锁后才能修改成功) UPDATE product SET price=2999 WHERE id=2;

  • 总结
    • 添加读锁：LOCK TABLE 表名 READ;
    • 添加写锁：LOCK TABLE 表名 WRITE；
    • 解锁：UNLOCK TABLES;
5.4、锁总结
  • 表锁和行锁
    • 行锁：锁的粒度更细，加行锁的性能损耗较大。并发处理能力较高。InnoDB引擎默认支持！
    • 表锁：锁的粒度较粗，加表锁的性能损耗较小。并发处理能力较低。InnoDB、MyISAM引擎支持！
  • InnoDB锁优化建议
    • 尽量通过带索引的列来完成数据查询，从而避免InnoDB无法加行锁而升级为表锁。
    • 合理设计索引，索引要尽可能准确，尽可能的缩小锁定范围，避免造成不必要的锁定。
    • 尽可能减少基于范围的数据检索过滤条件。
    • 尽量控制事务的大小，减少锁定的资源量和锁定时间长度。
    • 在同一个事务中，尽可能做到一次锁定所需要的所有资源，减少死锁产生概率。
    • 对于非常容易产生死锁的业务部分，可以尝试使用升级锁定颗粒度，通过表级锁定来减少死锁的产生。
六、MySQL 集群 6.1、MySQL 主从复制 1、复制的基本原理
  • slave会从master读取binlog来进行数据同步。
  • MySQL 复制过程分为三步：
    • master 将改变记录到二进制日志（binary log），这些记录过程叫做二进制日志事件，binary log events;
    • slave 将 master 的binary log events 拷贝到它的中继日志（relay log）；
    • slave 重做中继日志中的事件，将改变应用到自己的数据库中，MySQL 复制是异步的且串行话的。
2、复制的基本原则
  • 每个slave只有一个master
  • 每个slave只能有一个唯一的服务器ID
  • 每个master可以有多个slave
3、复制的最大问题
  • 延时
4、配置
  • 主服务器的配置
    • 在第一个服务器上，编辑mysql配置文件
    // 编辑mysql配置文件 vi /etc/my.cnf//在[mysqld]下面加上： log-bin=mysql-bin # 开启复制操作 server-id=1 # master is 1 innodb_flush_log_at_trx_commit=1 sync_binlog=1

    • 登录mysql，创建用户并授权
    // 登录mysql mysql -u root -p// 去除密码权限 SET GLOBAL validate_password_policy=0; SET GLOBAL validate_password_length=1; // 创建用户 CREATE USER 'hm'@'%' IDENTIFIED BY 'itheima'; // 授权 GRANT ALL ON *.* TO 'hm'@'%';

    • 重启mysql服务，登录mysql服务
    // 重启mysql service mysqld restart// 登录mysql mysql -u root -p

    • 查看主服务器的配置
    // 查看主服务器配置 show master status;

    MySQL|MySQL 学习-进阶
    文章图片

  • 从服务器的配置
    • 在第二个服务器上，编辑mysql配置文件
    // 编辑mysql配置文件 vi /etc/my.cnf// 在[mysqld]下面加上： server-id=2

    • 登录mysql
    // 登录mysql mysql -u root -p// 执行 use mysql; drop table slave_master_info; drop table slave_relay_log_info; drop table slave_worker_info; drop table innodb_index_stats; drop table innodb_table_stats; source /usr/share/mysql/mysql_system_tables.sql;

    • 重启mysql，重新登录，配置从节点
    // 重启mysql service mysqld restart// 重新登录mysql mysql -u root -p// 执行 change master to master_host='主服务器ip地址',master_port=3306,master_user='hm',master_password='itheima',master_log_file='mysql-bin.000001',master_log_pos=4642;

    • 重启mysql，重新登录，开启从节点
    // 重启mysql service mysqld restart// 重新登录mysql mysql -u root -p// 开启从节点 start slave; // 查询结果 show slave status\G; //Slave_IO_Running和Slave_SQL_Running都为yes才表示同步成功。

    MySQL|MySQL 学习-进阶
    文章图片

  • 测试
    • sqlyog连接主服务器
    -- 主服务器创建db1数据库,从服务器会自动同步 CREATE DATABASE db1;

    • sqlyog连接从服务器
    -- 从服务器创建db2数据库,主服务器不会自动同步 CREATE DATABASE db2;

  • 启动失败的解决方案
启动失败：Slave_IO_Running为 NO 方法一:重置slave slave stop; reset slave; start slave ; 方法二:重设同步日志文件及读取位置 slave stop; change master to master_log_file=’mysql-bin.000001’, master_log_pos=1; start slave ;

6.2、MySQL 读写分离
  • 读写分离的概念
    • 写操作只写入主服务器，读操作读取从服务器。
  • 在主服务器上修改server.xml
    • user标签主要用于定义登录mycat的用户和权限。如上面定义用户名mycat和密码123456，该用户可以访问的schema的HEIMADB逻辑库。
123456HEIMADB

  • 在主服务器上修改schema.xml
select user()

  • 配置详解
    • schema标签逻辑库的概念和mysql数据库中Datebase的概念相同，我们在查询这两个逻辑库中的表的时候，需要切换到该逻辑库下才可以查到所需要的表。
    • dataNode属性：该属性用于绑定逻辑库到某个具体的database上。
    • dataNode标签： dataNode标签定义了mycat中的数据节点，也就是数据分片。一个dataNode标签就是一个独立的数据分片。
    • name属性：定义数据节点的名字，这个名字需要是唯一的，我们需要在table标签上应用这个名字，来建立表与分片对应的关系。
    • dataHost属性：该属性用于定义该分片属于那个数据库实例，属性值是引用datahost标签定义的name属性。
    • database属性：该属性用于定义该分片属于那个具体数据库实例上的具体库，因为这里使用两个纬度来定义分片，就是：实例+具体的库。因为每个库上建立的表和表结构是一样的。所以这样做就可以轻松的对表进行水平拆分。
    • dataHost标签：该标签在mycat逻辑库中也是作为最底层的标签存在，直接定义了具体的数据库实例、读写分离配置和心跳语句。
    • balance属性： 负载均衡类型
      ? balance=0: 不开启读写分离，所有读操作都发送到当前可用的writeHost上。
      ? balance=1: 全部的readHost与Stand by writeHost都参与select语句的负载均衡
      ? balance=2: 所有的读操作都随机在writeHost，readHost上分发。
      ? balance=3: 所有的读请求都随机分配到writeHost对应的readHost上执行，writeHost不负担读压力。
    • switchType属性：
      ? -1：表示不自动切换。
      ? 1 ：默认值，表示自动切换
      ? 2：表示基于MySQL主从同步状态决定是否切换，心跳语句： show slave status.
      ? 3:表示基于mysql galary cluster的切换机制，适合mycat1.4之上的版本，心跳语句show status like "%esrep%";
    • writeHost标签，readHost标签：这两个标签指定后端数据库的相关配置给mycat，用于实例化后端连接池。唯一不同的是，writeHost指定写实例、readHost指定读实例，组合这些读写实例来满足系统的要求。
      • host属性：用于标识不同的实例，对于writehost，一般使用M1；对于readhost一般使用S1.
      • url属性：后端实例连接地址，如果使用native的dbDriver，则一般为address:port这种形式，用JDBC或其他的dbDriver，则需要特殊指定。当使用JDBC时则可以这么写：jdbc:mysql://localhost:3306/。
      • user属性：后端存储实例的用户名。
      • password属性：后端存储实例的密码
  • 测试
    • 重启主服务器的mycat
    // 重启mycat cd /root/mycat/bin./mycat restart// 查看端口监听 netstat -ant|grep 8066

    • sqlyog连接mycat
    -- 创建学生表 CREATE TABLE student( id INT PRIMARY KEY AUTO_INCREMENT, NAME VARCHAR(10) ); -- 查询学生表 SELECT * FROM student; -- 添加两条记录 INSERT INTO student VALUES (NULL,'张三'),(NULL,'李四'); -- 停止主从复制后，添加的数据只会保存到主服务器上。 INSERT INTO student VALUES (NULL,'王五');

    • sqlyog连接主服务器
    -- 主服务器：查询学生表，可以看到数据 SELECT * FROM student;

    • sqlyog连接从服务器
    -- 从服务器：查询学生表，可以看到数据(因为有主从复制) SELECT * FROM student; -- 从服务器：删除一条记录。(主服务器并没有删除，mycat中间件查询的结果是从服务器的数据) DELETE FROM student WHERE id=2;

6.3 MySQL 分库分表