Mysql索引优化（笔记）

最新推荐文章于 2024-08-07 23:56:08 发布
最新推荐文章于 2024-08-07 23:56:08 发布
阅读量615 收藏
点赞数
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/qq_15640665/article/details/109184066
版权

文章目录

EXPLAIN

explain查询计划，用来查看sql是否使用了索引，使用了多少索引，排序方式等。

创建表

  • actor表
CREATE TABLE `actor` (
  `id` int(11) NOT NULL,
  `name` varchar(45) DEFAULT NULL,
  `update_time` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  • emplyee表
CREATE TABLE `employees` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(24) NOT NULL DEFAULT '' COMMENT '姓名',
  `age` int(11) NOT NULL DEFAULT '0' COMMENT '年龄',
  `position` varchar(20) NOT NULL DEFAULT '' COMMENT '职位',
  `hire_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '入职时间',
  PRIMARY KEY (`id`),
  KEY `idx_name_age_position` (`name`,`age`,`position`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8 COMMENT='员工记录表';
  • film_actor表
CREATE TABLE `film_actor` (
  `id` int(11) NOT NULL,
  `film_id` int(11) NOT NULL,
  `actor_id` int(11) NOT NULL,
  `remark` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_film_actor_id` (`film_id`,`actor_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  • film表

 CREATE TABLE `film` (
	 `id` INT ( 11 ) NOT NULL AUTO_INCREMENT,
	 `name` VARCHAR ( 10 ) DEFAULT NULL,
	 PRIMARY KEY ( `id` ),
 KEY `idx_name` ( `name` )
) ENGINE = INNODB DEFAULT CHARSET = utf8;

explain字段解释

列含义
id有几个select就有几个id，id越大执行优先级越高，从上往下执行
select_type对应简单还是复杂查询
simple:简单查询。不包含子查询
primary:包含在select中的子查询
derived:包含在from语句中的子查询。结果放在一个临时表
subquery:在select中的子查询，不在from中
table表示正在查询的表；
1.有子查询时，<derivenN> 中N代表依赖id=N的查询
2.有union时，<union1,2> 表示id为1和2参与了union
type访问类型。执行效率system>const>eq_ref>ref>range>index>all,一般来讲查询需要达到range，尽量达到ref。字段详解
possible_keys可能需要用到的索引。如果此列有值，而key显示null，则表示当前表数据量比较少，全表索引速度会更快。如果该列为null，可以通过where看是否能创造适当的索引来提高性能。
key此列查看sql最终使用的哪个索引来查询
key_len用到的索引的字节数。具体计算规则
ref这一列显示了在key列记录的索引中，表查找值所用到的列或常量，常见的有：const（常量），字段名（例：film.id）
rows预计需要扫描的行数
extraextra详细

type字段详细解释

  1. NULL:一般在查询阶段不需要访问表，直接查索引即可。例如查询列最小值id的列在这里插入图片描述
    在这里插入图片描述

  2. const,system：一般查询条件可以确定为一个常量，与primary key和unique key的列进行比较，因为只有一个值，所以只会匹配一行，读取速度非常快。在这里插入图片描述
    在这里插入图片描述

  3. eq_ref:unique key或primary key索引的被连接使用，最多只会返回一条符合条件的记录在这里插入图片描述
    在这里插入图片描述

  4. ref:相比eq_ref，不使用唯一索引，使用普通索引或者唯一索引部分前缀，可能会找到多个符合的记录在这里插入图片描述
    在这里插入图片描述

  5. range:一般表示范围查找，in，between，<,>等操作在这里插入图片描述
    在这里插入图片描述

  6. index:扫描索引就能拿到结果（不需要回表），一般为覆盖索引（查询字段中包含了where的条件，且where条件符合走索引的规范）

  7. ALL：全表扫描，不走索引。这个一般会根据mysql的底层算法判断应该走索引还是全表。所以有时候走全表速度不一定比走索引慢

type_len计算规则

  • 字符串，char(n)和varchar(n)在5.0.3之后n代表字符数，不是字节数，utf-8一个汉字3个字节。
  1. char(n)：3n
  2. varchar(n)：3n+2 (2个字节为储存字符串长度)
  • 数值
  1. tinyint：1
  2. smallint：2
  3. int：4
  4. bigint： 8
  • 时间
  1. date：3
  2. timestamp:4
  3. datetime:8
  • 如果字段允许为null，还额外需要1字节保存是否为null。

extra详细

  1. Using index覆盖索引:select查询的字段都在where的索引字段中，只用通过索引就能拿到结果而不用回表查。
explain select film_id from film_actor where film_id = 1

在这里插入图片描述

  1. Using where:与Using index相反，查询的字段没有被where的索引覆盖。
explain select * from film_actor where actor_id = 1

在这里插入图片描述

  1. Using index condition：select查询的列没有完全被where的索引覆盖，where条件是一个前导列的范围
explain select * from film_actor where film_id > 3

在这里插入图片描述

  1. Using temporary：mysql需要建立一张临时表来处理。一般情况需要优化
  • 查询actor表，actor表没有name索引
EXPLAIN SELECT DISTINCT name from actor

在这里插入图片描述

  • 查询film表，film表有name索引
EXPLAIN SELECT DISTINCT name from film

在这里插入图片描述
5. Using filesort:使用外部排序不走索引排序，数据比较少时会从内存中排序，否则在磁盘中完成排序，一般也是需要优化。

  • actor表
EXPLAIN SELECT name from actor ORDER BY name

在这里插入图片描述

  • film表
EXPLAIN SELECT name from film ORDER BY name

在这里插入图片描述

索引优化

索引的底层数据结构

在这里插入图片描述
默认使用INNODB引擎。INNODB下，主键索引和数据使用b+tree数据结构组织在一个文件当中，多级索引则是单独一个文件，这里我们使用一个二级索引。

索引失效的几种情况

假设有index(a,b,c)

where语句                                        索引使用情况
a=1使用了a
a=1 and b=2使用了a，b
a=1 and b=2 and c=3使用了a,b,c
a=1 and c=3使用了a(b中断)
a=1 and b>2 and c=3使用了a,b
a=1 and b>=2 and c=3使用了a,b,c。个人认为使用=号更能确定范围，而使用>不能确定范围，mysql底层会认为纯粹使用>这样不确定的范围会走全表扫描效率比较高
b=2 或 b=2 and c=3 或c=3不使用
like这里总结一下like，like和>= 和<=类似，所以凡是like ‘k%’ 这样k开头的会走索引，还有一种情况’k%kk%’，这里使用了索引下推,最终也会走索引。
  1. employee表，全值索引
explain select * from employees where name = 'LiLei'

在这里插入图片描述

explain select * from employees where name = 'LiLei' and age = 10

在这里插入图片描述

explain select * from employees where name = 'LiLei' and age = 10 and position = 'sd'

在这里插入图片描述

  1. 最左前缀原则：指where条件语句中，需要按照索引的顺序来查询，且不能跳过中间某一个索引

  2. 不在索引列上做任何操作（计算、函数、（自动or手动）类型转换），会导致索引失效而转向全表扫描

EXPLAIN SELECT * FROM employees WHERE left(name,3) = 'LiLei';

在这里插入图片描述
4. 不能使用索引中范围条件右边的列，但是注意，使用>=可以走索引

 EXPLAIN SELECT * FROM employees WHERE name= 'LiLei' AND age > 22 AND position ='xxx';
  EXPLAIN SELECT * FROM employees WHERE name= 'LiLei' AND age >= 22 AND position ='manage r';

在这里插入图片描述
在这里插入图片描述
5. 尽量使用覆盖索引
6. mysql在使用不等于（！=或者<>），not in ，not exists 的时候无法使用索引会导致全表扫描 < 小于、 > 大于、 <=、>= 这些，mysql内部优化器会根据检索比例、表大小等多个因素整体评估是否使用索引
7. is null,is not null 一般情况下也无法使用索引
8. like以通配符开头（’$abc…’）mysql索引失效会变成全表扫描操作，但是使用覆盖索引可以使用到索引，但是后面的索引会失效。

索引下推

EXPLAIN SELECT * 
FROM employees 
WHERE name like 'LiLei%' AND age = 22 AND position ='manager';

Mysql5.6以前这条查询语句会回表查找到所有like 'LiLei%'的数据，然后再通过age 和 position进行过滤。之后会每拿到一个 like ‘LiLei%’ 这样的数据就在内存中和后面的条件进行比对，所以这里依旧会用到索引（这里like如果和后面两个条件顺序打乱依旧会走索引）。

in和or

in

在这里插入图片描述
在这里插入图片描述

还有一种情况，这里我复制一张employees_copy1表，和employee表结构一样，里面有10w条数据，在数据量比较大的情况也会走索引
在这里插入图片描述

or

在这里插入图片描述
在这里插入图片描述
与in一样，在数据量大的情况的下会走索引
在这里插入图片描述

order by 和 group by的优化

###    name匹配为 =   ################
##只要order by按最左前缀则不会出现using filesort
explain select * from employees where name = 'LiLei' ORDER BY age,position;

## 有using filesort
explain select * from employees where name = 'LiLei' ORDER BY position,age

## 有using filesort
explain select * from employees where name = 'LiLei' ORDER BY name,position

##当有两个及以上的索引在where条件中=，并且排序也是使用索引，排序都会using index。
explain select * from employees where  name = 'LiLei' and age = 10 order by age,position,name

##范围查找会using filesort
explain select * from employees where  name > 'LiLei' order by name

##使用覆盖索引可以using index
explain select name from employees where  name > 'LiLei' order by name

总结

  1. 排序有两种方式，using filesort和using index，效率index>filesort
  2. order by满足的几种条件会使用索引排序
  • where中出现两个条件使用=时，order by后面（只包含索引）顺序，排序怎么样都会走索引
  • 范围查找时需要使用覆盖索引走索引排序
  • where和order需要满足最左前缀原则

filesort

filesort使用临时文件进行排序，因为涉及到磁盘IO，因此出现fielsort的情况下都是需要优化sql的。

  • 单路排序：一次性取出所有满足条件的字段，在sort buffer排序。
  • 双路排序：取出需要排序的字段和能定位数据行的id（主键，或者rowid），在sort buffer排好序后再回表把所有字段返回。

Mysql通过比较系统变量max_length_for_sort_data(默认1024字节)来区分使用单路或者双路，
max_length_for_sort_data <排序字段，单路
max_length_for_sort_data >排序字段，双路

分页查询优化

  1. 分页查询的sql
##查询时间0.078s
SELECT * from employees ORDER BY name LIMIT 20000,20;

##查询时间0.036
select * from employees e INNER JOIN (select id from employees order by name limit 20000,20) tmp on tmp.id = e.id

在这里插入图片描述

在这里插入图片描述

因为第一条sql中，根据name排序数据量太大，mysql算法全表扫描会比索引快(需要回表查的时间啊成本更高)，所以不会走索引。此时应该将数据量大的查询返回尽可能少的字段，走索引，然后关联查询。

qq_15640665
关注
专栏目录
Django如何自定义model创建数据库索引的顺序
12-26
首先这是一个先有鸡还是先有蛋的问题，大部分时候数据都来自excel的整理，当数据越来越多，需要分析的点也越来越多的时候，通过excel来管理显然有些吃力了。 这时候就需要将excel导入到数据库中，然而model创建的索引并非是我们编写时候的顺序，特别是当存在 models.ForeignKey 时，我们必须要先创建 ForeignKey的class，最后再创建总表。 为了保证数据库的索引跟excel的索引一致，我们需要对model中的索引字段做一系列的处理（可能这是一个野路子）。 首先当然是先创建好ForeignKey，这部分class要写在前面，否则总表的class对于引用的Foreign
django models索引_Django Models中索引和联合索引的建立使用
weixin_39537397的博客
12-20 263
索引在数据库中有很重要的地位，它可以极大的提高数据的查询速度，但是过多的索引也会降低插入、删除、更新表的速度。因为在执行这些写操作时，还要操作索引文件。Django框架Model中如何建立索引？例如这样一张购物表：class Cart(models.Model):user = models.ForeignKey(MyUser,verbose_name="用户")goods = models.For...
Django 学习笔记之模型高级用法
weixin_40646872的博客
10-05 143
如果 abstract = True，模型会指定为抽象模型。它相当于面向对象编程中的抽象基类。
MySQL查询执行计划详解（EXPLAIN）
qq_25046827的博客
08-06 3064
如果被驱动表非常大，多次访问被驱动表可能导致很多次的磁盘IO（因为默认情况下是驱动表没找到一条记录就去查询被驱动表，如果被驱动表很大，那么可能会导致被驱动表前面引入的页面在后面被淘汰了，那么驱动表下一条记录进行连接时又要重新去磁盘中加载），因此引入join buffer，一次性读取多条驱动表记录放入其中。每个表都维护了一个变量，记录着对该表进行增删改的记录条数，**如果发生改动的记录数量超过了表大小的10%，并且自动重新计算统计数据的功能是打开的，那么服务器会重新计算一次统计数据。
django Model 建立联合Unique索引
DRC's CSDN
06-16 1456
类来创建联合唯一索引。这个类允许你在一个或多个字段上创建唯一约束，以确保这些字段的组合值在数据库表中是唯一的。如果尝试创建重复的组合值，Django会引发。注意：在使用联合唯一索引时，如果某个字段允许为。参数，我们创建了一个名为。在Django中，可以使用。通过将这两个字段传递给。，否则将会引发完整性错误。这将确保在数据库表中，
mysql优化笔记+资料
11-18
以下是一份详细的MySQL优化笔记，涵盖了多个方面： 一、查询优化 1. 使用索引：为经常用于搜索的列创建索引可以显著加快查询速度。B树和哈希索引是最常见的类型，适用于不同的查询场景。 2. 避免全表扫描：尽量使用...
mysql优化笔记.doc
10-22
MySQL数据库的优化是一个涵盖多个方面的复杂任务，旨在提高性能、减少资源消耗并确保系统的稳定性。以下是一些关键的优化策略： 1. **表的设计合理化（3NF）**： - **第一范式（1NF）**：确保每一列都具有原子性，...
MySQL学习笔记：索引优化
01-14
MindManager脑图，纯自己写的一些感想，如有不当，请各位老师多多指点！真心感谢！
MySQL优化文档笔记
05-04
MySQL优化是数据库管理中至关重要的任务，涉及到多个层面，包括SQL语句的编写、系统配置、存储引擎选择以及数据类型优化等。以下是对这些关键点的详细解释： 首先，MySQL Server的核心组件包括连接器、分析器、优化...
MySQL性能优化课程笔记
10-07
以下是对"MySQL性能优化课程笔记"中可能涵盖的重要知识点的详细解释： 1. **索引优化**：索引是提升查询速度的关键。学习如何正确创建和使用主键、唯一索引、全文索引、多列索引和空间索引，以及如何避免索引失效和...
Django 模型设置索引
rongDang的博客
03-31 3288
Django 模型设置索引
Python后台开发基础——模型类实现
学习服务
09-22 1558
1 ORM原理与数据库配置 1.1 模块安装 代码部分： pip install pymysql pip install mysqlclient 1.2 模块安装常见问题 time out 超时解决方案: pip install 包名 --user-i https://pypi.tuna.tsinghua.edu.cn/simple 缺少c ++ 插件解决方案:https://www.jb51.net/article/151033.htm 1.3 Pycharm连接 Mysql 保证pych
django-数据库访问优化-建立数据库的索引
adminwg的博客
10-27 2590
django-数据库访问优化-建立数据库的索引 在必要的字段上创建索引，有助于提升我们的查询速度，django提供了两种为字段创建索引的方式。
Django 模型（model）
Linxiaokang_1212的博客
11-19 7442
在当代 Web 应用中，主观逻辑经常牵涉到与数据库的交互。数据库驱动网站在后台连接数据库服务器，从中取出一些数据，然后在 Web 页面用漂亮的格式展示这些数据。
MySQL优化攻略：利用常量表提升数据库性能
最新发布
zgt_certificate的博客
08-07 411
MySQL中的常量表和表数据的访问方式是优化器进行查询优化的重要部分。通过使用合适的索引和扫描策略，MySQL能够极大提高查询性能，确保数据库的高效运行。在MySQL中，常量表指的是那些读取表时行数明确为零或一行的数据表。MySQL对索引扫描进行了更细化的分类，这使得优化器能选择最佳的扫描方式，计算出更精确的代价估算。: 这种表通常用于优化查询，因为其数据是固定的，因此对查询性能影响极小。: 在查询条件不包含索引字段或没有索引时，MySQL会进行全表扫描。: 不使用索引，而是直接顺序扫描整个表的数据。
django models索引_Django 模型 models详解
weixin_30677191的博客
11-30 370
前言 模型是关于您的数据的单一的、确定的信息来源。它包含您存储的数据的基本字段和行为。通常，每个模型映射到单个数据库表。基础知识:每个模型都是一个 Python 的类，这些类继承 django.db.models.Model模型类的每个属性都相当于一个数据库的字段。综上诉说，Django 给你一个自动生成访问数据库的 API一旦定义了模型，你需要告诉Django你将会使用这些模型。通过编辑你的设置...
Django 2.1.3 模型层 索引
百年da孤独 的专栏
12-10 783
索引Index 选项fieldsnamedb_tablespace 索引类可轻松创建数据库索引。可以通过Meta.indexes选项添加它们 。本文档解释了索引的API引用和index 选项。 引用内置索引 索引是在 django.db.models.indexes 中定义的，但为方便起见，它们被导入到 django.db.models。标准约定是使用from django.db import m...
sql 列转行_Effective SQL：编写高质量SQL语句的61个有效方法
weixin_39949413的博客
12-06 191
《Effective SQL：编写高质量SQL语句的61个有效方法》，机械工业出版社出版，作者：[法] 约翰· L. 维卡斯 著，文浩译。本书结合SQL实践与实际案例，整合了实用的解决方案与洞见，可帮助大家解决复杂的问题，设计出能简化数据管理的数据库。作者拥有担任世界数据库顾问与讲师的丰富经验，在本书中归纳了编写高质量SQL语句的61个具体方法，并提供不同SQL版本的差异详情，无论你使用...
Mongodb 查询重复数据
qq_41236493的博客
08-09 4673
# 方法一 db.集合名称.aggregate( {'$group':{ '_id': {'查询重复字段名': '$查询重复字段名'}, 'uniqueIds': {'$addToSet': '$_id'}, 'count' : {'$sum': 1} }}, {'$match': {...
MySQL存储引擎与索引优化笔记
"MySQL笔记-fxf，记录了MySQL的学习与复习要点，主要关注存储引擎、索引优化和性能调整。" 在MySQL数据库系统中，存储引擎是数据库管理数据的核心组件，不同的存储引擎有不同的特性和功能。InnoDB和MyISAM是最常见...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值