[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