mysql 索引

一、简介  

索引的作用： 加速查找和约束

二、索引的类型

1、普通索引
    a)、作用
        加速查找
    b)、创建
        1)、创建表
            create table test1(
            id int auto_increment primary key,
            name char(32),
            age int,
            addr text,
            index i_name(name) 
            )engine=innodb default charset=utf8;
        2)、添加
            create index i_age on test1(age);
            alter table test1 add index i_name(name);   --本人推荐
    c)、删除
        drop index i_name on test1;
        alter table test1 drop index i_age; --本人推荐
2、主键索引
    a)、作用
        加速查找，唯一且不为空
    b)、创建
        1)、创建表
            create table test2(
            id int,
            name char(10),
            age int,
            score decimal,
            primary key(id)
            )engine=innodb default charset=utf8;
            注意：想要删除和添加主键，主键不能设置为自增
        2)、添加
            alter table test2 add primary key(age);
    c)、删除
         alter table test2 drop primary key;
3、唯一索引
    a)、作用
        加速查找，唯一且可以为空
    b)、创建
        1)、创建表
            create table test3(
            id int auto_increment primary key,
            name char(32),
            age int,
            id_card int,
            unique uq_id_card(id_card)
            )engine=innodb default charset=utf8;
        2)、添加
            alter table test3 add unique index uq_name(name);   --本人主推
            create unique index uq_age on test3(age);
    c)、删除
        alter table test drop index uq_id_card; --本人主推
        drop index abd on test3;
4、组合索引
    a)、类型
        1)、组合普通索引
            a、创建
                1、创建表
                    create table test4(
                    id int auto_increment primary key,
                    name char(10),
                    age int,
                    index n_a(name,age)
                    )engine=innodb default charset=utf8;
                2、添加
                    alter table test4 add unique index a_n(name, age);
                    create unique index nm on test4(age,name);
            b、删除
                 alter table test4 drop index n_a;
                 show create table test4 G;
        2)、组合主键
            a、创建
                1、创建表
                    create table test5(
                    id int,
                    name char(10),
                    age int,
                    primary key(id, name)
                    )engine=innodb default charset=utf8;
                2、添加
                    alter table test5 add primary key(name, age);
            b、删除
                alter table test5 drop primary key;
            注意：表中主键唯一，但主键可以是多列
        3)、联合唯一索引
            a、创建
                1、创建表
                    create table test6(
                    id int auto_increment primary key,
                    name char(32),
                    age int,
                    addr text,
                    unique uq_a_n(age, name)
                    )engine=innodb default charset=utf8;  
                2、添加
                    alter table test6 add unique index n_a(name, age);
                    drop index n_a on test6;
            b、删除
                alter table test6 drop index uq_a_n;
                drop index n_a on test6;
    b)、特点
        最左前缀匹配
5、名词
    a)、覆盖索引
        1、含义
            查询的内容是索引
        2、示例
            select id from test6 where id = 3;
    b)、索引组合
        1、含义
            不同的索引column进行组合
            注意：不同于组合索引，组合索引的效率>索引组合
        2、示例
            select id,name from test6 where id =3 and name= 'tom';
注意：如果是text，创建索引要指明长度　　

alter table test6 add index i_addr(addr(10));

三、索引的种类

1、hash索引
    特点：单个快，范围慢
2、btree索引
    特点：二叉树
    注意：innodb是btree索引
3、索引的实质
    创建索引时，创建一个新的数据存储文件，查找索引时，先到数据存储文件中查找
4、索引的优点/缺点
    a)、优点
        查找速度快
    b)、缺点
        增、删、改速度慢
        原因：要操作额外的索引存储文件

四、非命中索引的情况

1、like
    值不确定
2、函数
    使用函数后，值会发生改变
3、数据类型不同
    值需要进行数据类型转换
4、or
    前面的是索引，若后面的不是索引，则是没有命中索引
5、!=
    没有确切的值，索引失去了意义
    注意：主键除外
6、>
    范围，没有确切的值
    注意：注意和数字column除外
7、order by
    若映射不是索引，那么不走索引
8、组合索引(最左前缀匹配)
    (name, age)
        name 走索引
        name age 走索引
        age 不走索引

 五、优化操作   

1、避免映射是:*
2、避免数据类型不正确
3、使用count(1)或count(column)
4、join比select效率高