MySQL索引

索引概念

索引是一种单独的、物理的对数据库表中一列或多列的值进行排序的一种存储结构，它是某个表中一列或若干列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单。

索引用于快速找出在某个列中有一特定值的行，不使用索引，MySQL必须从第一条记录开始读完整个表，直到找出相关的行，表越大，查询数据所花费的时间就越多，如果表中查询的列有一个索引，MySQL能够快速到达一个位置去搜索数据文件，而不必查看所有数据，那么将会节省很大一部分时间。

其中MySQL中的索引的存储类型有两种：BTREE、HASH。

索引我们分为四类：单列索引(普通索引，唯一索引，主键索引)、组合索引、全文索引、空间索引。

索引建立原则

1.  数据量大时采用

2.  区分度高

尽量选择区分度高的列作为索引,区分度的公式是count(distinct col)/count(*)，表示字段不重复的比例，比例越大我们扫描的记录数越少，唯一键的区分度是1，而一些状态、性别字段可能在大数据面前区分度就是0，那可能有人会问，这个比例有什么经验值吗？使用场景不同，这个值也很难确定，一般需要join的字段我们都要求是0.1以上，即平均1条扫描10条记录.

3.  经常作为查询条件，判断条件的列上加索引。

索引的优缺点

1. 优点

索引可以让服务器快速地定位到表的指定位置。但是这并不是索引的唯一作用。例如最常见的B-Tree 索引，按照顺序存储数据，所以MySQL可以用来做 order by 和 group by 操作。因为数据是有序的，所以B-Tree 也会将相关的列值都存储在一起。

1) 索引大大减少了服务器需要扫描的数据量。

2) 索引可以帮助服务器避免排序和临时表。

3) 索引可以将随机I/O变为顺序 I/O。

2. 缺点

1）  索引需要占用一定的存储空间；

2）  数据插入、更新、删除时会受索引的影响，性能会降低。因为数据变更索引也需要进行更新；一般情况这个问题不太严重，但如果你在一个大表上创建了多种组合索引，索引文件的会膨胀很快

3）  多个索引，优化器需要耗时则优选择；

什么情况下应不建或少建索引

1.表记录太少

如表zl_sybm（使用部门）一般只有几条记录，除了主关键字外对任何一个字段建索引都不会产生性能优化，实际上如果对这个表进行了统计分析后ORACLE也不会用你建的索引，而是自动执行全表访问。如：

select * from zl_sybm wheresydw_bh=’5401’（对sydw_bh建立索引不会产生性能优化）

2.经常插入、删除、修改的表

对一些经常处理的业务表应在查询允许的情况下尽量减少索引。

3.数据重复且分布平均的表字段（区分度不够）

假如一个表有10万行记录，有一个字段A只有T和F两种值，且每个值的分布概率大约为50%，那么对这种表A字段建索引一般不会提高数据库的查询速度。

前缀索引

有时候需要索引很长的字符列，这会让索引变得大且慢。通常可以索引开始的部分字符，这样可以大大节约索引空间，从而提高索引效率。但这样也会降低索引的选择性。

索引的选择性是指不重复的索引值（也称为基数，cardinality)和数据表的记录总数的比值count(distinct col)/count(*)，范围从1/#T到1之间。索引的选择性越高则查询效率越高，因为选择性高的索引可以让MySQL在查找时过滤掉更多的行。唯一索引的选择性是1，这是最好的索引选择性，性能也是最好的。

一般情况下某个前缀的选择性也是足够高的，足以满足查询性能。对于BLOB，TEXT，或者很长的VARCHAR类型的列，必须使用前缀索引，因为MySQL不允许索引这些列的完整长度。

诀窍在于要选择足够长的前缀以保证较高的选择性，同时又不能太长（以便节约空间）。前缀应该足够长，以使得前缀索引的选择性接近于索引的整个列.如下，A1最接近A0时的prefix值，即为合适的前缀长度。

select count(distinct columnname)/count(*) as A0 from tablename;

select count(distinct left(columnname,prefix))/count(*) as A1 from tablename;

mysql> alter table city add key (city(6));

最左前缀原则

最左前缀：顾名思义，就是最左优先，例如我们创建了a,b,c联合索引,相当于创建了(a)单列索引，(a,b)组合索引以及(a,b,c)组合索引。因而只查询b或者c或者b,c所在数据时不会用到该索引.在创建多列索引时，要根据业务需求，where子句中使用最频繁的一列放在最左边。

单列索引与多列索引 

索引可以是单列索引，也可以是多列索引。下面我们通过具体的例子来说明这两种索引的区别。假设有这样一个people表： 

ALTER TABLE people ADD INDEX fname_lname_age (firstname,lastname,age); 

由于索引文件以B-树格式保存，MySQL能够立即转到合适的firstname，然后再转到合适的lastname，最后转到合适的age。在没有扫描数据文件任何一个记录的情况下，MySQL就正确地找出了搜索的目标记录！ 
那么，如果在firstname、lastname、age这三个列上分别创建单列索引，效果是否和创建一个firstname、lastname、 age的多列索引一样呢？答案是否定的，两者完全不同。当我们执行查询的时候，MySQL只能使用一个索引。如果你有三个单列的索引，MySQL会试图选择一个限制最严格的索引(获得结果集记录数最少)。但是，即使是限制最严格的单列索引，它的限制能力也肯定远远低于firstname、lastname、age这三个列上的多列索引。 

MySQL索引实现

在MySQL中，索引属于存储引擎级别的概念，不同存储引擎对索引的实现方式是不同的。

Myisam索引实现

MyISAM引擎使用B+Tree作为索引结构，叶结点的data域存放的是数据记录的地址。

在MyISAM中，主索引和辅助索引（Secondary key）在结构上没有任何区别，只是主索引要求key是唯一的，而辅助索引的key可以重复。

MyISAM中索引检索的算法为首先按照B+Tree搜索算法搜索索引，如果指定的Key存在，则取出其data域的值，然后以data域的值为地址，读取相应数据记录。

MyISAM的索引方式也叫做“非聚集”的，之所以这么称呼是为了与InnoDB的聚集索引区分。

InnoDB索引实现

虽然InnoDB也使用B+Tree作为索引结构，但具体实现方式却与MyISAM截然不同。

区别一：

InnoDB的数据文件本身就是索引文件。从上文知道，MyISAM索引文件和数据文件是分离的，索引文件仅保存数据记录的地址。而在InnoDB中，表数据文件本身就是按B+Tree组织的一个索引结构，这棵树的叶结点data域保存了完整的数据记录。这个索引的key是数据表的主键，因此InnoDB表数据文件本身就是主索引。叶结点包含了完整的数据记录。这种索引叫做聚集索引。

因为InnoDB的数据文件本身要按主键聚集，所以InnoDB要求表必须有主键（MyISAM可以没有），如果没有显式指定，则MySQL系统会自动选择一个可以唯一标识数据记录的列作为主键，如果不存在这种列，则MySQL自动为InnoDB表生成一个隐含字段作为主键，这个字段长度为6个字节，类型为长整形。

区别二：

第二个与MyISAM索引的不同是InnoDB的辅助索引data域存储相应记录主键的值而不是地址。换句话说，InnoDB的所有辅助索引都引用主键作为data域。

聚集索引这种实现方式使得按主键的搜索十分高效，但是辅助索引搜索需要检索两遍索引：首先检索辅助索引获得主键，然后用主键到主索引中检索获得记录。

了解不同存储引擎的索引实现方式对于正确使用和优化索引都非常有帮助，例如知道了InnoDB的索引实现后，就很容易明白为什么不建议使用过长的字段作为主键，因为所有辅助索引都引用主索引，过长的主索引会令辅助索引变得过大。再例如，用非单调的字段作为主键在InnoDB中不是个好主意，因为InnoDB数据文件本身是一颗B+Tree，非单调的主键会造成在插入新记录时数据文件为了维持B+Tree的特性而频繁的分裂调整，十分低效，而使用自增字段作为主键则是一个很好的选择。

索引失效的各种情形

1)  没有查询条件，或者查询条件没有建立索引 

2)  在查询条件上没有使用引导列 

3)  查询的数量是大表的大部分，应该是30％以上。 

4)  索引本身失效

5)  查询条件使用函数在索引列上，或者对索引列进行运算，运算包括(+，-，*，/，! 等) 错误的例子：select * from test where id-1=9; 正确的例子：select* from test where id=10; 

6)  对小表查询 

7)  提示不使用索引

8)  统计数据不真实 

9)  CBO计算走索引花费过大的情况。其实也包含了上面的情况，这里指的是表占有的block要比索引小。 

10) 隐式转换导致索引失效.这一点应当引起重视.也是开发中经常会犯的错误. 由于表的字段tu_mdn定义为varchar2(20),但在查询时把该字段作为number类型以where条件传给Oracle,这样会导致索引失效. 错误的例子：select * from test where tu_mdn=13333333333;正确的例子：select * from test wheretu_mdn='13333333333'; 

11) 1,<> 2,单独的>,<,(有时会用到，有时不会) 

12) like"%_" 百分号在前. 

13) 表没分析. 

14) 单独引用复合索引里非第一位置的索引列. 

15) 字符型字段为数字时在where条件里不添加引号. 

16) 对索引列进行运算.需要建立函数索引. 

17) not in ,notexist. 

18) 当变量采用的是times变量，而表的字段采用的是date变量时.或相反情况。 

19) B-tree索引 is null不会走,isnot null会走,位图索引 is null,is notnull 都会走 

20) 联合索引 is not null 只要在建立的索引列（不分先后）都会走, in null时必须要和建立索引第一列一起使用,当建立索引第一位置条件是is null 时,其他建立索引的列可以是is null（但必须在所有列 都满足is null的时候）,或者=一个值；当建立索引的第一位置是=一个值时,其他索引列可以是任何情况（包括is null =一个值）,以上两种情况索引都会走。其他情况不会走。

建索引的注意点

1.使用短索引

对串列进行索引，如果可能应该指定一个前缀长度。

2.最左前缀匹配原则(针对组合索引)

非常重要的原则，mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配，比如a = 1 and b = 2 and c > 3and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。

=和in可以乱序，比如a = 1 and b = 2 andc = 3 建立(a,b,c)索引可以任意顺序，mysql的查询优化器会帮你优化成索引可以识别的形式

3. 单列索引与组合索引的选择

MySQL查询只使用一个索引，因此如果where子句中已经使用了索引的话，那么order by中的列是不会使用索引的。因此数据库默认排序可以符合要求的情况下不要使用排序操作；尽量不要包含多个列的排序，如果需要最好给这些列创建复合索引。

4.避免过度索引

尽量选择区分度高的列作为索引.

但是也不是什么情况都非得建索引不可，比如性别可能就只有两个值，建索引不仅没什么优势，还会影响到更新速度；

5.在Join表的时候使用相当类型的列，并将其索引

如果你的应用程序有很多 JOIN 查询，你应该确认两个表中Join的字段是被建过索引的。这样，MySQL内部会启动为你优化Join的SQL语句的机制。

而且，这些被用来Join的字段，应该是相同的类型的。

6.尽量的扩展索引，不要新建索引

比如表中已经有a的索引，现在要加(a,b)的索引，那么只需要修改原来的索引即可;

7. like语句操作

一般来说，在WHERE和JOIN中出现的列需要建立索引，但也不完全如此，因为MySQL只对<，<=，=，>，>=，BETWEEN，IN，以及某些时候的LIKE才会使用索引。因为在以通配符%和_开头作查询时，MySQL不会使用索引。

8.不使用NOT IN和<>操作

NOT IN和<>操作都不会使用索引将进行全表扫描。NOT IN可以NOT EXISTS代替，id<>3则可使用id>3 or id<3

9.不使用索引的情况

如果你符合条件的记录数量大于 30%则MYSQL会认为没必要使用索引，全表扫描反而更经济。