MySQL索引优化(一)

最新推荐文章于 2024-09-12 01:15:37 发布
最新推荐文章于 2024-09-12 01:15:37 发布
阅读量222 收藏
点赞数
文章标签： mysql 数据库
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/Lucky666666666666/article/details/105055183
版权

文章目录

如何定位到查询慢的sql语句

  1. 通过慢查询日志定位那些执行效率较低的SQL语句，用–log-slow-queries[=file_name]选项启动时，mysqld写一个包含所有执行时间超过long_query_time秒的SQL语句的日志文件。
    在这里插入图片描述
  2. 慢查询日志在查询结束以后才记录，所以在应用反映执行效率出现问题的时候查询慢查询日志文件并不能定位问题，可以使用show processlist命令查看当前MySQL在进行的线程，包括线程的状态，是否锁表等等，可以实时地查看SQL的执行情况，同时对一些锁表操作进行优化。

用EXPLAIN分析是什么原因造成sql的查询很慢

在这里插入图片描述其中各个字段的含义如下表所示

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

type字段的解释

  1. type=NULL，全表扫描
    在这里插入图片描述

  2. type=index，索引全扫描，MySQL遍历整个索引来查询匹配的行在这里插入图片描述

  3. type=range，索引范围扫描，常见于<、<=、>、>=、between等操作符
    在这里插入图片描述

  4. type=ref，使用非唯一索引扫描或唯一索引的前缀扫描，返回匹配某个单独值得记录行
    在这里插入图片描述

  5. type=eq_ref，类似于ref,但是使用的是唯一索引，对于每一个索引键值，表中只有一条记录匹配，多表连接中使用primary key 或者 unique index作为关联条件。
    在这里插入图片描述

  6. type = const/system，单表中最多一个匹配行，查询起来非常迅速，所以这个匹配行中的其他列的值可以被优化器在当前查询中当做常量来处理，例如，根据主键primary key 或者唯一索引unique index进行的查询
    在这里插入图片描述

  7. type=NULL,MySQL不用访问表或者索引，直接就能得到结果在这里插入图片描述

用索引进行优化

根据上述步骤我们可以确定问题的原因，例如我们确定了对客户表customer的全表扫描导致效率不理想，那么我们可以对客户表的customer的email字段创建索引
使用索引之前查询效果如下
在这里插入图片描述添加索引后在这里插入图片描述

如何使用索引（什么情况下能让索引生效）

在知道我们应该用索引来优化查询之后，我们更进一步地应该了解，什么情况下索引能够生效，索引使用于什么场景之下

  1. 匹配全值
    对索引中所有的列都指定值，例如我们有关于三列的索引出租日期rental_data(idx_rental_date)，库存编号inventory_id(idx_fk_inventory)，客户编号customer(idx_fk_customer_id)
    在这里插入图片描述

  2. 匹配值的范围查询
    对索引的列值可以进行范围查找
    在这里插入图片描述所谓的回表查询值得是：第一次使用idx_fk_customer_id索引查到了主键值，然后再根据主键值来找到对应的数据

  3. 匹配最左前缀
    对于联合索引(col1,col2,col3)而言，能够被col1、(col1 + col2)、(col1 + col2 + col3)查到，但是不能够被col2、(col2 + col3)查询
    在这里插入图片描述关于mysql最左匹配的理解：mysql最左匹配的理解，mysql最左匹配原则

  4. 仅仅对索引进行查询，当查询的列都在索引的字段中，查询效率高
    在这里插入图片描述关于索引覆盖推荐这篇文章：什么是索引覆盖，强力推荐

  5. 匹配列前缀
    在这里插入图片描述
    同理最最左匹配规则的原理

  6. 索引匹配部分精确而其他部分进行范围匹配
    在这里插入图片描述

  7. 如果列名是索引，那么使用column_name is null 就会使用索引
    在这里插入图片描述

参考文章

mysql最左匹配的理解
mysql最左匹配原则
什么是索引覆盖，强力推荐
《深入浅出MySQL》

Richard_or_Ricardo
关注
mysql索引详解
学Java，找哪吒
07-07 10万+
MySQL的存储引擎架构将查询处理与数据的存储/提取相分离
MySQL索引及优化技巧
土味儿
12-24 309
1、Mysql的执行计划 1.1、为什么需要执行计划？ 有的sql语句执行效率高，有的执行效率低，需要对sql语句做调整和优化，所以就会涉及到执行计划 1.2、执行计划是什么？ 执行计划具体来说就是一条sql语句的执行过程 可以看到执行过程中用到了哪些关键的信息，并根据这些信息做判断 1.3、如何使用执行计划？ 就是在sql语句前面加上关键字==explain==，在sql语句前面加上explain之后，它会输出n多个列 explain：美 [ɪkˈspleɪn] 解释;说明;阐明;说明(…的)
Mysql：索引分析及优化
weixin_42155491的博客
04-24 507
索引基本知识 什么是索引 索引是一种数据结构，可以提高查找效率。可以理解为：索引是一个排好序的快速查找数据结构。 我们平时说的索引，没有特殊说明，都是B+Tree结构。 索引实际上也是一张表，保存了主键和索引字段并指向实体表的记录。 创建索引create index [索引名] on [表名] ([列名]) 删除索引drop index [索引名] on [表名] 查看索引show in...
mysql 查询优化 索引 type级别
陈森森的博客
08-10 9646
mysql查询速度优化 这是type级别 越左边 查询速度越快 system>const>eq_ref>ref>range>index>ALL 怎么样才能让type靠左呢 就是建立索引 最左边的俩级 基本达不到 我们让查询type达到第三级和第四级就很好了 为什么这么说呢？ 表主键 唯一索引也只能达到 eq_ref 级别的 如果不做索引的话 肯定都是ALL了… Type级别解释 1.system级别 1.只有一条数据的系统表 2.或衍生表只能有一条数据的主查询 很明显
MySQL的索引是什么？怎么优化？
weixin_33892359的博客
04-01 446
2019独角兽企业重金招聘Python工程师标准>>> ...
mysql优化type要到什么程度_后台需要掌握的mysql优化
weixin_36058866的博客
01-19 314
先从索引结构说：mysql 的索引结构是 B+Tree- 非叶子节点不存储指针；- 叶子节点存储数据；- mysql 的 b+树 叶子节点有双向指针(适合范围查询)；InnoDB主键索引MyISAM索引不同存储引擎 索引结构有点不同，MyISAM 索引(主键和非主键)叶子节点存储记录所在页的指针，这种索引为非聚簇索引；InnoDB 主键索引叶子节点存储完整的数据记录，这种索引为聚簇索引，非主键索引...
mysql索引优化（二）
sunshine151的专栏
12-25 934
这次总结下mysql表关联优化 mysql表关联常见的有两种算法： Nested-Loop Join算法 Block Nested-Loop Join算法 怎样查看是哪种连接？例如t1表10000行记录，t2表100行记录。 sql语句 s select * from t1 inner join t2 on t1.a= t2.a; 添加Explain查看，一般 join 语句中，如果执行计划 Extra 中未出现 Using join buffer 则表示使用的 join 算法是 NL
mysql 索引优化（二）
weixin_43874650的博客
09-10 46
比如 t2 表有1000行记录， join_buffer 一次只能放800行数据，那么执行过程就是先往 join_buffer 里放800行记录，然 后从 t1 表里取数据跟 join_buffer 中数据对比得到部分结果，然后清空join_buffer ，再放入 t2 表剩余200行记录，再 次从 t1 表里取数据跟 join_buffer 中数据对比。所以就多扫了一次 t1 表。straight_join功能同join类似，但能让左边的表来驱动右边的表，能改表优化器对于联表查询的执 行顺序。
04-VIP-Mysql索引优化实战一.pdf
11-30
MySQL索引优化是数据库性能提升的关键环节，本篇主要探讨了几个关于MySQL索引使用和优化的重要知识点。 首先，创建了一个名为`employees`的员工记录表，其中包含`id`（主键）、`name`、`age`、`position`和`hire_...
MySQL索引优化的实际案例分析
12-15
Order by desc/asc limit M是我在mysql sql优化中经常遇到的一种场景，其优化原理也非常的简单，就是利用索引的有序性，优化器沿着索引的顺序扫描，在扫描到符合条件的M行数据后，停止扫描；看起来非常的简单，但是...
mysql中sakila介绍_Sakila—MySQL样例数据库解析
weixin_42303723的博客
02-08 3230
Salila——MySQL样例数据库解析Sakila可以作为数据库设计的参考，下载地址：TGZ格式ZIP格式压缩包包含三个文件：schema.sql、data.sql、sakila.mwbschema.sql文件包含创建Sakila数据库的结构：表、视图、存储过程和触发器data.sql文件包含：1)使用INSERT语句填充数据；2)在初始数据加载后，必须创建的触发器的定义sakila.mwb文件...
MySQL 普通索引191原因
乐杨俊浅谈LAMP
09-04 3156
最近在使用一个超长字段增加普通索引的时候发现后面自动追加了191，这是什么原因呢？ 原因：  单个索引最大长度是767字节，你用的编码是utf8mb4，这个最大可能一个字占4字节（767/4 约等于 191） 。 也就是说字段长度超出了索引最大值能后MySQL 默认在普通索引追加了191...
仿B站一（轮播图的制作）
Richard的博客
03-22 2290
仿B站一（轮播图的制作）前言概述轮播图制作思路html框架布局用js实现滚动效果 从大一的时候就开始用B站，对B站有种深沉的热爱，很想要弄清楚B站是怎么做出来的，最近在学习做网站方面的知识，在此记录一下我开发仿B站过程的点滴。以下是我制作的轮播图的效果 前言概述 从大一的时候就开始用B站，对B站有种深沉的热爱，很想要弄清楚B站是怎么做出来的，最近在学习做 网站方面的知识，在此记录一下我开发仿B站过...
Salila——MySQL样例数据库解析
糖糖糖糖糖先森
11-19 2274
Sakila可以作为数据库设计的参考， 下载地址： http://download.csdn.net/detail/issagui/2181393 sakila-schema.sql 文件包含创建Sakila数据库的结构：表、视图、存储过程和触发器 sakila-data.sql文件包含：1）使用INSERT语句填充数据；2）在初始数据加载后，必须创建的触发器的定义 sakila.mwb文
MySQL进阶【二】—— 一文讲清楚为什么MySQL选择B+树索引
shehuinidaye的博客
09-20 767
MySQL的几种索引结构 https://www.cs.usfca.edu/~galles/visualization/BST.html 数据结构的一个学习网站 B树 B树在MySQL的结构 1、叶子节点具有相同的深度，叶子阶段的指针都为空 2、叶子节点和跟节点，均挂有数据块（因所有节点都挂有数据块，大大降低了索引树存储索引的数量） 3、所有索引不重复 4、节点从左到右是递增的 Hash 1、数组+链表的实现方式（与Java中的hashmap类似） 2、对索引key进行一次hash运算就可以
为什么选择B+树，而不是B树，不是hash索引
Richard的博客
03-22 957
为什么选择B+树，而不是B树，不是hash表数据库索引为什么使用B+树而不是hash索引为什么选择B树（B+树）不选择红黑树为什么是B+树不选择B树参考文章 数据库索引为什么使用B+树而不是hash索引 数据库索引为什么使用B+树而不是hash。 hash表只能匹配是否相等，不能实现范围查找，因为原先是有序的键值，经过哈希算法后，有可能变成不连续的了，就没办法再利用索引完成范围查询检索。 ...
【MySQL】表的操作【有关表结构的操作】【创建、查看、删除、修改表结构】
最新发布
aaa114514aaaa的博客
09-12 1235
对于mysql数据库的表的结构的查看，创建、删除、修改、【对字段的添加、修改】
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值