索引_索引 面试 - CSDN
索引 订阅
在关系数据库中,索引是一种单独的、物理的对数据库表中一列或多列的值进行排序的一种存储结构,它是某个表中一列或若干列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单。索引的作用相当于图书的目录,可以根据目录中的页码快速找到所需的内容。索引提供指向存储在表的指定列中的数据值的指针,然后根据您指定的排序顺序对这些指针排序。数据库使用索引以找到特定值,然后顺指针找到包含该值的行。这样可以使对应于表的SQL语句执行得更快,可快速访问数据库表中的特定信息。当表中有大量记录时,若要对表进行查询,第一种搜索信息方式是全表搜索,是将所有记录一一取出,和查询条件进行一一对比,然后返回满足条件的记录,这样做会消耗大量数据库系统时间,并造成大量磁盘I/O操作;第二种就是在表中建立索引,然后在索引中找到符合查询条件的索引值,最后通过保存在索引中的ROWID(相当于页码)快速找到表中对应的记录。 展开全文
在关系数据库中,索引是一种单独的、物理的对数据库表中一列或多列的值进行排序的一种存储结构,它是某个表中一列或若干列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单。索引的作用相当于图书的目录,可以根据目录中的页码快速找到所需的内容。索引提供指向存储在表的指定列中的数据值的指针,然后根据您指定的排序顺序对这些指针排序。数据库使用索引以找到特定值,然后顺指针找到包含该值的行。这样可以使对应于表的SQL语句执行得更快,可快速访问数据库表中的特定信息。当表中有大量记录时,若要对表进行查询,第一种搜索信息方式是全表搜索,是将所有记录一一取出,和查询条件进行一一对比,然后返回满足条件的记录,这样做会消耗大量数据库系统时间,并造成大量磁盘I/O操作;第二种就是在表中建立索引,然后在索引中找到符合查询条件的索引值,最后通过保存在索引中的ROWID(相当于页码)快速找到表中对应的记录。
信息
作    用
应于表的SQL语句执行得更快
分    类
数据库概念
中文名
索引
外文名
index
索引发展历史
旧称通检、备检或引得。组成的基本单位是索引款目。款目一般包括索引词、说明或注释语 、出处3项内容 。所有索引款目实现有序化编排。其本质特征是只揭示内容出处或文献线索 ,并不直接提供事实、资料本身。主要功能是为人们准确、迅速地获得文献资料提供线索性指引。常见的索引主要有报刊论文资料索引、文集篇目索引、语词索引、文句索引、关键词索引、专名索引、主题索引等。索引最早出现于西方,主要是中世纪欧洲宗教著作的索引。18世纪以后西方开始有主题索引,至19世纪末,内容分析索引被广泛使用。中国的索引出现较晚。一般认为,明末傅山所编的《两汉书姓名韵》是现存最早的人名索引。清代乾嘉时期,章学诚曾力倡编纂群书综合索引。20世纪20年代,随着西方索引理论与编制技术的传入,中国现代意义上的索引编制与研究才蓬勃展开 。1930年钱亚新发表《索引和索引法》,1932年洪业发表《引得说》,标志着具有中国特色的现代索引理论、技术已迅速发展起来。20世纪50年代,计算机技术被运用于索引编制 。此后,机编索引的大量出现,使索引编制理论、技术、索引载体形式发生了深刻变革。SQL标准中没有涉及索引,但商用关系数据库管理系统一般都支持索引机制,只是不同的关系数据库管理系统支持的索引类型不尽相同。索引已经成为关系数据库非常重要的部分。它们被用作包含所关心数据的表指针。通过一个索引,能从表中直接找到一个特定的记录,而不必连续顺序扫描这个表,一次一个地去查找。对于大的表,索引是必要的。没有索引,要想得到一个结果要等好几个小时、好几天,而不是几秒钟。 [1] 
收起全文
精华内容
参与话题
  • 索引

    千次阅读 多人点赞 2019-07-10 23:29:19
    1、索引是什么  索引(Index)是帮助MySQL高效获取数据的数据结构。可以得到索引的本质:索引是数据结构。 可以理解为“排好序的快速查找数据结构” 在数据之外,数据库系统还维护着满足特定查找算法的数据结构...

    1、索引是什么

      索引(Index)是帮助MySQL高效获取数据的数据结构。可以得到索引的本质:索引是数据结构。
      可以理解为“排好序的快速查找数据结构”
      在数据之外,数据库系统还维护着满足特定查找算法的数据结构,这些数据结构以某种方式引用(指向)数据,
    这样就可以在这些数据结构上实现高级查找算法,这种数据结构就是索引。
    MySQL索引的建立对于MySQL的高效运行是很重要的,索引可以大大提高MySQL的检索速度。
    打个比方,如果合理的设计且使用索引的MySQL是一辆兰博基尼的话,那么没有设计和使用索引的MySQL就是一个人力三轮车。
    创建索引时,你需要确保该索引是应用在 SQL 查询语句的条件(一般作为 WHERE 子句的条件)。

    2、优势

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

    3、劣势

      实际上索引也是一张表,该表保存了主键与索引字段,并指向实体表的记录,所以索引列也是要占空间的。
      虽然索引大大提高了查询速度,同时确会降低更新表的速度,如对表进行INSERT、UPDATE、DELETE。
      因为更新表时,MySQL不仅要保存数据,还要保存一下索引文件每次更新添加了索引列的字段。
    都会调整因为更新所带来的键值变化后的索引信息。 
    建立索引会占用磁盘空间的索引文件。

    4、索引的分类

      主键索引、唯一索引、普通索引、全文索引、组合索引

    5、基本语法

    1,INDEX(普通索引):

    ALTER TABLE 'table_name' ADD INDEX index_name('col')

    最基本的索引,没有任何限制

    2,UNIQUE(唯一索引):

    ALTER TABLE 'table_name' ADD UNIQUE('col')

    与“普通索引”类似,不同的就是:索引列的值必须唯一,但允许有空值。

    3,PRIMARY KEY(主键索引):

    ALTER TABLE 'table_name' ADD PRIMARY KEY('col')

    是一种特殊的唯一索引,不允许有空值。
    4,FULLTEXT(全文索引):

    ALTER TABLE 'table_name' ADD FULLTEXT('col')

    仅可用于MyISAM和InoDB,针对较大的数据,生成全文索引很耗时耗空间
    5,组合索引:

    ALTER TABLE 'table_name' ADD INDEX index_name('col1','col2','col3')

    为了更多的提高mysql效率可建立组合索引,遵循“最左前缀”原则。创建复合索引应该将最常用(频率)做限制条件的列放在最左边,一次递减。组合索引最左字段用in是可以用到索引的。相当于建立了col1,col1col2,col1col2col3三个索引
    修改索引名称(mysql)
    对于MySQL 5.7及以上版本,可以执行以下命令:

    ALTER TABLE tbl_name RENAME INDEX old_index_name TO new_index_name

    对于MySQL 5.7以前的版本,可以执行下面两个命令:

    ALTER TABLE tbl_name DROP INDEX old_index_name

    ALTER TABLE tbl_name ADD INDEX new_index_name(column_name)

        
    6、哪些情况需要创建索引
        ①主键自动建立唯一索引
        ②频繁作为查询条件的字段应该创建索引
        ③查询中与其他表关联的字段,外键关系建立索引
        ④频繁更新的字段不适合建立索引,因为每次更新不单单是更新了记录还会更新索引
        ⑤WHERE条件里用不到的字段不创建索引
        ⑥单键/组合索引的选择问题,who?(在高并发下倾向创建组合索引)
        ⑦查询中排序的字段,排序的字段若通过索引去访问将大大提高排序速度
        ⑧查询中统计或者分组字段
    7、哪些情况不要创建索引
      ①表记录太少
      ②经常增删改的表
        提高了查询速度,同时却会降低更新表的速度,如对表进行INSERT、UPDATE、和DELETE。
        因为更新表时,MySQL不仅要保存数据,还要保存一下索引文件。
        数据重复且分布平均的表字段,因此应该只为最经常查询和最经常排序的数据建立索引。
      ③注意,如果某个数据列包含许多重复的内容,为它建立索引就没有太大的实际效果。

    索引使用注意事项

    1,不要滥用索引

    ①,索引提高查询速度,却会降低更新表的速度,因为更新表时,mysql不仅要更新数据,保存数据,还要更新索引,保存索引
    ②,索引会占用磁盘空间

    2,索引不会包含含有NULL值的列

    复合索引只要有一列含有NULL值,那么这一列对于此符合索引就是无效的,因此我们在设计数据库设计时不要让字段的默认值为NULL。

    3,MySQL查询只是用一个索引

    如果where字句中使用了索引的话,那么order by中的列是不会使用索引的

    4,like

    like '%aaa%'不会使用索引而like "aaa%"可以使用索引

    转载于:https://www.cnblogs.com/BrokenHeart/p/10616159.html

    展开全文
  • 索引的实现原理

    万次阅读 多人点赞 2017-09-17 21:48:37
    这篇文章是介绍MySQL数据库中的索引是如何根据需求一步步演变最终成为B+树结构的以及针对B+树索引的查询,插入,删除,更新等操作的处理方法。Oracle和DB2数据库索引的实现基本上也是大同小异的。文章写得很通俗易懂...

    这篇文章是介绍MySQL数据库中的索引是如何根据需求一步步演变最终成为B+树结构的以及针对B+树索引的查询,插入,删除,更新等操作的处理方法。Oracle和DB2数据库索引的实现基本上也是大同小异的。文章写得很通俗易懂,就转在这了。关于B+树和索引内部结构可以参考:《B 树、B- 树、B+ 树和B* 树》和《深入理解DB2索引(Index)》。


    00 – 背景知识

    - B-Tree & B+Tree

      http://en.wikipedia.org/wiki/B%2B_tree
      http://en.wikipedia.org/wiki/B-tree

    - 折半查找(Binary Search)

      http://en.wikipedia.org/wiki/Binary_search_algorithm

    - 数据库的性能问题

      A. 磁盘IO性能非常低,严重的影响数据库系统的性能。
      B. 磁盘顺序读写比随机读写的性能高很多。

    - 数据的基本存储结构

      A. 磁盘空间被划分为许多大小相同的块(Block)或者页(Page).
      B. 一个表的这些数据块以链表的方式串联在一起。
      C. 数据是以行(Row)为单位一行一行的存放在磁盘上的块中,如图所示.
      D. 在访问数据时,一次从磁盘中读出或者写入至少一个完整的Block。

                                                  Fig. 1


    01 – 数据基本操作的实现

      基本操作包括:INSERT、UPDATE、DELETE、SELECT。

    - SELECT

      A. 定位数据
      B. 读出数据所在的块,对数据加工
      C. 返回数据给用户

    - UPDATE、DELETE

      A. 定位数据
      B. 读出数据所在的块,修改数据
      C. 写回磁盘

    - INSERT

      A. 定位数据要插入的页(如果数据需要排序)
      B. 读出要插入的数据页,插入数据.
      C. 写回磁盘

    如何定位数据?
    - 表扫描(Table Scan)

      A. 从磁盘中依次读出所有的数据块,一行一行的进行数据匹配。
      B. 时间复杂度 是O(n), 如果所有的数据占用了100个块。尽管只查询一行数据,
         也需要读出所有100个块的数据。
      C. 需要大量的磁盘IO操作,极大的影响了数据定位的性能。

    因为数据定位操作是所有数据操作必须的操作,数据定位操作的效率会直接影响所有的数据操作的效率。
    因此我们开始思考,如何来减少磁盘的IO?
    - 减少磁盘IO

      A. 减少数据占用的磁盘空间
         压缩算法、优化数据存储结构
      B. 减少访问数据的总量
         读出或写入的数据中,有一部分是数据操作所必须的,这部分称作有效数据。剩余的
         部分则不是数据操作必须的数据,称为无效数据。例如,查询姓名是‘张三’的记录。
         那么这条记录是有效记录,其他记录则是无效记录。我们要努力减少无效数据的访问。

    02 – 索引的产生

    - 键(Key)

      首先,我们发现在多数情况下,定位操作并不需要匹配整行数据。而是很规律的只匹配某一个
      或几个列的值。 例如,图中第1列就可以用来确定一条记录。这些用来确定一条数据的列,统 
      称为键(Key).

                        Fig. 2

    - Dense Index

      根据减少无效数据访问的原则,我们将键的值拿过来存放到独立的块中。并且为每一个键值添
      加一个指针, 指向原来的数据块。如图所示,


                                Fig. 3

      这就是‘索引’的祖先Dense Index. 当进行定位操作时,不再进行表扫描。而是进行
      索引扫描(Index Scan),依次读出所有的索引块,进行键值的匹配。当找到匹配的键值后,
      根据该行的指针直接读取对应的数据块,进行操作。假设一个块中能存储100行数据,
      10,000,000行的数据需要100,000个块的存储空间。假设键值列(+指针)占用一行数据
      1/10的空间。那么大约需要10,000个块来存储Dense索引。因此我们用大约1/10的额外存储
      空间换来了大约全表扫描10倍的定位效率。

    03 – 索引的进化

      在实际的应用中,这样的定位效率仍然不能满足需求。很多人可能已经想到了,通过排序和查找
      算法来减少IO的访问。因此我们开始尝试对Dense Index进行排序存储,并且期望利用排序查
      找算法来减少磁盘IO。

    - 折半块查找

      A. 对Dense Index排序
      B. 需要一个数组按顺序存储索引块地址。以块为单位,不存储所有的行的地址。
      C. 这个索引块地址数组,也要存储到磁盘上。将其单独存放在一个块链中,如下图所示。
      D. 折半查找的时间复杂度是O(log 2(N))。在上面的列子中,dense索引总共有10,000个块。假设1个块
         能存储2000个指针,需要5个块来存储这个数组。通过折半块查找,我们最多只需要读取
         5(数组块)+ 14(索引块log 2(10000))+1(数据块)=20个块。


                                                                    Fig. 4

     - Sparse Index

      实现基于块的折半查找时发现,读出每个块后只需要和第一行的键值匹配,就可以决定下一个块
      的位置(方向)。 因此有效数据是每个块(最后一个块除外)的第一行的数据。还是根据减少无
      效数据IO的原则,将每一个块的第一行的数据单独拿出来,和索引数组的地址放到一起。这样就
      可以直接在这个数组上进行折半查找了。如下图所示,这个数组就进化成了Sparse Index


                                                            Fig. 5

      因为Sparse Index和Dense Index的存储结构是相同的,所以占用的空间也相同。大约需
      要10个块来存储10000个Dense Index块的地址和首行键值。通过Sparse索引,仅需要读
      取10(Sparse块)+1(Dense块)+1(数据块)=12个块.

    - 多层Sparse Index

      因为Sparse Index本身是有序的,所以可以为Sparse Index再建sparse Index。通过
      这个方法,一层一层的建立 Sparse Indexes,直到最上层的Sparse Index只占用一个块
      为止,如下图所示.


                                           Fig. 6

      A. 这个最上层的Sparse Index称作整个索引树的根(root).
      B. 每次进行定位操作时,都从根开始查找。
      C. 每层索引只需要读出一个块。
      D. 最底层的Dense Index或数据称作叶子(leaf).
      E. 每次查找都必须要搜索到叶子节点,才能定位到数据。
      F. 索引的层数称作索引树的高度(height).
      G. 索引的IO性能和索引树的高度密切相关。索引树越高,磁盘IO越多。

      在我们的例子中的Sparse Index,只有10个块,因此我们只需要再建立一个Sparse Index.
      通过两层Sparse Index和一层Dense Index查找时,只需读取1+1+1+1=4个块。

    - Dense Index和Sparse Index的区别

      A. Dense Index包含所有数据的键值,但是Sparse Index仅包含部分键值。
         Sparse Index占用更少的磁盘空间。
      B. Dense Index指向的数据可以是无序的,但是Sparse Index的数据必须是有序的。
      C. Sparse Index 可以用来做索引的索引,但是Dense Index不可以。
      D. 在数据是有序的时候,Sparse Index更有效。因此Dense Index仅用于无序的数据。
      E. 索引扫描(Index Scan)实际上是对Dense Index层进行遍历。

    - 簇索引(Clustered Index)和辅助索引(Secondary Index)

      如果数据本身是基于某个Key来排序的,那么可以直接在数据上建立sparse索引,
      而不需要建立一个dense索引层(可以认为数据就是dense索引层)。 如下图所示:


                                                    Fig. 7

      这个索引就是我们常说的“Clustered Index”,而用来排序数据的键叫做主键Primary Key.

      A. 一个表只能有一个Clustered Index,因为数据只能根据一个键排序.
      B. 用其他的键来建立索引树时,必须要先建立一个dense索引层,在dense索引层上对此键的值
         进行排序。这样的索引树称作Secondary Index.
      C. 一个表上可以有多个Secondary Index.
      D. 对簇索引进行遍历,实际上就是对数据进行遍历。因此簇索引的遍历效率比辅组索引低。
         如SELECT count(*) 操作,使用辅组索引遍历的效率更高。

    - 范围搜索(Range Search)

      由于键值是有序的,因此可以进行范围查找。只需要将数据块、Dense Index块分别以双向链表
      的方式进行连接, 就可以实现高效的范围查找。如下图所示:


                                                    Fig. 8   范围查找的过程:   A. 选择一个合适的边界值,定位该值数据所在的块   B. 然后选择合适的方向,在数据块(或Dense Index块)链中进行遍历。   C. 直到数据不满足另一个边界值,结束范围查找。 是不是看着这个索引树很眼熟?换个角度看看这个图吧!


        Fig. 9

    这分明就是传说中的B+Tree.
    - 索引上的操作
      A. 插入键值
      B. 删除键值
      C. 分裂一个节点
      D. 合并两个节点
    这些操作在教科书上都有介绍,这里就不介绍了。
    先写到这吧,实在写不动了,想明白容易,写明白就难了。下一篇里,打算谈谈标准B+Tree的几个问题,以及在
    实现过程中,B+Tree的一些变形。

    教科书上的B+Tree是一个简化了的,方便于研究和教学的B+Tree。然而在数据库实现时,为了
    更好的性能或者降低实现的难度,都会在细节上进行一定的变化。下面以InnoDB为例,来说说
    这些变化。

    04 - Sparse Index中的数据指针

      在“由浅入深理解索引的实现(1)”中提到,Sparse Index中的每个键值都有一个指针指向
      所在的数据页。这样每个B+Tree都有指针指向数据页。如图Fig.10所示:


    Fig.10

      如果数据页进行了拆分或合并操作,那么所有的B+Tree都需要修改相应的页指针。特别是
      Secondary B+Tree(辅助索引对应的B+Tree), 要对很多个不连续的页进行修改。同时也需要对
      这些页加锁,这会降低并发性。

      为了降低难度和增加更新(分裂和合并B+Tree节点)的性能,InnoDB 将 Secondary B+Tree中
      的指针替换成了主键的键值。如图Fig.11所示:


    Fig.11

      这样就去除了Secondary B+Tree对数据页的依赖,而数据就变成了Clustered B+Tree(簇
      索引对应的B+Tree)独占的了。对数据页的拆分及合并操作,仅影响Clustered B+Tree. 因此
      InnoDB的数据文件中存储的实际上就是多个孤立B+Tree。

      一个有趣的问题,当用户显式的把主键定义到了二级索引中时,还需要额外的主键来做二级索引的
      数据吗(即存储2份主键)? 很显然是不需要的。InnoDB在创建二级索引的时候,会判断主键的字段
      是否已经被包含在了要创建的索引中。

      接下来看一下数据操作在B+Tree上的基本实现。

    - 用主键查询

      直接在Clustered B+Tree上查询。

    - 用辅助索引查询
      A. 在Secondary B+Tree上查询到主键。
      B. 用主键在Clustered B+Tree

    可以看出,在使用主键值替换页指针后,辅助索引的查询效率降低了。
      A. 尽量使用主键来查询数据(索引遍历操作除外).
      B. 可以通过缓存来弥补性能,因此所有的键列,都应该尽量的小。

    - INSERT
      A. 在Clustered B+Tree上插入数据
      B. 在所有其他Secondary B+Tree上插入主键。

    - DELETE
      A. 在Clustered B+Tree上删除数据。
      B. 在所有其他Secondary B+Tree上删除主键。

    - UPDATE 非键列
      A. 在Clustered B+Tree上更新数据。

    - UPDATE 主键列
      A. 在Clustered B+Tree删除原有的记录(只是标记为DELETED,并不真正删除)。
      B. 在Clustered B+Tree插入新的记录。
      C. 在每一个Secondary B+Tree上删除原有的数据。(有疑问,看下一节。)
      D. 在每一个Secondary B+Tree上插入原有的数据。

    - UPDATE 辅助索引的键值
      A. 在Clustered B+Tree上更新数据。
      B. 在每一个Secondary B+Tree上删除原有的主键。
      C. 在每一个Secondary B+Tree上插入原有的主键。

    更新键列时,需要更新多个页,效率比较低。
      A. 尽量不用对主键列进行UPDATE操作。
      B. 更新很多时,尽量少建索引。

    05 – 非唯一键索引

      教科书上的B+Tree操作,通常都假设”键值是唯一的“。但是在实际的应用中Secondary Index是允
      许键值重复的。在极端的情况下,所有的键值都一样,该如何来处理呢?
      InnoDB 的 Secondary B+Tree中,主键也是此键的一部分。
      Secondary Key = 用户定义的KEY + 主键。如图Fig.12所示:


    Fig.12

      注意主键不仅做为数据出现在叶子节点,同时也作为键的一部分出现非叶子节点。对于非唯一键来说,
      因为主键是唯一的,Secondary Key也是唯一的。当然,在插入数据时,还是会根据用户定义的Key,
      来判断唯一性。按理说,如果辅助索引是唯一的(并且所有字段不能为空),就不需要这样做。可是,
      InnoDB对所有的Secondary B+Tree都这样创建。

    还没弄明白有什么特殊的用途?有知道的朋友可以帮忙解答一下。
    也许是为了降低代码的复杂性,这是我想到的唯一理由。

    弄清楚了,即便是非空唯一键,在二级索引的B+Tree中也可能重复,因此必须要将主键加入到非叶子节点。

    06 – <Key, Pointer>对

      标准的B+Tree的每个节点有K个键值和K+1个指针,指向K+1个子节点。如图Fig.13:


    Fig.13(图片来自于WikiPedia)

      而在“由浅入深理解索引的实现(1)”中Fig.9的B+Tree上,每个节点有K个键值和K个指针。
      InnoDB的B+Tree也是如此。如图Fig.14所示:


    Fig.14

      这样做的好处在于,键值和指针一一对应。我们可以将一个<Key,Pointer>对看作一条记录。
      这样就可以用数据块的存储格式来存储索引块。因为不需要为索引块定义单独的存储格式,就
      降低了实现的难度。

    - 插入最小值

      当考虑在变形后的B+Tree上进行INSERT操作时,发现了一个有趣的问题。如果插入的数据的健
      值比B+Tree的最小键值小时,就无法定位到一个适当的数据块上去(<Key,Pointer>中的Key
      代表了子节点上的键值是>=Key的)。例如,在Fig.5的B+Tree中插入键值为0的数据时,无法
      定位到任何节点。

      在标准的B+Tree上,这样的键值会被定位到最左侧的节点上去。这个做法,对于Fig.5中的
      B+Tree也是合理的。Innodb的做法是,将每一层(叶子层除外)的最左侧节点的第一条记录标
      记为最小记录(MIN_REC).在进行定位操作时,任何键值都比标记为MIN_REC的键值大。因此0
      会被插入到最左侧的记录节点上。如Fig.15所示:


                                                   Fig.15

    07 – 顺序插入数据

      Fig.16是B-Tree的插入和分裂过程,我们看看有没有什么问题?


    Fig.16(图片来自于WikiPedia)

      标准的B-Tree分裂时,将一半的键值和数据移动到新的节点上去。原有节点和新节点都保留一半
      的空间,用于以后的插入操作。当按照键值的顺序插入数据时,左侧的节点不可能再有新的数据插入。
      因此,会浪费约一半的存储空间。

      解决这个问题的基本思路是:分裂顺序插入的B-Tree时,将原有的数据都保留在原有的节点上。
      创建一个新的节点,用来存储新的数据。顺序插入时的分裂过程如Fig.17所示:


    Fig.17

      以上是以B-Tree为例,B+Tree的分裂过程类似。InnoDB的实现以这个思路为基础,不过要复杂
      一些。因为顺序插入是有方向性的,可能是从小到大,也可能是从大到小的插入数据。所以要区
      分不同的情况。如果要了解细节,可参考以下函数的代码。
        btr_page_split_and_insert();
        btr_page_get_split_rec_to_right();
        btr_page_get_split_rec_to_right();

    InnoDB的代码太复杂了,有时候也不敢肯定自己的理解是对的。因此写了一个小脚本,来打印InnoDB数
    据文件中B+Tree。这样可以直观的来观察B+Tree的结构,验证自己的理解是否正确。

    很多知识来自于下面这两本书。 “Database Systems: The Complete Book (2nd Edition) ”

    “Transaction Processing: Concepts and Techniques”


    转载自:http://www.mysqlops.com/2011/11/24/understanding_index.html

    展开全文
  • 什么是索引?Mysql目前主要的几种索引类型

    万次阅读 多人点赞 2018-02-27 10:11:16
    一、索引MySQL索引的建立对于MySQL的高效运行是很重要的,索引可以大大提高MySQL的检索速度。打个比方,如果合理的设计且使用索引的MySQL是一辆兰博基尼的话,那么没有设计和使用索引的MySQL就是一个人力三轮车。...

    一、索引

    MySQL索引的建立对于MySQL的高效运行是很重要的,索引可以大大提高MySQL的检索速度。

    打个比方,如果合理的设计且使用索引的MySQL是一辆兰博基尼的话,那么没有设计和使用索引的MySQL就是一个人力三轮车。

    索引分单列索引和组合索引。单列索引,即一个索引只包含单个列,一个表可以有多个单列索引,但这不是组合索引。组合索引,即一个索引包含多个列。

    创建索引时,你需要确保该索引是应用在 SQL 查询语句的条件(一般作为 WHERE 子句的条件)。

    实际上,索引也是一张表,该表保存了主键与索引字段,并指向实体表的记录。

    上面都在说使用索引的好处,但过多的使用索引将会造成滥用。因此索引也会有它的缺点:虽然索引大大提高了查询速度,同时却会降低更新表的速度,如对表进行INSERT、UPDATE和DELETE。因为更新表时,MySQL不仅要保存数据,还要保存一下索引文件。

    建立索引会占用磁盘空间的索引文件。

    二、索引类型

    Mysql目前主要有以下几种索引类型:FULLTEXT,HASH,BTREE,RTREE。

    1. FULLTEXT

    即为全文索引,目前只有MyISAM引擎支持。其可以在CREATE TABLE ,ALTER TABLE ,CREATE INDEX 使用,不过目前只有 CHAR、VARCHAR ,TEXT 列上可以创建全文索引。

    全文索引并不是和MyISAM一起诞生的,它的出现是为了解决WHERE name LIKE “%word%"这类针对文本的模糊查询效率较低的问题。

    2. HASH

    由于HASH的唯一(几乎100%的唯一)及类似键值对的形式,很适合作为索引。

    HASH索引可以一次定位,不需要像树形索引那样逐层查找,因此具有极高的效率。但是,这种高效是有条件的,即只在“=”和“in”条件下高效,对于范围查询、排序及组合索引仍然效率不高。

    3. BTREE

    BTREE索引就是一种将索引值按一定的算法,存入一个树形的数据结构中(二叉树),每次查询都是从树的入口root开始,依次遍历node,获取leaf。这是MySQL里默认和最常用的索引类型。

    4. RTREE

    RTREE在MySQL很少使用,仅支持geometry数据类型,支持该类型的存储引擎只有MyISAM、BDb、InnoDb、NDb、Archive几种。

    相对于BTREE,RTREE的优势在于范围查找。

    ps. 此段详细内容见此片博文:Mysql几种索引类型的区别及适用情况

    三、索引种类

    • 普通索引:仅加速查询

    • 唯一索引:加速查询 + 列值唯一(可以有null)

    • 主键索引:加速查询 + 列值唯一(不可以有null)+ 表中只有一个

    • 组合索引:多列值组成一个索引,专门用于组合搜索,其效率大于索引合并

    • 全文索引:对文本的内容进行分词,进行搜索

    ps.

    索引合并,使用多个单列索引组合搜索
    覆盖索引,select的数据列只用从索引中就能够取得,不必读取数据行,换句话说查询列要被所建的索引覆盖

    四、操作索引

    1. 创建索引

    1
    --创建普通索引CREATE INDEX index_name ON table_name(col_name);--创建唯一索引CREATE UNIQUE INDEX index_name ON table_name(col_name);--创建普通组合索引CREATE INDEX index_name ON table_name(col_name_1,col_name_2);--创建唯一组合索引CREATE UNIQUE INDEX index_name ON table_name(col_name_1,col_name_2);

    2. 通过修改表结构创建索引

    1
    ALTER TABLE table_name ADD INDEX index_name(col_name);

    3. 创建表时直接指定索引

    1
    2
    3
    CREATE TABLE table_name (
        ID INT NOT NULL,col_name VARCHAR (16) NOT NULL,INDEX index_name (col_name)
    );

    4. 删除索引

    1
    --直接删除索引DROP INDEX index_name ON table_name;--修改表结构删除索引ALTER TABLE table_name DROP INDEX index_name;

    5. 其它相关命令

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    - 查看表结构
        desc table_name;
     - 查看生成表的SQL
        show create table table_name;
     - 查看索引
        show index from  table_name;
     - 查看执行时间
        set profiling = 1;
        SQL...
        show profiles;

    五、创建索引的时机

    到这里我们已经学会了建立索引,那么我们需要在什么情况下建立索引呢?一般来说,在WHERE和JOIN中出现的列需要建立索引,但也不完全如此,因为MySQL只对<,<=,=,>,>=,BETWEEN,IN,以及某些时候的LIKE才会使用索引。例如:

    1
    SELECT t.Name  FROM mytable_t LEFT JOIN mytable_m ON t.Name=m.username WHERE m.age=20 AND m.city='郑州' ;

    此时就需要对city和age建立索引,由于mytable_m表的userame也出现在了JOIN子句中,也有对它建立索引的必要。

    刚才提到只有某些时候的LIKE才需建立索引。因为在以通配符%和_开头作查询时,MySQL不会使用索引。

    六、命中索引

    数据库表中添加索引后确实会让查询速度起飞,但前提必须是正确的使用索引来查询,如果以错误的方式使用,则即使建立索引也会不奏效。
    即使建立索引,索引也不会生效:

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    - like '%xx'    select * from tb1 where name like '%cn';- 使用函数
        select * from tb1 where reverse(name) = 'wupeiqi';- or    select * from tb1 where nid = 1 or email = 'seven@live.com';
        特别的:当or条件中有未建立索引的列才失效,以下会走索引
                select * from tb1 where nid = 1 or name = 'seven';
                select * from tb1 where nid = 1 or email = 'seven@live.com' and name = 'alex'- 类型不一致
        如果列是字符串类型,传入条件是必须用引号引起来,不然...
        select * from tb1 where name = 999;- !=    select * from tb1 where name != 'alex'    特别的:如果是主键,则还是会走索引
            select * from tb1 where nid != 123- >    select * from tb1 where name > 'alex'    特别的:如果是主键或索引是整数类型,则还是会走索引
            select * from tb1 where nid > 123        select * from tb1 where num > 123- order by    select email from tb1 order by name desc;
        当根据索引排序时候,选择的映射如果不是索引,则不走索引
        特别的:如果对主键排序,则还是走索引:
            select * from tb1 order by nid desc;
     - 组合索引最左前缀
        如果组合索引为:(name,email)
        name and email       -- 使用索引    name                 -- 使用索引    email                -- 不使用索引

    七、其它注意事项

    1
    - 避免使用select *- count(1)或count(列) 代替 count(*)- 创建表时尽量时 char 代替 varchar- 表的字段顺序固定长度的字段优先- 组合索引代替多个单列索引(经常使用多个条件查询时)- 尽量使用短索引- 使用连接(JOIN)来代替子查询(Sub-Queries)- 连表时注意条件类型需一致- 索引散列值(重复多)不适合建索引,例:性别不适合

    八、LIMIT分页

    若需求是每页显示10条数据,如何建立分页?

    我们可以先使用LIMIT尝试:

    1
    --第一页SELECT * FROM table_name LIMIT 0,10;--第二页SELECT * FROM table_name LIMIT 10,10;--第三页SELECT * FROM table_name LIMIT 20,10;

    但是这样做有如下弊端:

    • 每一条select语句都会从1遍历至当前位置,若跳转到第100页,则会遍历1000条记录

    • 若记录的id不连续,则会出错

    改善:

    若已知每页的max_id和min_id,则可以通过主键索引来快速定位:

    1
    --下一页SELECT * FROM table_name WHERE id in (SELECT id FROM table_name WHERE id > max_id LIMIT 10);--上一页SELECT * FROM table_name WHERE id in (SELECT id FROM table_name WHERE id < min_id ORDER BY id DESC LIMIT 10);--当前页之后的某一页SELECT * FROM table_name WHERE id in (SELECT id FROM (SELECT id FROM (SELECT id FROM table_name WHERE id < min_id ORDER BY id desc LIMIT (页数差*10)) AS N ORDER BY N.id ASC LIMIT 10) AS P ORDER BY P.id ASC);--当前页之前的某一页SELECT * FROM table_name WHERE id in (SELECT id FROM (SELECT id FROM (SELECT id FROM table_name WHERE id > max_id LIMIT (页数差*10)) AS N ORDER BY N.id DESC LIMIT 10) AS P) ORDER BY id ASC;

    九、执行计划

    explain + 查询SQL - 用于显示SQL执行信息参数,根据参考信息可以进行SQL优化

    1
    mysql> explain select * from tb2;+----+-------------+-------+------+---------------+------+---------+------+------+-------+| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra |+----+-------------+-------+------+---------------+------+---------+------+------+-------+|  1 | SIMPLE      | tb2   | ALL  | NULL          | NULL | NULL    | NULL |    2 | NULL  |+----+-------------+-------+------+---------------+------+---------+------+------+-------+1 row in set (0.00 sec)

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    37
    38
    39
    40
    41
    42
    43
    44
    45
    46
    47
    48
    49
    50
    51
    52
    53
    54
    id查询顺序标识
                如:mysql> explain select * from (select nid,name from tb1 where nid < 10) as B;+----+-------------+------------+-------+---------------+---------+---------+------+------+-------------+| id | select_type | table      | type  | possible_keys | key     | key_len | ref  | rows | Extra       |+----+-------------+------------+-------+---------------+---------+---------+------+------+-------------+|  1 | PRIMARY     | <derived2> | ALL   | NULL          | NULL    | NULL    | NULL |    9 | NULL        ||  2 | DERIVED     | tb1        | range | PRIMARY       | PRIMARY | 8       | NULL |    9 | Using where |+----+-------------+------------+-------+---------------+---------+---------+------+------+-------------+        特别的:如果使用union连接其值可能为null
     
     
        select_type
            查询类型
                SIMPLE          简单查询PRIMARY         最外层查询
                SUBQUERY        映射为子查询
                DERIVED         子查询UNION           联合UNION RESULT    使用联合的结果
                ...table正在访问的表名
     
     
        type
            查询时的访问方式,性能:all < index < range < index_merge < ref_or_null < ref < eq_ref < system/constALL             全表扫描,对于数据表从头到尾找一遍select * from tb1;
                                特别的:如果有limit限制,则找到之后就不再继续向下扫描                                   select * from tb1 where email = 'seven@live.com'   select * from tb1 where email = 'seven@live.com' limit 1;
                                       虽然上述两个语句都会进行全表扫描,第二句使用了limit,则找到一个后就不再继续扫描。INDEX           全索引扫描,对索引从头到尾找一遍select nid from tb1;
     
                RANGE          对索引列进行范围查找select *  from tb1 where name < 'alex';
                                PS:between andin>   >=  <   <=  操作
                                    注意:!= 和 > 符号
     
     
                INDEX_MERGE     合并索引,使用多个单列索引搜索select *  from tb1 where name = 'alex' or nid in (11,22,33);
     
                REF             根据索引查找一个或多个值select *  from tb1 where name = 'seven';
     
                EQ_REF          连接时使用primary key 或 unique类型select tb2.nid,tb1.name from tb2 left join tb1 on tb2.nid = tb1.nid;
     
     
     
                CONST           常量
                                表最多有一个匹配行,因为仅有一行,在这行的列值可被优化器剩余部分认为是常数,const表很快,因为它们只读取一次。select nid from tb1 where nid = 2 ;
     
                SYSTEM          系统
                                表仅有一行(=系统表)。这是const联接类型的一个特例。select * from (select nid from tb1 where nid = 1) as A;
        possible_keys
            可能使用的索引key真实使用的
     
        key_len
            MySQL中使用索引字节长度
     
        rows
            mysql估计为了找到所需的行而要读取的行数 ------ 只是预估值extra
            该列包含MySQL解决查询的详细信息
            “Using index”
                此值表示mysql将使用覆盖索引,以避免访问表。不要把覆盖索引和index访问类型弄混了。
            “Using where”
                这意味着mysql服务器将在存储引擎检索行后再进行过滤,许多where条件里涉及索引中的列,当(并且如果)它读取索引时,就能被存储引擎检验,因此不是所有带where子句的查询都会显示“Using where”。有时“Using where”的出现就是一个暗示:查询可受益于不同的索引。
            “Using temporary”
                这意味着mysql在对查询结果排序时会使用一个临时表。
            “Using filesort”
                这意味着mysql会对结果使用一个外部索引排序,而不是按索引次序从表里读取行。mysql有两种文件排序算法,这两种排序方式都可以在内存或者磁盘上完成,explain不会告诉你mysql将使用哪一种文件排序,也不会告诉你排序会在内存里还是磁盘上完成。
            “Range checked for each record(index map: N)”
                这个意味着没有好用的索引,新的索引将在联接的每一行上重新估算,N是显示在possible_keys列中索引的位图,并且是冗余的。

    上表详解

    十、慢查询日志

    MySQL的慢查询日志是MySQL提供的一种日志记录,它用来记录在MySQL中响应时间超过阀值的语句,具体指运行时间超过long_query_time值的SQL,则会被记录到慢查询日志中。long_query_time的默认值为10,意思是运行10S以上的语句。默认情况下,MySQLl数据库并不启动慢查询日志,需要我们手动来设置这个参数,当然,如果不是调优需要的话,一般不建议启动该参数,因为开启慢查询日志会或多或少带来一定的性能影响。慢查询日志支持将日志记录写入文件,也支持将日志记录写入数据库表。

    1. 查看慢日志参数:

    1
    --查询配置命令show variables like '%query%';--当前配置参数binlog_rows_query_log_events    OFFft_query_expansion_limit    20have_query_cache    YES--时间限制,超过此时间,则记录long_query_time    10.000000query_alloc_block_size    8192query_cache_limit    1048576query_cache_min_res_unit    4096query_cache_size    1048576query_cache_type    OFFquery_cache_wlock_invalidate    OFFquery_prealloc_size    8192--是否开启慢日志记录slow_query_log    OFF--日志文件slow_query_log_file    D:\Program Files (x86)\mysql-5.7.18-winx64\data\Jack-slow.log--

    2. 修改当前配置

    1
    set global 变量名 = 值;--例如,修改时间限制为20slong_query_time = 20;

    ps.也可以直接打开慢日志配置文件进行修改,但必须重启服务才能生效

    3. 查看MySQL慢日志

    1
    mysqldumpslow -s at -a  /usr/local/var/mysql/MacBook-Pro-3-slow.log

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    """--verbose    版本--debug      调试--help       帮助 -v           版本-d           调试模式-s ORDER     排序方式
                 what to sort by (al, at, ar, c, l, r, t), 'at' is default              al: average lock time
                  ar: average rows sent
                  at: average query time
                   c: count               l: lock time
                   r: rows sent
                   t: query time-r           反转顺序,默认文件倒序拍。reverse the sort order (largest last instead of first)-t NUM       显示前N条just show the top n queries-a           不要将SQL中数字转换成N,字符串转换成S。don't abstract all numbers to N and strings to 'S'-n NUM       abstract numbers with at least n digits within names
    -g PATTERN   正则匹配;grep: only consider stmts that include this string
    -h HOSTNAME  mysql机器名或者IP;hostname of db server for *-slow.log filename (can be wildcard),
                 default is '*', i.e. match all
    -i NAME      name of server instance (if using mysql.server startup script)
    -l           总时间中不减去锁定时间;don't subtract lock time from total time
    展开全文
  • 索引的数据结构分析,数据库面试到索引最常见的问题分析,我总结了一下。

    点赞再看,养成习惯,微信搜索【三太子敖丙】我所有文章都在这里,本文 GitHub https://github.com/JavaFamily 已收录,有一线大厂面试完整考点,文末有福利

    前言

    写数据库,我第一时间就想到了MySQL、Oracle、索引、存储过程、查询优化等等。

    不知道大家是不是跟我想得一样,我最想写的是索引,为啥呢?

    以下这个面试场景,不知道大家熟悉不熟悉:

    面试官:数据库有几千万的数据,查询又很慢我们怎么办?

    面试者:加索引。

    面试官:那索引有哪些数据类型?索引是怎么样的一种结构?哪些字段又适合索引呢?B+的优点?聚合索引和非聚合索引的区别?为什么说索引会降低插入、删除、修改等维护任务的速度?……..

    面试者:面试官怎么出我们公司门来着😂。

    是的大家可能都知道慢了加索引,那为啥加,在什么字段上加,以及索引的数据结构特点,优点啥的都比较模糊或者甚至不知道。

    那我们也不多BB了,直接开始这次的面试吧。

    正文

    我看你简历上写到了熟悉MySQL数据库以及索引的相关知识,我们就从索引开始,索引有哪些数据结构?

    Hash、B+

    大家去设计索引的时候,会发现索引类型是可以选择的。

    为什么哈希表、完全平衡二叉树、B树、B+树都可以优化查询,为何Mysql独独喜欢B+树?

    我先聊一下Hash:

    大家可以先看一下下面的动图

    注意字段值所对应的数组下标是哈希算法随机算出来的,所以可能出现哈希冲突

    那么对于这样一个索引结构,现在来执行下面的sql语句:

    select * from sanguo where name='鸡蛋'

    可以直接对‘鸡蛋’按哈希算法算出来一个数组下标,然后可以直接从数据中取出数据并拿到所对应那一行数据的地址,进而查询那一行数据, 那么如果现在执行下面的sql语句:

    select * from sanguo where name>'鸡蛋'

    则无能为力,因为哈希表的特点就是可以快速的精确查询,但是不支持范围查询

    如果做成了索引,那速度也是很慢的,要全部扫描。

    问个题外话,那Hash表在哪些场景比较适合?

    等值查询的场景,就只有KV(Key,Value)的情况,例如Redis、Memcached等这些NoSQL的中间件。

    你说的是无序的Hash表,那有没有有序的数据结构?

    有序数组,它就比较优秀了呀,它在等值查询的和范围查询的时候都很Nice。

    那它完全没有缺点么?

    不是的,有序的适合静态数据,因为如果我们新增、删除、修改数据的时候就会改变他的结构。

    比如你新增一个,那在你新增的位置后面所有的节点都会后移,成本很高。

    那照你这么说他根本就不优秀啊,特点也没地方放。

    此言差矣,可以用来做静态存储引擎啊,用来保存静态数据,例如你2019年的支付宝账单,2019年的淘宝购物记录等等都是很合适的,都是不会变动的历史数据。

    有点东西啊小伙子,那二叉树呢?

    二叉树的新增和结构如图:

    二叉树的结构我就不在这里多BB了,不了解的朋友可以去看看数据结构章节。

    二叉树是有序的,所以是支持范围查询的。

    但是他的时间复杂度是O(log(N)),为了维持这个时间复杂度,更新的时间复杂度也得是O(log(N)),那就得保持这棵树是完全平衡二叉树了。

    怎么听你一说,平衡二叉树用来做索引还不错呢?

    此言差矣,索引也不只是在内存里面存储的,还是要落盘持久化的,可以看到图中才这么一点数据,如果数据多了,树高会很高,查询的成本就会随着树高的增加而增加。

    为了节约成本很多公司的磁盘还是采用的机械硬盘,这样一次千万级别的查询差不多就要10秒了,这谁顶得住啊?

    如果用B树呢?

    同理来看看B树的结构:

    可以发现同样的元素,B树的表示要比完全平衡二叉树要“矮”,原因在于B树中的一个节点可以存储多个元素。

    B树其实就已经是一个不错的数据结构,用来做索引效果还是不错的。

    那为啥没用B树,而用了B+树?

    一样先看一下B加的结构:

    我们可以发现同样的元素,B+树的表示要比B树要“胖”,原因在于B+树中的非叶子节点会冗余一份在叶子节点中,并且叶子节点之间用指针相连。

    那么B+树到底有什么优势呢?

    其实很简单,我们看一下上面的数据结构,最开始的Hash不支持范围查询,二叉树树高很高,只有B树跟B+有的一比。

    B树一个节点可以存储多个元素,相对于完全平衡二叉树整体的树高降低了,磁盘IO效率提高了。

    而B+树是B树的升级版,只是把非叶子节点冗余一下,这么做的好处是为了提高范围查找的效率

    提高了的原因也无非是会有指针指向下一个节点的叶子节点。

    小结:到这里可以总结出来,Mysql选用B+树这种数据结构作为索引,可以提高查询索引时的磁盘IO效率,并且可以提高范围查询的效率,并且B+树里的元素也是有序的。

    那么,一个B+树的节点中到底存多少个元素最合适你有了解过么?

    额这个这个?卧*有点懵逼呀。

    过了一会还是没想出,只能老实交代:这个不是很了解咳咳。

    你可以换个角度来思考B+树中一个节点到底多大合适?

    B+树中一个节点为一页或页的倍数最为合适

    为啥?

    因为如果一个节点的大小小于1页,那么读取这个节点的时候其实也会读出1页,造成资源的浪费。

    如果一个节点的大小大于1页,比如1.2页,那么读取这个节点的时候会读出2页,也会造成资源的浪费。

    所以为了不造成浪费,所以最后把一个节点的大小控制在1页、2页、3页、4页等倍数页大小最为合适。

    你提到了页的概念,能跟我简单说一下么?

    首先Mysql的基本存储结构是(记录都存在页里边):

    • 各个数据页可以组成一个双向链表

    • 每个数据页中的记录又可以组成一个单向链表

    • - 每个数据页都会为存储在它里边儿的记录生成一个页目录,在通过主键查找某条记录的时候可以在页目录中使用二分法快速定位到对应的槽,然后再遍历该槽对应分组中的记录即可快速找到指定的记录

    • 其他列(非主键)作为搜索条件:只能从最小记录开始依次遍历单链表中的每条记录

    所以说,如果我们写 select * from user where username='丙丙'这样没有进行任何优化的sql语句,默认会这样做:

    • 定位到记录所在的页

    • - 需要遍历双向链表,找到所在的页

    • 从所在的页内中查找相应的记录

    • - 由于不是根据主键查询,只能遍历所在页的单链表了

    很明显,在数据量很大的情况下这样查找会很慢!看起来跟回表有点点像。

    哦?回表你聊一下。

    卧槽,该死,我嘴干嘛。

    回表大概就是我们有个主键为ID的索引,和一个普通name字段的索引,我们在普通字段上搜索:

    select * from table where name = '丙丙'

    执行的流程是先查询到name索引上的“丙丙”,然后找到他的id是2,最后去主键索引,找到id为2对应的值。

    回到主键索引树搜索的过程,就是回表。不过也有方法避免回表,那就是覆盖索引

    哦?那你再跟我聊一下覆盖索引呗?

    !!! 我这个嘴。。。

    这个其实比较好理解,刚才我们是 select * ,查询所有的,我们如果只查询ID那,其实在Name字段的索引上就已经有了,那就不需要回表了。

    覆盖索引可以减少树的搜索次数,提升性能,他也是我们在实际开发过程中经常用来优化查询效率的手段。

    很多联合索引的建立,就是为了支持覆盖索引,特定的业务能极大的提升效率。

    索引的最左匹配原则知道么?

    最左匹配原则

    • 索引可以简单如一个列 (a),也可以复杂如多个列 (a,b,c,d),即联合索引
    • 如果是联合索引,那么key也由多个列组成,同时,索引只能用于查找key是否存在(相等),遇到范围查询 (>、<、between、like左匹配)等就不能进一步匹配了,后续退化为线性查找。
    • 因此,列的排列顺序决定了可命中索引的列数

    例子:

    • 如有索引 (a,b,c,d),查询条件 a=1 and b=2 and c>3 and d=4,则会在每个节点依次命中a、b、c,无法命中d。(c已经是范围查询了,d肯定是排不了序了)

    总结

    索引在数据库中是一个非常重要的知识点!

    上面谈的其实就是索引最基本的东西,N叉树,跳表、LSM我都没讲,同时要创建出好的索引要顾及到很多的方面:

    • 最左前缀匹配原则。这是非常重要、非常重要、非常重要(重要的事情说三遍)的原则,MySQL会一直向右匹配直到遇到范围查询 (>,<,BETWEEN,LIKE)就停止匹配。
    • 尽量选择区分度高的列作为索引,区分度的公式是 COUNT(DISTINCT col)/COUNT(*)。表示字段不重复的比率,比率越大我们扫描的记录数就越少。
    • 索引列不能参与计算,尽量保持列“干净”。比如, FROM_UNIXTIME(create_time)='2016-06-06' 就不能使用索引,原因很简单,B+树中存储的都是数据表中的字段值,但是进行检索时,需要把所有元素都应用函数才能比较,显然这样的代价太大。所以语句要写成 : create_time=UNIX_TIMESTAMP('2016-06-06')。
    • 尽可能的扩展索引,不要新建立索引。比如表中已经有了a的索引,现在要加(a,b)的索引,那么只需要修改原来的索引即可。
    • 单个多列组合索引和多个单列索引的检索查询效果不同,因为在执行SQL时,MySQL只能使用一个索引,会从多个单列索引中选择一个限制最为严格的索引(经指正,在MySQL5.0以后的版本中,有“合并索引”的策略,翻看了《高性能MySQL 第三版》,书作者认为:还是应该建立起比较好的索引,而不应该依赖于“合并索引”这么一个策略)。
    • “合并索引”策略简单来讲,就是使用多个单列索引,然后将这些结果用“union或者and”来合并起来

    思路文献参考:

    《MySQL实战》

    《高性能MySQL》

    最后部分内容来自->java3y《索引和锁》

    丁奇《MySQL实战》

    絮叨

    之前在B站传了第一个视频:

    大家反馈效果还是ok的,我后续会多多尝试的,也希望把改进的建议留言反馈给我。

    我去年拍摄了第一个超级粗糙的vlog:

    因为拍摄剪辑手法都很垃圾,我就删了,但是最近又想着放上去,在纠结哈哈,想看留个言我就传了哈哈,我们下期间。

    今天丙丙也开始了来杭16天后的第一次上班,很开心我们公司在杭州第一批复工的名单中,我已经16天没和人这样说过话了,太开心了,不过不能开空调还得开窗户通风,真的是超级超级冷。

    这熟悉的工位,这熟悉的显示器,我的眼角又……

    Tip:本来有很多我准备的资料的,但是都是外链,或者不合适的分享方式,博客的运营小姐姐提醒了我,所以大家去公众号回复【资料】好了。

    白嫖不好,创作不易,各位的点赞就是丙丙创作的最大动力,我们下篇文章见!

    持续更新,未完待续……


    絮叨

    另外,敖丙把自己的面试文章整理成了一本电子书,共 1630页!目录如下

    现在免费送给大家,在我的公众号三太子敖丙回复 【888】 即可获取。

    我是敖丙,一个在互联网苟且偷生的程序员。

    你知道的越多,你不知道的越多人才们的 【三连】 就是丙丙创作的最大动力,我们下期见!

    注:如果本篇博客有任何错误和建议,欢迎人才们留言!


    文章持续更新,可以微信搜索「 三太子敖丙 」第一时间阅读,回复【资料】有我准备的一线大厂面试资料和简历模板,本文 GitHub https://github.com/JavaFamily 已经收录,有大厂面试完整考点,欢迎Star。

    展开全文
  • 数据库索引

    万次阅读 多人点赞 2019-02-22 09:18:43
    说白了,数据库的索引问题就是查找问题 数据库索引,是数据库管理系统中一个排序的数据结构,以协助快速查询,更新数据库中表的数据.索引的实现通常使用B树和变种的B+树(mysql常用的索引就是B+树) 除了数据之外,数据库...
  • 索引失效和注意事项

    千次阅读 2018-08-25 14:45:15
    如果是同样的sql如果在之前能够使用到索引,那么现在使用不到索引,以下几种主要情况: 随着表的增长,where条件出来的数据太多,大于15%,使得索引失效(会导致CBO计算走索引花费大于走全表) 统计信息失效 ...
  • 说到索引,很多人都知道“索引是一个排序的列表,在这个列表中存储着索引的值和包含这个值的数据所在行的物理地址,在数据十分庞大的时候,索引可以大大加快查询的速度,这是因为使用索引后可以不用扫描全表来定位某...
  • 什么是索引

    千次阅读 2019-04-04 16:00:09
    一、索引 MySQL索引的建立对于MySQL的高效运行是很重要的,索引可以大大提高MySQL的检索速度。 打个比方,如果合理的设计且使用索引的MySQL是一辆兰博基尼的话,那么没有设计和使用索引的MySQL就是一个人力三轮车...
  • 索引是什么,有什么用,怎么用?

    万次阅读 2016-12-27 16:57:08
    一、索引是什么 索引是与表或视图关联的磁盘上结构,可以加快从表或视图中检索行的速度。索引包含由表或视图中的一列或多列生成的键。这些键存储在一个结构(B树)中,使 SQL Server 可以快速有效地查找与键值...
  • MYSQL索引:对聚簇索引和非聚簇索引的认识

    万次阅读 多人点赞 2016-07-17 22:13:45
    聚簇索引是对磁盘上实际数据重新组织以按指定的一个或多个列的值排序的算法。特点是存储数据的顺序和索引顺序一致。 一般情况下主键会默认创建聚簇索引,且一张表只允许存在一个聚簇索引。 在《数据库原理》一书中...
  • 透明表索引有两种:分别是主索引和二级索引。 主索引是在我们创建表激活后由系统自动创建的,这个我们不能修改;二级索引可以我们自己创建。 主索引是表的主键,二级索引可以根据你自己需要用到表的任何字段的...
  • oracle创建、删除索引等操作

    万次阅读 2018-05-02 09:59:26
    1、创建索引 create index 索引名 on 表名(列名); 2、删除索引 drop index 索引名; 3、创建组合索引 create index 索引名 on 表名(列名1,,列名2); 4、查询索引 --根据索引名,查询表索引字段 select ...
  • elasticsearch查看所有索引

    万次阅读 2019-06-28 15:08:00
    GET _cat/indices
  • MySQL高级 之 索引失效与优化详解

    万次阅读 多人点赞 2017-05-16 23:29:28
    索引失效与优化1、全值匹配我最爱2、最佳左前缀法则(带头索引不能死,中间索引不能断)如果索引了多个列,要遵守最佳左前缀法则。指的是查询从索引的最左前列开始 并且 不跳过索引中的列。 正确的示例参考上图。...
  • 一、MYSQL索引的分类 索引用于快速查找具有特定列值的行。如果没有索引,MySQL必须从第一行开始,然后读取整个表以查找相关行。表越大,成本越高。如果表中有相关​​列的索引,MySQL可以快速确定要在数据文件中间...
  • 性别字段建立索引问题

    万次阅读 2019-01-15 21:35:37
    性别字段能不能建立索引 之前面试被问到一个问题 什么字段适合建索引,什么字段不适合建索引。 性别字段可以建索引吗? 我回答得不是很好。 性别字段这种重复性很强的字段,不要建立索引。为什么不能呢? 下面...
  • oracle 查看索引是否失效

    万次阅读 2018-09-22 14:50:13
    数据库使用的oracle数据库,可视化管理工具使用的PLSQL 查看表中的索引 选中表,右键, view –&gt;indexes就可以查看到表中的索引     select status from user_indexes where index_name='索引名' ...
  • 单一索引和复合索引区别及联系

    万次阅读 2019-01-10 10:51:28
    单一索引和复合索引区别及联系 - BABY的日志 - 网易博客 http://selectgoodboy.blog.163.com/blog/static/1032120612015191117118/   ...单一索引是指索引列为一列的情况,即新建索引的语句只实...
  • Mysql | 查看表的索引

    万次阅读 多人点赞 2018-09-13 11:25:54
    查看表的索引: show index from table_name(表名)
  • postgresql 查看索引、创建、删除索引

    万次阅读 2020-02-25 10:41:43
    查看索引 select * from pg_indexes where tablename='tbname'; 或者 select * from pg_statio_all_indexes where relname='tbname'; 创建索引 create index tbl_bb_index on tbl_bb(id,name); 注:tbl_bb 位...
1 2 3 4 5 ... 20
收藏数 1,803,767
精华内容 721,506
关键字:

索引