MySql性能优化

1. mysql逻辑结构

 

2. mysql 物理结构

日志文件：

Error log
       Binary log
       Slow query log
       Innodb redo log

数据文件：

.frm文件：
        .MYD文件：
        .MYI文件：
        .ibd文件：

其他文件：       

配置文件：
       pid文件：
       socket文件：

Inodb和MyISAM；
INNODB：行级锁、支持事务、外键，B+索引；---- 功能和安全优先。使用innodb;
MyISAM：表锁，不支持事务，不支持外键，B+索引；---性能优先，使用MyISAM；

 

3. sql语句执行过程

SQL性能优化目的

避免出现页面访问错误

增加数据库的稳定性

优化用户体验

 

SQL优化层次

SQL优化：
        索引优化；
        设计优化；
        数据库配置优化；
        操作系统配置优化；
        数据库服务器硬件优化：

4. 性能问题诊断

4.1 捕捉性能问题

使用慢日志功能来诊断性能问题；
show variables like '%slow%';
show variables like '%long%';
show variables like '%log_output%';

两种开启慢日志的方法：
方法一：
set global long_query_time=1.0;
set global slow_query_log=on;
方法二：修改配置文件（修改完成，重启数据库）
long_query_time=1.0;
slow_query_log=on;
show full  processlist ：https://www.cnblogs.com/tongcharge/p/11495393.html

cmd连接MySQL：

C:\CRM-NEW\Fuwushe\mysql\bin>mysql -u root -P 13306 -h 127.0.0.1 -p
Enter password: *********
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 92 to server version: 5.0.67-community

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql>

4.2 分析性能问题

4.2.1 使用mysqldumpslow分析性能问题（或者pt-query-digest工具）

mysqldumpslow -s at c -t 3 192-slow.log
pt-query-digest:    https://blog.csdn.net/cqr0591/article/details/100197898

4.2.2 使用explain分析性能问题

数据准备：

   create table a(
   aid int(3),
   aname varchar(20)
   );
   
   create table b(
   bid int(3),
   aname varchar(20),
   bcid int(3)
   );
   
   create table c(
   cid int(3),
   cname varchar(20)
   );
   
   insert into a()values(1,'a'),(2,'b'),(3,'c');
   insert into b()values(1,'aa',5),(2,'bb',6);
   insert into c()values(5,'aaa'),(6,'bbb'),(7,'ccc'),(8,'ddd');

4.2.2.1 Id字段解析（重点字段）

查询aid=1或cid=7的记录的所有信息：

explain select * from a,b,c where a.aid=b.bid and b.bcid=c.cid and (a.aid=1 or c.cid=7);

往b表中再插入两条数据，发现执行计划的执行顺序变成了a,b,c 

所以：多表连接查询的时候，笛卡尔积小的表先执行（ID相同，从上往下执行）；

查询cid=5的字段对应的aid,aname

explain select aid,aname from a where aid=(select bid from b where bcid=(select cid from c where cid=5)); 

 

explain select aid,aname from a where aid=(select bid from b ,c where b.bcid=c.cid and c.cid=5);

小结：ID大的先执行，ID相同的从上往下执行；   

4.2.2.2 select_type字段解析

explain select cr.aid,cr.aname from(select * from a where aid=1)cr;

explain select cr.aid,cr.aname from(select * from a where aid=1 union select * from a where aid=2)cr;

4.2.2.3 type字段解析（重点）：

理论上：system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL
常用的：system > const eq_ref > ref > range > index > all
其中 system > const是理想状态，可遇不可求；

alter table a add primary key(aid);
explain select * from (select * from a where aid=1)t;

range案例：

index案例：

all案例：

4.2.2.4 key_len字段解析（复合索引的时候有用，单索引没用）

https://www.cnblogs.com/zhoujinyi/p/3784450.html
PS：当前表使用的字符集、字段的类型、是否允许非空；

4.2.2.5 Extra字段解析（重点）

create table test02(
	a1 char(3),
	a2 char(3),
	a3 char(3),
	index idx_a1(a1),
	index idx_a2(a2),
	index idx_a3(a3)
	);

order by 语句有可能会出现文件排序，如果看见文件排序，要进行SQL优化

如下，当看到Using filesort时说明性能是有问题的，需要进行优化

 如下，复合索引跨列查询并排序也用到了Using filesort，不跨列却没有问题

如下，复合索引首列不使用也会存在Using filesort

下面的情况则不会出现文件排序，数据库遇到这个情况会自身帮我们做一个优化

小结：

        单列索引，where 谁，order by 谁 ---不会有问题，反之会出现文件排序；
        复合索引，复合索引不能跨列使用，复合索引的首列必须使用，不然会出现文件排序；

如下，Using temporary 对性能影响很大，需要优化；怎么优化，查询谁，就在谁上分组就好了；

4.2.3 使用profile分析性能问题

select @@profiling;