VB.net 2010 视频教程 VB.net 2010 视频教程 python基础视频教程
SQL Server 2008 视频教程 c#入门经典教程 Visual Basic从门到精通视频教程
当前位置:
首页 > 编程开发 > Java教程 >
  • MySQL索引详细

一、简介

索引用于快速找出在某个列中有一特定值的行。

不使用索引,MySQL必须从第一条记录开始读完整个表,直到找出相关的行,表越大,查询数据所花费的时间就越多,如果表中查询的列有一个索引,MySQL能够快速到达一个位置去搜索数据文件,而不必查看所有数据,那么将会节省很大一部分时间。

如果有了索引,那么会将该Phone字段,通过一定的方法进行存储,好让查询该字段上的信息时,能够快速找到对应的数据,

二、优缺点

2.1优点

  1. 所有的MySql列类型(字段类型)都可以被索引,也就是可以给任意字段设置索引

  2. 大大加快数据的查询速度

2.2缺点

创建索引和维护索引要耗费时间,并且随着数据量的增加所耗费的时间也会增加

索引也需要占空间,数据表中的数据也会有最大上限的
如果我们有大量的索引,索引文件可能会比数据文件更快达到上线值

当对表中的数据进行增加、删除、修改时,索引也需要动态的维护,降低了数据的维护速度。

三、分类

操作

查看索引:show index from

删除索引:drop index <索引名> on <表名>

3.1 单列索引

1.主键索引(自动创建)

 
create table user(id varchar(20) primary key,name varchar(20))

2.普通索引

MySQL中基本索引类型,没有什么限制,允许在定义索引的列中插入重复值和空值,纯粹为了查询数据更快一点。

 
-建表时创建(此时索引名和列名相同)
create table user(id varchar(20) primary key, name varchar(20),key(name))
-建表后创建
create index name_index on user(name)

3.唯一索引

索引列中的值必须是唯一的,但是允许为空值,

 
-建表示创建
create table user(id varchar(20) primary key, name varchar(20),unique(name))
-建表后创建
create unique index name_index on user(name)

3.2 组合索引(复合索引)

在表中的多个字段组合上创建的索引
只有在查询条件中使用了这些字段的左边字段时,索引才会被使用,使用组合索引时遵循最左前缀集合。

 
-建表示创建
create table user (id varchar(20) primary key, name varchar(20), age int,key(name,key))
-建表后创建
create index name_age_index on user(name,age)

MySQL 最左前缀原则

mysql 建立多列索引(联合索引)有最左前缀的原则,即最左优先,如:

如果有一个 2 列的索引 (col1, col2),则已经对 (col1)、(col1, col2) 上建立了索引;
如果有一个 3 列索引 (col1, col2, col3),则已经对 (col1)、(col1, col2)、(col1, col2, col3) 上建立了索引;

并且mysql 具有查询优化器

MySQL 的查询优化器会自动调整 where 子句的条件顺序以使用适合的索引,所以 MySQL 不存在 where 子句的顺序问题而造成索引失效

四、数据结构

在mysql 使用InnoDB存储引擎时,首先会将插入的数据按照主键进行排序从而形成一个单向链表,然后为了提高查找效率,mysql就将单向链表升级成了B+树。

一般高度为3的B+数可存储的记录为10亿左右,因此对于我们2-3层树就足够了。

4.1 B+树特点

B+树是B树的一种变形,比B树具有更广泛的应用

  • 为所有叶子结点增加一个链指针;
  • 所有关键字都在叶子结点出现
  • 非叶子结点作为叶子结点的索引;B+树总是到叶子结点才命中;

4.2为什么使用B+数

五、聚簇/非聚簇索引

5.1 聚簇索引

定义

将数据与索引放到了一起,索引结构的叶子节点保存了行数据。

简介

1.如果一个主键被定义了,那么这个主键就是作为聚集索引

  1. 如果没有主键被定义,那么该表的第一个唯一非空索引被作为聚集索引

  2. 如果没有主键也没有合适的唯一索引,那么innodb内部会生成一个隐藏的主键作为聚集索引,这个隐藏的主键是一个6个字节的列,改列的值会随着数据的插入自增。

  3. 自增主键会把数据自动向后插入,避免了插入过程中的聚集索引排序问题。聚集索引的排序,必然会带来大范围的数据的物理移动,这里面带来的磁盘IO性能损耗是非常大的。

注意

​ 主键索引一定是聚簇索引(在mysql数据库innodb引擎里面,主键的确就是聚集索引。)

5.2 非聚簇索引(辅助索引)

将数据与索引分开存储,索引结构的叶子节点指向了数据对应的位置。

辅助索引访问数据总是需要二次查找

叶子节点存储的是主键值:(不存主键地址原因)如果数据记录发生了页裂变导致数据地址变了,那辅助索引也要更新,对于这种情况来说存储主键更好

5.3注意

建议使用int 自增作为主键

原因:

聚簇索引的数据在索引中存放顺序与物理存放顺序是一样的,那么,只要索引是相邻的,对应的数据在磁盘上也是相邻的。
如果不是自增id,那么在添加记录时就会不断地调整数据的地址、数据的物理地址、分页,而不是直接添加到索引树的末尾。
如果是自增的,就简单了,直接一页一页逐步添加,索引结构相对紧凑,并且磁盘碎片也少,效率高。

六、无法使用索引

情况一:查询语句中使用like关键字

​ 如果使用like关键字,并且匹配字符串时的第一个字符为“%”,那么就无法查询索引树,因为不知道要查询什么。但如果%在后面,就可以 使用。

情况二:查询语句中使用多列索引

​ 不满足最左前缀原则时

情况三:查询语句中使用or关键字

​ 只有当or两边的字段都创建了索引,才可以使用索引,否则无法使用。

__EOF__

  • 本文作者: 郜宇博
  • 本文链接: https://www.cnblogs.com/Gao-yubo/p/15248347.html

    
    相关教程