MySQL (索引)

一、初识索引

      索引在MySQL中也叫是?种“键”,是存储引擎?于快速找到记录的?种数据结构。

  索引对于良好的性能 非常关键,尤其是当表中的数据量越来越?时,索引对于性能的影响

  愈发重要。索引优化应该是对查询性能优化最有效的?段了。索引能够轻易将查询性能提?

  好?个数量级。索引相当于字典的?序表,如果要查某个字,如果不使??序表,则需要从

  ?百?中逐?去查。

 

?、索引的原理

 

    索引的?的在于提?查询效率、通过不断地缩?想要获取数据的范围来筛选出最终想要

  结果,同时把随机的事件变成顺序的事件,也就是说,有了这种索引机制,我们可以总是?

  ?种查找?式来锁定数据。

 

三、索引的数据结构   

    树状图是?种数据结构,它是由n(n>=1)个有限结点组成?个具有层次关系的集合。把它叫做“树”是因为它
  看起来像?棵倒挂的树,也就是说它是根朝上,?叶朝下的。

    B+树 ,b+树应运??(B+树是通过?叉 查找树,再由平衡?叉树,B树演化?来)

  b+树性质 1**.索引字段要尽量的?**:通过上?的分析,我们知道IO次数取决于b+数的?度h,假设当前数据

  表的数据为 N,每个磁盘块的数据项的数量是m,则有h=㏒(m+1)N,当数据量N?定的情况下,m越?,h越?;

  ?m = 磁 盘块的?? / 数据项的??,磁盘块的??也就是?个数据?的??,是固定的,如果数据项占的空间

  越?, 数据项的数量越多,树的?度越低。这就是为什么每个数据项,即索引字段要尽量的?,比如int占4字

  节,要 比bigint8字节少?半。这也是为什么b+树要求把真实的数据放到叶?节点?不是内层节点,?旦放到内层

  节 点,磁盘块的数据项会?幅度下降,导致树增?。当数据项等于1时将会退化成线性表。 2.索引的最左匹配特

  性:当b+树的数据项是复合的数据结构,比如(name,age,sex)的时候,b+数是按照从左到右的顺序来建立搜索 树

  的,比如当(张三,20,F)这样的数据来检索的时候,b+树会优先比较name来确定下?步的所搜?向,如果 name相

  同再依次比较age和sex,最后得到检索的数据;但当(20,F)这样的没有name的数据来的时候,b+树就 不知道下

  ?步该查哪个节点,因为建立搜索树的时候name就是第?个比较因?,必须要先根据name来搜索才 能知道下?

  步去哪?查询。比如当(张三,F)这样的数据来检索时,b+树可以?name来指定搜索?向,但下?个 字段age的缺

  失,所以只能把名字等于张三的数据都找到,然后再匹配性别是F的数据了, 这个是非常重要的性 质,即索引的

  最左匹配特性。

四、聚集索引

    #InnoDB存储引擎表是索引组织表,即表中数据按照主键顺序存放。?聚集索引(clustered index)就是按照每
  张表的主键构造?棵B+树,同时叶?结点存放的即为整张表的?记录数据,也将聚集索引的叶?结点称为数据?。聚
  集索引的这个特性决定了索引组织表中数据也是索引的?部分。同B+树数据结构?样,每个数据?都通过?个双向链
  表来进?链接。 

  聚集索引的好处之?:它对主键的排序查找和范围查找速度非常快,叶?节点的数据就是?户所要查询的数 据。如?

  户需要查找?张表,查询最后的10位?户信息,由于B+树索引是双向链表,所以?户可以快速找到最 后?个数据?,

  并取出10条记录

  聚集索引的好处之?:范围查询(range query),即如果要查找主键某?范围内的数据,通过叶?节点的上 层中间节

  点就可以得到?的范围,之后直接读取数据?即可

  聚集索引和非聚集索引的区别

  聚集索引 1.纪录的索引顺序与??顺序相同 因此更适合between and和order by操作

                 2.叶?结点直接对应数据 从中间级的索引?的索引?直接对应数据?

                 3.每张表只能创建?个聚集索引

  非聚集索引 1.索引顺序和物理顺序?关

                     2.叶?结点不直接指向数据?

                     3.每张表可以有多个非聚集索引,需要更多磁盘和内容 多个索引会影响insert和update的速度

五、语法

  

  #?法?:创建表时
    CREATE TABLE 表名 (
        字段名1 数据类型 [完整性约束条件…],
        字段名2 数据类型 [完整性约束条件…],
        [UNIQUE | FULLTEXT | SPATIAL ] INDEX | KEY
        [索引名] (字段名[(?度)] [ASC |DESC])
        );
  #?法?:CREATE在已存在的表上创建索引
      CREATE [UNIQUE | FULLTEXT | SPATIAL ] INDEX 索引名
      ON 表名 (字段名[(?度)] [ASC |DESC]) ;
  #?法三:ALTER TABLE在已存在的表上创建索引
      ALTER TABLE 表名 ADD [UNIQUE | FULLTEXT | SPATIAL ] INDEX
                 索引名 (字段名[(?度)] [ASC |DESC]) ;
 
  #删除索引:DROP INDEX 索引名 ON 表名字;
 
 
  #1. 准备表
    create table s1(
    id int,
    name varchar(20),
    gender char(6),
    email varchar(50)
    );

  #2. 创建存储过程,实现批量插入记录
    delimiter $$ #声明存储过程的结束符号为$$
    create procedure auto_insert1()
    BEGIN
        declare i int default 1;
        while(i<3000000)do
            insert into s1 values(i,‘eva‘,‘female‘,concat(‘eva‘,i,‘@oldboy‘));
            set i=i+1;
        end while;
    END$$ #$$结束
    delimiter ; #重新声明分号为结束符号

  #3. 查看存储过程
  show create procedure auto_insert1\G 

  #4. 调用存储过程
  call auto_insert1();
 

 

MySQL (索引)

上一篇:使用PowerDesigner做数据库设计(二)


下一篇:MySQL学习随笔03