嘉兴ing

嘉兴ing 查看完整档案

厦门编辑桂林理工大学  |  软件工程 编辑  |  填写所在公司/组织填写个人主网站
编辑

PHPer@厦门

个人动态

嘉兴ing 赞了回答 · 3月19日

解决通过docker命令行,报错:Fatal error, can't open config file,在容器内执行没问题

docker inspect 可以看到,redis 的 docker 镜像的默认的 Entrypoint 是 docker-entrypoint.sh 。也就是说,CMD 定义的命令是由这个脚本执行的。

这个脚本的内容如下(可能不同的版本会有所不同):

#!/bin/sh
set -e

# first arg is `-f` or `--some-option`
# or first arg is `something.conf`
if [ "${1#-}" != "$1" ] || [ "${1%.conf}" != "$1" ]; then
        set -- redis-server "$@"
fi

# allow the container to be started with `--user`
if [ "$1" = 'redis-server' -a "$(id -u)" = '0' ]; then
        find . \! -user redis -exec chown redis '{}' +
        exec gosu redis "$0" "$@"
fi

exec "$@"

可以看到,当 CMD 的第一个元素为 redis-server 的时候,它将当前目录(WorkingDir,通过 docker inspect 可以看到是 /data) 的所有文件的所有者改为了 redis ,然后以 redis 用户运行命令。

Dockerfile 里,虽然将 /root/redis.conf 的权限改成了 777 ,但是 /root 目录的权限是 700 ,redis 用户不可读,于是出错。将 /root 的权限也改成 777 就可以解决。

========================

由于脚本在进行精确匹配,命令改为绝对路径后将不能匹配,会进入最后一行的 exec 。此时是用 root 用户跑的,所以有读权限,可以启动。

或者说,只要 CMD 的第一个元素不是 redis-server ,那么用户就是 root 。所以用 bash 进容器里再跑也是没有问题的。

最后,如果 CMD 改成: CMD redis-server /root/redis.conf ,这个 CMD 会被改写成:CMD ["/bin/sh", "-c", "redis-server /root/redis.conf"] 。参考 Dockerfile / CMD 文档 ,并且可以通过 docker inspect 看到实际的 Cmd 。这时,运行的用户也是 root ,也是有读权限的,可以运行。

在命令行指定 CMD 也会使用同样的规则,参考 docerker run / COMMAND 文档 , 可以解释 @cai2h 的问题。

======================

如果 CMD 的第一个参数以 - 开始,或者以 .conf 结束,那么 docker-entrypoint.sh 会在前面加一个 redis-server 。这是其中第一个 if 干的事情。此时,由于第一个参数是(新插入的)redis-server ,所以也会以 redis 用户运行。

也就是说,启动 redis 镜像时,redis-server 命令其实可以不写,只写参数。比如:CMD ["/root/redis.conf"] 。或者,docker run -it --rm a7f182f6c6dd /root/redis.conf

关注 6 回答 5

嘉兴ing 发布了文章 · 1月3日

Laravel 5.8 中使用 telescope 并自定义扩展缓存驱动报错分析及解决方案

前情提要

  1. 由于 FileStore 在存储不过期的key的expire时使用了 9999999999, 导致最后在使用 Carbon 处理时日期溢出, 因此自己修改了一下, 新增一个 App\Extensions\Cache\FileStore 文件

    <?php
    namespace App\Extensions\Cache;
    
    class FileStore extends \Illuminate\Cache\FileStore
    {
        protected function expiration($seconds)
        {
            $expiration = parent::expiration($seconds);
            return $expiration === 9999999999 ? 2147483600 : $expiration;
        }
    }
  2. 并在 App\Providers\AppServiceProvider::boot() 中扩展该缓存驱动

    Cache::extend('file2', function ($app, $config) {
        return Cache::repository(new FileStore($app['files'], $config['path']));
    });
  3. 最后修改了默认的缓存驱动 config/cache.php

    return [
        'default' => 'file2',
    
        'stores' => [
            ...
            'file' => [
                'driver' => 'file2',
                ...
            ],
            ...
        ]    
    ];

这时候问题出来了, 无论是启动 php artisan tinker 或网页直接访问, 都会报错:

Driver [file2] is not supported

先一顿分析

  1. laravel/telescope 在 composer.json 中配置了包自动发现策略:

    extra.laravel.providers: ["Laravel\\Telescope\\TelescopeServiceProvider"]

    composer 在安装/更新包时, 会将所有安装的包的信息存储在 vendor/composer/installed.json, 其中包含每个包的安装信息及其配置的composer.json文件

  2. 项目 composer.json 根据其配置 `scripts.post-autoload-dumpautoload-dump 后会执行 php artisan package:discover --ansi 命令

    上述命令对应的是 Illuminate\Foundation\Console\PackageDiscoverCommand 文件.

    它会调用 Illuminate\Foundation\PackageManifest::build() , 该方法会将 vendor/composer/installed.json中配置了 extra.laravel.providers 的项提取出来, 并保存在 bootstrap/cache/packages.php 中.

    这部分解析可以参考: https://divinglaravel.com/lar...
  1. 在laravel启动过程中, Illuminate\Foundation\Application::registerConfiguredProviders()会逐个注册所需的服务提供者, 服务提供者列表来源包括: config('app.providers') 以及 laravel 包自动发现策略.

    public function registerConfiguredProviders()
    {
        $providers = Collection::make($this->config['app.providers'])
            ->partition(function ($provider) {
                return Str::startsWith($provider, 'Illuminate\\');
            });
    
        $providers->splice(1, 0, [$this->make(PackageManifest::class)->providers()]);
    
        (new ProviderRepository($this, new Filesystem, $this->getCachedServicesPath()))
        ->load($providers->collapse()->toArray());
    }

    上述代码分析:

    第3行: 将配置中 app.providers 中的服务提供者根据字符串前缀匹配分开, 此时 $providers 值大致是这样的:

    {
        0 : [
            "Illuminate\....."
            ...
            "Illuminate\....."
        ],
        1 : [
            "App\Providers\AppServiceProvider::class",
            ...
            "App\Providers\RouteServiceProvider::class",
        ],
    }

    第8行: 将laravel包自动发现策略获取的服务提供者列表插入到 $providers 数组中 1 的位置, 原先的 1 挪到 2, 此时 $providers 数组大致如下:

    {
        0 : [
            "Illuminate\....."
            ...
            "Illuminate\....."
        ],
        1 : [
            ...
            "Laravel\Telescope\TelescopeServiceProvider",
            ...
        ],
        2 : [
            "App\Providers\AppServiceProvider",
            ...
            "App\Providers\RouteServiceProvider",
        ],
    }

    第10行: 将 $providers 数组扁平化, 顺序则是依次 0, 1, 2 这样分别 register(注册) 这些服务提供者.

  1. 在laravel启动初始化的最后还会依次按 register(注册) 的顺序依次 boot(启动)上述注册的服务提供者.

    对于 Laravel\Telescope\TelescopeServiceProvider 这个服务提供者, 按照如下的调用顺序

    Laravel\Telescope\TelescopeServiceProvider::boot()
        |
        V
    Laravel\Telescope\Telescope::start()
        |
        V
    Laravel\TelescopeRegistersWatchers::registerWatchers()
        |
        V
    Laravel\Telescope\Watchers\DumpWatcher::register()
        ↑ 这里的代码调用 $this->cache->get("...")

    Laravel\Telescope\Watchers\DumpWatcher::register() 其中的代码调用了缓存相关接口, 然而此时根本就没有执行到 App\Providers\AppServiceProvider::boot(), 自然会导致报错无法找到该缓存驱动.

解决办法

基本思路就是调整 Laravel\Telescope\TelescopeServiceProvider 服务提供者的加载顺序, 使其在 App\Providers\AppServiceProvider 之后加载.

这里给出一个方案:

  1. 配置项目的 composer.json, 使laravel的包自动发现策略忽略 TelescopeServiceProvider

    {
        ...
        "extra": {
            "laravel": {
                "dont-discover": [
                    "laravel/telescope"
                ]
            }
        },
        ...
    }
  2. TelescopeServiceProvider 手动加入到服务提供者列表中, 注意顺序

    修改 config/app.php

    return [
        ...
        'providers' => [
            ...
            App\Providers\AppServiceProvider::class,
            ...
            Laravel\Telescope\TelescopeServiceProvider::class,        
            App\Providers\TelescopeServiceProvider::class,
            ...        
        ],    
        ...
    ];
查看原文

赞 1 收藏 0 评论 0

嘉兴ing 赞了回答 · 2019-12-17

解决larave session问题,为什么每次session_id都要变

看了这块的源码找到了答案:

一切都是在EncryptCookies中进行的

\App\Http\Middleware\EncryptCookies::class

larave_session

先经过base64_decode,在json_decode
在进行一些列验证

clipboard.png

然后通过openssl_decrypt解密出真正存储在redis或其他drive里面的session_id
clipboard.png

之后再response里面对cookie在进行加密。 这就是为什么每次请求我们看到的laravel_session的值都不一样了
clipboard.png

clipboard.png

关注 4 回答 4

嘉兴ing 发布了文章 · 2019-10-28

MySQL WAL(Write-Ahead Log)机制及脏页刷新

最后更新: 2019年10月28日13:35:41

本篇文章属于个人备忘录, 主要内容来自: 极客时间《MySQL实战45讲》的第12讲 - 为什么我的MySQL会“抖”一下

WAL(Write-Ahead Loggin)

WAL 是预写式日志, 关键点在于先写日志再写磁盘.

在对数据页进行修改时, 通过将"修改了什么"这个操作记录在日志中, 而不必马上将更改内容刷新到磁盘上, 从而将随机写转换为顺序写, 提高了性能.

但由此带来的问题是, 内存中的数据页会和磁盘上的数据页内容不一致, 此时将内存中的这种数据页称为 脏页

Redo Log(重做日志)

这里的日志指的是Redo Log(重做日志), 这个日志是循环写入的.

它记录的是在某个数据页上做了什么修改, 这个日志会携带一个LSN, 同时每个数据页上也会记录一个LSN(日志序列号).

这个日志序列号(LSN)可以用于数据页是否是脏页的判断, 比如说 write pos对应的LSN比某个数据页的LSN大, 则这个数据页肯定是干净页, 同时当脏页提前刷到磁盘时, 在应用Redo Log可以识别是否刷过并跳过.

这里有两个关键位置点:

  • write pos 当前记录的位置, 一边写以便后移.
  • checkpoint 是当前要擦除的位置, 擦除记录前要把记录更新到数据文件.

脏页

当内存数据页和磁盘数据页内容不一致的时候, 将内存页称为"脏页".
内存数据页写入磁盘后, 两边内容一致, 此时称为"干净页".
将内存数据页写入磁盘的这个操作叫做"刷脏页"(flush).

InnoDB是以缓冲池(Buffer Pool)来管理内存的, 缓冲池中的内存页有3种状态:

  • 未被使用
  • 已被使用, 并且是干净页
  • 已被使用, 并且是脏页

由于InnoDB的策略通常是尽量使用内存, 因此长时间运行的数据库中的内存页基本都是被使用的, 未被使用的内存页很少.

刷脏页(flush)

时机

刷脏页的时机:

  1. Redo Log写满了, 需要将 checkpoint 向前推进, 以便继续写入日志

    checkpoint 向前推进时, 需要将推进区间涉及的所有脏页刷新到磁盘.

  2. 内存不足, 需要淘汰一些内存页(最久未使用的)给别的数据页使用.

    此时如果是干净页, 则直接拿来复用.

    如果是脏页, 则需要先刷新到磁盘(直接写入磁盘, 不用管Redo Log, 后续Redo Log刷脏页时会判断对应数据页是否已刷新到磁盘), 使之成为干净页再拿来使用.

  3. 数据库系统空闲时

    当然平时忙的时候也会尽量刷脏页.

  4. 数据库正常关闭

    此时需要将所有脏页刷新到磁盘.

InnoDB需要控制脏页比例来避免Redo Log写满以及单次淘汰过多脏页过多的情况.

Redo Log 写满

这种情况尽量避免, 因此此时系统就不接受更新, 所有更新语句都会被堵住, 此时更新数为0.

对于敏感业务来说, 这是不能接受的.

此时需要将 write pos 向前推进, 推进范围内Redo Log涉及的所有脏页都需要flush到磁盘中.

Redo Log设置过小或写太慢的问题: 此时由于Redo Log频繁写满, 会导致频繁触发flush脏页, 影响tps.

内存不足

这种情况其实是常态.

当从磁盘读取的数据页在内存中没有内存时, 就需要到缓冲池中申请一个内存页, 这时候根据LRU(最久不使用)就需要淘汰掉一个内存页来使用.

此时淘汰的是脏页, 则需要将脏页刷新到磁盘, 变成干净页后才能复用.

注意, 这个过程 Write Pos 位置是不会向前推进的.

当一个查询要淘汰的脏页数太多, 会导致查询的响应时间明显变长.

策略

InnoDB 控制刷脏页的策略主要参考:

  • 脏页比例

    当脏页比例接近或超过参数 innodb_max_dirty_pages_pct 时, 则会全力, 否则按照百分比.

  • redo log 写盘速度

    N = (write pos 位置的日志序号 - checkpoint对应序号), 当N越大, 则刷盘速度越快.

最终刷盘速度取上述两者中最快的.

参数 innodb_io_capacity

InnoDB 有一个关键参数: innodb_io_capacity, 该参数是用于告知InnoDB你的磁盘能力, 该值通常建议设置为磁盘的写IOPS.

该参数在 MySQL 5.5 及后续版本才可以调整.

测试磁盘的IOPS:

fio -filename=/data/tmp/test_randrw -direct=1 -iodepth 1 -thread -rw=randrw -ioengine=psync -bs=16k -size=500M -numjobs=10 -runtime=10 -group_reporting -name=mytest
注意, 上面的 -filename 要指定具体的文件名, 千万不要指定分区, 否则会导致分区不可用, 需要重新格式化.

innodb_io_capacity 一般参考 写能力的IOPS

innodb_io_capacity 设置过低导致的性能问题案例:

MySQL写入速度很慢, TPS很低, 但是数据库主机的IO压力并不大.

innodb_io_capacity 设置过小时, InnoDB会认为磁盘性能差, 导致刷脏页很慢, 甚至比脏页生成速度还慢, 就会造成脏页累积, 影响查询和更新性能.

innodb_io_capacity 大小设置:

  • 配置小, 此时由于InnoDB认为你的磁盘性能差, 因此刷脏页频率会更高, 以此来确保内存中的脏页比例较少.
  • 配置大, InnoDB认为磁盘性能好, 因此刷脏页频率会降低, 抖动的频率也会降低.

参数innodb_max_dirty_pages_pct

innodb_max_dirty_pages_pct 指的是脏页比例上限(默认值是75%), 内存中的脏页比例越是接近该值, 则InnoDB刷盘速度会越接近全力.

如何计算内存中的脏页比例:

show global status like 'Innodb_buffer_pool_pages%';

脏页比例 = 100 * Innodb_buffer_pool_pages_dirty / Innodb_buffer_pool_pages_total 的值

参数 innodb_flush_neighbors

当刷脏页时, 若脏页旁边的数据页也是脏页, 则会连带刷新, 注意这个机制是会蔓延的.

innodb_flush_neighbors=1 时开启该机制, 默认是1, 但在 MySQL 8.0 中默认值是 0.

由于机械硬盘时代的IOPS一般只有几百, 该机制可以有效减少很多随机IO, 提高系统性能.

但在固态硬盘时代, 此时IOPS高达几千, 此时IOPS往往不是瓶颈, "只刷自己"可以更快执行完查询操作, 减少SQL语句的响应时间.

如果Redo Log 设置太小

这里有一个案例:

测试在做压力测试时, 刚开始 insert, update 很快, 但是一会就变慢且响应延迟很高.

↑ 出现这种情况大部分是因为 Redo Log 设置太小引起的.

因为此时 Redo Log 写满后需要将 checkpoint 前推, 此时需要刷脏页, 可能还会连坐(innodb_flush_neighbors=1), 数据库"抖"的频率变高.

其实此时内存的脏页比例可能还很低, 并没有充分利用到大内存优势, 此时需要频繁flush, 性能会变差.

同时, 如果Redo Log中存在change buffer, 同样需要做相应的merge操作, 导致 change buffer 发挥不出作用.

查看原文

赞 2 收藏 1 评论 0

嘉兴ing 发布了文章 · 2019-10-23

MySQL必知必会个人备忘录

[TOC]

PDF:MySQL必知必会.pdf

PDF: [SQL学习指南]()

这一份笔记以 《MySQL必知必会》为基础,按照个人需求持续补充。

完善中...不仅限于该入门书上的知识

文章链接:

修改历史:

  • 2020年3月11日 补充图片
  • 2019年11月13日 增加 COUNT 的性能分析

基础概念

MySQL 的两种发音:

  • My-S-Q-L
  • sequel

    ['siːkw(ə)l]

数据库中的 schema : 关于数据库和表的布局及特性的信息

有时,schema 用作数据库的同义词。遗憾的是,schema 的含义通常在上下文中并不是很清晰。

主键(primary key): 一列(或一组列), 其值能唯一区分表中每一行。

  • 任意两行都不具有相同的主键值
  • 每个行都必须具有一个主键值(不允许为NULL值)
  • 使用多个列作为主键值, 多个列值的组合必须是唯一(但单个列的值可以不唯一)

子句(clause) SQL语句由子句构成,有些子句是必需的,而有的是可选的。

  • 一个子句通常由一个关键字和所提供的数据组成。
子句的例子有 SELECT 语句的 FROM 子句,

MariaDB 与 MySQL 版本替代:

  • MySQL 5.1 -> MariaDB 5.1, 5.2, 5.3
  • MySQL 5.5 -> MariaDB 5.5, 10.0
  • MySQL 5.6 -> MariaDB 10.0
  • MySQL 5.7 -> MariaDB 10.2

安装

CentOS 使用yum源安装

下载对应的yum仓库: https://dev.mysql.com/downloa...

# RHEL6
wget https://dev.mysql.com/get/mysql80-community-release-el6-3.noarch.rpm

# RHEL7
wget https://dev.mysql.com/get/mysql80-community-release-el7-3.noarch.rpm

#### 以下以 RHEL6(CentOS) 为例 ####
#### CentOS 7 一些命令不大一样, eg. service, chkconfig ####

# 安装yum源
yum localinstall mysql80-community-release-el6-3.noarch.rpm

# 确认是否已成功安装
yum repolist enabled | grep "mysql.*-community.*"

# 由于默认指定最新的版本(MySQL 8.0), 因此需要手动指定我们想安装的版本 (MySQL 5.7)
# 查看MySQL Yum存储库中的所有子存储库及其状态
yum repolist all | grep mysql
# 禁止8.0系列的子存储库
yum-config-manager --disable mysql80-community
# 启用5.7系列的子存储库
yum-config-manager --enable mysql57-community
# 或在安装时指定仓库 --disablerepo="*" --enablerepo="mysql57-community"

# 安装MySQL
# 会自动安装依赖: mysql-community-client, mysql-community-common, mysql-community-libs, mysql-community-libs-compat
yum install mysql-community-server -y

# 启动mysql server
service mysqld start

# 查看mysql server状态
service mysqld status

# 设置开机自动启动
chkconfig mysqld on

# 查看初始密码
# mysql server初始化时会创建账号 'root'@'localhost', 默认密码存放在错误日志中
grep 'temporary password' /var/log/mysqld.log

# 修改账号密码
mysql -uroot -p
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY '新密码需包含大小写,数字,字符';

# 创建新账号
mysql> GRANT ALL ON *.* TO yjx@'%' IDENTIFIED BY '复杂密码';

# 查看当前mysql编码
show variables like 'character%';

# 修改mysql server字符集
# 修改 /etc/my.cnf
# 设置
#
# [mysqld]
# character_set_server=utf8
# 
# 设置完后重启mysqld

# 不使用service来关闭mysqld的方法
mysqladmin -uroot -p shutdown
mysql_secure_installation 是用于设置root密码, 移除匿名用户等

MySQL 5.7 请不要运行 mysql_secure_installation, 因为安装时已经默认执行过了.

开启多实例

关闭默认实例

# 取消开机启动
chkconfig mysqld off
# 关闭默认mysqld
service mysqld stop

!!! mysql 5.7 以下没有 --initialize-insecure--initialize , 因此初始化数据库必须使用 mysql_install_db --datadir=【数据目录】

分别配置实例配置

mkdir -p /data/mysql_3307
mkdir -p /data/mysql_3308
chown -R mysql:mysql /data/mysql_33*

# 一份简单的配置文件
# basedir 指的是mysqld的安装目录
cat > /data/mysql_3307/my.cnf <<\EOF
[mysqld]
user        = mysql
port        = 3307
socket      = /data/mysql_3307/mysqld.sock
pid-file    = /data/mysql_3307/mysqld.pid
datadir     = /data/mysql_3307/data
basedir     = /usr
bind-address    = 0.0.0.0
character_set_server    = utf8
symbolic-links  = 0
log_error   = /data/mysql_3307/error.log
slow_query_log         = 1
slow_query_log_file    = /data/mysql_3307/slow.log
long_query_time = 2
EOF

# 复制配置文件
sed "s/3307/3308/g" /data/mysql_3307/my.cnf > /data/mysql_3308/my.cnf

# 初始化数据库
# --initialize-insecure 生成无密码的root账号
# --initialize 生成带随机密码的root账号
# 注意参数顺序, 必须先指定 --defaults-file=配置文件, 否则会报错
mysqld --defaults-file=/data/mysql_3307/my.cnf --initialize-insecure
mysqld --defaults-file=/data/mysql_3308/my.cnf --initialize-insecure

# 启动实例
mysqld_safe --defaults-file=/data/mysql_3307/my.cnf &
mysqld_safe --defaults-file=/data/mysql_3308/my.cnf &

# 关闭实例
mysqladmin -S /data/mysql_3307/mysqld.sock shutdown
mysqladmin -S /data/mysql_3308/mysqld.sock shutdown

# 连接实例
mysql -uroot -p -S /data/mysql_3307/mysqld.sock
# # 注意, 默认只有 root@localhost, 需自行创建 root@'%' 账号才能远程登录
mysql -uroot -h192.168.190.100 -P 3308        

使用mysqld_multi管理多实例

# 创建目录
mkdir -p /data/mysql_3307
mkdir -p /data/mysql_3308
chown -R mysql:mysql /data/mysql_33*

# 初始化数据库目录
# 读取配置文件初始化
#mysqld --defaults-file=/data/mysql_3309/my.cnf --initialize-insecure
#mysqld --defaults-file=/data/mysql_3310/my.cnf --initialize-insecure
# [推荐]不读取配置文件, 直接指定目录
mysqld -u mysql --basedir=/usr --datadir=/data/mysql_3309/data --initialize-insecure
mysqld -u mysql --basedir=/usr --datadir=/data/mysql_3310/data --initialize-insecure

# 直接修改 /etc/my.cnf 或新创建一份multi配置 /data/multi.cnf
# 若是新创建multi配置, 则执行mysql_multi时需指定该配置文件
cat > /etc/multi.cnf <<EOF
[mysqld_multi]
mysqld               = /usr/bin/mysqld_safe
mysqladmin           = /usr/bin/mysqladmin

[mysqld3309]
user        = mysql
port        = 3309
server-id   = 3309
socket      = /data/mysql_3309/mysqld.sock
pid-file    = /data/mysql_3309/mysqld.pid
datadir     = /data/mysql_3309/data
basedir     = /usr
bind-address    = 0.0.0.0
character_set_server    = utf8
symbolic-links  = 0
log_error   = /data/mysql_3309/error.log
slow_query_log         = 1
slow_query_log_file    = /data/mysql_3309/slow.log
long_query_time = 2
max_allowed_packet = 100m
EOF

[mysqld3310]
user        = mysql
port        = 3310
server-id   = 3310
socket      = /data/mysql_3310/mysqld.sock
pid-file    = /data/mysql_3310/mysqld.pid
datadir     = /data/mysql_3310/data
basedir     = /usr
bind-address    = 0.0.0.0
character_set_server    = utf8
symbolic-links  = 0
log_error   = /data/mysql_3310/error.log
slow_query_log         = 1
slow_query_log_file    = /data/mysql_3310/slow.log
long_query_time = 2
max_allowed_packet = 100m
EOF


# 启动实例 3309 和 3310, 支持使用连字符语法或用逗号分隔
mysqld_multi --defaults-file=/data/multi.cnf start 3309,3310
# 关闭实例
mysqld_multi --defaults-file=/data/multi.cnf stop 3309-3310
# 查看实例状态
mysqld_multi --defaults-file=/data/multi.cnf report 3309-3310
!!! mysqld_multi 不支持 !include 或 !includedir

eg. 采用 heartbeat + drbd + mysql 实现mysql高可用双机热备方案

未实践

主从复制

目的:

  • 实时备份
  • 读写分离, 主写, 从读

eg. MySQL 主从复制

简单原理

img

  1. Master的IO线程将操作记录到二进制日志(Binary log)中
  2. Slave的IO线程会同步拉取Master的二进制日志并写入本地中继日志(Relay log)
  3. Slave的SQL线程会从中继日志中读取操作并在当前实例上重放操作.

配置及操作

Master 配置文件

log_bin        = /data/3306/mysql-bin
server-id     = 1
#expire_logs_days        = 10
#max_binlog_size         = 100M
必须打开 master 端的 Binary Log

Slave 配置文件

read-only    = 1
log_bin        = /data/3307/mysql-bin
server-id     = 2
# 可选, 指定中继日志的位置和命名
relay_log    = /data/3307/relay.log
# 允许备库将其重放的事件也记录到自身的二进制日志中
log_slave_updates    = 1

1. 确认Master 开启了二进制日志

mysql> SHOW MASTER STATUS;

Tip. 锁表

FLUSH TABLE WITH READ LOCK;

UNLOCK TABLES;

2. Master 创建专门用于主从复制的账号

GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *.* TO 'rep'@'192.168.100.%' IDENTIFIED BY '123456';
复制账号在主库上只需要 REPLICATION SLAVE 权限,

1.用来监控和管理复制的账号需要REPLICATION CLIENT 权限,并且针对这两种目的使用同一个账号更加容易(而不是为了两个目的各创建一个账号)。

2.如果在主库上建立了账号,然后从主库将数据克隆到备库上时,备库也就设置好了-变成主库所需要的配置。这样后续有需要可以方便的交换主备库角色。

3. 主-从 数据保持一致

# 主库导出数据快照
# 若表全都使用InnoDB引擎, 则可使用 --single-transaction 来代替 --lock-all-tables
# --master-data=1 导出sql中会包含CHANGE MASTER语句
# eg.    CHANGE MASTER TO MASTER_LOG_FILE='bin.000003', MASTER_LOG_POS=25239;
# --master-data=2 导出CHANGE MASTER语句,但是会被注释(仅在平时备份时导出用)
mysqldump -uroot -p -S /data/3306/mysql.sock -A --master-data=1 --lock-all-tables > master.sql > master.sql

# 从库导入
mysql -uroot -p -S /data/3307/mysql.sock < master.sql
此处假设主数据库已经在使用, 而从数据库是新的, 因此需要先保持两边数据一致

4. Slave 更改从库的连接参数

# 尝试在此处不设置 MASTER_LOG_FILE,MASTER_LOG_POS, 结果后面 START SLAVE 后一直出错
# 此处的 MASTER_LOG_FILE,MASTER_LOG_POS 可以在日志中查看
mysql> CHANGE MASTER TO MASTER_HOST='192.168.190.100',MASTER_PORT=3309,MASTER_USER='rep',MASTER_PASSWORD='123456',MASTER_LOG_FILE='bin.000003', MASTER_LOG_POS=25239;

# 确认一下配置文件正确
cat /data/3307/data/master.info

# 从库连接主库
mysql> START SLAVE;
# 确认连接正常
mysql> SHOW SLAVE STATUS\G;

管理

-- 查看master的状态, 尤其是当前的日志及位置
show master status; 

-- 查看slave的状态. 
show slave status; 

-- 重置slave状态,用于删除SLAVE数据库的relaylog日志文件,并重新启用新的relaylog文件.会忘记 主从关系,它删除master.info文件和relay-log.info 文件
reset slave; 

-- 启动slave 状态(开始监听msater的变化)
start slave; 

-- 暂停slave状态;
stop slave; 

-- 跳过导致复制终止的n个事件,仅在slave线程没运行的状况下使用
set global sql_slave_skip_counter = n; 

关键参数配置

以下参数中, 部分参数只对InnoDB引擎有效

参数名含义建议
max_connections最大客户端连接数
innodb_buffer_pool_sizeInnodb存储引擎缓存池大小建议设置为物理内存的 80% 左右
innodb_file_per_tableInnodb表包含两部分: 表结构定义(.frm文件)和数据, 若该参数值为0, 则表的数据就存在共享表空间 , 若只为1, 则单独存放在一个.ibd的文件中.
若放在共享表空间, drop 表时, 空间是不会回收的.
设为 1, 表示每个表使用独立表空间(.ibd文件), 方便回收空间.
MySQL 5.6.6 开始默认为1
innodb_log_file_size事务日志(Redo Log)单个大小(文件 ib_logfile*总的日志大小足以容纳1个小时的量
innodb_log_files_in_group事务日志数量默认是 2
innodb_flush_logs_at_trx_commit事务提交时写日志的方式
0: 每秒将日志持久化到磁盘. 数据库崩溃时丢失最多1秒数据
1: 默认, 每次事务提交都将日志持久化到磁盘, 最安全, 性能最一般.
2: 每次事务提交都写入磁盘(指磁盘缓冲区), 具体何时持久化到磁盘则由操作系统控制. 系统崩溃时丢失数据
不推荐设为 0.
对性能要求较高可以设置为 2.
sync_binlogMySQL 控制写入 BinLog 的方式
0 : 每次事务提交写入磁盘缓冲区, 由操作系统控制何时持久化
N: 每进行N个事务提交后持久化到磁盘, 当N=1时最安全但性能最差
5.7.7及以后默认是1, 之前默认是0
mysql 自带 mysqlslap 压测工具, 可以自行测试, 个人未使用过.

计算合适的 Redo Log 大小

调整Redo Log大小一般是通过修改 innodb_log_file_size 配置.

  1. 在业务高峰期, 计算出1分钟写入的redo log量

    # 只显示结果中 Log 相关
    > pager grep Log;
    # 查看日志位置, sleep 
    > show engine innodb status\G; select sleep(60); show engine innodb status\G;
    # 取消结果过滤
    > nopager;

    通过查看两次的 Log sequence number 值, 计算出差值, 单位是字节.

  2. 评估1个小时的 redo log 量

    将上述值乘以 60 得到合理的 Redo Log 大小. 因此 innodb_log_file_size 推荐设置成 估算的Redo Log 大小 / 日志文件数(innodb_log_files_in_group)

  3. 正常关闭 mysql
  4. 修改配置中 innodb_log_file_size 值, 并将数据目录下的所有 ib_logfile* 文件move走(先不删, 防止出问题无法启动)
  5. 启动 mysql, 确认没问题后就可以删除 ib_logfile*
备注: 有看到说 mysql5.6 版本及以后无需手动删除 ib_logfile* 文件.

如果Redo Log太小, 会导致频繁刷脏页??

太大会导致故障恢复时恢复时间太长(甚至长到不可接受的程度)

基本使用

USE 数据库名;        -- 选择数据库

SHOW DATABASES;        -- 查看数据库列表

SHOW TABLES;        -- 查看当前数据库内的表的列表

SHOW COLUMNS FROM `表名`;        -- 查看表结构

SHOW STATUS;                    -- 用于显示广泛的服务器状态信息

SHOW CREATE DATABASE `数据库名`;    -- 显示创建特定数据库的MySQL语句
SHOW CREATE TABLE `表名`;            -- 显示创建表的MySQL语句;

SHOW GRANTS;                -- 显示授权指定用户的安全权限, 默认是当前用户
SHOW GRANTS FOR 用户@"..."    -- eg. root@'%' 或 root@localhost 或 root@0.0.0.0

SHOW ERRORS        -- 用来显示服务器错误消息
SHOW WARNINGS    -- 用来显示服务器警告消息
  • DESCRIBE 表名

    等同于 SHOW COLUMNS FROM 表名, MySQL独有

  • STATUS

    快速查看当前实例状态, eg.

    --------------
    mysql  Ver 14.14 Distrib 5.7.22, for Linux (x86_64) using  EditLine wrapper
    
    Connection id:        28
    Current database:    mysql_learn
    Current user:        root@localhost
    SSL:            Not in use
    Current pager:        stdout
    Using outfile:        ''
    Using delimiter:    ;
    Server version:        5.7.22-0ubuntu18.04.1 (Ubuntu)
    Protocol version:    10
    Connection:        127.0.0.1 via TCP/IP
    Insert id:        114
    Server characterset:    latin1
    Db     characterset:    latin1
    Client characterset:    utf8
    Conn.  characterset:    utf8
    TCP port:        3306
    Uptime:            7 days 23 hours 29 min 13 sec
    
    Threads: 6  Questions: 817  Slow queries: 0  Opens: 205  Flush tables: 1  Open tables: 150  Queries per second avg: 0.001
    --------------

数据类型

数值数据类型

<u>有符号或无符号</u>

所有数值数据类型(除 BIT 和 BOOLEAN 外)都可以有符号或无符号。有符号数值列可以存储正或负的数值,无符号数值列只能存储正数。默认情况为有符号,但如果你知道自己不需要存储负值,可以使用 UNSIGNED 关键字,这样做将允许你存储两倍大小的值。

类型大小范围(有符号)范围(无符号)用途
TINYINT1 字节(-128,127)(0,255)小整数值
SMALLINT2 字节(-32 768,32 767)(0,65 535)大整数值
MEDIUMINT3 字节(-8 388 608,8 388 607)(0,16 777 215)大整数值
INT或INTEGER4 字节(-2 147 483 648,2 147 483 647)(0,4 294 967 295)大整数值
BIGINT8 字节(-9,223,372,036,854,775,808,9 223 372 036 854 775 807)(0,18 446 744 073 709 551 615)极大整数值
FLOAT4 字节(-3.402 823 466 E+38,-1.175 494 351 E-38),0,(1.175 494 351 E-38,3.402 823 466 351 E+38)0,(1.175 494 351 E-38,3.402 823 466 E+38)单精度 浮点数值
DOUBLE8 字节(-1.797 693 134 862 315 7 E+308,-2.225 073 858 507 201 4 E-308),0,(2.225 073 858 507 201 4 E-308,1.797 693 134 862 315 7 E+308)0,(2.225 073 858 507 201 4 E-308,1.797 693 134 862 315 7 E+308)双精度 浮点数值
DECIMALDECIMAL(M,D) ,如果M>D,为M+2否则为D+2依赖于M和D的值依赖于M和D的值小数值

关键字INT是INTEGER的同义词,关键字DEC是DECIMAL的同义词。

int(m) 里的m是表示 SELECT 查询结果集中的显示宽度,并不影响实际的取值范围.

FLOAT 类型

  • float(m,d)

DOUBLE 类型

  • double(m,d)

DECIMAL 类型

  • 精确值
  • decimal(m,d) m<=65, d<=30, m是总位数, d是小数位数

字符串数据类型

类型大小用途
CHAR0-255字节定长字符串
VARCHAR0-65535 字节变长字符串
TINYBLOB0-255字节不超过 255 个字符的二进制字符串
TINYTEXT0-255字节短文本字符串
BLOB0-65 535字节二进制形式的长文本数据
TEXT0-65 535字节长文本数据
MEDIUMBLOB0-16 777 215字节二进制形式的中等长度文本数据
MEDIUMTEXT0-16 777 215字节中等长度文本数据
LONGBLOB0-4 294 967 295字节二进制形式的极大文本数据
LONGTEXT0-4 294 967 295字节极大文本数据

CHAR 类型

  • char(n) 若存入字符数小于n,则以空格补于其后,查询之时再将空格去掉。所以 char 类型存储的字符串末尾不能有空格
  • 效率比 VARCHAR 高点

VARCHAR 类型

  • 长度设置, 在MySQL 5之后是按<u>字符数</u>, 而不是字节数.
  • varchar(20) 可以存储20个<u>字符</u>
  • varchar 头部会占用 1个(n<=255)或2个字节(n>255)保存字符串长度, 若值可设为 null, 则还需要一个1字节记录null, 因此保存utf8编码的字符串最多可存 (65535 - 3)/3 = 21844
  • 若是utf8编码
  • 效率比 TEXT 高

TEXT 类型

  • 创建索引时要指定前多少个字符
  • 不能有默认值
  • text 头部会占用 2个字节来保存长度

日期和时间类型

类型大小 (字节)范围格式用途
DATE31000-01-01/9999-12-31YYYY-MM-DD日期值
TIME3'-838:59:59'/'838:59:59'HH:MM:SS时间值或持续时间
YEAR11901/2155YYYY年份值
DATETIME81000-01-01 00:00:00/9999-12-31 23:59:59YYYY-MM-DD HH:MM:SS混合日期和时间值
TIMESTAMP41970-01-01 00:00:00/2038结束时间是第 2147483647 秒,北京时间 2038-1-19 11:14:07,格林尼治时间 2038年1月19日 凌晨 03:14:07YYYYMMDD HHMMSS混合日期和时间值,时间戳

每个时间类型有一个有效值范围和一个"零"值,当指定不合法的MySQL不能表示的值时使用"零"值。

TIMESTAMP类型有专有的自动更新特性

  • update_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
  • 保存毫秒数(类似php 的 microtime(true))
  • timestamp列默认not null。没有显式指定nullable,那么default null不合法
  • mysql不会给timestamp设置默认值,除非显式设置default约束或者可空null。特例:mysql会给表第一个timestamp类型的字段同时添加default current_timestampon update timestamp
  • 其他情况均会引起不合法报错
  • ↑ 总结: 最好手动设置 NULL 以免出错

日期函数:

  • CURRENT_TIMESTAMP, CURRENT_TIMESTAMP()NOW() 的同义词
  • SYSDATE() 获取的是函数执行时的时间, NOW()获取的是执行语句时的<u>开始时间</u>.

二进制数据类型

1559035285382

检索数据

SELECT 语句

子句的书写顺序很重要:

SELECT ... FROM ... JOIN ... ON ... WHERE ... GROUP BY ... HAVING ... ORDER BY ... LIMIT ...

执行顺序:

FROM(包含JOIN) > WHERE > GROUP BY > 聚集函数字段计算 > HAVING > SELECT 的字段 > ORDER BY > LIMIT

举例:

SELECT player.team_id, count(*) as num    -- 顺序5
FROM player JOIN team ON player.team_id = team.team_id    -- 顺序1
WHERE height > 1.80 -- 顺序2
GROUP BY player.team_id    -- 顺序3
HAVING num > 2    -- 顺序4
ORDER BY num DESC -- 顺序6
LIMIT 2; -- 顺序7

基本检索

SELECT `列名1`,`列名2` FROM `表名`;    -- 检索指定列
SELECT * FROM `表名`;                -- 检索所有列

DISTINCT关键字: 只返回不同的值

SELECT DISTINCT `列名1` FROM `表名`;        -- 只返回唯一的 `列名1`行
SELECT DISTINCT `列名1`,`列名2` FROM `表名`;    -- DISTINCT应用于所有的列, 返回 (`列名1`, `列名2`) 不同的行

LIMIT关键字: 限制结果

SELECT * FROM `表名` LIMIT <limit>;        -- 限制返回最多 <limit> 条, 等同 0,<limit>
SELECT * FROM `表名` LIMIT <offset>,<limit>;    -- 略过前 <offset> 条记录, 返回最多 <limit> 条
SELECT * FROM `表名` LIMIT <limit> OFFSET <offset>;    -- MySQL 5 语法糖

完全限定名

SELECT `表名`.`列名` FROM `数据库名`.`表名`;        -- 有一些情形需要完全限定名

ORDER 子句

SELECT * FROM `表名` ORDER BY `列名` <dir>;        -- <dir> 默认是 ASC, 可指定 DESC.
SELECT * FROM `表名` ORDER BY `列名1` <dir1>,`列名2` <dir2>;    -- 仅在`列名1`等值时才按`列名2`排序

排序方式可选:

  • ASC 升序(默认), ASCENDING
  • DESC 降序
  • ORDER BY 字句使用的排序列不一定是显示的列, 这是允许的.
  • ORDER BY
  • 对文本数据排序时, 大小写的排序取决于数据库如何设置 COLLATE

WHERE 子句

操作符说明
=等于
<>不等于
!=不等于
<小于
<=小于等于
>大于
>=大于等于
BETWEEN ... AND ...在指定的两个值之间
... IS NULL没有值
... IS NOT NULL非空, 有值
IN (…)在元组
NOT IN (...)不在元组
  • 一个列不包含值时, 称其为空值NULL

AND, OR 操作符

计算次序: AND 运算符优先级更高, 在处理OR操作符前会先处理AND操作符, 举例:

SELECT prod_name,prod_price,vend_id FROM products WHERE vend_id=1002 OR vend_id=1003 AND prod_price>=10;

-- 等价于

SELECT prod_name,prod_price,vend_id FROM products WHERE (vend_id=1002) OR (vend_id=1003 AND prod_price>=10);

IN操作符

SELECT * FROM `表名` WHERE `列名` IN (值1, 值2, ..., 值N);

IN 功能等同于 OR, 那么为什么用 IN:

  • 语法清晰直观
  • 执行更快
  • <u>可以包含其他SELECT 语句, 得能够更动态地建立 WHERE 子句</u>

NOT操作符

否定它之后所跟的任何条件

... WHERE `列名` NOT IN (值1, ..., 值N);

... WHERE `列名` IS NOT NULL;

... WHERE `列名` NOT BETWEEN 1 AND 10;

... WHERE `列名` NOT EXISTS (...);

NOT 在复杂的WHERE字句中很有用.

例如,在与 IN 操作符联合使用时, NOT 使找出与条件列表不匹配的行非常简单。

MySQL支持使用 NOT 对 IN 、 BETWEEN 和
EXISTS子句取反,这与多数其他 DBMS允许使用 NOT 对各种条件
取反有很大的差别。

LIKE操作符

通配符(wildcard)

通配符含义
%任何字符出现任意次数(0,1,N), 注意不匹配 NULL
_匹配单个字符(1)

like 匹配完整的列.

通配符置于搜索模式开始处, 不会使用索引.

注意NULL 虽然似乎 % 通配符可以匹配任何东西,但有一个例外,即 NULL 。即使是 WHERE prod_name LIKE '%' 也不能匹配用值 NULL 作为产品名的行。
SELECT * FROM `表名` WHRER `列名` LIKE `a%d`;

SELECT * FROM `表名` WHRER `列名` LIKE `a_cd`;

REGEXP 正则表达式

MySQL仅支持多数正则表达式实现的一个很小的子集。

eg. 不支持 \d, \s
SELECT * FROM `表名` WHERE `列名` REGEXP '^[0-9]';    -- 匹配数字开头的, 默认不区分大小写

SELECT * FROM `表名` WHERE `列名` REGEXP BINARY "正则表达式";    -- 区分大小写

多数正则表达式实现使用单个反斜杠转义特殊字符,以便能使用这些字符本身。但MySQL要求两个反斜杠(MySQL自己解释一个,正则表达式库解释另一个)。

转义 . 时, 需使用 \\. 而非 \.

1558600943297

1558600935224

1558600968226

1558601019873

计算字段

计算字段并不实际存在于数据库表中。计算字段是运行时在 SELECT 语句内创建的。
字段(field) 基本上与列(column)的意思相同,经常互换使用,不过数据库列一般称为列,而术语字段通常用在计算字段的连接上。

AS 别名

别名(alias)是一个字段或值的替换名。

SELECT `列名` AS `别名` FROM `表名`;

别名的其他常见用途:

  • 在实际的表列名包含不符合规定的字符(如空格)时重新命名它
  • 在原来的名字含混或容易误解时扩充它,等等。

算数运算

1558601948352

圆括号可用来区分优先顺序。

函数

函数没有SQL的可移植性强, 几乎每种主要的DBMS的实现都支持其他实现不支持的函数,而且有时差异还很大。

字符串处理

concat()字符串拼接

SELECT concat(`列名1`, '(', `列名2`, ')') FROM `表名`;    -- 组成: `列名1`(`列名2`)
MySQL的不同之处 多数DBMS使用 + 或 || 来实现拼接,MySQL则使用 Concat() 函数来实现。当把SQL语句转换成MySQL语句时一定要把这个区别铭记在心

1558602282966

  • LOCATE(substr,str)

    返回子串 substr 在字符串 str 中第一次出现的位置。如果子串 substr 在 str 中不存在,返回值为 0:

  • LOCATE(substr,str,pos)

    多字节安全

    返回子串 substr 在字符串 str 中的第 pos 位置后第一次出现的位置。如果 substr 不在 str 中返回 0

  • substr(str,pos[, len])

    等同于 substring

    pos参数 表示从该位置(包含)开始截取

  • substring_index

    返回从字符串str分隔符delim中的计数发生前的子字符串。 如果计数是正的,则返回一切到最终定界符(从左边算起)的左侧。如果count为负,则从右边开始截取

  • concat_ws(separator, str1, str2, ...)

    使用指定分隔符拼接参数, 忽略NULL

  • group_concat(...)

    函数返回一个字符串结果,该结果由分组中的值连接组合而成。

!!! 注意 MySQL字符串位置是从1开始

日期和时间

1558603084142

MySQL 日期格式固定为: yyyy-mm-dd, 无论是插入,更新,或WHERE字句过滤.

补充

函数说明示例
from_unixtime()将时间戳转为日期from_unixtime(1559001600)
unix_timestamp()将指定日期或日期字符串转换为时间戳unix_timestamp(Now())
DATE_SUB()日期减少,类似 DATE_ADDdate_sub('2016-08-01',interval 1 day)

数值处理

1558603490365

聚集函数

1558603562124

标准偏差 MySQL还支持一系列的标准偏差聚集函数

  • AVG(), MAX(), MIN(), SUM() 函数忽略列值为 NULL 的行
  • COUNT(*) 对表中行的数目进行计数,不管表列中包含的是空值( NULL )还是非空值
  • COUNT(column) 对特定列中具有值的行进行计数,忽略NULL 值

上述聚集函数可配合 DISTINCT 来使用

SELECT COUNT(DISTINCT `列名`) FROM `表名`;
SELECT AVG(DISTINCT `列名`) FROM `表名`;
SELECT SUM(DISTINCT `列名`) FROM `表名`;

COUNT 性能问题

对于MyISAM引擎, 每张表保存了一个总行数的字段, 统计总行数直接返回该字段值即可.

对于InnoDB引擎, 由于MVCC(多版本并发控制)的存在, 因此每次都需要全表扫描, 这里只讨论InnoDB引擎需要注意的点.

对于统计表的总行数, 从效率角度看从高到低顺序如下:

  1. COUNT(*)

    优化器做了语义优化: 取行数

    Innodb遍历整张表, 但不取值, 直接计数.

  2. COUNT(1)

    Innodb遍历整张表, 但不取值,返回空行, server层对于返回的每一行自行放入一个 1, 并按行累加.

  3. COUNT(主键id)

    Innodb会遍历整张表, 取出每一行的主键id返回给server层, server层拿到主键id后按行累加.

  4. COUNT(字段)

    Innodb遍历整张表, 取出每一行的该字段返回给server层, server层拿到该字段后, 此时分2种情况:

    • 若该字段允许 null, 则需要先判断一下
    • 若该字段 not null, 则直接按行累加

COUNT(字段) < COUNT(主键id) < COUNT(1)COUNT(*)

对于上述的 COUNT, Innodb 会优先考虑使用最小的索引树(前提是要有COUNT所需的字段), 这样扫描的页比较少.

字段允许为null的二级索引, 它的主键是存在的, 因此也可以用于统计总行数.

优化考虑:

  1. 尽量使用 COUNT(*)
  2. 对于较为频繁的 COUNT 操作, 可以考虑找一个字段长度最小的对其建立索引, 以提高 COUNT 效率.
  3. 对于非常频繁的 COUNT 操作, 同样记录数多, 可以考虑使用缓存系统来保存计数(eg. Redis), 这种方式会存在一定程度上的不一致性(包括计数丢失和计数不精确), 如果能接受的话这是一种很好的方案,如果不能接受, 则可以考虑下面的这个方案.
  4. 对于非常频繁的 COUNT 操作, 同样记录数多, 也可以考虑使用数据库来保存计数, 通过建立一个单独的计数表, 每次insert或delete的同时在同一个事物中对该计数的记录做修改. 但有一些点要注意:

    • 考虑到锁对性能的影响, 因此修改计数记录的操作应放在事务的最后面来操作.
    • 同样考虑到锁对性能的影响, 可以将计数记录扩展到N条, 每次随机选取一条来操作. 最后统计总行数时, 再选取所有记录进行 SUM 操作.

类型转换函数

MySQL 提供了以下数据类型转换函数

  1. CAST()函数

    CAST(value as type) 就是CAST(xxx AS 类型)

  2. CONCERT()函数

    CONVERT(value, type) 就是CONVERT(xxx,类型)

支持的类型如下:

  1. 二进制: BINARY
  2. 字符型,可带参数 : CHAR()
  3. 日期 : DATE
  4. 时间: TIME
  5. 日期时间型 : DATETIME
  6. 浮点数 : DECIMAL
  7. 整数 : SIGNED
  8. 无符号整数 : UNSIGNED

其他函数-待整理

CHAR_LENGTH(str)
        返回值为字符串str 的长度,长度的单位为字符。一个多字节字符算作一个单字符。
        对于一个包含五个二字节字符集, LENGTH()返回值为 10, 而CHAR_LENGTH()的返回值为5。

    CONCAT(str1,str2,...)
        字符串拼接
        如有任何一个参数为NULL ,则返回值为 NULL。
    CONCAT_WS(separator,str1,str2,...)
        字符串拼接(自定义连接符)
        CONCAT_WS()不会忽略任何空字符串。 (然而会忽略所有的 NULL)。

    CONV(N,from_base,to_base)
        进制转换
        例如:
            SELECT CONV('a',16,2); 表示将 a 由16进制转换为2进制字符串表示

    FORMAT(X,D)
        将数字X 的格式写为'#,###,###.##',以四舍五入的方式保留小数点后 D 位, 并将结果以字符串的形式返回。若  D 为 0, 则返回结果不带有小数点,或不含小数部分。
        例如:
            SELECT FORMAT(12332.1,4); 结果为: '12,332.1000'
    INSERT(str,pos,len,newstr)
        在str的指定位置插入字符串
            pos:要替换位置其实位置
            len:替换的长度
            newstr:新字符串
        特别的:
            如果pos超过原字符串长度,则返回原字符串
            如果len超过原字符串长度,则由新字符串完全替换
    INSTR(str,substr)
        返回字符串 str 中子字符串的第一个出现位置。

    LEFT(str,len)
        返回字符串str 从开始的len位置的子序列字符。

    LOWER(str)
        变小写

    UPPER(str)
        变大写

    LTRIM(str)
        返回字符串 str ,其引导空格字符被删除。
    RTRIM(str)
        返回字符串 str ,结尾空格字符被删去。
    SUBSTRING(str,pos,len)
        获取字符串子序列

    LOCATE(substr,str,pos)
        获取子序列索引位置

    REPEAT(str,count)
        返回一个由重复的字符串str 组成的字符串,字符串str的数目等于count 。
        若 count <= 0,则返回一个空字符串。
        若str 或 count 为 NULL,则返回 NULL 。
    REPLACE(str,from_str,to_str)
        返回字符串str 以及所有被字符串to_str替代的字符串from_str 。
    REVERSE(str)
        返回字符串 str ,顺序和字符顺序相反。
    RIGHT(str,len)
        从字符串str 开始,返回从后边开始len个字符组成的子序列

    SPACE(N)
        返回一个由N空格组成的字符串。

    SUBSTRING(str,pos) , SUBSTRING(str FROM pos) SUBSTRING(str,pos,len) , SUBSTRING(str FROM pos FOR len)
        不带有len 参数的格式从字符串str返回一个子字符串,起始于位置 pos。带有len参数的格式从字符串str返回一个长度同len字符相同的子字符串,起始于位置 pos。 使用 FROM的格式为标准 SQL 语法。也可能对pos使用一个负值。假若这样,则子字符串的位置起始于字符串结尾的pos 字符,而不是字符串的开头位置。在以下格式的函数中可以对pos 使用一个负值。

        mysql> SELECT SUBSTRING('Quadratically',5);
            -> 'ratically'

        mysql> SELECT SUBSTRING('foobarbar' FROM 4);
            -> 'barbar'

        mysql> SELECT SUBSTRING('Quadratically',5,6);
            -> 'ratica'

        mysql> SELECT SUBSTRING('Sakila', -3);
            -> 'ila'

        mysql> SELECT SUBSTRING('Sakila', -5, 3);
            -> 'aki'

        mysql> SELECT SUBSTRING('Sakila' FROM -4 FOR 2);
            -> 'ki'

    TRIM([{BOTH | LEADING | TRAILING} [remstr] FROM] str) TRIM(remstr FROM] str)
        返回字符串 str , 其中所有remstr 前缀和/或后缀都已被删除。若分类符BOTH、LEADIN或TRAILING中没有一个是给定的,则假设为BOTH 。 remstr 为可选项,在未指定情况下,可删除空格。

        mysql> SELECT TRIM('  bar   ');
                -> 'bar'

        mysql> SELECT TRIM(LEADING 'x' FROM 'xxxbarxxx');
                -> 'barxxx'

        mysql> SELECT TRIM(BOTH 'x' FROM 'xxxbarxxx');
                -> 'bar'

        mysql> SELECT TRIM(TRAILING 'xyz' FROM 'barxxyz');
                -> 'barx'

分组

GROUP BY ... HAVING ...

创建分组

聚集 配合 分组 GROUP BY 子句指示MySQL分组数据,然后对每个组而不是整个结果集进行聚集。

使用 GROUP BY重要规定

  • ??? GROUP BY 子句可以包含任意数目的列。这使得能对分组进行嵌套,为数据分组提供更细致的控制。
  • ??? 如果在 GROUP BY 子句中嵌套了分组,数据将在最后规定的分组上进行汇总。换句话说,在建立分组时,指定的所有列都一起计算(所以不能从个别的列取回数据)。
  • <u>GROUP BY 子句中列出的每个列都必须是检索列或有效的表达式(但不能是聚集函数)。如果在 SELECT 中使用表达式,则必须在GROUP BY 子句中指定相同的表达式。不能使用别名。</u>
  • 除聚集计算语句外, SELECT 语句中的每个列都必须在 GROUP BY 子句中给出。
  • 如果分组列中具有 NULL 值,则 NULL 将作为一个分组返回。如果列中有多行 NULL 值,它们将分为一组。
  • GROUP BY 子句必须出现在 WHERE 子句之后, ORDER BY 子句之前。

1558604730593

??

想要SELECT 非分组字段

SQL92以及更早的SQL标准中不允许查询除了GROUP BY之外的非聚合的列 .

https://dev.mysql.com/doc/ref...

在MYSQL的5.7.5以及以上版本默认的设置是:ONLY_FULL_GROUP_BY ,该设置则约束查询必须是聚合的列。但是在其版本之前则允许查询非聚合的列。

如果在开启 ONLY_FULL_GROUP_BY 但是还想查询非聚合的列可以使用ANY_VALUE(非聚合列)进行查询,ANY_VALUE参考文档。还有一种情况开启ONLY_FULL_GROUP_BY时,如果GROUP BY是主键或者 unique NOT NULL 时是可以查询非聚合的列的,原因是此时分组的key是主键,则每一个分组只有一条数据,因此是可以进行查询非聚合的列的。最后对于高于5.7.5的版本如果想查询非聚合的列可以关闭ONLY_FULL_GROUP_BY 属性,即:

set sql_mode ='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
这部分参考
SELECT name, ANY_VALUE(address), MAX(age) FROM t GROUP BY name;

过滤分组

HAVING 过滤的是分组

WHERE 过滤的是行
  • HAVING 可以使用别名

HAVING 和 WHERE 的差别

这里有另一种理解方法, WHERE 在数据分组前进行过滤, HAVING 在数据分组后进行过滤。这是一个重要的区别, WHERE 排除的行不包括在分组中。这可能会改变计算值,从而影响 HAVING 子句中基于这些值过滤掉的分组。

子查询

根据子查询执行的次数, 对子查询进行分类:

  1. 非关联子查询

    查询不依赖外层(即与主查询无关), 该子查询只需执行一次, 得到的数据结果可以作为外层查询的条件直接使用.

  2. 关联子查询

    查询时依赖外层(用到外层表数据, 与主查询相关), 因此每次外层查询都要根据外层查询结果再进行子查询, 该子查询需要执行多次.

在实际使用时由于性能的限制,不能嵌套太多的子查询。

eg. 返回订购产品 TNT2 的客户列表

select * from customers
where cust_id in (select distinct cust_id 
                  from orders 
                  where order_num in (select order_num
                                      from orderitems
                                      where prod_id="TNT2")
                 );

子查询的关键字

存在性子查询

  • EXISTS

    判断条件是否满足, 满足为True, 否则为False

集合比较子查询

  • IN

    判断是否在集合中

  • ANY

    必须与比较操作符一起使用, 与子查询中<u>任意值</u>比较

    SELECT * FROM A WHERE A.cc > ANY (SELECT cc FROM B);
  • SOME

    是ANY的别名, 等价于 ANY, 一般常用 ANY

  • ALL

    必须与比较操作符一起使用, 与子查询中<u>所有值</u>比较

    SELECT * FROM A WHERE A.cc > ALL (SELECT cc FROM B);

Q. 对于 表 A, B 的子查询, EXISTS 和 IN 要选哪个?

A. 需要根据表A 和 表B 的表大小及索引情况而定.

通常使用 IN 及 EXISTS 情况可以概括为以下 SQL 语句:

-- 语句1
SELECT * FROM A WHERE cc IN (SELECT cc FROM B);

-- 语句2
SELECT * FROM A WHERE EXISTS (SELECT cc FROM B WHERE A.cc = B.cc);

原则: 小表驱动大表.

结论:

  • 如果 B表 比较小, 且 A表在 cc 列上有索引, 则推荐使用语句1.
这里的表大小指的是根据where筛选后的大小, 而非表的原始大小
  • 如果 A表 比较小, 且 B表在 cc 列上有索引, 则推荐使用语句2.

表联结/连接 Join

1558670169544

联结是一种机制,用来在一条 SELECT语句中关联表,因此称之为联结。

使用特殊的语法,可以联结多个表返回一组输出,联结在运行时关联表中正确的行。

使用联结的要点:

  • 注意所使用的联结类型。一般我们使用内部联结,但使用外部联结也是有效的。
  • 保证使用正确的联结条件,否则将返回不正确的数据。
  • 应该总是提供联结条件,否则会得出笛卡儿积。
  • 在一个联结中可以包含多个表,甚至对于每个联结可以采用不同的联结类型。虽然这样做是合法的,一般也很有用,但应该在一起测试它们前,分别测试每个联结。这将使故障排除更为简单。

<u>表别名</u>只在查询执行中使用。与列别名不一样,表别名返回到客户机。

<u>完全限定列名</u> 在引用的列可能出现二义性时,必须使用完全限定列名(用一个点分隔的表名和列名)。如果引用一个没有用表名限制的具有二义性的列名,MySQL将返回错误。

在联结两个表时,你实际上做的是将第一个表中的每一行与第二个表中的每一行配对。 WHERE 子句作为过滤条件,它只包含那些匹配给定条件(这里是联结条件)的行。没有WHERE 子句,第一个表中的每个行将与第二个表中的每个行配对,而不管它们逻辑上是否可以配在一起。

示例代码↓

-- 将表 vendors 与表 products 联结, 联结条件是: vendors.vend_id = products.vend_id
SELECT prod_id,vend_name,prod_name,prod_price FROM vendors,products WHERE vendors.vend_id = products.vend_id;

-- 表 vendors 每一行都将于表 products 每一行配对
-- 此时返回的结果为 笛卡尔积, 返回行数目是: count(表A) * count(表B)
SELECT prod_id,vend_name,prod_name,prod_price FROM vendors,products;

根据获取到的结果集的范围将连接进行分类:

  • <u>内连接</u>: 取多个表之间满足条件的数据行(交集)

    隐式的内连接: 不写 INNER JOIN

    显式的内连接: 写 INNER JOIN

  • <u>外连接</u>: 除了取满足条件的交集外, 还会取某一方不满足条件的记录.

    左外连接 LEFT OUTER JOIN

    右外连接 RIGHT OUTER JOIN

    全外连接 FULL OUTER JOIN

    书写时 OUTER 可以忽略

  • <u>交叉连接</u>: 笛卡尔积(cartesian product)(所有集合的所有组合).

    CROSS JOIN

    返回记录的条数是每个表的行数的乘积.

根据连接时的测试条件, 将连接进行分类:

  • <u>等值连接</u>: 连接条件是等号
  • <u>非等值连接</u>: 连接条件是非等号

当自身与自身进行连接时, 称为<u>自连接</u>.

内部联结 INNER JOIN

<u>内部联结</u>即上面的<u>等值联结</u>, 它基于两个表之间的相等测试。

SELECT vendors.vend_id,vend_name,prod_name,prod_price FROM vendors INNER JOIN products ON vendors.vend_id = products.vend_id;

<u>使用哪种语法?</u> ANSI SQL规范首选 INNER JOIN 语法。此外,尽管使用 WHERE 子句定义联结的确比较简单,但是使用明确的联结语法能够确保不会忘记联结条件,有时候这样做也能影响性能。

<u>性能考虑</u> MySQL在运行时关联指定的每个表以处理联结。这种处理可能是非常耗费资源的,因此应该仔细,不要联结不必要的表。联结的表越多,性能下降越厉害。

eg. 返回订购产品 TNT2 的客户列表

-- 子查询方式
select * from customers
where cust_id in (select distinct cust_id 
                  from orders 
                  where order_num in (select order_num
                                      from orderitems
                                      where prod_id="TNT2")
                 );
                 
-- 表联结方式1
SELECT cust_name,cust_contact FROM customers,orders,orderitems WHERE customers.cust_id = orders.cust_id AND orders.order_num = orderitems.order_num AND orderitems.prod_id = 'TNT2';

-- 表联结方式2
SELECT cust_name,cust_contact FROM customers INNER JOIN orders ON customers.cust_id = orders.cust_id INNER JOIN orderitems ON orders.order_num = orderitems.order_num  WHERE orderitems.prod_id = "TNT2";

自联结

Eg. 假如你发现某物品(其ID为 DTNTR )存在问题,因此想知道生产该物品的供应商生产的其他物品是否也存在这些问题。

-- 子查询方式
SELECT prod_id,prod_name 
FROM products 
WHERE vend_id = (
    SELECT vend_id 
    FROM products 
    WHERE prod_id = 'DTNTR'
);

-- 自联结方式
SELECT p1.prod_id,p1.prod_name 
FROM products as p1,products as p2 
WHERE p1.vend_id = p2.vend_id AND p2.prod_id = 'DTNTR';

<u>用自联结而不用子查询</u> 自联结通常作为外部语句用来替代从相同表中检索数据时使用的子查询语句。虽然最终的结果是相同的,但有时候处理联结远比处理子查询快得多。应该试一下两种方法,以确定哪一种的性能更好

外部联结 OUTER JOIN

<u>外部联结</u>: 联结包含了那些在相关表中没有关联行的行。

许多联结将一个表中的行与另一个表中的行相关联。但有时候会需
要包含没有关联行的那些行。例如,可能需要使用联结来完成以下工作:

  • 对每个客户下了多少订单进行计数,包括那些至今尚未下订单的
    客户;
  • 列出所有产品以及订购数量,包括没有人订购的产品;
  • 计算平均销售规模,包括那些至今尚未下订单的客户。
-- 使用 LEFT OUTER JOIN 从 FROM子句的左边表( customers 表)中选择所有行
select c.cust_id,o.order_num 
from customers as c 
left outer join orders as o 
on c.cust_id = o.cust_id;

-- 查看所有客户的订单数量(聚集函数), 包含从没下过单的客户
SELECT c.cust_id,cust_name,count(distinct o.order_num) 
FROM customers as c 
LEFT OUTER JOIN orders as o 
ON c.cust_id=o.cust_id 
GROUP BY c.cust_id;

与内部联结关联两个表中的行不同的是,外部联结还包括没有关联行的行。在使用 OUTER JOIN 语法时,必须使用 RIGHTLEFT 关键字指定包括其所有行的表( RIGHT 指出的是 OUTER JOIN 右边的表,而 LEFT指出的是 OUTER JOIN 左边的表)。

  • LEFT OUTER JOIN 左外联结, 包含坐标的全部记录, 若无对应的右边记录, 则其值为 NULL
  • RIGHT OUTER JOIN
OUTER 关键字可以省略不写.

<u>外部联结的类型</u> 存在两种基本的外部联结形式:左外部联结和右外部联结。它们之间的唯一差别是所关联的表的顺序不同。换句话说,左外部联结可通过颠倒 FROM 或 WHERE 子句中表的顺序转换为右外部联结。因此,两种类型的外部联结可互换使用,而究竟使用哪一种纯粹是根据方便而定。

组合查询 UNION

MySQL也允许执行多个查询(多条 SELECT 语句),并将结果作为单个查询结果集返回。这些组合查询通常称为并(union)或复合查询(compound query)。

<u>组合查询和多个 WHERE 条件</u> 多数情况下,组合相同表的两个查询完成的工作与具有多个 WHERE 子句条件的单条查询完成的工作相同。

-- 返回查询(过滤重复行)
SELECT ... FROM ...
UNION
SELECT ... FROM ...
ORDER BY ...

-- 返回查询(保留所有行)
SELECT ... FROM ...
UNION ALL
SELECT ... FROM ...
ORDER BY ...

对于更复杂的过滤条件,或者从多个表(而不是单个表)中检索数据的情形,使用 UNION 可能会使处理更简单。

UNION规则

  • UNION 必须由两条或两条以上的 SELECT 语句组成,语句之间用关键字 UNION 分隔(因此,如果组合4条 SELECT 语句,将要使用3个UNION 关键字)。
  • UNION 中的每个查询必须包含相同的列、表达式或聚集函数(不过各个列不需要以相同的次序列出)。
  • 列数据类型必须兼容:类型不必完全相同,但必须是DBMS可以隐含地转换的类型(例如,不同的数值类型或不同的日期类型)。
  • 组合查询可以引用于不同的表

特点

  • UNION 默认会去除重复的行, 如果要返回所有匹配行, 则要使用 UNION ALL
  • UNION查询只能使用一条ORDER BY 子句, 只能出现在最后一条 SELECT 语句之后.

全文本搜索

重要说明

  • 在索引全文本数据时,短词被忽略且从索引中排除。短词定义为那些具有3个或3个以下字符的词(如果需要,这个数目可以更改)。
  • MySQL带有一个内建的非用词(stopword)列表,这些词在索引全文本数据时总是被忽略。如果需要,可以覆盖这个列表(请参阅MySQL文档以了解如何完成此工作)。
  • 许多词出现的频率很高,搜索它们没有用处(返回太多的结果)。因此,MySQL规定了一条50%规则,如果一个词出现在50%以上的行中,则将它作为一个非用词忽略。50%规则不用于 IN BOOLEANMODE 。
  • 如果表中的行数少于3行,则全文本搜索不返回结果(因为每个词或者不出现,或者至少出现在50%的行中)。
  • 忽略词中的单引号。例如, don't 索引为 dont 。
  • 不具有词分隔符(包括日语和汉语)的语言不能恰当地返回全文本搜索结果。
  • 如前所述,仅在 MyISAM 数据库引擎中支持全文本搜索。

启用全文本搜索支持 FULLTEXT

MyISAM 引擎支持, InnoDB 引擎不支持.

为了进行全文本搜索,必须索引被搜索的列


CREATE TABLE table_name(
    note_id        int        NOT NULL    AUTO_INCREMENT,
    note_text    text    NULL,
    PRIMARY KEY (note_id),
    FULLTEXT(note_text),    -- 创建全文本索引
) ENGINE=MyISAM;
<u>!!不要在导入数据时使用 FULLTEXT</u>

更新索引要花时间,虽然不是很多,但毕竟要花时间。如果正在导入数据到一个新表,此时不应该启用 FULLTEXT 索引。应该首先导入所有数据,然后再修改表,定义 FULLTEXT 。这样有助于更快地导入数据(而且使索引数据的总时间小于在导入每行时分别进行索引所需的总时间)。

进行全文本搜索 MATCH…AGAINST…

全文本搜索返回的结果默认排序是按照关联程度最高的排在最前面

-- 针对指定的列进行搜索
SELECT * FROM `表名` WHERE Match(`列名`) Against('搜索词');
Match(列名) Against('搜索词') 实际上是计算出一个代表关联程度的数值, 该数值可以在 SELECT 中直接查看.
?? <u>使用完整的 Match() 说明</u> 传递给 Match() 的值必须与FULLTEXT() 定义中的相同。如果指定多个列,则必须列出它们(而且次序正确)。
<u>!!搜索不区分大小写</u> 除非使用 BINARY 方式(本章中没有介绍),否则全文本搜索不区分大小写。

查询扩展 WITH EXPANSION

-- WITH QUERY EXPANSION 使用查询扩展
SELECT note_id,note_text 
FROM productnotes 
WHERE match(note_text) against('anvils' WITH QUERY EXPANSION);    

MySQL对数据和索引进行两遍扫描来完成搜索:

  • 首先,进行一个基本的全文本搜索,找出与搜索条件匹配的所有行;
  • 其次,MySQL检查这些匹配行并选择所有有用的词
  • 再其次,MySQL再次进行全文本搜索,这次不仅使用原来的条件,而且还使用所有有用的词。

利用查询扩展,能找出可能相关的结果,即使它们并不精确包含所查找的词。

<u>行越多越好</u> 表中的行越多(这些行中的文本就越多),使用查询扩展返回的结果越好。

布尔文本搜索

布尔方式(booleanmode)

  • 要匹配的词;
  • 要排斥的词(如果某行包含这个词,则不返回该行,即使它包含
    其他指定的词也是如此);
  • 排列提示(指定某些词比其他词更重要,更重要的词等级更高);
  • 表达式分组;
  • 另外一些内容。
<u>即使没有 FULLTEXT 索引也可以使用</u> 布尔方式不同于迄今为止使用的全文本搜索语法的地方在于,即使没有定义
FULLTEXT 索引,也可以使用它。但这是一种非常缓慢的操作(其性能将随着数据量的增加而降低)。
SELECT note_id,note_text 
FROM productnotes 
WHERE match(note_text) against('heavy -rope*' IN BOOLEAN MODE);

说明:

  • 匹配 heavy
  • 排除 rope 开头的词

1558685103097

插入数据 INSERT INTO

几种使用方式

  • 插入完整的行;
  • 插入行的一部分;
  • 插入多行;
  • 插入某些查询的结果

插入时必须对每个列必须提供一个值.

-- 简单但不安全, 依赖表中列的定义次序
INSERT INTO customer VALUES(NULL,'pep', '100 main', 'los angles', 'CA', '90046', 'USA', NULL, NULL);

-- 指定插入的列, 推荐(但很繁琐)
INSERT INTO customers(cust_name,cust_address,cust_city,cust_state,cust_zip,cust_country,cust_contact,cust_email) VALUES('pep', '100 main', 'los angles', 'CA', '90046', 'USA', NULL, NULL);

-- 插入多行
INSERT INTO `表名`(`列名1`, `列名2`) VALUES("值1", "值2"),("值3", "值4"),("值5", "值6");

-- 插入检索出的数据, 注意避免主键的冲突
INSERT INTO `表1`(`列名1`, `列名2`) SELECT `列名1`, `列名2` FROM `表2`;

<u>插入时省略列需满足以下任一条件</u>:

  • 该列定义为允许 NULL 值(无值或空值)。
  • 在表定义中给出默认值。这表示如果不给出值,将使用默认值。

<u>降低插入优先级</u>INSERT LOW PRIORITY INTO

LOW PRIORITY 同样适用于 UPDATEDELETE 语句

提高 INSERT 的性能 一次插入多条记录可以提高数据库处理的性能,因为MySQL用单条 INSERT 语句处理多个插入比使用多条 INSERT语句快。

<u>INSERT SELECT 中的列名</u> MySQL不关心 SELECT 返回的列名。它使用的是列的位置,因此 SELECT 中的第一列(不管其列名)将用来填充表列中指定的第一个列,第二列将用来填充表列中指定的第二个列,如此等等。这对于从使用不同列名的表中导入数据是非常有用的。

更新和删除数据

好习惯:

  • 除非确实打算更新和删除每一行,否则绝对不要使用不带 WHERE子句的 UPDATE 或 DELETE 语句。
  • 在对 UPDATE 或 DELETE 语句使用 WHERE 子句前,应该先用 SELECT 进行测试,保证它过滤的是正确的记录,以防编写的 WHERE 子句不正确。
  • 使用强制实施引用完整性的数据库,这样MySQL将不允许删除具有与其他表相关联的数据的行。

更新数据 UPDATE

UPDATE `表名`
SET `列1`="值1", `列2`="值2"
WHERE ...;

-- IGNORE, 更新多行时, 忽略错误
UPDATE IGNORE `表名`
SET ...
WHERE ...;

<u>IGNORE 关键字</u> 如果用 UPDATE 语句更新多行,并且在更新这些行中的一行或多行时出一个现错误,则整个 UPDATE 操作被取消(错误发生前更新的所有行被恢复到它们原来的值)。为即使是发生错误,也继续进行更新,可使用 IGNORE 关键字

删除数据 DELETE

DELETE FROM `表名`
WHERE ...;

<u>删除表的内容而不是表</u> DELETE 语句从表中删除行,甚至是删除表中所有行。但是, DELETE 不删除表本身。

<u>更快的删除</u> 如果想从表中删除所有行,不要使用 DELETE 。可使用 TRUNCATE TABLE 语句,它完成相同的工作,但速度更快( TRUNCATE 实际是删除原来的表并重新创建一个表,而不是逐行删除表中的数据)。

创建和操纵表

创建表 CREATE TABLE

-- 示例
CREATE TABLE IF NOT EXISTS `user_accounts`(
    `id` int(100) unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY,
    `password` varchar(64) NOT NULL COMMENT '用户密码',
    `reset_password` tinyint(2) NOT NULL DEFAULT 0 COMMENT '用户类型:0-不需要重置密码;1-需要重置密码',
    `mobile` varchar(20) NOT NULL DEFAULT '' COMMENT '手机',
    `create_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `update_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    -- 创建唯一索引, 不允许重复
    UNIQUE KEY idx_user_mobile(`mobile`)    -- 索引名可忽略: UNIQUE INDEX (`mobile`)
        
    -- 创建外键
    -- FOREIGN KEY (`dept_id`) REFERENCES `depts`(`id`) ON DELETE cascade
        
    -- PRIMARY KEY (`id`)
    -- PRIMARY KEY (`key1`,`key2`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

主键值 必须唯一。表中的每个行必须具有唯一的主键值。如果主键使用单个列,则它的值必须唯一。如果使用多个列,则这些列的组合值必须唯一。

eg. 多个列的组合作为主键

CREATE TABLE IF NOT EXISTS orderitems
(
    order_num int NOT NULL,
    order_item int NOT NULL,
    prod_id char(10) NOT NULL,
    quantity int NOT NULL,
    item_price decimal(8,2) NOT NULL,
    PRIMARY KEY(order_num, order_item)
) ENGINE=InnoDB;

orderitems 表包含orders表中每个订单的细节。每个订单有多项物品,但每个订单任何时候都只有1个第一项物品,1个第二项物品,如此等等。因此,订单号( order_num 列)和订单物品( order_item 列)的组
合是唯一的,从而适合作为主键

NULL值就是没有值或缺值。允许 NULL 值的列也允许在插入行时不给出该列的值。不允许 NULL 值的列不接受该列没有值的行,

<u>理解 NULL</u> 不要把 NULL 值与空串相混淆。 NULL 值是没有值,它不是空串。如果指定 '' (两个单引号,其间没有字符),这在 NOT NULL 列中是允许的。空串是一个有效的值,它不是无值。 NULL 值用关键字 NULL 而不是空串指定。

主键和 NULL 值 主键为其值唯一标识表中每个行的列。<u>主键中只能使用不允许 NULL 值的列</u>。允许 NULL 值的
列不能作为唯一标识。

AUTO_INCREMENT

  • 每个表只允许一个 AUTO_INCREMENT 列,而且它必须被索引(如,通过使它成为主键)
  • 在 INSERT 语句中指定一个值,只要它是唯一的(至今尚未使用过)即可,该值将被用来替代自动生成的值。后续的增量将开始使用该手工插入的值。
  • last_insert_id() 函数返回最后一个 AUTO_INCREMENT 值.

    eg. 增加一个新订单

    1. orders 表中创建一行
    2. 使用 last_insert_id() 获取自动生成的 order_num
    3. 在 orderitms 表中对订购的每项物品创建一行。 order_num 在 orderitems 表中与订单细节一起存储。

DEFAULT

  • 使用当前时间作为默认值

    CREATE TABLE `表名`(
        ...,
        `create_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
    );
  • 许多数据库开发人员使用默认值而不是 NULL 列,特别是对用于计算或数据分组的列更是如此。

ENGINE

  • InnoDB 是一个可靠的事务处理引擎,它不支持全文本搜索;
  • MEMORY 在功能等同于 MyISAM ,但由于数据存储在内存(不是磁盘)中,速度很快(特别适合于临时表);
  • MyISAM 是一个性能极高的引擎,它支持全文本搜索(参见第18章),但不支持事务处理。

<u>外键不能跨引擎</u> 混用引擎类型有一个大缺陷。外键(用于强制实施引用完整性,如第1章所述)不能跨引擎,即使用一个引擎的表不能引用具有使用不同引擎的表的外键。

更新表 ALTER TABLE

列和外键的操作

-- 新增列
ALTER TABLE `表名` ADD COLUMN `列名` 列属性;

-- 删除列
ALTER TABLE `表名` DROP COLUMN `列名`;

-- 修改列(属性替换)
-- CHANGE 可以重命名列名, MODIFY 不能
ALTER TABLE `表名` CHANGE COLUMN `旧列名` `新列名` 列属性;
ALTER TABLE `表名` MODIFY `列名` 列属性;
    

-- 删除表
DROP TABLE `表名`;

-- 重命名表
RENAME TABLE `表名1` TO `表名2`;

复杂的表结构更改一般需要手动删除过程

用新的列布局创建一个新表;

  • 使用 INSERT SELECT 语句从旧表复制数据到新表。如果有必要,可使用转换函数和计算字段;
  • 检验包含所需数据的新表;
  • 重命名旧表(如果确定,可以删除它);
  • 用旧表原来的名字重命名新表;
  • 根据需要,重新创建触发器、存储过程、索引和外键。

<u>小心使用 ALTER TABLE</u> 使用 ALTER TABLE 要极为小心,应该在进行改动前做一个完整的备份(模式和数据的备份)。数据库表的更改不能撤销,如果增加了不需要的列,可能不能删除它们。类似地,如果删除了不应该删除的列,可能会丢失该列中的所有数据。

约束, 索引

-- 删除外键
-- 约束名可以用 show create table `表名` 语句来查看
ALTER TABLE `表名` DROP FOREIGN KEY `约束名`;    

-- 查看索引
SHOW INDEX FROM `表名`;
SHOW KEY FROM `表名`;

-- 创建普通索引(省略索引名)
ALTER TABLE `表名` ADD INDEX (`列名`);
ALTER TABLE `表名` ADD UNIQUE KEY(`列名`);
ALTER TABLE `表名` ADD PRIMARY KEY(`列名`);
ALTER TABLE `表名` ADD FOREIGN KEY(`列名`) REFERENCES `关联表名`(`关联列名`);
ALTER TABLE `表1` ADD CONSTRAINT `约束名` FOREIGN KEY (`外键`) REFERENCES `表2` (`表2的键`);

-- CREATE INDEX 只可对表增加普通索引或UNIQUE索引
CREATE INDEX `索引名` ON `表名` (`列名`);
CREATE UNIQUE INDEX `索引名` ON `表名` (`列名`);

-- 删除索引
ALTER TABLE `表名` DROP PRIMARY KEY;
ALTER TABLE `表名` DROP INDEX `索引名`;
ALTER TABLE `表名` DROP FOREIGN KEY `约束名`;

DROP INDEX `索引名` ON `表名`;

索引

2019年5月29日17:18:22 开始补充

种类:

  • INDEX 普通索引:仅加速查询
  • UNIQUE KEY 唯一索引:加速查询 + 列值唯一(可以有null)
  • PRIMARY KEY 主键索引:加速查询 + 列值唯一 + 表中只有一个(不可以有null)
  • INDEX 组合索引:多列值组成一个索引,专门用于组合搜索,其效率大于索引合并
  • FULLTEXT 全文索引:对文本的内容进行分词,进行搜索

<u>术语</u>

  • 索引合并:使用多个单列索引组合查询搜索
  • 覆盖索引:select的数据列只用从索引中就能够取得,不必读取数据行,换句话说查询列要被所建的索引覆盖

组合索引

  • 同时搜索多个条件时,组合索引的性能效率好过于多个单一索引合并

SQL Explain

备注: 此处将仅作为存档, 后续更新将在 这里 中处理. 2019年10月18日15:53:53

1559124811057

EXPLAIN 结果字段分析

  • select_type

    查询类型说明
    SIMPLE简单查询
    不包含UNION查询或子查询
    PRIMARY最外层查询
    SUBQUERY子查询中的第一个 SELECT
    DEPENDENT SUBQUERY!!! 子查询, 但依赖于外层查询的结果
    注意确认, 避免大表驱动小表
    DERIVED子查询
    UNION联合
    UNION RESULT使用联合的结果
  • table

    访问的表名

  • partitions

    匹配的分区

  • type

    查询时的访问方式, 性能:all < index < range < index_merge < ref_or_null < ref < eq_ref < system/const

    访问方式说明
    ALL全表扫描,对于数据表从头到尾找一遍
    select * from tb1;
    特别的:如果有limit限制,则找到之后就不在继续向下扫描
    select * from tb1 where email = 'seven@live.com'
    select * from tb1 where email = 'seven@live.com' limit 1;
    虽然上述两个语句都会进行全表扫描,第二句使用了limit,则找到一个后就不再继续扫描。
    INDEX全索引扫描,对索引从头到尾找一遍
    select nid from tb1;
    RANGE对索引列进行范围查找
    INDEX_MERGE合并索引,使用多个单列索引搜索
    REF使用索引快速定位(根据索引查找一个或多个值)
    EQ_REF通常出现在多表的join查询, 连接时使用primary key 或 unique 索引(都只能匹配到一行记录)
    CONST通过主键或唯一索引精确查找到一行
    常量
    表最多有一个匹配行(主键或唯一索引),因为仅有一行,在这行的列值可被优化器剩余部分认为是常数,const表很快,因为它们只读取一次
    SYSTEM系统
    表仅有一行(=系统表)。这是const联接类型的一个特例。
  • possible_keys

    表示查询时,可能使用的索引

  • key

    实际使用的索引

  • key_len

    使用索引字段长度, 该字段可以评估组合索引是否完全被使用或仅仅是最左前缀被用到.

    计算规则

    • 字符串

      • char(n): n 字节长度
      • varchar(n): 如果是 utf8 编码, 则是 3 n + 2字节; 如果是 utf8mb4 编码, 则是 4 n + 2 字节.
    • 数值类型:

      • TINYINT: 1字节
      • SMALLINT: 2字节
      • MEDIUMINT: 3字节
      • INT: 4字节
      • BIGINT: 8字节
    • 时间类型

      • DATE: 3字节
      • TIMESTAMP: 4字节
      • DATETIME: 8字节
    • 字段属性: NULL 属性 占用一个字节. 如果一个字段是 NOT NULL 的, 则没有此属性.
  • ref

    列与索引的比较,表示上述表的连接匹配条件,即哪些列或常量被用于查找索引列上的值

  • row

    估算的需要扫描的行数

  • filtered
  • Extra

    该列包含MySQL解决查询的详细信息

    说明
    Using filesortmysql对结果排序进行额外排序.
    mysql有两种文件排序算法,这两种排序方式都可以在内存或者磁盘上完成
    explain不会告诉你mysql将使用哪一种文件排序
    也不会告诉你排序会在内存里还是磁盘上完成。
    Using index使用覆盖索引,以避免访问表。不要把覆盖索引和index访问类型弄混了。
    Using index condition索引下推优化, 5.6新增特性
    Using temporary意味着mysql在对查询结果排序时会使用一个临时表
    Using where这意味着mysql服务器将在存储引擎检索行后再进行过滤
    许多where条件里涉及索引中的列,当(并且如果)它读取索引时,就能被存储引擎检验
    因此不是所有带where子句的查询都会显示“Using where”。
    有时“Using where”的出现就是一个暗示:查询可受益于不同的索引。
    Range checked for each record(index map: N)这个意味着没有好用的索引,新的索引将在联接的每一行上重新估算,N是显示在possible_keys列中索引的位图,并且是冗余的。

Profile

备注: 此处将仅作为存档, 后续更新将在 这里 中处理. 2019年10月18日15:53:53

show profile 分析SQL性能工具(检测数据存在临时表中)

  • 开启profile SET profiling=1;
  • 发送sql
  • 查看profile的资源开销结果 show profiles, show profile, show profile for query <id>
  • 关闭profile

视图

视图是虚拟的表。与包含数据的表不一样,视图只包含使用时动态检索数据的查询。

视图包含的是一个SQL查询, 它不包含数据!!

个人理解: 视图即别名~

-- 创建视图
CREATE VIEW `视图名` AS SELECT ...

-- 查看创建指定视图的语句
SHOW CREATE VIEW `视图名`;

-- 删除视图
DROP VIEW `视图名`;

-- 更新视图
CREATE OR REPLACE VIEW AS SELECT ...

视图的作用:

  • 简化数据处理
  • 重新格式化基础数据
  • 保护基础数据

为什么使用视图:

  • 重用SQL语句。
  • 简化复杂的SQL操作。在编写查询后,可以方便地重用它而不必知道它的基本查询细节。
  • 使用表的组成部分而不是整个表。
  • 保护数据。可以给用户授予表的特定部分的访问权限而不是整个表的访问权限。
  • 更改数据格式和表示。视图可返回与底层表的表示和格式不同的数据。

视图的规则和限制

  • 与表一样,视图必须唯一命名(不能给视图取与别的视图或表相同的名字)。
  • 对于可以创建的视图数目没有限制。
  • 为了创建视图,必须具有足够的访问权限。这些限制通常由数据库管理人员授予。
  • 视图可以嵌套,即可以利用从其他视图中检索数据的查询来构造一个视图。
  • ORDER BY 可以用在视图中,但如果从该视图检索数据 SELECT 中也含有 ORDER BY ,那么该视图中的 ORDER BY 将被覆盖。
  • 视图不能索引,也不能有关联的触发器或默认值。
  • 视图可以和表一起使用。例如,编写一条联结表和视图的 SELECT语句。

<u>创建可重用的视图</u>

创建不受特定数据限制的视图是一种好办法。例如,上面创建的视图返回生产所有产品的客户而不仅仅是 生产TNT2 的客户。扩展视图的范围不仅使得它能被重用,而且甚至更有用。这样做不需要创建和维护多个类似视图。

<u>将视图用于检索</u>

一般,应该将视图用于检索( SELECT 语句)而不用于更新( INSERT 、 UPDATE 和 DELETE )。

存储过程

存储过程

简单来说: 存储过程是为以后的使用而保存的一条或多条MySQL语句的集合.

使用存储过程的原因:

  • 封装复杂操作, 统一调用
  • 提高性能

    使用存储过程比使用单独的SQL语句要快
  • 存在一些只能用在单个请求中的MySQL元素和特性,存储过程可以使用它们来编写功能更强更灵活的代码

存储过程一般并不显示结果, 而是把结果返回给你指定的变量.

-- 调用存储过程
CALL `过程名`()

-- 更改mysql命令行客户端语句分隔符, 除了 \ 符号外,其他字符都可以作为语句分隔符.
DELIMITER //

-- 创建存储过程
CREATE PROCEDURE `过程名`()
BEGIN

END//

-- 还原mysql命令行客户端语句分隔符
DELIMITER ;

-- 删除存储过程
DROP PROCEDURE IF EXISTS `过程名`;

-- 检查(查看)存储过程
SHOW CREATE PROCEDURE `过程名`;

-- 查看存储过程的元数据(创建时间, 创建人..)
SHOW PROCEDURE STATUS LIKE '过程名';

MySQL支持存储过程的参数:

  • IN
  • OUT
  • INOUT

<u>参数的数据类型</u>

存储过程的参数允许的数据类型与表中使用的数据类型相同。

!!! 记录集不是允许的类型,因此,不能通过一个参数返回多个行和列。

一个简单的示例: 计算商品的最低、最高、平均价格

DELIMITER //

CREATE PROCEDURE productpricing(
    OUT pl DECIMAL(8,2),
    OUT ph DECIMAL(8,2),
    OUT pm DECIMAL(8,2)
) 
BEGIN
    SELECT Min(prod_price) INTO pl FROM products;
    SELECT Max(prod_price) INTO ph FROM products;
    SELECT Avg(prod_price) INTO pm FROM products;
END//

DELIMITER ;

CALL productpricing(@pricelow, @pricehigh, @priceaverage);
SELECT @pricelow, @pricehigh, @priceaverage;

另一个简单示例: 接受订单号并返回该订单的统计

DELIMITER //

CREATE PROCEDURE ordertotal(IN onumber INT, OUT ototal DECIMAL(6,2))
BEGIN
    SELECT Sum(item_price*quantity) 
    FROM orderitems 
    WHERE order_num = onumber 
    INTO ototal;
END//

DELIMITER ;

CALL ordertotal(20005, @total);

SELECT @total;

变量

<u>变量(variable)</u>

内存中一个特定的位置,用来临时存储数据。

变量名不区分大小写

用户变量

@变量名, 仅对当前连接有效

可以使用 SET @变量=值SELECT @变量:=值; 来赋值给变量

-- 
SET @变量=值;

-- 在SELECT中, = 是比较符, 因此需要用 :=
SELECT @变量:=值;

系统变量

全局变量

对当前mysqld实例有效

SET GLOBAL 变量名=值;

SET @@变量名=值;

需要 SUPER 权限, 需重新连接后才生效.

会话变量

只对当前连接有效

-- 设置变量值
SET SESSION 变量名=值;

-- LOCAL 是SESSION的同义词
SET LOCAL 变量名=值;

-- 不指定 GLOBAL,SESSION 时, 默认就是 SESSION
-- 此处没有 @
SET 变量名=值;


-- 若存在会话变量则返回, 否则返回全局变量.
SELECT @@变量名;

SHOW VARIABLES LIKE '变量名';

局部变量

declare定义, 仅在当前块有效(begin...end)

语法

条件语句

IF ... THEN
    
ELSEIF ... THEN
    
ELSE

END IF;    

循环语句

-- WHILE 循环
WHILE ... DO

END WHILE;


-- ---------------------------------------------------
-- REPEAT 循环
REPEAT

UNTIL ...
END REPEAT;


-- ---------------------------------------------------
-- LOOP 循环
loop标记: LOOP
    
    IF ... THEN
        LEAVE loop标记;
    END IF;
END LOOP;

-- LOOP 示例
CREATE PROCEDURE proc_loop ()
BEGIN    
    declare i int default 0;
    loop_label: loop
        select i;
        set i=i+1;
        if i>=5 then
            leave loop_label;
            end if;
    end loop;

END

动态执行SQL语句

PREPARE 变量 FROM "...";
EXECUTE 变量 USING @p1;
DEALLOCATE PREPARE 变量;

-- 示例
SET @num = 20005;
PREPARE stmt FROM 'SELECT * FROM orders WHERE order_num = ?';
EXECUTE stmt USING @num;
DEALLOCATE PREPARE stmt;
参数只能使用<u>用户变量</u>来传递

智能存储过程

在存储过程中包含:

  • 业务规则
  • 智能处理
-- Name: ordertotal
-- Parameters: onumber = order number
--                         taxable = 0 if not taxable, 1 if taxable
--            ototal = order total VARIABLES
CREATE PROCEDURE ordertotal(
    IN onumber INT,
    IN taxable BOOLEAN,
    OUT ototal DECIMAL(8,2)
) COMMENT '获取订单总额, 可选增加营业税'
BEGIN
-- Declare variable for total
DECLARE total DECIMAL(8,2);
-- Declare tax percentage
DECLARE taxrate INT DEFAULT 6;

-- Get the order total
SELECT sum(item_price*quantity) FROM orderitems WHERE order_num = onumber INTO total;

-- Is this taxable?
IF taxable THEN
    -- yes, so add taxrate to the total
    SELECT total+(total/100*taxrate) INTO total;
END IF;

-- And finally, save to out vaiable
SELECT total INTO ototal;
END;
  • DECLARE 定义了两个局部变量, 支持可选的默认值

游标

游标(cursor)是一个存储在MySQL服务器上的数据库查询,它不是一条 SELECT 语句,而是被该语句检索出来的结果集。在存储了游标之后,应用程序可以根据需要滚动或浏览其中的数据。

<u>只能用于存储过程</u> 不像多数DBMS,MySQL游标只能用于存储过程(和函数)。

使用游标的步骤:

  • 声明游标(仅定义查询语句)
  • 打开游标(执行查询)
  • 使用游标检索数据(遍历)
  • 关闭游标

    • 游标关闭后必须重新打开才能再次使用
    • 存储过程结束时会自动将已打开的游标关闭
CREATE PROCEDURE `processorders`()
BEGIN
    -- create cursor
    DECLARE ordernumbers CURSOR
    FOR
    SELECT order_num FROM orders;

    -- open cursor
    OPEN ordernumbers;

    -- close cursor
    CLOSE ordernumbers;
END

书上示例

DROP PROCEDURE processorders;

CREATE PROCEDURE processorders()
BEGIN
    DECLARE done BOOLEAN DEFAULT 0;
    DECLARE o INT;
    DECLARE t DECIMAL(8,2);

    -- Declare CURSOR
    DECLARE ordernumbers CURSOR
    FOR
    SELECT order_num FROM orders;

    -- Declare continue handler
    -- FOR NOT FOUND
    DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=1;

    -- Create a table to store the results
    CREATE TABLE IF NOT EXISTS ordertotals(
        order_num INT,
        total DECIMAL(8,2)
    );

    OPEN ordernumbers;

    REPEAT
        -- Get order number
        FETCH ordernumbers INTO o;
        
        CALL ordertotal(o,1,t);

        INSERT INTO ordertotals(order_num,total) VALUES(o,t);
    UNTIL done 
    END REPEAT;

    -- Close the cursor
    CLOSE ordernumbers;
END

改进示例

DROP PROCEDURE processorders;

CREATE PROCEDURE processorders()
BEGIN
    DECLARE done BOOLEAN DEFAULT 0;
    DECLARE o INT;
    DECLARE t DECIMAL(8,2);

    -- Declare CURSOR
    DECLARE ordernumbers CURSOR
    FOR
    SELECT order_num FROM orders;

    -- Declare continue handler
    -- FOR NOT FOUND
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;

    -- Create a table to store the results
    CREATE TABLE IF NOT EXISTS ordertotals(
        order_num INT,
        total DECIMAL(8,2)
    );

    OPEN ordernumbers;

    FETCH ordernumbers INTO o;
    -- 避免插入 (NULL,NULL) 到 ordertotals 表
    WHILE NOT done DO        
        CALL ordertotal(o,1,t);

        SELECT o,t;

        INSERT INTO ordertotals(order_num,total) VALUES(o,t);

        FETCH ordernumbers INTO o;
    END WHILE;

    -- Close the cursor
    CLOSE ordernumbers;
END

触发器

触发器是MySQL响应以下任意语句而自动执行的一条MySQL语句(或位于 BEGIN 和 END 语句之间的一组语
句):

  • DELETE ;
  • INSERT ;
  • UPDATE
创建触发器可能需要特殊的安全访问权限,但是,触发器的执行是自动的。如果 INSERT 、 UPDATE 或 DELETE 语句能够执行,则相关的触发器也能执行。
  • 应该用触发器来保证数据的一致性(大小写、格式等)。在触发器中执行这种类型的处理的优点是它总是进行这种处理,而且是透明地进行,与客户机应用无关。
  • 触发器的一种非常有意义的使用是创建审计跟踪。使用触发器,把更改(如果需要,甚至还有之前和之后的状态)记录到另一个表非常容易。
  • 遗憾的是,MySQL触发器中不支持 CALL 语句。这表示不能从触发器内调用存储过程。所需的存储过程代码需要复制到触发器内。

创建触发器

在创建触发器时,需要给出4条信息:

  • 唯一的触发器名;
  • 触发器关联的表;
  • 触发器应该响应的活动( DELETE 、 INSERT 或 UPDATE );
  • 触发器何时执行(处理之前或之后)。

触发器按每个表每个事件每次地定义,每个表每个事件每次只允许一个触发器。因此,每个表最多支持6个触发器(每条 INSERT 、 UPDATE和 DELETE 的之前和之后)。单一触发器不能与多个事件或多个表关联,所以,如果你需要一个对 INSERT 和 UPDATE 操作执行的触发器,则应该定义两个触发器。

<u>仅支持表</u> 只有表才支持触发器,视图不支持(临时表也不支持)

-- 创建触发器
CREATE TRIGGER `触发器名` 
AFTER|BEFORE 
INSERT|UPDATE|DELETE 
ON `表名`
FOR EACH ROW
...

-- 删除触发器
DROP TRIGGER `触发器名`;

<u>BEFORE 或 AFTER ?</u> 通常,将 BEFORE 用于数据验证和净化(目的是保证插入表中的数据确实是需要的数据)。本提示也适用于 UPDATE 触发器。

INSERT 触发器

  • 在 INSERT 触发器代码内,可引用一个名为 NEW 的虚拟表,访问被插入的行;
  • 在 BEFORE INSERT 触发器中, NEW 中的值也可以被更新(允许更改被插入的值);
  • 对于 AUTO_INCREMENT 列, NEW 在 INSERT 执行之前包含 0 ,在 INSERT执行之后包含新的自动生成值。
-- mysql中无法执行: 禁止触发器返回结果集
-- ERROR 1415 (0A000): Not allowed to return a result set from a trigger
CREATE TRIGGER neworder AFTER INSERT ON orders
FOR EACH ROW
SELECT NEW.order_num;

DELETE 触发器

  • 在 DELETE 触发器代码内,你可以引用一个名为 OLD 的虚拟表,访问被删除的行;
  • OLD 中的值全都是只读的,不能更新。

在任意订单被删除前将执行此触发器。它使用一条 INSERT 语句将 OLD 中的值(要被删除的订单)保存到一个名为 archive_orders 的存档表中

CREATE TRIGGER deleteorders BEFORE DELETE ON orders
FOR EACH ROW
BEGIN
    INSERT INTO archive_orders(order_num,order_date,cust_id)
    VALUES(OLD.order_num,OLD.order_date,OLD.cust_id);
END

使用 BEFORE DELETE 触发器的优点(相对于 AFTER DELETE 触发器来说)为,如果由于某种原因,订单不能存档, DELETE 本身将被放弃。

UPDATE 触发器

  • 在 UPDATE 触发器代码中,你可以引用一个名为 OLD 的虚拟表访问以前( UPDATE 语句前)的值,引用一个名为 NEW 的虚拟表访问新更新的值;
  • 在 BEFORE UPDATE 触发器中, NEW 中的值可能也被更新(允许更改将要用于 UPDATE 语句中的值);
  • OLD 中的值全都是只读的,不能更新。
CREATE TRIGGER updatevendor
BEFORE UPDATE ON vendors 
FOR EACH ROW 
SET NEW.vend_state=Upper(NEW.vend_state);

事务

事务存在的问题:

  • 脏读
  • 不可重复读
  • 幻读

参考: https://www.cnblogs.com/balfi...

数据库事务隔离级别:

  1. read-uncommited 读未提交

    存在所有问题, 最低的隔离级别。一个事务可以读取另一个事务并未提交的更新结果。

  2. read-commited 读已提交

    解决"脏读", 大部分数据库采用的默认隔离级别。一个事务的更新操作结果只有在该事务提交之后,另一个事务才可以的读取到同一笔数据更新后的结果。

  3. repeatable-read 可重复读

    解决"不可重复读", 整个事务过程中,对同一笔数据的读取结果是相同的,不管其他事务是否在对共享数据进行更新,也不管更新提交与否。

  4. serializable 序列化

    解决"幻读", 最高隔离级别。所有事务操作依次顺序执行。注意这会导致并发度下降,性能最差。通常会用其他并发级别加上相应的并发锁机制来取代它。

  • 不可重复读
  • 可重复读
  • 幻读

MySQL 默认级别是 repeatable-read


术语

  • 事务( transaction )指一组SQL语句;
  • 回退( rollback )指撤销指定SQL语句的过程;
  • 提交( commit )指将未存储的SQL语句结果写入数据库表;
  • 保留点( savepoint )指事务处理中设置的临时占位符(place-holder),你可以对它发布回退(与回退整个事务处理不同)。
-- 标识事务开始
START TRANSACTION;

-- ROLLBACK;
-- COMMIT;

<u>哪些语句可以回退?</u> 事务处理用来管理 INSERT 、 UPDATE 和DELETE 语句。你不能回退 SELECT 语句。(这样做也没有什么意义。)你不能回退 CREATE 或 DROP 操作。事务处理块中可以使用这两条语句,但如果你执行回退,它们不会被撤销。


-- 关闭本次连接的mysql自动提交
SET autocommit=0;

保留点 SavePoint

为了支持回退部分事务处理,必须能在事务处理块中合适的位置放置占位符。这样,如果需要回退,可以回退到某个占位符。

-- 创建保留点
SAVEPOINT `保留点名`;

-- 回退到指定保留点
ROLLBACK TO `保留点名`;

字符集

术语

  • 字符集为字母和符号的集合;
  • 编码为某个字符集成员的内部表示;
  • 校对为规定字符如何比较的指令。
-- 查看可用的字符集
SHOW CHARACTER SET;
SHOW CHARSET;

-- 查看可用的校对
SHOW COLLATION;

-- 查看当前使用的字符集
SHOW VARIABLES LIKE 'character%';
SHOW VARIABLES LIKE 'collation%';

通常系统管理在安装时定义一个默认的字符集和校对。此外,也可以在创建数据库时,指定默认的字符集和校对。

实际上,字符集很少是服务器范围(甚至数据库范围)的设置。不同的表,甚至不同的列都可能需要不同的字符集,而且两者都可以在创建表时指定。

CREATE TABLE mytable(
    column1 INT, 
    column2 VARCHAR(10),
    
    -- 指定特定列使用特定的字符集及校对
    column3 VARCHAR(10) CHARSET latin1 COLLATE latin1_general_ci
) Engine=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;


-- 临时区分大小写排序
SELECT * FROM mytable
ORDER BY column3 COLLATE latin1_general_cs;

<u>SELECT 的其他 COLLATE 子句</u> 除了这里看到的在 ORDER BY子句 中使用以外, COLLATE 还可以用于 GROUP BY 、 HAVING 、聚集函数、别名等。

串可以在字符集之间进行转换。为此,使用 Cast() 或 Convert ()函数

安全管理

用户应该对他们需要的数据具有适当的访问权,既不能多也不能少

<u>不要使用 root</u> 应该严肃对待 root 登录的使用。仅在绝对需要时使用它(或许在你不能登录其他管理账号时使用)。不应该在日常的MySQL操作中使用 root 。

  • MySQL用户账号和信息存储在名为 mysql 的MySQL数据库中。
  • mysql 数据库有一个名为 user 的表,它包含所有用户账号。
-- 查看当前所有用户
SELECT host,user FROM mysql.user;

-- 创建用户
CREATE USER 用户名 IDENTIFIED BY '密码';

-- 重命名用户账号
RENAME USER 旧用户名 TO 新用户名;

-- 删除用户账号
DROP USER 用户名;

-- 修改用户名
RENAME USER '旧用户名@..' TO '新用户名@..';

-- 更改自己口令
SET PASSWORD = Password('密码');

-- 更改指定用户口令
SET PASSWORD FOR 用户名 = Password('密码');

ALTER USER 用户名 IDENTIFIED BY '密码';

mysqladmin

-- 设置密码(若修改密码, 则需输入原密码)
mysqladmin -u root password

设置访问权限

-- 查看赋予当前用户账号的权限
SHOW GRANTS;

-- 查看赋予某个用户账号的权限
-- 完整的用户定义: user@host
-- 不指定主机名时使用默认的主机名 %
-- 默认查询: 用户名@'%'
SHOW GRANTS FOR 用户名;

-- 创建账号并赋予最高权限
GRANT ALL ON *.* TO '用户名'@'%' IDENTIFIED BY '密码' WITH GRANT OPTION;

-- 赋予 SELECT 权限
GRANT SELECT ON `数据库名`.* TO 用户名;

-- 撤销 SELECT 权限
REVOKE SELECT ON `数据库名`.* FROM 用户名;

新用户的权限为: GRANT USAGE ON *.* TO 'yjx'@'%', 即没有任何权限.

常用权限

权限说明
ALL除GRANT OPTION外的所有权限
SELECT仅查询
SELECT,INSERT查询和插入
USAGE无权限

目标

目标说明
数据库名.*指定数据库所有
数据库名.表指定数据库的指定表
数据库名.存储过程指定数据库的指定存储过程
.所有数据库

用户

用户说明
用户名@ip指定ip登陆的用户
用户名@'192.168.1.%'指定ip段登陆的用户
用户名@'%'任意ip下登陆的用户
用户名@localhost本地登陆的用户
用户名@‘192.168.200.0/255.255.255.0’(子网掩码配置)

GRANT 和 REVOKE 可在几个层次上控制访问权限:

  • 整个服务器,使用 GRANT ALL 和 REVOKE ALL;
  • 整个数据库,使用 ON database.*;
  • 特定的表,使用 ON database.table;
  • 特定的列;
  • 特定的存储过程。

1559027891075

数据库维护

数据库备份

  • msyqldump 程序
  • BACKUP TABLESELECT INTO OUTFILE 语句转储数据到外部文件, 使用 RESTORE TABLE 还原

    select * from '表' into outfile '/path/to/file.sql';

mysqldump 备份

# 到处某个库的某些表
mysqldump -uroot -p '数据库名' '表名'...

# 导出为文本文件
mysqldump -uroot -p -B '数据库名1' '数据库名2' > /tmp/mysql.bak

# 直接导出为压缩文件
mysqldump -uroot -p -B '数据库名1' '数据库名2' | gzip > /tmp/mysql.bak.gz

# -A, --all-databases    备份所有库
# -B, --database        备份指定库
# -F                    刷新binlog日志
# -x,--lock-all-tables
# -l,--locktables
# --single-transaction  适合innodb事务数据库备份
# --default-character-set=utf8 字符集
# --triggers            备份触发器

# -d, --no-data            只备份表结构
# -t, --no-create-info  只备份数据, 无 create table 语句
# --master-data         增加binlog日志文件名及对应的位置点



# 生产环境全备份
# 进行数据库全备,(生产环境还通过定时任务每日凌晨执行)
mysqldump -uroot -p123456 -S /data/3306/mysql.sock --single-transaction -F -B "数据库名" | gzip > /server/backup/mysql_$(date +%F).sql.gz
# innodb引擎备份
mysqldump -u$MYUSER -p$MYPASS -S $MYSOCK -F --single-transaction -A | gzip > $DATA_FILE
# myisam引擎备份
mysqldump -u$MYUSER -p$MYPASS -S $MYSOCK -F -A -B --lock-all-tables |gzip >$DATA_FILE

恢复

# 直接从sql文件恢复
mysql -uroot -p < /tmp/mysql.sql

# 从压缩文件中恢复
gunzip < /tmp/mysql.bak.gz | mysql -uroot -p

# 若备份文件未指定库, 则此处需要自行指定要恢复到哪个库
mysql -uroot -p <数据库名> < /tmp/mysql.sql

复制表

-- 此时要求 `目标表` 这个表不能存在, 同时该语句会自动创建 `目标表` 这个表.
select * into `目标表` from `原始表`;

-- mysql 不支持上述的 select into 语法, 因此可以用以下语句来处理
-- 但是这种方式仅仅会创建表基本结构一致, 其他的索引之类的一概没有.
create table `目标表` (select * from `原始表`)

-- mysql 可以用这种方式来处理
create table `目标表` like `原始表`;
insert into `目标表` select * from `原始表`;

改善性能

  • SHOW PROCESSLIST 显示所有活动进程(以及它们的线程ID和执行时间)。你还可以用 KILL 命令终结某个特定的进程(使用这个命令需要作为管理员登录)。
  • 使用 EXPLAIN 语句让MySQL解释它将如何执行一条 SELECT 语句。
  • 在导入数据时,应该关闭自动提交。你可能还想删除索引(包括FULLTEXT 索引),然后在导入完成后再重建它们。
  • 你的 SELECT 语句中有一系列复杂的 OR 条件吗?通过使用多条SELECT 语句和连接它们的 UNION 语句,你能看到极大的性能改进。
  • 索引改善数据检索的性能,但损害数据插入、删除和更新的性能。如果你有一些表,它们收集数据且不经常被搜索,则在有必要之前不要索引它们。(索引可根据需要添加和删除。)
  • LIKE 很慢。一般来说,最好是使用 FULLTEXT 而不是 LIKE 。

加快数据库恢复速度

在恢复数据时,可能会导入大量的数据。

此时有一些技巧可以提高导入速度:

  • 导入时禁用索引, 导入结束后再开启索引

    ALTER TABLE 表名 disable keys;
    
    ALTER TABLE 表名 enable keys;
  • 对于InnoDB, 由于默认 autocommit=1 , 即每条语句作为单独的事务, 因此可以将多条合并成少数几条事务以加快速度.

    -- 关闭自动提交, 也可以用 begin 或 start transaction
    set autocommit = 0;
    
    -- 插入若干条提交
    insert into ...;
    ...
    insert into ...;
    commit;
    
    -- 插入若干条提交
    insert into ...;
    ...
    insert into ...;
    commit;
    
    set autocommit = 1;

查看表锁情况

mysql> show open tables where in_use > 0;

+----------+-------+--------+-------------+
| Database | Table | In_use | Name_locked |
+----------+-------+--------+-------------+
| test_yjx | user  |      1 |           0 |
+----------+-------+--------+-------------+

慢查询分析

备注: 此处将仅作为存档, 后续更新将在 这里 中处理. 2019年10月18日15:53:53

开启慢查询

[mysqld]
# ...此处省略了其他与慢查询日志不相关的配置

############### 慢查询日志 ################
slow_query_log=1    # 打开慢查询日志
log_output=file        # 日志记录位置
slow_query_log_file=/var/run/mysqld/mysqld-slow.log    # 慢查询日志记录文件
long_query_time=3    # 慢查询时间阀值
也可以在mysql shell中修改相关 global 变量来开启(重启后失效)

当前会话中临时启用慢日志分析

set global slow_query_log = 1;
set long_query_time = 0;
测试慢查询是否有效
mysql> select sleep(4);

此时日志或多出一条:

# Time: 190215 15:16:14
# User@Host: root[root] @ localhost []
# Query_time: 11.000205  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 0
SET timestamp=1550214974;
select sleep(11);

慢查询分析 mysqldumpslow

# 分析慢日志
mysqldumpslow -a -n 50 -s c /var/run/mysqld/mysqld-slow.log

# 参数说明
--verbose    版本
--debug      调试
--help       帮助
 
-v           版本
-d           调试模式
-s ORDER     排序方式
             what to sort by (al, at, ar, c, l, r, t), 'at' is default
              al: average lock time
              ar: average rows sent
              at: average query time
               c: count
               l: lock time
               r: rows sent
               t: query time
-r           反转顺序,默认文件倒序拍。reverse the sort order (largest last instead of first)
-t NUM       显示前N条just show the top n queries
-a           不要将SQL中数字转换成N,字符串转换成S。don't abstract all numbers to N and strings to 'S'
-n NUM       abstract numbers with at least n digits within names
-g PATTERN   正则匹配;grep: only consider stmts that include this string
-h HOSTNAME  mysql机器名或者IP;hostname of db server for *-slow.log filename (can be wildcard),
             default is '*', i.e. match all
-i NAME      name of server instance (if using mysql.server startup script)
-l           总时间中不减去锁定时间;don't subtract lock time from total time

设计范式

表的键和属性概念

超键 唯一标识元组(数据行)的属性集叫做超键.

比如普通表中主键 id 是超键, (id, name) 也是超键, (id, age) 也是超键, 因为都可以唯一标识元组(数据行).

候选键 最小超键, 不包含无用字段, 也称为 .

超键 中的例子来讲, (id, name) 因为包含无用的 name 字段, 所以显然它不是候选键. 而单独的 id 是候选键

主键 从候选键中选择一个, 也称为 主码.

外键 数据表中的字段是别的数据表的主键, 则称它为外键.

主属性 包含在任意候选键中的属性称为主属性.

范式

所有范式(按照严格顺序排列):

  • 1NF(第一范式)

    关键: 表中任何属性都是原子性的, 不可再分.

    解释: 字段不要是可以由其他字段组合/计算的.

  • 2NF(第二范式)

    需要保证表中的非主属性与候选键(码)完全依赖 (即消除了部分依赖)

  • 3NF(第三范式)

    需要保证表中的非主属性与候选键(码)不存在传递依赖

    通常只要求到这个级别.

  • BCNF(巴斯-科德范式)

    消除主属性之间的部分依赖和传递依赖

  • 4NF(第四范式)
  • 5NF(完美范式)

这里的

  • 部分依赖 也称为 部分函数依赖
  • 传递依赖 也称为 传递函数依赖

通常要求: <u>3NF</u>

根据实际情况, 必须时可以新增冗余字段来提高查询效率, 需要权衡.

范式的严格程度是依次递增, 且高级别范式肯定是满足低级别范式的.

存储引擎

该部分主要来自: https://juejin.im/post/5c2c53...

功能差异

show engines
EngineSupportComment
InnoDBDEFAULTSupports transactions, row-level locking, and foreign keys
MyISAMYESMyISAM storage engine

存储差异

MyISAMInnodb
文件格式数据和索引是分别存储的,数据.MYD,索引.MYI数据和索引是集中存储的,.ibd
文件能否移动能,一张表就对应.frmMYDMYI3个文件否,因为关联的还有data下的其它文件
记录存储顺序按记录插入顺序保存按主键大小有序插入
空间碎片(删除记录并flush table 表名之后,表文件大小不变)产生。定时整理:使用命令optimize table 表名实现不产生
事务不支持支持
外键不支持支持
锁支持(锁是避免资源争用的一个机制,MySQL锁对用户几乎是透明的)表级锁定行级锁定、表级锁定,锁定力度小并发能力高
锁扩展

表级锁(table-level lock):lock tables <table_name1>,<table_name2>... read/writeunlock tables <table_name1>,<table_name2>...。其中read是共享锁,一旦锁定任何客户端都不可读;write是独占/写锁,只有加锁的客户端可读可写,其他客户端既不可读也不可写。锁定的是一张表或几张表。

行级锁(row-level lock):锁定的是一行或几行记录。共享锁:select * from <table_name> where <条件> LOCK IN SHARE MODE;,对查询的记录增加共享锁;select * from <table_name> where <条件> FOR UPDATE;,对查询的记录增加排他锁。这里值得注意的是:innodb的行锁,其实是一个子范围锁,依据条件锁定部分范围,而不是就映射到具体的行上,因此还有一个学名:间隙锁。比如select * from stu where id < 20 LOCK IN SHARE MODE会锁定id20左右以下的范围,你可能无法插入id1822的一条新纪录。

课程数据

create.sql

########################################
# MySQL Crash Course
# http://www.forta.com/books/0672327120/
# Example table creation scripts
########################################


########################
# Create customers table
########################
CREATE TABLE customers
(
  cust_id      int       NOT NULL AUTO_INCREMENT,
  cust_name    char(50)  NOT NULL ,
  cust_address char(50)  NULL ,
  cust_city    char(50)  NULL ,
  cust_state   char(5)   NULL ,
  cust_zip     char(10)  NULL ,
  cust_country char(50)  NULL ,
  cust_contact char(50)  NULL ,
  cust_email   char(255) NULL ,
  PRIMARY KEY (cust_id)
) ENGINE=InnoDB;

#########################
# Create orderitems table
#########################
CREATE TABLE orderitems
(
  order_num  int          NOT NULL ,
  order_item int          NOT NULL ,
  prod_id    char(10)     NOT NULL ,
  quantity   int          NOT NULL ,
  item_price decimal(8,2) NOT NULL ,
  PRIMARY KEY (order_num, order_item)
) ENGINE=InnoDB;


#####################
# Create orders table
#####################
CREATE TABLE orders
(
  order_num  int      NOT NULL AUTO_INCREMENT,
  order_date datetime NOT NULL ,
  cust_id    int      NOT NULL ,
  PRIMARY KEY (order_num)
) ENGINE=InnoDB;

#######################
# Create products table
#######################
CREATE TABLE products
(
  prod_id    char(10)      NOT NULL,
  vend_id    int           NOT NULL ,
  prod_name  char(255)     NOT NULL ,
  prod_price decimal(8,2)  NOT NULL ,
  prod_desc  text          NULL ,
  PRIMARY KEY(prod_id)
) ENGINE=InnoDB;

######################
# Create vendors table
######################
CREATE TABLE vendors
(
  vend_id      int      NOT NULL AUTO_INCREMENT,
  vend_name    char(50) NOT NULL ,
  vend_address char(50) NULL ,
  vend_city    char(50) NULL ,
  vend_state   char(5)  NULL ,
  vend_zip     char(10) NULL ,
  vend_country char(50) NULL ,
  PRIMARY KEY (vend_id)
) ENGINE=InnoDB;

###########################
# Create productnotes table
###########################
CREATE TABLE productnotes
(
  note_id    int           NOT NULL AUTO_INCREMENT,
  prod_id    char(10)      NOT NULL,
  note_date datetime       NOT NULL,
  note_text  text          NULL ,
  PRIMARY KEY(note_id),
  FULLTEXT(note_text)
) ENGINE=MyISAM;


#####################
# Define foreign keys
#####################
ALTER TABLE orderitems ADD CONSTRAINT fk_orderitems_orders FOREIGN KEY (order_num) REFERENCES orders (order_num);
ALTER TABLE orderitems ADD CONSTRAINT fk_orderitems_products FOREIGN KEY (prod_id) REFERENCES products (prod_id);
ALTER TABLE orders ADD CONSTRAINT fk_orders_customers FOREIGN KEY (cust_id) REFERENCES customers (cust_id);
ALTER TABLE products ADD CONSTRAINT fk_products_vendors FOREIGN KEY (vend_id) REFERENCES vendors (vend_id);

populate.sql

########################################
# MySQL Crash Course
# http://www.forta.com/books/0672327120/
# Example table population scripts
########################################


##########################
# Populate customers table
##########################
INSERT INTO customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact, cust_email)
VALUES(10001, 'Coyote Inc.', '200 Maple Lane', 'Detroit', 'MI', '44444', 'USA', 'Y Lee', 'ylee@coyote.com');
INSERT INTO customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact)
VALUES(10002, 'Mouse House', '333 Fromage Lane', 'Columbus', 'OH', '43333', 'USA', 'Jerry Mouse');
INSERT INTO customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact, cust_email)
VALUES(10003, 'Wascals', '1 Sunny Place', 'Muncie', 'IN', '42222', 'USA', 'Jim Jones', 'rabbit@wascally.com');
INSERT INTO customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact, cust_email)
VALUES(10004, 'Yosemite Place', '829 Riverside Drive', 'Phoenix', 'AZ', '88888', 'USA', 'Y Sam', 'sam@yosemite.com');
INSERT INTO customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact)
VALUES(10005, 'E Fudd', '4545 53rd Street', 'Chicago', 'IL', '54545', 'USA', 'E Fudd');


########################
# Populate vendors table
########################
INSERT INTO vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country)
VALUES(1001,'Anvils R Us','123 Main Street','Southfield','MI','48075', 'USA');
INSERT INTO vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country)
VALUES(1002,'LT Supplies','500 Park Street','Anytown','OH','44333', 'USA');
INSERT INTO vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country)
VALUES(1003,'ACME','555 High Street','Los Angeles','CA','90046', 'USA');
INSERT INTO vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country)
VALUES(1004,'Furball Inc.','1000 5th Avenue','New York','NY','11111', 'USA');
INSERT INTO vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country)
VALUES(1005,'Jet Set','42 Galaxy Road','London', NULL,'N16 6PS', 'England');
INSERT INTO vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country)
VALUES(1006,'Jouets Et Ours','1 Rue Amusement','Paris', NULL,'45678', 'France');


#########################
# Populate products table
#########################
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('ANV01', 1001, '.5 ton anvil', 5.99, '.5 ton anvil, black, complete with handy hook');
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('ANV02', 1001, '1 ton anvil', 9.99, '1 ton anvil, black, complete with handy hook and carrying case');
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('ANV03', 1001, '2 ton anvil', 14.99, '2 ton anvil, black, complete with handy hook and carrying case');
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('OL1', 1002, 'Oil can', 8.99, 'Oil can, red');
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('FU1', 1002, 'Fuses', 3.42, '1 dozen, extra long');
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('SLING', 1003, 'Sling', 4.49, 'Sling, one size fits all');
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('TNT1', 1003, 'TNT (1 stick)', 2.50, 'TNT, red, single stick');
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('TNT2', 1003, 'TNT (5 sticks)', 10, 'TNT, red, pack of 10 sticks');
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('FB', 1003, 'Bird seed', 10, 'Large bag (suitable for road runners)');
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('FC', 1003, 'Carrots', 2.50, 'Carrots (rabbit hunting season only)');
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('SAFE', 1003, 'Safe', 50, 'Safe with combination lock');
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('DTNTR', 1003, 'Detonator', 13, 'Detonator (plunger powered), fuses not included');
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('JP1000', 1005, 'JetPack 1000', 35, 'JetPack 1000, intended for single use');
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('JP2000', 1005, 'JetPack 2000', 55, 'JetPack 2000, multi-use');



#######################
# Populate orders table
#######################
INSERT INTO orders(order_num, order_date, cust_id)
VALUES(20005, '2005-09-01', 10001);
INSERT INTO orders(order_num, order_date, cust_id)
VALUES(20006, '2005-09-12', 10003);
INSERT INTO orders(order_num, order_date, cust_id)
VALUES(20007, '2005-09-30', 10004);
INSERT INTO orders(order_num, order_date, cust_id)
VALUES(20008, '2005-10-03', 10005);
INSERT INTO orders(order_num, order_date, cust_id)
VALUES(20009, '2005-10-08', 10001);


###########################
# Populate orderitems table
###########################
INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20005, 1, 'ANV01', 10, 5.99);
INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20005, 2, 'ANV02', 3, 9.99);
INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20005, 3, 'TNT2', 5, 10);
INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20005, 4, 'FB', 1, 10);
INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20006, 1, 'JP2000', 1, 55);
INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20007, 1, 'TNT2', 100, 10);
INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20008, 1, 'FC', 50, 2.50);
INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20009, 1, 'FB', 1, 10);
INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20009, 2, 'OL1', 1, 8.99);
INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20009, 3, 'SLING', 1, 4.49);
INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20009, 4, 'ANV03', 1, 14.99);

#############################
# Populate productnotes table
#############################
INSERT INTO productnotes(note_id, prod_id, note_date, note_text)
VALUES(101, 'TNT2', '2005-08-17',
'Customer complaint:
Sticks not individually wrapped, too easy to mistakenly detonate all at once.
Recommend individual wrapping.'
);
INSERT INTO productnotes(note_id, prod_id, note_date, note_text)
VALUES(102, 'OL1', '2005-08-18',
'Can shipped full, refills not available.
Need to order new can if refill needed.'
);
INSERT INTO productnotes(note_id, prod_id, note_date, note_text)
VALUES(103, 'SAFE', '2005-08-18',
'Safe is combination locked, combination not provided with safe.
This is rarely a problem as safes are typically blown up or dropped by customers.'
);
INSERT INTO productnotes(note_id, prod_id, note_date, note_text)
VALUES(104, 'FC', '2005-08-19',
'Quantity varies, sold by the sack load.
All guaranteed to be bright and orange, and suitable for use as rabbit bait.'
);
INSERT INTO productnotes(note_id, prod_id, note_date, note_text)
VALUES(105, 'TNT2', '2005-08-20',
'Included fuses are short and have been known to detonate too quickly for some customers.
Longer fuses are available (item FU1) and should be recommended.'
);
INSERT INTO productnotes(note_id, prod_id, note_date, note_text)
VALUES(106, 'TNT2', '2005-08-22',
'Matches not included, recommend purchase of matches or detonator (item DTNTR).'
);
INSERT INTO productnotes(note_id, prod_id, note_date, note_text)
VALUES(107, 'SAFE', '2005-08-23',
'Please note that no returns will be accepted if safe opened using explosives.'
);
INSERT INTO productnotes(note_id, prod_id, note_date, note_text)
VALUES(108, 'ANV01', '2005-08-25',
'Multiple customer returns, anvils failing to drop fast enough or falling backwards on purchaser. Recommend that customer considers using heavier anvils.'
);
INSERT INTO productnotes(note_id, prod_id, note_date, note_text)
VALUES(109, 'ANV03', '2005-09-01',
'Item is extremely heavy. Designed for dropping, not recommended for use with slings, ropes, pulleys, or tightropes.'
);
INSERT INTO productnotes(note_id, prod_id, note_date, note_text)
VALUES(110, 'FC', '2005-09-01',
'Customer complaint: rabbit has been able to detect trap, food apparently less effective now.'
);
INSERT INTO productnotes(note_id, prod_id, note_date, note_text)
VALUES(111, 'SLING', '2005-09-02',
'Shipped unassembled, requires common tools (including oversized hammer).'
);
INSERT INTO productnotes(note_id, prod_id, note_date, note_text)
VALUES(112, 'SAFE', '2005-09-02',
'Customer complaint:
Circular hole in safe floor can apparently be easily cut with handsaw.'
);
INSERT INTO productnotes(note_id, prod_id, note_date, note_text)
VALUES(113, 'ANV01', '2005-09-05',
'Customer complaint:
Not heavy enough to generate flying stars around head of victim. If being purchased for dropping, recommend ANV02 or ANV03 instead.'
);
INSERT INTO productnotes(note_id, prod_id, note_date, note_text)
VALUES(114, 'SAFE', '2005-09-07',
'Call from individual trapped in safe plummeting to the ground, suggests an escape hatch be added.
Comment forwarded to vendor.'
);
查看原文

赞 4 收藏 2 评论 0

嘉兴ing 发布了文章 · 2019-10-21

MySQL 性能分析备忘录

[TOC]

最后修改时间: 2019年10月21日15:08:59

救急

show full processlist

查看当前线程处理情况, 确认当前有哪些语句在执行, 执行情况如何.

特别注意执行时间长的, 如果确定有问题, 那么可以使用 kill {id} 干掉该连接

show full processlist 等价于以下语句

select id, db, user, host, command, time, state, info
from information_schema.processlist
order by time desc;

注意:

  • show processlist时, root账号可以看到所有账号的连接, 如果是普通账号则只能看到自己的连接.
  • 如果显示长度太长看的很乱, 可以使用 \G, 即 show full processlist\G , 来将显示结果纵向输出, 方便查看.
扩展.

\g 等价于分号

\G 是将显示的表格以纵向输出, 方便查看.

Slow Query Log 慢查询日志

慢查询日志用于记录执行时间超过指定阀值的SQL命令.

确认开启情况

mysql> show variables like 'slow_query_log%';
+---------------------+------------------------------------------------+
| Variable_name       | Value                                          |
+---------------------+------------------------------------------------+
| slow_query_log      | ON                                             |
| slow_query_log_file | C:\laragon\data\mysql\DESKTOP-C1GGBS1-slow.log |
+---------------------+------------------------------------------------+

mysql> show variables like 'long_query_time';
+-----------------+----------+
| Variable_name   | Value    |
+-----------------+----------+
| long_query_time | 1.000000 |
+-----------------+----------+

开启方式

配置文件

############### 慢查询日志 ################
# 打开慢查询日志
slow_query_log=1
# 日志记录位置
log_output=file
# 慢查询日志记录文件
slow_query_log_file=/var/run/mysqld/mysqld-slow.log
# 慢查询时间阀值
long_query_time=10

命令方式(mysqld实例重启后失效)

-- 必须全局开启慢查询日志记录
set global slow_query_log=1;

-- 设置慢查询时间阀值
set global long_query_time=1;

如果想要分析一些语句的执行, 则可以考虑将当前Session的慢查询时间阀值设为0

set long_query_time=0;

注意

当数据库被拖垮时(高负载), 任何简单的语句都可能执行超时, 此时的慢查询日志能提供的帮助就有限了.

慢查询日志分析工具

mysqldumpslow

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

# 分析慢日志
mysqldumpslow -a -n 50 -s c /var/run/mysqld/mysqld-slow.log

# 参数说明
--verbose    版本
--debug      调试
--help       帮助
 
-v           版本
-d           调试模式
-s ORDER     排序方式, 默认是 'at'
             what to sort by (al, at, ar, c, l, r, t), 'at' is default
              al: average lock time 平均锁定时间
              ar: average rows sent 平均返回记录数
              at: average query time 平均查询时间
               c: count 访问计数
               l: lock time 锁定时间
               r: rows sent 返回记录
               t: query time 查询时间
-r           反转顺序,默认文件倒序拍。reverse the sort order (largest last instead of first)
-t NUM       显示前N条
-a           不要将SQL中数字转换成N,字符串转换成S。don't abstract all numbers to N and strings to 'S'
-n NUM       abstract numbers with at least n digits within names
-g PATTERN   正则匹配;grep: only consider stmts that include this string
-h HOSTNAME  mysql机器名或者IP;hostname of db server for *-slow.log filename (can be wildcard),
             default is '*', i.e. match all
-i NAME      name of server instance (if using mysql.server startup script)
-l           总时间中不减去锁定时间;don't subtract lock time from total time

示例

得到返回记录集最多的10个SQL。
mysqldumpslow -s r -t 10 /database/mysql/mysql06_slow.log
 
得到访问次数最多的10个SQL
mysqldumpslow -s c -t 10 /database/mysql/mysql06_slow.log

得到按照时间排序的前10条里面含有左连接的查询语句。
mysqldumpslow -s t -t 10 -g “left join” /database/mysql/mysql06_slow.log
 
另外建议在使用这些命令时结合 | 和more 使用 ,否则有可能出现刷屏的情况。
mysqldumpslow -s r -t 20 /mysqldata/mysql/mysql06-slow.log | more

SQL Explain

对于慢查询日志中执行慢的语句分析其 SQL语句的执行计划

EXPLAIN 可以帮助了解:

  • 数据表的读取顺序
  • SELECT子句的类型
  • 数据表的访问类型
  • 可使用的索引 possible_keys
  • 实际使用的索引 key
  • 使用的索引长度 ken_len
  • 上一个表的连接匹配条件
  • 被优化器查询的行的数量
  • 额外的信息(如使用使用外部排序, 是否使用临时表)

举例

1559124811057.png

EXPLAIN 结果列分析

<u>字段 id</u>

SQL 执行顺序是根据

  • id从大到小执行
  • id相同时按照顺序从上往下执行.

<u>字段 select_type(查询类型)</u>

查询类型说明
SIMPLE简单查询
不包含UNION查询或子查询
PRIMARY最外层查询
查询中若 包含任何复杂的子部分,最外层查询则被标记为PRIMARY
SUBQUERY子查询
在 SELECT 或 WHERE 中包含了子查询
DEPENDENT SUBQUERY!!! 子查询, 但依赖于外层查询的结果
注意确认, 避免大表驱动小表
DERIVED子查询
在 FROM 列表中包含的子查询被标记为 DERIVED(衍生)
UNION联合
UNION 若第二个SELECT出现在UNION之后,则被标记为UNION
UNION RESULT使用联合的结果
从UNION表获取结果的SELECT
关于UNION, 网上有写以下这段, 但我个人不理解

UNION 若第二个SELECT出现在UNION之后,则被标记为UNION:若UNION包含在FROM子句的子查询中,外层SELECT将被标记为:DERIVED


<u>字段 table(数据表)</u>

访问的数据表


<u>字段 partitions(分区)</u>

匹配的分区


<u>字段 type(访问方式)</u>

查询时的访问方式, 性能:all < index < range < index_merge < ref < eq_ref < system/const

一般来说至少需要保证访问方式是 range, 最好是 ref 级别.

访问方式说明
ALL全表扫描,对于数据表从头到尾找一遍
select * from tb1;
特别的:如果有limit限制,则找到之后就不在继续向下扫描
select * from tb1 where email = 'seven@live.com'
select * from tb1 where email = 'seven@live.com' limit 1;
虽然上述两个语句都会进行全表扫描,第二句使用了limit,则找到一个后就不再继续扫描。
INDEX全索引扫描,对索引从头到尾找一遍
因为非主键索引树比较小, 所以会比 ALL 更快
RANGE对索引列进行范围查找
通常是在索引树上快速定位到某一索引项, 再向左/右遍历.
INDEX_MERGE合并索引,使用多个单列索引搜索, 最后结果取交集或并集
比如使用了UNION 且单独用到了两个索引.
REF使用索引快速定位(根据索引查找一个或多个值), 该索引是 普通索引唯一索引的部分前缀
EQ_REF使用主键索引或唯一索引快速定位
通常出现在多表的join查询, 连接时使用primary key 或 unique 索引(都只能匹配到一行记录)
CONST通过主键或唯一索引精确查找到一行
常量
表最多有一个匹配行(主键或唯一索引),因为仅有一行,在这行的列值可被优化器剩余部分认为是常数,const表很快,因为它们只读取一次
SYSTEM系统
表仅有一行, 这是const联接类型的一个特例, 可以忽略这种情况。

Q. ALL 和 INDEX 的区别

A. 两个都是全索引扫描, 不同的是 ALL 是对主键索引扫描, INDEX 是对非主键索引扫描.

这里要理解, 所谓的全表扫描指的是对主键索引扫描.

Q. EQ_REFCONST 的区别

A. 相同点都是使用主键/唯一索引精确查找到行记录. 不同点在于:

  • CONST 查询条件通常是 索引列 = 具体常量值
  • EQ_REF 通常是在多表关联查询时作为连接条件使用.

<u>字段 possible_keys(候选索引)</u>


<u>字段 key(实际使用的索引)</u>

如果是合并索引(INDEX_MERGE), 则此处可能存在超过1个的key


<u>字段 key_len(使用索引的实际长度)</u>

该字段可以评估组合索引是否完全被使用或仅仅是最左前缀被用到.

该字段显示的值为索引字段的最大可能长度, 并非实际使用长度.(即 key_len 是根据表定义计算, 而非表内检索)

计算规则

字段类型计算方式
字符串char(n)n字节长度
varchar(n)若是utf8编码, 则是 n3 + 2 字节
如果是 utf8mb4 编码, 则是 4
n + 2 字节.
数值tinyint1字节
smallint2字节
mediumint3字节
int4字节
bigint8字节
时间date3字节
timestamp4字节
datetime8字节

如果对应索引字段允许为 null, 则还要额外消耗1个字节来存储 NULL.


<u>字段 ref</u>

表示索引的查找条件, 可能是常量(const) 或 联合查询中另一张表的某个字段.


<u>字段 row(扫描行数)</u>

估算的需要扫描的行数, 注意是估算的.

在某些情况下若索引统计信息偏差较大, 则此处的预估扫描行数也会过大, 导致影响查询计划的选择.

可以使用 SHOW INDEX FROM 表名 来查看索引统计信息

可以使用 ANYLYSIS TABLE 表名来重新统计索引信息.


<u>字段 filtered</u>


<u>字段 Extra(额外信息)</u>

该列包含MySQL解决查询的详细信息

说明
Using filesortmysql无法依靠索引直接获取有序记录, 而是对结果进行额外排序.
mysql有两种文件排序算法,这两种排序方式都可以在内存或者磁盘上完成
explain不会告诉你mysql将使用哪一种文件排序
也不会告诉你排序会在内存里还是磁盘上完成。
Using index使用覆盖索引,以避免访问表。不要把覆盖索引和index访问类型弄混了。
Using index condition索引下推优化, 5.6新增特性
Using temporary意味着mysql在对查询结果排序时会使用一个临时表
Using where使用了 where 过滤
这意味着mysql服务器将在存储引擎检索行后再进行过滤
许多where条件里涉及索引中的列,当(并且如果)它读取索引时,就能被存储引擎检验
因此不是所有带where子句的查询都会显示“Using where”。
有时“Using where”的出现就是一个暗示:查询可受益于不同的索引。
Range checked for each record(index map: N)这个意味着没有好用的索引,新的索引将在联接的每一行上重新估算,N是显示在possible_keys列中索引的位图,并且是冗余的。
using join buffer在表联结时, 使用了连接缓存

Profiling

写在最前: SHOW PROFILE 命令将被弃用, 注意, 仅仅是 SHOW PROFILE命令弃用.

替代方案是从 information_schema 中的profiling数据表进行查看, 可参数链接:

SHOW PROFILE 命令方式(旧)

查看语句执行的时间在各个步骤的开销

show profile 分析SQL性能工具(检测数据存在于临时表中)

  • 在会话级别开启profile SET profiling=1;
  • 发送sql
  • 查看profile的资源开销结果

    • show profiles 查看所有的分析结果(会有一个数量上限)
    • show profile 查看最后一条执行语句的分析结果
    • show profile for query <id> 查看指定执行语句的详细分析结果
    • show profile cpu, block io for query <id> 查看详细信息, 且包含 cpu, block.io 执行时间
  • 关闭profile

这一部分更详细的可以参考如:

information_schema .profiling

待用到时补充.

查看原文

赞 0 收藏 0 评论 0

嘉兴ing 关注了用户 · 2019-09-27

三青木木 @sanqingmumu

关注 1

嘉兴ing 发布了文章 · 2019-09-27

关于 MySQL 中 InnoDB 行锁的理解及案例

Last-Modified: 2019年9月29日10:08:11

本文内容主要是 《MySQL实战45讲》 课程中第 20,21,30 课程的个人笔记及相关理解.

主要是对于加锁规则的理解及分析.

以下仅针对 MySQL 的 InnoDB 引擎.

MyISM 引擎就是表锁

基本概念

锁的种类

MySQL 中的锁主要分为:

  • 全局锁

    flush table with read lock;
  • 表级锁

    • 表锁

      lock table 表名 read;
      lock table 表名 write;
    • 元数据锁(Meta Data Lock, MDL)
  • 行锁
还有个自增锁, 后续补充.

意向锁在此先不做讨论.

InnoDB 中的锁

行锁

行锁也叫做记录锁, 这个锁是加在具体的索引项上的.

行锁分为两种:

  • 读锁: 共享锁
  • 写锁: 排它锁

行锁冲突情况:

  • 读锁与写锁冲突
  • 写锁与写锁冲突

需要明确:

  • 锁的对象是索引

间隙锁

记录之间是存在间隙的, 这个间隙也是可以加上锁实体, 称为间隙锁.

间隙锁存在的目的: 解决幻读问题.

间隙锁冲突情况:

  • 间隙锁之间是不冲突的, 它们都是为了防止插入新的记录.
  • 间隙锁与插入操作(插入意向锁)产生冲突

需要明确:

  • 间隙锁仅在 可重复读隔离级别下才存在.
  • 间隙锁的概念是动态的

    对间隙(a,b)加锁后, 存在间隙锁 (a,b).

    此时若 a 不存在(删除), 则间隙锁会向左延伸直到找到一条记录.

    若b不存在了(删除), 则间隙锁会向右延伸直到找到一条记录.

    假设主键上存在记录 id=5 和 id=10 和 id=15 的3条记录, 当存在某个间隙锁 (10,15) 时, 若我们将 id=10 这一行删掉, 则间隙锁 (10, 15) 会动态扩展成 (5, 15), 此时想要插入 id=7 的记录会被阻塞住.

    此处的删除指的是事务提交后, 否则间隙锁依旧是 (10,15)

next-key lock

next-key lock = 行锁 + 间隙锁

next-key lock 的加锁顺序:

  1. 先加间隙锁
  2. 再加行锁
如果加完间隙锁后, 再加行锁时被阻塞进入锁等待时, 间隙锁在此期间是不会释放的.

索引搜索

索引搜索指的是就是:

  1. 在索引树上利用树搜索快速定位找到第一个值
  2. 然后向左或向右遍历

order by desc 就是用最大的值来找第一个

order by 就是用最小的值来找第一个

等值查询

等值查询指的是:

  • 在索引树上利用树搜索快速定位 xx=yy的过程

    where xx > yy 时, 也是先找到 xx = yy 这条记录, 这一个步骤是等值查询.但后续的向右遍历则属于范围查询.
  • 以及在找到具体记录后, 使用 xx=yy 向右遍历的过程.

例子

例子1

begin;
select * from c20 where id=5 for update;

在主键索引 id 上快速查找到 id=5 这一行是等值查询

例子2

begin;
select * from c20 where id > 9 and id < 12 for update;

在主键索引 id 上找到首个大于 9 的值, 这个过程其实是在索引树上快速找到 id=9 这条记录(不存在), 找到了 (5,10) 这个间隙, 这个过程是等值查询.

然后向右遍历, 在遍历过程中就不是等值查询了, 依次扫描到 id=10 , id=15 这两个记录, 其中 id=15 不符合条件, 根据优化2退化为间隙锁, 因此最终锁范围是 (5,10], (10, 15)

例子3

begin;
select * from c20 where id > 9 and id < 12 order by id desc for update;

根据语义 order by id desc, 优化器必须先找到第一个 id < 12 的值, 在主键索引树上快速查找 id=12 的值(不存在), 此时是向右遍历到 id=15, 根据优化2, 仅加了间隙锁 (10,15) , 这个过程是等值查询.

接着向左遍历, 遍历过程就不是等值查询了, 最终锁范围是: (0,5], (5, 10], (10, 15)

例子4

begin;
select * from t where c>=15 and c<=20 order by c desc lock in share mode;

执行过程:

  1. 在索引c上搜索 c=20 这一行, 由于索引c是普通索引, 因此此处的查找条件是 <u>最右边c=20</u> 的行, 因此需要继续向右遍历, 直到找到 c=25 这一行, 这个过程是等值查询. 根据优化2, 锁的范围是 (20, 25)
  2. 接着再向左遍历, 之后的过程就不是等值查询了.

例子5

begin;
select * from t where c<=20 order by c desc lock in share mode;

这里留意一下 , 加锁范围并不是 (20, 25], (15, 20], (10,15], (5,10], (0, 5], (-∞, 5], 而是

...........

..........

.........

........

.......

......

.....

......

.......

........

.........

..........

...........

所有行锁+间隙锁.

具体为什么, 其实只要 explain 看一下就明白了.

例子6 - 个人不理解的地方???????????

-- T1 事务A
begin;
select * from c20 where id>=15 and id<=20 order by id desc lock in share mode;

-- T2 事务B
begin;
update c20 set d=d+1 where id=25;    -- OK
insert into c20 values(21,21,21);    -- 阻塞

-- T3 事务A 人为制造死锁, 方便查看锁状态
update c20 set d=d+1 where id=25;    -- OK
/*
此时 事务B 提示:
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
*/

个人理解:

根据order by id desc, T1 时刻事务A首先在主键索引上搜索 id=20 这一行, 正常来说主键索引上 id=20 的只有一行, 没必要向右遍历.

但实际上, (20,25) 这个间隙被锁上了, 且没有对 id=25 这一行加行锁, 初步理解是根据优化2: 索引上的等值查询在向右遍历且最后一个值不符合条件时, next-key lock 退化为间隙锁.

也就是说这个地方在搜索到 id=20 这一行后还是继续向右遍历了.....不理解为什么

mysql> show engine innodb status
------------------------
LATEST DETECTED DEADLOCK
------------------------
2019-09-27 10:34:29 0xe2e8
*** (1) TRANSACTION:
TRANSACTION 1645, ACTIVE 100 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1080, 4 row lock(s), undo log entries 1
MySQL thread id 82, OS thread handle 77904, query id 61115 localhost ::1 root update
insert into c20 values(21,21,21)
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 23 page no 3 n bits 80 index PRIMARY of table `test_yjx`.`c20` trx id 1645 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 7 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
 0: len 4; hex 80000019; asc     ;;
 1: len 6; hex 00000000066d; asc      m;;
 2: len 7; hex 6e0000019a0110; asc n      ;;
 3: len 4; hex 80000019; asc     ;;
 4: len 4; hex 8000001a; asc     ;;

*** (2) TRANSACTION:
TRANSACTION 1646, ACTIVE 271 sec starting index read
mysql tables in use 1, locked 1
5 lock struct(s), heap size 1080, 5 row lock(s)
MySQL thread id 81, OS thread handle 58088, query id 61120 localhost ::1 root updating
update c20 set d=d+1 where id=25
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 23 page no 3 n bits 80 index PRIMARY of table `test_yjx`.`c20` trx id 1646 lock mode S locks gap before rec
Record lock, heap no 7 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
 0: len 4; hex 80000019; asc     ;;
 1: len 6; hex 00000000066d; asc      m;;
 2: len 7; hex 6e0000019a0110; asc n      ;;
 3: len 4; hex 80000019; asc     ;;
 4: len 4; hex 8000001a; asc     ;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 23 page no 3 n bits 80 index PRIMARY of table `test_yjx`.`c20` trx id 1646 lock_mode X locks rec but not gap waiting
Record lock, heap no 7 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
 0: len 4; hex 80000019; asc     ;;
 1: len 6; hex 00000000066d; asc      m;;
 2: len 7; hex 6e0000019a0110; asc n      ;;
 3: len 4; hex 80000019; asc     ;;
 4: len 4; hex 8000001a; asc     ;;

*** WE ROLL BACK TRANSACTION (1)

上述的:

  • (1) TRANSACTION(事务1) 指的是事务B
  • (2) TRANSACTION(事务2) 指的是事务A

注意与上面的 事务A, 事务B 顺序是相反了, 别看错了.

分析:

  • (1) TRANSACTION

    • insert into c20 values(21,21,21) 最后一句执行语句
  • (1) WAITING FOR THIS LOCK TO BE GRANTED

    • index PRIMARY of table test_yjx.c20 说明在等表 c20 主键索引上的锁
    • lock_mode X locks gap before rec insert intention waiting 说明在插入一条记录, 试图插入一个意向锁, 与间隙锁产生冲突了
    • 0: len 4; hex 80000019; asc ;; 冲突的间隙锁: 16进制的 19, 即 10进制的 id=25 左边的间隙.
  • (2) TRANSACTION 事务2信息

    • update c20 set d=d+1 where id=25 最后一句执行语句
  • (2) HOLDS THE LOCK(S) 事务2持有锁的信息

    • index PRIMARY of table test_yjx.c20 说明持有c20表主键索引上的锁
    • lock mode S locks gap before rec 说明只有间隙锁
    • 0: len 4; hex 80000019; asc ;; 间隙锁: id=25 左边的间隙
  • (2) WAITING FOR THIS LOCK TO BE GRANTED: 事务2正在等待的锁

    • index PRIMARY of table test_yjx.c20 说明在等待 c20 表主键索引上的锁
    • lock_mode X locks rec but not gap waiting 需要对行加写锁
    • 0: len 4; hex 80000019; asc ;; 等待给 id=25 加行锁(写)
  • WE ROLL BACK TRANSACTION (1) 表示回滚了事务1

个人猜测实际情况是:

  1. 首先找到 id=20 这一条记录, 由于bug, 引擎认为可能存在不止一条的 id=20 的记录(即将其认为是普通索引), 因此向右遍历, 找到了 id=25 这一行, 由于此时是等值查询, 根据优化2, 锁退化为间隙锁, 即 (20,25)
  2. 之后正常向左遍历.

无法证实自己的猜测. 已在课程21和课程30留下以下留言, 等待解答(或者无人解答). 2019年9月27日

-- T1 事务A
begin;
select * from c20 where id>=15 and id<=20 order by id desc lock in share mode;

-- T2 事务B
begin;
update c20 set d=d+1 where id=25;    -- OK
insert into c20 values(21,21,21);    -- 阻塞

不能理解, 为什么事务A执行的语句会给 间隙(20,25) 加上锁.
通过 show engine innodb status; 查看发现事务A确实持有上述间隙锁.
通过 explain select * from c20 where id>=15 and id<=20 order by id desc lock in share mode; 查看 Extra 也没有 filesort, key=PRIMARY, 因此个人认为是按照主键索引向左遍历得到结果.

按照我的理解, 由于 order by id desc , 因此首先是在主键索引上搜索 id=20, 同时由于主键索引上这个值是唯一的, 因此不必向右遍历. 然而事实上它确实这么做了, 这让我想到了 BUG1: 主键索引上的范围查询会遍历到不满足条件的第一个.
但是这一步的搜索过程应该是等值查询才对, 完全一脸懵住了...
不知道老师现在还能看到这条评论不?

加锁规则

该部分源自《MySQL实战45讲》中的 《21-为什么我只改了一行的语句, 锁这么多》

以下仅针对 MySQL 的 InnoDB 引擎在 可重复读隔离级别, 具体MySQL版本:

  • 5.x 系列 <= 5.7.24
  • 8.0 系列 <=8.0.13

以下测试若未指定, 则默认使用以下表, 相关案例为了避免污染原始数据, 因此在不影响测试结果前提下, 都放在事务中执行, 且最终不提交.

create table c20(
    id int not null primary key, 
    c int default null, 
    d int default null, 
    key `c`(`c`)
) Engine=InnoDB;

insert into c20 values(0,0,0),(5,5,5),(10,10,10),(15,15,15),(20,20,20),(25,25,25);

/*
+----+------+------+
| id | c    | d    |
+----+------+------+
|  0 |    0 |    0 |
|  5 |    5 |    5 |
| 10 |   10 |   10 |
| 15 |   15 |   15 |
| 20 |   20 |   20 |
| 25 |   25 |   25 |
+----+------+------+
*/

2个"原则", 2个"优化", 1个"BUG"

  1. 原则1: 加锁的基本单位是next-key lock, 前开后闭区间
  2. 原则2: 访问到的对象才会加锁

    select id from t where c = 15 lock in share mode;

    加读锁时, 覆盖索引优化情况下, 不会访问主键索引, 因此如果要通过 lock in share mode 给行加锁避免数据被修改, 那就需要绕过索引优化, 如 select 一个不在索引中的值.

    但如果改成 for update , 则 mysql 认为接下来会更新数据, 因此会将对应主键索引也一起锁了

  3. 优化1: 索引上的等值查询, 对唯一索引加锁时, next-key lock 会退化为行锁

    select * from t where id = 10 for update;

    引擎会在主键索引上查找到 id=10 这一行, 这一个操作是等值查询.

    锁范围是

  4. 优化2: 索引上的等值查询, 向右遍历时且最后一个值不满足等值条件时, next-key Lock 会退化为间隙锁

    select * from t where c = 10 for update;

    由于索引c是普通索引, 引擎在找到 c=10 这一条索引项后继续向右遍历到 c=15 这一条, 此时锁范围是 (5, 10], (10, 15)

    select * from t where c >= 10;

    由于索引c是普通索引, 引擎在找到 c=10 这一条索引项后继续向右遍历到 c=15 这一条, 此时锁范围是 (5, 10], (10, 15)

  5. BUG 1: 唯一索引上的范围查询会访问到不满足条件的第一个值

    id> 10 and id <=15, 这时候会访问 id=15 以及下一个记录.

读提交与可重复读的加锁区别

  1. 读提交下没有间隙锁
  2. 读提交下有一个针对 update 语句的 "semi-consistent" read 优化.

    如果 update 语句碰到一个已经被锁了的行, 会读入最新的版本, 然后判断是不是满足查询条件, 若满足则进入锁等待, 若不满足则直接跳过.

    注意这个策略对 delete 是无效的.

  3. ?????? 语句执行过程中加上的行锁, 会在语句执行完成后将"不满足条件的行"上的行锁直接释放, 无需等到事务提交.

加锁案例

案例: 主键索引 - 等值查询 - 间隙锁

-- T1 事务A
begin;
update c20 set d=d+1 where id=7;
/*
1. 在主键索引上不存在id=7记录, 根据规则1: 加锁基本单位是 next-key lock, 因此加锁范围是(5,10]
2. 由于id=7是一个等值查询, 根据优化2, id=10不满足条件, 因此锁退化为间隙锁 (5,10)
*/

-- T2 事务B
begin;
insert into c20 values(8,8,8);        -- 阻塞
update c20 set d=d+1 where id=10;    -- OK
对应课程的案例一

案例: 非唯一索引 - 等值查询 - 间隙锁

-- T1 事务A
begin;
update c20 set d=d+1 where c=7;
/* 分析
1. 加锁基本单位是next-key lock, 加锁范围就是 (5,10]   -- 此时只是分析过程, 并非加锁过程
2. 根据优化2, 索引上的等值查询(c=7)向右遍历且最后一个值不满足条件时, next-key lock 退化为间隙锁, 加锁范围变为 (5, 10)
3. 由于是在索引c上查询, 因此加锁范围实际上是 ((5,5), (10,10)) , 格式 (c, id)
*/

-- T2 事务B
begin;
insert into c20 values(4,5,4);    -- OK
insert into c20 values(6,5,4);    -- 被间隙锁堵住
insert into c20 values(9,10,9);    -- 被间隙锁堵住
insert into c20 values(11,10,9);    -- OK

案例: 非唯一索引 - 等值查询 - 覆盖索引

关注重点: 覆盖索引优化导致无需回表的情况对主键索引影响

-- T1 事务A
begin;
select id from c20 where c = 5 lock in share mode;    
-- 索引c是普通索引, 因此会扫描到 c=10 这一行, 因此加锁范围是 (0,5], (5,10]
-- 同时由于优化2: 索引上的等值查询向右遍历且最后一个值不满足条件时next-key lock退化为间隙锁, 即加锁范围实际是  (0,5], (5,10)
-- 注意, 该条查询由于只 select id, 实际只访问了索引c, 并没有访问到主键索引, 根据规则2: 访问到的对象才会加锁, 因此最终只对索引c 的范围 (0,5], (5,10) 加锁

-- T2 事务B
begin;
update c20 set d=d+1 where id=5;    -- OK, 因为覆盖索引优化导致并没有给主键索引上加锁
insert into c20 values(7,7,7);
对应课程的案例二

注意, 上面是使用 lock in share mode 加读锁, 因此会被覆盖索引优化.

如果使用 for update, mysql认为你接下来要更新行, 因此也会锁上对应的主键索引.

案例: 非主键索引 - 范围查询 - 对主键的影响

关注重点在于: 普通索引上的范围查询时对不符合条件的索引加锁时, 是否会对对应的主键索引产生影响.

-- T1 事务A
begin;
select * from c20 where c>=10 and c<11 for update;
/*
1. 首先查找到 c=10 这一行, 锁范围 (5,10]
2. 接着向右遍历, 找到 c=15 这一行, 不符合条件, 查询结束. 根据规则2: 只有访问到的对象才会加锁, 由于不需要访问c=15对应的主键索引项, 因此这里的锁范围是索引c上的 (5,10], (10,15], 以及主键上的行锁[10]
*/

-- T2 事务B
begin;
select * from c20 where c=15 for update;     -- 阻塞
select * from c20 where id=15 for update;    -- OK

案例: 主键索引 - 范围锁

-- T1 事务A
begin;
select * from c20 where id>=10 and id<11 for update;
/*
1. 首先在主键索引上查找 id=10 这一行, 根据优化1: 索引上的等值查询在对唯一索引加锁时, next-key lock 退化为行锁, 此时加锁范围是 [10]
2. 继续向右遍历到下一个 id=15 的行, 此时并非等值查询, 因此加锁范围是 [10], (10,15]
*/

-- T2 事务B
begin;
insert into c20 values(8,8,8);        -- OK
insert into c20 values(13,13,13);    -- 阻塞
update c20 set d=d+1 where id=15;    -- 阻塞
对应课程案例三

这里要注意, 事务A首次定位查找id=10这一行的时候是等值查询, 而后续向右扫描到id=15的时候是范围查询判断.

案例: 非唯一索引 - 范围锁

-- T1 事务A
begin;
select * from t where c >= 10 and c < 11 for update;
/*
1. 首先在索引c上找到 c=10 这一行, 加上锁 (5,10]
2. 向右遍历找到 c=15 这一行, 不满足条件, 最终加锁范围是 索引c上的 (5,10], (10,15], 及主键索引 [5]
*/

-- T2 事务B
begin;
insert into c20 values(8,8,8);        -- 阻塞
update c20 set d=d+1 where c=15;    -- 阻塞
update c20 set d=d+1 where id=15;    -- 阻塞
对应课程案例四

案例: 唯一索引 - 范围锁 - bug

-- T1 事务A
begin;
select * from c20 where id>10 and id<=15 for update;
/*
1. 在主键索引上找到 id=15 这一行, 加锁, 根据优化1, next-key lock 退化为行锁 [15]
2. 向右遍历找到 id=20 这一行, 加锁 (15,20]
3. 最终锁范围是 [15], (15,20]
*/

-- T2 事务B
begin;
update c20 set d=d+1 where id=20;    -- 阻塞
insert into c20 values(16,16,16);    -- 阻塞

顺便提一下:

begin;
select * from c20 where id>10 and id<15 for update;
/*
1. 在主键索引上找到id=15这一行, 不满足条件, 根据原则1, 加锁 (10,15]
*/

对应课程案例五

案例: 非唯一索引 - 等值

-- T1 事务A
begin;
insert into c20 values(30,10,30);
commit;
/*
在索引c上, 此时有两行 c=10 的行
由于二级索引上保存着主键的值, 因此并不会有两行完全一致的行, 如下:
c    0    5    10    10    15    20    25
id    0    5    10    30    15    20    25

此时两个 (c=10, id=10) 和 (c=10, id=30) 之间也是存在间隙的
*/

-- T2 事务B
begin;
delete from c20 where c=10;
/*
1. 首先找到索引c上 (c=10, id=10) 这一行, 加锁 (5,10]
2. 向右遍历, 找到 (c=10, id=30) 这一行, 加锁 ( (c=10,id=10), (c=10,id=30) ]
3. 向右遍历, 找到 c=20 这一行, 根据优化2, 索引上的等值查询向右遍历且最后一个值不匹配时, next-key lock 退化为间隙锁, 即加锁 (10,15)
4. 总的加锁范围是 (5,10], ( (c=10,id=10), (c=10,id=30) ], (10,15]
*/

-- T3 事务C
begin;
insert into c20 values(12,12,12);    -- 阻塞
update c20 set d=d+1 where c=15;    -- OK


-- T4 扫尾, 无视
delete from c20 where id=30;
对应课程案例六

delete 的加锁逻辑跟 select ... for update 是类似的.

案例: 非唯一索引 - limit

-- T0 初始环境
insert into c20 values(30,10,30);

-- T1 事务A
begin;
delete from c20 where c=10 limit 2;
/*
1. 找到 c=10 的第一条, 加锁 (5,10]
2. 向右遍历, 找到 c=10,id=30 的记录, 加锁 ( (c=10,id=10), (c=10,id=30) ], 此时满足 limit 2
*/

-- T2, 事务B
begin;
insert into c20 values(12,12,12);    -- OK

如果不加 limit 2 则会继续向右遍历找到 c=15 的记录, 新增加锁范围 (10,15)

对应课程案例七

指导意义:

  • 在删除数据时尽量加 limit, 不仅可以控制删除的条数, 还可以减小加锁的范围.

案例: 死锁例子

-- T1 事务A
begin;
select id from c20 where c=10 lock in share mode;
/*
1. 在索引c上找到 c=10 这一行, 由于覆盖索引的优化, 没有回表, 因此只会在索引c上加锁 (5,10]
2. 向右遍历, 找到 c=15, 不满足, 根据优化2, 加锁范围退化为 (10,15)
3. 总的加锁范围是在索引c上的 (5,10], (10,15)
*/

-- T2 事务B
begin;
update c20 set d=d+1 where c=10;    -- 阻塞
/*
1. 找到 c=10 这一行, 试图加上锁 (5,10], 按照顺序先加上间隙锁(5,10), 由于间隙锁之间不冲突, OK. 之后再加上 [10] 的行锁, 但被T1时刻的事务A阻塞了, 进入锁等待
*/

-- T3 事务A
insert into t values(8,8,8);    -- OK, 但造成 事务B 回滚
/*
往 (5,10) 这个间隙插入行, 此时与 T2时刻事务B 加的间隙锁产生冲突.
同时由于 事务B 也在等待 T1时刻事务A 加的行锁, 两个事务间存在循环资源依赖, 造成死锁.
此时事务B被回滚了, 报错如下:
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
*/
对应课程案例八

案例: 非主键索引 - 逆序

-- T1 事务A
begin;
select * from c20 where c>=15 and c<=20 order by c desc lock in share mode;
/*
1. 在索引c上找到 c=20 这一行, 加锁 (15,20]
2. 向左遍历, 找到 c=15 这一行, 加锁 (10,15]
3. 继续向左遍历, 找到 c=10 这一行, 由于不满足优化条件, 因此直接加锁 (5,10], 不满足查询条件, 停止遍历. 
4. 最终加锁范围是 (5,10], (10,15], (15, 20]
*/

-- T2 事务B
insert into c20 values(6,6,6);    -- 阻塞
对应课程的上期答疑

案例: 读提交级别 - semi-consistent 优化

-- 表结构
create table t(a int not null, b int default null)Engine=Innodb;
insert into t values(1,1),(2,2),(3,3),(4,4),(5,5);

-- T1 事务A
set session transaction isolation level read committed;
begin;
update t set a=6 where b=1;
/*
b没有索引, 因此全表扫描, 对主键索引上所有行加上行锁
*/

-- T2 事务B
set session transaction isolation level read committed;
begin;
update t set a=7 where b=2;    -- OK
/*
在读提交隔离级别下, 如果 update 语句碰到一个已经被锁了的行, 会读入最新的版本, 然后判断是不是满足查询条件, 若满足则进入锁等待, 若不满足则直接跳过.
*/
delete from t where b=3;    -- 阻塞
/*
注意这个策略对 delete 是无效的, 因此delete语句被阻塞
*/
对应课程评论下方 @时隐时现 2019-01-30 的留言

案例: 主键索引 - 动态间隙锁 - delete

-- T1 事务A
begin;
select * from c20 where id>10 and id<=15 for update;
/*
加锁 (10,15], (15, 20]
*/

-- T2 事务B 注意此处没加 begin, 是马上执行并提交的单个事务.
delete from c20 where id=10;    -- OK
/*
事务A在T1时刻加的间隙锁 (10,15) 此时动态扩展成 (5,15)
*/

-- T3 事务C
insert into c20 values(10,10,10);    -- 阻塞
/*
被新的间隙锁堵住了
*/
对应课程评论下方 @Geek_9ca34e 2019-01-09 的留言

如果将上方的 T2时刻的事务B 和 T3时刻的事务C 合并在一个事务里, 则不会出现这种情况.

个人理解是, 事务未提交时, 期间删除/修改的数据仅仅是标记删除/修改, 此时记录还在, 因此间隙锁范围不变.

只有在事务提价后才会进行实际的删除/修改, 因此间隙锁才"会动态扩大范围"

案例: 普通索引 - 动态间隙锁 - update

-- T1 事务A
begin;
select c from c20 where c>5 lock in share mode;
/*
找到 c=5, 不满足, 向右遍历找到 c=10, 加锁 (5,10], 继续遍历, 继续加锁...
*/

-- T2 事务B
update c20 set c=1 where c=5;    -- OK
/*
删除了 c=5 这一行, 导致 T1时刻事务A 加的间隙锁 (5,10) 变为 (1,10)
*/

-- T3 事务C
update c20 set c=5 where c=1;    -- 阻塞
/*
将 update 理解为两步:
1. 插入 (c=5, id=5) 这个记录    -- 被间隙锁阻塞
2. 删除 (c=1, id=5) 这个记录
*/

案例: 非主键索引 - IN - 等值查询

begin;
select id from c20 where c in (5,20,10) lock in share mode;

通过 explain 分析语句:

mysql> explain select id from c20 where c in (5,20,10) lock in share mode;
+----+-------------+-------+-------+---------------+------+---------+------+------+---------------------
| id | select_type | table | type  | possible_keys | key  | key_len | ref  | rows | Extra     
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+---------
|  1 | SIMPLE      | c20   | range | c             | c    | 5       | NULL |    3 | Using where; Using index |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+---------
1 row in set, 1 warning (0.00 sec)
显示结果太长, 因此将 partitions, filtered 列删除了

结果分析:

  • 使用了索引 c
  • rows = 3 说明这3个值都是通过 B+ 树搜索定位的

语句分析:

  1. 在索引c上查找 c=5, 加锁 (0,5], 向右遍历找到 c=10, 不满足条件, 根据优化2, 加锁 (5,10)
  2. 在索引c上查找 c=10, 类似步骤1, 加锁 (5,10], (10, 15)
  3. 在索引c上查找 c=20, 加锁 (15,20], (20, 25)

注意上述锁是一个个逐步加上去的, 而非一次性全部加上去.

考虑以下语句:

begin;
select id from c20 where c in (5,20,10) order by id desc for update;

根据语义 order by id desc, 会依次查找 c=20, c=10, c=5.

由于加锁顺序相反, 因此如果这两个语句并发执行的时候就有可能发生死锁.

相关命令

查看最后一个死锁现场

show engine innodb status;

查看 LATEST DETECTED DEADLOCK 这一节, 记录了最后一次死锁信息.

示例

------------------------
LATEST DETECTED DEADLOCK
------------------------
2019-09-24 16:24:18 0x5484
*** (1) TRANSACTION:
TRANSACTION 1400, ACTIVE 191 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 1080, 3 row lock(s)
MySQL thread id 54, OS thread handle 74124, query id 36912 localhost ::1 root updating
update c20 set d=d+1 where c=10
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 23 page no 4 n bits 80 index c of table `test_yjx`.`c20` trx id 1400 lock_mode X waiting
Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 8000000a; asc     ;;
 1: len 4; hex 8000000a; asc     ;;

*** (2) TRANSACTION:
TRANSACTION 1401, ACTIVE 196 sec inserting
mysql tables in use 1, locked 1
5 lock struct(s), heap size 1080, 3 row lock(s), undo log entries 1
MySQL thread id 53, OS thread handle 21636, query id 36916 localhost ::1 root update
insert into c20 values(8,8,8)
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 23 page no 4 n bits 80 index c of table `test_yjx`.`c20` trx id 1401 lock mode S
Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 8000000a; asc     ;;
 1: len 4; hex 8000000a; asc     ;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 23 page no 4 n bits 80 index c of table `test_yjx`.`c20` trx id 1401 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 8000000a; asc     ;;
 1: len 4; hex 8000000a; asc     ;;

*** WE ROLL BACK TRANSACTION (1)

结果分为3个部分:

  • (1) TRANSACTION 第一个事务的信息

    • WAITING FOR THIS LOCK TO BE GRANTED, 表示这个事务在等待的锁资源
  • (2) TRANSACTION 第二个事务的信息

    • HOLDS THE LOCK(S) 显示该事务持有哪些锁
  • WE ROLL BACK TRANSACTION (1) 死锁检测的处理: 回滚了第一个事务

第一个事务的信息中:

  • update c20 set d=d+1 where c=10 导致死锁时执行的最后一条 sql 语句
  • WAITING FOR THIS LOCK TO BE GRANTED

    • index c of table test_yjx.c20, 说明在等的是表 c20 的索引 c 上面的锁
    • lock_mode X waiting 表示这个语句要自己加一个写锁, 当前状态是等待中.
    • Record lock 说明这是一个记录锁
    • n_fields 2 表示这个记录是两列, 即 字段c 和 主键字段 id
    • 0: len 4; hex 8000000a; asc ;; 是第一个字段(即字段c), 值(忽略里面的8)是十六进制 a, 即 10

      值 8000000a 中的 8...我也不理解为什么, 先忽略
    • 1: len 4; hex 8000000a; asc ;; 是第二个字段(即字段id), 值是 10
    • 上面两行里的 asc 表示, 接下来要打印出值里面的"可打印字符", 但10不是可打印字符, 因此就显示空格

      这里不太理解
  • 第一个事务信息只显示出等锁的状态, 在等待 (c=10, id=10) 这一行的锁
  • 没有显示当前事务持有的锁, 但可以从第二个事务中推测出来.

第二个事务的信息中:

  • insert into c20 values(8,8,8) 导致死锁时最后执行的语句
  • HOLDS THE LOCK(S)

    • index c of table test_yjx.c20 trx id 1401 lock mode S 表示锁是在表 c20 的索引 c 上, 加的是读锁
    • hex 8000000a;表示这个事务持有 c=10 这个记录锁
  • WAITING FOR THIS LOCK TO BE GRANTED

    • index c of table test_yjx.c20 trx id 1401 lock_mode X locks gap before rec insert intention waiting

      • insert intention 表示试图插入一个记录, 这是一个插入意向锁, 与间隙锁产生锁冲突
      • gap before rec 表示这是一个间隙锁, 而不是记录锁.

补充:

  • lock_mode X waiting 表示 next-key lock
  • lock_mode X locks rec but not gap 表示只有行锁
  • locks gap before rec 就是只有间隙锁

从上面信息可以知道:

  • 第一个事务

    • 推测出持有间隙锁 (?, 10)
    • 试图更新 c=10 这一行, 但被索引c 的 行锁 c=10 阻塞了
  • 第二个事务

    • 持有行锁 c=10
    • 试图插入 (8,8,8), 但被间隙锁 (?, 10) 阻塞了
  • 检测到死锁时, InnoDB 认为 第二个事务回滚成本更高, 因此回滚了第一个事务.
查看原文

赞 1 收藏 0 评论 0

嘉兴ing 关注了专栏 · 2019-07-31

面向 offer 编程

当你感到悲哀痛苦时,最好是去学些什么东西

关注 12

嘉兴ing 关注了用户 · 2019-07-19

hoohack @hoohack

关注 76

认证与成就

  • 获得 52 次点赞
  • 获得 6 枚徽章 获得 0 枚金徽章, 获得 0 枚银徽章, 获得 6 枚铜徽章

擅长技能
编辑

(゚∀゚ )
暂时没有

开源项目 & 著作
编辑

(゚∀゚ )
暂时没有

注册于 2016-03-13
个人主页被 544 人浏览