MySQL体系结构与管理

MySQL介绍与安装

1.1 什么是数据？

1 2 3 4 5 6 7

数据：文字、图片、视频。。。人类认知的数据表现方式 计算机：二进制、16进制的机器语言 基于数据的重要性和复杂性的不同，我们可能有不同的管理方式。 哪些数据是适合存储到数据库的呢？ 重要性比较高的 关系较复杂的数据

1.2 什么是数据库管理系统（DBMS）？

1 2 3 4 5 6

RDBMS: 关系型数据库管理系统 比较适合于,安全级别要求高的数据以及关系较复杂的数据 NoSQL:非关系型数据库管理系统 适合于高性能存取数据,一般是配合RDBMS进行使用的 针对大数据处理分析,分布式架构更加擅长

1.3 数据库管理系统种类

1 2 3 4 5 6

RDBMS:Relational Database Management System,关系数据库管理系统 MySQL 、Oracle、MSSQL（SQL Server）、PostgreSQL NoSQL：Not Only SQL 键-值（key-value）：Redis, memcached 文档（document）：Mongodb

二 MySQL简介及产品线

2.1 MySQL行业主流版本

1 2 3 4

5.5 ：企业基本不用了 5.6 ：****：四星 5.6.36 5.6.38 5.6.40 ...5.6.46 5.7 ：*****：五星 5.7.20 5.7.22 ....5.7.28 5.7.30(5.7版本最新) 8.0(最新) ：**：两星 8.0.11 开始，之前都是测试版 ...8.0.20

2.2 企业版本选择(MySQL分支版本)

1 2 3 4 5 6 7 8 9 10

# 分支 Oracle: MySQL官方版 红帽 : MariaDB：红帽维护的分支 Percona: PerconaDB：Percona公司维护的分支 # 版本 企业版：收费 社区版：开源免费

2.3 版本选择建议

1 2 3

1.一般选择官方主流版本:5.6,5.7 2.GA（稳定发布版） 3.6-12月的产品版本

2.4 课程版本：

1	5.7.30 二进制安装，其他安装方式自己扩展

2.5 下载（社区版源码安装）

1 2 3 4 5 6 7 8 9 10 11

企业版：Enterprise 互联网行业一般选择社区版 源码包：source code .tar.gz ## 下载-官网 1 https://www.mysql.com/ 2 选择downloads -https://www.mysql.com/downloads/ 3 底部选择：MySQL Community (GPL) Downloads » 4 只能下到最新版：MySQL Community Server 5 选择Download Archives 6 选择MySQL Community Server 7 选择版本，平台下载即可

2.6 安装方式

1 2 3

1 源码安装：编译安装，非常慢，需要研究源码 2 rmp，yum 安装：配置官方yum源，直接yum install 3 通用二进制版：解压即用，绿色版（企业用）

源码安装

rpm方式

yum方式

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22

# 配置源 # Enable to use MySQL 5.7 [mysql57-community] name=MySQL 5.7 Community Server baseurl=http://repo.mysql.com/yum/mysql-5.7-community/el/6/$basearch/ enabled=1 gpgcheck=1 gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql [mysql80-community] name=MySQL 8.0 Community Server baseurl=http://repo.mysql.com/yum/mysql-8.0-community/el/6/$basearch/ enabled=1 gpgcheck=1 gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql # sudo yum install mysql-community-server # sudo systemctl start mysqld.service # sudo systemctl status mysqld.service # sudo grep 'temporary password' /var/log/mysqld.log # mysql -uroot -p # ALTER USER 'root'@'localhost' IDENTIFIED BY 'MyNewPass4!';

通用二进制安装

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100

# 1 环境准备 一台centos 7.6虚拟机，能上网， # 2 清理一下环境 rpm -qa |grep mariadb yum remove mariadb-libs # 3 创建用户和组 useradd mysql useradd mysql -s /sbin/nologin # 不能登录，没有家目录 id mysql # 4 创建相关目录（软件目录，数据目录） # 软件目录 mkdir -p /app/database/ # 数据目录 mkdir -p /data/3306 # 日志目录 mkdir -p /binlog/3306 # mkdir -p /app/database/ /data/3306/ /binlog/3306 # 生产建议：软件和数据和日志不要放在同一个地方，一般数据放在另一块磁盘上 # 5 设置权限 chown -R mysql.mysql /app/ /data/ /binlog # 6 上传并解压软件到 /app/databases/ wget https://downloads.mysql.com/archives/get/p/23/file/mysql-5.7.30-linux-glibc2.12-x86_64.tar.gz tar xzf mysql-5.7.30-linux-glibc2.12-x86_64.tar.gz # 7 建立软连接 ln -s mysql-5.7.30-linux-glibc2.12-x86_64 mysql # 8 设置环境变量 -bin # mysql执行的命令 -support-files # 存放脚本 # 把bin目录设置到环境变量中 vim /etc/profile #添加一行 export PATH=/app/database/mysql/bin:$PATH # 生效配置 source /etc/profile # 验证 mysql -V # 9 初始化系统库表（系统数据，数据库要启动，必须依赖一些表，系统表） mysqld --initialize-insecure --user=mysql --basedir=/app/database/mysql --datadir=/data/3306/ #会报错： mysqld: error while loading shared libraries: libaio.so.1: cannot open shared object file: No such file or directory 报错原因: Linux系统中缺少libaio-devel 软件包 #解决: yum install -y libaio-devel # 如果再执行一下上面的初始化命令，会报data目录不为空，需要删除数据目录再执行 #########补充： # 1 初始化数据自带密码 # 5.7开始,MySQL加入了全新的 密码的安全机制: 1.初始化完成后,会生成临时密码(显示到屏幕上,并且会往日志中记一份) 2.密码复杂度:长度:超过12位? 复杂度:字符混乱组合 3.密码过期时间180天 # 初始化数据，初始化管理员的临时密码 mysqld --initialize --user=mysql --basedir=/app/database/mysql --datadir=/data/3306/ # 会打印出密码，记住，然后需要修改root密码后才能正常使用数据库 # S44GNgK!h.qg #2 5.6初始化的区别 ./mysql/scripts/mysql_install_db --user=mysql --basedir=/app/database/mysql --datadir=/data/3306/ # 10 编写默认配置文件 vim /etc/my.cnf [mysqld] user=mysql basedir=/app/database/mysql datadir=/data/3306/ server_id=6 port=3306 socket=/tmp/mysql.sock [mysql] socket=/tmp/mysql.sock # 11 配置启动脚本 cd /app/database/mysql/support-files ./mysql.server start # copymysql的启动脚本至系统管理目录中 cp mysql.server /etc/init.d/mysqld # centos6中，使用service 启动 service mysqld start service mysqld stop service mysqld restart # centos7中：使用systemctl启动 chkconfig --add mysqld # 把mysqld的脚本添加到systemctl的管理中 systemctl start mysqld ###至此，启动方式可以是 service mysqld start systemctl start mysqld /etc/init.d/mysqld start /app/database/mysql/support-files/mysql.server start # 12 在登陆前修改密码（使用mysqld --initialize 初始化的数据） mysqladmin -uroot -p password lqz1234. 输入原始密码

 

MySQL体系结构与管理

一 体系结构

1.1 C/S(客户端/服务端)模型介绍

1 2 3 4

TCP/IP方式（远程、本地）： mysql -uroot -poldboy123 -h 10.0.0.51 -P3306 Socket方式(仅本地)： mysql -uroot -poldboy123 -S /tmp/mysql.sock

1.2 实例介绍

1 2	实例=mysqld后台守护进程+Master Thread +干活的Thread+预分配的内存 公司=老板+经理+员工+办公室

1.3 mysqld程序运行原理

1.3.1 mysqld程序结构

1	# cd /app/database/mysql/bin 路径下的mysqld程序

1.3.2 一条SQL语句的执行过程

1.3.2.1 连接层

1 2 3 4 5 6

（1）提供连接协议：TCP/IP 、SOCKET （2）提供验证：用户、密码，IP，SOCKET （3）提供专用连接线程：接收用户SQL，返回结果 通过以下语句可以查看到连接线程基本情况 mysql> show processlist;

1.3.2.2 SQL层 （重点）

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

（1）接收上层传送的SQL语句 （2）语法验证模块：验证语句语法,是否满足SQL_MODE （3）语义检查：判断SQL语句的类型 DDL ：数据定义语言 DCL ：数据控制语言 DML ：数据操作语言 DQL： 数据查询语言 ... （4）权限检查：用户对库表有没有权限 （5）解析器：对语句执行前,进行预处理，生成解析树(执行计划),说白了就是生成多种执行方案. （6）优化器：根据解析器得出的多种执行计划，进行判断，选择最优的执行计划 代价模型：资源（CPU IO MEM）的耗损评估性能好坏 （7）执行器：根据最优执行计划，执行SQL语句，产生执行结果 执行结果：在磁盘的xxxx位置上 （8）提供查询缓存（默认是没开启的），一般不用，会使用redis替代查询缓存功能 （9）提供日志记录（日志管理章节）：binlog，默认是没开启的。

1.3.2.3 存储引擎层（类似于Linux中的文件系统）

1 2 3

负责根据SQL层执行的结果，从磁盘上拿数据。 将16进制的磁盘数据，交由SQL结构化化成表， 连接层的专用线程返回给用户。

1.4 逻辑结构

1.4.1 库（类似于目录）

1 2 3

库名，库属性 # show databases; # use mysql

1.4.2 表（类似于文件）

1 2 3 4 5 6

表名 属性 列:列名(字段),列属性(数据类型,约束等) 数据行(记录) # show tables; # desc user; # 查看表列的情况

1.5 物理存储结构引入

1.5.1 库的物理存储结构

1	用文件系统的目录来存储

1.5.2 表的物理存储结构

1 2 3 4 5 6 7 8 9 10

MyISAM（一种引擎）的表： -rw-r----- 1 mysql mysql 10816 Apr 18 11:37 user.frm -rw-r----- 1 mysql mysql 396 Apr 18 12:20 user.MYD -rw-r----- 1 mysql mysql 4096 Apr 18 14:48 user.MYI InnoDB(默认的存储引擎)的表： -rw-r----- 1 mysql mysql 8636 Apr 18 11:37 time_zone.frm -rw-r----- 1 mysql mysql 98304 Apr 18 11:37 time_zone.ibd time_zone.frm：存储列相关信息 time_zone.ibd：数据行+索引

1.5.3 表的段、区、页（16k）（了解）

1 2 3

段：一个表就是一个段，可以由一个或者多个区构成 区/簇：一个区（簇），默认1M，连续的64个页（pages） 页：一个页，默认16k，连续的4个os的block，最小的存储单元

二 基础管理

2.1 用户、权限管理

2.1.1 用户

作用：

1	登录，管理数据库逻辑对象

定义：

1 2 3 4 5 6 7 8 9

用户名@'白名单' 白名单支持的方式？ wordpress@'10.0.0.%' # wordpress用户可以通过10.0.0地址段的ip登陆 wordpress@'%' # wordpress用户可以通过所有ip登陆 wordpress@'10.0.0.200' # wordpress用户只能通过10.0.0.200ip登陆 wordpress@'localhost'# 本地 wordpress@'db02' # 通过主机名db02登陆 wordpress@'10.0.0.5%' #wordpress用户可以通过50--59的ip登陆 wordpress@'10.0.0.0/255.255.254.0' # 地址段

管理操作：

1 2 3 4 5 6 7 8 9 10 11 12 13

# mysql库下user表，存储用户和授权信息 # 增： create user lqz@'localhost'; create user lqz@'%' identified by '123'; # 查： desc mysql.user; ----> authentication_string select user ,host ,authentication_string from mysql.user # 改: alter user lqz@'%' identified by '456'; # 删： drop user lqz@'%'; # 注意：8.0以前，可以通过grant命令，建立用户+授权，8.0以后不再支持，必须先建用户设置密码，再授权

2.1.2 权限

针对用户设置权限，权限是用户的属性

权限管理操作：

1 2 3 4 5

# 8.0以前 # grant 权限 on 对象 to 用户 identified by '密码'； # 8.0以后 # create user 用户 identified by '密码'； # grant 权限 on 对象 to 用户；

常用权限介绍:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

show privileges; # 查看所有权限 ALL: SELECT,INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CRE ATE TABLESPACE ALL : 以上所有权限，一般是普通管理员拥有的 with grant option：超级管理员才具备的，给别的用户授权的功能 ###################权限： ALL SELECT,INSERT,UPDATE,DELETE grant option ## 例子 grant all on wordpress.* to wordpress@'10.0.0.%' identified by '123'; grant SELECT,INSERT,UPDATE,DELETE on wordpress.* to wordpress@'10.0.0.%' identified by '123'; # all权限不包含grant option，需要单独设置。给其他用户授权 grant all on wordpress.* to wordpress@'10.0.0.%' with grant option;

权限作用范围:

1 2 3 4

# 对象：库，表 *.* # 所有库，所有表（管理员） lqz.* # lqz库下的所有表（用的多） lqz.article # lqz库下的article表

需求1：windows机器的navicat登录到linux中的MySQL，管理员用户。

1	mysql> grant all on *.* to root@'10.0.0.%' identified by '123';

需求2：创建一个应用用户app用户，能从windows上登录mysql，并能操作app库

1	mysql> grant select ,update,insert,delete on app.* to app@'10.0.0.%' identified by '123';

2.1.3 开发人员用户授权流程

1 2 3 4

1.权限 2.对谁操作 3.你从哪来 4.密码要求

2.1.4 提示：8.0在grant命令添加新特性

1 2 3

建用户和授权分开了 grant 不再支持自动创建用户了，不支持改密码 授权之前，必须要提前创建用户。

2.1.5 查看授权

1 2 3 4 5 6 7 8 9 10 11

show grants for app@'10.0.0.%'; # 查看用户基本信息 select * from mysql.userG; # N和Y表示 # mysql授权表mysql库下，每次数据库启动，会把数据加载到内存中 user: *.*范围，存放创建的用户密码包括全局实例级别管理权限 db： 库级别范围 lqz.* tables_priv： 表级别范围 lqz.article columns_priv： 列范围，字段级别 procs_priv: 存放存储过程的权限

2.1.6 回收权限

1 2 3 4

# 用户删了，权限也就没了 # oracle中删除用户，用户的表也没了，mysql删除用户只是权限没了，表和库还在 # 不能通过重复授权覆盖之前的权限，进行修改，只能回收权限，只回收某个权限，多次grants是叠加权限 revoke delete on app.* from app@'10.0.0.%'；

2.1.7 本地管理员用户密码忘记.

1 2 3 4 5 6 7 8 9 10 11 12 13

# mysqld启动时，跳过授权表，跳过tcp/ip连接 --skip-grant-tables 跳过授权表 --skip-networking 跳过tcp/ip连接，不让远程用户登录，否则远程用户不用密码可以直接登录 #### ？ service mysqld start --skip-grant-tables --skip-networking [root@db01 ~]mysqld_safe --skip-grant-tables --skip-networking & # 手工把授权表加载到内存 mysql> flush privileges; mysql> alter user root@'localhost' identified by '123456'; [root@db01 ~]# pkill mysqld [root@db01 ~]# systemctl start mysqld

2.2 连接管理

2.2.1 自带客户端命令

mysql 常用参数：

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35

-u 用户 -p 密码 -h IP -P 端口 -S socket文件 -e 免交互执行命令，做自动化运维，查询数据库用户，创建用户等等 < 导入SQL脚本 [root@db01 ~]# mysql -uroot -p -h 10.0.0.51 -P3306 Enter password: mysql> select @@socket; +-----------------+ | @@socket | +-----------------+ | /tmp/mysql.sock | # 数据库中必须先授权 root@'localhost' 用户 [root@db01 ~]# mysql -uroot -p -S /tmp/mysql.sock Enter password: [root@db01 ~]# mysql -uroot -p -e "select user,host from mysql.user;" Enter password: +---------------+-----------+ | user | host | +---------------+-----------+ | root | 10.0.0.% | | mysql.session | localhost | | mysql.sys | localhost | | root | localhost | +---------------+-----------+ [root@db01 ~]# [root@db01 ~]# mysql -uroot -p <world.sql Enter password: # 查看客户端连接情况，区分哪个是远程连接过来，哪个是本地连接过来 show processlist;

2.3 多种启动方式介绍

提示：

1 2 3 4 5 6

以上多种方式，都可以单独启动MySQL服务 mysqld_safe和mysqld一般是在临时维护时使用。 另外，从Centos 7系统开始，支持systemd直接调用mysqld的方式进行启动数据库 方式三四，可以客户端连进去，输入 shutdown关闭服务端 或者 mysqladmin -uroot -p123 shutdown

2.4 初始化配置

2.4.0 作用

1 2	控制MySQL的启动 影响到客户端的连接

2.4.1 初始化配置的方法

1 2 3

预编译 **配置文件(所有启动方式)** 命令行参数 (仅限于 mysqld_safe mysqld)

2.4.2 初始配置文件

初始化配置文件的默认读取路径

1 2 3 4 5 6

[root@db01 ~]# mysqld --help --verbose |grep my.cnf /etc/my.cnf /etc/mysql/my.cnf /usr/local/mysql/etc/my.cnf ~/.my.cnf 注: 默认情况下，MySQL启动时，会依次读取以上配置文件，如果有重复选项，会以最后一个文件设置的为准。 # 手动指定使用哪个配置文件启动，指定默认文件位置点 但是，如果启动时加入了--defaults-file=xxxx时，以上的所有文件都不会读取.

配置文件的书写方式:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28

[标签] 配置项=xxxx 标签类型： 服务端：影响数据库服务端运行 客户端：只影响本地客户端连接，不影响远程 服务器端标签： [mysqld] [mysqld_safe] [server] 代表所有客户端 客户端标签： [mysql] [mysqldump] [client] 代表所有客户端 配置文件的示例展示： [root@db01 ~]# cat /etc/my.cnf [mysqld] user=mysql # 负责数据库管理的用户 basedir=/app/mysql #软件位置 datadir=/data/mysql #数据位置 socket=/tmp/mysql.sock #套接在文件 server_id=6 #标识节点的编号，主从复制会用 port=3306 #当前节点端口号 log_error=/data/mysql/mysql.log # 日志文件位置 [mysql] # 客户端标签 socket=/tmp/mysql.sock

2.5 多实例的应用

2.5.1 准备多个目录

1 2	mkdir -p /data/330{7,8,9}/data mkdir -p /binlog/330{7,8,9}

2.5.2 准备配置文件

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32

cat > /data/3307/my.cnf <<EOF [mysqld] basedir=/app/database/mysql datadir=/data/3307/data socket=/tmp/mysql3307.sock log_error=/data/3307/mysql.log port=3307 server_id=7 log_bin=/data/3307/mysql-bin EOF cat > /data/3308/my.cnf <<EOF [mysqld] basedir=/app/database/mysql datadir=/data/3308/data socket=/tmp/mysql3308.sock log_error=/data/3308/mysql.log port=3308 server_id=8 log_bin=/data/3308/mysql-bin EOF cat > /data/3309/my.cnf <<EOF [mysqld] basedir=/app/database/mysql datadir=/data/3309/data socket=/tmp/mysql3309.sock log_error=/data/3309/mysql.log port=3309 server_id=9 log_bin=/data/3309/mysql-bin EOF

2.5.3 初始化三套数据

1 2 3 4 5

chown -R mysql.mysql /data /binlog mv /etc/my.cnf /etc/my.cnf.bak mysqld --initialize-insecure --user=mysql --datadir=/data/3307/data --basedir=/app/database/mysql mysqld --initialize-insecure --user=mysql --datadir=/data/3308/data --basedir=/app/database/mysql mysqld --initialize-insecure --user=mysql --datadir=/data/3309/data --basedir=/app/database/mysql

2.5.4 systemd管理多实例

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69

cd /etc/systemd/system cp mysqld.service mysqld3307.service cp mysqld.service mysqld3308.service cp mysqld.service mysqld3309.service # 脚本介绍 [Unit] Description=MySQL Server # 描述服务 Documentation=man:mysqld(8) Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html After=network.target # 描述服务类别 After=syslog.target [Install] WantedBy=multi-user.target [Service] # 服务运行参数设置 User=mysql Group=mysql ExecStart=/app/mysql/bin/mysqld --defaults-file=/data/3307/my.cnf LimitNOFILE = 5000 EOF cat >/etc/systemd/system/mysqld3307.service <<EOF [Unit] Description=MySQL Server Documentation=man:mysqld(8) Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html After=network.target After=syslog.target [Install] WantedBy=multi-user.target [Service] User=mysql Group=mysql ExecStart=/app/database/mysql/bin/mysqld --defaults-file=/data/3307/my.cnf LimitNOFILE = 5000 EOF cat >/etc/systemd/system/mysqld3308.service <<EOF [Unit] Description=MySQL Server Documentation=man:mysqld(8) Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html After=network.target After=syslog.target [Install] WantedBy=multi-user.target [Service] User=mysql Group=mysql ExecStart=/app/database/mysql/bin/mysqld --defaults-file=/data/3308/my.cnf LimitNOFILE = 5000 EOF cat >/etc/systemd/system/mysqld3309.service <<EOF [Unit] Description=MySQL Server Documentation=man:mysqld(8) Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html After=network.target After=syslog.target [Install] WantedBy=multi-user.target [Service] User=mysql Group=mysql ExecStart=/app/database/mysql/bin/mysqld --defaults-file=/data/3309/my.cnf LimitNOFILE = 5000 EOF

2.5.5 授权

1	chown -R mysql.mysql /data/*

2.5.6 启动

1 2 3

systemctl start mysqld3307.service systemctl start mysqld3308.service systemctl start mysqld3309.service

2.5.7 验证多实例

1 2 3 4

netstat -lnp|grep 330 mysql -S /data/3307/mysql.sock -e "select @@server_id" mysql -S /data/3308/mysql.sock -e "select @@server_id" mysql -S /data/3309/mysql.sock -e "select @@server_id"

SQL基础应用

一 SQL介绍

1 2 3 4 5 6 7 8 9 10 11 12 13 14

结构化查询语言 有一些标准：89 92 99 03 5.7 以后符合SQL92严格模式 通过sql_mode参数来控制 # 查看sql_mode，sql_mode用来规范sql语句的书写方式 select @@sql_mode; # 查看sql_mode ONLY_FULL_GROUP_BY, # 5.7新加入 STRICT_TRANS_TABLES, NO_ZERO_IN_DATE, NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO, NO_AUTO_CREATE_USER, NO_ENGINE_SUBSTITUTION

二 常用SQL分类

1 2 3 4 5 6 7 8 9

# help:客户端功能帮助 # help contents：服务端功能帮助 # help Data Definition # help DROP DATABASE DDL：数据定义语言 DCL：数据控制语言 DML：数据操作语言 DQL：数据的查询语言

2.1 客户端命令

1 2 3 4 5 6 7 8 9 10 11 12 13

# 客户端输入help # c 结束上一条命令 # G 格式化输出 # exit，q,control+d，quit退出会话 # notee和tee 开启日志记录， tee/tmp/mysql.log # 开启，以后执行的sql都会被记录到日志，包括结果 select * from t2； # source 导入sql脚本，类似于<,恢复备份 source /root/my.sql # system 在mysql中执行linux命令 system ls system cd /tmp && ls

三 数据类型、表属性、字符集

3.1 数据类型

3.1.1 作用

1	保证数据的准确性和标准性。

3.1.2 种类

数值类型

1 2 3 4 5 6 7

tinyint ： -128~127 1个字节，8个比特位，正负2的7次方减1 int ：-2^31~2^31-1 4个字节，32个比特位，正负2的31次方减1 bigint : -2^63~2^63-1 8个字节，64个比特位，正负2的63次方减1 说明：手机号是无法存储到int的。一般是使用char类型来存储收集号 # 创建表指定数据类型 create tabel t1(id int,name varchar(64),age tinyint)

字符类型

image

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27

char(11) ： 定长 的字符串类型,在存储字符串时，最大字符长度11个，立即分配11个字符长度的存储空间，如果存不满，空格填充。 varchar(11): 变长 的字符串类型看，最大字符长度11个。在存储字符串时，自动判断字符长度，按需分配存储空间。 varchar类型，除了会存储字符串之外，还会额外使用1-2个字节存储字符长度 enum('bj','tj','sh')： 枚举类型，存字符串类型，比较适合于将来此列的值是固定范围内的特点，可以使用enum,可以很大程度的优化我们的索引结构。 # 括号中数字指的是字符长度，所以存英文和中文，其实占得空间是不一样的 # 英文和数字，一个字符是1个字节，中文3个字节,emoji占4个字节 # 因为编码方式规定了utf8，所以不需要自行考虑此问题 # 测试： create database db1 charset utf8mb4; create table t2(id int,name varchar(10),sex char(10)); insert into t2 values(1,'aaaaaaaaaa','一二三四五六七八九十'); insert into t2 values(1,'aaaaaaaaaa','一二三四五六七八九十一'); insert into t2 values(1,'aaaaaaaaaa','1234567891'); ERROR 1406 (22001): Data too long for column 'sex' at row 1 desc t2；# 查看表结构 select length(sex) from t2; # 查看字符所占用的空间 # mysql 5.6 超长会存进去，自动截断 # mysql 5.6 超长会报错 varchar最多能存储65535个字节的数据，一般我们最多定义varchar(255)，超过255会被转成text类型

时间类型

1 2 3 4 5

DATETIME （8个字节长度） 范围为从 1000-01-01 00:00:00.000000 至 9999-12-31 23:59:59.999999。 TIMESTAMP （4个字节长度） 1970-01-01 00:00:00.000000 至 2038-01-19 03:14:07.999999。 timestamp会受到时区的影响

二进制类型

json格式

5.6以后支持

3.2 表属性

3.2.1 列属性

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22

约束(一般建表时添加): # primary key ：主键约束， 唯一且非空，每个表只能有一个主键，作为聚簇索引 设置为主键的列，此列的值必须非空且唯一，主键在一个表中只能有一个，但是可以有多个列一起构成。 #not null ：非空约束 列值不能为空，也是表设计的规范，尽可能将所有的列设置为非空。可以设置默认值为0 # unique key：唯一键 列值不能重复 # unsigned：无符号 针对数字列，非负数。 其他属性: # key:索引 可以在某列上建立索引，来优化查询,一般是根据需要后添加 # default :默认值 列中，没有录入值时，会自动使用default的值填充 # auto_increment :自增长 针对数字列，顺序的自动填充数据（默认是从1开始，将来可以设定起始点和偏移量） # comment : 注释

3.2.2 表的属性

1 2 3 4 5

存储引擎: InnoDB（默认的） 字符集和排序规则: utf8 utf8mb4

3.3 字符集和校对规则

3.3.1 字符集（charset）

1 2 3 4 5 6 7 8 9 10

# show charset; 有非常多，现在只关注如下两种 utf8 utf8mb4 # 5.6以后出现，8.0以后默认使用utf8mb4，8.0以前默认是latin1（拉丁） # 差别： utf8：最大存储长度，单个字符最多3个字节 utf8mb4支持的编码比utf8更多，比如emoji字符，emoji字符，一个字符占4个字节 # 查看库的字符编码 show create databaes mysql

3.3.2 校对规则（排序规则，collation）

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17

show collation; # 影响排序规则 a b A aB Ba select ascii('a'); # 查看a的ascii码 如果大小写敏感排序一个样 如果大小写敏感排序另一个样 # 简单来说就是：大小写是否敏感，默认不敏感 +--------------------------+----------+-----+---------+----------+---------+ | Collation | Charset | Id | Default | Compiled | Sortlen | +--------------------------+----------+-----+---------+----------+---------+ | utf8mb4_general_ci | utf8mb4 | 45 | Yes | Yes | 1 | | utf8mb4_bin | utf8mb4 | 46 | | Yes | 1 |

四 DDL应用

4.1 数据定义语言

数据定义语言，对库和表进行操作，操作mysql的对象，即库和表，对元数据进行操作

4.2 库定义

4.2.1 创建

4.2.1 创建数据库

1 2 3 4 5 6 7 8 9 10 11 12

create database school; create schema sch; show charset; # 查看支持的字符集 show collation; # 查看支持的校对规则，collation CREATE DATABASE test CHARSET utf8; create database xyz charset utf8mb4 collate utf8mb4_bin; 建库规范： 1.库名不能有大写字母 2.建库要加字符集 3.库名不能有数字开头，不能使用保留字段（database，table） 4.库名要和业务相关

建库标准语句

1 2	mysql> create database db charset utf8mb4; mysql> show create database xuexiao;

4.2.2 删除(生产中禁止使用)

1	mysql> drop database lqz;

4.2.3 修改

1 2 3 4 5 6

SHOW CREATE DATABASE school; ALTER DATABASE school CHARSET utf8; # 注意： 只能改库属性，不能改库名（只能改字符集） 修改字符集，修改后的字符集一定是原字符集的严格超集 从小往大改，从utf8改到utf8mb4可以，从utf8mb4改成utf8可能会乱码

4.2.4 查询库相关信息（DQL）

1 2	show databases； # 查看库 show create database lqz； # 查看具体信息

4.3 表定义

4.3.1 创建

1 2 3 4 5

create table stu( 列1 属性（数据类型、约束、其他属性） ， 列2 属性， 列3 属性 )

4.3.2 建表

1 2 3 4 5 6 7 8 9

USE school; CREATE TABLE stu( id INT NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT '学号', sname VARCHAR(255) NOT NULL COMMENT '姓名', sage TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '年龄', sgender ENUM('m','f','n') NOT NULL DEFAULT 'n' COMMENT '性别' , sfz CHAR(18) NOT NULL UNIQUE COMMENT '身份证', intime TIMESTAMP NOT NULL DEFAULT NOW() COMMENT '入学时间' ) ENGINE=INNODB CHARSET=utf8 COMMENT '学生表';

建表规范：

1 2 3 4 5 6 7

1. 表名小写（多平台兼容性问题，window不区分大小写，linux严格大小写）， 2. 不能是数字开头，名字不要太长15个字符以内 3. 注意字符集和存储引擎 4. 表名和业务有关，不能使用关键字 5. 选择合适的数据类型：合适，简短，足够 6. 必须有主键，每个列都要有注释 7. 每个列设置为非空，无法保证非空，用0来填充。

4.3.2 删除(生产中禁用命令)

1	drop table t1;

4.3.3 修改

1. 在stu表中添加qq列

1 2	DESC stu; ALTER TABLE stu ADD qq VARCHAR(20) NOT NULL UNIQUE COMMENT 'qq号';

1. 在sname后加微信列

1	ALTER TABLE stu ADD wechat VARCHAR(64) NOT NULL UNIQUE COMMENT '微信号' AFTER sname ;

1. 在id列前加一个新列num

1 2	ALTER TABLE stu ADD num INT NOT NULL COMMENT '数字' FIRST; DESC stu;

1. 把刚才添加的列都删掉(危险)

1 2 3

ALTER TABLE stu DROP num; ALTER TABLE stu DROP qq; ALTER TABLE stu DROP wechat;

1. 修改sname数据类型的属性

1	ALTER TABLE stu MODIFY sname VARCHAR(128) NOT NULL ;

1. 将sgender 改为 sg 数据类型改为 CHAR 类型（change需要把原来不需要修改的也带上）

1 2	ALTER TABLE stu CHANGE sgender sg CHAR(1) NOT NULL DEFAULT 'n' ; DESC stu;

注意：

在mysql中，DDL语句在对表进行操作时，是要锁“元数据表”的，此时，所有修改类的命令无法运行

（元数据：记录表的各种信息，对数据锁定，才能修改，否则都去改，就会出问题）

大表加一列，业务繁忙的表，要谨慎

8.0以前版本需要借助，可以借助pt-osc(pt-online-shaema-change)，gh-ost工具进行DDL操作

4.3.4 表属性查询（DQL）

1 2 3 4 5

use school show tables； # 查看该库下所有表 desc stu; # 查看表结构 show create table stu；# 查看详细建表语句 CREATE TABLE ceshi LIKE stu;

五 DCL应用 ****

1 2	grant revoke

六 DML应用

6.1 作用

1	对表中的数据行进行增、删、改

6.2 insert

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22

--- 最标准的insert语句 INSERT INTO stu(id,sname,sage,sg,sfz,intime) VALUES (1,'zs',18,'m','123456',NOW()); SELECT * FROM stu; --- 省事的写法 INSERT INTO stu VALUES (2,'ls',18,'m','1234567',NOW()); --- 针对性的录入数据 INSERT INTO stu(sname,sfz) VALUES ('w5','34445788'); --- 同时录入多行数据 INSERT INTO stu(sname,sfz) VALUES ('w55','3444578d8'), ('m6','1212313'), ('aa','123213123123'); SELECT * FROM stu; # HWM：记录自增数字，高水位线

6.3 update

1 2 3 4

DESC stu; SELECT * FROM stu; UPDATE stu SET sname='zhao4' WHERE id=2; 注意：update语句必须要加where。

6.4 delete（危险！！）

1	DELETE FROM stu WHERE id=3;

全表删除:

1 2 3 4 5 6 7 8 9 10 11 12

DELETE FROM stu; truncate table stu; drop table stu; 区别: delete: DML操作, 是逻辑性质删除,逐行进行删除,数据库很多，速度慢，并没有在磁盘上真正删除，磁盘空间不会立即释放，自增的值，也不会释放（HWM高水位线不会降低）， truncate: DDL操作,物理层次删除，对与表段中的数据页进行清空,速度快，立即释放控件，HWM高水位线会降低 drop:将表结构（元数据）和物理层次删除 # 常规方法： 以上三者，都能通过备份+日志，恢复数据 # 灵活办法： delete 可以通过翻转日志（binlog） 三种删除数据的情况，可以通过《延时从库》进行恢复

伪删除：

用update来替代delete，最终保证业务中查不到（select）即可

1 2 3 4 5 6 7

1.添加状态列 ALTER TABLE stu ADD state TINYINT NOT NULL DEFAULT 1 ; SELECT * FROM stu; 2. UPDATE 替代 DELETE UPDATE stu SET state=0 WHERE id=6; 3. 业务语句查询 SELECT * FROM stu WHERE state=1;

七 DQL应用(select )

7.1 单独使用

1 2 3 4 5 6 7 8 9

-- select @@xxx 查看系统参数，配置文件中配的都可以查看 SELECT @@port; SELECT @@basedir; SELECT @@datadir; SELECT @@socket; SELECT @@server_id; show variables; # mysql中的500多个参数 show variables like '%or%';

– select 函数();

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19

SELECT NOW(); # 当前时间 SELECT DATABASE(); # 当前数据库 SELECT USER(); # 当前登录用户 SELECT CONCAT("hello world"); # 字符串拼接 SELECT CONCAT(USER,"@",HOST) FROM mysql.user; SELECT GROUP_CONCAT(USER,"@",HOST) FROM mysql.user; select version(); # 当前数据库版本号 # 相关函数查询，官方文档或者使用help https://dev.mysql.com/doc/refman/5.7/en/func-op-summary-ref.html?tdsourcetag=s_pcqq_aiomsg help contents help functions help String Functions # 标准sql，其他关系型数据库，必须要有from select NOW() from dual; # 计算 select 10*100

默认执行顺序

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30

select 列，列 from 表1，表2。。 where 过滤条件1 过滤条件2。。 group by 条件1 条件2。。 having 过滤条件1 过滤条件2。。 order by 条件列1 条件列2 limit 限制 # d select 1 from 表1，表2。。。 2 where 过滤条件1 过滤条件2。。 3 group by 条件1 条件2。。 3.5 select_list name,age 列名列表 4 having 过滤条件1 过滤条件2。。 5 order by 条件列1 条件列2 6 limit 限制 # 完整select 执行顺序 (6) SELECT 列 (8) DISTINCT <select_list> (1) FROM <left_table> (3) <join_type> JOIN <right_table> (2) ON <join_condition> (4) WHERE <where_condition> (5) GROUP BY <group_by_list> (7) HAVING <having_condition> (9) ORDER BY <order_by_condition> (10) LIMIT <limit_number>

导入数据

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

mysql < world.sql 库：world 表： city country countrylanguage city:城市表 DESC city; ID : 城市ID NAME : 城市名 CountryCode: 国家代码，比如中国CHN 美国USA District : 区域 Population : 人口

7.2 单表子句-from

1 2	SELECT 列1,列2 FROM 表 SELECT * FROM 表

例子:
– 查询city中所有的数据(不要对大表进行操作)

1	SELECT * FROM city ;

– 查询city表中,id和姓名

1	SELECT id ,name FROM city;

7.3 单表子句-where

1	SELECT col1,col2 FROM TABLE WHERE colN 条件;

7.3.1 where配合等值查询

例子:
– 查询中国(CHN)所有城市信息

1	SELECT * FROM city WHERE countrycode='CHN';

– 查询北京市的信息

1	SELECT * FROM city WHERE NAME='peking';

– 查询甘肃省所有城市信息

1	SELECT * FROM city WHERE district='gansu';

7.3.2 where配合比较操作符(> < >= <= <>)

例子:
– 查询世界上少于100人的城市

1	SELECT * FROM city WHERE population<100;

7.3.3 where配合逻辑运算符(and or )

例子:
– 中国人口数量大于500w

1	SELECT * FROM city WHERE countrycode='CHN' AND population>5000000;

– 中国或美国城市信息

1	SELECT * FROM city WHERE countrycode='CHN' OR countrycode='USA';

7.3.4 where配合模糊查询

例子:
– 查询省的名字前面带guang开头的

1 2	SELECT * FROM city WHERE district LIKE 'guang%'; 注意:%不能放在前面,因为不走索引.只能用字符串的列

7.3.5 where配合in语句

– 中国或美国城市信息

1 2 3 4 5 6 7

SELECT * FROM city WHERE countrycode ='CHN' or countrycode ='USA'; SELECT * FROM city WHERE countrycode IN ('CHN' ,'USA'); # 错误 SELECT * FROM city WHERE countrycode ='CHN' or countrycode ='USA'AND population>5000000; # 正确 SELECT * FROM city WHERE countrycode IN ('CHN' ,'USA') AND population>5000000;

7.3.6 where配合between and

例子:
– 查询世界上人口数量大于100w小于200w的城市信息

1 2	SELECT * FROM city WHERE population >1000000 AND population <2000000; SELECT * FROM city WHERE population BETWEEN 1000000 AND 2000000;

7.4 group by + 常用聚合函数

7.4.1 作用

1	根据 by后面的条件进行分组，方便统计，by后面跟一个列或多个列

7.4.2 常用聚合函数

1 2 3 4 5 6

**max()** ：最大值 **min()** ：最小值 **avg()** ：平均值 **sum()** ：总和 **count()** ：个数 group_concat() : 列转行

7.4.3 例子：

例子1：统计世界上每个国家的总人口数.

1 2	USE world SELECT countrycode ,SUM(population) FROM city GROUP BY countrycode;

例子2： 统计中国各个省的总人口数量(练习)

1	SELECT district,SUM(Population) FROM city WHERE countrycode='chn' GROUP BY district;

例子3：统计世界上每个国家的城市数量(练习)

1	SELECT countrycode,COUNT(id) FROM city GROUP BY countrycode;

例子4：统计中国，每个省总人口，城市个数，城市名列表(重点)

1 2 3 4 5

select district,sum(Population),count(id),name from world.city where countrycode='CHN' group by district; # 报错： ERROR 1055 (42000): Expression #4 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'world.city.Name' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by # 原因：违反了sql_mode=only_full_group_by，SELECT list必须是group by的字段和聚合函数，name不属于，因为name有很多，在mysql5.6之前可以查询，但是只取name的第一个，5.7以后直接报错，这是合理的 select district,sum(Population),count(id),group_concat(name) from world.city where countrycode='CHN' group by district;

7.5 having

1	where|group|having

例子4：统计中国每个省的总人口数，只打印总人口数小于100w

1 2 3 4 5

SELECT district,SUM(Population) FROM city WHERE countrycode='chn' GROUP BY district HAVING SUM(Population) < 1000000 ;

7.6 order by + limit

7.6.1 作用

1	实现先排序，by后添加条件列

7.6.2 应用案例

1. 查看中国所有的城市，并按人口数进行排序(从大到小)

1	SELECT * FROM city WHERE countrycode='CHN' ORDER BY population DESC;

1. 统计中国各个省的总人口数量，按照总人口从大到小排序

1 2 3 4 5

SELECT district AS 省 ,SUM(Population) AS 总人口 FROM city WHERE countrycode='chn' GROUP BY district ORDER BY 总人口 DESC ;

1. 统计中国,每个省的总人口,找出总人口大于500w的,并按总人口从大到小排序,只显示前三名

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17

SELECT district, SUM(population) FROM city WHERE countrycode='CHN' GROUP BY district HAVING SUM(population)>5000000 ORDER BY SUM(population) DESC LIMIT 3 ; LIMIT N ,M --->跳过N,显示一共M行 LIMIT 5,5 LIMIT 5 OFFSET 5; SELECT district, SUM(population) FROM city WHERE countrycode='CHN' GROUP BY district HAVING SUM(population)>5000000 ORDER BY SUM(population) DESC LIMIT 5,5;

7.7 distinct：去重复

1 2	SELECT countrycode FROM city ; SELECT DISTINCT(countrycode) FROM city ;

7.8 联合查询- union all

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

# 求并集 -- 中国或美国城市信息 SELECT * FROM city WHERE countrycode IN ('CHN' ,'USA'); SELECT * FROM city WHERE countrycode ='CHN' or countrycode ='USA'; SELECT * FROM city WHERE countrycode='CHN' UNION ALL SELECT * FROM city WHERE countrycode='USA' 说明:一般情况下,我们会将 IN 或者 OR 语句 改写成 UNION ALL,来提高性能 都是聚合两个结果集 UNION 去重复 UNION ALL 不去重复

7.9 join 多表连接查询

7.9.0 案例准备

按需求创建一下表结构:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109

use school student ：学生表 sno： 学号 sname：学生姓名 sage： 学生年龄 ssex： 学生性别 teacher ：教师表 tno： 教师编号 tname：教师名字 course ：课程表 cno： 课程编号 cname：课程名字 tno： 教师编号 score ：成绩表 sno： 学号 cno： 课程编号 score：成绩 -- 项目构建 drop database school; CREATE DATABASE school CHARSET utf8; USE school CREATE TABLE student( sno INT NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT '学号', sname VARCHAR(20) NOT NULL COMMENT '姓名', sage TINYINT UNSIGNED NOT NULL COMMENT '年龄', ssex ENUM('f','m') NOT NULL DEFAULT 'm' COMMENT '性别' )ENGINE=INNODB CHARSET=utf8; CREATE TABLE course( cno INT NOT NULL PRIMARY KEY COMMENT '课程编号', cname VARCHAR(20) NOT NULL COMMENT '课程名字', tno INT NOT NULL COMMENT '教师编号' )ENGINE=INNODB CHARSET utf8; CREATE TABLE sc ( sno INT NOT NULL COMMENT '学号', cno INT NOT NULL COMMENT '课程编号', score INT NOT NULL DEFAULT 0 COMMENT '成绩' )ENGINE=INNODB CHARSET=utf8; CREATE TABLE teacher( tno INT NOT NULL PRIMARY KEY COMMENT '教师编号', tname VARCHAR(20) NOT NULL COMMENT '教师名字' )ENGINE=INNODB CHARSET utf8; INSERT INTO student(sno,sname,sage,ssex) VALUES (1,'zhang3',18,'m'); INSERT INTO student(sno,sname,sage,ssex) VALUES (2,'zhang4',18,'m'), (3,'li4',18,'m'), (4,'wang5',19,'f'); INSERT INTO student VALUES (5,'zh4',18,'m'), (6,'zhao4',18,'m'), (7,'ma6',19,'f'); INSERT INTO student(sname,sage,ssex) VALUES ('oldboy',20,'m'), ('oldgirl',20,'f'), ('oldp',25,'m'); INSERT INTO teacher(tno,tname) VALUES (101,'oldboy'), (102,'hesw'), (103,'oldguo'); DESC course; INSERT INTO course(cno,cname,tno) VALUES (1001,'linux',101), (1002,'python',102), (1003,'mysql',103); DESC sc; INSERT INTO sc(sno,cno,score) VALUES (1,1001,80), (1,1002,59), (2,1002,90), (2,1003,100), (3,1001,99), (3,1003,40), (4,1001,79), (4,1002,61), (4,1003,99), (5,1003,40), (6,1001,89), (6,1003,77), (7,1001,67), (7,1003,82), (8,1001,70), (9,1003,80), (10,1003,96); SELECT * FROM student; SELECT * FROM teacher; SELECT * FROM course; SELECT * FROM sc;

7.9.1 语法

1 2 3 4 5 6 7 8 9 10

笛卡尔积：select * from teacher，course； 内连接(inner join)： select * from teacher join course on teacher.tno=course.tno; select city.name,country.name,city.population from city join country on city.countrycode = country.code and city.population <100; 外连接: left join:左表所有数据和右表满足条件的数据 select * from teacher join course on teacher.tno=course.tno; select city.name,country.name,city.population from city left join country on city.countrycode = country.code and city.population <100; right join：右表所有数据和左表满足条件的数据 select city.name,country.name,city.population from city right join country on city.countrycode = country.code and city.population <100;

查询张三的家庭住址

1 2 3 4

SELECT A.name,B.address FROM A JOIN B ON A.id=B.id WHERE A.name='zhangsan'

7.9.2 例子：

1. 查询一下世界上人口数量小于100人的城市名和国家名

1 2 3 4 5

SELECT b.name ,a.name ,a.population FROM city AS a JOIN country AS b ON b.code=a.countrycode WHERE a.Population<100

1. 查询城市shenyang，城市人口，所在国家名（name）及国土面积（SurfaceArea）

1 2 3 4

SELECT a.name,a.population,b.name ,b.SurfaceArea FROM city AS a JOIN country AS b ON a.countrycode=b.code WHERE a.name='shenyang';

7.9.3 别名

1 2 3 4 5 6 7 8 9

列别名,表别名 SELECT a.Name AS an , b.name AS bn , b.SurfaceArea AS bs, a.Population AS bp FROM city AS a JOIN country AS b ON a.CountryCode=b.Code WHERE a.name ='shenyang';

7.9.4 多表SQL练习题

1. 统计zhang3,学习了几门课

1 2 3 4 5 6

SELECT st.sname , COUNT(sc.cno) FROM student AS st JOIN sc ON st.sno=sc.sno WHERE st.sname='zhang3'

1. 查询zhang3,学习的课程名称有哪些?

1 2 3 4 5 6 7

SELECT st.sname , GROUP_CONCAT(co.cname) FROM student AS st JOIN sc ON st.sno=sc.sno JOIN course AS co ON sc.cno=co.cno WHERE st.sname='zhang3'

1. 查询oldguo老师教的学生名.

1 2 3 4 5 6 7 8 9

SELECT te.tname ,GROUP_CONCAT(st.sname) FROM student AS st JOIN sc ON st.sno=sc.sno JOIN course AS co ON sc.cno=co.cno JOIN teacher AS te ON co.tno=te.tno WHERE te.tname='oldguo';

1. 查询oldguo所教课程的平均分数

1 2 3 4 5 6 7

SELECT te.tname,AVG(sc.score) FROM teacher AS te JOIN course AS co ON te.tno=co.tno JOIN sc ON co.cno=sc.cno WHERE te.tname='oldguo'

4.1 每位老师所教课程的平均分,并按平均分排序

1 2 3 4 5 6 7 8

SELECT te.tname,AVG(sc.score) FROM teacher AS te JOIN course AS co ON te.tno=co.tno JOIN sc ON co.cno=sc.cno GROUP BY te.tname ORDER BY AVG(sc.score) DESC ;

1. 查询oldguo所教的不及格的学生姓名

1 2 3 4 5 6 7 8 9

SELECT te.tname,st.sname,sc.score FROM teacher AS te JOIN course AS co ON te.tno=co.tno JOIN sc ON co.cno=sc.cno JOIN student AS st ON sc.sno=st.sno WHERE te.tname='oldguo' AND sc.score<60;

5.1 查询所有老师所教学生不及格的信息

1 2 3 4 5 6 7 8 9

SELECT te.tname,st.sname,sc.score FROM teacher AS te JOIN course AS co ON te.tno=co.tno JOIN sc ON co.cno=sc.cno JOIN student AS st ON sc.sno=st.sno WHERE sc.score<60;

注意：

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

join 左边的为左表，右边的为右边 驱动表：驱动表会拿着每一行，逐一的跟另一个表匹配，匹配成功就合并 #双层for循环 for 每一行 in 驱动表： for 每一行 in 另一个表： 匹配成功合并 # 假设驱动表是1000行，另一个表是10行，至少要1000次循环，如果拿另一个表去找，最少10次 多表关联，驱动表要选小表，降低next loop次数 对于内连接来讲，我们没法控制驱动表是谁，完全由优化器决定，如果要人为干预，要把内连接写成外连接的方式 如果使用left join 可以强制左表为驱动表 总结： 1 小表作为驱动表，降低next loop次数 2 left join 可以强制左表为驱动表

7.9.5 综合练习

1 2 3 4 5 6 7 8 9

1. 查询平均成绩大于60分的同学的学号和平均成绩； 2. 查询所有同学的学号、姓名、选课数、总成绩； 3. 查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分 4. 统计各位老师,所教课程的及格率 5. 查询每门课程被选修的学生数 6. 查询出只选修了一门课程的全部学生的学号和姓名 7. 查询选修课程门数超过1门的学生信息 8. 统计每门课程:优秀(85分以上),良好(70-85),一般(60-70),不及格(小于60)的学生列表 9. 查询平均成绩大于85的所有学生的学号、姓名和平均成绩

7.9.6 补充

别名

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17

# 1 表别名 SELECT te.tname,st.sname,sc.score FROM teacher AS te JOIN course AS co ON te.tno=co.tno JOIN sc ON co.cno=sc.cno JOIN student AS st ON sc.sno=st.sno WHERE sc.score<60; # 表别名可以在全局使用，只在当次查询中有用 # 2 列别名 select student.sno as '学号',student.sname as '姓名' from studnet; # 列别名在哪些子句中可以使用 在having之前都不能使用（参照上面select执行顺序）

 

八 元数据信息

8.1 逻辑表有关组成部分

1 2 3 4 5 6

# 每次数据库启动，会自动在内存中生成nformation_schema，生成查询mysql部分元数据的视图 # 视图：select 语句的执行方法，不保存数据本身 ## 创建视图 create view v_city as select name from city where id <10; ## 使用视图 select * from v_city;

8.2 information_schema.tables视图

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38

use information_schema; # 进入information_schema show tables; # 可以看到所有视图 # 我们研究tables这个视图 desc tables; # DESC information_schema.TABLES TABLE_SCHEMA ---->表所在库名 TABLE_NAME ---->表名 ENGINE ---->引擎 TABLE_ROWS ---->表的行数(不是特别实时) AVG_ROW_LENGTH ---->表中行的平均行长度（字节） INDEX_LENGTH ---->索引的占用空间大小（字节） DATA_FREE ---->表中是否有碎片 # 所有的 TABLE_CATALOG TABLE_SCHEMA TABLE_NAME TABLE_TYPE ENGINE VERSION ROW_FORMAT TABLE_ROWS AVG_ROW_LENGTH DATA_LENGTH MAX_DATA_LENGTH INDEX_LENGTH DATA_FREE AUTO_INCREMENT CREATE_TIME UPDATE_TIME CHECK_TIME TABLE_COLLATION CHECKSUM CREATE_OPTIONS TABLE_COMMENT # 使用场景 资产统计，自动化运维平台，多少个库，多少个表，占用空间怎么样，统计表增长

1. 查询整个数据库中所有库和所对应的表信息

1 2 3 4

SELECT table_schema,GROUP_CONCAT(table_name) FROM information_schema.tables GROUP BY table_schema;

1. 统计所有库下的表个数

1 2 3

SELECT table_schema,COUNT(table_name) FROM information_schema.TABLES GROUP BY table_schema

1. 查询所有innodb引擎的表及所在的库

1 2	SELECT table_schema,table_name,ENGINE FROM information_schema.`TABLES` WHERE ENGINE='innodb';

1. 统计world数据库下每张表的磁盘空间占用

1 2	SELECT table_name,CONCAT((TABLE_ROWS*AVG_ROW_LENGTH+INDEX_LENGTH)/1024," KB") AS size_KB FROM information_schema.tables WHERE TABLE_SCHEMA='world';

1. 统计所有数据库的总的磁盘空间占用

1 2 3 4 5 6

SELECT TABLE_SCHEMA, CONCAT(SUM(TABLE_ROWS*AVG_ROW_LENGTH+INDEX_LENGTH)/1024," KB") AS Total_KB FROM information_schema.tables GROUP BY table_schema; mysql -uroot -p123 -e "SELECT TABLE_SCHEMA,CONCAT(SUM(TABLE_ROWS*AVG_ROW_LENGTH+INDEX_LENGTH)/1024,' KB') AS Total_KB FROM information_schema.tables GROUP BY table_schema;"

1. 统计每个库，所有表的个数，表名

1	select table_schema,count(table_name),GROUP_CONCAT(table_name) from TABLES group by table_schema;

1. 统计每个库占用空间大小

1 2 3

select table_schema,sum(AVG_ROW_LENGTH*TABLE_ROWS+INDEX_LENGTH)/1024 from information_schema.tables group by table_schema; select table_schema,sum(DATA_LENGTH)/1024 from information_schema.tables group by table_schema;

1. 生成整个数据库下的所有表的单独备份语句

1 2 3 4 5 6 7 8

模板语句： mysqldump -uroot -p123 world city >/tmp/world_city.sql SELECT CONCAT("mysqldump -uroot -p123 ",table_schema," ",table_name," >/tmp/",table_schema,"_",table_name,".sql" ) FROM information_schema.tables WHERE table_schema NOT IN('information_schema','performance_schema','sys') INTO OUTFILE '/tmp/bak.sh' ; CONCAT("mysqldump -uroot -p123 ",table_schema," ",table_name," >/tmp/",table_schema,"_",table_name,".sql" )

1. 107张表，都需要执行以下2条语句

1 2 3 4 5 6

ALTER TABLE world.city DISCARD TABLESPACE; ALTER TABLE world.city IMPORT TABLESPACE; SELECT CONCAT("alter table ",table_schema,".",table_name," discard tablespace") FROM information_schema.tables WHERE table_schema='world' INTO OUTFILE '/tmp/dis.sql';

九 show 命令

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26

show databases; #查看所有数据库 show tables; #查看当前库的所有表 SHOW TABLES FROM #查看某个指定库下的表 show create database world #查看建库语句 show create table world.city #查看建表语句 show grants for root@'localhost' #查看用户的权限信息 show charset； #查看字符集 show collation #查看校对规则 show processlist; #查看数据库连接情况 show full processlist; #查看数据库连接情况详细信息 show privileges #查看权限信息 show index from #表的索引情况 show status #数据库状态查看 SHOW STATUS LIKE '%lock%'; #模糊查询数据库某些状态 SHOW VARIABLES #查看所有配置信息 SHOW variables LIKE '%lock%'; #查看部分配置信息 show engines #查看支持的所有的存储引擎 show engine innodb statusG #查看InnoDB引擎相关的状态信息 show binary logs #列举所有的二进制日志 show master status #查看数据库的日志位置信息 show binlog evnets in #查看二进制日志事件 show slave status G #查看从库状态 SHOW RELAYLOG EVENTS in #查看从库relaylog事件信息 desc (show colums from city) #查看表的列定义信息 http://dev.mysql.com/doc/refman/5.7/en/show.html help show 查看其它的

索引及执行计划

一 索引作用

1	提供了类似于书中目录的作用,目的是为了优化查询

二 索引的种类(算法)

1 2 3 4 5

B树索引：b tree， B+tree，B*tree Hash索引 R树 Full text GIS

三 B树 基于不同的查找算法分类介绍

B 树

B+树

B*树

在b+tree基础上，枝节点也加入了双向指针（Innodb，使用B*树）

1 2 3

B-tree B+Tree 在范围查询方面提供了更好的性能(> < >= <= like) B*Tree

四 在功能上的分类

4.1 聚簇索引构建B树（簇就是区）

4.1.1 前提

1 2 3 4

(1)建表时，指定了主键列，MYSQL InnoDB会将主键作为聚簇索引列，比如 id not null primary key (2)如果没有主键,会选择唯一键(unique)作为聚集索引. (3)聚簇必须在建表时才有意义,一般是表的无关列(ID) (4)如果以上都没有，自动生成隐藏的聚簇索引

4.1.2 作用

有了聚簇索引，将来插入的数据行，在同一个区内，都会按照id值的顺序，有序存储数据

4.2.3 聚簇索引构建B树过程

1 2 3 4 5 6 7 8 9 10 11 12 13

段：一个表就是一个段，可以由一个或者多个区构成 区/簇：一个区（簇），默认1M，连续的64个页（pages）,一张表由多个簇构成 页：一个页，默认16k，连续的4个os的block，最小的存储单元 # 注意 上图只是举例说明，并不是一个叶子节点只存4行数据 枝节点也是由一个页存储，当然存储的数据可能更多 一颗b树索引至少要有root节点和叶子节点，枝节点可以没有（数据量少的情况） 聚簇索引的作用：拿主键列去查询的时候，可以快速锁定要查询的数据行所在的页，3次io 如果没有这个，需要全表扫描，代价很高，只能加速有主键列的查询速度，所以按主键查，是效率最高的 mysql 的 innoDB的表，是聚簇索引组织存储数据表，每个页是稀疏存储，不一定全存满整个页 # 其他列怎么办？引出辅助索引

4.2 辅助索引(S)构建B+树

4.2.1 前提

1	在除主键以外的普通列上构建索引，例如name字段

4.2.2 作用

1	优化非聚簇索引列之外的查询

4.2.3 辅助索引构建B树过程

1 2 3 4 5 6 7 8 9 10

# 查询时，拿着name=er去一层一层找到er这个值，对的id，因为查的是*，所有，通过id再去原来的聚簇索引中找具体数据（回表过程） (1). 索引是基于表中,列(索引键)的值生成的B树结构 (2). 首先提取此列所有的值,进行自动排序 (3). 将排好序的值,均匀的分布到索引树的叶子节点中(16K) (4). 然后生成此索引键值所对应得后端数据页的指针 (5). 生成枝节点和根节点,根据数据量级和索引键长度,生成合适的索引树高度 id name age gender select * from t1 where id=10; 问题: 基于索引键做where查询,对于id列是顺序IO,但是对于其他列的查询,可能是随机IO.

4.4 聚簇索引和辅助索引构成区别

1 2 3 4 5

# 聚集索引只能有一个,非空唯一,一般时主键 # 辅助索引,可以有多个,时配合聚集索引使用的 # 聚簇索引叶子节点,就是磁盘的数据行存储的数据页，辅助索引不存整体数据 # MySQL是根据聚簇索引,组织存储数据,数据存储时就是按照聚集索引的顺序进行存储数据 # 辅助索引,只会提取索引键值,进行自动排序生成B树结构

五 辅助索引细分

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33

# 1.普通的单列辅助索引 # 2.联合索引（多列构建一个索引） 多个列作为索引条件,生成索引树,理论上设计的好的,可以减少大量的回表 如果 select * from t1 where name='er' and gender='男'; 这种比较多，建议name和gender建联合索引 构建索引过程相同，只不过现在按name和gender两列排序，生成枝节点时，只存储最左列（name）的值，不会存所有索引列（name和gender），所以，重复值少的列，放在最左侧 联合索引的：注意最左原则(a,b,c 建立索引，相当于a索引，ab索引，abc索引) 1 查询条件中，必须包含最左列，上面的例子就是a列，只有b，c走不了索引， 2 建立索引时，一定要选择重复值少的列，作为最左列 # 全覆盖 select * from t1 where a = and b= or c= # 走索引（极小情况不走索引：索引失效，统计信息不真实） select * from t1 where a = and b in and c in # in条件等同于=，也会走索引 select * from t1 where c = and b= and a = # 也会走索引，因为sql优化器会把a位置调整 select * from t1 where a = and b= order by c #全覆盖 # 部分覆盖 select * from t1 where a = select * from t1 where a = and b= select * from t1 where a = and c= select * from t1 where a = and b < > >= <= like and c= # 不等值，只能覆盖到a,b 不能覆盖到c select * from t1 where a < > >= <= like and b = like and c= # 不等值，只能覆盖到a select * from t1 where a = order by b # 走ab的索引 select * from t1 where c = order by a # 就不走索引，多子句要按照执行顺序建立联合索引，c和a没有按顺序，不会走索引 # 不覆盖 bc b c # 3.唯一索引 索引列的值都是唯一的. # 4.前缀索引 假设建立索引的列非常长，我们选择的索引列值长度过长（一个页存储的数据固定），会导致索引树变高，导致io次数变多 mysql中建议索引树高度3--4层，800w--1000w行，20--30个列，会在3--4层之间 数据量特别少，也会有两层，根和叶子 只取大字段的前几个字符，作为索引生成条件

六 关于索引树的高度受什么影响

1 2 3 4 5 6 7 8

# 那些因素导致 1. 数据行过多，数据量级大, 解决方法:分区表（分库分表），归档表（一个月生成一个表：手工，pt-archive），分布式架构 2. 索引列值过长 , 解决方法:前缀索引 3. 数据类型:（选择合适的数据类型） 变长长度字符串,使用了char,解决方案:变长字符串使用varchar enum类型的使用enum ('山东','河北','黑龙江','吉林','辽宁','陕西'......)，enum更加省空间 1 2 3

七 索引的基本管理

7.1 索引建立前

1 2 3 4 5 6

# 什么情况下建索引 按业务语句的需求创建合适的索引，并不是将所有列都建立索引（不是越多越好） 将索引建立在经常where，group by order by join on 的条件 # 为什么不能乱建索引 1 插入，删除数据，都会涉及到索引树的更新，如果冗余索引过多，表的数据变化，可能会导致索引频繁更新，会阻塞正常业务的更新请求 2 索引过多，会导致优化器选择出现偏差，性能可能达不到预想的效果

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19

db01 [world]>desc city; # 查看表的索引情况 +-------------+----------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------------+----------+------+-----+---------+----------------+ | ID | int(11) | NO | PRI | NULL | auto_increment | | Name | char(35) | NO | | | | | CountryCode | char(3) | NO | MUL | | | | District | char(20) | NO | | | | | Population | int(11) | NO | | 0 | | +-------------+----------+------+-----+---------+----------------+ 5 rows in set (0.00 sec) Field :列名字 key :有没有索引,索引类型 PRI: 主键索引（聚簇索引） UNI: 唯一索引，唯一建unique MUL: 辅助索引(单列,联和,前缀) show index from city; # 查看更具体的索引信息

7.1 单列普通辅助索引

7.1.1 创建索引，删除索引

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

### 新建索引 # 方式1 db01 [world]>alter table city add index idx_name(name); 表 索引名（列名） # 方式2 db01 [world]>create index idx_name1 on city(name); # 查看索引 db01 [world]>show index from city; # 注意: 以上操作不代表生产操作,我们不建议在一个列上建多个索引 同一个表中，索引名不能同名。 ##### 删除索引: db01 [world]>alter table city drop index idx_name1; 表名 索引名

7.2 覆盖索引(联合索引)

1	Master [world]>alter table city add index idx_co_po(countrycode,population);

7.3 前缀索引

1 2	db01 [world]>alter table city add index idx_di(district(5)); 注意：数字列不能用作前缀索引。

7.4 唯一索引

1 2	db01 [world]>alter table city add unique index idx_uni1(name); ERROR 1062 (23000): Duplicate entry 'San Jose' for key 'idx_uni1'

统计city表中，以省的名字为分组，统计组的个数

1 2 3 4

select district,count(id) from city group by district; 需求: 找到world下,city表中 name列有重复值的行,最后删掉重复的行 db01 [world]>select name,count(id) as cid from city group by name having cid>1 order by cid desc; db01 [world]>select * from city where name='suzhou';

7.5 查看是否走索引

1 2 3

# explain select * from city where name ='shanghai'; # 图一 type 是all 表示全表扫描 # 图二 type 是ref 表示走了索引

7.6 是否走索引压测

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24

# 1 创建数据库test :create database test charset='utf8'; # 2 导入100w条数据 source t100w.sql # 3 执行：模仿100个用户，同时查询select * from test.t_100w where k2='780P'，一共执行200次，平均一人两次 mysqlslap --defaults-file=/etc/my.cnf --concurrency=100 --iterations=1 --create-schema='test' --query="select * from test.t100w where k2='780P'" engine=innodb --number-of-queries=200 -uroot -verbose # 4 创建索引再测试： alter table test.t100w add index idx_name(k2); Benchmark Running for engine rbose Average number of seconds to run all queries: 0.084 seconds Minimum number of seconds to run all queries: 0.084 seconds Maximum number of seconds to run all queries: 0.084 seconds Number of clients running queries: 100 Average number of queries per client: 2 # 5 删除索引再测试 alter table test.t100w drop index idx_name; Benchmark Running for engine rbose Average number of seconds to run all queries: 51.012 seconds Minimum number of seconds to run all queries: 51.012 seconds Maximum number of seconds to run all queries: 51.012 seconds Number of clients running queries: 100 Average number of queries per client: 2

八 执行计划获取及分析

8.0 介绍

1 2 3 4 5 6 7 8

(1) 获取到的是优化器选择完成的,他认为代价最小的执行计划. 作用: 语句执行前,先看执行计划信息,可以有效的防止性能较差的语句带来的性能问题. 如果业务中出现了慢语句，我们也需要借助此命令进行语句的评估，分析优化方案。 (2) select 获取数据的方法 1. 全表扫描(应当尽量避免,因为性能低) 2. 索引扫描 3. 获取不到数据

8.1 执行计划获取

获取优化器选择后的执行计划

1 2 3 4 5

方式一：desc +sql 语句 desc select * from test.t100w; 方式二：explain +sel语句 explain select * from test.t100w; explain select * from test.t100wG;

8.2 执行计划分析

8.2.0 重点关注的信息

1 2 3 4 5 6 7

table: city # 查询操作的表 （后期可能多表关联查询） type:ref # 查询类型 （全表，索引扫描） possible_keys: CountryCode,idx_co_po # 可能会走的索引，执行计划会有多种方案 key: CountryCode # 真正走的索引名字，最后优化器选择的 key_len:null # 索引覆盖长度 rows:997529 #查询结果集的长度，此次查询需要扫描的行数 Extra: Using index condition # 额外信息

1 2 3 4 5

desc select country.name,city.name from city join country on city.countrycode=country.code where city.population='CHN'G; # city 表没有走索引，type 是all # 优化一下，给populations字段加索引 alter table city add index idx(population); # 再看，就走索引了

8.2.1 type详解

8.2.1.1 简介

1 2 3

type类型：all，index ， range ，ref， eq_ref，const(system) all：是全表扫描 index ，range ，ref，eq_ref，const(system)是索引扫描，但是顺序从左向右，效率依次提高

8.2.1.2 ALL

1 2 3 4 5 6 7

######## 全表扫描的例子######## #1 ALL : 全表扫描,不走索引 desc select * from city； desc select * from city where 1=1； desc select * from city where countrycode not in ('chn','usa'); # not in不走索引，in走索引 desc select * from city where countrycode like '%ch%'; # like前后都加%，不走索引，构建索引要排序，前面是百分号，没法排序，遵循最左前缀，左侧要确定 desc select * from city where countrycode !='usa'; # 不等于也会全文扫描

8.2.1.3 index

1 2 3 4

######## 索引扫描的例子######## # index < range <ref <eq_ref<const(system) # 2 index:全索引扫描 desc select countrycode from world.city; # countrycode有索引，但是需要扫描整棵索引树

8.2.1.4 range

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

# 3 range：索引范围查询 辅助索引> < >= <= LIKE IN OR 主键 <> NOT IN desc select * from city where id <10; desc select * from city where countrycode like 'CH%'; desc select * from city where countrycode in ('CHN','USA'); # 改写后，变成ref desc select * from city where countrycode ='CHN' union all select * from city where countrycode ='USA'; # 特殊情况，主键的不等于，not in 是range类型 desc select * from city where id !=10;# 做成了<10 and >10 desc select * from city where id not in (10,20);

8.2.1.5 ref

1 2	# 辅助索引等值查询 name='er'的情况 desc select * from city where countrycode ='CHN'

8.2.1.6 eq_ref

1 2 3 4 5

# 多表连接中，非驱动表连接条件是主键或唯一键 # A join B on A.xx=B.yy desc select country.name,city.name from city join country on city.countrycode=country.code where city.population='CHN'G; # 非驱动表使用了主键索引

8.2.1.7 const

1 2	唯一索引的等值查询 DESC SELECT * FROM city WHERE id=10;

8.2.2 其他字段解释

8.2.2.1 possible_keys和key

1 2	possible_keys：可能会走的索引，所有和此次查询有关的索引 key：此次查询选择的索引

8.2.2.2 key_len

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43

# 联合索引覆盖长度 # 对于联合索引：index(a,b,c)，我们希望将来的查询对联合索引用的越充分越好 # key_len 可以帮我们判断，此次查询走了联合索引的几部分 # key_len计算： select * from t1 where a = and b= or c= 上面语句完全使用联合索引 key_len=a长度+b长度+c长度 ##### 数字类型 not null约束 没有not null 约束 tinyint 1 1+1 int 4 4+1 bigint 8 8+1 # key_len: a列 int类型 not null ----》长度为4 a列 int类型 没有非空约束 ----》长度为5 #### 字符类型：utf8 ----》一个字符最大占3个字节 not null约束 没有not null 约束 char(10) 3*10 3*10+1 varchar(10) 3*10+2 3*10+2+1 # 选择此列最大字符长度 b列 char(10) not null ---》30 b列 char(10) 没有非空约束 ---》31 c列 varchar(10) not null ---》32 c列 varchar(10) 没有非空约 ---》33 # 假设是utf8mb4格式，该如何算？ create table t1( a int not null, 4 b int, 5 c char(10) not null, 40 d varchar(10) 43 )charset =utf8mb4 # index(a,b,c,d) # 问：查询中完全覆盖到4列索引，key_len是多少？ 92 # 测试：新建表，建立4列索引， desc select * from t1 where a =1 and b=2 or c='a' and d='c'; 92 desc select * from t1 where a =1 ; 4 # 通过数字可以判断是否完全走了索引

8.2.2.3 rows

1	# 评估查询需要扫描的数据行数

8.2.2.4 extra

1 2 3 4 5 6 7 8 9 10 11

# 如果出现useing filesort:表示此次查询使用到了文件排序，说明在查询中的排序操作(查询语句中有如下语句，索引应用的不是特别合理)：order by，group by ，distinct... DESC SELECT * FROM city WHERE countrycode='CHN' ORDER BY population # 可以看到使用了额外的排序 # 需要将countrycode和population建立联合索引，再次查询就没有useing filesort了，在索引里排好序了 结论: 1.当我们看到执行计划extra位置出现filesort,说明由文件排序出现 2.观察需要排序(ORDER BY,GROUP BY ,DISTINCT )的条件,有没有索引 3. 根据子句的执行顺序,去创建联合索引

8.2.3 explain(desc)使用场景（面试题）

1 2 3 4 5 6 7 8 9 10 11 12

题目意思: 我们公司业务慢,请你从数据库的角度分析原因 1.mysql出现性能问题,我总结有两种情况: （1）应急性的慢：突然夯住 应急情况:数据库hang(卡了,资源耗尽) 处理过程: 1.show processlist; 获取到导致数据库hang的语句 2. explain 分析SQL的执行计划,有没有走索引,索引的类型情况 3. 建索引,改语句 （2）一段时间慢(持续性的): (1)记录慢日志slowlog,分析slowlog (2)explain 分析SQL的执行计划,有没有走索引,索引的类型情况 (3)建索引,改语句

九 索引应用规范

1 2 3 4 5

业务 1.产品的功能 2.用户的行为 "热"查询语句 --->较慢--->slowlog "热"数据

9.1 建立索引的原则（DBA运维规范）

9.1.0 说明

1	为了使索引的使用效率更高，在创建索引时，必须考虑在哪些字段上创建索引和创建什么类型的索引。那么索引设计原则又是怎样的?

9.1.1 (必须的) 建表时一定要有主键,一般是个无关列

1	一定要有主键，数字列最好，无关业务

9.1.2 选择唯一性索引

1 2 3 4 5 6 7 8 9 10 11

唯一性索引的值是唯一的，可以更快速的通过该索引来确定某条记录。 例如，学生表中学号是具有唯一性的字段。为该字段建立唯一性索引可以很快的确定某个学生的信息。 如果使用姓名的话，可能存在同名现象，从而降低查询速度。 优化方案: (1) 如果非得使用重复值较多的列作为查询条件(例如:男女),可以将表逻辑拆分 (2) 可以将此列和其他的查询类,做联和索引 (3) 联合索引，要把重复值少的放在最左侧 select count(*) from world.city; select count(distinct countrycode) from world.city; select count(distinct countrycode,population ) from world.city;

9.1.3(必须的) 为经常需要where 、ORDER BY、GROUP BY,join on等操作的字段建立索引

1 2 3 4 5 6 7 8

排序操作会浪费很多时间。 where A B C ----》 A B C in where A group by B order by C A,B，C 如果为其建立索引，优化查询 注：如果经常作为条件的列，重复值特别多，可以建立联合索引。

9.1.4 尽量使用前缀来索引

1	如果索引字段的值很长，最好使用值的前缀来索引，减少索引树高度

9.1.5 限制索引的数目

1 2 3 4 5 6

索引的数目不是越多越好。 可能会产生的问题: (1) 每个索引都需要占用磁盘空间，索引越多，需要的磁盘空间就越大。 (2) 修改表时，对索引的重构和更新很麻烦。越多的索引，会使更新表变得很浪费时间。 (3) 优化器的负担会很重,有可能会影响到优化器的选择. percona-toolkit中有个工具,专门分析索引是否有用

9.1.6 删除不再使用或者很少使用的索引(percona toolkit)

1 2 3 4

pt-duplicate-key-checker 表中的数据被大量更新，或者数据的使用方式被改变后，原有的一些索引可能不再需要。数据库管理 员应当定期找出这些索引，将它们删除，从而减少索引对更新操作的影响。

9.1.7 大表加索引,要在业务不繁忙期间操作

9.1.8 尽量少在经常更新值的列上建索引

9.1.9 建索引原则

1 2 3 4 5 6

(1) 必须要有主键,如果没有可以做为主键条件的列,创建无关列 (2) 经常做为where条件列 order by group by join on, distinct 的条件(业务:产品功能+用户行为) (3) 最好使用唯一值多的列作为索引,如果索引列重复值较多,可以考虑使用联合索引 (4) 列值长度较长的索引列,我们建议使用前缀索引. (5) 降低索引条目,一方面不要创建没用索引,不常使用的索引清理,percona toolkit(xxxxx) (6) 索引维护要避开业务繁忙期

9.2 不走索引的情况（开发规范）

9.2.1 没有查询条件，或者查询条件没有建立索引

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

select * from tab; 全表扫描。 select * from tab where 1=1; 在业务数据库中，特别是数据量比较大的表。 是没有全表扫描这种需求。 1、对用户查看是非常痛苦的。 2、对服务器来讲毁灭性的。 （1） select * from tab; SQL改写成以下语句： select * from tab order by price limit 10 ; 需要在price列上建立索引 （2） select * from tab where name='zhangsan' name列没有索引 改： 1、换成有索引的列作为查询条件 2、将name列建立索引

9.2.2 查询结果集是原表中的大部分数据，应该是25％以上。

1 2 3 4 5 6 7 8

查询的结果集，超过了总数行数25%，优化器觉得就没有必要走索引了。 假如：tab表 id，name id:1-100w ，id列有(辅助)索引 select * from tab where id>500000; 如果业务允许，可以使用limit控制。 怎么改写 ？ 结合业务判断，有没有更好的方式。如果没有更好的改写方案 尽量不要在mysql存放这个数据了。放到redis里面。

9.2.3 索引本身失效，统计数据不真实

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23

索引有自我维护的能力。 对于表内容变化比较频繁的情况下，统计信息不准确，过旧，有可能会出现索引失效。 一般是删除重建 # 统计信息 现象: 有一条select语句平常查询时很快,突然有一天很慢,会是什么原因 select? --->索引失效,，统计数据不真实，大量修改，删除性的操作 DML ? --->锁冲突 解决： 重建索引，优化表 # 统计信息放在了mysql数据库的，数据改了，记录的统计信息不真实，会导致索引失效 innodb_index_stats innodb_table_stats select * from innodb_table_stats; # 有哪个库，哪个表，上次更新时间，数据行数，聚簇索引大小，辅助索引大小等 假设我们删除一部分数据，这个记录不是实时更新的 delete from city where id=100; # 再查看，行数不变，可以使用如下两条命令：优化表 optimize table world.city; alter table world.city engine=innodb; # 再查看就更新了

9.2.4 查询条件使用函数在索引列上，或者对索引列进行运算，运算包括(+，-，*，/，! 等)

1 2 3 4 5 6

例子： 错误的例子：select * from test where id-1=9; 正确的例子：select * from test where id=10; 算术运算 函数运算 子查询

9.2.5 隐式转换导致索引失效.这一点应当引起重视.也是开发中经常会犯的错误.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

这样会导致索引失效. 错误的例子： # 创建表 create table tab(id int,telnum char(11)); # 给telnum增加索引 mysql> alter table tab add index inx_tel(telnum); # 查看 mysql> desc tab; # 查询数据 mysql> select * from tab where telnum='1333333'; mysql> select * from tab where telnum=1333333; # 分析 # 走索引 mysql> explain select * from tab where telnum='1333333'; # 不走索引（出现了隐士转换，做了函数运算） mysql> explain select * from tab where telnum=1555555;

9.2.6 <> ，not in 不走索引（辅助索引）

1 2 3 4 5 6 7 8 9 10 11 12 13 14

# <> ，not in 不走索引，但是对于主键走range索引 EXPLAIN SELECT * FROM teltab WHERE telnum <> '110'; EXPLAIN SELECT * FROM teltab WHERE telnum NOT IN ('110','119'); mysql> select * from tab where telnum <> '1555555'; mysql> explain select * from tab where telnum <> '1555555'; 单独的>,<,in 有可能走，也有可能不走，和结果集有关（当查询结果集超过25%，也会不走索引），尽量结合业务添加limit or或in 尽量改成union EXPLAIN SELECT * FROM teltab WHERE telnum IN ('110','119'); 改写成： EXPLAIN SELECT * FROM teltab WHERE telnum='110' UNION ALL SELECT * FROM teltab WHERE telnum='119'

9.2.7 like “%_” 百分号在最前面不走

1 2 3

EXPLAIN SELECT * FROM teltab WHERE telnum LIKE '31%' 走range索引扫描 EXPLAIN SELECT * FROM teltab WHERE telnum LIKE '%110' 不走索引 %linux%类的搜索需求，可以使用elasticsearch+mongodb 专门做搜索服务的数据库产品

十扩展:优化器针对索引的算法

10.1 mysql索引的自优化-AHI

1 2 3 4 5

# 自适应哈希索引：AHI，自动统计索引页使用情况，内存中放在buffer pool中，可能会在内存回收的情况下，把经常使用的索引页回收（置换）掉（这是我们不希望看到的），我们需要把热的索引页，生成一个hash表的类型，存到AHI中 # 自带的，自优化能力 # 作用:自动评估 ’热‘的内存索引page，生成hash索引表，帮助innodb快速读取索引页，加速索引读取速度

10.2 mysql索引的自优化-Change buffer

1 2 3 4 5 6 7 8 9

比如insert，update，delete 数据 对于聚簇索引会立即更新 对于辅助索引，不是实时更新 在innodb内存结构中，加入了insert buffer（会话），现在的版本叫change buffer change buffer的功能是临时缓冲辅助索引需要的数据更新 当我们要查询新insert的数据，会在内存中进行merge(合并)操作，此时辅助索引就是最新的 每个会话都分一个，可以调整，但是不能调太大

以上是（AHI，Change buffer）自优化能力，不需要单独配置，下面的是优化算法

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39

show variables like '%switch%'; select @@optimizer_switchG; # 如下算法 index_merge=on, index_merge_union=on, index_merge_sort_union=on, index_merge_intersection=on, engine_condition_pushdown=on, index_condition_pushdown=on, # 索引下推 mrr=on, mrr_cost_based=on, # block_nested_loop=on, # batched_key_access=off, # materialization=on, semijoin=on, loosescan=on, firstmatch=on, duplicateweedout=on, subquery_materialization_cost_based=on, use_index_extensions=on, condition_fanout_filter=on, derived_merge=on # 如何修改？ 方式一： 配置文件my.cnf 方式二： set global optimizer_switch='index_condition_pushdown=on,mrr_cost_based=on'; set global optimizer_switch='batched_key_access=on'; # 重启会话，退出重连 方式三：单独给某个语句开 BKA hins方式 https://dev.mysql.com/doc/relnotes/mysql/5.7/en/news-5-7-7.html SELECT /*+ NO_RANGE_OPTIMIZATION(t3 PRIMARY, f2_idx) */ f1 FROM t3 WHERE f1 > 30 AND f1 < 33; SELECT /*+ BKA(t1) NO_BKA(t2) */ * FROM t1 INNER JOIN t2 WHERE ...; SELECT /*+ NO_ICP(t1, t2) */ * FROM t1 INNER JOIN t2 WHERE ...; EXPLAIN SELECT /*+ NO_ICP(t1) */ * FROM t1 WHERE ...;

10.3 ICP：索引下推

1 2 3 4 5 6 7 8 9 10 11 12 13

# 原理： select 查询语句在sql层解析后，由优化器选择好方案，进入引擎层后，再由引擎层进行一次过滤，过滤好后再访问硬盘的页的数据 ICP是在引擎层又进行一次过滤，把索引优化的能力，下推到了引擎层 # 作用： 减少无关数据页的扫描，最大程度使用索引,解决了联合索引只能部分应用的情况 将不走索引的条件，在engine层取出数据之前做二次过滤 过滤掉一些无关数据 ### 举个例子 假设有索引：index(a,b,c) 、 查询数据：select * from t1 where a= and c = 正常是在server层通过优化器优化，只能走a的索引，所以查a的数据走了索引，查c的数据还需要再全表扫描，这样导致扫描数据量很大（a走索引，c在a的结果集上走全表） 通过ICP，把索引优化下推到引擎层，在引擎层再做一次过滤，得到更少量的数据，从而提高io速度（本来是要拿出满足a条件的数据，然后在结果集上过滤c，现在拿出a的数据集之前再做一次过滤，数据集更少，然后再过滤c条件）

没有ICP的情况

1	server 层在做完索引优化以后，需要去磁盘上取4个数据页（红色的），但是实际上满足条件的只有一个，没有icp会多余读取3个没用的数据页

有ICP的情况

1	server 层在做完索引优化以后，需要去磁盘上取4个数据页（红色的），但是实际上满足条件的只有一个，到达engin层后，再做一次过滤，发现满足条件的只有一个页，所以，只取有用的那个页（其实就是引擎层又加了一个判断，减少无关数据页的扫描）

10.4 MRR-multi range read

1 2 3 4 5 6 7 8 9 10

mrr=on, # 开启 mrr_cost_based=on, #关闭，是否通过cost base的方式来启用MRR，由系统判断是否值得，我们关闭 set global optimizer_switch='mrr=on,mrr_cost_based=off'; # 原理 范围查询 （大于，小于） like查询 有重复值 从辅助索引得到一个id值就要回表一次 在回表之前，先把id预存一下（缓冲区），排一下序（sort id），最后一次性回表（这样有顺序的就可以通过B+树的neighbour直接顺序取）

mrr之前

mrr之后

10.5 SNLJ

10.6 BNLJ

10.7 BKA

十一 问题汇总

11.1 怎样减少回表

11.2 更新数据时，会对索引有影响吗，数据的变化索引实时更新吗？

 

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22

insert delete一行数据 聚簇索引会立即更新 辅助索引不是实时更新 update 一行数据 看是不是更新辅助索引字段（聚簇索引字段不会改），辅助索引不会立即变化 # 补充 在InnoDB内存结构中（内存空间），加入了insert buffer（会话缓冲区），现在叫change buffer 原来主要针对insert操作，现在修改插入删除都会走 1 聚簇索引，辅助索引，数据都在磁盘上存，innodb 存到ibd(表空间文件:有段，区，页)文件中 2 当去查询select * from t1 where name='zs',会把辅助索引的数据页加载到内存（buffer pool） 3 回表，需要聚簇索引，也加载到内存中 4 新录入数据，会更新聚簇索引，立即更新到磁盘 5 对于辅助索引，不是立即更新，先把变更放到change buffer（独立内存区域）中，这样磁盘上的辅助索引是旧数据 6 假设要读新插入的一行，mysql会在内存中把change buffer中的变更的辅助索引和原来内存中的辅助索引merge（合并）一下，这个过程叫index merge（在内存中合并到一起） 7 这样搜新插入的数据，是能搜到的 8 辅助索引没有实时更新，减少了更新的频次 9 当有查询操作查询这条数据后，辅助索引的数据会落到磁盘上（因为有查询需求） 10 一旦涉及到更新磁盘，就会有一定程度的阻塞 11 每个会话（每个链接上来）都会有一个change buffer，大小可以调，通过调change buffer来优化大量的update和删除等操作 12 当我们要查询新insert的数据，会在内存中将辅助索引合并，这样辅助索引就是最新的了（就是为了减少频繁磁盘更新）