MySQL数据查询优化

最新推荐文章于 2024-05-23 20:16:10 发布
最新推荐文章于 2024-05-23 20:16:10 发布
阅读量1k 收藏 19
点赞数 17
文章标签： mysql 数据库 MySQL调优
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/qq_45774406/article/details/138760500
版权

MySQL调优是开发中必不可少的内容，以下是对MySQL查询性能优化的部分总结

1. explain关键字的使用

        explain关键字可以模拟优化器执行sql查询语句，获取sql的执行信息，使用方法：

explain+sql语句

        1.1 字段详解

        

  1.  id（select 查询的序列号，包含一组数字，表示查询中执行select子句或操作表的顺序）

    ①id相同，执行顺序从上往下
    ②id全不同，如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行
    ③id部分相同，执行顺序是先按照数字大的先执行，然后数字相同的按照从上往下的顺序执行

  2. select_type（查询类型，用于区别普通查询、联合查询、子查询等复杂查询）
    ①SIMPLE ：简单的select查询，查询中不包含子查询或UNION
    ②PRIMARY：查询中若包含任何复杂的子部分，最外层查询被标记为PRIMARY
    ③SUBQUERY：在select或where列表中包含了子查询
    ④DERIVED：在from列表中包含的子查询被标记为DERIVED，MySQL会递归执行这些子查询，把结果放在临时表里
    ⑤UNION：若第二个select出现在UNION之后，则被标记为UNION，若UNION包含在from子句的子查询中，外层select将被标记为DERIVED
    ⑥UNION RESULT：从UNION表获取结果的select

  3. table（显示这一行的数据是关于哪张表的）

  4. type（显示查询使用了那种类型，从最好到最差依次排列 system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL ）
    ①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和index都是读全表，但index是从索引中读取的，而all是从硬盘中读的）
    ⑦ALL：Full Table Scan，将遍历全表找到匹配的行
     一般来说，得保证查询至少达到range级别，最好到达ref

  5. possible_keys（显示可能应用在这张表中的索引，一个或多个，查询涉及到的字段若存在索引，则该索引将被列出，但不一定被查询实际使用）

  6. key
    ①    实际使用的索引，如果为NULL，则没有使用索引

    ②查询中若使用了覆盖索引，则该索引和查询的 select 字段重叠，仅出现在key列表中

  7. key_len
    用于处理查询的索引长度，表示索引中使用的字节数。通过这个值，可以得出一个多列索引里实际使用了哪一部分。

    注：key_len显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出的。另外，key_len只计算where条件用到的索引长度，而排序和分组就算用到了索引，也不会计算到key_len中。

  8. ref

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

  9. rows

    表示MySQL根据表统计信息及索引选用情况，大致估算的找到所需的目标记录所需要读取的行数，不是精确值。这个值非常直观的显示 sql 效率好坏， 原则上 rows 越少越好

  10. filtered

    当你的索引统计直接获取了需要的所有数据时，就会显示100
    因此一个比较低filtered值表示需要有一个更好的索引，假如type=all，表示以全表扫描的方式得到1000条记录，且filtered=0.1%，表示只有1条记录是符合搜索条件的。

  11. extra

    Using filesort
    MySQL有两种方式可以生成有序的结果，通过排序操作或者使用索引，当Extra中出现了Using filesort 说明MySQL使用了后者，但注意虽然叫filesort但并不是说明就是用了文件来进行排序，只要可能排序都是在内存里完成的。大部分情况下利用索引排序更快，所以一般这时也要考虑优化查询了。使用文件完成排序操作，这是可能是ordery by，group by语句的结果，这可能是一个CPU密集型的过程，可以通过选择合适的索引来改进性能，用索引来为查询结果排序。

    Using temporary
    用临时表保存中间结果，常用于GROUP BY 和 ORDER BY操作中，一般看到它说明查询需要优化了，就算避免不了临时表的使用也要尽量避免硬盘临时表的使用。

    Not exists
    MYSQL优化了LEFT JOIN，一旦它找到了匹配LEFT JOIN标准的行， 就不再搜索了。

    Using index
    说明查询是覆盖了索引的，不需要读取数据文件，从索引树（索引文件）中即可获得信息。如果同时出现using where，表明索引被用来执行索引键值的查找，没有using where，表明索引用来读取数据而非执行查找动作。这是MySQL服务层完成的，但无需再回表查询记录。

    Using index condition
    这是MySQL 5.6出来的新特性，叫做“索引条件推送”。简单说一点就是MySQL原来在索引上是不能执行如like这样的操作的，但是现在可以了，这样减少了不必要的IO操作，但是只能用在二级索引上。

    Using where
    使用了WHERE从句来限制哪些行将与下一张表匹配或者是返回给用户。注意：Extra列出现Using where表示MySQL服务器将存储引擎返回服务层以后再应用WHERE条件过滤。

    Using join buffer
    使用了连接缓存：Block Nested Loop，连接算法是块嵌套循环连接;Batched Key Access，连接算法是批量索引连接

    impossible where
    where子句的值总是false，不能用来获取任何元组

    select tables optimized away
    在没有GROUP BY子句的情况下，基于索引优化MIN/MAX操作，或者对于MyISAM存储引擎优化COUNT(*)操作，不必等到执行阶段再进行计算，查询执行计划生成的阶段即完成优化。

    distinct
    优化distinct操作，在找到第一匹配的元组后即停止找同样值的动作

    参考链接

2. MySQL索引原理

  • 顾名思义，B-tree索引使用B-tree的数据结构存储数据，不同的存储引擎以不同的方式使用B-Tree索引，比如MyISAM使用前缀压缩技术使得索引空间更小，而InnoDB则按照原数据格式存储，且MyISAM索引在索引中记录了对应数据的物理位置，而InnoDB则在索引中记录了对应的主键数值。B-Tree通常意味着所有的值都是按顺序存储，并且每个叶子页到根的距离相同。

  • B-Tree索引驱使存储引擎不再通过全表扫描获取数据，而是从索引的根节点开始查找，在根节点和中间节点都存放了指向下层节点的指针，通过比较节点页的值和要查找值可以找到合适的指针进入下层子节点，直到最下层的叶子节点，最终的结果就是要么找到对应的值，要么找不到对应的值。整个B-tree树的深度和表的大小直接相关。

  • 全键值匹配：和索引中的所有列都进行匹配，比如查找姓名为zhang san，出生于1982-1-1的人

  • 匹配最左前缀：和索引中的最左边的列进行匹配，比如查找所有姓为zhang的人

  • 匹配列前缀：匹配索引最左边列的开头部分，比如查找所有以z开头的姓名的人

  • 匹配范围值：匹配索引列的范围区域值，比如查找姓在li和wang之间的人

  • 精确匹配左边列并范围匹配右边的列：比如查找所有姓为Zhang，且名字以K开头的人

  • 只访问索引的查询：查询结果完全可以通过索引获得，也叫做覆盖索引，比如查找所有姓为zhang的人的姓名

参考链接

3. 实例
以下是我的测试表device_everyday_count_value的表结构：

CREATE TABLE `device_everyday_count_value` (
  `sid` int NOT NULL AUTO_INCREMENT COMMENT '主键 序号 自增',
  `device_id` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '设备id',
  `device_name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '设备名称',
  `node_id` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '点位id',
  `node_describe` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '点位描述',
  `count_value` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '统计值',
  `latest_value` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '当天最新的（最晚的）记录',
  `value_type` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '信号值属性（dict_device_valuetype)',
  `count_date` date DEFAULT NULL COMMENT '统计时间',
  PRIMARY KEY (`sid`) USING BTREE,
  KEY `idx_deviceId_countDate_countValue` (`device_id`,`count_date`,`count_value`) USING BTREE COMMENT 'deviceId,countDate,CountValue普通索引'
) ENGINE=InnoDB AUTO_INCREMENT=515 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ROW_FORMAT=DYNAMIC COMMENT='设备点位每日用能统计表';

1.1 select * 

explain select t1.* from `device_everyday_count_value` t1;

 

根据上图能够发现当我们使用select *时，索引是失效的而且扫描了全表

现在改为如下所示：

explain select  t1.device_id, t1.count_date from `device_everyday_count_value` t1;

发现索引生效了，所以应当避免使用select * 

1.2 where条件相等判断

explain select t1.device_id , t1.count_date from `device_everyday_count_value` t1 where t1.device_name = 'test';

发现没有走索引，且全表扫描。现改为如下所示：

explain select t1.device_id , t1.count_date from `device_everyday_count_value` t1 where t1.device_id = '20';

 

能够走索引，where过滤条件要符合最左原则。

1.3 回表

explain select  t1.device_name, t1.device_id, t1.count_date from `device_everyday_count_value` t1;

可以发现select字段中出现了没在索引列上的device_name，在执行的时候发现没有走索引并且type为all。现改为如下所示：

explain select  t1.device_id, t1.count_date from `device_everyday_count_value` t1;

能够发现索引生效了，并且没有回表，表名我们在使用的过程中最好覆盖索引，如果是比较常用的name属性可以考虑走缓存，然后数据库查询的时候只查询device_id

1.4 type---查询范围优化

1.4.1 引号问题

explain select  t1.device_id, t1.count_date from `device_everyday_count_value` t1 where t1.device_id > 1;

发现我们的type类型为index（system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL）是比较低的，

现改为如下所示：

explain select  t1.device_id, t1.count_date from `device_everyday_count_value` t1 where t1.device_id > '1';

我们type类型变为了range类型，查询范围优化了，通过表结构能够发现我们的device_id是varchar类型的虽然数据库存储的是数字，但是类型是字符串类型的所以我们应该加上引号

1.4.2 索引顺序

在执行前先device_id的索引顺序移动到第二位

执行以下代码：

explain select  t1.device_id, t1.count_date from `device_everyday_count_value` t1 where t1.device_id > '1';

接下来再将device_id的顺序移动到最后

执行同样的sql代码

explain select  t1.device_id, t1.count_date from `device_everyday_count_value` t1 where t1.device_id > '1';

可以发现我们的type的等级由range降为index了，索引我们在使用的时候需要符合最左前缀原则。

内容会持续更新，还会更新多表联合查询的优化处理以及注意事项！

锲而舍之，朽木不折；锲而不舍，金石可镂。——诗句出自：《荀子·劝学》

卷心菜的学习路
关注
  • 17
    点赞
  • 踩
  • 19
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
mysql limit查询优化分析
10-30
MYSQL的优化是非常重要的。其他最常用也最需要优化的就是limit。mysql的limit给分页带来了极大的方便，但数据量一大的时候，limit的性能就急剧下降
mysql 数据插入优化方法
01-21
通常来说，在MyISAM里读写操作是串行的，但当对同一个表进行查询和插入操作时，为了降低锁竞争的频率，根据concurrent_insert的设置，MyISAM是可以并行处理查询和插入的： 当concurrent_insert=0时，不允许并发插入...
MySQL百万级数据分页查询优化方案
09-09
在mysql中limit可以实现快速分页，但是如果数据到了几百万时我们的limit必须优化才能有效的合理的实现分页了，否则可能卡死你的服务器哦。
MySQL海量数据查询优化策略.
02-28
MySQL海量数据查询优化策略,方法。 1.对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引。 2.应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引...
【MySQL精通之路】SQL优化(1)-查询优化(1)-WHERE子句
Anakki的博客
05-22 499
与PRIMARY KEY或UNIQUE索引上的WHERE子句一起使用的表，其中所有索引部分都与常量表达式进行比较，并定义为NOT NULL。由于MySQL会自动进行类似的优化，因此您通常可以避免这项工作，并将查询保留为更易于理解和维护的形式。因为MySQL优化器的开发工作还在进行中，所以这里并没有记录MySQL优化器所有的优化。您可能会试图重写查询以加快算术运算，同时牺牲可读性。，这有助于简化join连接。有关更多信息和示例，请参见“有关更多信息，请参见“空表或只有一行的表。
mysql中InnoDB的表空间--独立表空间
qq_41534540的博客
05-22 652
大家好，上篇文章我们在讲mysql数据目录的时候提到了表空间这个名词，它是一个抽象的概念，对于系统表空间来说，对应着文件系统中一个或多个实际文件；对于每个独立表空间来说，对应着文件系统中一个名为表名.ibd的实际文件。我们可以把表空间比作存储页的容器，当我们想为某个表插入一条记录的时候，就从容器中找出一个对应的页来把数据写进去。今天我们就先来深入聊一下InnoDB的独立表空间。在讲表空间之前，我们先回忆一下页的相关内容，这有助于下面我们对表空间的理解。
MySQL--增、删、改、查，
weixin_65476290的博客
05-15 1624
数据库的概述、发展、现状、历史、分类MySQL关系型数据库、架构（C/S）window系统安装MySQL数据库Linux系统【选学】数据库对象——数据库（database）show、create、drop命令数据库对象——表（table）数据类型数据库表的约束表结构的调整（alter）数据库授权和权限管理（grant）数据库的编码情况【编码一致，建议统一采用utf8mb4】
【MySQL进阶之路 | 基础篇】MySQL新特性 : 窗口函数
最新发布
2301_80912559的博客
05-23 256
.
【14】bat脚本备份mysql数据
欢迎你们到来，希望能帮到大家
05-20 101
##一、参数说明。
【MySQL精通之路】InnoDB(7)-锁和事务模型
Anakki的博客
05-22 268
1.InnoDB锁 2.InnoDB事务模型 3.InnoDB中不同SQL语句设置的锁 4.幻影行 5.InnoDB中的死锁 5.1InnoDB死锁示例 5.2死锁检测 5.3如何最小化和处理死锁 6事务调度
我的心情JSP+Servlet+JDBC+MySQL
高调做事，低调做人！
05-22 485
将项目从idea中导入，然后配置项目的结构，包括jdk,库，模块，项目，工件，Facet， 配置Tomcat，将项目部署到Tomcat进行访问。 浏览器中输入地址：http://localhost:8080/ROOT/Do 进行访问。
MySQL运算符
qq_72641424的博客
05-20 1144
MySQL运算符，算术运算符，比较运算符，逻辑运算符，位运算符
mysql - 索引基本知识梳理
zzsan的博客
05-21 494
官方介绍索引是帮助MySQL高效获取数据的数据结构, 原理为, mysql的索引采用的是的结构。
温故而知新-MySQL篇【面试复习】
小心星的博客
05-20 895
温故而知新-MySQL篇【面试复习】
【MySQL精通之路】SQL优化(1)-查询优化(8)-嵌套联接优化
Anakki的博客
05-22 1029
尽管理论上可以在没有括号的情况下解析查询：我们仍然会有明确的查询语法结构，因为LEFT JOIN ON扮演着表达式（t2，t3）的左右分隔符的角色。你可以看到，连接词C1（T1）、C2（T2）、C3（T3）中的每一个都被从最内部的循环推到最外部的循环，在那里它可以被评估。通过键从一个内部表访问同一嵌套联接中的另一个表是被禁止的，如果它是由WHERE条件中的谓词引发的。（关于表达式），而P（T1，T2，T3）是表T1、T2、T3的列上的条件。具有嵌套外部联接的查询以与具有内部联接的查询相同的管道方式执行。
mysql表约束基础 【default | 主键 | 外键 | 唯一键】
菜菜成长为后端开发大佬
05-23 1030
目录一，表的约束 1. default && not null2. comment(软约束)3. zerofill4. 主键复合键5.自增长6、唯一键7.外键 表中将会有一些约束，换句话说就是限制，来规范输入的数据，使其能符合数据库的期望。约束本质上是通过技术手段，插入正确的数据，达到：数据库数据的完成性和可预期性。mysql常见的约束手段：很显然易见，首先我们需要认识到的是，not null 与 default 两者并不互斥，有时可以相辅相成，示例如下：曾经我们之所以可以在不设置defau
MySQL 的表约束详解
良月柒
05-20 701
表约束是应用于数据库表的规则，用于限制表中的数据，以确保数据的完整性和有效性。约束有助于防止不正确的数据进入数据库，从而保证数据的一致性和准确性。MySQL 的表约束是确保数据完整性和一致性的关键工具。通过合理地使用 NOT NULL、UNIQUE、PRIMARY KEY、FOREIGN KEY 和 CHECK 约束，可以有效防止错误数据进入数据库，保证数据的准确性和可靠性。在实际开发中，理解和应用这些约束将大大提高数据库的设计质量和应用程序的稳定性。
MySQL的自增ID连续性控制变量innodb_autoinc_lock_mode
一见
05-23 320
在高并发环境下，建议使用连续模式（1）或交错模式（2）以提高性能，然而这可能会导致自增 ID 的不连续分配。在需要保持自增 ID 连续性的场景下，可以使用传统模式（0），但这可能会导致性能下降。这种策略可以提高性能，特别是在高并发环境下，但可能导致自增 ID 的不连续分配。当插入一行数据时，InnoDB 会锁定自增序列的一个范围，并在事务提交时释放锁。这种策略可以提高性能，特别是在高并发环境下，同时保持自增 ID 的连续性。这种策略可以避免自增 ID 的重复分配，但可能导致性能下降，特别是在高并发环境下。
mysql百万级数据查询优化
09-01
MySQL百万级数据查询优化是提高数据库性能的重要步骤。下面是一些建议： 1. 索引优化：通过合理的索引设计，能够加快查询速度。对于经常作为查询条件的列，添加索引能够提高查询效率。 2. 分页查询优化：对于大...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值