• mysql索引总结


    一、分类

      1、主键索引

        设定为主键后数据库会自动建立索引,innodb为聚簇索引

        特点:

           不能重复,  id 不能是null

        语法:   

    #随表一起建索引:
    #使用AUTO_INCREMENT关键字的列必须有索引(只要有索引就行)
    CREATE TABLE customer (id INT(10) UNSIGNED  AUTO_INCREMENT ,customer_no VARCHAR(200),customer_name VARCHAR(200),PRIMARY KEY(id));
    
    #单独建主键索引:
    ALTER TABLE customer add PRIMARY KEY customer(customer_no);
    
    #删除建主键索引:
    ALTER TABLE customer drop PRIMARY KEY ; 
    
    #修改建主键索引:
    #必须先删除掉(drop)原索引,再新建(add)索引

      2、单值索引

        即一个索引只包含单个列,一个表可以有多个单列索引

       特点:

          单列,一个表可以有多个单值索引

       语法:

    #随表一起建索引:
    CREATE TABLE customer (id INT(10) UNSIGNED  AUTO_INCREMENT ,customer_no VARCHAR(200),customer_name VARCHAR(200),
      PRIMARY KEY(id),
      KEY `index_customer_name` (`customer_name`)  
    );
    
    #单独建单值索引:
    CREATE INDEX idx_customer_name ON customer(customer_name);
    
    #删除索引:
    DROP INDEX idx_customer_name on customer;  

      3、唯一索引

      索引列的值必须唯一,但允许有空值

      特点:

        不能重复,比如id 可以是null

      语法:

    #随表一起建索引:
    CREATE TABLE customer (id INT(10) UNSIGNED  AUTO_INCREMENT ,customer_no VARCHAR(200),customer_name VARCHAR(200),
      PRIMARY KEY(id),
      KEY `index_customer_name` (`customer_name`),
      UNIQUE `index_custommer_no` (`customer_no`)
    );
    #单独建唯一索引:
    CREATE UNIQUE INDEX idx_customer_no ON customer(customer_no);
    #删除索引:
    DROP INDEX idx_customer_no on customer ;

      4、复合索引

        即一个索引包含多个列

        在数据库操作期间,复合索引比单值索引所需要的开销更小(对于相同的多个列建索引)

        当表的行数远大于索引列的数目时可以使用复合索引

        特点:

          多个列构成的索引(相当于二级目录)

        语法:

    #随表一起建索引:
    CREATE TABLE customer (id INT(10) UNSIGNED  AUTO_INCREMENT ,customer_no VARCHAR(200),customer_name VARCHAR(200),
      PRIMARY KEY(id),
      KEY  `index_customer_name`(`customer_name`),
      UNIQUE `customer_name`(`customer_name`),
      KEY `index_customer_no_customer_name`(customer_no,customer_name)
    );
    #单独建索引:
    CREATE INDEX idx_no_name ON customer(customer_no,customer_name); 
    #删除索引:
    DROP INDEX idx_no_name  on customer;

       5、查询索引

    show index from 表名;

      

     二、需要创建索引的情况总结

      1、主键自动建立唯一索引

      2、频繁作为查询条件的字段应该创建索引(where 后面的语句)

      3、查询中与其它表关联的字段,外键关系建立索引

      4、单键/组合索引的选择问题,who?(在高并发下倾向创建组合索引)

      5、查询中排序的字段,排序字段若通过索引去访问将大大提高排序速度

      6、查询中统计或者分组字段

    三、不需要创建索引的情况总结

      1、表记录太少

      2、经常增删改的表:提高了查询速度,同时却会降低更新表的速度,如对表进行INSERT、UPDATE和DELETE。 因为更新表时,MySQL不仅要保存数据,还要保存一下索引文件

      3、Where条件里用不到的字段不创建索引

      4、数据重复且分布平均的表字段,因此应该只为最经常查询和最经常排序的数据列建立索引,某个数据列包含许多重复的内容,建立索引没有太大实际效果

    四、小结

      分类:

    主键索引: 不能重复 id 不能是null
    单值索引: 单列,一个表可以有多个单值索引;
    唯一索引: 不能重复,比如id 可以是null
    复合索引:    多个列构成的索引(相当于二级目录)

      创建索引

    方式一:
        create 索引类型  索引名 on 表(字段)
                
    单值索引:    create index dept_index on tb(dept);  
    唯一索引:     create UNIQUE index name_index on tb(name);  
    复合索引:    create index dept_name_index on tb(dept,name);
                
                
    方式二:
        alter table 表名 add 索引类型  索引名 (字段)
                
    单值索引:    alter table tb add index dept_index (dept);  
    唯一索引:     alter table tb add UNIQUE index name_index (name);    
    复合索引:    alter table tb  add index dept_name_index (dept,name);
    
    注意:如果一个字段是primary key ,则该字段默认是主键索引

      删除索引

    drop index 索引名 on 表名;
    drop index name_index on tb;

      查询索引

    show index from 表名;

       索引的优势

    1、提高查询效率(降低IO使用率)
    2、降低CPU使用率(...order by desc,因为B树索引本身是已经排序好的)

      索引的弊端

    1、索引本身很大,可以存放在内存/硬盘(通常为硬盘)
    2、索引不是所有情况均适用:a、少量数据   b、频繁更新的字段
    3、索引会降低增删改的效率
    知道、想到、做到、得到
  • 相关阅读:
    [Java] 使用@SelectProvider注解实现多表关联查询(全注解,不使用不配置xml)
    c#winform线程间操作UI的五种方法
    C#调用Excel,拷贝图表到其他Excel文档中
    VisualSvn破解、VS2017以上版本的VisualSvn破解
    汉字数据库,汉字大全,JSON格式汉字数据,收录16159个汉字
    golang Logrus简易使用教程
    excel操作-基础篇
    02-PyQt5程序基本结构分析
    QObject信号的操作
    基于webGL三维停车场,可视化管理Demo
  • 原文地址:https://www.cnblogs.com/Durant0420/p/15309657.html
Copyright © 2020-2023  润新知