mysql 索引

索引其实是一种数据结构

一、索引类型

可以使用SHOW INDEX FROM table_name;查看索引详情

1、主键索引 PRIMARY KEY

它是一种特殊的唯一索引，不允许有空值。一般是在建表的时候同时创建主键索引。

注意：一个表只能有一个主键

2、唯一索引 UNIQUE

唯一索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。

可以通过ALTER TABLE table_name ADD UNIQUE (column);创建唯一索引

可以通过ALTER TABLE table_name ADD UNIQUE (column1,column2);创建唯一组合索引

3、普通索引 INDEX

最基本的索引，它没有任何限制。

可以通过ALTER TABLE table_name ADD INDEX index_name (column);创建普通索引

4、组合索引 INDEX

组合索引，即一个索引包含多个列。多用于避免回表查询。

可以通过ALTER TABLE table_name ADD INDEX index_name(column1, column2, column3);创建组合索引

肯定有人要问了，如果分别在column1, column2, column3上建立单列索引，让该表有3个单列索引，查询时和上述的组合索引效率一样吧？嘿嘿，大不一样，远远低于我们的组合索引。

虽然此时有了三个索引，但MySQL查询优化器只会用到其中的那个它认为似乎是最有效率的单列索引（并非最左）。因为和全表扫描或只使用一个索引的速度比起来，去分析检索多个索引二叉树更加耗费时间。

而建立这样的组合索引，其实是相当于分别建立了

        column1, column2, column3

        column1, column2 

        column1

三个索引。这是因为mysql组合索引"最左前缀"的结果。where条件里字段左右顺序不会影响使用索引，即（column1='' and column2 ='')和（column2='' and column1 ='')走一样的索引，但是如果使用column2, column3两个字段作为查询条件就无法使用索引，column1和column3查询仅能使用column1单列索引。

通常创建联合索引时把where子句中使用最频繁的一列放在最左边。

5、全文索引 FULLTEXT

也称全文检索，是目前搜索引擎使用的一种关键技术。注意这只适合数据量很小的项目。
实际开发中，推荐使用第三方如 Sphinx（中文版coreseek）或者ElasticSearch

a、可以通过ALTER TABLE table_name ADD FULLTEXT (column);创建

alter table testtable add fulltext index testfulltext(clumn) with parser ngram;
ngram中文插件在5.7+版本支持。

b、全文索引搜索格式
SELECT * FROM testtable WHERE MATCH (clumn) AGAINST ('text' IN BOOLEAN MODE);
一般都用布尔全文搜索模式，以避免75%搜索不匹配问题。

text不能是单个汉字或字母，这种情况不走全文索引，无法查到结果。
text的格式——
'数据库 管理'     包含'数据库'或包含'管理'的数据
'"数据库 管理"'   不会因为空格而分隔，搜索包含'数据库 管理'的数据
'+数据库 +管理'   同时包含'数据库'和'管理'的数据
'+数据库 -管理'   包含'数据库'但没有'管理'的数据
'+数据库 管理'    包含'数据库'的数据，有'管理'的数据优先显示
'+数据库 >管理'   包含'数据库'的数据，有'管理'的数据优先显示
'+数据库 <管理'   包含'数据库'的数据，有'管理'的数据优先显示,但排位低于'>'的数据
以上三个最好不要混用

 

二、索引建立的基本原则

1、需要加索引的字段，要在where条件中 
2、数据量少的字段不需要加索引，比如性别只有两三个值就没必要创建索引；因为建索引有一定开销，如果数据量小则没必要建索引（速度反而慢） 
3、如果where条件中是OR关系，加索引不起作用 
4、联合索引比对每个列分别建索引更有优势，因为索引建立得越多就越占磁盘空间，在更新数据的时候速度会更慢。另外建立联合索引时，顺序也是需要注意的，应该将严格的索引放在前面，这样筛选的力度会更大，效率更高。

5、使用短索引，如果对长字符串列进行索引，应该指定一个前缀长度，这样能够节省大量索引空间；        create index personname on person (person(10)) ; person字段长度可能很大，而创建索引只使用前10个字符。

6、不要过度索引。索引需要额外的磁盘空间，并降低写操作的性能。在修改表内容的时候，索引会进行更新甚至重构，索引列越多，这个时间就会越长。所以只保持需要的索引有利于查询即可。