1200字范文 > MySql高级：explain及索引优化

MySql高级：explain及索引优化

时间：2021-11-06 02:41:40

一、mysql安装linux版本rpm安装

查看是否安装了mysl

rpm -qa | grep -i mysql

一定要下载指定的64位，因为电脑是64位的否则会安装失败

/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-f /etc/f

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或表结构的性能瓶颈

执行计划包含的信息

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

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_idexplain 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;# 下面开始分析explainexplain select * from class c left join book b on b.card = c.card;# 结论：两个都 type是all# 添加索引优化alter table book add index Y(card);# 第二次explainexplain select * from class c left join book b on b.card = c.card;# 可以看到第二行type变为ref，rows变为1，优化比较明显#这个是由左连接特性决定，left join条件用于确定如何从右边搜搜行，左边一定有，所以右边是关键点，一定要建立索引# 删除旧的索引+新建+第三次explaindrop 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优于existsselect * from A where exists (select 1 from B where A.id=B.id)等价于for select* from Afor 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 aorder by a，border by a,b,corder by a desc,b desc,c desc如果where使用索引最左前缀定义为常量，则order by能使用索引where a=const order by b,cwhere a=const and b=cosnt order by cwhere a=const order by b,cwhere 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;如果要永久生效，要修改配置文件。f下的 mysqlid下面增加或者修改参数slow_query_log和slow_query_log_file然后重启mysql服务器，也即下面两行配置进fslow_query_log=1slow_query_log_file=/var/lib/mysql/atguigu-slow.loglong_queyr_time = 3log_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个sqlmysqldumpslow -s r -t 10 /var/lib/mysql/atguigu-slow.log得到访问次数最多的10个sqlmysqldumpslow -s c -t 10 /var/lib/mysql/atguigu-slow.log得到按照时间排序前10条里面含有左连接的查询sqlmysqldumpslow -s t -t 10 -g "left join" /var/lib/mysql/atguigu-slow.log另外建议在使用这些命令时结合| more使用否则可能有爆屏情况

六、show profiles

是什么：mysql提供可以用来分析当前会话语句执行资源的消耗情况，可以用于sq调优测量

官网：/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：产生表级锁定的次数，表示可以立即获取锁查询的次数，每次获取就增加1table_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值