MySQL高级笔记

一、MySQL架构介绍

1、MySQL 简介

1.1 概述

1.2 高级 MySQL

mysql 内核
sql 优化工程师
mysql 服务器的优化
查询语句优化
主从复制
软硬件升级
容灾备份
sql编程

2、MySQL Linux 版本的安装mysql—rpm方式

2.1 准备工作

2.1.1 下载地址

下载地址:https://dev.mysql.com/downloads/mysql/

2.1.2 检查当前系统是否安装过 mysql

　　（1）centos6 环境下

rpm -qa|grep mysql

　　默认 Linux 在安装的时候，自带了 mysql 相关的组件。

　　先卸载系统自带的 mysql ，执行卸载命令：

rpm -e --nodeps mysql-libs

　　（2）centos7 环境下

rpm -qa|grep mariadb

　　默认 Linux（centos7）在安装的时候，自带了 mariadb(mysql 完全开源版本)相关的组件。

　　先卸载系统自带的 mariadb，执行卸载命令 rpm -e --nodeps mariadb-libs

2.2 MySQL 安装

　　安装的版本是 mysql 5.5

　　（1）将 rpm 安装包拷贝到 opt/module 目录下

　　（2）安装目录下执行 rpm 安装 mysql 客户端

#安装 mysql 客户端
rpm -ivh MySQL-client-5.5.54-1.linux2.6.x86_64.rpm

　　（3）安装目录下执行 rpm 安装 mysql 客户端

#安装 mysql 服务端
rpm -ivh MySQL-server-5.5.54-1.linux2.6.x86_64.rpm

　　安装完成后，出现如上警告，需要为软件设置 root 用户的密码

　　（4）查看是否安装成功

mysqladmin --version

　　或者也可以通过 rpm 命令来查看

rpm -qa|grep MySQL

　　（5）给ROOT用户设置密码

　　注意：MySQL 服务启动后开始连接，首次连接成功：因为MySQL默认没有密码，所以这里我们没有输入密码就直接连上了。下面我们给ROOT用户设置密码

mysqladmin -u root password

2.3 MySQL 服务

2.3.1 MySQL 服务的启动和停止

　　（1）查看状态

service mysql status

　　（2）启动服务

service mysql start

　　（3）停止服务

service mysql stop

　　（4）重启服务

service mysql restart

　　启动之后，查看进程

ps -ef|grep mysql

2.3.2 MySQL 的安装位置

2.3.3 MySQL 服务的自启动

　　mysql 服务是开机自动启动的！

#设置开机自启
chkconfig mysql on

#查看启动级别
chkconfig --list|grep mysql

　　如果要取消开机自动启动，则输入命令

ntsysv

　　出现以下界面：

　　使用空格取消选中，然后按 TAB 确定！

2.3.4 MySQL 的重复启动问题

　　此时查看，多了很多进程：

　　杀死全部进城之后重启 MySQL 服务即可

2.4 修改字符集

2.4.1 常用命令

2.4.2 字符集乱码原因

　　如果在建库建表的时候，没有明确指定字符集，则采用默认的字符集 latin1,其中是不包含中文字符的。查看默认的编码字符集：

2.4.3 永久修改

　　（1）修改配置文件位置，并修改字符集和数据存储路径

　　在/usr/share/mysql/ 中找到 my.cnf 的配置文件，拷贝其中的 my-huge.cnf 到 /etc/ 并命名为 my.cnf 。添加以下内容后再重启服务。

[client] 
default-character-set=utf8 
[mysqld] 
character_set_server=utf8 
character_set_client=utf8 
collation-server=utf8_general_ci 
[mysql] 
default-character-set=utf8

　　再次查看

　　注意：已经创建的数据库的设定不会发生变化，参数修改只对新建的数据库有效！

　　（2）修改已创建库、表字符集　

修改数据库的字符集 
mysql> alter database mydb character set 'utf8'; 

修改数据表的字符集 
mysql> alter table mytbl convert to character set 'utf8';

　　（3）修改已经乱码数据

　　无论是修改 mysql 配置文件或是修改库、表字符集，都无法改变已经变成乱码的数据。

　　只能删除数据重新插入或更新数据才可以完全解决

2.5 设置大小写不敏感

　　（1）查看大小写是否敏感

show variables like '%lower_case_table_names%'

　　windows 系统默认大小写不敏感，但是 linux 系统是大小写敏感的

　　（2）设置大小写不敏感

　　在 my.cnf 这个配置文件 [mysqld] 中加入 lower_case_table_names = 1 ，然后重启服务器

　　注意：如果要设置属性为大小写不敏感，要在重启数据库实例之前就需要将原来的数据库和表转换为小写，否则将找不到数据库名。在进行数据库参数设置之前，需要掌握这个参数带来的影响，切不可盲目设置。

3、MySQL Linux 版本的安装—docker方式

　　以下操作步骤基于centos7的

3.1 centos7 安装 docker

　　（1）确定你是 CentOS7 及以上版本

cat /etc/redhat-release

　　（2）yum 安装 gcc 相关

　　（a）确保你的 CentOS 7 能上外网

ping www.baidu.com

　　（b）安装 gcc

yum -y install gcc

　　（c）安装 gcc-c++

yum -y install gcc-c++

　　（4）卸载旧版本

yum -y remove docker docker-common docker-selinux docker-engine

　　如果是 2018.3 官网版本

yum remove docker \
                  docker-client \
                  docker-client-latest \
                  docker-common \
                  docker-latest \
                  docker-latest-logrotate \
                  docker-logrotate \
                  docker-selinux \
                  docker-engine-selinux \
                  docker-engine

　　（6）设置 stable 镜像仓库

　　（a）大坑（不推荐使用）

yum-config-manager --add-repo https://download.docker.com/linux/centos/docker-ce.repo

　　因为网址是国外的，由于防火墙，几乎不能成功，经常连接超时

　　（b）推荐

yum-config-manager --add-repo https://mirrors.aliyun.com/docker-ce/linux/centos/docker-ce.repo

　　（7）更新 yum 软件包索引

yum makecache fast

　　（8）安装 docker-ce

yum -y install docker-ce

　　（9）启动 docker

systemctl start docker

　　（10）测试

docker version

docker run hello-world

　　（11）配置镜像加速

　　（a）创建级联目录

mkdir -p /etc/docker

　　（b）创建docker配置文件

vim /etc/docker/daemon.json

#网易云
{"registry-mirrors": ["http://hub-mirror.c.163.com"] }
 
 或者
 
 #阿里云
{
  "registry-mirrors": ["https://｛自已的编码｝.mirror.aliyuncs.com"]
}

　　（c）重新加载使其生效

systemctl daemon-reload

　　（d）重启 docker

systemctl restart docker

　　至此，Centos 7 的 docker 的安装配置已完成，如果想卸载，如下：

　　（12）卸载

systemctl stop docker

yum -y remove docker-ce

rm -rf /var/lib/docker

3.2 使用docker安装mysql

　　（1）查看本地 docker 是否有mysql镜像

docker images

　　（2）没有则拉取镜像

docker pull mysql:5.7

　　（3）创建用于挂载的目录（日志目录、数据目录、配置目录）

　　如果不创建挂载目录，一旦容器重启，则数据会全部丢失。

mkdir -p /usr/data/mysql/logs /usr/data/mysql/data /usr/data/mysql/conf

　　（4）创建 my.cnf 配置文件

[mysqld]
user=mysql
character-set-server=utf8
default_authentication_plugin=mysql_native_password
secure_file_priv=/var/lib/mysql
expire_logs_days=7
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
max_connections=1000
 
[client]
default-character-set=utf8
 
[mysql]
default-character-set=utf8

　　（5）创建容器实例并启动

docker run --restart=always -p 3306:3306 --name mysql -v /usr/data/mysql/conf:/etc/mysql  -v /usr/data/mysql/logs:/var/log/mysql -v /usr/data/mysql/data:/var/lib/mysql -v /usr/data/mysql/my.cnf:/etc/mysql/my.cnf -e MYSQL_ROOT_PASSWORD=123456 -d mysql:5.7

　　（a）如果该步骤报错，重启docker再运行该脚本即可（重启命令：systemctl restart docker 由于docker是层层封装创建容器的，因此即便是创建容器报错，也有可能创建成功容器，因此重新执行上述命令前，需要停止并删除已运行的mysql容器）

　　（b）此处注意，挂载的宿主机文件夹最好建立在有权限的文件夹下，刚开始我直接在/data下新建了相关文件夹用来挂载，容器一直重启。

命令详解：
--restart=always    当docker重启时，容器也重启
--name   容器命名
-p 3306:3306        宿主机和容器端口映射
-v（此命令中有三个-v参数） 挂载数据等内容，避免容器重启造成的数据丢失
-e 设置环境变量（此处设置了mysql的密码）
-d 后台启动（容器不会因为shell的退出而停止运行）

　　（6）查看是否启动成功（正在运行的容器）

docker ps

3.3 连接mysql

　　（1）在命令行连接

#进入mysql容器（可以使用名称或者容器ID进入）
docker exec -it mysql bash

# 连接mysql服务器
mysql -uroot -p123456

　　（2）使用Navicat远程连接

　　点击连接，选择mysql，输入ip地址与用户名密码，端口由于直接使用的宿主机的3306映射的mysql容器内的3306端口，因此直接写3306即可，写完后点击测试链接，显示连接成功即OK。

3.4 设置开机启动

　　（1）设置自启动

docker update --restart=always mysql

　　（2）查看启动级别

chkconfig --list|grep mysql

3.5 安装位置

　　Docker容器就是一个小型的Linux环境，进入到MySQL容器中，其结构与Linux基本一致

docker exec -it mysql /bin/bash

　　Linux环境下MySQL的安装目录

3.6 解决docker没有vim和vi命令问题

　　在进入docker启动的容器中，需要修改配置文件信息，但是发现vi命令不识别，解决办法记录：

　　（1）键盘输入命令

apt-get install vim

　　如果出现如下情况，则需进行第二步；

　　（2）键盘输入命令

apt-get update

　　（3）等更新完，再输入apt-get install vim，就会安装vim命令了

　　（4）测试一下

3.7 修改字符集

　　如果在建库建表的时候，没有明确指定字符集，则采用默认的字符集 latin1,其中是不包含中文字符的。

　　（1）进入到mysql数据库并查看字符集

show variables like 'character%';

show variables like '%char%';

　　可以看到，我们的字符集已经是utf8了，那是因为我们在容器初始化之前已经配置好了mysql配置文件的映射文件，里面已经设置好了编码

vim /etc/mysql/my.cnf

　　我们不妨测试修改一下将utf8改成默认字符集latin1,保存之后看是否生效

　　综上，发现字符修改生效。

　　测试完毕之后，切记一定要将字符编码改回utf8格式

　　注意：安装MySQL完毕之后，第一件事就是修改字符集编码。

4、MySQL 配置文件

　　MySQL 配置文件详解：

# MySql5.7配置文件my.cnf设置
[client]
port = 3306
socket = /tmp/mysql.sock
 
[mysqld]
###############################基础设置#####################################
#Mysql服务的唯一编号 每个mysql服务Id需唯一
server-id = 1
 
#服务端口号 默认3306
port = 3306
 
#mysql安装根目录
basedir = /opt/mysql
 
#mysql数据文件所在位置
datadir = /opt/mysql/data
 
#临时目录 比如load data infile会用到
tmpdir  = /tmp
 
#设置socke文件所在目录
socket  = /tmp/mysql.sock
 
#主要用于MyISAM存储引擎,如果多台服务器连接一个数据库则建议注释下面内容
skip-external-locking
 
#只能用IP地址检查客户端的登录，不用主机名
skip_name_resolve = 1
#数据库默认字符集,主流字符集支持一些特殊表情符号（特殊表情符占用4个字节）
character-set-server = utf8mb4
 
#数据库字符集对应一些排序等规则，注意要和character-set-server对应
collation-server = utf8mb4_general_ci
 
#设置client连接mysql时的字符集,防止乱码
init_connect='SET NAMES utf8mb4'
 
#是否对sql语句大小写敏感，1表示不敏感
lower_case_table_names = 1
 
#最大连接数
max_connections = 400
#最大错误连接数
max_connect_errors = 1000
 
#TIMESTAMP如果没有显示声明NOT NULL，允许NULL值
explicit_defaults_for_timestamp = true
 
#SQL数据包发送的大小，如果有BLOB对象建议修改成1G
max_allowed_packet = 128M
 
#MySQL连接闲置超过一定时间后(单位：秒)将会被强行关闭
#MySQL默认的wait_timeout  值为8个小时, interactive_timeout参数需要同时配置才能生效
interactive_timeout = 1800
wait_timeout = 1800
 
#内部内存临时表的最大值 ，设置成128M。
#比如大数据量的group by ,order by时可能用到临时表，
#超过了这个值将写入磁盘，系统IO压力增大
tmp_table_size = 134217728
max_heap_table_size = 134217728


 
##----------------------------用户进程分配到的内存设置BEGIN-----------------------------##
##每个session将会分配参数设置的内存大小
#用于表的顺序扫描，读出的数据暂存于read_buffer_size中，当buff满时或读完，将数据返回上层调用者
#一般在128kb ~ 256kb,用于MyISAM
#read_buffer_size = 131072
#用于表的随机读取，当按照一个非索引字段排序读取时会用到，
#一般在128kb ~ 256kb,用于MyISAM
#read_rnd_buffer_size = 262144
#order by或group by时用到
#建议先调整为2M，后期观察调整
sort_buffer_size = 2097152
#一般数据库中没什么大的事务，设成1~2M，默认32kb
binlog_cache_size = 524288
 
############################日  志 设置##########################################
#数据库错误日志文件
log_error = error.log
 
#慢查询sql日志设置
slow_query_log = 1
slow_query_log_file = slow.log
#检查未使用到索引的sql
log_queries_not_using_indexes = 1
#针对log_queries_not_using_indexes开启后，记录慢sql的频次、每分钟记录的条数
log_throttle_queries_not_using_indexes = 5
#作为从库时生效,从库复制中如何有慢sql也将被记录
log_slow_slave_statements = 1
#慢查询执行的秒数，必须达到此值可被记录
long_query_time = 2
#检索的行数必须达到此值才可被记为慢查询
min_examined_row_limit = 100
 
#mysql binlog日志文件保存的过期时间，过期后自动删除
expire_logs_days = 5
 
############################主从复制 设置########################################
#开启mysql binlog功能
log-bin=mysql-bin
#binlog记录内容的方式，记录被操作的每一行
binlog_format = ROW

#作为从库时生效,想进行级联复制，则需要此参数
log_slave_updates
 
#作为从库时生效,中继日志relay-log可以自我修复
relay_log_recovery = 1
 
#作为从库时生效,主从复制时忽略的错误
slave_skip_errors = ddl_exist_errors
 
##---redo log和binlog的关系设置BEGIN---##
#(步骤1) prepare dml相关的SQL操作，然后将redo log buff中的缓存持久化到磁盘
#(步骤2)如果前面prepare成功，那么再继续将事务日志持久化到binlog
#(步骤3)如果前面成功，那么在redo log里面写上一个commit记录
#当innodb_flush_log_at_trx_commit和sync_binlog都为1时是最安全的，
#在mysqld服务崩溃或者服务器主机crash的情况下，binary log只有可能丢失最多一个语句或者一个事务。
#但是都设置为1时会导致频繁的io操作，因此该模式也是最慢的一种方式。
#当innodb_flush_log_at_trx_commit设置为0，mysqld进程的崩溃会导致上一秒钟所有事务数据的丢失。
#当innodb_flush_log_at_trx_commit设置为2，只有在操作系统崩溃或者系统掉电的情况下，上一秒钟所有事务数据才可能丢失。
#commit事务时,控制redo log buff持久化磁盘的模式 默认为1
innodb_flush_log_at_trx_commit = 2
#commit事务时,控制写入mysql binlog日志的模式 默认为 0
#innodb_flush_log_at_trx_commit和sync_binlog都为1时，mysql最为安全但性能上压力也是最大
sync_binlog = 1
##---redo log 和 binlog的关系设置END---##
 
############################Innodb设置##########################################
#数据块的单位8k，默认是16k，16kCPU压力稍小，8k对select的吞吐量大
#innodb_page_size的参数值也影响最大索引长度，8k比16k的最大索引长度小
#innodb_page_size = 8192
#一般设置物理存储的60% ~ 70%
innodb_buffer_pool_size = 1G
 
#5.7.6之后默认16M
#innodb_log_buffer_size = 16777216
#该参数针对unix、linux，window上直接注释该参数.默认值为NULL
#O_DIRECT减少操作系统级别VFS的缓存和Innodb本身的buffer缓存之间的冲突
innodb_flush_method = O_DIRECT
 
#此格式支持压缩, 5.7.7之后为默认值
innodb_file_format = Barracuda
 
#CPU多核处理能力设置，假设CPU是2颗4核的，设置如下
#读多，写少可以设成2:6的比例
innodb_write_io_threads = 4
innodb_read_io_threads = 4
 
#提高刷新脏页数量和合并插入数量，改善磁盘I/O处理能力
#默认值200（单位：页）
#可根据磁盘近期的IOPS确定该值
innodb_io_capacity = 500
 
#为了获取被锁定的资源最大等待时间，默认50秒，超过该时间会报如下错误:
# ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
innodb_lock_wait_timeout = 30
 
#调整buffer pool中最近使用的页读取并dump的百分比,通过设置该参数可以减少转储的page数
innodb_buffer_pool_dump_pct = 40
 
#设置redoLog文件所在目录, redoLog记录事务具体操作内容
innodb_log_group_home_dir = /opt/mysql/redolog/
 
#设置undoLog文件所在目录, undoLog用于事务回滚操作
innodb_undo_directory = /opt/mysql/undolog/
 
#在innodb_log_group_home_dir中的redoLog文件数, redoLog文件内容是循环覆盖写入。
innodb_log_files_in_group = 3
 
#MySql5.7官方建议尽量设置的大些，可以接近innodb_buffer_pool_size的大小
#之前设置该值较大时可能导致mysql宕机恢复时间过长，现在恢复已经加快很多了
#该值减少脏数据刷新到磁盘的频次
#最大值innodb_log_file_size * innodb_log_files_in_group <= 512GB,单文件<=256GB
innodb_log_file_size = 1024M
 
#设置undoLog文件所占空间可以回收
#5.7之前的MySql的undoLog文件一直增大无法回收
innodb_undo_log_truncate = 1
innodb_undo_tablespaces = 3
innodb_undo_logs = 128
 
#5.7.7默认开启该参数 控制单列索引长度最大达到3072
#innodb_large_prefix = 1
 
#5.7.8默认为4个, Inodb后台清理工作的线程数
#innodb_purge_threads = 4
 
#通过设置配置参数innodb_thread_concurrency来限制并发线程的数量，
#一旦执行线程的数量达到这个限制，额外的线程在被放置到对队列中之前，会睡眠数微秒，
#可以通过设定参数innodb_thread_sleep_delay来配置睡眠时间
#该值默认为0,在官方doc上，对于innodb_thread_concurrency的使用，也给出了一些建议:
#(1)如果一个工作负载中，并发用户线程的数量小于64，建议设置innodb_thread_concurrency=0；
#(2)如果工作负载一直较为严重甚至偶尔达到顶峰，建议先设置innodb_thread_concurrency=128,
###并通过不断的降低这个参数，96, 80, 64等等，直到发现能够提供最佳性能的线程数
#innodb_thread_concurrency = 0
############################其他内容 设置##########################################
[mysqldump]
quick
max_allowed_packet = 128M
[mysql]
no-auto-rehash
[myisamchk]
key_buffer_size = 20M
sort_buffer_size = 256k
read_buffer = 2M
write_buffer = 2M
[mysqlhotcopy]
interactive-timeout
[mysqld_safe]
#增加每个进程的可打开文件数量.
open-files-limit = 28192

4.1 二进制日志： log-bin

　　主要作用：主从复制

# my.cnf
# 开启mysql binlog功能
log-bin=mysql-bin

4.2 错误日志：log-error

　　默认是关闭的，记录严重的警告和错误信息，每次启动和关闭的详细信息等。

# my.cnf
# 数据库错误日志文件
log-error = error.log

4.3 查询日志：log

　　默认关闭，记录查询的sql语句，如果开启会降低MySQL整体的性能，因为记录日志需要消耗系统资源。

# my.cnf
# 慢查询sql日志设置
slow_query_log = 1
slow_query_log_file = slow.log

4.4 数据文件

frm文件：存放表结构
myd文件：存放表数据
myi文件：存放表索引

# mysql5.7 使用.frm文件来存储表结构
# 使用 .ibd文件来存储表索引和表数据
-rw-r-----  1 mysql mysql   8988 Jun 25 09:31 pms_category.frm
-rw-r-----  1 mysql mysql 245760 Jul 21 10:01 pms_category.ibd

　　MySQL5.7的Innodb存储引擎可将所有数据存放于ibdata*的共享表空间，也可将每张表存放于独立的.ibd文件的独立表空间。

　　共享表空间以及独立表空间都是针对数据的存储方式而言的。

共享表空间: 某一个数据库的所有的表数据，索引文件全部放在一个文件中，默认这个共享表空间的文件路径在data目录下。默认的文件名为:ibdata1 初始化为10M。
独立表空间: 每一个表都将会生成以独立的文件方式来进行存储，每一个表都有一个.frm表描述文件，还有一个.ibd文件。其中这个文件包括了单独一个表的数据内容以及索引内容，默认情况下它的存储位置也是在表的位置之中。在配置文件my.cnf中设置： innodb_file_per_table。

4.5 如何配置

Windows：my.ini文件
Linux：/etc/my.cnf文件

5、MySQL 逻辑架构介绍

Connectors：指的是不同语言中与SQL的交互。
Connection Pool：管理缓冲用户连接，线程处理等需要缓存的需求。MySQL数据库的连接层。
Management Serveices & Utilities：系统管理和控制工具。备份、安全、复制、集群等等。。
SQL Interface：接受用户的SQL命令，并且返回用户需要查询的结果。
Parser：SQL语句解析器。
Optimizer：查询优化器，SQL语句在查询之前会使用查询优化器对查询进行优化。就是优化客户端请求query，根据客户端请求的 query 语句，和数据库中的一些统计信息，在一系列算法的基础上进行分析，得出一个最优的策略，告诉后面的程序如何取得这个 query 语句的结果。For Example： select uid,name from user where gender = 1;这个select查询先根据where语句进行选取，而不是先将表全部查询出来以后再进行gender过滤；然后根据uid和name进行属性投影，而不是将属性全部取出以后再进行过滤。最后将这两个查询条件联接起来生成最终查询结果。
Caches & Buffers：查询缓存。
Pluggable Storage Engines：存储引擎接口。MySQL区别于其他数据库的最重要的特点就是其插件式的表存储引擎(注意：存储引擎是基于表的，而不是数据库)。
File System：数据落地到磁盘上，就是文件的存储。

　　和其他数据库相比，MySQL有点与众不同，主要体现在存储引擎的架构上，插件式的存储引擎架构将查询处理和其他的系统任务以及数据的存储提取相分离。这种架构可以根据业务的需求和实际需求选择合适的存储引擎。

逻辑架构分层

连接层：最上层是一些客户端和连接服务，包含本地sock通信和大多数基于客户端/服务端工具实现的类似于tcp/ip的通信。主要完成一些类似于连接处理、授权认证、及相关的安全方案。在该层上引入了线程池的概念，为通过认证安全接入的客户端提供线程。同样在该层上可以实现基于SSL的安全链接。服务器也会为安全接入的每个客户端验证它所具有的操作权限。
服务层：MySQL的核心服务功能层，该层是MySQL的核心，包括查询缓存，解析器，解析树，预处理器，查询优化器。主要进行查询解析、分析、查询缓存、内置函数、存储过程、触发器、视图等，select操作会先检查是否命中查询缓存，命中则直接返回缓存数据，否则解析查询并创建对应的解析树。

引擎层：存储引擎层，存储引擎真正的负责了MySQL中数据的存储和提取，服务器通过API与存储引擎进行通信。不同的存储引擎具有的功能不同，这样我们可以根据自己的实际需要进行选取。
存储层：数据存储层，主要是将数据存储在运行于裸设备的文件系统之上，并完成与存储引擎的交互。

6、MySQL 存储引擎

6.1 查看命令

　　（1）看你的mysql现在已提供什么存储引擎

show engines；

　　（2）看你的mysql当前默认的存储引擎

show variables like 'default_storage_engine%';

6.2 InnoDB和MyISAM对比

二、索引优化分析

1、SQL 性能下降的原因

查询语句写的差
索引失效：索引建了，但是没有用上
关联查询太多join（设计缺陷或者不得已的需求）
服务器调优以及各个参数的设置（缓冲、线程数等）

2、常见通用的join查询

2.1 SQL 执行顺序

select              # 7
from                # 1
join                # 3
on                  # 2
where               # 4 
group by            # 5
having              # 6
distinct            # 8
order by            # 9
limit               # 10

2.2 七种 JOIN理论

/* 1 */
SELECT <select_list> FROM TableA A LEFT JOIN TableB B ON A.Key = B.Key;

/* 2 */
SELECT <select_list> FROM TableA A RIGHT JOIN TableB B ON A.Key = B.Key;

/* 3 */
SELECT <select_list> FROM TableA A INNER JOIN TableB B ON A.Key = B.Key;

/* 4 */
SELECT <select_list> FROM TableA A LEFT JOIN TableB B ON A.Key = B.Key WHERE B.Key IS NULL;

/* 5 */
SELECT <select_list> FROM TableA A RIGHT JOIN TableB B ON A.Key = B.Key WHERE A.Key IS NULL;

/* 6 */
SELECT <select_list> FROM TableA A FULL OUTER JOIN TableB B ON A.Key = B.Key;
/* MySQL不支持FULL OUTER JOIN这种语法 可以改成 1+2 */
SELECT <select_list> FROM TableA A LEFT JOIN TableB B ON A.Key = B.Key
UNION
SELECT <select_list> FROM TableA A RIGHT JOIN TableB B ON A.Key = B.Key;

/* 7 */
SELECT <select_list> FROM TableA A FULL OUTER JOIN TableB B ON A.Key = B.Key 
WHERE A.Key IS NULL OR B.Key IS NULL;
/* MySQL不支持FULL OUTER JOIN这种语法 可以改成 4+5 */
SELECT <select_list> FROM TableA A LEFT JOIN TableB B ON A.Key = B.Key 
WHERE B.Key IS NULL;
UNION
SELECT <select_list> FROM TableA A RIGHT JOIN TableB B ON A.Key = B.Key 
WHERE A.Key IS NULL;

3、索引简介

3.1 索引是什么？

　　MySQL官方对索引的定义为：索引（INDEX）是帮助MySQL高效获取数据的数据结构。

　　从而可以获得索引的本质：索引是排好序的快速查找数据结构。

　　索引的目的在于提高查询效率，可以类比字典的目录。如果要查mysql这个这个单词，我们肯定要先定位到m字母，然后从上往下找y字母，再找剩下的sql。如果没有索引，那么可能需要a---z，这样全字典扫描，如果我想找Java开头的单词呢？如果我想找Oracle开头的单词呢？？？

　　重点：索引会影响到MySQL查找(WHERE的查询条件)和排序(ORDER BY)两大功能！

　　除了数据本身之外，数据库还维护着一个满足特定查找算法的数据结构，这些数据结构以某种方式指向数据，这样就可以在这些数据结构的基础上实现高级查找算法，这种数据结构就是索引。

　　一般来说，索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储在磁盘上。

#Linux下查看磁盘空间命令 
df -h

　　我们平时所说的索引，如果没有特别指明，都是指B树（多路搜索树，并不一定是二叉的）结构组织的索引。其中聚集索引，次要索引，覆盖索引，复合索引，前缀索引，唯一索引默认都是使用B+树索引，统称索引。当然，除了B+树这种数据结构的索引之外，还有哈希索引（Hash Index）等。

3.2 索引的优势和劣势

优势

查找：类似大学图书馆的书目索引，提高数据检索的效率，降低数据库的IO成本
排序：通过索引対数据进行排序，降低数据排序的成本，降低了CPU的消耗

劣势

实际上索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录，所以索引列也是要占用空间的
虽然索引大大提高了查询速度，但是同时会降低表的更新速度，例如对表频繁的进行INSERT、UPDATE和DELETE。因为更新表的时候，MySQL不仅要保存数据，还要保存一下索引文件每次更新添加的索引列的字段，都会调整因为更新所带来的键值变化后的索引信息。
索引只是提高效率的一个因素，如果MySQL有大数据量的表，就需要花时间研究建立最优秀的索引。

3.3 MySQL索引分类

（1）索引分类

单值索引：一个索引只包含单个列，一个表可以有多个单列索引。
唯一索引：索引列的值必须唯一，但是允许空值。
复合索引：一个索引包含多个字段。

　　建议：一张表建的索引最好不要超过5个！

（2）基本语法

/* 1、创建索引 [UNIQUE]可以省略*/
/* 如果只写一个字段就是单值索引，写多个字段就是复合索引 */
CREATE [UNIQUE] INDEX indexName ON tabName(columnName(length));
ALTER TABLE tabName ADD [UNIQUE] INDEX indexName ON (columnName(length));

/* 2、删除索引 */
DROP INDEX [indexName] ON tabName;

/* 3、查看索引 */
/* 加上\G就可以以列的形式查看了 不加\G就是以表的形式查看 */
SHOW INDEX FROM tabName \G;

使用ALTER命令来为数据表添加索引

/* 1、该语句添加一个主键，这意味着索引值必须是唯一的，并且不能为NULL */
ALTER TABLE tabName ADD PRIMARY KEY(column_list);

/* 2、该语句创建索引的键值必须是唯一的(除了NULL之外，NULL可能会出现多次) */
ALTER TABLE tabName ADD UNIQUE indexName(column_list);

/* 3、该语句创建普通索引，索引值可以出现多次 */
ALTER TABLE tabName ADD INDEX indexName(column_list);

/* 4、该语句指定了索引为FULLTEXT，用于全文检索 */
ALTER TABLE tabName ADD FULLTEXT indexName(column_list);

3.4 MySQL 索引数据结构

　　索引数据结构：

- B+Tree索引
- Hash索引

Full-text全文索引
R-Tree索引

3.5 哪些情况需要创建索引　

- 主键自动建立主键索引（唯一 + 非空）
- 频繁作为查询条件的字段应该创建索引

- 查询中与其他表关联的字段，外键关系建立索引
- 查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度，建的复合索引尽量与Order by 一致

- 查询中统计或者分组字段（group by也和索引有关）

3.6 哪些情况不要建索引

- 记录太少的表。
- 经常增删改的表。

- 频繁更新的字段不适合创建索引。因为每次更新不单单是更新了记录还会更新索引，加重IO负担
- Where条件里用不到的字段不创建索引。

- 数据重复且分布平均的表字段，因此应该只为经常查询和经常排序的数据列建立索引。注意：如果某个数据列包含许多重复的内容，为它建立索引就没有太大的实际效果。
- 假如一个表有10万行记录，有一个字段A只有true和false两种值，并且每个值的分布概率大约为50%，那么对A字段建索引一般不会提高数据库的查询速度。索引的选择性是指索引列中不同值的数目与表中记录数的比。如果一个表中有2000条记录，表索引列有1980个不同的值，那么这个索引的选择性就是1980/2000=0.99。一个索引的选择性越接近于1，这个索引的效率就越高。

4、性能分析

4.1 MySQL Query Optimizer

　　MySQL中专门负责优化SELECT语句的优化器模块

　　主要功能：通过计算分析系统中收集到的统计信息，为客户端请求的Query提供它认为最优的执行计划（它认为最优的数据检索方式，但不见得是DBA认为最优的，这部分最耗费时间）

　　当客户端像MySQL请求一条Query，命令解析器模块完成请求分类，去别处是SELECT并转发给MySQL Query Optimizer ，MySQL Query Optimizer首先会对整条Query进行优化，处理掉一些常量表达式的预算，直接换算成常量值。并对Query中的查询条件进行简化和转换，如去掉一些无用或显而易见的条件，结构调整等。然后分析Query中的Hint信息（如果有），看显示Hint信息是否可以完全确定该Query的执行计划。如果没有Hint或Hint信息还不足以完全确定执行计划，则会读区所涉及对象的统计信息，根据Query进行写相应的计算分析，然后再得出最后的执行计划。

4.2 MySQL常见瓶颈

　　CPU：CPU在饱和的时候一般发生在数据装入内存或从磁盘上读取数据的时候

　　IO：磁盘I/O瓶颈发生在装入数据远大于内存容量的时候

　　服务器硬件的性能瓶颈：top free iostat和vmstat来查看系统的性能状态

4.3 Explain

4.3.1 Explain 是什么

EXPLAIN：SQL的执行计划，使用EXPLAIN关键字可以模拟优化器执行SQL查询语句，从而知道MySQL是如何处理SQL语句的。分析你的查询语句或是结构的性能瓶颈

4.3.2 Explain 怎么使用

语法：explain + SQL语句

4.3.3 Explain 能干嘛

　　可以查看以下信息

id：表的读取顺序
select_type：数据读取操作的操作类型
possible_keys：哪些索引可以使用
key：哪些索引被实际使用
ref：表之间的引用
rows：每张表有多少行被优化器查询

4.3.4 Explain 各个字段的解释

　　（1）id：表的读取和加载顺序

　　　　值有以下三种情况

id相同，执行顺序由上至下
id不同，如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行
id相同不同，同时存在。永远是id大的优先级最高，id相等的时候顺序执行

　　（2）select_type：数据查询的类型，主要是用于区别，普通查询、联合查询、子查询等的复杂查询。

- SIMPLE：简单的SELECT查询，查询中不包含子查询或者UNION。
- PRIMARY：查询中如果包含任何复杂的子部分，最外层查询则被标记为PRIMARY。
- SUBQUERY：在SELECT或者WHERE子句中包含了子查询。
- DERIVED：在FROM子句中包含的子查询被标记为DERIVED(衍生)，MySQL会递归执行这些子查询，把结果放在临时表中。
- UNION：如果第二个SELECT出现在UNION之后，则被标记为UNION；若UNION包含在FROM子句的子查询中，外层SELECT将被标记为DERIVED。
- UNION RESULT：从UNION表获取结果的SELECT。

　　（3）type：访问类型排列

　　　　从最好到最差依次是：system > const > eq_ref >ref > range > index > ALL。除了ALL没有用到索引，其他级别都用到索引了。

　　　　一般来说，得保证查询至少达到range级别，最好达到ref

- system——表只有一行记录（等于系统表），这是const类型的特例，平时不会出现，这个可以忽略不计。

- const——表示通过索引一次就找到了，const用于比较primary key或者unique索引。因为只匹配一行数据，所以很快。如将主键置于where中，MySQL就能将该查询转化为一个常量

- eq_ref——唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配，常见于主键或唯一索引扫描。除了 system 和const 类型之外, 这是最好的联接类型。

- ref——非唯一性索引扫描，返回本表和关联表某个值匹配的所有行，查出来有多条记录。

- range——只检索给定范围的行，一般就是在WHERE语句中出现了BETWEEN、< >、in等的查询。这种范围扫描索引比全表扫描要好，因为它只需要开始于索引树的某一点，而结束于另一点，不用扫描全部索引。

- index——FullIndex Scan，全索引扫描，index和ALL的区别为index类型只遍历索引树。

　　　　　　　　　　也就是说虽然ALL和index都是读全表，但是index是从索引中读的，ALL是从磁盘中读取的。

- ALL——Full Table Scan，没有用到索引，全表扫描。

　　（4）possible_keys

　　　　显示可能应用在这张表中的索引，一个或者多个。

　　　　查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询实际使用。

　　（5）key

　　　　实际使用的索引。如果为NULL，则没建或没有使用索引，即索引失效。

　　　　查询中如果使用了覆盖索引，则该索引仅仅出现在key列表中。与Extra有关

　　（6）key_len

　　　　表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度。

　　　 key_len显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出的。在不损失精度的情况下，长度越短越好。

　　　 key_len计算规则：https://blog.csdn.net/qq_34930488/article/details/102931490

　　　　　　-- 索引列为字符串类型的情况

　　　　　　　　1) 列长度：

　　　　　　　　2) 列是否为空： NULL(+1)，NOT NULL(+0)

　　　　　　　3) 字符集：如 utf8mb4=4,utf8=3,gbk=2,latin1=1

　　　　　　　　4) 列类型为字符：如 varchar(+2), char(+0)

　　　　　　　　计算公式：key_len=(表字符集长度) * 列长度 + 1(null) + 2(变长列)

　　　　　　-- 数值数据的key_len计算公式：

　　　　　　　　TINYINT允许NULL = 1 + 1(NULL)

　　　　　　　　SMALLINT允许为NULL = 2 + 1(NULL)

　　　　　　　　INT允许为NULL = 4 + 1(NULL)

　　　　　　-- 日期时间型的key_len计算：（针对mysql5.5及之前版本）

　　　　　　　　DATETIME允许为NULL = 8 + 1(NULL)

　　　　　　　　TIMESTAMP允许为NULL = 4 + 1(NULL)

　　（7）ref

　　　　显示索引的哪一列被使用了，如果可能的话，是一个常数。哪些列或常量被用于查找索引列上的值。

　　　　NULL const 库名.表名.字段

　　（8）rows

　　　　根据表统计信息及索引选用情况，大致估算出找到所需的记录需要读取的行数。

　　（9）Extra

　　　　包含不适合在其他列中显示但十分重要的额外信息。

- Using filesort

　　　　　　说明MySQL会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。

　　　　　　MySQL中无法利用索引完成的排序操作称为"文件内排序"。

- Using temporary

　　　　　　使用了临时表保存中间结果，MySQL在対查询结果排序时使用了临时表。常见于排序order by和分组查询group by。临时表対系统性能损耗很大。

- Using index

　　　　　　表示相应的SELECT操作中使用了覆盖索引，避免访问了表的数据行，效率不错！如果同时出现Using where，表示索引被用来执行索引键值的查找；如果没有同时出现Using where，表明索引用来读取数据而非执行查找动作。

- Using where：表明使用了WHERE过滤。
- Using join buffer：使用了连接缓存。
- impossible where：WHERE子句的值总是false，不能用来获取任何元组。

5、索引优化

5.1 索引分析

5.1.1 单表索引分析

　　（1）数据准备

DROP TABLE IF EXISTS `article`;

CREATE TABLE IF NOT EXISTS `article` (
    `id`          INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT '主键',
    `author_id`   INT(10) UNSIGNED NOT NULL COMMENT '作者id',
    `category_id` INT(10) UNSIGNED NOT NULL COMMENT '分类id',
    `views`       INT(10) UNSIGNED NOT NULL COMMENT '被查看的次数',
    `comments`    INT(10) UNSIGNED NOT NULL COMMENT '回帖的备注',
    `title`       VARCHAR(255)     NOT NULL COMMENT '标题',
    `content`     VARCHAR(255)     NOT NULL COMMENT '正文内容'
) COMMENT '文章';

INSERT INTO `article`(`author_id`, `category_id`, `views`, `comments`, `title`, `content`)
VALUES (1, 1, 1, 1, '1', '1'),
       (2, 2, 2, 2, '2', '2'),
       (3, 3, 3, 3, '3', '3'),
       (1, 1, 3, 3, '3', '3'),
       (1, 1, 4, 4, '4', '4');

　　（2）案例

　　查询category_id为1且comments大于1的情况下，views最多的article_id。

　　（a）编写SQL语句并且查看SQL执行计划

# 1、sql语句
SELECT id,author_id FROM article 
WHERE category_id = 1 AND comments > 1 ORDER BY views DESC LIMIT 1;

# 2、sql执行计划
EXPLAIN SELECT id,author_id FROM article 
WHERE category_id = 1 AND comments > 1 ORDER BY views DESC LIMIT 1;

　　结论：很显然，type是ALL，即是最坏的情况。Extra里还出现了 Using filesort，也是最坏的情况。优化是必须的。

　　（b）创建索引 idx_article_ccv

CREATE INDEX idx_article_ccv ON article(category_id,comments,views);

　　（c）查看当前索引

SHOW INDEX FROM article;

　　（d）查看现在SQL语句的执行计划

　　我们发现，创建符合索引idx_article_ccv之后，虽然解决了全表扫描（type变成range了）的问题，但是在order by排序的时候没有用到索引，MySQL居然还是用的Using filesort，为什么？

　　这是因为按照 BTree 索引的工作原理，先排序 category_id，如果遇到相同的 category_id 再排序 comments，如果遇到相同的 comments 则再排序 views。当 comments 字段在联合索引里处于中间位置时候，因 comments>1 条件是一个范围值（所谓range），MySQL 无法利用索引再对后面的 views 部分进行检索，即 range 类型查询字段后面的索引无效。

　　（e）我们试试修改SQL，看看SQL的执行计划

SELECT id,author_id FROM article 
WHERE category_id = 1 AND comments = 1 ORDER BY views DESC LIMIT 1;

　　推论：当comments > 1的时候order by排序views字段索引就用不上，但是当comments = 1的时候order by排序views字段索引就可以用上！！！所以，范围之后的索引会失效。

　　（f）我们现在知道范围之后的索引会失效，原来的索引idx_article_ccv最后一个字段views会失效，那么我们如果删除这个索引，创建idx_article_cv索引呢？？？？

#删除索引 idx_article_ccv 
DROP INDEX idx_article_ccv ON article;

/* 创建索引 idx_article_cv */
CREATE INDEX idx_article_cv ON article(category_id,views);

　　查看当前的索引

　　（g）当前索引是idx_article_cv，来看一下SQL执行计划。

5.1.2 两张表索引分析

　　（1）数据准备

CREATE TABLE IF NOT EXISTS `class`(
`id` INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
`card` INT (10) UNSIGNED NOT NULL
);
CREATE TABLE IF NOT EXISTS `book`(
`bookid` INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
`card` INT (10) UNSIGNED NOT NULL
);
INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
 
INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));

　　（2）案例

　　两表连接查询的 SQL 执行计划

　　（a）不创建索引的情况下，SQL 的执行计划

explain select * from book left join class on book.card=class.card;

　　book和class两张表都是没有使用索引，全表扫描，那么如果进行优化，索引是创建在book表还是创建在class表呢？下面进行大胆的尝试！

　　（b）左表(book表)创建索引idx_book_card

/* 在book表创建索引 */
CREATE INDEX idx_book_card ON book(card);

　　在book表中有idx_book_card索引的情况下，查看SQL执行计划

　　（c）删除book表的索引，右表(class表)创建索引idx_class_card。

/* 删除book表的索引 */
DROP INDEX idx_book_card ON book;

/* 在class表创建索引 */
CREATE INDEX idx_class_card ON class(card);

　　在class表中有idx_class_card索引的情况下，查看SQL执行计划

　　可以看出第二行的 type 变为了 ref， rows 也变成了优化比较明显。这是由做链接特性决定的，LEFT JOIN 条件用于确定如何从右表搜索行，左边一定都有，所以右边是我们的关键点，一定需要建立索引。

　　由此可见，左连接将索引创建在右表上更合适，右连接将索引创建在左表上更合适（ref>index）。

5.1.3 三张表索引分析

　　（1）数据准备

CREATE TABLE IF NOT EXISTS `phone`(
`phoneid` INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
`card` INT (10) UNSIGNED NOT NULL
)ENGINE = INNODB;

INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));

　　（2）案例

　　三表连接查询 SQL 优化

　　（a）不加任何索引，查看SQL执行计划。

SELECT
    *
FROM
    class
LEFT JOIN book ON class.card = book.card
LEFT JOIN phone ON book.card = phone.card;

　　（b）根据两表查询优化的经验，左连接需要在右表上添加索引，所以尝试在book表和phone表上添加索引。

/* 在book表创建索引 */
CREATE INDEX idx_book_card ON book(card);

/* 在phone表上创建索引 */
CREATE INDEX idx_phone_card ON phone(card);

　　再次执行SQL的执行计划

　　后两个都是ref且总row优化很好，效果不错。因此索引最好设置再需要经常查询的字段中。

5.1.4 结论

　　JOIN语句的优化：

- 尽可能减少JOIN语句中的NestedLoop（嵌套循环）的总次数：永远都是小的结果集驱动大的结果集。
- 优先优化NestedLoop的内层循环。

保证JOIN语句中被驱动表上JOIN条件字段已经被索引。
当无法保证被驱动表的JOIN条件字段被索引且内存资源充足的前提下，不要太吝惜Join Buffer 的设置。

5.2 索引失效

　　数据准备

CREATE TABLE staffs(
id INT PRIMARY KEY AUTO_INCREMENT,
`name` VARCHAR(24)NOT NULL DEFAULT'' COMMENT'姓名',
`age` INT NOT NULL DEFAULT 0 COMMENT'年龄',
`pos` VARCHAR(20) NOT NULL DEFAULT'' COMMENT'职位',
`add_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT'入职时间'
)CHARSET utf8 COMMENT'员工记录表';

INSERT INTO staffs(`name`,`age`,`pos`,`add_time`) VALUES('z3',22,'manager',NOW());
INSERT INTO staffs(`name`,`age`,`pos`,`add_time`) VALUES('July',23,'dev',NOW());
INSERT INTO staffs(`name`,`age`,`pos`,`add_time`) VALUES('2000',23,'dev',NOW());

ALTER TABLE staffs ADD INDEX index_staffs_nameAgePos(`name`,`age`,`pos`)

5.2.1 索引失效的情况

全值匹配我最爱。
最佳左前缀法则。

不在索引列上做任何操作（计算、函数、(自动or手动)类型转换），会导致索引失效而转向全表扫描。
索引中范围条件右边的字段会全部失效。

尽量使用覆盖索引（只访问索引的查询，索引列和查询列一致），减少SELECT *。
MySQL在使用!=或者<>的时候无法使用索引会导致全表扫描。

is null、is not null也无法使用索引。
like以通配符开头%abc索引失效会变成全表扫描（使用覆盖索引就不会全表扫描了）。

字符串不加单引号索引失效。
少用or，用它来连接时会索引失效。

5.2.2 最佳左前缀法则

　　案例

/* 用到了idx_staffs_name_age_pos索引中的name字段 */
EXPLAIN SELECT * FROM `staffs` WHERE `name` = 'Ringo';

/* 用到了idx_staffs_name_age_pos索引中的name, age字段 */
EXPLAIN SELECT * FROM `staffs` WHERE `name` = 'Ringo' AND `age` = 18;

/* 用到了idx_staffs_name_age_pos索引中的name，age，pos字段 这是属于全值匹配的情况！！！*/
EXPLAIN SELECT * FROM `staffs` WHERE `name` = 'Ringo' AND `age` = 18 AND `pos` = 'manager';

/* 索引没用上，ALL全表扫描 */
EXPLAIN SELECT * FROM `staffs` WHERE `age` = 18 AND `pos` = 'manager';

/* 索引没用上，ALL全表扫描 */
EXPLAIN SELECT * FROM `staffs` WHERE `pos` = 'manager';

/* 用到了idx_staffs_name_age_pos索引中的name字段，pos字段索引失效 */
EXPLAIN SELECT * FROM `staffs` WHERE `name` = 'Ringo' AND `pos` = 'manager';

　　概念

最佳左前缀法则：如果索引是多字段的复合索引，要遵守最佳左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的字段。

　　口诀：带头大哥不能死，中间兄弟不能断。

5.2.3 索引列上不计算

　　（1）案例

# 现在要查询`name` = 'Ringo'的记录下面有两种方式来查询！

# 1、直接使用 字段 = 值的方式来计算
mysql> SELECT * FROM `staffs` WHERE `name` = 'Ringo';
+----+-------+-----+---------+---------------------+
| id | name  | age | pos     | add_time            |
+----+-------+-----+---------+---------------------+
|  1 | Ringo |  18 | manager | 2020-08-03 08:30:39 |
+----+-------+-----+---------+---------------------+
1 row in set (0.00 sec)

# 2、使用MySQL内置的函数
mysql> SELECT * FROM `staffs` WHERE LEFT(`name`, 5) = 'Ringo';
+----+-------+-----+---------+---------------------+
| id | name  | age | pos     | add_time            |
+----+-------+-----+---------+---------------------+
|  1 | Ringo |  18 | manager | 2020-08-03 08:30:39 |
+----+-------+-----+---------+---------------------+
1 row in set (0.00 sec)

　　我们发现以上两条SQL的执行结果都是一样的，但是执行效率有没有差距呢？

　　通过分析两条SQL的执行计划来分析性能。

　　由此可见，在索引列上进行计算，会使索引失效。

　　口诀：索引列上不计算。

5.2.4 范围之后会失效

　　案例

/* 用到了idx_staffs_name_age_pos索引中的name，age，pos字段 这是属于全值匹配的情况！！！*/
EXPLAIN SELECT * FROM `staffs` WHERE `name` = 'Ringo' AND `age` = 18 AND `pos` = 'manager';


/* 用到了idx_staffs_name_age_pos索引中的name，age字段，pos字段索引失效 */
EXPLAIN SELECT * FROM `staffs` WHERE `name` = '张三' AND `age` > 18 AND `pos` = 'dev';

　　查看上述SQL的执行计划

　　由此可知，查询范围的字段使用到了索引，但是范围之后的索引字段会失效

　　口诀：范围之后全失效。

5.2.5 覆盖索引尽量用

　　在写SQL的不要使用SELECT *，用什么字段就查询什么字段。

/* 没有用到覆盖索引 */
EXPLAIN SELECT * FROM `staffs` WHERE `name` = 'Ringo' AND `age` = 18 AND `pos` = 'manager';

/* 用到了覆盖索引 */
EXPLAIN SELECT `name`, `age`, `pos` FROM `staffs` 
WHERE `name` = 'Ringo' AND `age` = 18 AND `pos` = 'manager';

　　口诀：查询一定不用*。

5.2.6 不等有时会失效

/* 会使用到覆盖索引 */
EXPLAIN SELECT `name`, `age`, `pos` FROM `staffs` WHERE `name` != 'Ringo';
type：index

/* 索引失效 全表扫描 */
EXPLAIN SELECT * FROM `staffs` WHERE `name` != 'Ringo';
type：ALL

5.2.7 like 百分百加右边

/* 索引失效 全表扫描 */
EXPLAIN SELECT * FROM `staffs` WHERE `name` LIKE '%ing%';
type：ALL

/* 索引失效 全表扫描 */
EXPLAIN SELECT * FROM `staffs` WHERE `name` LIKE '%ing';
type：ALL

/* 使用索引范围查询 */
EXPLAIN SELECT * FROM `staffs` WHERE `name` LIKE 'Rin%';
type：range

　　口诀：like百分加右边。

　　如果一定要使用%like，而且还要保证索引不失效，那么使用覆盖索引来编写SQL。

/* 使用到了覆盖索引 */
EXPLAIN SELECT `id` FROM `staffs` WHERE `name` LIKE '%in%';
type：index

/* 使用到了覆盖索引 */
EXPLAIN SELECT `name` FROM `staffs` WHERE `name` LIKE '%in%';
type：index

/* 使用到了覆盖索引 */
EXPLAIN SELECT `age` FROM `staffs` WHERE `name` LIKE '%in%';
type：index

/* 使用到了覆盖索引 */
EXPLAIN SELECT `pos` FROM `staffs` WHERE `name` LIKE '%in%';
type：index

/* 使用到了覆盖索引 */
EXPLAIN SELECT `id`, `name` FROM `staffs` WHERE `name` LIKE '%in%';
type：index

/* 使用到了覆盖索引 */
EXPLAIN SELECT `id`, `age` FROM `staffs` WHERE `name` LIKE '%in%';
type：index

/* 使用到了覆盖索引 */
EXPLAIN SELECT `id`,`name`, `age`, `pos` FROM `staffs` WHERE `name` LIKE '%in';
type：index

/* 使用到了覆盖索引 */
EXPLAIN SELECT `id`, `name` FROM `staffs` WHERE `pos` LIKE '%na';
type：index

/* 索引失效 全表扫描 */
EXPLAIN SELECT `name`, `age`, `pos`, `add_time` FROM `staffs` WHERE `name` LIKE '%in';
type：ALL

5.2.8 字符要加单引号

/* 使用到了覆盖索引 */
EXPLAIN SELECT `id`, `name` FROM `staffs` WHERE `name` = 'Ringo';

/* 使用到了覆盖索引 */
EXPLAIN SELECT `id`, `name` FROM `staffs` WHERE `name` = 2000;

/* 索引失效 全表扫描 */
EXPLAIN SELECT * FROM `staffs` WHERE `name` = 2000;

　　这里name = 2000在MySQL中会发生强制类型转换，将数字转成字符串。

　　口诀：字符要加单引号。

5.2.9 索引相关题目

　　假设 index(a,b,c)

5.2.10 面试题分析

　　（1）数据准备

/* 创建表 */
CREATE TABLE `test03`(
`id` INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
`c1` CHAR(10),
`c2` CHAR(10),
`c3` CHAR(10),
`c4` CHAR(10),
`c5` CHAR(10)
);

/* 插入数据 */
INSERT INTO `test03`(`c1`,`c2`,`c3`,`c4`,`c5`) VALUES('a1','a2','a3','a4','a5');
INSERT INTO `test03`(`c1`,`c2`,`c3`,`c4`,`c5`) VALUES('b1','b22','b3','b4','b5');
INSERT INTO `test03`(`c1`,`c2`,`c3`,`c4`,`c5`) VALUES('c1','c2','c3','c4','c5');
INSERT INTO `test03`(`c1`,`c2`,`c3`,`c4`,`c5`) VALUES('d1','d2','d3','d4','d5');
INSERT INTO `test03`(`c1`,`c2`,`c3`,`c4`,`c5`) VALUES('e1','e2','e3','e4','e5');

/* 创建复合索引 */
CREATE INDEX idx_test03_c1234 ON `test03`(`c1`,`c2`,`c3`,`c4`);

　　（2）题目

/* 最好索引怎么创建的，就怎么用，按照顺序使用，避免让MySQL再自己去翻译一次 */

/* 1.全值匹配 用到索引c1 c2 c3 c4全字段 */
EXPLAIN SELECT * FROM `test03` 
WHERE `c1` = 'a1' AND `c2` = 'a2' AND `c3` = 'a3' AND `c4` = 'a4';

/* 2.用到索引c1 c2 c3 c4全字段 MySQL的查询优化器会优化SQL语句的顺序*/
EXPLAIN SELECT * FROM `test03` 
WHERE `c1` = 'a1' AND `c2` = 'a2' AND `c4` = 'a4' AND `c3` = 'a3';

/* 3.用到索引c1 c2 c3 c4全字段 MySQL的查询优化器会优化SQL语句的顺序*/
EXPLAIN SELECT * FROM `test03` 
WHERE `c4` = 'a4' AND `c3` = 'a3' AND `c2` = 'a2' AND `c1` = 'a1';

/* 4.用到索引c1 c2 c3字段，c4字段失效，范围之后全失效 type：range*/
EXPLAIN SELECT * FROM `test03` 
WHERE `c1` = 'a1' AND `c2` = 'a2' AND `c3` > 'a3' AND `c4` = 'a4';

/* 5.用到索引c1 c2 c3 c4全字段 MySQL的查询优化器会优化SQL语句的顺序 type：range*/
EXPLAIN SELECT * FROM `test03` 
WHERE `c1` = 'a1' AND `c2` = 'a2' AND `c4` > 'a4' AND `c3` = 'a3';

/* 6.用到了索引c1 c2 c3三个字段, c1和c2两个字段用于查找,  
        c3字段用于排序了但是没有统计到key_len中，c4字段失效*/
EXPLAIN SELECT * FROM `test03` 
WHERE `c1` = 'a1' AND `c2` = 'a2' AND `c4` = 'a4' ORDER BY `c3`;

/* 7.用到了索引c1 c2 c3三个字段，c1和c2两个字段用于查找, 
         c3字段用于排序了但是没有统计到key_len中*/
EXPLAIN SELECT * FROM `test03` 
WHERE `c1` = 'a1' AND `c2` = 'a2' ORDER BY `c3`;

/* 8.用到了索引c1 c2两个字段，c4失效，c1和c2两个字段用于查找，
         c4字段排序产生了 Using filesort 说明排序没有用到c4字段 */
EXPLAIN SELECT * FROM `test03` 
WHERE `c1` = 'a1' AND `c2` = 'a2' ORDER BY `c4`;

/* 9.用到了索引c1 c2 c3三个字段，c1用于查找，c2和c3用于排序 */
EXPLAIN SELECT * FROM `test03` 
WHERE `c1` = 'a1' AND `c5` = 'a5' ORDER BY `c2`, `c3`;

/* 10.用到了c1一个字段，c1用于查找，c3和c2两个字段索引失效，产生了Using filesort */
EXPLAIN SELECT * FROM `test03` 
WHERE `c1` = 'a1' AND `c5` = 'a5' ORDER BY `c3`, `c2`;

/* 11.用到了c1 c2 c3三个字段，c1 c2用于查找，c2 c3用于排序 */
EXPLAIN SELECT * FROM `test03` 
WHERE `c1` = 'a1' AND  `c2` = 'a2' ORDER BY c2, c3;

/* 12.用到了c1 c2 c3三个字段，c1 c2用于查找，c2 c3用于排序 */
EXPLAIN SELECT * FROM `test03` 
WHERE `c1` = 'a1' AND  `c2` = 'a2' AND `c5` = 'a5' ORDER BY c2, c3;

/* 13.用到了c1 c2 c3三个字段，c1 c2用于查找，c2 c3用于排序 没有产生Using filesort 
      因为之前c2这个字段已经确定了是'a2'了，这是一个常量，
      再去ORDER BY c3,c2 这时候c2已经不用排序了！
      所以没有产生Using filesort 和(10)进行对比学习！*/
EXPLAIN SELECT * FROM `test03` 
WHERE `c1` = 'a1' AND `c2` = 'a2' AND `c5` = 'a5' ORDER BY c3, c2;


/* GROUP BY 表面上是叫做分组，但是分组之前必定排序。 */

/* 14.用到c1 c2 c3三个字段，c1用于查找，c2 c3用于排序，c4失效 */
EXPLAIN SELECT * FROM `test03` WHERE `c1` = 'a1' AND `c4` = 'a4' GROUP BY `c2`,`c3`;

/* 15.用到c1这一个字段，c4失效，c2和c3排序失效产生了Using filesort Using temporary 
            5.7之后这种不是分组函数和GROUP BY出现的字段不能select*/
EXPLAIN SELECT * FROM `test03` WHERE `c1` = 'a1' AND `c4` = 'a4' GROUP BY `c3`,`c2`;

　　GROUP BY基本上都需要进行排序，索引优化几乎和ORDER BY一致，但是GROUP BY会有临时表的产生。

5.2.11 总结

　　索引优化的一般性建议：

- 对于单值索引，尽量选择针对当前query过滤性更好的索引。
- 在选择复合索引的时候，当前query中过滤性最好的字段在索引字段顺序中，位置越靠前越好。

- 在选择复合索引的时候，尽量选择可以能够包含当前query中的where子句中更多字段的索引。
- 尽可能通过分析统计信息和调整query的写法来达到选择合适索引的目的。

　　口诀

　　带头大哥不能死。

　　中间兄弟不能断。

　　索引列上不计算。

　　范围之后全失效。

　　覆盖索引尽量用。

　　不等有时会失效。

　　like百分加右边。

　　字符要加单引号。

　　一般SQL少用or。

三、查询截取分析

1、分析慢 SQL 的步骤

　　分析

1. 观察，至少跑1天，看看生产的慢 SQL情况。
2. 开启慢查询日志，设置阈值，比如超过5秒钟的就是慢SQL，并将它抓取出来。
3. explain + 慢SQL分析。
4. show Profile。
5. 运维经理 OR DBA，进行MySQL数据库服务器的参数调优。

　　总结（大纲）

1. 慢查询的开启并捕获。
2. explain + 慢SQL分析。
3. show Profile查询SQL在MySQL数据库中的执行细节和生命周期情况。
4. MySQL数据库服务器的参数调优。

2、查询优化

2.1 小表驱动大表

　　优化原则：对于MySQL数据库而言，永远都是小表驱动大表。

/**
* 举个例子：可以使用嵌套的for循环来理解小表驱动大表。
* 以下两个循环结果都是一样的，但是对于MySQL来说不一样，
* 第一种可以理解为，和MySQL建立5次连接每次查询1000次。
* 第二种可以理解为，和MySQL建立1000次连接每次查询5次。
*/
for(int i = 1; i <= 5; i ++){
    for(int j = 1; j <= 1000; j++){
        
    }
}
// ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~
for(int i = 1; i <= 1000; i ++){
    for(int j = 1; j <= 5; j++){
        
    }
}

IN 和 EXISTS

#优化原则：小表驱动大表，即小的数据集驱动大的数据集

#IN适合B表比A表数据小的情况
SELECT * FROM A WHERE id IN (SELECT id FROM B)
#等价于
for SELECT id FROM B
for SELECT * FROM A WHERE A.id = B.id

#EXISTS适合B表比A表数据大的情况
SELECT * FROM A WHERE EXISTS (SELECT 1 FROM B WHERE B.id = A.id);
#等价于
for SELECT * FROM A 
for SELECT * FROM B WHERE B.id = A.id

EXISTS：

语法：SELECT...FROM tab WHERE EXISTS(subquery);该语法可以理解为：

　　该语法可以理解为：将主查询的数据，放到子查询中做条件验证，根据验证结果（true或是false）来决定主查询的数据结果是否得以保留。

提示：

EXISTS(subquery)子查询只返回true或者false，因此子查询中的SELECT *可以是SELECT 1 OR SELECT X，它们并没有区别。
EXISTS(subquery)子查询的实际执行过程可能经过了优化而不是我们理解上的逐条对比，如果担心效率问题，可进行实际检验以确定是否有效率问题。
EXISTS(subquery)子查询往往也可以用条件表达式，其他子查询或者JOIN替代，何种最优需要具体问题具体分析。

2.2 ORDER BY优化

　　（1）数据准备

CREATE TABLE `talA`(
 id integer primary key auto_increment,
`age` INT,
`birth` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO `talA`(`age`) VALUES(18);
INSERT INTO `talA`(`age`) VALUES(19);
INSERT INTO `talA`(`age`) VALUES(20);
INSERT INTO `talA`(`age`) VALUES(21);
INSERT INTO `talA`(`age`) VALUES(22);
INSERT INTO `talA`(`age`) VALUES(23);
INSERT INTO `talA`(`age`) VALUES(24);
INSERT INTO `talA`(`age`) VALUES(25);

/* 创建索引 */
CREATE INDEX idx_talA_age_birth ON `talA`(`age`, `birth`);

　　（2）案例

/* 1.使用索引进行排序了 不会产生Using filesort */
EXPLAIN SELECT * FROM `talA` WHERE `age` > 20 ORDER BY `age`;

/* 2.使用索引进行排序了 不会产生Using filesort */
EXPLAIN SELECT * FROM `talA` WHERE `age` > 20 ORDER BY `age`,`birth`;

/* 3.没有使用索引进行排序 产生了Using filesort */
EXPLAIN SELECT * FROM `talA` WHERE `age` > 20 ORDER BY `birth`;

/* 4.没有使用索引进行排序 产生了Using filesort */
EXPLAIN SELECT * FROM `talA` WHERE `age` > 20 ORDER BY `birth`,`age`;

/* 5.没有使用索引进行排序 产生了Using filesort */
EXPLAIN SELECT * FROM `talA` ORDER BY `birth`;

/* 6.没有使用索引进行排序 产生了Using filesort */
EXPLAIN SELECT * FROM `talA` WHERE `birth` > '2020-08-04 07:42:21' ORDER BY `birth`;

/* 7.使用索引进行排序了 不会产生Using filesort */
EXPLAIN SELECT * FROM `talA` WHERE `birth` > '2020-08-04 07:42:21' ORDER BY `age`;

/* 8.没有使用索引进行排序 产生了Using filesort */
EXPLAIN SELECT * FROM `talA` ORDER BY `age` ASC, `birth` DESC;

ORDER BY子句，尽量使用索引排序，避免使用Using filesort排序。

MySQL支持两种方式的排序，FileSort和Index，Index的效率高，它指MySQL扫描索引本身完成排序。FileSort方式效率较低。

ORDER BY满足两情况，会使用Index方式排序

ORDER BY语句使用索引最左前列。
使用WHERE子句与ORDER BY子句条件列组合满足索引最左前列。

结论：尽可能在索引列上完成排序操作，遵照索引建的最佳左前缀原则。

如果不在索引列上，File Sort有两种算法：MySQL就要启动双路排序算法和单路排序算法

双路排序算法：MySQL4.1之前使用双路排序，字面意思就是两次扫描磁盘，最终得到数据，读取行指针和ORDER BY列，対他们进行排序，然后扫描已经排序好的列表，按照列表中的值重新从列表中读取对应的数据输出。一句话，从磁盘取排序字段，在buffer中进行排序，再从磁盘取其他字段。

　　　　取一批数据，要对磁盘进行两次扫描，众所周知，IO是很耗时的，所以在MySQL4.1之后，出现了改进的算法，就是单路排序算法。

单路排序算法：从磁盘读取查询需要的所有列，按照ORDER BY列在buffer対它们进行排序，然后扫描排序后的列表进行输出，它的效率更快一些，避免了第二次读取数据。并且把随机IO变成了顺序IO，但是它会使用更多的空间，因为它把每一行都保存在内存中了。

　　　　由于单路排序算法是后出的，总体而言效率好过双路排序算法。

　　　　但是单路排序算法有问题：如果SortBuffer缓冲区太小，导致从磁盘中读取所有的列不能完全保存在SortBuffer缓冲区中，这时候单路复用算法就会出现问题，反而性能不如双路复用算法。

单路复用算法的优化策略：

增大sort_buffer_size参数的设置。
增大max_length_for_sort_data参数的设置。

提高ORDER BY排序的速度：

ORDER BY时使用SELECT *是大忌，查什么字段就写什么字段，这点非常重要。在这里的影响是：

当查询的字段大小总和小于max_length_for_sort_data而且排序字段不是TEXT|BLOB类型时，会使用单路排序算法，否则使用多路排序算法。
两种排序算法的数据都有可能超出sort_buffer缓冲区的容量，超出之后，会创建tmp临时文件进行合并排序，导致多次IO，但是单路排序算法的风险会更大一些，所以要增大sort_buffer_size参数的设置。

尝试提高sort_buffer_size：不管使用哪种算法，提高这个参数都会提高效率，当然，要根据系统的能力去提高，因为这个参数是针对每个进程的。
尝试提高max_length_for_sort_data：提高这个参数，会增加用单路排序算法的概率。但是如果设置的太高，数据总容量sort_buffer_size的概率就增大，明显症状是高的磁盘IO活动和低的处理器使用率。

　　（3）总结

为排序使用索引

MySQL两种排序方式：Using filesort和Using Index扫描有序索引排序。
MySQL能为排序与查询使用相同的索引，创建的索引既可以用于排序也可以用于查询。

/* 创建a b c三个字段的索引 */
idx_table_a_b_c(a, b, c)

/* 1.ORDER BY 能使用索引最左前缀 */
ORDER BY a;
ORDER BY a, b;
ORDER BY a, b, c;
ORDER BY a DESC, b DESC, c DESC;

/* 2.如果WHERE子句中使用索引的最左前缀定义为常量，则ORDER BY能使用索引 */
WHERE a = 'Ringo' ORDER BY b, c;
WHERE a = 'Ringo' AND b = 'Tangs' ORDER BY c;
WHERE a = 'Ringo' AND b > 2000 ORDER BY b, c;

/* 3.不能使用索引进行排序 */
ORDER BY a ASC, b DESC, c DESC;  /* 排序不一致 */
WHERE g = const ORDER BY b, c;   /* 丢失a字段索引 */
WHERE a = const ORDER BY c;      /* 丢失b字段索引 */
WHERE a = const ORDER BY a, d;   /* d字段不是索引的一部分 */
WHERE a IN (...) ORDER BY b, c;  /* 对于排序来说，多个相等条件(a=1 or a=2)也是范围查询 */

2.3 GRIUP BY优化

GROUP BY实质是先排序后进行分组，遵照索引建的最佳左前缀。
当无法使用索引列时，会使用Using filesort进行排序，增大max_length_for_sort_data参数的设置和增大sort_buffer_size参数的设置，会提高性能。
WHERE执行顺序高于HAVING，能写在WHERE限定条件里的就不要写在HAVING中了。

3、慢查询日志

3.1 基本介绍

慢查询日志是什么？

MySQL的慢查询日志是MySQL提供的一种日志记录，它用来记录在MySQL中响应时间超过阈值的语句，具体指运行时间超过 long_query_time 值的SQL，则会被记录到慢查询日志中。
long_query_time 的默认值为10，意思是运行10秒以上的语句。

由慢查询日志来查看哪些SQL超出了我们的最大忍耐时间值，比如一条SQL执行超过5秒钟，我们就算慢SQL，希望能收集超过5秒钟的SQL，结合之前 explain 进行全面分析。

特别说明

3.2 怎么用

　　默认情况下，MySQL数据库没有开启慢查询日志，需要我们手动来设置这个参数。

　　当然，如果不是调优需要的话，一般不建议启动该参数，因为开启慢查询日志会或多或少带来一定的性能影响。慢查询日志支持将日志记录写入文件。

　　查看慢查询日志是否开以及如何开启

查看慢查询日志是否开启：SHOW VARIABLES LIKE '%slow_query_log%';。
开启慢查询日志：SET GLOBAL slow_query_log = 1;。使用该方法开启MySQL的慢查询日志只对当前数据库生效，如果MySQL重启后会失效。

# 1、查看慢查询日志是否开启
mysql> SHOW VARIABLES LIKE '%slow_query_log%';
+---------------------+---------------------------------------+
| Variable_name       | Value                                 |
+---------------------+---------------------------------------+
| slow_query_log      | OFF                                   |
| slow_query_log_file | /var/lib/mysql/cess-Ubuntu20-slow.log |
+---------------------+---------------------------------------+
2 rows in set (0.01 sec)

# 2、开启慢查询日志
mysql> SET GLOBAL slow_query_log = 1;
Query OK, 0 rows affected (0.00 sec)

　　如果要使慢查询日志永久开启，需要修改my.cnf文件，在[mysqld]下增加修改参数。

# my.cnf
[mysqld]
# 1.这个是开启慢查询。注意ON需要大写
slow_query_log=ON  

# 2.这个是存储慢查询的日志文件。这个文件不存在的话，需要自己创建
slow_query_log_file=/var/lib/mysql/slow.log

注意：slow_query_log_file 对应的文件夹需要有权限才行，否则配置不能生效、
#修改文件的所属权限。在这里不建议采用chmod 777的方式。因为这样会造成系统安全风险。重启数据库后，此时慢查询日志生效了
chown -R mysql:mysql slow.log

　　开启了慢查询日志后，什么样的SQL才会被记录到慢查询日志里面呢？

　　这个是由参数long_query_time控制的，默认情况下long_query_time的值为10秒。

　　MySQL中查看long_query_time的时间：SHOW VARIABLES LIKE 'long_query_time%';。

# 查看long_query_time 默认是10秒
# 只有SQL的执行时间>10才会被记录
mysql> SHOW VARIABLES LIKE 'long_query_time%';
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
1 row in set (0.00 sec)

mysql> set global long_query_time=3;
Query OK, 0 rows affected (0.00 sec)

　　修改long_query_time的时间，需要在my.cnf修改配置文件

[mysqld]
# 这个是设置慢查询的时间，我设置的为1秒
long_query_time=1

　　修改后需要重新连接或新开一个会话才能看到修改值

　　查新慢查询日志的总记录条数：SHOW GLOBAL STATUS LIKE '%Slow_queries%';

mysql> select sleep(4);
+----------+
| sleep(4) |
+----------+
|        0 |
+----------+
1 row in set (4.00 sec)

mysql> SHOW GLOBAL STATUS LIKE '%Slow_queries%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Slow_queries  | 1     |
+---------------+-------+
1 row in set (0.00 sec)

　　打开慢查询日志文件

cat /var/lib/mysql/slow.log

3.3 日志分析工具

　　日志分析工具 mysqldumpslow：在生产环境中，如果要手工分析日志，查找、分析SQL，显然是个体力活，MySQL提供了日志分析工具 mysqldumpslow。

　　（1）查看 mysqldumpslow 的帮助信息

　　（2）案例

# 2.1、得到返回记录集最多的10个SQL
mysqldumpslow -s r -t 10 /var/lib/mysql/slow.log
 
# 2.2、得到访问次数最多的10个SQL
mysqldumpslow -s c -t 10 /var/lib/mysql/slow.log
 
# 2.3、得到按照时间排序的前10条里面含有左连接的查询语句
mysqldumpslow -s t -t 10 -g "left join" /var/lib/mysql/slow.log

# 2.4、另外建议使用这些命令时结合|和more使用，否则出现爆屏的情况
mysqldumpslow -s r -t 10 /var/lib/mysql/slow.log | more

4、批量插入数据脚本

4.1 环境准备

　　（1）建表 SQL

/* 1.dept表 */
CREATE TABLE `dept` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
  `deptno` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '部门id',
  `dname` varchar(20) NOT NULL DEFAULT '' COMMENT '部门名字',
  `loc` varchar(13) NOT NULL DEFAULT '' COMMENT '部门地址',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='部门表';

/* 2.emp表 */
CREATE TABLE `emp` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
  `empno` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '员工编号',
  `ename` varchar(20) NOT NULL DEFAULT '' COMMENT '员工名字',
  `job` varchar(9) NOT NULL DEFAULT '' COMMENT '职位',
  `mgr` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '上级编号',
  `hiredata` date NOT NULL COMMENT '入职时间',
  `sal` decimal(7,2) NOT NULL COMMENT '薪水',
  `comm` decimal(7,2) NOT NULL COMMENT '分红',
  `deptno` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '部门id',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='员工表';

　　（2）由于开启过慢查询日志，开启了bin-log，我们就必须为function指定一个参数，否则使用函数会报错。

# 在mysql中设置 
# log_bin_trust_function_creators 默认是关闭的 需要手动开启
mysql> SHOW VARIABLES LIKE 'log_bin_trust_function_creators';
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| log_bin_trust_function_creators | OFF   |
+---------------------------------+-------+
1 row in set (0.00 sec)

mysql> SET GLOBAL log_bin_trust_function_creators=1;
Query OK, 0 rows affected (0.00 sec)

　　上述修改方式MySQL重启后会失败，在my.cnf配置文件下修改永久有效。

[mysqld]
log_bin_trust_function_creators=ON

4.2 创建函数

# 1、函数：随机产生字符串
DELIMITER $$
CREATE FUNCTION rand_string(n INT) RETURNS VARCHAR(255)
BEGIN
    DECLARE chars_str VARCHAR(100) DEFAULT 'abcdefghijklmnopqrstuvwsyzABCDEFGHIJKLMNOPQRSTUVWXYZ';
    DECLARE return_str VARCHAR(255) DEFAULT '';
    DECLARE i INT DEFAULT 0;
    WHILE i < n DO
    SET return_str = CONCAT(return_str,SUBSTRING(chars_str,FLOOR(1+RAND()*52),1));
    SET i = i + 1;
    END WHILE;
    RETURN return_str;
END $$

# 2、函数：随机产生部门编号
DELIMITER $$
CREATE FUNCTION rand_num() RETURNS INT(5)
BEGIN
    DECLARE i INT DEFAULT 0;
    SET i = FLOOR(100 + RAND() * 10);
    RETURN i;
END $$

4.3 创建存储过程

# 1、函数：向dept表批量插入
DELIMITER $$
CREATE PROCEDURE insert_dept(IN START INT(10),IN max_num INT(10))
BEGIN
DECLARE i INT DEFAULT 0;
    SET autocommit = 0;
    REPEAT
    SET i = i + 1;
    INSERT INTO dept(deptno,dname,loc) VALUES((START + i),rand_string(10),rand_string(8));
    UNTIL i = max_num
    END REPEAT;
    COMMIT;
END $$

# 2、函数：向emp表批量插入
DELIMITER $$
CREATE PROCEDURE insert_emp(IN START INT(10),IN max_num INT(10))
BEGIN
DECLARE i INT DEFAULT 0;
    SET autocommit = 0;
    REPEAT
    SET i = i + 1;
    INSERT INTO emp(empno,ename,job,mgr,hiredata,sal,comm,deptno) VALUES((START + i),rand_string(6),'SALESMAN',0001,CURDATE(),2000,400,rand_num());
    UNTIL i = max_num
    END REPEAT;
    COMMIT;
END $$

4.4 调用存储过程

# 1、调用存储过程向dept表插入10个部门。
DELIMITER ;
CALL insert_dept(100,10);

# 2、调用存储过程向emp表插入50万条数据。
DELIMITER ;
CALL insert_emp(100001,500000);

5、Show Profile

Show Profile

　　MySQL提供可以用来分析当前会话中语句执行的资源消耗情况。

　　可以用于SQL的调优的测量。默认情况下，参数处于关闭状态，并保存最近15次的运行结果。

分析步骤

（1）是否支持，看看当前的MySQL版本是否支持。

# 查看Show Profile功能是否开启
mysql> SHOW VARIABLES LIKE 'profiling';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| profiling     | OFF   |
+---------------+-------+
1 row in set (0.00 sec)

（2）开启Show Profile功能，默认是关闭的，使用前需要开启。

# 开启Show Profile功能
mysql> SET profiling=ON;
Query OK, 0 rows affected, 1 warning (0.00 sec)

（3）运行 SQL

SELECT * FROM `emp` GROUP BY `id`%10 LIMIT 150000;

SELECT * FROM `emp` GROUP BY `id`%20 ORDER BY 5;

（4）查看结果，执行SHOW PROFILES;

　　Duration：持续时间。

mysql> SHOW PROFILES;
+----------+------------+---------------------------------------------------+
| Query_ID | Duration   | Query                                             |
+----------+------------+---------------------------------------------------+
|        1 | 0.00156100 | SHOW VARIABLES LIKE 'profiling'                   |
|        2 | 0.56296725 | SELECT * FROM `emp` GROUP BY `id`%10 LIMIT 150000 |
|        3 | 0.52105825 | SELECT * FROM `emp` GROUP BY `id`%10 LIMIT 150000 |
|        4 | 0.51279775 | SELECT * FROM `emp` GROUP BY `id`%20 ORDER BY 5   |
+----------+------------+---------------------------------------------------+
4 rows in set, 1 warning (0.00 sec)

（5）诊断SQL，SHOW PROFILE cpu,block io FOR QUERY Query_ID;

# 这里的3是第四步中的Query_ID。
# 可以在SHOW PROFILE中看到一条SQL中完整的生命周期。
mysql> SHOW PROFILE cpu,block io FOR QUERY 3;
+----------------------+----------+----------+------------+--------------+---------------+
| Status               | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out |
+----------------------+----------+----------+------------+--------------+---------------+
| starting             | 0.000097 | 0.000090 |   0.000002 |            0 |             0 |
| checking permissions | 0.000010 | 0.000009 |   0.000000 |            0 |             0 |
| Opening tables       | 0.000039 | 0.000058 |   0.000000 |            0 |             0 |
| init                 | 0.000046 | 0.000046 |   0.000000 |            0 |             0 |
| System lock          | 0.000011 | 0.000000 |   0.000000 |            0 |             0 |
| optimizing           | 0.000005 | 0.000000 |   0.000000 |            0 |             0 |
| statistics           | 0.000023 | 0.000037 |   0.000000 |            0 |             0 |
| preparing            | 0.000014 | 0.000000 |   0.000000 |            0 |             0 |
| Creating tmp table   | 0.000041 | 0.000053 |   0.000000 |            0 |             0 |
| Sorting result       | 0.000005 | 0.000000 |   0.000000 |            0 |             0 |
| executing            | 0.000003 | 0.000000 |   0.000000 |            0 |             0 |
| Sending data         | 0.520620 | 0.516267 |   0.000000 |            0 |             0 |
| Creating sort index  | 0.000060 | 0.000051 |   0.000000 |            0 |             0 |
| end                  | 0.000006 | 0.000000 |   0.000000 |            0 |             0 |
| query end            | 0.000011 | 0.000000 |   0.000000 |            0 |             0 |
| removing tmp table   | 0.000006 | 0.000000 |   0.000000 |            0 |             0 |
| query end            | 0.000004 | 0.000000 |   0.000000 |            0 |             0 |
| closing tables       | 0.000009 | 0.000000 |   0.000000 |            0 |             0 |
| freeing items        | 0.000032 | 0.000064 |   0.000000 |            0 |             0 |
| cleaning up          | 0.000019 | 0.000000 |   0.000000 |            0 |             0 |
+----------------------+----------+----------+------------+--------------+---------------+
20 rows in set, 1 warning (0.00 sec)

Show Profile查询参数备注：

ALL：显示所有的开销信息。
BLOCK IO：显示块IO相关开销（通用）。
CONTEXT SWITCHES：上下文切换相关开销。
CPU：显示CPU相关开销信息（通用）。
IPC：显示发送和接收相关开销信息。
MEMORY：显示内存相关开销信息。
PAGE FAULTS：显示页面错误相关开销信息。
SOURCE：显示和Source_function。
SWAPS：显示交换次数相关开销的信息。

（6）Show Profile查询列表，日常开发需要注意的结论：

converting HEAP to MyISAM：查询结果太大，内存都不够用了，往磁盘上搬了。
Creating tmp table：创建临时表（拷贝数据到临时表，用完再删除），非常耗费数据库性能。
Copying to tmp table on disk：把内存中的临时表复制到磁盘，危险！！！
locked：死锁。

6、全局查询日志

　　只在测试环境下才可以用

在mysql的my.cnf中，设置如下
#开启
general_log=1

#记录日志文件的路径
general_log_file=/path/logfile

#输出格式
log_output=FILE

set global general_log=1;
set global log_output='TABLE';

#此后编写的sql语句，将会记录到mysql库里的general_log表，可以用下面的命令查看
select * from mysql.general_log;

四、MySQL 锁机制

1、概述

1.1 定义

1.2 锁的分类

　　（1）从数据操作的类型（读、写）分：

　　　　读锁（共享锁）：针对同一份数据，多个读操作可以同时进行而不会互相影响

　　　　写锁（排它锁）：当前写操作没有完成前，它会阻断其他写锁和读锁

　　（2）从对数据操作的颗粒度分：

　　　　表锁

　　　　行锁

2、表锁（偏读）

表锁的特点：

　　表锁偏向 MyISAM 存储引擎，开销小，加锁快，无死锁，锁定粒度大，发生锁冲突的概率最高，并发度最低

2.1 环境准备

# 1、创建表
CREATE TABLE `mylock`(
`id` INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
`name` VARCHAR(20)
)ENGINE=MYISAM DEFAULT CHARSET=utf8 COMMENT='测试表锁';

# 2、插入数据
INSERT INTO `mylock`(`name`) VALUES('ZhangSan');
INSERT INTO `mylock`(`name`) VALUES('LiSi');
INSERT INTO `mylock`(`name`) VALUES('WangWu');
INSERT INTO `mylock`(`name`) VALUES('ZhaoLiu');

2.2 锁表的命令

　　（1）查看数据库表锁的命令

# 查看数据库表锁的命令
SHOW OPEN TABLES;

　　（2）给mylock表上读锁，给book表上写锁

# 给mylock表上读锁，给book表上写锁
LOCK TABLES `mylock` READ, `book` WRITE;

# 查看当前表的状态
mysql> SHOW OPEN TABLES;
+--------------------+-------------------------+--------+-------------+
| Database           | Table                   | In_use | Name_locked |
+--------------------+-------------------------+--------+-------------+
| sql_analysis       | book                    |      1 |           0 |
| sql_analysis       | mylock                  |      1 |           0 |
+--------------------+-------------------------+--------+-------------+

　　（3）释放锁

# 释放给表添加的锁
UNLOCK TABLES;

# 查看当前表的状态
mysql> SHOW OPEN TABLES;
+--------------------+-------------------------+--------+-------------+
| Database           | Table                   | In_use | Name_locked |
+--------------------+-------------------------+--------+-------------+
| sql_analysis       | book                    |      0 |           0 |
| sql_analysis       | mylock                  |      0 |           0 |
+--------------------+-------------------------+--------+-------------+

2.3 读锁案例

　　（1）打开两个会话，SESSION1为mylock表添加读锁。

# 为mylock表添加读锁
LOCK TABLES `mylock` READ;

　　（2）打开两个会话，SESSION1是否可以读自己锁的表？是否可以修改自己锁的表？是否可以读其他的表？那么SESSION2呢？

　　　　能不能不能

　　　　能阻塞能

# SESSION1

# 问题1：SESSION1为mylock表加了读锁，可以读mylock表！
mysql> SELECT * FROM `mylock`;
+----+----------+
| id | name     |
+----+----------+
|  1 | ZhangSan |
|  2 | LiSi     |
|  3 | WangWu   |
|  4 | ZhaoLiu  |
+----+----------+
4 rows in set (0.00 sec)

# 问题2：SESSION1为mylock表加了读锁，不可以修改mylock表！
mysql> UPDATE mylock SET name = 'abc' WHERE id = 1;
ERROR 1099 (HY000): Table mylock was locked with a READ lock and cannot be updated

# 问题3：SESSION1为mylock表加了读锁，不可以读其他的表！
mysql> SELECT * FROM `book`;
ERROR 1100 (HY000): Table 'book' was not locked with LOCK TABLES

# SESSION2

# 问题1：SESSION1为mylock表加了读锁，SESSION2可以读mylock表！
mysql> SELECT * FROM `mylock`;
+----+----------+
| id | name     |
+----+----------+
|  1 | ZhangSan |
|  2 | LiSi     |
|  3 | WangWu   |
|  4 | ZhaoLiu  |
+----+----------+
4 rows in set (0.00 sec)

# 问题2：SESSION1为mylock表加了读锁，SESSION2修改mylock表会被阻塞，
#        需要等待SESSION1释放mylock表！
mysql> UPDATE `mylock` SET `name` = 'abc' WHERE `id` = 1;
^C^C -- query aborted
ERROR 1317 (70100): Query execution was interrupted

# 问题3：SESSION1为mylock表加了读锁，SESSION2可以读其他表！
mysql> SELECT * FROM `book`;
+--------+------+
| bookid | card |
+--------+------+
|      1 |    1 |
|      7 |    4 |
|      8 |    4 |
|      9 |    5 |
|      5 |    6 |
|     17 |    6 |
|     15 |    8 |
+--------+------+
24 rows in set (0.00 sec)

2.4 写锁案例

　　（1）打开两个会话，SESSION1为mylock表添加写锁。

# 为mylock表添加写锁
LOCK TABLES mylock WRITE;

　　（2）打开两个会话，SESSION1是否可以读自己锁的表？是否可以修改自己锁的表？是否可以读其他的表？那么SESSION2呢？

　　　　能能不能

　　　　阻塞阻塞能

# SESSION1

# 问题1：SESSION1为mylock表加了写锁，可以读mylock的表！
mysql> SELECT * FROM `mylock`;
+----+----------+
| id | name     |
+----+----------+
|  1 | ZhangSan |
|  2 | LiSi     |
|  3 | WangWu   |
|  4 | ZhaoLiu  |
+----+----------+
4 rows in set (0.00 sec)

# 问题2：SESSION1为mylock表加了写锁，可以修改mylock表!
mysql> UPDATE `mylock` SET `name` = 'abc' WHERE `id` = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

# 问题3：SESSION1为mylock表加了写锁，不能读其他表!
mysql> SELECT * FROM `book`;
ERROR 1100 (HY000): Table 'book' was not locked with LOCK TABLES

# SESSION2

# 问题1：SESSION1为mylock表加了写锁，SESSION2读mylock表会阻塞，等待SESSION1释放！
mysql> SELECT * FROM `mylock`;
^C^C -- query aborted
ERROR 1317 (70100): Query execution was interrupted

# 问题2：SESSION1为mylock表加了写锁，SESSION2读mylock表会阻塞，等待SESSION1释放！
mysql> UPDATE `mylock` SET `name` = 'abc' WHERE `id` = 1;
^C^C -- query aborted
ERROR 1317 (70100): Query execution was interrupted

# 问题3：SESSION1为mylock表加了写锁，SESSION2可以读其他表！
mysql> SELECT * FROM `book`;
+--------+------+
| bookid | card |
+--------+------+
|      1 |    1 |
|      7 |    4 |
|      8 |    4 |
|      9 |    5 |
|      5 |    6 |
|     17 |    6 |
|     15 |    8 |
+--------+------+
24 rows in set (0.00 sec)

2.5 案例结论

　　MyISAM引擎在执行查询语句SELECT之前，会自动给涉及到的所有表加读锁，在执行增删改之前，会自动给涉及的表加写锁。

　　MySQL的表级锁有两种模式：

- 表共享读锁（Table Read Lock）

- 表独占写锁（Table Write Lock）

　　対MyISAM表进行操作，会有以下情况

- 対MyISAM表的读操作（加读锁），不会阻塞其他线程対同一表的读操作，但是会阻塞其他线程対同一表的写操作。只有当读锁释放之后，才会执行其他线程的写操作。

- 対MyISAM表的写操作（加写锁），会阻塞其他线程対同一表的读和写操作，只有当写锁释放之后，才会执行其他线程的读写操作。

简言之，就是读锁会阻塞写，但不会阻塞读。而写锁会把读跟写都阻塞。

2.6 表锁分析

mysql> SHOW STATUS LIKE 'table%';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| Table_locks_immediate      | 173   |
| Table_locks_waited         | 0     |
| Table_open_cache_hits      | 5     |
| Table_open_cache_misses    | 8     |
| Table_open_cache_overflows | 0     |
+----------------------------+-------+
5 rows in set (0.00 sec)

可以通过 Table_locks_immediate 和 Table_locks_waited 状态变量来分析系统上的表锁定。具体说明如下

Table_locks_immediate：产生表级锁定的次数，表示可以立即获取锁的查询次数，每立即获取锁值加1
Table_locks_waited：出现表级锁定争用而发生等待的次数（不能立即获取锁的次数，每等待一次锁值加1），此值高则说明存在较严重的表级锁争用情况。

　　此外，MyISAM的读写锁调度是写优先，这也是MyISAM不适合作为主表的引擎。因为写锁后，其他线程不能进行任何操作，大量的写操作会使查询很难得到锁，从而造成永远阻塞。

3、行锁（偏写）

行锁特点

偏向InnoDB存储引擎，开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度最高。

InnoDB存储引擎和MyISAM存储引擎最大不同有两点：一是支持事务，二是采用行锁

3.1 事务相关知识复习

3.1.1 事务及其ACID属性

3.1.2 并发事务处理带来的问题

3.1.3 事务隔离级别

3.2 环境准备

# 建表语句
CREATE TABLE `test_innodb_lock`(
`a` INT,
`b` VARCHAR(16)
)ENGINE=INNODB DEFAULT CHARSET=utf8 COMMENT='测试行锁'; 

# 插入数据
INSERT INTO `test_innodb_lock`(`a`, `b`) VALUES(1, 'b2');
INSERT INTO `test_innodb_lock`(`a`, `b`) VALUES(2, '3');
INSERT INTO `test_innodb_lock`(`a`, `b`) VALUES(3, '4000');
INSERT INTO `test_innodb_lock`(`a`, `b`) VALUES(4, '5000');
INSERT INTO `test_innodb_lock`(`a`, `b`) VALUES(5, '6000');
INSERT INTO `test_innodb_lock`(`a`, `b`) VALUES(6, '7000');
INSERT INTO `test_innodb_lock`(`a`, `b`) VALUES(7, '8000');
INSERT INTO `test_innodb_lock`(`a`, `b`) VALUES(8, '9000');

# 创建索引
CREATE INDEX idx_test_a ON `test_innodb_lock`(a);
CREATE INDEX idx_test_b ON `test_innodb_lock`(b);

3.3 行锁案例

　　（1）打开SESSION1和SESSION2两个会话，都开启手动提交

# 开启MySQL数据库的手动提交
mysql> SET autocommit=0;
Query OK, 0 rows affected (0.00 sec)

　　（2）读自己所写

# SESSION1 

# SESSION1対test_innodb_lock表做写操作，但是没有commit。
# 执行修改SQL之后，查询一下test_innodb_lock表，发现数据被修改了。
mysql> UPDATE `test_innodb_lock` SET `b` = '88' WHERE `a` = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT * FROM `test_innodb_lock`;
+------+------+
| a    | b    |
+------+------+
|    1 | 88   |
|    2 | 3    |
|    3 | 4000 |
|    4 | 5000 |
|    5 | 6000 |
|    6 | 7000 |
|    7 | 8000 |
|    8 | 9000 |
+------+------+
8 rows in set (0.00 sec)

# SESSION2 

# SESSION2这时候来查询test_innodb_lock表。
# 发现SESSION2是读不到SESSION1未提交的数据的。
mysql> SELECT * FROM `test_innodb_lock`;
+------+------+
| a    | b    |
+------+------+
|    1 | b2   |
|    2 | 3    |
|    3 | 4000 |
|    4 | 5000 |
|    5 | 6000 |
|    6 | 7000 |
|    7 | 8000 |
|    8 | 9000 |
+------+------+
8 rows in set (0.00 se

　　（3）行锁两个SESSION同时対一条记录进行写操作

# SESSION1 対test_innodb_lock表的`a`=1这一行进行写操作，但是没有commit
mysql> UPDATE `test_innodb_lock` SET `b` = '99' WHERE `a` = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

# SESSION2 也对test_innodb_lock表的`a`=1这一行进行写操作，但是发现阻塞了！！！
# 等SESSION1执行commit语句之后，SESSION2的SQL就会执行了
mysql> UPDATE `test_innodb_lock` SET `b` = 'asdasd' WHERE `a` = 1;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

　　（4）行锁两个SESSION同时对不同记录进行写操作

# SESSION1 対test_innodb_lock表的`a`=6这一行进行写操作，但是没有commit
mysql> UPDATE `test_innodb_lock` SET `b` = '8976' WHERE `a` = 6;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

# SESSION2 対test_innodb_lock表的`a`=4这一行进行写操作，没有阻塞！！！
# SESSION1和SESSION2同时对不同的行进行写操作互不影响
mysql> UPDATE `test_innodb_lock` SET `b` = 'Ringo' WHERE `a` = 4;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

3.4 索引失效行锁变表锁

# SESSION1 执行SQL语句，没有执行commit。
# 由于`b`字段是字符串，但是没有加单引号导致索引失效
mysql> UPDATE `test_innodb_lock` SET `a` = 888 WHERE `b` = 8000;
Query OK, 1 row affected, 1 warning (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 1

# SESSION2 和SESSION1操作的并不是同一行，但是也被阻塞了？？？
# 由于SESSION1执行的SQL索引失效，导致行锁升级为表锁。
mysql> UPDATE `test_innodb_lock` SET `b` = '1314' WHERE `a` = 1;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

3.5 间隙锁的危害

3.5.1 什么是间隙锁　　

　　当我们用范围条件而不是相等条件检索数据，并请求共享或者排他锁时，InnoDB会给符合条件的已有数据记录的索引项加锁，对于键值在条件范文内但并不存在的记录，叫做"间隙(GAP)"。

　　InnoDB也会对这个"间隙"加锁，这种锁的机制就是所谓的"间隙锁"。

3.5.1 间隙锁的危害

　　因为Query执行过程中通过范围查找的话，他会锁定整个范围内所有的索引键值，即使这个键值不存在。

　　间隙锁有一个比较致命的缺点，就是当锁定一个范围的键值后，即使某些不存在的键值也会被无辜的锁定，而造成在锁定的时候无法插入锁定键值范围内的任何数据。在某些场景下这可能会対性能造成很大的危害。

3.6 如何锁定一行

SELECT .....FOR UPDATE 在锁定某一行后，其他写操作会被阻塞，直到锁定的行被 COMMIT

　　mysql InnoDB引擎默认的修改数据语句，update,delete,insert都会自动给涉及到的数据加上排他锁，select语句默认不会加任何锁类型，如果加排他锁可以使用select ...for update语句，加共享锁可以使用select ... lock in share mode语句。所以加过排他锁的数据行在其他事务种是不能修改数据的，也不能通过for update和lock in share mode锁的方式查询数据，但可以直接通过select ...from...查询数据，因为普通查询没有任何锁机制。

3.7 案例结论

　　InnoDB存储引擎由于实现了行级锁定，虽然在锁定机制的实现方面所带来的性能损耗可能比表级锁定会要更高一些，但是在整体并发处理能力方面要远远优于MyISAM的表级锁定的。当系统并发量较高的时候，InnoDB的整体性能和MyISAM相比就会有比较明显的优势了。

　　但是，InnoDB的行级锁定同样也有其脆弱的一面，当我们使用不当的时候，可能会让InnoDB的整体性能表现不仅不能比MyISAM高，甚至可能会更差。

3.8 行锁分析

mysql> SHOW STATUS LIKE 'innodb_row_lock%';
+-------------------------------+--------+
| Variable_name                 | Value  |
+-------------------------------+--------+
| Innodb_row_lock_current_waits | 0      |
| Innodb_row_lock_time          | 124150 |
| Innodb_row_lock_time_avg      | 31037  |
| Innodb_row_lock_time_max      | 51004  |
| Innodb_row_lock_waits         | 4      |
+-------------------------------+--------+
5 rows in set (0.00 sec)

対各个状态量的说明如下：

Innodb_row_lock_current_waits：当前正在等待锁定的数量
Innodb_row_lock_time：从系统启动到现在锁定总时间长度（重要）
Innodb_row_lock_time_avg：每次等待所花的平均时间（重要）
Innodb_row_lock_time_max：从系统启动到现在等待最长的一次所花的时间
Innodb_row_lock_waits：系统启动后到现在总共等待的次数（重要）

　　尤其是当等待次数很高，而且每次等待时长也不小的时候，我们就需要分析系统中为什么会有如此多的等待，然后根据分析结果着手制定优化策

优化建议

尽可能让所有数据检索都通过索引来完成，避免无索引行锁升级为表锁
合理设计索引，计量缩小所的范围
尽可能较少检索条件，避免间隙锁
尽量控制事务大小，减少锁定资源量和时间长度
尽可能低级别事务隔离

4、页锁

　　开销和加锁时间介于表锁和行锁之间，会出现死锁，锁定粒度介于表锁和行锁之间，并发度一般

五、主从复制

1、什么是主从复制？

　　主从复制是指将主数据库的DDL和DML操作通过二进制日志传到从数据库上，然后在从数据库上对这些日志进行重新执行，从而使从数据库和主数据库的数据保持一致。

2、主从复制的原理

3、主实例搭建

（1）拉取镜像

docker pull mysql:5.7

（2）运行mysql主实例

docker run -p 3307:3306 --name mysql-master \
-v /mydata/mysql-master/log:/var/log/mysql \
-v /mydata/mysql-master/data:/var/lib/mysql \
-v /mydata/mysql-master/conf:/etc/mysql \
-e MYSQL_ROOT_PASSWORD=root  \
-d mysql:5.7

（3）在mysql的配置文件夹/mydata/mysql-master/conf中创建一个配置文件my.cnf：

touch my.cnf

（4）修改配置文件my.cnf，配置信息如下：

[mysqld]
## 设置server_id，同一局域网中需要唯一
server_id=101 
## 指定不需要同步的数据库名称
binlog-ignore-db=mysql  
## 开启二进制日志功能
log-bin=mall-mysql-bin  
## 设置二进制日志使用内存大小（事务）
binlog_cache_size=1M  
## 设置使用的二进制日志格式（mixed,statement,row）
binlog_format=mixed  
## 二进制日志过期清理时间。默认值为0，表示不自动清理。
expire_logs_days=7  
## 跳过主从复制中遇到的所有错误或指定类型的错误，避免slave端复制中断。
## 如：1062错误是指一些主键重复，1032错误是因为主从数据库数据不一致
slave_skip_errors=1062

（5）修改完配置后重启实例：

docker restart mysql-master

（6）进入mysql-master容器中：

docker exec -it mysql-master /bin/bash

（7）在容器中使用mysql的登录命令连接到客户端：

mysql -uroot -proot

（8）建数据同步用户：

CREATE USER 'slave'@'%' IDENTIFIED BY '123456';
GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'slave'@'%';

4、从实例搭建

（1）运行mysql从实例：

docker run -p 3308:3306 --name mysql-slave \
-v /mydata/mysql-slave/log:/var/log/mysql \
-v /mydata/mysql-slave/data:/var/lib/mysql \
-v /mydata/mysql-slave/conf:/etc/mysql \
-e MYSQL_ROOT_PASSWORD=root  \
-d mysql:5.7

（2）在mysql的配置文件夹/mydata/mysql-slave/conf中创建一个配置文件my.cnf：

touch my.cnf

（3）修改配置文件my.cnf：

[mysqld]
## 设置server_id，同一局域网中需要唯一
server_id=102
## 指定不需要同步的数据库名称
binlog-ignore-db=mysql  
## 开启二进制日志功能，以备Slave作为其它数据库实例的Master时使用
log-bin=mall-mysql-slave1-bin  
## 设置二进制日志使用内存大小（事务）
binlog_cache_size=1M  
## 设置使用的二进制日志格式（mixed,statement,row）
binlog_format=mixed  
## 二进制日志过期清理时间。默认值为0，表示不自动清理。
expire_logs_days=7  
## 跳过主从复制中遇到的所有错误或指定类型的错误，避免slave端复制中断。
## 如：1062错误是指一些主键重复，1032错误是因为主从数据库数据不一致
slave_skip_errors=1062  
## relay_log配置中继日志
relay_log=mall-mysql-relay-bin  
## log_slave_updates表示slave将复制事件写进自己的二进制日志
log_slave_updates=1  
## slave设置为只读（具有super权限的用户除外）
read_only=1

（4）修改完配置后重启实例：

docker restart mysql-slave

5、将主从数据库进行连接

（1）连接到主数据库的mysql客户端，查看主数据库状态：

show master status;

　　主数据库状态显示如下：

（2）进入mysql-slave容器中：

docker exec -it mysql-slave /bin/bash

（3）在容器中使用mysql的登录命令连接到客户端：

mysql -uroot -proot

（4）在从数据库中配置主从复制：

change master to master_host='192.168.184.204', master_user='slave', master_password='123456', master_port=3307, master_log_file='mall-mysql-bin.000002', master_log_pos=617, master_connect_retry=30;

主从复制命令参数说明：

master_host：主数据库的IP地址；
master_port：主数据库的运行端口；
master_user：在主数据库创建的用于同步数据的用户账号；
master_password：在主数据库创建的用于同步数据的用户密码；
master_log_file：指定从数据库要复制数据的日志文件，通过查看主数据的状态，获取File参数；
master_log_pos：指定从数据库从哪个位置开始复制数据，通过查看主数据的状态，获取Position参数；
master_connect_retry：连接失败重试的时间间隔，单位为秒。

（5）查看主从同步状态：

show slave status \G;

　　从数据库状态显示如下：

（6）开启主从同步

start slave;

　　查看从数据库状态发现已经同步：

6、主从复制测试

（1）在主实例中创建一个数据库mall；

　　（2）在从实例中查看数据库，发现也有一个mall数据库，可以判断主从复制已经搭建成功。

posted @ 2021-11-10 12:43 沧海一粟hr 阅读(112) 评论(0) 编辑收藏举报

刷新页面返回顶部

沧海一粟11

MySQL高级笔记

一、MySQL架构介绍

1、MySQL 简介

1.1 概述

1.2 高级 MySQL

2、MySQL Linux 版本的安装mysql—rpm方式

2.1 准备工作

2.1.1 下载地址

2.1.2 检查当前系统是否安装过 mysql

2.2 MySQL 安装

2.3 MySQL 服务

2.3.1 MySQL 服务的启动和停止

2.3.2 MySQL 的安装位置

2.3.3 MySQL 服务的自启动

2.3.4 MySQL 的重复启动问题

2.4 修改字符集

2.4.1 常用命令

2.4.2 字符集乱码原因

2.4.3 永久修改

2.5 设置大小写不敏感

3、MySQL Linux 版本的安装—docker方式

3.1 centos7 安装 docker

3.2 使用docker安装mysql

3.3 连接mysql

3.4 设置开机启动

3.5 安装位置

3.6 解决docker没有vim和vi命令问题

3.7 修改字符集

4、MySQL 配置文件

4.1 二进制日志： log-bin

4.2 错误日志：log-error

4.3 查询日志：log

4.4 数据文件

4.5 如何配置

5、MySQL 逻辑架构介绍

6、MySQL 存储引擎

6.1 查看命令

6.2 InnoDB和MyISAM对比

二、索引优化分析

1、SQL 性能下降的原因

2、常见通用的join查询

2.1 SQL 执行顺序

2.2 七种 JOIN理论

3、索引简介

3.1 索引是什么？

3.2 索引的优势和劣势

3.3 MySQL索引分类

3.4 MySQL 索引数据结构

3.5 哪些情况需要创建索引

3.6 哪些情况不要建索引

4、性能分析

4.1 MySQL Query Optimizer

4.2 MySQL常见瓶颈

4.3 Explain

4.3.1 Explain 是什么

4.3.2 Explain 怎么使用

4.3.3 Explain 能干嘛

4.3.4 Explain 各个字段的解释

5、索引优化

5.1 索引分析

5.1.1 单表索引分析

5.1.2 两张表索引分析

5.1.3 三张表索引分析

5.1.4 结论

5.2 索引失效

5.2.1 索引失效的情况

5.2.2 最佳左前缀法则

5.2.3 索引列上不计算

5.2.4 范围之后会失效

5.2.5 覆盖索引尽量用

5.2.6 不等有时会失效

5.2.7 like 百分百加右边

5.2.8 字符要加单引号

5.2.9 索引相关题目

5.2.10 面试题分析

5.2.11 总结

三、查询截取分析

1、分析慢 SQL 的步骤

2、查询优化

3.5 哪些情况需要创建索引　

2、表锁（偏读）

3.5.1 什么是间隙锁　　