MySql索引总结

标签: mysql 索引 | 发表时间:2020-01-09 15:57 | 作者:love_eagle
出处:https://juejin.im/welcome/backend

MySQL 索引底层数据结构

  Mysql索引使用的数据结构主要有 BTree索引Hash索引。对于Hash索引来说,底层数据结构就是哈希表,因此在绝大多数需求为单条记录查询的时候,使用Hash索引查询性能最快。其余大多数场景建议使用BTree索引。

为什么索引能够提高查询速度

  MySQL的基本存储结构为 ,页与页之间使用双向链表进行链接,页内记录使用单向链表进行链接。查询数据时,先使用双向链表查询到数据所在的页,页内查询时,where子句中如果是主键则根据二分查找进行查询,如果为非主键则使用遍历链表的方式进行查询。整个时间复杂度为O(n)。

  使用索引之后,可以利用BTree的数据结构进行查找数据,时间复杂度可以做到O(log n)。

使用索引的缺点

  • 创建索引和维护索引需要耗费时间,这种时间随着数据量的增加而增加。
  • 索引需要占用物理空间,数据量越大,占用的空间越大。
  • 在进行增删改的过程中,由于需要维护索引,因此会降低效率,对于增删改操作比较多的表,不适合建立太多索引。

MySQL索引类型

  1. 主键索引:特殊的索引,唯一的标识一条记录,不能为空,一般用primary key来约束。
        ALTER TABLE `table_name` ADD PRIMARY KEY ( `column` ) 
    复制代码
  2. 唯一索引:索引列的值必须唯一,且不能为空,如果是组合索引,则列值的组合必须唯一。
        ALTER TABLE `table_name` ADD UNIQUE ( `column` ) 
    复制代码
  3. 普通索引:最基本的索引,它没有任何限制。
        ALTER TABLE `table_name` ADD INDEX index_name ( `column` )
    复制代码
  4. 全文索引:全文索引(FULLTEXT)仅可以适用于MyISAM引擎的数据表;作用于CHAR、VARCHAR、TEXT数据类型的列。
        ALTER TABLE `table_name` ADD FULLTEXT ( `column`) 
    复制代码
  5. 联合索引:将几个列作为一条索引进行检索,使用最左匹配原则。
        ALTER TABLE `table_name` ADD INDEX index_name ( `column1`, `column2`, `column3` )
    复制代码

联合索引的 最左匹配原则

简单来讲,建立联合索引时,当最左边的一个或多个索引确定之后,紧跟着的索引才是有序的。(如下图,建立a,b的联合索引后,a的值为[1,1,2,2,3,3]有序,b的值为[1,2,1,4,1,2])当不确定a的值时,b的值在结构上是无序的,这时仅where子查询中如果只使用b进行查找则会全表查找。 所以当我们用 where a = 1 and b = 2 进行查询时,由于a确定之后b的值有序,所以b字段查询时使用上索引。当我们用 where a > 1 and b = 2 进行查询时,a字段可以使用索引,但b字段由于无序所以无法使用索引。

join index

  • 总结
  • 最左前缀匹配原则。MySQL会一直向右匹配直到遇到范围查询(>,<,BETWEEN,LIKE)就停止匹配,比如: a = 1 AND b = 2 AND c > 3 AND d = 4,如果建立 (a,b,c,d)顺序的索引,d是用不到索引的,如果建立(a,b,d,c)的索引,则都可以用到,a,b,d的顺序可以任意调整。
  • 等于(=)和in 可以乱序。比如,a = 1 AND b = 2 AND c = 3 建立(a,b,c)索引可以任意顺序,MySQL的查询优化器会帮你优化成索引可以识别的模式

聚集索引和非聚集索引

简单概括

  • 聚集索引就是以主键创建的索引
  • 非聚集索引就是以非主键创建的索引

二者区别

  • 聚集索引中,叶子节点存储的是表中的数据
  • 非聚集索引中,叶子节点存储的是主键和索引列
  • 使用非聚集索引查询出数据时,拿到叶子上的主键再去查到想要查找的数据。(拿到主键再查找这个过程叫做回表)
  • 创建多个非聚集索引,每个聚集索引会生成一个索引树,因此建立多个索引会占用更多的磁盘空间。

索引和查询优化的注意事项

  • 前导的模糊查询无法使用索引,如 where like '%索引' 无法使用索引
  • Union、in、or可以命中索引,建议使用in
  • 负条件查询不能使用索引,可以优化为in查询,其中负条件有!=、<>、not in、not exists、not like等
  • 建立联合查询时,区分度最高的字段在最左边
  • 把计算放到业务层而不是数据库层,因为在字段上计算不能命中索引
  • 强制类型转换会全表扫描,如果phone字段是varcher类型,则下面的SQL不能命中索引。 Select * fromuser where phone=13800001234
  • 建立索引的列不能为null,使用not null约束及默认值
  • 如果明确知道查询结果只要一条,limit 1能够提高效率,比如验证登录的时候
  • 使用合理的分页提高效率。 select id,name from product limit 866613, 20使用上述SQL语句做分页的时候,可能有人会发现,随着表数据量的增加,直接使用limit分页查询会越来越慢。优化的方法如下:可以取前一页的最大行数的id,然后根据这个最大的id来限制下一页的起点。比如此列中,上一页最大的id是866612。SQL可以采用如下的写法: select id,name from product where id> 866612 limit 20。

面试经历

Q:请说说索引的注意事项,或者说说索引的优缺点。

参考资料:

相关 [mysql 索引] 推荐:

[MySQL] B+树索引

- - CSDN博客推荐文章
B+树是一种经典的数据结构,由平衡树和二叉查找树结合产生,它是为磁盘或其它直接存取辅助设备而设计的一种平衡查找树,在B+树中,所有的记录节点都是按键值大小顺序存放在同一层的叶节点中,叶节点间用指针相连,构成双向循环链表,非叶节点(根节点、枝节点)只存放键值,不存放实际数据. 保持树平衡主要是为了提高查询性能,但为了维护树的平衡,成本也是巨大的,当有数据插入或删除时,需采用拆分节点、左旋、右旋等方法.

mysql 索引技巧

- - 小彰
MySQL索引的建立对于MySQL的高效运行是很重要的. 下面介绍几种常见的MySQL索引类型. 在数据库表中,对字段建立索引可以大大提高查询速度. 假如我们创建了一个 mytable表:. CREATE TABLE mytable(   ID INT NOT NULL,    username VARCHAR(16) NOT NULL  );   我们随机向里面插入了10000条记录,其中有一条:5555, admin.

mysql选择索引

- - CSDN博客数据库推荐文章
1、尽量为用来搜索、分类或分组的数据列编制索引,不要为作为输出显示的数据列编制索引. 最适合有索引的数据列是那些在where子句中数据列,在联结子句中出现的数据列,或者是在Group by 、Order by子句中出现的数据列. select 后的数据列最好不要用索引. 2、综合考虑各数据列的维度.

mysql 索引详解

- - 行业应用 - ITeye博客
本文以MySQL数据库为研究对象,讨论与数据库索引相关的一些话题. 特别需要说明的是,MySQL支持诸多存储引擎,而各种存储引擎对索引的支持也各不相同,因此MySQL数据库支持多种索引类型,如BTree索引,哈希索引,全文索引等等. 为了避免混乱,本文将只关注于BTree索引,因为这是平常使用MySQL时主要打交道的索引,至于哈希索引和全文索引本文暂不讨论.

mysql索引认识

- - 数据库 - ITeye博客
数据在磁盘中是以 “块”的形式存储的,所以一张表涉及的数据可能会存在多个块中,而在磁盘中查询数据则会根据字段是否为有序与无序来区分,. 无序情况:1.数值具有唯一性则需要查找 总块数/2.                   2.无序+无唯一性则需要查找  总块数. 有序情况:1.数值唯一性:log2(总块数/2)   (log2是二分查找算法).

MySQL 索引方式

- - zzm
本文配图来自《高性能MySQL(第二版)》. 在数据库中,对性能影响最大的几个策略包括数据库的锁策略、缓存策略、索引策略、存储策略、执行计划优化策略. 索引策略决定数据库快速定位数据的效率,存储策略决定数据持久化的效率. MySQL中两大主要存储引擎MyISAM和InnoDB采用了不同的索引和存储策略,本文将分析它们的异同和性能.

MySql索引总结

- - 掘金后端
MySQL 索引底层数据结构.   Mysql索引使用的数据结构主要有 BTree索引 和 Hash索引. 对于Hash索引来说,底层数据结构就是哈希表,因此在绝大多数需求为单条记录查询的时候,使用Hash索引查询性能最快. 其余大多数场景建议使用BTree索引. 为什么索引能够提高查询速度.

MySQL InnoDB B+树索引

- - OurMySQL
B+树索引在DB中有一个特点就是高扇出性,一般在DB中B+树的高度在2-3层左右,也就意味着只需要2-3次的IO操作即可. 而现在的磁盘每秒差不多在100次IO左右,2-3次意味着查询时间只需0.02-0.03秒. InnoDB存储引擎表是索引组织表,即表中数据安装主键顺序存放. 而聚集索引就是按照每张表的主键构造一颗B+,并且叶节点存放着整张表的行记录数据,因此也让聚集索引也是索引的一部分.

[转] Mysql索引的问答

- - 数据库 - ITeye博客
相信很多人对于MySQL的索引都不陌生,索引(Index)是帮助MySQL高效获取数据的数据结构. 因为索引是MySQL中比较重点的知识,相信很多人都有一定的了解,尤其是在面试中出现的频率特别高. 楼主自认为自己对MySQL的索引相关知识有很多了解,而且因为最近在找工作面试,所以单独复习了很多关于索引的知识.

深入理解MySQL索引

- - InfoQ推荐
当提到MySQL数据库的时候,我们的脑海里会想起几个关键字:索引、事务、数据库锁等等, 索引是MySQL的灵魂,是平时进行查询时的利器,也是面试中的重中之重. 可能你了解索引的底层是b+树,会加快查询,也会在表中建立索引,但这是远远不够的,这里列举几个索引常见的面试题:. 1、索引为什么要用b+树这种数据结构.