6 索引

定义

是帮助MySQL高效获取数据的数据结构.
索引的本质是数据结构.
简单理解为:排好序的快速查找数据数据结构

我们平常所说的索引,如未特别指明,都是指B树(多路搜索树,并不一定是2叉树)结构组织的索引;其中聚合索引,符合索引,前缀索引和唯一索引都是默认使用B树索引;还有一种是hash索引

优势

a) 提高数据的检索效率
b) 降低数据的排序成本

劣势

a) 实际上索引也是一张表,该表保存了主键与索引字段,并指向实体表的记录,所以索引也要占用空间
b) 索引虽然提高了查询速度,但是却会降低更新表的速度,如对表进行INSERT,UPDATE和DELETE;因为更新表时,MySQL不仅要保存数据,还要保存一下索引文件每次更新添加索引列的字段

索引结构

a) B树索引
b) hash索引
c) full-text全文索引
d) R-Tree索引

适合建立索引的情况

a) 主键自动建立唯一索引
b) 频繁作为查询条件的字段应该创建索引
c) 查询中与其他表关联的字段,外键关系建立索引
d) 在高并发下倾向建立组合索引
e) 查询中要排序,分组或者统计的字段

不适合建立索引的情况

a) 频繁更新的字段不适合建立索引
b) 数据平均的字段,如性别

索引

四种索引(主键索引/唯一索引/全文索引/普通索引)

1 索引添加

# 1.1 主键索引添加

    当一张表，把某个列设为主键的时候，则该列就是主键索引
        create table aaa(
            id int unsigned primary key auto_increment ,
            name varchar(32) not null defaul ''
        );
    这是id 列就是主键索引.


    如果你创建表时，没有指定主键索引，也可以在创建表后，在添加, 指令:
        alter table 表名 add primary key (列名);

    举例: 
        create table bbb (id int , name varchar(32) not null default '');
        alter table bbb add primary key (id);

# 1.2 普通索引
    一般来说，普通索引的创建，是先创建表，然后在创建普通索引
    比如:
        create table ccc(
            id int unsigned,
            name varchar(32)
        );

        create index 索引名 on 表 (列1,列名2);



# 1.3 创建全文索引

    全文索引，主要是针对对文件，文本的检索, 比如文章, 全文索引针对MyISAM有用.

    创建 ：
        CREATE TABLE articles (
           id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
           title VARCHAR(200),
           body TEXT,
           FULLTEXT (title,body)
         )engine=myisam charset utf8;

        INSERT INTO 
            articles (title,body) 
        VALUES
            ('MySQL Tutorial','DBMS stands for DataBase ...'),
            ('How To Use MySQL Well','After you went through a ...'),
            ('Optimizing MySQL','In this tutorial we will show ...'),
            ('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
            ('MySQL vs. YourSQL','In the following database comparison ...'),
            ('MySQL Security','When configured properly, MySQL ...');

    如何使用全文索引:
        错误用法:
            select * from articles where body like '%mysql%'; 【不会使用到全文索引】
        证明:
            explain  select * from articles where body like '%mysql%'

    正确的用法是:
        select * from articles where match(title,body) against('database'); 【可以】

    说明:
        1.在mysql中fulltext 索引只针对 myisam生效
        2.mysql自己提供的fulltext针对英文生效->sphinx (coreseek) 技术处理中文
        3.使用方法是 match(字段名..) against(‘关键字’)
        4.全文索引一个 叫 停止词,  因为在一个文本中，创建索引是一个无穷大的数，
            因此，对一些常用词和字符，就不会创建，这些词，称为停止词.

# 1.4 唯一索引
    ① 当表的某列被指定为unique约束时，这列就是一个唯一索引
        create table ddd(id int primary key auto_increment , name varchar(32) unique);
        这时, name 列就是一个唯一索引.
        unique字段可以为NULL,并可以有多NULL, 但是如果是具体内容，则不能重复.
        主键字段，不能为NULL,也不能重复.

        create table tablename（
            name char（10），
            email char（20），
            key name（name),                 以name为普通索引，给name赋值时可以重复
            unique key email                 以email为唯一索引，给email赋值时不能重复
            (email（10）)                     表示以email前十个字符为索引
        ）;

    ② 在创建表后，再去创建唯一索引
        create table eee(id int primary key auto_increment, name varchar(32));
        create unique index 索引名  on 表名 (列名..);

# 1.5 复合索引
    create table tablename()
        xing  char（2），
        ming  char（3），
        key  xm（xing，ming）
    );
    查询时查‘xing’和查‘xing’ and ‘ming’ 都能达到查询的效果，都会用到索引，但查‘ming’则不能。

2.查询索引

        desc 表名 【该方法的缺点是：　不能够显示索引名.】
        show index(es) from 表名
        show keys from 表名

3.删除索引

alter table 表名 drop index 索引名; 

如果删除主键索引。
alter table 表名 drop primary key

4.修改索引

先删除，再重新创建.

如何查看索引使用的情况:

show status like 'handler_read%';

大家可以注意：

handler_read_key:这个值越高越好，越高表示使用索引查询到的次数。
handler_read_rnd_next:这个值越高，说明查询低效。