大佬教程收集整理的这篇文章主要介绍了MySQL高级学习笔记，大佬教程大佬觉得挺不错的，现在分享给大家，也给大家做个参考。
请参考博客：在Linux中安装MySQL
Windows系统下mysql的配置文件为my.ini文件c;Linux系统下mysql的配置文件为/etc/my.cnf文件。
@H_538_5@mySQL主要配置文件如下：总体架构：
2、业务逻辑处理层：主要完成大多数的核心服务功能c;如SQL接口c;并完成缓存的查询c;SQL的分析和优化及部分内置函数的操作。所有跨操作引擎的功能也在这一层实现c;如过程c;函数等。在该层服务器会解析查询并创建相应的内部解析树c;并对其完成相应的优A化如确定查询表的顺序c;是否利用索引等c;最后生成相应的执行操作。如果是SELECT语句c;服务器还会查询内部的缓存c;如果缓存空间足够大c;这样在解决大量读操作的环境中能够很好的提升系统的性能。注：提供SQL操作的接口c;对SQL脚本按一定规则进行解析c;并通过SQL优化器优化执行顺序c;对于查询的语句还会进入缓存区c;提升系统的性能。
3、数据存储引擎层：存储引擎真正的负责了MySQL中数据的存储和提取c;服务器通过API与存储引[擎进行通信c;不同的存储引|擎具有的功能不同c;这样我们可以根据自己的实际需要进行选选取。注：存储引擎都是可插拔的c;每个存储引l擎所提供的服务都有所差异c;所以我们需要根据具体的业务需要c;选择合适的存储引[擎c;常用的只有两种MyISAM和lnnoDB。
4、数据存储层：主要是将数据存储在运行于裸设备的文件系统上c;并完成与存储引擎的交互。注：将数据存储到磁盘上c;并协同存储引擎对数据进行读写操作。
数据库逻辑结构共分为四层c;分别是连接层（线程连接池)、业务逻辑处理层(SQL解析读取)、数据存储引擎层(存储擎)、数据存储层〈(数据存储)和其它的数据库相比c;R_730_11845@ySQL有点与众不同c;它的架构可以在多种不同场景中应用并发挥良好作用c;主要体现在存储引擎的架构上c;插件式的存储引擎架构将查询处理和其它的系统任务以及数据的存储提取相分离c;这种架构可以根据业务的需求和实际需要选择合适的存储引擎。
可以通过如下命令查看支持的存储引擎：
show ENGInes; #查看支持的存储引擎
通过如下命令查看当前默认的存储引擎：
show variables like '%storage_ENGInes%'; #查看当前默认的存储引擎
①手写
通常我们按照需求写的SQL查询语句是这样的：
SELECT DISTinCT查询列表
FROM 左表 INNER|LEFT|RIGHT
JOIN 右表 ON 连接条件
WHERE 分组前的筛选条件
GROUP BY 分组列表
HAVING 分组后的筛选条件
ORDER BY 排序列表
LIMIT 分页参数
我们手写的SQL中SELECT在最前面的位置。
②机读
随着 Mysql 版本的更新换代c;其优化器也在不断的升级c;优化器会分析不同执行顺序产生的性能消耗不同而动态调整执行顺序。下面是经常出现的查询顺序：
FROM 左表 INNER|LEFT|RIGHT
JOIN 右表 ON 连接条件
WHERE 分组前的筛选条件
GROUP BY 分组列表
HAVING 分组后的筛选条件
SELECT DISTinCT查询列表
ORDER BY 排序列表
LIMIT 分页参数
而在机读中FROM是最先执行的。
③总结
A表独有部分+AB两表的公有部分。
-- sql语句如下：
SELECT 查询列表 FROM A LEFT JOIN B ON A.key=B.key# 其中a.key=B.key指的是连接条件
-- 注：
-- 左外连接中左边的是主表c;右边的是从表
-- sql语句如下：
SELECT 查询列表 FROM A RIGHT JOIN B ON A.key=B.key# 其中a.key=B.key指的是连接条件
-- 注：
-- 右外连接中右边的是主表c;左边的是从表
获取的是两张表的公有部分。
-- sql语句如下：
SELECT 查询列表 FROM A INNER JOIN B ON A.key=B.key# 其中a.key=B.key指的是连接条件
-- 注：
-- 内连接求的是多张表的交集部分
SELECT 查询列表 FROM A LEFT JOIN B ON A.key=B.key WHERE B.key IS NULL;# 将从表B的连接条件作为NULL值判断
SELECT 查询列表 FROM A RIGHT JOIN B ON A.key=B.key WHERE A.key IS NULL;# 将从表A的连接条件作为NULL值判断
注意：MySQL不支持full join。
SELECT 查询列表 FROM A FULL JOIN B ON A.key=B.key;# 全外连接就是求并集
# 可以间接完成效果
SELECT 查询列表 FROM A LEFT JOIN B ON A.key=B.key
UNION
SELECT 查询列表 FROM A RIGHT JOIN B ON A.key=B.key
SELECT 查询列表 FROM A FULL JOIN B ON A.key=B.key WHERE A.key IS NULL OR B.key IS NULL;
实例如下：
-- 左外连接
SELEct * from beauty left join boys on beauty.boyfriend_id=boys.id;
-- 右外连接
SELEct * from beauty right join boys on beauty.boyfriend_id=boys.id;
-- 内连接
SELEct * from beauty inner join boys on beauty.boyfriend_id=boys.id;
-- 左外连接去除交集
SELEct * from beauty left join boys on beauty.boyfriend_id=boys.id where boys.id IS NULL;
-- 右外连接去除交集
SELEct * from beauty right join boys on beauty.boyfriend_id=boys.id where beauty.boyfriend_id IS NULL;
-- 全外连接(MySQL不能运行)
SELEct * from beauty full join boys on beauty.boyfriend_id=boys.id;
-- 全外连接去除交集(MySQL不能运行)
SELEct * from beauty full join boys on beauty.boyfriend_id=boys.id where boys.id IS NULL OR beauty.boyfriend_id IS NULL;
在数据之外c;数据库系统还维护着满足特定查找算法的数据结构c;这些数据结构以某种方式引用（指向）数据c;这样就可以在这些数据结构上实现高级查找算法。这种数据结构c;就是索引。下图就是一种可能的索引方式示例：
我们平常所说的索引c;如果没有特别指明c;都是指B树(多路搜索树c;并不一定是二叉的)结构组织的索引。其中聚集索引c;次要索引c;覆盖索引c;复合索引c;前缀索引c;唯一索引默认都是使用B+树索引c;统称索引。当然c;除了B+树这种类型的索引之外c;还有哈希索引(hashindeX)等。
一般来说索引本身也很大c;不可能全部存储在内存中c;因此索引往往以索引文件的形式存储的磁盘上。
优势：
劣势：
索引可以分为单值索引、唯一索引、主键索引和复合索引。
创建单值索引的语法如下：
-- 创建情况一：在创建表时创建
CREATE TABLE 表名(
字段名 字段类型 [约束],
字段名 字段类型 [约束],
...
字段名 字段类型 [约束],
KEY(字段名)# 这一句就是创建单列索引的语句c;直接在KEY()中写字段名即可
);
-- 创建情况二：单独创建单值索引
CREATE INDEX 索引名 ON 表名(字段名);
# 注释：
# 1.索引名通常是idx_表名_字段名这样的格式c;比如IDx_user_name
# 2.单独创建索引的示例：CREATE INDEX idx_user_name user(nmaE);
即索引列的值必须唯一c;但允许有空值。
创建唯一索引的语法如下：
-- 创建情况一：在创建表时创建
CREATE TABLE 表名(
字段名 字段类型 [约束],
字段名 字段类型 [约束],
...
字段名 字段类型 [约束],
UNIQUE(字段名)# 这一句就是创建唯一索引的语句c;直接在UNIQUE()中写字段名即可
);
-- 创建情况二：单独创建唯一索引
CREATE UNIQUE INDEX 索引名 ON 表名(字段名);
# 注释：
# 1.索引名通常是idx_表名_字段名这样的格式c;比如IDx_user_name
# 2.单独创建唯一索引的示例：CREATE UNIQUE INDEX idx_user_id user(id);
设定某字段为主键后c;数据库会自动建立索引c;innodb存储引擎的主键为聚簇索引。
创建索引的基本语法如下：
-- 创建情况一：在创建表时创建
CREATE TABLE 表名(
字段名 字段类型 [约束],
字段名 字段类型 [约束],
...
字段名 字段类型 [约束],
PRIMary KEY(字段名)# 这一句就是创建主键索引的语句c;直接在PRIMary KEY()中写字段名即可
);
-- 创建情况二：单独创建主键索引
ALTER TABLE 表名 ADD PRIMary KEY 表名(字段名);
# 注释：
# 1.创建示例：alter table user ADD PRIMary KEY user(id);
-- 删除主键索引
ALTER TABLE 表名 DROP PRIMary KEY;
# 注释：
# 1.删除示例：alter table user DROP PRIMary KEY;
# 2.如果要修改主键索引c;那么必须先删除掉原索引c;再新建索引
即一个索引包含多个列。
创建的基本语法如下：
-- 创建情况一：在创建表时创建
CREATE TABLE 表名(
字段名 字段类型 [约束],
字段名 字段类型 [约束],
...
字段名 字段类型 [约束],
KEY(字段名,字段名,..)# 这一句就是创建复合索引的语句c;直接在KEY()中写多个字段名即可
);
-- 创建情况二：单独创建复合索引
CREATE INDEX 索引名 ON 表名(字段名,字段名,...);
①创建索引
-- 语法
CREATE [UNIQUE] INDEX 索引名 ON 表名(字段名[,字段名,字段名,..]);
②删除索引
-- 语法
DROP INDEX 索引名 ON 表名;
③查看索引
-- 语法
SHOW INDEX FROM 表名;
④修改索引
-- 语法
-- 通过修改语句添加主键索引
ALTER TABLE 表名 ADD PRIMary KEY(字段名);# 该语句添加一个主键c;这意味着索引值必须是唯一的c;且不能为 NULL。
-- 通过修改语句添加普通索引
ALTER TABLE 表名 ADD INDEX 索引名(字段名,[字段名,..]);# 添加普通索引
-- 通过修改语句添加全文索引
ALTER TABLE 表名 ADD FULLTEXT 索引名(字段名,[字段名,..]);# 该语句指定了索引为FULLTEXTc;用于全文索引
1、Mysql中有专门负责优化SELECT语句的优化器模块c;主要功能:通过计算分析系统中收集到的统计信息c;为客户端请求的Query提供他认为最优的执行计划（他认为最优的数据检索方式c;但不见得是DBA认为是最优的c;这部分最耗费时间)
2、当客户端向MySQL请求一条Queryc;命令解析器模块完成请求分类c;区别出是SELECT并转发给MySQLQuery Optimizer时c;R_730_11845@ySQL Query Optimizer首先会对整条Query进行优化c;处理掉一些常量表达式的预算c;直接换算成常量值。并对Query中的查询条件进行简化和转换c;如去掉一些无用或显而易见的条件、结构调整等。然后分析Query中的Hint信息(如果有)c;看显示Hint信息是否可以完全确定该Query的执行计划。如果没有Hint或Hint信息还不足以完全确定执行计划c;则会读取所涉及对象的统计信息c;根据Query进行写相应的计算分析c;然后再得出最后的执行计划。
使用EXPLAIN关键字可以模拟优化器执行SQL查询语句c;从而知道MySQL是如何处理你的sql语句的。分析你的查询语句或是表结构的性能瓶颈。
-- 语法
EXPLAIN 查询语句;
-- 示例
EXPLAIN SELECT * FROM user;
查询出来所包含的信息如下：
示例：
SELEct查询的序列号,包含一组数字c;表示查询中执行SELEct子句或操作表的顺序
三种情况：
说了表的读取顺序：先加载括号内的子查询。
SELEct_type有如下类型值：
查询的类型SELEct_type主要用于区别普通查询、联合查询、子查询等复杂查询。
显示这一行的数据是关于哪张表的。
一般上百万条数据才进行优化。
type是访问类型排列c;显示查询使用了何种类型c;跟索引优化有很大的关系c;需掌握。type的值有如下几种：
从最好到最差依次是：system>const>eq_ref>ref>range>index>all。
注：一般来说c;得保证查询至少达到range级别c;最好能达到ref。
如果百万级别或千万级别的记录查询出现的type是allc;那么就需要考虑优化了。
显示可能应用在这张表中的索引c;一个或多个。查询涉及到的字段上若存在索引c;则该索引将被列出c; 但不一定被查询实际使用。是列出理论上可能会被用到的索引。
列出实际上被用到的索引。如果为nullc;则没有使用索引。
通常同possible_keys来说c;possible_keys是理论上可能会被用到的索引c;而key是实际上用到的索引。例如请客估计应该来10人c;这是possible_keysc;而当天实际上来了6人c;这是key。
若查询中使用了覆盖索引c;则该索引和查询的SELEct字段重叠。所谓的覆盖索引就是查询的字段正好是复合索引中的字段列表c;那么就直接在索引中查找c;而不是从全表中查找c;如下图：
表示索引中使用的字节数c;可通过该列计算查询中使用的索引的长度。 key_len 字段能够帮你检查是否充分的利用上了索引。ken_len 越长c;说明索引使用的越充分。但使用的长度越小越好。
如何计算：
第一组：key_len=age 的字节长度+name 的字节长度=4+1 + (20*3+2)=5+62=67
第二组：key_len=age 的字节长度=4+1=5
显示索引的哪一列被使用了c;如果可能的话c;是一个常数。哪些列或常量被用于查找索引列上的值。
根据表统计信息及索引选用情况c;大致估算出找到所需的记录所需要读取的行数。
rows 列显示 MySQL 认为它执行查询时必须检查的行数。越少越好！
extra中包含不适合在其他列中显示c;但又十分重要的额外信息。
它可能的值有如下几种情况（重点关于①、②、③的情况）：
①Using filesort
说明mysql 会对数据使用一个外部的索引排序c;而不是按照表内的索引顺序进行读取。MySQL 中无法利用索引完成的排序操作称为“文件排序”。
注：出现了这个值就应该对查询语句进行优化了。
出现 filesort 的情况：
查询中排序的字段c;排序字段若通过索引去访问将大大提高排序速度（即将排序的字段添加到索引）。
②Using temporary
使了用临时表保存中间结果,MySQL 在对查询结果排序时使用临时表。常见于排序 order by 和分组查询 group by。
注：出现了这个值就应该对查询语句进行优化了。
优化前：
将group by、order by后面的字段添加到索引中去c;如果已有索引c;那么它们后面的字段的顺序应该跟复合索引中的字段顺序一样。
优化后：
③Using index
Using index 代表表示相应的 SELEct 操作中使用了覆盖索引(Covering IndeX)c;避免访问了表的数据行c;效率不错！如果同时出现 using wherec;表明索引被用来执行索引键值的查找;如果没有同时出现 using wherec;表明索引只是用来读取数据而非利用索引执行查找。
利用索引进行了排序或分组。
④Using where
表明使用了 where 过滤。
⑤Using join buffer
使用了连接缓存。
⑥impossible where
where 子句的值总是 falsec;不能用来获取任何元组。比如查找一个人名字既为"张三"又为"李四"c;不可能筛选成功。
⑦ SELEct tables optimized away
在没有 GROUPBY 子句的情况下c;基于索引优化 MIN/MAX 操作或者对于 MyISAM 存储引擎优化 COUNT(*)操作c;不必等到执行阶段再进行计算c;查询执行计划生成的阶段即完成优化。
在 innodb 中：
在 Myisam 中：
⑧disTinct
优化disTinct操作c;在找到第一匹配的元组后即停止找同样值的动作。
例子练习：
①创建测试表
测试用的数据如下：
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` VARBINARY(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'),
(3,3,3,3,'3','3');
②查询及执行情况分析
查询需求：查询category_id为1并且comments>1的情况下c;观看数量最多的文章
explain SELEct id,author_id from article where category_id = 1 and comments > 1 order by views desc limit 1;
执行情况：
③如何优化
考虑建立表索引来进行优化。两种方式建立索引都可以c;先创建索引试试：
ALTER TABLE article ADD INDEX idx_article_ccv (category_id, comments, views); --第一种方式
CREATE INDEX idx_article_ccv ON article (category_id, comments, views) ; --第二种方式
再次查看执行计划：使用了索引c;type也由all变成了range。
DROP INDEX idx_article_ccv ON article; -- 删除索引
CREATE INDEX idx_article_ccv ON article (category_id,views); -- 重建索引
@H_351_2624@
解决了Using filesort问题c;也使用了索引。
总结：对单表中要查询的字段添加索引c;对如果条件是一个范围值的话就不添加索引。
①创建测试表
测试用的数据如下：
CREATE TABLE IF NOT EXISTS `class`(
`id` INT(10) UNSIGNED NOT NULL PRIMary KEY AUTO_INCREMENT,
`card` INT (10) UNSIGNED NOT NULL
);
CREATE TABLE IF NOT EXISTS `book`(
`bookid` INT(10) UNSIGNED NOT NULL PRIMary KEY AUTO_INCREMENT,
`card` INT (10) UNSIGNED NOT NULL
);
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表和book表
EXPLAIN SELECT * from class LEFT JOIN book ON class.card = book.card;
发现是全表扫描c;即type为ALLc;那么需要进行优化。
如何优化
由于是LEFT JOINc;所以左表class表是主表c;因此第一次添加索引的尝试添加在主表上。
CREATE INDEX idx_class_card ON class (card); -- 对class表的card字段添加索引
EXPLAIN SELECT * from class LEFT JOIN book ON class.card = book.card; -- 再次查看执行计划
发现class表添加索引并且使用索引成功。
但查看rows字段发现还是全表扫描。
那么接下来为右表book添加索引：
DROP INDEX idx_class_card on class; -- 删除class表索引
CREATE INDEX idx_book_card ON book (card); -- 为book表的card字段添加索引
EXPLAIN SELECT * from class LEFT JOIN book ON class.card = book.card; -- 查看执行计划
发现type变成了refc;效果比index好c;并且rows只扫描了一行。
总结：当是两张表时c;如果是LEFT JOIN左连接c;由于左表数据全部都有c;所以关键在于如何从右表进行搜索c;故右表一定要为连接条件的字段添加索引；如果是RIGHT JOIN右连接c;由于右表数据全部都有c;所以关键在于如何从左表进行搜索c;故左表一定要为连接条件的字段添加索引。
①创建测试表
在双表的基础上新增一张表：
CREATE TABLE IF NOT EXISTS `@H_971_426@phone`(
`@H_971_426@phoneid` INT(10) UNSIGNED NOT NULL PRIMary KEY AUTO_INCREMENT,
`card` INT (10) UNSIGNED NOT NULL
)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)));
使用show index from book;语句发现book表刚才添加的索引没有删除掉c;那么删除干净：
show index from book; -- 查看book表的索引情况
drop index idx_book_card on book; -- 删除book表的索引
②查询及执行情况分析
查询需求：关联book、class、phone三张表。
查看查询计划情况：
EXPLAIN SELECT * from class LEFT JOIN book ON class.card = book.card LEFT JOIN phone ON book.card = phone.card;
发现都是全表扫描并且使用了连接缓存（Using join buffer）c;需要考虑优化。
如何优化
根据双表得出的结论c;如果是LEFT JOIN左连接c;那么在右表添加索引即可c;但这里使用了三张表c;那么需要在book表为card字段添加索引c;在phone表为card字段添加索引。
CREATE INDEX idx_phone_card ON phone(card); -- 为phone表的card字段添加索引
CREATE INDEX idx_book_card ON book (card); -- 为book表的card字段添加索引
EXPLAIN SELECT * from class LEFT JOIN book ON class.card = book.card LEFT JOIN phone ON book.card = phone.card; -- 查看执行计划
总结：如果是三表及多表查询c;按照双表的左外连接和右外连接的情况来分批次处理即可。
对于索引失效应该尽可能地避免。
测试表的SQL如下：
CREATE TABLE staffs(
id INT PRIMary KEY AUTO_INCREMENT,
`name` VARCHAR(24) DEFAULT NULL 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());
INSERT INTO staffs(`name`,`age`,`pos`,`add_time`) VALUES(NULL,23,'test',NOW());
ALTER TABLE staffs ADD INDEX index_staffs_nameAgePos(`name`,`age`,`pos`); # 创建索引
（1）全值匹配最好
查询的字段按照顺序在索引中都可以匹配c;此时速度最快。
比如上面的测试用例中创建了复合索引(name, age, pos)c;那么条件的参数（where name=xxx and age=xxx and pos=xxx）和索引中的字段个数相同并且顺序一致（name对应namec;age对应agec;pos对应age）c;那么情况最好c;如下：
（2）最佳左前缀法制
如果索引了多列c;要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列。
过滤条件要使用索引必须按照索引建立的顺序c;依次满足c;一旦跳过某个字段c;索引后面的字段都无法被使用
多列索引是先按照第一列进行排序c;然后在第一列排好序的基础上再对第二列排序c;如果没有第一列的话c;直接访问第二列c;那第二列肯定是无序的c;直接访问后面的列就用不到索引。
EXPLAIN SELECT * FROM staffs WHERE NAME = 'July';
EXPLAIN SELECT * FROM staffs WHERE NAME = 'July' and age = 15;
EXPLAIN SELECT * FROM staffs WHERE NAME = 'July' and age = 15 and pos= 'dev';
如果是下面这些不按照顺序的c;那么就是无效的：
EXPLAIN SELECT * FROM staffs WHERE age=25 and pos='dev';
EXPLAIN SELECT * FROM staffs WHERE pos='dev';
（3）不在索引列上做任何操作（计算、函数、(自动or手动)类型转换）c;会导致索引失效而转向全表扫描
如下示例：
EXPLAIN SELECT * FROM staffs WHERE NAME = 'July';
EXPLAIN SELECT * FROM staffs WHERE LEFT(NAME,4) = 'July'; -- 使用函数
EXPLAIN SELECT * FROM staffs WHERE NAME = '2000';
EXPLAIN SELECT * FROM staffs WHERE NAME = 2000; -- 使用类型转换
（4）存储引擎不能使用索引中范围条件右边的列
使用范围查询（如>、<、in等）后c;如果范围内的记录过多c;会导致索引失效c;因为从自定义索引映射到主键索引需要耗费太多的时间c;反而不如全表扫描来得快
建议：将可能做范围查询的字段的索引顺序放在最后
EXPLAIN SELECT * FROM staffs WHERE name='July' and age=23 and pos='dev';
EXPLAIN SELECT * FROM staffs WHERE name='July' and age>21 and pos='dev'; -- 使用范围查询
**（5）尽量使用覆盖索引(只访问索引的查询(索引列和查询列一致))c;减少SELEct ***
使用覆盖索引（Using index）会提高检索效率：只访问索引列的查询（索引列和查询列一致c;尽量不使用SELEct *）。
即如果复合索引列（下例：name,age,pos）和*的字段个数相同c;那么建议使用字段名检索而不是星号（ * ）。
例如：
EXPLAIN SELECT * FROM staffs WHERE NAME = 'July' and age = 23 and pos= 'dev'; -- 应该减少SELEct *
EXPLAIN SELECT name,age,pos FROM staffs WHERE NAME = 'July' and age = 23 and pos= 'dev'; -- 尽量使用覆盖索引
（6）mysql在使用不等于(!=或者<>)的时候无法使用索引会导致全表扫描
如：
EXPLAIN SELECT * FROM staffs WHERE NAME = 'July'; -- 使用=等号
EXPLAIN SELECT * FROM staffs WHERE NAME != 'July'; -- 使用!=不等号
EXPLAIN SELECT * FROM staffs WHERE NAME <> 'July'; -- 使用<>不等号
（7）is nullc;is not null也无法使用索引
如果允许字段为空c;则
EXPLAIN SELECT * FROM staffs WHERE name IS NULL; -- IS NULL的情况
EXPLAIN SELECT * FROM staffs WHERE name IS NOT NULL; -- IS NOT NULL的情况
（8）like以通配符开头（‘%abc…’）c;R_730_11845@ysql索引失效c;变成全表扫描的操作
如下：
EXPLAIN SELECT * FROM staffs WHERE NAME like '%July%'; -- 左右都有通配符
EXPLAIN SELECT * FROM staffs WHERE NAME like '%July'; -- 模糊查询加左边
EXPLAIN SELECT * FROM staffs WHERE NAME like 'July%'; -- 模糊查询加右边
由上图可知c;如果要使用模糊查询c;那么尽可能让通配符%加在字符串的右边c;这样不会让索引失效。
如果实在必须要用like '%字符串%'并且索引又不失效c;那么可以使用覆盖索引（查询的字段尽量和索引字段匹配）。
（9）字符串不加单引号索引失效
如：
EXPLAIN SELECT * FROM staffs WHERE NAME = '2000';
EXPLAIN SELECT * FROM staffs WHERE NAME = 2000; -- 如果不加单引号c;就会发生类型转换c;导致索引失效
（10）少用orc;用它来连接时会索引失败
如：
EXPLAIN SELECT * FROM staffs WHERE NAME = 'July' OR NAME = 'z3'; -- 使用OR导致索引失效
可以创建一张表进行测试：
CREATE TABLE abcd(
a VARCHAR(10);
b VARCHAR(10);
c VARCHAR(10);
d VARCHAR(10);
);
INSERT INTO abcd(a,b,c,d) VALUES('1','2','3','4');
INSERT INTO abcd(a,b,c,d) VALUES('1','kkkk','4','5');
全值匹配我最爱c;最左前缀要遵守； 带头大哥不能死c;中间兄弟不能断； 索引列上少计算c;范围之后全失效； Like百分写最右c;覆盖索引不写星； 不等空值还有orc;索引失效要少用； VAR引号不可丢c;SQL高级也不难！
SQL题目如下：
#【建表语句】
create table test03 (
id int priMary key not null auto_increment,
c1 varchar(10),
c2 varchar(10),
c3 varchar(10),
c4 varchar(10),
c5 varchar(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', 'b2', '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');
SELEct * from test03;
题目练习：
#索引建立
create index idx_test03_c1234 on test03(c1, c2, c3, c4);
show index from test03;
#问题：我们创建了复合索引 idx_test03_c1234, 根据一下 SQL 分析下索引使用情况？
explain SELEct * from test03 where c1 = 'a1';
explain SELEct * from test03 where c1 = 'a1' and c2 = 'a2';
explain SELEct * from test03 where c1 = 'a1' and c2 = 'a2' and c3 = 'a3';
explain SELEct * from test03 where c1 = 'a1' and c2 = 'a2' and c3 = 'a3' and c4 = 'a4';
#1) Yes
explain SELEct * from test03 where c1='a1' and c2='a2' and c3='a3' and c4='a4';
#2) Yes
explain SELEct * from test03 where c1='a1' and c2='a2' and c4='a4' and c3='a3';
#3) Yes(5.6+ Using index condition)
explain SELEct * from test03 where c1='a1' and c2='a2' and c3>'a3' and c4='a4';
#4) Yes(5.6+ Using index condition)
explain SELEct * from test03 where c1='a1' and c2='a2' and c4>'a4' and c3='a3';
#5)Yes(5.6+ Using index condition)
explain SELEct * from test03 where c1='a1' and c2='a2' and c4='a4' order by c3;
#c3 的作用在于排序而不是查找
#6）Yes(5.6+ Using index condition)
explain SELEct * from test03 where c1='c1' and c2='c2' order by c3;
#7) Yes(5.6+ Using index condition)
explain SELEct * from test03 where c1='c1' and c2='c2' order by c4;
#8) Yes(5.6+ Using index condition, Using wherE)
explain SELEct * from test03 where c1='a1' and c5='a5' order by c2, c3;
#9) NO (Using index condition; Using where; Using filesort)
explain SELEct * from test03 where c1='a1' and c5='a5' order by c3, c2;
#10) Yes(5.6+ Using index condition)
explain SELEct * from test03 where c1='a1' and c2='a2' order by c2, c3;
#11) Yes(Using index condition; Using wherE)
explain SELEct * from test03 where c1='a1' and c2='a2' and c5='a5' order by c2, c3;
#12) Yes(5.6+ Using index condition)
explain SELEct * from test03 where c1='a1' and c2='a2' and c5='a5' order by c3, c2;
#本例有常量c2的情况c;和#9对比
#13) No (Using index condition; Using where; Using filesort)
explain SELEct * from test03 where c1='a1' and c5='a5' order by c3, c2;
#14) Yes(Using where; Using indeX)
explain SELEct c2, c3 from test03 where c1='a1' and c4='a4' group by c2, c3;
#15) NO (Using where; Using index; Using temporary; Using filesort)
explain SELEct c2, c3 from test03 where c1='a1' and c4='a4' group by c3, c2;
总结：
分析：
总结：
# 优化原则：小表驱动大表c; 即小的数据集合驱动大的数据集合
##################### 原理 （RBO）###############
SELEct * from A where id in (SELEct id from B)
#等价于
for SELEct id from B
for SELEct id from A where A.id = B.id
# 当B表的数据集必须小于A表的数据集时c;用in 优于 exists
SELEct * from A where exists (SELEct 1 from B where B.id = A.id)
#等价于
for SELEct id from A
for SELEct id from B where B.id = A.id
#当 A 表的数据集系小于表的数据集c; 用 exists 优于 in
#注意： A与B表的id 字段应该建立索引
## exists
SELEct ... from table where exists (subquery);
# 该语法可以理解为：将主查询的数据c;放到子查询中做条件验证c;根据验证结果（TRUE 或者 falSE）来决定主查询数据结果是否得到保留。
## 提示
# 1c;EXISTS (subquery) 只返回 True 或 false c; 因此查询的 SELET * 也可以是SELET 1 或其他c; 官方说法是执行时会忽略SELECT 清单c; 因此没有区别
# 2. EXISTS 子查询的实际执行过程可能经过了优化而不是我们理解的逐条比对c;如果担忧效率问题c;可以进行实际检验以确定是否有效率问题。
# 3. EXISTS 子查询往往也可以使用条件表达式、其他子查询或者 JOIN 来代替c;何种最优化需要具体分析。
优化原则：小表驱动大表
首先创建测试表：
# 创建表
create table tblA(
#id int priMary key not null auto_increment,
age int,
birth timestamp not null
);
# 插入测试数据
insert into tblA(age, birth) values(22, now());
insert into tblA(age, birth) values(23, now());
insert into tblA(age, birth) values(24, now());
# 创建索引
create index idx_A_ageBirth on tblA(age, birth);# 创建复合索引age和birth
查看执行计划：案例一
EXPLAIN SELECT * FROM tblA WHERE age > 20 ORDER BY age;
EXPLAIN SELECT * FROM tblA WHERE age > 20 ORDER BY age,birth;
EXPLAIN SELECT * FROM tblA WHERE age > 20 ORDER BY birth;
EXPLAIN SELECT * FROM tblA WHERE age > 20 ORDER BY birth,age;
EXPLAIN SELECT * FROM tblA WHERE birth > '2021-02-19 22:45:00' ORDER BY birth;
EXPLAIN SELECT * FROM tblA WHERE birth > '2021-02-19 22:45:00' ORDER BY age;
查看执行计划：案例二
EXPLAIN SELECT * FROM tblA ORDER BY age ASC,birth DESC; #排序不一致c;要么都是升序或者都是降序
最后得出结论：
当发生 Using filesort 时c;R_730_11845@ySQL会根据自己的算法对查询结果进行排序。有两种排序算法：双路排序和单路排序。
简单来说c;取一批数据c;要对磁盘进行了两次扫描c;众所周知c;IO 是很耗时的c;所以在 mysql4.1 之后c;出现了第二种改进的算法c;就是单路排序。
从磁盘读取查询需要的所有列c;按照 order by 列在 buffer 对它们进行排序c;然后扫描排序后的列表进行输出c; 它的效率更快一些c;避免了第二次读取数据。并且把随机 IO 变成了顺序 IO,但是它会使用更多的空间c; 因为它把每一行都保存在内存中了。
单路排序存在的问题：在 sorT_Buffer 中c;方法 B（单路排序） 比方法 A（双路排序） 要多占用很多空间c;因为方法 B 是把所有字段都取出, 所以有可能取出的数据的总大小超出了 sorT_Buffer 的容量c;导致每次只能取 sorT_Buffer 容量大小的数据c;进行排序（创建 tmp 文件c;多 路合并）c;排完再取取 sorT_Buffer 容量大小c;再排……从而多次 I/O。也就是本来想省一次 I/O 操作c;反而导致了大量的 I/O 操作c;反而得不偿失。
增大sorT_Buffer_size参数的设置
不管用哪种算法c;提高这个参数都会提高效率c;当然c;要根据系统的能力去提高c;因为这个参数是针对每个进程的 1M-8M 之间调整。
增大max_length_for_sort_data参数的设置
@H_538_5@mysql 使用单路排序的前提是排序的字段大小要小于 max_length_for_sort_datac; 提高这个参数c;会增加使用改进算法的概率。但是如果设的太高c;数据总容量超出 sorT_Buffer_size 的概率反而会增大c; 就会出现高频磁盘 I/O 和低的处理器使用率。（1024-8192 之间调整）
*减少 SELEct 后面的查询的字段（少用SELEct ）
查询的字段减少c;缓冲就能容纳更多的内容c;也就相当于间接增大了sorT_Buffer_size。
当Query的字段大小总和小于max_length_for_sort_data而且排序字段不是TEXT|IBLOB类型时c;会用改进后的算法一—单路排序c;否则用老算法——多路排序。
两种算法的数据都有可能超出sorT_Buffer的容量c;超出之后c;会创建tmp文件进行合并排序c;导致多次Oc;但是用单路排序算法的风险会更大一些,所以要提高sorT_Buffer_size。
为排序使用索引：
（1）MySQL两种排序方式：文件排序或扫描有序索引排序
（2）MySQL能为排序与查询使用相同的索引。（因为索引有两个作用：排序和查找）
例如：key a_b_c(a,b,c) #为a,b,c三个字段创建复合索引
## 第一种情况：ORDER BY能使用索引最左前缀
- ORDER BY a
- ORDER BY a,b
- ORDER BY a,b,c #相当于ORDER BY a ASC,b ASC,c ASC
- ORDER BY a DESC,b DESC,c DESC
## 第二种情况：如果WHERE使用索引的最左前缀定义为常量（如 WHERE name='张三'）c;则ORDER BY 能使用索引
- WHERE a=const ORDER BY b,c #也按照了索引顺序：a,b,c
- WHERE a=const AND b=const ORDER BY c #也按照了索引顺序：a,b,c
- WHERE a=const ORDER BY b,c #也按照了索引顺序：a,b,c
- WHERE a=const AND b>const ORDER BY b,c #虽然b>const会导致后面的索引失效c;但前面的常量加上ORDER BY后面的字段也符合索引顺序：a,b,c
## 第三种情况：不能使用索引进行排序的情况
- ORDER BY a ASC,b DESC,c DESC #排序不一致c;既存在升序c;也存在降序
- WHERE g=const ORDER BY b,c #丢失a索引c;不能使用索引排序
- WHERE a=const ORDER BY c #丢死b索引c;也不能使用索引排序
- WHERE a=const ORDER BY a,d #d不是索引的一部分c;也不能使用索引排序
- WHERE a in (...) ORDER BY b,c #对于排序来说c;多个相等条件也是范围查询
group by优化和order by优化大致相似。
group by实质是先排序后进行分组c;遵照索引建的最佳左前缀。
当无法使用索引列c;增大max_length_for_sort_data参数的设置+增大sorT_Buffer_size参数的设置。
where高于havingc;能写在where限定的条件就不要去having限定了。
要想在排序时使用索引c;避免 Using filesortc;可以采用索引覆盖。
ORDER BY /GROUP BY后面字段的顺序要和复合索引的顺序完全一致。
ORDER BY /GROUP BY后面的索引必须按照顺序出现c;排在后面的可以不出现。
要进行升序或者降序时c;字段的排序顺序必须一致。不能一部分升序c;一部分降序c;可以都升序或者都降序。
如果复合索引前面的字段作为常量出现在过滤条件中c;排序字段可以为紧跟其后的字段。
默认情况下c;R_730_11845@ySQL 数据库没有开启慢查询日志c;需要我们手动来设置这个参数。
如果不是调优需要的话c;一般不建议启动该参数c;因为开启慢查询日志会将sql语句写入日志c;因此或多或少带来一定的性能影响。
| sql语句 | 说明 |
|---|---|
| SHOW VARIABLES LIKE ‘%slow_query_log%’; | 查询慢查询日志是否开启c;默认为OFF表示未开启 |
| SET GLOBAL slow_query_log=1; | 开启慢查询日志 |
| SET GLOBAL slow_query_log=0; | 关闭慢查询日志 |
| SHOW VARIABLES LIKE ‘long_query_time%’; | 查询慢查询设定阈值c;默认为10秒 |
| SET long_query_time=5; | 设定慢查询阈值为5秒c;可以设置为几秒c;单位为秒 |
注意：
# 下面的配置语句是添加在[mysqld]标签下的
[@H_971_426@mysqld]
# 配置慢查询日志
# 开启慢查询日志c;1表示开启c;0表示关闭c;默认关闭
slow_query_log=1
# 设置日志路径c;日志路径通常由在mysql下执行SHOW VARIABLES LIKE '%slow_query_log%';命令>得到的slow_query_log_file参数的值
slow_query_log_file=/var/lib/mysql/localhost-slow.log
# 设置慢查询阈值为5秒c;当有查询SQL执行时间超过5秒就会被记录在慢查询日志中
long_query_time=5
log_output=FILE
退出保存后执行service mysqld restart命令重启mysql服务c;再次进入mysql中查看已经设置成功了
生产环境中手工查找c;分析日志c;非常的耗费时间c;因此MySQL提供了日志分析工具@H_177_424@mysqldumpslow。
通过@H_177_424@mysqldumpslow --Help命令可以查看该工具的帮助：
帮助信息的各参数说明：
工作常用参考案例：
-- 获取返回集最多的10条SQL
mysqldumpslow -s r -t 10 /var/lib/mysql/localhost-slow.log
-- 获取访问次数最多的10条SQL
mysqldumpslow -s c -t 10 /var/lib/mysql/localhost-slow.log
-- 获取按时间排序的前10条含有LEFT JOIN的sql语句
mysqldumpslow -s t -t 10 -g "lEFT JOIN" /var/lib/mysql/localhost-slow.log
-- 结合|@H_971_426@more使用c;否则有可能会爆屏
mysqldumpslow -s r -t 10 /var/lib/mysql/localhost-slow.log |@H_59_5552@more
批处理数据脚本是为了插入百万、千万条数据的脚本。
第一步：创建测试表：
create database big_data;
use big_data;
# dept
create table dept(
id int priMary key auto_increment,
deptno @H_980_425@mediumint not null default 0,
dname varchar(20) not null default '',
loc varchar(13) not null default ''
) ENGIne = innodb default charset = utf8;
# emp
create table emp(
id int priMary key auto_increment,
empno @H_980_425@mediumint not null default 0,
ename varchar(20) not null default '',
job varchar(9) not null default '' comment '工作',
mgr @H_980_425@mediumint not null default 0 comment '上级编号',
hirdate date not null comment '入职时间',
sal decimal(18,2) not null comment '薪水',
comm decimal(18,2) not null comment '红利',
deptno @H_980_425@mediumint not null default 0 comment '部门编号'
) ENGIne = innodb default charset = utf8;
第二步：创建函数c;保证每条数据都不相同c;产生随机字符串和随机部门编号：
#随机字符串函数
delimiter $$
create function rand_str(n int) returns varchar(255)
begin
declare chars_str varchar(100) default 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRstuVWXYZ';
declare return_str varchar(100) default '';
declare i int default 0;
while i < n do
set return_str = concat(return_str, subString(chars_str, floor(1 + rand() * 52) ,1));
set i = i+1;
end while;
return return_str;
end $$
DELIMITER ;
#删除函数
#drop function if exists rand_str;
#使用函数
SELEct rand_str(5);
#用于随机产生多少到多少的编号
DELIMITER $$
CREATE FUNCTION rand_num (from_num INT ,to_num INT) RETURNS INT(11)
BEGIN
DECLARE i INT DEFAULT 0;
SET i = FLOOR(from_num +RAND()*(to_num -from_num+1)) ;
RETURN i;
END$$
DELIMITER ;
#删除函数
#drop function if exists rand_num;
#使用函数
SELEct rand_num(100,100000);
第三步：创建存储过程
-- 为dept创建存储过程
delimiter $$
create procedure insert_dept(in start int(10), in max_num int(10))
begin
declare i int default 0;
set autocommit = 0;#设置不让一次插入后就自动提交c;批量插入的话如果每次都自动提交那么会连接无数次c;耗费性能
repeat
set i = i+1;
insert into dept(deptno, dname, loc) values ((start+i), rand_num() , rand_str(6));
until i = max_num
end repeat;# 结束循环
commit;# 最后批量插入后再插入
end $$
delimiter ;
-- 为emp创建存储过程
delimiter $$
create procedure insert_emp(in start int(10), in max_num int(10))
begin
declare i int default 0;
set autocommit = 0;
repeat
set i = i+1;
insert into emp (empno, ename, job, mgr, hirdate, sal, comm, deptno) values ((start+i), rand_str(6), 'SALEsmaN', 001, curdate(), 2000, 400, rand_num(100,50000));
until i = max_num
end repeat;
commit;
end $$
delimiter ;
第四步：调用存储过程批量插入数据
# 添加数据到部门表
#执行存储过程c;往 dept 表添加 1 万条数据
CALL insert_dept(0,10000);
# 添加数据到员工表
CALL insert_emp(10,50000);
Show Profiles是MySQL提供c;可以分析sql语句执行的资源消耗情况c;可用于SQL调优。
通过配置profiling参数启用SQL剖析c;该参数可以在全局和session级别来设置。
全局级别作用于整个MySQL实例c;而session级别只影响当前回话。
该参数开启后c;后续执行的sql语句都将记录其资源开销c;诸如IOc;上下文切换c;CPUc;R_730_11845@emory等
Show profiles是5.0.37之后添加的c;要想使用此功能c;要确保MySQL版本 > 5.0.37。
官网：https://dev.mysql.com/doc/refman/8.0/en/show-profile.html
默认情况下c;参数处于关闭状态c;并保存最近15次的运行结果。
第一步：查看是否支持c;即查看当前的mysql版本是否支持。
执行show variables like 'PROfiling'命令查询是否启用c;为OFF表示未启用
第二步：由于默认是关闭的c;使用前需要开启。
执行set profiling=on命令开启profile。
第三步：运行SQL
# 随便写条查询SQL查看效果
SELECT id%10 from emp GROUP BY id%10 LIMIT 10000;
第四步：执行show profiles;
第五步：诊断SQL
# 语法
show profile [type] for query 上一步前面问题的SQL数字号码;
# type参数说明
# all 表示显示全部开销信息
# block io 表示显示块IO相关开销
# contexxtswitchaes 表示显示上下文切换相关开销
# cpu 表示显示CPU相关的开销信息
# ipc 表示显示发生和接收相关的信息
# memory 表示显示内存相关的信息
# page faults 表示显示页面错误相关的开销信息
# source 表示显示和source_function,source_file,source_line相关的开销信息
# swaps 表示显示交换次数相关的开销的信息
# 示例
show profile cpu,block io for query 35;
第六步：日常开放需要注意的事项
注意：永远不要在生产环境中开启此功能。
注意：永远不要在生产环境中开启此功能。
注意：永远不要在生产环境中开启此功能。
只能在测试环境中使用c;一旦开启c;会记录每条查询SQL。有如下两种启用方式：
在mysql的 /etc/my.cnf 配置文件中的 [mysqld] 标签下添加如下内容：
# 开启c;默认是关闭的
general_log=1
# 记录日志文件的路径
general_log_file=/var/lib/mysql/localhost.log
# 输出格式
log_output=FILE
在mysql环境下执行如下语句来启用全局查询日志：
set global general_log=1; #开启配置文件
set global log_output='TABLE'; #设置后c;所以执行的sql语句c;都会被记录在mysql库的general_log表中c;可以用下面的语句查看
SELEct * from mysql.general_log;
锁是计算机协调多个进程或线程并发访问某个资源的机制。
生活中的案例：比如在淘宝上买一件商品c;商品库存只有一件c;这个时候c;如果有其他的卖家c;那么如何解决是你买到还是别人买到的问题。
这里肯定要用到事务c;我们先从库存中取出物品的数量c;然后插入订单。付款后插入付款信息。然后更新商品数量c;这个过程使用锁,可以对有限的资源进行保护c;解决隔离和并发的矛盾。
读锁：针对同一份数据c;对该数据的读操作可以同时进行且不受影响。
写锁：写操作未完成前c;会阻断其他的读操作和写操作。
从对数据的操作粒度分为：表锁 和 行锁
在数据库中有三种锁：表锁（偏读）、行锁（偏写）和页锁。
表锁的特点： MylSAM引擎使用表锁c;开销小c;加锁快c;无死锁c;锁定力度大c;发生锁冲突的概率最高。并发度最低。不支持事务
插入测试要用到的表：
use big_data;
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;
需要开启两个会话来测试加读锁后的情况。
加读锁的语法如下：
# 加锁语法
lock table 表名 read|write; # read表示为该表添加读锁；write表示为该表添加写锁
# 释放锁语法
unlock tables;
①打开两个会话session-1和session-2c;然后在session-1会话中为mylock表添加读锁。
②在两个会话中都能读取当前已经添加了读锁的mylock表。
③在session-1会话（已经加了读锁）中不能查询没有加锁的表c;而其他会话如session-2能够查询或更新其他表。
④当前session-1（已经加了读锁）会话中插入或更新锁定了的表都会提示错误c;其他会话中插入或更新锁定了的话会进入阻塞状态一直等待获得解锁。
⑤当前session-1（已经加了读锁）会话中释放锁c;那么其他会话中陷入阻塞状态的操作也会继续完成。
①为session-1会话中的mylock表添加写锁。
②在session-1会话（已经加了写锁）中查询其他表失败c;而其他会话能够查询其他表成功。
③在session-1会话（已经加了写锁）可以更新和插入数据c;而其他会话不能插入数据。
④在session-1会话（已经加了写锁）查询mylock表c;而其他会话中查询mylock表会陷入阻塞状态。
⑤当前session-1（已经加了写锁）会话中释放锁c;那么其他会话中陷入阻塞状态的操作也会继续完成。
| 锁类型 | 可否兼容 | 读锁 | 写锁 |
|---|---|---|---|
| 读锁 | 是 | 是 | 否 |
| 写锁 | 是 | 否 | 否 |
结合上表c;所以对 MylSAM表进行操作c;会有一下的情况:
结论：简而言之、就是读锁会阻塞写c;但是不会阻塞读c;而写锁则会把读和写都阻塞
查询被锁定的表的语法：
# 语法
show open tables;
如果要分析表锁c;可以通过下面的方法来对表锁进行分析：
通过检查table_locks_waited和table_locks_immediate状态变量来分析系统上的表锁定。table_locks_waited变量和table_locks_immediate变量可以通过下面的sql语句来进行查看：
show status like 'table%'; #查看表锁信息
在查询出来的结果集中需要关注Table_locks_immediate变量和Table_locks_waited变量c;它们的说明如下：
此外c;R_730_11845@yisam的读写锁调度是写优先c;这也是myisam不适合做写为主表的引擎。因为写锁后c;其他线程不能做任何操作大量的更新会使查询很难得到锁c;从而造成永远阻塞。
偏向InnoDB存储引擎c;开销大c;加锁慢;会出现死锁;锁定粒度最小c;发生锁冲突的概率最低,并发度也最高。
InnoDB与MyISAM的最大不同有两点：一是支持事务（transaCTION)；二是采用了行级锁。
所以可以复习下事务的知识：
插入测试要用到的表：
-- 创建表
CREATE TABLE test_innodb_lock (a INT(11),b VARCHAR(16))ENGINE=INNODB;
-- 插入数据
INSERT INTO test_innodb_lock VALUES(1,'b2');
INSERT INTO test_innodb_lock VALUES(3,'3');
INSERT INTO test_innodb_lock VALUES(4, '4000');
INSERT INTO test_innodb_lock VALUES(5,'5000');
INSERT INTO test_innodb_lock VALUES(6, '6000');
INSERT INTO test_innodb_lock VALUES(7,'7000');
INSERT INTO test_innodb_lock VALUES(8, '8000');
INSERT INTO test_innodb_lock VALUES(9,'9000');
INSERT INTO test_innodb_lock VALUES(1,'b1');
-- 创建索引
CREATE INDEX test_innodb_a_ind ON test_innodb_lock(a);
CREATE INDEX test_innodb_lock_b_ind ON test_innodb_lock(b);
-- InnnDB事务自动提交c;如果需要演示行锁c;需要关闭自动提交
SET autocommit=0;
①开启事务c;即关闭自动提交。
②在session-1会话中更新但不提交事务c;即没有手写commit;语句执行c;其他会话如session-2会陷入阻塞c;只能等待。
③在session-1会话中提交事务c;而其他会话中堵塞的操作解除c;继续执行。
④如果对不同的记录进行修改c;那么也就不会堵塞
索引失效c;行锁变表锁（通过varchar类型不加单引号让索引失效）。
当索引失效后c;即使多个客户端操作的不是同一条记录c;如果未提交c;其他客户端也会进入阻塞状态c;所以要避免索引失效。
为甚么索引失效行锁会变表锁：InnoDB 行级锁是通过给索引上的索引项加锁来实现的c;InnoDB行级锁只有通过索引条件检索数据c;才使用行级锁。否则c;InnoDB使用表锁c;在不通过索引(主 键)条件查询的时候c;InnoDB是表锁而不是行锁。
①什么是间隙锁
当我们用范围条件而不是相等条件检索数据c;并请求共享或排他锁时c;InnoDB会给符合条件的已有数据记录的索引项加锁；对于键值在条件范围内但并不存在的记录c;叫做“间隙（GAp)”c;InnoDB也会对这个“间隙”加锁c;这种锁机制就是所谓的间隙锁(Next-Key锁）。
比如一共有1、2、4、5、6、7、8、9共八条记录c;表中没有3号记录c;但是在使用范围条件（id>=2 and id<=6）进行查询检索的时候c;那么也会给3号记录添加锁c;如果这时候其他会话插入一条id为3的记录就会陷入阻塞c;也就是所谓的间隙锁发生了。
②间隙锁演示
间隙锁会带来插入问题c;当产生阻塞时就暂时不能插入c;只有阻塞解除后才能完成插入。
③间隙锁的危害
因为Query执行过程中通过过范围查找的话c;他会锁定整个范围内所有的索引键值c;即使这个键值并不存在。
间隙锁有一个比较致命的弱点c;就是当锁定一个范围键值之后c;即使某些不存在的键值也会被无辜的锁定c;而造成在锁定的时候无法插入锁定键值范围内的任何数据。在某些场景下这可能会对性能造成很大的危害
SELEct xxx from xxx where key=xxx for update锁定某一行后c;其他的操作会被阻塞c;直到指定行的会话提交commit。
如果两个客户端对同一条记录进行修改
可以通过innodb_row_lock这个变量来分析行锁的竞争情况：
show status like 'innodb_row_lock%';
各字段解释说明：
| 字段 | 说明 |
|---|---|
| Innodb_row_lock_current_waits | 当前正在等待锁定的数量 |
| Innodb_row_lock_time（重要） | 从系统启动至今c;总锁定了多长时间 |
| Innodb_row_lock_time_avg（重要） | 每次锁定的平均时间 |
| Innodb_row_lock_time_max | 最长的一次锁定时间 |
| Innodb_row_lock_waits（重要） | 从系统启动至今c;一共锁定了多少次 |
对于页锁了解即可。
slave会从master 读取binlog来进行数据同步。
原理：
主要涉及三个线程：binlog 线程、I/O 线程和 SQL 线程。
延时。
以最常见的一主一从配置为例说明该如何配置。
第一步：mysql版本一致并且在后台以服务运行。
执行@H_177_424@mysql -V命令可以查看版本c;执行net start mysql命令可以查看服务启动情况c;如果没有启动则启动。
执行@H_177_424@mysql -V命令可以查看版本c;执行service mysqld status命令可以查看服务启动情况c;如果没有启动则执行service mysqld start命令启动。
@H_445_10696@
第二步：主从配置都在配置文件的[mysqld]节点下c;都是小写。
在这里作为主服务器的Windows系统下的配置文件是my.ini。
在这里作为从服务器的Linux系统下的配置文件是my.cnf。
第三步：主服务器修改my.ini配置文件
可以在my.ini配置文件的[mysqld]节点下作如下配置：
# 1.[必须]主服务器唯一ID
server-id=1
# 2.[必须]启用二进制日志
log-bin=本地MySQL的安装目录下的data目录的路径/@H_971_426@mysqlbin #语法
log-bin=E:/@H_971_426@mySQL/InstallationFiles/data/@H_971_426@mysqlbin #示例
# 3.[可选]启用错误日志
log-err=本地MySQL的安装目录下的data目录的路径/@H_971_426@mysqlerr #语法
log-err=E:/@H_971_426@mySQL/InstallationFiles/data/@H_971_426@mysqlerr
# 4.[可选]根目录
basedir="本地MySQL的安装目录的路径" #语法
basedir="E:/MySQL/InstallationFiles/" #示例
# 5.[可选]临时目录
tempdir="本地MySQL的安装目录的路径" #语法
tempdir="E:/MySQL/InstallationFiles/" #示例
# 6.[可选]数据目录
datadir="本地MySQL的安装目录下的data目录的路径" #语法
datadir="E:/MySQL/InstallationFiles/data" #示例
# 7.主机读写都可以
read-only=0
# 8.[可选]设置不要复制的数据库
binlog-ignore-db=不要复制的数据库名 #语法
binlog-ignore-db=@H_971_426@mysql #示例
# 9.[可选]设置需要复制的数据库
binlog-do-db=需要复制的主数据库名字 #语法
binlog-do-db=test #示例
而我选择的配置是：
[@H_971_426@mysqld]
# 设置主服务器唯一ID[必须]
server-id=1
# 启用二进制日志[必须] log-bin=数据库路径mysqlbin
log-bin=E:/@H_971_426@mySQL/InstallationFiles/data/@H_971_426@mysqlbin
# 启用错误日志[可选]
log-err=E:/@H_971_426@mySQL/InstallationFiles/data/@H_971_426@mysqlerr
# 设置mysql的安装目录
basedir=E:/@H_971_426@mySQL/InstallationFiles
# 设置临时目录[可选]
tmpdir=E:/@H_971_426@mySQL/InstallationFiles
# 设置主机读写均可以
read-only=0
# 设置不需要备份的数据库[可选]
binlog-ignore-db=@H_971_426@mysql
# 设置需要备份的数据库[可选]
# binlog-do-db=layman
第四步：修改从服务器的my.conf配置文件
[@H_971_426@mysqld]
# 配置从机服务器
# 设置主服务器唯一ID[必须]
server-id=2
# 启用二进制日志[可选]c;Linux环境下采用默认值即可
log-bin=@H_971_426@mysql-bin
保存并退出。
第五步：重启主服务器和从服务器的mysql服务
第六步：主服务器和从服务器都关闭防火墙
第七步：在主服务器Windows上建立账户并授权给从服务器
登录进入mysql下
输入如下内容：
# grant Replication slave on *.* to '从机数据库用户名'@'从机IP地址' identified by '从机数据库密码'; #语法
grant Replication slave on *.* to 'Root'@'192.168.0.117' identified by 'Root';
flush privileges; #刷新
可以执行show master status命令查看主机状态
注意：记录下File和Positionc;会用到。
第八步：在Linux上配置需要复制的主机
在Linux中进入mysql下
输入如下内容进行配置：
# 语法
CHANGE MASTER TO MASTER_HOST='主机IP地址', #这里我使用的是Windows系统作为主机c;所以为Windows系统的IP地址
MASTER_USER='主机数据库的用户名',
MASTER_password='主机数据库的密码',
MASTER_LOG_FILE='具体数字', #通过上面在主机查询出来的File值
MASTER_LOG_POS=具体值; #通过上面在主机查询出来的Position值
------------------------- 分割线 ------------------------
# 示例
CHANGE MASTER TO MASTER_HOST='192.168.0.105',
MASTER_USER='Root',
MASTER_password='Root',
MASTER_LOG_FILE='mysqlbin.000001',
MASTER_LOG_POS=107;
第九步：启动从机服务的复制功能
在从服务器Linux系统的R_730_11845@ysql下执行start slave;命令启动复制功能。
第十步：查看是否配置成功
在从服务器Linux系统的R_730_11845@ysql下执行show slave statusG命令查看是否配置成功
配置成功后在主机上新建库c;表c;插入数据都会同步至从机。
使用stop slave命令可以停止从机的复制。
读写分离能够提高系统性能：
以上是大佬教程为你收集整理的MySQL高级学习笔记全部内容，希望文章能够帮你解决MySQL高级学习笔记所遇到的程序开发问题。
如果觉得大佬教程网站内容还不错，欢迎将大佬教程推荐给程序员好友。
本图文内容来源于网友网络收集整理提供，作为学习参考使用，版权属于原作者。
如您有任何意见或建议可联系处理。小编QQ：384754419，请注明来意。