mysql优化

最新推荐文章于 2021-10-20 19:45:45 发布

早起晚睡

最新推荐文章于 2021-10-20 19:45:45 发布

阅读量140

点赞数 1

分类专栏： mysql 文章标签： mysql 数据库

本文链接：https://blog.csdn.net/bjgzxx/article/details/120591978

版权

mysql 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

1、准备数据表和数据

创建表直接照搬御世制人的例子

1.1、创建表

CREATE TABLE emp
(empno  MEDIUMINT UNSIGNED  NOT NULL  DEFAULT 0 COMMENT '编号',
ename VARCHAR(20) NOT NULL DEFAULT "" COMMENT '名字',
job VARCHAR(9) NOT NULL DEFAULT "" COMMENT '工作',
mgr MEDIUMINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '上级编号',
hiredate DATE NOT NULL COMMENT '入职时间',
sal DECIMAL(7,2)  NOT NULL COMMENT '薪水',
comm DECIMAL(7,2) NOT NULL COMMENT '红利',
deptno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '部门编号'
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

1.2、我们构建一个存储函数

这个存储函数会返回一个长度为参数n的随机字符串：（创建存储函数需要设置下my.ini在最下面加一条log_bin_trust_function_creators=1没有主备库的可以这样设置，有主备的自己查资料）

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 $$
  
delimiter ;

1.3、创建一个存储函数

该存储函数会返回一个随机int值

delimiter $$ 
create function rand_num( )
returns int(5)
begin 
declare i int default 0;
set i = floor(10+rand()*500);
return i; 
end $$
delimiter ;

1.4、创建一个存储过程

该存储过程包含一个参数，该参数表示插入数据表emp的数据条数

delimiter $$
create procedure insert_emp(in max_num int(10))
begin
declare i int default 0;
set autocommit = 0; 
repeat
set i = i + 1;
insert into emp values (i ,rand_string(6),'SALESMAN',0001,curdate(),2000,400,rand_num());
until i = max_num
end repeat;
commit;
end $$
delimiter;

最后，我们调用创建的存储过程，对emp表插入100w条数据：

call insert_emp(1000000);

执行1000000条数据需要大概6分钟，如果1000w就是一小时所以就测试100w条数据。

这里强调下

存储过程中是都插入完成才提交所以会出现事务中断一条数据都没有，可以每条语句都提交一次把commit放在循环里面。

2、查看慢查询

2.1、简单的查询

select * from emp

执行时间是1.876秒

设置

slow-query-log=1 slow_query_log_file="SC-201903201647-slow.log" long_query_time=1

slow_query_log_file是日志文件可以自己定义我这里就用系统带的，long_query_time是超出多少时间开始记录我这里设置的是1秒钟超过一秒就算慢查询。

然后看日志文件如下

Time: 2021-09-18T01:35:22.315240Z

User@Host: root[root] @ localhost [::1] Id: 8

Query_time: 1.861003 Lock_time: 0.000085 Rows_sent: 1000000 Rows_examined: 1000000

SET timestamp=1631928922; select * from emp;

下面是查询慢查询次数

show status like 'slow_queries';

Slow_queries 2

可以看出是两次，第一次是调用call insert_emp(1000000);的时候产生的，第二次是查询产生的。

2.2、其他查询

用or查询需要索引的条数

select * from emp
where empno=1592
or empno=1593

# Time: 2021-09-18T01:58:36.460969Z
# User@Host: root[root] @ localhost [::1]  Id:     8
# Query_time: 1.473280  Lock_time: 0.000107 Rows_sent: 2  Rows_examined: 1000000
SET timestamp=1631930316;
select * from emp
where empno=1592
or empno=1593;

返回两行搜索了100w条数据

select * from emp
where empno=1592
UNION all
select * from emp
where empno=1593

执行时间: 2.554s

# Time: 2021-09-18T02:02:34.784947Z
# User@Host: root[root] @ localhost [::1]  Id:     8
# Query_time: 2.554436  Lock_time: 0.000307 Rows_sent: 2  Rows_examined: 2000000
SET timestamp=1631930554;
select * from emp
where empno=1592
UNION all
select * from emp
where empno=1593;

结果都是一样都返回来所要的结果但是检索的数据是不同的执行的时间是不同的。第一个执行了1.54秒检索了100w条数据，第二执行了2.554秒检索了200w条数据，这些都是没有引入索引和主键的情况下的查询。然后往下看：

3、Explain执行计划

3.1、explain命令的使用

explain后面只需要跟着sql语句有OK了如下

explain select * from emp
where empno=1592
or empno=1593

然后再看union查询

explain select * from emp
where empno=1592
UNION all
select * from emp
where empno=1593

可以看出估计的行数是第一个的2倍（rows）。

1、id：SELECT识别符。这是SELECT的查询序列号；

2、select_type：查询类型，主要有PRIMARY(子查询中最外层查询）、SUBQUERY（子查询内层第一个SELECT）、UNION（UNION语句中第二个SELECT开始后面所有SELECT）、SIMPLE（除了子查询或者union之外的其他查询）；

3、table：所访问的数据库表明；

4、type：对表的访问方式，包括以下类型all(全表扫描)，index（全索引扫描），rang（索引范围扫描），ref（join语句中被驱动表索引引用查询），eq_ref（通过主键或唯一索引访问，最多只会有一条结果），const（读常量，只需读一次），system（系统表。表中只有一条数据），null（速度最快）。

5、possible_keys：查询可能使用到的索引；

6、key：最后选用的索引；

7、key_len：使用索引的最大长度；

8、ref：列出某个表的某个字段过滤；

9、rows：估算出的结果行数；

10、extra：查询细节信息，可能是以下值：distinct、using filesort（order by操作）、using index（所查数据只需要在index中即可获取）、using temporary（使用临时表）、using where（如果包含where，且不是仅通过索引即可获取内容，就会包含此信息）。

访问类型, 显示查询使用了何种类型, 从最好到最差依次是 :

system>const>eq_ref>ref>range>index>ALL

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

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

const : 表示通过索引1次就找到了, const用于比较primary key或者unique索引。因为只匹配一行数据, 索引很快, 如将主键置于where列表中, MySQL就能将该查询转换为一个常量 eq_ref : 唯一性索引扫描, 对于每个索引键, 表中只有一条记录与之匹配。常见于主键或唯一索引扫描

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

range : 只检索给定范围的行, 使用一个索引来选择行。key列显示使用了哪个索引, 一般就是在where语句中出现了between, < ,> ,in等的查询。这种范围索引扫描比全表扫描要好, 因为它只需要开始于索引的某一点, 而结束于另一点, 不用扫描全部索引。

ndex : Full Index Scan, index与ALL的区别为index类型只遍历索引树。这通常比ALL快, 因为索引文件通常比数据文件小。(也就是说虽然ALL和Index都是读全表, 但index是从索引中读取的, 而all是从硬盘中读取的)

3.2、添加索引对比

这里就设置主键为例因为主键是索引的一种 -----根据数据库的功能，可以在数据库设计器中创建三种索引：唯一索引、主键索引和聚集索引。而，主键只是其中的一种。

ALTER TABLE `emp` ADD PRIMARY KEY ( empno )

再次执行

explain select * from emp
where empno=1592
UNION all
select * from emp
where empno=1593

得到运行结果如下，因为where中是以主键为条件的所以type的类型就是const，通过索引一次就找到了不需要全表扫描了大大提高了效率。

1   PRIMARY emp     const   PRIMARY PRIMARY 3   const   1   100.00  
2   UNION   emp     const   PRIMARY PRIMARY 3   const   1   100.00

rows 100只是一个估算值不精确。

这里用了union all 和 or比较下面就是or的语句对应的

explain select * from emp
where empno=1592
or empno=1593

结果是如下：

1   SIMPLE  emp     range   PRIMARY PRIMARY 3       2   100.00  Using where

其中type是 range

理论来说union的比 or的反应快，但是查询速度都是几毫秒级的没有区别。

再来看看in

explain select * from emp
where empno in (1592,1593)

结果是：

1   SIMPLE  emp     range   PRIMARY PRIMARY 3       2   100.00  Using where

type是 range

和“or”和一样的就我的表来说没有速度的差别。

通过有索引和没有索引差别特别的大，有没有索引用union是两遍的全表查询速度会比用“or”慢很多可以说时间就是or的二倍，但是到了添加索引为什么就快了呢，是因为可以说直接定位了，不用再全表检索了。

对于索引列来最好使用union all，因复杂的查询【包含运算等】将使or、in放弃索引而全表扫描，除非你能确定or、in会使用索引。

对于只有非索引字段来说你就老老实实的用or 或者in，因为非索引字段本来要全表扫描而union all 只成倍增加表扫描的次数。

4、索引

4.1、列独立

如果需要某个字段上使用索引，则需要在字段参与的表达中，保证字段独立在一侧。

select * from emp where empno=123456;
select * from emp where empno+1=123456;
select * from emp where empno=123456+1;
explain select * from emp where empno=123456;
explain select * from emp where empno+1=123456;
explain select * from emp where empno=123456+1;

执行后如下：

explain select * from emp where empno=123456;
1   SIMPLE  emp     const   PRIMARY PRIMARY 3   const   1   100.00  
explain select * from emp where empno+1=123456;
1   SIMPLE  emp     ALL                 996072  100.00  Using where
explain select * from emp where empno=123456+1;
1   SIMPLE  emp     const   PRIMARY PRIMARY 3   const   1   100.00

可以看出 empno+1=123456字段不独立索引也就不起作用了。

4.2、左原则

Like：匹配模式必须要左边确定不能以通配符开头。

--“增加ename索引”
alter table `emp` add  INDEX (ename);

select * from emp where ename like 'abc%';
select * from emp where ename like '%abc';
select * from emp where ename like '_abc%';

执行完效果

explain select * from emp where ename like 'abc%';
1   SIMPLE  emp     range   ename   ename   62      46  100.00  Using index condition
explain select * from emp where ename like '%abc';
1   SIMPLE  emp     ALL                 996072  11.11   Using where
explain select * from emp where ename like '_abc%';
1   SIMPLE  emp     ALL                 996072  11.11   Using where

可以看到

第一个索引起到了作用，第二第三个没有起到索引的作用。

4.3、Mysql智能索引

增加deptno索引

alter table `emp` add  INDEX (deptno);

select * from emp where deptno>500;
select * from emp where deptno>100;

然后咱们执行

explain select * from emp where deptno>500;
1   SIMPLE  emp     range   deptno  deptno  3       32906   100.00  Using index condition
explain select * from emp where deptno>100;
1   SIMPLE  emp     ALL deptno              996072  50.00   Using where

deptno已经加上索引了但是在查询deptno>500的时候使用了索引但是在deptno>100的时候就全表检索了

# Time: 2021-09-18T09:12:07.499494Z
# User@Host: root[root] @ localhost [::1]  Id:     8
# Query_time: 10.783283  Lock_time: 0.000107 Rows_sent: 17785  Rows_examined: 17785
SET timestamp=1631956327;
select * from emp where deptno>500;

# Time: 2021-09-18T09:14:57.339698Z
# User@Host: root[root] @ localhost [::1]  Id:     8
# Query_time: 2.000405  Lock_time: 0.000077 Rows_sent: 817842  Rows_examined: 1000000
SET timestamp=1631956497;
select * from emp where deptno>100;

索引起作用的时候查询是10.78全表检索的执行时间是2秒，mysql智能判断是否需要索引，弃用索引的主要原因：

查询即使使用索引，会导致出现大量的随机IO，相对于从数据记录的第一条遍历到最后一条的顺序IO开销，还要大。