草庐IT

SQL的索引详细介绍

阑珊处的秋月 2023-10-24 原文

 文章目录

前言

一、MySQL索引 

1、聚集索引(主键索引)

2、二级索引（辅助索引）

3、基数、选择性、回表

二、MySQL扩展

总结

前言

        大多数性能问题都和SQL语句有关，本文主要介绍了MySQL中的索引以及索引的重要性。

一、MySQL索引 

        索引的定义：在关系型数据库中，索引是一种单独的、物理的对数据库表中一列或多列的值进行排序的一种存储结构，它是某个表中一列或若干列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单（类似于图书目录，通过图书页码迅速找到所需内容）。一个索引是存储的表中一个特定列的值数据结构。索引是在表的列上创建。索引包含一个表中列的值，并且这些值存储在一个数据结构中。

优点：提高数据检索的效率，降低数据排序的成本。
缺点：会降低更新表的速度。

1、聚集索引(主键索引)

        聚集索引是将主键与行记录存储在一起，当根据主键进行查询时，可直接在表中获取到数据，不用回表查询。InonDB的所有的表都是索引组织表，主键与数据存放在一起。InnoDB选择聚集索引遵循以下原则：

  • 在创建表时，如果指定了主键，则将其作为聚集索引。
  • 如果没有指定主键，则选择第一个NOT NULL（非空）的唯一索引作为聚集索引。
  • 如果没有唯一索引，则内部会产生一个6字节的rowID（主键值）作为主键 

2、二级索引（辅助索引）

二级索引的叶子结点存储了索引值+rowID（主键值） 。

建议：在MySQL中创建表时，尽量指定一个显示的自增主键

优势：显示指定的主键可以是普通的int类型，这样存储空间就是4字节，在二级索引的叶子结点中存储主键的所占用空间就会变小

扩展：二级子节点之所以选择存储主键的值，而不是存储主键的指针，是为了避免如果主键位置发生变化时，需要修改二级索引的叶子节点对应存储的指针。 

3、基数、选择性、回表

  • 基数：字段distinct后的值，主键或NOT NULL（非空）的唯一索引的基数等于表的总行数。
  • 选择性：指基数与总行数的比值乘以100%，通常表示在字段上是否适合创建索引。
  • 回表：当需要查询的字段不能在索引中完全获取时，需要回表查询取出所需的数据。

为了体现出基数的重要性，做下列测试： 

❶测试相同数据值的cost值的消耗

Ⅰ、在数据库中随便选择一个数据表。

Ⅱ、在数据库中创建一个新表 t1 。

create table t1 (id int , c1 char(20),c2 char(20),c3 char(20));

Ⅲ、插入6条values值相同的测试数据。

insert into t1 values (10,'a','b','c');

Ⅸ、为 t1 表添加索引。

create index idx_cl on t1 (c1);

Ⅴ、通过执行计划查看cost值的消耗。（已创建索引）

explain format=json select * from t1 where c1 ='a';

Ⅵ、删除索引。

drop index idx_cl on t1;

Ⅶ、再次通过执行计划查看cost值的消耗。（未创建索引）

explain format=json select * from t1 where c1 ='a';

总结：对比两次查询的cost值，通过索引查询的cost值比全表扫描的cost值打。通过索引查询时，索引数据都是重复的（基数低），即做了一个索引全扫描，同时     “ SELECT * ”扫描完索引后要回表查询 id , c2 , c3 这几个字段。

❷清空数据表方便做不同值对比

Ⅷ、清空t1表

truncate table t1;

❸测试不同数据值的cost值的消耗

Ⅸ、插入6条测试数据，其中 c1 为不同的值。

insert into t1 values (10,'a','b','c');

Ⅹ、通过执行计划查看cost值的消耗。（未创建索引）

explain format=json select * from t1 where c1 ='a';

Ⅺ、为 t1 表添加索引。

 create index idx_cl on t1 (c1);

Ⅻ、通过执行计划查看cost值的消耗。（已创建索引）

explain format=json select * from t1 where c1 ='a';

总结：测试不同数据值时，因为字段的值不重复（基数高），通过索引查询的cost值比全表扫描的cost值小。

        一般情况下，在创建索引的时候就应该考虑上面的内容(回表、基数、选择性)，在MySQl中通过系统表innodb_index_stats来查看索引选择性如何、看到组合索引中每个字段的选择性，以及计算索引大小。 

 二、MySQL扩展

① SQL优化的重点是：减少SQL语句的扫描行数

② 索引：是一种数据结构，通过缩小一张表中需要查询的记录/行的数目来加快搜索的速度。

③ MySQL存储引擎包括：

  • InnoDB存储引擎
  • MyISAM存储引擎
  • MERGE存储引擎
  • MEMORY存储引擎
  • ARCHIVE存储引擎
  • CSV存储引擎
  • BLACKHOLE存储引擎（黑洞引擎）
  • PERFORMANCE_SCHEMA存储引擎
  • Federated存储引擎

④ InnoDB 和 MyISAM区别：

  • InnoDB支持主外键、事务；
  • InnoDB是行锁，操作时候只锁一行数据，适合高并发；MyISAM是表索；
  • InnoDB不仅缓存索引，还缓存真实数据；MyISAM只缓存索引；
  • InnoDB需要表空间大；
  • InnoDB关注事务，MyISAM关注性能；

总结

        创建索引确实会使查询操作变得更加快速，但不能盲目的创建索引，应只为那些查询操作频繁的列创建索引，因为索引会降低添加、删除、更新操作的速度，执行这些操作的同时会对索引文件进行重新排序或更新。

 

SQL的xffimgimg-blog数据库索引

有关SQL的索引详细介绍的更多相关文章

  1. Unity 热更新技术 | （三） Lua语言基本介绍及下载安装 - 2

    ?博客主页：https://xiaoy.blog.csdn.net?本文由呆呆敲代码的小Y原创，首发于CSDN??学习专栏推荐：Unity系统学习专栏?游戏制作专栏推荐：游戏制作?Unity实战100例专栏推荐：Unity实战100例教程?欢迎点赞?收藏⭐留言?如有错误敬请指正！?未来很长，值得我们全力奔赴更美好的生活✨------------------❤️分割线❤️-------------------------

  2. 在VMware16虚拟机安装Ubuntu详细教程 - 2

    在VMware16.2.4安装Ubuntu一、安装VMware1.打开VMwareWorkstationPro官网，点击即可进入。2.进入后向下滑动找到Workstation16ProforWindows，点击立即下载。3.下载完成，文件大小615MB，如下图：4.鼠标右击，以管理员身份运行。5.点击下一步6.勾选条款，点击下一步7.先勾选，再点击下一步8.去掉勾选，点击下一步9.点击下一步10.点击安装11.点击许可证12.在百度上搜索VM16许可证，复制填入，然后点击输入即可，亲测有效。13.点击完成14.重启系统，点击是15.双击VMwareWorkstationPro图标，进入虚拟机主

  3. Hive SQL 五大经典面试题 - 2

    目录第1题连续问题分析：解法：第2题分组问题分析：解法：第3题间隔连续问题分析：解法：第4题打折日期交叉问题分析：解法：第5题同时在线问题分析：解法：第1题连续问题如下数据为蚂蚁森林中用户领取的减少碳排放量iddtlowcarbon10012021-12-1212310022021-12-124510012021-12-134310012021-12-134510012021-12-132310022021-12-144510012021-12-1423010022021-12-154510012021-12-1523.......找出连续3天及以上减少碳排放量在100以上的用户分析：遇到这类

  4. sql - 查询忽略时间戳日期的时间范围 - 2

    我正在尝试查询我的Rails数据库(Postgres)中的购买表，我想查询时间范围。例如，我想知道在所有日期的下午2点到3点之间进行了多少次购买。此表中有一个created_at列，但我不知道如何在不搜索特定日期的情况下完成此操作。我试过:Purchases.where("created_atBETWEEN?and?",Time.now-1.hour,Time.now)但这最终只会搜索今天与那些时间的日期。 最佳答案 您需要使用PostgreSQL'sdate_part/extractfunction从created_at中提取小时

  5. ruby-on-rails - 协会的 Rails 索引 - 2

    我发现自己需要这个。假设cart是一个包含用户列表的模型。defindex_of_itemcart.users.each_with_indexdo|u,i|ifu==current_userreturniendend获取此类关联索引的更简单方法是什么？ 最佳答案 indexArray上的方法与您的index_of_item方法相同，例如cart.users.index(current_user)返回数组中第一个对象的索引==给obj。如果未找到匹配项，则返回nil。 关于ruby-on-

  6. ruby - Rails -- :id attribute? 所需的数据库索引 - 2

    因此，当我遵循MichaelHartl的RubyonRails教程时，我注意到在用户表中，我们为:email属性添加了一个唯一索引，以提高find的效率方法，因此它不会逐行搜索。到目前为止，我们一直在根据情况使用find_by_email和find_by_id进行搜索。然而，我们从未为:id属性设置索引。:id是否自动索引，因为它在默认情况下是唯一的并且本质上是顺序的？或者情况并非如此，我应该为:id搜索添加索引吗？ 最佳答案 大多数数据库(包括sqlite，这是RoR中的默认数据库)会自动索引主键，对于RailsMigration

  7. ruby - 引用具有指定索引的枚举器值 - 2

    假设我有一个可枚举对象enum，现在我想获取第三个项目。我知道一种通用方法是转换成数组，然后使用索引访问，如:enum.to_a[2]但这种方式会创建一个临时数组，效率可能很低。现在我使用:enum.each_with_index{|v,i|breakvifi==2}但这非常丑陋和多余。执行此操作最有效的方法是什么？ 最佳答案 你可以使用take剥离前三个元素，然后剥离last从take给你的数组中获取第三个元素:third=enum.take(3).last如果您根本不想生成任何数组，那么也许:#Ifenumisn'tanEnum

  8. sql - 在 Rails Console for PostgreSQL 的表中显示数据 - 2

    我找到了这样的东西:Rails:Howtolistdatabasetables/objectsusingtheRailsconsole?这一行没问题:ActiveRecord::Base.connection.tables并返回所有表但是ActiveRecord::Base.connection.table_structure("users")产生错误:ActiveRecord::Base.connection.table_structure("projects")我认为table_structure不是Postgres方法。如何列出Postgres数据库的Rails控制台中表中的所有

  9. ruby - 将 Logstash 中的时间戳时区转换为输出索引名称 - 2

    在我的场景中，Logstash收到的系统日志行的“时间戳”是UTC，我们在Elasticsearch输出中使用事件“时间戳”:output{elasticsearch{embedded=>falsehost=>localhostport=>9200protocol=>httpcluster=>'elasticsearch'index=>"syslog-%{+YYYY.MM.dd}"}}我的问题是，在UTC午夜，Logstash在外时区(GMT-4=>America/Montreal)结束前将日志发送到不同的索引，并且索引在20小时(晚上8点)之后没有日志，因为“时间戳”是UTC。我们已

  10. ruby - 防止SQL注入(inject)/好的Ruby方法 - 2

    Ruby中防止SQL注入(inject)的好方法是什么？ 最佳答案 直接使用ruby？使用准备好的语句:require'mysql'db=Mysql.new('localhost','user','password','database')statement=db.prepare"SELECT*FROMtableWHEREfield=?"statement.execute'value'statement.fetchstatement.close 关于ruby-防止SQL注入(inject

随机推荐