• day039 数据库索引


    今日内容:

    1.为什么要有索引

    简而言之,索引出现的意义是为了更方便,更快速的查询数据.

    什么是索引

      索引在mysql中也叫''键''或'key'(primary key unique key,index key),是存储引擎用于快速找到记录的一种数据结构.索引对于良好的性能非常关键,尤其是当表中的数据量越来越大时,索引对于性能的影响越发重要,减少is次数,加速查询.(其中primary key和unique key,除了有加速查询的效果之外,还有约束的效果,primary key 不为空且唯一,unique key唯一,而index key 只有加速查询的效果,没有约束的效果).

      索引优化应该是对查询性能优化最有效的手段了.索引能够轻松将查询性能提高好几个数量级.

      强调: 一旦为表创建了索引,以后的查询最好先查询索引,再根据索引定位的结果去找数据.

    2.索引的原理

      索引的目的就在于提高查询效率,就好比查字典的目录一样,先定位到某一个字母,以此类推.直到找到这个字.

    索引的影响: 

      1.在表中有大量数据的前提下,创建索引速度会很慢.

      2.在索引创建完毕后,对表的查询性能会大幅度提升,但是写性能会降低.

    本质是 : 通过不断地缩小想要获取数据的范围来筛选出最终想要的结果,同时把随机的事件变成顺序的事件,也就是说,有了这种索引机制,我们可以总是用同一种查找方式在锁定数据.

    3.聚集索引与辅助索引

    在数据库中,B+树的高度一般都在2-4层,这也就是说,查找某一个键值的行记录时最多只需要2到4次IO,这倒不错,因为当前一般的机械硬盘至少可以做100次IO,2-4次的IO以为这查询时间只需要0.02-0.04秒.

    数据库中的B+树索引可以分为聚集索引和辅助索引.

    聚集索引与辅助索引相同的是,不管是聚集索引还是辅助索引,其内部都是B+树的形式,即高度是平衡的,椰子阶段存放着所有的数据.

    不同的是 叶子节点存放的数据是否是一整行的信息.

    聚集索引

    #InnoDB存储引擎表示索引组织表,即表中数据按照主键顺序存放。而聚集索引(clustered index)就是
    按照每张表的主键构造一棵B+树,同时叶子结点存放的即为整张表的行记录数据,也将聚集索引的叶子结点称
    为数据页。聚集索引的这个特性决定了索引组织表中数据也是索引的一部分。同B+树数据结构一样,每个数据
    页都通过一个双向链表来进行链接。 #如果未定义主键,MySQL取第一个唯一索引(unique)而且只含非空列(NOT NULL)作为主键,InnoDB使
    用它作为聚簇索引。 #如果没有这样的列,InnoDB就自己产生一个这样的ID值,它有六个字节,而且是隐藏的,使其作为聚簇索引。 #由于实际的数据页只能按照一棵B+树进行排序,因此每张表只能拥有一个聚集索引。在多少情况下,查询优化
    器倾向于采用聚集索引。因为聚集索引能够在B+树索引的叶子节点上直接找到数据。此外由于定义了数据的逻
    辑顺序,聚集索引能够特别快地访问针对范围值得查询。

      聚集索引的好处之一: 他对主键的排序查找和范围查找速度非常快,叶子结点的数据就是用户所要查询的数据.如果用户需要查找一张表,查询最后的10位用户信息,由于B+树索引是双向链表,所以用户可以快速找到最后一个数据页,并取出10条记录.

      聚集索引的好处之二: 范围查询 ,即如果要查找主键某一范围内的数据,通过叶子节点的上层中间阶段就可以得到页的范围,之后直接读取数据页即可.

    辅助索引

      我们在查询的时候,where后面需要些id之外的其他字段名字来进行查询,比如说where name=xx,没法用到主键索引的效率,这是就需要我们添加辅助索引了,给name添加一个辅助索引.

    表中除了聚焦索引外其他索引都是辅助索引,与聚焦索引的区别: 辅助索引的叶子结点不包括行记录的全部数据.

    mysql索引管理

    1.索引的功能就是加速查找

    2.mysql中的primary key ,unique ,联合唯一也都是索引,这些索引出了加速查找以外,还有约束的功能.

    mysql常用的索引 

      1.普通索引: index 加速查找

      2.唯一索引: 

        主键索引primary key :加速查找+约束(不能为空,不能重复)

        唯一索引 unique : 加速查找+约束(不能重复)

      3.联合索引:

        primary key (id,name): 联合主键索引

        unique(id,name): 联合唯一索引

        index(id,name) : 联合普通索引

    索引的两大类型hash 与btree

    我们可以在创建上述索引的时候,为其指定索引类型,分两类
    hash类型索引: 查询单条快,范围查询慢
    btree类型的索引:b+树,层数越多,数量指数级增长(我们就用它,因为innodb默认支持他)
    
    #不用的储存索引引擎支持的索引类型也不一样
    InnoDB 支持事务,支持行级别锁定,支持 B-tree、Full-text 等索引,不支持 Hash 索引;
    MyISAM 不支持事务,支持表级别锁定,支持 B-tree、Full-text 等索引,不支持 Hash 索引;
    Memory 不支持事务,支持表级别锁定,支持 B-tree、Hash 等索引,不支持 Full-text 索引;
    NDB 支持事务,支持行级别锁定,支持 Hash 索引,不支持 B-tree、Full-text 等索引;
    Archive 不支持事务,支持表级别锁定,不支持 B-tree、Hash、Full-text 等索引;
    

    创建/删除索引的语法

    删除索引 : 

      语法: drop index 索引名 on 表名;

    方法1和方法2 是在创建表的时候插入索引,方法3是创建表之后插入索引

    方法一
        create table t1(
        id int,
        name char,
        age int,
        sex enum('male','female'),
        unique key un_id(id),
        index ix_name(name) # index 没有key
        );
    
    方法二
        create index ix_age(age) on t1(age);
    
    方法三
        alter table t1 add index ix_sex(sex);
    
    查看
    mysql> show create table t1;
    | t1    | CREATE TABLE `t1` (
      `id` int(11) DEFAULT NULL,
      `name` char(1) DEFAULT NULL,
      `age` int(11) DEFAULT NULL,
      `sex` enum('male','female') DEFAULT NULL,
      UNIQUE KEY `uni_id` (`id`),
      KEY `ix_name` (`name`),
      KEY `ix_age` (`age`),
      KEY `ix_sex` (`sex`)
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1
    

      尽量选择区分度高的列作为索引,区分度公式是count() .表示字段不重复的比例,比例越大我们扫描的记录数越少,唯一键的区分度是1,而一些状态,性别字段可能在大数据面前区分度是0,

  • 相关阅读:
    SGU 271 Book Pile (双端队列)
    POJ 3110 Jenny's First Exam (贪心)
    HDU 4310 Hero (贪心)
    ZOJ 2132 The Most Frequent Number (贪心)
    POJ 3388 Japanese Puzzle (二分)
    UVaLive 4628 Jack's socks (贪心)
    POJ 2433 Landscaping (贪心)
    CodeForces 946D Timetable (DP)
    Android Studio教程从入门到精通
    Android Tips – 填坑手册
  • 原文地址:https://www.cnblogs.com/zty1304368100/p/10300842.html
Copyright © 2020-2023  润新知