黑马程序员技术交流社区

标题: 【南京校区】mysql创建索引优劣分析 [打印本页]

---

作者: 大蓝鲸Java    时间: 2018-4-6 11:14
标题: 【南京校区】mysql创建索引优劣分析

mysql创建索引优劣分析

    索引的优点:
    1.减少服务器需要扫描的数据量
    2.避免服务器排序和临时表
    3.将随机I/O变成顺序I/O

    为什么添加索引后查询速度会提高?
    添加索引之后,mysql会通过BTREE算法创建索引文件,当查询数据时候,会找到索引文件进行遍历.


    索引添加在哪些列上比较好?
    1.比较频繁作为查询条件的字段应该创建索引
    2.唯一性太差字段不适合创建索引
    3.更新比较频繁的字段不适合做索引
    4.要考虑在where以及order by涉及列上创建索引

    例子:
    创建表
    create table test(
        id int not null,
        username varchar(16) not null
    )

    插入100000条数据
    DELIMITER #
    CREATE PROCEDURE pro1()
    BEGIN
     DECLARE i INT DEFAULT 0;
     WHILE i<100000 DO
      INSERT INTO test VALUES(i, CONCAT('aa', i));  
      SET i=i+1;
     END WHILE;
    END #
   
    CALL pro1();
   
    查询一条记录id=54321
    select * from test where id=54321;
    查看执行时间Total Time     : 00:00:00:063

    通过explain查看执行信息
    explain select * from test where id=54321;

    explain关键参数分析:
    type:连接访问类型,比较重要参数
         一般来说,至少range级别,最后能够达到ref级别

    rows: 估计值,mysql认为获取一个查询结果需要扫描的行数

    添加id索引:
    create index index_id on test(id);

    查看索引信息
    show index from test;

    查询一条记录id=54321
    select * from test where id=54321;
    查看执行时间Total Time     : 00:00:00:000

    通过explain查看执行信息
    explain select * from test where id=54321;


    普通索引:加速查询
    1.创建表时候指定索引
    create table test2(
        id int not null,
        username varchar(20) not null,
        index index_username (username(10))
    )

    2.使用create创建索引
    create index index_username on test2(username(10));

    3.使用alter添加索引
    alter table test2 add index index_username(username(10));

    4.删除索引
    drop index index_username on test2;



    唯一索引:加速查询,唯一约束(可以含有null)
    1.创建表时候指定索引
    create table test3(
        id int not null,
        username varchar(20) not null,
        unique index index_username (username(10))
    )

    2.使用create创建索引
    create unique index index_username on test3(username(10));

    3.使用alter添加索引
    alter table test3 add unique index index_username(username(10));

    4.删除索引
    drop index index_username on test3;


    复合(组合,多列)索引:
    应用场景:频繁的同事使用多个列进行查询,例子: where n1='aaa' and n2=888;
    1.创建表指定索引
    create table test4(
        name varchar(32) not null,
        age int not null,
        index index_name_age (name(10), age)
    )

    2.使用create创建索引
    create index index_name_age on test4(name(10), age);


    3.使用alter添加索引
    alter table test4 add index index_name_age(name(10), age);


    主键索引:加速查询,唯一约束(不可以含有null)
    1.创建表的时候指定
    create table test5(
        id int not null primary key auto_increment,
        username varchar(20)
    )   


    索引注意事项:
    索引的长度要合适.
    列子:
    create table test(
        n1 char(200),
        index index_n1 (n1(10))
    )

    索引的缺点:
    1.降低更新表的速度,因为更新表的时候,不仅要保存数据,还要保存索引文件
    2.创建索引会占用磁盘空间,一般情况下不会太严重,但是如果在一个大表上面创建多种组合索引,索引文件会膨胀很大.
   

---

欢迎光临 黑马程序员技术交流社区 (http://bbs.itheima.com/)

黑马程序员IT技术论坛 X3.2