MySQL高级学习笔记

1、MySQL架构组成

1.1 高级MySQL介绍

什么是DBA？

• 数据库管理员，英文是Database Administrator，简称DBA；

百度百科介绍
数据库管理员（简称DBA），是从事管理和维护数据库管理系统（DBMS）的工作人员的统称，属于运维工程师的一个分支，主要负责业务数据库从设计、测试到部署交付的全生命周期管理。

DBA的核心目标是保证数据库管理系统的稳定性、安全性、完整性和高性能。

在国外，也有公司把DBA称作数据库工程师（Database Engineer），两者的工作内容基本相同，都是保证数据库服务7*24小时的稳定高效运转，但是需要区分一下DBA和数据库开发工程师：
1、数据库开发工程师的主要职责是设计和开发数据库管理系统和数据库应用软件系统，侧重于软件研发；
2、DBA的主要职责是运维和管理数据库管理系统，侧重于运维管理；

一个高级DBA的职责：

• 负责MySQL的容量规划，架构设计及安装、部署；
• 负责MySQL的日常管理，监控和维护，并对MySQL进行持续性能优化；
• 负责MySQL开发支持，参与数据架构规划设计，以及相关业务的数据建模、设计评审、SQL代码审核优化；

中级Java开发工程师对数据库知识的掌握程度

• 熟练操作主流数据库，能够通过代码（框架）完成日常的数据库操作；
• 熟练使用SQL，熟悉SQL优化，熟悉存储过程、视图等创建及使用；
• 了解MySQL的整体体系结构，了解MySQL事务、存储引擎的特点；
• 了解MySQL索引优化，了解MySQL相关锁机制；

我们作为Java开发工程师，关注的应该是跟开发相关的数据库知识，了解这些高级的知识，目的是让我们编写出更加高效的应用程序；

专业的数据库维护、服务器优化、性能参数调优等等数据库相关的运维工作还是要交给DBA去做的。

1.2 MySQL逻辑架构

学习MySQL就好比盖房子，如果想把房子盖的特别高，地基一定要稳，基础一定要牢固。学习MySQL数据库前要先了解它的体系结构，这是学好MySQL数据库的前提。

1.2.1 MySQL架构体系介绍

MySQL由链接池、SQL接口、解析器、优化器、缓存、存储引擎等组成，可以分为四层，即连接层、服务层、引擎层和文件系统层。

• 如下是官方文档中MySQL的基础架构图：
  

1、连接层
最上面是一些客户端和连接服务，不是MySQL特有的，所有基于网络的C/S的网络应用程序都应该包括连接处理、认证、安全管理等。

2、服务层
中间层是MySQL的核心，包括查询解析、分析、优化和缓存等。同时它还提供跨存储引擎的功能，包括存储过程、触发器和视图等。

3、引擎层
存储引擎层，它负责存取数据。服务器通过API可以和各种存储引擎进行交互。不同的存储引擎具有不同的功能，我们可以根据实际需求选择使用对应的存储引擎。

4、存储层
数据存储层，主要是将数据存储与运行与裸设备的文件系统之上，并完成与存储引擎的交互。

1.2.2 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语句和结果完整地保存到Query Cache中，以后若有相同的SQL语句执行则直接返回结果；

1.3 MySQL物理文件

物理文件包括：日志文件，数据文件，配置文件；

1.3.1 日志文件

日志文件包括：

• error log：错误日志 排错 /var/log/mysqld.log【默认开启】；
• bin log：二进制日志 备份 增量备份 DDL DML DCL；
• relay log：中继日志 复制 接收replication master；
• slow log：慢查询日志 调优 查询时间超过指定值；

-- 查看错误日志文件路径
show variables like 'log_error';

-- 慢查询日志文件路径
show variables like 'slow_query_log_file';

-- bin log日志文件 需要在my.cnf中配置
log-bin=/var/log/mysql-bin/bin.log
server-id=2

-- 查看relay log相关参数
show variables like '%relay%'

1.3.2 配置文件&数据文件

1）配置文件my.cnf
在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/mysqlldata/3307/mysql.pid   #pid文件所在目录
basedir = /usr/local/mysqll-5.7.11           #使用该目录作为根目录（安装目录）
datadir = /data/mysqldata/3307/data          #数据文件存放的目录
tmpdir = /data/mysqldata/3307/tmp            #mysql存放临时文件的目录
character_set_server = utf8mb4               #服务端默认编码（数据库级别）

2）数据文件

-- 查看数据文件的位置
show variables like '%dir%';

1、.frm文件
不论是什么存储引擎，每一个表都会有一个以表名命名的.frm文件，与表相关的元数据信息都存放在此文件中，包括表结构的定义信息等；

2、.myd文件
myisam存储引擎专用，存放myisam表的数据（data）。每一个myisam表都会有一个.myd文件与之呼应，同样存放在所属数据库的目录下；

3、.myi文件
也就是myisam存储引擎专用，存放myisam表的索引相关信息。每一个myisam表对应一个.myi文件，其存放的位置和.frm及.myd一样

4、.ibd文件
存放innoDB的数据文件（包括索引）

5、db.opt文件
此文件在每一个自建的库里都会有，记录这个库的默认使用的字符集和校验规则；

2.MySQL的备份与恢复

2.1 为什么要进行数据备份

我们试想一下，在生产环境中什么最重要？如果我们服务器的硬件坏了可以维修或者换新，软件问题可以修复或重新安装，但是如果数据没了呢，对于一些网站、系统来说，数据库就是一切，所以做好数据库的备份是至关重要的！

2.1.1 数据库备份的应用场景

数据备份在很多工作中都是经常会用到的，因为数据容易因为各种原因而丢失，造成数据丢失的原因有哪些呢？

• 数据丢失应用场景
  • 系统硬件或软件故障
  • 自然灾害，比如水灾、火灾、地震等
  • 黑客攻击，非法访问者故意破坏
  • 误操作，认为的误操作占比最大
    
• 非数据丢失应用场景：
  • 开发测试环境数据库搭建
  • 数据库或者数据迁移

2.2 数据备份的类型

2.2.1 按照业务方式分

完全备份
将数据库的全部信息进行备份，包括数据库的数据文件、日志文件，还需要备份数据的存储位置以及数据库中的全部对象和相关信息。

差异备份
备份从最近的完全备份后对数据所做的修改，备份完全备份后变化了的数据文件、日志文件以及数据库中其他被修改的内容。

增量备份
增量备份是指在一次全备份或上一次增量备份后，以后每次的备份只需备份与前一次相比增加或者被修改的文件。

	完全备份	差异备份	增量备份
备份方法	备份所有文件	一次全备份后，备份与全备份差异的部分	一次全备份后与上次备份的差异部分
备份速度	最慢	较快	最快
恢复速度	最快	较快	最慢
空间要求	最多	较多	最少
优势	最快的恢复速度，只需要上一次完全备份就能恢复	相比增量，更快也更简单并且只需要最近一次的完全备份和最后一次的差异备份就能恢复	备份速度快，较少的空间需求，没有重复的备份文件
劣势	最多的空间需求，大量重复的备份	较慢的备份速度，仍然会存在许多的备份文件	最慢的恢复速度，恢复需要最近一次完全备份和全部增量备份

2.2.2 备份的组合方式

完全备份与差异备份
以每周数据备份为例，可以在星期一进行完全备份，在星期二至星期六进行差异备份。如果在星期六数据被破坏了，则只需要还原星期一完全的备份和星期五的差异备份。

这种策略备份数据需要较多的时间，但还原数据使用较少的时间。

完全备份与增量备份
以每周数据备份为例，在星期一进行完全备份，在星期二至星期六进行增量备份。如果在星期六数据被破坏了，则需要还原星期一正常的备份和从星期二至星期五的所有增量备份。

这种策略备份数据需要较少的时间，但还原数据使用较长的时间。

2.3 MySQL冷备份和热备份

冷备份与热备份指的是，按照数据库的运行状态分类；

2.3.1 冷备份

冷备份指的是当数据库进行备份时，数据库不能进行读写操作，即数据库要下线；

冷备份的优点：

• 是操作比较方便的备份方法（只需拷贝文件）
• 低度维护，高度安全

冷备份的缺点：

• 在实施备份的全过程中，数据库必须要作备份而不能作其他工作；
• 若磁盘空间有限，只能拷贝到磁带等其他外部存储设备上，速度比较慢；
• 不能按表或按用户恢复；

2.3.2 热备份

热备份
热备份是在数据库运行的情况下，备份数据库操作的SQL语句，当数据库发生问题时，可以重新执行一遍备份的sql语句。

热备份的优点：

• 可在表空间或数据文件级备份，备份时间短；
• 备份时数据库仍可使用；
• 可达到秒级恢复（恢复到某一时间点上）；

热备份的缺点：

• 不能出错，否则后果严重；
• 因难维护，所以要特别仔细小心，不允许“以失败而告终”；

2.4 实战演练

2.4.1 冷备份实战

1、关闭SELinux
修改selinux配置文件，将SELINUX=enforcing改成SELINUX=disabled，保存后退出；

vim /etc/selinux/config
SELINUX=disabled

修改后需要重启

reboot # 重启命令

2、找到MySQL数据文件位置，停止MySQL服务；

show variables like '%dir%';

-- 结果显示，数据目录就是datadir的所在位置，即/var/lib/mysql/

service mysqld stop -- 停止mysql

3、进入到/mysql目录，执行打包命令，将数据文件打包备份；

cd /var/lib/ # 进入其上级目录
tar jcvf /root/backup.tar.bz2 mysql/  #打包压缩到root目录下

4、删除掉数据目录下的所有数据

rm -rf /var/lib/mysql/

5、恢复数据（使用tar命令）

-- 解压
tar jxvf backup.tar.bz2 mysql/

-- 把备份的文件移动到/var/lib里面去替代原来的mysql
mv /root/mysql/ /var/lib/

6、启动MySQL，然后登录MySQL，查看数据是否丢失，如果数据正常代表冷备份成功；

service mysqld start

2.4.2 热备份实战

mysqldump备份工具
mysqldump是MySQL数据库用来备份和数据转移的一个工具，一般在数据量很小的时候（几个G）可以用于备份。热备份可以对多个库进行备份，可以对单张表活着某几张表进行备份。

备份单个数据库
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、备份表数据

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查询和慢查询日志分析

3.1 SQL性能下降的原因

在日常的运维过程中，经常会遇到DBA将一些执行效率较低的SQL发过来找开发人员分析，当我们拿到这个SQL语句之后，在对这些SQL进行分析之前，需要明确可能导致SQL执行性能下降的原因进行分析，执行性能下降可以提现在以下两个方面：

1、等待时间长
锁表导致查询一直处于等待状态，后续我们从MySQL锁的机制去分析SQL执行的原理；

2、执行时间长
a. 查询语句写的烂；
b. 索引失效；
c. 关联查询太多join；
d. 服务器调优及各个参数的设置；

3.2 需要遵守的优化原则

查询优化是一个复杂的工程，涉及从硬件到参数配置、不同数据库的解析器、优化器实现、SQL语句的执行顺序，索引以及统计信息的采集等等方面。

下面给大家介绍几个编写SQL的关键原则，可以帮助我们编写出更加高效的SQL查询：

• 第一条：只返回需要的结果
  • 一定要为查询语句指定where条件，过滤掉不需要的数据行；
  • 避免使用select * from，因为它表示查询表中的所有字段；
• 第二条：确保查询使用了正确的索引
  • 经常出现在WHERE条件中的字段建立索引，可以避免全表扫描；
  • 将order by排序的字段加入到索引中，可以避免额外的排序操作；
  • 多表连接查询的关联字段建立索引，可以提高连接查询的性能；
  • 将group by分组操作字段加入到索引中，可以利用索引完成分组；
• 第三条：避免让索引失效
  • 在where子句中对索引字段进行表达式运算或者使用函数都会导致索引失效；
  • 使用like匹配时，如果通配符出现在左侧，则无法使用索引；
  • 如果where条件中的字段上创建了索引，尽量设置为not null；

3.3 SQL的执行顺序

程序员编写的SQL

select distinct <select_list>

from <left_table> <join_type>

join <right_table> on <join_condition>

where <where_condition>

group by <group_by_list>

having <having_condition>

order by <order_by_condition>

limit <limit_number>

MySQL执行的SQL

1、from <left_table> <join_type>

2、on <join_condition>

3、<join_type> join <right_table>

4、where <where_condition>

5、group by <group_by_list>

6、having <having_condition>

7、select

8、distinct <select_list>

9、order by <order_by_condition>

10、limit <limit_number>

1、from子句：左右两个表的笛卡尔积；
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：取出指定行的记录，并将结果返回；

注意：mysql在没有建立索引的情况下，执行查询时，会进行全表扫描；

查看下面的SQL分析执行顺序

select
    id,
    sex,
    count(*) AS num
from
    employee
where name is not null
group by sex
order by id

上面的SQL执行顺序如下：
1、首先执行from子句，从employee表组装数据源的数据；
2、执行where子句，筛选employee表中所有name不为null的数据；
3、执行group by子句，按“性别”列进行分组；
4、执行select操作，获取需要的列；
5、最后执行order by，对最终的结果进行排序；

3.4 join查询的七种方式

7种join，可以分为四类：内连接、左连接、右连接、全连接；

3.5 join查询sql编写

1、创建表 插入数据

---部门表
DROP TABLE IF EXISTS `t_dept`; CREATE TABLE `t_dept` (
  `id` varchar(40) NOT NULL,
  `name` varchar(40) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

---员工表
DROP TABLE IF EXISTS `t_emp`; CREATE TABLE `t_emp` (
  `id` varchar(40) NOT NULL,
  `name` varchar(40) DEFAULT NULL,
  `age` int(3) DEFAULT NULL,
  `deptid` varchar(40) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `deptid` (`deptid`),
  CONSTRAINT `deptid` FOREIGN KEY (`deptid`) REFERENCES `t_dept` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--插入部门数据
INSERT INTO `t_dept` VALUES ('1', '研发部'); 
INSERT INTO `t_dept` VALUES ('2', '人事部'); 
INSERT INTO `t_dept` VALUES ('3', '财务部');

--插入员工数据
INSERT INTO `t_emp` VALUES ('1', '赵四', 23, '1'); 
INSERT INTO `t_emp` VALUES ('2', '刘能', 25, '2'); 
INSERT INTO `t_emp` VALUES ('3', '广坤', 27, '1'); 
INSERT INTO `t_emp` VALUES ('4', '玉田', 43, NULL);

3.5.1 内连接

select * from t_emp e inner join t_dept d on e.deptid = d.id

3.5.2 左连接

select * from t_emp e left join t_dept d on e.deptid = d.id

3.5.3 左连接去重叠部分

select * from t_emp e left join t_dept d on e.deptid = d.id and e.deptid is null;

### 3.5.4 右连接  ```sql select * from t_emp e right join t_dept d on e.deptid = d.id; ```

右连接去重叠部分

select * from t_emp e right join t_dept d on e.deptid = d.id and e.id is null;

3.5.6 全连接

SELECT * FROM t_emp e LEFT JOIN t_dept d ON e.deptid = d.id
UNION
SELECT * FROM t_emp e RIGHT JOIN t_dept d ON e.deptid = d.id

MySQL union操作符用于连接两个以上的select语句的结果组合到一个结果集合中。多个select语句会删除重复的数据。

3.5.7 各自独有

SELECT * FROM t_emp e LEFT JOIN t_dept d ON e.deptid = d.id
WHERE e.deptid IS NULL
UNION
SELECT * FROM t_emp e RIGHT JOIN t_dept d ON e.deptid = d.id
WHERE e.id IS NULL

3.6 慢查询日志分析

3.6.1 慢查询介绍

MySQL的慢查询，全名是慢查询日志，是MySQL提供的一种日志记录，用来记录在MySQL中响应时间超过阈值的语句。

默认情况下，MySQL数据库并不启动慢查询日志，需要手动来设置这个参数。

如果不是调优需要的话，一般不建议启动该参数，因为开启慢查询日志会或多或少带来一定的性能影响。

慢查询日志支持将日志记录写入文件和数据库表。

3.6.2 慢查询参数

1）执行下面的语句

show variables like "%query%";

2）MySQL慢查询的相关参数解释：

• slow_query_log：是否开启慢查询日志， 1-表示开启 0-表示关闭；
• slow_query_log_file：新版（5.6及以上版本）MySQL数据库慢查询日志存储路径；
• long_query_time：慢查询阈值，当查询时间多于设定的阈值时，记录日志；

3.6.3 慢查询配置方式

1、默认情况下slow_query_log的值为off，表示慢查询日志是禁用的

mysql> show variables like '%slow_query_log%';

2、可以通过设置slow_query_log的值来开启

mysql> set global slow_query_log=1;

mysql> show variables like '%slow_query_log%';

3、使用set global slow_query_log=1开启了慢查询日志只对当前数据库生效，MySQL重启后则会失效。如果要永久生效，就必须修改配置文件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%';

4、那么开启了慢查询日志后，什么样的SQL才会记录到慢查询日志里面呢？这个是由参数long_query_time控制，默认情况下long_quuery_time的值为10秒；

mysql> show variables like 'long_query_time';

mysql> set global long_query_time =1;

mysql> show variables like 'long_query_time'; ## 查询出来还是10s

5、我修改了变量long_query_time，但是查询变量long_query_time的值还是10，难道没有修改到？注意：使用命令set global long_query_time=1修改后，需要重新连接或新开一个会话才能看到修改值；

6、log_output参数是指定日志的存储方式。log_output='FILE’表示将日志存入文件，默认值是‘FILE’。log_output='TABLE’表示将日志存入数据库，这样日志信息就会被写入到mysql.show_log表中。

MySQL数据库支持同时两种日志的存储方式，配置的时候以逗号隔开即可，如：log_ouutput=‘FILE,TABLE’。日志记录到系统的专用日志表中，要比记录到文件耗费更多的系统资源，因此对于需要开启慢查询日志，又需要能够获得更高的系统性能，那么建议优先记录到文件。

7、系统变量log_queries_not_using_indexes：未使用索引的查询也被记录到慢查询日志中（可选项）。如果调优的话，建议开启这个选项。

set global log_queries_not_using_indexes=1;

4.MySQL存储引擎

4.1 存储引擎介绍

什么是存储引擎
百度百科：MySQL中的数据用各种不同的技术存储在文件（或内存）中。这些技术中的每一种技术都使用不同的存储引擎、索引技巧、锁定水平并且最终提供广泛的不同的功能和能力。通过选择不同的技术，你能够获取额外的速度或者功能，从而改善你的应用的整体功能。

存储引擎就是如何存储数据、如何为存储的数据建立索引和如何更新、查询数据等技术的实现方法。就像汽车的发动机一样，存储引擎好坏，决定数据库提供的功能和性能；

存储引擎的作用

• 并发性
• 事务支持
• 引用完整性
• 索引支持
  
  
  
  

4.2 常见的3种存储引擎

MySQL给用户提供了很多种类的存储引擎，主要分两大类：

• 事务安全表：InnoDB
• 非食物安全表：MyISAM、MEMORY、MERGE、EXAMPLE、NDB Cluster、ARCHIVE、CSV、BLACKHOLE、FEDERATED等。

1、查看MySQL数据的存储引擎有哪些

show engines;

2、查看当前的默认存储引擎（MySQL5.7 默认使用InnoDB）

show variables like '%default_storage_engine%';

3、在MySQL中，不需要整个服务器都是用同一种引擎，针对具体的要求，可以对每一个表使用不同的存储引擎，并且想要进一步优化，还可以自己编写一个存储引擎。

-- 创建新表时指定存储引擎
create table() engine=MyISAM;

4.2.1 InnoDB（推荐）

InnoDB是一个健壮的事务型存储引擎，这种存储引擎已经被很多互联网公司使用，为用户操作非常大的数据存储提供了一个强大的解决方案。InnoDB还引入了行级锁定和外键约束，在以下场合下，使用InnoDB是最理想的选择；

• 优点

  • Innodb引擎提供了对数据库ACID事务的支持，并且实现了SQL标准的四种隔离界别；
  • 支持多版本并发控制的行级锁，由于锁粒度小，写操作和更新操作并发高、速度快；
  • 支持自增长列；
  • 支持外键；
  • 适合于大容量数据库系统，支持自动灾难恢复；

• 缺点

  • 它没有保存表的行数，当select count(*) from table时需要扫描全表；

• 应用场景

  • 当需要使用数据库事务时，该引擎当然是首选，由于锁的粒度更小，写操作不会锁定全表，所以在并发较高时，使用Innodb引擎会提升效率；
  • 更新密集的表，InnoDB存储引擎特别适合处理多重并发的更新请求；

4.2.2 MyISAM

MyISAM引擎不支持事务、也不支持外键，优势是访问速度快，对事务完整性没有要求或者以select、insert为主的应用基本上可以用这个引擎来创建表

• 优点
  • MyISAM表是独立于操作系统的，这说明可以轻松地将其从windows服务器移植到Linux服务器；
  • MyISAM存储引擎在查询大量数据时非常迅速，这是它最突出的优点；
  • 另外进行大批量插入操作时执行速度也比较快；
• 缺点
  • MyISAM表没有提供对数据库事务的支持；
  • 不支持行级锁和外键；
  • 不适合用于经常update（更新）的表，效率低；
• 应用场景
  • 以读为主的业务，例如：图片信息数据库，博客数据库，商品库等业务；
  • 对数据一致性要求不是非常高的业务（不支持事务）；
  • 硬件资源比较差的机器可以用MyISAM（占用资源少）

4.2.3 MEMORY

MEMORY的特点是将表中的数据放在内存中，适用于存储临时数据的临时表和数据仓库中的维度表；

• 优点
  • memory类型的表访问非常的快，因为它的数据是存放在内存中的；
• 缺点
  • 一旦服务关闭，表中的数据就会丢失掉；
  • 只支持表锁，并发性能差，不支持TEXT和BLOB列类型，存储varchar时是按照char的方式；
• 应用场景
  • 目标数据较小，而且被非常频繁的访问；
  • 如果数据是临时的，而且要求必须立即可用，那么就可以存放在内存表中；
  • 存储在Memory表中的数据如果突然丢失，不会对应用服务产生实质的负面影响；

4.3 如何选择存储引擎

不同的存储引擎都有各自的特点，以适应不同的需求，如表所示。为了做出选择，首先要考虑每一个存储引擎提供了哪些不同的功能。

特征	InnoDB	MyISAM	MEMORY
存储限制	64TB	No	Yes
支持事务	Yes	No	No
锁机制	行锁	表锁	表锁
B树索引	Yes	Yes	Yes
哈希索引	Yes	No	Yes
外键支持	Yes	No	No
存储空间消耗	高	低	低
内存消耗	高	低	高
批量数据写入效率	慢	快	快

提供几个选择标准，然后按照标准，选择对应的存储引擎

• 是否需要支持事务；
• 崩溃恢复，能否接收崩溃；
• 是否需要外键支持；
• 存储的限制；
• 对索引和缓存的支持；

5. MySQL索引优化

5.1 索引简介

5.1.1 什么是索引

索引就是排好序的，帮助我们进行快速查找的数据结构。

简单来说，索引就是一种将数据库中的记录按照特殊形式存储的数据结构。通过索引，能够显著地提高数据查询的效率，从而提升服务器的性能。

专业一点来说，索引是一个排好序的列表，在这个列表中存储着索引的值和包含这个值的数据所在行的物理地址。在数据库十分庞大的时候，索引可以大大加快查询的速度，这是因为使用索引后可以不用扫描全表来定位某行的数据，而是先通过索引表找到该行数据对应的物理地址然后访问相应的数据。

1、没有用索引时执行select * from table1 where name = ，数据从磁盘一条一条拿取最终找到结果，效率低下；
2、为了加快查找，可以维护一个二叉树，左侧节点小于父节点，右侧节点大于父节点，每个节点分别保存字段数据和一个指向对应数据记录物理地址的指针；
3、查找时，就可以使用二叉树查找获取相应的数据，从而快速检索出符合条件的记录；

一般来说索引本身也比较大，不可能全部保存在内存中，因此索引通常是以索引文件的形式存储在磁盘上。、

5.1.2 索引的种类

• 普通索引
  • 这是最基本的索引类型，基于普通字段建立的索引，没有任何限制。

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 table tablename([...],primary key (字段名));

alter table tablename add primary key (字段名);

• 复合索引
  • 用户可以在多个列上建立索引，这种索引叫做复合索引（组合索引）。复合索引可以代替多个单一索引，相比多个单一索引复合索引所需的开销更小。

create index <索引的名字> on tablename(字段名1，字段名2...);

alter table tablename add index [索引的名字](字段名1，字段名2...);

create table tablename([...],index [索引的名字](字段名1，字段名2...));

复合索引使用注意事项：
1. 何时使用复合索引，要根据where条件建索引，注意不要过多使用索引，过多使用会对更新操作效率有很大影响。
2. 如果表已经建立了(col1,col2)，就没有必要再单独建立（col1）;如果现在有（col1）索引，如果查询需要col1和col2条件，可以建立（col1，col2）复合索引，对于查询有一定提高。

• 全文索引
  • 查询操作在数量比较少时，可以使用like模糊查询，但是对于大量的文本数据检索，效率很低。如果使用全文索引，查询速度会比like快很多倍。在MySQL5.6以前的版本，只有MyISAM存储引擎支持全文索引，从MySQL5.6开始MyISAM和InnoDB存储引擎均支持。

create fulltext index <索引的名字> on tablename (字段名);

alter table tablename add fulltext [索引的名字] (字段名);

create table tablename([...],fullltext key [索引名字] (字段名));

全文索引方式有自然语言检索in natural language mode和布尔检索in boolean mode两种。

和常用的like模糊查询不同，全文索引有自己的语法格式，使用match和against关键字，比如：

select * from user where match(name) against('ab');

-- *表示通配符，只能在词的后面
select * from user where match(name) against('aa*' in boolean mode);

全文索引使用注意事项：

• 全文索引必须在字符串上、文本字段上建立。
• 全文索引字段必须在最小字符和最大字符之间的才会有效。（innodb：3-84；myisam：4-84）

5.1.3 索引的优势与劣势

• 优点

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

• 缺点

  • 创建索引和维护索引要耗费时间，这种时间随着数量的增加而增加；
  • 索引需要占物理空间，除了数据表占用数据空间之外，每一个索引还要占用一定的物理空间；
  • 当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，降低了数据的维护速度；

• 创建索引的原则

  • 在经常需要搜索的列上创建索引，可以加快搜索的速度；
  • 在作为主键的列上创建索引，强制该列的唯一性和组织表中数据的排列结构；
  • 在经常用在连接的列上，这些列主要是一些外键，可以加快连接的速度；
  • 在经常需要根据范围进行搜索的列上创建索引，因为索引已经排序，其指定的范围是连接的；
  • 在经常需要排序的列上创建索引，因为索引已经排序，这样查询可以利用索引的排序，加快排序查询时间；
  • 在经常使用在where子句中的列上面创建索引，加快条件的判断速度；

5.2 索引原理

MySQL中索引的常用数据结构有两种，一种是Hash，另一种是BTree。

5.2.1 HASH结构

Hash底层实现是Hash表来实现的，是根据键值<key,value>存储数据的结构。非常适合根据key查找value值，也就是单个key查询，或者说等值查询。

• 对于每一行数据，存储引擎都会对所有的索引列计算一个哈希码，哈希码是一个较小的值，并且不同键值的行计算出来的哈希码也不一样；
  
• Hash索引的缺点：
  • 哈希索引只包含哈希值和行指针，而不存储字段值，所以不能使用索引中的值来避免读取行；
  • 哈希索引数据并不是按照索引值顺序存储的，所以也就无法用于排序；
  • 哈希索引只支持等值比较查询，不支持任何范围查询和部分索引列匹配查询；
• Hash索引的优点：
  • 只需要做等值比较查询，而不包含排序或范围查询的要求，都适合使用哈希索引；
  • 访问哈希索引的数据非常快，除非有很多哈希冲突；

5.2.2 B+Tree结构

MySQL数据库索引采用的是B+Tree结构，在B-Tree结构上做了优化改造；

B+Tree结构

• 非叶子节点不存储data数据，只存储索引值，这样便于存储更多的索引值；
• 叶子结点包含了所有的索引值和data的数据；
• 叶子结点用指针连接，提高区间的访问性能；
  

B树索引的应用

• 全键值查询 where x=123
• 键值范围查询 where 45<x<123

5.3 EXPLAIN性能分析

5.3.1 EXPLAIN简介

概述
使用explain关键字可以模拟优化器执行sql查询语句，从而知道MySQL是如何处理你的SQL语句的。分析你的查询语句或是表结构的性能瓶颈。通过explain我们可以获取以下信息：

EXPLAIN的作用

• 表的读取顺序；（对应id）
• 数据读取操作的操作类型；（对应select_type）
• 哪些索引可以使用；（对应possible_keys）
• 哪些索引被实际使用；（对应key）
• 表直接的引用；（对应ref）
• 每张表有多少行被优化器查询；（对应rows）

EXPLAIN的入门

• explain使用：explain+sql语句，通过执行explain可以获取sql语句执行的相关信息；

explain select * from course;

5.3.2 EXPLAIN字段介绍

1、数据准备

-- 创建数据库
CREATE DATABASE test_explain CHARACTER SET 'utf8';

-- 创建表
CREATE TABLE L1(id INT PRIMARY KEY AUTO_INCREMENT,title VARCHAR(100) ); 
CREATE TABLE L2(id INT PRIMARY KEY AUTO_INCREMENT,title VARCHAR(100) );
CREATE TABLE L3(id INT PRIMARY KEY AUTO_INCREMENT,title VARCHAR(100) ); 
CREATE TABLE L4(id INT PRIMARY KEY AUTO_INCREMENT,title VARCHAR(100) );

-- 每张表插入3条数据
INSERT INTO L1(title) VALUES('lagou01'); 
INSERT INTO L2(title) VALUES('lagou02'); 
INSERT INTO L3(title) VALUES('lagou03'); 
INSERT INTO L4(title) VALUES('lagou04');

2、ID介绍
select查询的序列号，包含一组数字，表示查询中执行select子句或操作表的顺序；

• id相同，执行顺序由上至下

explain select * from l1,l2,l3 where l1.id=l2.id and l2.id=l3.id;

• id不同，如果是自查询，id的序号会递增，id值越大优先级越高，越先被执行；

explain select * from l2 where id = (select id from l1 where id = (select l3.id from l3 where l3.title='lagoou03'));

3、select_type和table介绍
查询类型，主要用于区别普通查询，联合查询，子查询等的复杂查询；

• simple：简单的select查询，查询中不包含子查询或者union；

explain select * from l1;

• primary：查询中若包含任何复杂的子部分，最外层查询被标记；

explain select * from l2 where id = (select id from l1 where id = (select l3.id from l3 where l3.title='lagoou03'));

• subquery：在select或where列表中包含了子查询；

explain select * from l2 where l2.id = (select id from l3 where l3.title = 'lagou03');

• derived：在from列表中包含的子查询被标记为derived（衍生），MySQL会递归执行这些子查询，把结果放到临时表中；
• union：如果第二个select出现在union之后，则被标记为union，如果union包含在from子句的子查询中，外层select被标记为derived;
• union result：union的结果；

6.MySQL锁机制

6.1 MySQL锁概念

1、锁的概念
数据库锁定机制简单来说，就是数据库为了保证数据的一致性，而使各种共享资源在被并发访问变得有序所设计的一种规则。

假设当前商品只有一件，两个用户同时购买，我们需要保证只有一个用户能下单成功；
因为购买行为是一组操作，这里需要使用事务控制，从获取商品数量、插入订单、到付款后插入付款信息、更新商品数量。在这个过程中，使用锁可以对有限的资源进行保护；

6.2 MySQL的锁分类

MySQL数据库由于其自身架构的特点，存在多种数据存储引擎，MySQL中不同的存储引擎支持不同的锁机制；

• MyISAM和MEMORY存储引擎采用的是表级锁；
• InnoDB存储引擎既支持行级锁，也支持表级锁，默认情况下采用行级锁；
• BDB采用的是页面锁，也支持表级锁；

按照数据操作的类型分：

• 读锁（共享锁）：针对同一份数据，多个读操作可以同时进行而不会互相影响；
• 写锁（排他锁）：当前写操作没有完成前，它会阻断其他写锁和读锁；

按照数据操作的力度分

• 表级锁：开销小，加锁块；不会出现死锁；锁定力度大，发生锁冲突的概率最高，并发度最低；
• 行级锁：开销大，加锁慢；会出现死锁；锁定力度最小，发生锁冲突的概率最低，并发度也最高；
• 页面锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般；

按照操作性能可分为乐观锁和悲观锁

• 乐观锁：一般的实现方式是对记录数据版本进行对比，在数据更新提交的时候才会进行冲突检测，如果发现冲突了，则提示错误信息；
• 悲观锁：在对一条数据修改的时候，为了避免同时被其他人修改，在修改数据之前先锁定，再修改的控制方式。共享锁和排他锁是悲观锁的不同实现，但都属于悲观锁范畴；

6.3 表级锁（偏读）

表级锁是MySQL中锁定粒度最大的一种锁，表示对当前操作的整张表加锁，它实现简单，资源消耗较少，被大部分MySQL引擎支持。最常使用的MyISAM与InnoDB都支持表级锁定；

表级锁定分为：表共享读锁（共享锁）与表独占写锁（排他锁）；

特点：开销小，加锁块；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低；

6.3.1 数据准备

-- 创建数据库
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;

6.3.2 加锁语法

查看表中加过的锁

-- 0表示没有加锁，当前的所有数据库表都没有加锁
show open tables;

-- 查询加锁的表，条件In_user 大于0
show open tables where In_use > 0 ;

手动增加表锁

-- 语法格式：lock table 表名1 read(write), 表名2 read(write), 其他;
-- 为mylock01加读锁(共享锁)，给mylock02加写锁(排他锁)
lock table mylock01 read,mylock02 write;

show open tables where In_use > 0;

释放锁，解除锁定

-- 方式1
unlock tables;

-- 方式2 找到锁进程，得到id
show processlist;
kill id;

注意：

• 对MyISAM表的读操作（读加锁），不会阻塞其他进程对同一表的读请求，但是会阻塞对同一表的写请求，只有当读锁释放后，才会执行其他进程的写操作。
• 对MyISAM表加写锁，会阻塞其他进程对同一表的读和写操作，只有当写锁释放后，才会执行其他进程的操作；