mysql高级01【2021-10-29】

 

[client]
default-character-set=utf8
[mysql]
default-character-set=utf8
[mysqld]
port=3306
bind-address=0.0.0.0
init_connect='SET collation_connection = utf8_unicode_ci'
init_connect='SET NAMES utf8'
character-set-server=utf8
collation-server=utf8_unicode_ci
skip-character-set-client-handshake
skip-name-resolve
max_connections=1024
#skip-grant-tables
log-error=/var/log/mysql/mysqld.log

1、基础知识 

log-bin ：二进制日志，主要用于 主从复制

log-error: 记录严重的警告和错误信息，每次启动和关闭的信息，默认是关闭的

log ： 查询日志，记录查询的sql语句，如果开启会降低性能，默认是关闭的

数据文件：

默认路径：/var/lib/mysql

frm文件：存放表结构

myd文件：存放表数据

myi文件：存放表索引

1、逻辑架构

连接层：

服务层：管理服务、连接池、SQL接口、解析Parser、优化、缓存和缓冲

引擎层：可拔插组件引擎

存储层：文件系统、数据和日志

2、存储引擎

MyISAM：不支持事务，不支持主外键、支持表锁，只缓存索引，不缓存数据，表空间小、性能强

InnoDB：支持主外键、事务，行锁、不仅缓存索引，也缓存数据，表空间大

3、sql性能下降的原因

出现：性能下降sql慢，执行时间长，等待时间长

原因：sql语句写的烂、索引失效，关联查询太多join，服务器调优（缓存、多线程）

索引：建立索引会对数据进行排序，查找的快一点。

单值索引就是一个字段作为索引，复合索引是将多个字段作为一个索引

4、常见的join查询

 

5、七种join

mysql中不支持全连接，可以使用联合查询代替

SELECT * FROM `departments` d LEFT JOIN employees e ON d.department_id = e.department_id
UNION
SELECT * FROM departments d RIGHT JOIN employees e ON d.department_id = e.department_id

内连接 只包含2张表的公有部分。

左连接 以左边的表为主表，不仅仅包含公有部分，还包括主表中有，而副表中没有的部分。

右连接 以右边的表为主表，不仅仅包含公有部分，还包括主表中有，而副表中没有的部分。

想要获取 主表除了公有部分的其他部分如何写呢？

只能通过左连接或者右连接实现，加上 where 连接字段为 null

2、索引

官方的定义是：索引index是帮助mysql高效获取数据的数据结构。

索引是一种数据结构。提高查询效率，类似于字典。

排好序的快速查找数据结构。

索引影响 排序order by、查找

在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查找算法，这种数据结构就是索引。

Btree

索引本身也很大，不可能全部存储在内存中，索引以索引文件存储在磁盘上。

平时所说的索引，默认都是指B树（多路搜索数，不一定是二叉的），其中聚集索引，次要索引，复合索引，前缀索引，唯一索引等默认都是B+数索引，统称索引。

除了B+数索引还有哈希索引等。

2.1 索引的优劣

优势：

1、提供检索的效率，降低数据库的IO成本

2、通过索引列对数据进行排序，降低数据排序的成本，降低了cpu的消耗

劣势：

1、索引实际上也是一张表，该表保存了主键与索引字段，并指向实体表的记录，因此索引列也是要占用空间的。

2、虽然索引会大大的提供查找速度，同时却会降低更新表的速度，如对表进行 insert、update、delete

因为更新表时，不仅要保存数据，还要更新索引文件信息。

索引知识提高效率的一个因素，如果mysql有大数据量的表，就需要研究建立最合适的索引。

2.2 索引分类与基本使用

分类：

1、单值索引：一个索引只包含单个列，一个表可以有多个单例索引

2、唯一索引：索引列的值必须唯一，运行有null值，但是null也只能有一个。

3、符合索引：一个索引包含多个列

创建：

CREATE [索引类型 如：UNIQUE] INDEX 索引名 ON TABLE 表名(字段名(length));
# 或者
ALTER 表名 ADD [索引类型] INDEX 索引名 ON (字段名(length))

查看：

SHOW INDEX FROM 表名;

删除：

DROP INDEX 索引名 ON 表名;

2.3 索引的结构

主要分为4种：

BTree索引：检索原理

 

每一个磁块（浅蓝色）包含2个数据（深蓝色）以及3个指针（黄色）

例如磁块1包含数据项 17和35，指针p1、p2、p3；

p1指向小于17的磁盘块，p2指向17和35之间的磁盘块，p3指向大于35的磁盘块。

真实的数据存储在叶子节点（没有子节点，即没有指针了），非叶子节点不存储真实数据，只存储指引搜索方向的数据项，17,35并不真实存在数据表中。

Hash索引

full-text全文索引

R-tree索引

• 哪些情况下需要创建索引？

1、主键会自动创建唯一索引

2、查找非常频繁的字段

3、查询中与其他表关联的字段，外键关系建立索引

4、频繁更新的字段不适合创建索引

5、where 条件用不到的字段不创建索引

6、单键与组合索引选择？ 在高并发下，倾向创建组合索引

7、查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度

8、查询中统计或者分组字段

• 那些情况不用创建索引

1、表记录太少 低于300w

2、经常增删改

3、数据重复且分布平均的字段，如果某个数据列包含很多重复内容，为它建立索引就没有太大效果。

2.4 性能分析

mysql 查询优化器：

1、mysql有专门用于优化select语句的优化器，通过运算分析收集的统计信息，为客户端请求的查询提供mysql认为最优的执行计划（mysql认为最优的数据检索方式，不一定是DBA认为是最优的，这部分最消耗时间）

2、当客户端向mysql请求一条查询，

命令解析器完成请求分类，区别是select并发送给查询优化器，优化器会对整条查询语句进行优化，处理掉一些常量表达式的预算，直接换算成常量值，

并对查询中的条件进行简化和转换，去除一些无用或者显而易见的条件、结构调整等

。然后分析查询中的hint信息（如果有），看显示hint信息是否可以完全确定这个查询的执行计划，

如果没有hint或者hint不足以完全确定执行计划，则会读取所涉及到的对象的统计信息，根据查询进行写相应的计算分析，然后得出最后的执行计划。

常见瓶颈：

1、CPU：cpu在饱和时，一般发生在数据装入内存或者从磁盘读取数据时

2、IO：磁盘IO瓶颈发生在装入数据远大于内存容量时

3、服务器硬件的性能瓶颈：top、free、iostat和vmstat来查看系统的性能状态。

Explain：执行计划

使用explain 关键字可以模拟优化器执行sql查询语句，从而知道mysql是如果处理你的sql语句，分析查询语句或者是表结构的性能瓶颈。

EXPLAIN SELECT * FROM `departments`

语法就是 explain + Sql

 

• id：select查询的序列号，包含一组数组，表示查询中执行select字句或操作表的顺序

三种情况：id相同，表示执行顺序由上到下；id不同，id越大优先级越高，越先被执行（可以想到，子查询一定优先级别高 ）；id既有相同，又有不同，同时存在，先执行数字大的，再执行数字小的。相同的顺序执行。

• select_type: 查询类型，

simple普通查询：不包含子查询或者联合查询

primary 最外层查询：查询中包含子查询，最外层的查询就是primary

subquery 子查询：就是子查询（在select或者where中包含的子查询）

derived 衍生：在from列表中包含的子查询标记为衍生查询，mysql会递归执行子查询，把结果放在临时表中。

union联合查询: 第二个select出现在union之后，会被标记为union，如果union包含在from字句的子查询中，外层select被标记为衍生查询

union result 联合结果：从union表获取的结果

• table 表，反应数据来自哪一张表

• type 显示查询使用了哪一种类型

type 有8种值，all、index、range、ref、eq_ref、const，system、null

从好到坏依次是：system > const > eq_ref > ref > range > index > all > null

事实上有这些：

 

all 是全表扫描，百万级别数据需要优化。一般来说需要达到range级别，最后是ref级别

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

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

eq_ref: 唯一索引扫描，对于每一个索引键，表中只有一条记录与之匹配，常见于主键或唯一索引扫描。

ref: 非唯一性索引扫描，返回匹配某个单独值的所有行，本质上是一种索引访问，返回所有匹配某个单独值的行，然而它可能找到多个符号条件的行，属于查找和扫描的混合体。

range：只检索给定范围的行，使用一个索引来选择行，key列显示使用了哪一个索引，一般就是在你的where语句中出现了between、<、>、in等查询

这种范围扫描索引比全表扫描要好，因为它只需要开始于索引的某一点，而结束于另一点，不用扫描全部索引。

index： 只遍历索引树，通常比all快，因为索引文件比数据文件通常要小。

all：扫描全表。

• possible_keys、key

possible_keys，显示可能应用在这张表的索引，一个或者多个。不一定被使用。

key：实际使用到的索引，查询中若使用了覆盖索引，则该索引仅出现在key列表中。

• key_len 表示索引中使用的字节数，可以通过该列计算使用的索引的长度，在不损失精确性的情况下，长度越短越好。

key_len显示的值为索引字段的最大可能长度，并非实际使用长度，即key_lens是根据表定义计算而来，不是通过表内检索得出。

• ref 显示索引的哪一列被使用了，如果可能的话，是一个常数。哪些列或者常量被用于查找索引列上的值。

 

• rows 行数，反应有多少行被查询

• extra 额外的，包含不适合在其他列显示，但又十分重要的信息

1、Using filesort ：说明mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。mysql无法利用索引完成的排序操作称为“文件排序”。 如果可以尽快优化

2、Using temporary： 说明mysql新建了临时表，在查询结果排序时，使用临时表，常见于排序、分组。出现这个在大数据情况下必须优化。

3、Useing index；使用了索引，这个是好事。如果同时出现了Useing where： 表示索引用来执行索引键值的查找。如果没有同时出现，则说明索引用来读取数据而非执行查找动作。

覆盖索引：select的数据列只用从索引中就能获取，不必读取数据行，mysql可以利用索引返回select列表中的字段，而不必根据索引再次读取数据文件，换句话说 查询列要被所建的索引覆盖。

注意：要使用覆盖索引，一定要注意select列表中只取出需要的列，不要用select *

4、Useing where

5、Using join buffer 使用了连接缓存

6、impossible where ：where字句的值总是false，不能用来获取任何元祖

7、select tables optimized away： 在没有groupby字句的情况下，基于索引优化Min/max 操作或者对于myisam存储引擎优化 count（*）操作，不必等到执行阶段再进行计算，查询执行计划生成的阶段即完成优化。

8、distinct： 优化distinct操作，在找到第一匹配的元组即停止找相同值的操作。

能干嘛？

1、表的读取顺序 id

2、数据读取操作的操作类型 type

3、哪些索引可以使用 possible_keys

4、哪些索引被实际使用 key

5、表之间的引用 table

6、每张表有多少行被优化器查询 rows

 

 

3、索引优化

3.1 单表

准备数据

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

需求：查询category_id 为1 且comments大于1的情况下，views最多的文章的id

# 查询category_id 为1 且comments大于1的情况下，views最多的文章的id
SELECT id, author_id FROM article WHERE category_id=1 AND comments>1 ORDER BY views DESC LIMIT 1;

结构可以查出来。但是我们来分析一下

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

结果：

 

可以看到，type是all说明是 全表扫描，而且extra 有 Using filessort 说明使用了外部的文件索引。都是最差的情况

优化：

1.1、 新建索引 + 删除索引

# 创建一个复合索引
# ALTER TABLE article ADD INDEX idx_article_ccv(category_id, comments, views);
CREATE INDEX idx_atricle_ccv ON article(category_id,comments,views);

再次分析一下：

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

 

可以看到，建立索引之后，解决了type的问题，变成了range，但是没有解决 Using filessort

已经建立了索引，为什么会失效呢？

按照Btree索引的工作原理，先排序category_id,如果遇到相同的category_id则再排序comments，如果遇到相同的comments则再排序views，当comments字段在联合索引里面处于中间位置是，

因为comments>1 条件是一个范围即range，mysql无法利用索引对后面的views进行检索，range类型查询字段后面的索引无效。

上面建立的这个索引不合适，我们删除掉。

DROP INDEX idx_atricle_ccv ON article;

我们再来建立一个索引：

既然range会失效，那就略过这个commens建索引试试

CREATE INDEX idx_atricle_cv ON article(category_id,views);

再分析：

 

我们发现，type 变成了ref，而且没有了 filessort，ref里面是const。这就非常好了，解决了问题。

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

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

分析：

 

结果：type都是 all

优化：思考索引到底加在哪一张表上？

添加索引，我们给从表 book 为card 建立索引

ALTER TABLE book ADD INDEX idx_book_card(card);

分析：

 

 

建立了索引的从表b的type从all变成了ref，实际使用了索引。

删除这个索引，我们试试加在主表上

DROP INDEX idx_book_card ON book;
ALTER TABLE class ADD INDEX idx_class_card(card);

分析：

 

结果：加在主表上，主表上面的type 变成了index，还不如上面加在从表上效果好。

结论：左连接加在从表上，同理，右连接也是要加在从表上。

3.3 三表

准备第三张表

CREATE TABLE IF NOT EXISTS phone(
	phoneid INT(10) UNSIGNED NOT NULL auto_increment,
	card INT(10) UNSIGNED NOT NULL,
	PRIMARY KEY(phoneid)
);

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

删除上一次建立的索引

DROP INDEX idx_class_card ON class;

如果不记得索引，可以查看一下

SHOW INDEX FROM class;
SHOW INDEX FROM book;

需求：

SELECT * FROM class c LEFT JOIN book b ON c.card = b.card LEFT JOIN phone p ON b.card = p.card;

分析：

 

都是全表扫描

优化：

建立2条索引，主表是class，给副表 book 和 phone建立索引

ALTER TABLE phone ADD INDEX idx_p_card(card);
ALTER TABLE book ADD INDEX idx_b_card(card);

分析：

 

现在变成了2个ref，一个all了。索引需要设置在经常查询的字段中

JOIN 的优化：

1、尽可能减少join的NestedLoop 的循环总次数，用小结果驱动大的结果集，即主表用小表，副表用大表，大表建索引

2、优先优化NestedLoop的内存循环

3、保证join语句中被驱动表上join条件字段已经被索引

4、当无法保证被驱动表的join条件字段被索引且内存充足的前提下，不要太吝啬joinbuffer的设置

3.4 索引优化

索引失效（应该避免）

准备数据

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());
SELECT * FROM staffs;

ALTER TABLE staffs ADD INDEX idx_staffs_nameAgePos(NAME,age,pos);

1、全值匹配我最爱，即索引里面的字段都要有。

2、最佳左前缀法则

如果索引了多列，要遵守最佳左前缀法则，即查询从索引的最左前列开始并且不要跳过索引中的列。

简单讲，就是索引的第一个字段不能丢掉，比如idx_staffs_nameAgePos(NAME,age,pos)， 这里面的第一个NAME 字段，不能丢失。而且中间的age也不要跳过去不用直接用pos

结论：where条件使用索引字段，要按照索引的顺序来

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

EXPLAIN SELECT * FROM staffs WHERE NAME = 'July';

 

EXPLAIN SELECT * FROM staffs WHERE LEFT(NAME,4) = 'July';

 

上面2条sql的结果一样，但是效率差很多。增加了left函数操作的的sql的type变成了all

4、存储引擎不能使用索引中范围条件右边的列

EXPLAIN SELECT * FROM staffs WHERE NAME = 'July' AND age > 25 AND pos='manager';

 

pos 在age后面，age是一个范围，会导致pos无法使用到索引。

5、尽量使用覆盖索引，只访问索引的查询，减少使用select *

#比较
EXPLAIN SELECT * FROM staffs WHERE `NAME`='July' AND age=25 AND pos='dev';
EXPLAIN SELECT NAME,age,pos FROM staffs WHERE `NAME`='July' AND age=25 AND pos='dev';

第一条sql结果

 

第二条sql的结果

 

可以看到，第二种写法的extra中使用到了 using index，这是非常好的。效率会高一点，尤其体现在数据量特别大时。

6、mysql在使用不等于的时候无法使用索引，导致全表扫描

7、is null，is not null 也无法使用索引

8、like以通配符开头的，索引也会失效，变成全表扫描。

EXPLAIN SELECT * FROM staffs WHERE NAME LIKE '%July%';

 

再看下面这条sql

EXPLAIN SELECT * FROM staffs WHERE NAME LIKE 'July%';

 

因此, 在使用like时,最好是不要以%开头。

如何解决 like以%开头时，索引失效的问题？

CREATE TABLE IF NOT EXISTS 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)
);
INSERT INTO tbl_user(`NAME`, age, email) VALUES('1aa1', 21, 'b@163.com'),
('2aa2', 222, 'a@163.com'),
('3aa3', 265, 'c@163.com'),
('4aa4', 21, 'd@163.com');

SELECT * FROM tbl_user;

建立一个索引

CREATE INDEX idx_user_nameAge ON tbl_user(NAME, age);

使用覆盖索引，来保证不是全表扫描

EXPLAIN SELECT `NAME`, age FROM tbl_user WHERE `NAME` LIKE '%aa%';

 

注意select之后的字段必须与索引相关，不要用* 不要加上索引中没有的字段。

所谓覆盖索引，就是查询的字段的个数和顺序最好与索引中字段的个数和顺序一致。

9、字符串不加单引号 索引会失效【必须注意】

EXPLAIN SELECT `NAME` FROM staffs WHERE NAME='2000';

EXPLAIN SELECT `NAME` FROM staffs WHERE NAME=2000;

 

可以看到，字符串类型的name没有写单引号时，type从 ref下降到了index！！！

原因是进行了自动类型转换。

10、少用or，or会导致索引失效

EXPLAIN SELECT * FROM staffs WHERE name='July' OR name='z3';

 

or 会导致索引失效

 

3.5 练习

准备数据

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'),
('b1','b2','b3','b4','b5'),
('a1','a2','a3','a4','a5'),
('c1','c2','c3','c4','c5'),
('d1','d2','d3','d4','d5');

建立索引：

CREATE INDEX idx_test03_c1234 ON test03(c1,c2,c3,c4);

分析索引使用情况。

语句1

EXPLAIN SELECT * FROM test03 WHERE c1='a1' AND c2='a2' AND c4='a4' AND c3='a3';

 

使用了4个索引

语句2

EXPLAIN SELECT * FROM test03 WHERE c4='a4' AND c3='a3' AND c2='a2' AND c1='a1';

 

倒转顺序发现，也用到了索引，4个。

看起来违反了前面讲的 最左侧原则，实际上这是因为mysql对我们的sql进行了优化，在常量级别它会改变我们写的顺序。

语句3

EXPLAIN SELECT * FROM test03 WHERE c1='a1' AND c2='a2' AND c3>'a3' AND c4='c4';

 

由于c3 是范围，因此只用到了3个索引，c4失效

语句4

EXPLAIN SELECT * FROM test03 WHERE c1='a1' AND c2='a2' AND c4>'a4' AND c3='a3';

 

使用了4个，因为mysql调优之后会调转顺序，c4会到最后，而c4后面没有东西了，无所谓导致后面的失效。

语句5

EXPLAIN SELECT * FROM test03 WHERE c1='a1' AND c2='a2' AND c4='a4' ORDER BY c3;

 

c1，c2肯定用到了索引，而c4跳过了c3，没有用到。c3在order by后面，用于排序，也用到了索引

语句6

EXPLAIN SELECT * FROM test03 WHERE c1='a1' AND c2='a2' ORDER BY c3;

这条sql和上一条差不多

语句7

EXPLAIN SELECT * FROM test03 WHERE c1='a1' AND c2='a2' ORDER BY c4;

 

这里 跳过了c3，直接从c4开始排序，extra出现了 filesort

而上面这一条没有！！！

语句8

EXPLAIN SELECT * FROM test03 WHERE c1='a1' AND c5='a5' ORDER BY c2, c3;

 

没有索引失效，只用c1一个字段索引，但是c2、c3用于排序 没有filesort

语句9

EXPLAIN SELECT * FROM test03 WHERE c1='a1' AND c5='a5' ORDER BY c3, c2;

 

这条sql就是将c3、c2的顺序调换了一下，发现出现了 filesort！！！ 原因是没有按照索引顺序来

语句10、11

EXPLAIN SELECT * FROM test03 WHERE c1='a1' AND c2='a2' ORDER BY c2, c3;

 

EXPLAIN SELECT * FROM test03 WHERE c1='a1' AND c2='a2' AND c5='a5' ORDER BY c2, c3;

 

这2条sql 的执行效率是一样的，与未建索引的列 c5 无关

语句12

EXPLAIN SELECT * FROM test03 WHERE c1='a1' AND c2='a2' AND c5='a5' ORDER BY c3, c2;

 

但是这里，却发现没有出现 filesort？？？

这是是因为 虽然按照是 c3、c2排序，但是这里面c2=‘a2’ 已经是一个常量了！！！不用排序了，相当于只有c3排序

语句13

EXPLAIN SELECT * FROM test03 WHERE c1='a1' AND c4='a4' GROUP BY c2, c3;

 

用了1个索引查找，c4跳过了c2、c3没有用上，表面上groub by表面是分组，实际上分组前要排序！！！

语句14

EXPLAIN SELECT * FROM test03 WHERE c1='a1' AND c4='a4' GROUP BY c3, c2;

 

调换了c3、c2顺序，发现不仅出现了 filesort还有temporary 临时表！！！ 就是 c3、c2的顺序与索引不一致！！！

3.6 查询截取分析

1、观察 至少跑一天，看看生产的慢sql情况

2、开启慢查询日志，设置阈值比如5s，抓去处来

3、explain+慢sql分析 可以解决8层问题

4、show profile 再分析查询sql在服务器的执行细节和生命周期情况 解决9成9问题

5、sql服务器参数调优

小表驱动大表：

1、对于join，主表用小表，从表用大表，因为索引建在从表才有效

2、对于in，in里面是字句，先执行，是外循环，用小表

 

exists

select ... from table where exists（子查询）

可以理解为：将主查询的数据放到子查询中做条件验证，根据验证结果 true或者false来决定主查询的数据结构是否得以保留。

Order by 关键字的排序优化

order by 尽量使用index方式进行排序，避免使用filesort方式

准备数据

CREATE TABLE IF NOT EXISTS 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_tblA ON tblA(age, birth);

语句1

EXPLAIN SELECT * FROM tblA WHERE age>20 ORDER BY age;

 

语句2

EXPLAIN SELECT * FROM tblA WHERE age>20 ORDER BY age,birth;

 

语句3

EXPLAIN SELECT * FROM tblA WHERE age>20 ORDER BY birth;

 

这里产生了 filesort，原因是跳过了 第一个索引字段

语句4

EXPLAIN SELECT * FROM tblA WHERE age>20 ORDER BY birth, age;

 

这里也产生了filesort， 原因是 order by 的字段顺序与索引不一致

语句5

EXPLAIN SELECT * FROM tblA ORDER BY birth;

 

通用产生了filesort

语句6

EXPLAIN SELECT * FROM tblA ORDER BY age;

 

这里就没有了，因为age就是索引的第一个字段

语句7

EXPLAIN SELECT * FROM tblA WHERE birth>'2020-01-01 00:00:00' ORDER BY birth;

 

加上where条件也没什么用，order by 的字段虽然是索引字段，但是不是第一个，或者说跳过了第一个，还是出现了filesort

语句8

EXPLAIN SELECT * FROM tblA WHERE birth>'2020-01-01 00:00:00' ORDER BY age;

 

语句9

EXPLAIN SELECT * FROM tblA ORDER BY age ASC, birth DESC;

 

这里也产生了filesort，order by 默认是升序，使用desc会产生filesort

order by 语句使用索引最左前列，order by 与 where 字句条件满足索引最左前列原则。

filesort 有2种算法，双路排序和单路排序

4.1版本之前，使用双路排序，扫描2次磁盘，得到数据

单路排序：从磁盘读取需要的所有列，安装order by列在buffer对它们进行排序，然后扫描排序后的列表进行输出，它的效率高一些，避免了第二次读取数据，把随机io变成了顺序io，但是会消耗更多的空间，因为把每一行都保存在内存中了。 如果内存不足，会导致单路算法反而不如双路算法。

优化策略： 增大sort_buffer_size参数的设置

增大max_length_for_sort_data参数的设置

提高order by 的速度：

1、order by 时，不要用select *，只写需要的字段。

当 查询的字段大小总和小于 max_length_for_sort_data 而且排序字段不是 TEXT|BLOB 类型时，会用改进后的算法，否则用老算法。

两种算法的数据都有可能超出sort_buffer的容量，超出之后，会创建tmp文件进行合并排序，导致多次io，但是用单路排序风险会更大一些，所有要提高sort_buffer_size

2、尝试提高 sort_buffer_size

不管用哪一种算法，提高这个参数都会提高效率。

3、尝试提高 max_length_for_sort_data

提高这个参数，会增加使用改进算法的概率，但是如果设置的太高，数据总容量超出sort_buffer_size 的概率就增大，明显症状是高的磁盘io活动和低的cup使用率。

为排序使用索引

1、mysql2种排序方式：文件排序和扫描有序排序

2、mysql能为排序与查询使用相同的索引

 

group by 实质是先排序后分组

where高于having，能写在where限定的条件就不要使用having

其他和order by 一致

3.7 慢查询日志

mysql提供的一种日志记录，会记录超过设置阈值的sql执行。

默认情况下，mysql没有开启慢查询日志，需要手动设置

如果不是调优，建议不要开启慢查询日志！

查看：

SHOW VARIABLES LIKE '%slow_query_log%';

 

我这里已经开启了。

可以使用命令开启

SET GLOBAL slow_query_log=1;

使用命令只能是本次生效。重启失效。

永久生效需要修改配置文件。

阈值时间的设置？

可以先查询

SHOW VARIABLES LIKE '%long_query_time%';

 

可以使用命令设置，也可以修改配置文件。

注意，需要大于阈值才会被记录，小于等于不会记录。

set GLOBAL long_query_time=3;

注意，修改后需要重新开一个连接才能看到修好后的值。

查看慢查询状态

SHOW GLOBAL STATUS LIKE '%slow_queries%'

配置文件可以配置如下的配置

slow_query_log=1;
slow_query_log_file=/var/lib/mysql/xxx.log;
long_query_time=3;
log_output=FILE;

日志分析工具： mysqldumpslow

得到返回记录集合最多的10个sql

mysqldumpslow -s r -t 10 /var/lib/mysql/xxx.log

得到访问次数最多的10个sql

mysqldumpslow -s c -t 10 /var/lib/mysql/xxx.log

得到按照时间排序的前10个sql里面含有左连接的查询语句

mysqldumpslow -s t -t 10 -g "left join" /var/lib/mysql/xxx.log

-s ：按照何种方式排序

c：访问次数

l：锁定时间

r：返回记录

t：查询时间

al：平均锁定时间

ar：平均返回记录数

at：平均查询时间

-t：返回前面多少条的数据

-g：后面搭配正则表达式，大小写不敏感

建议结合| more 使用，否则会有暴屏的可能

mysqldumpslow -s r -t 10 /var/lib/mysql/xxx.log | more