sql优化篇（二）：索引的使用

最新推荐文章于 2023-10-30 10:53:01 发布
最新推荐文章于 2023-10-30 10:53:01 发布
阅读量240 收藏
点赞数
分类专栏： sql优化
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/ll_mor/article/details/79427343
版权

1、索引的存储分类

  • B-tree索引：最常见的索引类型
  • HASH索引：只有Memory引擎支持
  • R-tree索引：空间索引MyISAM的一个特殊索引类型
  • Full-text索引：全文索引也是MyISAM，InnoDB从mysql5.6版本提供全文检索的支持

B-tree索引和Hash索引比较：
Hash索引相对简单，只有Memory引擎支持Hash索引，Hash索引适用于Key-value查询，通过Hash索引要比通过B-tree索引查询更迅速；Hash索引不适合范围查询，例如<、>、<=..这类。

2、Mysql如何适用索引

Mysql中能够使用索引的典型场景

（1）匹配全值

对索引中具体的列指定具体的值：
继续使用sakila作为数据库，重命名租赁表rental上的索引rental_date为idx_rental_date;
这里写图片描述
可以看到优化器选择了复合索引idx_rental_date。

（2）匹配值的范围查询

这里写图片描述

（3）匹配最左前缀（做左匹配原则可以算是MySQL中B-TREE索引使用的首要原则）

仅仅使用索引中的最左边的列进行查找。

mysql>alter table payment add index idx_payment_date(payment_date,amount,last_update);

Query OK, 0 rows affected (0.14 sec)

Records: 0 Duplicates: 0  Warnings: 0

如果对第一个和第三个可以使用索引

mysql>explain select * from payment where payment_date='2006-02-14 15:16:03' andlast_update='2006-02-15 22:12:32'\G;

*************************** 1. row***************************

          id: 1

 select_type: SIMPLE

       table: payment

        type: ref

possible_keys: idx_payment_date

         key: idx_payment_date

     key_len: 5

         ref: const

         rows: 182

       Extra: Using index condition

1 row in set (0.00 sec)



ERROR:

No query specified

但是对第二个和第三个就不可以使用索引

mysql>explain select * from payment where amount=3.98 and last_update='2006-02-1522:12:32'\G;

*************************** 1. row***************************

          id: 1

 select_type: SIMPLE

       table: payment

        type: ALL

possible_keys: NULL

         key: NULL

     key_len: NULL

         ref: NULL

        rows: 16086

       Extra: Using where

1 row in set (0.00 sec)



ERROR:

No query specified
（4）仅仅对索引进行查询

当查询的列都在索引的字段中时，查询效率更高。

mysql>explain select *from payment where payment_date='2006-02-14 15:16:03' andamount=3.98\G;

*************************** 1. row***************************

          id: 1

 select_type: SIMPLE

       table: payment

        type: ref

possible_keys: idx_payment_date

         key: idx_payment_date

     key_len: 8

         ref: const,const

        rows: 8

       Extra: NULL

1 row in set (0.00 sec)



ERROR:

No query specified

mysql>explain select last_update from payment where payment_date='2006-02-1415:16:03' and amount=3.98\G;

*************************** 1. row***************************

          id: 1

 select_type: SIMPLE

       table: payment

        type: ref

possible_keys: idx_payment_date

         key: idx_payment_date

     key_len: 8

         ref: const,const

        rows: 8

        Extra: Using index

1 row in set (0.00 sec)



ERROR:

No query specified

可以发现第二次查询extra部分变成了Using index 也就意味着，现在直接访问索引足够获取到所需的数据，不需要通过索引回表，Using index也就是平常说的覆盖索引扫描。至访问必须访问的数据，减少不必要的数据访问能够提高效率。

（5）匹配列前缀
mysql>explain select title from film_text where title like 'AFRICAN%'\G;

*************************** 1. row***************************

          id: 1

 select_type: SIMPLE

       table: film_text

        type: range

possible_keys:idx_title_desc_part,idx_title_description

         key: idx_title_desc_part

     key_len: 32

         ref: NULL

        rows: 1

       Extra: Using where

1 row in set (0.00 sec)



ERROR:

No query specified

EXTRA值为Using where 表示优化器需要通过索引回表查询数据。

（6）能够实现索引匹配部分精确而其他部分进行范围匹配
mysql> explain select inventory_id from rentalwhere rental_date='2006-02-14 15:16:03' and customer_id>=300 and customer_id<=400\G;

*************************** 1. row***************************

          id: 1

 select_type: SIMPLE

       table: rental

        type: ref

possible_keys:rental_date,idx_fk_customer_id

         key: rental_date

     key_len: 5

         ref: const

        rows: 181

       Extra: Using where; Using index

1 row in set (0.00 sec)



ERROR:

No query specified

usingindex表示查询使用了覆盖索引扫描

（7）如果列名是索引，那么使用column_name is null就会使用索引
mysql>explain select * from payment where rental_id is null \G;

*************************** 1. row***************************

          id: 1

 select_type: SIMPLE

       table: payment

        type: ref

possible_keys: fk_payment_rental

         key: fk_payment_rental

     key_len: 5

         ref: const

        rows: 5

       Extra: Using index condition

1 row in set (0.00 sec)



ERROR:

No query specified

…

MySQL中存在索引但是不能使用索引的典型场景

（1）以%开头的LIKE查询不能够利用B-Tree索引
mysql>explain select * from actor where last_name like '%NI%'\G;

*************************** 1. row***************************

          id: 1

 select_type: SIMPLE

       table: actor

        type: ALL

possible_keys: NULL

         key: NULL

     key_len: NULL

         ref: NULL

        rows: 200

       Extra: Using where

1 row in set (0.00 sec)



ERROR:

No query specified

因为是B-Tree索引的结构，所以以%开头查询自然没法利用索引，这种情况一般推荐使用全文索引来解决类似问题。或者考虑利用InnoDB的表都是聚簇表的特点，采用轻量级别的解决方式：一般情况下，索引都会比表小，扫描索引要比扫描表更快，而InnoDB表上二级索引idx_last_name实际上存储字段last_name还有主键actor_id，那么理想的访问方式是首先扫描二级索引idx_last_name获得满足条件last_name like %IN%的主键actor_id列表，之后根据主键去检索记录，避开全表扫描演员表actor产生的大量IO。

（2）数据类型出现隐式转换的时候也不会使用索引
mysql>explain select * from actor where last_name=1\G;

*************************** 1. row***************************

           id: 1

 select_type: SIMPLE

       table: actor

        type: ALL

possible_keys: idx_actor_last_name

         key: NULL

     key_len: NULL

         ref: NULL

        rows: 200

       Extra: Using where

1 row in set (0.00 sec)

mysql>explain select * from actor where last_name='1'\G;

*************************** 1. row***************************

          id: 1

 select_type: SIMPLE

       table: actor

        type: ref

possible_keys: idx_actor_last_name

         key: idx_actor_last_name

     key_len: 137

         ref: const

        rows: 1

       Extra: Using index condition

1 row in set (0.00 sec)



ERROR:

No query specified

我们可以看到前者没有加引号，即便是此列有索引，使用错误的数据类型导致没有使用索引。

（3）符合索引情况下，不满足最左原则不会使用符合索引
mysql>explain select * from payment where amount=3.89 and last_update='2006-02-1522:12:32'\G;

*************************** 1. row***************************

          id: 1

 select_type: SIMPLE

       table: payment

        type: ALL

possible_keys: NULL

         key: NULL

     key_len: NULL

         ref: NULL

        rows: 16086

       Extra: Using where

1 row in set (0.00 sec)



ERROR:

No query specified
（4）如果MySQL估计使用索引比全表扫描更慢，则不使用索引
mysql> update film_text set title = concat('s',title);
Query OK, 1000 rows affected (0.08 sec)
Rows matched: 1000  Changed: 1000  Warnings: 0

mysql>explain select * from film_text where title like 'S%'\G;

*************************** 1. row***************************

          id: 1

 select_type: SIMPLE

       table: film_text

        type: ALL

possible_keys: idx_title_desc_part,idx_title_description

         key: NULL

     key_len: NULL

         ref: NULL

        rows: 1000

       Extra: Using where

1 row in set (0.00 sec)



ERROR:

No query specified
（5）用or分割卡id条件，如果or钱的条件中的列有索引，而后面的列没有索引，那么就不会使用索引。
mysql>explain select * from payment where customer_id=203 or amount=3.96\G

*************************** 1. row***************************

          id: 1

 select_type: SIMPLE

       table: payment

        type: ALL

possible_keys: idx_fk_customer_id

         key: NULL

     key_len: NULL

         ref: NULL

        rows: 16086

       Extra: Using where

1 row in set (0.00 sec)

因为or后面的条件没有索引，那么后面的查询肯定要走全表扫描，存在全表扫描的情况下，就没必要多一次索引扫描而增加I/O访问，一次全表扫描就够了。

3、查看索引使用情况

如果索引正在工作，Headler_read_key的值很高，这个值代表了一个行被索引值读的次数，很低的值表明增加索引得到的性能改善不高，因为索引并不经常使用。
这里写图片描述

最后一行Handler_read_rnd_next表示读下一行的请求数，值越大，则说明索引不正确或者写入的查询没有利用索引，需要建立索引改善。

Send_youCherry
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
有关Sql优化中索引的使用
qq_35883086的博客
08-05 136
最近在做项目的优化，首先是针对数据库的建立的索引下手，发现一些很大的表中建立了太多的索引，大家都知道索引太多也会影响sql执行效率，比如数据插入删除等。 为了研究下单列索引和联合索引的区别，本来打算自己做个测试的，为了节约时间，打算网上搜一下，一搜发现果然有同学已经做过了测试，就借助该同学的测试做下总结吧，以下为测试过程和结果： 一、联合索引测试 注：Mysql版本为 5.7.20 创建测试...
不可错过的SQL优化干货分享-sql优化、索引使用
weixin_43805705的博客
02-15 307
本文是向大家介绍在sql调优的几个操作步骤，它能够在日常遇到慢sql时有分析优化思路，能够让开发者更好的了解sql执行的顺序和原理。
sql server 索引的使用
03-18
sql server 索引的使用 --创建索引create index(索引关键字) indexName(索引名) on userInfo(userName,userAge) create index indexName on userInfo(userName,userAge) --查看索引 sp_helpindex userInfo --创建唯一性索引 create unique index indexAge on userInfo(userAge) --删除索引 drop index indexName on userInfo
MYSQL学习笔记——sql语句优化之索引
weixin_34413802的博客
10-28 134
上一篇博客讲了可以使用慢查询日志定位耗时sql，使用explain命令查看mysql的执行计划，以及使用profiling工具查看语句执行真正耗时的地方，当定位了耗时之后怎样优化呢？这篇博客会介绍mysql中最简单快速的优化方法——添加索引。 一、索引的添加 ...
sql优化，索引的使用场景
搬砖的鱼的博客
04-02 386
索引使用 1.1.1 适合使用索引的场景 主键自动创建唯一索引 频繁作为查询条件的字段 查询中与其他表关联的字段 查询中排序的字段 查询中统计或分组字段 1.1.2 不适合使用索引的场景 频繁更新的字段 where 条件中用不到的字段 表记录太少 经常增删改的表 字段的值的差异性不大或重复性高 1.1.3 索引创建和使用原则 单表查询：哪个列作查询条件，就在该列创建索引 多表查询：left jo...
SQL索引的使用
笑看人生的博客
08-08 1967
通过explain ， 查看执行计划，执行SQL时是否使用索引 1.避免索引失效 1). 全值匹配 ，对索引中所有列都指定具体值。 该情况下，索引生效，执行效率高。 2). 最左前缀法则 如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始，并且不跳过索引中的列。 匹配最左前缀法则，走索引： 违背最左前缀法则 ， 索引失效： 如果符合最左法则，但是出现跳跃某一列，只有最...
SQL优化基础 使用索引（一个小例子）
09-11
综上所述，SQL优化的核心在于合理地创建和使用索引。非聚集索引能减少全表扫描，组合索引可以针对多字段查询优化，而覆盖索引则能在不回表的情况下提供所需数据，减少额外的I/O操作。在实际应用中，应根据查询条件和...
OracleSQL优化-中级篇.rar
11-15
中级篇的Oracle SQL优化通常涉及更深入的技术和策略，包括但不限于查询优化、索引优化、表设计优化以及资源管理。以下是对这些知识点的详细说明： 1. 查询优化：查询优化是SQL优化的核心，它涉及如何最有效地执行...
sql优化中出现的索引合并问题(多条件查询时使用单索引还是联合索引)
12-14
在SQL优化过程中，索引合并问题是一个常见的性能瓶颈，特别是在多条件查询时。索引设计是数据库性能调优的关键因素，正确地选择单索引和联合索引对于提高查询速度和减少I/O操作至关重要。本篇文章将深入探讨索引合并...
SQLServer性能优化篇
07-23
资源名称：SQLServer性能优化篇内容简介： 本文档主要讲述的是SQLServer性能优化;在良好的数据库设计基础上，能有效地使用索引是SQL Server取得高性能的基础，SQL Server采用基于代价的优化模型，它对每一个提交的...
Sql查询优化 数据库索引使用及优化
Oumuv的博客
03-23 308
可能导致查询缓慢的原因 数据量过大 表设计不合理 sql语句写得不好 没有合理使用索引 SQL查询语句的优化 查询语句中不要使用 * 尽量减少子查询，使用关联查询（left join,right join,inner join）替代 减少使用IN或者NOT IN ,使用exists，not exists或者关联查询语句替代 or 的查询尽量用 union或者union all 代替 (在确...
SQL查询性能优化原则和索引使用
最新发布
qq_48950375的博客
10-30 52
使用合适的索引：确保表中的列都有适当的索引，尤其是经常用于搜索和连接的列。避免全表扫描。使用EXPLAIN分析查询：使用语句来分析查询执行计划，以确定查询是否有效使用了索引。限制结果集的大小：仅检索需要的列，避免使用，并使用来限制结果集的大小。避免使用通配符%开头的LIKE查询：以开头的查询通常无法利用索引，可以考虑使用全文搜索引擎或其他搜索优化方法。缓存查询结果：对于不经常更改的查询，可以使用缓存来存储查询结果，以减少数据库服务器的负载。使用连接适当：使用INNER JOIN、LEFT JOIN等连接类型
SQL索引的使用以及优化
m0_50677223的博客
03-24 769
来自B站尚硅谷课程总结。
SQL优化案例-正确的使用索引（二）
05-25 104
下面sql30秒执行出结果，查看sql谓词中有like，我们知道谓词中有这样的语句是不走索引的（为了保护客户的隐私，表名和部分列已经重命名）。 点击(此处)折叠或打开 ...
SQL索引结构、使用、优化-深入浅出—看不懂你来锤鹅
看一看吧，说不定就学到新知识了呢。
08-14 1495
数据库索引使用、优化、结构举例详解。
SQL优化（二）、2.2.1如何使用索引
小浪与艳花
11-02 242
1111
SQL优化(索引问题)
感悟人生
09-25 3101
摘自: www.phpv.net 随着“金盾工程”建设的逐步深入和公安信息化的高速发展，公安计算机应用系统被广泛应用在各警种、各部门。与此同时，应用系统体系的核心、系统数据的存放地――数据库也随着实际应用而急剧膨胀，一些大规模的系统，如人口系统的数据甚至超过了1000万条，可谓海量。那么，如何实现快速地从这些超大容量的数据库中提取数据（查询）、分析、统计以及提取数据后进行数据分页已成为各地系统管理
SQL 索引的用法
wobuwei的专栏
08-26 5339
下面测试的数据是3852916条记录。测试环境是 os：windows xp sp2， 内存：1G，cpu：双核 2.66 GHZ。(1)ItemTransaction 表什么都没有，没有主键，没有外键，没有索引。declare @d datetimeset @d=getdate()select * from Itemtransactionwhere
SQL-索引的使用
weixin_42060331的博客
03-30 872
3.1 索引的使用：索引使用时满足最左前缀匹配原则，即对于组合索引(col1, col2)，在不考虑引擎优化时，条件必须是col1在前col2在后，或者只使用col1，索引才会生效；在列上进行运算后不会使用索引，如year(start_time)<2020不会使用start_time上的索引。一个查询只使用一次索引，where中如果使用了索引，order by就不会使用。like做字段比较时只有前缀确定时才会使用索引。索引创建、删除与使用：（注意：要使用大写）索引不包含有NULL值的列。
Oracle SQL优化：理解组合索引的使用
"这篇文档是关于Oracle SQL优化的教程，特别强调了组合索引的使用。文中通过示例解释了当查询只涉及组合索引的非首列时，Oracle优化器可能不会选择使用该索引，导致全表扫描。此外，文档还提供了SQL优化的基础知识，...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值