01-MySQL简介
一、MySQL 简介
MySQL是一个关系型数据库管理系统，由瑞典MySQL AB（创始人Michael Widenius）公司开发，2008被Sun收购（10亿美金），2009年Sun被Oracle收购。
MySQL是一种关联数据库管理系统，将数据保存在不同的表中，而不是将所有数据放在一个大仓库内，这样就增加了速度并提高了灵活性。
MySQL是开源的，所以你不需要支付额外的费用。
MySQL是可以定制的，采用了GPL（GNU General Public License）协议，你可以修改源码来开发自己的MySQL系统。
MySQL支持大型的数据库。可以处理拥有上千万条记录的大型数据库。
MySQL支持大型数据库，支持5000万条记录的数据仓库，32位系统表文件最大可支持4GB，64位系统支持最大的表文件为8TB。
MySQL使用标准的SQL数据语言形式。
MySQL可以允许运行在多个系统上，并且支持多种语言。这些编程语言包括C、C++、Python、Java、Perl、PHP、Eiffel、Ruby和Tcl等。
二、MySQL高手是怎样炼成的
1、角色
开发工程师、项目经理、技术经理、运维、DBA
2、职责
数据库内部结构和原理
数据库建模优化
数据库索引建立
SQL语句优化
SQL编程(自定义函数、存储过程、触发器、定时任务)
MySQL服务器的安装配置
数据库的性能监控分析与系统优化
各种参数常量设定
主从复制
分布式架构搭建、垂直切割和水平切割
数据迁移
容灾备份和恢复
shell或python等脚本语言开发
对开源数据库进行二次开发
02-MySQL安装-Docker方式
一、CentOS7安装Docker
1、安装需要的软件包
yy -utils提供了yy-config-manager相关功能，device-mapper-persistent-data和lvm2是设备映射器驱动程序所需要的。
yum install -y yum-utils \
device-mapper-persistent-data \
lvm2
2、设置docker下载镜像
设置阿里云镜像
yum-config-manager --add-repo http://mirrors.aliyun.com/docker-ce/linux/centos/docker-ce.repo
3、更新yum软件包索引
我们在更新或配置yum源之后，通常都会使用yum makecache 生成缓存，这个命令是将软件包信息提前在本地缓存一份，用来提高搜索安装软件的速度
yum makecache fast
4、安装docker ce
yum install -y docker-ce
5、启动docker
systemctl start docker
6、版本验证
docker version
7、设置开机启动
systemctl enable docker
二、Docker中安装MySQL
1、创建并运行容器
-d：守护式启动（交互式启动类似tomcat的默认启动方式，会使命令行挂起）
-p：设置端口映射
-v：设置数据卷目录（配置、日志、数据文件）
-e：设置环境变量，MYSQL_ROOT_PASSWORD指定root密码
#首先确保在宿主机环境下
docker run -d \
-p 3306:3306 \
-v /docker/mysql/conf:/etc/mysql/conf.d \
-v /docker/mysql/logs:/logs \
-v /docker/mysql/data:/var/lib/mysql \
-e MYSQL_ROOT_PASSWORD=123456 \
--name atguigu_mysql \
mysql:5.7
2、查看容器中的进程：docker top
docker top atguigu_mysql
3、进入容器内部的文件系统
docker exec -it atguigu_mysql /bin/bash
4、进入mysql命令行
mysql -uroot -p
5、执行MySQL命令
mysql> SHOW DATABASES;
6、退出MySQL命令行
mysql> quit
7、退出到宿主机
Ctrl+p, q
8、SQLyog连接测试
注意连接宿主机的ip地址
三、字符集
1、查看字符集
mysql> SHOW VARIABLES LIKE 'character%';
默认latin1字符编码，不支持中文
2、设置字符集
宿主机上创建mysql配置文件：vim /docker/mysql/conf/my.cnf
[client]
default-character-set=utf8
[mysqld]
character-set-server=utf8
3、重启MySQL
docker restart atguigu_mysql
如果服务无法启动，则可以查看日志，分析无法启动的原因
docker logs atguigu_mysql
四、什么是数据卷
1、需要解决的问题
出于效率等一系列原因，docker 容器的文件系统在宿主机上存在的方式很复杂，这会带来下面几个问题：
- 当容器删除时，容器中产生的数据将丢失：
- 例如，MySQL容器损坏无法启动，需要删除容器重新创建，那么数据库数据将会丢失
- 例如，MySQL容器损坏无法启动，需要删除容器重新创建，那么数据库数据将会丢失
- 无法在多个容器之间共享数据：
- 例如，Tomcat集群部署成功后，无法共享程序文件
- 例如，Tomcat集群部署成功后，无法共享程序文件
2、数据卷的概念
为了解决以上的问题，docker 引入了数据卷(volume) 机制。数据卷是存在于一个或多个容器中的特定文件或文件夹，这个文件或文件夹以独立于 docker 文件系统的形式存在于宿主机中。
-
当容器删除时，容器中的数据可以被持久化：
-
在多个容器之间共享数据：
数据卷的最大特点是：其生存周期独立于容器的生存周期。使用数据卷可以在多个容器之间共享数据。
MySQL(5.7)的默认配置文件是 /etc/mysql/my.cnf 文件。如果想要自定义配置，建议向 /etc/mysql/conf.d 目录中创建 .cnf 文件。新建的文件可以任意起名，只要保证后缀名是 cnf 即可。新建的文件中的配置项可以覆盖 /etc/mysql/my.cnf 中的配置项。
03-MySQL安装-rpm离线安装
一、下载
1、官网
2、下载
打开官网，点击DOWNLOADS
然后，点击MySQL Community(GPL) Downloads
MySQL分为社区版（Community Server）和企业版（Enterprise Server）
点击MySQL Community Server
在General Availability(GA) Releases中选择适合的版本
如果安装Windows 系统下的MySQL ，推荐下载MSI安装程序
Windows下的MySQL安装有两种安装程序
- mysql-installer-web-community-x.x.x.msi；安装时需要联网安装
- mysql-installer-community-x.x.x.msi；安装时离线安装即可
如果安装Linux系统下MySQL，官方给出多种安装方式
| 安装方式 | 特点 |
|---|---|
| rpm | 安装简单，灵活性差，无法灵活选择版本、升级 |
| rpm repository | 安装包极小，版本安装简单灵活，升级方便，需要联网安装 |
| 通用二进制包 | 安装比较复杂，灵活性高，平台通用性好 |
| 源码包 | 安装最复杂，时间长，参数设置灵活，性能好 |
目前最新版本为8.x.x。
本课程中使用5.7版本。
下载MySQL Community Server 5.7.28
这里没有CentOS7系统的版本，所以选择与之对应的Red Hat Enterprise Linux 7
直接点Download下载RPM Bundle全量包。包括了所有组件：
下载的tar包，用压缩工具打开
解压后rpm安装包
二、卸载自带MySQL
1、查看是否安装过MySQL
-i忽略大小写
rpm -qa | grep -i mysql
rpm -qa | grep -i mariadb
2、卸载MySQL
如果上一个步骤查询到有已经安装的相关软件，可以使用以下命令将相关的软件删除
–nodeps 排除依赖，否则别的软件依赖mysql，无法删除
rpm -e --nodeps mysql57-community-release
rpm -e --nodeps mysql-community-server
rpm -e --nodeps mysql-community-client
rpm -e --nodeps mysql-community-common
rpm -e --nodeps mysql-community-libs
rpm -e --nodeps mariadb-libs
3、检查/tmp临时目权限
ll -a /
权限不够则授权
chmod -R 777 /tmp
4、执行查询命令检查依赖
以下是安装mysql必要的依赖项，安装mysql之前需要确保Linux系统中已经安装了以下依赖项。如果不存在需要到centos安装盘里进行rpm安装。如果安装linux时包含了图形化界面，那么这些依赖都是安装好的。
rpm -qa|grep libaio
rpm -qa|grep net-tools
如果存在相关资源包，则查询结果如下
三、安装
1、解压安装程序
将安装程序 mysql-5.7.28-1.el7.x86_64.rpm-bundle.tar 拷贝到/opt目录下，并解压
cd /opt
tar -xvf mysql-5.7.28-1.el7.x86_64.rpm-bundle.tar
2、执行安装命令
在mysql的安装文件目录下执行：（必须按照顺序执行）
rpm -ivh mysql-community-common-5.7.28-1.el7.x86_64.rpm
rpm -ivh mysql-community-libs-5.7.28-1.el7.x86_64.rpm
rpm -ivh mysql-community-client-5.7.28-1.el7.x86_64.rpm
rpm -ivh mysql-community-server-5.7.28-1.el7.x86_64.rpm
-i, --install 安装软件包
-v, --verbose 提供更多的详细信息输出
-h, --hash 软件包安装的时候列出哈希标记 (和 -v 一起使用效果更好)，展示进度条
3、查看是否安装成功
rpm -qa|grep -i mysql
4、查看mysql版本
mysqladmin --version
5、安装后的目录结构
| 参数 | 路径 | 解释 | 备注 |
|---|---|---|---|
| - -basedir | /usr/bin | 相关命令目录 | mysqladmin mysqldump等命令 |
| - -datadir | /var/lib/mysql/ | mysql数据库文件的存放路径 | |
| - -plugin-dir | /usr/lib64/mysql/plugin | mysql插件存放路径 | |
| - -log-error | /var/log/mysqld.log | mysql错误日志路径 | |
| - -pid-file | /var/run/mysqld/mysqld.pid | 进程pid文件 | |
| - -socket | /var/lib/mysql/mysql.sock | 本地连接时用的unix套接字文件 | |
| - | /usr/share/mysql | 配置文件目录 | mysql脚本及配置文件 |
| - | /etc/systemd/system/multi-user.target.wants/mysqld.service | 服务启停相关脚本 | |
| - | /etc/my.cnf | mysql配置文件 | cat /etc/my.cnf |
6、启动
#启动
systemctl start mysqld
#关闭
systemctl stop mysqld
#重启
systemctl restart mysqld
#查看状态
systemctl status mysqld
7、设置开机启动
#查看服务是否自动启动（是：enabled | 否：disabled）
systemctl list-unit-files|grep mysqld.service
#设置开机启动：如不是enabled可以运行如下命令设置自启动
systemctl enable mysqld
#重新加载服务配置
systemctl daemon-reload
#如果希望不进行自启动，运行如下命令设置
systemctl disable mysqld
8、查看进程
ps -ef | grep -i mysql
四、配置密码
1、设置root密码
mysql安装完成之后，在/var/log/mysqld.log文件中给root生成了一个默认密码。通过下面的方式找到root默认密码，然后登录mysql。
grep 'temporary password' /var/log/mysqld.log
修改密码：因为初始化密码默认是过期的，所以查看数据库会报错
mysql -u root -p #然后输入上面的默认密码
--注意：需要localhost本地执行，远程执行默认无法登录，报告socket错误，修改密码后远程可登录
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY '123456My!';
--或者
mysql> SET password FOR 'root'@'localhost'= password('123456My!');
注意：mysql5.7默认安装了密码安全检查插件（validate_password），默认密码检查策略要求密码必须包含：大小写字母、数字和特殊符号，并且长度不能少于8位。否则会提示ERROR 1819 (HY000): Your password does not satisfy the current policy requirements错误，如下图所示：
使用quit退出，使用新密码重新登录。
2、关于密码策略
如果不需要密码策略，在/etc/my.cnf文件中的最后添加如下配置禁用即可：
validate_password = off
重新启动mysql服务使配置生效：
systemctl restart mysqld
3、忘记root密码
在/etc/my.cnf 文件[mysqld]的段中加上一句：skip-grant-tables 保存并且退出vim
mysql -u root
mysql> UPDATE mysql.user SET authentication_string = password('123456') WHERE User='root' AND Host = 'localhost';
mysql> FLUSH PRIVILEGES
五、字符集
1、查看字符集
创建数据库atguigudb，插入中文数据，不支持中文
mysql> CREATE DATABASE atguigudb;
mysql> USE atguigudb;
mysql> CREATE TABLE mytbl (id INT, `name` VARCHAR(8));
mysql> INSERT INTO mytbl(id, `name`) VALUES (1, '张三');
mysql> SELECT * FROM mytbl;
查看字符集
mysql> SHOW VARIABLES LIKE 'character%';
默认latin1字符编码，不支持中文
2、设置字符集
mysql配置文件：vim /etc/my.cnf
[client]
default-character-set=utf8
[mysqld]
character-set-server=utf8
3、重启服务
systemctl restart mysqld
4、修改已有数据库符集
已生成的库表字符集如何变更？
--修改已创建数据库的字符集
mysql> ALTER DATABASE 数据库名 CHARACTER SET 'utf8';
--修改已创建数据表的字符集
mysql> ALTER TABLE 表名 CONVERT TO CHARACTER SET 'utf8';
但是数据库中已存在的数据如果是用非’utf8’编码的话，数据本身不会发生改变，需要将数据删除，重新录入。
六、客户端远程连接
1、SQLyog连接效果
2、设置远程访问权限
默认root账户只能本机访问，需要设置其远程访问权限，或者，创建普通用户进行授权访问。
下面为放行root的远程连接权限的方式：
mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY '123456' WITH GRANT OPTION;
3、重启服务器
systemctl restart mysqld
4、防火墙设置
远程访问前必须关闭防火墙或开放3306端口
#查看防火墙状态
firewall-cmd --state
#关闭防火墙
systemctl stop firewalld.service
#禁用开机启动
systemctl disable firewalld.service
启用防火墙并开放3306端口
#查看防火墙状态
firewall-cmd --state
#开启防火墙
systemctl start firewalld.service
#设置开机自启
systemctl enable firewalld.service
#设置开放的端口号
firewall-cmd --zone=public --add-port=3306/tcp --permanent
#重启防火墙
firewall-cmd --reload
#查看开放的端口
firewall-cmd --list-ports
04-MySQL管理与配置
一、用户管理
1、mysql库中的user表
- host ： 表示连接类型
- % ：表示允许所有远程通过 TCP方式的连接
- IP 地址： 如 (192.168.1.2,127.0.0.1)，允许指定ip地址进行的TCP方式的连接
- 机器名：允许指定网络中的机器名进行的TCP方式的连接
- ::1：IPv6的本地ip地址，等同于IPv4的 127.0.0.1，允许TCP方式的连接
- localhost：本地方式通过命令行方式的连接 ，比如mysql -u xxx -p xxx 方式的连接。
- user ： 表示用户名
- 同一用户通过不同方式链接的权限是不一样的。
- password ： 密码
- 所有密码串通过 password(明文字符串) 生成的密文字符串。加密算法为MYSQLSHA1，不可逆 。
- mysql 5.7 的密码保存到 authentication_string 字段中，不再使用password 字段。
- select_priv , insert_priv等
- 为该用户所拥有的权限。
2、创建用户
表示创建名称为helen的用户，密码设为123456；
mysql> CREATE USER helen IDENTIFIED BY '123456';
3、设置密码
修改当前用户的密码：
mysql> SET password = PASSWORD('123456')
修改某个用户的密码：
--mysql5.5
mysql> UPDATE mysql.user SET password = PASSWORD('1234') WHERE user = 'helen';
--mysql5.7
mysql> UPDATE mysql.user SET authentication_string = PASSWORD('1234') WHERE user = 'helen';
--必须用该命令才能生效。否则，需重启服务。
mysql> FLUSH PRIVILEGES;
4、修改用户
修改用户名：
mysql> UPDATE mysql.user SET user = 'annie' WHERE user = 'helen';
5、删除用户
不要通过delete from user u where user=‘annie’ 进行删除，系统会有残留信息保留。
mysql> DROP user annie;
二、权限管理
1、授权
语法：
GRANT 权限1, 权限2, …权限n ON 数据库名称.表名称 TO '用户名'@'用户地址' IDENTIFIED BY '连接口令';
如果发现没有该用户，则会直接新建一个用户。如：
--给helen授予atguigudb这个库下的所有表的插删改查的权限，用户可以远程访问数据库。
mysql> GRANT SELECT, INSERT, DELETE, UPDATE ON atguigudb.* TO 'helen'@'%' IDENTIFIED BY '123456';
--给joe授予对所有库所有表的全部权限，密码设为123.
mysql> GRANT ALL PRIVILEGES ON *.* TO 'joe'@'%' IDENTIFIED BY '123';
2、查看权限
--查看当前用户权限：
mysql> SHOW GRANTS;
--查看某用户的全局权限：
mysql> SELECT * FROM mysql.user WHERE user = 'helen' \G;
3、回收权限
语法：
mysql> REVOKE 权限1, 权限2, …权限n ON 数据库名称.表名称 FROM '用户名'@'用户地址';
如：
--收回全库全表的所有权限
mysql> REVOKE ALL PRIVILEGES ON *.* FROM 'joe'@'%';
--收回helen的mysql库下的所有表的插删改查权限
mysql> REVOKE SELECT, INSERT, UPDATE, DELETE ON *.* FROM 'helen'@'%';
三、sql_mode
1、设置sql_mode
sql_mode设置是否允许一些非法操作，比如允许一些非法数据的插入。
在生产环境必须将这个值设置为严格模式，所以开发、测试环境的数据库也必须要设置，这样在开发测试阶段就可以发现问题。
查询sql_mode的值
mysql> SHOW VARIABLES LIKE 'sql_mode';
（mysql5.7.5版本前，这个值默认是空的）
设置sql_mode的值：当前会话
mysql> SET SESSION sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
设置sql_mode的值：所有会话，重启mysql服务后失效
mysql> SET sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
在 /etc/my.cnf 中配置sql_mode：永久生效
[mysqld]
#set the SQL mode to strict
#sql-mode="modes..."
sql-mode = "ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
2、sql_mode常用值
- ONLY_FULL_GROUP_BY：对于GROUP BY聚合操作，SELECT子句中只能包含函数和 GROUP BY 中出现的字段。
- NO_AUTO_VALUE_ON_ZERO：该值影响自增长列的插入。默认设置下，插入0或NULL代表生成下一个自增长值。如果用户希望插入的值为0，而该列又是自增长的，那么去掉该选项。
- STRICT_ALL_TABLES,STRICT_TRANS_TABLES：
- 对于支持事务的表，这两种模式是一样的：如果发现某个值缺失或非法，MySQL将抛出错误，语句会停止运行并回滚。
- 对于不支持事务的表，这两种模式的效果：
- 如果在插入或修第一个数据行时就发现某个值非法或缺失，那该语句直接抛错，语句停止执行。这个和支持事务的数据表行为是一样的。
- 如果在插入或修改第n个（n>1)数据行时才发现错误，那就会出现下面的情况：
- 在STRICT_ALL_TABLES模式下，停止语句执行，存在部分更新的问题
- 在STRICT_TRANS_TABLES模式下，MySQL将继续执行该语句避免“部分更新问题”，对每个非法值将其转换为最接近的合法值。
- *** NO_ZERO_IN_DATE：在严格模式下，不允许日期和月份为零。
- NO_ZERO_DATE：设置该值，mysql数据库不允许插入零日期，插入零日期会抛出错误而不是警告。
- ERROR_FOR_DIVISION_BY_ZERO：在INSERT或UPDATE过程中，如果数据被零除，则产生错误而非警告。如果未给出该模式，那么数据被零除时MySQL返回NULL。
- NO_AUTO_CREATE_USER：禁止GRANT创建密码为空的用户。
- NO_ENGINE_SUBSTITUTION：如果需要的存储引擎被禁用或不存在，那么抛出错误。不设置此值时，用默认的存储引擎替代。
3、示例：错误开发演示
建表并插入数据：
CREATE TABLE employee(id INT,NAME VARCHAR(16),age INT,dept INT);
INSERT INTO employee VALUES(1,'zhang3',33,101);
INSERT INTO employee VALUES(2,'li4',34,101);
INSERT INTO employee VALUES(3,'wang5',34,102);
INSERT INTO employee VALUES(4,'zhao6',34,102);
INSERT INTO employee VALUES(5,'tian7',36,102);
需求：查询每个部门年龄最大的人
SELECT name, dept, MAX(age) FROM employee GROUP BY dept;
以上查询语句在 “ONLY_FULL_GROUP_BY” 模式下查询出错，因为select子句中的name列并没有出现在group by子句中，也没有出现在函数中
在非 “ONLY_FULL_GROUP_BY” 模式下可以正常执行，但是得到的是错误的结果
正确的查询方式：查询应该分开两部分来查
1、查询每个部门最大的年龄
2、查询人
正确的语句：
SELECT e.*
FROM employee e
INNER JOIN (SELECT dept, MAX(age) age FROM employee GROUP BY dept) AS maxage
ON e.dept = maxage.dept AND e.age = maxage.age
05-MySQL逻辑架构
一、逻辑架构概览
和其它数据库相比，MySQL有点与众不同，它的架构可以在多种不同场景中应用并发挥良好作用。主要体现在存储引擎的架构上，插件式的存储引擎架构将查询处理和其它的系统任务以及数据的存储提取相分离。这种架构可以根据业务的需求和实际需要选择合适的存储引擎。
1、连接层
最上层是一些客户端和连接服务，包含本地sock通信和大多数基于客户端/服务端工具实现的类似于tcp/ip的通信。
主要完成一些类似于连接处理、授权认证、及相关的安全方案。
连接池
在该层上引入了线程池的概念，为通过认证安全接入的客户端提供线程。
同样在该层上可以实现基于SSL的安全链接。服务器也会为安全接入的每个客户端验证它所具有的操作权限。
2、服务层
主要完成大多数的核心服务功能，如SQL接口，并完成缓存的查询，SQL的分析和优化及部分内置函数的执行。
所有跨存储引擎的功能也在这一层实现，如过程、函数等。
在该层，服务器会解析查询并创建相应的内部解析树，并对其完成相应的优化：如确定查询表的顺序，是否利用索引等，最后生成相应的执行操作。
如果是select语句，服务器还会查询内部的缓存。如果缓存空间足够大，这样在解决大量读操作的环境中能够很好的提升系统的性能。
- Cache 和 Buffer： 缓存和缓冲区。
- 如果查询缓存有命中的查询结果，查询语句就可以直接去查询缓存中取数据。
- 这个缓存机制是由一系列小缓存组成的。比如表缓存，记录缓存，key缓存，权限缓存等
- SQL Interface：SQL接口
- 接受用户的SQL命令，并且返回用户需要查询的结果。比如select from就是调用SQL Interface
- Parser：解析器
- SQL命令传递到解析器的时候会被解析器验证和解析。
- Optimizer：查询优化器。
- SQL语句在查询之前会使用查询优化器对查询进行优化。
- 用一个例子就可以理解： select uid, name from user where gender = 1;
- 优化器来决定先投影还是先过滤。
- Management Serveices & Utilities： 系统管理和控制工具
3、引擎层
存储引擎层，存储引擎真正的负责了MySQL中数据的存储和提取，服务器通过API与存储引擎进行通信。不同的存储引擎具有的功能不同，这样我们可以根据自己的实际需要进行选取。后面介绍MyISAM和InnoDB
4、存储层
数据存储层，主要是将数据存储在运行于裸设备的文件系统之上，并完成与存储引擎的交互。
二、sql的执行周期
1、开启查询缓存
修改配置文件：vim /etc/my.cnf
新增一行：query_cache_type=1
重启MySQL：systemctl restart mysqld
2、开启查询执行计划
注意：使用MySQL命令行客户端测试
mysql> SHOW VARIABLES LIKE '%profiling%'; -- profiling = OFF
mysql> SET profiling = 1; -- profiling = ON
3、执行语句两次
mysql> SELECT * FROM atguigudb.employee WHERE id = 5;
4、显示最近执行的语句
mysql> SHOW PROFILES;
第二次执行的时间较少，因为命中了缓存
注意：SQL必须是一致的，否则，不能命中缓存。
如：以下两条SQL语句虽然查询结果一致，但第二条查询并不会命中缓存。
SELECT * FROM atguigudb.employee WHERE id = 2
SELECT * FROM atguigudb.employee WHERE id > 1 AND id < 3
5、显示执行计划
显示第一次执行查询的查询计划
mysql> SHOW PROFILE cpu, block io FOR QUERY 2;
显示第二次执行查询的查询计划
mysql> SHOW PROFILE cpu, block io FOR QUERY 3;
MySQL的查询流程大致是：
- 首先，MySQL客户端通过协议与MySQL服务器建连接，发送查询语句，先检查查询缓存，如果命中，直接返回结果，否则进行语句解析，也就是说，在解析查询之前，服务器会先访问查询缓存(query cache)——它存储SELECT语句以及相应的查询结果集。
- 如果某个查询结果已经位于缓存中，服务器就不会再对查询进行解析、优化、以及执行。它仅仅将缓存中的结果返回给用户，这会大大提高系统的性能。
- 语法解析器和预处理：首先MySQL通过关键字将SQL语句进行解析，并生成一颗对应的“解析树”。
- MySQL解析器将使用MySQL语法规则验证和解析查询；
- 预处理器则根据一些MySQL规则进一步检查解析树是否合法。
- 查询优化器当解析树被认为是合法的了，并且由优化器将其转化成执行计划。
- 一条查询可以有很多种执行方式，最后都返回相同的结果。优化器的作用就是找到这其中最好的执行计划。
- 然后，MySQL默认使用的BTREE索引，并且一个大致方向是：无论怎么折腾sql，至少在目前来说，MySQL最多只用到表中的一个索引。
06-MySQL存储引擎
一、查看命令
1、查看MySQL提供什么存储引擎
mysql> SHOW ENGINES;
2、查看默认的存储引擎
mysql> SHOW VARIABLES LIKE '%storage_engine%';
二、各种引擎介绍
1、InnoDB存储引擎
- 大于等于5.5之后，默认采用InnoDB引擎。
- InnoDB是MySQL的默认事务型引擎，它被设计用来处理大量的短期(short-lived)事务。可以确保事务的完整提交(Commit)和回滚(Rollback)。
- 除了增加和查询外，还需要更新，删除操作，那么，应优选选择InnoDB存储引擎。
- 除非有非常特别的原因需要使用其他的存储引擎，否则应该优先考虑InnoDB引擎。
2、MyISAM存储引擎
- MyISAM提供了大量的特性，包括全文索引、压缩、空间函数(GIS)等，但MyISAM不支持事务和行级锁，有一个毫无疑问的缺陷就是崩溃后无法安全恢复。
- 5.5之前默认的存储引擎
- 数据文件结构：
- .frm 存储定义表
- .MYD 存储数据
- .MYI 存储索引
- 特别注意：
- 静态表字段都是非变长字段，存储占用空间比动态表多。存储数据时会按照列宽补足空格，但在访问时候并不会得到这些空格。
- 如果存储数据本身后面有空格，那么，查询时也会被去掉。
- 如果存储数据本身前面有空格，则查询时不会被去掉。
- 在没有where条件情况下统计表count(*)数量，不需要全表扫描，而是直接获取保存好的值。
- 静态表字段都是非变长字段，存储占用空间比动态表多。存储数据时会按照列宽补足空格，但在访问时候并不会得到这些空格。
3、Archive引擎
- Archive档案存储引擎只支持INSERT和SELECT操作，在MySQL5.1之前不支持索引。
- Archive表适合日志和数据采集（档案）类应用。
- 根据英文的测试结论来看，Archive表比MyISAM表要小大约75%，比支持事务处理的InnoDB表小大约83%。
4、Blackhole引擎
- Blackhole引擎没有实现任何存储机制，它会丢弃所有插入的数据，不做任何保存。
- 但服务器会记录Blackhole表的日志，所以可以用于复制数据到备库，或者简单地记录到日志。但这种应用方式会碰到很多问题，因此并不推荐。
5、CSV引擎
- CSV引擎可以将普通的CSV文件作为MySQL的表来处理，但不支持索引。
- CSV引擎可以作为一种数据交换的机制，非常有用。
- CSV存储的数据直接可以在操作系统里，用文本编辑器，或者excel读取。
6、Memory引擎
- 如果需要快速地访问数据，并且这些数据不会被修改，重启以后丢失也没有关系，那么使用Memory表是非常有用。
- Memory表至少比MyISAM表要快一个数量级。
7、Federated引擎
- Federated引擎是访问其他MySQL服务器的一个代理，尽管该引擎看起来提供了一种很好的跨服务器的灵活性，但也经常带来问题，因此默认是禁用的。
三、MyISAM和InnoDB
| 对比项 | MyISAM | InnoDB |
|---|---|---|
| 外键 | 不支持 | 支持 |
| 事务 | 不支持 | 支持 |
| 行表锁 | 表锁，即使操作一条记录也会锁住整个表，不适合高并发的操作 | 行锁,操作时只锁某一行，不对其它行有影响，适合高并发的操作 |
| 缓存 | 只缓存索引，不缓存真实数据 | 不仅缓存索引还要缓存真实数据，对内存要求较高，而且内存大小对性能有决定性的影响 |
| 自带系统表使用 | Y | N |
| 关注点 | 性能：节省资源、消耗少、简单业务 | 事务：并发写、事务、更大资源 |
| 默认安装 | Y | Y |
| 默认使用 | N | Y |
阿里巴巴、淘宝用哪个
- Percona 为 MySQL 数据库服务器进行了改进，在功能和性能上较 MySQL 有很显著的提升。
- 该版本提升了在高负载情况下的 InnoDB 的性能、为 DBA 提供一些非常有用的性能诊断工具；另外有更多的参数和命令来控制服务器行为。
- XtraDB引擎完全可以替代InnoDB,并且在性能和并发上做得更好,
- 阿里巴巴大部分 MySQL 数据库其实使用的Percona的原型加以修改。
- AliSql+AliRedis
扫一扫
2934
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
