千字对MySQL索引的理解_列值可以包含duplicate和null的索引称为-优快云博客

本文链接：https://blog.youkuaiyun.com/weixin_48304611/article/details/123832068

数据库为什么要有索引

如果没有索引的前提下,假设一个表中有十万条数据,从第一个条记录开始读完整个表,直到找出相关的行。表数据越多，查询的数据所花费的时间就越多.
如果表中某一列加上索引，MySQL能快速到达某个位置去搜寻数据文件,不必要查看所有数据.

索引简介

教科书式回答:索引是对数据库表中一列或者多列的值进行排序的一种数据结构,使用索引可以提高数据库中特定数据的查询速度

通俗式回答:索引就是用于快速找出某个列有特定值的一行

例如Table表中有5万条数据,现在要执行一个查询 select * from table where num=50000,如果没有索引,就必须遍历整个表,直到找到最后一行数据;如果在num列加上索引,MySQL不需要任何扫描,直接在索引找到50000,就可以直到这一行的位置

索引是在存储引擎中实现,因此,每一种存储引擎的索引都不一定完全相同,并且每种存储引擎也不一定支持所有的索引类型.MySQL中索引的存储类型有两种即BTREE和HASH,具体和表的存储引擎有关;MyISAM和InnoDB存储引擎支持BTREE索引(90%人使用InnoDB);MEMORY/HEAP存储引擎可以支持HASH和BTREE

索引的优缺点

优点

通过创建唯一索引,可以保证数据库每一行的唯一性
可以快速加大查询速度
在实现数据的参考完整性方面,可以加速表和表之间的连接
在使用分组和排序进行数据查询时,也可以显著减少查询分组和排序的时间

缺点

创建和维护索引时要耗费时间,并且随着数据的增加所耗费的时间会增加
索引存储在磁盘中,每个索引要占用一定的物理空间
当对表中的数据进行增,删,改时,索引也要进行维护

索引的逻辑分类

普通索引和唯一索引

普通索引:是指那一列的数据可以有重复值和null值

唯一索引:要求那一列的数据不允许有重复值,但可以允许有空值,主键索引是特殊的唯一索引,不允许有空值

单列索引和组合索引

单列索引:即一个索引值包含单个列,一个表中可以包括多个单个索引

组合索引:是指在表中的多个列上创建的索引,只有在查询条件中使用了这些字段的左边字段时,索引才会被使用

全文索引

全文索引:可以在char,varchar,text类型的列上创建,MySQL中只有MyISAM存储引擎支持全文索引,允许有重复值和空值

空间索引

空间索引:对空间数据类型的字段建立的索引,MySQL中空间数据类型有四种geometry,point,linestring,polygon,MySQL中只有MyISAM存储引擎支持空间索引

索引设计的原则

最好不要建立在数据区别度低的列上,比如性别列上,只有男女两类数据,建立索引意义不大
索引并非越多越好,一个表中的索引越多,不仅占用磁盘空间,还会影响INSERT,DELETE,UPDATE等语句
数据量小的表最好不使用索引,因为数据较短,查询花费的时间可能比遍历索引的时间还要短
频繁更新数据的列上尽量不使用索引
在频繁进行排序或分组的列上建议添加索引

索引的创建和删除

MySQL支持多种方法建立索引;在创建表时定义索引,或者在已存在的表上添加索引

创建表时添加索引

 CREATE TABLE book (
	id INT NOT NULL,
	bookname VARCHAR (25) NOT NULL,
	author VARCHAR (25) NOT NULL,
	info VARCHAR (25) NOT NULL,
	COMMENT text NOT NULL,
	year_publication YEAR NOT NULL,
	INDEX(year_publication),-- 普通索引
	UNIQUE INDEX uniqueIdx(id), -- 唯一索引
    FULLTEXT INDEX fulltxIdx(comment) -- 全文索引
	)

使用ALTER TABLE创建索引

alter table book add index bkNameIdx(bookname(30)) -- 添加长度为30的普通索引

alter table book add unique index bkNameIdx(info) -- 添加唯一索引

使用create index创建索引

在MySQL中,create index 被映射到一个Alter table语句上

create index BkNameIdx on book(bookname)

使用ALTER TABLE删除索引

alter table book drop index bkNameIdx

使用DROP INDEX删除索引

drop index index_name on table_name

tips:删除表中的列时,如果要删除的列为索引的组成部分,则该列也会从索引中删除,如果索引中的列全被删除,整个索引也会被删除

聚簇索引和非聚簇索引

这两种索引不是一种索引类型而是一种数据存储方式,这种存储方式是靠B+树实现,根据表的主键结构构造一颗B+树且B+树叶子节点存放的都是表的行记录数据时,方可称为主键索引为聚簇索引.

聚簇索引可以理解为将数据存储与索引放到了一块,找到索引也就找到了数据

非聚簇索引:数据和索引是分开的,B+树叶子节点存放的不是数据表的行记录

组合索引为什么要注意索引中的顺序

MySQL可以使用多个字段同时建立一个索引,在联合索引中,如果想命中索引,需要按照建立索引时的字段挨个使用

具体原因:MySQL中使用索引时需要索引有序,假设现在建立了name,age,sex的联合索引,那么索引的排序为:name排序,如果那么相同,就按照age,age之后为sex,进行查询时,此时索引仅仅按照name严格有序,因此必须首先使用name字段等值查询,对于匹配之后的列而言,在对age字段严格有序,以此类推.因此在建立联合索引时候需要注意索引的顺序,将查询需求频繁或字段选择性高的列放在前面