MySQL性能优化之索引详解

写在前面： mysql的性能优化其实大多都依赖于索引的优化，因此理解mysql的索引原理是优化的关键，在这之前，我们先来了解一下mysql的执行流程。

mysql执行流程：

SQL的执行过程；
1. 客户端发送一条查询给服务器；
2. 服务器通过权限检查之后,先会检查查询缓存，如果命中了缓存，则立即返回存储在缓存中的结果。否则进入下一阶段；
3. 服务器端进行SQL解析、预处理，再由优化器根据该SQL所涉及到的数据表的统计信息进行计算，生成对应的执行计划；
4. MySQL根据优化器生成的执行计划，调用存储引擎的API来执行查询；
5. 将结果返回给客户端。

SQL执行的最大瓶颈在于磁盘的IO，即数据的读取；不同SQL的写法，会造成不同的执行计划的执行，而不同的执行计划在IO的上面临完全不一样的数量级，从而造成性能的差距；
所以,我们说,优化SQL,其实就是让查询优化器根据程序猿的计划选择匹配的执行计划,来减少查询中产生的IO;

1.为什么要使用索引？

当我们在数据库中检索一条数据的时候，我们首先想到的是全表一条一条的查找，进行全表扫描，当找到的时候返回给客户端，但是在数据库中数据十分大的时候，这就不得不考虑效率的问题了，怎样提高效率呢？这时就引出了索引，什么是索引呢？索引能干什么呢？说白了就是将无序的数据通过建立数据结构来变成有序的数据方便查找，当这些记录变成有序的数据时候，这时就大大的提高了查找效率，假设我们的一张表中有100万条记录，在最坏的情况下，不使用索引，需要进行全表扫描 ，对这100万条数据全部查询，那么就需要10^6个时间单位，当我们使用索引对数据进行有序化，如果使用二分查找，需要log10^6个时间单位，这就比之前的全表扫描好多了，如果使用B-树索引结构，那效率就很高了。

2.索引原理

索引是如何创建的呢？我们有一张表如下：

如果我们要按照用户名来查找数据：

SELECT * FROM t_uer WHERE account_id=6;

如果没有建立索引，mysql会进行全表扫描,比较account_id是否为6，如果为6，则将结果放入内存中的结果集中，这种情况IO操作频繁。

如果给account_id这一列创建一个索引，将account_id进行排序，把排序的结果变为一个倒排表：

在查询的时候，找到索引account_id为6的那条倒排表中的数据，顺序得到4,14对应的数据，将结果放入结果集中。
在修改数据时候：
1）当增加一条数据（account_id为8的数据，id为19）
2）当删除一条数据的时候，查询中倒排表中account_id为8的数据，从倒排表中删除。
3）当更新一条数据的时候，重复步骤2，1即可。

3.索引的结构

1）索引的物理结构：
我们找到没有mysql目录下的my.ini配置文件。找到dataDir属性对应的目录，进入该目录，每一个数据库对应一个文件夹：
1,MYISAM引擎:每一个表(table_name)–>
table_name.MYI:存放的是数据表对应的索引信息和索引内容;
table_name.FRM:存放的是数据表的结构信息;
table_name.MYD:存放的是数据表的内容;
2,InnoDB引擎:每一个表(table_name)–>
table_name.frm:存放的是数据表的结构信息;
数据文件和索引文件都是统一存放在ibdata文件中;
3,索引文件都是额外存在的,对索引的查询和维护都是需要消耗IO的;

索引的结构：
1,默认情况下,一旦创建了一个表,这个表设置了主键,那么MYSQL会自动的为这个主键创建一个unique的索引;
2,索引类型:
1）Normal:普通的索引;允许一个索引值后面关联多个行值;
2）UNIQUE:唯一索引;允许一个索引值后面只能有一个行值;之前对列添加唯一约束其实就是为这列添加了一个unique索引;当我们为一个表添加一个主键的时候,其实就是为这个表主键列(设置了非空约束),并为主键列添加了一个唯一索引;
3）Fulltext:全文检索,mysql的全文检索只能用myisam引擎,并且性能较低,不建议使用;
3,索引的方法(规定索引的存储结构):
1）b-tree:是一颗树(二叉树,平衡二叉树,平衡树(B-TREE)) 使用平衡树实现索引，是mysql中使用最多的索引类型；在innodb中，存在两种索引类型，第一种是主键索引（primary key），在索引内容中直接保存数据的地址；第二种是其他索引，在索引内容中保存的是指向主键索引的引用；所以在使用innodb的时候，要尽量的使用主键索引，速度非常快； b-tree中保存的数据都是按照一定顺序保存的数据,是可以允许在范围之内进行查询;
select * from accountflow where account_id <100;
2）hash:把索引的值做hash运算，并存放到hash表中，使用较少，一般是memory引擎使用；优点:因为使用hash表存储，按照常理，hash的性能比B-TREE效率高很多。 hash索引的缺点：
1，hash索引只能适用于精确的值比较，=，in，或者<>；无法使用范围查询;
2，无法使用索引排序；
3，组合hash索引无法使用部分索引；
4，如果大量索引hash值相同，性能较低；

4.怎么创建索引

1），较频繁的作为查询条件的字段应该创建索引；
2），唯一性太差的字段不适合单独创建索引，即使频繁作为查询条件；作为索引的列,如果不能有效的区分数据,那么这个列就不适合作为索引列;比如(性别,状态不多的状态列)
3），更新非常频繁的字段不适合创建索引；原因,索引有维护成本;
4），不会出现在WHERE 子句中的字段不该创建索引；
5）, 索引不是越多越好;(只为必要的列创建索引)

• 不管你有多少个索引,一次查询至多采用一个索引;(索引和索引之间是独立的)
• 因为索引和索引之间是独立的,所以说每一个索引都应该是单独维护的;数据的增/改/删,会导致所有的索引都要单独维护;

5.索引的使用限制

1）,BLOB 和TEXT 类型的列只能创建前缀索引
2）,MySQL 目前不支持函数索引(在MYSQL中,索引只能是一个列的原始值,不能把列通过计算的值作为索引);
3）,使用不等于（!= 或者<>）的时候MySQL 无法使用索引
4）,过滤字段使用了函数运算后（如abs(column)），MySQL 无法使用索引
5）, Join 语句中Join 条件字段类型不一致的时候MySQL 无法使用索引
6）,使用LIKE 操作的时候如果条件以通配符开始（ ‘%abc…’）MySQL 无法使用索引
7）,字符串是可以用来作为索引的;
8),字符串创建的索引按照字母顺序排序;
9),如果使用LIKE,实例:SELECT * FROM userinfo WHERE realName LIKE ‘吴%’;这种情况是可以使用索引的;但是LIKE ‘_嘉’ 或者LIKE ‘%嘉’都是不能使用索引的;
10),使用非等值查询的时候MySQL 无法使用Hash 索引

6.索引的类型

单列索引、复合索引、聚簇索引与非聚簇索引:
1. 因为一个查询一次至多只能使用一个索引,所以,如果都使用单值索引(一个列一个索引),在数据量较大的情况下,不能很好的区分数据;
2. 所以,MYSQL引入了多值索引(复合索引);复合索引就是由多列的值组成的索引;并且(注意),多列的索引是有顺序的。
3. 复合索引的原理:就是类似orderby(orderby后面可以跟多个排序条件order by hire_date,username desc);就是在排序和分组(创建倒排表的时候),按照多个列进行排序和合并;

 SELECT * FROM accountflow WHERE actionTime < 'xxxxx' AND account_id = 5 可以使用actionTime+account_id的复合索引;
 SELECT * FROM accountflow WHERE actionTime < 'xxxxx' 可以使用actionTime+account_id的复合索引;
SELECT * FROM accountflow WHERE account_id = 5 不可以使用actionTime+account_id的复合索引;
SELECT * FROM accountflow WHERE account_id = 5 AND actionTime < 'xxxxx' 不可以使用actionTime+account_id的复合索引;

4. 复合索引,在查询的时候,遵守向左原则;只要在查询的时候,是按照复合索引从左到右的顺序依次查询,不管查询条件是否完全满足所有的符合索引的列,都可以使用部分的符合索引;
5. 聚簇索引：聚簇索引的顺序就是数据的物理存储顺序。通俗的说就是索引与数据存放在一起。
6. 非聚簇索引：索引顺序与数据物理排列顺序无关。索引文件与数据文件分开存放。
7. 在实际应用中,基本上都使用复合索引;

上面是关于索引类型的简单介绍，关于聚簇索引与非聚簇索引以及索引优化更加详细的内容可以参照这篇文章：聚簇索引(Clustered Index)和非聚簇索引 (Non- Clustered Index)