转载

Mysql学习笔记（四）聊聊数据库索引

小心情（可直接跳到分割线后）

今天心情好些了。一些浓的化不开的坏情绪，也渐渐的在晚上解决掉一个复杂的逻辑问题后，渐渐消散了。

今天中午去吃饭的时候，坤哥漫不经心的说：'我这么多年终于悟出了一个道理，人年轻的时候不要那么拼命，都没有时间做其他事情了'

我问什么其他事情,坤哥看了我一眼，又继续漫不经心的说，那么忙，都没有时间谈恋爱了。

我想不那么拼命可以嘛？房价每年都涨，生活的成本与日俱增。如果不努力让自己的价值所赚取的金钱，超过经济增速。那么只能继续成为一个社会底层的loser,继续在青春的美好时光，生活在痛苦之中。

唯有努力而已，努力太不够了，拼命的才够，才能与别人的比拼中，占得一点先机。

那么努力，很痛苦的，生活也很单调。这样的生活，又有那个女孩子喜欢呢？

我想近半年来，我的情商、人际交往能力有所下降就与我将全部身心，放在编程上有关。

我本来想用这些话，去和坤哥去聊聊的，但话终于噎在喉咙间，没有去说。

每个人都有自己要走的路，或孤独或热闹，都是自己选的。

你在选择一条道路的时候，必须去抛弃很多东西。

我愿意得到，所以更能下的狠心失去。

吐槽完毕，正文开始：

什么事索引呢？容易被专业名词忽悠的同学请自觉百度看通俗解释：检寻图书资料的一种工具。将书刊中的内容或项目分类摘录，标明页数，按一定次序排列，附在一书之后，或单独编印成册，以便读者查阅。旧称通检或备检。也称引得。

《MySQL核心技术与最佳实践》这一个章节讲的太棒了，所以今天的内容绝大多数都是提炼总结书中的。

用更简单的方式理解索引：索引就是新华字典前面的按拼音、按部首查字的那部分。

生活中，查一个生僻字，如果不知道读音，也不借助与笔画查找，恐怕很难查得到吧，如果让机器去做这件事，那就是查阅整本字典的所有字，匹配后返回。这就造成了全表扫描，效率很低。

但是我们通过部首去找，然后通过笔画，可能查阅的数据量很小，让机器去做，只扫描相关数据项就好了，速度很快。

因此查询速度的快慢，和数据量的大小没有绝对关系，查询速度取决于取出所需的数据，扫描了多少条表数据。他们的比值越小，效率越高。

几个比较重要的知识点：

1、MySQL在检索表中的数据时，先按照索引“关键字”的值在索引中进行查找，如果能够查找的到，则可以直接定位到起始页，如果没有，则全表扫描。

听着《忘记他》刘惜君的歌，开始总结下面的内容。

2、索引其实就是数据库表中字段值的复制，改字段称为索引的关键字。

3、一张数据表可以建立多个索引。

4、什么是前缀索引：

新华字典中的“部首检字表”的使用方法。首先我们先确认要查找这个字的部首是什么，然后看部首外的字笔画是多少，然后查找相应的字。同样对于数据库表而言，索引中关键字的值可以是索引“关键字的一部分，这种索引成为"前缀索引"。例如我可以给用户表中姓名表建立前缀索引。统计姓名中姓张的人数、姓李的人数。

5、索引可以是字段的组合吗？

对数据库而言，索引可以是字段的组合。数据库表中的某个索引如果有多个关键字构成，此时该索引被称为复合索引。无论索引是一个字段、还是几个字段的组合。这些字段必须来自同一张表，比昂切关键字的值必须是表中相应字段值的copy，。

6、不能跨表建立索引。

7、索引数据需要额外的存储空间吗？

这个问题很扯淡，比如想让牛跑的好，又想让牛不吃草。索引文件肯定占存储空间了。对于数据库表的索引而言，索引关键字经过排序后放在外存中。对于MyISAM数据库表而言，索引存放在外存MYI索引文件中。对于InnoDB存储引擎而言，索引数据存放在外存InnoDB表空间文件中（可能是共享表空间文件，也可能是独享表空间文件）。为了提升数据的检索效率，无论MyISAM表的索引还是，InnoDB的索引，索引的关键字经过升序排序后放在外存中。

8、表中那些字段适合选作表的索引?什么事主索引，什么是聚簇索引？

对于MyISAM表而言，MySQL会自动的将表中所有记录主键值的备份以及每条记录所在的起始页编入索引中。像部首检字表一样生成一张“索引表”存放在外存中，这种索引称为主索引（primary key）。MyISAM表的MYI索引文件，与MYD数据文件位于两个文件。通过MYI索引文件中的“表记录指针”，可以找到MYD数据文件中表记录所在的物理地址。

Innodb表的“主索引”与MyISAM表的主索引不同。InnoDB表的主索引关键字的顺序与InnoDB表记录主键值的顺序一致。这种索引就称为“聚簇索引”，并且每一张表只能拥有一个聚簇索引。

9、索引与数据结构是什么关系？

数据库中的所以关键字在索引文件中存储规则十分复杂。为了有效的提高数据库检索效率。索引通常使用平衡树（btree）或者哈希表等复杂的数据结构进行“编排”。在操作数据库的时候，底层操作在执行这种复杂的运算，但我们是感觉不到的。

10、索引越多越好吗？

如果索引过多，在更新数据（添加、修改、删除）时，除了需要修改表中的数据外，还需要对该表的所有索引进行维护，以维持表字段值和索引关键字值的一致性。反而降低了数据的更新速度。

实践表名，当修改表记录的操作特别频繁时，过多的索引会导致硬盘I/O次数明显增加，反而会明显的降低服务器性能。甚至可能会导致宕机。

11、索引关键字的选取原则。

原则1、表中的某字段离散度越高，该字段越适合作为选作索引的关键字。

数据库用户在创建主键约束的同时，MySQL会自动的创建主索引（primary index）且索引名为primary ；在创建唯一约束的同时，MySQL会自动创建唯一性约束（unique index）默认情况下，索引名为唯一约束性约束的字段名。

原则2、占用存储空间少的字段更适合选作索引的关键字。

原则3、存储空间固定的字段更适合选作索引的关键字。

原则4、 where字句中经常使用的字段应该创建索引，分组字段或者排序字段应该创建索引，两个表的连接字段应该创建索引。

昨晚，迟迟不能入眠。浏览空间的时候，得知朋友要离婚了。她是我大学喜欢的女孩子，当初因为任性等原因，相处了很短一段时间，就分开了。结婚才一年时间，也有了孩子。心突然很心痛，幻想能回到从前。那个星星挂满天空的夜晚，我倔强而任性，她热烈而芬芳。算了，还是不要为这些事伤心了，凡人必有命。我还是好好的码代码吧。

引入索引的目的是为了提高检索的效率，因此索引关键字的选择与select语句息息相关。这句话有两个方面的含义，select语句的设计可以决定索引的设计；索引的设计也同样影响着select的设计。select语句中的where字句、group by 字句，以及order by字句，又可以影响索引的设计。两个表的链接字段应该创建索引，外键约束一经创建，mysql便会自动地创建与外键相对应的索引，这是由于外键字段通常是两个表的连接字段。

原则5、更新频繁的字段不适合创建索引，不会出现在where字句中的字段不应该创建索引。

原则6、最左前缀的原则。

复合索引还有另外一个有点，他通过被称为“最左前缀”（leftmost prefixing）的概念体现出来，假设向一个表的多个字段（例如 firstName ，lastName、address）创建复合索引（索引名：firstname_lastname_address）。当where查询的条件是以下各种字段的组合时，mysql将使用fname_lastname_address索引。其他情况将无法使用 fname_lastname_address索引。

firstname,lastname,address

firstname,;lastname

firstname

原则7、尽量使用前缀索引。

仅仅在姓名中的姓氏部分创建索引，从而可以节省索引的存储空间，提高检索效率。

与数据库的设计一样，索引的设计同样需要数据库开发人员经验的积累，以及智慧的沉淀，同时需要依据系统各自的特点设计出更好的索引，在“加快检索效率”与“降低更新速度”之间做好平衡。从而大幅提升数据库的整体性能。