Mysql数据库优化专题

最新推荐文章于 2023-07-17 17:32:57 发布

知了灬

最新推荐文章于 2023-07-17 17:32:57 发布

阅读量243

点赞数

分类专栏： java 数据库 web开发

本文链接：https://blog.csdn.net/xf271838153/article/details/104581966

版权

java 同时被 3 个专栏收录

16 篇文章 0 订阅

订阅专栏

数据库

4 篇文章 0 订阅

订阅专栏

web开发

3 篇文章 0 订阅

订阅专栏

Mysql优化-version-1.0.0

心怀敬畏，砥砺前行

本文主要内容

找出执行效率低的SQL语句
示例讲解了insert语句、分页查询优化思路等
后面对比介绍了mysql的两大存储引擎

一、前言

在WEB应用开发的过程中，由于初期系统的数据量比较少，开发人人员在书写SQL语句时更加重视功能上的实现，而当系统上线后，随着业务生产数据量的不断增长，很多SQL语句开始暴露出性能问题，对生产的影响也越来越大，此时这些有问题的SQL语句就会成为系统的性能瓶颈。如何对这些SQL语句进行优化提升性能就成为了我们必须要解决的问题。
想要合理的优化这些查询效率低的SQL，我们首先要找到他们。

二、找出执行效率低的SQL语句

1、Mysql的慢查询日志

mysql的慢查询日志是msyql提供的一种日志记录，它用来记录在msyql中响应时间超过阀值的语句，比如具体运行时间超过long_query_time值的sql，则会被记录到慢查询日志中。

2、慢查询日志配置

2.1查看是否开启慢查询日志，1/on表示开启，0/off表示关闭。

show VARIABLES like ‘slow_query_log’;

2.2未使用索引的查询也被记录到慢查询日志中，on表示开启，off表示关闭(默认值)。

show VARIABLES like ‘log_queries_not_using_indexes’;

2.3 慢查询阈值（秒级），当查询时间大于设定的阈值时，记录日志。

show VARIABLES like ‘long_query_time’;

2.4慢查询日志存储路径

show variables like ‘slow_query_log_file’;

2.5 自定义慢查询日志配置

set global slow_query_log = on;

set global log_queries_not_using_indexes = on;

set global long_query_time = 0;

3、使用慢查询日志定位效率低的SQL语句

3.1 安装慢查询日志分析工具

1.下载安装工具
wget percona.com/get/pt-query-digest

2.授予用户执行权限
chmod u+x pt-query-digest

3.移动位置,目的是方便pt-query-digest的使用
mv /pt-query-digest /usr/bin/

4.安装与Perl相关的模块
yum install perl-DBI perl-Digest-MD5 perl-ExtUtils-CBuilder perl-ExtUtils-MakeMaker perl-ExtUtils-Embed perl-Time-HiRes -y

或依次执行下面命令
yum install perl-DBI -y
yum install perl-Digest-MD5 -y
yum install perl-ExtUtils-CBuilder perl-ExtUtils-MakeMaker -y
yum install perl-ExtUtils-Embed -y
yum install perl-Time-HiRes -y

3.2 使用pt-query-digest

1.分析慢查询日志中SQL的统计结果:

pt-query-digest /var/run/mysqld/mysqld-slow.log | more

# 70ms user time, 20ms system time, 20.12M rss, 165.41M vsz
# Current date: Sun Dec  1 21:20:22 2019
# Hostname: javaInit01
# Files: /var/run/mysqld/mysqld-slow.log
//总共有多少条查询
# Overall: 2 total, 1 unique, 0.00 QPS, 0.02x concurrency ________________
//查询日志记录的时间范围
# Time range: 2018-11-01 20:58:47 to 21:35:46
//属性                 总计     最小     最大     平均    95%     标准     中等
# Attribute          total     min     max     avg     95%  stddev  median
# ============     ======= ======= ======= ======= ======= ======= =======
//执行时间
# Exec time            39s     14s     24s     19s     24s      7s     19s
//锁占用时间
# Lock time          233us    69us   164us   116us   164us    67us   116us
//发送到客户端的行数
# Rows sent              6       1       5       3       5    2.83       3
//select语句扫描行数
# Rows examine      38.15M  19.07M  19.07M  19.07M  19.07M    2.83  19.07M
//查询的字符数
# Query size            84      42      42      42      42       0      42

/**注释：
Rank：所有语句的排名，默认按查询时间降序排列，通过--order-by指定
Query ID：语句的ID，（去掉空格和查询条件中的文本值，计算hash值）
Response：总的响应时间
time：该查询在本次分析中总的时间占比
calls：执行次数，即本次分析总共有多少条这种类型的查询语句
R/Call：平均每次执行的响应时间
V/M：方差均值比（Variance-to-mean），也就是常说的离差指数。
Item：查询对象
*/
# Profile
# Rank Query ID                           Response time  Calls R/Call  V/M
# ==== ================================== ============== ===== ======= ===
#    1 0x3992D6F8E9C2C994AC70C8DFD95C72CE 38.7578 100.0%     2 19.3789  2.68 SELECT card

/**注释：
ID：查询的ID号，和上面的Query ID对应
*/
# Query 1: 0.00 QPS, 0.02x concurrency, ID 0x3992D6F8E9C2C994AC70C8DFD95C72CE at byte 404
# This item is included in the report because it matches --limit.
# Scores: V/M = 2.68
# Time range: 2019-12-01 20:58:47 to 21:35:46
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count        100       2
# Exec time    100     39s     14s     24s     19s     24s      7s     19s
# Lock time    100   233us    69us   164us   116us   164us    67us   116us
# Rows sent    100       6       1       5       3       5    2.83       3
# Rows examine 100  38.15M  19.07M  19.07M  19.07M  19.07M    2.83  19.07M
# Query size   100      84      42      42      42      42       0      42
# String:
//数据库名
# Databases    test
//服务器地址IP
# Hosts        192.168.246.1
//各个用户执行的次数（占比)
# Users        root
//查询时间分布, 长短体现区间占比，本例中1s-10s之间查询数量是10s以上的两倍。
# Query_time distribution
#   1us
#  10us
# 100us
#   1ms
#  10ms
# 100ms
#    1s
#  10s+  ################################################################
//查询中涉及到的表
# Tables
#    SHOW TABLE STATUS FROM `test` LIKE 'card'\G
#    SHOW CREATE TABLE `test`.`card`\G
//SQL语句
# EXPLAIN /*!50100 PARTITIONS*/
select * from card order by rand() limit 5\G

2.分析select语句统计结果
pt-query-digest --filter ‘$event->{fingerprint} =~ m/^select/i’ /var/run/mysqld/mysqld-slow.log | more

3.查询指定用户(root)访问的查询
pt-query-digest --filter ‘($event->{user}) =~ m/^root/i’ /var/run/mysqld/mysqld-slow.log | more

4. 使用explain对单条SQL语句分析

在这里插入图片描述

select_type:
	表示查询的类型，常见的取值有:
		SIMPLE(简单查询，即不使用表连接或者子查询)
		PRIMARY(主查询，即最外层的查询)
		UNION(UNION中的第二个或者后面的查询语句)
		SUBQUERY(子查询中的第一个查询)
		
table：对应输出结果集的表

type：
	表示Mysql在表中找到所需行的方式，或者叫访问类型，常见访问类型有：
		all 表示全表扫描，Mysql遍历全表来找到匹配的行
		const 单表中最多有一个匹配行，查询起来很迅速，所以这个匹配行中的其他列的值可以被优化器在当前查询中当做常量来处理，比如根据主键或者唯一索引进行的查询。
		ref	表示使用非唯一索引扫描或唯一索引的前缀扫描。
		eq_ref 类似ref,与ref区别就是使用的索引是唯一索引。
		range 表示索引范围扫描，常见于<、 <=、 >、 >=、 between等操作符。
		index 表示Mysql遍历整个索引来查询匹配的行
		null Mysql不用访问表或者索引，直接就能得到结果。
	demo:
	  all:
		explain select * from emp where name='e93a1e2a-194d-11ea-9074-000c2916013c';
	  
	  const:
	  	explain select * from emp where emp_id=1;
      
      ref:  
        create index emp_name_idx on emp(name);
        explain select * from emp where name='e93a1e2a-194d-11ea-9074-000c2916013c';
         
      eq_ref:
    	 explain select e.name,d.dept_name from emp e left join dept d on e.dept_id=d.dept_id;
      
      range:
    	explain select * from emp where emp_id in (1,2,3);
      
      index:
		explain select * from emp order by emp_id desc;
	  
	  null:
		select now();
		 
possible_keys：表示查询时可能使用的索引

key：表示实际使用的索引

key_len：表中对应的索引最大可能长度,可以通过设置索引长度改变该值

rows：预计扫描行数

Extra：表示执行情况的说明和描述，包含不适合在其他列中显示但是对执行计划非常重要的额外信息

1、Using index：列数据仅仅使用了索引中的信息而没有读取实际的表
explain select emp_id from emp where emp_id=100;

2、Using where：MySQL服务器将在存储引擎检索行后，通过Where子句条件进行过滤
explain select * from emp where emp_id>=100;

3、Using filesort：文件排序
explain select * from emp  order by name;
4、using temporary: 表示用到了一张临时表，至于这临时表在内存里面，还是在磁盘里面都不明。
...

Explain的局限性:
EXPLAIN不会告诉你关于触发器、存储过程的信息或用户自定义函数对查询的影响情况
EXPLAIN不考虑各种Cache
EXPLAIN不能显示MySQL在执行查询时所作的优化工作，部分统计信息是估算的，并非精确值
EXPALIN只用于解释SELECT操作

三、常用SQL的语句优化

1.优化insert语句

如果是同一个客户端进行添加数据，应尽量使用多个值表的insert语句，这种方式将大大缩减客户端与服务器之间的建立连接和关闭连接的消耗。

insert into table_name values(...),(...),(...);

另外针对不同的引擎还有不同的优化策略，这里针对InnoDB给出方案:

插入数据时，影响插入速度的主要是索引、唯一性校验、一次插入的数据条数等

1、禁用索引
对于非空表，插入记录时，MySQL会根据表的索引对插入的记录建立索引。如果插入大量数据，建立索引会降低插入数据速度。
为了解决这个问题，可以在批量插入数据之前禁用索引，数据插入完成后再开启索引。
    【禁用索引的语句：
    ALTER TABLE table_name DISABLE KEYS
    开启索引语句：
    ALTER TABLE table_name ENABLE KEYS
    对于空表批量插入数据，则不需要进行操作，因为MyISAM引擎的表是在导入数据后才建立索引。】

2、禁用外键检查
插入数据之前执行禁止对外键的检查，数据插入完成后再恢复，可以提供插入速度。
禁用：SET foreign_key_checks = 0;
开启：SET foreign_key_checks = 1;

3、禁止自动提交
插入数据之前执行禁止事务的自动提交，数据插入完成后再恢复，可以提高插入速度。
禁用：SET autocommit = 0;
开启：SET autocommit = 1;

2.优化order by语句

首先我们了解一下mysql中的排序方式，先来查看emp表上的索引使用情况：

show index from emp\G

mysql中两种排序方式:

第一种是通过有序索引顺序扫描，然后直接返回数据，在explain的分析中显示为using index , 不需要额外的排序，效率较高。

第二种是通过返回数据进行排序，所有不是通过索引返回排序结果的都叫做Filesort排序。

案例

1.查询的字段，应该只包含此次查询使用的索引字段和主键，其余的非索引字段和索引字段作为查询字段则不会使用索引。

只查询用于排序的索引字段，可以利用索引排序:
explain select addr_id,email from t_user order by addr_id,email;

只查询用于排序的索引主键，可以利用索引排序:
explain select addr_id from t_user order by addr_id;

优化策略：尽量减少额外的排序，通过索引直接返回有序数据。where条件和order by使用相同的索引，并且order by的顺序和索引的顺序相同，并且order by的字段都是升序或者都是降序。
使用索引排序的情况：
select * from tab_name where col1=1 order by col1 desc,col2 desc;
select * from tab_name order by col1 desc,col2 desc;

停用索引排序的情况：
select * from tab_name order by col1 desc,col2 asc;//order by 中的字段混合使用asc & desc
select * from tab_name where col1=1 order by col2 desc; // 用于查询行的关键字与order by中的不相同
select * from tab_name order by col1,col2...;// 对不同的关键字使用 order by

3.优化嵌套查询

能使用join连接查询完成的，不使用子查询。因为mysql中的join查询不需要在内存中创建临时表来完成。

4.优化分页查询

尽量给出查询的大致范围

SELECT * FROM `account` ORDER BY card_id LIMIT 3000000 , 10
改写成
SELECT * FROM `account` WHERE card_id >= (SELECT card_id FROM `account` ORDER BY card_id LIMIT 3000000 , 1) LIMIT 10

**
1、先使用有索引的列进行排序，查询出最后一条的id
2、使用上一步查询到的id进行筛选，拿出指定页码的数据，这样做减少了结果集滚动的范围，可以很大程度上提高分页查询的效率。

四、数据库对象的优化

MySQL支持的数据类型非常多，选择正确的数据类型对于获得高性能至关重要。不管存储哪种类型的数据，遵循以下几个原则：
更小的通常更好
一般情况下，应该尽量选择使用可以正确存储数据的最小数据类型。例如：只需要存储-128~127，tinyint更好。更小的数据类型通常更快，因为它们占用更少的磁盘，内存和CPU缓存，并且处理时需要的CPU周期也更少。
简单就好
简单数据类型的操作通常需要更少的CPU周期。例如，整形比字符操作代价更低，因为字符集和校对规则（排序规则）使字符比整形更复杂。例如：使用date/time/datetime而不是字符串来存储日期和时间，使用整形存储IP地址。
尽量避免NULL
如果查询中包含可为NULL的列，对MySQL来说更难优化，因为可为NULL的列使得索引、索引统计和值比较都更复杂。可为NULL的列会使用更多的存储空间，在MySQL中也需要特殊处理。当可为NULL的列被索引时，每个索引记录需要一个额外的字节。如果计划在列上建索引，尽量避免设计为可为NULL的列。当然也有例外，例如InnoDB使用单独的位（bit）存储NULL值，所以对于稀疏数据有很好的空间效率。

五、Mysql的存储引擎

show engines; //查看数据库存储引擎支持

1.InnoDB引擎

InnoDB表是基于聚簇索引建立的，其索引结构和MySQL的其他存储引擎有很大区别。内部做了很多优化，包括从磁盘读取数据时采用的可预测性预读。

InnoDB特性：

1、InnoDB是事务型数据库的首选引擎，支持事务安全表（ACID）。

2、InnoDB支持行级锁。行级锁可以最大程度的支持并发，行级锁是由存储引擎层实现的。

3、InnoDB支持外键。

2.MyISAM引擎

在MySQL5.1版本之前，MyISAM是默认的存储引擎。MyISAM提供了大量的特性，包括全文检索、压缩、空间函数等，但MyISAM不支持事务和行级锁，而有一个缺陷就是崩溃后无法安全恢复。虽然MyISAM有这样的缺陷，但并非一无是处。对于只读的数据，或者表比较小，可以忍受修复操作，则依然可以继续使用MyISAM引擎。

MyISAM特性：

1、MyISAM不支持事务。

2、MyISAM支持全文索引（FULLTEXT）。

3、MyISAM只支持表级锁，用户在操作myisam表时，select，update，delete，insert语句都会给表自动加锁，如果加锁以后的表满足insert并发的情况下，可以在表的尾部插入新的数据。也可以通过lock table命令来锁表，这样操作主要是可以模仿事务，但是消耗非常大，一般只在实验演示中使用。

4、MyISAM引擎不支持外键。

附:测试数据

drop table `account`;
CREATE TABLE `account` (
   `card_id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'ID',
   `card_number` varchar(100) DEFAULT NULL COMMENT '卡号',
   PRIMARY KEY (`card_id`)
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8;
 
DROP PROCEDURE IF EXISTS proc1;
DELIMITER $$
SET AUTOCOMMIT = 0$$
CREATE  PROCEDURE proc1()
BEGIN
DECLARE v_cnt DECIMAL (10)  DEFAULT 0 ;
dd:LOOP
          INSERT  INTO account (card_number) VALUES (UUID());
                  COMMIT;
                    SET v_cnt = v_cnt+1 ;
                           IF  v_cnt = 10000000 THEN LEAVE dd;
                          END IF;
         END LOOP dd ;
END;$$
DELIMITER ;

call proc1;


select * from account order by rand() limit 1;   
select * from account order by card_id; 
select * from account where card_number like '%xxx%';

drop table `dept`;
CREATE TABLE `dept` (
   `dept_id` bigint(20) NOT NULL AUTO_INCREMENT ,
   `dept_name` varchar(100) DEFAULT NULL,
   PRIMARY KEY (`dept_id`)
 ) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8;

drop table `emp`;
CREATE TABLE `emp` (
   `emp_id` bigint(20) NOT NULL AUTO_INCREMENT ,
   `name` varchar(100) DEFAULT NULL,
   `age` tinyint,
   `dept_id` bigint(20) references dept(dept_id),
   PRIMARY KEY (`emp_id`)
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8;

insert into dept(dept_name) values('A');
insert into dept(dept_name) values('B');
insert into dept(dept_name) values('C');

DROP PROCEDURE IF EXISTS emp_proc;
DELIMITER $$
SET AUTOCOMMIT = 0$$
CREATE  PROCEDURE emp_proc()
BEGIN
DECLARE v_cnt DECIMAL (10)  DEFAULT 0 ;
dd:LOOP
          INSERT  INTO emp (name,age) VALUES (UUID(),18);
                  COMMIT;
                    SET v_cnt = v_cnt+1 ;
                           IF  v_cnt = 10000 THEN LEAVE dd;
                          END IF;
         END LOOP dd ;
END;$$
DELIMITER ;

call emp_proc();

update emp set dept_id=1 where emp_id<=5000;
update emp set dept_id=2 where emp_id>5000;

如有纰漏，望留言指正，将不胜感激。

知了灬

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
Mysql数据库优化专题

Mysql优化-version-1.0.0心怀敬畏，砥砺前行本文主要内容找出执行效率低的SQL语句示例讲解了insert语句、分页查询优化思路等后面对比介绍了mysql的两大存储引擎一、前言在WEB应用开发的过程中，由于初期系统的数据量比较少，开发人人员在书写SQL语句时更加重视功能上的实现，而当系统上线后，随着业务生产数据量的不断增长，很多SQL语句开始暴露出性能问题...
复制链接

扫一扫