MySQL 高级
2021/9/12 19:05:25
本文主要是介绍MySQL 高级，对大家解决编程问题具有一定的参考价值，需要的程序猿们随着小编来一起学习吧！
MySQL 高级
- 一、mysql 的架构介绍
- 1、MySQL简介
- 2、MysqlLinux 版的安装
- 3、Mysql 配置文件
- 4、Mysq逻辑架构
- 5、Mysql存储引擎
- 二、索引优化分析
- 1、性能下降SQL慢、执行时间长、等待时间长
- 2、常见通用的Join查询
- 2.1、SQL执行顺序
- 2.2、Join图
- 2.3、建表SQL
- 2.4、7种JOIN
- 3、索引简介
- 3.1、是什么
- 3.2、优势、劣势
- 3.3、mysql 索引分类
- 3.4、mysql 索引结构
- 3.5、哪些情况下需要或不需要创建索引
- 4、性能分析
- 5、索引优化
- 5.1、单表索引优化分析
- 5.2、两表索引优化
- 5.3、三表索引优化
- 6、索引失效
- 6.1、索引失效准则
- 6.2、索引失效总结
- 三、查询截取分析
- 1、查询优化
- 1.1、ORDER BY关键字优化
- 1.2、GROUP BY关键字优化
- 2、慢查询日志
- 2.1、是什么
- 2.2、怎么用
- 2.3、日志分析工具mysqldumpslow
- 3、批量数据脚本
- 4、Show Profile
- 5、全局查询日志
- 四、MySQL锁机制
- 1、概述
- 2、三锁
- 2.1、表锁（偏读）
- 2.2、行锁（偏写）
- ~1.事务复习
- 2.3、页锁
- 五、主从复制
- 1、复制的基本原理
- 2、复制的基本原则
- 3、复制的最大问题
- 4、一主一从常见配置
一、mysql 的架构介绍
1、MySQL简介
- mysql 内核
- sql 优化工程师
- mysql 服务器的优化
- 各种参数常量的设定
- 查询语句优化
- 主从复制
- 软硬件升级
- 容灾备份
- sql 编程
2、MysqlLinux 版的安装
1）安装mysql 服务器端
[root@centos64 opt]# rpm -ivh MySQL-server-5.5.48-1.linux2.6.i386.rpm
2）安装mysql 客户端
[root@centos64 opt]# rpm -ivh MySQL-client-5.5.48-1.linux2.6.i386.rpm
注意：若安装时出现
error: Failed dependencies:
libaio.so.1 is needed by MySQL-server-5.5.48-1.linux2.6.i386
libaio.so.1(LIBAIO_0.1) is needed by MySQL-server-5.5.48-1.linux2.6.i386
libaio.so.1(LIBAIO_0.4) is needed by MySQL-server-5.5.48-1.linux2.6.i386
error: Failed dependencies:
libncurses.so.5 is needed by MySQL-client-5.5.48-1.linux2.6.i386
解决方案： 添加对应的依赖
yum -y install libncurses.so.5
yum -y install libaio.so.1
3）查看mysql的用户和mysql组
即为安装成功
4）mysql 服务的启+停
[root@centos64 opt]# service mysql start[root@centos64 opt]# service mysql stop
5）ROOT密码设置
[root@centos64 opt]# /usr/bin/mysqladmin -u root password 123456
6） 自启动 mysql 服务
[root@centos64 opt]# chkconfig mysql on
7）mysql 安装位置
| 路径 | 解释 | 备注 |
|---|---|---|
| /var/lib/mysql | mysql数据库文件的存放路径 | /var/lib/mysql/atguigu.cloud.pid |
| /uer/share/mysql/ | 配置文件目录 | mysql.server命令及配置文件 |
| /usr/bin | 相关命令目录 | mysqladmin mysqldump等命令 |
| /etc/init.d/mysql | 启停相关脚本 |
在 linux 下查看安装目录 ps -ef|grep mysql
8）修改字符集
① 拷贝文件 my-huge.cnf
[root@centos64 opt]# cd /usr/share/mysql/[root@centos64 mysql]# cp my-huge.cnf /etc/my.cnf
② 重新启动 MySQL 服务
③ 查看字符集
show variables like '%char%'
默认的客户端和服务器端都用了 latin 1 ，所以会乱码
④ 修改字符集
-
[root@centos64 etc]# vim /etc/my.cnf
修改此三处
⑤ 重启 mysql
3、Mysql 配置文件
主要配置文件：
-
二进制日志文件log-bin
运用于主从复制 -
错误日志log-error
默认时关闭的，记录严重的警告和错误信息，每次启动和关闭的详细信息等 -
查询日志log
默认关闭，记录查询的sql语句，如果开启会减低mysql的整体性能，因为记录日志也是需要消耗能量的 -
数据文件
① 两系统 ：Windows Linux
② frm文件 ：存放表结构
③ myd 文件：存放表数据
④ myi文件：存放表索引 -
如何配置
Windows ：my.ini 文件
Linux：/etc/my.cnf 文件
4、Mysq逻辑架构
和其他数据库相比，mysql有点与众不同，它的架构可以在多种不同场景中应用并发挥良好的作用，主要体现在存储引擎的架构上，插件式的存储引擎架构将查询处理和其他系统任务以及数据的存储提取相分离，这种架构可以根据业务的需求选择合适的存储引擎
- 1）连接层
- 2）服务层
- 3）引擎层
- 4）存储层
5、Mysql存储引擎
1）用命令查看mysql现在已提供什么引擎
mysql> show engines;
2）看你的mysql当前默认的存储引擎
mysql> show variables like '%storage_engine';
| 对比项 | MyISAM | InnoDB |
|---|---|---|
| 主外键 | 不支持 | 支持 |
| 事务 | 不支持 | 支持 |
| 行表锁 | 表锁，即使操作一条记录也会锁住整个表，不适合高并发的操作 | 行锁，操作时只锁某一行，不对其它行有影响，适合高并发的操作 |
| 缓存 | 只缓存索引，不缓存真实数据 | 不仅缓存索引还缓存真实数据，对内存要求较高，而且内存大小对性能有决定性的影响 |
| 表空间 | 小 | 大 |
| 关注点 | 性能 | 事务 |
| 默认安装 | Y | Y |
二、索引优化分析
1、性能下降SQL慢、执行时间长、等待时间长
- 查询语句写的烂
- 索引失效（单值索引、复合索引）
- 关联查询太多join（设计缺陷或不得已的需求）
- 服务器调优及各个参数设置（缓冲、线程数等）
2、常见通用的Join查询
2.1、SQL执行顺序
1）手写
SELECT DISTINCT < select_list > FROM < left_table > < join_type > JOIN < right_table > ON < join_condition > WHERE < where_condition > GROUP BY < group_by_list > HAVING < having_condition > ORDER BY < order_by_condition > LIMIT < limit number >
2）机读
1 FROM <left_table> 2 ON <join_condition> 3 <join_type> JOIN <right_table> 4 WHERE <where_condition> 5 GROUP BY <group_by_list> 6 HAVING <having_condition> 7 SELECT 8 DISTINCT <select_list> 9 ORDER BY <order_by_condition> 10 LIMIT <limit_number>
3）总结
2.2、Join图
2.3、建表SQL
CREATE TABLE `tbl_dept`(
`id` INT(11) NOT NULL AUTO_INCREMENT,
`deptName` VARCHAR(30) DEFAULT NULL,
`locAdd` VARCHAR(40) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
CREATE TABLE `tbl_emp`(
`id` INT(11) NOT NULL AUTO_INCREMENT,
`name` VARCHAR(20) DEFAULT NULL,
`deptld` INT(11) DEFAULT NULL,
PRIMARY KEY(`id`),
KEY `fk_dept_id` (`deptld`)
#CONSTRAINT `fk_dept_id` FOREIGN KEY (`deptld`) REFERENCES `tbl_dept` (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
INSERT INTO tbl_dept(deptName,locAdd) VALUES('RD',11);
INSERT INTO tbl_dept(deptName,locAdd) VALUES('HR',12);
INSERT INTO tbl_dept(deptName,locAdd) VALUES('MK',13);
INSERT INTO tbl_dept(deptName,locAdd) VALUES('MIS',14);
INSERT INTO tbl_dept(deptName,locAdd) VALUES('FD',15);
INSERT INTO tbl_emp(NAME,deptld) VALUES('Z3',1);
INSERT INTO tbl_emp(NAME,deptld) VALUES('Z4',1);
INSERT INTO tbl_emp(NAME,deptld) VALUES('Z5',1);
INSERT INTO tbl_emp(NAME,deptld) VALUES('w5',2);
INSERT INTO tbl_emp(NAME,deptld) VALUES(`w6`,2);
INSERT INTO tbl_emp(NAME,deptld) VALUES('s7',3);
INSERT INTO tbl_emp(NAME,deptld) VALUES('s8',4);
INSERT INTO tbl_emp(NAME,deptld) VALUES('s9',5);
2.4、7种JOIN
3、索引简介
3.1、是什么
索引是帮助MySQL高效获取数据的数据结构
简单理解为：排好序的快速查找数据结构
我们平常所说的索引，如果没有特别指明，都是指B树（多路搜索树，并不一定是二叉的）结构组织的索引。**其中聚集索引，次要索引，覆盖索引，复合索引，前缀索引，唯一索引默认的都是使用B+树索引，统称索引。当然，除了B+树这种类型的索引之外，还有哈希索引（hash index）等
目的： 提高查找效率，可以类比字典。
3.2、优势、劣势
优势：
- 提高数据的检索效率，降低数据库的IO成本。
- 通过索引列对数据进行排序，降低数据的排序成本，降低了CPU的消耗。
劣势：
- 实际索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录，所以索引也是占空间的。一般而言，索引表占用空间是数据表的1.5倍。
- 虽然索引大大提高了查询速度，同时却降低了更新表的速度。如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件每次更新添加了索引列的字段，都会调整因为更新所带来的键值变化后的索引信息
- 索引只是提高效率的一个因素，如果你的MySQL有大数据量的表，就需要花时间研究建立最优秀的索引，或者优化查询
3.3、mysql 索引分类
分类：
- 单值索引：即一个索引只包含单个列，一个表可以有多个单列索引
- 唯一索引：索引列的值必须唯一，但允许有空值
- 复合索引：即一个索引包含多个列
语法：
- 创建
CREATE [UNIQUE] INDEX indexName ON myTable(columnname(length));
ALTER mytable ADD [UNIQUE] INDEX [indexName] ON (columnname(length)) - 删除
DROP INDEX [indexName] ON myTable; - 查看
SHOW INDEX FROM tableName;
3.4、mysql 索引结构
1） BTree索引
2）Hash索引
3）full-text全文索引
4）R-Tree索引
3.5、哪些情况下需要或不需要创建索引
需要创建索引：
- 主键自动建立唯一索引
- 频繁作为查询条件的字段应该创建索引
- 查询中与其它表关联的字段，外键关系建立索引
- 频繁更新的字段不适合创建索引，因为每次更新不单单是更新了记录，还会更新索引，加重IO负担
- where条件里用不到的字段不创建索引
- 单键/组合索引的选择问题，who？（在高并发下倾向创建组合索引）
- 查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度
- 查询中统计或者分组字段
不需要创建索引：
- 表记录太少
- 经常增删改的表
- Why：提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件
- 数据重复且分布平均的表字段，因此应该只为最经常查询和最经常排序的数据列建立索引。注意，如果某个数据列包含许多重复的内容，为它建立索引就没有太大的实际效果
4、性能分析
MySQL Query Optimizer
MySQL常见瓶颈
- CPU：CPU在饱和的时候一般发生在数据装入内存或从磁盘上读取数据时候
- IO：磁盘I/O瓶颈发生在装入数据远大于内存容量的时候
- 服务器硬件的性能瓶颈：top，free，iostat和vmstat来查看系统的性能状态
Explain
1）是什么：
- 使用EXPLAIN关键字可以模拟优化器执行SQL查询语句，从而知道MySQL是如何处理你的SQL语句的。分析你的查询语句或是表结构的性能瓶颈。
2）能干嘛
- 表的读取顺序
- 数据读取操作的操作类型
- 哪些索引可以使用
- 哪些索引被实际使用
- 表之间的应用
- 每张表有多少行被优化器查询
3）怎么玩
- Explain+SQL语句
- 执行计划包含的信息
4）各字段解释
id：
- select查询的序列号，包含一组数字，表示查询中执行select子句或操作表的顺序
- 三种情况：
id相同，执行顺序由上至下
id不同，如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行
id相同不同，同时存在 - 衍生：DERIVED
select_type：
1）查询的类型，主要是用于区别普通查询、联合查询、子查询等的复杂查询
- SIMPLE：简单的select查询，查询中不包含子查询或者UNION
- PRIMARY：查询中包含任何复杂的子部分，最外层查询则被标记为PRIMARY
- SUBQUERY：在FROM列表中包含的子查询被标记为DERIVED（衍生），MySQL会递归执行这些子查询，把结果放在临时表里
- DERIVED：在FROM列表中包含的子查询被标记为DERIVED（衍生）。MySQL会递归执行这些子查询，把结果放在临时表里
- UNION：若第二个SELECT出现在UNION之后，则被标记为UNION；若UNION包含在FROM子句的子查询中，外层SELECT将被标记为：DERIVED
- UNION RESULT：从UNION表中获取结果的SELECT
table：
显示这一行的数据是关于哪些表的
type：
-
访问类型排序
type显示的是访问类型，是较为重要的一个指标，结果值从最好到最坏依次是：
system>const>eq_ref>ref>fulltext>ref_or_null>index_merge>unique_subquery>index_subquery>range>index>All -
显示查询使用了何种类型，从最好到最差依此是：
system>const>eq_ref>ref>range>index>All -
system：表只有一行记录（等于系统表），这是const类型的特例，平时不会出现，这个也可以忽略不计
-
const：表示通过索引一次就找到了，const用于比较primary key或则unique索引。因为只匹配一行数据，所以很快。如将主键置于where列表中，MySQL就能将该查询转换为一个常量
-
eq_ref：唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描
-
ref：非唯一性索引扫描，返回匹配某个单独值的所有行。本质上也是一种索引访问，它返回所有匹配某个单独值的行，然而，它可能会找到多个符合条件的行，所以它应该属于查找和扫描的混合体
-
range：只检索给定范围的行，使用一个索引来选择行。key列显示使用了哪个索引。一般就是在你的where语句中出现了between、<、>、in等的查询。这种范围扫描索引扫描比全表扫描要好，因为它只需要开始于索引的某一点，而结束于另一点，不会扫描全部索引
-
index：Full Index Scan，index与All区别为index类型只遍历索引树。这通常比All快，因为索引文件通常比数据文件小。（也就是说虽然all和index都是读全表，但index是从索引中读取的，而all是从硬盘中读的）
-
all：Full Table Scan，将遍历全表以找到匹配的行
-
一般来说，得保证查询至少达到range级别，最好能达到ref。
possible_keys：
显示可能应用在这张表中的索引，一个或多个。查询涉及到的字段上若存在索引，则该索引将被列出。但不一定被查询实际使用
key：
实际使用的索引。如果为NULL，则没有使用索引。查询中若使用了覆盖索引，则该索引仅出现在key列表中，不会出现在possible_keys列表中。（覆盖索引：查询的字段与建立的复合索引的个数一一吻合）
key_len
表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度。在不损失精确性的情况下，长度越短越好。key_len显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出的
ref
显示索引的哪一列被使用了，如果可能的话，是一个常数。哪些列或常量被用于查找索引列上的值。查询中与其它表关联的字段，外键关系建立索引
rows：
根据表统计信息及索引选用情况，大致估算出找到所需的记录所需要读取的行数
Extra：
包含不适合在其他列中显示但十分重要的额外信息。
- Using filesort：说明mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。MySQL中无法利用索引完成的排序操作成为“文件排序”
- Using temporary：使用了临时表保存中间结果，MySQL在对查询结果排序时使用临时表。常见于排序order by和分组查询group by
- Using index：表示相应的select操作中使用了覆盖索引（Covering Index），避免访问了表的数据行，效率不错！如果同时出现using where，表明索引被用来执行索引键值的查找；如果没有同时出现using where，表明索引用来读取数据而非执行查找动作
- Using where：表明使用了where过滤。
- Using join buffer：使用了连接缓存。
- impossible where：where子句的值总是false，不能用来获取任何元组。（查询语句中where的条件不可能被满足，恒为False）
- select tables optimized away：在没有GROUPBY子句的情况下，基于索引优化MIN/MAX操作或者对于MyISAM存储引擎优化COUNT(*)操作，不必等到执行阶段再进行计算，查询执行计划生成的阶段即完成优化
- distinct：优化distinct操作，在找到第一匹配的元组后即停止找相同值的动作
5、索引优化
5.1、单表索引优化分析
创建表：
- 建表SQL
CREATE TABLE IF NOT EXISTS article( id INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT, author_id INT(10) UNSIGNED NOT NULL, category_id INT(10) UNSIGNED NOT NULL, views INT(10) UNSIGNED NOT NULL, comments INT(10) UNSIGNED NOT NULL, title VARCHAR(255) NOT NULL, content TEXT NOT NULL ); INSERT INTO article(author_id,category_id,views,comments,title,content) VALUES (1,1,1,1,'1','1'), (2,2,2,2,'2','2'), (1,1,3,3,'3','3');
- 表中的测试数据
SELECT * FROM article; +----+-----------+-------------+-------+----------+-------+---------+ | id | author_id | category_id | views | comments | title | content | +----+-----------+-------------+-------+----------+-------+---------+ | 1 | 1 | 1 | 1 | 1 | 1 | 1 | | 2 | 2 | 2 | 2 | 2 | 2 | 2 | | 3 | 1 | 1 | 3 | 3 | 3 | 3 | +----+-----------+-------------+-------+----------+-------+---------+ 3 rows in set (0.00 sec)
查询案例：
5.2、两表索引优化
两表索引优化分析：主外键
创建表：
- 建表 SQL
CREATE TABLE IF NOT EXISTS class( id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT, card INT(10) UNSIGNED NOT NULL, PRIMARY KEY(id) ); CREATE TABLE IF NOT EXISTS book( bookid INT(10) UNSIGNED NOT NULL AUTO_INCREMENT, card INT(10) UNSIGNED NOT NULL, PRIMARY KEY(bookid) ); INSERT INTO class(card) VALUES(FLOOR(1+(RAND()*20))); INSERT INTO class(card) VALUES(FLOOR(1+(RAND()*20))); INSERT INTO class(card) VALUES(FLOOR(1+(RAND()*20))); INSERT INTO class(card) VALUES(FLOOR(1+(RAND()*20))); INSERT INTO class(card) VALUES(FLOOR(1+(RAND()*20))); INSERT INTO class(card) VALUES(FLOOR(1+(RAND()*20))); INSERT INTO class(card) VALUES(FLOOR(1+(RAND()*20))); INSERT INTO class(card) VALUES(FLOOR(1+(RAND()*20))); INSERT INTO class(card) VALUES(FLOOR(1+(RAND()*20))); INSERT INTO class(card) VALUES(FLOOR(1+(RAND()*20))); INSERT INTO class(card) VALUES(FLOOR(1+(RAND()*20))); INSERT INTO class(card) VALUES(FLOOR(1+(RAND()*20))); INSERT INTO class(card) VALUES(FLOOR(1+(RAND()*20))); INSERT INTO class(card) VALUES(FLOOR(1+(RAND()*20))); INSERT INTO class(card) VALUES(FLOOR(1+(RAND()*20))); INSERT INTO class(card) VALUES(FLOOR(1+(RAND()*20))); INSERT INTO class(card) VALUES(FLOOR(1+(RAND()*20))); INSERT INTO class(card) VALUES(FLOOR(1+(RAND()*20))); INSERT INTO class(card) VALUES(FLOOR(1+(RAND()*20))); INSERT INTO class(card) VALUES(FLOOR(1+(RAND()*20))); INSERT INTO class(card) VALUES(FLOOR(1+(RAND()*20))); INSERT INTO book(card) VALUES(FLOOR(1+(RAND()*20))); INSERT INTO book(card) VALUES(FLOOR(1+(RAND()*20))); INSERT INTO book(card) VALUES(FLOOR(1+(RAND()*20))); INSERT INTO book(card) VALUES(FLOOR(1+(RAND()*20))); INSERT INTO book(card) VALUES(FLOOR(1+(RAND()*20))); INSERT INTO book(card) VALUES(FLOOR(1+(RAND()*20))); INSERT INTO book(card) VALUES(FLOOR(1+(RAND()*20))); INSERT INTO book(card) VALUES(FLOOR(1+(RAND()*20))); INSERT INTO book(card) VALUES(FLOOR(1+(RAND()*20))); INSERT INTO book(card) VALUES(FLOOR(1+(RAND()*20))); INSERT INTO book(card) VALUES(FLOOR(1+(RAND()*20))); INSERT INTO book(card) VALUES(FLOOR(1+(RAND()*20))); INSERT INTO book(card) VALUES(FLOOR(1+(RAND()*20))); INSERT INTO book(card) VALUES(FLOOR(1+(RAND()*20))); INSERT INTO book(card) VALUES(FLOOR(1+(RAND()*20))); INSERT INTO book(card) VALUES(FLOOR(1+(RAND()*20))); INSERT INTO book(card) VALUES(FLOOR(1+(RAND()*20))); INSERT INTO book(card) VALUES(FLOOR(1+(RAND()*20))); INSERT INTO book(card) VALUES(FLOOR(1+(RAND()*20))); INSERT INTO book(card) VALUES(FLOOR(1+(RAND()*20)));
查询案例：
实现两表的连接，连接条件是 class.card = book.card
SELECT * FROM class LEFT JOIN book ON class.card = book.card; +----+------+--------+------+ | id | card | bookid | card | +----+------+--------+------+ | 1 | 12 | 6 | 12 | | 2 | 13 | 8 | 13 | | 2 | 13 | 9 | 13 | | 2 | 13 | 12 | 13 | | 3 | 12 | 6 | 12 | | 4 | 17 | 3 | 17 | | 5 | 11 | NULL | NULL | | 6 | 3 | 4 | 3 | | 7 | 1 | 2 | 1 | | 7 | 1 | 11 | 1 | | 7 | 1 | 15 | 1 | | 8 | 16 | 1 | 16 | | 8 | 16 | 18 | 16 | | 9 | 17 | 3 | 17 | | 10 | 16 | 1 | 16 | | 10 | 16 | 18 | 16 | | 11 | 9 | 17 | 9 | | 12 | 17 | 3 | 17 | | 13 | 18 | 7 | 18 | | 14 | 16 | 1 | 16 | | 14 | 16 | 18 | 16 | | 15 | 7 | NULL | NULL | | 16 | 8 | NULL | NULL | | 17 | 19 | NULL | NULL | | 18 | 9 | 17 | 9 | | 19 | 6 | NULL | NULL | | 20 | 5 | NULL | NULL | | 21 | 6 | NULL | NULL | +----+------+--------+------+ 28 rows in set (0.00 sec)
- 使用 explain 分析 SQL 语句的性能，可以看到：驱动表是左表 class 表
EXPLAIN SELECT * FROM class LEFT JOIN book ON class.card = book.card; +----+-------------+-------+------+---------------+------+---------+------+------+----------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+------+------+----------------------------------------------------+ | 1 | SIMPLE | class | ALL | NULL | NULL | NULL | NULL | 21 | NULL | | 1 | SIMPLE | book | ALL | NULL | NULL | NULL | NULL | 20 | Using where; Using join buffer (Block Nested Loop) | +----+-------------+-------+------+---------------+------+---------+------+------+----------------------------------------------------+ 2 rows in set (0.00 sec)
- 结论：
type 有 All ，rows 为表中数据总行数，说明 class 和 book 进行了全表检索
即每次 class 表对 book 表进行左外连接时，都需要在 book 表中进行一次全表检索
添加索引：在右表添加索引
- 添加索引的 SQL 指令
ALTER TABLE 'book' ADD INDEX Y ('card');
- 在 book 的 card 字段上添加索引
ALTER TABLE book ADD INDEX Y (card); Query OK, 0 rows affected (0.30 sec) Records: 0 Duplicates: 0 Warnings: 0 SHOW INDEX FROM book; +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | book | 0 | PRIMARY | 1 | bookid | A | 20 | NULL | NULL | | BTREE | | | | book | 1 | Y | 1 | card | A | 20 | NULL | NULL | | BTREE | | | +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ 2 rows in set (0.00 sec)
- 测试结果：可以看到第二行的type变为了ref，rows也变成了优化比较明显
EXPLAIN SELECT * FROM class LEFT JOIN book ON class.card = book.card; +----+-------------+-------+------+---------------+------+---------+-----------------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+-----------------+------+-------------+ | 1 | SIMPLE | class | ALL | NULL | NULL | NULL | NULL | 21 | NULL | | 1 | SIMPLE | book | ref | Y | Y | 4 | db01.class.card | 1 | Using index | +----+-------------+-------+------+---------------+------+---------+-----------------+------+-------------+ 2 rows in set (0.00 sec)
- 分析：
这是由左连接特性决定的。LEFT JOIN条件用于确定如何从右表搜索行，左边一定都有，所以右边是我们的关键点，一定需要建立索引
左表连接右表，则需要拿着左表的数据去右表里面查，索引需要在右表中建立索引
添加索引：在右表添加索引
- 删除之前 book 表中的索引
DROP INDEX Y ON book;
- 在 class 表的 card 字段上建立索引
ALTER TABLE class ADD INDEX X(card);
- 再次执行左连接，凉凉~~
EXPLAIN SELECT * FROM class LEFT JOIN book ON class.card = book.card; +----+-------------+-------+-------+---------------+------+---------+------+------+----------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+---------------+------+---------+------+------+----------------------------------------------------+ | 1 | SIMPLE | class | index | NULL | X | 4 | NULL | 21 | Using index | | 1 | SIMPLE | book | ALL | NULL | NULL | NULL | NULL | 20 | Using where; Using join buffer (Block Nested Loop) | +----+-------------+-------+-------+---------------+------+---------+------+------+----------------------------------------------------+ 2 rows in set (0.00 sec)
- 我们来执行右连接：可以看到第二行的type变为了ref，rows也变成了优化比较明显
EXPLAIN SELECT * FROM class RIGHT JOIN book ON class.card = book.card; +----+-------------+-------+------+---------------+------+---------+----------------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+----------------+------+-------------+ | 1 | SIMPLE | book | ALL | NULL | NULL | NULL | NULL | 20 | NULL | | 1 | SIMPLE | class | ref | X | X | 4 | db01.book.card | 1 | Using index | +----+-------------+-------+------+---------------+------+---------+----------------+------+-------------+ 2 rows in set (0.00 sec)
-
分析：
这是因为RIGHT JOIN条件用于确定如何从左表搜索行，右边一定都有，所以左边是我们的关键点，一定需要建立索引
class RIGHT JOIN book ：book 里面的数据一定存在于结果集中，我们需要拿着 book 表中的数据，去 class 表中搜索，所以索引需要建立在 class 表中 -
为了不影响之后的测试，删除该表的 idx_article_ccv 索引
DROP INDEX X ON class;
5.3、三表索引优化
创建表
- 建表 SQL
CREATE TABLE IF NOT EXISTS phone( phoneid INT(10) UNSIGNED NOT NULL AUTO_INCREMENT, card INT(10) UNSIGNED NOT NULL, PRIMARY KEY(phoneid) )ENGINE=INNODB; INSERT INTO phone(card) VALUES(FLOOR(1+(RAND()*20))); INSERT INTO phone(card) VALUES(FLOOR(1+(RAND()*20))); INSERT INTO phone(card) VALUES(FLOOR(1+(RAND()*20))); INSERT INTO phone(card) VALUES(FLOOR(1+(RAND()*20))); INSERT INTO phone(card) VALUES(FLOOR(1+(RAND()*20))); INSERT INTO phone(card) VALUES(FLOOR(1+(RAND()*20))); INSERT INTO phone(card) VALUES(FLOOR(1+(RAND()*20))); INSERT INTO phone(card) VALUES(FLOOR(1+(RAND()*20))); INSERT INTO phone(card) VALUES(FLOOR(1+(RAND()*20))); INSERT INTO phone(card) VALUES(FLOOR(1+(RAND()*20))); INSERT INTO phone(card) VALUES(FLOOR(1+(RAND()*20))); INSERT INTO phone(card) VALUES(FLOOR(1+(RAND()*20))); INSERT INTO phone(card) VALUES(FLOOR(1+(RAND()*20))); INSERT INTO phone(card) VALUES(FLOOR(1+(RAND()*20))); INSERT INTO phone(card) VALUES(FLOOR(1+(RAND()*20))); INSERT INTO phone(card) VALUES(FLOOR(1+(RAND()*20))); INSERT INTO phone(card) VALUES(FLOOR(1+(RAND()*20))); INSERT INTO phone(card) VALUES(FLOOR(1+(RAND()*20))); INSERT INTO phone(card) VALUES(FLOOR(1+(RAND()*20))); INSERT INTO phone(card) VALUES(FLOOR(1+(RAND()*20)));
查询案例
- 实现三表的连接查询：
SELECT * FROM class LEFT JOIN book ON class.card = book.card LEFT JOIN phone ON book.card = phone.card;
- 使用 explain 分析 SQL 指令：
EXPLAIN SELECT * FROM class LEFT JOIN book ON class.card = book.card LEFT JOIN phone ON book.card = phone.card;
- 结论：
type 有All ，rows 为表数据总行数，说明 class、 book 和 phone 表都进行了全表检索
Extra 中 Using join buffer ，表明连接过程中使用了 join 缓冲区
创建索引
- 创建索引的 SQL 语句
ALTER TABLE book ADD INDEX Y (card); ALTER TABLE phone ADD INDEX Z (card);
- 进行 LEFT JOIN ，永远都在右表的字段上建立索引
ALTER TABLE book ADD INDEX Y (card); Query OK, 0 rows affected (0.06 sec) Records: 0 Duplicates: 0 Warnings: 0 SHOW INDEX FROM book; +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | book | 0 | PRIMARY | 1 | bookid | A | 20 | NULL | NULL | | BTREE | | | | book | 1 | Y | 1 | card | A | 20 | NULL | NULL | | BTREE | | | +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ 2 rows in set (0.00 sec) ALTER TABLE phone ADD INDEX Z (card); Query OK, 0 rows affected (0.05 sec) Records: 0 Duplicates: 0 Warnings: 0 SHOW INDEX FROM phone; +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | phone | 0 | PRIMARY | 1 | phoneid | A | 20 | NULL | NULL | | BTREE | | | | phone | 1 | Z | 1 | card | A | 20 | NULL | NULL | | BTREE | | | +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ 2 rows in set (0.00 sec)
- 执行查询：后2行的type都是ref，且总rows优化很好，效果不错。因此索引最好设置在需要经常查询的字段中
EXPLAIN SELECT * FROM class LEFT JOIN book ON class.card=book.card LEFT JOIN phone ON book.card = phone.card; +----+-------------+-------+------+---------------+------+---------+-----------------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+-----------------+------+-------------+ | 1 | SIMPLE | class | ALL | NULL | NULL | NULL | NULL | 21 | NULL | | 1 | SIMPLE | book | ref | Y | Y | 4 | db01.class.card | 1 | Using index | | 1 | SIMPLE | phone | ref | Z | Z | 4 | db01.book.card | 1 | Using index | +----+-------------+-------+------+---------------+------+---------+-----------------+------+-------------+ 3 rows in set (0.00 sec)
Join 语句优化的结论
将 left join 看作是两层嵌套 for 循环
- 尽可能减少Join语句中的NestedLoop的循环总次数；
- 永远用小结果集驱动大的结果集（在大结果集中建立索引，在小结果集中遍历全表）；
- 优先优化NestedLoop的内层循环；
- 保证Join语句中被驱动表上Join条件字段已经被索引；
- 当无法保证被驱动表的Join条件字段被索引且内存资源充足的前提下，不要太吝惜JoinBuffer的设置；
6、索引失效
索引失效（应该避免）
创建表
- 建表 SQL
CREATE TABLE staffs(
id INT PRIMARY KEY AUTO_INCREMENT,
`name` VARCHAR(24)NOT NULL DEFAULT'' COMMENT'姓名',
`age` INT NOT NULL DEFAULT 0 COMMENT'年龄',
`pos` VARCHAR(20) NOT NULL DEFAULT'' COMMENT'职位',
`add_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT'入职时间'
)CHARSET utf8 COMMENT'员工记录表';
INSERT INTO staffs(`name`,`age`,`pos`,`add_time`) VALUES('z3',22,'manager',NOW());
INSERT INTO staffs(`name`,`age`,`pos`,`add_time`) VALUES('July',23,'dev',NOW());
INSERT INTO staffs(`name`,`age`,`pos`,`add_time`) VALUES('2000',23,'dev',NOW());
ALTER TABLE staffs ADD INDEX index_staffs_nameAgePos(`name`,`age`,`pos`);
- staffs 表中的测试数据
select * from staffs; +----+------+-----+---------+---------------------+ | id | name | age | pos | add_time | +----+------+-----+---------+---------------------+ | 1 | z3 | 22 | manager | 2020-08-04 14:42:33 | | 2 | July | 23 | dev | 2020-08-04 14:42:33 | | 3 | 2000 | 23 | dev | 2020-08-04 14:42:33 | +----+------+-----+---------+---------------------+ 3 rows in set (0.00 sec)
- staffs 表中的复合索引：name、age、pos
SHOW INDEX FROM staffs; +--------+------------+-------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +--------+------------+-------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | staffs | 0 | PRIMARY | 1 | id | A | 3 | NULL | NULL | | BTREE | | | | staffs | 1 | index_staffs_nameAgePos | 1 | name | A | 3 | NULL | NULL | | BTREE | | | | staffs | 1 | index_staffs_nameAgePos | 2 | age | A | 3 | NULL | NULL | | BTREE | | | | staffs | 1 | index_staffs_nameAgePos | 3 | pos | A | 3 | NULL | NULL | | BTREE | | | +--------+------------+-------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ 4 rows in set (0.00 sec)
6.1、索引失效准则
索引失效判断准则
- 全值匹配我最爱
- 最佳左前缀法则：如果索引了多例，要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列。
- 不在索引列上做任何操作（计算、函数、（自动or手动）类型转换），会导致索引失效而转向全表扫描
- 存储引擎不能使用索引中范围条件右边的列
- 尽量使用覆盖索引（只访问索引的查询（索引列和查询列一致）），减少select *
- mysql在使用不等于（!=或者<>）的时候无法使用索引会导致全表扫描
is null，is not null也无法使用索引（早期版本不能走索引，后续版本应该优化过，可以走索引）- like以通配符开头（’%abc…’）mysql索引失效会变成全表扫描操作
- 字符串不加单引号索引失效
- 少用or，用它连接时会索引失效
最佳左匹配法则：带头大哥不能死，中间兄弟不能断
1）只有带头大哥 name 时
- key = index_staffs_nameAgePos 表明索引生效
- ref = const ：这个常量就是查询时的 ‘July’ 字符串常量
2）带头大哥 name 带上小弟 age
- key = index_staffs_nameAgePos 表明索引生效
- ref = const,const：两个常量分别为 ‘July’ 和 23
3）带头大哥 name 带上小弟 age ，小弟 age 带上小小弟 pos
- key = index_staffs_nameAgePos 表明索引生效
- ref = const,const,const ：三个常量分别为 ‘July’、23 和 ‘dev’
4）带头大哥 name 挂了
- key = NULL 说明索引失效
- ref = null 表示 ref 也失效
5）带头大哥 name 没挂，小弟 age 跑了
- key = index_staffs_nameAgePos 说明索引没有失效
- ref = const 表明只使用了一个常量，即第二个常量（pos = ‘dev’）没有生效
在索引列上进行计算，会导致索引失效，进而转向全表扫描
6）不对带头大哥 name 进行任何操作：key = index_staffs_nameAgePos 表明索引生效
7）对带头大哥 name 进行操作：使用 LEFT 函数截取子串
- key = NULL 表明索引生效
- type = ALL 表明进行了全表扫描
范围之后全失效
8）精确匹配
- type = ref 表示非唯一索引扫描，SQL 语句将返回匹配某个单独值的所有行
- key_len = 140 表明表示索引中使用的字节数
9）将 age 改为范围匹配
- ype = range 表示范围扫描
- key = index_staffs_nameAgePos 表示索引并没有失效
- key_len = 78 ，ref = NULL 均表明范围搜索使其后面的索引均失效
尽量使用覆盖索引（只访问索引的查询（索引列和查询列一致）），减少 select *
10）SELECT * 的写法
EXPLAIN SELECT * FROM staffs WHERE name = 'July'AND age > 23 AND pos = 'dev'; +----+-------------+--------+-------+-------------------------+-------------------------+---------+------+------+-----------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------+-------+-------------------------+-------------------------+---------+------+------+-----------------------+ | 1 | SIMPLE | staffs | range | index_staffs_nameAgePos | index_staffs_nameAgePos | 78 | NULL | 1 | Using index condition | +----+-------------+--------+-------+-------------------------+-------------------------+---------+------+------+-----------------------+ 1 row in set (0.00 sec)
11）覆盖索引的写法：Extra = Using where; Using index ，Using index 表示使用索引列进行查询，将大大提高查询的效率
EXPLAIN SELECT name, age, pos FROM staffs WHERE name = 'July'AND age = 23 AND pos = 'dev'; +----+-------------+--------+------+-------------------------+-------------------------+---------+-------------------+------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------+------+-------------------------+-------------------------+---------+-------------------+------+--------------------------+ | 1 | SIMPLE | staffs | ref | index_staffs_nameAgePos | index_staffs_nameAgePos | 140 | const,const,const | 1 | Using where; Using index | +----+-------------+--------+------+-------------------------+-------------------------+---------+-------------------+------+--------------------------+ 1 row in set (0.00 sec)
12）覆盖索引中包含 range 条件：type = ref 并且 Extra = Using where; Using index ，虽然在查询条件中使用了 范围搜索，但是由于我们只需要查找索引列，所以无需进行全表扫描
EXPLAIN SELECT name, age, pos FROM staffs WHERE name = 'July'AND age > 23 AND pos = 'dev'; +----+-------------+--------+------+-------------------------+-------------------------+---------+-------+------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------+------+-------------------------+-------------------------+---------+-------+------+--------------------------+ | 1 | SIMPLE | staffs | ref | index_staffs_nameAgePos | index_staffs_nameAgePos | 74 | const | 1 | Using where; Using index | +----+-------------+--------+------+-------------------------+-------------------------+---------+-------+------+--------------------------+ 1 row in set (0.00 sec)
mysql在使用不等于（!=或者<>）的时候无法使用索引会导致全表扫描
13）在使用 != 会 <> 时会导致索引失效：
- key = null 表示索引失效
- rows = 3 表示进行了全表扫描
is null，is not null 也无法使用索引
- is null，is not null 会导致索引失效：key = null 表示索引失效
like % 写最右
- staffs 表的索引关系
SHOW INDEX from staffs; +--------+------------+-------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +--------+------------+-------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | staffs | 0 | PRIMARY | 1 | id | A | 3 | NULL | NULL | | BTREE | | | | staffs | 1 | index_staffs_nameAgePos | 1 | name | A | 3 | NULL | NULL | | BTREE | | | | staffs | 1 | index_staffs_nameAgePos | 2 | age | A | 3 | NULL | NULL | | BTREE | | | | staffs | 1 | index_staffs_nameAgePos | 3 | pos | A | 3 | NULL | NULL | | BTREE | | | +--------+------------+-------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ 4 rows in set (0.00 sec)
- like % 写在左边的情况
- type = All ，rows = 3 表示进行了全表扫描
- key = null 表示索引失效
- like % 写在右边的情况：key = index_staffs_nameAgePos 表示索引未失效
解决【like ‘%str%’ 】索引失效的问题：覆盖索引
创建表
- 建表 SQL
CREATE TABLE `tbl_user`(
`id` INT(11) NOT NULL AUTO_INCREMENT,
`name` VARCHAR(20) DEFAULT NULL,
`age`INT(11) DEFAULT NULL,
`email` VARCHAR(20) DEFAULT NULL,
PRIMARY KEY(`id`)
)ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
INSERT INTO tbl_user(`name`,`age`,`email`)VALUES('1aa1',21,'a@163.com');
INSERT INTO tbl_user(`name`,`age`,`email`)VALUES('2bb2',23,'b@163.com');
INSERT INTO tbl_user(`name`,`age`,`email`)VALUES('3cc3',24,'c@163.com');
INSERT INTO tbl_user(`name`,`age`,`email`)VALUES('4dd4',26,'d@163.com');
- tbl_user 表中的测试数据
select * from tbl_user; +----+------+------+-----------+ | id | name | age | email | +----+------+------+-----------+ | 1 | 1aa1 | 21 | a@163.com | | 2 | 2bb2 | 23 | b@163.com | | 3 | 3cc3 | 24 | c@163.com | | 4 | 4dd4 | 26 | d@163.com | +----+------+------+-----------+ 4 rows in set (0.00 sec)
创建索引
- 创建索引的 SQL 指令
CREATE INDEX idx_user_nameAge ON tbl_user(name, age);
- 在 tbl_user 表的 name 字段和 age 字段创建联合索引
CREATE INDEX idx_user_nameAge ON tbl_user(name, age); Query OK, 0 rows affected (0.05 sec) Records: 0 Duplicates: 0 Warnings: 0 SHOW INDEX FROM tbl_user; +----------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +----------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | tbl_user | 0 | PRIMARY | 1 | id | A | 4 | NULL | NULL | | BTREE | | | | tbl_user | 1 | idx_user_nameAge | 1 | name | A | 4 | NULL | NULL | YES | BTREE | | | | tbl_user | 1 | idx_user_nameAge | 2 | age | A | 4 | NULL | NULL | YES | BTREE | | | +----------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ 3 rows in set (0.00 sec)
测试覆盖索引
1）如下 SQL 的索引均不会失效：
- 只要查询的字段能和覆盖索引扯得上关系，并且没有多余字段，覆盖索引就不会失效
- 如下 SQL 的索引均会失效：但凡有多余字段，覆盖索引就会失效
EXPLAIN SELECT * FROM tbl_user WHERE NAME LIKE '%aa%'; +----+-------------+----------+------+---------------+------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+----------+------+---------------+------+---------+------+------+-------------+ | 1 | SIMPLE | tbl_user | ALL | NULL | NULL | NULL | NULL | 4 | Using where | +----+-------------+----------+------+---------------+------+---------+------+------+-------------+ 1 row in set (0.00 sec) EXPLAIN SELECT id, name, age, email FROM tbl_user WHERE NAME LIKE '%aa%'; +----+-------------+----------+------+---------------+------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+----------+------+---------------+------+---------+------+------+-------------+ | 1 | SIMPLE | tbl_user | ALL | NULL | NULL | NULL | NULL | 4 | Using where | +----+-------------+----------+------+---------------+------+---------+------+------+-------------+ 1 row in set (0.00 sec)
字符串不加单引号索引失效
- 正常操作，索引没有失效
SHOW INDEX FROM staffs; +--------+------------+-------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +--------+------------+-------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | staffs | 0 | PRIMARY | 1 | id | A | 3 | NULL | NULL | | BTREE | | | | staffs | 1 | index_staffs_nameAgePos | 1 | name | A | 3 | NULL | NULL | | BTREE | | | | staffs | 1 | index_staffs_nameAgePos | 2 | age | A | 3 | NULL | NULL | | BTREE | | | | staffs | 1 | index_staffs_nameAgePos | 3 | pos | A | 3 | NULL | NULL | | BTREE | | | +--------+------------+-------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ 4 rows in set (0.00 sec) explain select * from staffs where name='2000'; +----+-------------+--------+------+-------------------------+-------------------------+---------+-------+------+-----------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------+------+-------------------------+-------------------------+---------+-------+------+-----------------------+ | 1 | SIMPLE | staffs | ref | index_staffs_nameAgePos | index_staffs_nameAgePos | 74 | const | 1 | Using index condition | +----+-------------+--------+------+-------------------------+-------------------------+---------+-------+------+-----------------------+ 1 row in set (0.00 sec)
- 如果字符串忘记写 ‘’ ，那么 mysql 会为我们进行隐式的类型转换，但凡进行了类型转换，索引都会失效
explain select * from staffs where name=2000; +----+-------------+--------+------+-------------------------+------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------+------+-------------------------+------+---------+------+------+-------------+ | 1 | SIMPLE | staffs | ALL | index_staffs_nameAgePos | NULL | NULL | NULL | 3 | Using where | +----+-------------+--------+------+-------------------------+------+---------+------+------+-------------+ 1 row in set (0.00 sec)
少用or，用它连接时会索引失效
- 使用 or 连接，会导致索引失效
SHOW INDEX FROM staffs; +--------+------------+-------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +--------+------------+-------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | staffs | 0 | PRIMARY | 1 | id | A | 3 | NULL | NULL | | BTREE | | | | staffs | 1 | index_staffs_nameAgePos | 1 | name | A | 3 | NULL | NULL | | BTREE | | | | staffs | 1 | index_staffs_nameAgePos | 2 | age | A | 3 | NULL | NULL | | BTREE | | | | staffs | 1 | index_staffs_nameAgePos | 3 | pos | A | 3 | NULL | NULL | | BTREE | | | +--------+------------+-------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ 4 rows in set (0.00 sec) explain select * from staffs where name='z3' or name = 'July'; +----+-------------+--------+------+-------------------------+------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------+------+-------------------------+------+---------+------+------+-------------+ | 1 | SIMPLE | staffs | ALL | index_staffs_nameAgePos | NULL | NULL | NULL | 3 | Using where | +----+-------------+--------+------+-------------------------+------+---------+------+------+-------------+ 1 row in set (0.00 sec)
6.2、索引失效总结
一般性建议
- 对于单键索引，尽量选择针对当前query过滤性更好的索引
- 在选择组合索引的时候，当前query中过滤性最好的字段在索引字段顺序中，位置越靠左越好。
- 在选择组合索引的时候，尽量选择可以能包含当前query中的where子句中更多字段的索引
- 尽可能通过分析统计信息和调整query的写法来达到选择合适索引的目的
索引优化的总结
- like 后面以常量开头，比如 like ‘kk%’ 和 like ‘k%kk%’ ，可以理解为就是常量
全值匹配我最爱， 最左前缀要遵守；
带头大哥不能死， 中间兄弟不能断；
索引列上少计算， 范围之后全失效；
LIKE 百分写最右， 覆盖索引不写 *；
不等空值还有 OR， 索引影响要注意；
VAR 引号不可丢， SQL 优化有诀窍
三、查询截取分析
分析：
- 观察，至少跑1天，看看生产的慢SQL情况。
- 开启慢查询日志，设置阈值，比如超过5秒钟的就是慢SQL，并将它抓取出来。
- explain+慢SQL分析
- show profile
- 运维经理 or DBA，进行SQL数据库服务器参数调优。
总结
- 慢查询的开启并捕获
- explain+慢SQL分析
- show profile查询SQL在Mysql服务器里面的执行细节和生命周期情况
- SQL数据库服务器的参数调优
1、查询优化
1）永远小表驱动大表，类似嵌套循环Nested Loop
- 优化原则：小表驱动大表，即小的数据集驱动大的数据集
- 当B表的数据集必须小于A表的数据集时，用in优于exists
- 当A表的数据集必须小于B表的数据集时，用exists优于in
- 注意：A表与B表的ID字段应建立索引。
- EXISTS
SELECT … FROM table WHERE EXISTS(subquery)
该语法可以理解为：将主查询的数据，放到子查询中做条件验证，根据验证结果（TRUE或FALSE）来决定主查询的数据结果是否得以保留 - 提示
EXISTS（subquery）只返回TRUE或FALSE，因此子查询中的SELECT *也可以是SELECT 1或SELECT ‘X’，官方说法是实际执行时会忽略SELECT清单，因此没有区别。
EXISTS子查询的实际执行过程可能经过了优化而不是我们理解上的逐条对比，如果担心效率问题，可进行实际检验以确定是否有效率问题
EXISTS子查询往往也可以用条件表达式/其他子查询或者JOIN来替代，何种最优需要具体问题具体分析
1.1、ORDER BY关键字优化
- ORDER BY子句，尽量使用Index方式排序，避免使用FileSort方式排序
① MySQL支持两种方式的排序：
FileSort和Index，Index效率高。FileSort方式效率较低
Using Index，它指MySQL扫描索引本身完成排序。
② ORDER BY满足两种情况，会使用Index方式排序
ORDER BY语句使用索引最左前列
使用Where子句与ORDER BY子句条件列组合满足索引最左前列
- 尽可能在索引列上完成排序操作，遵照索引建的最佳最前缀
- 如果不在索引列上，filesort有两种算法：
双路排序
MySQL4.1之前是使用双路排序，字面意思就是两次扫描磁盘，最终得到数据。读取行指针和order by列，对他们进行排序，然后扫描已经排好序的列表，按照列表中的值重新从列表中读取对应的数据输出。
从磁盘取排序字段，在buffer进行排序，再从磁盘读取其他字段
取一批数据，要对磁盘进行了两次扫描，众所周知，I\O是很耗时的，所以在mysql4.1之后，出现了第二种改进的算法，就是单路排序
单路排序
从磁盘读取查询需要的所有列，按照order by列在buffer对它们进行排序，然后扫描排序后的列表进行输出，它的效率更快一些，避免了第二次读取数据。并且把随机IO变成了顺序IO，但是它会使用更多的空间。
结论及引申出的问题
由于单路是后出的，总体而言好过双路
但是用单路有问题
本来想省一次I/O操作，反而导致了大量的I/O操作，反而得不偿失。（原因：数据的总大小超过sort_buffer的容量）
-
优化策略
增大sort_buffer_size参数的设置
增大max_length_for_sort_data参数的设置
1.2、GROUP BY关键字优化
- group by实质是先排序后进行分组，遵照索引建的最佳左前缀
- 当无法使用索引列，增大max_length_for_sort_data参数的设置+增大sort_buffer_size参数的设置
- where高于having，能写在where限定的条件就不要去having限定了
2、慢查询日志
2.1、是什么
- MySQL的慢查询日志是MySQL提供的一种日志记录，它用来记录在MySQL中响应时间超过阈值的语句，具体指运行时间超过long_query_time值的SQL，则会被记录到慢查询日志中。
- long_query_time的默认值是10，意思是运行10秒以上的语句。
- 由它来查看哪些SQL超出了我们的最大忍耐时间值，比如一条sql执行超过5秒钟，我们就算慢SQL，希望能收集超过5秒的sql，结合之前的explain进行全面分析
2.2、怎么用
1）说明
- 默认情况下，MySQL数据库没有开启慢查询日志，需要我们手动来设置这个参数。
- 当然，如果不是调优需要的话，一般不建议启动该参数，因为开启慢查询日志会或多或少带来一定的性能影响。慢查询日志支持将日志记录写入文件。
2）查看是否开启及如何开启
- 默认：SHOW VARIABLES LIKE ‘%slow_query_log%’;
- 开启：set global slow_query_log=1;
3）Case
- 查看当前多少秒算慢：SHOW VARIABLES LIKE ‘long_query_time%’;
- 设置慢的阈值时间：set global long_query_time=3;
- 为什么设置后看不出变化（设置3之后，查询依然显示10）：
需要重新连接或新开一个会话才能看到修改值。
SHOW VARIABLES LIEK ‘long_query_time%’;
show global variables like ‘long_query_time’; - 记录慢SQL并后续分析
- 查询当前系统中有多少条慢查询记录：
show global status like ‘%Slow_queries%’;
2.3、日志分析工具mysqldumpslow
在生产环境中，如果要手工分析日志，查找、分析SQL，显然是个体力活，MySQL提供了日志分析工具mysqldumpslow。
查看mysqldumpslow的帮助信息：
- mysqldumpslow --help
- s：是表示按照何种方式排序
- c：访问次数
- I：锁定时间
- r：返回记录
- t：查询时间
- al：平均锁定时间
- ar：平均返回记录数
- at：平均查询时间
- t：即为返回前面多少条的数据
- g：后边搭配一个正则匹配模式，大小写不敏感
3、批量数据脚本
1）往表里插入1000w数据
2）建表
4、Show Profile
是mysql提供的可以用来分析当前会话中语句执行的资源消耗情况。可以用于SQL的调优的测量
默认情况下，参数处于关闭状态，并保存最近15次的运行结果
步骤：
1）是否支持，看看当前的mysql版本是否支持
show variables like ‘profiling’;
2）开启功能，默认是关闭，使用前需要开启
3）运行SQL
select * from emp group by id%10 limit 150000;select * from emp group by id%20 order by 5;
4）查看结果
show profiles;
5）诊断SQL
show profile cpu, block io for query [上一步前面的问题SQL数字号码];
6）日常开发需要注意的结论
-
converting HEAP to MyISAM：查询结果太大，内存都不够用了往磁盘上搬了
-
Creating tmp table：创建临时表
拷贝数据到临时表
用完再删除 -
Copying to tmp table on disk：把内存中临时表复制到磁盘，危险！！
-
locked
5、全局查询日志
1）配置启用
在mysql的my.cnf 种，设置如下
#开启
general_log=1
#记录日志文件的路径
general_log_file=/path/logfile
#输出格式
log_output=FILE
2）编码启用
set global general_log=1;set global llog_output='TABLE';
此后，你所编写的sql语句，将会记录到mysql库里的general_log表，可以用下面的命令查看
select * from mysql.general_log;
3）永远不要在生产环境开启这个功能
四、MySQL锁机制
1、概述
锁是计算机协调多个进程并发访问某一资源的机制。
锁的分类
从对数据操作的类型（读/写）分：
- 读锁（共享锁）：针对同一份数据，多个读操作可以同时进行而不会互相影响
- 写锁（排它锁）：当前写操作没有完成前，它会阻断其他写锁和读锁
从对数据操作的粒度分：
- 表锁
- 行锁
2、三锁
- 开销、加锁速度、死锁、粒度、并发性能
- 只能就具体应用的特点来说那种锁更合适
2.1、表锁（偏读）
特点：
- 偏向MyISAM存储引擎，开销小，加锁快；无死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低
案例分析：
- 建表 SQL：引擎选择 myisam
create table mylock (
id int not null primary key auto_increment,
name varchar(20) default ''
) engine myisam;
insert into mylock(name) values('a');
insert into mylock(name) values('b');
insert into mylock(name) values('c');
insert into mylock(name) values('d');
insert into mylock(name) values('e');
select * from mylock;
- mylock 表中的测试数据
mysql> select * from mylock; +----+------+ | id | name | +----+------+ | 1 | a | | 2 | b | | 3 | c | | 4 | d | | 5 | e | +----+------+ 5 rows in set (0.00 sec)
手动加锁和释放锁
- 查看当前数据库中表的上锁情况：show open tables;，0 表示未上锁
mysql> show open tables; +--------------------+----------------------------------------------------+--------+-------------+ | Database | Table | In_use | Name_locked | +--------------------+----------------------------------------------------+--------+-------------+ | performance_schema | events_waits_history | 0 | 0 | | performance_schema | events_waits_summary_global_by_event_name | 0 | 0 | | performance_schema | setup_timers | 0 | 0 | | performance_schema | events_waits_history_long | 0 | 0 | | performance_schema | events_statements_summary_by_digest | 0 | 0 | | performance_schema | mutex_instances | 0 | 0 | | performance_schema | events_waits_summary_by_instance | 0 | 0 | | performance_schema | events_stages_history | 0 | 0 | | mysql | db | 0 | 0 | | performance_schema | events_waits_summary_by_host_by_event_name | 0 | 0 | | mysql | user | 0 | 0 | | mysql | columns_priv | 0 | 0 | | performance_schema | events_statements_history_long | 0 | 0 | | performance_schema | performance_timers | 0 | 0 | | performance_schema | file_instances | 0 | 0 | | performance_schema | events_stages_summary_by_user_by_event_name | 0 | 0 | | performance_schema | events_stages_history_long | 0 | 0 | | performance_schema | setup_actors | 0 | 0 | | performance_schema | cond_instances | 0 | 0 | | mysql | proxies_priv | 0 | 0 | | performance_schema | socket_summary_by_instance | 0 | 0 | | performance_schema | events_statements_current | 0 | 0 | | mysql | event | 0 | 0 | | performance_schema | session_connect_attrs | 0 | 0 | | mysql | plugin | 0 | 0 | | performance_schema | threads | 0 | 0 | | mysql | time_zone_transition_type | 0 | 0 | | mysql | time_zone_name | 0 | 0 | | performance_schema | file_summary_by_event_name | 0 | 0 | | performance_schema | events_waits_summary_by_user_by_event_name | 0 | 0 | | performance_schema | socket_summary_by_event_name | 0 | 0 | | performance_schema | users | 0 | 0 | | mysql | servers | 0 | 0 | | performance_schema | events_waits_summary_by_account_by_event_name | 0 | 0 | | db01 | tbl_emp | 0 | 0 | | performance_schema | events_statements_summary_by_host_by_event_name | 0 | 0 | | db01 | tblA | 0 | 0 | | performance_schema | table_io_waits_summary_by_index_usage | 0 | 0 | | performance_schema | events_waits_current | 0 | 0 | | db01 | user | 0 | 0 | | mysql | procs_priv | 0 | 0 | | performance_schema | events_statements_summary_by_thread_by_event_name | 0 | 0 | | db01 | emp | 0 | 0 | | db01 | tbl_user | 0 | 0 | | db01 | test03 | 0 | 0 | | mysql | slow_log | 0 | 0 | | performance_schema | file_summary_by_instance | 0 | 0 | | db01 | article | 0 | 0 | | performance_schema | objects_summary_global_by_type | 0 | 0 | | db01 | phone | 0 | 0 | | performance_schema | events_waits_summary_by_thread_by_event_name | 0 | 0 | | performance_schema | setup_consumers | 0 | 0 | | performance_schema | socket_instances | 0 | 0 | | performance_schema | rwlock_instances | 0 | 0 | | db01 | tbl_dept | 0 | 0 | | performance_schema | events_statements_summary_by_user_by_event_name | 0 | 0 | | db01 | staffs | 0 | 0 | | db01 | class | 0 | 0 | | mysql | general_log | 0 | 0 | | performance_schema | events_stages_summary_global_by_event_name | 0 | 0 | | performance_schema | events_stages_summary_by_account_by_event_name | 0 | 0 | | performance_schema | events_statements_summary_by_account_by_event_name | 0 | 0 | | performance_schema | table_lock_waits_summary_by_table | 0 | 0 | | performance_schema | hosts | 0 | 0 | | performance_schema | setup_objects | 0 | 0 | | performance_schema | events_stages_current | 0 | 0 | | mysql | time_zone | 0 | 0 | | mysql | tables_priv | 0 | 0 | | performance_schema | table_io_waits_summary_by_table | 0 | 0 | | mysql | time_zone_leap_second | 0 | 0 | | db01 | book | 0 | 0 | | performance_schema | session_account_connect_attrs | 0 | 0 | | db01 | mylock | 0 | 0 | | mysql | func | 0 | 0 | | performance_schema | events_statements_summary_global_by_event_name | 0 | 0 | | performance_schema | events_statements_history | 0 | 0 | | performance_schema | accounts | 0 | 0 | | mysql | time_zone_transition | 0 | 0 | | db01 | dept | 0 | 0 | | performance_schema | events_stages_summary_by_host_by_event_name | 0 | 0 | | performance_schema | events_stages_summary_by_thread_by_event_name | 0 | 0 | | mysql | proc | 0 | 0 | | performance_schema | setup_instruments | 0 | 0 | | performance_schema | host_cache | 0 | 0 | +--------------------+----------------------------------------------------+--------+-------------+ 84 rows in set (0.00 sec)
- 添加锁
lock table 表名1 read(write), 表名2 read(write), ...;- 释放表锁
unlock tables;
读锁示例
- 在 session 1 会话中，给 mylock 表加个读锁
mysql> lock table mylock read; - 在 session1 会话中能不能读取 mylock 表：可以读
mysql> select * from mylock; +----+------+ | id | name | +----+------+ | 1 | a | | 2 | b | | 3 | c | | 4 | d | | 5 | e | +----+------+ 5 rows in set (0.00 sec)
- 在 session1 会话中能不能读取 book 表：并不行
mysql> select * from book; ERROR 1100 (HY000): Table 'book' was not locked with LOCK TABLES
- 在 session2 会话中能不能读取 mylock 表：可以读
mysql> select * from mylock; +----+------+ | id | name | +----+------+ | 1 | a | | 2 | b | | 3 | c | | 4 | d | | 5 | e | +----+------+ 5 rows in set (0.00 sec)
- 在 session1 会话中能不能修改 mylock 表：并不行
mysql> update mylock set name='a2' where id=1; ERROR 1099 (HY000): Table 'mylock' was locked with a READ lock and can't be updated
- 在 session2 会话中能不能修改 mylock 表：阻塞，一旦 mylock 表锁释放，则会执行修改操作
mysql> update mylock set name='a2' where id=1;
结论：
- 当前 session 和其他 session 均可以读取加了读锁的表
- 当前 session 不能读取其他表，并且不能修改加了读锁的表
- 其他 session 想要修改加了读锁的表，必须等待其读锁释放
写锁示例
- 在 session 1 会话中，给 mylock 表加个写锁
mysql> lock table mylock write; - 在 session1 会话中能不能读取 mylock 表：可以
mysql> select * from mylock; +----+------+ | id | name | +----+------+ | 1 | a2 | | 2 | b | | 3 | c | | 4 | d | | 5 | e | +----+------+ 5 rows in set (0.00 sec)
- 在 session1 会话中能不能读取 book 表：不可以
mysql> select * from book; ERROR 1100 (HY000): Table 'book' was not locked with LOCK TABLES
- 在 session1 会话中能不能修改 mylock 表：可以
mysql> update mylock set name='a2' where id=1; Query OK, 0 rows affected (0.00 sec)
- 在 session2 会话中能不能读取 mylock 表：
mysql> select * from mylock;
结论：
- 当前 session 可以读取和修改加了写锁的表
- 当前 session 不能读取其他表
- 其他 session 想要读取加了写锁的表，必须等待其读锁释放
案例结论
- MyISAM在执行查询语句（SELECT）前，会自动给涉及的所有表加读锁，在执行增删改操作前，会自动给涉及的表加写锁
- MySQL的表级锁有两种模式：
1、表共享读锁（Table Read Lock）
2、表独占写锁（Table Write Lock）
| 锁类型 | 可否兼容 | 读锁 | 写锁 |
|---|---|---|---|
| 读锁 | 是 | 是 | 否 |
| 写锁 | 是 | 否 | 否 |
结论：
- 对MyISAM表的读操作（加读锁），不会阻塞其他进程对同一表的读请求，但会阻塞对同一表的写请求。只有当读锁释放后，才会执行其它进程的写操作
- 对MyISAM表的写操作（加写锁），会阻塞其他进程对同一表的读和写操作，只有当写锁释放后，才会执行其它进程的读写操作
- 简而言之，就是读锁会阻塞写，但是不会堵塞读。而写锁则会把读和写都堵塞
表锁分析
- 查看哪些表被锁了，0 表示未锁，1 表示被锁
show open tables;
【如何分析表锁定】可以通过检查table_locks_waited和table_locks_immediate状态变量来分析系统上的表锁定，通过 show status like 'table%'; 命令查看
- Table_locks_immediate：产生表级锁定的次数，表示可以立即获取锁的查询次数，每立即获取锁值加1；
- Table_locks_waited：出现表级锁定争用而发生等待的次数（不能立即获取锁的次数，每等待一次锁值加1），此值高则说明存在着较严重的表级锁争用情况
- 此外，Myisam的读写锁调度是写优先，这也是myisam不适合做写为主表的引擎。因为写锁后，其他线程不能做任何操作，大量的更新会使查询很难得到锁，从而造成永远阻塞
2.2、行锁（偏写）
行锁特点：
- 偏向InnoDB存储引擎，开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高
- InnoDB与MyISAM的最大不同有两点：一是支持事务（TRANSACTION）；二是采用了行级锁
~1.事务复习
事务是由一组SQL语句组成的逻辑处理单元，事务具有以下4个属性，通常简称为事务的ACID属性。
- 原子性（Atomicity）：事务是一个原子操作单元，其对数据的修改，要么全都执行，要么全都不执行。
- 一致性（Consistent）：在事务开始和完成时，数据都必须保持一致状态。这意味着所有相关的数据规则都必须应用于事务的修改，以保持数据的完整性；事务结束时，所有的内部数据结构（如B树索引或双向链表）也都必须是正确的
- 隔离性（Isolation）：数据库系统提供一定的隔离机制，保证事务在不受外部并发操作影响的“独立”环境执行。这意味着事务处理过程中的中间状态对外部是不可见的，反之亦然
- 持久性（Durability）：事务院成之后，它对于数据的修改是永久性的，即使出现系统故障也能够保持
并发事务处理带来的问题
-
更新丢失（Lost Update）：
1、当两个或多个事务选择同一行，然后基于最初选定的值更新该行时，由于每个事务都不知道其他事务的存在，就会发生丢失更新问题一一最后的更新覆盖了由其他事务所做的更新。
2、例如，两个程序员修改同一java文件。每程序员独立地更改其副本，然后保存更改后的副本，这样就覆盖了原始文档。最后保存其更改副本的编辑人员覆盖前一个程序员所做的更改
3、如果在一个程序员完成并提交事务之前，另一个程序员不能访问同一文件，则可避免此问题。 -
脏读（Dirty Reads）：
1、一个事务正在对一条记录做修改，在这个事务完成并提交前，这条记录的数据就处于不一致状态；这时，另一个事务也来读取同一条记录，如果不加控制，第二个事务读取了这些“脏”数据，并据此做进一步的处理，就会产生未提交的数据依赖关系。这种现象被形象地叫做”脏读”
2、一句话：事务A读取到了事务B已修改但尚未提交的的数据，还在这个数据基础上做了操作。此时，如果B事务回滚，A读取的数据无效，不符合一致性要求 -
不可重复读（Non-Repeatable Reads）：
1、一个事务在读取某些数据后的某个时间，再次读取以前读过的数据，却发现其读出的数据已经发生了改变、或某些记录已经被删除了！这种现象就叫做“不可重复读”
2、一句话：事务A读取到了事务B已经提交的修改数据，不符合隔离性 -
幻读（Phantom Reads）：
1、一个事务按相同的查询条件重新读取以前检索过的数据，却发现其他事务插入了满足其查询条件的新数据，这种现象就称为“幻读一句话：事务A读取到了事务B体提交的新增数据，不符合隔离性
2、幻读和脏读有点类似，脏读是事务B里面修改了数据，幻读是事务B里面新增了数据。
事物的隔离级别
- 1、脏读”、“不可重复读”和“幻读”，其实都是数据库读一致性问题，必须由数据库提供一定的事务隔离机制来解决。
- 2、数据库的事务隔离越严格，并发副作用越小，但付出的代价也就越大，因为事务隔离实质上就是使事务在一定程度上“串行化”进行，这显然与“并发”是矛盾的。
- 同时，不同的应用对读一致性和事务隔离程度的要求也是不同的，比如许多应用对“不可重复读”和“幻读”并不敏感，可能更关心数据并发访问的能力
- 4、查看当前数据库的事务隔离级别：
show variables like 'tx_isolation';mysql 默认是可重复读
2.3、页锁
- 开销和加锁时间介于表锁和行锁之间
- 会出现死锁。
- 锁定粒度介于表锁和行锁之间
- 并发度一般
五、主从复制
1、复制的基本原理
- slave会从master读取binlog来进行数据同步
- 三步骤+原理图
2、复制的基本原则
- 每个slave只有一个master
- 每个slave只能有一个唯一的服务器ID
- 每个master可以有多个slave
3、复制的最大问题
- 延时
4、一主一从常见配置
-
mysql版本一致且后台以服务运行
-
主从都配置在[mysqld]结点下，都是小写
-
主机修改my.ini配置文件
1、（必须）主服务器唯一ID server-id=1
2、（必须）log-bin=自己本地的路径/mysqlbin
3、（可选）log-err=自己本地的路径/mysqlerr
4、（可选）basedir=自己本地路径
5、（可选）temdir=自己本地路径
6、（可选）datadir=自己本地路径/Data/
7、read-only=0
8、【可选】设置不要复制的数据库
binlog-ignore-db=mysql
9、【可选】设置需要复制的数据库
binlog-do-db=需要复制的主数据库名字 -
从机修改my.cnf配置文件
1、（必须）从服务器唯一ID server-id=2
2、可选】启用二进制日志 -
因修改过配置文件，请主机+从机都重启后台mysql服务
-
主机从机都关闭防火墙
service iptables stop -
在Windows主机上建立账户并授权slave
1、GRANT REPLICATION SLAVE ON *.* TO 'zhangsan' @ '192.168.1.100【从机数据库IP】' IDENTIFIED BY '123456';
2、flush privileges;
3、查询master的状态show master status
记录下File和Position的值
4、执行完此步骤后不要再操作主服务器MYSQL，防止主服务器状态值变化 -
在Linux从机上配置需要复制的主机
1、CHANGE MASTER TO MASTER_HOST='主机IP', MASTER_USER='zhangsan', MASTER_PASSWORD='123456', MASTER_LOG_FILE='file名字', MASTER_LOG_POS=position数字;
2、启动从服务器复制功能start slave;
3、show slave status
Slave_IO_Running：Yes
Slave_SQL_Running：Yes -
主机新建库、新建表、insert记录，从机复制
-
停止从服务复制功能
stop slave;
这篇关于MySQL 高级的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！
- 2024-11-15MySQL教程：初学者必备的MySQL数据库入门指南
- 2024-11-15MySQL教程：初学者必看的MySQL入门指南
- 2024-11-04部署MySQL集群项目实战：新手入门教程
- 2024-11-04如何部署MySQL集群资料：新手入门指南
- 2024-11-02MySQL集群项目实战：新手入门指南
- 2024-11-02初学者指南：部署MySQL集群资料
- 2024-11-01部署MySQL集群教程：新手入门指南
- 2024-11-01如何部署MySQL集群：新手入门教程
- 2024-11-01部署MySQL集群学习：新手入门教程
- 2024-11-01部署MySQL集群入门：新手必读指南
