MySQL优化

一、优化概述

1、分析MySQL数据库的性能

使用show status语句查询MySQL数据库的性能：show status like 'value';

value：connections、uptime、slow_queries、com_insert、com_select、com_delete

-- 显示连接MySQL服务器的次数
show status like 'connections';
-- 显示慢查询次数
show status like 'slow_queries';
-- 显示查询操作的次数
show status like 'com_select';

2、通过profile工具分析语句消耗性能

查看profile工具是否开启：show variables like '%pro%';

profiling值为ON表示开启，值为OFF表示未开启

开启profile工具：set profiling=1;

使用profile工具查询语句性能的方法：

1. 执行SQL语句
2. 查看SQL语句执行的时间：show profiles;（该命令执行结果显示的是该会话的所有语句的执行时间）

二、优化查询

1、分析查询语句

在MySQL中，可以使用explain语句和describe语句来分析查询语句

explain select 语句：explain select * from student;

describe select 语句：describe select * from student;

DESC SELECT *  FROM tb_bookinfo;

sql优化之explain:https://blog.csdn.net/qq_37436172/article/details/125593428

5.6版本需要使用explain extended才会出现filtered这个列 

2、索引对查询速度的影响

3、使用索引查询

 

三、优化数据库结构

1、将字段很多的表分解成多个表

假设，在学生表tb_student中有很多字段，其中extra字段中存储着学生的备注信息。有些 备注信息的内容特别多 。 但是，备注信息很少使用。

这样就可以分解出另外一个表（同时将tb_student表中的extra字段删除），将这个分解出来的表取名为tb_student_extra。表中存储两个字段，分别 为 S no 和 extra。

2、增加中间表

3、优化插入记录的速度

插入记录时，索引、唯一校验都会影响到插入记录的速度。而且一次插入多条记录和多次插入记录所消耗的时间是不一样的

1.禁用/重新开启索引的语句：alter table 表名 disable/enable keys;（disable禁用、enable开启）

2.禁用/重新开启唯一性检查语句如下：set unique_checks=1/0;（0禁用、1开启）

3.一个insert语句插入多条记录

        insert into s(sno,sname) values('001','小明'),('002','小红','003','小白');

4、分析表、检查表和优化表

1.分析表的主要作用是分析关键字的分布，语法如下：

        analyze table 表名1[,表名2...];

2.检查表的主要作用是检查表是否存在错误，语法如下：

        check table 表名1[,表名2...];

3.优化表的主要作用是消除删除或更新造成的空间浪费，语法如下：

        optimize table 表名1,[表名2...];

由于mysql采用innodb引擎，因此可用alter table 表名 engine='innodb';代替

例：alter table s engine='innodb'; 

查看效果：show table status from 数据库名 like '表名';

四、优化多表查询

在多表查询中，可用应用子查询来优化多表查询，即在seleci语句中嵌套其他select

select sc.sno,sc.cno,grade from sc where sc.sno=(select sno from s where sname='小明');

五、优化表设计

设计数据表应要考虑的优化因素：

1、设计数据表时应优先考虑使用特定字段长度，后考虑使用变长字段

将字段长度设置成其可能应用的最大范围可用充分的优化查询效率，该方法不适用于InnoDB存储引擎

2、使用optimize table命令处理用户经常操作的表

频繁地操作数据库中的特定表会导致磁盘碎片的增加，降低MySQL的效率

3、进行连接查询时，连接的列采用相同的数据类型和长度，可以达到查询优化的作用

六、习题

2、选择题

1）分析查询语句可以使用（  D  ）两个关键字。

A．EXPLAIN和SHOW                       B．EXPLAIN和SELECT

C．SHOW和DESCRIBE                     D．EXPLAIN和DESCRIBE

2）下列（  D  ）语句不正确。

A．SHOW STATUS LIKE 'Connections';

B．SHOW STATUS LIKE 'Com_select';

C．SHOW STATUS LIKE 'Com_insert';

D．SHOW STATUS LIKE 'Com_Uptime ';

3）下列（  D  ）语句可以查看SQL语句的执行时间。

A．SHOW STATUS LIKE ‘Connections’;     B．SHOW VARIABLES LIKE '%pro%';

C．SET profiling=1;                                                                 D．SHOW profiles;

4）下列（  C  ）语句不能实现分析查询语句。

A．DESCRIBE SELECT * FROM tb_bookinfo;              B．EXPLAIN SELECT *  FROM tb_bookinfo;

C．SHOW SELECT *  FROM tb_bookinfo;                   D．DESC SELECT *  FROM tb_bookinfo;

5）为了优化查询，在用户设计数据表时，应该考虑（  D  ）因素。

A．在设计数据表时应优先考虑使用特定字段长度，后考虑使用变长字段。

B．使用OPTIMIZE TABLE命令处理用户经常操作的表。

C．在查询过程中，用户可以使用连接，但是连接的列需要采用相同的数据类型和长度。

D．以上都是

6）下列（  D  ）项不能优化插入记录的速度。

A．禁用索引

B．禁用唯一性检查

C．一个INSERT语句插入多条记录

D．执行多个INSERT语句来插入多条记录

2、填空题

1）MySQL主要是通过_____ profiling _______参数标记profile工具是否开启的。

2）应用LIKE关键字优化索引查询时，如果匹配字符串中，第一个字符为____%_____时，索引不会被使用。

3）禁用索引的语句为_____alter table 表名 disable keys;______。

4）禁用唯一性检查的语句为______setunique_checks=0;______________。

5）MySQL中使用_______analyze table 表名;_________语句来分析表。

6）MySQL中使用_____optimize table 表名;________语句来优化表。

7）___check table 表名;_____语句能够检查InnoDB和MyISAM类型的表是否存在错误。