mysql排序命中索引_Mysql 索引使用规则和设计优化

大部分情况下，尤其是记录数量较少的情况下Mysql总是能正常运转的很好，但不可避免的，随着数据库记录数的增长以及SQL语句越来越复杂，总会有一些实际效果与数据库或SQL设计人员理解相违背的情况，这就需要开发者对Mysql的原理和存在的问题有一个基本的认识。本文主要探讨了Mysql索引的使用和相关知识，这些知识并不复杂，不需要专业的数据库学习经验就能搞明白，理解了这些可以帮助开发人员更好的进行数据库索引设计和SQL查询语句的编写。

1. Mysql 是如何使用索引的

索引可以帮助我们快速的找到包含指定列值的行。假如没有索引的话，Mysql必须从第一行开始查找整个表，才能找到我们想要的那些行。如果没有索引，表越大，花费的时间也就越大。如果我们在查询条件中指定了某几个列的值，并且这个表恰好有一个建立在这些列上的索引，那么Mysql就可以从数据文件中快速的定位到数据所在的位置，而不用查找整个数据文件。这比不断的一行行读取数据快多了[1]。大部分Mysql索引(Primary Key、Unique index和FullText)都通过B树来存储和实现。也有一些例外：空间数据类型使用的索引是基于R-树的；内存表还支持哈希索引；InnoDB为Fulltext索引使用了逆转链表[1]。本文不打算去赘述B树的原理和创建过程，有兴趣的可以点击B树了解。假设现在索引已经创建完毕了，那么Mysql是如何查找到我们需要的数据的呢？下面我们就MyISAM和Innodb两种不同的存储引擎做讨论。关于MyISAM和Innodb我们需要知道的有：MyISAM不支持事务，而Innodb支持。

MyISAM索引和数据的存储是分开的(不同的文件)，索引中最终检索到的是数据的物理地址偏移量。而InnoDB中，索引段和数据段在同一个文件中的不同段，查到索引后可以直接取出数据。

MyISAM是非聚集索引，而Innodb则是聚集索引。所谓聚集索引是指索引和数据的逻辑排列顺序与实际物理存储顺序一致，新华字典就是典型的聚集索引，字(叶子索引)和释意(数据)靠在一起，且按一定顺序排列的。而“非聚集索引”则相反，索引单独放在一块区域，并且叶子节点存放的是数据的地址偏移量。

下面4张图分别为MyISAM和Innodb的主索引和辅助索引逻辑图：

1.1 MyISAM存储引擎

在MyISAM中，索引(含叶子节点)存放在单独的.myi文件中，叶子节点存放的是数据的物理地址偏移量(通过偏移量访问就是随机访问，速度很快)。主索引是指主键索引，键值不可能重复；辅助索引则是普通索引，键值可能重复。

假设有以下语句

select * from table_name where id = 3

其中id为主键，那么首先检索的是索引，索引中经过2层查找，找到了索引为3的节点，值为0xABAB，代表了从.myd文件中偏移量为0xABAB的地方开始读取一行的数据。辅助索引对应普通索引，存在相同的键值。

1.2 Innodb存储引擎

在Innodb中，索引分叶子节点和非叶子节点，非叶