mysql高级

安逸0306

已于 2022-03-25 18:29:04 修改

阅读量239

点赞数

分类专栏：学习日记文章标签： mysql docker linux

于 2022-03-17 16:58:18 首次发布

本文链接：https://blog.csdn.net/weixin_44021708/article/details/123535390

版权

学习日记专栏收录该内容

11 篇文章 0 订阅

订阅专栏

前言

本篇文章主要涉及mysql的高级篇，主要是mysql的架构介绍、索引优化分析、查询截取分析、mysql锁机制以及主从复制等

1.简介

1.1 安装

地址

MySQL高级 1 安装架构存储引擎 · 语雀 (yuque.com)https://www.yuque.com/cessstudy/mysql/ikewnr

1.2 MySQL逻辑架构

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

连接层
最上层是一些客户端和连接服务，包含本地sock通信和大多数基于客户端/服务端工具实现的类似于tcplip的通信。主要完成一些类似于连接处理、授权认证、及相关的安全方案。在该层上引入了线程池的概念，为通过认证安全接入的客户端提供线程。同样在该层上可以实现基于SSL的安全链接。服务器也会为安全接入的每个客户端验证它所具有的操作权限。

服务层
第二层架构主要完成大多少的核心服务功能，如SQL接口，并完成缓存的查询，SQL的分析和优化及部分内置函数的执行。所有跨存储引擎的功能也在这一层实现，如过程、函数等。在该层，服务器会解析查询并创建相应的内部解析树，并对其完成相应的优化如确定查询表的顺序是否利用索引等，最后生成相应的执行操作。如果是select语句，服务器还会查询内部的缓存。如果缓存空间足够大，这样在解决大量读操作的环境中能够很好的提升系统的性能。

引擎层
存储引擎层，存储引擎真正的负责了MySQL中数据的存储和提取，服务器通过API与存储引擎进行通信。

存储层
数据存储层，文件系统之上，完成与存储引擎的交互。

1.2.1 存储引擎

查看mysql以提供什么存储引擎 show engines;

查看mysql当前默认的存储引擎 show variables like '%storage_engine%';

2.索引优化和分析

2.1 索引简介

索引是什么？

mysql官方对索引的定义为：索引（index）是帮助mysql高效的获取数据的数据结果

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

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

重点:索引会影响到MysQL查找(WHERE的查询条件）和排序(ORDER BY)两大功能!

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

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

索引的优势和劣势优势

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

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

2.2 mysql的索引分类

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

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

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

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

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

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

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

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

2.3 mysql索引的数据结构

BTree索引
Hash索引
full-text全文索引
R-Tree索引

BTree索引：

初始化介绍

浅蓝色的称之为一个磁盘块，可以看到每个磁盘块包含几个数据项（深蓝色所示）和指针（黄色所示)
如磁盘块1包含数据项17和35，包含指针P1、P2、P3,
P1表示小于17的磁盘块，P2表示在17和35之间的磁盘块，P3表示大于35的磁盘块。

真实的数据存在于叶子节点即3、5、9、10、13、15、28、29、36、60、75、79、90、99。

非叶子节点只不存储真实的数据，只存储指引搜索方向的数据项，如17、35并不真实存在于数据表中。

查找过程

如果要查找数据项29，那么首先会把磁盘块1由磁盘加载到内存，此时发生一次IO。
在内存中用二分查找确定29在17和35之间，锁定磁盘块1的P2指针，内存时间因为非常短（相比磁盘的IO）可以忽略不计，通过磁盘块1的P2指针的磁盘地址把磁盘块3由磁盘加载到内存，发生第二次IO，
29在26和30之间，锁定磁盘块3的P2指针，通过指针加载磁盘块8到内存，发生第三次IO，同时内存中做二分查找找到29，结束查询，总计三次IO。

真实的情况是，3层的b+树可以表示上百万的数据，如果上百万的数据查找只需要三次IO，性能提高将是巨大的，如果没有索引，每个数据项都要发生一次IO，那么总共需要百万次的IO，显然成本非常非常高。

2.4 索引情况

分为有索引和无索引
索引（查找，排序）

需要建立索引的情况有：

1.主键自动建立唯一索引
2.频繁作为查询条件的字段应该创建索引
3.查询中与其它表关联的字段，外键关系建立索引
4.单键/组合索引的选择问题，who?(在高并发下倾向创建组合索引)
5.查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度
6.查询中统计或者分组字段（分组都是需要排序的）

不需要简历索引的情况有：

1.表记录太少
2.经常增删改的表（提高了查询速度，但是会同时江低更新表的速度，对表进行更新的时候，mysql还要保存数据，还要保存一下索引文件）
3.数据重复且分布平均的表字段，因此应该只为最经常查询和最经常排序的数据列建立索引。注意，如果某个数据列包含许多重复的内容，为它建立索引就没有太大的实际效果。
4.频繁更新的字段不适合创建索引，因为每次更新不单单是更新了记录还会更新索引
5.Where条件里用不到的字段不创建索引
一个索引的选择性越接近于1，这个索引的效率就越高

2.5 性能分析

MySQL Query Optimizer：Mysql中有专门负责优化SELECT语句的优化器模块，主要功能:通过计算分析系统中收集到的统计信息，为客户端请求的Query提供他认为最优的执行计划，但不见得最优

MySQL常见瓶颈（通过cpu 、io 、服务器的硬件进行分析）

1.CPU：CPU在饱和的时候一般发生在数据装入内存或从磁盘上读取数据时候
2.IO：磁盘I/O瓶颈发生在装入数据远大于内存容量的时候
3.服务器硬件的性能瓶颈：top，free，iostat和vmstat来查看系统的性能状态

EXPLAIN能干嘛
可以查看以下信息
id :表的读取顺序
select_type :数据读取操作的操作类型possible_keys :哪些索引可以使用
key :哪些索引被实际使用
ref:表之间的引用
rows:图张表有多少行被优化器查询

EXPLAIN字段

id表的读取和加载顺序
值有以下三种情况：
1.id相同，执行顺序由上至下
2.id不同，如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行
3.id相同不同，同时存在。永远是id大的优先级最高，id相等的时候顺序执行

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

具体的类型有：

SIMPLE - 简单的select查询,查询中不包含子查询或者UNION。
PRIMARY - 查询中若包含任何复杂的子部分，最外层查询则被标记为。（最外层）
SUBQUERY - 在SELECT或WHERE列表中包含了子查询。（内层）越内层等级越高，越先执行

DERIUED - 在FROM列表中包含的子查询被标记为DERIVED（衍生）MySQL会递归执行这些子查询，把结果放在临时表里。

UNION - 若第二个SELECT出现在UNION之后，则被标记为UNION；若UNION包含在FROM子句的子查询中外层SELECT将被标记为：DERIVED。
UNION RESULT - 从UNION表获取结果的SELECT。（合并之后的查询就是这个选项）

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

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

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

const：表示通过索引一次就找到了，const用于比较primary key或者unique索引。因为只匹配一行数据，所以很快如将主键置于where列表中，MySQL就能将该查询转换为一个常量。（单表中的主键id，一张表一个条件）

eq_ref：唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫（联表唯一，和上面的区别在于索引数量不同）

ref：非唯一性索引扫描，返回匹配某个单独值的所有行，本质上也是一种索引访问，它返回所有匹配某个单独值的行，然而，它可能会找到多个符合条件的行，所以他应该属于查找和扫描的混合体。（上面的条件是一对一，这个条件是一对多）

range：只检索给定范围的行,使用一个索引来选择行。key列显示使用了哪个索引一般就是在你的where语句中出现了between、<、>、in等的查询。这种范围扫描索引扫描比全表扫描要好，因为它只需要开始于索引的某一点，而结束语另一点，不用扫描全部索引。

index：Full Index Scan，index与ALL区别为index类型只遍历索引树。这通常比ALL快，因为索引文件通常比数据文件小（也就是说虽然all和Index都是读全表，但index是从索引中读取的，而all是从硬盘中读的）。

all：Full Table Scan，将遍历全表以找到匹配的行。
（也就是通过 select * 全部数据读取）

possible_keys 、key 和 key_len（可能用到索引、实际用到索引、长度）
possible_keys（理论上要多少索引）
显示可能应用在这张表中的索引，一个或多个。
查询涉及到的字段若存在索引，则该索引将被列出，但不一定被查询实际使用。

key（实际用到的索引）
实际使用的索引。如果为NULL，则没有使用索引
查询中若使用了覆盖索引，则该索引仅出现在key列表中

所谓的覆盖索引：查询时未发生回表。查询的字段只能建立在索引的字段中

key_len （估计用到的长度）
表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度。在不损失精确性的情况下，长度越短越好
key_len显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出的

ref（条件查询）

（显示使用到的条件查询，如果是常量就为const）
显示索引的哪一列被使用了，如果可能的话，是一个常数。哪些列或常量被用于查找索引列上的值。

由key_len可知t1表的idx_col1_col2被充分使用，col1匹配t2表的col1，col2匹配了一个常量，即 ‘ac’。
查询中与其它表关联的字段，外键关系建立索引。

rows（行数）

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

每张表被优化器查询
把不合适的索引删除，慢慢优化

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

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

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

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

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

Using where :表明使用了WHERE过滤.

using join buffer :使用了连接缓存
impossible where : WHERE子句的值总是false，不能用来获取任何元组

2.6 索引分析

2.6.1 单表索引分析

数据准备：

DROP TABLE IF EXISTS `article`;

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

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

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

select id,author_id from article where category_id=1 and comments>1 order by views desc limit 1;

1.分析执行计划：

explain select id,author_id from article where category_id=1 and comments>1 order by views desc limit 1;

2.创建索引 idx_article_cvv

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

3.查看当前索引

4.查看加完索引后sql语句的执行计划

创建复合索引 idx_article_ccv之后，虽然解决了全表扫描的问题，但是在order by排序的时候没有用到索引，MySQL居然还是用的Using filesort ，为什么?

5.我们试着修改sql语句将> 改成= 查看sql的执行计划

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

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

6、知道了范围之后的索引会失效，原来的索引idx_article_ccv最后一个字段views 会失效，那么如果删除这个索引，创建idx_article_cv索引呢?

删除索引
DROP INDEX idx_article_ccv ON article;

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

7.查看修改索引后的执行计划

主要的原因是：

这是因为按照BTree索引的工作原理，先排序category_id，如果遇到相同的category_id则再排序comments,如果遇到相同的comments 则再排序views。

当comments字段在联合索引里处于中间位置时，因comments > 1条件是一个范围值(所谓range)，MySQL无法利用索引再对后面的views部分进行检索，即range类型查询字段后面的索引无效。

所以建立复合索引是对的
但是其思路要避开中间那个范围的索引进去
只加入另外两个索引即可create index idx_article_cv on article(category_id, views);

2.6.2 两表索引优化

数据准备：

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

1.不创建索引执行sql计划

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

2.右表创建索引

CREATE INDEX idx_book_card ON book(card);

执行sql计划

3.删除右表索引，左表创建索引，查看执行计划

drop index idx_book_card ON book;

CREATE INDEX idx_class_card ON class(card);

所以：索引两表优化，左连接右表建索引，右连接左表建索引

2.6.3 三表索引优化

数据准备：（前面例子已经创建了二张表，现在只需要创建一张即可）

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

1.执行计划

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

2.左连接在右边添加索引分别在 book 和phone上添加索引

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

因此索引最好设置在需要经常查询的字段中

Join语句的优化

1.尽可能减少Join语句中的NestedLoop的循环总次数：“永远用小结果集驱动大的结果集”。

2.优先优化NestedLoop的内层循环

3.保证Join语句中被驱动表上Join条件字段已经被索引。

4.当无法保证被驱动表的Join条件字段被索引且内存资源充足的前提下，不要太吝惜JoinBuffer的设置。

2.7 索引失效

1. 全值匹配我最爱

2. 最佳左前缀法则

3.不在索引列上做任何操作（计算，函数，（自动or手动类型转换）），会导致索引失效而转向全表扫描

4.索引中范围条件右边的字段会全部失效

5.尽量使用覆盖索引（只访问索引的查询，索引列和查询列一致），减少 select *

6..mysql在使用！=或者<> 的时候无法使用索引导致全表扫描

7. is null,is not null 也无法使用索引

8.like以通配符开头%abc索引失效会变成全表扫描（使用覆盖索引就不会全表扫描）

9.字符串不加单引号索引失效

10.少用or，用它连接时会失效

2.7.1 索引相关题目

2.7.2 索引相关面试题

数据准备：

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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


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

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

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

最好索引如何创建就如何使用，按顺序使用，避免mysql自己再去翻译一次

2.7.3 总结

索引优化的一般性建议
1对于单值索引，尽量选择针对当前query过滤性更好的索引
2在选择复合索引的时候，当前query中过滤性最好的字段在索引字段顺序中，位置越靠前越好·在3.选择复合索引的时候，尽量选择可以能够包含当前query 中的where子句中更多字段的索引

4尽可能通过分析统计信息和调整query的写法来达到选择合适索引的目的

优化的口诀如下：

全值匹配我最爱，最左前缀要遵守；

带头大哥不能死，中间兄弟不能断；

索引列上少计算，范围之后全失效；

LIKE 百分写最右，覆盖索引不写 *；

不等空值还有 OR，索引影响要注意；

VAR 引号不可丢， SQL 优化有诀窍

3.查询截取分析

SQL调优过程：

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

总结：

慢查询的开启并捕获
explain + 慢SQL分析
show profile查询SQL在Mysql服务器里面的执行细节和生命周期情况
SQL数据库服务器的参数调优。

3.1 in和exists

小表驱动大表
RBO原理：

当B表的数据集必须小于A表的数据集时，用in优于exists

select * from A where id in (select id from B)
等价于:
for select id from B
for select * from A where A.id = B.id

当A表的数据集系小于B表的数据集时，用exists优于in

select * from A where exists (select 1 from B where B.id = A.id)
等价于：
for select * from A
for select * from B where B.id = A.id

关于exists的关键字

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

提示：

1.EXSTS(subquey)只返回TRUE或FALSE，因此子查询中的SELECT * 也可以是 SELECT 1 或select ‘X’，官方说法是实际执行时会忽略SELECT清单，因此没有区别。
2.EXISTS子查询的实际执行过程可能经过了优化而不是我们理解上的逐条对比，如果担忧效率问题，可进行实际检验以确定是否有效率问题。
3.EXISTS子查询往往也可以用条件表达式，其他子查询或者JOIN来替代，何种最优需要具体问题具体分析

3.2 orderby优化

数据准备

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

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

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

案例

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

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

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

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

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

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

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

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

MySQL支持二种方式的排序：FileSort和lIndex

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

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

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

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

如果不在索引列上，mysql的filesort有两种算法：双路排序、单路排序

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

从磁盘取排序字段，在buffer进行排序，再从磁盘取其他字段。取一批数据，要对磁盘进行了两次扫描，众所周知，I\O是很耗时的，所以在mysql4.1之后，出现了第二种改进的算法，就是单路排序。

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

单路排序问题

在sort_buffer中，单路排序比双路排序要多占用很多空间，因为单路排序是把所有字段都取出,所以有可能取出的数据的总大小超出了sort_buffer的容量，导致每次只能取sort_buffer容量大小的数据，进行排序（创建tmp文件，多路合并)，排完再取取sort_buffer容量大小，再排……从而多次I/O。
本来想省一次I/O操作，反而导致了大量的I/O操作，反而得不偿失。

优化策略

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

为什么设置sort_buffer_size、max_length_for_sort_data参数能优化排序？

主要是因为可以提高Order By的速度

具体原因如下：

Order by时select * 是一个Query需要的字段，这点非常重要。在这里的影响是;

1. 当Query的字段大小总和小于max_length_for_sort_data而且排序字段不是TEXT|BLOB类型时，会用改进后的算法——单路排序，否则用老算法——多路排序。
2.两种算法的数据都有可能超出sort_buffer的容量，超出之后，会创建tmp文件进行合并排序，导致多次IO，但是用单路排序算法的风险会更大一些，所以要提高sort_buffer__size。
3.尝试提高sort_buffer_size，不管用哪种算法，提高这个参数都会提高效率，当然，要根据系统的能力去提高，因为这个参数是针对每个进程的。
4.尝试提高max_length_for_sort_data，提高这个参数，会增加用改进算法的概率。但是如果设的太高，数据总容量超出sort_buffer_size的概率就增大，明显症状是高的磁盘I/O活动和低的处理器使用率。

3.3 GroupBy优化 GroupBy优化（和order by差不多）

1.group by实质是先排序后进行分组，遵照索引建的最佳左前缀。
2.当无法使用索引列，增大max_length_for_sort_data参数的设置 + 增大sort_buffer_size参数的设置。
3.where高于having，能写在where限定的条件就不要去having限定了。

4. 锁机制

4.1 定义

锁是计算机协调多个进程或线程并发访问某一资源的机制。

在数据库中，除传统的计算资源（如CPU、RAM、I/O等）的争用以外，数据也是一种供许多用户共享的资源。如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题，锁冲突也是影响数据库并发访问性能的一个重要因素。从这个角度来说，锁对数据库而言显得尤其重要，也更加复杂。

锁的分类

从对数据操作的类型

读锁（共享锁）：针对同一份数据，多个读操作可以同时进行而不会互相影响。
写锁（排它锁）：当前写操作没有完成前，它会阻断其他写锁和读锁。

从对数据操作的粒度分

表锁
行锁

4.2 表锁（读写锁）

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

4..2.1 环境准备：

# 1、创建表
CREATE TABLE IF NOT EXISTS `book`(
  `bookid` INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT '主键',
  `card` INT(10) UNSIGNED NOT NULL COMMENT '分类'
) COMMENT '书籍';

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

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

4.2.2 锁的命令

1.查看数据库表锁命令：

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

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

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

3.释放锁

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

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

读锁案例

案例1：
1. 如果A加了读锁

A可以查自已的表，但不能更新或者增加自已的表数据。

B可以查A的表，但不能更新或者增加A的表数据（会阻塞），直到A释放了读锁

直到A释放锁，B才可以修改不然就一直阻塞。

案例2：

2. 如果A加了写锁

A可以查自已的表，也可以更新或者添加自已的表数据

B不可以查A的表，也不能更新或者增加A的数据（会阻塞），直到A释放了写锁

通过如上的加读写锁：

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

对MyISAM表的写操作〈加写锁），会阻塞其他进程对同一表的读和写操作，只有当写锁释放后，才会执行其它进程的读写操作。

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

可以通过检查table_locks_waited和table_locks_immediate状态变量来分析系统上的表锁定
通过这个命令行show status like 'table_locks%';

Table_locks_immediate：产生表级锁定的次数，表示可以立即获取锁的查询次数，每立即获取锁值加1 ;
Table_locks_waited：出现表级锁定争用而发生等待的次数(不能立即获取锁的次数，每等待一次锁值加1)，此值高则说明存在着较严重的表级锁争用情况；
此外，MyISAM的读写锁调度是写优先，这也是MyISAM不适合做写为主表的引擎。因为写锁后，其他线程不能做任何操作，大量的更新会使查询很难得到锁，从而造成永远阻塞。

4.3 行锁

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

InnoDB与MyISAM的最大不同有两点：一是支持事务(TRANSACTION)；二是采用了行级锁

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

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

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

怎么操作行锁，只需要将其自动提交关闭即可
通过命令行的 SET autocommit=0;该命令是开启手动提交事务

案例1：

两个进程都开启手动提交，而且修改同一行数据

如果A进程修改了事务，B事务打算修改的话，进程会被阻塞

只有当A提交了事务之后，B进程才会解除阻塞

在这里插入图片描述

案例2：

两个进程都开启手动提交，修改不同行数据

都是可以的，但是事务不一样，只有都提交的时候，才会更新

如果提交事务的过程中，类型出错
类型转换导致索引失效，失效之后只能全表锁定，因为innodb是索引加锁。行锁变表锁
由于表锁了，即使A提交了事务，B想修改事务（即使不同行也回阻塞），也会被阻塞。只有等他到表锁他自动解除

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

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

如何分析行锁定

通过检查lnnoDB_row_lock状态变量来分析系统上的行锁的争夺情况

通过命令行进行分析：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：系统启动后到现在总共等待的次数；（等待总次数）

具体可以通过如下进行优化：

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

索引失效行锁变表锁

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

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

间隙锁

当我们用范围条件而不是相等条件检索数据，并请求共享或排他锁时，InnoDB会给符合条件的已有数据记录的索引项加锁，对于键值在条件范围内但并不存在的记录，叫做“间隙（GAP）”。加了间隙锁，但是想添加间隙的东西，发现不可修改

InnoDB也会对这个“间隙”加锁，这种锁机制就是所谓的间隙锁（Next-Key锁）。

危害

因为Query执行过程中通过过范围查找的话，他会锁定整个范围内所有的索引键值，即使这个键值并不存在。
间隙锁有一个比较致命的弱点，就是当锁定一个范围键值之后，即使某些不存在的键值也会被无辜的锁定，而造成在锁定的时候无法插入锁定键值范围内的任何数据。在某些场景下这可能会对性能造成很大的危害。

补充：
如何锁定一行的数据（排他锁）

begin
sql数据 for update
commit

只有commit 才不会阻塞

添加共享锁是：
加共享锁可以使用select ... lock in share mode语句

`总结`

表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低；行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高；页面锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般。适用：从锁的角度来说，表级锁更适合于以查询为主，只有少量按索引条件更新数据的应用，如Web应用；而行级锁则更适合于有大量按索引条件并发更新少量不同数据，同时又有并发查询的应用，如一些在线事务处理（OLTP）系统。

5.mysql主从复制

1.master将改变记录到二进制日志(binary log)。这些记录过程叫做二进制日志事件，binary log events;
2.slave将master的binary log events拷贝到它的中继日志(relay log) ;
3.slave重做中继日志中的事件，将改变应用到自己的数据库中。MySQL复制是异步的且串行化的

复制的原则主要有：