什么是索引

索引是对数据表中一列或多列的值进行排序的一种结构。
索引是一个单独的、存储在磁盘上的数据库结构,
他们包含着对数据表里所有记录的引用指针。
用于快速找出某个列中有特定值的行。
不使用索引的话,Mysql必须从第一条记录开始读知道查出相关行,
使用索引可以快速到达一个位置去搜索特定数据

索引是存储引擎实现的,因此,每种存储引擎的索引都不一定完全相同,并且每种存储引擎也不一定支持所有索引类型。

根据存储引擎定义每个表的最大索引数和最大索引长度。所有存储引擎支持每个表至少16个索引,总索引长度至少为256字节。大多数存储引擎有更高的限制。

MySql中索引的存储类型有两种:BTREE和HASH,
MyISAM和InnoDB存储引擎都支持BTREE索引;
MEMORY/HEAP存储引擎可以支持BTREE和HASH索引。

索引的分类

MyISAM 和InnoDB存储引擎,InnoDB的索引类型目前只有两种:BTREE(B树)索引和HASH索引。
索引大体分为四类:

  1. 单列索引: 一个索引只包含一列,一张表可以包含多个单列索引

    • 普通索引: MySQL中基本索引类型,没有什么限制,允许在定义索引的列中插入重复值和空值,纯粹为了查询数据更快一点。
    • 唯一索引: 索引列中的值必须是唯一的,但是允许为空值,
    • 主键索引: 是一种特殊的唯一索引,不允许有空值。
  2. 组合索引: 在表中的多个字段组合上创建的索引,只有在查询条件中使用了这些字段的左边字段时,索引才会被使用,使用组合索引时遵循最左前缀集合

  3. 全文索引: 全文索引,只有在MyISAM引擎上才能使用,只能在CHAR,VARCHAR,TEXT类型字段上使用全文索引,介绍了要求,说说什么是全文索引,就是在一堆文字中,通过其中的某个关键字等,就能找到该字段所属的记录行,比如有”你是个靓仔,靓女 …” 通过靓仔,可能就可以找到该条记录。全文索引用于实现关键词搜索。但它只能根据空格分词,因此不支持中文。

  4. 空间索引: 空间索引是对空间数据类型的字段建立的索引,MySQL中的空间数据类型有四种,GEOMETRY、POINT、LINESTRING、POLYGON。在创建空间索引时,使用SPATIAL关键字。要求,引擎为MyISAM,创建空间索引的列,必须将其声明为NOT NULL。

索引的优点

  1. 加快数据检索的速度,减少查询的代价
  2. 可以利用唯一索引来确保表里每一行数据的唯一性
  3. 使用分组和排序子句进行数据检索时,按照索引执行的话节省时间
  4. 通过使用索引,可以在查询的过程中,使用优化隐藏qi,提高系统性能。
  5. 加速表与表之间的连接,特别是在实现数据的参考完整性方便特别有意义。

索引的缺点

  • 创建和维护索引都需要耗费时间
  • 索引需要占据物理空间
  • 当对表中数据增加删除修改的时候,索引也要动态的维护,降低了数据维护速度

哪些情况下需要加索引?

  1. 检索条件下,也就是where后面加
  2. 聚合函数对聚合字段加索引 【count, sum, max, 】
  3. 排序字段加索引 【order by】
  4. 非聚簇索引加索引,减少回表操作
  5. 关联查询在关联字段加索引

正确使用索引

  1. 用 like 语句时,%在右边才会使用索引。
  2. or 条件中有未建立索引的列才,索引失效
  3. 条件的类型不一致 where name=1 【where name=’1’】
  4. != 号不适用索引,如果时主键例外,会走索引
  5. ,> 号不适用索引, 是主键或索引是整数类型,则会走索引
  6. order by, 如果 order by 是主键或索引是整数类型,则会走索引
  7. 组合索引, 遵循最左前缀 (k1, k2)
    • k1 and k2 索引
    • k2 and k1 不走索引
    • k1 索引
    • k2 不走索引

order by 示例情况

当order by 字段出现在where条件中时,才会利用索引而无需排序操作。其他情况,order by不会出现排序操作。
更准确的说,order by 中的字段在执行计划中利用了索引时,不用排序操作。
这个结论不仅对order by有效,对其他需要排序的操作也有效。比如group by 、union 、distinct等。

一条SQL实际上可以分为三步。

  1. 得到数据
  2. 处理数据
  3. 返回处理后的数据

第一步:根据where条件和统计信息生成执行计划,得到数据。
第二步:将得到的数据排序。
当执行处理数据(order by)时,数据库会先查看第一步的执行计划,看order by 的字段是否在执行计划中利用了索引。如果是,则可以利用索引顺序而直接取得已经排好序的数据。如果不是,则排序操作。
第三步:返回排序后的数据。

什么是最左前缀原则?什么是最左匹配原则?

最左优先,在创建多列索引时,要根据业务需求,where子句中使用最频繁的一列放在最左边。最左前缀匹配原则,非常重要的原则,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的查询优化器会帮你优化成索引可以识别的形式

索引的数据结构

索引的数据结构和具体存储引擎的实现有关,在MySQL中使用较多的索引有Hash索引,B+树索引等,而我们经常使用的InnoDB存储引擎的默认索引实现为:B+树索引。对于哈希索引来说,底层的数据结构就是哈希表,因此在绝大多数需求为单条记录查询的时候,可以选择哈希索引,查询性能最快;其余大部分场景,建议选择BTree索引。

B树索引

B树索引是Mysql数据库中使用最频繁的索引类型,基本所有存储引擎都支持BTree索引。通常我们说的索引不出意外指的就是(B树)索引(实际是用B+树实现的,因为在查看表索引时,mysql一律打印BTREE,所以简称为B树索引)
n棵子tree的节点包含n个关键字,不用来保存数据而是保存数据的索引。所有的叶子结点中包含了全部关键字的信息,即指向含这些关键字记录的指针,且叶子结点本身依关键字的大小自小而大顺序链接。所有的非终端结点可以看成是索引部分,结点中仅含其子数中的最大(或最小)关键字。B+ 树中,数据对象的插入和删除仅在叶节点上进行。B+树有2个头指针,一个是树的根节点,一个是最小关键码的叶节点。
BTree是最常用的mysql数据库索引算法,也是mysql默认的算法。因为它不仅可以被用在=,>,>=,<,<=和between这些比较操作符上,而且还可以用于like操作符,只要它的查询条件是一个不以通配符开头的常量。

哈希索引

类似于数据结构中简单实现的HASH表(散列表)一样,当我们在mysql中用哈希索引时,主要就是通过Hash算法(常见的Hash算法有直接定址法、平方取中法、折叠法、除数取余法、随机数法),将数据库字段数据转换成定长的Hash值,与这条数据的行指针一并存入Hash表的对应位置;如果发生Hash碰撞(两个不同关键字的Hash值相同),则在对应Hash键下以链表形式存储
Hash Hash索引只能用于对等比较,例如=,<=>(相当于=)操作符。由于是一次定位数据,不像BTree索引需要从根节点到枝节点,最后才能访问到页节点这样多次IO访问,所以检索效率远高于BTree索引。

使用索引查询一定能提高查询的性能吗?

通常,通过索引查询数据比全表扫描要快。但是我们也必须注意到它的代价。索引需要空间来存储,也需要定期维护, 每当有记录在表中增减或索引列被修改时,索引本身也会被修改。 这意味着每条记录的INSERT,DELETE,UPDATE将为此多付出4,5 次的磁盘I/O。 因为索引需要额外的存储空间和处理,那些不必要的索引反而会使查询反应时间变慢。使用索引查询不一定能提高查询性能,索引范围查询(INDEX RANGE SCAN)适用于两种情况:基于一个范围的检索,一般查询返回结果集小于表中记录数的30%基于非唯一性索引的检索

百万级别或以上的数据如何删除?

删除数据的速度和创建的索引数量是成正比的。所以我们想要删除百万数据的时候可以先删除索引(此时大概耗时三分多钟)然后删除其中无用数据(此过程需要不到两分钟)删除完成后重新创建索引(此时数据较少了)创建索引也非常快,约十分钟左右。与之前的直接删除绝对是要快速很多,更别说万一删除中断,一切删除会回滚。那更是坑了。

如何在生产环境删除百万级以上的数据

1
2
3
SELECT CONCAT(“delete from gusers_db.gusers_detail where user_id=”,id,”;”)
FROM gusers_db.gusers_detail WHERE user_id>=100 AND user_id<=150
INTO OUTFILE ‘/tmp/user.sql’

mysql -uroot -p gusers_db < /tmp/user.sql

创建索引