数据库索引基础及常见问题_指定索引查询有什么问题

一、什么是索引

1. 概念

• 数据本身之外，数据库还维护着一个满足特定找找算法的数据结构，这些数据结构以某种方式指向数据，这样就可以再这些数据的基础上实现高级查找算法，这种数据结构就是索引。
• MySQL官方对索引的定义：索引是帮助MySQL高效获取数据的数据结构。即索引是一种数据结构。
•  索引的目的在于提高查找效率，它是排好序的。

2.索引的存储

• 一般来说索引本身也很大，因此往往以索引文件的形式存储在磁盘上。

3.索引的优缺点

（1）优点

• 提高了数据检索的效率，降低了数据库的IO成本。
• 通过索引列对数据进行排序，降低数据排序的成本，降低了CPU的消耗。

（2）缺点

• 索引实际也是一张表，保存了主键与索引字段，并指向实体表的记录，所以索引是占用空间的。
• 索引虽然提高了查找效率，但是降低了表更新速度，在进行更新表操作时，不但要保存数据，还需要保存索引文件每次更新后添加了的索引列的字段。也就是说，表更新不但更新数据，还更新索引信息。

二、mysql索引分类

1. 单值索引

• 即一个索引只包含单个列，一个表可以有多个单值索引。

（1）唯一索引：索引列的值必须唯一，但是允许有空值。

（2）普通索引：允许在定义索引的列中插入重复值和空值。

（3）主键索引：是特殊的唯一索引，不允许有空值。

2. 复合索引

• 在表的多个属性组合上创建的索引，并且列值的组合必须唯一。
• 使用复合索引时，遵循最左前缀集合。

3. 全文索引

• 底层实现为倒排索引。
• 只有MyISAM存储引擎支持全文索引。其类型为FULLTEXT，在定义索引的列上支持值的全文查找。
• 允许在索引列中插入重复值和空值，它可以在CHAR,VARCHAR,TEXT类型的列上创建。

4. 空间索引

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

5. 基本语法

-- 创建
 
-- 方法一
-- UNIQUE表示是唯一索引
-- 如果小括号中只有一个字段，那就是单值索引，否则是复合索引
CREATE [UNIQUE] INDEX indexName ON mytable(columnname(`length`));
 
-- 方法二
ALTER mytable ADD [UNIQUE] INDEX [indexName] ON (columnname(`length`));
 
 
-- 删除
DROP INDEX [indexName] ON mytable;
 
-- 查看 
SHOW INDEX FROM table_name;
 
 
-- 四种方式添加索引
 
-- 主键索引
ALTER TABLE tbl_name ADD PRIMARY KEY(clnum_list);
 
-- 唯一索引
ALTER TABLE tbl_name ADD UNIQUE index_neme(clnum_list);
 
-- 普通索引
ALTER TABLE tbl_name ADD INDEX index_neme(clnum_list);
 
-- 全文索引
ALTER TABLE tbl_name ADD FULLTEXT index_neme(clnum_list);

三、mysql索引结构

1. BTree索引

• MyISAM和InnoDB存储引擎只支持B+Tree索引，默认使用BTree。

（1）B树结构

• B树是为了磁盘或其他存储设备设计的一种多叉平衡查找树。
• B树高度一般在2-4，树的高度直接影响IO读写的次数。
• 三层树结构支撑数据可达20G，四层树结构支撑数据可达几十T。

（2）B树和B+树的区别

• 最大区别在于：非叶子节点是否存储数据。B树是非叶子节点和叶子节点都会存储数据，而B+树只有叶子节点才存储数据，因为中间节点不保存数据，所以磁盘页能容纳更多节点元素，更“矮胖”，而且这些数据都有指针指向，也就是有顺序的。
• b+树查询必须查找到叶子节点，b树只要匹配到即可不用管元素位置，因此b+树查找更稳定。
• 对于范围查找来说，b+树只需遍历叶子节点链表即可，b树却需要重复地中序遍历。

2. Hash索引

• MEMORY/HEAP存储引擎支持Hash和BTree索引。

（1）优点

• Hash索引的检索可以一次到位，所以Hash索引的查询效率更高。

（2）缺点

• Hash索引只能满足"="，“IN”，"!="，不能使用范围查询。
• Hash值的大小关系不一定个原键值一样，不能做排序操作。
• 联合索引中不能利用部分索引键查询。
• 遇到大量Hash值相等的情况后，性能不一定比B树高。
• 存储引擎会为Hash索引中的每一列都计算hash码，Hash索引中存储的即hash码，所以每次读取都会进行两次查询。

四、聚簇索引与非聚簇索引

1. 非聚集索引（MyISAM）

• 数据和索引不在一起
• data保存的都是地址。
• 主键索引：主键索引要求key是唯一的，叶节点的data域存放的是数据记录的地址。

• 辅助索引（次要索引）：辅助索引的key可以重复，data域保存数据记录的地址。MyISAM中索引检索：首先按照B+Tree搜索算法搜索索引，如果指定的Key存在，则取出其data域的值，然后以data域的值为地址，读取相应数据记录。

2. 聚集索引（InnoDB）

• 数据和索引在一起。
• 主键索引：data域保存了完整的数据记录。InnoDB要求表必须有主键，如果没有显式指定，或默认选择一个可以唯一标识的列作为主键，如果没有符合条件的列，MySQL自动为InnoDB表生成一个隐含字段（类型为长整形）作为主键。

• 辅助索引：data域存储主键值。采用回表查询，需要两遍检索：首先检索辅助索引获得主键，然后用主键在主索引中检索获得记录。

 

五、索引建立原则

1. 适合建立索引的情况：

• 在指定unique或primary key的列上，建立唯一索引。
• 频繁作为查询条件的字段应创建索引。
• 有外键关系的字段适合建立索引。
• 在高并发的情况下更适合创建组合索引。
• 为查询中统计或者分组字段上建立索引。

2. 不适合建立索引的情况：

• 表记录太少。
• 频繁增删改的字段和表不适合创建索引。
• where条件中不需使用的字段不创建索引。
• 如果某个数据列包含许多重复内容，那么建立索引意义不大。

六、常见问题

1. 主键和唯一索引的区别

2.Hash索引和B+树索引的区别

（1）hash索引

• hash索引底层是hash表。
• 进行查询时，调用一次hash函数就可以获取到相应的键值，之后进行回表查询获得实际数据。因此无法避免回表查询。
• 因为hash函数的不可预测，所以它不支持使用索引排序，不支持范围查询，不支持模糊查询以及多列索引的最左前缀匹配。
• hash索引查询不稳定，性能不可预测，当某个键值存在大量重复的时候，会发生hash碰撞，此时查询效率可能极差。

（2）B+树索引

• B+树底层实现原理是多路平衡查找树。
• 每次查询都从根节点出发，查询到叶子节点才能获得所查键值，然后判断是否需要回表查询。在符合某些条件的时候，可以只通过索引完成查询，不需要回表查询。
• B+树的所有节点皆遵循：左节点小于父节点，右节点大于父节点，支持范围查询。
• 查询效率比较稳定，对于查询都是从根节点到叶子节点，且树的高度较低。

3. 如何删除百万级别以上数据

1. 首先，删除索引，此过程耗时三分钟左右；
2. 其次，删除无用数据，此过程耗时不到两分钟；
3. 最后，在删除完成后，重新创建索引，此过程耗时十分钟左右。

4. 何时使用聚簇索引与非聚簇索引