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 student\G; # 如果字段名过多，就添加一个\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 user\G;
2、删除表的索引
