MySQL：数据结构及算法原理

情况七：萌芽前提中含有函数或表达式。

很不幸，如不雅萌芽前提中含有函数或表达式，则MySQL不会为这列应用索引(固然某些在数学意义上可以应用)。例如：

固然这个萌芽和情况五中功能雷同，然则因为应用了函数left，则无法为title列应用索引，而情况五顶用LIKE则可以。再如：

索引选择性与前缀索引

既然索引可以加快萌芽速度，那么是不是只如果萌芽语句须要，就建上索引?谜底是否定的。因为索引固然加快了萌芽速度，但索引也是有价值的：索引文件本身要消费存储空间，同时索引会加重插入、删除和修改记录时的包袱，别的，MySQL在运行时也要消费资本保护索引，是以索引并不是越多越好。一般两种情况下不建议建索引。

第一种情况是表记录比较少，例如一两千条甚至只有几百笔记录的表，没须要建索引，让萌芽做全表扫描就好了。至于若干笔记录才算多，这个小我有小我的看法，我小我的经验是以2000作为分界线，记录数不跨越 2000可以推敲不建索引，跨越2000条可以酌情推敲索引。

另一种不建议建索引的情况是索引的选择性较低。所谓索引的选择性(Selectivity)，是指不反复的索引值(也叫基数，Cardinality)与表记录数(#T)的比值：

Index Selectivity = Cardinality / #T

显然选择性的取值范围为(0, 1]，选择性越高的索引价值袈浣大年夜，这是由B+Tree的性质决定的。例如，上文用到的employees.titles表，如不雅title字段经常被零丁萌芽，是否须要建索引，我们看一下它的选择性：

title的选择性不足0.0001(精确值为0.00001579)，所以实袈溱没有什么须要为其零丁建索引。

有一种与索引选择性有关的索引优化策略叫做前缀索引，就是用列的前缀代替全部列作为索引key，当缁ず长度合合时，可以做到既使得前缀索引的选择性接近全列索引，同时因为索引key变短而削减了索引文件的大年夜小和保护开销。下面以employees.employees表为例介绍前缀索引的选择和应用。

大年夜图12可以看到employees表只有一个索引<emp_no>，那么如不雅我们想按名字搜刮一小我，就只能全表扫描了：

如不雅频繁按名字搜刮员工，如许显然效力很低，是以我们可以推敲建索引。有两种选择，建<first_name>或<first_name, last_name>，看下两个索引的选择性：

<first_name>显然选择性太低，<first_name, last_name>选择性很好，然则first_name和last_name加起来长度为30，有没有兼顾长度和选择性的办法?可以推敲用first_name和last_name的前几个字符建立索引，例如<first_name, left(last_name, 3)>，看看其选择性：

选择性还不错，但离0.9313照样有点距离，那么把last_name前缀加到4：

这时选择性已经很幻想了，而这个索引的长度只有18，比<first_name, last_name>短了接近一半，我们把这个前缀索引建上：

ALTER TABLE employees.employees 
 
ADD INDEX `first_name_last_name4` (first_name, last_name(4));

此时再履行一遍按名字萌芽，比较分析一下与建索引前的结不雅：

机能的晋升是明显的，萌芽速度进步了120多倍。

第二个与MyISAM索引的不合是InnoDB的帮助索引data域存储响应记录主键的值而不是地址。换句话说，InnoDB的所有帮助索引都引用主键作为data域。例如，图11为定义在Col3上的一个帮助索引：

前缀索引兼顾索引大年夜小和萌芽速度，然则其缺点是不克不及用于ORDER BY和GROUP BY操作，也不克不及用于Covering index(即当索引本身包含萌芽所需全部数据时，不再拜访数据文件本身)。

InnoDB的主键选择与插入优化

如不雅表应用自增主键，那么每次插入新的记录，记录就会次序添加到当前索引节点的后续地位，当一页写满，就会主动开辟一个新的页。如下图所示：

图13

如许就会形成一个紧凑的索引构造，近似次序填满。因为每次插入时也不须要移动已稀有据，是以效力很高，也不会增长很多开销在保护索引上。

如不雅应用非自增主键(如不哑?奢证号或学号等)，因为每次插入主键的值近似于随机，是以每次新记载都要被插到现有索引页得中心某个地位：

6/7 首页上一页 4 5 6 7 下一页尾页

　　推荐阅读

　　华为携手石化盈科共推智能制造平台给石化行业“减负增效”

【51CTO.com原创稿件】前不久，华为与石化盈科隆重推出了两边深度合作后首个重要>>>详细阅读

本文标题：MySQL：数据结构及算法原理

地址：http://www.17bianji.com/lsqh/35159.html

1/2 1