什么是索引？
MySQL官方定义为：索引index是帮助MySQL高效获取数据的数据结构。
索引本质：索引是数据结构。
数据库查询是数据库的最主要功能之一，希望查询数据的速度能尽可能的快，因此数据库系统的设计者会从查询算法的角度进行优化。
MySQL目前主要有普通索引、唯一索引、主键索引、组合索引和全文索引共5种索引类型。
普通索引和唯一索引、主键索引
普通索引和唯一索引是根据索引的功能来划分。
普通索引是MySQL中的基本索引类型，允许在定义索引的列中插入重复值和空值。
唯一索引的索引列的值必须唯一，但允许空值。如果是组合索引，则列的值得纸盒必须唯一。主键索引是一种特殊的唯一索引，不允许有空值。
主键索引，一种特殊的唯一索引，不允许有空值，一般在建表的时候同时建立主键索引
单列索引和组合索引
单列索引和组合索引的划分是根据创建索引时所引用的列的数量来划分。
单列索引是只一个索引只包含单个列，一个表可以有多个单列索引。
组合索引指在表的多个字段组合上创建索引，但只有在查询条件中使用了这些字段的左边字段时，索引才会被使用。
ALTER TABLE `table` ADD INDEX name_city_age (name,city,age);
组合索引可以起到多个索引的作用，但是使用时并不是随便哪个字段都可以使用索引的，而是遵从“最左前缀”：利用索引中最左边的列集列匹配行，这样的列集称为最左前缀。例如由id、name、age这3个字段字段构成的索引，索引行中按id/name/age的顺序存放，索引可以搜索下面字段组合：（id, name , age）、（id , name）、（id）。如果不构成索引的最左边的前缀，MySQL不能使用局部索引，如（age）、（name, age）都不能使用索引进行查询。
全文索引
全文索引类型为FULLTEXT，在定义索引的列上至此值需得全文查找，允许在这些索引列上插入空值和重复值
like + % 在文本比较少时是合适的，但是对于大量的文本数据检索，是不可想象的。全文索引在大量的数据面前，能比 like + % 快 N 倍，速度不是一个数量级，但是全文索引可能存在精度问题。
只有字段的数据类型为 char、varchar、text 及其系列才可以建全文索引
create fulltext index cft on test(content,tag);
在具体的应用中，一般不会使用数据库管理系统的全文索引，而是引入ElasticSearch这种NoSQL数据库实现
空间索引
空间索引可以用作地理数据存储，通过R树来实现。空间索引会从所有维度来索引数据，使得空间搜索变得高效。查询时可以有效地使用任意维度来组合查询。必须使用MySQL的GIS相关函数来维护数据.
space geometry not null,
spatial index index6_sp(space)
创建了一个名为index6_sp的空间索引
索引的优点
可以通过建立唯一索引或者主键索引,保证数据库表中每一行数据的唯一性
建立索引可以大大提高检索的数据,以及减少表的检索行数
在表连接的连接条件 可以加速表与表直接的相连
在分组和排序字句进行数据检索,可以减少查询时间中 分组 和 排序时所消耗的时间(数据库的记录会重新排序)
建立索引,在查询中使用索引 可以提高性能，但是会影响修改的性能
索引的缺点
在创建索引和维护索引 会耗费时间,随着数据量的增加而增加，所以一般建议一个表上创建的索引数不要超过6个
索引文件会占用物理空间,除了数据表需要占用物理空间之外,每一个索引还会占用一定的物理空间
当对表的数据进行 INSERT,UPDATE,DELETE 的时候,索引也要动态的维护,这样就会降低数据的维护速度,(建立索引会占用磁盘空间的索引文件。一般情况这个问题不太严重，但如果你在一个大表上创建了多种组合索引，索引文件的会膨胀很快)
注意事项
1、索引不会包含有null值的列，在数据库设计时不要让索引字段的默认值为null。
2、使用短索引
3、索引列排序
4、like语句操作， like “%aaa%” 不会使用索引而like “aaa%”可以使用索引。
5、不要在列上进行运算
6、不使用not in和<>操作
创建索引
创建索引的方式有直接创建、修改表添加和创建时直接创建三种
CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name ON table_name (column(length),…) [ASC|DESC]
-- 普通索引
create index bknameIndex on book(bookname) ;
-- 唯一索引
create unique index unique_Index on book(bookId) ;
--单列索引
create index single_Index on book(comment(10)) ;
-- 多列索引
create index mutil_Index on book(authors(20) , info(20)) ;
--全文索引
create fulltext index fulltext_Index on book(info) ;
ALTER TABLE table_name ADD [UNIQUE|FULLTEXT|SPATIAL] [INDEX|KEY] index_name (column(length),…) [ASC|DESC]
-- 普通索引
alter table book add index bkName(bookname(30)) ;
--唯一索引
alter table book add unique index uniqueIndex(bookId) ;
--单列索引
alter table book add index signalIndex2 on(comment(50)) ;
--多列索引
alter table book add index mutilIndex (authors(30) , info(50)) ;
--创建全文索引
alter table book add fulltext index fulltextIndex (info) ;
CREATE TABLE table_name (
……（创建字段和约束）,
[UNIQUE|FULLTEXT|SPATIAL] [INDEX|KEY] [INDEX|KEY] [index_name] (col_name [length]) [ASC|DESC]
)
-- 普通索引
create table book(
……
index pub_index(year_publication)
);
-- 唯一索引
create table book(
……
unique index pub_index(bookid)
);
-- 单列索引
create table book(
……
index single_index(authors)
);
-- 多列索引
create table book(
……
index mutil_index(bookid, authors)
);
-- 全文索引,需要修改表的存储引擎为MyISAM
create table book(
……
fulltext index fulltext_index(info)
);
使用ALTER TABLE删除索引
ALTER TABLE table_name DROP INDEX index_name ;
有AUTO_INCREMENT约束的字段的唯一索引不能被删除
删除book表中名为fulltext_Index的索引alter table book drop index fulltext_Index ;
使用DROP INDEX删除索引
DROP INDEX index_name ON table_name ;
删除book表中名为fulltext_Index的索引drop index mutil_index on book ;
不适合创建索引
二进制多媒体数据
超大文本数据
web应用中活跃性大但是数据量不是很大的数据
索引的设计原则
索引设计不合理或者缺少索引都会对数据库性能造成不良影响
-
索引并非越多越好，一个表中如果有大量的索引，不仅浪费磁盘空间，而且会影响INSERT、DELETE、UPDATE等语句的性能，因为当表中的数据更改的同时，索引也会进行调整和更新
-
数据量小的表最好不要使用索引，由于数据量较小，查询所花费的时间可能比表里索引的时间还要短，索引可能不会产生优化的效果
-
避免对经常更新的表进行过多的索引，并且索引中的列尽可能少。对经常用于查询的字段应该创建少量索引，但要避免添加不必要的字段
-
在条件表达式中经常用到的不同值较多的列上建立索引，在不同值很少的列（例如性别字段，只有男女两个取值）不要建立索引
-
当唯一性是数据本身的特征时，指定唯一索引，可确保数据完整性并提高查询速度
-
在频繁进行排序、分组的列上建立索引，如果排序的列有多个，可以在这些列上建立组合索引。
使用索引的场景
在建立索引的时候应该考虑索引应该建立在数据库表中的某些列上面 哪一些索引需要建立,哪一些所以是多余的
1、在经常需要搜索的列上,可以加快索引的速度
2、主键列上可以确保列的唯一性
3、在表与表的而连接条件上加上索引,可以加快连接查询的速度
4、在经常需要排序(order by),分组(group by)和的distinct 列上加索引 可以加快排序查询的时间, (单独order by 用不了索引，索引考虑加where 或加limit)
4、在一些where 之后的 < <= > >= BETWEEN IN 以及某个情况下的like 建立字段的索引(B-TREE)
5、like语句的 如果你对nickname字段建立了一个索引.当查询的时候的语句是 nickname lick ‘%ABC%’ 那么这个索引讲不会起到作用.而nickname lick ‘ABC%’ 那么将可以用到索引
6、索引不会包含NULL列,如果列中包含NULL值都将不会被包含在索引中,复合索引中如果有一列含有NULL值那么这个组合索引都将失效,一般需要给默认值0或者 ’ '字符串
7、使用短索引,如果你的一个字段是Char(32)或者int(32),在创建索引的时候指定前缀长度 比如前10个字符 (前提是多数值是唯一的…)那么短索引可以提高查询速度,并且可以减少磁盘的空间,也可以减少I/0操作
8、不要在列上进行运算,这样会使得mysql索引失效,也会进行全表扫描
9、选择越小的数据类型越好,因为通常越小的数据类型通常在磁盘,内存,cpu,缓存中 占用的空间很少,处理起来更快
不创建索引的场景
查询中很少使用到的列 不应该创建索引,如果建立了索引然而还会降低mysql的性能和增大了空间需求
很少数据的列也不应该建立索引,比如 一个性别字段 0或者1,在查询中,结果集的数据占了表中数据行的比例比较大,mysql需要扫描的行数很多,增加索引,并不能提高效率
定义为text和image和bit数据类型的列不应该增加索引
当表的修改(UPDATE,INSERT,DELETE)操作远远大于检索(SELECT)操作时不应该创建索引,这两个操作是互斥的关系
索引的实现原理
MySQL支持诸多存储引擎，而各种存储引擎对索引的支持也各不相同，因此MySQL数据库支持多种索引类型，如B-Tree索引，B+Tree索引，哈希索引，全文索引等
常见的数据结构对比
散列表
散列表（也称哈希表）是根据关键码值(Key value)而直接进行访问的数据结构，它让码值经过哈希函数的转换映射到散列表对应的位置上，查找效率非常高。哈希索引就是基于散列表实现的，假设对名字建立了哈希索引，则查找过程
哈希索引主要用于memory引擎中
1、针对哈希索引，只有精确匹配索引所有列的查询才有效，比如在列（A,B）上建立了哈希索引，如果只查询数据列 A，则无法使用该索引
2、哈希索引并不是按照索引值顺序存存储的，所以也就无法用于排序，也就是说无法根据区间快速查找
3、哈希索引只包含哈希值和行指针，不存储字段值，所以不能使用索引中的值来避免读取行，不过，由于哈希索引多数是在内存中完成的，大部分情况下这一点不是问题
4、哈希索引只支持等值比较查询，包括 =,IN()，不支持任何范围的查找，如 age > 17
链表
双向链表支持顺序查找和逆序查找，索引是按照大小顺序进行存储的
什么是跳表?简单地说，跳表是在链表之上加上多层索引构成的。
跳表是能满足需求的，实际上它的结构已经和B+树非常接近了，只不过B+树是从平衡二叉查找树演化而来的而已
二叉查找树
图中的圆为二叉查找树的节点，节点中存储了键key和数据data。键对应user表中的id，数据对应user 表中的行数据。
二叉查找树的特点就是任何节点的左子节点的键值都小于当前节点的键值，右子节点的键值都大于当前节点的键值。顶端的节点称为根节点，没有子节点的节点称之为叶节点。
平衡二叉树
平衡二叉树又称 AVL 树，在满足二叉查找树特性的基础上，要求每个节点的左右子树的高度差不能超过 1
平衡二叉树保证了树的构造是平衡的，当插入或删除数据导致不满足平衡二叉树不平衡时，平衡二叉树会进行调整树上的节点来保持平衡。平衡二叉树相比于二叉查找树来说，查找效率更稳定，总体的查找速度也更快。
B-树
因为内存的易失性。一般情况下都会选择将user表中的数据和索引存储在磁盘这种外围设备中。但是和内存相比，从磁盘中读取数据的速度会慢上百倍千倍甚至万倍，所以应当尽量减少从磁盘中读取数据的次数。另外从磁盘中读取数据时，都是按照磁盘块来读取的，并不是一条一条的读。如果能把尽量多的数据放进磁盘块中，那一次磁盘读取操作就会读取更多数据，那查找数据的时间也会大幅度降低。
如果用树这种数据结构作为索引的数据结构，那每查找一次数据就需要从磁盘中读取一个节点，也就是说的一个磁盘块。
二叉树的节点将会非常多，高度也会极其高，查找数据时也会进行很多次磁盘 IO，查找数据的效率将会极低
B 树相对于平衡二叉树，每个节点存储了更多的键值key和数据data，并且每个节点拥有更多的子节点，子节点的个数一般称为阶，例如B树为3阶B树，高度也会很低。基于这个特性，B树查找数据读取磁盘的次数将会很少，数据的查找效率也会比平衡二叉树高很多。
在BTree的机构下，就可以使用二分查找的查找方式，查找复杂度为【层高*log(结点数)】，一般来说树的高度是很小的，一般为3左右，因此BTree是一个非常高效的查找结构
B树的一些特点：
-
关键字集合分布在整颗树中
-
任何一个关键字出现且只出现在一个结点中
-
搜索有可能在非叶子结点结束
-
其搜索性能等价于在关键字全集内做一次二分查找
B树在插入删除新的数据记录会破坏B-Tree的性质，因为在插入删除时，需要对树进行一个分裂、合并、转移等操作以保持B-Tree性质
B+树
主要区别就是所有的节点值都在最后叶节点上用双向链表连接在了一起
但是二叉树不能使用，因为比对次数太多，所以使用多叉树以减少比对次数
B+Tree中的非叶子结点不存储数据，只存储键值
非叶子节点可以看成索引部分，节点中仅含有其子树（根节点）中的最大（或最小）关键字
B+Tree的叶子结点没有指针，所有键值都会出现在叶子结点上，且key存储的键值对应data数据的物理地址
B+Tree的每个非叶子节点由n个键值key和n个指针point组成
B+Tree对比B-Tree
磁盘读写代价更低：每个节点中的key个数越多，那么树的高度越小，需要I/O的次数越少，因此一般来说B+Tree比BTree更快，因为B+Tree的非叶节点中不存储data，就可以存储更多的key。
查询速度更稳定。由于B+Tree非叶子节点不存储数据data，因此所有的数据都要查询至叶子节点，而叶子节点的高度都是相同的，因此所有数据的查询速度都是一样的。
B+树的查找过程，与B树类似，只不过查找时，如果在非叶子节点上的关键字等于给定值，并不终止，而是继续沿着指针直到叶子节点位置。因此在B+树，不管查找成功与否，每次查找都是走了一条从根到叶子节点的路径。
B+树的特性如下：
所有关键字都存储在叶子节上，且链表中的关键字恰好是有序的。
非叶子节点相当于叶子节点的索引，叶子节点相当于是存储（关键字）数据的数据层。
更适合文件索引系统。
一般在数据库系统或文件系统中使用的B+Tree结构都在经典B+Tree的基础上进行了优化，增加了顺序访问指针。
具体的索引实现
在MyISAM中，主索引和辅助索引Secondary key在结构上没有任何区别，只是主索引要求key是唯一的，而辅助索引的key可以重复。
主键索引MyISAM引擎使用B+树作为索引结构，叶节点的data域存放的是数据记录的地址。
辅助索引：同样也是一棵B+树，data域保存数据记录的地址。因此，MyISAM中索引检索的算法为首先按照B+树搜索算法搜索索引，如果指定的Key存在，则取出其data域的值，然后以data域的值为地址，读取相应数据记录。
MyISAM的索引方式也叫做“非聚集”的，之所以这么称呼是为了与InnoDB的聚集索引区分
innodb是索引组织表。在InnoDB中，表数据文件本身就是按B+树组织的一个索引结构（就是索引组织表），这棵树的叶节点data域保存了完整的数据记录。
因为InnoDB的数据文件本身要按主键聚集（聚集索引），所以InnoDB要求表必须有主键（MyISAM可以没有），如果没有显式指定，则MySQL会自动选择一个可以唯一标识数据记录的列作为主键，如果不存在这种列，则MySQL自动为InnoDB表生成一个隐含字段作为主键，这个字段长度为6个字节，类型为长整型。
辅助索引首先检索辅助索引获得主键，然后用主键到主索引中检索获得记录。
扫一扫
610
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
