mysql优化

最新推荐文章于 2025-02-26 11:28:51 发布
最新推荐文章于 2025-02-26 11:28:51 发布
阅读量371 收藏
点赞数
文章标签： java mysql windows
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.youkuaiyun.com/a201206440/article/details/115870471
版权

mysql优化专题

说明

【本博客用到的数据库版本是mysql8，所有的表述未做特殊说明情况下均是对mysql8版本的描述，用到的数据是mysql提供的示意数据库：salika数据库，获取方式下载链接： https://blog.youkuaiyun.com/a201206440/article/details/115331113 】

mysql说明文档的官方链接https://dev.mysql.com/doc/refman/8.0/en/

一、数据库优化的必要性

​ 数据库的优化可以大大提高用户访问时数据的时效性，从而提高用户的使用体验。

  1. 避免用户使用时在数据库层面造成用户访问错误，例如：因为数据量大、数据库连接池满了或其他原因造成的数据库【慢查询】，导致了数据库连接超时造成用户页面5xx错误；数据库阻塞、锁表等导致用户数据无法提交等；
  2. 增加数据库的稳定性，防止数据库层面的资源消耗过大导致系统问题，例如：系统随着使用越久造成数据越来越臃肿，在访问时会造成数据库的【低效】查询，导致系统的资源消耗过大，严重的造成系统卡死崩溃等；
  3. 万变不离其宗：就是为了提高系统运行速率，优化用户体验，包括上边的1和2都是在为优化用户体验做准备。

二、mysql数据库的优化概述

1、优化层面
序号优化层面成本排名（1:成本最大）效果排名（1:效果最大）主要涉及人员
1商业需求提出61产品经理、项目经理
2系统架构设计52项目经理、系统架构师
3sql及索引设计43开发人员
4数据库表结构设计34开发人员
5系统配置25DBA、运维
6硬件16DBA、运维

​ 表中展示共有6个层面，不难看出这六个层面基本上概括了一个项目的提出到上线的全过程，也就是说在整个项目阶段都可以对数据库做优化，优化层面：商业需求提出、系统架构设计、sql及索引设计、数据库表结构设计、系统配置、硬件，这几个层面的成本越来越高但是效果越来越低，不难理解，浪费一张设计图和报废一座楼肯定是浪费一张设计图成本更低。对于程序员来说，我们涉及到的层面主要是sql及索引设计和数据库表结构设计，当然我们也会涉及到商业需求、架构涉及以及系统配置，当然在这些方面程序员的建议并不重要，你懂得，嘿嘿。

  1. 商业需求提出：不合理、无用、投入产出比不合理的商业需求会造成系统的臃肿、运行效率低、用户体验差，同时会造成数据库系统的压力增加，将其消除掉是对数据库以及整个系统的最根本的优化。
  2. 系统架构设计：在数据库架构设计方面，需要考虑哪些数据不适用于存放在数据库中，例如：二进制的多媒体数据（音频，视频）；流水队列的数据（数据更新快且不会insert进表中的数据）；超大的文件等，这些如果存放入数据库中会严重消耗i/o，同时严重增加cpu资源消耗，也会产生大量的日志。解决方案：音频、视频、超大文本等存放在服务器磁盘上【流行的存储方式】，流水队列采用消息队列处理（ribbitMq等）。
  3. sql及索引优化：这是程序员能涉及到并且话语权很重的一个层面，这个层面的主要目的是通过sql的编写和索引的优化来降低查询的中间结果集，从而降低物理IO，也就是降低对磁盘的读取次数。通常会先构想出大概涉及到的查询内容以及查询条件，然后据此构想索引，有了以上构想，才能进行表结构的初步设计。
  4. 数据库表结构设计：这也是程序员能涉及到并且话语权很重的一个层面，这个层面主要目的是将【sql及索引】层面的构想落实下来，同时通过合表、分表等操作让查询的物理IO降到最低。设计尽量遵循数据库表设计范式，尽量大于等于第三范式【数据库表设计范式】，设计表结构时也需要考虑系统类型，OLTP和OLAP系统的设计表结构不同【OLAP和OLTP基本概念】。
  5. 系统配置：大多数的系统会运行在Linux系统之上，当然也有其他系统，但是需要优化：tcp连接数限制，打开文件数量限制，安全性限制等
  6. 硬件层面：理论上是硬件无限扩则系统无限跑，当然硬件使用公司不可能无限制，所以需要考虑：数据库主机的IO性能（最优先考虑），主机的CPU处理能力，主机的网卡等性能

以上这些，对数据库的优化都很重要，其中对3、4的要求最苛刻，因为3和4理论上是在后期维护中成本最低的优化层面（不排除特殊情况）。

2、优化思路
  • 优化之前判断哪些sql需要优化，优化sql目标是实现高并发低消耗
  • 判断优化目标的性能瓶颈，影像性能一般考虑：cpu占用量大【sql运算体量太大例如分组排序复杂】；IO频繁、时间长【是否正确使用索引】；网络带宽太小等。
  • 明确优化程度【将sql优化到什么程度可以满足后续的业务要求，制定合理的优化级别，最终目的是缩短查询时间】
  • 优化之前先看一下explain执行计划
  • 永远用小的结果集驱动大的结果集，因为前边的结果集全表扫描的概率恨到即：type=ALL
  • 排序操作尽可能在索引中完成
  • 不要用select *
  • 将过滤条件调成最优
  • 尽可能避免复杂的join和子查询【join会造成锁表】
  • order by，group by，distinct语句会造成表的排序，尽量谨慎使用这几个参数
  • 合理设计并利用索引【增删改和查询之间的比例要考虑，一般高频查询，低频增删改需要做索引】, 索引虽然会提高查询效率，但是会降低更新表的效率。比如每次对表进行增删改操作，MySQL不仅要保存数据，还有保存或者更新对应的索引文件。

三、explain关键字在sql优化中的作用及详细使用

【本博客用到的数据库版本是mysql8，用到的数据是mysql提供的示意数据库：salika数据库，获取方式下载链接： https://blog.youkuaiyun.com/a201206440/article/details/115331113 】

*简介

​ 根据表，列，索引的详细信息以及WHERE子句中的条件，MySQL优化器会考虑多种技术来有效执行SQL查询中涉及的查找。可以在不读取所有行的情况下执行对巨大表的查询。可以在不比较行的每个组合的情况下执行涉及多个表的联接。优化器选择执行最有效查询的一组操作称为“查询执行计划”，也称为 EXPLAIN计划。认识到 EXPLAIN 表示查询优化的计划，并且如果发现一些低效的操作，则可以通过SQL语法和索引技术来改进计划。

1、explain关键字介绍

​ 对于一个程序员来说，mysql8的数据库优化无论如何是绕不开explain关键字的，explain主要是为了查看所写sql语句的执行计划，在8版本中 EXPLAIN语句提供了有关MySQL如何执行语句的信息，explain与select、delete、insert、replace和update语句一起使用。从而了解sql在mysql引擎中的执行方式，然后分析查询语句或者目前表结构的性能瓶颈，是否有优化空间，提高sql效率。通过explain获取到的执行计划可以明确得到：

  1. 所涉及到的表的读取顺序；
  2. 数据读取操作的操作类型；
  3. 那些索引可以使用；
  4. 实际使用的索引有哪些；
  5. 表之间的引用关系
  6. 每张表有多少行数据被查询
  7. 实际有多少数据返回给了业务
  8. …

2、explain用法以及关键字解读

​ explain关键字使用及其简单：explain+sql语句，例如：explain select * from 表名；

​ 查看explain的关键字，首先在salika数据库中执行一下语句：

mysql> explain select * from film;

会得到如下内容：其中红框行就是其关键字行

列JSON名称意义
idselect_id该SELECT标识符
select_typeNone该SELECT类型
tabletable_name输出行表
partitionspartitions匹配的分区
typeaccess_type联接类型
possible_keyspossible_keys可能的索引选择
keykey实际选择的索引
key_lenkey_length所选键的长度
refref列与索引的比较
rowsrows估计要检查的行
filteredfiltered按表条件过滤的行百分比
ExtraNone附加信息

各列的解释：

1.id:

执行先后的序号列，数据不一定是连续值，数值越大越先执行，如果数值相同则从上向下执行。例如：下图一中sql执行顺序【544311对应到表就是：film6–film4–film5–film3–film1–film2】，如果id值为NULL，则说明其是一个结果集，不需要用它执行，此时在【table列】会显示<unionM,N>,例如图二

图一：

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Mc4bOD5M-1618826106333)(D:\0AWQY20191031\private\面试\mysql优化.assets\1617167874738.png)]

图二：

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-fd4LPy3E-1618826106348)(D:\0AWQY20191031\private\面试\mysql优化.assets\1617112591768.png)]

2.select_type：

该sql语句的查询类型，有以下表格的类型

价值JSON名称意义
SIMPLENone简单SELECT（不使用 UNION或子查询）
PRIMARYNone最外层 SELECT
UNIONNoneUNION语句中第二个及以后的所有SELECT
DEPENDENT UNIONdependent（true）依赖外部的select查询，第二个及以后的所有UNION链接的SELECT
UNION RESULTunion_resultUNION的结果集，此时id为NULL。
SUBQUERYNone子查询中的第一个SELECT
DEPENDENT SUBQUERYdependent（true）依赖外部select的查询中（外部子查询）的第一个SELECT
DERIVEDNone派生表
DEPENDENT DERIVEDdependent（true）派生表，依赖于另一个表
MATERIALIZEDmaterialized_from_subquery实体化子查询？？
UNCACHEABLE SUBQUERYcacheable（false）子查询，其结果无法缓存，必须针对外部查询的每一行重新进行评估
UNCACHEABLE UNIONcacheable（false）UNION 属于不可缓存子查询的中的UNION语句中第二个及以后的所有SELECT（请参阅参考资料 UNCACHEABLE SUBQUERY）
注释：依赖外部的查询？？实体化子查询？？
  • Simple：简单的查询，不适用union或者子查询等；

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-sLucypFY-1618826106355)(D:\0AWQY20191031\private\面试\mysql优化.assets\1617159939648.png)]

  • Primary：在存在子查询的情况下所有平级的最外层的select,如下表的红框两个select是平级的均为最外层的select，绿色的是一个子查询；或者在Union语句中的第一个select。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-fpFp08zK-1618826106358)(D:\0AWQY20191031\private\面试\mysql优化.assets\1617160336628.png)]

  • Union：联合查询中，除了第一个select其余的UNION连接的select均为UNION类型

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-adkQ5512-1618826106360)(D:\0AWQY20191031\private\面试\mysql优化.assets\1617160667493.png)]

  • Dependent Union：依赖外部的查询中第二个及以后的所有UNION链接的SELECT，，如下图：红框部分是一个fi表依赖的外部查询，外部查询中有Union连接，此时union的链接表中，第一个表fa是【dependent subquery】，第二个表ac是【dependent union】

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-YQ6iWBlN-1618826106364)(D:\0AWQY20191031\private\面试\mysql优化.assets\1617162668676.png)]

  • UNION RESULT：Union连接的所有表运算过程中出现的结果集，此时id是NULL，table列显示<union M，N,O>，如上表所示
  • SubQuery：子查询中的第一个select；如下图的红框中是子查询，第一个select对应表actor是subquery

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-H0QbNNS9-1618826106367)(D:\0AWQY20191031\private\面试\mysql优化.assets\1617167542664.png)]

  • Dependent SubQuery：依赖外部的查询中（外部子查询）中的第一个SELECT，如下图中的红框中的actor表

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-u7U54BxN-1618826106370)(D:\0AWQY20191031\private\面试\mysql优化.assets\1617170009127.png)]

  • Derived：派生表， 子查询–>在From后where前的子查询

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-NSLHAXlw-1618826106373)(D:\0AWQY20191031\private\面试\mysql优化.assets\1617172184201.png)]

  • Dependent Derived：依赖于外部表的派生表

  • MateRialized：实例化子查询

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-N4Etz5O7-1618826106376)(D:\0AWQY20191031\private\面试\mysql优化.assets\1617696966757.png)]

  • Unchacheable Subquery：

  • Unchacheable Union：

3.table列：

是该行引用的数据表的名称，也可以是以下值：

  • <unionM,N>:指该行引用的是ID为M和N的行所引用的表的并集；
  • :指该行引用的是ID为N的行所引用的表的派生表的结果；
  • :指该行引用的是ID为N的行所引用的表的实例化子查询

4.partitions：

查询与记录相匹配的分区，如果没有分区则为NULL；

5.type：

数据库表之间的join类型，即描述table间的join关系，是重点优化的方向，type类型从优到劣排序是：

type类型优化排序

system>const>eq_ref>ref>fulltext>ref_or_null>index_merge>unique_subquery>index_subquery>range>index>all

-system

代表所查询的表仅有一行数据，可以理解为是const类型的一个特殊类型；

只能用于MyIsam和Memory引擎（system是最高的），如果是InnoDB引擎则显示All或者Index；

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-HtVE71ym-1618826106379)(D:\0AWQY20191031\private\面试\mysql优化.assets\1617184070159.png)]

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-dlplV5PI-1618826106385)(D:\0AWQY20191031\private\面试\mysql优化.assets\1617183756987.png)]

-const

代表查询出的数据仅有一个匹配行一般情况下是指where后跟的条件有且仅有一个索引并且索引正确使用同时查询出来的数据仅有一条的情况，

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-1J8BN7j2-1618826106418)(D:\0AWQY20191031\private\面试\mysql优化.assets\1617184736362.png)]

-eq_ref

在join查询中使用PRIMARY KEY 或者 UNIQUE NOT NULL索引关联

代表：发生在有组合表（需要连接多个表），并且两表组合的条件涉及的两表字段均有唯一索引（包括主键），且组合起来后前表的每一行数据在后表中均只有一个对应行，且不为not null。唯一主键和索引是组合主键或者索引时，需要所有的组合主键和索引都在条件中使用到。【另一种描述：在组合表（多个表连接）中，对应前表的每一个行在此表中仅仅只有一个行被查询出来且不为NULL，并且条件中涉及到的单一索引或者组合索引中的所有字段均使用，且其索引必须是主键或者唯一不为空索引。】

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-XzTnDwNi-1618826106422)(D:\0AWQY20191031\private\面试\mysql优化.assets\1617367379358.png)]

-ref

与eq_ref不同，ref不需要苛刻的要求是主键或者唯一索引等条件，仅满足正确使用索引并且使用【=】检索条件就可能出现（为什么是可能出现呢，因为这个范围包含了上边system，const等条件），【当正确使用索引且使用’=N’，但是索引列的为N值占的比重比较大（自测试发现基本上比重在80%以上）时，是ALL】[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-TSlPBKJL-1618826106426)(D:\0AWQY20191031\private\面试\mysql优化.assets\1617692303902.png)]

-fulltext

全文索引，使用在MyISAM引擎的表中，使用基本语法：

select * from tableName where match（列明） against（‘匹配内容例如：A，中国等字符’）

-ref_or_null

与ref类似，只不过ref_or_null支持查询null值，并且在查询时需要增加上null的条件会出现

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-EixRlrdT-1618826106429)(D:\0AWQY20191031\private\面试\mysql优化.assets\1617694577449.png)]

-index_merge

混合使用索引，key会展示使用的索引列表，key_len会展示key所显示的索引列表中最长的索引

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-3fwnEife-1618826106434)(D:\0AWQY20191031\private\面试\mysql优化.assets\1617695362278.png)]

-unique_subquery

使用在where后的子查询，子查询结果不重复, 在子查询中使用 eq_ref

-index_subquery

使用在where后的子查询，子查询结果可以重复, 在子查询中使用 ref

-range

正确使用索引，并且采用范围查询的方式例如： =, <>, >, >=, <, <=, IS NULL, <=>, BETWEEN, LIKE, IN()

-index

索引全表扫描，把索引从头到尾扫一遍，常见于使用索引列就可以处理不需要读取数据文件的查询、可以使用索引排序或者分组查询

-all

对表进行全表扫描

6.possible_keys

可能使用的索引的集合，如下图可能使用的索引是：idx_title和idx_fu_title

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-MvpRR8RO-1618826106438)(D:\0AWQY20191031\private\面试\mysql优化.assets\1617701221627.png)]

7.key

实际使用的索引，如下图实际使用的索引是key，结合possible_keys学习

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-O96NOlw1-1618826106441)(D:\0AWQY20191031\private\面试\mysql优化.assets\1617701271866.png)]

8.key_len

实际使用到的索引的长度，如果实际使用的索引是组合索引（及有多个索引）则key_len指的是key列的索引集合中长度最大的索引的长度，例如下图：key_len指的是key列中primary和idx_title两个索引比较长的那个

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-h0KtqnVj-1618826106449)(D:\0AWQY20191031\private\面试\mysql优化.assets\1617701378245.png)]

9.ref

ref 列显示使用哪个列或常数与key一起从表中选择数据行。指出对 key 列所选择的索引的查找方式，常见的值有 const, func, NULL, 具体字段名。当 key 列为 NULL ，即不使用索引时 。如果值是func，则使用的值是某个函数的结果 ， 【注意:当 key 列为 NULL ， ref 列也相应为 NULL】。 【如果使用常数等值查询，这里会显示const,如果是链接查询，被驱动表的执行计划这里会显示驱动表的关联字段，如果是条件使用了表达式或者函数，或者条件列发生了内部隐式转换，这里可能显示为func】

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-F8uQAX24-1618826106460)(D:\0AWQY20191031\private\面试\mysql优化.assets\1617862585406.png)]

https://blog.youkuaiyun.com/weixin_30126739/article/details/113122041?utm_medium=distribute.pc_relevant.none-task-blog-baidujs_baidulandingword-4&spm=1001.2101.3001.4242

10.rows

这一列是mysql评估 为了找到所需的行而要读取的行数。这个数字是内嵌循环关联计划里的循环数目，也就是说它不是mysql认为它最终要从表里读取出来的行数，而是mysql为了找到符合查询的每一点上标准的那些行而必须读取的行的平均数。

rows 列提供了试图分析所有存在于累计结果集中的行数目的MySQL 优化器估计值。执行计划很容易描述这个很困难的统计量。

查询中总的读操作数量是基于合并之前行的每一行的rows 值的连续积累而得出的。这是一种嵌套行算法。

简单且重要，数值越大越不好，说明没有用好索引

11.filtered

5.7之后默认展示字段（之前版本需要explain extended 时才会出现此列），表示通过查询条件过滤后实际生效的行数所占的百分比是一个估计值，此百分比越小，说明被过滤掉的数据占比越多；最大是100%，代表没有数据被过滤掉，所有行数均有效。

12.extra

属于附加的扩展信息，表示mysql处理查询用到的方式，如果你想让你的查询尽可能的快，则要保证尽量不要使用到：Using filesort 和 Using temporary，这两个都会增加内存占用以及IO操作，甚至是造成物理IO操作，也就是会跟物理磁盘进行读写。

filesort和temporary的使用场景的区别并不是很明显，不过，有以下的原则:filesort只能应用在单个表上，如果有多个表的数据需要排序，那么MySQL会先使用using temporary保存临时数据，然后再在临时表上使用filesort进行排序，最后输出结果。

NULL:需要查询的列有的使用到了索引有的没有使用到索引，如下图的对比

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-5bveGPKH-1618826106463)(D:\0AWQY20191031\private\面试\mysql优化.assets\1617865438504.png)]

-Using filesort：

主要针对的是单表或者中间结果集

mysql对数据不是按照表内的索引顺序进行读取，而是使用了其他字段重新排序，需要建立额外的表进行索引排序，例如下图展示样例

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-po6UmvgS-1618826106467)(D:\0AWQY20191031\private\面试\mysql优化.assets\1617957285872.png)]

下图是实操的sql

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-7Ele55yG-1618826106471)(D:\0AWQY20191031\private\面试\mysql优化.assets\1617866382964.png)]

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-kz7qDJ7P-1618826106483)(D:\0AWQY20191031\private\面试\mysql优化.assets\1617867089722.png)]

-Using temporary：

当使用多表联查时也就是类似join的操作，使用order by或者group by，会产生一个中间结果集，这个结果集有可能会产生一个临时表，此时的临时表就是temporary，这是也会用到Using filesort。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-1c9tLybo-1618826106489)(D:\0AWQY20191031\private\面试\mysql优化.assets\1617957717359.png)]

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-ujZbLjIL-1618826106512)(D:\0AWQY20191031\private\面试\mysql优化.assets\1617957881326.png)]

四、索引优化的原理

索引前文

​ 官方给的解释是：经过查询测试后得出最好的提高select语句效率的方法就是创建单列或者多列索引。索引的工作方式就像是给表的每一行都做了一个指针，从而实现where条件的快速匹配到具体行，同时取出对应行的其他列的数据。所有的mysql数据类型都可以作为索引。虽然期望可以给每一列都创建索引，但是不必须的索引会减慢mysql选择并决定使用哪一个索引的效率，会浪费内存和时间。索引也会增加insert，update，delete的成本，因为每次的对数据的以上操作都同时会触发索引的更新，所以你必须在使用索引时找到一个最佳的平衡点。例如一下示意的sql以及时间对比表格

-- 创建表
mysql> show create table dept_manager \G
*************************** 1. row ***************************
       Table: dept_manager
Create Table: CREATE TABLE `dept_manager` (
  `dept_no` bigint NOT NULL,
  `emp_no` bigint NOT NULL,
  `from_date` date NOT NULL,
  `to_date` date NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

-- 没有索引：
mysql> insert into dept_manager values(1000001,1000001,'1995-03-05','1995-03-05');
Query OK, 1 row affected (0.01 sec)

mysql> select dept_no from dept_manager where dept_no =1000001;
+---------+
| dept_no |
+---------+
| 1000001 |
+---------+
1 row in set (0.79 sec)

mysql> update dept_manager set dept_no=1000002 where dept_no=1000001;
Query OK, 1 row affected (1.28 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> delete from dept_manager where dept_no=1000002;
Query OK, 1 row affected (1.21 sec)

-- 有单独索引：
-- 正确使用索引：
mysql> create index idx_dept_no on dept_manager(dept_no);
Query OK, 0 rows affected (2.46 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> insert into dept_manager values(1000001,1000001,'1995-03-05','1995-03-05');
Query OK, 1 row affected (0.01 sec)

mysql> select dept_no from dept_manager where dept_no =1000001;
+---------+
| dept_no |
+---------+
| 1000001 |
+---------+
1 row in set (0.00 sec)

mysql> update dept_manager set dept_no=1000002 where dept_no=1000001;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> delete from dept_manager where dept_no=1000002;
Query OK, 1 row affected (0.01 sec)


-- 未正确使用索引：

mysql> insert into dept_manager values(1000001,1000001,'1995-03-05','1995-03-05');
Query OK, 1 row affected (0.01 sec)

mysql> select dept_no from dept_manager where emp_no =1000001;
+---------+
| dept_no |
+---------+
| 1000001 |
+---------+
1 row in set (1.11 sec)

mysql> update dept_manager set dept_no=1000002 where emp_no=1000001;
Query OK, 1 row affected (1.35 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> delete from dept_manager where emp_no=1000001;
Query OK, 1 row affected (1.65 sec)


-- 有组合索引
-- 首先删除单独索引创建组合索引：
mysql> drop index idx_dept_no on dept_manager;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table dept_manager add index idx_dept_emp(dept_no,emp_no);
Query OK, 0 rows affected (3.58 sec)
Records: 0  Duplicates: 0  Warnings: 0

-- 正确使用索引：
mysql> insert into dept_manager values(1000001,1000001,'1995-03-05','1995-03-05');
Query OK, 1 row affected (0.01 sec)

mysql> select dept_no from dept_manager where dept_no =1000001;
+---------+
| dept_no |
+---------+
| 1000001 |
+---------+
1 row in set (0.00 sec)

mysql> update dept_manager set dept_no=1000002 where dept_no=1000001;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> delete from dept_manager where dept_no=1000002;
Query OK, 1 row affected (0.01 sec)


-- 未使用到索引：
mysql> insert into dept_manager values(1000001,1000001,'1995-03-05','1995-03-05');
Query OK, 1 row affected (0.01 sec)

mysql> select dept_no from dept_manager where emp_no =1000001;
+---------+
| dept_no |
+---------+
| 1000001 |
+---------+
1 row in set (1.01 sec)

mysql> update dept_manager set dept_no=1000002 where emp_no=1000001;
Query OK, 0 rows affected (1.95 sec)
Rows matched: 1  Changed: 0  Warnings: 0

mysql> delete from dept_manager where emp_no=1000002;
Query OK, 0 rows affected (1.49 sec)

时间对比图表展示：

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-6nLhLPP2-1618826106515)(D:\0AWQY20191031\private\面试\mysql优化.assets\1618196013429.png)]

​ 索引一般有：主键索引【Primary Key】，唯一索引【Unique Index】，普通索引【Index】，全文索引【FullText Index】

​ 索引存储方式是B树，有些特殊数据类型的索引使用的是R树，Memory引擎的表支持hash索引，InnoDB索引中的全文索引fulltext使用的是倒置列表。

mysql 索引说明：
  • 可以使where匹配的条件更快检索对应行的数据
  • 去除检索过程中的无用行。在有多个索引同时存在的情况下，mysql正常会选用查找行数最少的索引【最优索引】
  • 在组合索引中，要是用索引必须遵循"最左前缀原则"，例如：有一个索引"idx_three(col1,col2,col3)"，你如果要正确使用索引idx_three必须用一下某一种方式触发：【col1】，【col1，col2】，【col1，col2，col3】，其他排序的方式均不可以用，也就是说必须要col1打头，后边两个如果使用也必须按照建立索引时的顺序使用。
  • 在单表中或者在有join操作的表连接中也可以使用索引，但是要注意，连接条件的【列类型】、【列的字符集】尽量保持一致，否则极大的可能不能使用索引，例如：连接条件是1=‘1’【int类型=类型varcher】此时可能是无法使用索引的。但是某些类型在一定条件下可以互通：例如【varchar(5)=char(5) 字符数目相等】可以使用，但是【varchar(5)=char(15) 字符数目不相等】则不可以使用
  • 获取MIN()，MAX()值，在有明确【where+索引】的搜索条件时可以最快的取出对应列
  • 在order by 和group by中有奇效，前提是遵从最左前缀原则正确使用索引，例如有组合索引【idx_p1_p2(key_part1,key_part2)】，使用 【ORDER BY key_part1 , key_part2】，可以快速提取出来排序后的数据
  • 当select的信息仅仅包含有索引的列，则当使用索引列作为查询条件时，不需要去原始表中检索任何列，仅需要在索引中获取数据并返回即可

以上可以参考以下截图进行理解，增加组合索引之后索引存储和数据库存储比对效果：

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-dpscGQkE-1618826106517)(D:\0AWQY20191031\private\面试\mysql优化.assets\1618293520608.png)]

持一致，否则极大的可能不能使用索引，例如：连接条件是1=‘1’【int类型=类型varcher】此时可能是无法使用索引的。但是某些类型在一定条件下可以互通：例如【varchar(5)=char(5) 字符数目相等】可以使用，但是【varchar(5)=char(15) 字符数目不相等】则不可以使用

  • 获取MIN()，MAX()值，在有明确【where+索引】的搜索条件时可以最快的取出对应列
  • 在order by 和group by中有奇效，前提是遵从最左前缀原则正确使用索引，例如有组合索引【idx_p1_p2(key_part1,key_part2)】，使用 【ORDER BY key_part1 , key_part2】，可以快速提取出来排序后的数据
  • 当select的信息仅仅包含有索引的列，则当使用索引列作为查询条件时，不需要去原始表中检索任何列，仅需要在索引中获取数据并返回即可

以上可以参考以下截图进行理解，增加组合索引之后索引存储和数据库存储比对效果：

[外链图片转存中…(img-dpscGQkE-1618826106517)]

java码农007
关注
Mysql执行单个sql脚本、执行多个sql脚本
絮落锦乡的博客
09-25 3447
执行单个脚本 1、打开mysql命令窗口 source test.sql source 不带分号 执行多个脚本 1、待执行的sql文件为test1.sql、test2.sql、test.sql、4.sql等 2、写一个总的sql文件例如：all.sql文件： source test1.sql source test2.sql source test3.sql source te...
【mysql优化专题】本专题终极总结（共12篇）
勇往直前的专栏
11-19 624
本专题目录如下： 一、为什么要进行mysql优化？（重点） 二、增删改优化，多数人都会忽略的优化 三、关于单表查询，可以这么优化 四、关于多表查询，不得不看的优化 五、索引优化（重点中的重点） 六、表的优化，分表分库（重点） 七、存储过程和存储函数教学 八、视图应用优化详解 九、引擎(InnoDB,MyISAM)的内存优化 十、通过慢查询日志定位优化 十一、满分主从复制面试宝...
MYSQL全文索引及Match() against()踩坑记录-超详细超实用
LETIAN_Z的博客
11-01 8645
MYSQL全文索引FULLTEXT及Match() against()踩坑记录-超详细超实用
MySQL-MATCH ... AGAINST工具
最新发布
zx733988的博客
02-26 804
在MySQL中，MATCH……AGAINST是全文索引（Full-Text index）的查询语法，它允许你对文本进行高效的全文搜素，支持自然语言搜索和布尔搜索模式。以下是MATCH……AGAINST的详细用法和示例。
Mysql全文搜索之MATCH...AGAINST的用法介绍
热门推荐
飞鱼的空间
08-16 1万+
前提：mysql只支持英文内容的全文索引，所以只考虑英文的全文搜索。假定数据表名为post，有三列：id、title、content。id是自增长序号，title是varchar，content是text，给content添加全文索引。 mysql全文搜索有三种模式： 一、自然语言查找。这是mysql默认的全文搜索方式，sql示例：
学习笔记之《MySQL必知必会》
smart_ferry的博客
12-22 627
唯一标识表中每行的这个列（或这组列）称为主键 注:主键可以为多个字段的组合！！！ 主键列不允许NULL值  语句以“；”或者“\g”结束 SELECT DISTINCT vend_id,prod_price，除非指定的两个列都相同，否则所有行都将被检索出来（即只有两个列对应值都一样才视为一样）。如下： limit：限制展示出来的记录数。可提供1或两个参数。若只提供一个参数a，表示从原结果...
mysql 8 全文检索_MySQL 8中使用全文检索示例
weixin_36162906的博客
01-27 228
首先建议张册测试用的表test，并使用fulltext说明将title和body两列的数据加入全文检索的索引列中：drop table if exists test;create table test (id integer not null primary key auto_increment,title varchar(50),body longtext,fulltext(title, bod...
mysql优化笔记+资料
11-18
以下是一份详细的MySQL优化笔记，涵盖了多个方面： 一、查询优化 1. 使用索引：为经常用于搜索的列创建索引可以显著加快查询速度。B树和哈希索引是最常见的类型，适用于不同的查询场景。 2. 避免全表扫描：尽量使用...
MySQL优化方案参考
12-15
MySQL优化是一个涵盖多个层面的复杂任务，涉及到硬件配置、操作系统设置以及数据库本身的调整。这篇文章将主要探讨硬件层、系统层和MySQL层的优化方案。 在硬件层，优化首先从CPU开始。为了最大化CPU性能，应调整...
MYSQL.rar_MYSQL_mysql 优化_mysql优化_mysql备份_sql优化
09-14
一、MySQL优化 1. 结构优化：这涉及到数据库设计，如合理创建索引、避免全表扫描、选择合适的数据类型，以及规范化与反规范化设计等。正确地设置主键和外键，可以提高数据一致性并优化查询速度。 2. 查询优化：编写...
MySQL—04—MySQL的其他对象
weixin_30872671的博客
09-23 151
一、MySQL 中的其他对象 1.索引 MySQL 索引的建立对于 MySQL 的高效运行是很重要的，索引可以大大提高 MySQL 的检索速度。 1.1MySQL 中的索引类型 • 普通索引 • 唯一索引 • 主键索引 • 组合索引 • 全文索引 1.2普通索引 是最基本的索引，它没有任何限制。在创建索引时，可以指定索引长度。length ...
【已解决】MySQL（Navicat）中如何一次性执行多个sql脚本文件
weixin_42585386的博客
03-19 7272
因为我这200多个sql文件都来自于不同的数据库，我需要根据sql文件名的前缀，去检索出我在每个数据库中对应的需要用到的文件，然后将他们合并到一个sql文件中去，然后再去Navicat中对应的数据库执行这个合并后的sql文件即可。既然Navicat中不支持一次性运行多个sql文件，那有没有办法可以把所有的sql文件中的sql都归纳到同一个汇总的sql文件去，然后在Navicat中运行这个汇总的sql文件不就可以了吗！不信邪的我，再次上网查资料，最后发现了一个也很方便的方法，非常好的解决了我的问题！
mysql批量执行sql文件
qq_22483767的博客
01-03 240
【代码】mysql批量执行sql文件。
MySQL性能分析及explain的使用
weixin_33714884的博客
08-21 449
MySQL性能分析及explain用法的知识是本文我们主要要介绍的内容，接下来就让我们通过一些实际的例子来介绍这一过程，希望能够对您有所帮助。 1.使用explain语句去查看分析结果 如explain select * from test1 where id=1;会出现：id selecttype table type possible_keys key key_len...
【转】mysql之explain详解（分析索引的最佳使用）
qq_35649305的博客
09-07 189
1、type类型查询速度： system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL 就type进行详细的介绍： System,const,eq_ref,ref,range,index,all all : 即全表扫描 index : 按索引次序扫描
MySQL详解（四）——高级 2.0
qq_43409401的博客
02-01 661
Explain性能分析，优化实战。
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值