mysql优化探索（4）在mysql中正确的创建索引1（sql优化）

概述：在mysql中一般有索引是在存储引擎层实现的，也就是说选择不同存储引擎会支持不同的索引。mysql中有四种索引分别是B-Tree、HASH、R-Tree、Full-text(全文索引)，其中B-Tree中的B不是指二叉树，而是指平衡树，平衡树是一种以一个主根，和多个分支，以及下面的叶子组成的。下面是一个索引对存储引擎的支持表：

根据上图可以得知，所有存储引擎都支持B-Tree索引，HASH索引只被Memory存储引擎支持，R-Tree索引只被MyISAM引擎支持，Full-text索引只被MyISAM引擎支持。另外HASH索引只适合等值查找，不适合范围查找，B-Tree索引可以进行全关键字、关键字范围和关键字前缀查询。

删除索引和添加索引的语法：

alter table 表名 drop index 索引名;

alter table 表名 add index 索引名(索引字段用逗号分隔);

一、典型用到索引的sql语句

1.全索引匹配，对索引中所有的列都指定了对应值，如下图：

上图where条件中的字段都属于复合索引的列，并且都赋了等号值，这个时候会把rental_date=‘2005-05-25 17：22：10’、customer_id=343、inventory_id=373和customer_id=343作为常量等值匹配所以查询类型是const。

2.匹配值的范围查询，对索引的值能够进行范围查找。

上图customer_id 是索引idx_fk_customer_id的列所以对customer_id进行范围查找可以用到idx_fk_customer_id索引。

3.复合索引匹配最左前缀可以使用索引。比如复合索引列是col1+col2+col3,如果匹配索引字段中col1+col2可以用到索引，col1+col3可以用到索引，col1+col2+col3可以用到索引，但是col2+col3不能用到索引，例如：

上图用到了索引中的第一个列payment_date和第三个列last_update所以可以用到复合索引，再例如：

上图用到复合索引中的第二个列amount和第三个字段last_update所以用不到索引。

4.仅仅对索引列进行查询，即查询的列都在索引字段中这时候会用到索引如图：

5.匹配列前缀，仅仅包含索引中的第一列，并且只包含索引中的第一列开头前的一部分

其中语句中的title like 'AFRICAN' 用到了复合索引中idx_title_desc_part中的前缀title(10).

6.索引部分匹配精确，其他部分范围查找如下图：

其中rental_date ='2006-02-14 15:16:03'是索引部分，customer_id >=300 and customer_id <=400\G是范围匹配，这种查询会用到范围索引，且索引覆盖查询。

7.如果列明是索引，那么列名+is null 就会用到索引，如下图：

上图案例中rental_id 就是列名。

8.mysql5.6实现了索引下放，比mysql5.5索引不下放用了更好性能的索引，在mysql5.5中如果使用rental_date='2006-02-14 15:16:03'用到复合过滤出记录后，然后再回表去查询复合customer_id >=300 and customer_id <=300的记录，而再MySQL5.6中就不用直接回表查了。这就是因为mysql5.6引入index condition pushdown（IPC）的特性，进一步优化了查询：

mysql5.5中：

mysql5.6中：

其中using index condition就说明了mysql用到了index condition pushdown（IPC）的特性。

第二、什么是回表查询和索引覆盖查询：

1.回表查询：

要说回表查询得先说两个概念，聚焦索引和普通索引，这两种索引都是B-Tree索引，其中普通索引存储的是记录行的id，而聚焦索引存储的是记录行的值，当发生查询时，进程先去普通索引找到记录的id号，然后再回到表中用这个id号去找到对用的记录，这就叫回表查询。

2.索引覆盖查询：

进程不需要像上面那样回表，而是直接用一个索引一次性就能找到对应的行记录。