MySql优化一篇就够了

NotRepent

已于 2022-05-14 16:22:43 修改

阅读量208

点赞数

分类专栏： Mysql优化文章标签：开发语言

于 2022-05-13 20:49:09 首次发布

本文链接：https://blog.csdn.net/NotRepent/article/details/124742262

版权

Mysql优化专栏收录该内容

1 篇文章 0 订阅

订阅专栏

1.存储引擎

1.1Mysql逻辑架构介绍

和其它数据库相比，MySQL有点与众不同，它的架构可以在多种不同场景中应用并发挥良好作用。主要体现在存储引擎的架构上，

插件式的存储引擎架构将查询处理和其它的系统任务以及数据的存储提取相分离。这种架构可以根据业务的需求和实际需要选择合适的存储引擎。

1.连接层

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

2.服务层

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

3.引擎层

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

4.存储层

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

1.2 查看命令

1.3 MyISAM和InnoDB存储引擎的区别

2.Join查询

2.1 SQL执行顺序(一般情况下)

2.2 Join图

3.索引与数据处理

3.1 是什么

1.可以理解为排好序的快速查找的数据结构,一般情况下默认使用的是B-Tree的数据结构主要目的是为了加快索引数据的效率

2.处理数据表以外还维护着一种数据结构 B-Tree，每个节点包含一个索引值和一个指向数据库记录的物理地址，运用二叉树算法检索到数据

3.2 详解

3.3 优势

提高检索效率，在查询大量数据时候降低数据库IO成本，通过对数据排序，降低数据库排序成本，降低CPU消耗

3.4 劣势

frm文件---------------------------->表示数据的表结构

MYD文件--------------------------->表示的是数据

MYI文件----------------------------->表示的是数据的索引

3.5 分类

4. SQL优化Explain

SQL优化执行器，查看SQL的性能瓶颈

4.1 怎么玩

4.2 各字段解释

4.2.1 ID

拿不准的话可以看括号，括号最里面的先执行

4.2.2 select_type

4.2.3 Tabel

显示这一行的数据是关于哪张表的

4.2.4 Type

4.2.5 possible_keys

4.2.6 key

4.2.7 key_len

表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度。在不损失精确性的情况下，长度越短越好

key_len显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出的

官网解释

4.2.7.1 数据类型长度

1）数值类型

2）日期和时间类型

3）字符串类型

计算方法估值表

4.2.7.2 案例演示

字符型

索引字段为char类型+不可为Null时

CREATE TABLE `t2` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` char(10) NOT NULL,
  `addr` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 
explain select * from t2 where name='atguigu';

索引字段为char类型+允许为Null时

CREATE TABLE `t1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` char(10) DEFAULT NULL,
  `addr` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 
explain select * from t1 where name='atguigu';

索引字段为varchar类型+不可为Null时

CREATE TABLE `t4` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(10) NOT NULL,
  `addr` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 
explain select * from t4 where name='atguigu';

变长字段需要额外的2个字节（VARCHAR值保存时只保存需要的字符数，另加一个字节来记录长度(如果列声明的长度超过255，则使用两个字节)，所以VARCAHR索引长度计算时候要加2）

索引字段为varchar类型+允许为Null时

CREATE TABLE `t3` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(10) DEFAULT NULL,
  `addr` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

explain select * from t3 where name='atguigu';

整数/浮点数/时间类型的索引长度

总结

变长字段需要额外的2个字节（VARCHAR值保存时只保存需要的字符数，另加一个字节来记录长度(如果列声明的长度超过255，则使用两个字节)，所以VARCAHR索引长度计算时候要加2），固定长度字段不需要额外的字节。

而NULL都需要1个字节的额外空间,所以索引字段最好不要为NULL，因为NULL让统计更加复杂并且需要额外的存储空间。

所以，复合索引有最左前缀的特性，如果复合索引能全部使用上，则是复合索引字段的索引长度之和，这也可以用来判定复合索引是否部分使用，还是全部使用。

4.2.8

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

4.2.9 Rows

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

4.3 Extra

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

4.3.1 Using filesort

说明mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。MySQL中无法利用索引完成的排序操作称为“文件排序”

4.3.2 Using temporary

使了用临时表保存中间结果,MySQL在对查询结果排序时使用临时表。常见于排序 order by 和分组查询 group by。

4.3.3 USING index

表示相应的select操作中使用了覆盖索引(Covering Index)，避免访问了表的数据行，效率不错！如果同时出现using where，表明索引被用来执行索引键值的查找;如果没有同时出现using where，表明索引用来读取数据而非执行查找动作。

一个锅正好扣一个合适盖

4.3.4 Using where

表明使用了where过滤。

4.3.5 using join buffer

使用了连接缓存。

4.3.6 impossible where

where子句的值总是false，不能用来获取任何元组

4.4 Case

第一行（执行顺序4）：id列为1，表示是union里的第一个select，select_type列的primary表示该查询为外层查询，table列被标记为<derived3>，表示查询结果来自一个衍生表，其中derived3中3代表该查询衍生自第三个select查询，即id为3的select。【select d1.name......】

第二行（执行顺序2）：id为3，是整个查询中第三个select的一部分。因查询包含在from中，所以为derived。【select id,name from t1 where other_column=''】

第三行（执行顺序3）：select列表中的子查询select_type为subquery，为整个查询中的第二个select。【select id from t3】

第四行（执行顺序1）：select_type为union，说明第四个select是union里的第二个select，最先执行【select name,id from t2】

第五行（执行顺序5）：代表从union的临时表中读取行的阶段，table列的<union1,4>表示用第一个和第四个select的结果进行union操作。【两个结果union操作】

5.索引失效

5.1建表

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',24,'dev',NOW());
SELECT * FROM staffs;
ALTER TABLE staffs ADD INDEX idx_staffs_nameAgePos(name, age, pos);

5.2 案例(索引失效)

5.2.1全值匹配我最爱

5.2.2 最佳左前缀法则

如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列。

5.2.3 不在索引列上做任何操作（计算、函数、(自动or手动)类型转换），会导致索引失效而转向全表扫描

5.2.4 存储引擎不能使用索引中范围条件右边的列

5.2.5 尽量使用覆盖索引(只访问索引的查询(索引列和查询列一致))，减少select *

5.2.6 mysql 在使用不等于(!= 或者<>)的时候无法使用索引会导致全表扫描

5.2.7 注意null/not null对索引的可能影响

 建议新建一个库，试试测试脚本
 
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());
ALTER TABLE staffs ADD INDEX idx_staffs_nameAgePos(NAME, age, pos);
ALTER TABLE staffs ADD INDEX idx_staffs_name(NAME);
EXPLAIN SELECT * FROM staffs WHERE NAME IS NULL;
EXPLAIN SELECT * FROM staffs WHERE NAME IS NOT NULL;

CREATE TABLE staffs2 (
  id INT PRIMARY KEY AUTO_INCREMENT,
  NAME VARCHAR (24),
  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 staffs2(NAME,age,pos,add_time) VALUES('z3',22,'manager',NOW());
ALTER TABLE staffs2 ADD INDEX idx_staffs2_nameAgePos(NAME, age, pos);
ALTER TABLE staffs2 ADD INDEX idx_staffs2_name(NAME);
 
EXPLAIN SELECT * FROM staffs2 WHERE NAME IS NULL;
EXPLAIN SELECT * FROM staffs2 WHERE NAME IS NOT NULL;

5.2.8 like以通配符开头('%abc...')mysql索引失效会变成全表扫描的操作

CREATE TABLE `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`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
 
#drop table tbl_user
 
INSERT INTO tbl_user(NAME,age,email) VALUES('1aa1',21,'b@163.com');
INSERT INTO tbl_user(NAME,age,email) VALUES('2aa2',222,'a@163.com');
INSERT INTO tbl_user(NAME,age,email) VALUES('3aa3',265,'c@163.com');
INSERT INTO tbl_user(NAME,age,email) VALUES('4aa4',21,'d@163.com');
INSERT INTO tbl_user(NAME,age,email) VALUES('aa',121,'e@163.com');
 
#before index
 
EXPLAIN SELECT NAME,age    FROM tbl_user WHERE NAME LIKE '%aa%';
 
EXPLAIN SELECT id    FROM tbl_user WHERE NAME LIKE '%aa%';
EXPLAIN SELECT NAME     FROM tbl_user WHERE NAME LIKE '%aa%';
EXPLAIN SELECT age   FROM tbl_user WHERE NAME LIKE '%aa%';
 
EXPLAIN SELECT id,NAME    FROM tbl_user WHERE NAME LIKE '%aa%';
EXPLAIN SELECT id,NAME,age FROM tbl_user WHERE NAME LIKE '%aa%';
EXPLAIN SELECT NAME,age FROM tbl_user WHERE NAME LIKE '%aa%';
 
 
 
EXPLAIN SELECT *     FROM tbl_user WHERE NAME LIKE '%aa%';
EXPLAIN SELECT id,NAME,age,email  FROM tbl_user WHERE NAME LIKE '%aa%';
 
 
 
#create index
CREATE INDEX idx_user_nameAge ON tbl_user(NAME,age);
 
#DROP INDEX idx_user_nameAge ON tbl_user
 
#after index
 
EXPLAIN SELECT * FROM tbl_user WHERE NAME =800 AND age = 33;

5.2.9 字符串不加单引号索引失效

虽然说不加‘单引号也能查询出数据，但是索引已经失效了，mysql底层其实为我们做了自动隐试转换 name ='917'

5.3.1 少用or,用它来连接时会索引失效

在开发中我们可能需要使用到or关键字，但又害怕索引失效我们可以采取 union all 进行来解决

例如：

select * from staff where name='July' union all select * from staff where name='z3'

5.3.2 小总结

6.优化口诀儿歌】

全职匹配我最爱，最左前缀要遵守；

带头大哥不能死，中间兄弟不能断；

索引列上少计算，范围之后全失效；

LIKE百分写最右，覆盖索引不写*；

不等空值还有OR，索引影响要注意；

VAR引号不可丢， SQL优化有诀窍。

7.批量数据脚本

7.1建表

 
# 新建库
create database bigData;
use bigData;
 
 
#1 建表dept
CREATE TABLE dept(  
id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,  
deptno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,   
dname VARCHAR(20) NOT NULL DEFAULT "",  
loc VARCHAR(13) NOT NULL DEFAULT ""  
) ENGINE=INNODB DEFAULT CHARSET=GBK ;  
 
 
#2 建表emp
CREATE TABLE emp  
(  
id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,  
empno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0, /*编号*/  
ename VARCHAR(20) NOT NULL DEFAULT "", /*名字*/  
job VARCHAR(9) NOT NULL DEFAULT "",/*工作*/  
mgr MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,/*上级编号*/  
hiredate DATE NOT NULL,/*入职时间*/  
sal DECIMAL(7,2) NOT NULL,/*薪水*/  
comm DECIMAL(7,2) NOT NULL,/*红利*/  
deptno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0 /*部门编号*/  
)ENGINE=INNODB DEFAULT CHARSET=GBK ;

7.2 设置参数log_bin_trust_function_creators

为什么要设置这个参数？

当开启二进制日志后(可以执行show variables like 'log_bin'查看是否开启)，
如果变量log_bin_trust_function_creators为OFF，那么创建或修改存储函数就会报
“ERROR 1418 (HY000): This function has none of DETERMINISTIC, NO SQL,
or READS SQL DATA in its declaration and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)”这样的错误

【解决方法】

show variables like 'log_bin_trust_function_creators';
set global log_bin_trust_function_creators=1;

# 这样添加了参数以后，如果mysqld重启，上述参数又会消失，永久方法：
windows下my.ini[mysqld]加上log_bin_trust_function_creators=1
linux下 /etc/my.cnf下my.cnf[mysqld]加上log_bin_trust_function_creators=1

7.3创建函数,保证每条数据都不同

随机产生字符串

 
 
DELIMITER $$
CREATE FUNCTION rand_string(n INT) RETURNS VARCHAR(255)
BEGIN
 DECLARE chars_str VARCHAR(100) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ';
 DECLARE return_str VARCHAR(255) DEFAULT '';
 DECLARE i INT DEFAULT 0;
 WHILE i < n DO
 SET return_str =CONCAT(return_str,SUBSTRING(chars_str,FLOOR(1+RAND()*52),1));
 SET i = i + 1;
 END WHILE;
 RETURN return_str;
END $$
 
#假如要删除
#drop function rand_string;

随机产生部门编号

 
#用于随机产生部门编号
DELIMITER $$
CREATE FUNCTION rand_num( ) 
RETURNS INT(5)  
BEGIN   
 DECLARE i INT DEFAULT 0;  
 SET i = FLOOR(100+RAND()*10);  
RETURN i;  
 END $$
 
 
#假如要删除
#drop function rand_num;

7.4 创建往emp表中插入数据的存储过程

创建往emp表中插入数据的存储过程

 
 
DELIMITER $$
CREATE PROCEDURE insert_emp(IN START INT(10),IN max_num INT(10))  
BEGIN  
DECLARE i INT DEFAULT 0;   
#set autocommit =0 把autocommit设置成0  
 SET autocommit = 0;    
 REPEAT  
 SET i = i + 1;  
 INSERT INTO emp (empno, ename ,job ,mgr ,hiredate ,sal ,comm ,deptno ) VALUES ((START+i) ,rand_string(6),'SALESMAN',0001,CURDATE(),2000,400,rand_num());  
 UNTIL i = max_num  
 END REPEAT;  
 COMMIT;  
 END $$
 
#删除
# DELIMITER ;
# drop PROCEDURE insert_emp;

创建往dept表中插入数据的存储过程

 
#执行存储过程，往dept表添加随机数据
DELIMITER $$
CREATE PROCEDURE insert_dept(IN START INT(10),IN max_num INT(10))  
BEGIN  
DECLARE i INT DEFAULT 0;   
 SET autocommit = 0;    
 REPEAT  
 SET i = i + 1;  
 INSERT INTO dept (deptno ,dname,loc ) VALUES ((START+i) ,rand_string(10),rand_string(8));  
 UNTIL i = max_num  
 END REPEAT;  
 COMMIT;  
 END $$ 
 
#删除
# DELIMITER ;
# drop PROCEDURE insert_dept;

7.5 调用存储过程

//dept

DELIMITER ;
CALL insert_dept(100,10); 

//emp

#执行存储过程，往emp表添加50万条数据
DELIMITER ;
CALL insert_emp(100001,500000);

NotRepent

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
MySql优化一篇就够了

1.存储引擎1.1Mysql逻辑架构介绍和其它数据库相比，MySQL有点与众不同，它的架构可以在多种不同场景中应用并发挥良好作用。主要体现在存储引擎的架构上，插件式的存储引擎架构将查询处理和其它的系统任务以及数据的存储提取相分离。这种架构可以根据业务的需求和实际需要选择合适的存储引擎。1.连接层最上层是一些客户端和连接服务，包含本地sock通信和大多数基于客户端/服务端工具实现的类似于tcp/ip的通信。主要完成一些类似于连接处理、授权认证、及相关的安全方案。在该层上引入了线程池.
复制链接

扫一扫

专栏目录