MYSQL索引

1.什么是索引？怎么样创建索引？

索引是对数据表中一列或多列的值进行排序的一种结构，使用索引可快速访问数据库表中的特定信息。

（1）直接创建索引

CREATE INDEX index_name ON table(column(length))

（2）修改表结构的方式添加索引

ALTER TABLE table_name ADD INDEX index_name ON (column(length))

（3）创建表的时候同时创建索引

CREATE TABLE `table` (
    `id` int(11) NOT NULL AUTO_INCREMENT ,
    `title` char(255) CHARACTER NOT NULL ,
    `content` text CHARACTER NULL ,
    `time` int(10) NULL DEFAULT NULL ,
    PRIMARY KEY (`id`),
    INDEX index_name (title(length))
)

（4）删除索引

DROP INDEX index_name ON table

2.索引的类型

普通索引：任意字段都可以创建的索引。

CREATE INDEX index_name ON table(column(length))

聚集索引：聚集索引是指数据库表行中数据的物理顺序与键值的逻辑（索引）顺序相同。一个表只能有一个聚集索引，因为一个表的物理顺序只有一种情况。聚集索引有着更快的检索速度。

CREATE CLUSTERED INDEX indexName ON table(column(length))

唯一索引：索引列的值必须唯一，允许索引的值为空，但是空值只能有一个，加速查询。

CREATE UNIQUE INDEX indexName ON table(column(length))

主键索引：在主键上创建的索引，是一种特殊的唯一索引，不允许有空值。一个表中只有一个主键索引，因为一个表只能有一个主键。当一张表,把某个列设为主键的时候,则该列就自动有了一个主键索引。

CREATE TABLE `table` (
    `id` int(11) NOT NULL AUTO_INCREMENT ,
    `title` char(255) NOT NULL ,
    PRIMARY KEY (`id`)
);

那么这个表就自动有了一个索引字段是“id”的主键索引。

组合索引：多列值组成一个索引。（复合索引遵循缀左原则）使用一个表的多个字段创建的索引，专门用于组合搜索，其效率大于多个索引值的合并。

只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用。使用组合索引时遵循最左前缀集合。

ALTER TABLE `table` ADD INDEX name_city_age (name,city,age); 

全文索引：对文本的内容进行分词，进行搜索。主要用来查找文本中的关键字，而不是直接与索引中的值相比较。

CREATE FULLTEXT INDEX index_content ON article(content)

3.全文索引和普通索引的区别

普通索引的结构主要以B+树和哈希索引为主，用于实现对字段中数据的精确查找，比如查找某个字段值等于给定值的记录，A=10这种查询，因此适合数值型字段和短文本字段。

全文索引是用于检索字段中是否包含或不包含指定的关键字，有点像搜索引擎的功能，其内部的索引结构采用的是与搜索引擎相同的倒排索引结构，其原理是对字段中的文本进行分词，然后为每一个出现的单词记录一个索引项，这个索引项中保存了所有出现过该单词的记录的信息，也就是说在索引中找到这个单词后，就知道哪些记录的字段中包含这个单词了。因此适合用大文本字段的查找。

4.索引有什么缺点？

优点：提高查询速度。但是使用也会带来一些缺点：

（1）虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行insert、update和delete。因为更新表时，不仅要保存数据，还要保存一下索引文件。

（2）建立索引会占用磁盘空间的索引文件。一般情况这个问题不太严重，但如果你在一个大表上创建了多种组合索引，索引文件的会增长很快。

所以并不是索引建的越多越好的。

 5.使用索引的注意事项

（1）使用短索引
对串列进行索引，如果可能应该指定一个前缀长度。例如，如果有一个char(255)的列，如果在前10个或20个字符内，多数值是惟一的，那么就不要对整个列进行索引。短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作。

（2）索引列排序
查询只使用一个索引，因此如果where子句中已经使用了索引的话，那么order by中的列是不会使用索引的。因此数据库默认排序可以符合要求的情况下不要使用排序操作；尽量不要包含多个列的排序，如果需要最好给这些列创建复合索引。

6.什么情况下加索引

（1）频繁查找的列加索引。

（2）主键加索引。

（3）外键加索引。

7.什么情况下不加索引

（1）查找不频繁的列就不要加索引了。

8.索引在什么情况下失效

（1）包含有null值的列
列中包含有null值，复合索引中只要有一列含有null值，那么这一列对于此复合索引就是无效的。所以我们在数据库设计时不要让字段的默认值为null。

（2）like语句操作
含有like操作的语句使用索引时like “%aaa%” 不会使用索引而like “aaa%”可以使用索引。

（3）包含运算的列
不要在列上进行运算，这将导致索引失效而进行全表扫描，例如：

SELECT * FROM table_name WHERE YEAR(column_name)<2017;

（4）包含not in和<>操作

这也会触发全表扫描。

（2）复合索引中使用了不符合缀左原则的查询

ALTER TABLE `table` ADD INDEX name_city_age (name,city,age) where city = ’nanjing’ and age = ‘24'; 

 上述语句就不符合缀左原则，进行查找时就不会使用索引。想要在进行查找时复合索引生效，where子句必须包含最左边的索引列。在这个例子中就是必须包含“name”属性列。

9.建立索引的原则

5、6、7、8条合起来。

10.索引的实现方式有哪几种

主要有两种：Hash和B+树，有什么区别？

Hash是基于Hash运算，精确等值查找的时间快，O(1)时间内就能完成。但是不适合范围查找。

B+树查找是基于B+树实现，查找的时间复杂度稳定，都是要查到叶子节点才能找到，时间复杂度log(N)，不仅适用于精确的等值查找，也适用于范围查找。

参考：（1）https://www.cnblogs.com/luyucheng/p/6289714.html