之前我们已经详细介绍了索引的原理和索引查询的原理。都说工欲善其事必先利其器。你必须在学习阶段一步步学习这些知识。不要眼高手低，一定要沉着冷静，争取一萝卜一穴，学完以后，把这些知识点一下子拿下，然后运用。我们在上一篇文章中讨论过，索引的设计应该根据WHERE条件、ORDERBY以及GROUPBY后面的字段来设计。在此我们做一个简要的概述。MySQL会为主键索引维护一个B+树结构，我们称之为聚集索引。对于非主键（一般建立联合索引），索引字段会按顺序排序，然后从第一个字段值开始比较，如果第一个字段值相同，比较下一个字段值，然后推回转动。如果联合索引中的字段值相同，则按照主键排序。另外，聚集索引（主键索引）的B+树存储一行记录的所有信息，而非聚集索引（非主键索引）只存储索引字段值和主键字段值.好吧，我们将在这里停下来回顾索引原则。在本文中，我们将继续介绍MySQL设置的基本原理。这也很好理解。就是在设计和建立索引时需要遵循什么原则，按照“标准”来建立索引。今天我们就一次性把索引设计的原则全部说清楚。这个知识点我多说几句。面试的时候，我经常会要求应聘者判断自己是否真正理解索引，而不是简单的死记硬背八股作文！主键索引对于主键索引其实是最简单的，但是这里有几点需要注意。当你设计主键时，它必须是自增的。不建议使用UUID作为主键。为什么？因为UUID是无序的，所以MySQL在维护聚集索引的时候是按照主键的顺序排序的，也就是说每个数据页中的数据都要按照主键从小到大排序，数据和数据以前，它是通过单向链表连接的。上一个数据页中最大主键的值必须小于下一个数据页中最小主键的值。数据页通过双向链表维护。画个图帮助大家理解，如果主键是自增的，MySQL可以根据主键目录快速定位到应该插入新记录的位置。如果主键不是自增的，每次都需要从头开始比较，然后找到合适的位置，然后插入记录，确实严重影响效率，所以主键的设计必须自增。另外，唯一索引和主键索引类似，但是唯一索引不一定是自增的，所以维护唯一索引的开销一定要大于主键索引。但是，唯一索引的值是唯一的（唯一索引可以有NULL值），通过索引字段可以更快的确定一条记录，但是可能需要回表查询（至于什么是返回表，我就不赘述了，在上一篇文章中已经详细讲解了）。为经常查询的字段建立索引我们在建立索引的时候，需要为那些经常作为查询条件的字段建立索引，这样可以提高整个表的查询速度。但是查询条件一般不是一个字段，所以一般会建立更多的联合索引。另外，查询条件中一般会有like之类的模糊查询。如果是模糊查询，最好遵循最左前缀查询原则。避免为“大字段”建立索引这可以换句话说：尽量使用数据量小的字段作为索引。例如，假设有两个这样的字段，一个是varchar(5)，另一个是varchar(200)。这种情况下，最好是为varchar(5)的字段建一个索引，因为MySQL维护索引的时候，有时字段值是一起维护的，势必会导致索引占用更多的空间，而且会排序时需要更多时间进行比较。那么，如果要为varchar(100)创建索引怎么办？然后取一些数据，比如地址类型是varchar(200)，建索引的时候可以这样写：CREATEINDEXtbl_addressONdual(address(20));选择辨别度高的那个列做索引是什么意思？举个例子，相信大家一下子就明白了。假设现在有一个“性别”字段，里面存储的数据的值不是男就是女，那么这样的字段不适合作为索引。这种字段值的主要特点是区分度不够高，区分度低的字段不适合做索引。为什么？因为如果值出现的概率几乎相等，那么无论搜索哪个值，都可能得到一半的数据。在这些情况下，最好不要建立索引，因为MySQL也有一个查询优化器。当查询优化器发现某个值出现在表中高比例的数据行时，一般会忽略索引，进行全表查询。扫描。通常的百分比截止值为“30%”。（当匹配数据量超过一定限度时，查询者会放弃使用索引（这也是索引失效的场景之一）。就是这个道理。所以相信大家应该知道为什么要尝试避免使用基数小的字段作为Index，其实这涉及到MySQL的一个专有名词【Cardinality（索引基数）是mysql索引的一个很重要的概念】尝试为ORDERBY和GROUPBY后面的字段建立索引并且对OrderBy后面的字段进行索引，这样查询的时候就不需要再次排序了，因为我们都知道B+树中的记录都是建立索引后进行排序的，GROUPBY和ORDERBY其实是类似的，所以这两个放在一起。因为在GROUPBY中，首先要根据GROUPBY后面的字段进行排序，然后进行聚合操作。如果GROUPBY后面的字段没有排序，那么MySQL需要先排序在这次，so会生成一个临时表，排序后的临时表，然后在临时表中进行聚合操作。当然，这是非常低效的。如果GROUPBY后面的字段已经被索引了，那么MySQL就不需要去排序，不会生成临时表。但是如果GROUPBY的列和ORDERBY的列不一样，即使有索引也会生成临时表。其实我在网上搜了一下这些情况，好像有很多，这里给大家罗列一下。说实话，虽然这些都是标准，但是这个标准好像很难达到，因为实际场景肯定没有这么简单简单。如果GROUPBY的列没有索引，则会生成一个临时表。如果是GROUPBY，则SELECT列不止一个GROUPBY列，且GROUPBY列不是主键，生成临时表。如果GROUPBY列有索引，则ORDERBY列没有索引。生成一个临时表。如果GROUPBY列与ORDERBY列不同，即使两者都有索引，也会生成一个临时表。如果GROUPBY或ORDERBY的列不是来自JOIN语句的第一个表，则会生成一个临时表。如果DISTINCT和ORDERBY的列没有索引，则会生成一个临时表。GROUPBY和ORDERBY的列相同，都是主键。但是，如果SELECT列包含GROUPBY列以外的列，也会生成一个临时表。不要在条件中使用函数。如果已经建立索引的字段在使用时进行函数操作，则不会使用该索引。为什么是这样？因为MySQL为索引维护的B+树是基于字段的原始数据。如果在使用过程中增加了一个函数，MySQL不会认为这是原来的字段，肯定不会使用索引。但是如果有人固执己见，需要使用功能怎么办？我不能为索引更改业务？如果是因为使用了MySQL内部函数导致索引失效，那么可以在创建索引的同时创建该函数。这是什么意思？假设有一个字段叫age，并为它创建了一个索引，但是在使用的时候是这样的：SELECT*FROMstudentWHEREround(age)=2;这个时候并没有使用索引，所以如果是true如果你想让round(age)被索引，那么你可以创建一个像createindexstu_age_roundontest(round(age));这样的索引这时候如果通过上面的方法查询，索引就会生效。我相信每个人都能理解这一点。不要创建太多索引，因为在MySQL中维护索引需要空间和性能。MySQL会为每个索引字段维护一个B+树。所以如果索引过多，这无疑会增加MySQL的负担。很容易理解，频繁增删改查的字段不应该建立索引，因为我们前面介绍过，当字段发生变化时，MySQL需要重新维护索引。假设一个字段被频繁修改，就意味着需要频繁重建索引，这势必会影响MySQL的性能。这里我就不多说了。说到这里，大部分都是在讲设计时需要注意的一些原则。其实真正的原理还是需要根据实际业务进行更改。没有所谓的“公式”，只要适合自己实际业务场景的设计就是最好的。OK所以大家不要过分追求“优化”，因为往往会适得其反。毕竟不谈生意只谈技术就是耍流氓。好吧，让我们关注一下索引会失败的情况。（PS：本文基本都是理论，本来想画个图表达一下，结果发现根本无从下手，希望大家坚持下去，很快就结束了。）常见场景使用OR关键字的索引失败会导致索引失败，但是如果你想使用OR并且不想让索引失效，你需要为or条件下的每一列创建一个索引。这显然与上面不建太多索引的说法相悖。如果联合索引不遵循最左前缀原则，索引也会失效。在使用模糊查询时，以%开头也会导致索引失败（原因这里不再赘述，因为前面的文章都已经说了，这里是为了帮助大家再次回忆）如果索引列使用隐式转换，索引也会失效如果你这样写SELECT*FROMstudentWHEREage='15'，这种情况下不能使用索引，即age列的索引无效。如果字段的基数小，也可能导致索引失败，本文上半部分已经详细说明，这是MySQL查询优化器导致的。其他的一些原理，还是要看索引原理和查询的基本原理。没有之前的铺垫，这些就显得有些空洞了。所以请大家在索引部分一定要一步步学习。这部分基本上就是我们平时使用MySQL时的一些核心知识点。
