MySQL-索引

azhou 2020-05-03

1.索引的概念

数据库查找时,没有索引的话需要遍历,浪费时间。通过索引能更快定位要要查找的内容的位置,索引就是Java/C里面的数组下标,通过下标直接获取数组的内容。数据库索引的实现有2种,B+树和哈希。

2.索引的优缺点

(1)优点:所有的字段都可以通过添加索引从而提高查找效率。

(2)缺点:创建索引需要花费时间,删除、修改、插入记录即维护表的索引也要花费时间。索引也占空间,数据表中的数据也会有最大上线设置的,如果我们有大量的索引,索引文件可能会比数据文件更快达到上线值。

(3)使用原则:对于需要经常查询的字段可以添加索引提高查找效率;对于需要频繁修改的表最好不要设置太多索引;对于数据量少的表暴力一下就完了,添加索引未必能有明显优化效果。
3.了解一下存储引擎

MySQL数据库在实际的工作中其实分为了语句分析层和存储引擎层,其中语句分析层就主要负责与客户端完成连接并且事先分析出SQL语句的内容和功能,而存储引擎层则主要负责接收来自语句分析层的分析结果,完成相应的数据输入输出和文件操作。简而言之,就是如何存储数据、如何为存储的数据建立索引和如何更新、查询数据等技术的实现方法。因为在关系数据库中数据的存储是以表的形式存储的,所以存储引擎也可以称为表类型(即存储和操作此表的类型)。与其他数据库例如Oracle和SQL Server等数据库中只有一种存储引擎不同的是,MySQL支持多种存储引擎供用户具体问题具体选择。

有两种常用的存储引擎MyISAM(高速引擎,拥有较高的插入,查询速度,但不支持事务)和InnoDB(5.5版本后MySQL的默认引擎支持事务和行级锁定,比MyISAM处理速度稍慢)。都是B+树索引。不同的是前者是非聚集索引,后者主键是聚集索引,所谓聚集索引是物理地址连续存放的索引,在取区间的时候,查找速度非常快,但同样的,插入的速度也会受到影响而降低。聚集索引的物理位置使用链表来进行存储。

MyISAM和InnoDB存储引擎:默认只支持B+树索引;不能够更换MEMORY/HEAP存储引擎:支持哈希和B+树索引。

4.了解一下文件结构

--InnoDB数据库引擎文件结构目录
|--- mysql
    |--- data
        |--- ib_logfile0  
        |--- ib_logfile1  
        |--- ibdata1  系统表空间文件,存储InnoDB系统信息、用户数据库表数据、索引
        |--- 数据库
            |--- 表名.frm  保存表的元数据,包括表结构定义;在数据库奔溃的时候恢复表结构
            |--- 表名.ibd  单表表空间文件,每个表使用一个表空间文件,存放用户数据库表数据、索引
--MyISAM数据库引擎
|--- mysql
    |--- data
        |--- 数据库
            |--- 表名.frm
            |--- 表名.myd   全称 .mydata,存放表数据
            |--- 表名.myi   全称 .myindex,存放索引文件
            |--- 表名.log   日志文件

MySQL-索引

6.了解一下哈希与B+树

(1)哈希

把Key通过一个固定的算法函数既所谓的哈希函数转换成一个整型数字,然后就将该数字对数组长度进行取余,取余结果就当作数组的下标,将value存储在以该数字为下标的数组空间里。读写都是O(1),效率高,无非多开点哈希数组,空间换时间。

  • 仅能满足“=”、“in”和“<=>”查询,不能使用范围查询;
  • 无法被用来避免数据的排序操作,通过Hash计算得到的Hash值不一定是原来的键值。
  • 不能利用部分索引键查询,对于组合索引,是把所有组合索引键合并在一起再计算Hash值。
  • 如果大量Hash值相等,性能不一定比B+树高。

(2)B+树

B+树的插入删除操作

哈希索引不好的地方用B+树索引都是好的。

6.索引分类

(1)单列索引:一个索引只包含单个列,但一个表中可以有多个单列索引。 这里不要搞混淆了。

  • 普通索引:MySQL中基本索引类型,没有什么限制,允许在定义索引的列中插入重复值和空值,纯粹为了查询数据更快一点
  • 唯一索引:索引列中的值必须是唯一的,但是允许为空值
  • 主键索引:是一种特殊的唯一索引,不允许有空值

(2)组合索引:在表中的多个字段组合上创建的索引,只有在查询条件中使用了这些字段的左边字段时,索引才会被使用,使用组合索引时遵循最左前缀集合。例如,这里由id、name和age3个字段构成的索引,索引行中就按id/name/age的顺序存放,索引可以索引下面字段组合(id,name,age)、(id,name)或者(id)。如果要查询的字段不构成索引最左面的前缀,那么就不会是用索引,比如,age或者(name,age)组合就不会使用索引查询

(3)全文索引:全文索引,只有在MyISAM引擎上才能使用,只能在CHAR,VARCHAR,TEXT类型字段上使用全文索引,介绍了要求,说说什么是全文索引,就是在一堆文字中,通过其中的某个关键字等,就能找到该字段所属的记录行,比如有"你是个靓仔,靓女 ..." 通过靓仔,可能就可以找到该条记录。

(4)空间索引:空间索引是对空间数据类型的字段建立的索引,MySQL中的空间数据类型有四种,grometry、point、linestring、polygon。在创建空间索引时,使用spatial关键字。要求,引擎为MyISAM,创建空间索引的列,必须将其声明为not null。


参考&引用

https://www.jianshu.com/p/0d6c828d3c70

https://www.jianshu.com/p/efc4ff85932d

https://blog.csdn.net/Baron0071/article/details/86089914

相关推荐