mysql相关优化

于 2020-12-30 14:15:11 发布
阅读量93 收藏
点赞数
分类专栏： MySQL集合
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_45785469/article/details/111984142
版权

MySQL优化

1.索引sql语句

查看索引:

show index from 表名;

创建单例索引:

create index 索引名  on  表名(列1,列2,……);

删除索引:

drop index 索引名 on 表名

2.SQL优化

2.1explain分析执行计划

通过 explain或者 desc获取 MySQL如何执行 SELECT 语句的信息，包括在 SELECT 语句执行过程中表如何连接和连接的顺序。

字段含义
idselect查询的序列号，是一组数字，表示的是查询中执行select子句或者是操作表的顺序。
select_type表示 SELECT 的类型，常见的取值有 SIMPLE（简单表，即不使用表连接或者子查询）、PRIMARY（主查询，即外层的查询）、UNION（UNION 中的第二个或者后面的查询语句）、SUBQUERY（子查询中的第一个 SELECT）等
table输出结果集的表
type表示表的连接类型，性能由好到差的连接类型为( system —> const -----> eq_ref ------> ref -------> ref_or_null----> index_merge —> index_subquery -----> range -----> index ------> all )
possible_keys表示查询时，可能使用的索引
key表示实际使用的索引
key_len索引字段的长度
rows扫描行的数量
extra执行情况的说明和描述
2.1.1 explain 之 id

id 字段是 select查询的序列号，是一组数字，表示的是查询中执行select子句或者是操作表的顺序.

id 有相同，也有不同，同时存在。id相同的可以认为是一组，从上往下顺序执行；在所有的组中，id的值越大，优先级越高，越先执行。

2.2.2 explain 之 select_type

表示 SELECT 的类型，常见的取值，如下表所示：

select_type含义
SIMPLE简单的select查询，查询中不包含子查询或者UNION
PRIMARY查询中若包含任何复杂的子查询，最外层查询标记为该标识
SUBQUERY在SELECT 或 WHERE 列表中包含了子查询
DERIVED在FROM 列表中包含的子查询，被标记为 DERIVED（衍生） MYSQL会递归执行这些子查询，把结果放在临时表中
UNION若第二个SELECT出现在UNION之后，则标记为UNION ； 若UNION包含在FROM子句的子查询中，外层SELECT将被标记为 ： DERIVED
UNION RESULT从UNION表获取结果的SELECT
2.2.3 explain 之 table

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

2.2.4 explain 之 type

type 显示的是访问类型，是较为重要的一个指标，可取值为：

type含义
NULLMySQL不访问任何表，索引，直接返回结果
system表只有一行记录(等于系统表)，这是const类型的特例，一般不会出现
const表示通过索引一次就找到了，const 用于比较primary key 或者 unique 索引。因为只匹配一行数据，所以很快。如将主键置于where列表中，MySQL 就能将该查询转换为一个常亮。const于将 “主键” 或 “唯一” 索引的所有部分与常量值进行比较
eq_ref类似ref，区别在于使用的是唯一索引，使用主键的关联查询，关联查询出的记录只有一条。常见于主键或唯一索引扫描
ref非唯一性索引扫描，返回匹配某个单独值的所有行。本质上也是一种索引访问，返回所有匹配某个单独值的所有行（多个）
range只检索给定返回的行，使用一个索引来选择行。 where 之后出现 between ， < , > , in 等操作。
indexindex 与 ALL的区别为 index 类型只是遍历了索引树， 通常比ALL 快， ALL 是遍历数据文件。
all将遍历全表以找到匹配的行

结果值从最好到最坏以此是：

NULL > 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

一般来说， 我们需要保证查询至少达到 range 级别， 最好达到ref 。

2.2.6 explain 之 key
possible_keys : 显示可能应用在这张表的索引， 一个或多个。 
key ： 实际使用的索引， 如果为NULL， 则没有使用索引。
key_len : 表示索引中使用的字节数， 该值为索引字段最大可能长度，并非实际使用长度，在不损失精确性的前提下， 长度越短越好 。
2.2.7 explain 之 rows

扫描行的数量。

2.2…8 explain 之 extra

其他的额外的执行计划信息，在该列展示 。

extra含义
using filesort说明mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取， 称为 “文件排序”, 效率低。
using temporary使用了临时表保存中间结果，MySQL在对查询结果排序时使用临时表。常见于 order by 和 group by； 效率低
using index表示相应的select操作使用了覆盖索引， 避免访问表的数据行， 效率不错。

3.索引优化

1.全职匹配,对索引中所有列都指定具体值

2.最左前缀法则

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

3.范围查询右边的列，不能使用索引

4.不要在索引列上进行运算操作,否则索引会失效

5.查询的时候,字符串不加单引号,会造成索引失效

  • 在查询时,没有对字符串加单引号，MySQL的查询优化器，会自动的进行隐式转换，造成索引失效

6.尽量使用覆盖索引,避免使用select *

7.使用or分割的条件,如果or之前的条件中的列有索引，而后面的列没有索引，那么设涉及到的索引都不会使用到

8.以%开头的like模糊匹配，索引会失效

  • 如果仅仅时尾部进行模糊匹配，索引不会失效，如果是头部模糊匹配，索引会失效
  • 解决方案:通过覆盖所有来解决

9.如果mysql评估使用索引比全表慢,则不使用索引

10.is null，is not null，有时会索引失效

11.in走索引，not in不走索引

12.单列索引和复合索引(尽量使用符合索引)

4.SQL优化

4.1大量数据导入

  1. 主键顺序插入

​ 因为InnoDB类型的表是按照主键的顺序保存的，所以将导入的数据按照主键的顺序排列，可以有效的提高导入数据的效率。如果InnoDB表没有主键，那么系统会自动默认创建一个内部列作为主键，所以如果可以给表创建一个主键，将可以利用这点，来提高导入数据的效率。

  1. 关闭唯一性校验

    ​ 在导入数据前执行 SET UNIQUE_CHECKS=0，关闭唯一性校验，在导入结束后执行SET UNIQUE_CHECKS=1，恢复唯一性校验，可以提高导入的效率。

  2. 手动提交事务

    ​ 如果应用使用自动提交的方式，建议在导入前执行 SET AUTOCOMMIT=0，关闭自动提交，导入结束后再执行 SET AUTOCOMMIT=1，打开自动提交，也可以提高导入的效率。

4.2优化insert语句

  1. 如果需要同时对一张表插入很多行数据时，应该尽量使用多个值表的insert语句，这种方式将大大的缩减客户端与数据库之间的连接、关闭等消耗。使得效率比分开执行的单个insert语句快
  2. 在事务中进行数据插入。
  3. 数据有序插入

4.3order by语句优化

​ 两种排序方式:

  1. 通过对返回数据进行排序，也就是通常说的 filesort 排序，所有不是通过索引直接返回排序结果的排序都叫 FileSort 排序。
  2. 通过有序索引顺序扫描直接返回有序数据，这种情况即为 using index，不需要额外排序，操作效率高。

​ 尽量减少额外的排序，通过索引直接返回有序数据。

​ where 条件和Order by 使用相同的索引，并且Order By 的顺序和索引顺序相同， 并且Order by 的字段都是升序，或者都是降序。

​ 否则肯定需要额外的操作，这样就会出现FileSort。

4.4group by语句优化

​ 由于GROUP BY 实际上也同样会进行排序操作，而且与ORDER BY 相比，GROUP BY 主要只是多了排序之后的分组操作。当然，如果
在分组的时候还使用了其他的一些聚合函数，那么还需要一些聚合函数的计算。所以，在GROUP BY 的实现过程中，与 ORDER BY 一
样也可以利用到索引。如果查询包含 group by 但是用户想要避免排序结果的消耗， 则可以执行order by null 禁止排序。

select age,count(*) from emp group by age order by null;

4.5优化or条件

  1. UNION 语句的 type 值为 ref，OR 语句的 type 值为 range，可以看到这是一个很明显的差距
  2. UNION 语句的 ref 值为 const，OR 语句的 type 值为 null，const 表示是常量值引用，非常快
    这两项的差距就说明了 UNION 要优于 OR 。

4.6优化嵌套(子)查询

子查询是可以被更高效的连接（JOIN）替代。

连接(Join)查询之所以更有效率一些 ，是因为MySQL不需要在内存中创建临时表来完成这个逻辑上需要两个步骤的查询工作。

5.MYSQL常用查询

查询当前数据库支持的存储引擎:					  show engines;

查看Mysql数据库默认的存储引擎:                 show variables like '%storage_engine%'
weixin_45785469
关注
专栏目录
mysql使用多个索引_MySQL多索引查询选择
weixin_28683165的博客
01-18 4679
MySQL多索引查询选择MySQL选择索引-引入我们知道我们一个表里面可以有多个索引的，那么我们查询数据的时候不指定索引，MySQL就会帮我们自动选择。既然是MySQL程序帮我们自动选择的那么会不会有问题的呢？答案是会的，MySQL的优化器也有bug，有时候选择的索引并不是最优的。案例1假如一张表有10w的数据，有id主键和a，b普通索引,执行以下SQLselect * from t where ...
千金良方：MySQL性能优化金字塔法则.docx
09-13
根据优化后的执行计划，数据库服务器会访问相关的表和索引，以获取所需的数据。最后，数据库服务器会将结果返回给客户端。 MySQL性能优化金字塔法则为开发人员和数据库管理员提供了实用的指导和方法论，帮助他们更...
Mysql Explain 详解 [原创]
cixin1802的博客
08-06 794
一.语法 explain < table_name > 例如: explain select * from t3 where id=3952602; 二.explain输出解释 +----+---------...
mysql explain详解
acq的博客
01-22 2918
explian可以查看sql的执行效率，下面是各个字段的含义。 id select查询的序列号，包含一组数字，表示查询中执行select子句或操作表的顺序。 三种情况： id相同。执行顺序从上至下。 id不相同。如果是子查询，id的序列会递增，id的值越大优先级越高，越先被执行。 id相同又不相同。id如果相同，可以认为是一组，从上往下顺序执行；在所在组中，id值越大，优先级越高，越先执行。 ...
关于mysql中explain的那些事儿
东南风的专栏
12-29 622
有两种用法： 1.EXPLAIN tbl_name 2.EXPLAIN [EXTENDED] SELECT select_options 为了更好的说明它，我们需要建两张表，下面的语句用于创建一张测试用的订单表： CREATE TABLE `t_order` (  `order_id` int(10) unsigned NOT NULL AUTO_INCREMENT COMME
mysql的sql执行计划详解（非常有用）
热门推荐
Gavin's Blog
05-11 2万+
引言： 实际项目开发中，由于我们不知道实际查询的时候数据库里发生了什么事情，数据库软件是怎样扫描表、怎样使用索引的，因此，我们能感知到的就只有 sql语句运行的时间，在数据规模不大时，查询是瞬间的，因此，在写sql语句的时候就很少考虑到性能的问题。但是当数据规模增大，如千万、亿的时候，我们运 行同样的sql语句时却发现迟迟没有结果，这个时候才知道数据规模已经限制了我们查询的速度。所以，查询优
Mysql优化之Zabbix分区优化
09-09
MySQL优化是提升数据库性能的关键，尤其对于Zabbix这样的监控系统来说，其性能直接影响到监控数据的收集和告警的及时性。Zabbix的数据主要存储在`history`和`trends`两个表中，随着时间的推移，这两个表会变得非常...
MySQL性能优化和高可用架构实践.pptx
最新发布
10-04
"MySQL性能优化和高可用架构实践" 本书《MySQL性能优化和高可用架构实践》是一本详细介绍MySQL性能优化和高可用架构实践的书籍，旨在帮助读者提升MySQL数据库的性能和可靠性。本书的内容涵盖了查询优化的基本原则和...
MySQL数据库优化SQL篇PPT课件.pptx
10-07
MySQL数据库优化SQL篇PPT课件.pptx 本PPT课件主要讲述了MySQL数据库优化的重要知识点，特别是SQL优化方面的内容。从执行计划、SELECT语句、IN和EXIST语句、LIMIT语句、RAND函数、Order by、Group by、Distinct和...
Mysql Explain
02-28
Mysql Explain
[转]MySQL Explain详解
weixin_34417200的博客
10-09 1000
在日常工作中，我们会有时会开慢查询去记录一些执行时间比较久的SQL语句，找出这些SQL语句并不意味着完事了，些时我们常常用到explain这个命令来查看一个这些SQL语句的执行计划，查看该SQL语句有没有使用上了索引，有没有做全表扫描，这都可以通过explain命令来查看。所以我们深入了解MySQL的基于开销的优化器，还可以获得很多可能被优化器考虑到的访问策略的细节，以及当运行SQL语句时哪种策略...
Mysql explain
regrethh的博客
04-11 790
用explain来获取关于查询执行计划的信息。比如mysql中有慢查询语句，可以通过explian来返回其执行计划中的信息，还可以获得很多可能被优化器考虑到的访问策略的细节，以及当运行SQL语句时哪种策略预计会被优化器采用。 explain命令 EXPLAIN select * from address; explain 的列 执行结果 id select_type ta...
mysql-explain
peng0107的博客
10-22 652
explain显示了mysql如何使用索引来处理select语句以及连接表。可以帮助选择更好的索引和写出更优化的查询语句。 使用方法，在select语句前加上explain就可以了： 如：explain select surname,first_name form a,b where a.id=b.id 下面解释下explain执行计划qn包含信息： id:select查询的一个序列号，包含一组...
MySql explain
咸鱼的博客
08-24 181
mysql&amp;gt; explain select * from (select * from ( select * from t1 where id=2602) a) b; +----+-------------+------------+--------+-------------------+---------+---------+------+------+-------+ | id | s...
MySQL Explain详解
s1070的专栏
05-20 359
列名 描述 说明 相关链接 id 若没有子查询和联合查询，id则都是1。 Mysql会按照id从大到小的顺序执行query，在id相同的情况下，则从上到下执行。   select_type select类型。     table
Mysql explain解释
Ysh-uestc
10-05 179
https://blog.csdn.net/wuseyukui/article/details/71512793 https://segmentfault.com/a/1190000008131735 https://www.cnblogs.com/butterfly100/archive/2018/01/15/8287569.html
Mysql关键字Explain 性能优化神器
weixin_31351409的博客
08-26 123
Explain工具介绍 使用EXPLAIN关键字可以模拟优化器执行SQL语句，分析查询语句或是结构的性能瓶颈。在select语句之前增加explaion关键字，MySQL会在查询上设置一个标记，执行查询会返回执行计划的信息，而不是执行SQL。 Explaion分析示例 -- actor建表语句： CREATE TABLE `actor` ( `id` int(11) NOT NULL, `...
mysql之explain
王者之峰
05-15 2269
explain是提供一些信息关于增删查改(mysql 5.6之前只支持查)是如何执行的命令。 执行结果列下面说明下。 下面例子测试的表 CREATE TABLE `test_people` ( `people_id` bigint(20) NOT NULL AUTO_INCREMENT, `last_name` varchar(50) NOT NULL, `fist_name...
MySQL数据库优化详解
"MySQL数据库优化文档，涵盖了从...以上内容仅为基础知识概述，实际的MySQL优化过程需要根据具体的业务场景和数据库负载进行细致的分析与调整。理解并熟练运用这些技巧，将有助于提升MySQL数据库的整体性能和稳定性。
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值