一、索引
MySQL索引的建立对于MySQL的高效运行是很重要的,索引可以大大提高MySQL的检索速度。
打个比方,如果合理的设计且使用索引的MySQL是一辆兰博基尼的话,那么没有设计和使用索引的MySQL就是一个人力三轮车。
索引分单列索引和组合索引。单列索引,即一个索引只包含单个列,一个表可以有多个单列索引,但这不是组合索引。组合索引,即一个索引包含多个列。
创建索引时,你需要确保该索引是应用在 SQL 查询语句的条件(一般作为 WHERE 子句的条件)。
实际上,索引也是一张表,该表保存了主键与索引字段,并指向实体表的记录。
上面都在说使用索引的好处,但过多的使用索引将会造成滥用。因此索引也会有它的缺点:虽然索引大大提高了查询速度,同时却会降低更新表的速度,如对表进行INSERT、UPDATE和DELETE。因为更新表时,MySQL不仅要保存数据,还要保存一下索引文件。
建立索引会占用磁盘空间的索引文件。
二、索引类型
Mysql目前主要有以下几种索引类型:FULLTEXT,HASH,BTREE,RTREE。
- FULLTEXT
即为全文索引,目前只有MyISAM引擎支持。其可以在CREATE TABLE ,ALTER TABLE ,CREATE INDEX 使用,不过目前只有 CHAR、VARCHAR ,TEXT 列上可以创建全文索引。
全文索引并不是和MyISAM一起诞生的,它的出现是为了解决WHERE name LIKE “%word%"这类针对文本的模糊查询效率较低的问题。
- HASH
由于HASH的唯一(几乎100%的唯一)及类似键值对的形式,很适合作为索引。
HASH索引可以一次定位,不需要像树形索引那样逐层查找,因此具有极高的效率。但是,这种高效是有条件的,即只在“=”和“in”条件下高效,对于范围查询、排序及组合索引仍然效率不高。
-
BTREE
BTREE索引就是一种将索引值按一定的算法,存入一个树形的数据结构中(二叉树),每次查询都是从树的入口root开始,依次遍历node,获取leaf。这是MySQL里默认和最常用的索引类型。 -
RTREE
RTREE在MySQL很少使用,仅支持geometry数据类型,支持该类型的存储引擎只有MyISAM、BDb、InnoDb、NDb、Archive几种。
相对于BTREE,RTREE的优势在于范围查找。
ps. 此段详细内容见此片博文:Mysql几种索引类型的区别及适用情况
三、索引种类
普通索引:仅加速查询
唯一索引:加速查询 + 列值唯一(可以有null)
主键索引:加速查询 + 列值唯一(不可以有null)+ 表中只有一个
组合索引:多列值组成一个索引,专门用于组合搜索,其效率大于索引合并
全文索引:对文本的内容进行分词,进行搜索
ps.
索引合并,使用多个单列索引组合搜索
覆盖索引,select的数据列只用从索引中就能够取得,不必读取数据行,换句话说查询列要被所建的索引覆盖
主键索引和唯一索引的区别
主键约束(PRIMARY KEY):
1.主键用于唯一的标识表中的每一条记录,可以定义一类或多列为主键。
2.表里面只能有一个主键约束,但可以有多个唯一约束。
3.主键列上没有任何两行具有相同值(即重复值),不允许空(null)。
4.主键可作外键,唯一索引不可。
唯一约束(UNIQUE):
1.唯一约束用来限制不受主键约束的列上的数据的唯一性,用于作为访问某行的可选手段,一个表上可以防止多个唯一性约束。
2.只要唯一就可以更新。
3.表中任意两行在指定列上都不允许有相同的值,允许空(NULL)。
4.一个表上可以放置多个唯一约束。
索引是一种特殊的文件(InnoDB数据表上的索引是表空间的一个组成部分),它们包含着对数据表里所有记录的引用指针。
总体来说:主键相当于一本书的页码,索引相当于书的目录。
其实主键和索引都是键,不过主键是逻辑键,索引是物理键,意思就是主键不实际存在,而索引实际存在在数据库中,主键一般都要建,主要是用来避免一张表中有相同的记录,索引一般可以不建,但如果需要对该表进行查询操作,则最好建,这样可以加快检索的速度。
索引是一种高效获取数据的存储结构
hash、 二叉、 红黑
Mysql为什么不用上面三种数据结构而采用B+Tree:
若仅仅是 select * from table where id=45 , 上面三种算法可以轻易实现,但若是select *
from table where id<6 ,
就不好使了,它们的查找方式就类似于"全表扫描",因为他们的高度是不可控的(如下图)。B+Tree的高度是可控的,mysql通常是3到5层。注意:B+Tree只在最末端叶子节点存数据,叶子节点是以链表的形势互相指向的。
MyISAM引擎和InnoDB引擎的区别
MyISAM:支持全文索引;不支持事务;它是表级锁;会保存表的具体行数.
InnoDB:5.6以后才有全文索引;支持事务;它是行级锁;不会保存表的具体行数.
一般:不用事务的时候,count计算多的时候适合myisam引擎。对可靠性要求高就是用innodby引擎。推荐用InnoDB引擎.加了索引之后能够大幅度的提高查询速度,但是索引也不是越多越好,一方面它会占用存储空间,另一方面它会使得写操作变得很慢。通常我们对查询次数比较频繁,值比较多的列才建索引。
例如:select * from user where sex = “女”,
这个就不需要建立索引,因为性别一共就两个值,查询本身就是比较快的。
select * from user where user_id = 1995 ,这个就需要建立索引,因为user_id的值是非常多的。
Myisam引擎(非聚集索引)
若以这个引擎创建数据库表Create table user (……),它实际是生成三个文件:
user.myi 索引文件 user.myd数据文件 user.frm数据结构类型。
如下图:当我们执行 select * from user where id = 1的时候,它的执行流程。
(1)查看该表的myi文件有没有以id为索引的索引树。
(2)根据这个id索引找到叶子节点的id值,从而得到它里面的数据地址。(叶子节点存的是索引和数据地址)。
(3)根据数据地址去myd文件里面找到对应的数据返回出来。
Innodb引擎(聚集索引)
若以这个引擎创建数据库表Create table user (……),它实际是生成两个文件:
user.ibd 索引文件 user.frm数据结构类型
因为innodb引擎创建表默认就是以主键为索引,所以不需要myi文件。
下图为innodb表的结构图:很显然它与myisam最大的区别是将整条数据存在叶子节点,而不是地址。(叶子节点存的是主键索引和数据信息)
若此时,你在其他列创建索引例如name,它就会另外创建一个以name为索引的索引树,(叶子节点存的是索引和主键索引)。
你在执行select * from user where name = ‘吴磊’,他的执行过程如下:
(1)找到name索引树
(2)根据name的值找到该树下叶子的name索引和主键值
(3)用主键值去主键索引树去叶子节点到该条数据信息
B+Tree的特性
(1)由图能看出,单节点能存储更多数据,使得磁盘IO次数更少。
(2)叶子节点形成有序链表,便于执行范围操作。
(3)聚集索引中,叶子节点的data直接包含数据;非聚集索引中,叶子节点存储数据地址的指针。