浅谈MySql索引及其优化原理

索引：

是一种特殊的文件，它们包含着对所有记录的引用指针，索引的原理是根据索引值得到行指针,然后快速定位到数据库记录，它好比是一本书前面的目录，能加快数据库的查询速度。索引可以极大地提高数据查询速度，但是由于添加了索引，在增、删、改的时候也要按照索引的规律存放，所以无形中也会降低增、删、改的速度，因为在执行这些操作时，也要操作索引文件用来维护，所以说索引不是建的越多越好而是要建在合理的字段。

普通索引：

（由关键字KEY或INDEX定义的索引）的唯一任务是加快对数据的访问速度。普通索引允许被索引的数据列包含重复的值。

唯一索引：

不允许两行具有相同的索引值。如果能确定某个数据列将只包含彼此各不相同的值，在为这个数据列常创建索引时应该用就用关键字UNIQUE把他定义成一个唯一索引。也就是说，唯一索引可以保证数据记录的唯一性。

联合索引 ：

可以覆盖多个数据列，像INDEX(columnA, columnB)索引，这就是联合索引。

主键：

是一种特殊的唯一索引，一张表中只能定义一个主键索引，通常有一列或列组合，用于唯一标识一条记录，使用关键字PRIMARY KEY来创建。

聚簇索引与非聚簇索引：

首先，聚簇索引不是一种单独的索引类型，其实是数据的存储方式。聚簇索引的叶子节点存放数据的物理位置，找到了索引也就找到了数据。

在MySql的InnoDB引擎中，表数据的文件是按照B+树组织的一个索引结构。而聚簇索引就是按照每张表的主键构造出来的B+树，叶子节点就是整张表的行数据，所以聚簇索引的叶子节点也被称为数据页。

非聚簇索引也是通过B+树的形式存储的，但它的存储并非与真实数据关联，而是关联到聚簇索引对应的主键值，所以在查询中，它需要先查到聚簇索引对应的物理位置，然后再关联到具体的数据行。

联合主键：

 多个字段联合形成一个主键组合

主键和唯一索引的区别：

对于主键/unique constraint ，oracle/sql server/mysql等都会自动建立唯一索引；

1.  主键可作外键，唯一索引不可；
2. 主键不可为空，唯一索引可以；
3.  主键索引一定是唯一索引， 唯一索引不是主键索引;
4. 主键可以与外键构成参照完整性约束，防止数据不一致;

索引的使用：

动作	使用聚簇索引	使用非聚簇索引
列经常被分组排序	应	应
返回某范围内的数据	应	不应
一个或极少不同值	不应	不应
小数目的不同值	应	不应
大数目的不同值	不应	应
频繁更新的列	不应	应
外键列	应	应
主键列	应	应
频繁修改索引列	不应	应

联合索引生效原则：按照联合索引的顺序，从左往右依次使用生效，如果中间某个索引没有使用，那么断点前面的索引部分起作用，断点后面的索引没有起作用，即：联合索引遵循最左匹配原则。

例如：INDEX(a,b,c,d)是一个联合索引，那么where a=1 and b=2 and d=4;只有a和b索引起作用，中间c断了，d索引也就不起作用。

另外，数据库会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配。

比如where a = 1 and b = 2 and c > 3 and d = 4 ，如果建立(a,b,c,d)顺序的索引，索引对d是起不了作用的,因为c出现了“>”。

如果建立(a,b,d,c)的索引则where a = 1 and b = 2 and c > 3 and d = 4 中索引对a、b、c、d都能起到作用，并且a,b,d的顺序可以任意调整，即：select * from test where b=2 and d=4 and a=1and c>3中索引对a、b、c、d都起到了作用。

注意：

1）只要列中包含有NULL值都将不会被包含在索引中，复合索引中只要有一列含有NULL值，那么这一列对于此复合索引就是无效的。所以我们在数据库设计时不要让字段的默认值为NULL。

2）一般情况下不鼓励使用like操作，如果非使用不可，如何使用也是一个问题。like “%aaa%” 不会使用索引而like “aaa%”可以使用索引。