为什么要用索引
假设有一张表,表中有100万条数据,这100万条数据在硬盘上是存储在数据页上的,一页数据大小为16k。存储100万条数据那么就需要数据页,假设其中有一条数据是“id为7900”的,那么如果要查询这条数据,其中SQL是SELECT * FROM 表名 WHERE id = 7900。在执行这条SQL语句的时候,MySQL需要扫描全表来查询id = 7900的记录。
全表扫描就是从“数据页1”开始,向后逐页查询。对于少量数据来说,查询的速度会很快,但是,当随着数据量的增加,性能会急剧下降。100万条数据进行逐页查询的时间是无法被用户所接受的。
索引是什么
索引是帮助MySQL高效获取数据的数据结构。
是排好序的快速查找的数据结构。
索引就是类似于书的目录。
数据库在存储数据本身以外,还有维护着一个满足特定查找算法的数据结构,这些数据结构就以某种方式指向数据,这样就可以在这些数据结构的基础上实现高级查找算法,这种数据结构就是索引。
索引的原理
索引的目的在于提高查询效率,与我们查询图书所用的目录是一个道理:先定位到章,然后定位到该章下的一个小节,然后找到页数。相似的例子还有:查字典、查火车车次,飞机航班等。
本质上都是:通过不断地缩小想要获取数据的范围来筛选出最最终想要的结果,同时把随机的事件变成顺序的事件,也就是说,有了这种索引机制,我们就可以总是用同一种查找方式来锁定数据。
索引类似于书的目录,在一本书前面加上目录,查找内容时就不必逐页翻阅就能够快速地找到所要查找的内容。借助索引,执行查询时就不必扫描整个表就能够快速地找到所需要的数据。
优点
索引提高了数据检索的效率,降低了数据库IO成本。
通过索引列对数据进行排序,降低了数据排序的成本,降低了CPU的消耗。
缺点
实际上,索引本质上也是一张表,这个表保存了主键与索引之间的字段,并指向实体表的记录,所以索引列也是要占用磁盘的空间。
虽然索引大大提高了插叙的速度,但同时也会降低更新表数据的速度。例如:INSERT、UPDATE和DELETE,因为更新表时,MySQL不仅要保存数据,还要保存一次索引文件,每次更新添加了索引列的字段,都会调整因为更新所带来的键值变化后的索引信息。
创建索引的原则
索引虽好,但也不能乱用。
什么情况下需要索引
- 主键自动建立唯一索引。
- 频繁作为查询条件的字段应该创建索引(WHERE 后面的语句)。
- 查询中与其它表关联的字段,外键关系建立索引。
- 查询中排序的字段,排序字段若通过索引去访问将大大提高排序速度分组中的字段。
什么情况下不需要索引
- 表记录过少
- 经常性的增删改的表。虽然有提高了查询的速度,但同时却会降低更新表的速度,如对表进行INSERT、UPDATE和DELETE,因为更新表时,MySQL不仅要保存数据,还要保存一下索引的文件。(所以在遇到这种情况下,就会分表,分表就是将读、写的数据进行分离)
- WHERE条件里用不到的字段不会创建索引
- 数据重复且分布平均的表字段,因此应该只为最经常查询和最经常排序的数据列表建立索引,某个数据列包含许多重复的内容,建立索引没有太大的实际效果。
索引的分类
主键索引
就是设定主键后数据库会自动建立索引,一个表只能有一个主键。
ALTER TABLE 表名 ADD PRIMARY KEY 表名(列名);
删除主键索引:
ALTER TABLE 表名 DROP PRIMARY KEY;
单值索引
就是一个索引只包含单个列,一个表可以有多个单列索引。
加这个索引肯定是比不加索引要快的。
创建单值索引:
CREATE INDEX 索引名 ON 表名(列名);
删除索引:
DROP INDEX 索引名;
唯一索引
索引列的值必须唯一,允许为null;
CREATE UNIQUE INDEX 索引名 ON 表名 (列名);
删除索引:
DROP INDEX 索引名 ON 表名;
组合索引(复合索引)
即一个索引包含多个列,在数据库操作期间,复合索引比单值索引所需要的开销是更小的(对于相同的多个列键索引)。
当表的行数远大于索引列的数目时可以使用复合索引。
创建复合索引
CREATE INDEX 索引名 ON 表名(列1,列2,…);
删除索引
DROP INDEX 索引名 ON 表名;
组合索引最左前缀原则
例如表中有a,b,c 3列,为a,b两列创建组合索引,那么在使用时需要满足最左侧索引原则。即在使用组合索引的列为条件的时候,是必须要出现最左侧列作为条件,否则索引是不生效的。
例如:
SELECT * FROM 表名 WHERE a = ' ' AND b = ' ' ; #索引生效
SELECT * FROM 表名 WHERE b = ' ' AND b = ' ' ; #索引生效
SELECT * FROM 表名 WHERE a = ' ' AND b = ' ' ; # 索引生效
SELECT * FORM 表名 WHERE b = ' ' AND c = ' ' ; # 索引不生效
全文索引(仅在MySQL8之后有)
是需要模糊查询的,一般索引是无效的,这个时候就可以使用全文索引。
比如: WHERE name LIKE %J%,这样查询的时候,即使name列添加了索引,但也会索引失效的。所以是不建议使用的,在MySQL8当中就建议使用全文索引。
创建全文索引列:
CREATE FULLTEXT INDEX 索引名 ON 表名(列名) WITH TARSER ngram;
使用全文索引:
SELECT * FROM 表名 WHERE MATCH(列名) AGAINST(‘输入的参数’);
全文索引的插叙效率是比使用LIKE来查询要快的,所以在MySQL8之后是建议使用全文索引的。
查找索引:
SHOW INDEX FROM 表名;
索引的数据结构
在MySQL数据库中的InnoDB引擎是使用B+树来进行数据存储的。
B+树由二叉查找树、平衡二叉树(AVLTree)和平衡多落查找树(B-Tree)逐步优化而来的。使其更适合实现外存索引结构。
B+数的特点:
- 它是排好序的,一个节点可以存储多个数据。
- 非叶子节点是不存储数据的,只存储索引,是可以放更多的索引。
- 数据都是存放在在叶子节点中的。
- 所有的叶子节点之间都是有一个链指针的。
就是使用B+树使得树是横向扩展的,让树的高度降低了。在叶子节点也可以存储多个数据。
MySQL所以使用B+树,因为索引是用来加快查询的,而B+数通过对数据进行排序所以是可以提高查询速度,然后通过一个节点可以存储多个元素,从而可以使得B+树的高度不会太高。并且叶子节点之间存在指针,可以很好的支持全表扫描,范围查找等SQL语句。
聚簇索引和非聚簇索引
聚簇索引
就是找到了索引就找到了需要的数据,那么这个索引就是聚簇索引,所以主键就是聚簇索引。
非聚簇索引
索引的存储和数据的存储时分离的,也就是意味着找到了索引但没有找到数据,需要根据索引上的值(主键)再次回表查询,非聚簇索引也叫做辅助索引。
举例:
先创建一个表,用来说明说明情况下是聚簇索引和非聚簇索引:
CREATE TABLE student (
`id` INT PRIMARY KEY NOT NULL AUTO_INCREMENT COMMENT '主键id',
`name` VARCHAR (50) COMMENT '学生姓名',
`age` INT NOT NULL DEFAULT 0 COMMENT '学生年龄',
KEY `idx_name` (`name`)
)
创建的数据库表中,主键id是一个索引,给age添加了索引。
① 直接根据主键查询获取所有字段数据,此时主键就是聚簇索引。因为主键对应的索引叶子节点存储的就是id的所有字段值。
SELECT * FROM student WHERE id = 1;
② 根据name查询所有信息,name本身就是一个唯一索引,但查询的数据包括了所有的数据,所以当命中name索引的时候,该索引的节点的数据存储的就是主键的ID,然后需要ID再查询一次。
这就是非聚簇索引。
SELECT * FROM student WHERE name = “张三”;
索引就是再单独的创建一张表,在这个表中查找数据,然后拿到ID在返回所有数据的表中查询所有的数据。
③ 根据name查询的时候,我们只查询name的值,不查询其他的信息。这种查询就是命中name索引,直接返回name的值,因为所需要的数据就是索引,此时不需要会表查询。这种场景就是非聚簇索引。
SELECT name FROM student WHERE name = “张三”;
拓展
在MySQL中InnoDB引擎的索引和文件是存放在一起的,找到索引就可以找到数据,是聚簇式设计。(默认是聚簇设计的,当你加上其他的索引,根据变化就成为了非聚簇式)
而MyISAM引擎采用的是非聚簇式设计,索引文件和数据文件不在同一个文件中。(不管怎么创建,它本质上就是非聚簇的设计)文章来源:https://www.toymoban.com/news/detail-431134.html
因为在MyISAM当中,它是在MYD中存储文件的数据;在MYI中存储文件的索引;在sdi中存储表的结构。
文章来源地址https://www.toymoban.com/news/detail-431134.html
到了这里,关于MySQL数据库中的索引(含SQL语句)的文章就介绍完了。如果您还想了解更多内容,请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!