1.MySQL架构组成
-
高级DBA的职责:
- 负责MySQL的容量规划，架构设计及安装、部署.
- 负责MySQL的日常管理，监控和维护, 并对MySQL进行持续性能优化.
- 负责MySQL开发支持，参与数据架构规划设计，以及相关业务的数据建模、设计评审、SQL代码审核优化
-
中级 Java开发工程师对数据库知识的掌握程度
- 熟练操作主流数据库,能够通过代码(框架) 完成日常的数据库操作.
- 熟练使用SQL, 熟悉SQL优化, 熟悉存储过程 视图 等创建及使用.
- 了解MySQL的整体体系结构，了解MySQL事务 存储引擎的特点
- 了解MySQL索引优化,了解MySQL相关锁机
- MySQL架构体系介绍：MySQL 由连接池、SQL 接口、解析器、优化器、缓存、存储引擎等组成，可以分为四层，即连接层、 服务层、引擎层和文件系统层。可插拔式
-
- 连接层：最上面是一些客户端和连接服务, 不是MySQL特有的，所有基于网络的C/S的网络应用程序都应该包括连接处理、认证、安全管理等
- 服务层：中间层是MySQL的核心，包括查询解析、分析、优化和缓存等。同时它还提供跨存储引擎的功能，包括存储过程、触发器和视图等。
- 引擎层：存储引擎层，它负责存取数据。服务器通过API可以和各种存储引擎进行交互。不同的存储引擎具有不同的功能,我们可以根据实际需求选择使用对应的存储引擎
- 存储层：数据存储层,主要是将数据存储在运行于裸设备的文件系统之上,并完成与存储引擎的交互
- 连接层：最上面是一些客户端和连接服务, 不是MySQL特有的，所有基于网络的C/S的网络应用程序都应该包括连接处理、认证、安全管理等
- SQL查询流程：用 SQL SELECT 语句的执行轨迹来说明客户端与 MySQL 的交互过程，如下图所示
1. 通过客户端/服务器通信协议与 MySQL 建立连接 2. 查询缓存，这是 MySQL 的一个可优化查询的地方，如果开启了 Query Cache 且在查询缓存过程中查询到完全相同的 SQL 语句，则将查询结果直接返回给客户端；如果没有开启Query Cache 或者没有查询到 完全相同的 SQL 语句则会由解析器进行语法语义解析，并生成解析树。 3. 预处理器生成新的解析树。 4. 查询优化器生成执行计划。 5. 查询执行引擎执行 SQL 语句，此时查询执行引擎会根据 SQL 语句中表的存储引擎类型，以及对应的API 接口与底层存储引擎缓存或者物理文件的交互情况，得到查询结果，由MySQL Server 过滤后将查询结 果缓存并返回给客户端。若开启了 Query Cache，这时也会将SQL 语句和结果完整地保存到 QueryCache 中，以后若有相同的 SQL 语句执行则直接返回结果。
- mysql物理文件
- 日志文件
- error log 错误日志，用于排错， 存放在/var/log/mysqld.log【默认开启】
- bin log 二进制日志 ，用于备份，增量备份 DDL DML DCL
- Relay log 中继日志，用于主从复制，接收 replication master
- slow log 慢查询日志，用于性能调优， 查询时间超过指定值
- 日志文件
-- 查看错误日志文件路径 show variables like 'log_error'; +---------------+---------------------+ | Variable_name | Value | +---------------+---------------------+ | log_error | /var/log/mysqld.log | +---------------+---------------------+ -- 慢查询日志文件路径 show variables like 'slow_query_log_file'; +---------------------+-----------------------------------+ | Variable_name | Value | +---------------------+-----------------------------------+ | slow_query_log_file | /var/lib/mysql/localhost-slow.log | +---------------------+-----------------------------------+ -- bin log 日志文件 需要在 my.cnf 中配置 log-bin=/var/log/mysql-bin/bin.log server-id=2 -- 查看 relay log 相关参数 show variables like '%relay%';
-
- 配置文件my.cnf进行数据库客户端服务端等高级配置
[client] #客户端设置，即客户端默认的连接参数 port = 3307 #默认连接端口 socket = /data/mysqldata/3307/mysql.sock #用于本地连接的socket套接字 default-character-set = utf8mb4 #编码 [mysqld] #服务端基本设置 port = 3307 MySQL监听端口 socket = /data/mysqldata/3307/mysql.sock #为MySQL客户端程序和服务器之间的本地通讯指定一 个套接字文件 pid-file = /data/mysqldata/3307/mysql.pid #pid文件所在目录 basedir = /usr/local/mysql-5.7.11 #使用该目录作为根目录（安装目录） datadir = /data/mysqldata/3307/data #数据文件存放的目录 tmpdir = /data/mysqldata/3307/tmp #MySQL存放临时文件的目录 character_set_server = utf8mb4 #服务端默认编码（数据库级别）
- 数据文件
-- 查看数据文件的位置 show variables like '%dir%'; +-----------------------------------------+----------------------------+ | Variable_name | Value | +-----------------------------------------+----------------------------+ | datadir | /var/lib/mysql/ | +-----------------------------------------+----------------------------+ 1、.frm文件 不论是什么存储引擎，每一个表都会有一个以表名命名的.frm文件，与表相关的元数据（meta)信息都存放在此文件中，包括表结构的定义信息等。 2、.MYD文件 myisam存储引擎专用，存放myisam表的数据（data)。每一个myisam表都会有一个.MYD文件与之呼应，同样存放在所属数据库的目录下 3、.MYI文件 也是myisam存储引擎专用，存放myisam表的索引相关信息。每一个myisam表对应一个.MYI文件，其存放的位置和.frm及.MYD一样 4、.ibd文件 存放innoDB的数据文件（包括索引）。 5. db.opt文件 此文件在每一个自建的库里都会有，记录这个库的默认使用的字符集和校验规则
- 配置文件my.cnf进行数据库客户端服务端等高级配置
-
数据丢失与备份场景
- 开发测试环境数据库搭建
- 数据库或者数据迁移
- 数据备份方式
- 完全备份：将数据库的全部信息进行备份，包括数据库的数据文件、日志文件，还需要备份文件的存储位置以及数据库中的全部对象和相关信息。
- 差异备份：备份从最近的完全备份后对数据所做的修改，备份完全备份后变化了的数据文件、日志文件以及数据库中其他被修改的内容
- 增量备份：增量备份是指在一次全备份或上一次增量备份后，以后每次的备份只需备份与前一次相比增加或者被修改的文件。
| 完整备份 | 差异备份 | 增量备份 | ||
| 备 份 方 法 | 备份所有文件 |
一次全备份后,备份与全 备份差异的部分 |
一次全备份后,备份与上次备 份的差异部分 |
|
| 备 份 速 度 | 最慢 | 较快 | 最快 | |
| 恢 复 速 度 | 最快 | 较快 | 最慢 | |
| 空 间 要 求 | 最多 | 较多 | 最少 | |
| 优 势 |
最快的恢复速度, 只需要上 一次完全备份就能恢复 |
相比增量,更快也更简单 并且只需要最近一次的 完全备份 和最后一次的差异备份 就能恢复 |
备份速度快,较少的空间需求,没有重复的备份文件 | |
| 劣 势 | 最多的空间需求 大量重复的备份 |
较慢的备份速度,仍然会 存在许多的备份文件 |
最慢的恢复速度，恢复需要最近一次完全备份和全部增量备份 |
- 备份的组合方式：完全备份+差异备份；完全备份+增量备份
- 冷备份：当数据库进行备份时, 数据库不能进行读写操作, 即数据库要下线
- 是操作比较方便的备份方法（只需拷贝文件），低度维护，高度安全
- 在实施备份的全过程中，数据库必须要作备份而不能作其它工作。
- 若磁盘空间有限，只能拷贝到磁带等其它外部存储设备上，速度比较慢。
-
不能按表或按用户恢复
- 实现：
1. 关闭SELinux，修改 selinux 配置文件,将SELINUX=enforcing改为SELINUX=disabled，保存后退出 vim /etc/selinux/config SELINUX=disabled 2. 重启 reboot # 重启命令 3. 找到MySQL数据文件位置,停止MySQL服务 SHOW VARIABLES LIKE '%dir%'; -- 结果显示, 数据目录就是datadir的所在位置，即 /var/lib/mysql/ service mysqld stop -- 停止mysql 4.进入到 /mysql 目录, 执行打包命令 将数据文件打包备份 cd /var/lib/ # 进入其上级目录 tar jcvf /root/backup.tar.bz2 mysql/ # 打包压缩到 root目录下 5. 删除掉数据目录下的所有数据 -- 删除原目录 rm -rf /var/lib/mysql/ 6. 恢复数据 (使用tar命令) -- 解压 tar jxvf backup.tar.bz2 mysql/ -- 把备份的文件移动到/var/lib/里面去替代原来的mysql mv /root/mysql/ /var/lib/ 7. 启动MySQL, 然后登陆MySQL,查看数据是否丢失, 如果数据正常代表冷备成功 service mysqld start
- 热备份：在数据库运行的情况下，备份数据库操作的sql语句，当数据库发生问题时，可以重新执行一遍备份的sql语句
- 可在表空间或数据文件级备份，备份时间短。备份时数据库仍可使用。可达到秒级恢复（恢复到某一时间点上）
- 不能出错，否则后果严重。因难维护，所以要特别仔细小心，不允许“以失败而告终”
-
- mysqldump工具实现热备份，可以对多个库进行备份，可以对单张表或者某几张表进行备份
## 备份单个数据库 1. 创建文件夹 , 备份数据 mkdir databackup cd databackup mysqldump -uroot -p lagou_edu > lagou_edu.sql 2.模拟数据丢失，删除数据库,然后重新创建一个新的库 DROP DATABASE lagou_edu; CREATE DATABASE lagou_edu CHARACTER SET 'utf8'; 3.恢复数据 cd databackup mysql -uroot -p lagou_edu < lagou_edu.sql ## 备份数据库的某些表 1. 备份数据表 [root@localhost databackup]# mysqldump -uroot -p lagou_edu course course_lesson > backupTable.sql 2. 模拟数据丢失，删除数据表 DROP TABLE course; DROP TABLE course_lesson; 3. 恢复数据 mysql -uroot -p lagou_edu < backupTable.sql ##直接将MySQL数据库压缩备份 1. 备份数据 mysqldump -uroot -p lagou_edu | gzip > lagou_edu.sql.gz 2. 模拟删除 DROP DATABASE lagou_edu; CREATE DATABASE lagou_edu CHARACTER SET 'utf8'; 3 .恢复数据 gunzip < lagou_edu.sql.gz | mysql -uroot -p lagou_edu
3.MySQL查询和慢查询日志分析
-
sql 性能下降原因
- 等待时间长：锁表导致查询一直处于等待状态
- 执行时间长：查询语句烂、索引失效、关联查询太多join、服务器调优及各个参数设置问题
-
优化原则
- 只返回需要的结果：一定要为查询语句指定 WHERE 条件，过滤掉不需要的数据行；避免使用 select * from , 因为它表示查询表中的所有字段
- 确保查询使用了正确的索引：经常出现在 WHERE 条件中的字段建立索引,可以避免全表扫描；将 ORDER BY 排序的字段加入到索引中，可以避免额外的排序操作；多表连接查询的关联字段建立索引，可以提高连接查询的性能；将 GROUP BY 分组操作字段加入到索引中，可以利用索引完成分组
- 避免让索引失效：在 WHERE 子句中对索引字段进行表达式运算或者使用函数都会导致索引失效；使用 LIKE 匹配时，如果通配符出现在左侧无法使用索引；如果 WHERE 条件中的字段上创建了索引，尽量设置为 NOT NULL
- sql执行顺序
1. FORM子句 : 左右两个表的笛卡尔积 2. ON: 筛选满足条件的数据 3. JOIN： 如果是 inner join 那就正常,如果是 outer join 则会添加回来上面一步过滤掉的一些行 4. WHERE： 对不满足条件的行进行移除, 并且不能恢复 5. GROUP BY: 分组后只能得到每组的第一行数据,或者聚合函数的数值 6. HAVING： 对分组后的数据进行筛选 7. SELECT： 执行select操作，获取需要的列。 8. DISTINCT： 去重 9. ORDER BY: 排序 10. LIMIT：取出指定行的记录, 并将结果返回。
- join 查询的7种方式：内连接、左右连接、全连接
- 慢查询日志分析
- MySQL的慢查询，全名是慢查询日志，是MySQL提供的一种日志记录，用来记录在MySQL中响应时间超过阈值的语句。
- 默认情况下，MySQL数据库并不启动慢查询日志，需要手动来设置这个参数。
- 如果不是调优需要的话，一般不建议启动该参数，因为开启慢查询日志会或多或少带来一定的性能影响。
- 慢查询日志支持将日志记录写入文件和数据库表
- 慢查询参数
SHOW VARIABLES LIKE "%query%"； ## slow_query_log：是否开启慢查询日志， 1 表示开启， 0 表示关闭。 ## slow-query-log-file：新版（5.6及以上版本）MySQL数据库慢查询日志存储路径 ## long_query_time： 慢查询阈值，当查询时间多于设定的阈值时，记录日志 SHOW VARIABLES LIKE "%output%"； ## log_output 日志文件放到文件里还是表里
- 配置慢查询
- 默认慢查询方式是禁用的，可以通过set方式开启；设置后只对当前数据库生效，mysql重启后会失效
SHOW VARIABLES LIKE "%query%"； set global slow_query_log=1; - 修改my.cnf文件永久生效
-- 编辑配置 vim /etc/my.cnf -- 添加如下内容 slow_query_log =1 slow_query_log_file=/var/lib/mysql/lagou-slow.log -- 重启MySQL service mysqld restart mysql> SHOW VARIABLES LIKE '%slow_query_log%'; +---------------------+-------------------------------+ | Variable_name | Value | +---------------------+-------------------------------+ | slow_query_log | ON | | slow_query_log_file | /var/lib/mysql/lagou-slow.log | +---------------------+-------------------------------+
- 设置慢查询的时间：show variables like 'long_query_time';set global long_query_time=1; show variables like 'long_query_time';使用命令 set global long_query_time=1 修改后，需要重新连接或新开一个会话才能看到修改值
-
log_output 参数是指定日志的存储方式。 log_output='FILE' 表示将日志存入文件，默认值是'FILE'。 log_output='TABLE' 表示将日志存入数据库，这样日志信息就会被写入到mysql.slow_log 表中
mysql> SHOW VARIABLES LIKE '%log_output%'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | log_output | FILE | +---------------+-------+ MySQL数据库支持同时两种日志存储方式，配置的时候以逗号隔开即可，如：log_output='FILE,TABLE'。日志记录到系统的专用日志表中，要比记录到文件耗费更多的系统资源，因此对于需要启用慢查询日志，又需要能够获得更高的系统性能，那么建议优先记录到文件.
-
系统变量 log-queries-not-using-indexes ：未使用索引的查询也被记录到慢查询日志中（可选项）。如果调优的话，建议开启这个选项
mysql> show variables like 'log_queries_not_using_indexes'; +-------------------------------+-------+ | Variable_name | Value | +-------------------------------+-------+ | log_queries_not_using_indexes | OFF | +-------------------------------+-------+ mysql> set global log_queries_not_using_indexes=1; Query OK, 0 rows affected (0.00 sec) mysql> show variables like 'log_queries_not_using_indexes'; +-------------------------------+-------+ | Variable_name | Value | +-------------------------------+-------+ | log_queries_not_using_indexes | ON | +-------------------------------+-------+ 1 row in set (0.00 sec)
- 查看慢查询日志 cat /var/lib/mysql/mysql.log
- 默认慢查询方式是禁用的，可以通过set方式开启；设置后只对当前数据库生效，mysql重启后会失效
- 存储引擎如何存储数据、如何为存储的数据建立索引和如何更新、查询数据等技术的实现方法。要求支持并发性、事务、引用的完整性和索引的支持
- 查看数据库的存储引擎
-
SHOW ENGINES;查看MySQL数据 的所有引擎
- 事务安全表: InnoDB
- 非事务安全表: MyISAM、MEMORY、MERGE、EXAMPLE、NDB Cluster、ARCHIVE、CSV、BLACKHOLE、FEDERATED等
- SHOW VARIABLES LIKE '%default_storage_engine%'; 查看当前的默认存储引擎 (MySQL5.7 默认使用 InnoDB）
- 在MySQL中,不需要整个服务器都是用同一种引擎,针对具体的需求,可以对 每一个表使用不同的存储引擎 .并且想要进一步优化, 还可以自己编写一个存储引擎：create table(...) engine=MyISAM;
-
SHOW ENGINES;查看MySQL数据 的所有引擎
-
常见的存储引擎
-
InnoDB：一个健壮的事务型存储引擎，这种存储引擎已经被很多互联网公司使用，为用户操作非常大的数据存储提供了一个强大的解决方案。InnoDB还引入了行级锁定和外键约束，在以下场合下，使用InnoDB是最理想的选择
- 优点：Innodb引擎提供了对数据库ACID事务的支持，并且实现了SQL标准的四种隔离级别；支持多版本并发控制的行级锁，由于锁粒度小，写操作和更新操作并发高、速度快；支持自增长列；支持外键；适合于大容量数据库系统，支持自动灾难恢复
- 缺点：它没有保存表的行数，当SELECT COUNT(*) FROM TABLE时需要扫描全表
-
应用场景：当需要使用数据库事务时，该引擎当然是首选。由于锁的粒度更小，写操作不会锁定全表，所以在并发较高时，使用Innodb引擎会提升效率，更新密集的表, InnoDB存储引擎特别适合处理多重并发的更新请求。
-
MyISAM：不支持事务、也不支持外键，优势是访问速度快，对事务完整性没有 要求或者以select，insert为主的应用基本上可以用这个引擎来创建表
- 优点：MyISAM表是独立于操作系统的，这说明可以轻松地将其从Windows服务器移植到Linux服务器；MyISAM存储引擎在查询大量数据时非常迅速，进行大批量插入操作时执行速度也比较快。
- 缺点：MyISAM表没有提供对数据库事务的支持。不支持行级锁和外键。不适合用于经常UPDATE（更新）的表，效率低。
- 应用场景：以读为主的业务，例如：图片信息数据库，博客数据库，商品库等业务；对数据一致性要求不是非常高的业务（不支持事务）；硬件资源比较差的机器可以用 MyiSAM （占用资源少）
-
MEMORY：将表中的数据放在内存中，适用于存储临时数据的临时表和数据仓库中的纬度表
- 优点：memory类型的表访问非常的快，因为它的数据是放在内存中的
- 缺点：一旦服务关闭，表中的数据就会丢失掉。只支持表锁，并发性能差，不支持TEXT和BLOB列类型，存储varchar时是按照char的方式
- 应用场景：目标数据较小，而且被非常频繁地访问。如果数据是临时的，而且要求必须立即可用，那么就可以存放在内存表中。存储在Memory表中的数据如果突然丢失，不会对应用服务产生实质的负面影响。
-
InnoDB：一个健壮的事务型存储引擎，这种存储引擎已经被很多互联网公司使用，为用户操作非常大的数据存储提供了一个强大的解决方案。InnoDB还引入了行级锁定和外键约束，在以下场合下，使用InnoDB是最理想的选择
- 如何选择存储引擎
- 是否需要支持事务；
- 崩溃恢复，能否接受崩溃；
- 是否需要外键支持；
- 存储的限制；
- 对索引和缓存的支持
| 特性 | InnoDB | MyISAM | MEMORY |
| 存储限制 | 64TB | 无 | 有 |
| 支持事务 | 支持 | 不支持 | 不支持 |
| 锁机制 | 行锁 | 表锁 | 表锁 |
| B-tree索引 | 支持 | 支持 | 支持 |
| 哈希索引 | 支持 | 不支持 | 支持 |
| 外键支持 | 支持 | 不支持 | 不支持 |
| 存储空间消耗 | 高 | 低 | 低 |
| 内存消耗 | 高 | 低 | 高 |
| 批量写入数据消耗 | 慢 | 快 | 快 |
5.MySQL索引优化
- 索引就是一种将数据库中的记录按照特殊形式存储的数据结构。通过索引，能够显著地提高数据查询的效率，从而提升服务器的性能
- 使用索引后可以不用扫描全表来定位某行的数据，而是先通过索引表找到该行数据对应的物理地址然后访问相应的数据
-
索引种类
- 主键索引
CREATE TABLE tablename ( [...], PRIMARY KEY (字段名) ); ALTER TABLE tablename ADD PRIMARY KEY (字段名);- 普通索引
CREATE INDEX <索引的名字> ON tablename (字段名); ALTER TABLE tablename ADD INDEX [索引的名字] (字段名); CREATE TABLE tablename ( [...], INDEX [索引的名字] (字段名) );- 唯一索引
CREATE UNIQUE INDEX <索引的名字> ON tablename (字段名); ALTER TABLE tablename ADD UNIQUE INDEX [索引的名字] (字段名); CREATE TABLE tablename ( [...], UNIQUE [索引的名字] (字段名) ;- 复合索引
-- 复合索引可以代替多个单一索引，相比多个单一索引复合索引所需的开销更小。 CREATE INDEX <索引的名字> ON tablename (字段名1，字段名2...); ALTER TABLE tablename ADD INDEX [索引的名字] (字段名1，字段名2...); CREATE TABLE tablename ( [...], INDEX [索引的名字] (字段名1，字段名2...) );
- 全文索引
--使用全文索引，查询速度会比like快很多倍。 -- 在MySQL 5.6 以前的版本，只有MyISAM存储引擎支持全文索引 -- 从MySQL 5.6开始MyISAM和InnoDB存储引擎均支持 CREATE FULLTEXT INDEX <索引的名字> ON tablename (字段名); ALTER TABLE tablename ADD FULLTEXT [索引的名字] (字段名); CREATE TABLE tablename ( [...], FULLTEXT KEY [索引的名字] (字段名) ;
-
-
.全文索引方式有自然语言检索 IN NATURAL LANGUAGE MODE 和布尔检索 IN BOOLEAN MODE两种,有自己的语法格式，使用 match 和 against 关键字
SELECT * FROM users3 WHERE MATCH(NAME) AGAINST('aabb'); -- * 表示通配符,只能在词的后面 SELECT * FROM users3 WHERE MATCH(NAME) AGAINST('aa*' IN BOOLEAN MODE); - 全文索引必须在字符串、文本字段上建立。全文索引字段值必须在最小字符和最大字符之间的才会有效。（innodb：3-84；myisam：4-84）
-
.全文索引方式有自然语言检索 IN NATURAL LANGUAGE MODE 和布尔检索 IN BOOLEAN MODE两种,有自己的语法格式，使用 match 和 against 关键字
- 索引的优缺点
- 提高数据检索的效率,降低数据库的IO成本；通过索引列对数据进行排序,降低数据排序的成本,降低了CPU的消耗
- 创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加；索引需要占物理空间，除了数据表占用数据空间之外，每一个索引还要占用一定的物理空间；当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，降低了数据的维护速度
- 创建索引的原则
- 在经常需要搜索的列上创建索引，可以加快搜索的速度；
- 在作为主键的列上创建索引，强制该列的唯一性和组织表中数据的排列结构；
- 在经常用在连接的列上，这些列主要是一些外键，可以加快连接的速度；
- 在经常需要根据范围（in,between and）进行搜索的列上创建索引，因为索引已经排序，其指定的范围是连续的；
- 在经常需要排序的列上创建索引，因为索引已经排序，这样查询可以利用索引的排序，加快排序查询时间；
- 在经常使用在WHERE子句中的列上面创建索引，加快条件的判断速度
- 哈希索引：Hash底层实现是由Hash表来实现的，是根据键值 <key,value> 存储数据的结构。非常适合根据key查找value值，也就是单个key查询，或者说等值查询。对于每一行数据，存储引擎都会对所有的索引列计算一个哈希码，哈希码是一个较小的值，并且不同键值的行计算出来的哈希码也不一样
- 缺点：哈希索引只包含哈希值和行指针，而不存储字段值，所以不能使用索引中的值来避免读取行；哈希索引数据并不是按照索引值顺序存储的，所以也就无法用于排序。
哈希索引只支持等值比较查询。不支持任何范围查询和部分索引列匹配查找 - 优点：只需要做等值比较查询，而不包含排序或范围查询的需求，都适合使用哈希索引；访问哈希索引的数据非常快，除非有很多哈希冲突
- 缺点：哈希索引只包含哈希值和行指针，而不存储字段值，所以不能使用索引中的值来避免读取行；哈希索引数据并不是按照索引值顺序存储的，所以也就无法用于排序。
- B+Tree索引：
- 特点：非叶子节点不存储data数据，只存储索引值，这样便于存储更多的索引值；叶子节点包含了所有的索引值和data数据；叶子节点用指针连接，提高区间的访问性能
- 优点：等值查询或范围查询，排序
- 特点：非叶子节点不存储data数据，只存储索引值，这样便于存储更多的索引值；叶子节点包含了所有的索引值和data数据；叶子节点用指针连接，提高区间的访问性能
-
使用 EXPLAIN 关键字可以模拟优化器执行SQL查询语句，从而知道MySQL是如何处理你的SQL语句的。分析你的查询语句或是表结构的性能瓶颈。
- 表的读取顺序。（对应id）
- 数据读取操作的操作类型。（对应select_type）
- 哪些索引可以使用。（对应possible_keys）
- 哪些索引被实际使用。（对应key）
- 表直接的引用。（对应ref）
- 每张表有多少行被优化器查询。（对应rows）
- explain使用：explain+sql语句，通过执行explain可以获得sql语句执行的相关信息。
-
ID： select查询的序列号，包含一组数字，表示查询中执行select子句或操作表的顺序；表的读取顺序
- id相同，执行查询的表的顺序由上至下
- id不同，如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行
-
select_type和table ：查询类型，主要用于区别普通查询，联合查询，子查询等的复杂查询
- simple：简单的select查询，查询中不包含子查询或者UNION
- primary：查询中若包含任何复杂的子部分，最外层查询被标记
- subquery：在select或where列表中包含了子查询
- derived：在from列表中包含的子查询被标记为derived（衍生），MySQL会递归执行这些子查询，把结果放到临时表中
- union：如果第二个select出现在UNION之后，则被标记为UNION，如果union包含在from子句的子查询中，外层select被标记为derived
- union result : UNION 的结果
- 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 : 出现index 是 SQL 使用了索引, 但是没有通过索引进行过滤,一般是使用了索引进行排序分组
- ALL : 对于每个来自于先前的表的行组合,进行完整的表扫描
-
possible_keys：显示可能应用到这张表上的索引, 一个或者多个. 查询涉及到的字段上若存在索引, 则该索引将被列出, 但不一定被查询实际使用
-
key：实际使用的索引，若为null，则没有使用到索引。（两种可能，1.没建立索引, 2.建立索引，但索引失效）。查询中若使用了覆盖索引，则该索引仅出现在key列表中。
- 覆盖索引：一个索引包含(或覆盖)所有需要查询的字段的值,通过查询索引就可以获取到字段值
- 理论上没有使用索引,但实际上使用了
- 理论和实际上都没有使用索引
-
理论和实际上都使用了索引
-
key_len：表示索引中使用的字节数, 可以通过该列计算查询中使用索引的长度；key_len 字段能够帮你检查是否充分利用了索引 ken_len 越长, 说明索引使用的越充分
- 字符集是utf8 一个字符char3个字节
- int类型是4个字节
- ref：显示索引的哪一列被使用了，如果可能的话，是一个常数。哪些列或常量被用于查找索引列上的值
- L1.id='1'; 1是常量 , ref = const
- L2表被关联查询的时候,使用了主键索引, 而值使用的是驱动表(执行计划中靠前的表是驱动表)L1表的ID, 所以 ref = test_explain.L1.id
-
rows ：表示MySQL根据表统计信息及索引选用情况，估算的找到所需的记录所需要读取的行数；越少越好
-
使用like 查询,会产生全表扫描, L2中有3条记录,就需要读取3条记录进行查找
-
如果使用等值查询, 则可以直接找到要查询的记录,返回即可,所以只需要读取一条
-
使用like 查询,会产生全表扫描, L2中有3条记录,就需要读取3条记录进行查找
-
extra：Extra 是 EXPLAIN 输出中另外一个很重要的列，该列显示MySQL在查询过程中的一些详细信息。filtered 指返回结果的行占需要读到的行(rows列的值)的百分比
-
Using filesort ：得到所需结果集，需要对所有记录进行文件排序。这类SQL语句性能极差，需要进行优化；在一个没有建立索引的列上进行了order by，就会触发filesort，常见的优化方案是，在order by的列上添加索引，避免每次查询都全量排序。
-
Using temporary：说明需要建立临时表 (temporary table) 来暂存中间结果。常见与 group by 和 order by，这类SQL语句性能较低，往往也需要进行优化
-
Using where:表示使用了where条件过滤数据;返回所有记录的SQL，不使用where条件过滤数据，大概率不符合预期，对于这类SQL往往需要进行优化；使用了where条件的SQL，并不代表不需要优化，往往需要配合explain结果中的type（连接类型）来综合判断。例如本例查询的 age 未设置索引，所以返回的type为ALL，仍有优化空
间，可以建立索引优化查询 .
-
Using index:说明sql所需要返回的所有列数据均在一棵索引树上，而无需访问实际的行记录
- Using join buffer:说明需要进行嵌套循环计算, 这里每个表都有五条记录，内外表查询的type都为ALL。问题在于 两个关联表join 使用 uname，关联字段均未建立索引，就会出现这种情况。常见的优化方案是，在关联字段上添加索引，避免每次嵌套循环计算
-
Using filesort ：得到所需结果集，需要对所有记录进行文件排序。这类SQL语句性能极差，需要进行优化；在一个没有建立索引的列上进行了order by，就会触发filesort，常见的优化方案是，在order by的列上添加索引，避免每次查询都全量排序。
-
当我们需要优化一个SQL语句的时候，我们需要知道该SQL的执行计划，比如是全表扫描，还是索引扫描； 使用 explain 关键字可以模拟优化器执行 sql 语句，从而知道 mysql 是如何处理 sql 语句的,方便我们开发人员有针对性的对SQL进行优化.
- 表的读取顺序。（对应id）
- 数据读取操作的操作类型。（对应select_type）
- 哪些索引可以使用。（对应possible_keys）
- 哪些索引被实际使用。（对应key）
- 每张表有多少行被优化器查询。（对应rows）
- 评估sql的质量与效率 (对应type)
-
MySQL的锁分类
-
MySQL数据库由于其自身架构的特点,存在多种数据存储引擎, MySQL中不同的存储引擎支持不同的锁机制。
- MyISAM和MEMORY存储引擎采用的表级锁
- InnoDB存储引擎既支持行级锁，也支持表级锁，默认情况下采用行级锁。
- BDB采用的是页面锁，也支持表级锁
-
按照数据操作的类型分
- 读锁（共享锁）：针对同一份数据，多个读操作可以同时进行而不会互相影响。
- 写锁（排他锁）：当前写操作没有完成前，它会阻断其他写锁和读锁。
-
按照数据操作的粒度分
- 表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低。
- 行级锁： 开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。
- 页面锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般
-
按照操作性能可分为乐观锁和悲观锁
- 乐观锁：一般的实现方式是对记录数据版本进行比对，在数据更新提交的时候才会进行冲突检测，如果发现冲突了，则提示错误信息。
- 悲观锁：在对一条数据修改的时候，为了避免同时被其他人修改，在修改数据之前先锁定，再修改的控制方式。共享锁和排他锁是悲观锁的不同实现，但都属于悲观锁范畴。
-
MySQL数据库由于其自身架构的特点,存在多种数据存储引擎, MySQL中不同的存储引擎支持不同的锁机制。
-
表级锁（偏读）
- 表级锁是MySQL中锁定粒度最大的一种锁，表示对当前操作的整张表加锁，它实现简单，资源消耗较少，被大部分MySQL引擎支持。最常使用的MYISAM与INNODB都支持表级锁定。
- 表级锁定分为: 表共享读锁（共享锁）与表独占写锁（排他锁）。
- 特点: 开销小，加锁快；不会出现死锁；锁定粒度大，发出锁冲突的概率最高，并发度最低。
- 演示：
- 数据准备
-- 创建数据库 CREATE DATABASE test_lock CHARACTER SET 'utf8'; -- 创建表,选择 MYISAM存储引擎 CREATE TABLE mylock01( id INT PRIMARY KEY AUTO_INCREMENT, title VARCHAR(20) )ENGINE MYISAM; -- 创建表 CREATE TABLE mylock02( id INT PRIMARY KEY AUTO_INCREMENT, title VARCHAR(20) )ENGINE MYISAM; -- mylock01表中向插入数据 INSERT INTO mylock01(title) VALUES('a1'); INSERT INTO mylock01(title) VALUES('b1'); INSERT INTO mylock01(title) VALUES('c1'); INSERT INTO mylock01(title) VALUES('d1'); INSERT INTO mylock01(title) VALUES('e1'); -- mylock02表中向插入数据 INSERT INTO mylock02(title) VALUES('a'); INSERT INTO mylock02(title) VALUES('b'); INSERT INTO mylock02(title) VALUES('c'); INSERT INTO mylock02(title) VALUES('d'); INSERT INTO mylock02(title) VALUES('e'); SELECT * FROM mylock01;
- 加锁语法
- 查看表中锁
- 手动加锁
- 释放锁
- 查看表中锁
- 加读锁测试
- lock table mylock01 read;
-
开启两个窗口,对mylock01进行读操作, 两个窗口都可以读；select * from mylock01
-
在1窗口进行写操作 (update), 失败
-
在1窗口中 读取其他的表,比如读取 mylock 02表. 读取失败
-
在2窗口中 对 mylock01表 进行写操作
-
解除 mylock01 的锁定,窗口2 的修改执行
- 总结：对MyISAM表的读操作 (加读锁) ,不会阻塞其他进程对同一表的读请求,但是会阻塞对同一表的写请求. 只有当读锁释放后,才会执行其他进程的写操作.
- 加写锁测试
- 在窗口1中 对mylock01表加写 ：lock table mylock01 write
- 在窗口1中, 对 mylock01 进行读写操作, 都是可以进行的
- 在窗口1中读其他表, 还是不允许
- 在窗口2 中读mylock01 表, 读操作被阻塞
- 在窗口2 中 对mylock01表进行写操作, 仍然被阻塞
- 释放锁, 窗口2操作执行：unlock tables;
- 总结: 对MyISAM表加写锁, 会阻塞其他进程对同一表的读和写操作,只有当写锁释放后,才会执行其他进程的操作
- 数据准备
- 行级锁（偏写）：行锁的是mysql锁中粒度最小的一种锁，因为锁的粒度很小，所以发生资源争抢的概率也最小，并发性能最大，但是也会造成死锁，每次加锁和释放锁的开销也会变大。
- 使用MySQL行级锁的两个前提：使用 innoDB 引擎+开启事务 (隔离级别为 Repeatable Read )
-
InnoDB行锁的类型
- 共享锁（S）：当事务对数据加上共享锁后, 其他用户可以并发读取数据，但任何事务都不能对数据进行修改（获取数据上的排他锁），直到已释放所有共享锁
- 排他锁（X）：如果事务T对数据A加上排他锁后，则其他事务不能再对数据A加任任何类型的封锁。获准排他锁的事务既能读数据，又能修改数据。
-
加锁的方式：
- InnoDB引擎默认更新语句，update,delete,insert 都会自动给涉及到的数据加上排他锁，
-
select语句默认不会加任何锁类型，如果要加可以使用下面的方式
加共享锁（S）：select * from table_name where ... lock in share mode; 加排他锁（x）：select * from table_name where ... for update;
- for update是在数据库中上锁用的，可以为数据库中的行上一个排他锁。
- for update 的应用场景：存在高并发并且对于数据的准确性很有要求的场景，是需要了解和使用for update的
- for update 的注意点：for update 仅适用于InnoDB，并且必须开启事务，在begin与commit之间才生效
-
锁兼容
- 共享锁只能兼容共享锁, 不兼容排它锁
- 排它锁互斥共享锁和其它排它锁 行级锁都是基于索引的，如果一条 SQL 语句用不到索引是不会使用行级锁的，而会使用表级锁把整张表锁住
-
-
更新时的行锁测试
- 数据准备
#创建表 CREATE TABLE innodb_lock( id INT PRIMARY KEY AUTO_INCREMENT, NAME VARCHAR(20), age INT, INDEX idx_name(NAME) ); # 插入数据 INSERT INTO innodb_lock VALUES(NULL,'a', 13); INSERT INTO innodb_lock VALUES(NULL,'b', 23); INSERT INTO innodb_lock VALUES(NULL,'c', 33); INSERT INTO innodb_lock VALUES(NULL,'d', 43);
- 环境配置：打开两个窗口, 都开启手动提交事务 ( 提交事务或回滚事务就会释放锁 )：SET autocommit=0;
-
执行不同会话修改操作, 窗口1读，窗口2 写。窗口1 进行, 对id为1的数据 进行更新操作,但是不进行commit.执行之后,在当前窗口查看表数据,发现被修改了.
-
在窗口2 查看表信息, 无法看到更新的内容。说明行级锁中的写锁主要是为了解决在修改数据时，不允许其他事务对当前数据进行修改和读取操作，从而可以有效避免”脏读”问题的产生。
-
窗口1 对innodb_lock表的 id=1 的这一行,进行写操作,但是不要commit
-
窗口2 也对innodb_lock表的 id=1 的这一行,进行写操作,发现发生了阻塞
-
等窗口1执行commit语句之后，窗口2的SQL就会执行了
。说明在有写锁的情况下，其他事务不能再对当前数据添加写锁，从而保证数据的一致性,从而避免了不可重复读的问题
- 数据准备
-
查询时的排他锁测试
- select语句加排他锁方式 : select * from table_name where ... for update;
-
在窗口1中, 首先开启事务, 然后对 id为1 的数据进行排他查询
- 在窗口2中，对同一数据分别使用 排他查 和 共享锁 两种方式查询，开了排他锁查询和共享锁查询都会处于阻塞状态，因为id=1的数据已经被加上了排他锁，此处阻塞是等待排他锁释放。
- 如果只是使用普通查询,我们发现是可以的：select * from innodb_lock where id = 1;
-
查询时的共享锁测试：事务获取了共享锁，在其他查询中也只能加共享锁,但是不能加排它锁。
- 窗口1 开启事务, 使用共享锁查询 id = 2 的数据 ,但是不要提交事务
begin; select * from innodb_lock where id = 2 lock in share mode;
- 窗口2 开启事务, 使用普通查询和共享锁查询 id = 2 的数据 ,是可以的
select * from innodb_lock where id = 2 lock in share mode; select * from innodb_lock where id = 2;
-
加排他锁就查不到，因为排他锁与共享锁不能存在同一数据上。
- 行锁分析：当等待次数很高，而且每次等待时长也不小的时候，我们就需要分析系统中为什么会有如此多的等待，然后根据分析结果着手制定优化策略
-
更新时的行锁测试
8.MySQL高可用集群
-
MySQL高可用：系统无中断地执行其功能的能力，代表系统的可用性程度。是进行系统设计时的准则之一。高可用性系统与构成该系统的各个组件相比可以更长时间运行。
-
A表示可用性; MTBF表示平均故障间隔; MTTR表示平均恢复时间
- 高可用应用场景
- 如果数据库发生宕机或意外中断等故障，需要尽快恢复数据库的可用性，减少停机时间，保证业务不会因为数据库的故障而中断
- 用作备份、只读副本的非主节点的数据要和主节点的数据实时或最终保持一致，当业务发生数据库切换时，切换前后的数据库应当一致，不会因为数据缺失或不一致而影响业务
- 客户端通过master对数据库进行写操作，slave端进行读操作。master出现问题后可以手动切换到slave
-
A表示可用性; MTBF表示平均故障间隔; MTTR表示平均恢复时间
-
MySQL高可用集群方案
- 主从复制+读写分离
- 主从复制：客户端通过Master对数据库进行写操作，slave端进行读操作，并可进行备份。Master出现问题后，可以手动将应用切换到slave端
- 实时灾备，用于故障切换（高可用）
- 读写分离，提供查询服务（读扩展）
- 数据备份，避免影响业务（高可用）
- 读写分离
- 主从只负责各自的写和读，极大程度的缓解锁争用
- 从库可配置myisam引擎，提升查询性能以及节约系统开销
- 从库同步主库，通过主库发送来的binlog恢复数据
- 读写分离适用与读远大于写的场景，如果只有一台服务器，当select很多时，update和delete会被这些select访问中的数据堵塞，等待select结束，并发性能不高。 对于写和读比例相近的应用，应该部署双主相互复制
- 主从复制：客户端通过Master对数据库进行写操作，slave端进行读操作，并可进行备份。Master出现问题后，可以手动将应用切换到slave端
- 主从复制+读写分离
-
- 双主从复制
- 双主模式是指两台服务器互为主从，任何一台服务器数据变更，都会通过复制应用到另外一方的数据库中。
-
建议使用双主单写，因为双主双写存在以下缺点：
- ID冲突：在A主库写入，当A数据未同步到B主库时，对B主库写入，如果采用自动递增容易发生ID主键的冲突。
- 可以采用MySQL自身的自动增长步长来解决，例如A的主键为1,3,5,7...，B的主键为2,4,6,8... ，但是对数据库运维、扩展都不友好
-
更新丢失：同一条记录在两个主库中进行更新，会发生前面覆盖后面的更新丢失
-
MMM架构：MMM（Master-Master Replication Manager for MySQL）是一套用来管理和监控双主复制，支持双主故障切换 的第三方软件。MMM 使用Perl语言开发，虽然是双主架构，但是业务上同一时间只允许一个节点进行写入操作。下图是基于MMM实现的双主高可用架构 MMM故障处理机制
- 双主从复制
-
-
- MMM 包含writer和reader两类角色，分别对应写节点和读节点。当 writer节点出现故障，程序会自动移除该节点上的VIP写操作切换到 Master2，并将Master2设置为writer，将所有Slave节点会指向Master2
- 除了管理双主节点，MMM 也会管理 Slave 节点，在出现宕机、复制延迟或复制错误，MMM 会移除该节点的 VIP，直到节点恢复正常。
- MHA架构：MHA（Master High Availability）目前在MySQL高可用方面是一个相对成熟的解决方案，它由日本人开发，是一套优秀的作为MySQL高可用性环境下故障切换和主从提升的高可用软件。
-
-
-
- 在MySQL故障切换过程中，MHA能做到在0~30秒之内自动完成数据库的故障切换操作，并且在进行故障切换的过程中，MHA能在最大程度上保证数据的一致性，以达到真正意义上的高可用。
- 目前MHA主要支持一主多从的架构，要搭建MHA,要求一个复制集群中必须最少有三台数据库服务器，一主二从，即一台充当master，一台充当备用master，另外一台充当从库
- MHA Manager管理多组主从复制 该软件由两部分组成：MHA Manager（管理节点）和MHA Node（数据节点）
- MHA Manager可以单独部署在一台独立的机器上管理多个master-slave集群，也可以部署在一台slave节点上
- MHA Node运行在每台MySQL服务器上，MHA Manager会定时探测集群中的master节点，当master出现故障时，它可以自动将最新数据的slave提升为新的master，然后将所有其他的slave重新指向新的master。整个故障转移过程对应用程序完全透明。
-
MHA故障处理机制：
- 把宕机master的binlog保存下来，根据binlog位置点找到最新的slave
- 用最新slave的relay log修复其它slave，将保存下来的binlog在最新的slave上恢复
- 将最新的slave提升为master，将其它slave重新指向新提升的master，并开启主从复制
- MHA优点：自动故障转移快；主库崩溃不存在数据一致性问题；性能优秀，支持半同步复制和异步复制；一个Manager监控节点可以监控多个集群
-
unique_subquery > index_subquery > range > index > ALL
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
