MySQL优化

最新推荐文章于 2023-03-31 09:17:23 发布
最新推荐文章于 2023-03-31 09:17:23 发布
阅读量129 收藏 1
点赞数
分类专栏： MySQL 文章标签： 数据库 索引 mysql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-NC-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/dreaming_coder/article/details/108673891
版权

SQL执行慢的原因

  • 查询语句写的烂

  • 索引失效

    • 单值索引

      假设一张表id name email VXNum，我们格局name来查询，如果建立索引则会快很多

      create index idx_user_name on user(name);

    • 复合索引

      create index idx_user_nameEmail on user(name,email);

  • 关联查询太多join（设计缺陷或不得已的需求）

  • 服务器调优及各个参数设置（缓冲、线程数等）

SQL执行顺序

  • 手写

在这里插入图片描述

  • 机读

在这里插入图片描述

索引

索引是什么

索引是帮助MySQL高效获取数据的数据结构

索引的目的在于提高查询效率，可以类比字典

可以简单理解为 排好序的快速查找额数据结构

在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查找算法，这种数据结构就是索引。

为了加快查找速度，可以维护一个二叉排序树，每个节点分别包含索引键值和一个指向对应数据记录物理地址的指针，这样就可以在一定复杂度之内就能获取到相应数据，从而快速检索出符合条件的记录

一般来说，索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储在磁盘上

我们常说的索引，如果没有特别指明，都是B树（多路搜索树，不一定是二叉的）结构组织的索引，其中聚集索引，次要索引，覆盖索引，唯一索引默认都是使用B+树所欲，统称索引。当然，除了B+树这种类型的索引之外，还有哈希索引。

索引的优势和劣势

  • 优点

    提高数据检索效率，降低数据库的IO成本

    通过索引列对数据进行排序，降低数据的排序成本，降低了CPU的消耗

  • 缺点

    实际上索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录，所以索引列也是要占用空间的

    虽然提高了查询速度，但是降低更新表的速度

索引分类

  • 单值索引

一个索引只包含单个列，一个表可以有多个单列索引

  • 唯一索引

索引列的值必须唯一，但允许有空值

  • 复合索引

    即一个索引包含多个列

基本语法

  • 创建

    CREATE [UNIQUE] INDEX indexName ON tableName(columnName_list);

ALTER TABLE tableName ADD [UNIQUE] INDEX [indexName] ON (columnName_list);

  • 删除

    DROP INDEX [indexName] ON tableName;

  • 查看

    SHOW INDEX FROM tableName;

可以有四种方式更改数据表的索引

  • ALTER TABLE tableName ADD PRIMARY KEY(columnName_list);

    该语句添加一个主键，索引值必须唯一且不为NULL

  • ALTER TABLE tableName ADD UNIQUE [indexName] ON (columnName_list);

    创建索引值是唯一的（除了NULL，并且NULL可能出现多次）

  • ALTER TABLE tableName ADD INDEX [indexName] ON (columnName_list);

    添加普通索引，索引值可重复

  • ALTER TABLE tableName ADD FULLTEXT [indexName] ON (columnName_list);

    全文索引

MySQL索引结构

  • B-TREE
  • FULL-TEXT
  • HASH
  • R-TREE

哪些情况需要创建索引

  • 主键自动建立唯一索引
  • 频繁作为查询条件的字段应该创建索引
  • 查询中与其它表关联的字段，外键关系建立索引
  • 频繁更新的字段不适合创建索引
  • where条件里用不到的字段不要创建索引
  • 单键/组合索引的选择问题（在高并发下倾向创建组合索引）
  • 查询中排序的字段若通过索引访问将大大提高排序速度
  • 查询中统计或者分组字段

哪些情况不要建索引

  • 表记录太少
  • 经常增删改的表
  • 数据重复且分布较为平均的列

性能分析——Explain

使用explain关键字可以模拟优化器执行SQL查询语句，从而知道MySQL是如何处理你的SQL语句的，分析你的查询语句或是表结构的性能瓶颈

使用方法：EXPLAIN + SQL语句，结果如图：

在这里插入图片描述

能干嘛：

  • 表的读取顺序
  • 数据读取操作的操作类型
  • 哪些索引可以使用
  • 哪些索引被实际使用
  • 每张表有多少行被优化器查询

表头解释：

在这里插入图片描述

  • id

    select 查询的序列号，包含一组数字，表示查询中执行select子句或操作表的顺序，有两种情况

    • id相同，执行顺序由上至下
    • id不同，如果是子查询，id的序号会递增，id值越大，优先级越高，越先被执行

  • select_type

    常用值：

    1. SIMPLE
    2. PRIMARY
    3. SUBQUERY
    4. DERIVED
    5. UNION
    6. UNION RESULT

    查询的类型主要是用于区别普通查询、联合查询、子查询等复杂查询

    • SIMPLE

      简单的select查询，查询中不包含子查询或者UNION

    • PRIMARY

      查询中若包含任何复杂的子部分，最外层查询则被标记为PRIMARY

    • SUBQUERY

      在select或where列表中包含了子查询

    • DERIVED

      在from列表中包含的子查询被标记为DERIVED（衍生）

      MySQL会递归执行这些子查询，把结果放在临时表里

    • UNION

      若第二个select出现在UNION之后，则被标记为UNION

      若UNION包含在FROM子句的子查询中，外层SELECT将被标记为DERIVED

    • UNION RESULT

      从UNION表获取结果的select

  • table

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

  • type

    可能取值：

    1. ALL
    2. index
    3. range
    4. ref
    5. eq_ref
    6. const
    7. system
    8. NULL

    访问类型的排列，显示查询使用了何种类型，从最好到最差依次是：system>const>eq_ref>ref>range>index>ALL

    一般来说，至少达到range或ref级别，最好达到ref

    • system

      表只有一行记录（等于系统表），这是const类型的特例，平时不会出现，可以忽略不计

    • const

      表示通过索引一次就找到了，const用于比较primary key 或者 unique 索引。因为只匹配一行数据，所以很快将主键置于where列表中，MySQL就能将该查询转换为一个常量

    • eq_ref

      唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描

    • ref

      非唯一性索引扫描，返回匹配某个单独值的所有行

      本质上也是一种索引访问，它返回所有匹配某个单独值的行，然而，它可能会找到多个符合条件的行，所以他应该属于查找和扫描的混合体

    • range

      只检索给定范围的行，使用一个索引来选择行。key列显示使用了哪个索引，一般就是在你的where语句中出现了between、<、>、in等的查询

      这种范围扫描索引比全表扫描要好，因为它只需要开始于索引的某一点，而结束于另一点，不用扫描全部索引

    • index

      full index scan，index与ALL的区别为index类型只遍历索引树。这通常比ALL快，因为索引文件通常比数据文件小

    • ALL

      全表扫描

  • possible_keys

    显示可能应用在这张表中的索引，一个或多个

    查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询实际使用

  • key

    实际使用的索引

    如果为NULL，则没有使用索引

    查询中若使用了覆盖索引，则该索引仅出现在key列表中

    查询的字段及顺序和你建立的索引刚好符合，这就是覆盖索引，MySQL将直接从索引上扫描

  • key_len

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

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

  • ref

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

  • rows

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

  • Extra

    包含不适合在其他列中显示但十分重要的额外信息，下面只列出最主要的3种

    • Using filesort

      九死一生

      说明MySQL会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。

      MySQL中无法利用索引完成的排序操作称为“文件排序”

    • Using temporary

      十死无生

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

    • Using index

      发了

      表示相应的select操作使用了覆盖索引，避免访问了表的数据行，效率不错！

      如果同时出现 Using where，表名索引被用来执行索引键值的查找

      如果没有同时出现Using where，表明索引用来读取数据而非执行查找动作

索引失效的原因

在这里插入图片描述

解决like “%字符串%“ 时索引失效的办法：覆盖索引

最好**“字符串%”**右边，这样不会索引失效

show profile

是MySQL提供可以用来分析当前绘画中语句执行的资源消耗情况，可以用于SQL调优的测量

默认情况下，参数处于关闭状态，并保存最近15次的运行结果

  • 查看结果

    show profiles;

  • 诊断SQL

    SHOW PROFILE cpu,block io for QUERY 前一步查询出来的结果的ID;

    还可以有其他参数

ツぃ☆ve芜情
关注
专栏目录
Mysql数据库基本操作（十五）Mysql优化（1）性能分析
weixin_42445051的博客
02-22 888
在应用的的开发过程中，由于初期数据量小，开发人员写SQL语句时更重视功能上的实现，但是当应用系统正式上线后，随着生产数据量的急剧增长，很多SQL语句开始逐渐显露出性能问题，对生产的影响也越来越大，此时这些有问题的SQL语句就成为整个系统性能的瓶颈，因此我们必须要对它们进行优化. 一般Mysql的优化分为两步，首先分析Mysql的性能，然后想出对应的方法优化。 一，性能分析 1.1 查看sQL执行频率 MySQL客户端连接成功后，通过show [session|global] status命令可以查看服务器状
Mysql优化之Zabbix分区优化
09-09
MySQL优化是提升数据库性能的关键，尤其对于Zabbix这样的监控系统来说，其性能直接影响到监控数据的收集和告警的及时性。Zabbix的数据主要存储在`history`和`trends`两个表中，随着时间的推移，这两个表会变得非常...
MySQL：性能分析(二)关键字详解
小树不倒我不倒
07-31 709
Id：表的读取顺序 select查询的序列号,包含一组数字，表示查询中执行select子句或操作表的顺序 三种情况 id相同，执行顺序由上至下 如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行 id如果相同，可以认为是一组，从上往下顺序执行； 在所有组中，id值越大，优先级越高，越先执行 衍生 = DERIVED Select_type：数据读取操作的操作类型  ...
mysql Sql slow log_mysql --explain+slowlog
weixin_35398679的博客
02-01 187
一、EXPALIN在SQL语句之前加上EXPLAIN关键字就可以获取这条SQL语句执行的计划那么返回的这些字段是什么呢？我们先关心一下比较重要的几个字段：1. select_type 查询类型1)simple 简单查询，没有UNION和子查询2)priamry 主查询，有UNION或子查询的最外层查询3)union 联合查询，有UNION的第二个和以后的查询4)subquery 子查询，有子查询的...
MySQL_explain关键字分析查询语句
weixin_30268071的博客
08-16 162
　　版权声明：本文为博主原创文章，转载请注明出处。 　　通过对查询语句的分析，可以了解查询语句的执行情况。MySQL中，可以使用EXPLAIN语句和DESCRIBE语句来分析查询语句。 　　EXPLAIN语句的基本语法如下:（DESCRIBE语法一致，DESCRIBE可简写为DESC） 　　EXPLAIN Select 语句； 　　eg：explain SELECT * FR...
Mysql性能分析关键字Explain详解（附例子 ）
qq_43801592的博客
11-02 2016
Explain 定义（是什么） 使用EXPLAIN关键字可以模拟优化器执行SQL查询语句，从而知道MySQL是如何处理你的SQL语句的。分析你的查询语句或是表结构的性能瓶颈。 官网介绍：https://dev.mysql.com/doc/refman/8.0/en/using-explain.html。 作用（能干嘛） 表的读取顺序（id） 数据读取操作的操作类型（select_type） 哪些索引可以使用（possible_keys) 哪些索引被实际使用（key） 表之间的应用（ref） 每张表
mysql优化笔记+资料
11-18
以下是一份详细的MySQL优化笔记，涵盖了多个方面： 一、查询优化 1. 使用索引：为经常用于搜索的列创建索引可以显著加快查询速度。B树和哈希索引是最常见的类型，适用于不同的查询场景。 2. 避免全表扫描：尽量使用...
MySQL优化方案参考
12-15
MySQL优化是一个涵盖多个层面的复杂任务，涉及到硬件配置、操作系统设置以及数据库本身的调整。这篇文章将主要探讨硬件层、系统层和MySQL层的优化方案。 在硬件层，优化首先从CPU开始。为了最大化CPU性能，应调整...
mysql中分析性能的关键字_mysql 性能分析Explain使用-Go语言中文社区
weixin_29091445的博客
01-28 146
说明只是单纯的记录学习过程介绍使用EXPLAIN关键字可以模拟优化器执行SQL查询语句,从而知道MySQL是如何处理你的SQL语句的,分析你的查询语句或是表结构的性能瓶颈能干嘛表的读取顺序数据读取操作的操作类型那些索引可以使用那些索引被实际使用表之间的引用每张表有多少航被优化器查询如何使用很简单 explain + sql语句比如包含信息idselect 查询的序列号,包含一组数字,表示查询中执行...
mysql性能分析关键字_Mysql：性能分析
weixin_34329874的博客
01-19 258
1、EXPLAIN关键字(1)概念使用EXPLAIN关键字可以模拟优化器执行sQL查询语句，从而知道MySQL是如何处理你的SQL语句的。分析你的查询语句或是表结构的性能瓶颈(2)使用mysql> select *-> fromstudent;+------------+------------+---------+-------------+------+-------+------...
详解MySQL性能优化
qq_24692021的博客
12-20 356
作者：LanceToBigData cnblogs.com/zhangyinhua/p/7620964.html 说起MySQL的查询优化，相信大家积累一堆技巧：不能使用SELECT *、不使用NULL字段、合理创建索引、为字段选择合适的数据类型….. 你是否真的理解这些优化技巧？是否理解其背后的工作原理？在实际场景下性能真有提升吗？ 我想未必。因而理解这些优化建议背后的原理就尤为重要，希望本文能让你重新审视这些优化建议，并在实际业务场景下合理的运用。 MySQL逻辑架构 如果能在头脑中构建一幅.
MySQL 性能分析--Explain关键字
Strong_shady的博客
05-17 157
Explain怎么玩： explain + SQL语句 explain执行计划各个字段的含义： 1.id： id数字大的优先级最高 此字段代表的是 表的加载顺序，小表驱动大表 2.select_type: 查询的类型，主要是用于区别 普通查询、联合查询、子查询等的复杂查询 3.table: 表示这一行的数据是关于哪张表的。 4.type（注意结合脑图中的sql语句理解）： 从最好到最次依次如下： system>const>eq_ref>ref>range>index&gt
MySql详解（四）--性能分析篇
qq_35105015的博客
03-03 547
Mysql-详解，看这篇就够了
mysql用于分析查询过程的关键字及其用法
最新发布
qq_43432523的博客
03-31 468
mysql性能分析关键字
MySQL性能分析 二
PinkCoder
04-12 1393
MySQL性能分析，慢查询、执行计划分析
一文搞懂MySQL性能
m0_51736969的博客
03-15 199
慢查询日志记录了所有执行时间超过指定参数（long_query_time，单位：秒，默认10秒）的所有 SQL 语句的日志。MySQL5.6提供了对SQL的跟踪 trace，通过 trace 文件能够进一步了解为什么优化器选择 A 执行计划而不选择 B 执行计划，帮助我们更好地理解优化器的行为。然后，检查INFORMATION_SCHEMA.OPTIMIZER_TRACE就可以知道MySQL是如何执行 MySQL 的，其会输出一个超长的 JSON 跟踪文件。值），那么对一个 MyISAM 表的。
mysql关键字explain,优化查询效率
Prodigal
10-17 437
（参考）https://blog.csdn.net/hll814/article/details/50765232 https://blog.csdn.net/lr131425/article/details/61918741 explain关键字 如果在select语句前放上关键词explain，mysql将解释它如何处理select，提供有关表如何联接和联接的次序。 explain属性： id...
MySQL优化技术详解
“MYSQL优化技术.ppt - 大型IT公司的内部文档，涵盖了MySQL的基本介绍、历史、优化方式和技巧分享。” MySQL是世界上最流行的开源关系型数据库管理系统之一，它以其高效、稳定和易于管理的特点，被广泛应用于各种...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值