精华内容
下载资源
问答
  • 列表中最大索引小于列表大小
    千次阅读
    2019-04-18 18:03:42

    获得list中最大元素的索引

    aa = [1,2,3,4,5]
    aa.index(max(aa))

    相应的最小值使用

    aa = [1,2,3,4,5]
    aa.index(min(aa))
    •  

    获得numpy数组中最大元素的索引

    1. 可以使用numpy的函数,argmax获得最大元素的索引,相应的获得最小值的话需要使用argmin。
    aa = [1,2,3,4,5]
    arr_aa = np.array(aa)
    maxindex  = np.argmax(arr_aa )
    1. 也可以将numpy转为list,然后使用list或者最大值索引的方法获得最大值。
    aa = numpy.array([1,2,3,4,5])

    先把aa转换为List,再求索引:

    bb = aa.tolist()
    bb.index(max(bb))
    1. python 比较灵活,所以还可以有其他的方法,如使用where函数。 
      首先我们可以得到array在全局和每行每列的最大值(最小值同理)
    >>> a = np.arange(9).reshape((3,3))
    >>> a
    array([[0, 1, 2],
           [3, 4, 5],
           [6, 7, 8]])
    >>> print(np.max(a))        #全局最大
    8
    >>> print(np.max(a,axis=0)) #每列最大
    [6 7 8]
    >>> print(np.max(a,axis=1)) #每行最大
    [2 5 8]

    然后用where得到最大值的索引,返回值中,前面的array对应行数,后者对应列数

    >>> print(np.where(a==np.max(a)))
    (array([2], dtype=int64), array([2], dtype=int64)) #表示最大值在第二行第二列
    >>> print(np.where(a==np.max(a,axis=0)))
    (array([2, 2, 2], dtype=int64), array([0, 1, 2], dtype=int64)) # 表示最大值分别在第二行第零列,第二行第一列,第二行第二列

    如果array中有相同的最大值,where会将其位置全部给出

    >>> a[1,0]=8
    >>> a
    array([[0, 1, 2],
           [8, 4, 5],
           [6, 7, 8]])
    >>> print(np.where(a==np.max(a)))
    (array([1, 2], dtype=int64), array([0, 2], dtype=int64))
    更多相关内容
  • 索引 - 索引键的最大大小

    千次阅读 2019-05-17 15:13:29
    在设计包含许多键列或大型列的索引时, 计算索引键的大小以确保不超过最大索引键的大小. SQL Server 为所有索引键列的最大总大小保留 900 字节. 这排除了在非聚集索引的定义包括的非键列. 计算索引键的大小 若要...

    在设计包含许多键列或大型列的索引时, 计算索引键的大小以确保不超过最大索引键的大小. SQL Server 为所有索引键列的最大总大小保留 900 字节. 这排除了在非聚集索引的定义中包括的非键列.

    计算索引键的大小

    若要计算索引键的大小, 请按下列步骤执行操作。

    1. 显示索引将依据的表列的属性, 可以使用 sys.columns 目录视图执行此操作.
    2. 求索引键中将定义的每一列的长度之和.
      例如, 以下语句聚合 Person.Address 表中指定列的 sys.columns 视图索引的 max_length 列.
    USE AdventureWorks2008R2;
    GO
    
    SELECT SUM(max_length) AS TotalIndexKeySize
    FROM sys.columns
    WHERE name IN(N'AddressLine1', N'AddressLine2', N'City', N'StateProvinceID', N'PostalCode')
    AND object_id = OBJECT_ID(N'Person.Address');
    

    如果表列为 Unicode 数据类型(例如, ncharnvarchar), 则所显示的列长度是该列的存储长度. 这是 CREATE TABLE语句中指定的字符数的两倍. 在前面的示例中, City 定义为 nvarchar(30) 数据类型. 因此, 该列的存储长度为60.

    1. 如果总长度小于 900 字节, 则该列可以作为索引键列. 如果总长度超过 900 字节, 则查看有关选项的以下信息和其他注意事项.

    CREATE INDEX 语句使用下列算法来计算索引键大小 :

    • 如果所有固定键列的大小加上 CREATE INDEX 语句中指定的所有可变键列的最大大小小于 900 字节, 则 CREATE INDEX语句将成功完成, 而不出现警告或错误.
    • 如果所有固定键列的大小加上所有可变键列的最大大小超过 900 字节, 而所有固定键列的大小加上所有可变键列的最小大小小于 900 字节,则 CREATE INDEX 语句将成功执行, 并显示一条警告, 指示如果后续 INSERTUPDATE语句指定的值所生成的键值大于 900 字节, 则该语句可能会失败. 若表中现有数据行的值所生成的键值大于 900 字节, CREATE INDEX 语句将失败. 若后续 INSERTUPDATE 语句指定的数据值所生成的键值大于 900 字节,该语句将失败.
    • 如果所有固定键列的大小加上 CREATE INDEX 语句中指定的所有可变列的最小大小超过 900 字节, CREATE INDEX语句将失败.

    下表总结了创建达到或超过最大索引键大小限制的索引的结果.

    可变长度列的最小大小 + 固定数据列的大小可变长度列的最大大小 + 固定数据列的大小现有行的索引键列长度总和的最大值*是否创建索引消息类型由于索引键值过大导致 INSERT 或 UPDATE 运行时错误
    > 900 字节不相关不相关错误索引没有生成错误。
    <= 900 字节<= 900 字节不相关否。
    <= 900 字节> 900 字节<= 900 字节警告仅当所有索引列的当前长度之和大于 900 字节时。
    <= 900 字节> 900 字节> 900 字节错误索引没有生成错误。

    在执行 CREATE INDEX 语句时, 表中的任何行都不能有总长度超过 900 字节的索引键值.

    使用包含列以避免大小限制

    可以将非键列包含在非聚集索引中来避免 16 键列的最大当前索引大小限制和 900 字节的最大索引键大小. SQL Server 数据库引擎在计算索引键列数或索引键列的总大小时不考虑非键列. 在具有包含列的非聚集索引中, 索引键列的总大小限制在 900 字节. 所有非键列的总大小只受 INCLUDE 子句中所指定列的大小限制. 例如, varchar(max) 列限制为 2 GB. INCLUDE 子句中的列可以为除 text, ntextimage 以外的所有数据类型.

    注意: 在对表进行分区时, 如果分区键列尚未出现在非唯一聚集索引中, 则它们将会由数据库引擎添加到索引中. 索引列合并后的大小(不将包含列计算在内)加上任何添加的分区列在非唯一聚集索引中不能超过 1800 字节.

    展开全文
  • 默认的话,axis=0,idxmax会在每一列去找一个最大值 当我们在axis=1时,也就是按行来找最大值 import numpy as np import pandas as pd df = pd.DataFrame(np.random.randint(low=1, high=10, size=(3, 3)), ...

    这里写目录标题

    dataFrame

    • 默认的话,axis=0,idxmax会在每一列去找一个最大值
    • 当我们在axis=1时,也就是按行来找最大值
    import numpy as np
    import pandas as pd
    
    df = pd.DataFrame(np.random.randint(low=1, high=10, size=(3, 3)), columns=['a', 'b', 'c'])
    id_a = df.idxmax(axis=1)  # 打印每一行最大值的索引【行号、列号】
    max_a = df.max(axis=1)
    
    id_b = df.idxmin(axis=1)  # 打印每一行最小值的索引
    min_b = df.min(axis=1)
    
    id_max = df.idxmax()   # 打印最大值的索引
    mx = df.max()
    
    id_min = df.idxmin() # 打印最小值的索引
    mi = df.min()
    
    print(df)
    print("+++++++++++++++++++++")
    
    print(id_a)
    print(max_a)
    
    print(id_b)
    print(min_b)
    
    print("---------------------")
    
    print(id_max)
    print(mx)
    
    
    print(id_min)
    print(mi)
    
    输出
       a  b  c
    0  9  1  7
    1  1  2  6
    2  5  7  5
    +++++++++++++++++++++
    0    a
    1    c
    2    b
    dtype: object
    0    9
    1    6
    2    7
    dtype: int32
    0    b
    1    a
    2    a
    dtype: object
    0    1
    1    1
    2    5
    dtype: int32
    ---------------------
    a    0
    b    2
    c    0
    dtype: int64
    a    9
    b    7
    c    7
    dtype: int32
    a    1
    b    0
    c    2
    dtype: int64
    a    1
    b    1
    c    5
    dtype: int32
    

    Series

    import pandas as pd
    
    index = ['A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J']
    s = pd.Series(range(10), index=index)
    print(s)
    print(s.idxmin())
    print(s.idxmax())   # axis有无都一样
    print(s.idxmax(axis=0))
    print(s.idxmax(axis=1))
    
    输出:
    A    0
    B    1
    C    2
    D    3
    E    4
    F    5
    G    6
    H    7
    I    8
    J    9
    dtype: int64
    A
    J
    J
    J
    
    Process finished with exit code 0
    
    展开全文
  • 一文搞懂 MySQL 索引

    千次阅读 多人点赞 2021-12-01 09:47:24
    1. 什么是索引 MySQL 官方对索引的定义为:索引(Index)是帮助 MySQL 高效获取数据的数据结构。可以得到索引的本质:索引是数据结构。 举一个例子,平时看任何一本书,首先看到的都是目录,通过目录去查询书籍里面...

    1. 什么是索引

    MySQL 官方对索引的定义为:索引(Index)是帮助 MySQL 高效获取数据的数据结构。可以得到索引的本质:索引是数据结构

    举一个例子,平时看任何一本书,首先看到的都是目录,通过目录去查询书籍里面的内容会非常的迅速,如下:

    image-20211124200643776

    书籍的目录是按顺序放置的,有第一章,第二章…,它本身就是一种顺序存放的数据结构,是一种顺序结构。

    但是如果我们要去图书馆找一本书怎么办?最好的方式就是有如下的的指引:

    image-20211125091959077

    从上面可见,整个索引结构是一棵倒挂着的树,其实它就是一种数据结构,这种数据结构比前面讲到的线性目录更好的增加了查询的速度,否者你可能的一层楼一层楼的去找。

    MySql 中的索引其实也是这么一回事,我们可以在数据库中建立一系列的索引,比如创建主键 ID 的时候默认会创建主键索引,当我们通过 ID 来查询内容的时候,首先去查索引库,查到索引后能快速的根据索引定位数据的具体位置。

    所以索引是应用程序设计和开发的一个重要方面。若索引太多,应用程序的性能可能会受到影响,而索引太少,对查询性能又会产生影响。要找到一个合适的平衡点,这对应用程序的性能至关重要。

    :以下关于索引的介绍,在没有特殊说明主要都是指的 InnoDB 中的索引。

    2. 索引的分类

    不知道你们有没有这样一种情况,就是每次背面试题的时候发现好像有各种索引,怎么都理不清楚,但实际上我们对索引进行分类。

    数据结构的角度

    • B+Tree 索引
    • Hash 索引
    • 全文索引(Full-texts)

    物理存储角度

    • 聚集索引(聚簇索引)
    • 辅助索引(二级索引,非聚集索引)

    逻辑角度

    • 主键索引
    • 唯一索引
    • 普通索引
    • 前缀索引

    实际使用角度

    • 单列索引
    • 联合索引(复合索引)

    3. 索引的数据结构

    InnoDB 存储引擎支持以下几种常见的索引:B+Tree 索引全文索引哈希索引,其中比较关键的是 B+Tree 索引,

    B+Tree 索引就是传统意义上的索引,这是目前关系型数据库系统中查找最常用和最为有效的索引。B+Tree 索引的构造类似于二叉树,根据键值(Key Value)快速找到数据。注意B+Tree 中的 B 不是代表二叉(binary),而是代表平衡(balance),因为 B+Tree 是从最早的平衡二叉树演化而来,但是 B+Tree 不是一个二叉树。

    知识补充

    3.1 二叉树

    树中节点的子节点不超过2的有序树,通俗来说就是节点左小右大

    image-20210804135913978

    3.2 二叉查找树

    二叉查找树(BST,Binary Sort Tree),也称二叉排序树,或二叉搜索树。一棵二叉查找树满足以下条件:

    • 首先它是一棵二叉树
    • 左子树的所有值均小于根节点的值
    • 右子树的所有值均大于根节点的值
    • 左右子树同时也满足以上两点

    image-20211124205820679

    3.3 平衡二叉树

    通过二叉查找树的查找操作可以发现,一棵二叉查找树的查找效率取决于树的高度,如果使树的高度最低,那么树的查找效率也会变高

    如下面一个二叉树,全部由右子树构成:

    image-20211124210054316

    此时可以看到,这个二叉树查找树实际上退化类似链表的结构了,如果我们要找 60 就得一直遍历到最后一个才能找到,查找时间复杂度为 O(n),所以效率并不会高。

    那么什么是平衡二叉树(AVL)

    平衡二叉树,是一个二叉排序树,同时任意节点左右两个子树的高度差(或平衡因子,简称BF)的绝对值不超过1,并且左右两个子树也满足,平衡二叉查找树查询的性能接近于二分查找法,时间复杂度是 O(logn),因此查找性能必然高于普通二叉查找树。

    image-20211124210447468
    对于时间复杂度,我之前也写过一篇文章:时间复杂度和空间复杂度
    在这里插入图片描述

    那么问题来了,既然平衡二叉树查找效率高,为什么不用它而要用 B+Tree 呢?

    平衡二叉树的查找性能是比较高的,但不是最高的,只是接近最高性能。最好的性能需要建立一棵最优二叉树,但是最优二叉树的建立和维护需要大量的操作,因此,用户一般只需建立一棵平衡二叉树即可。

    平衡二叉树的查询速度的确很快,但是维护一棵平衡二叉树的代价是非常大的。通常来说,为了保持平衡,需要 1 次或多次左旋右旋来得到插入、更新和删除后树的平衡性。(关于AVL的调整可以我之前写的:AVL 树的调整)。

    平衡二叉树对一个数据库来说,有什么问题

    因为二叉树每个节点最多只有两个子节点,所以当节点数比较多时,二叉树的高度增长很快,比如 1000 个节点时,树的高度差不多有 9 到 10 层。我们知道数据库是持久化的,数据是要从磁盘上读取的,一般的机械磁盘每秒至少可以做 100 次 IO,一次 IO 的时间基本上在 0.01 秒,1000 个节点在查找时就需要 0.1 秒,如果是 10000 个节点,100000 个节点呢?所以对数据库来说,为了减少树的高度,提出了 B+Tree 的数据结构,在这之前,先看看B-Tree。

    3.4 B-Tree

    在二叉树中,每个节点有一个数据项(可以理解为节点的值,key),且每个节点有最多有 2 个子节点,如果允许每个节点可以有更多的数据项和子节点,就是多叉树,如果这棵树还是平衡树,那么就可以叫他 B-Tree(B树,不叫 B 减树),B树的主要特点如下:

    1. B树的节点中存储着多个元素,每个内节点有多个分叉;
    2. 节点中的元素包含键值和数据,节点中的键值从大到小排列。也就是说,在所有的节点都储存数据;
    3. 父节点当中的元素不会出现在子节点中;
    4. 所有的叶子结点都位于同一层,叶节点具有相同的深度,叶节点之间没有指针连接。

    如 2-3-4 树就属于B树的一种。

    2-3-4 树是一种阶(阶,可以理解为节点的分支)为 4 的 B树,所以也可称它为 4 叉树,主要具备以下性质:

    1. 有 1 个 key 的节点有2个子节点,有 2 个 key 的有 3 个子节点,有 3 个 key 的有 4 个子节点,对应 key 的节点分别称为2节点3节点4节点,这也是为什么叫 2-3-4 树的原因;

    2. 所有的叶子节点总在同一层;

    3. 每个节点的值从左到右保持了从小到大的顺序,两个 key 之间的子树中所有的 key 一定大于它的父节点的左key,小于父节点的右 key。如下:

      image-20211124212423853

    如下面这棵 2-3-4 树:

    image-20211124212447179

    很明显,节点多,高度就降低了,比如:有1-100个数,二叉树一次只能分两个范围,0-50和51-100,而B树,分成4个范围 1-25, 25-50,51-75,76-100一次就能筛选走四分之三的数据。那么为什么选择 B+Tree 而不是 B-Tree 呢?

    我们先看这样一棵 B-Tree :

    image-20211126155034700

    我们知道,数据库索引是存储在磁盘上的,当数据量大时,就不能把整个索引全部加载到内存了,只能逐一加载每一个磁盘页(对应索引树的节点)。所以我们要减少 IO 次数,对于树来说,IO 次数就是树的高度。

    比如在上图中我们要查目标元素 8 的值:

    1. 第一次 IO,把 5 和 20 所在的节点加载到内存中,把目标元素与 5 和 20 比较,定位到中间区域 p2 指针所对应的节点;
    2. 第二次 IO,将 7 和 10 所在节点加载到内存中,把目标元素与 7 和 10 比较,定位到中间区域 p2 指针所对应的节点;
    3. 第三次 IO,将节点 8 和 9 加载到内存中,把目标元素与 8和 9 比较发现存在目标值,于是找到目标元素。

    可以看到,B-Tree 在查询时的比较次数并不比二叉树少,尤其是节点中的数非常多时,但是内存的比较速度非常快,耗时可以忽略,所以只要树的高度低,IO 少,就可以提高查询性能,这是B-Tree的优势之一。

    在来看看 B+Tree。

    3.5 B+Tree索引

    B+树和二叉树、平衡二叉树一样,都是经典的数据结构。B+树由 B-Tree和索引顺序访问方法演化而来,但是在现实使用过程中几乎已经没有使用 B 树的情况了。

    B+Tree 的定义在很多数据结构书中都能找到,非常复杂,我们概略它的定义:

    B+Tree 是 B-Tree 的一种变形形式,B+Tree 上的叶子结点存储关键字以及相应记录的地址,叶子结点以上各层作为索引使用。一棵 m 阶的 B+Tree定义如下:

    • 每个节点最多可以有 m 个元素;
    • 除了根节点外,每个节点最少有 (m/2) 个元素;
    • 如果根节点不是叶节点,那么它最少有 2 个孩子节点;
    • 所有的叶子节点都在同一层;
    • 一个有 k 个孩子节点的非叶子节点有 (k-1) 个元素,按升序排列;
    • 某个元素的左子树中的元素都比它小,右子树的元素都大于或等于它;
    • 非叶子节点只存放关键字和指向下一个孩子节点的索引,记录只存放在叶子节点中;
    • 相邻的叶子节点之间用指针相连

    概念很繁琐,但是我们可以总结出 B+Tree 和 B-Tree 的区别

    1. B+Tree 非叶子节点不会存储数据;
    2. B+Tree 叶子节点之间使用双向指针连接,最底层的叶子节点形成了一个双向有序链表。

    看这样一棵 B+Tree:

    image-20211126161238129

    如果要找目标元素 8,肯定也是要进行 3 次 IO 操作(同 B-Tree),这是等值查询的情况,如果我们要进行范围查询呢?比如我们要查询 8~25 之间的数据。

    1. 第一步肯定是先查询到最小值 8 的元素,此时进行了 3 次 IO;
    2. 因为各叶子节点通过指针连接,是一个有序的链表,因此只需要遍历由叶子结点组成的链表即可,直到找到最大值 25。

    那么对比 B-Tree 的范围查找呢?

    因为 B-Tree 的每个节点都是存在数据的,所以,当在叶子节点找到 8 时,但是确找不到最大值就需要在回头进行遍历查找,也就是需要进行中序遍历

    树的遍历有三种方法。前序(preorder),中序(inorder),后序(postorder)。

    image-20211125214538255

    所以,对于MySQL的索引为什么要使用 B+Tree 而不是 B-Tree

    总的来说,影响mysql查找性能的主要还是磁盘 IO 的次数,而 B-Tree 不管叶子节点还是非叶子节点,都会保存数据,这样导致在非叶子节点中能保存的指针数量变少,指针少的情况下要保存大量数据,只能增加树的高度,导致 IO 操作变多,查询性能变低。

    3.6 Hash索引

    Hash索引 和 B+Tree主要存在以下的区别:

    1. 单条数据查询时,Hash索引查询时间复杂度为O(1), B+Tree索引时间复杂度为O(logN);
    2. Hash索引只适合等值查询,但是无法进行范围查询,因为是随机分布的;
    3. 哈希索引没有办法利用索引完成排序,还是因为数据是随机分布的;
    4. 哈希索引不支持联合索引的最左匹配规则;
    5. 如果有大量重复键值的情况下,哈希索引的效率会很低,因为存在哈希碰撞,数据量越大,哈希碰撞几率越高。

    3.7 全文索引

    主要用来查找文本中的关键字,而不是直接与索引中的值相比较。fulltext索引跟其它索引大不相同,它更像是一个搜索引擎,而不是简单的where语句的参数匹配。fulltext索引配合match against操作使用,而不是一般的where语句加like。它可以在create table,alter table ,create index使用,不过目前只有char、varchar,text 列上可以创建全文索引。值得一提的是,在数据量较大时候,现将数据放入一个没有全局索引的表中,然后再用CREATE index创建fulltext索引,要比先为一张表建立fulltext然后再将数据写入的速度快很多。

    了解了索引的数据结构,再来了解各索引的区别。

    4. 物理存储角度分类

    4.1 聚集索引(聚簇索引)

    InnoDB 中的索引自然也是按照 B+树来组织的,前面我们说过 B+树的叶子节点用来放数据的,但是放什么数据呢?索引自然是要放的,因为 B+树的作用本来就是就是为了快速检索数据而提出的一种数据结构,不放索引放什么呢?但是数据库中的表,数据才是我们真正需要的数据,索引只是辅助数据,甚至于一个表可以没有自定义索引。那么 InnoDB 中的数据到底是如何组织的?

    InnoDB 中使用了聚集索引,就是将表的主键用来构造一棵 B+树,并且将整张表的行记录数据存放在该 B+Tree 的叶子节点中,即叶子节点存储着「主键和当前行的数据」。也就是所谓的索引即数据,数据即索引。由于聚集索引是利用表的主键构建的,所以每张表只能拥有一个聚集索引

    image-20211129104929990

    B+Tree 结构

    image-20211129114440364

    聚集索引的叶子节点就是数据页。换句话说,数据页上存放的是完整的每行记录。因此聚集索引的的优点就是:

    1. 通过聚集索引能获取完整的整行数据;
    2. 对于主键的排序查找和范围查找速度非常快。

    如果我们没有定义主键呢?MySQL 会使用唯一性索引,没有唯一性索引,MySQL 也会创建一个隐含列 RowID 来做主键,然后用这个主键来建立聚集索引。

    4.2 辅助索引(二级索引)

    上边介绍的聚簇索引只能在搜索条件是主键值时才能发挥作用,因为 B+Tree 中的数据都是按照主键进行排序的,那如果我们想以别的列作为搜索条件怎么办?我们一般会建立多个索引,这些索引被称为辅助索引(二级索引,或者非聚集索引),叶子节点存储着「主键和当前索引列值」

    对于辅助索引,叶子节点并不包含行记录的全部数据。叶子节点除了包含键值以外,每个叶子节点中的索引行中还包含了一个书签( bookmark)。该书签用来告诉 InnoDB 存储引擎哪里可以找到与索引相对应的行数据。因此 InnoDB 存储引擎的辅助索引的书签就是相应行数据的聚集索引键。

    对 name 创建索引,则叶子节点存储数据如下:

    image-20211129114630171

    4.3 回表

    由于辅助索引的节点没有存储全部的数据,当通过 name 要查询全部数据的时候怎么办?

    辅助索引的存在并不影响数据在聚集索引中的组织,因此每张表上可以有多个辅助索引。当通过辅助索引来寻找数据时,InnoDB 存储引擎会遍历辅助索引并通过叶级别的指针获得指向主键索引的主键,然后再通过主键索引(聚集索引)来找到一个完整的行记录。这个过程也被称为回表。也就是根据辅助索引的值查询一条完整的用户记录需要使用到 2 棵 B+Tree :一次辅助索引,一次聚集索引,如下:

    image-20211129134623236

    4.4 覆盖索引

    很明显,回表需要额外的 B+Tree 搜索过程,必然增大查询耗时。那什么情况下不需要回表呢?

    我们知道在辅助索引的叶子节点中存储着主键和当前索引列值,如果我们只需要查询叶子结点中的值,那么就不需要回表,这种情况称为覆盖索引或称触发了索引覆盖。

    select id,name from account where name='H';
    

    覆盖索引严格来说并不是一种索引结构,可以理解为是一种优化手段,比如建立联合索引。

    5. 逻辑角度分类

    5.1 主键索引

    建立在主键字段上的索引,索引列中的值必须是唯一的,不允许有空值。

    如:订单表(订单编号,名称,价格等等),订单编号是唯一标识的,可以作为主键。

    5.2 唯一索引

    建立在 UNIQUE 字段上的索引就是唯一索引,一张表可以有多个唯一索引,索引列值允许为 NULL,避免同一个表中某数据列中的值重复。

    如:身份证号等。

    5.3 普通索引

    主键索引和唯一索引对字段的要求是要求字段为主键或 UNIQUE字段,而那些建立在普通字段上的索引叫做普通索引,既不要求字段为主键也不要求字段为 UNIQUE,index 和 key 关键字都可以设置普通索引。

    5.4 前缀索引

    前缀索引是指对字符类型字段的前几个字符或对二进制类型字段的前几个 bytes 建立的索引,而不是在整个字段上建索引。

    例如,可以对上表中的 name 字段的前5个字符建立索引。

    image-20211129151907913

    前缀索引可以建立在类型为char、varchar、text、binary、varbinary的列上,可以大大减少索引占用的存储空间,也能提升索引的查询效率。

    6. 实际使用角度

    上面的索引都是建立在一个列上的,因此可以称为单列索引,而在实际使用过程中我们经常会建立2个甚至多个列的索引。

    6.1 联合索引

    将表上的多个列组合起来进行索引我们称之为联合索引或者复合索引。

    如:index(a,b)就是将 a,b 两个列组合起来构成一个索引。

    建立联合索引只会建立 1 棵 B+Tree,多个列分别建立索引会分别以每个列建立 B+Tree,有几个列就有几个 B+Tree,比如,index(a)、 index(b),就分别对 a,b 两个列各构建了一个索引,2 棵 B+Tree。

    index(a,b)在索引构建上,包含了两个意思:

    1. 先把各个记录按照 a 列进行排序;
    2. 在记录的 a 列相同的情况下,采用 b 列进行排序。

    因此,对于一棵联合索引的 B+Tree 的其中节点如下:

    image-20211129211840463

    按照上述说法,可以看到 a 的值是有顺序的,即1,1,2,2,3,3,而 b 的值为1,2,1,4,1,2,是无序的。

    同时我们还可以发现在 a 值相等的情况下,b 值又是按顺序排列的,但是这种顺序是相对的。所以最左匹配原则遇上范围查询就会停止,剩下的字段都无法使用索引。例如 a = 1 and b = 2a,b字段都可以使用索引,因为在 a 值确定的情况下 b 是相对有序的,而a>1 and b=2,a 字段可以匹配上索引,但 b 值不可以,因为a的值是一个范围,在这个范围中 b 是无序的。

    6.2 最左匹配原则

    上面的这种匹配方式,称之为最左匹配原则

    最左优先,以最左边的为起点任何连续的索引都能匹配上。同时遇到范围查询(>、<、between、like)就会停止匹配。

    假设我们建立了这样一个联合索引index(a,b,c),相当于创建了a、a-b、a-b-c 三个索引。

    1、全值匹配查询

    SELECT * FROM users WHERE a=1 AND b=3 AND c=1;
    
    SELECT * FROM users WHERE b=3 AND a=1 AND c=1;
    
    SELECT * FROM users WHERE c=1 AND a=1 AND b=3;
    

    通过执行计划分析是否使用到了索引

    EXPLAIN SELECT * FROM users WHERE c=1 AND a=1 AND b=3;
    ...
    

    经过测试发现都使用到了索引:

    image-20211129214455464

    不是说是最左原则吗?为什么 c 在最左边也能使用索引呢?

    这是因为 Mysql 中有查询优化器,会自动优化查询顺序,它会判断纠正这条 sql 语句该以什么样的顺序执行效率最高,最后才生成真正的执行计划。

    2、匹配左边的列

    等值查询的情况:

    ① 遵循最左原则,用到了索引。

    select * from users where a = '1' 
    
    select * from users where a = '1' and b = '2'  
    
    select * from users where a = '1' and b = '2' and c = '3'
    

    ② 不遵循最左原则,没用到索引,全表扫描。

    select * from users where  b = '2'; 
    
    select * from users where  c = '3';
    
    select * from users where  b = '1' and c = '3'; 
    

    ③ 如果是不连续的,则只用到了 a 的索引。

    select * from users where a = '1' and c = '3';
    

    3、匹配范围值

    范围查询的情况:

    ① 对最左边的列进行范围查询,用到了索引。

    select * from users where  a > 1 and a < 3;
    

    ② 多个列同时进行范围查找时,只有对索引最左边的那个列进行范围查找才用到 B+Tree 索引,也就是只有 a 用到索引,在1<a<3的范围内 b 是无序的,不能用索引,找到1<a<3的记录后,只能根据条件 b > 1继续逐条过滤。

    select * from users where  a > 1 and a < 3 and b > 1;
    

    ③ 左边的列等值匹配,范围匹配另外一列,用到了索引。

    select * from users where  a = 1 and b > 3;
    

    7. 其他索引

    7.1 自适应哈希索引

    InnoDB 存储引擎除了我们前面所说的各种索引,还有一种自适应哈希索引,我们知道 B+树的查找次数,取决于 B+树的高度,在生产环境中,B+Tree的高度一般为 3~4 层,故需要 3~4 次的 IO 查询。

    所以在 InnoDB 存储引擎内部自己去监控索引表,如果监控到某个索引经常用,那么就认为是热数据,然后内部自己创建一个 hash 索引,称之为自适应哈 希索引( Adaptive Hash Index,AHI),创建以后,如果下次又查询到这个索引, 那么直接通过 hash 算法推导出记录的地址,直接一次就能查到数据,比重复去 B+Tree 索引中查询三四次节点的效率高了不少。

    InnoDB 存储引擎使用的哈希函数采用除法散列方式,其冲突机制采用链表方式。注意,对于自适应哈希索引仅是数据库自身创建并使用的,我们并不能对其进行干预

    通过命令 show engine innodb status\G 可以看到当前自适应哈希索引的使用状况。

    8. MyISAM 中的索引

    我们知道 InnoDB 中索引即数据,也就是聚集索引的那棵 B+Tree 的叶子节点中已经把所有完整的用户记录都包含了,而 MyISAM 的索引方案虽然也使用树形结构,但是却将索引和数据分开存储的。

    MyISAM 将表中的记录按照记录的插入顺序单独存储在一个文件中,称之为数据文件。这个文件并不划分为若干个数据页,有多少记录就往这个文件中塞多少记录就成了。我们可以通过行号而快速访问到一条记录。

    由于在插入数据的时候并没有刻意按照主键大小排序,所以我们并不能在这些数据上使用二分法进行查找。

    使用 MyISAM 存储引擎的表会把索引信息另外存储到一个称为索引文件的另一个文件中。MyISAM 会单独为表的主键创建一个索引,只不过在索引的叶子节点中存储的不是完整的用户记录,而是主键值+行号的组合。也就是先通过索引找到对应的行号,再通过行号去找对应的记录。

    这一点和 InnoDB 是完全不相同的,在 InnoDB 存储引擎中,我们只需要根据主键值对聚集索引进行一次查找就能找到对应的记录,而在 MyISAM 中却需要进行一次回表操作,意味着 MyISAM 中建立的索引相当于全部都是二级索引,如果有需要的话,我们也可以对其它的列分别建立索引或者建立联合索引,原理和 InnoDB 中的索引差不多,不过在叶子节点处存储的是相应的列+行号,这些索引也全部都是二级索引。

    索引文件存储在.MYI中,数据文件存储在 .MYD中。

    9. 索引创建策略

    正确地创建和使用索引是实现高性能查询的基础。前面了解了索引相关的数据结构,各种类型的索引及其对应的优缺点,那么该如何正确的创建和使用索引呢?

    9.1 索引列的类型大小

    我们在定义表结构的时候要显式的指定列的类型,以整数类型为例,有 TTNYINT、NEDUMNT、INT、BIGTNT 这么几种,它们占用的存储空间依次递增,我们这里所说的类型大小指的就是该类型表示的数据范围的大小。能表示的整数范围当然也是依次递增,如果我们想要对某个整数列建立索引的话,在表示的整数范围允许的情况下,尽量让索引列使用较小的类型,比如我们能使用 INT 就不 要使用 BIGINT,能使用 NEDIUMINT 就不要使用 INT,这是因为:

    • 数据类型越小,在查询时进行的比较操作越快(CPU 层次)
    • 数据类型越小,索引占用的存储空间就越少,在一个数据页内就可以放下更多的记录,从而减少磁盘 IO 带来的性能损耗,也就意味着可以把更多的数据页缓存在内存中,从而加快读写效率。

    这个建议对于表的主键来说更加适用,因为不仅是聚簇索引中会存储主键值,其他所有的二级索引的节点处都会存储一份记录的主键值,如果主键适用更小的数据类型,也就意味着节省更多的存储空间和更高效的 IO。

    注:在 InnoDB 里,默认定义的 B+Tree 的节点大小是 16KB,这就是说,假如一个 Key 是 8 个字节,那么一个节点可以存放大约 1000 个 Key,意味着 B+Tree 可以有 1000 个分叉。同时 InnoDB 每一次磁盘 IO,读取的都是 16KB 的 整数倍的数据。也就是说 InnoDB 在节点的读写上是可以充分利用磁盘顺序 IO 的高速读写特性。

    9.2 搜索、排序或分组

    一般可以为只为用于搜索、排序或分组的列创建索引

    也就是说,只为出现在 WHERE 子句中的列、连接子句中的连接列创建索引, 而出现在查询列表中的列一般就没必要建立索引了,除非是需要使用覆盖索引;又或者为出现在 ORDER BYGROUP BY 子句中的列创建索引。

    9.3 索引选择性

    在《高性能MySql》一书中的介绍:

    索引的选择性是指,不重复的索引值(也称为基数)和数据表中的记录总数(#T)的比值,范围从 1/#T 到 1 之间。

    索引的选择性越高则查询效率越高,因为选择性高的索引可以让Mysql在查找时过滤掉更多的行。唯一索引的选择性是 1,这是最好的索引选择性,性能也是最好。

    很差的索引选择性就是列中的数据重复度很高,比如性别字段,一般情况下,只有两种可能,男或女。那么我们在查询时,即使使用这个索引,从概率的角度来说,依然可能查出一半的数据出来。

    比如下面这个表:

    image-20211130101256057

    那么上表那个适合做索引呢,当然是姓名字段,因为里面的数据没有任何重复,性别字段是最不适合做索引的,因为数据的重复度非常高。

    怎么计算索引的选择性?如上表:

    性别的选择性:

    image-20211130114248586

    姓名的选择性:

    image-20211130114334377

    9.4 建立联合索引

    创建联合索引最重要的一点就是索引的顺序,对于如何选择索引的列顺序有一个经验法则:把频繁使用的列、区分度高的列放在前面

    当不需要考虑排序和分组时,将选择性最高的列放在前面通常是很好的。这时候索引的作用只是用于优化 WHERE 条件的查找,在这种情况下,这样设计的索引确实能够最快地过滤出需要的行。

    另外,如果还能使用到覆盖索引,也是推荐建立联合索引的。

    PS:什么叫区分度? 我的理解其实就是选择性,只不过这涉及到前缀索引。

    9.5 建立前缀索引

    前面讲到对于字符类型的可以设立前缀索引,一般会对字段的前面几个字符设置索引,也就是说索引的长度不是越长越好。

    举个栗子:王,王二,王二蛋

    image-20211130152925568

    • 如果说前缀索引的长度为 1 的话,那么每一行的索引都是 王 这个字,就完全没有区分度;
    • 索引的长度为 3 ,区分度为 100%。

    前缀索引的选择性(区分度)计算:

    select count(distinct left(field,5))/count(*) from table;   
    

    image-20211130152942155

    而对于一些特殊的字段,如上表中的 url,都是已http://www. 开头,很显然这里的前缀索引需要超过 11,索引的长度过长不是我们所期望的,那么有没有后缀索引呢?

    MySQL 原生并不支持反向索引,但是我们可以把字符串反转后存储,并基于此建立前缀索引也是可以的。

    另外,MySQL 无法使用前缀索引做 ORDER BY 和 GROUP BY,也无法使用前缀索引做覆盖扫描。

    9.6 冗余和重复索引

    MySQL 允许在相同列上创建多个索引,无论是有意的还是无意的。MySQL 需要单独维护重复的索引,并且优化器在优化查询的时候也需要逐个地进行考虑,这会影响性能。

    重复索引是指在相同的列上按照相同的顺序创建的相同类型的索引。应该避免这样创建重复索引,发现以后也应该立即移除。

    9.7 删除未使用的索引

    除了冗余索引和重复索引,可能还会有一些服务器永远不用的索引。这样的索引完全是累赘,建议考虑删除。

    10. 总结

    尽管索引能帮助我们提升查询效率,并不意味着它一定很好,或者什么时候都需要用索引,因此我们要明确它的优缺点。

    • 优点:可以大大加快数据的检索速度
    • 缺点:
      • 时间方面:创建索引和维护索引需要消耗时间
      • 空间方面:索引需要占物理空间

    哪些情况下需要创建索引

    1. 主键自动建立唯一索引
    2. 频繁作为查询条件的字段
    3. 多表关联查询中的关联字段
    4. 排序的字段
    5. 频繁查找的字段,可以建立联合索引,进行覆盖索引
    6. 查询中统计或者分组字段

    哪些情况下不需要创建索引

    1. 表记录太少
    2. 经常进行增删改查操作的字段
    3. where条件里使用频率不高的字段

    11. 参考

    在这里插入图片描述

    展开全文
  • 聚集索引:指索引项的排序方式和表数据记录排序方式一致的索引 也就是说聚集索引的顺序就是数据的物理存储顺序。它会根据聚集索引键的顺序来存储表的数据,即对表的数据按索引键的顺序进行排序,然后重新存储到...
  • 》一文,我们介绍了索引的数据结构,正是因为索引使用了B+树,才使得查询变快。说白了,索引的原理就是减少查询的次数、减少磁盘IO,达到快速查找所需数据的目的 我们一起来看一下InnoDB存储引擎索引 聚集索引...
  • 每个节点中子节点的个数不能超过 N,也不能小于 N/2(不然会造成页分裂或页合并) 根节点的子节点个数可以不超过 m/2,这是一个例外 m 叉树只存储索引,并不真正存储数据,只有最后一行的叶子节点存储行数据。 通过...
  • 在本文,我们以 InnoDB 为例,介绍 MySQL 的索引结构以及其使用 B+ 树实现索引的原因。 表空间 首先,来了解一下 MySQL 的表空间。的所有数据被存储在一个空间内,称之为表空间,表空间内部又可以分为段(segment...
  • MySQL优化索引

    千次阅读 2021-01-28 06:20:18
    如果表有相关列的索引,MySQL可以快速确定要在数据文件中间查找的位置,而不必查看所有数据。这比顺序读取每一行要快得多。大多数MySQL索引(PRIMARY KEY,UNIQUE,INDEX和FULLTEXT)存储在B树(B-tree)。例外...
  • MySQL使用全文索引(fulltext index) 及中文全文索引使用

    万次阅读 多人点赞 2019-07-24 10:52:56
    全文索引介绍 ----------------------------------------------------------------------------------------------------------------------------- 1.创建全文索引(FullText index) 旧版的MySQL的全文索引只能...
  • 1、创建索引的几种方式; 2、mysql索引知识; 3、mysql索引优化;
  • mysql索引

    千次阅读 2019-08-18 10:33:10
    一句话简单来说,索引的出现其实就是为了提高数据查询的效率,就像书的目录一样。一本 500 页的书,如果想快速找到其中的某一个知识点,在不借助目录的情况下,那估计得找一会儿。同样,对于数据库的表而言,索引...
  • MySQL索引的数据结构

    千次阅读 2022-01-22 17:42:09
    InnoDB中索引的推演3.1 索引之前的查找1. 在一个页的查找2. 在很多页查找3.2 设计索引1. 一个简单的索引设计方案2. InnoDB索引方案3.3 常见索引概念1. 聚簇索引2. 二级索引(辅助索引、非聚簇索引)3. 联合...
  • 这样一轮下来,就能找到当前这些数字里的最小值,也就是应该在升序的列表中排在队首的元素。而这一轮当中,我们进行了“”元素总个数-1”次比较。紧接着,把这个最小的元素从原列表中删除。再用同样的方法,...
  • mysql的各种索引大总结

    千次阅读 2020-09-12 13:36:44
    1:非空左子树的所有键值小于其根结点的键值。 2:非空右子树的所有键值大于其根结点的键值。 3:左、右子树都是二叉搜索树。就是左节点<根节点<右节点。 看下图树太高了。查询效率太低,故不推荐。
  • db2获取某个表/索引占用空间的大小 在数据库运维,经常会遇到需要统计db2表大小的问题。在db2提供了3种方法已供我们选择。分别为db2pd -tcbstats、admin_get_tab_info表函数和SYSIBMADM.ADMINTABINFO系统管理...
  • MySQL 索引原理

    千次阅读 2022-04-24 16:29:44
    MySQL索引深入剖析
  • 数据库索引,真的越建越好吗?

    千次阅读 多人点赞 2021-06-01 00:07:25
    索引是提高关系型数据库查询性能的利器,但其并非银弹,必须精通其原理,才能发挥奇效。 InnoDB底层是如何存储数据的? MySQL把数据存储和查询操作抽象成了存储引擎。MySQL支持多种存储引擎,并且可以以表为粒度设置...
  • MySQL索引及常见面试题

    千次阅读 2022-01-19 09:33:44
    索引(Index)是帮助 MySQL 高效获取数据的数据结构,是对表一列或多列值进行排序的结构。 就比如索引是一本书的目录,可以通过目录快速查找自己想要查询的东西。 二、索引为什么使用B+树? 先看一下常见的索引...
  • MySQL索引系列:全文索引

    千次阅读 2020-11-07 16:34:12
    试想在1M大小的文件搜索一个词,可能需要几秒,在100M的文件可能需要几十秒,如果在更大的文件搜索那么就需要更大的系统开销,这样的开销是不现实的。 所以在这样的矛盾下出现了全文索引技术,有时候有人叫倒...
  • 点击datagridview 索引超出范围的错误

    千次阅读 2019-12-24 17:11:51
    必须为非负值并小于集合大小。 dataGridView1.SelectedRows[0].Cells[0].Value.ToString();//获取第一个单元格的值 因为dataGridView1.SelectedRows[0]的默认selectionMode是 RowHeaderSelect,如果你点击行某一个...
  • 前面文章MySQL性能优化(三):深入理解索引的这点事已经介绍了各种类型的索引及其特点,而在实际使用索引中,如何真正有效地发挥索引的价值,还需要进一步持续不断第学习、磨练。接下来本篇文章将分享如何...
  • 第一种就是仿饿了么点餐时,商品列表的悬停头部切换“动画效果”,如下: 第二种是一种头部折叠起来的视效,个人觉得也还不错~如下: 果然比上部残篇里的效果好看多了,那么代码多不多呢,看我的git show 记录: ...
  • MySQL高级篇——索引的数据结构

    千次阅读 多人点赞 2022-04-11 16:35:12
    3.InnoDB索引 3.1 设计索引 3.2 常见索引概念 3.2.1 聚簇索引 3.2.2 非聚簇索引 3.2.3 联合索引 4.InnoDB与MyISAM的索引对比 5.B-Tree和B+Tree的差异 1.为什么使用索引? 假如给数据使用 二叉树 ...
  • MySQL索引

    千次阅读 2021-03-06 17:45:44
    就是select的数据列只用从索引中就能获得,不必读取数据行,MySQL可以利用索引返回select列表中的字段,而不必根据索引再次读取数据文件,换句话说:查询列要被所见的索引覆盖。 注意:如果要使用索引覆盖,一定要...
  • 索引压缩

    千次阅读 2017-08-10 16:59:27
    就拿最常见的倒排索引来说,特别是当用户查询的关键词是常用词时,这些词所对应的倒排列表可以达到几百兆,而将这样庞大的索引由磁盘读入内存,势必会严重增加检索响应时间,影响用户的搜索体验。为了解决这样的问题...
  • 非聚簇索引:数据存储和索引分开放,索引结构的叶子节点指向了数据的对应行,myisam通过 key_buffer 把索引先缓存到内存,当需要访问数据时(通过索引访问数据),在内存直接搜索索引,然后通过索引找到磁盘...
  • MySql之索引

    万次阅读 多人点赞 2019-11-05 20:50:12
    Mysql 索引精讲 开门见山,直接上图,下面的思维导图即是现在要讲的内容,可以先有个印象~ 常见索引类型(实现层面) 索引种类(应用层面) 聚簇索引与非聚簇索引 覆盖索引 最佳索引使用策略 1.常见索引类型...
  • MySQL 索引详解

    万次阅读 2021-12-09 23:05:07
    如果是唯一索引,可以确保数据表的每一行记录的唯一性 缺点: 索引需要占用额外的存储空间 创建和维护索引需要耗费时间。MySQL执行增删改操作时,也需要对索引进行修改,会降低SQL的执行效率 常见的索引结构 ...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 138,308
精华内容 55,323
热门标签
关键字:

列表中最大索引小于列表大小