Mysql—索引原理与详解

索引的原理

索引的优点和缺点和使用原则

索引优点：

• 可以加快数据的检索速度，提高查询速度。
• 所有的MySql列类型(字段类型)都可以被索引，也就是可以给任意字段建立索引。
• 全文检索字段进行搜索优化。

索引缺点：

•  创建索引和维护索引要耗费时间，并且随着数据量的增加所耗费的时间也会增加。
• 当对表中的数据进行增加、删除、修改的时候，索引也需要动态维护，降低了数据的维护速度。
• 索引也需要占用物理存储空间（数据库目录：/var/lib/mysql）。
• 我们知道数据表中的数据也会有最大上线设置的，如果有大量的索引，索引文件可能会比数据文件更快达到上线值。

使用原则：

• 索引不是越多越好，并不是每个字段都设置索引就好，而是需要自己合理的使用。
• 对经常更新的表就避免对其进行过多的索引，对经常用于查询的字段应该创建索引，不要对经常变动的数据加索引。
• 数据量小的表最好不要使用索引，因为数据较少，可能查询全部数据花费的时间比遍历索引的时间还要短，索引就可能不会产生优化效果。
• 在一同值少的列上(字段上)不要建立索引，比如在学生表的"性别"字段上只有男，女两个不同值。相反的，在一个字段上不同值较多可以建立索引。

什么时候添加索引：

• 在 where、order by 子句中经常使用的字段。
• 字段的值是多个（例如性别字段则不适合）。
• 字段内容不是经常变化的，经常变化的字段，添加索引反而降低性能。
• 不宜过多添加索引，每添加一条索引都会占用磁盘空间。

索引的分类

• 普通索引：加速查找
• 唯一索引：加速查找+约束 （唯一）
• 主键索引：加速查找+约束（不为空且唯一）
• 外键索引：  
• 组合索引：primary key(id,name):联合主键索引，unique(id,name):联合唯一索引，index(id,name):联合普通索引
• 全文索引：用于搜索很长一篇文章的时候，效果最好。（full text）
• 空间索引：了解就好，几乎不用。（spatial）

普通索引（index）

使用规则
1.一个表中可以有多个index字段
2.字段的值可以有重复，也可以为null值。字段值无约束
3.经常把做查询条件的字段设置为index字段
4.index字段的key标志为：mul

创建普通索引（有三种方法）
方法1：创建表时创建索引index
create table student(
    id int,
    name varchar(25),
    score float(5,2),
    index(name),              # 创建name索引
    index(score)              # 创建score索引
);
方法2：创建表后创建索引index：索引名一般和字段名一样，只要自己能认出就行，可以随便起名。
mysql> create index 索引名 on 表名(字段名)
mysql> create index name on student(name); 
方法3：创建表后创建索引index
mysql> alter table 表名 add index 索引名(字段名);
mysql> alter table student add index(name);
mysql> alter table student add index name6(name);

查看普通索引：key一列就是索引的列，我们会发现在name和score有值MUL。
mysql> desc 表名;  
mysql> desc student;                          # Key标志为：MUL
mysql> show index from 表名;                   # Key_name值为索引名
mysql> show index from studentG;             # 如果字段名过多，就添加一个G。

删除普通索引：删除普通索引只能一个一个删除
mysql> drop index 索引名 on 表名;
mysql> drop index name6 on student;

唯一索引（unique）

使用规则
1.一个表中可以有多个unique字段
2.unique字段的值不允许重复，可以为空值null
3.unique的key标志是UNI

创建唯一索引(基本等同index创建)
方法1：创建表时创建索引
create table student(
    id int,
    name varchar(25),
    score float(5,2), 
    unique(name)，
    unique(score)
);   
方法2：创建表后创建索引：索引名一般和字段名一样，只要自己能认出就行，可以随便起名。
mysql> create unique index 索引名 on 表名(字段名);
方法3：创建表后创建索引
mysql> alter table student add unique(name);
mysql> alter table student add unique name6(name);
mysql> alter table student add unique index(name);
mysql> alter table student add unique index name6(name);

查看唯一索引
mysql> desc 表名; 
mysql> show index from 表名;

删除唯一索引
mysql> drop index 索引名 on 表名;

主键索引（primary key）

外键索引（foreign key）

全文索引（fulltext）

使用须知
1.在MySQL5.6以下，只有MyISAM引擎表支持全文检索。在MySQL5.6以上Innodb引擎表也提供支持全文检索。
2.在MySQL5.6以下，目前只支持英文字符的全文搜索，不支持中文全文索引，原因很简单：与英文不同，中文的文字是连着一起写的，中间没有MySQL能找到分词的地方。
3.相应字段建立FULLTEXT索引，只能在CHAR、VARCHAR、TEXT类型字段上使用全文索引。
4.预设搜寻是不分大小写，若要分大小写，columne 的 character set要从utf8改成utf8_bin。
5.MATCH(title, content)里的字段必须和FULLTEXT(title, content)里的字段一模一样。
6.全文索引，就是在一堆文字中，通过其中的某个关键字等，就能找到该字段所属的记录行。

创建全文索引
方法1：创建表时创建全文索引
create table article (      
    id int auto_increment not null primary key,   
    title varchar(200),
    body text,
    fulltext(title, body)
) engine=myisam;
方法2：创建表后创建全文索引
mysql> alter table 表名称 add fulltext index 索引名称  (字段名1,字段名2)
mysql> alter table article add fulltext index(body);
mysql> alter table article add fulltext index(title, body);
方法3：创建表后创建全文索引
mysql> create fulltext index 索引名称 on 表名称(字段名1,字段名2)

使用全文索引
mysql> select * from 表名称 WHERE MATCH(字段名) AGAINST('条件字符串1*')
mysql> select * from 表名称 WHERE MATCH(字段名) AGAINST('条件字符串1' IN BOOLEAN MODE)
mysql> select * from article where match(title) against('中华日报') ;
mysql> select * from article where match(title,body) against('杜月笙');

删除全文索引
mysql> drop index 索引名称 on 表名称;

重建索引：在数据库运行了较长时间后，索引都有损坏的可能
mysql> repair table 表名称 quick;

全文索引变量值查询
mysql> show variables like 'ft%';

索引常见命令

1、查看表的索引

mysql> show index from table_name（表名）;
mysql> show index from user;
mysql> show index from userG;

2、删除表的索引