MySQL索引优化

已于 2023-07-26 14:46:32 修改
阅读量206 收藏
点赞数
分类专栏： Mysql 文章标签： mysql 数据库
于 2023-07-26 13:21:06 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/Ascend1977/article/details/131935758
版权

        MySQL索引优化是提高数据库查询性能的重要步骤。索引是一种数据结构，用于加速数据库表的数据检索操作。当数据库表中的数据量增加时，索引的优化变得尤为重要，因为它可以减少查询的执行时间，从而提高系统的响应速度。

MySQL索引优化的建议和技巧 

联合索引尽量覆盖条件

        比如可以设计一个或者两三个联合索引(尽量少建单值索引)，让每一个联合索引都尽量去包含sql语句里的where、order by、group by的字段，还要确保这些联合索引的字段顺序尽量满足sql查询的最左前缀原则。

不要在小基数字段上建立索引

        索引基数是指这个字段在表里总共有多少个不同的值，比如一张表总共100万行记录，其中有个性别字段，其值不是男就是女，那么该字段的基数就是2。

        如果对这种小基数字段建立索引的话，还不如全表扫描了，因为你的索引树里就包含男和女两种值，根本没法进行快速的二分查找，那用索引就没有太大的意义了。一般建立索引，尽量使用那些基数比较大的字段，就是值比较多的字段，那么才能发挥出B+树快速二分查找的优势来。

长字符串采用前缀索引

        尽量对字段类型较小的列设计索引，比如说什么tinyint之类的，因为字段类型较小的话，占用磁盘空间也会比较小，此时你在搜索的时候性能也会比较好一点。当然，这个所谓的字段类型小一点的列，也不是绝对的，很多时候你就是要针对varchar(255)这种字段建立索引，哪怕多占用一些磁盘空间也是有必要的。对于这种varchar(255)的大字段可能会比较占用磁盘空间，可以稍微优化下，比如针对这个字段的前20个字符建立索引，就是说，对这个字段里的每个值的前20个字符放在索引树里，类似于 KEY index(name(20),age,position)。

        此时你在where条件里搜索的时候，如果是根据name字段来搜索，那么此时就会先到索引树里根据name字段的前20个字符去搜索，定位到之后前20个字符的前缀匹配的部分数据之后，再回到聚簇索引提取出来完整的name字段值进行比对。但是假如你要是order by name，那么此时你的name因为在索引树里仅仅包含了前20个字符，所以这个排序是没法用上索引的，group by也是同理。

where与order by冲突时优先where

        在where和order by出现索引设计冲突时，到底是针对where去设计索引，还是针对order by设计索引？到底是让where去用上索引，还是让order by用上索引?一般这种时候往往都是让where条件去使用索引来快速筛选出来一部分指定的数据，接着再进行排序。因为大多数情况基于索引进行where筛选往往可以最快速度筛选出你要的少部分数据，然后做排序的成本可能会小很多。

常见索引优化

全值匹配

        分析：全值匹配 ，就是对索引中的所有列指定具体值，这种SQL一般效率都比较高，访问类型是ref级别，如果是联合索引就尽量把索引列都指定具体值，这样效率更高一些，因为区分度会更高。


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

最左前缀法则

        分析：如果索引了多列，要遵守最左前缀法则，指的是查询从索引的最左前列开始并且不跳过索引中的列。最左前缀原则是针对联合索引的，它的底层是一个B+树，但键值数是大于1的，而构建一个B+树就只能根据一个键值来进行，所以数据库依据联合索引最左的字段来构建B+树，是按照联合索引从左到右排好序的，如果跳过前面的，这样后面的就不是有序的了，查询就需要全表扫描。


EXPLAIN SELECT * FROM employees WHERE name ='Bill' and age =31;
EXPLAIN SELECT * FROM employees WHERE age =30 AND position ='dev';
EXPLAIN SELECT * FROM employees WHERE position ='manager';

不在索引列上做操作

        分析：计算、函数等操作可能会导致索引失效而转向全表扫描，比如下面这个SQL截取name左边的三位，这样就不是有序了，走不了索引。


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

不能使用索引中范围条件右边的列

        分析：在联合索引中，如果前面索引列使用了范围，那么后面的索引列就走不了索引，这是因为在索引树中前面的是范围，后面的就不一定是有序的。


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

尽量使用覆盖索引，减少 select * 语句

        分析：查询的时候指明具体的字段，尽量被联合索引覆盖掉，如果要查的是全部列的话，数据量又特别大，可以考虑使用搜索引擎。

select * 的坏处：

  • 使用 * 号查询，会查询出多个我们不需要的字段，增加sql执行的时间，同时大量的多余字段，会增加网络开销。

  • 失去MySQL优化器“覆盖索引”策略优化的可能性。

  • 对于无用的大字段，如 varchar、blob、text，会增加 io 操作。


EXPLAIN SELECT name``,age ``FROM employees ``WHERE name``=``'LiLei'AND age =  23``AND position =``'manager'``;

尽量不使用!=，not in ，not exists

        分析：因为这些操作无法使用索引可能会导致全表扫描 ，!=的结果集可能会很大，走索引也和全表扫描差不多，这样MySQL优化器会根据检索比例、表大小等多个因素整体评估是否使用索引。


EXPLAIN SELECT * FROM employees WHERE name !=``'LiLei'``;

is null,is not null 一般也无法使用索引

        分析：虽然这个字段是索引字段，但是也可能不会走索引，对于null的字段，在索引树中会集中起来处理，统一在左端或者右端。


EXPLAIN SELECT * FROM employees WHERE name is``null``;

like不要以%开头

        分析：like以通配符开头（’%Lei’）MySQL索引失效会变成全表扫描操作，%号在前就意味着前面还有很多其他的字符串，跳过这些字符串在整个索引树里面就不是有序的了，定位不到，没办法用索引；%号在后等于是用了%号前面的字符串，这些字符串在整个索引树里面是有序的，所以能走索引。


EXPLAIN SELECT * FROM employees WHERE name like'%Lei';
EXPLAIN SELECT * FROM employees WHERE name like'Lei%';

问题：解决like’%字符串%’索引不被使用的方法？

  • 使用覆盖索引，查询字段必须是建立覆盖索引字段。


EXPLAIN SELECT name,age,position FROM employees WHERE name like '%Lei%'``;

  • 如果不能使用覆盖索引则可能需要借助搜索引擎

字符串不加单引号索引失效

        分析：字段类型和值的类型要一致，如果不一致，MySQL有可能会做一个转换，但是有时候也不一定会转换，这样的话就会导致索引失效。


EXPLAIN SELECT * FROM employees WHERE name ='1000';
EXPLAIN SELECT * FROM employees WHERE name =  1000;

少用or或in

        因为用它查询时，MySQL不一定使用索引，MySQL内部优化器会根据检索比例、表大小等多个因素整体评估是否使用索引。


EXPLAIN SELECT * FROM employees WHERE name = 'LiLei' or name = 'HanMeimei';

 特殊例子

  • 联合索引第一个字段用范围不会走索引


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

                分析：联合索引第一个字段就用范围查找不会走索引，MySQL内部可能觉得第一个字段就用范围，结果集应该很大，回表效率不高，还不如就全表扫描。

  • 强制走索引


EXPLAIN SELECT * FROM employees forceindex(idx_name_age_position) WHERE name >``'LiLei'AND age =``22 AND position =``'manager'``;

        分析：虽然使用了强制走索引让联合索引第一个字段范围查找也走索引，扫描的行rows看上去也少了点，但是最终查找效率不一定比全表扫描高，因为回表效率不高。

测试：


--  执行时间:0.351
SELECT * FROM employees WHEREname >'LiLei';
--  执行时间：0.7333s
SELECT * FROM employees forceindex(idx_name_age_position) WHERE name >'LiLei';

  • in和or在表数据量比较大的情况会走索引，在表记录不多的情况下会选择全表扫描


EXPLAIN SELECT * FROM employees 
WHERE name in  ('LiLei','HanMeimei','Lucy') 
AND age =22 AND position = 'manager';

EXPLAIN SELECT * FROM employees 
WHERE (name ='LiLei'orname ='HanMeimei') 
AND age =22 AND position = 'manager';

--  做一个小测试，将employees表复制一张employees_copy的表，里面保留两三条记录。
EXPLAIN SELECT * FROM employees_copy WHERE name in  ('LiLei','HanMeimei','Lucy') AND age =22AND position ='manager';


EXPLAINS ELECT * FROM employees_copy WHERE (name ='LiLei'orname ='HanMeimei') AND age =22 AND position ='manager';

        分析：在这种情况下数据量小的时候全表扫描比走索引可能还要快一点，所以不会走索引；当数据大的时候，走索引比全表扫描肯定会快一点，所以会走索引。

深入优化

order by优化

示例一：


EXPLAIN SELECT * FROM employees 
WHERE name=``'LiLei' and position=``'dev' ORDER BY age;

        分析：利用最左前缀法则：中间字段不能断，因此查询用到了name索引，从key_len=74也能看出，age索引列用在排序过程中，因为Extra字段里没有Using filesort。

示例二：


EXPLAIN SELECT * FROM employees WHERE age=``22 ORDER BY name,position;

        分析：从Explain的执行结果来看：key=null，没有走索引，出现了Using filesort，这是因为不符合最左前缀的原则。

优化总结：

        order by满足两种情况会使用Using index，order by语句使用索引最左前列；使用where子句与order by子句条件列组合满足索引最左前列，如果order by的条件不在索引列上，就会产生Using filesort。

分页查询优化

示例：


SELECT * FROM employees LIMIT``9000``,``5``;

        表示从表employees中取出从9001行开始的5行记录，看似只查询了5条记录，实际这条SQL是先读取9005条记录，然后抛弃前9000条记录，然后读到后面5条想要的数据。因此要查询一张大表比较靠后的数据，执行效率会很低。

优化：

  •  根据自增且连续的主键排序的分页查询

        在employees中 ，因为主键是自增并且连续的，所以可以改写成按照主键去查询从第9001开始的五行数据，如下：


SELECT * FROM employees WHERE id >``9000 LIMIT``5``;

        通过对比执行计划，显然改写后SQL走了索引，而且扫描的行数大大减少，执行效率更高。注意：如果主键不连续或者不是按照主键排序则不能使用上面描述的优化方法。

  • 根据非主键字段排序的分页查询


EXPLAIN SELECT * FROM employees ORDER BY name LIMIT ``9000``,``5``;

        发现并没有使用name字段的索引（key字段对应的值为null），这个是因为：扫描整个索引并查找到没索引的行(可能要遍历多个索引树)的成本比扫描全表的成本更高，所以优化器放弃使用索引。

        知道不走索引的原因，那么怎么优化呢？其实关键是让排序时返回的字段尽可能少，所以可以让排序和分页操作先查出主键，然后根据主键查到对应的记录，SQL改写如下：


SELECT * FROM employees e1 INNER JOIN (SELECT id FROM employees ORDER BY name LIMIT``9000``,``5``) e2 on e1.id = e2.id;

原SQL使用的是filesort排序，而优化后的SQL使用的是索引排序。

优化总结：

如果主键是自增连续的主键就用主键排序进行分页查询，如果不是就用非主键字段排序分页查询。

MySql索引优化
12-14
MySQL索引优化是数据库管理中的关键环节，它直接影响着数据查询效率和系统整体性能。创建索引的主要目的是为了加速数据的检索，特别是对于大型数据库，这一点尤为重要。索引能够确保数据的唯一性，使得数据库表中的...
MySQL索引优化（超详细）
weixin_55816781的博客
05-10 7840
MySQL索引优化，涉及到索引介绍，性能分析，查询优化，慢查询日志。
深入理解Mysql事务隔离级别与锁机制
weixin_47188125的博客
04-13 1674
我们的数据库一般都会并发执行多个事务，多个事务可能会并发的对相同的一批数据进行增删改查操作，可能就会导致我们说的脏写、脏读、不可重复读、幻读这些问题。这些问题的本质都是数据库的多事务并发问题，为了解决多事务并发问题，数据库设计了事务隔离机制、锁机制、MVCC多版本并发控制隔离机制，用一整套机制来解决多事务并发问题。二：事务及其ACID特性事务是由一组SQL语句组成的逻辑处理单元,事务具有以下4个属性,通常简称为事务的ACID属性。
Mysql执行计划与索引详解
Mark-Zoe的博客
04-23 454
使用EXPLAIN关键字可以模拟优化器执行SQL语句，分析你的查询语句或是结构的性能瓶颈 在 select 语句之前增加 explain 关键字，MySQL 会在查询上设置一个标记，执行查询会返 回执行计划的信息，而不是执行这条SQL。 注意：如果 from 中包含子查询，仍会执行该子查询，将结果放入临时表中。 Explain分析示例 mysql>expla...
一文带你你搞懂索引如何优化！！！
码猿技术专栏
08-30 3507
前言索引的相信大家都听说过，但是真正会用的又有几人？平时工作中写SQL真的会考虑到这条SQL如何能够用上索引，如何能够提升执行效率？此篇文章详细的讲述了索引优化的几个原则，只要在工作中能...
MySQL索引系列--索引的优化--LIKE模糊查询
IT利刃出鞘的博客
02-08 8868
本文介绍MySQL的LIKE模糊查询索引的优化。主要是索引失效的解决方案。
04-VIP-Mysql索引优化实战一.pdf
11-30
MySQL索引优化是数据库性能提升的关键环节，本篇主要探讨了几个关于MySQL索引使用和优化的重要知识点。 首先，创建了一个名为`employees`的员工记录表，其中包含`id`（主键）、`name`、`age`、`position`和`hire_...
mysql 索引优化
12-14
MySQL索引优化是数据库性能提升的关键技术之一，尤其在处理大量数据时，高效的索引能够显著减少查询时间。本文将围绕MySQL 5.7社区版的索引优化展开，包括索引类型、优化策略以及使用`optimizer_trace`进行查询分析...
MySQL索引失效场景及优化
qq_39618837的博客
02-02 1195
在 MySQL 中，对于一些索引类型（例如 B-Tree 索引），索引的有效性通常依赖于有序性，而在通配符出现在查询开头的情况下，索引就无法提供有序性。因此，在索引列上进行对 NULL 值的查询可能导致无法使用索引，因为索引不存储 NULL 值的信息，它只存储非 NULL 值的信息。如果索引列的选择性很低，即不同值的数量相对较少，查询优化器可能会认为使用该索引进行查询的代价较高，因此选择放弃使用索引，采用全表扫描等方式。在MySQL中，对于复合索引，只有在查询中使用索引的最左前缀列时，索引才能被有效利用。
MySQL Like查询优化大全
小张童鞋
10-12 4971
MySQL Like优化方案大全
MySQL 中 LIKE 模糊查询如何优化？
最新发布
乐闻世界
10-22 1254
在 MySQL 中，使用 LIKE 关键字进行模糊查询是非常常见的操作。然而，模糊查询往往性能较差，尤其是在大型数据库中，可能导致查询速度变慢，影响系统的整体性能。那么，有哪些方法可以优化 LIKE 模糊查询呢？本文将通俗易懂地介绍几种优化策略。优化 MySQL 中的 LIKE 模糊查询需要综合考虑多种因素，包括查询方式、索引使用、缓存机制、表设计等。通过合理的优化策略，可以显著提高查询性能，提升系统的整体效率，根据具体的应用场景选择合适的优化策略，才能真正发挥数据库的性能。
mysql like 4种优化_MySQL高级（四）：索引优化
weixin_35890852的博客
02-17 1415
1、准备上一篇的内容学习了如何使用explain来分析SQL的性能问题，通过这个性能分析利器我们就能够对SQL语句进行索引优化，来帮助我们写出更高性能的SQL语句。好了，接下来开始实战吧。首先创建一张表：1234567create table staffs(id int primary key auto_increment,name varchar(24) not null default '' ...
其实 MySQL 中的 like 关键字也能用索引
2401_84010901的博客
03-31 1739
PRIMARY KEY (),KEY (,)) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;username 和 age 组成了复合索引，复合索引名为 username，下文提到的 username 索引都是指该复合索引。根据上篇文章（是时候检查一下使用索引的姿势是否正确了！）的讲解，我们知道，对于如下 SQL：select username,age from user2 where u
Mysql——索引优化
weixin_45682305的博客
03-01 392
Mysql索引优化实战
MySQL索引
garmoo的博客
10-29 191
MySQL主要索引类型 B-Tree索引 大多数的存储引擎使用的是B-Tree（从技术上来说是B+Tree）索引，B-Tree索引通常所有的值都是按顺序存储的，每个叶子到的根的距离相同。InnoDB使用的是B+Tree。 B-Tree索引使用可以以如下数据表为例进行分析： CREATE TABLE product ( id int primary key auto_increment, name ...
mysql8.0.30索引优化与查询优化
HappyLearnerL的博客
12-04 462
mysql8.0.30索引优化与查询优化
MySQL索引优化深度解析
本文主要探讨了MySQL如何使用索引以及索引优化的策略，涵盖了索引的分类、MyISAM和InnoDB存储引擎的索引结构、索引的优缺点以及设计原则。 在MySQL中，索引对于提高查询速度至关重要。它们可以显著提升全值匹配和...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值