mysql筛选第二条字段_MySql索引总结

索引的数学原理

索引的实现是B+树，类型的数据结构，B+树内节点不存data，只存key；叶子节点不存储指针。利用磁盘预读局部性原理，一次I\/O操作多个页(4K)到内存，数据库系统的设计者巧妙利用了磁盘预读原理，将一个节点的大小设为等于一个页，这样每个节点只需要一次I\/O就可以完全载入。B-Tree中一次检索最多需要h-1次I\/O(根节点常驻内存)，渐进复杂度为O(h)=O(logdN)。一般实际应用中，出度d是非常大的数字，通常超过100，因此h非常小(通常不超过3)。

聚集索引和非聚集索引

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

MyISAM

MyISAM引擎使用B+Tree作为索引结构，叶节点的data域存放的是数据记录的地址。在MyISAM中，主索引和辅助索引(Secondary key)在结构上没有任何区别，只是主索引要求key是唯一的，而辅助索引的key可以重复。MyISAM中索引检索的算法为首先按照B+Tree搜索算法搜索索引，如果指定的Key存在，则取出其data域的值，然后以data域的值为地址，读取相应数据记录。

InnoDB

InnoDB的数据文件本身就是索引文件，B+树的叶节点data域保存了完整的数据记录，这种索引叫做聚集索引。这个索引的key是数据表的主键，因此InnoDB表数据文件本身就是主索引。InnoD的数据文件本身要按主键聚集，所以InnoDB要求表必须有主键，如果没有显式指定，则MySQL系统会自动选择一个可以唯一标识数据记录的列作为主键，如果不存在这种列，则MySQL自动为InnoDB表生成一个隐含字段作为主键，这个字段长度为6个字节，类型为长整形。

InnoDB的辅助索引data域存储相应记录主键的值而不是地址，辅助索引都引用主键作为data域。

建索引规则

尽量为搜索、分组的数据编写索引，例如where，join，order by和group by

考虑索引选择性：所谓索引的选择性(Selectivity)，是指不重复的索引值(也叫基数，Cardinality)与表记录数(#T)的比值 Index Selectivity = Cardinality/#T

对较短的值进行索引：加快查找，减小索引体积，索引块可以容纳更多的键值，二级索引找主键更快

字符串值可以考虑前缀索引

联合索引的情况下，考虑最左前缀匹配

适可而止

索引优化

使用explain命令查看select命令执行情况，考虑以下优化情况

尽量使用相同数据类型比较

带索引的数据列在表达式，函数中单独出现，例如，下面两个语句(Oracle)： where TO_DAYS(date_col) - TO_DAYS(CURDATE) < cutoff

where date_col < DATE_ADD(CURDATE(), INTERVAL cutoff DAY)

第二条就能用上date_col上的索引

不要在LIKE模式开始的时候使用通配符’%’

避免过多使用MySql的自动类型转换功能：例如字符串表示的整型数字

其他问题

索引覆盖 Covering Index

MySql可以利用索引返回select列中的字段，而不必根据索引再次读取文件，包含所有满足查询需要的数据的索引称为覆盖索引。在Explain的时候，输出的Extra信息中如果有Using Index，就表示这条查询使用了覆盖索引。

Query Cache原理

当MySql接收到一条select类型的query时，会对这条query进行hash计算而得到一个hash值，然后通过该hash值到query cache中去匹配，如果没有匹配中，则将这个hash值存放在一个hash链表中，同时将query的结果集存放进cache中，存放hash值的链表的每一个hash节点存放了相应query结果集在cache中的地址，以及该query所涉及到的一些table的相关信息；如果通过hash值匹配到了一样的query，则直接将cache中相应的query结果集返回给客户端。如果mysql任何一个表中的任何一条数据发生了变化，便会通知query cache需要与该table相关的query的cache全部失效，并释放占用的内存地址。

应该避免在变化频繁的table上使用query cache

通过设置字段Variable变量开启或者关闭query cache

SET profiling = 1;

SET query_cache_type = 0;

SET GLOBAL query_cache_size = 0;

Index Condition Pushdown(ICP)

ICP的原理简单说来就是将可以利用索引筛选的where条件在存储引擎一侧进行筛选，而不是将所有index access的结果取出放在server端进行where筛选。联合索引情况下，ICP只能作用于二级索引。MySQL默认开启

通过设置参数SET optimizer_switch='index_condition_pushdown=off';关闭ICP

在不支持ICP的系统下，索引仅仅作为data access使用。

在ICP优化开启时，在存储引擎端首先用索引过滤可以过滤的where条件，然后再用索引做data access，被index condition过滤掉的数据不必读取，也不会返回server端。

参考文献