MySql高级:explain及索引优化

一、mysql安装linux版本rpm安装

查看是否安装了mysl
rpm -qa | grep -i mysql

一定要下载指定的64位,因为电脑是64位的否则会安装失败
https://www.jb51.net/article/129294.htm 报错解决办法
安装mysql服务端

rpm -ivh  MySQL-client-5.5.48-1.linux2.6.x86_64.rpm 

安装mysql客户端

rpm -ivh  MySQL-server-5.5.48-1.linux2.6.x86_64.rpm 

查看是否安装成功,可以在以下看到mysql相关用户和组
cat /etc/passwd| grep mysql
cat /etc/grop | grep mysql

设置root密码
/usr/bin/mysqladmin -u root password root

远程xshell机器无法连接时候需要设置host为%
update user set host = ‘%’ where user =‘root’ and host=‘127.0.0.1’;

1 查看mysql安装目录

ps -ef | grep mysql

/var/lib/mysql mysql数据库文件存放路径
/usr/share/mysql 配置文件目录
/usr/bin 相关命令目录
/etc/init.d/mysql 启停相关脚本

2 修改配置文件的位置:跟版本有关系

cp var/share/mysql/my-huge.cnf /etc/my.cnf

3 修改字符集

查看字符集:shwo variables like ‘%char%’;
[client]下面
default-character-set=utf8

mysqlId下面
character_set_server=utf8
character_set_client=utf8
collation-server=utf8_general_ci

mysql下面
default-character-set=utf8

4 mysql配置文件

二进制日志log-bin 主从赋值
错误日志log-error 默认关闭,记录严重警告和错误信息,每次启动和关闭的信息
查询日志log 默认关闭,记录查询的sql语句,如果开启会减低mysql整体性能,
数据文件
ls -lF | grep ^d
linux 默认路径/var/lib/mysql

frm文件:存放表结构
myd文件:存数据
myi文件:查询数据表的索引

5 逻辑架构

连接层,服务层,引擎层,存储层

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

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

三:引擎层:存储引擎层,存储引擎真正的负责Mysql中数据的存储和读取,服务器通过API与存储引擎进行通信,
不同的存储引擎具有的功能不同,这样我们可以根据自己的实际需要进行选取,后面介绍MyISAM和InnoDB

四:存储层:数据存储层,主要是将数据存储在运行于该设备的文件系统之上,并完成与存储引擎的交互。

6 存储引擎

在这里插入图片描述

show engines 
show variables like '%storage_engine%'

InnoDB:支持行锁,事务,外键,缓存 适合高并发

7 sql性能下降原因

1 查询语句写的烂
2 索引时效:单值,复合
3 关联查询太多join(设计缺陷或者不得已的需求)
4 服务调优及各个参数设置(缓冲,线程数等)

8 sql执行顺序

手写顺序
在这里插入图片描述
机读顺序
在这里插入图片描述

from
on
join type join
where
group by
having
select
distinct
order by
limit

9 七种join理论

select a from A a left join B b on a.key=b.key;
select a from A a right join B b on a.key=b.key;
select a from A a join B b on a.key=b.key;
select a from A a right join B b on a.key=b.key where b.key is null;
select a from A a right join B b on a.key=b.key where a.key is null;
select a from A a full join B b on a.key=b.key ;
select a from A a full join B b on a.key=b.key where a.key is null or b.key is null;

10 索引

帮助mysql高效获取数据的数据结构,可以得到索引的本质是数据结构

在数据之外,数据库还维护着满足特定查找算法的数据结构,这些数据结构以某种方式引用(指向)数据
这样就可以在这些数据结构上实现高效查找算法,这种数据结构就是索引

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

11 索引优劣

优势:
提高检索效率,降低数据库io成本
通过索引对数据进行排序,降低数据排序成本,降低cpu消耗

劣势:
1 索引也是一张表,保存了主键和索引字段,并指向实体表的记录,所以索引也是要占用空间的。
2 虽然索引大大提高了查询速度,同时却会降低更新表的速度,如对表进行insert ,update,delete,
因为更新表时,mysql不仅保存数据,还要保存一下索引文件每次更新添加索引列的字段,这样都会带来调整因为更新所带来的键值变化后的索引信息
3 索引只是提高更高效率一个因素,如果你的mysql有大量表,需要花时间研究建立最优索引或者查询优化查询

12 索引分类

一个表建议最多五个索引

单值索引
复合索引

基本语法:

创建索引
create 【unique】index index_name on tablename(columnname(length))
删除索引
drop index [indexname] on mytable
显示索引
show  index from  table_name

;
有四种方式添加数据表索引
alter table tbl_name add primary key (column_list),添加一个主键
alter table tbl_name add unique index_name(column_list) 添加唯
alter table tbl_name add index index_name(column_list) 普通索引
alter table tbl_name add FULLTEXT index_name(column_list) 指定索引为FULLTEXT,用于全文检索

13 索引结构与检索原理

在这里插入图片描述

一棵B+树,浅蓝色的块我们称之为一个磁盘块,可以看到每个磁盘块包含几个数据项(深蓝色所示)和指针(黄色所示),
如磁盘块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指针,通过指针加载磁盘块B到内存,
发生第三次IO,同时内存中做二分查找找到29,结束查询,总计三次IO。

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

14 哪些情况适合建立索引

一:主键自动建立唯一索引。

二:频繁作为查询条件的字段应该建立索引

三:查询中与其他表关联的字段,外键关系建立索引

四:频繁更新的字段不适合建立索引,因为每次更新不单单是更新了记录还会更新索引

五:where条件里用不到的字段不创建索引

六:单键/组合索引的选择问题,who?(在高并发下倾向创建组合索引)

七:查询中排序的字段,排序字段若通过索引去访问将大大提高排序的速度。

八:查询中统计或者分组字段

哪些不适合建立索引

一:表的记录太少

二:经常增删改的表,因为,提高了查询速度,同时却会降低更新表的速度,如对表进行INsert,
UPDATE,DELETE。因为更新表时,MYSQL不仅要保存数据,还要保存一下索引文件,

三:数据重复且分布不均匀的表的字段,因此应该只为最经常排序的数据列建立索引,
注意:如果某个数据列包含许多重复的内容,为它建立索引就没有太大的实际效果。

例子:假如一个表有10万行记录,有一个字段A只有T和F两种值,且每个值的分布概率大约为
50%,那么对这种表A字段建索引一般不会提高数据库的查询速度。
索引的选择性是指索引列中不同值的数目与表中记录数的比,如果一个表中有2000条记录,
表索引列有1980个不同的值,那么这个索引的选择性就是1980/2000=0.99一个索引的选择性
越接近1,这个索引的效率就越高。

二、 explain 执行计划

查看执行计划,使用关键字explain可以模拟优化器执行sql查询语句,从而可以理解你sql语句。分析你的查询sql或表结构的性能瓶颈

考虑到查询效率问题,要尽量避免全表扫描和全索引扫描,也要避免 extra 的结果为 using filesort 的语句。
执行计划包含的信息
在这里插入图片描述
在这里插入图片描述

id

查询的序列号,包含一组数字,表示查询中执行select子句或者操作表的顺序
id相同,执行顺序自上而下
id不同,如果是子查询,id的序号会递增,id值越大优先级越高,越早被执行
id相同,不同同时存在,id值越大优先级越高,同级的按顺序。

select_type

1 sipmle:简单select查询,查询中不包含子查询或者union
2 primary:查询中若包含任何复杂子查询部分,最外层查询被标记为
3 subquery:在select或者where列表中包含了子查询
4 derived:在from列表中包含的子查询被标记为derived(衍生)
mysql会递归执行这些子查询,把结果放在临时表
5 union:若第二个select出现在union之后则被标记为union
若union包含在from子句的子查询中,外层select将会被标记为derived
6 union result:从union表获取结果的select

DEPENDENT SUBQUERY:子查询中的第一个SELECT,取决于外面的查询 ,

注意如果外部查询的结果集数量比较大,

比如几十万上百万,就会执行几十万上百万次子查询,必然造成慢查。

很多使用子查询的人 想当然的认为 子查询会由内到外,先完成子查询的结果,

然后在用子查询来驱动外查询的表,完成查询。

例如:select * from test where tid in (select aid from sub_test where gid=3)

如果是出现DEPENDENT SUBQUERY , 那么就会先执行外部的查询 , 然后再循环执行内部的查询
DEPENDENT SUBQUERY:子查询中的第一个SELECT,取决于外面的查询 ,

注意如果外部查询的结果集数量比较大,

比如几十万上百万,就会执行几十万上百万次子查询,必然造成慢查。

很多使用子查询的人 想当然的认为 子查询会由内到外,先完成子查询的结果,

然后在用子查询来驱动外查询的表,完成查询。

例如:select * from test where tid in (select aid from sub_test where gid=3)

如果是出现DEPENDENT SUBQUERY , 那么就会先执行外部的查询 , 然后再循环执行内部的查询
避免执行计划中出现dependent subquery关健字;
通常可以将dependent subquery子查询改写为inner join查询

另外,在执行sql前,都尽量的explain一下吧,看看结果集是否可接受.在结果集看到SUBQUERY , DEPENDENT SUBQUERY ,或者Using temporary,Using join buffer类似的,赶紧优化,该加索引的加,该改sql的改.关于explain的结果集,这里只是举例说明,优化是个漫长而艰巨的过程!

table

table:表示查询的哪个表里面的值

type

访问类型排列 all index range ref eq_ref const system null
从最好到最差
system > const > eq_ref > ref > range > index > all

全部范围:system>const>eq_ref>ref>fulltext>ref_or_null>index_merge>unique_subquery>index_subquery>range>index>all

一般说能保证查询至少达到range级别,最好达到ref

system

system:表中只有一个记录(等于系统表) 这是const类型的特例,平时不会出现,这个可以忽略

const

const 表示通过索引一次就可以找到,const用于比较primary或者卫衣 缩索引,因为只匹配一行记录,如将主键配置在where列表,mysql就能将该查询转化为一个常量

eq_ref

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

ref

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

range

range:只检索指定范围的行,使用一个索引来显示行,key列显示了哪个索引,一般就是在你的where语句中出现了between < > in 等查询。
这种范围扫描会比表扫表好。

index

index:full index scan index与all的区别就在于index遍历索引树,通常比all快,因为索引文件通常比数据文件小,
(也就是说虽然all和index都是读全表,但是index是从索引中获取,而all从硬盘获取)

all

all full table scan 遍历全表

possible_keys

显示可能应用在这张表中的索引,一个或者多个,查询中设计到的字段上若存在索引,则会被列出,但不一定被查询实际使用

key

实际使用的索引,如果是null,则没有使用索引

key_len

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

ref

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

rows

根据表统计信息及索引选用情况,大致估算出找到所需的记录所需要读取的行数

extra

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

using filesort

using filesort 说明mysq会对数据使用一个外部索引排序,而不是按照表内的索引顺序进行读取,Mysql中无法利用索引文成的排序称为文件排序

using temporary

using temporary : 使用临时表保存中间结果,mysql在对查询结果排序时使用临时表,常见于order by和分组查询group by

using index

using index :表示相应的select操作中使用了覆盖索引,避免访问表的数据行,效率还行。如果同时出现using where 表明索引被用来执行索引键值的查找,如果没有同时出现using where,表明索引用来读取数据而非执行查找动作。

using where

using join buffer

impossible where

select table optimized away

distinct

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) not null,
	views int(10) UNSIGNED not null,
	conmments int(10) UNSIGNED not null,
	title varchar(255) not null,
	content text not null
);

insert into article (author_id,category_id,views,conmments,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的情况下,views最多的article_id

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

# 结论:很显然type是all,最坏的情况,extra  里还出现filesort 也是最坏情况,优化是必须的。

# 开始优化
# 1.1 新建索引+ 删除索引
# alter table article add index idx_article_cvv(category_id,comments,views);
# create index idx_article_cvv on article(category_id,comments,views);
 create index idx_article_cvv on article(category_id,conmments,views);

show index from article;

drop index idx_article_cvv on article;
# 大于等范围操作会导致索引后面的失效,这里views这个索引失效
create index idx_article_cv on article(category_id,conmment);
drop index idx_article_cv on article;

create index idx_article_cv on article(category_id,views);

2 两表索引优化案例

create table if not EXISTS class(
	id int(10) UNSIGNED not null auto_increment,
	card int(10) not null,
	PRIMARY key(id)
);

create table if not EXISTS book(
	bookid int(10) not null auto_increment,
	card int(10) 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)));

select * from class;

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


select * from book;

select * from book b inner join class c on b.card = c.card;
# 下面开始分析explain
explain select * from class c left join book b on b.card = c.card;
# 结论:两个都 type是all
# 添加索引优化
alter table book add index Y(card);

# 第二次explain
explain select * from class c left join book b on b.card = c.card;

# 可以看到第二行type变为ref,rows变为1,优化比较明显
#这个是由左连接特性决定,left join条件用于确定如何从右边搜搜行,左边一定有,所以右边是关键点,一定要建立索引

# 删除旧的索引+新建+第三次explain
drop index Y on book;
create index Y on class(card);
explain select * from class c left join book b on b.card = c.card;
# 结论 class 表用到索引,但是rows还是没有减少,效果不明显。

# 然后来看一个右连接查询
explain select * from class c right join book b on b.card = c.card;

# 优化比较明显,这是因为right join条件用于确定如何从左侧表搜索行,右边一定有,从左边是我们的关键,一定要建立索引

#都是相反方向建索引

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

select * from phone;

show index from class;
drop index Y on class;

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


alter table phone add index Z(card);
alter table book add index Y(card);


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

结论

后两行type都是ref,总行优化很好,因此索引最好设置在需要经常查询的字段

join语句的优化,尽量减少join语句中循环的总次数,永远用小结果集驱动大的结果集
优先优化嵌套循环内层循环
保证join语句中被驱动表上join条件字数已经被索引
当无法保证被驱动表的join字段被索引且内存资源充足的情况下,不太吝啬joinbuffer设置

三、避免索引失效

1 全值匹配我最爱:索引怎么建的就怎么查几列
2 最佳最前缀法则:如果索引了多列,需要遵循最左侧前缀法则,指的是查询从索引的最左列开始并且不能跳过索引中得列。
3 不在索引列上做任何操作(计算,函数,自动或者手动类型转化,会导致索引失效)
4 存储引擎不能使用索引中范围条件右边的列 > in like between and等
5 尽量使用覆盖索引(只访问索引查询(索引列和查询列一致)),减少select

6 mysql在使用不等于(<>或者!=)时候无法使用索引会导致全表扫描
7 is null,is not null也无法使用索引
8 like以通配符开头(“%abc”)索引失效变成全表扫描
9 字符串不加单引号会导致索引失效
10 少用or,用它来连接时会索引失效
*

四、索引两大功能:索引和排序

order by 字段在where中是常量等于的时候就在排序中无所谓,不会filesort
explain select * from test where c1=‘a1’ and c2=‘a2’ order by c3,c2;

定值,范围还是排序,一般order by 是给个范围
group by 基本上都需要进行排序,会产生临时表

五、查询优化

1 观察,至少跑一天,看看生产的慢sql情况
2 开启慢日志查询,设置阈值(阙值)比如超过5秒就是慢sql,并将它抓取出来
3 explain+慢sql分析
4 show profile
5 运维经理或者DBA进行数据库服务器参数调优

== 步骤总结:
1 慢查询开启并捕获
2 explain+慢sql分析
3 show profile 查询sq在mysql服务器里面的执行细节和生命周期
4 sql数据库服务器参数调优

1 小表驱动大表

in和exists性能比较

优化查询原则:小表驱动大表,即小数据集驱动大的数据集:原理RBO
select * from A where id in (select id from B);
等价于
for select id from B
for select * from A where A.id = B.id

 当B的数据集必须小于A表时,用in优于exists
 
 select * from A where exists (select 1 from B where A.id=B.id)
 等价于
 for select* from A
 for select * from B where B.id = A.id
 当A表数据集小于B表数据集时,用exists优于in
 
 注意A表与B表的id字段应该建立索引 

exists:select ...from table where exists(subquery)
语法可以理解为将主查询的数据,放在子查询中做条件验证,根据验证结果(true、false)来决定主表查询结果是否保留


提示:
1 exists(subquery)只返回ture或者false,因此子查询select也可以是select* 或者select 1 等其他。
   官方说法是实际执行时会忽略select清单,因此没有区别
2 exists子查询的实际执行过程可能经过优化而不是我们理解上的逐条比对,如果担心效率问题,可以实际验证以确定是否有效率问题。
3 exists子查询往往可以用条件表达式,其他子查询或者join来代替,何种最优需要具体问题具体分析。

2 order by 关键字优化

order by 子句尽量使用index方式排序,避免使用filesort排序尽可能在索引上完成排序操作,遵照索引最左前缀原则

如果不在索引列上,filesort有两种算法:双路排序和单路排序

双路算法

mysql4.1之前是使用双路排序,字面意思就是两次扫描磁盘,最终得到数据。
读取行指针和orderby列,对他们进行排序,然后扫描已经排好序的列表,按照列表中值重新从列表或获取数据
从磁盘取排序字段,在buffer进行排序,再从磁盘获取其他字段
取一批数据,要对磁盘进行两次扫描,总所周知IO很耗时所以在mysql4.1后出现第二种算法,单路排序

单路算法

从磁盘读取查询所需要的所有列,按照orderby在buffer对他们进行排序,然后扫
描排序后的列表进行输出,它的效率更快,避免第二次读取数据,并把随机io变成顺序io,但是它会使用更多空间,
因为它把每一行都保存在内存中。
在使用单路有问题,在sort_buffer中,方法B比方法A占用更多空间,因为方法多B把所有字段取出,所以有可能取出来的数据总大小
超出了sort_buffer容量,导致每次只能读取sort_buffer大小数据,进行排序(创建tmp文件,多路合并),排序完再取sort_buffer容量大小再排…多次io,
得不偿失。

优化策略

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

create table tblA (
	age int,
	birth timestamp not null
);

insert into tblA(age,birth)values(22,now()),(23,now()),(24,now());

create index idx_A_ageBirth on tblA(age,birth);
select * from tblA;

提高order by 速度

1 order by 时select*是大忌,只query需要的字段,这非常重要,在这里的影响是
1.1 当query字段大小总和小于max_leng_for_sort_data而且排序字段不是text|blob类型时候,会用改进后的算法–单路排序算法排序
否则就用多路排序
1.2 两种算法的数据有可能超过sort_buffer,超出后会创建临时文件tmp并进行合并,导致多次io,但是单路排序算法风险更大,所以要提高sort_buffer_size

2 尝试提高sort_buffer_size
不管哪种算法,提高这参数都会提高效率。

3 提高max_length_for_sort_data
提高这个参数,会增加用改进后的算法。如果设置过高,数据总容量超出sort_buffer_size概率会大。明细症状是
高磁盘io和低处理器使用。

3 group by 关键字优化

group by实质是先排序后分组,遵照索引最左前缀原则,
当无法使用索引列,增大max_leng_for_sort_data+sort_buffer_size参数
where高于having,能写在where限定条件就不要去having限定。

4 索引排序优化小总结

为排序使用索引
	mysql两种排序方式:文件排序或者扫描有序索引排序
	mysql能为排序和查询使用相同索引。
	
	key a_b_c(a,b,c);
	order by 能使用索引最左前缀
	  order by a
	  order by a,b
	  order by a,b,c
	  order by a desc,b desc,c desc
	如果where使用索引最左前缀定义为常量,则order by能使用索引
	  where a=const order by b,c
	  where a=const and b=cosnt order by c
	  where a=const order by b,c
	  where a=const and b>const order by b,c
	  
	 不能使用索引进行排序
	 order by a ASC,b DESC,c DESC 排序不一致
	 where g=const order by b,c  丢失a索引
	 where a=const order by c  丢失b索引
	 where a=const order by a,d d不是索引一部分
	 where a in(...) order by b,c  对排序来说,多个相等条件也是范围查找

5 慢查询日志

是什么:
mysql慢查询日志是mysql提供的一种日志记录。用来记录mysql响应时间超过阀值的语句,具体
指运行时间超过long_qury_time的sql会被记录到慢查询日志中。
long_qury_time默认是10秒,意思是运行10秒以上的语句。
由他来查看哪些sql超过了最大忍耐时间,比如一条sql超过5秒,我们算慢sql,希望能收集超过5秒的sql,结果explain进行全面分析。
说明:
默认情况,myslq没有开启慢sql查询日志,需要手动设置参数。
当然如果不是调优需要的话,一般不建议开启该参数。因为开启或多或少会影响性能。
慢查询日志支持将日志记录写入文件

	show variables like '%slow_query_log%';
	set global slow_query_log = 1;
	
	如果要永久生效,要修改配置文件。my.cnf下的 mysqlid下面增加或者修改参数
 slow_query_log和slow_query_log_file然后重启mysql服务器,也即下面两行配置进my.cnf
 
 slow_query_log=1
 slow_query_log_file=/var/lib/mysql/atguigu-slow.log
 long_queyr_time = 3
 log_output=FILE
 关于慢查询参数slow_query_log_file,指的是存放慢查询日志的sql文件,系统默认是host_name-slow.log

案例

查看当前多少秒算慢 show variables like ‘%slow_queyr_time%’;
设置慢阈值时间 set global slow_query_time = 3;

	设置select sleep(4);
	
	查看文件内有数据记录
	show global  status like '%slow_queries%';
	
	
	日志分析工具mysqldumpslow 
	得到返回记录集最多的10个sql
	mysqldumpslow -s r -t 10 /var/lib/mysql/atguigu-slow.log
	
	得到访问次数最多的10个sql
	mysqldumpslow -s c -t 10 /var/lib/mysql/atguigu-slow.log
	
	得到按照时间排序前10条里面含有左连接的查询sql
	mysqldumpslow -s t -t 10 -g "left join" /var/lib/mysql/atguigu-slow.log
	
	另外建议在使用这些命令时结合| more使用否则可能有爆屏情况

六、show profiles

是什么:mysql提供可以用来分析当前会话语句执行资源的消耗情况,可以用于sq调优测量
官网:http://dev.mysql.com/doc/refman/5.5/en/show-profile.html
默认情况参数处于关闭状态,并保存最近15次操作

1 是否支持 看看当前mysql数据库是否支持 show variables like ‘profiling’;
默认是关闭的,需要开启 set profiling=on;

2 运行sql:就是简单的sql或者查询sql select * from emp group by id%10 limit 15000;
select * from emp group by id%20 order by 5;

3 查看结果show profiels

4 诊断sql show profile cpu ,block io for query 5; – 上面show profiel查询的queryid

参数说明:

  type:
	all  显示所有的开销
	block io 显示io相关开销
	Context switches  上下文切换开销
	cpu  cpu相关开销
	ipc  发送和接收相关开销
	memory  内存相关
	page faults  页面错误相关
	source 显示和source_function,source_file,source_line相关
	swaps  显示交换次数相关

日常开发中需要注意的结论

	covnerting HEAP to MyISAM 查询结果太大,内存不够用往磁盘上搬
	create tmp table 创建了临时表,拷贝数据到临时表,用完删除临时表
	copying to tmp table on  disk 把内存临时表数据复制到磁盘
	locked 锁表

七、mysql锁机制

添加锁
lock table 表名字 read| write ,表名字2 read|wrt ,其他

show open tables; 显示表释放被锁住

释放锁
unlock tables;

myISam 在执行查询语句前,会自动给涉及的所有表加读锁,在增删改操作前,会自动给涉及的表加写锁。
对myisam表读操作(加读锁)不会阻塞其他进程对同一个表的读请求,但会阻塞同一个写操作。
只有读锁释放后才能执行其他进程的写操作。

对myisam表写操作(加写锁),会阻塞其他进程对同一个表的读写操作,只有当锁释放后才能执行其他进程操作

简而言之:读锁会阻塞写,但是不会阻塞读,而写锁会阻塞读和写

InnoDb支持事务,行锁,并发高,无索引行锁升级为表锁,也就是说索引失效会导致行锁升级为表锁
间隙锁危害,范围查找会锁住范围内的所有数据,即使这个id可能不存在也会锁住。
导致新增或者其他操作比如新增一个id在范围内的数据就无法插入成功

表锁分析工具

表锁分析:
		看看哪些表被加锁 show open tables;
		如何分析表锁定 show status like 'table%';
			检查table_waited和table_locks_immediate状态变量分析系统上的表锁定
			table_locks_immediate:产生表级锁定的次数,表示可以立即获取锁查询的次数,每次获取就增加1
			table_waited: 出现表级锁定争用而发生等待的次数(不能立即获取锁的次数,每等待一次就锁增加1)
							此值高说明存在较严重的表级锁争用情况

如何锁定一行:

begin;
select … from table for update;
… 操作这行数据
commit;

行锁分析

通过检查innodb_row_lock 状态变量来分析系统上的行锁争夺情况
show status like ‘innodb_row_lock%’;

	innodb_row_lock_current_waits 当前正在等待锁定的数量
	innodb_row_lock_time 从系统启动到现在锁定总时间长度
	innodb_row_lock_avg 每次等待所花平均时间
	innodb_row_lock_max 从系统启动到现在等待最长的一次所花时间
	innodb_row_lock_waits 系统启动到现在总共等待次数

八、mysql主从复制

slave会从master读取binlog来进行数据同步

1 mysql复制过程分为三步

1 master将改变记录到二进制日志(binary log).这些记录过程叫做二进制日志时间
2 slave将master的二进制日志事件拷贝到他的中继日志 relay log、
3 slave重做中继日志中事件,将改变应用到自己的数据库中,mysql复制是异步串行化的

复制的基本原则

每个slave只有一个master,并且每个slave只能有一个唯一的服务器id,复制是延迟的。

2 一主一从常见配置:

mysql版本大体一致且后台服务运行,主从都配置在mysqlid节点下,都小写。主从服务器修改后都重启mysql服务,保证机器互通

1 主机修改配置文件

server-id=1 主服务器唯一id 必须
	启用二进制日志:比须
		log-bin=d://devSoft/MySqlServer5.5/data/mysqlbin
	启用错误日志:可选
		log-err=d://devSoft/MySqlServer5.5/data/mysqlerr
	根目录:可选
		basedir="d://devSoft/MyServer5.5"
	临时目录:可选
		tmpdir="d://devSoft/MyServer5.5"
	数据目录:可选
		datadir="d://devSoft/MyServer5.5/Data"
	主机读写都可以 read-only=0
	设置不要复制的数据库:可选
		binlog-ignore-db=mysql
	设置需要复制的数据库:可选
		binlog-do-db=需要复制的主数据库名称

主机建立账户并授权slave

grant replication slave on *.* to 'zhangsan'@'从机ip' identified by '123456';
	
刷新	flush privileges;
	
查看状态	show master status; 查询master状态记录file和position值

2 从数据库配置修改

从服务器唯一id:必须 
		server-id=2 把注释去掉,=1的注释掉
	启用二进制日志:可选
	log-bin=mysql-bin

从机设置需要复制的主机

change master to master_host = '主机ip'master_user='zhangsan',
	master_password='123456',
	master_log_file='mysqlbin.具体数字',
	master_log_pos=具体值

3 启动从服务器复制功能

start slave

4 验证主从配置成功

show save status\G

保证下面两个都是yes才可以说明主从配置成功
	slave_io_running=yes
	save_sql_running=yes

5 停止从服务器复制功能

stop slave

  • 1
    点赞
  • 2
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
MySQL中,优化查询的一个重要手段就是使用索引EXPLAIN是用来分析查询语句的工具,可以通过分析EXPLAIN输出结果来进行索引优化。下面是一些常用的优化技巧: 1. 索引覆盖:通过使用覆盖索引,可以避免MySQL访问表格数据,从而提高查询速度。如下所示: ``` EXPLAIN SELECT id FROM table WHERE col1 = 'value'; ``` 在这个查询中,只需要访问索引就可以返回结果,不用访问表格数据。 2. 索引选择:使用合适的索引可以提高查询的效率。一般来说,选择最左前缀列的索引可以让MySQL选择更加有效的索引。如下所示: ``` EXPLAIN SELECT * FROM table WHERE col1 = 'value' AND col2 = 'value2'; ``` 在这个查询中,如果存在(col1, col2)的复合索引,则MySQL会选择它来执行查询,这样可以避免全表扫描。 3. 避免使用函数:在查询中,使用函数会导致MySQL无法使用索引,从而降低查询效率。如下所示: ``` EXPLAIN SELECT * FROM table WHERE YEAR(date_col) = '2021'; ``` 在这个查询中,使用YEAR函数会导致MySQL无法使用(date_col)的索引来执行查询,可以考虑将查询条件改为: ``` EXPLAIN SELECT * FROM table WHERE date_col >= '2021-01-01' AND date_col < '2022-01-01'; ``` 4. 避免使用OR:在查询中,使用OR会导致MySQL无法使用索引,从而降低查询效率。如下所示: ``` EXPLAIN SELECT * FROM table WHERE col1 = 'value1' OR col2 = 'value2'; ``` 在这个查询中,可以考虑将查询条件改为: ``` EXPLAIN SELECT * FROM table WHERE col1 = 'value1' UNION SELECT * FROM table WHERE col2 = 'value2'; ``` 5. 避免全表扫描:全表扫描是一种效率比较低下的查询方式,可以通过优化查询条件或者添加合适的索引来避免全表扫描。如下所示: ``` EXPLAIN SELECT * FROM table WHERE col LIKE '%value%'; ``` 在这个查询中,可以考虑将查询条件改为: ``` EXPLAIN SELECT * FROM table WHERE col LIKE 'value%'; ``` 这样可以使用(col)的索引来执行查询,避免全表扫描。

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值