前言一、索引概述二、索引优缺点
2.1索引优点2.2索引缺点 三、索引创建原则
3.1 建议创建索引的情况3.2不建议创建索引的情况 四、索引分类
4.1主键索引4.2单值索引4.3唯一索引4.4组合索引4.5全文索引4.6聚簇索引和非聚簇索引 五、B+树
5.1二分搜索树5.2红黑树5.3B树5.4B+树5.5mysql索引为什么用B+树 总结
前言
以往我们在数据库里查询数据的时候,因为数据量不大,所以我们对查询效率的高低没有一个明显的感觉,试想一下如果我们的数据库中有几百万条数据,我们现在要在数据库中找到学号为1921024987的数据,需要从第一页数据开始逐页查找(mysql采用数据页存储数据,一页数据大小为16K,如果数据量很大会产生很多的数据页),这种查找效率是非常低的。这时候索引的作用就体现出来了,索引能帮助我们快速找到所需要数据的位置,从而提高数据库操作效率。
一、索引概述
数据库中的索引与书籍中的目录类似,在一本书中,利用索引可以快速找到所需要的信息。在数据库中,索引使数据库程序不需要对整个表进行扫描(这种复杂度为O(n)的算法在数据量很大时效率非常低),就可以找到所需要的数据。
简单的来说索引可以提高SQL查询效率,让用户更快的得到查询结果
索引是排好序的快速查找的数据结构,在下面的内容中会详细介绍索引所使用的数据结构
二、索引优缺点 2.1索引优点
1.有助于加快数据检索,降低数据库I/O成本,这也是创建索引的最主要的原因。
2.通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性
3.可以加速表和表之间的连接，实现数据的参考完整性方面特别有意义。
4.在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间,降低了 CPU 的消耗。
2.2索引缺点
1.创建索引和维护索引需要耗费时间,当数据量增大的时候更加明显
2.索引需要占物理空间
3.表中的数据进行增加、删除和修改的时候，索引也要动态的维护,提高了CPU的消耗
三、索引创建原则
从上面我们可以知道索引虽然能给我们带来很多的好处,但同时也会给我们带来一系列的维护和空间问题。所以我们在创建索引的时候需要遵循一定的原则,这样才能把索引利用的恰到好处。
3.1 建议创建索引的情况1.根据查询要求建立索引:查询频率高、实时要求高的字段应该创建索引,如主键、外键、经常需要连接查询的字段、排序的字段、查询指定范围的字段。
2.数据量大的大表应该创建索引
3.2不建议创建索引的情况
1.对数据表查询时很少引用到的、大量重复的字段不应该创建索引。
2.数据量非常小的数据表,索引能够改进其数据访问的效率十分有限,不必创建索引。
3.对于一个基本表不应该建立过多的索引,数据表进行增删改时,索引也随之变化。索引需要占用文件目录和存储空间,而且需要维护,过多会使系统负担加重。
四、索引分类 4.1主键索引
设定为主键后数据库会自动建立索引
-- 创建主键索引 ALTER TABLE test ADD PRIMARY KEY test(id); -- 删除主键索引 ALTER TABLE test DROp PRIMARY KEY
4.2单值索引
一个索引只包含单个列,一个表可以有多个单列索引。
-- 创建单值索引 CREATE INDEX idx_name ON test(NAME); -- 删除索引 DROP INDEX idx_name;
4.3唯一索引
索引列的值必须唯一,允许为 null,唯一索引保证了数据的唯一性。
-- 创建唯一性索引 CREATE UNIQUE INDEX idx_sno ON test(sno); -- 删除索引 DROP INDEX idx_sno ON test;
关于主键和唯一性索引的联系和区别
1.主键一定是唯一性索引,唯一性索引不一定是主键
2.一个表中只能有一个主键,但可以有多个唯一性索引
3.主键不允许有null值,唯一性索引允许有null值
4.4组合索引
组合索引也可以成为复合索引,一个索引包含多个列,组合索引比单值索引开销更小(对于相同的多个列建索引)
-- 创建复合索引 CREATE INDEX idx_grade_class_phone ON test(grade,class,phone); -- 删除索引 DROP INDEX idx_grade_class_phone ON test;
组合索引最左前缀原则
在使用组合索引的列作为条件时,必须要出现最左侧列为条件,否则索引不生效
-- 生效 SELECT * FROM test WHERe grade='' AND class='' AND phone='' SELECt * FROM test WHERe grade='' AND sno='' -- 不生效 SELECt * FROM test WHERe class='' AND phone=''
4.5全文索引
在数据量非常大的时候使用 like 进行模糊匹配效率十分低下(会导致索引失效,导致全表扫描),这时候就要用到全文索引
-- 创建全文索引
CREATE FULLTEXT INDEX idx_address ON test(address) WITH PARSER ngram;
-- 使用全文索引
SELECt * FROM test WHERe MATCH(address) AGAINST('搜索词')
4.6聚簇索引和非聚簇索引
聚簇索引: 找到了索引,就找到了数据。
非聚簇索引 : 也叫辅助索引, 找到了索引但没有找到数据, 需要根据索引上的值(主键)再次回表查询
下面我们通过几个场景来理解聚簇索引和非聚簇索引的区别。
1.根据主键查询获取所有字段数据，此时主键是聚簇索引
SELECt * FROM student WHERe id = 1
2.给学号加了唯一索引,但需要查询姓名信息,需要先找到主键(ID),然后通过主键回表查询,这种情况学号不是聚簇索引。
SELECt Sno,NAME FROM student WHERe Sno = 123
3.通过学号查学号(验证是否存在),这种情况直接返回学号,不需要回表查询,是聚簇索引
SELECt Sno FROM student WHERe Sno = 123
MySQL中InnoDB 引擎的索引和文件是存放在一起的,找到索引就可以找到数据,是聚簇式设计
MyISAM 引擎采用的是非聚簇式(即使是主键)设计,索引文件和数据文件不在同一个文件中
五、B+树 5.1二分搜索树
如果数据是单边增长的情况 那么出现的就是和链表一样的数据结构了，树高度大
在二分搜索树的基础上多了树平衡，也叫平衡二叉树，不像二分搜索树那样极端的情况会往一个方向发展。
在红黑树的基础上，每个节点可以存放多个数据
B树的变种
以InnoDB存储引擎为例：
InnoDB 存储引擎就是用 B+Tree 实现其索引结构。B+树只会在叶子节点上面挂载数据，非叶子节点不会存放数据，非叶子节点只存放索引。
通过对数据排序提高查询效率,一个节点中可以存储多个元素，从而可以使得 B+树的高度不会太高,并且叶子节点之间有指针，可以很好的支持全表扫描，范围查找等 SQL 语句。
总结
本文介绍了索引的概念、优缺点、创建原则、分类以及索引的数据结构等内容。索引类似书籍的目录可以快速帮助我们找到需要的信息。索引在sql优化中起到了关键的作用,好的索引设计能极大的提高数据库效率,减少数据库的压力。本文对于索引的数据结构只是做了一个简单的概述,读者可以自行阅读相关资料深入了解。
