MySql索引优化

最新推荐文章于 2024-10-02 06:14:43 发布
最新推荐文章于 2024-10-02 06:14:43 发布
阅读量55 收藏
点赞数
文章标签： mysql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/shell_x/article/details/103662571
版权

索引优化

-- 创建两个新表
create table staffs(
    id int primary key auto_increment,
    name varchar(24) not null default "",
    age int not null default 0,
    pos varchar(20) not null default "",
    add_time timestamp not null default CURRENT_TIMESTAMP 
)charset utf8;

-- 插入数据
insert into staffs(`name`,`age`,`pos`,`add_time`) values('z3',22,'manager',now());
insert into staffs(`name`,`age`,`pos`,`add_time`) values('July',23,'dev',now());
insert into staffs(`name`,`age`,`pos`,`add_time`) values('2000',23,'dev',now());

-- 建立索引
create index idx_staffs_nameAgePos on staffs(name,age,pos);

mysql 中CURRENT_TIMESTAMP用于创建时间字段的时候，表示插入数据的时候，该字段默认值为当前时间。ON UPDATE CURRENT_TIMESTAMP。

timestamp是时间戳

如何优化？是改变索引，还是改变查询语句？先做一下测试

-- 查询姓名为july,年龄大于22,pos为dev的信息(我们先不要管语句是否合理)
explain select * from staffs where name='july' and age>22 and pos='dev';
+----+-------------+--------+------------+-------+----------------+----------------+---------+------+------+----------+-----------------------+
| id | select_type | table  | partitions | type  | possible_keys  | key            | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+--------+------------+-------+----------------+----------------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | staffs | NULL       | range | idx_staffs_nap | idx_staffs_nap | 78      | NULL |    1 |    33.33 | Using index condition |
+----+-------------+--------+------------+-------+----------------+----------------+---------+------+------+----------+-----------------------+

explain select * from staffs where name='july';
+----+-------------+--------+------------+------+----------------+----------------+---------+-------+------+----------+-------+
| id | select_type | table  | partitions | type | possible_keys  | key            | key_len | ref   | rows | filtered | Extra |
+----+-------------+--------+------------+------+----------------+----------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | staffs | NULL       | ref  | idx_staffs_nap | idx_staffs_nap | 74      | const |    1 |   100.00 | NULL  |
+----+-------------+--------+------------+------+----------------+----------------+---------+-------+------+----------+-------+

explain select * from staffs where name='july' and age >22;
+----+-------------+--------+------------+-------+----------------+----------------+---------+------+------+----------+-----------------------+
| id | select_type | table  | partitions | type  | possible_keys  | key            | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+--------+------------+-------+----------------+----------------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | staffs | NULL       | range | idx_staffs_nap | idx_staffs_nap | 78      | NULL |    1 |   100.00 | Using index condition |
+----+-------------+--------+------------+-------+----------------+----------------+---------+------+------+----------+-----------------------+
  1. 通过观察key_len，可以看出第一条语句中只用到了复合索引中的name和age，因为单用name的key_len是74，name+age的key_len是78；没有用到pos，因为范围后的索引会失效。
  2. 当我只查询name，或name+age时，索引也没有全部用到，既然建立复合索引，最好是与之相匹配，如复合索引(name,age,pos),那查询条件也按照name…age…pos…去写

优化方案

我们应该根据实际需求选择不同的优化方案，下面提供思路。

-- 将范围改为常量
explain select * from staffs where name='july' and age =22 and pos='dev';

+----+-------------+--------+------------+------+-----------------------+-----------------------+---------+-------------------+------+----------+-------+
| id | select_type | table  | partitions | type | possible_keys         | key                   | key_len | ref               | rows | filtered | Extra |
+----+-------------+--------+------------+------+-----------------------+-----------------------+---------+-------------------+------+----------+-------+
|  1 | SIMPLE      | staffs | NULL       | ref  | idx_staffs_nameAgePos | idx_staffs_nameAgePos | 140     | const,const,const |    1 |   100.00 | NULL  |
+----+-------------+--------+------------+------+-----------------------+-----------------------+---------+-------------------+------+----------+-------+
 
-- age使用范围查找
explain select name,age,pos from staffs where name='july' and age between 23 and 30 and pos='dev'\G

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: staffs
   partitions: NULL
         type: range
possible_keys: idx_staffs_nameAgePos
          key: idx_staffs_nameAgePos
      key_len: 140
          ref: NULL
         rows: 1
     filtered: 33.33
        Extra: Using where; Using index
1 row in set, 1 warning (0.00 sec)

更改需求

-- 在上面索引的基础上，查询年龄大于23的员工信息
explain select * from staffs where age>23;

+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table  | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | staffs | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    3 |    33.33 | Using where |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+

type为all，全表扫描，没有用到索引，之前说过复合索引遵从最左前缀原则，该索引中最左前缀为name，跳过name使用age显然是不行的。

优化方案

-- 不要小菊花，需要什么拿什么
explain select name,age from staffs where age>23;
--或者如下，总之不要小菊花
explain select id from staffs where age>23;

+----+-------------+--------+------------+-------+---------------+-----------------------+---------+------+------+----------+--------------------------+
| id | select_type | table  | partitions | type  | possible_keys | key                   | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+--------+------------+-------+---------------+-----------------------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | staffs | NULL       | index | NULL          | idx_staffs_nameAgePos | 140     | NULL |    3 |    33.33 | Using where; Using index |
+----+-------------+--------+------------+-------+---------------+-----------------------+---------+------+------+----------+--------------------------+

-- 遵照最左原则
mysql> explain select * from staffs where name='july' and age>23;

+----+-------------+--------+------------+-------+-----------------------+-----------------------+---------+------+------+----------+-----------------------+
| id | select_type | table  | partitions | type  | possible_keys         | key                   | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+--------+------------+-------+-----------------------+-----------------------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | staffs | NULL       | range | idx_staffs_nameAgePos | idx_staffs_nameAgePos | 78      | NULL |    1 |   100.00 | Using index condition |
+----+-------------+--------+------------+-------+-----------------------+-----------------------+---------+------+------+----------+-----------------------+

如果我按照name和pos设置条件，根据当前索引跳过了age，索引只会用到name

索引上少计算

explain select * from staffs where name='2000' and age+1=23;
+----+-------------+--------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+-----------------------+
| id | select_type | table  | partitions | type | possible_keys         | key                   | key_len | ref   | rows | filtered | Extra                 |
+----+-------------+--------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+-----------------------+
|  1 | SIMPLE      | staffs | NULL       | ref  | idx_staffs_nameAgePos | idx_staffs_nameAgePos | 74      | const |    1 |   100.00 | Using index condition |
+----+-------------+--------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+-----------------------+

没啥大问题，就是本身的索引和后面的索引会失效，但可以使用聚合函数（需要改一下索引）。

使用不等于(!= or <>)、空值、or后面的索引也会失效

varchar类型一定要带引号

不带引号会怎么样呢？表面上不会怎样，依然可以查询出来，因为mysql会做隐式类型转换，看着好像没啥，这样的语句写多了是要翻车的；隐式转换不仅会影响性能（转换也要耗费资源的，且很难发现这种错误），还会带来安全问题。

模糊查询%写最右

explain select * from staffs where name like '%ju%' and age=23;

+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table  | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | staffs | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    3 |    33.33 | Using where |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+

-- 上面的解决方案，把j前的%去掉就可以了
explain select * from staffs where name like 'ju%' and age=23;
+----+-------------+--------+------------+-------+-----------------------+-----------------------+---------+------+------+----------+-----------------------+
| id | select_type | table  | partitions | type  | possible_keys         | key                   | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+--------+------------+-------+-----------------------+-----------------------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | staffs | NULL       | range | idx_staffs_nameAgePos | idx_staffs_nameAgePos | 78      | NULL |    1 |    33.33 | Using index condition |
+----+-------------+--------+------------+-------+-----------------------+-----------------------+---------+------+------+----------+-----------------------+

本文中介绍的是思想，上面有很多不合理的地方，有些问题改索引是更好的解决方案。

练习

假设index(a,b,c)

where语句索引是否被使用到
where a = 3Y,使用到a
where a = 3 and b = 5Y,使用到a,b
where a = 3 and b = 5 and c = 4Y,使用到a,b,c
where b = 3 或 where b = 3 and c = 4 或 where c = 4N
where a = 3 and c = 5使用到a,c没有被使用,b中间断了
where a = 3 and b > 4 and c = 5使用到了a,b
where a = 3 and b like ‘kk%’ and c = 4使用到了a,b,c
where a = 3 and b like ‘%kk’ and c = 4使用到了a
where a = 3 and b like ‘%kk%’ and c = 4使用到了a
shell-x
关注
mysql索引详解
学Java，找哪吒
07-07 10万+
MySQL的存储引擎架构将查询处理与数据的存储/提取相分离
MySQL索引性能优化分析
热门推荐
张彦峰的博客
04-07 173万+
总结MySQL索引性能优化，主要集中在索引的背景介绍以及索引的高效创建、使用和查询等，最有给出除了索引的一些其他优化思路
MySQL索引优化
mz474920631的博客
06-16 1万+
这里说一下几种常见优化索引的方法：前缀索引顾名思义就是使用某个字段中字符串的前几个字符建立索引，那我们为什么需要使用前缀来建立索引呢？使用前缀索引是为了减小索引字段大小，可以增加一个索引页中存储的索引值，有效提高索引的查询速度。在一些大字符串的字段作为索引时，使用前缀索引可以帮助我们减小索引项的大小。不过，前缀索引有一定的局限性，例如：覆盖索引是指 SQL 中 query 的所有字段，在索引 B+Tree 的叶子节点上都能找得到的那些索引，从二级索引中查询得到记录，而不需要通过聚簇索引查询获得，可以避免回表
mysql 索引优化
GuoWangWang11的博客
07-12 3865
mysql索引优化
mysql索引优化
LJJ1338的博客
09-10 2021
SQL如下： CREATE TABLE IF NOT EXISTS staffs( id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(24) NOT NULL DEFAULT "" COMMENT'姓名', age INT NOT NULL DEFAULT 0 COMMENT'年龄', pos VARCHAR(20)...
MySQL索引优化总结
何哥的博客
09-30 7713
前言：相信大家都知道索引可以大大提高MySQL的检索速度，但是真正在平时工作中写SQL，真的会考虑到这条SQL如何能够用上索引提升执行效率？本篇博客详细的介绍了索引优化的20个原则，只要在工作中能够随时应用到，相信你写出的SQL能够命中索引，效率杠杠的。 一、索引的分类 1.1、普通索引、主键索引和唯一索引 1.2、聚集索引和非聚集索引 1.3、联合索引最左匹配原则 1.4、索引覆盖和回表 1.5、前缀索引 1.6、索引合并 1.7、索引下推 二、索引优化规则 查询的条...
MySQL索引优化（超详细）
weixin_55816781的博客
05-10 7440
MySQL索引优化，涉及到索引介绍，性能分析，查询优化，慢查询日志。
mysql索引优化助记口诀
张步云的专栏
12-25 3017
口诀 不等not null还有or，索引全中也无效； 大等小等is null，全索命中才有效； 除此全匹最高效，最左匹配要记牢； 大小跳索百分号，后面用索也无效； 索无函数和计算，才能保证索有效。 解释 sql中只要包含了!= not null or ，会导致sql中全部索引失效。但是 is null >= <= 不会。 >= <= is null只会有两种情况：只有在命中了全部索引，也就是组合索引中的索引全用到了才会都有效并且是全部有效，否则就全无效。 组合索引中除了上面的特例外，
【MySQL】深入理解MySQL索引优化器原理（MySQL专栏启动）
小明的Java问道之路
11-08 8642
本文将解读MySQL数据库查询优化器（CBO）的工作原理。简单介绍了MySQL Server的组成，MySQL优化器选择索引额原理以及SQL成本分析，最后通过 select 查询总结整个查询过程。
MySQL索引优化的几种方式
weixin_45750855的博客
11-11 4886
索引高性能优化 1. 使用独立的索引列 ​ 错误的查询语句会使得索引无效… select user_id from user where user_id-1=4; 第一个例子中，对索引列user_id进行了一次方程运算，然后进行查询，然而这样的操作MySQL无法自动识别，导致主键索引失效了。 select date_col from t where TO_DAYS(CURRENT_DATE) -TO_DAYS(data_col)<=15; select date_col from t where
04-VIP-Mysql索引优化实战一.pdf
11-30
MySQL索引优化是数据库性能提升的关键环节，本篇主要探讨了几个关于MySQL索引使用和优化的重要知识点。 首先，创建了一个名为`employees`的员工记录表，其中包含`id`（主键）、`name`、`age`、`position`和`hire_...
MySQL索引优化的实际案例分析
09-10
MySQL索引优化是数据库性能提升的关键环节，特别是在处理大量数据时，正确的索引策略能够显著减少查询时间，提高系统响应速度。本文将通过实际案例深入探讨如何优化包含`ORDER BY DESC/ASC LIMIT M`的SQL语句。 ...
docker容器mysql数据备份 mysql容器无法启动备份数据
zhangxu1998lq的博客
09-30 321
mysql是旧容器名 因为mysql的配置文件my.cnf的路径没有映射到服务器，我修改了配置文件，配置文件有错误导致，容器启动不了，一直restarting。nmysql是新容器名 mysql是旧容器名 mysql:5.7是我的镜像名。新容器正常启动，旧容器的数据都还在。
Python 3 和 MySQL(PyMySQL) 的完美结合
最新发布
wjs2024的博客
10-02 471
PyMySQL 是一个纯 Python 编写的 MySQL 客户端库，它提供了与 MySQL 数据库进行交互的接口。PyMySQL 设计轻量、易于安装和使用，且完全兼容 MySQLdb 接口，使得从 MySQLdb 迁移变得非常简单。Python 3 和 PyMySQL 库的结合为开发人员提供了一种高效、简洁的方式来与 MySQL 数据库进行交互。通过本文的介绍，您应该能够理解如何安装 PyMySQL、建立数据库连接、执行 SQL 语句、处理查询结果以及处理可能出现的错误。
滚雪球学MySQL[1.3讲]：MySQL客户端工具：详解与实践
**My Coding Family**
10-01 1321
在上期文章中，我们深入探讨了MySQL的安装与配置，这为我们顺利使用MySQL打下了基础。具体来说，我们介绍了如何正确安装MySQL、配置环境变量、调整字符集，并确保数据库可以正常运行。这些基础配置是后续操作的前提，理解并掌握这些技能能够帮助你在未来的开发工作中减少不必要的麻烦。然而，安装与配置仅仅是迈出了使用MySQL的第一步。真正的工作是如何高效地与数据库交互，并对数据进行增删改查。这时候就需要借助强大的MySQL客户端工具。
基于SpringBoot+Vue+MySQL的考勤管理系统
程序员大金的博客
09-30 954
基于SpringBoot+Vue+MySQL的考勤管理系统，附源码。
MySQL索引优化深度解析
本文主要探讨了MySQL如何使用索引以及索引优化的策略，涵盖了索引的分类、MyISAM和InnoDB存储引擎的索引结构、索引的优缺点以及设计原则。 在MySQL中，索引对于提高查询速度至关重要。它们可以显著提升全值匹配和...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值