关于mysql:MySQL之高性能的索引策略1

5次阅读

共计 2915 个字符，预计需要花费 8 分钟才能阅读完成。

让咱们先看看上面的这段 MySQL 代码：

mysql> SELECT id FROM user WHERE id + 1 = 2;

咱们会发现，在下面这段 SQL 查问语句中，咱们给的条件是一个有变量的表达式，如果咱们此时的 id 列上是存在索引的，那下面的语句能不能应用到索引呢？
答案是不能的，因为 MySQL 无奈主动的解析 id + 1 = 2 这个条件语句，只管咱们能够一眼的看进去此时等价于 id = 1，然而这种做法是无奈应用到索引的，因而咱们在查问的时候，应该使得索引列不能是表达式的一部分，也不能是函数的参数。

如果咱们须要在某一列，例如寄存 url 的一列数据上增加索引来放慢查问的速度，咱们先看看 url 数据的特点，长，相似的还有 TEXT 类型的数据等，这些都是一些很长，占据很大空间的数据，而且会使得对应的索引大且慢。这时候咱们能够应用一些优化的索引策略，例如前缀索引。前缀索引与个别的索引不同，他在查问的时候并不会比对该列数据的所有值，而只是比对它的后面的一部分数据。这样会使得索引变得更加灵便有效率，然而却升高了 索引选择性 。
什么是索引选择性呢？咱们给定一个公示：
索引选择性 = 不同的索引值 / 数据表的记录总数
首先思考，为什么会有所谓的不同索引值和雷同的索引值？这都要建设在咱们是应用前缀索引这种形式建设索引的根底上。例如有两个数据，“abcalkjsdhgasdfasdf”和“abcalaasdasdqwe”。很显然这两个数据是截然不同的，然而如果咱们规定前缀索引的长度是数据的前五个字符，那么会发现这两个数据的索引值都是“abcal”，即这两个数据的索引值是一样的。因而也就升高了索引选择性。简略来说，索引选择性越高，咱们通过索引值能查找到惟一的数据的可能性就越大，索引选择性越低，咱们通过索引值能查找到的惟一的数据的可能性就越小。那么这是否就意味着前缀索引是一个很差的抉择呢？并不，因为个别状况下应用失当的前缀索引，也是能够精确的进行数据的查问，并且可能节俭空间的，而且对于 BLOB，TEXT 或者很长的 VARCHAR 类型的列，必须应用前缀索引，因为 MySQL 不容许索引这些列的残缺长度。

首先，如果一个数据表有 3 个列，那么咱们为每一个列都独自的创立一个索引，是不是就可能使得在查问的时候，无论进行怎么的查找，咱们都能取得最快的效率呢？进行上面的表格建设语句：

mysql> CREATE TABLE temp (c1 INTEGER,c2 INTEGER,KEY (c1),KEY (c2));

事实证明，在理论的操作中，这种为每一个列都建设一个索引的“单纯”的想法，对查找的效率晋升十分的无限，与最佳的索引计划往往效率差距了几个量级。
在 MySQL5.0 之后的版本多出了“索引合并”的策略，肯定水平上是帮程序员优化了这种在一个数据表上创立许多单列索引的操作，然而还是不倡议应用这种索引策略。在 MySQL5.0 之前的版本，如果咱们为表 film_actor 的字段 film_id 和 actor_id 别离创立一个单列索引，而后执行以下的查问操作：

mysql> SELECT film_id, actor_id FROM film_actor
       WHERE  film_id  = 1 OR actor_id = 1;

事实上，在这个查问语句中，咱们所定义的两个单列索引都无奈帮忙提高效率，甚至于在老版本的 MySQL 中，将会应用全表扫描来实现这个查问，这就使得这个索引策略变得齐全没有意义。
除非将下面的语句改写成以下的模式：

mysql> SELECT actor_id, film_id FROM film_actor WHERE actor_id = 1
       UNION ALL
       SELECT actor_id, film_id FROM film_actor WHERE film_id = 1

行将查问改写成两个查问的交加，即每个查问都只是用一个列作为判断的条件，拿着整 MySQL 就会很天然的使用这个列的索引。

因而咱们还是倡议在须要在多个列上建设索引的时候不要独自的给每个列建设一个索引，而是抉择建设一个多列索引。

mysql> CREATE TABLE temp (c1 INTEGER,c2 INTEGER,KEY (c1, c2));

这个时候抉择一个适合的索引列程序就显得十分重要了，因为咱们晓得，如果咱们应用默认的 B -Tree 建设一个多列索引的话，MySQL 会依照咱们创立时候指定的程序建设索引，即先排 c1，再在 c1 排列好的根底上排列 c2。
而且查问的时候每次都是从左开始扫描，意味着如果你第一个查问的列并不是索引的最左列，那这个索引对于你来说就形同虚设。
咱们看看如下的一个查问语句：

mysql> SELECT * FROM payment WHERE staff_id = 2 AND customer_id = 584;

那咱们是应该建设一个 (staff_id, customer_id) 的多列索引还是将他们的程序颠倒过去呢？咱们能够先看看这两个条件的数据量有多大：

sum(staff_id = 2) = 7992
sum(customer_id = 584) = 30

依据教训，咱们应该将索引列 customer_id 放到后面，因为对应条件值的数据量更小。因而如何抉择索引列的程序还是应该依据具体的状况来确定，没有惟一的准则。

聚簇索引其实只是一种非凡的 B -Tree 索引，他并不是一种区别于其余索引的独自的索引模式，而是一种存储形式。
当应用聚簇索引的时候，所有的数据都存储在索引树的叶子节点上。
下图展现了聚簇索引中的记录是如何寄存的：

在创立聚簇索引时，InnoDB 应用主键作为索引列汇集数据。如果数据表没有定义主键，则会抉择一个惟一的非空索引代替，如果没有这样的索引 InnoDB 则会隐式定义一个主键来作为聚簇索引。
聚簇索引有以下的长处：

能够吧相干的数据保留在一起。例如实现电子邮箱时，能够依据用户的 id 来汇集数据，这样只须要从磁盘读取大量的数据页就能获取某个用户的全副邮件，如果没有应用聚簇索引，那么邮件的排列就是凌乱的，有可能每封邮件都要导致一次磁盘 IO。
数据拜访更快。聚簇索引将索引和数据保留在同一个 B -Tree 中（一般索引只保留索引和指向数据行的指针，不保留数据），因而聚簇索引的查问会更快。
应用笼罩索引扫描的查问能够间接应用页节点中的主键值。

同时，聚簇索引也有以下的缺点：

聚簇索引最大限度地进步了 IO 密集型利用的性能，但如果数据全副寄存在内存中，则拜访的程序就没有那么重要了，聚簇索引就没有什么劣势了。
插入速度重大依赖插入程序。
更新聚簇索引列的代价很高。
基于聚簇索引的表在插入新行，或者主键被更新导致须要挪动行的时候，可能面临“页决裂”的问题。当行的主键要求必须将这一行插入到某个已满的页面时，存储引擎会将该页决裂成两个页面包容该行，页决裂将会倒是表占据更多的磁盘空间。
聚簇索引可能导致全表扫描变慢，特地是在行比拟稠密的时候，或者因为页决裂导致存储不间断的时候。
二级索引（非聚簇索引）可能比设想中的大没因为耳机索引的叶子节点蕴含了援用行的主键列。
二级索引拜访须要两次索引查找，而不是一次。

因为聚簇索引的内容比拟多，会专门出一篇来较为深刻的将聚簇索引。

正文完