mysql索引的基本介绍和索引失效的情况

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

一 索引简介

  MySQL官方对索引的定义为：索引（Index）是帮助MySQL高效获取数据的数据结构。可以得到索引的本质：索引是数据结构。索引的目的在于提高查询效率。

1 索引存储位置

  一般来说索引本身也很大，不可能全部存储在内存中，因此索引往往以以索引文件的形式存储的磁盘上,索引是数据库中用来提高性能的最常用的工具。

2 索引的优势

  1. 类似大学图书馆建书目索引，提高数据检索的效率，降低数据库的IO成本
  2. 通过索引列对数据进行排序，降低数据排序的成本，降低了CPU的消耗

3 索引的劣势

  1. 虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件每次更新添加了索引列的字段，都会调整因为更新所带来的键值变化后的索引信息
  2. 实际上索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录，所以索引列也是要占用空间的
  3. 索引只是提高效率的一个因素，如果你的MySQL有大数据量的表，就需要花时间研究建立最优秀的索引，或优化查询语句

4 索引的分类

1）单值索引

  即一个索引只包含单个列，一个表可以有多个单列索引

-- 随表一起建索引：
CREATE TABLE customer (
    id INT(10) UNSIGNED  AUTO_INCREMENT ,
    customer_no VARCHAR(200),
    customer_name VARCHAR(200),
    PRIMARY KEY(id),
    KEY (customer_name)
)
-- 单独建单值索引：
CREATE  INDEX idx_customer_name ON customer(customer_name); 
-- 删除索引：
DROP INDEX idx_customer_name  on customer;

2）唯一索引

  索引列的值必须唯一，但允许有空值

-- 随表一起建索引：
CREATE TABLE customer (id INT(10) UNSIGNED  AUTO_INCREMENT ,
    customer_no VARCHAR(200),
    customer_name VARCHAR(200),
    PRIMARY KEY(id),
    KEY (customer_name),
    UNIQUE (customer_no)
);
  
-- 单独建唯一索引：
CREATE UNIQUE INDEX idx_customer_no ON customer(customer_no); 
 
-- 删除索引：
DROP INDEX idx_customer_no on customer ;

3）主键索引

  设定为主键数据库会自动建立索引，innodb为聚簇索引

-- 随表一起建索引：
CREATE TABLE customer (id INT(10) UNSIGNED  AUTO_INCREMENT ,
customer_no VARCHAR(200),customer_name VARCHAR(200),
  PRIMARY KEY(id) 
);
   
CREATE TABLE customer2 (id INT(10) UNSIGNED   ,
customer_no VARCHAR(200),customer_name VARCHAR(200),
  PRIMARY KEY(id) 
);
 
 -- 单独建主键索引：
ALTER TABLE customer add PRIMARY KEY customer(customer_no);  
 
-- 删除建主键索引：
ALTER TABLE customer drop PRIMARY KEY ;

4）复合索引

  即一个索引包括多个列

-- 随表一起建索引：
CREATE TABLE customer (id INT(10) UNSIGNED  AUTO_INCREMENT ,
customer_no VARCHAR(200),customer_name VARCHAR(200),
  PRIMARY KEY(id),
  KEY (customer_name),
  UNIQUE (customer_name),
  KEY (customer_no,customer_name)
);
 
-- 单独建索引：
CREATE  INDEX idx_no_name ON customer(customer_no,customer_name); 
 
-- 删除索引：
DROP INDEX idx_no_name  on customer ;

5 索引的基本语法

1）CREATE创建索引

-- 创建索引
CREATE  [UNIQUE|FULLTEXT|SPATIAL]  INDEX indexName ON table_name(column))

2）ALTER 添加索引

-- 该语句添加一个主键，这意味着索引值必须是唯一的，且不能为NULL。
ALTER TABLE tbl_name ADD PRIMARY KEY (column_list):

-- 这条语句创建索引的值必须是唯一的（除了NULL外，NULL可能会出现多次）。
ALTER TABLE tbl_name ADD UNIQUE index_name (column_list): 

-- 添加普通索引，索引值可出现多次。
ALTER TABLE tbl_name ADD INDEX index_name (column_list): 

-- 该语句指定了索引为 FULLTEXT ，用于全文索引。
ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list):

3）删除索引

-- 删除：
DROP INDEX indexName ON mytable;

4）查看索引

SHOW INDEX FROM table_name

6 哪些情况需要创建索引

  1. 主键自动建立唯一索引
  2. 频繁作为查询条件的字段应该创建索引
  3. 查询中与其他表关联的字段，外键关系建立索引
  4. 单键/组合索引的选择问题，组合索引性价比更高
  5. 查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度
  6. 查询中统计或者分组字段

7 哪些情况不要创建索引

  1. 表记录太少
  2. 经常增删改的表或者字段；原因:提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件
  3. where条件中用不到的字段不创建索引
  4. 过滤性不好的不适合建索引

二 避免索引失效

数据准备

create table `tb_seller` (
    `sellerid` varchar (100),
    `name` varchar (100),
    `nickname` varchar (50),
    `password` varchar (60),
    `status` varchar (1),
    `address` varchar (100),
    `createtime` datetime,
     primary key(`sellerid`)
)engine=innodb default charset=utf8mb4;


insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`,`address`, `createtime`) 
values('alibaba','阿里巴巴','阿里小店','e10adc3949ba59abbe56e057f20f883e','1','北京市','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`,`address`, `createtime`)
 values('baidu','百度科技有限公司','百度小店','e10adc3949ba59abbe56e057f20f883e','1','北京市','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`,`address`, `createtime`)
 values('huawei','华为科技有限公司','华为小店','e10adc3949ba59abbe56e057f20f883e','0','北京市','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`,`address`, `createtime`) 
values('itcast','西天取经有限公司','西天取经','e10adc3949ba59abbe56e057f20f883e','1','北京市','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`,`address`, `createtime`)
 values('itheima','蜀汉集团有限公司','西蜀','e10adc3949ba59abbe56e057f20f883e','0','北京市','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`,`address`, `createtime`)
 values('luoji','罗技科技有限公司','罗技小店','e10adc3949ba59abbe56e057f20f883e','1','北京市','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`,`address`, `createtime`) 
values('oppo','OPPO科技有限公司','OPPO官方旗舰店','e10adc3949ba59abbe56e057f20f883e','0','北京市','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`,`address`, `createtime`)
 values('ourpalm','掌趣科技股份有限公司','掌趣小店','e10adc3949ba59abbe56e057f20f883e','1','北京市','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`,`address`, `createtime`) 
values('qiandu','千度科技','千度小店','e10adc3949ba59abbe56e057f20f883e','2','北京市','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`,`address`, `createtime`) 
values('sina','新浪科技有限公司','新浪官方旗舰店','e10adc3949ba59abbe56e057f20f883e','1','北京市','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`,`address`, `createtime`) 
values('xiaomi','小米科技','小米官方旗舰店','e10adc3949ba59abbe56e057f20f883e','1','西安市','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`,`address`, `createtime`) 
values('yijia','宜家家居','宜家家居旗舰店','e10adc3949ba59abbe56e057f20f883e','1','北京市','2088-01-01 12:00:00');

select * from tb_seller

在这里插入图片描述

1 全值匹配

  全值匹配是指查询语句中使用了索引的所有列，并且所有列的值都与索引中的值完全匹配。这意味着查询的条件必须与索引的所有列的值完全一致，才能利用索引进行快速查找。全值匹配可以提高查询性能，减少数据库的工作负载。如果查询语句中的条件没有与索引的所有列完全匹配，那么就无法利用索引进行快速查找，可能需要进行全表扫描，效率较低。因此，在设计数据库表和索引时，需要根据实际情况选择合适的索引，并且编写查询语句时要保证全值匹配，以提高查询效率。

create index idx_name_sta_addr on tb_seller(name,status,address);
explain select * from tb_seller where name='小米科技' and status='1' and address='北京市';

在这里插入图片描述
Extrra字段中属性说明

  • using index ：使用覆盖索引的时候就会出现
  • using where：在查找使用索引的情况下，需要回表去查询所需的数据
  • using index condition：查找使用了索引，但是需要回表查询数据
  • using index ; using where：查找使用了索引，但是需要的数据都在索引列中能找到，所以不需要回表查询数据

  从结果可以看出，查询使用了索引，但是需要回表进行查看

explain select * from tb_seller where name='小米科技' and status='1' and address='北京市';

在这里插入图片描述

2 最佳左前缀法则

  如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列。

explain select * from tb_seller where name='小米科技'

在这里插入图片描述

explain select * from tb_seller where name='小米科技' and status='1'

在这里插入图片描述
  以上2个示例查询语句都是从复合索引（idx_name_sta_addr ）最左前列开始的，而且复合索引 （idx_name_sta_addr ）包含查询条件的字段，所以可以使用索引

违法最左前缀法则 ， 索引失效：

explain select * from tb_seller where status='1'

在这里插入图片描述
  如果符合最左法则，但是出现跳跃某一列，只有最左列索引生效：

explain select * from tb_seller where name='小米科技' and address='北京市';

在这里插入图片描述
最左前缀法则的原理
  mysql中InnoDB引擎中，索引基于B+树索引的结构。在B+树索引中，索引的数据是按照索引列的顺序有序存储的。当查询时，MySQL可以利用B+树的有序性，从根节点开始，按照索引列的顺序进行搜索，直到找到满足查询条件的数据行。违反最左前缀会破坏索引的有序性，所以索引无效。

3. 范围查询右边的列，不能使用索引 。

explain select * from tb_seller where name='小米科技' and status>'1' and address='北京市';

在这里插入图片描述
  说明：根据key_len为410可以看出前面的两个字段name ， status 查询是走索引的， 但是最后一个条件address 没有用到索引。同样是因为范围查询后会破会索引的有序性，导致索引失效。

4 不要在索引列上进行运算操作， 索引将失效。

explain select * from tb_seller where substring(name,3,2)='科技'

在这里插入图片描述
  因为索引的数据是按照特定的排序方式存储的，而运算会改变数据的排序方式或者比较方式，导致索引无法直接进行匹配和查找。MySQL优化器会尽可能地使用索引提高查询性能，但当索引被运算操作影响时，优化器会选择放弃使用索引，而选择其他的查询方式，如全表扫描。
  另外，一些函数操作或表达式计算可能会导致索引列的值发生变化，进而无法准确匹配索引中的值。例如，如果在查询时对索引列进行了函数操作或计算，这可能导致实际匹配的值与索引中的值不一致，从而无法使用索引进行准确的查找。
  因此，在使用MySQL索引时，应尽量避免对索引列进行运算操作，尽量保持索引列的原始值，以便优化器能够正确地使用索引提高查询性能。如果需要进行运算操作，可以考虑在查询语句中使用索引列的原始值进行运算，或者考虑创建额外的计算列来存储运算结果，并在需要时使用该计算列进行查询。

5 字符串不加单引号，造成索引失效。

explain select name,status,address from tb_seller where name='小米科技' and status=1

在这里插入图片描述
  说明：由于在查询时，没有对字符串加单引号，MySQL的查询优化器，会自动的进行类型转换，造成索引失效。

6 尽量使用覆盖索引，避免select *

  尽量使用覆盖索引（只访问索引的查询（索引列完全包含查询列）），减少select *

explain select * from tb_seller where name='小米科技' and status='1' and address='北京市';

在这里插入图片描述

explain select name,status,address from tb_seller where name='小米科技' and status='1' and address='北京市';

在这里插入图片描述
  上面第一条查询语句，Extra为Using index condition，表示查找使用了索引，但是需要回表查询数据，而第二条语句Extra为Using where; Using index，表示查找使用了索引，需要的数据都在索引列中能找到，所以不需要回表查询数据，为覆盖索引。回表查询会影响查询效率，所以尽量使用覆盖索引。

7 or使索引失效

   用or分割开的条件， 如果or前的条件中的列有索引，而后面的列中没有索引，那么涉及的索引都不会被用到。示例，name字段是索引列 ， 而createtime不是索引列，中间是or进行连接是不走索引的 ：

explain select * from tb_seller where name='西天取经有限公司' or createtime = '2088-01-01 12:00:00';

在这里插入图片描述
  因为"or"条件需要对每个条件进行逻辑判断，而不是简单地进行索引匹配。当使用"OR"条件时，优化器可能无法确定哪个条件会返回满足条件的数据，因此可能会选择放弃使用索引，而进行全表扫描来查找匹配的数据。

8 以%开头的Like模糊查询，索引失效。

  如果仅仅是尾部模糊匹配，索引不会失效。如果是头部模糊匹配，索引失效。

explain select * from tb_seller where name like '西天取经有限公司%'

在这里插入图片描述

explain select * from tb_seller where name like '%西天取经有限公司'

在这里插入图片描述

9 如果MySQL评估使用索引比全表更慢，则不使用索引。

创建单值索引

create index inx_address on tb_seller(address);

查询语句1

explain select * from tb_seller where address='北京市';

在这里插入图片描述
查询语句2

explain select * from tb_seller where address='西安市';

在这里插入图片描述
  数据中绝大部分address都是北京市，使用查询语句1，在这种情况下，优化器可能认为使用索引+回表进行查找的成本大于进行全表扫描，从而放弃使用索引。

10 is NULL ， is NOT NULL 有时索引失效。

查询1

explain select * from tb_seller where name is null;

在这里插入图片描述
查询2

explain select * from tb_seller where name is not null;

在这里插入图片描述
  上面查询一索引有效是因为数据中name为null的不存在，或者在其他数据表中如果查询列，建立了索引，但是数据为空值的情况很少，会使用到索引，反之，优化器可能认为使用索引+回表进行查找的成本大于进行全表扫描，从而放弃使用索引。

11 在使用不等于(!= 或者<>)，有时无法使用索引会导致全表扫描

explain select * from tb_seller where address !='北京市';

在这里插入图片描述

explain select * from tb_seller where address <>'北京市';

在这里插入图片描述

explain select * from tb_seller where address <>'西安市';

在这里插入图片描述

12 in 走索引， not in 有时索引失效。

explain select * from tb_seller where sellerid in ('baidu','sina');

在这里插入图片描述

explain select * from tb_seller where sellerid  not in ('baidu','sina');

在这里插入图片描述

explain select * from tb_seller where sellerid  not in ('oppo','xiaomi','sina');

在这里插入图片描述

  情况与9,10,11 道理一样。MySQL 会在选择索引的时候进行优化，如果 MySQL 认为全表扫描比走索引+回表效率高， 那么他会选择全表扫描，如果认为走索引的效率高，那么肯定也是会走索引的。总之，如果过滤性不好，索引会失效。

总结
全值匹配我最爱，最左前缀要遵守；
带头大哥不能死，中间兄弟不能断；
索引列上少计算，范围之后全失效；
LIKE百分写最右，覆盖索引不写*；
不等空值还有OR，索引影响要注意；
VAR引号不可丢，SQL优化有诀窍。

欢乐海豚
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
Mysql索引失效的常见原因&如何用好索引
H_Jason_的博客
10-11 1369
本篇文章主要介绍了索引失效的常见原因和如何用好索引，并以案例的形式进行了说明
数据库建立索引以及索引失效问题
PHP/Python学习
09-24 739
操作数据库索引能起到优化数据库查询，提高查询效率的作用。并且对也稍微大一点的数据量的服务来说，索引的创建是必须的，也是关键的。但是索引也要合理应用，不要遇到查询就建索引，也不能乱建。如果造成索引失效那还不如不建。索引失效有的时候是因为自己的语句问题造成的。下面是几种造成索引失效的几种原因，  一、以下的方法会引起索引失效  ‍1, 2,单独的>, 3,like "%_" 百分号在前. 
MYSQL 关于IS NULL 和 IS NOT NULL会不会走索引
qq_41154871的博客
08-07 8112
一.索引列key 默认值为NULL 允许为空 分析: IS NULL 使用了索引 , IS NOT NULL 没有走索引 二.索引列key 默认值为'123' 允许为空 分析: IS NULL 使用了索引 , IS NOT NULL 没有走索引 三.索引列key 默认值为'123' 不允许为空 分析: IS NULL ...
MySQL到底走不走索引
weixin_43846185的博客
07-28 3019
工作中，常常遇到sql走不走索引的问题，一般只要认为建了索引就会走，其实不然。本文较为深入地讲解了走索引的判断问题
Mysql基础篇-13-索引-查询优化
Alan0517
01-11 400
答案是：会走 idx_no（task_no） 索引，而不是走idx_de_no(deleted, task_no),原因是索引查询优化器还会根据统计信息、表的大小和复杂度等因素来决定最终使用的索引，delete字段只有0和1两种可能，复杂度太低，优化器最终会选择idx_no索引进行查询，所以我们在选取字段做复合索引时，会优先使用复杂度高的(零散度高的)字段；比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序，mysql的查询优化器会帮你优化成索引可以识别的形式。
MySQL 索引失效详解
王林的博客
04-27 3042
如：其中userId加了索引，但是age没有加索引的。如：给单个name字段加上索引，并查询name为非空的语句，其实会走索引的，如下:select * from user where name is not null;解释：对于or+没有索引的age这种情况，假设它走了userId的索引，但是走到age查询条件时，它还得全表扫描，也就是需要三步过程：全表扫描+索引扫描+合并。解释：其实这个也是跟mySQL优化器有关，如果优化器觉得即使走了索引，还是需要扫描很多很多行的话，它觉得不划算，不如直接不走索引。
MySQL索引可能失效之or、is null、is not null、不等于(!=,＜＞)、联合索引
最新发布
孟德尚且敬我，汝意欲何为
08-23 2170
MySQL索引可能失效之or、is null、is not null、联合索引
导致MySQL索引失效的一些常见写法总结
09-14
在MySQL数据库中，索引是一种重要的优化手段，它能够显著...以上就是关于导致MySQL索引失效的一些常见情况及其避免方法。在实际工作中，我们需要结合具体业务场景和查询需求，合理设计和使用索引，以提高数据库性能。
Mysql5.7及8.0版本索引失效情况汇总
01-19
没有特殊说明，测试环境均为MySQL8.0，早期版本可能会有更多情况导致索引失效。8.0失效的情况，早期版本也失效；8.0不失效的情况，早期版本可能失效。 所有测试默认不考虑表为空的情况，特殊情况文中会有说明。 本文...
mysql索引失效的五种情况分析
09-10
主要介绍了mysql索引失效的5种情况分析,需要的朋友可以参考下
Mysql索引会失效的几种情况分析
12-15
索引并不是时时都会生效的，比如以下几种情况，将导致索引失效：  1.如果条件中有or，即使其中有条件带索引也不会使用(这也是为什么尽量少用or的原因) 　注意：要想使用or，又想让索引生效，只能将or条件中的每个...
mysql之索引失效场景
qq_41820066的博客
11-10 671
MySQL之索引失效场景
数据库索引使用的注意事项以及索引失效的情况【Mysql】
qq_36944952的博客
07-11 1455
数据库索引使用的注意事项以及索引失效的情况 1.最左前缀法则。 2.范围查询。 索引失效的集中情况：包括：索引列上进行运算、模糊查询、or连接条件， 字符串不加引号，数据分布情况。
索引使用规则及索引失效情况
Maringss的博客
12-05 3444
索引使用规则及索引失效情况
is null、is not null、!= 到底走不走索引实验
wys0912的博客
09-16 2958
is null、is not null、!= 到底走不走索引
mysql not 索引_mysql 存在索引但不能使用索引的典型场景
weixin_31437187的博客
01-18 238
mysql 演示数据库：http://downloads.mysql.com/docs/sakila-db.zip以%开头的LIKE查询不能够利用B-tree索引explain select * from actor where last_name like '%NI%'\G;explain select * from actor where last_name like 'NI%'\G;解决办法...
mysql 给几个主键值 批量校验是否存在_MySQL中IS NULL、IS NOT NULL、!=不能用索引？胡扯！
weixin_39869791的博客
10-21 636
不知道从什么时候开始，网上流传着这么一个说法：MySQL的WHERE子句中包含 IS NULL、IS NOT NULL、!= 这些条件时便不能使用索引查询，只能使用全表扫描。这种说法愈演愈烈，甚至被很多同学奉为真理。咱啥话也不说，举个例子。假如我们有个表s1，结构如下：CREATE 这个表里有10000条记录：mysql下边我们直接贴几个图：上边几个查询语句的WHERE子句中用了IS NULL、I...
MySQL索引
weixin_38044597的博客
09-24 255
参考博文：MySQL索引——阿里面试 1.索引模型、索引概念 当数据库业务量增加时，每天数据达到百万级的数据量时，有了以下对话。 面试官：你们每天这么大的数据量，都是保存在关系型数据库中吗？ 回答：是的，线上使用的MySQL数据库。 面试官：每天百万级数据，一个月千万级的数据量了，是否有对数据库查询做一些优化？ 回答：我们是对数据库中建立了索引。 一般技术能力比较强的面试官，会采用...
mysql IS NULL 使用索引
热门推荐
祈雨v的博客
08-28 1万+
简介 mysql的sql查询语句中使用is null、is not null、!=对索引并没有任何影响，并不会因为where条件中使用了is null、is not null、!=这些判断条件导致索引失效而全表扫描。 mysql官方文档也已经明确说明is null并不会影响索引的使用。 MySQL can perform the same optimization on col_name IS ...
mysql索引失效的情况
07-29
MySQL索引失效的情况有以下几种： 1. 数据量过小：当表中记录的数量较少时，使用索引可能会变得不必要，因为...这些是一些常见的导致MySQL索引失效的情况和优化方法。根据具体情况进行分析和调整，可以提高查询性能。

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

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

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值