T O P

[资源分享]     MySQL索引详细

  • By - 楼主

  • 2021-09-09 20:00:01
  • 一、简介

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

    不使用索引,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两边的字段都创建了索引,才可以使用索引,否则无法使用。

    本帖子中包含资源

    您需要 登录 才可以下载,没有帐号?立即注册