MySQL进阶之索引优化

#0. 简介

本文配合B站学习视频SQL优化^[1]使用效果更佳。

#1. MySQL版本

主流版本：5.x版

• 5.0 - 5.1：早期产品的延续，升级维护

• 5.4 - 5.x：MySQL整合了三方公司的新存储引擎（5.5）

安装：rpm -ivh xxx或tar -zxvf xxx.tar.gz

查看已有的相关文件：rpm -qa | grep xxx

安装过程中出现冲突时需将冲突的软件卸载掉：yum -y remove xxx或rpm -e --nodeps xxx

验证：mysqladmin --version

服务：

启动服务：service mysql start

关闭服务：service mysql stop

重启服务：service mysql restart

服务开机自启/关闭：chkconfig mysql on/off

登录：mysql

设置初始密码：/usr/bin/mysqladmin -u root password 'new-password'

授权远程连接：

授权：grant all privileges on *.* to '用户名' @'%' identified by '密码';

刷新权限：flush privileges;

开启防火墙服务：systemctl start firewalld.service

开启3306端口：firewall-cmd --zone = public --query-port = 3306/tcp

重新加载防火墙服务：firewall-cmd --reload

数据库存放目录：ps -ef|grep mysql

数据存放目录：datadir=/var/lib/mysql

pid文件目录：pid-file=/var/lib/mysql/chiaki01.pid

进入目录cd /var/lib/mysql，其中mysql和mysql.sock比较重要

MySQL核心目录：

MySQL安装目录：/var/lib/mysql

MySQL配置文件：/usr/share/mysql中的``my-huge.cnf、my-large.cnf`等

MySQL命令目录：/usr/bin，包含mysqladmin、musqldump等命令

MySQL启停脚本：/etc/init.d/mysql

MySQL配置文件目录：/etc/my.cnf，不存在就复制过来cp /usr/share/mysql/my-huge.cnf /etc/my.cnf

MySQL编码查询：show variables like '%char%';

统一编码为utf8：进入配置文件my.cnf进行修改

[mysql]：default-character-set=utf8

[client]：default-character-set=utf8

[server]：character_set_server=utf8，character_set_client=utf8，collation_server=utf8_general_ci

注意事项：修改编码只对修改之后的创建的数据库生效

MySQL清屏：system clear, ctrl+L

备注：5.5以上安装的一些命令

查看初始密码：cat /root/.mysql_sercet

安装完初始登录：mysql -uroot -p并输入密码

登录成功设置密码安全策略并修改密码（5.5以上）：

• 改变密码等级：set global validate_password_policy=0;
• 改变密码最小长度：set global validate_password_length=4;
• 修改密码：SET PASSWORD = PASSWORD('密码');

授权：grant all privileges on *.* to '用户名' @'%' identified by '密码';

刷新权限：flush privileges;

开放远程连接（关闭防火墙服务或者开放防火墙3306端口）

• 关闭防火墙服务：systemctl stop firewalld.service
• 开启防火墙服务：systemctl start firewalld.service
• 开启3306端口：firewall-cmd --zone = public --query-port = 3306/tcp
• 重新加载防火墙服务：firewall-cmd --reload
• 查看服务：firewall-cmd --list-all

详见：CentOS7安装MySQL5.7^[2]

#2. MySQL底层原理

#逻辑分层（自顶向下）

连接层：提供与客户端连接的服务

服务层：提供各种用户使用的接口（select等）；提供SQL优化器（MySQL Query Optimizer）

引擎层：提供了各种存储数据的方式（InnoDB和MyISAM等）

存储层：存储数据

#引擎区别

InnoDB：事务优先（适合高并发操作；行锁）（5.5及以上默认引擎）

MyISAM：性能优先（不支持事务；表锁）

#引擎相关SQL语句

查询数据库支持的引擎：show engines

查询当前使用的引擎：show variables like '%storage_engine%';

创建数据库对象时指定引擎：

CREATE TABLE tb (
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR (5),
dept VARCHAR (5)
) ENGINE = MYISAM AUTO_INCREMENT = 1 DEFAULT CHARSET = utf8 ;

指定数据库对象的引擎：show table status like "tb" ;

#3. SQL优化

#为什么要SQL优化

性能低

执行时间长

等待时间长

SQL语句欠佳（连接查询）

索引失效

服务器参数设置不合理（缓冲、线程）

#SQL解析

编写过程：select dinstinct...from...join...on...where...group by...having...order by...limit...

解析过程：from...on...join...where...group by...having...select dinstinct...order by...limit...

详见：步步深入：MySQL架构总览->查询执行流程->SQL解析顺序^[3]

#SQL优化之索引简介

索引（Index）：是帮助MySQL高效获取数据的数据结构（B树（MySQL默认）和哈希索引）。

B树中的2-3树：3层B树可以存放上百万条数据

B+树：B树的一种，是MySQL使用的索引结构，数据全部存放在叶节点中。B+树中给查询任意的数据次数为n次，即B+树的高度。

索引的弊端：

• 索引本身很大，可以存放在内存/硬盘（通常为硬盘）。
• 索引不是所有情况均适用：少量数据；频繁更新的字段；很少使用的字段。
• 索引确实可以提高查询效率，但是会降低增删改的效率。

索引的优势：

• 提高查询效率（降低IO使用率）
• 降低CPU使用率（...order by age desc，因为B树索引已经是一个排好序的结构）

#4. 索引

#索引分类

单值索引：单列，一个表可以有多个单值索引

唯一索引：在单值索引基础上，字段的值不可重复，一般为id

复合索引：多个列构成的索引（相当于二级目录）

#索引创建

创建索引（一）：create 索引类型 索引名 on 表名（字段）

单值索引：create index dept_index on tb(dept);

唯一索引：create unique index name_index on tb(name);

复合索引：create index dept_name_index on tb(dept,name);

创建索引（二）：alter table 表名 add 索引类型 索引名（字段）

单值索引：alter table tb add index dept_index(dept);

唯一索引：alter table tb add unique index name_index(name);

复合索引：alter table tb add index dept_name_index(dept,name);

#注意事项

如果一个字段设置为主键，则该字段默认就是主键索引，与唯一索引较类似，但存在区别。

主键索引不能是null，唯一索引可以为null。

#删除及查询索引

删除索引：drop index 索引名 on 表名

查询索引：show index from 表名

#5. SQL性能问题及优化

分析SQL的执行计划：explain，模拟SQL优化器执行SQL语句，使开发人员清除编写的SQL状况。SQL优化器会干扰优化。

#Explian查询执行计划

查询执行计划：explain + SQL语句

explain select * from tb;

• id：编号
• select_type：查询类型
• table：表
• type：类型
• possible_keys：预测用到的索引
• key：实际使用的索引
• ken_len：实际使用索引的长度
• ref：表之间的引用关系
• rows：通过索引查询到的数据记录数
• Extra：额外信息

案例：

建表并插入记录：

USE myDB;

-- 试验中先不设置主键和外键
CREATE TABLE teacherCard (
tcid INT,
tcdesc VARCHAR (30)
) ;

CREATE TABLE teacher (
tid INT ,
tname VARCHAR (20),
tcid INT
) ;

CREATE TABLE course (
cid INT ,
cname VARCHAR (20),
tid INT
) ;

INSERT INTO teacherCard VALUES(1,'tzdesc');
INSERT INTO teacherCard VALUES(2,'twdesc');
INSERT INTO teacherCard VALUES(3,'tldesc');

INSERT INTO teacher VALUES(1,'tz',1);
INSERT INTO teacher VALUES(2,'tw',2);
INSERT INTO teacher VALUES(3,'tl',3);

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);

explain + SQL语句：

练习：查询课程编号为2或教师证编号为3的老师信息

EXPLAIN
SELECT 
t.* 
FROM
teacher t,
course c,
teacherCard tc 
WHERE t.tid = c.tid 
AND t.tcid = tc.tcid 
AND (c.cid = 2 
 OR tc.tcid = 3) ;

#id：编号

• id值相同，从上往下顺序执行。

执行顺序t(3)-tc(3)-c(4)（括号中表示表中的记录数）。现向teacher标值再插入3条数据，并执行同样的SQL语句。

INSERT INTO teacher VALUES(4,'ta',4);
INSERT INTO teacher VALUES(5,'tb',5);
INSERT INTO teacher VALUES(6,'tc',6);

EXPLAIN
SELECT 
t.* 
FROM
teacher t,
course c,
teacherCard tc 
WHERE t.tid = c.tid 
AND t.tcid = tc.tcid 
AND (c.cid = 2 
 OR tc.tcid = 3) ;

上图结果中，执行顺序变为：tc(3)-c(4)-t(6)

表的执行顺序因表中记录数的改变而改变，其原因在于：笛卡尔积。记录数最小的表优先查询，使中间笛卡尔积最小。

验证：删除course表中的两条记录，再次执行查看结果：

DELETE FROM course WHERE cid > 2;

EXPLAIN
SELECT 
t.* 
FROM
teacher t,
course c,
teacherCard tc 
WHERE t.tid = c.tid 
AND t.tcid = tc.tcid 
AND (c.cid = 2 
 OR tc.tcid = 3) ;

上图结果中，执行顺序变为：c(2)-tc(3)-t(6)

• id值不同，id值越大的越优先执行（本质：在嵌套子查询时，先查内层，在查外层）。

练习：查询教授SQL课程的老师的描述。

EXPLAIN
SELECT 
tc.tcdesc 
FROM
teacherCard tc,
course c,
teacher t 
WHERE t.tid = c.tid 
AND t.tcid = tc.tcid 
AND c.cname = 'SQL' ;

将以上多表查询转为子查询形式

EXPLAIN
SELECT 
tc.tcdesc 
FROM
teacherCard tc 
WHERE tcid = 
(SELECT 
 t.tcid 
FROM
 teacher t 
WHERE t.tid = 
 (SELECT 
   c.tid 
 FROM
   course c 
 WHERE c.cname = 'SQL')) ;

id值越大的先执行，执行顺序为：c(2)-t(6)-tc(3)

• id值有相同有不同：id值越大越优先，如果id值相同，从上往下依次执行。

综合：子查询+多表：查询教授SQL课程的老师的描述。

EXPLAIN
SELECT 
t.tname,
tc.tcdesc 
FROM
teacher t,
teacherCard tc 
WHERE t.tcid = tc.tcid 
AND t.tid = 
(SELECT 
 c.cid 
FROM
 course c 
WHERE c.cname = 'SQL') ;

上图结果中，执行顺序变为：c(2)-tc(3)-t(6)

#select_type：查询类型

PRIMARY：包含子查询SQL中的主查询（最外层）

SUBQUERY：包含子查询SQL中的子查询（非最外层）

SIMPLE：简单查询（不包含子查询和union连接查询）

EXPLAIN SELECT * FROM teacher t;

DERIVED：衍生查询（使用到了临时表）

UNION：见下例

UNION RESULT：告知开发者哪些表存在UNION查询，见下例

• 在FROM子查询中只有一张表：查询教课老师编号是1或2的课程信息。

  EXPLAIN
  SELECT
  cr.cname
  FROM
  (SELECT
  *
  FROM
  course
  WHERE tid IN (1, 2)) cr ;
  

  

• 在FROM子查询中，如果table1 union table2，则table1（左表）就是DERIVED，而table2就是UNION：查询教课老师编号是1或2的课程信息。

  EXPLAIN
  SELECT
  cr.cname
  FROM
  (SELECT
  *
  FROM
  course
  WHERE tid = 1
  UNION
  SELECT
  *
  FROM
  course
  WHERE tid = 2) cr ;
  

  

#type：索引类型、类型

常用type：

system > const > eq_ref > ref > range > index > all，性能依次降低，其中system和const只是理想情况，实际能达到最高为ref。要对type进行优化的前提是要 有索引。

• system：只有一条数据的系统表或衍生表只有一条数据的主查询。

  -- 创建test01表
  CREATE TABLE test01 (tid INT, tname VARCHAR (20));
  -- test01表中插入1条数据
  INSERT INTO test01 VALUES(1,'a');
  -- 添加主键索引（优化type的前提）
  ALTER TABLE test01 ADD CONSTRAINT tid_pk PRIMARY KEY(tid);
  -- 分析执行计划（衍生表只有1条数据）
  EXPLAIN SELECT * FROM (SELECT * FROM test01) t WHERE t.tid = 1;
  

  

  上图中衍生表的type为system。

• const：仅能查到一条数据的SQL，用于Primary key或Unique索引（与索引类型有关）

   EXPLAIN SELECT t.tid FROM test01 t WHERE t.tid = 1;
  

  

  上图中由于仅能查到1条数据，同时用于主键索引，因此type为const。

  -- 删除主键索引
  ALTER TABLE test01 DROP PRIMARY KEY;
  -- 添加单值索引
  ALTER TABLE test01 ADD INDEX test01_index(tid);
  -- 再次分析执行计划
  EXPLAIN SELECT t.tid FROM test01 t WHERE t.tid = 1;
  

  

  上图中仅能查到1条数据，但用于一般单值索引，因此type不是const。

• eq_ref：唯一性索引，即对于每个索引键的查询，返回唯一匹配行数据（有且仅有一个），常见于唯一索引和主键索引。

   -- teacherCard表设置主键
   ALTER TABLE teacherCard ADD CONSTRAINT tcid_pk PRIMARY KEY(tcid);
   -- teacher表设置唯一键约束
   ALTER TABLE teacher ADD CONSTRAINT uk_tcid UNIQUE INDEX(tcid);
   -- 连接查询
   SELECT t.tcid FROM teacher t, teacherCard tc WHERE t.tcid = tc.tcid;
   -- 分析执行计划
   EXPLAIN SELECT t.tcid FROM teacher t, teacherCard tc WHERE t.tcid = tc.tcid;
   -- 查询teacher表
   SELECT * FROM teacher;
  

  

  上图中type的结果不是eq_ref，其原因在于不满足有且仅有一个，因为在teacher表中的tid唯一索引的返回的结果有6条，而连接查询返回的结果只有3条，所以不满足条件。

  

  删除后teacher表的后三条数据再次分析执行计划：

  -- 删除后三条数据
  DELETE FROM teacher WHERE tid > 3;
  -- 分析执行计划
  EXPLAIN SELECT t.tcid FROM teacher t, teacherCard tc WHERE t.tcid = tc.tcid;
  

  

  上图结果中type为eq_ref。以上SQL，用到的索引是t.tcid，即teacher表中的tcid字段。如果teacher表的数据个数和链接连接查询的数据个数一直，才能满足eq_ref级别。

• ref：非唯一性索引：对于每隔索引键的索引，返回匹配的所有行。

   -- 数据准备使得teacher表中tname列中存在重复tz
   INSERT INTO teacher VALUES(4,'tz',4);
   INSERT INTO teacherCard VALUES(4,'tz2222');
   -- 创建teacher表tname列的索引
   ALTER TABLE teacher ADD INDEX tname_index(tname);
   -- 使用tname作为索引进行查询
   SELECT * FROM teacher t WHERE t.tname = 'tz';
   -- 分析执行计划
   EXPLAIN SELECT * FROM teacher t WHERE t.tname = 'tz';
  

  

  上图的结果中type为ref。

• range：检索指定范围的行，where后是一个范围查询（between...and...，>，< 等），其中范围查询使用in时，有可能失效转为无索引all。

   -- teacher表的tid列添加索引
   ALTER TABLE teacher ADD INDEX tid_index(tid);
   -- 分析执行计划
   EXPLAIN SELECT t.* FROM teacher t WHERE t.tid IN (1, 2); -- 失效 变为all
   EXPLAIN SELECT t.* FROM teacher t WHERE t.tid < 3; -- range
   EXPLAIN SELECT t.* FROM teacher t WHERE t.tid > 3; -- range
   EXPLAIN SELECT t.* FROM teacher t WHERE t.tid BETWEEN 1 AND 2; -- range
  

  

• index：查询全部索引中的数据

   -- 查询teacher表中tid列的所有数据（确保tid列已有索引） 只需扫描索引表
   EXPLAIN SELECT t.tid FROM teacher t; -- type为index
  

  

• all：查询全部表中的数据

   -- course表没有索引 需要全表扫描
   EXPLAIN SELECT c.cid FROM course c; -- type为all
  

  

总结：

• system/const：结果只有一条数据。
• eq_ref：结果多条，但每条数据有且仅有一条（不能为0也不能为多）。
• ref：结果多条名单每条数据是0或多（唯一则为eq_ref）。

#possible_keys：可能用的索引

#key：实际用的索引

possible_keys是一种预测，不准。如果possible_keys和key是null，表示没有使用索引。

Eg1：

-- 确保添加索引
-- 将未添加索引的字段添加索引
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.cid 
FROM
 course c 
WHERE c.cname = 'SQL') ;

Eg2：

-- 确保添加索引
-- 分析执行计划
EXPLAIN 
SELECT 
tc.tcdesc 
FROM
teacherCard tc,
course c,
teacher t 
WHERE t.tid = c.tid 
AND t.tcid = tc.tcid 
AND c.cname = 'SQL' ;

#key_len：索引的长度

作用：用于判断复合索引是否被完全使用。

常识：

• utf8：1个字符3个字节
• gbk：1个字符2个字节
• latin：1个字符1个字节

固定长度的索引类型：

Eg1-1：

-- 创建test_kl表用于key_len的试验
CREATE TABLE test_kl (NAME CHAR(20) NOT NULL DEFAULT '')； -- name字段非空
-- 添加单值索引
ALTER TABLE test_kl ADD INDEX name_index(NAME);
-- 分析执行计划
EXPLAIN SELECT * FROM test_kl WHERE NAME = '';  -- key_len = 60

结果key_len = 60。原因是在utf8中一个char类型字符占3个字节，所以60 = 3 * 20。

Eg1-2：

-- 添加字段
ALTER TABLE test_kl ADD COLUMN name1 CHAR(20); -- name1字段可以为空
-- 添加单值索引
ALTER TABLE test_kl ADD INDEX name1_index(name1);
-- 分析执行计划
EXPLAIN SELECT * FROM test_kl WHERE name1 = ''; -- key_len = 61

结果key_len = 61。如果索引字段可以为null，则会使用1个字节作为标识。

Eg1-3：

-- 删除索引
DROP INDEX name_index ON test_kl;
DROP INDEX name1_index ON test_kl;
-- 添加复合索引
ALTER TABLE test_kl ADD INDEX name_name1_index(NAME,name1);
-- 分析执行计划 使用name1字段
EXPLAIN SELECT * FROM test_kl WHERE name1 = ''; -- key_len = 121
-- 分析执行计划 使用name字段
EXPLAIN SELECT * FROM test_kl WHERE name = ''; -- key_len = 60

使用复合索引查询时，使用name字段导致复合索引没有被完全使用，使得key_len = 60 ，使用name1字段使得复合索引被完全使用，key_len = 121。

可变长度的索引类型：

Eg2：

-- 添加新字段
ALTER TABLE test_kl ADD COLUMN name2 VARCHAR(20); -- 可以为null
-- 添加单值索引
ALTER TABLE test_kl ADD INDEX name2_index(name2);
-- 分析执行计划
EXPLAIN SELECT * FROM test_kl WHERE name2 = ''; -- key_len = 63

结果key_len = 63。原因是63 = 3 * 20 + 1 (标识null) + 2 (标值可变长度)。

#ref：表之间的引用关系

作用：指明当前表所参照的字段。注意与type中的ref值区分。

Eg：

-- 分析执行计划
EXPLAIN 
SELECT 
  * 
FROM
  course c,
  teacher t 
WHERE c.tid = t.tid 
  AND t.tname = 'tw' ;

上图结果中，where后的条件包含两部分c.tid = t.tid以及t.tname = 'tw'。对于前一部分，c.tid参照的字段为t表中的t.tid ，由于c.tid未设置索引，所以ref的值为null；对于后一部分，t.tname参照的字段为'tw'，是一个给定的常量，所以ref的值为const。

给t表中的c.tid添加索引后重新分析执行计划：

-- course表的tid字段添加索引
ALTER TABLE course ADD INDEX tid_index(tid);
-- 分析执行计划
EXPLAIN 
SELECT 
  * 
FROM
  course c,
  teacher t 
WHERE c.tid = t.tid 
  AND t.tname = 'tw' ;

添加索引后，c.tid = t.tid条件中c表的c.tid字段参照了t表的t.tid字段，所以ref的值为myDB.t.tid。

#rows：通过索引查询的记录数

Eg：

-- 分析执行计划
EXPLAIN 
SELECT 
  * 
FROM
  course c,
  teacher t 
WHERE c.tid = t.tid 
  AND t.tname = 'tz' ;
-- 查询
SELECT * FROM course c, teacher t WHERE c.tid = t.tid AND t.tname = 'tz';
-- 查询c表
SELECT * FROM course;
-- 查询t表
SELECT * FROM teacher;

上图结果中，c表通过索引查询得到的记录数为2条，所以c表的rows值为2；虽然执行查询语句得到了t 表的2条记录，但是其是重复的，真正通过t表索引查询得到的记录只有1条，所以t表的rows值为1。

#Extra：额外信息

常见信息：

• Using filesort：性能消耗大；需要**“额外”**的一次排序（查询），常见于order by语句中。

  Eg1：单值索引

   -- 创建新表
   -- 创建新表并添加单值索引
   CREATE TABLE test02 (
   a1 CHAR(3),
   a2 CHAR(3),
   a3 CHAR(3),
   INDEX idx_a1 (a1),
   INDEX idx_a2 (a2),
   INDEX idx_a3 (a3)
   ) ;
   -- 分析执行计划
   EXPLAIN SELECT * FROM test02 WHERE a1 = '' ORDER BY a1;
   EXPLAIN SELECT * FROM test02 WHERE a1 = '' ORDER BY a2;
  

  

  上图结果中，按字段a1排序时Extra的值为Using where，按字段a2排序时Extra的值为Using where; Using filesort。由于查询的字段是a1，当按a1排序时，就按照查出来的结果排序即可，然而当按a2排序时就需要以a2为字段进行一次额外的查询，然后将查询的结果排序，所以Extra的信息中包含Using filesort。

  小结：对于单索引，如果排序和查找是同一字段，则不会出现Using filesort的情况，反之则会出现。为了避免这一问题，可以采用如下方法：where哪些字段就order by哪些字段。

  Eg2：复合索引（满足最左前缀原则）

  -- 删除单值索引
  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);
  -- 分析执行计划
  EXPLAIN SELECT * FROM test02 WHERE a1 = '' ORDER BY a3; -- Using filesort
  EXPLAIN SELECT * FROM test02 WHERE a2 = '' ORDER BY a3; -- Using filesort
  EXPLAIN SELECT * FROM test02 WHERE a1 = '' ORDER BY a2;
  

  

  小结：对于复合索引，为了避免出现Using filesort，where和order by按照复合索引的顺序使用，不要跨列或无序使用。

• Using temporary：性能损耗大，用到了临时表，常见于group by语句中。

  Eg1：

   -- 分析执行计划
   EXPLAIN SELECT a1 FROM test02 WHERE a1 IN ('1','2','3') GROUP BY a1;
   EXPLAIN SELECT a1 FROM test02 WHERE a1 IN ('1','2','3') GROUP BY a2; -- Using temporary
  

  

  上图结果中，以a1索引对字段a1进行查询却按字段a2进行分组，导致需要用到临时表，Extra中出现Using temporary。要避免这种情况可以采用如下方法：查询哪些列就根据那些列group by。

• Using index：性能提升；覆盖索引。原因：出现Using index，说明不读取原文件，只从索引文件中获取数据，即不需要回表查询。只要是用到的列全部都在索引中，就是覆盖索引。

  Eg1：test02表中存在复合索引（idx_a1_a2_a3）；正例

   -- 分析执行计划
   EXPLAIN SELECT a1, a2 FROM test02 WHERE a1 = '' OR a2 = '';
  

  

  上图结果中，由于使用的字段a1和字段a2均包含在复合索引中，是覆盖索引，因此Extra中出现Using index。

  Eg2：反例

  -- 删除复合索引
  DROP INDEX idx_a1_a2_a3 ON test02;
  -- 添加字段a1和a2的复合索引
  ALTER TABLE test02 ADD INDEX idx_a1_a2(a1, a2);
  -- 分析执行计划
  EXPLAIN SELECT a1, a3 FROM test02 WHERE a1 = '' OR a3 = '';
  

  

  上图结果中，使用了字段a1和a3进行查询，而复合索引中不包含字段a3，因此不是覆盖索引，所以Extra中不会出现Using index。

  Eg3： 覆盖索引会对其他属性产生影响

  -- 分析执行计划
  EXPLAIN SELECT a1, a2 FROM test02 WHERE a1 = '' OR a2 = '';
  EXPLAIN SELECT a1, a2 FROM test02;
  

  

  如果使用覆盖索引（Using index），会对possible_keys和key造成影响：

  • 若没有where，则索引只出现在key中；
  • 如果没有索引，则索引出现在possible_keys和key中。

• Using where：可能需要回表查询

   -- 分析执行计划
  EXPLAIN SELECT a1, a3 FROM test02 WHERE a3 = ''; -- 需要回表查询
  

  

  上图结果中，字段a3不在索引中，因此需要回表查询，Extra的信息为Using where。

  -- 分析执行计划
  EXPLAIN SELECT a1, a2 FROM test02 WHERE a1 = '' OR a2 = '';
  

  

  上图结果中使用了覆盖索引，所以Extra中包含了Using index，但同时Extra的信息中还出现了Using where，其实此时并未发生回表查询。Using index和Using where一起出现时一定不发生回表查询。

  备注：

  • Using index condition与ICP(index condition pushdown)（MySQL5.6新特性）
  • 需要回表查询
  • 详见：索引条件下推^[4]

• Impossible WHERE：where子句永远为false

   -- 分析执行计划
   EXPLAIN SELECT * FROM test02 WHERE a1 = 'x' AND a1 = 'y'; -- where子句永远为false，出现Impossible where
  

  

#6. 优化案例

#单表优化

准备：

-- 创建book表
CREATE TABLE book (
bid INT PRIMARY KEY,
NAME VARCHAR (20) NOT NULL,
authorId INT NOT NULL,
publicId INT NOT NULL,
typeId INT NOT NULL
) ;

-- 插入数据
INSERT INTO book VALUES(1, 'tjava', 1, 1, 2);
INSERT INTO book VALUES(2, 'tc', 2, 1, 2);
INSERT INTO book VALUES(3, 'wx', 3, 2, 1);
INSERT INTO book VALUES(4, 'math', 4, 2, 3);

查询：typeId =2或typeId=3且authorID=1的bid

-- 查询
SELECT bid FROM book WHERE typeId IN (2, 3) AND authorId = 1;
-- 分析执行计划
EXPLAIN SELECT bid FROM book WHERE typeId IN (2, 3) AND authorId = 1;
EXPLAIN SELECT bid FROM book WHERE typeId IN (2, 3) AND authorId = 1 ORDER BY typeId DESC;

从结果可以看出，该查询语句性能较低，需要优化。

优化1：添加复合索引

-- 添加索引
ALTER TABLE book ADD INDEX idx_bid_tid_aid(bid, typeId, authorId);
-- 分析执行计划
EXPLAIN SELECT bid FROM book WHERE typeId IN (2, 3) AND authorId = 1 ORDER BY typeId DESC;

从结果看出，type由all提升为index，Extra的信息中出现Using index，但是Using filesort仍然存在，继续优化。

优化2：按照SQL的实际解析顺序调整索引顺序，重新添加索引。

-- 删除索引
DROP INDEX idx_bid_tid_aid ON book;
-- 按解析顺序添加索引
ALTER TABLE book ADD INDEX idx_tid_aid_bid(typeId, authorId, bid);
-- 分析执行计划
EXPLAIN SELECT bid FROM book WHERE typeId IN (2, 3) AND authorId = 1 ORDER BY typeId DESC;

上图结果中，Extra中的信息中只有Using index和Using where ，是覆盖索引，不需要回表查询，效率提升。同时覆盖索引对possible_keys和key产生了影响。

优化3：提升type级别。因为使用范围查新时in有时会失效，因此交换索引的顺序，同时改变查询语句where子句的顺序。

-- 删除索引
DROP INDEX idx_tid_aid_bid ON book;
-- 按解析顺序添加索引
ALTER TABLE book ADD INDEX idx_aid_tid_bid(authorId, typeId, bid);
-- 分析执行计划
EXPLAIN SELECT bid FROM book WHERE  authorId = 1 AND typeId IN (2, 3) ORDER BY typeId DESC;

上图结果中，type由index提升至ref，性能进一步提升。

小结：

• 最左前缀原则，保持索引的定义和使用的顺序一致性；
• 索引需要逐步优化；
• 将含in的范围查询放到where子句最后防止失效。

#两表优化

准备：

-- 创建表
CREATE TABLE teacher2 (tid INT PRIMARY KEY, cid INT NOT NULL) ;
CREATE TABLE course2 (cid INT, cname VARCHAR (20)) ;
-- 插入数据
INSERT INTO course2 VALUES(1,'java');
INSERT INTO course2 VALUES(2,'python');
INSERT INTO course2 VALUES(3,'kotlin');

INSERT INTO teacher2 VALUES(1,2);
INSERT INTO teacher2 VALUES(2,1);
INSERT INTO teacher2 VALUES(3,3);

Eg：左连接添加索引进行优化

小表驱动大表：where 小表.x = 大表.x

索引建立在经常使用的字段上

-- 不加索引分析执行计划
EXPLAIN SELECT * FROM teacher2 t LEFT OUTER JOIN course2 c ON t.cid = c.cid WHERE c.cname = 'java';
-- 添加索引
ALTER TABLE teacher2 ADD INDEX index_teacher2_cid(cid);
-- 添加索引分析执行计划
EXPLAIN SELECT * FROM teacher2 t LEFT OUTER JOIN course2 c ON t.cid = c.cid WHERE c.cname = 'java';

上图结果中，添加索引后t表的type由all提升至index，同时t表的Extra信息为Using index。c 表的Extra中出现Using join buffer表明MySQL引擎使用了连接缓存。

Eg：继续添加索引

-- 添加索引
ALTER TABLE course2 ADD INDEX index_course2_cname(cname);
-- 分析执行计划
EXPLAIN SELECT * FROM teacher2 t LEFT OUTER JOIN course2 c ON t.cid = c.cid WHERE c.cname = 'java';

上图结果中，c表和t表的type均提升至ref。

#三表优化

原则：

• 小表驱动大表
• 索引建立在经常查询的字段上

#7. 避免索引失效的原则

原则：

• 复合索引

  • 复合索引不要跨列或无序使用（最左前缀原则）；
  • 复合索引尽量使用全索引匹配。

• 不要在索引上进行任何操作（计算、函数、类型转换），否则索引失效。

  Eg：

   -- 查看索引
   SHOW INDEX FROM book;
   -- 分析执行计划
   EXPLAIN SELECT * FROM book WHERE authorId = 1 AND typeId = 2;
   EXPLAIN SELECT * FROM book WHERE authorId = 1 AND typeId * 2 = 2;
   EXPLAIN SELECT * FROM book WHERE authorId * 2 = 1 AND typeId * 2 = 2;
   EXPLAIN SELECT * FROM book WHERE authorId * 2 = 1 AND typeId = 2;
  

  

  上图结果中，通过key_len可以清楚地看出对索引进行操作导致索引失效。值得注意地是，复合索引中，如果左侧失效，其右侧全部失效（最左前缀）。

• 复合索引中不能使用不等于（!= ，<>）或is null（is not null），否则自身以及右侧索引全部失效。由于SQL优化器的原因，大多情况下，范围查询（>, <, in）之后的索引失效。

  Eg：

   -- 删除添加索引
   DROP INDEX idx_aid_tid_bid ON book;
   ALTER TABLE book ADD INDEX idx_authorId(authorId);
   ALTER TABLE book ADD INDEX idx_typeId(typeId);
   -- 分析执行计划
   EXPLAIN SELECT * FROM book WHERE authorId = 1 AND typeId = 2;
   EXPLAIN SELECT * FROM book WHERE authorId <> 1 AND typeId = 2;
   EXPLAIN SELECT * FROM book WHERE authorId <> 1 AND typeId <> 2;
  

  

  由于MySQL服务层中SQL优化器的存在，SQL优化是一种概率层面的优化。实际中是否使用优化，需要通过explain进行推测。因此在第一次查询结果中，理想情况下应该是使用idx_authorId和idx_typeId两个索引，但实际中只使用了idx_authorId。第二次查询中由于对idx_authorId使用了不等于操作，使得idx_authorId索引失效，而使用了idx_typeId索引。第三次查询中，两个索引都进行了不等于操作，使得索引都失效。

  SQL优化器影响的例子：

  -- 删除添加索引
  DROP INDEX idx_authorId ON book;
  DROP INDEX idx_typeId ON book;
  ALTER TABLE book ADD INDEX idx_aid_tid(authorId, typeId);
  -- 分析执行计划
  -- 复合索引全部使用
  EXPLAIN SELECT * FROM book WHERE authorId = 1 AND typeId = 2;
  -- 复合索引全部失效
  EXPLAIN SELECT * FROM book WHERE authorId > 1 AND typeId = 2;
  -- 复合索引全部使用
  EXPLAIN SELECT * FROM book WHERE authorId = 1 AND typeId > 2;
  -- 复合索引部分失效
  EXPLAIN SELECT * FROM book WHERE authorId < 1 AND typeId = 2;
  -- 复合索引全部失效
  EXPLAIN SELECT * FROM book WHERE authorId < 4 AND typeId = 2;
  

  

• 尽量使用覆盖索引（Using index）。

• like尽量以常量开头，不要以%开头，否则索引失效。

   -- 查看索引
  SHOW INDEX FROM teacher;
   -- 分析执行计划
   EXPLAIN SELECT * FROM teacher WHERE tname LIKE 'x%';
   EXPLAIN SELECT * FROM teacher WHERE tname LIKE '%x%';
   EXPLAIN SELECT tname FROM teacher WHERE tname LIKE '%x%';
  

  

  上图结果中由于在like后面以%开头导致索引失效。如果必须要like后面以%开头，可以使用覆盖索引（Using index）。

• 尽量不要使用类型转换（显示、隐式），否则索引失效。

   -- 分析执行计划
  EXPLAIN SELECT * FROM teacher WHERE tname = 'abc';
   EXPLAIN SELECT * FROM teacher WHERE tname = 123;
  

  

  上图结果中，程序底层将123转换为'123'，即进行了类型转换，因此索引失效。

• 尽量不要使用or，否则索引失效。

   -- 分析执行计划
   EXPLAIN SELECT * FROM teacher WHERE tname = '' AND tcid > 1;
   EXPLAIN SELECT * FROM teacher WHERE tname = '' OR tcid > 1;
  

  

  上图结果中，在使用了or之后，索引失效。

#8. 一些其他的优化方法

#EXIST和IN

exist和in：如果主查询的数据集大，则使用in，效率高；如果子查询的数据集大，则使用exist，效率高。

exist语法：将主查询的结果放到子查询中进行条件校验（看子查询是否有数据，如果有数据，则校验成功），如果校验成功则保留查询结果，否则不保留。

SELECT tname FROM teacher WHERE EXISTS (SELECT * FROM teacher); -- 有效
SELECT tname FROM teacher WHERE EXISTS (SELECT * FROM teacher WHERE tid = 60); -- 失效

#ORDER BY

order by优化：常出现Using filesort，Using filesort有两种排序算法：双路排序、单路排序（根据IO的次数）。

MySQL4.1之前，默认使用双路排序：扫描2次磁盘，即（1）从磁盘读取排序字段并在缓冲区中进行排序；（2）扫描其他字段。MySQL4.1之后，为了减少IO访问次数消耗性能，默认使用单路排序：只扫描一次磁盘，即 一次读取全部字段并在缓冲区进行排序，但存在隐患（实际上不一定真的是一次IO，可能是多次IO）。原因在于如果数据量特别大则无法将所有数据一次性读取完毕，因此会进行分片多次读取。

注意：

• 单路排序比双路排序会占用更多的缓冲区（buffer）；

• 单路排序在使用时，如果数据量特别大，可以考虑扩增buffer的容量大小

   -- 调整buffer的容量大小 单位byte
   SET max_length_for_sort_data = 1024;
  

• 如果需要排序的数据（order by 后的字段）总大小超过了max_length_for_sort_data定义的字节数，那么MySQL会自动由单路排序切换为双路排序。

提高order by查询效率的策略：

• 选择使用单路排序或双路排序，调整buffer容量的大小；
• 尽量避免select * ...语句；
• 复合索引不要跨列使用，避免出现Using filesort；
• 尽量保证全部排序字段的排序一致性（都是升序或都是降序）。

#9. SQL排查

#慢查询日志

MySQL提供的一种日志记录，用于记录MySQL中响应时间超过阀值的SQL语句（long_query_time：默认10秒）。

慢查询日志默认关闭，在开发调优是建议打开，最终部署时关闭。

检查是否开启了慢查询日志以及开启慢查询日志：

-- 检查是否开启
SHOW VARIABLES LIKE '%slow_query_log%';
-- 临时开启，重启MySQL服务失效
SET GLOBAL slow_query_log = 1;
-- 永久开启
-- 在/etc/my.cnf配置文件中的[mysqld]后追加配置：
-- slow_query_log = 1
-- slow_query_log_file = /vaar/lib/mysql/localhost-slow.log

查询并修改慢查询阀值：

-- 查询慢查询阀值
SHOW VARIABLES LIKE '%long_query_time%';
-- 临时设置慢查询阀值
-- 设置完毕后重新登录生效
SET GLOBAL long_query_time = 3;
-- 永久开启
-- 在/etc/my.cnf配置文件中的[mysqld]后追加配置：
-- long_query_time = 3

Eg：

-- 查询慢查询阀值
SHOW VARIABLES LIKE '%long_query_time%';
-- 查询线程休眠4秒
SELECT SLEEP(4);
-- 查看响应时间超过慢查询阀值的SQL条数
SHOW GLOBAL STATUS LIKE '%slow_queries%';

查看具体的慢SQL：

• 通过慢查询日志可以查看具体的SQL语句：cat /var/lib/mysql/localhost-slow.log

• 用mysqldumpslow工具查看慢SQL，可以通过一些过滤条件找到需要定位的慢SQL

  

  s：排序方式；r：逆序；l：锁定时间；g：正则表达式

   -- 多增加几条慢SQL
   SELECT SLEEP(5);
   SELECT SLEEP(3);
   SELECT SLEEP(3);
   SELECT SLEEP(3);
  

  Eg1：获取返回记录最多的3个慢SQL

  mysqldumpslow -s r -t 3 /var/lib/mysql/localhost-slow.log

  

  Eg2：获取访问次数最多的3个慢SQL

  mysqldumpslow -s c -t 3 /var/lib/mysql/localhost-slow.log

  

  Eg3：按时间排序，前十条包含left join查询语句的SQL

  mysqldumpslow -s t -t 10 -g "LEFT JOIN" /var/lib/mysql/localhost-slow.log

  

#10. 分析海量数据

#模拟海量数据

利用存储过程（无return）/存储函数（有return）：

-- 创建新数据库并切换
CREATE DATABASE testdata;
USE testdata;
-- 创建新表
CREATE TABLE dept (
dno INT PRIMARY KEY DEFAULT 0,
dname VARCHAR (20) NOT NULL DEFAULT '',
loc VARCHAR (20) DEFAULT ''
) ENGINE = INNODB DEFAULT CHARSET = utf8 ;

CREATE TABLE emp (
eid INT PRIMARY KEY,
ename VARCHAR (20) NOT NULL DEFAULT '',
job VARCHAR (20) NOT NULL DEFAULT '',
deptno INT NOT NULL DEFAULT 0
) ENGINE = INNODB DEFAULT CHARSET = utf8 ;
-- 通过存储函数插入海量数据
-- 创建随机字符串模拟员工名称
DELIMITER $
CREATE FUNCTION randstring(n INT) RETURNS VARCHAR (255) 
BEGIN
DECLARE all_str VARCHAR (100) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ' ;
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(RAND() * 52) + 1, 1)
 ) ;
 SET i = i + 1 ;
END WHILE ;
RETURN return_str ;
END $

-- 创建随机数字模拟编号
DELIMITER $
CREATE FUNCTION ran_num () RETURNS INT (5) 
BEGIN
DECLARE i INT DEFAULT 0 ;
SET i = FLOOR(RAND() * 100) ;
RETURN i ;

END $

-- 通过存储过程向emp表插入海量数据
DELIMITER $
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表插入海量数据
DELIMITER $
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 $

-- 插入数据
CALL insert_emp(1000,800000);
CALL insert_dept(10,30);

-- 验证
SELECT COUNT(1) FROM emp;
SELECT COUNT(1) FROM dept;

可能会出现报错：

• SQL syntax：SQL语法有错，需修改SQL语句

• This function has none of DETERMINISTIC......：慢查询日志冲突，可以按如下方式解决

  -- 临时解决
  SHOW VARIABLES LIKE '%log_bin_trust_function_creators%';
  SET GLOBAL log_bin_trust_function_creators = 1;
  -- 永久解决
  -- 永久开启
  -- 在/etc/my.cnf配置文件中的[mysqld]后追加配置：
  -- log_bin_trust_function_creators = 1
  

#分析海量数据

• 利用profiles：当profiling开启后会记录全部SQL语句的相关信息（id，执行时间和SQL语句）。缺点在于只能看多总执行时间，不能看到各个硬件消耗的时间。

   -- 查看
   SHOW VARIABLES LIKE '%profiling%';
   -- 使用
   SHOW PROFILES;
   -- 开启
   SET profiling = ON;
   -- 查看
   SHOW VARIABLES LIKE '%profiling%';
   -- 使用
   SHOW PROFILES;
   -- 查询
   SELECT COUNT(1) FROM dept;
   -- 使用
   SHOW PROFILES;
  

  

• 精确分析：SQL诊断

   -- SQL诊断
   SHOW PROFILE ALL FOR QUERY 2;
   SHOW PROFILE cpu, block io FOR QUERY 2;
  

  

• 全局查询日志：记录profileing开启后的全部SQL语句（全局的记录操作仅仅在调优和开发过程中打开即可，在最终部署时一定关闭），在mysql.general_log表中可以查看日志。

   -- 查看
   SHOW VARIABLES LIKE '%general_log%';
   -- 将全部的SQL记录在表中
   SET GLOBAL general_log = ON;
   SET GLOBAL log_output = 'table';
   -- 查看
   SHOW VARIABLES LIKE '%general_log%';
   -- 查询
   SELECT * FROM emp;
   SELECT COUNT(*) FROM emp;
   -- 查看日志
   SELECT * FROM mysql.general_log;
   -- 也可以将全部的SQL记录到文件
   SET GLOBAL general_log_file = ON;
   SET GLOBAL log_output = 'file';
   SET GLOBAL general_log_file = '/tmp/general.log';
   -- 查询
   SELECT COUNT(1) FROM dept;
  

  

  查看日志文件：cat /tmp/general.log

#11. 锁机制

解决因资源共享而造成的并发问题。

分类：

• 操作类型：
  • 读锁（共享锁）：对同一数据，多个读操作可以同时进行，互不干扰。
  • 写锁（互斥锁）：如果当前写操作没有完成，则无法进行其他的读操作和写操作。
• 操作范围：
  • 表锁：一次性对整张表加锁。如MyISAM存储引擎使用表锁，开销小，加锁块；无死锁；但锁的范围大，容易发生锁冲突，并发度低。
  • 行锁：一次性对一条数据加锁。如InnoDB存储引擎使用行锁，开销大，加锁慢；容易出现死锁；锁的范围较小，不易发生锁冲突，并发度高（发生高并发问题：脏读、修改丢失、不可重复读和幻读）。
  • 页锁

#表锁（MyISAM）

加读锁

-- 建表设置为MyISAM引擎
CREATE TABLE tablelock (
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR (20)
) ENGINE MYISAM ;

-- 插入数据
INSERT INTO tablelock VALUES(NULL,'a1');
INSERT INTO tablelock VALUES(NULL,'a2');
INSERT INTO tablelock VALUES(NULL,'a3');
INSERT INTO tablelock VALUES(NULL,'a4');
INSERT INTO tablelock VALUES(NULL,'a5');

-- 查看加锁的表
SHOW OPEN TABLES;

-- 加读锁
LOCK TABLE tablelock READ;

会话1（加锁的会话）：

如果会话1对表加了read锁，那么会话1可以对该表进行读操作，不能进行写操作；会话1对其他表既不可以进行读操作也不可以进行写操作。 换句话说，若会话1对数据库中的一个表加了read锁，那么会话1只能进行对加锁表的读操作。

会话2（其他会话）：

其他会话能对加锁表进行读操作，不能进行写操作，可以对其他表进行读操作和写操作。

	加锁的会话	其他会话
加锁表的读操作	✅	✅
加锁表的写操作	❌	✅ 需要等待锁释放
其他表的读操作	❌	✅
其他表的写操作	❌	✅

加写锁：

-- 释放锁
UNLOCK TABLES;
-- 加写锁
LOCK TABLE tablelock WRITE;

	加锁的会话	其他会话
加锁表的读操作	✅	✅ 需要等待锁释放
加锁表的写操作	✅	✅ 需要等待锁释放
其他表的读操作	❌	✅
其他表的写操作	❌	✅

MySQL表级锁的锁模式：

MyISAM在执行查询语句（SELECT）前会自动给涉及的所有表加read锁，在执行更新操作（DML）前会自动给涉及的表加write 锁。所以对MyISAM表进行操作会出现以下情况：

• 对MyISAM表的读操作（加读锁），不会阻塞其他进程（会话）对同一表的读请求，但会阻塞对同一表的写请求。只有当读锁释放后，才会执行其他进程的写操作；
• 对MyISAM表的写操作（加写锁），会阻塞其他进程（会话）对同一表的读和写操作，只有当写锁释放后，才会执行其他进程的读写操作。

分析表锁定：

-- 查看加锁的表
SHOW OPEN TABLES;

In_use：当其值为1时，表示被加了锁。

-- 分析表锁定的严重程度
SHOW STATUS LIKE 'table%';

Table_locks_immediate：可能获取到的锁的数量

Table_lock_waited：需要等待的表锁数（如果该值越大，说明存在越大的锁竞争）。

一般建议：

计算比值n = Table_locks_immediate / Table_lock_waited ，若n > 5000，建议采用InnoDB引擎，否则采用MyISAM引擎。

#行锁（InnoDB）

InnoDB存储引擎默认使用行锁

-- 创建表
CREATE TABLE linelock (
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR (20)
) ENGINE = INNODB DEFAULT CHARSET = utf8 ;

-- 插入数据
INSERT INTO linelock VALUES(NULL, '1');
INSERT INTO linelock VALUES(NULL, '2');
INSERT INTO linelock VALUES(NULL, '3');
INSERT INTO linelock VALUES(NULL, '4');
INSERT INTO linelock VALUES(NULL, '5');

两个会话进行操作

会话1：

-- 关闭自动提交
SET autocommit = 0;
-- 会话1进行写操作
INSERT INTO linelock VALUES(6,'a6');

会话2：

-- 关闭自动提交
SET autocommit = 0;
-- 会话2对同一条数据进行写操作
UPDATE linelock SET NAME = 'ax' WHERE id = 6;

会话1结果：

会话2结果：

行锁机制：

• 如果会话1对某条数据进行DML操作（关闭自动提交的情况下），则其他操作必须等待会话或事务结束后（commit/rollback）后才能进行操作。
• 表锁通过UNLOCK TABLES;释放锁，行锁通过事务解锁（commit/rollback）。
• 行锁一次锁一行数据，因此操作不同行的数据互不干扰。

行锁的注意事项：

• 如果没有索引，则行锁会转为表锁。（注意回顾索引失效的情况）
• 行锁的一种特殊情况（间隙锁）：值在范围内，但却不存在。MySQL会自动给间隙加间隙锁。实际中where子句后面加范围查询时，实际加锁的范围就是查询的范围（不是数据库表中实际的值）。

行锁小结：

• InnoDB默认采用行锁；
• 缺点在于相比表锁性能损耗大，优点在于并发能力强以及效率高。
• 建议高并发使用InnoDB存储引擎，否则用MyISAM存储引擎。

分析行锁定：

SHOW STATUS LIKE '%innodb_row_lock%';

Innodb_row_lock_current_waits：当前正在等待锁的数量

Innodb_row_lock_time：从系统启动到现在锁定的总时长

Innodb_row_lock_time_avg：从系统启动到现在锁定的平均时长

Innodb_row_lock_time_max：从系统启动到现在锁定的最大时长

Innodb_row_lock_waits：从系统启动到现在等待的次数

查询时加锁：

通过for update对query语句进行加锁。

-- 开启事务
BEGIN
-- 会话1进行查询
SELECT * FROM linelock WHERE id = 2 FOR UPDATE; -- 加锁

-- 会话2进行更新
UPDATE linelock SET NAME = '222' WHERE id = 2; -- 等待锁释放

#12. 主从复制

#什么是主从复制

主从复制，是用来建立一个和主数据库完全一样的数据库环境，称为从数据库；

主数据库一般是准实时的业务数据库。

#主从复制的作用

• 实时灾备，用于故障切换：做数据的热备，作为后备数据库，主数据库服务器故障后，可切换到从数据库继续工作，避免数据丢失。
• 架构扩展，提升机器性能：业务量越来越大，I/O访问频率过高，单机无法满足，此时做多库的存储，降低磁盘I/O访问的频率，提高单个机器的I/O性能。
• 读写分离，避免影响业务 ：读写分离使数据库能支撑更大的并发。在报表中尤其重要。由于部分报表sql语句非常的慢，导致锁表，影响前台服务。如果前台使用master，报表使用slave，那么报表sql将不会造成前台锁，保证了前台速度。

#主从复制的原理

• 数据库有个bin-log二进制文件，记录了所有sql语句。

• 目标就是把主数据库的bin-log文件的sql语句复制过来。

• 使其在从数据库的relay-log重做日志文件中再执行一次这些sql语句即可。

• 主从复制配置具体需要三个线程：

  • binlog输出线程：每当有从库连接到主库的时候，主库都会创建一个线程然后发送binlog内容到从库。在从库里，当复制开始的时候，从库就会创建以下两个线程进行处理。
  • 从库I/O线程：当START SLAVE语句在从库开始执行之后，从库创建一个I/O线程，该线程连接到主库并请求主库发送binlog里面的更新记录到从库上。从库I/O线程读取主库的binlog输出线程发送的更新并拷贝这些更新到本地文件，其中包括relay log文件。
  • 从库SQL线程：从库创建一个SQL线程，这个线程读取从库I/O线程写到relay log的更新事件并执行。

• 对于每一个主从复制的连接，都有三个线程。拥有多个从库的主库为每一个连接到主库的从库创建一个binlog输出线程，每一个从库都有它自己的I/O线程和SQL线程。

#主从复制的问题及解决方法

存在问题：

• 主库宕机后，数据可能丢失；
• 从库只有一个sql Thread，主库写压力大，复制很可能延时。

解决方法：

• 半同步复制：解决数据丢失的问题
• 并行复制：解决从库复制延迟的问题