1、什么是MySQL索引？
- 官方上面说索引是帮助MySQL高效获取数据的数据结构，通俗点的说，数据库索引好比是一本书的目录，可以直接根据页码找到对应的内容，目的就是为了加快数据库的查询速度。
- 索引是对数据库表中一列或多列的值进行排序的一种结构，使用索引可快速访问数据库表中的特定信息。
- 一种能帮助mysql提高了查询效率的数据结构：索引数据结构。
1.1、索引原理
索引的存储原理大致可以概括为一句话：以空间换时间。
一般来说索引本身也很大，不可能全部存储在内存中，因此索引往往是存储在磁盘上的文件中的（可能存储在单独的索引文件中，也可能和数据一起存储在数据文件中）。
数据库在未添加索引进行查询的时候默认是进行全文搜索，也就是说有多少数据就进行多少次查询，然后找到相应的数据就把它们放到结果集中，直到全文扫描完毕。
1.2、索引的分类
MySQL索引分为普通索引、唯一索引、主键索引、组合索引、全文索引。索引不会包含有null值的列，索引项可以为null（唯一索引、组合索引等），但是只要列中有null值就不会被包含在索引中。
1.2.1、普通索引
普通索引是最基本的索引，它没有任何限制；
- 创建索引语法：
create index index_name on table(column)；
- 修改表结构方式添加索引：
ALTER TABLE table_name ADD INDEX index_name ON (column(length))
- 删除索引
DROP INDEX index_name ON table
1.2.2、唯一索引
唯一索引与前面的普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。
CREATE UNIQUE INDEX indexName ON table(column(length))
1.2.3、主键索引
主键索引是一种特殊的唯一索引，一个表只能有一个主键，不允许有空值。简单来说：主键索引是加速查询 + 列值唯一（不可以有null）+ 表中只有一个。
CREATE TABLE mytable( ID INT NOT NULL, username VARCHAR(16) NOT NULL, PRIMARY KEY(ID) );
1.2.4、组合索引
组合索引指在多个字段上创建的索引，只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用。使用组合索引时遵循最左前缀集合。
ALTER TABLE `table` ADD INDEX name_city_age (name,city,age);
1.2.5、全文索引
- 概念
全文索引主要用来查找文本中的关键字，而不是直接与索引中的值相比较。fulltext索引跟其它索引大不相同，它更像是一个搜索引擎，而不是简单的where语句的参数匹配。
fulltext索引配合match against操作使用，而不是一般的where语句加like。它可以在create table，alter table ，create index使用，不过目前只有char、varchar，text 列上可以创建全文索引。
值得一提的是，在数据量较大时候，现将数据放入一个没有全局索引的表中，然后再用CREATE index创建fulltext索引，要比先为一张表建立fulltext然后再将数据写入的速度快很多。
- 版本支持
Mysql 5.6之前版本，只有MyISAM支持全文索引，5.6之后，Innodb和MyISAM均支持全文索引。另外，只有字段的数据类型为 char、varchar、text 及其系列才可以建全文索引。
- 案例
创建数据表t_articles
CREATE TABLE t_articles ( id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, title VARCHAR(200), body TEXT, FULLTEXT (title,body) );
给现有的article表的title和body字段创建全文索引，索引名称为fulltext_article
ALTER TABLE t_articles ADD FULLTEXT INDEX fulltext_article (title, body);
导入测试数据
INSERT INTO t_articles VALUES (NULL,'MySQL Tutorial', 'DBMS stands for DataBase ...'), (NULL,'How To Use MySQL Efficiently', 'After you went through a ...'), (NULL,'Optimising MySQL','In this tutorial we will show ...'), (NULL,'1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'), (NULL,'MySQL vs. YourSQL', 'In the following database comparison ...'), (NULL,'MySQL Security', 'When configured properly, MySQL ...'); INSERT INTO t_articles VALUES (NULL,'abc', 'zs red blue ...'), (NULL,'xyz', 'ls white'); INSERT INTO t_articles VALUES (NULL,'aaa', 'zs red blue ...'), (NULL,'bbb', 'ls white red');
- 示例一：自然语言模式（IN NATURAL LANGUAGE MODE，默认模式）
SELECT * FROM t_articles where MATCH (title,body) AGAINST ('database')
1.3、索引的优缺点
优点：
- 大大提高数据查询速度。
- 可以提高数据检索的效率，降低数据库的IO成本，类似于书的目录。
- 通过索引列对数据进行排序，降低数据的排序成本降低了CPU的消耗。
- 被索引的列会自动进行排序，包括【单例索引】和【组合索引】，只是组合索引的排序需要复杂一些。
- 如果按照索引列的顺序进行排序，对order 不用语句来说，效率就会提高很多。
缺点：
- 索引会占据磁盘空间。
- 索引虽然会提高查询效率，但是会降低更新表的效率。比如每次对表进行增删改查操作，MySQL不仅要保存数据，还有保存或者更新对应的索引文件。
- 维护索引需要消耗数据库资源。
综合索引的优缺点：
数据库表中不是索引越多越好，而是仅为那些常用的搜索字段建立索引效果最佳!
2、何时不使用索引
- 表记录太少
- 经常增删改的表
- 数据重复且分布均匀的表字段，只应该为经常查询和最经常排序的数据列建立索引（如果某个数据类包含太多的重复数据，建立索引没有太大意义）
- 频繁更新的字段不适合创建索引(会增加IO负担)
- where条件里用不到的字段不创建索引
3、索引何时失效
- like以通配符%开头索引失效
- 当全表扫描比走索引查询的快的时候，会使用全表扫描，而不走索引
- 字符串不加单引号索引会失效
- where中索引列使用了函数（例如substring字符串截取函数）
- where中索引列有运算（用了< or > 右边的索引会失效，用<= or >= 索引不会失效）
- is null可以走索引，is not null无法使用索引（取决于某一列的具体情况）
- 复合索引没有用到左列字段（最左前缀法则，如果没用用到最左列索引，或中间跳过了某列有索引的列，索引会部分失效）
- 条件中有or，前面的列有索引，后面的列没有，索引会失效。想让索引生效，只能将or条件中的每个列都加上索引
