MySQL--索引

索引概述

索引本质上是表字段的有序子集，它是提高查询速度最有效的方法。而索引即目录。
MyISAM引擎是将数据行放在数据文件里，索引值存放在索引文件里。

InnoDB引擎它是索引也是排好序的数组，但它的数据行与索引值存放在同一个文件里。

索引种类

一般有四种索引：
1、普通索引（Index），用于提示查询效率；
2、唯一索引（Unique），除了提升查询效率还要求字段值不得重复；
3、主键索引（Primary Key），唯一性且不得为空的索引；
4、全文索引（Fulltext），用于在大量文章搜索中建立的索引。

PS：这里主键索引和唯一索引的区别在于：
主键索引不能为空值，唯一索引允许控制；
主键索引在一张表内只能创建一个，唯一索引可以创建多个；
主键索引肯定是唯一索引，但唯一索引不一定是主键索引。

相比单表查询，如果没有建立索引，最多就是全表扫描一遍。但对于多表查询时，索引的重要度就要大很多，比如三张表的联合查询时，如果没有建立索引，将会是：

//假设每张表有1000条数据，那么需要扫描1000*1000*1000=10亿次。
WHERE t1.c1=t2.c2 AND t1.c1=t3.c3;

如果给c2，c3分别建立了索引，那么只扫描t1一个全表即可。

索引在 提高效率和速度的同时也增加了某些负担：
1、索引需要使用更多的磁盘空间，索引越多，占用磁盘越多，有时候甚至比表占用的多；
2、索引在提升查询速度的同时，降低了插入、更细和删除的操作速度，因为它们多了一项工作，就是在增删改的过程中更新索引。索引越多，速度越慢；

所以，索引优化很重要，需要根据业务、环境、配置等一系列问题进行最优设计。

索引操作

1、创建主键索引
该索引较简单，我们一直在使用，只要创建一个无符号整型且自动增长的列，然后设置成主键即可

//通过EXPLAIN语句查看索引状态
EXPLAIN SELECT * FROM think_user WHERE id =1;

PS：索引的查询使用也很简单，比如用主键索引：

EXPLAIN SELECT * FROM think_user WHERE id=1;
//此时就会显示使用了主键索引。

2、创建普通或唯一索引
直接进入navicat设计表的第二栏，选择一个字段（比如user字段），添加一个Normal（普通索引）或Unique（唯一索引）。

//通过Explain语句查看索引状态
EXPLAIN SELECT * FROM think_user WHERE user='张三';

//查看表所有索引状态
SHOW INDEX FORM think_user；

PS：在添加主键、唯一、普通索引的时候，我们会需要选择一种数据结构，默认为B-Tree（二叉树）结构，还有一种为HASH（哈希）结构。从理论上看，HASH索引的效率比B-Tree高，但常用的还是B-Tree，因为两种在各自查询的方式和范围有所不同。

    1、Hash索引仅仅能满足“=”，“IN”，“<=>”查询，不能使用范围查询。
    2、Hash索引无法被用来避免数据的排序操作；
    3、Hash索引不能利用部分索引键查询；
    4、Hash索引在任何时候都不能避免表扫描；
    5、Hash索引遇到大量Hash值相等的情况后性能并不一定就会比B-Tree索引高。索引，要使用HASH，就必须结合具体业务和大量经验。

3、全文索引
该索引一般来说对于我们用处不大，属于在大文章里进行对某些关键字进行索引，但由于中文分词识别问题，基本无法使用，如果是英文状态的话，建立全文索引后，可以依次SQL查询。

//SELECT * FROM think_user WHERE MATCH (intro) AGAINST ('teacher');

PS:如果搜索is，则无效，因为这是最常见的却无意义的词，被MySQL设置成停止词。

索引原则

1、不要过度索引，索引越多，占用空间越大，反而性能越慢；
2、只对WHERE子句中频繁使用的建立索引；
3、尽可能使用唯一索引，重复值越少，索引效果越强；
4、使用短索引，如果char（255）太大，应该给它指定一个前缀长度，大部分情况下前10位或20位的值基本是唯一的，那么久不要对整改列进行索引；
5、充分利用左前缀，这是针对复合索引，因为WHERE语句如果有AND并列，只能识别一个索引（获取记录最少的那个），索引需要使用复合索引，那么应该将WHERE最平凡的设置在左边。