Mysql索引的正确打开姿势

2016-07-10 本文已影响593人一只小哈

什么是索引：####

要想深入的了解索引，需要先知道什么是索引，在我刚接触数据库的时候，只是知道什么是CRUD，对于索引一无所知。CRUD是一个DB的基本功能，而索引则是让我们的CRUD更快的工具或者方法，当然主要是查询。举一个栗子：
在我们很小的时候我们都用过新华字典，查过新华字典的都知道，字典前几页都是一些字或者拼音对应的页码，这个就是字典的索引。如果没有索引，假设我们想找一个字，那么我们需要通篇的去翻字典，但是一旦我们有了索引，就可以找到对应的页，从而直接找到我们想要的东西。Mysql的索引也是做这个事情的。
众所周知Mysql主流的存储引擎有两种MyISAM和InnoDB,MyISAM在我的公司用的还是比较少的，主要是使用InnoDB引擎的Mysql。为什么呢？那就需要看看二者有什么区别。

一、MyISAM和InnoDB的比较：####

</br>
下面看一下两种引擎的主要不同之处：

tips	MyISAM	InnoDB
Full Text 索引	支持	不支持
count（*）性能（不使用where过滤）	内置计数器，性能比较高	扫描全表，性能较差（慎用），使用where过滤，同时不使用主键进行count，性能较好
对事务的支持	不支持	支持事务，具有ACDI特性，同时具有四种隔离级别
索引结构	索引采用B+树，同时数据和索引是分离的	主键索引和数据是一起的，其他索引是和数据分离的
锁级别	MyISAM主要是表锁，所以性能不高	InnoDB主要是行锁，操作的粒度降低，性能比较好
外键支持	不支持外键	支持外键

其实两者还是有很多的不同的，上面的只是一些关键的case，可能不全，欢迎补充。

二、Mysql索引种类：###

</br>

唯一索引：

创建索引

create unique index 索引名 on 表名(列名);
alter table 表名 add unique index 索引名 (列名);

删除索引

drop index 索引名 on 表名；
alter table 表名 drop index 索引名;

主键索引：
其实主键索引是唯一索引的一种，一般指定主键的表会默认创建主键索引，如果不指定主键，其实DB会默认的生成主键，但是这种主键性能不高，所以在建表的时候还是指定主键吧。==！

ALTER TABLE `table_name` ADD PRIMARY KEY ( `column` )

全文索引：
就是对Char、Text等结构建索引，只有MyISAM支持，现在应该很少用了，大家都用基于Lucene的索引中间件解决这种Case了，没用过也不过多纠缠。

ALTER TABLE `table_name` ADD FULLTEXT ( `column`)

4.单列索引：
对单个列建立索引，对于经常用来进行检索的字段，做好建立索引，能很大程度的优化查询，但是索引的建立是有一定规则的，一会儿会提到这些规则。

ALTER TABLE `table_name` ADD INDEX index_name ( `column` )

5.组合索引：
组合索引是将多个列组合到一起建立一个索引，组合索引有很著名的“左前缀规则”，同时也因为这个规则，其实有时候单列索引和组合索引是有重复的。什么是左前缀规则，一会儿会交代。

ALTER TABLE `table_name` ADD INDEX index_name ( `column1`, `column2`, `column3` )

三、Mysql索引建立原则：###

1.首先，最重要的一点是，索引不是越多越好。索引会增加插入更新代价，所以过多的索引会拖慢数据的写入。一般，一张表的索引不要超过五个。
2.单个索引中的字段数不要超过5个，多索引，容易产生锁等待和冲突。
3.建立索引的字段，最好是区分度比较高的字段，例如：性别这种字段，其实是不适合建索引的，因为即使建了索引对你的查询也没什么帮助。区分度的计算可通过：select count(distinct coloum)) /count(*) from table_name来实现。
4.用于Join的字段，一定要建索引，这样对查询效率会有很大的提高。
5.组合索引其实可以替代部分单列索引,要尽量避免冗余和重复。例如对ABC建索引，其实可以达到对A单独建索引和AB建索引的情况。
6.Like的查询有时候是不能用到索引的。例如'%s',前缀模糊的查询是不走索引的，这种查询语句应该尽量避免，DB是主要业务数据的存储，模糊查询应该尽量少用。
7.不要使用索引计算，因为查询走索引的活最好是直接命中，每一次都有对索引进行计算，是不合理的，例如：

SELECT *  from table where FROM_UNIXTIME( coloum, '%Y%m%d' ) = “xxxx”

改写成:

SELECT *  from table where coloum = FROM_UNIXTIME( 'xxxx'， '%Y%m%d' ) ;

性能会提高很多。

上述这些原则可能覆盖的不是很全面，也希望有知道的同学帮我补充。

四、Mysql索引原理：###

首先说mysql的索引结构，其实mysql支持很多种索引类型，其中主要的是Hash和Btree索引，后者是主要的索引结构。

简单介绍B-tree B+ tree树
B-tree结构视图 B-tree结构视图
一棵m阶的B-tree树，则有以下性质
Ki表示关键字值，上图中，k1<k2<…<ki<k0<Kn(可以看出，一个节点的左子节点关键字值<该关键字值<右子节点关键字值)
Pi表示指向子节点的指针，左指针指向左子节点，右指针指向右子节点。即是：p1[指向值]<k1<p2[指向值]<k2……
所有关键字必须唯一值（这也是创建MyISAM 和innodb表必须要主键的原因），每个节点包含一个说明该节点多少个关键字，如上图第二行的i和n
节点：每个节点最可以有m个子节点。
根节点若非叶子节点，至少2个子节点，最多m个子节点
每个非根，非叶子节点至少[m/2]子节点或叫子树（[]表示向上取整），最多m个子节点
关键字：根节点的关键字个数1~m-1
非根非叶子节点的关键字个数[m/2]-1_{m-1,如m=3，则该类节点关键字个数：2-1}2
关键字数k和指向子节点个数指针p的关系：k+1=p ，注意根据储存数据的具体需求，左右指针为空时要有标志位表示没有 B+tree结构示意图如下： B+tree结构示意图
B+树是B-树的变体，也是一种多路搜索树： * 非叶子结点的子树指针与关键字个数相同 * 为所有叶子结点增加一个链指针（红点标志的箭头）
MyISAM的索引结构：
MyISAM索引用的B+ tree来储存数据，MyISAM索引的指针指向的是键值的地址，地址存储的是数据，如下图：
MyISAM索引用的B+ tree
结构讲解：上图3阶树，主键是Col2，Col值就是改行数据保存的物理地址，其中红色部分是说明标注。
1标注部分也许会迷惑，前面不是说关键字15右指针的指向键值要大于15，怎么下面还有15关键字？因为B+tree的所有叶子节点包含所有关键字且是按照升序排列（主键索引唯一，辅助索引可以不唯一），所以等于关键字的数据值在右子树
2标注是相应关键字存储对应数据的物理地址，注意这也是之后和InnoDB索引不同的地方之一
2标注也是一个所说MyISAM表的索引和数据是分离的，索引保存在”表名.MYI”文件内，而数据保存在“表名.MYD”文件内，2标注的物理地址就是“表名.MYD”文件内相应数据的物理地址。（InnoDB表的索引文件和数据文件在一起）
辅助索引和主键索引没什么大的区别，辅助索引的索引值是可以重复的（但InnoDB辅助索引和主键索引有很明显的区别，这里先提醒注意一下）
3.InnoDB的索引结构：
（1）结构图

结构上：由上图可以看出InnoDB的索引结构很MyISAM的有很明显的区别
MyISAM表的索引和数据是分开的，用指针指向数据的物理地址，而InnoDB表中索引和数据是储存在一起。看红框1可看出一行数据都保存了。
还有一个上图多了三行的隐藏数据列（虚线表），这是因为MyISAM不支持事务，InnoDB处理事务在性能上并发控制上比较好，看图中的红框2中的DB_TRX_ID是事务ID，自动增长；db_roll_ptr是回滚指针，用于事务出错时数据回滚恢复；db_row_id 是记录行号，这个值其实在主键索引中就是主键值，这里标出重复是为了容易介绍，还有的是若不是主键索引（辅助索引）， db_row_id会找表中unique的列作为值，若没有unique列则系统自动创建一个。关于InnoDB跟多事务MVCC点此：http://www.phpben.com/?post=72
（2）加入上表中Col1是主键（下图标错），而Col2是辅助索引，则相应的辅助索引结构图：
可以看出InnoDB辅助索引并没有保存相应的所有列数据，而是保存了主键的键值（图中1、2、3….）这样做利弊也是很明显：
在已有主键索引，避免数据冗余，同时在修改数据的时候只需修改辅助索引值。
但辅助索引查找数据事要检索两次，先找到相应的主键索引值然后在去检索主键索引找到对应的数据。这也是网上很多 mysql性能优化时提到的“主键尽可能简短”的原因，主键越长辅助索引也就越大，当然主键索引也越大。

四、Mysql索引的使用：###

</br>
见下回.....