mysql 索引

存储引擎是MyISAM的，创建一个表后会对应三个文件分别是：*.frm表结构，*.myd表数据， *.myi这个是索引。

 

索引的类型

    1、主键索引，主键自动的为主索引 (类型Primary key)
         PS：不能为NULL，且不能重复。 

    2、普通索引 (INDEX)

    3、唯一索引 (UNIQUE)
         PS：可以为NULL，并且可以有多个NULL。但如果是具体内容，则不能重复。 

    4、全文索引 (FULLTEXT)（只有MyISAM存储引擎支持）     sphinx + 中文分词    coreseek
         PS：主要针对 文件、文本的检索，比如文章，全文索引针对MyISAM存储引擎才能使用
         用法：select * from `表名` where match(索引字段1,索引字段2) against(‘查询内容’);

        说明：

        1.  在mysql中fulltext 索引只针对 myisam生效

        2.  mysql自己提供的fulltext针对英文生效->sphinx(coreseek) 技术处理中文

        3.  使用方法是 match(字段名..) against(‘关键字’)

        4.  全文索引一个 叫 停止词,  因为在一个文本中，创建索引是一个无穷大的数，

        因此，对一些常用词和字符，就不会创建，这些词，称为停止词.

    5、 综合使用=>复合索引

 

索引的操作

    1、主键索引：PRIMARY KEY
         创建：ALTER TABLE `表名` ADD PRIMARY KEY(`字段`);    //修改表的方式创建索引
         删除：ALTER TABLE `表名` DROP PRIMARY KEY;
    2、普通索引：INDEX  
        创建：CREATE INDEX  索引名 ON `表名`(`索引字段`);
        修改：ALTER TABLE  `表名` ADD INDEX  索引名(`索引字段`);
        删除：DROP INDEX  索引名 ON `表名`;

                 ALTER TABLE `表名` DROP INDEX 索引名;

    3、唯一索引： UNIQUE  
        创建：CREATE UNIQUE INDEX  索引名  ON `表名`(`索引字段`);
        修改：ALTER TABLE  `表名` ADD UNIQUE  索引名(`索引字段`);
        删除：DROP INDEX  索引名 ON `表名`;
                ALTER TABLE `表名` DROP INDEX  索引名; 
    4、全文索引：FULLTEXT 
        创建：CREATE FULLTEXT INDEX  索引名  ON `表名`(`索引字段`);

        修改：ALTER TABLE  `表名` ADD FULLTEXT 索引名(`索引字段`);
        删除：DROP INDEX  索引名 ON `表名`;
                ALTER TABLE `表名` DROP INDEX  索引名; 

======================================================================

索引的方法（一般同建索引的时候使用）
    1、哈希： HASH

        创建：
            a) 建表创建：CREATE TABLE `表名` ( 索引类型 `索引名称` USING HASH (`字段名`));

    2、BTREE（默认）
        创建：

            a) 建表创建：CREATE TABLE `表名` ( 索引类型 `索引名称` USING BTREE (`字段名`)); 

======================================================================

索引的显示

    show index(es) from `表名`;
    show keys from `表名`;

    show create table `表名`; //主要查看创建表结构

    desc `表名`;    //主要查看表结构

======================================================================

索引的带价

    1、占用磁盘空间。
    2、对dml（添、删、改）操作有影响。 

======================================================================

哪些列上适合添加索引

    1、较频繁的作为查询条件字段应该创建索引
        select * from emp where `empno` = 1;
    2、唯一性太差的字段不适合单独创建索引，即使频繁作为查询条件
        select * from empwhere sex = '男'
    3、更新非常频繁的字段不适合创建索引
        select * from `emp` where `logincount` = 1;

    4、不会出现在WHERE子句中字段不该创建索引

======================================================================

索引的使用

查询要使用索引最重要的条件是：查询条件中需要使用索引。

    1、下列几种情况下有可能使用到索引：
        1> 对于创建的多列索引，只要查询条件使用了最左边的列，索引一般就会被使用。
        2> 对于使用like的查询，查询如果是  ‘%aaa’ 或 '_aaa%'不会使用到索引，‘aaa%’ 会使用到索引。

    2、下列的表将不使用索引：
        1> 如果条件中有or，即使其中有条件带索引也不会使用。
        2> 对于多列索引，不是使用的第一部分，则不会使用索引。
        3> like查询是以%开头
        4> 如果列类型是字符串，那一定要在条件中将数据使用引号引用起来。否则不使用索引。

        5> 如果mysql估计使用全表扫描要比使用索引快，则不使用索引。 

======================================================================

索引的使用情况

show status like 'Handler_read%';
1、Handler_read_key //改值越高越好，代表使用的索引越多
2、Handler_read_rnd_next //该值越低越好