MySQL 優(yōu)化學(xué)習(xí)筆記

簡(jiǎn)述

目錄

  • 《高性能高可用 MySQL》視頻中的前置課程 1~4
    • (1~2) 前置課程，搭建 Centos7 集群環(huán)境
    • (3) 前置課程，linux 下 RMP 版 MySQL 安裝、啟停
    • (4) 前置課程，MySQL 啟動(dòng)問(wèn)題，配置文件，編碼問(wèn)題
  • 《SQL 優(yōu)化》視頻中的 3~25
    • (3) MySQL 分層、存儲(chǔ)引擎
    • (4) SQL 優(yōu)化
    • (5) B 樹(shù)與索引
    • (6) SQL 優(yōu)化準(zhǔn)備
    • (7) explain 中的 id、table
    • (8) type 級(jí)別詳解
    • (9) 索引類(lèi)型及逐步優(yōu)化、key_len 計(jì)算方法
    • (10) ref、rows
    • (11) Extra 字段
    • (12) 優(yōu)化示例
    • (13) 單表優(yōu)化及總結(jié)
    • (14~15) 多表優(yōu)化及總結(jié)，避免索引失效原則
    • (16) 常見(jiàn)優(yōu)化方法及慢查詢 SQL 排查
    • (17) 慢查詢閾值和 mysqldumpslow 工具
    • (18) 模擬并通過(guò) profiles 分析海量數(shù)據(jù)
    • (19) 全局查詢?nèi)罩?/li>
    • (20) 鎖機(jī)制詳解
    • (21) 寫(xiě)鎖示例與 MyISAM 模式特征
    • (22) 寫(xiě)鎖情況分析及行鎖解析
    • (23) 行鎖的注意事項(xiàng)
    • (24) 查詢行鎖

1~2. 前置課程，搭建 Centos7 集群環(huán)境

DT 課堂顏群：高性能高可用 MySQL
https://www.bilibili.com/video/BV1ry4y1v7Tr

配置網(wǎng)絡(luò)

  • 進(jìn)入 Centos7 修改 hostname
    • hostnamectl set-hostname bigdata01
  • 關(guān)機(jī)后配置網(wǎng)絡(luò)
    • 編輯-編輯虛擬機(jī)網(wǎng)絡(luò)編輯器
    • 子網(wǎng)：192.168.2.0
    • 子網(wǎng)掩碼：255.255.255.0
    • 起始：192.168.2.128
    • 終止：192.168.2.254
  • 在 windows 設(shè)置 VMnet8
    • IP: 192.168.2.2
    • 網(wǎng)關(guān)：192.168.2.1
  • 查看虛擬機(jī)網(wǎng)卡
    • cd /etc/sysconfig/network-scripts/
  • 編輯網(wǎng)卡
    • vi /etc/sysconfig/network-scripts/ifcfg-ens33
IPADDR=192.168.2.128
GATEWAY=192.168.2.1
BROADCAST=192.168.2.255
DNS1=114.114.114.114
DNS2=8.8.8.8
  • 增加映射
    • vi /etc/hosts
    • 192.168.2.128 bigdata01
    • windows 下
      • C:\Windows\System32\drivers\etc\hosts
      • 192.168.2.128 bigdata01
  • 配置網(wǎng)絡(luò)服務(wù)
    • service NetworkManager stop
    • /etc/init.d/network restart
    • chkconfig NetworkManager off
    • 追加 nameserver
      • vi /etc/resolv.conf
      • nameserver 192.168.2.1
  • 重啟網(wǎng)關(guān)
    • systemctl restart network
  • 檢查是否配置成功
    • centos
      • ping baidu.com
        • 成功則返回
        • 64 bytes from 220.181.38.148 (220.181.38.148): icmp_seq=1 ttl=128 time=7.16 ms
      • ping 192.168.2.2
        • 成功則返回
        • 64 bytes from 220.181.38.148 (220.181.38.148): icmp_seq=1 ttl=128 time=7.16 ms
    • windows
      • ping 192.168.2.128
        • 成功則返回
        • 來(lái)自 192.168.2.128 的回復(fù): 字節(jié)=32 時(shí)間<1ms TTL=64

設(shè)置時(shí)間同步

  • yum -y install npt ntpdate
  • ntpdate cn.pool.ntp.org
  • hwclock --systoch

安裝 JDK

  • 上傳 jdk-8u171-linux-x64.rpm 到 /usr 目錄
  • 安裝
    • rpm -ivh jdk-8u171-linux-x64.rpm
    • /usr/java/jdk1.8.0_171-amd64

配置環(huán)境變量

  • vi /etc/profile
  • 追加如下內(nèi)容
    • export JAVA_HOME=/usr/java/jdk1.8.0_171-amd64
    • export CLASSPATH=$JAVA_HOME\lib:$CLASSPATH
    • export PATH=$JAVA_HOME\bin:$PATH
  • 環(huán)境變量生效
    • source /etc/profile
  • 測(cè)試是否配置成功
    • java -version

克隆并修改網(wǎng)絡(luò)配置

  • IP
    • vi /etc/sysconfig/network-scripts/ifcfg-ens33
    • IPADDR=192.168.2.129
    • IPADDR=192.168.2.130
    • 同時(shí)刪除網(wǎng)卡唯一標(biāo)識(shí) UID 屬性
  • hostname
    • 分別修改克隆機(jī)的 hostname
      • hostnamectl set-hostname bigdata02
      • hostnamectl set-hostname bigdata03
    • centos
      • vi /etc/hosts
      • 192.168.2.129 bigdata02
      • 192.168.2.130 bigdata03
    • windows
      • C:\Windows\System32\drivers\etc\hosts
      • 192.168.2.129 bigdata02
      • 192.168.2.130 bigdata03
  • 重啟網(wǎng)絡(luò)
    • systemctl restart network
  • 檢查三個(gè)節(jié)點(diǎn)之間的連通
    • ping bigdata01
    • ping bigdata02
    • ping bigdata03

設(shè)置節(jié)點(diǎn)之間免密登錄

  • 生成密鑰
    • ssh-keygen -t rsa
  • 私鑰拷貝給自己
    • ssh-copy-id localhost
  • 公鑰拷貝給其他節(jié)點(diǎn)
    • ssh-copy-id bigdata01
    • ssh-copy-id bigdata02
    • ssh-copy-id bigdata03
  • 測(cè)試連通
    • ssh bigdata01
    • ssh bigdata02
    • ssh bigdata03

3. 前置課程，linux 下 RMP 版 MySQL 安裝、啟停

  • 安裝目錄 /app
  • 下載
  • 安裝 5.5.58
    • rpm -ivh MySQL-server-5.5.58-1.el6.x86_64.rpm
    • rpm -ivh MySQL-client-5.5.58-1.el6.x86_64.rpm
    • 卸載沖突
      • yum -y remove xxx
    • 驗(yàn)證安裝
      • mysqladmin --version
  • 安裝 5.6.63
    • 課程視頻中安裝的是 5.5.58
    • 5.6.63 安裝方式與 5.5.58 略有不同
    • 參考文章
    • 安裝前
      • 查看 centos7 系統(tǒng)自帶的 mariadb 版本
        • rpm -qa|grep mariadb
      • 如果存在則卸載
        • 加 --nodeps 避免依賴問(wèn)題
        • rpm -e mariadb-libs --nodeps
      • 安裝 perl-Data-Dumper，否則 server 安裝時(shí)會(huì)報(bào)錯(cuò)
        • yum install -y perl-Data-Dumper
    • rpm -ivh MySQL-server-5.6.36-1.el7.x86_64
      • server 安裝完成后會(huì)自動(dòng)初始化
    • rpm -ivh MySQL-client-5.6.36-1.el7.x86_64.rpm
    • 驗(yàn)證安裝
      • mysqladmin --version
  • 啟動(dòng)、關(guān)閉、重啟
    • service mysql start
    • service mysql stop
    • service mysql restart
  • 在系統(tǒng)里啟動(dòng) mysql 服務(wù)
    • 系統(tǒng)開(kāi)機(jī)手動(dòng)啟動(dòng)
      • /etc/init.d/mysql start
    • 開(kāi)機(jī)自動(dòng)啟動(dòng)
      • chkconfig mysql on
    • 關(guān)閉開(kāi)機(jī)自動(dòng)啟動(dòng)
      • chkconfig mysql off
    • 檢查開(kāi)機(jī)自啟動(dòng)項(xiàng)
      • ntsysv
  • 設(shè)置密碼
    • /usr/bin/mysqladmin -u root password root
    • /usr/bin/mysqladmin -u root -h bigdata01 password root
  • 設(shè)置密碼后進(jìn)入 mysql
    • mysql -u root -p
  • 5.6 及以上版本初始密碼修改
    • 無(wú)法查看初始密碼，進(jìn)入 mysql 報(bào)錯(cuò)
      • error: 'Access denied for user 'root'@'localhost' (using password: NO)'
    • 重置密碼
      • 參考文章
      • /etc/init.d/mysql stop
      • mysqld_safe --user=mysql --skip-grant-tables --skip-networking &
      • mysql -u root mysql
      • mysql> UPDATE user SET Password=PASSWORD('root') where USER='root';
      • mysql> FLUSH PRIVILEGES;
      • mysql> quit
      • mysql> mysql -u root -p

4. 前置課程，MySQL 啟動(dòng)問(wèn)題，配置文件，編碼問(wèn)題

  • ps -ef|grep mysql
    • 數(shù)據(jù)庫(kù)文件目錄
      • --datadir=/var/lib/mysql
    • pid （唯一標(biāo)識(shí)符）文件目錄
      • --pid-file=/var/lib/mysql/bigdata01.pid

mysql 核心目錄

  • 安裝目錄
    • /var/lib/mysql
  • 配置文件目錄
    • /user/share/mysql
  • 命令目錄
    • /usr/bin
    • mysqladmin
    • mysqldump

mysql 配置文件

  • 4 個(gè)配置文件模板
    • my-huge.cnf 高端服務(wù)器 1~2G 內(nèi)存
    • my-large.cnf
    • my-medium.cnf
    • my-small.cnf
  • 將模板中的一個(gè)拷貝給系統(tǒng)默認(rèn)配置
    • MySQL 5.5：/etc/my.cnf
    • MySQL 5.6：/etc/mysql-default.cnf
    • cp /usr/share/mysql/my-huge.cnf /etc/my.cnf

mysql 字符編碼

  • 查看編碼
    • show variables like '%char%';
  • 將所有編碼設(shè)置為 UTF-8
    • vi /etc/my.cnf
[mysql]
default-character-set=utf8

[client]
default-character-set=utf8

[mysqld]
character_set_server=utf8
character_set_client=utf8
collation_server=utf8_general_ci
  • 重啟 mysql
    • service mysql restart
  • 再次查看字符編碼集
    • show variables like '%char%';
  • 編碼修改只對(duì)之后生成的數(shù)據(jù)庫(kù)有效

mysql 清屏

  • ctrl + L
  • system clear

3. MySQL 分層、存儲(chǔ)引擎

DT 課堂顏群：SQL優(yōu)化
https://www.bilibili.com/video/BV1es411u7we

mysql 分層

  • 連接層
    • 提供與客戶端連接的服務(wù)
  • 服務(wù)層
    • 提供各種用戶使用的接口
    • 提供 SQL 優(yōu)化器（MySQL QUery Optimizer）
  • 引擎層
    • 提供各種存儲(chǔ)數(shù)據(jù)的方式
    • InnoDB
    • MyISAM
  • 存儲(chǔ)層
    • 存儲(chǔ)數(shù)據(jù)

InnoDB 和 MyISAM 區(qū)別

  • InnoDB
    • 事務(wù)優(yōu)先
    • 適合高并發(fā)操作
    • 行鎖
  • MyISAM
    • 性能優(yōu)先
    • 表鎖
  • 查詢數(shù)據(jù)庫(kù)支持哪些引擎
    • show engines;
  • 查詢默認(rèn)引擎
    • show variables like '%storage_engine%';
  • 創(chuàng)建表時(shí)指定引擎
mysql>create database myDB;

mysql>use myDB;

mysql>
create table tb(
id int(4) auto_increment,
name varchar(5),
dept varchar(5),
primary key(id)
) ENGINE=MyISAM AUTO_INCREMENT=1 
DEFAULT CHARSET=utf8;

4. SQL 優(yōu)化

原因

  • 性能低
  • 執(zhí)行時(shí)間長(zhǎng)
  • 等待時(shí)間長(zhǎng)
  • SQL 語(yǔ)句欠佳（連接查詢）
  • 索引失效
  • 服務(wù)器參數(shù)設(shè)置不佳
    • 緩沖
    • 線程數(shù)

編寫(xiě)過(guò)程和解析過(guò)程的差異

  • 編寫(xiě)過(guò)程
    • select distinct ... from ... join ... on ... where ... group by ... having ... order by ... limit
  • 解析過(guò)程
    • from ... on ... join ... where ... group by ... having ... select distinct... order by ... limit
  • 參考文章
    • https://www.cnblogs.com/annsshadow/p/5037667.html

優(yōu)化索引

  • 索引是幫助 MYSQL 高效獲取數(shù)據(jù)的數(shù)據(jù)結(jié)構(gòu)
  • 索引一般采用樹(shù)結(jié)構(gòu)
    • B 樹(shù)
    • Hash 樹(shù)
  • 索引弊端
    • 索引本身需要空間
    • 索引不適用
      • 少量數(shù)據(jù)
      • 頻繁更新的字段
      • 很少使用的字段
    • 提高查詢，降低增刪改效率
  • 優(yōu)點(diǎn)
    • 降低 IO 使用率
    • 降低 CPU 使用率
      • B 樹(shù)本身已經(jīng)排好序，可以直接使用

5. B 樹(shù)與索引

  • 三層 B 樹(shù)可以存放百萬(wàn)級(jí)別數(shù)據(jù)
  • B 樹(shù)一般都是指 B+ 樹(shù)
    • 數(shù)據(jù)都保存在葉結(jié)點(diǎn)
  • B + 樹(shù)中查找數(shù)據(jù)的次數(shù)
    • n 次
    • 即 B+ 樹(shù)的高度

索引

分類(lèi)

  • 單值索引
    • 單列
    • 一個(gè)表可以有多個(gè)單值索引
  • 主鍵索引
    • 不能重復(fù)
    • 如 id
    • 不能為 null
  • 唯一索引
    • 不能重復(fù)
    • 如 id
    • 可以為 null
  • 復(fù)合索引
    • 多個(gè)列構(gòu)成的索引
    • 相當(dāng)于二級(jí)目錄

創(chuàng)建索引

方式一

  • create 索引類(lèi)型 索引名 on 表(字段)
  • 單值索引
    • create index dept_index on tb(dept);
  • 唯一索引
    • create unique index name_index on tb(name);
  • 復(fù)合索引
    • create index dept_name_index on tb(dept, name);

方式二

  • alter table 表名 索引類(lèi)型 索引名(字段)
  • 單值
    • alter table tb add index dept_index(dept);
  • 唯一
    • alter table tb add unique index name_index(name);
  • 復(fù)合
    • alter table tb add index dept_name_index(dept, name);
  • DDL 語(yǔ)句不需要 commit; 自動(dòng)提交
  • 如果一個(gè)字段是 primary key，該字段默認(rèn)是主鍵索引

刪除索引

  • drop index 索引名 on 表名;
  • drop index name_index on tb;

查詢索引

  • show index from 表名
  • show index from tb;

SQL 性能問(wèn)題

  • 分析 sql 執(zhí)行計(jì)劃
    • explain
    • 可以模擬 SQL 優(yōu)化器執(zhí)行 SQL 語(yǔ)句
  • MYSQL 查詢優(yōu)化會(huì)干擾我們的優(yōu)化

6. SQL 優(yōu)化準(zhǔn)備

  • explain SQL 語(yǔ)句
  • id 編號(hào)
  • select_type 查詢類(lèi)型
  • table 表名
  • type 類(lèi)型
  • possible_keys 預(yù)測(cè)用到的索引
  • key 實(shí)際用到的索引
  • key_len 實(shí)際使用索引的長(zhǎng)度
  • ref 表之間的引用
  • rows 通過(guò)索引查詢到的數(shù)據(jù)量
  • Extra 額外信息

準(zhǔn)備數(shù)據(jù)

create table course
(cid int(3),
cname varchar(20),
tid int(3)
);

create table teacher
(tid int(3),
tname varchar(20),
tcid int(3)
);

create table teacherCard
(
tcid int(3),
tcdesc varchar(200)
);

insert into course values(1,'java', 1);
insert into course values(2,'html', 1);
insert into course values(3,'sql', 2);
insert into course values(4,'web', 3);

insert into teacher values(1, 'tz', 1);
insert into teacher values(2, 'tw', 2);
insert into teacher values(3, 'tl', 3);
insert into teacher values(4, 'ta', 4);
insert into teacher values(5, 'tb', 5);
insert into teacher values(6, 'tc', 6);

insert into teacherCard values(1, 'tzdesc');
insert into teacherCard values(2, 'twdesc');
insert into teacherCard values(3, 'tldesc');

7. explain 中的 id、table

id 值相同

  • id 值相同，從上往下，順序執(zhí)行
  • 表的執(zhí)行順序，跟隨數(shù)據(jù)量變化，原理是笛卡爾積
  • 數(shù)據(jù)量小的表優(yōu)先查詢

查詢課程編號(hào)為 2 或教師證編號(hào)為 3 的老師信息

explain select t.* from teacher t, course c, teacherCard tc 
where t.tid=c.tid and t.tid=tc.tcid and (c.cid = 2 or tc.tcid=3);

查詢教授 SQL 課程的老師描述信息

  • 多表連接形式
explain select tc.tcdesc from teacherCard tc, course c, teacher t 
where c.tid = t.tid and t.tcid = tc.tcid and c.cname='sql';

id 值不同

  • id 值不同，id 值大的優(yōu)先查詢
  • 本質(zhì)：在嵌套子查詢時(shí)，先查內(nèi)層，再查外層

查詢教授 SQL 課程的老師描述信息

  • 子查詢形式
explain select tc.tcdesc from teacherCard tc where tc.tcid=
(select t.tcid from teacher t where t.tid = 
(select c.tid from course c)
);

id 值相同 + id 值不同

  • id 值大的優(yōu)先
  • id 值相同的從上往下順序執(zhí)行

查詢教授 SQL 課程的老師描述信息

  • 多表+子查詢
explain select t.tname, tc.tcdesc from teacher t, teacherCard tc 
where t.tcid=tc.tcid and t.tid=(select c.tid from course c where cname='sql');

select_type

  • primary 包含子查詢 SQL 中的主查詢（最外層）
  • SUBQUERY 包含子查詢 SQL 中的子查詢（非最外層）
  • simple 簡(jiǎn)單查詢，不包含子查詢和 union
  • derived 衍生查詢，使用到了臨時(shí)表
    • from 子查詢中只有一張表
      • explain select cr.cname from (select * from course where tid in (1, 2)) cr;
    • from 子查詢中，如果有 table1 union table2，table1 就是 derived
      • explain select cr.cname from (select * from course where tid=1 union select * from course where tid =2) cr;
  • union result
    • 告知關(guān)聯(lián)關(guān)系的表是哪兩張

8. type 級(jí)別詳解

system

create table test01
(
tid int(3),
tname varchar(20)
);

alter table test01 add constraint tid_pk primary key(tid);

insert into test01 values(1, 'a');

explain select * from (select * from test01) t where tid=1;
  • system>const>eq_ref>ref>range>index>all
  • system 和 const 只是理想情況，一般優(yōu)化很難達(dá)到
  • system 只有一條數(shù)據(jù)的系統(tǒng)表，或衍生表只有一條數(shù)據(jù)的主查詢

const

explain select tid from test01 where tid=1;
/* 刪除 primary 索引 */
alter table test01 drop primary key;
/* 修改索引為一般索引 */
create index test01_index on test01(tid);
  • const 只能查到一條數(shù)據(jù)的 SQL
  • 只能用于 primary key 或 unique 索引
  • 如果是一般索引，不會(huì)出現(xiàn) const

eq_ref

alter table teacherCard add constraint pk_tcid primary key(tcid);
alter table teacher add constraint uk_tcid unique index(tcid);
delete from teacher where tcid>3;
explain select t.tcid from teacher t, teacherCard tc where t.tcid = tc.tcid;
  • 對(duì)于每個(gè)索引鍵的查詢，返回匹配有且只有一行數(shù)據(jù)
  • 常見(jiàn)于唯一索引和主鍵索引
  • 上述語(yǔ)句用到的索引是 teacher 表的 tcid 字段
  • 如果 teacher 表的數(shù)據(jù)個(gè)數(shù)和連接查詢的數(shù)據(jù)個(gè)數(shù)一致，才有可能滿足 eq_ref 級(jí)別

ref

insert into teacher values(4, 'tz', 4);
insert into teacherCard values(4, 'tzc');

alter table teacher add index index_name(tname);

explain select * from teacher where tname='tz';
  • 非唯一索引
  • 對(duì)于每個(gè)索引鍵的查詢，返回匹配的所有行

9. 索引類(lèi)型及逐步優(yōu)化，ken_len 計(jì)算方法

range

alter table teacher add index tid_index(tid);

explain select t.* from teacher t where t.tid <3;
  • 檢索指定范圍的行，where 后面是一個(gè)范圍查詢
  • between, in, <, >, >=, <=
  • in 查詢，有時(shí)會(huì)失效，從 range 級(jí)別轉(zhuǎn)為 all 無(wú)索引級(jí)別

index，all

/* tid 有索引，只掃描 tid 列 */
explain select tid from teacher;
/* course 表無(wú)索引，掃描全部數(shù)據(jù) */
explain select cid from course;
  • index 查詢?nèi)克饕龜?shù)據(jù)
  • all 查詢?nèi)繑?shù)據(jù)

總結(jié)

  • system/const
    • 結(jié)果只有一條
  • eq_ref
    • 結(jié)果多條
    • 每條數(shù)據(jù)唯一
  • ref
    • 結(jié)果多條
    • 每條數(shù)據(jù)可能是多條

possible_keys, key

alter table course add index cname_index(cname);

explain select t.tname, tc.tcdesc from teacher t, teacherCard tc 
where t.tcid=tc.tcid and t.tid=(select c.tid from course c where cname='sql');

explain select tc.tcdesc from teacherCard tc, course c, teacher t 
where c.tid = t.tid and t.tcid = tc.tcid and c.cname='sql';
  • possible_keys
    • 可能用到的索引
    • 是一種預(yù)測(cè)
  • key
    • 實(shí)際使用到的索引
  • null 表示無(wú)索引

key_len

create table test_kl 
(
name char(20) not null default ''
);

alter table test_kl add index index_name(name);

explain select * from test_kl where name='';

alter table test_kl add column name1 char(20);
alter table test_kl add index index_name1(name1);

explain select * from test_kl where name1='';

drop index index_name on test_kl;
drop index index_name1 on test_kl;

alter table test_kl add index name_name1_index (name, name1);

explain select * from test_kl where name1='';

alter table test_kl add column name2 varchar(20);
alter table test_kl add index name2_index(name2);

/* key_len=63 = 60+1(null)+2(varchar) */
explain select * from test_kl where name2='';
  • 索引的長(zhǎng)度
  • 用于判斷復(fù)合索引是否被完全使用
  • utf8 中，1 個(gè)字符占 3 個(gè)字節(jié)
    • char(20)，key_len = 60
  • gbk 中，1 個(gè)字符 2 個(gè)字節(jié)
  • latin 中，1 個(gè)字符 1 個(gè)字節(jié)
  • 如果索引字段可以為 null，mysql 底層會(huì)用 1 個(gè)字節(jié)用于標(biāo)識(shí)
  • 索引字段為 varchar，用 2 個(gè)字節(jié)代表可變長(zhǎng)度

10. ref, rows

ref

alter table course add index tid_index(tid);

explain select * from course c, teacher t where c.tid = t.tid and t.tname='tw';
  • 與 type 中的 ref 區(qū)分
  • 指明當(dāng)前表所參照的字段
  • select ... where a.c=b.x
  • 其中 b.x 可以是常量，const

rows

explain select * from course c, teacher t where c.tid = t.tid and t.tname='tz';
  • 實(shí)際通過(guò)索引查詢到的數(shù)據(jù)個(gè)數(shù)

11. Extra 字段

Using filesort

create table test02
(
a1 char(3),
a2 char(3),
a3 char(3),
index idx_a1(a1),
index idx_a2(a2),
index idx_a3(a3)
);

/* 排序和查找不是同一個(gè)字段 Using filesort */
explain select * from test02 where a1 = '' order by a2;

drop index idx_a1 on test02;
drop index idx_a2 on test02;
drop index idx_a3 on test02;

alter table test02 add index idx_a1_a2_a3(a1, a2, a3);
/* 復(fù)合索引跨列 */
explain select * from test02 where a1='' order by a3;
explain select * from test02 where a2='' order by a3;
explain select * from test02 where a1='' order by a2;
  • 性能消耗大，需要額外一次排序或查詢
  • 如果排序和查找不是同一個(gè)字段，則會(huì)出現(xiàn) Using filesort
  • 如果符合索引跨列，會(huì)出現(xiàn) Using filesort
    • where 和 order by 按照符合索引的順序使用，不要跨列或無(wú)序
  • 常見(jiàn)于 order by

Using temporary

explain select a1 from test02 where a1 in ('1', '2', '3') group by a2;
  • 用到了臨時(shí)表
  • 常見(jiàn)于 group by
  • 避免
    • 查詢哪列就使用哪列 group by

Using index

explain select a1, a2 from test02 where a1='' or a2='';

drop index idx_a1_a2_a3 on test02;
alter table test02 add index id_a1_a2(a1, a2);
explain select a1, a3 from test02 where a1='' or a3='';

/* 對(duì) possible_keys 和 key 的影響 */
explain select a1, a2 from test02 where a1='' or a2='';
explain select a1, a2 from test02;
  • 使用到的列都在索引中，稱為索引覆蓋
  • 性能提升
  • 不讀取原文件，只從索引文件中獲取數(shù)據(jù)
  • 不需要回表查詢
  • 索引覆蓋對(duì) possible_keys 和 key 的影響
    • 如果沒(méi)有 where，則索引只出現(xiàn)在 key 中
    • 如果有 where，則索引出現(xiàn)在 key 和 possible_keys 中

Using where

explain select a1, a3 from test02 where a3='';
  • 回表查詢

impossible where

explain select * from test02 where a1='x' and a1='y';
  • where 子句永遠(yuǎn)為 false

12. 優(yōu)化示例

create table test03
(
a1 int(4) not null,
a2 int(4) not null,
a3 int(4) not null,
a4 int(4) not null
);

alter table test03 add index idx_a1_a2_a3_4(a1, a2, a3, a4);

/* Using index */
/* 推薦按照復(fù)合索引的順序查詢 */
explain select a1, a2, a3, a4 from test03 where a1=1 and a2=2 and a3=3 and a4=4;

/* Using index */
/* 經(jīng)過(guò) SQL 優(yōu)化器后，效果與上一個(gè)查詢語(yǔ)句一致 */
explain select a1, a2, a3, a4 from test03 where a4=1 and a3=2 and a2=3 and a1=4;

/* Using where; Using index */
/* a4 跨列，索引失效，造成回表查詢 */
/* where a1=1 and a2=2 ... order by a3 仍然遵循復(fù)合索引的順序，因此有 Using index */
explain select a1, a2, a3, a4 from test03 where a1=1 and a2=2 and a4=4 order by a3;

/* Using where; Using index; Using filesort */
/* where a1=1 ... order by a3 跨列，多了一次查找/排序，出現(xiàn) Using filesort */
explain select a1, a2, a3, a4 from test03 where a1=1 and a4=4 order by a3;

總結(jié)

  • 如果復(fù)合索引使用順序完全一致，索引全部使用
  • 如果部分一致，索引部分使用
  • where 和 order 拼接不要跨列

13. 單表優(yōu)化及總結(jié)

create table book
(
bid int(4) primary key,
name varchar(20) not null,
authorid int(4) not null,
publicid int(4) not null,
typeid int(4) not null
);

insert into book values(1, 'java', 1, 1, 2);
insert into book values(2, 'html', 2, 1, 2);
insert into book values(3, 'sql', 3, 2, 1);
insert into book values(4, 'C', 4, 4, 3);

commit;

/* type:All*/
/* Using where; Using filesort */
explain select bid from book where typeid in(2, 3) and authorid=1 order by typeid desc;

/* type:index */
/* Using where; Using index; Using filesort */
alter table book add index idx_bta(bid, typeid, authorid);

/* 為避免干擾，優(yōu)化之前刪除老的索引 */
drop index idx_bta on book;

/* 根據(jù) sql 實(shí)際解析的順序，調(diào)整索引順序 */
/* type:index */
/* Using where; Using index */
alter table book add index idx_tab(typeid, authorid, bid);


/* 刪除索引，創(chuàng)建新索引測(cè)試 */
drop index idx_tab on book;

/* 將出現(xiàn)范圍查詢的字段 typeid 放到后面 */
alter table book add index idx_atb(authorid, typeid, bid);

/* 將范圍查詢 typeid in (2, 3) 放到 authorid=1 后面 */
/* type:ref */
/* Using where; Using index */
/* key_len: 4 */
explain select bid from book where authorid=1 and typeid in(2, 3) order by typeid desc;

/* Using index */
/* key_len: 8 */
/* typeid in(2, 3) 改為 typeid=3，不使用范圍查詢，typeid 索引有效 */
/* 通過(guò) key_len 也可以佐證，此處有 2 個(gè)索引，typeid 索引有效 */
explain select bid from book where authorid=1 and typeid=3 order by typeid desc;

小結(jié)

  • 索引不能跨列使用，保持索引定義和使用順序一致性
  • 索引需要逐步優(yōu)化
  • 將含 in 的范圍查詢放到條件最后，防止整個(gè)索引失效
  • Using index
    • where authorid=1 ... authorid 在索引中，不需要回原表
  • Using where
    • ... and typeid in (2,3) typeid 在索引中，但是使用了 in 范圍查詢，索引失效，需要回原表

14~15. 多表優(yōu)化及總結(jié)，避免索引失效原則

兩張表

create table teacher2
(
tid int(4) primary key,
cid int(4) not null
);

insert into teacher2 values(1, 2);
insert into teacher2 values(2, 1);
insert into teacher2 values(3, 3);

create table course2
(
cid int(4),
cname varchar(20)
);

insert into course2 values(1, 'java');
insert into course2 values(2, 'python');
insert into course2 values(3, 'kotlin');
commit;

/* 左連接，將數(shù)據(jù)量少的表放到左邊 */
/* type:All */
/* Extra:  */
/* type:All */
/* Extra: Using where; Using join buffer  */
select * from teacher2 t left outer join course2 c 
on t.cid=c.cid where c.cname='java';

/* 增加索引 */
/* type: index */
/* Extra: Using index */
/* type: All */
/* Extra: Using where; Using join buffer*/
alter table teacher2 add index index_teacher2_cid(cid);

/* type: ref */
/* Extra: Using where */
/* type: ref */
/* Extra: Using index*/
alter table course2 add index index_course2_cname(cname);
  • 索引添加原則
    • 小表驅(qū)動(dòng)大表
    • 索引建立在經(jīng)常使用的字段上
    • 三表或更多表使用相同的原則
  • 左外連接，給左表加索引
  • 右外連接，給右表加索引
  • Using join buffer
    • mysql 引擎使用了連接緩存

避免索引失效的原則

/* 2 個(gè)索引都有效 */
/* type:ref */
/* Extra: */
/* key_len: 8 */
explain select * from book where authorid=1 and typeid=2;

/* 只有 1 個(gè)索引有效 */
/* type:ref */
/* Extra: using where */
/* key_len: 4 */
explain select * from book where authorid=1 and typeid*2=2;


/* 2 個(gè)索引都失效 */
/* type:All */
/* Extra: using where */
/* key_len: NULL */
explain select * from book where authorid*2=1 and typeid*2=2;

/* 2 個(gè)索引都失效，復(fù)合索引左邊失效，整個(gè)索引失效 */
/* type:All */
/* Extra: using where */
/* key_len: NULL */
explain select * from book where authorid*2=1 and typeid=2;

/* 刪除復(fù)合索引 */
drop index idx_atb on book;

alter table book add index idx_authorid(authorid);
alter table book add index idx_typeid(typeid);

/* 1 個(gè)索引都失效，獨(dú)立索引，第 1 個(gè)索引失效，不影響后面的索引 */
/* type:ref */
/* Extra: using where */
/* key_len: 4 */
explain select * from book where authorid*2 = 1 and typeid=2;

/* 索引有效 */
explain select * from book where authorid =1 and typeid =2;
/* 使用了不等于，索引失效 */
explain select * from book where authorid !=1 and typeid =2;
  • 復(fù)合索引，不要跨列或無(wú)序使用
  • 盡量使用全索引匹配
  • 不要在索引上進(jìn)行任何操作
    • 計(jì)算
    • 函數(shù)
    • 類(lèi)型轉(zhuǎn)換
    • 如 ... where a.x*3
  • 復(fù)合索引，左邊索引失效，所有索引失效
  • 復(fù)合索引使用不等于或者 is null，自身索引會(huì)失效，右側(cè)索引可能會(huì)失效
  • MySQL 本身有 sql 優(yōu)化器，實(shí)際優(yōu)化效果并非百分之百達(dá)到預(yù)期

索引優(yōu)化與預(yù)期不符合的情況

drop index idx_typeid on book;
drop index idx_authorid on book;

alter table book add index idx_book_at(authorid, typeid);

/* 復(fù)合索引全部使用 */
/* key_len:8 */
/* type: ref */
explain select * from book where authorid =1 and typeid =2;

/* where 中最左側(cè)的索引字段有 > 號(hào)，復(fù)合索引中自身及右側(cè)全部失效 */
/* type:All */
/* Extra: Using where */
/* key_len: NULL */
explain select * from book where authorid >1 and typeid =2;

/* 最右側(cè)索引使用了 > 號(hào)，復(fù)合索引沒(méi)有失效 */
/* type: range */
/* Extra: Using where */
/* key_len: 8 */
explain select * from book where authorid =1 and typeid>2;

/* 復(fù)合索引只有 1 個(gè)生效 */
/* type: range */
/* key_len: 4 */
/* Extra: Using where */
explain select * from book where authorid <1 and typeid=2;

/* 相比上一條 SQL，只將 authorid<1 改為 authorid<4，右側(cè)索引也失效 */
/* type: ALL */
/* key_len: NULL */
/* Extra: Using where */
explain select * from book where authorid <4 and typeid=2;

/* 使用百分號(hào)開(kāi)頭，索引失效 */
/* type: ALL */
/* key_len: NULL */
/* Extra: Using where */
explain select * from teacher where tname like '%x%';

/* 不使用百分號(hào)開(kāi)頭，索引仍然有效 */
/* type: range */
/* key_len: NULL */
/* Extra: Using where */
explain select * from teacher where tname like 'x%';

/* 使用百分號(hào)開(kāi)頭，但是實(shí)現(xiàn)索引覆蓋，仍然起到了一定的優(yōu)化作用 */
/* type: index */
/* key_len: 63 */
/* Extra: Using where; Using index */
explain select tname from teacher where tname like '%x%';

/* tname 和 'abc' 都是字符形式，索引有效 */
/* type: ref */
/* key_len: 63 */
/* Extra: Using where */
explain select * from teacher where tname = 'abc';

/* tname 是字符類(lèi)型，123 是整數(shù)，查找時(shí)有類(lèi)型轉(zhuǎn)換操作，導(dǎo)致索引失效 */
/* type: ALL */
/* key_len: NULL */
/* Extra: Using where */
explain select * from teacher where tname = 123;

/* 使用 and，索引仍然有效 */
/* type: ref */
/* key_len: 63 */
/* Extra: Using where */
explain select * from teacher where tname = '' and tcid>1;

/* 使用了 or，導(dǎo)致 or 左側(cè)的索引也失效 */
/* type: ALL */
/* key_len: NULL */
/* Extra: Using where */
explain select * from teacher where tname = '' or tcid>1;
  • 一般情況，范圍查詢之后的索引失效
  • 使用索引覆蓋，索引優(yōu)化會(huì)完全符合預(yù)期
  • like 盡量以常量開(kāi)頭，不以 '%' 開(kāi)頭，否則索引失效
  • 使用類(lèi)型轉(zhuǎn)換（顯式或隱式），會(huì)導(dǎo)致索引失效
  • 使用 or 會(huì)導(dǎo)致索引失效，甚至?xí)绊懽髠?cè)的索引

16. 常見(jiàn)的優(yōu)化方法及慢 SQL 排查

exist 和 in

/* 有數(shù)據(jù) */
select tname from teacher where exists(select * from teacher);

/* 無(wú)數(shù)據(jù) */
select tname from teacher where exists(select * from teacher where tid=9999);
  • 如果主查詢數(shù)據(jù)集大，使用 in
  • 如果子查詢數(shù)據(jù)集大，使用 exist
  • 將主查詢的結(jié)構(gòu)放到子查詢結(jié)果中進(jìn)行條件校驗(yàn)
    • 如果子查詢有數(shù)據(jù)，則校驗(yàn)成功
    • 如果符合校驗(yàn)，則保留數(shù)據(jù)

order by 優(yōu)化

  • Using filesort
    • 雙路排序 MySQL 4.1 之前
      • 掃描 2 次磁盤(pán)
      • 第 1 次
        • 從磁盤(pán)讀取排序字段
        • 對(duì)排序字段進(jìn)行排序
        • 在 buffer 中進(jìn)行排序
      • 第 2 次：掃描其他字段
    • 單路排序
      • 一次性讀取全部磁盤(pán)
      • 在 buffer 中進(jìn)行排序
      • 不一定是真正的單路，仍然可能是多次 IO
        • 數(shù)據(jù)量過(guò)大時(shí)，分片讀取
  • 單路排序比雙路排序占用更多 buffer
  • 調(diào)整 buffer
    • set max_length_for_sort_data=1024
  • 單路自動(dòng)切換到雙路的條件
    • 需要排序的列總大小超過(guò) set max_length_for_sort_data=1024 定義的字節(jié)數(shù)
  • 提供 order by 效率的策略
    • 選擇使用單路，雙路
    • 調(diào)整 buffer 容量大小
    • 避免使用 select *
    • 復(fù)合索引避免跨列
    • 保證全部排序字段順序的一致性

慢查詢?nèi)罩?/h2>
  • MySQL 用于記錄響應(yīng)時(shí)間超過(guò)閾值的 SQL 語(yǔ)句
  • long_query_time 閾值默認(rèn) 10 秒
  • 慢查詢?nèi)罩灸J(rèn)關(guān)閉
  • 建議在調(diào)優(yōu)時(shí)打開(kāi)，部署上線時(shí)關(guān)閉
  • 檢查是否開(kāi)啟了慢查詢?nèi)罩?
    • show variables like '%slow_query_log%';
  • 開(kāi)啟慢查詢?nèi)罩?
    • 臨時(shí)開(kāi)啟
      • set global slow_query_log =1;
      • mysql 服務(wù)重啟后失效
    • 永久開(kāi)啟
      • vi /etc/my.cnf
      • [mysqld] slow_query_log=1 slow_query_log_file=/var/lib/mysql/localhost-slow.log
  • 慢查詢閾值修改
    • show variables like '%long_query_time%';
    • 臨時(shí)修改
      • set global long_query_time=5;
      • 重新登錄后生效
    • 永久修改
      • vi /etc/my.cnf
      • [mysqld] long_query_time=3

17. 慢查詢閾值和 mysqldumpslow 工具

  • 休眠模擬慢查詢
    • select sleep(4);
  • 查詢超過(guò)閾值的 SQL 數(shù)量
    • show global status like '%slow_queries%';
  • 在 linux 命令行，通過(guò)日志查看慢查詢 SQL 的詳情
    • cat /var/lib/mysql/localhost-slow.log

通過(guò) mysqldumpslow 工具查看慢 SQL

/* 模擬慢查詢 */
select sleep(5);
select sleep(4);
select sleep(3);

/* 獲取返回記錄最多的 3 個(gè) SQL */
mysqldumpslow -s r -t 3 /var/lib/mysql/bigdata01-slow.log

/* 獲取訪問(wèn)次數(shù)最多的 3 個(gè) SQL */
mysqldumpslow -s c -t 3 /var/lib/mysql/bigdata01-slow.log

/* 按照時(shí)間排序，前 10 條包含 left join 查詢語(yǔ)句的 SQL */
mysqldumpslow -s t -t 10 -g "left join" /var/lib/mysql/bigdata01-slow.log
  • mysqldumpslow
  • 常用參數(shù)
    • s 排序方式
    • r 逆序
    • l 鎖定時(shí)間
    • g 正則匹配模式
  • 標(biāo)準(zhǔn)語(yǔ)法
    • mysqldumpslow 各種參數(shù) 慢查詢?nèi)罩疚募窂?/code>

18. 模擬并通過(guò) profiles 分析海量數(shù)據(jù)

模擬海量數(shù)據(jù)，存儲(chǔ)過(guò)程/存儲(chǔ)函數(shù)

create database testdata;
use testdata;

create table dept 
(
dno int(5) primary key default 0,
dname varchar(20) not null default '',
loc varchar(30) default ''
) engine=innodb default charset=utf8;

create table emp
(
eid int(5) primary key,
ename varchar(20) not null default '',
job varchar(20) not null default '',
deptno int(5) not null default 0
)engine=innodb default charset=utf8;

創(chuàng)建存儲(chǔ)函數(shù)

use testdata;
delimiter $
create function randstring(n int) returns varchar(255)
begin 

    declare all_str varchar(100) default 'abcdefghijklmnopqrestuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';
    declare return_str varchar(255) default '';
    declare i int default 0;
    while i<n
    do
        set return_str=concat(return_str, substring(all_str, FLOOR(1+rand()*52), 1));
        set i=i+1;
    end while;
    return return_str;
end $

沖突與解決

/* 開(kāi)啟慢查詢?nèi)罩?，再?chuàng)建存儲(chǔ)過(guò)程/存儲(chǔ)函數(shù)，報(bào)如下錯(cuò)誤   */
/* ERROR 1418 (HY000): 
This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA 
in its declaration and binary logging is enabled 
(you *might* want to use the less safe log_bin_trust_function_creators variable) */

/* 臨時(shí)解決 */
set global log_bin_trust_function_creators=1;
  • 永久解決
    • vi /etc/my.cnf
    • [mysqld] log_bin_trust_function_creators=1

通過(guò)存儲(chǔ)函數(shù)插入隨機(jī)整數(shù)

use testdata;
create function ran_num() returns int(5)
begin 

declare i int default 0;
set i=floor(rand()*100);
return i;

end$

通過(guò)存儲(chǔ)過(guò)程插入海量數(shù)據(jù)

emp 表

create procedure insert_emp(in eid_start int(10), in data_times int(10))
begin 
declare i int default 0;
set autocommit =0;

repeat 
insert into emp values(eid_start+i, randstring(5), 'other', ran_num());
set i=i+1;
until i=data_times
end repeat;

commit;

end $

dept 表

create procedure insert_dept(in dno_start int(10), in data_times int(10))
begin 
declare i int default 0;
set autocommit =0;

repeat 
insert into dept values(dno_start+i, randstring(6), randstring(8));
set i=i+1;
until i=data_times
end repeat;

commit;

end $

插入數(shù)據(jù)

delimiter ;

call insert_emp(1000, 800000);
call insert_dept(10, 30);

/* 驗(yàn)證插入數(shù)據(jù)量 */
select count(1) from emp;

分析海量數(shù)據(jù)

show variables like '%profiling%';
/* profiling 影響性能，在部署實(shí)施前，應(yīng)關(guān)閉此項(xiàng) */
set profiling=on;

/* 記錄 profiling 打開(kāi)之后的所有 SQL 語(yǔ)句消耗的時(shí)間 */
show profiles;

/* 精確查詢更多詳情，Query_Id 參考上個(gè)語(yǔ)句的查詢結(jié)果 */
show profile all for query 2;
show profile cpu, block io for query 2;

19. 全局查詢?nèi)罩?/h1> 
show variables like '%general_log%';

/* 開(kāi)啟全局日志，記錄開(kāi)啟之后的所有 SQL 語(yǔ)句 */
set global general_log=1;
/* 將日志記入表中 */
set global log_output='table';

/* 設(shè)置后執(zhí)行一條查詢 */
select count(1) from dept;

/* 顯示日志信息 */
select * from mysql.general_log;

/* 將日志記入文件 */
set global log_output='file';

/* 通過(guò)默認(rèn)保存地址查看日志文件 */
cat /var/lib/mysql/bigdata01.log;
  • 開(kāi)啟 general_log 后，所有 SQL 會(huì)被記錄到系統(tǒng)自帶的 mysql.general_log 表中

20. 鎖機(jī)制詳解

  • 解決因資源共享造成的并發(fā)問(wèn)題

分類(lèi)

  • 操作類(lèi)型
    • 讀鎖（共享鎖）
      • 對(duì)同一條數(shù)據(jù)，多個(gè)讀操作可以同時(shí)進(jìn)行，互不干擾
    • 寫(xiě)鎖（互斥鎖）
      • 如果當(dāng)前寫(xiě)操作沒(méi)有完畢，則無(wú)法進(jìn)行其他讀操作
  • 操作范圍
    • 表鎖
      • 對(duì)整張表加鎖
      • 開(kāi)銷(xiāo)小，加鎖快
      • 無(wú)死鎖
      • 容易發(fā)生鎖沖突
        • 同時(shí)操作一條數(shù)據(jù)的概率增高
      • 并發(fā)度低
      • MyISAM 采用表鎖
    • 行鎖
      • 對(duì)一條數(shù)據(jù)加鎖
      • 開(kāi)銷(xiāo)大，加鎖慢
      • 容易出現(xiàn)死鎖
      • 鎖的范圍較小，不易發(fā)生鎖沖突
      • 高并發(fā)概率低
      • InnoDB 行鎖
    • 頁(yè)鎖

表鎖

/* MYSQL/SQLSERVER 支持自增，Oracle 需要借助于序列來(lái)實(shí)現(xiàn)自增 */
create table tablelock
(
id int primary key auto_increment,
name varchar(20)
) engine myisam;

insert into tablelock(name) values('a1');
insert into tablelock(name) values('a2');
insert into tablelock(name) values('a3');
insert into tablelock(name) values('a4');
insert into tablelock(name) values('a5');

/* 查看加鎖情況 */
show open tables;

/* 加鎖 */
lock table tablelock read;

/* 加鎖后可以讀 */
select * from tablelock;

/* 加鎖后不能寫(xiě) */
/* ERROR 1099 (HY000): Table 'tablelock' was locked with a READ lock and can't be updated */
delete from tablelock where id=1;

/* 加鎖后，當(dāng)前會(huì)話不能對(duì)其他表進(jìn)行讀操作 */
/* ERROR 1100 (HY000): Table 'dept' was not locked with LOCK TABLES */
select count(1) from dept;

/* 加鎖后，當(dāng)前會(huì)話不能對(duì)其他表進(jìn)行寫(xiě)操作 */
/* ERROR 1100 (HY000): Table 'dept' was not locked with LOCK TABLES */
insert into dept values(39,'xxxxxx', 'yyyyyyyy');

/* 釋放鎖 */
unlock tables;
  • 會(huì)話
    • 每一個(gè)訪問(wèn)數(shù)據(jù)庫(kù)的 dos 命令行，數(shù)據(jù)庫(kù)客戶端工具，都是一個(gè)會(huì)話
  • 如果一個(gè)會(huì)話，對(duì) A 表加了 read 鎖
    • 該會(huì)話對(duì) A 表
      • 讀：可以
      • 寫(xiě)：不能
    • 該會(huì)話對(duì)其他表
      • 讀：不能
      • 寫(xiě)：不能
  • 此時(shí)其他會(huì)話
    • 對(duì) A 表
      • 讀：可以
      • 寫(xiě)，可以，需要等待鎖釋放
    • 對(duì)其他表
      • 讀：可以
      • 寫(xiě)：可以

21. 寫(xiě)鎖示例與 MyISAM 模式特征

/* 加寫(xiě)鎖 */
lock table tablelock write;

/* 不能對(duì)其他表進(jìn)行任何操作 */
/* ERROR 1100 (HY000): Table 'dept' was not locked with LOCK TABLES */
select count(1) from dept;
  • 對(duì) A 表加寫(xiě)鎖
    • 當(dāng)前會(huì)話對(duì) A 表
      • 可以進(jìn)行任何操作
    • 當(dāng)前會(huì)話對(duì)其他表
      • 不能進(jìn)行任何操作
  • 其他會(huì)話
    • 對(duì) A 表進(jìn)行操作的前提是等待寫(xiě)鎖釋放

MySQL 表級(jí)鎖的鎖模式

  • MyISAM 在執(zhí)行查詢語(yǔ)句前，會(huì)自動(dòng)給涉及的所有表加讀鎖
  • MyISAM 在執(zhí)行更新操作（DML）前，會(huì)自動(dòng)給涉及的表加寫(xiě)鎖
  • 對(duì) MyISAM 表進(jìn)行讀操作
    • 其他進(jìn)程對(duì)同一表的操作
      • 讀：不阻塞
      • 寫(xiě)：阻塞
    • 只有讀鎖釋放后，才會(huì)執(zhí)行其他進(jìn)程的寫(xiě)操作
  • 對(duì) MyISAM 表進(jìn)行寫(xiě)操作
    • 其他進(jìn)程對(duì)同一表操作
      • 讀：阻塞
      • 寫(xiě)：阻塞
    • 只有寫(xiě)鎖釋放后，才會(huì)執(zhí)行其他進(jìn)程的寫(xiě)操作

22. 表鎖情況分析及行鎖解析

  • 分析表鎖定
    • 查看哪些表加了鎖
      • show open tables;
    • 分析表鎖定的嚴(yán)重程度
      • show status like '%table%'
        • Table_locks_immediate 能夠獲取到的鎖
        • Table_locks_waited 需要等待的鎖
      • Table_locks_immediate/Table_locks_waited> 5000
        • 建議采用 InnoDB 引擎
        • 否則使用 MyISAM 引擎
        • 能夠獲取到的資源充分時(shí)，使用行鎖，因此采用 InnoDB

行鎖

create table linelock
(
id int(5) primary key auto_increment,
name varchar(20)
)engine=innodb;

insert into linelock(name) values('1');
insert into linelock(name) values('2');
insert into linelock(name) values('3');
insert into linelock(name) values('4');
insert into linelock(name) values('5');

set autocommit=0;
/* 當(dāng)前會(huì)話操作第 6 行 */
insert into linelock values(6, 'a6');
/* 其他會(huì)話操作第 6 行 */
/* 無(wú)法操作，需要等待鎖釋放 */
update linelock set name='ax' where id=6;
/* 其他會(huì)話操作第 8 行，沒(méi)有鎖，可以操作 */
insert into linelock values(8, 'a8');
  • 某個(gè)會(huì)話對(duì)一行數(shù)據(jù)進(jìn)行 DML 操作時(shí)，其他會(huì)話需要等待鎖釋放
  • 釋放鎖
    • 表鎖：unlock tables; 或 commit/rollback 事務(wù)提交
    • 行鎖：commit/rollback 事務(wù)提交

23. 行鎖的主意事項(xiàng)及使用情況分析

行鎖轉(zhuǎn)為表鎖

show index from linelock;
/* 為 name 列增加索引 */
alter table linelock add index idx_linelock_name(name);

/* 當(dāng)前會(huì)話操作 name='3' 的行 */
update linelock set name='a3x' where name='3';
/* 其他會(huì)話操作 name='4' 的行 */
/* name 列索引有效，不同的行操作互不影響 */
update linelock set name='a4x' where name='4';

/* 當(dāng)前會(huì)話操作 name=3 的行 */
/* name 列是 varchar 類(lèi)型，而 3 是整數(shù)類(lèi)型，類(lèi)型轉(zhuǎn)換時(shí)索引失效，行鎖轉(zhuǎn)為表鎖 */
update linelock set name='a3x' where name=3;
/* 其他會(huì)話操作 name='4' 的行 */
/* name 列索引失效，表被鎖定，無(wú)法操作 name='4' 行，需要等待鎖釋放 */
update linelock set name='a4x' where name='4';
  • 如果沒(méi)有索引，行鎖會(huì)轉(zhuǎn)為表鎖

間隙鎖

/* 不存在 id=7 的數(shù)據(jù)，此時(shí) MySQL 會(huì)自動(dòng)加上間隙鎖 */
update linelock set name='x' where id>1 and id<9;

/* 其他會(huì)話操作 id=7 需要等待鎖釋放 */
insert into linelock value(7, 'a7');
  • 行鎖的一種特殊情況
  • MySQL 會(huì)自動(dòng)給間隙加鎖

行鎖

  • 如果加鎖時(shí)有 where 語(yǔ)句，where 范圍內(nèi)的數(shù)據(jù)都會(huì)被加鎖
  • 并發(fā)能力強(qiáng)，效率高

行鎖分析

show status like '%innodb_row_lock%';
類(lèi)型 說(shuō)明
Innodb_row_lock_current_waits 當(dāng)前正在等待鎖的進(jìn)程數(shù)量
Innodb_row_lock_time 從系統(tǒng)啟動(dòng)到現(xiàn)在，等待總時(shí)長(zhǎng)
Innodb_row_lock_time_avg 從系統(tǒng)啟動(dòng)到現(xiàn)在，平均等待時(shí)長(zhǎng)
Innodb_row_lock_time_max 從系統(tǒng)啟動(dòng)到現(xiàn)在，最大等待時(shí)長(zhǎng)
Innodb_row_lock_waits 從系統(tǒng)啟動(dòng)到現(xiàn)在，等待次數(shù)

24. 查詢行鎖

/* for update 為查詢語(yǔ)句加鎖 */
select * from linelock where id=2 for update;

/* 其他會(huì)話操作該行要等待鎖釋放 */
update linelock set name='x' where id=2;
  • 通過(guò) for update 對(duì) query 語(yǔ)句加鎖
  • 關(guān)閉事務(wù)自動(dòng)提交的三種方式
    • set autocommit =0;
    • start transaction;
    • begin;
?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請(qǐng)聯(lián)系作者
【社區(qū)內(nèi)容提示】社區(qū)部分內(nèi)容疑似由AI輔助生成，瀏覽時(shí)請(qǐng)結(jié)合常識(shí)與多方信息審慎甄別。
平臺(tái)聲明：文章內(nèi)容（如有圖片或視頻亦包括在內(nèi)）由作者上傳并發(fā)布，文章內(nèi)容僅代表作者本人觀點(diǎn)，簡(jiǎn)書(shū)系信息發(fā)布平臺(tái)，僅提供信息存儲(chǔ)服務(wù)。

相關(guān)閱讀更多精彩內(nèi)容

友情鏈接更多精彩內(nèi)容