目录
18、案例实战：陌生人社交APP的MySQL索引设计实战（一）
19、案例实战：陌生人社交APP的MySQL索引设计实战（二）
20、案例实战：陌生人社交APP的MySQL索引设计实战（三）
21、案例实战：陌生人社交APP的MySQL索引设计实战（四）
写在前面
数据库的索引原理以及查询原理
1、深入研究索引之前，先来看看磁盘数据页的存储结构
磁盘上的数据文件中的数据页的物理存储结构：
数据库最终所有的数据（包括我们建的各种表以及表里的数据）都是要存放在磁盘上的文件里的，然后在文件里存放的物理格式就是数据页，那么大量的数据页在磁盘文件里是怎么存储的？
大量的数据页是按顺序一页一页存放的，然后两两相邻的数据页之间会采用双向链表的格式互相引用：
其实一个数据页在磁盘文件里就是一段数据，可能是二进制或者别的特殊格式的数据，然后数据页里包含两个指针，一个指针指向自己上一个数据页的物理地址，一个指针指向自己下一个数据页的物理地址。
DataPage: xx=xx, xx=xx, linked_list_pre_pointer=15367, linked_list_next_pointer=34126 ||DataPage: xx=xx, xx=xx, linked_list_pre_pointer=23789, linked_list_next_pointer=46589 ||DataPage: xx=xx, xx=xx, linked_list_pre_pointer=33198, linked_list_next_pointer=55681
每个数据页在磁盘文件里都是连续的一段数据。
然后一个数据页内部会存储一行一行的数据，也就是平时我们在一个表里插入的一行一行的数据就会存储在数据页里，然后数据页里的每一行数据都会按照主键大小进行排序存储，同时每一行数据都有指针指向下一行数据的位置，组成单向链表。
2、假设没有任何索引，数据库是如何根据查询语句搜索数据的？
数据页之间是组成双向链表的，然后数据页内部的数据行是组成单向链表的，而且数据行是根据主键从小到大排序的。 然后每个数据页里都会有一个页目录，里面根据数据行的主键存放了一个目录，同时数据行是被分散存储到不同的槽位里去的，所以实际上每个数据页的目录里，就是这个页里每个主键跟所在槽位的映射关系（因为一个槽位含多个数据行，个人理解是多个主键对应一个槽位即多个数据行）
假设数据库里那个表就没几条数据，那个表总共就一个数据页。
- 根据主键查找一条数据，首先就会先到数据页的页目录里根据主键进行二分查找，然后通过二分查找在目录里迅速定位到主键对应的数据是在哪个槽位里，然后到那个槽位里去，遍历槽 位里每一行数据，就能快速找到那个主键对应的数据了。每个槽位里都有一组数据行，你就是在里面遍历查找就可以了。
- 根据非主键的其他字段查找数据，此时你是没办法使用主键的那种页目录来二分查找的，只能进入到数据页里，根据单向链表依次遍历查找数据了，这就性能很差了。
正常一个表里往往都是有大量数据的，可能有多达成百上千个数据页，这些数据页就存放在物理磁盘文件里。
- 假设没有建立任何索引，那么无论是根据主键查询，还是根据其他字段来条件查询，直接从第一个数据页开始遍历所有数据页，从第一个数据页开始，你得先把第一个数据页从磁盘上读取到内存buffer pool的缓存页里来。然后你就在第一个数据页对应的缓存页里，按照上述办法查找。
-
根据主键查找的，你可以在数据页的页目录里二分查找。
-
根据其他字段查找的，只能是根据数据页内部的单向链表来遍历查找。 假设第一个数据页没找到你要的那条数据呢？ 只能根据数据页的双向链表去找下一个数据页，然后读取到 buffer pool 的缓存页里去，然后按 一样的方法在一个缓存页内部查找那条数据。 如果依然还是查找不到呢？ 那只能根据双向链表继续加载下一个数据页到缓存页里来了，以此类推，循环往复。
3、不断在表中插入数据时，物理存储是如何进行页分裂的
在一个表里不停的插入数据的时候，会涉及到一个页分裂的过程，也就是说这个表里是如何出现一个又一个的数据页的。 正常情况下我们在一个表里插入一些数据后，他们都会进入到一个数据页里去，在数据页内部，他们会组成一个单向链表。
里面就是一行一行的数据，刚开始第一行是个起始行，他的行类型是2，就是最小的一行，然后他有一个指针指向了下一行数据，每一行数据都有自己每个字段的值，然后每一行通过一个指针不停的指向下一行数据，普通的数据行的类型都是0，最后一行是一个类型为3的，就是代表最大的 一行。
页分裂是什么意思呢？
你不停的在表里插入数据，那么刚开始是不是就是不停的在一个数据页插入数据？接着数据越来越多，越来越多，此时就要再搞一个数据页了。
- 如果你的主键是自增的，那还可以保证这一点，因为你新插入后一个数据页的主键值一定都大于前一个数据页的主键值。
- 如果你的主键并不是自增长的，所以可能会出现你后一个数据页的主键值里，有的主键是小于前一个数据页的主键值的。
举例：
假设新数据页里，有两条数据的主键值明显是小于上一个数据页的主键值。
此时就会出现页分裂的行为，把新数据页里的两条数据挪动到上一个数据页，上一个数据页里挪两条数据到新数据页里去。
页分裂的核心目标就是保证下一个数据页里的主键值都比上一个数据页里的主键值要大。
4、基于主键的索引是如何设计的，以及如何根据主键索引查询？
索引索引------》索引原理----〉查询原理-----》SQL调优
针对主键设计一个索引：针对主键的索引实际上就是主键目录，就是把每个数据页的页号，还有数据页里最小的主键值放在一起，组成一个索引的目录。
查找过程：此时查找直接就可以到主键目录里去搜索，比如你要找id=3的数据，此时就会跟每个数据页的最小主键来比，首先id=3大于了数据页2里的最小主键值1，接着小于了数据页8里的最小主键值4。所以既然如此，你直接就可以定位到id=3的数据一定是在数据页2里的！
5、索引的页存储物理结构，是如何用B+树来实现的？
正常表里的数据可能很多很多，比如有几百万，几千万，甚至单表几亿条数据都是有可能的，所以此时你可能有大量的数据页，然后你的主键目录里就要存储大量的数据页和最小主键 值，这怎么行呢？
所以实际上是采取了一种把索引数据存储在数据页里的方式来做的。也就是说，你的表的实际数据是存放在数据页里的，表的索引也是存放在页里的，此时索引放在页里之后，就会有索引页，假设你有很多很多的数据页，那么此时你就可以有很多的索引页。
又会存在一个问题了，你现在有很多索引页，但是此时你需要知道，你应该到哪个索引页里去找你的主键数据，是索引页20？还是索引页28？
此时，假设我们要查找id=46的，直接先到最顶层的索引页35里去找，直接通过二分查找可以定位到下一步应该到索引页20里去找，接下来到索引页20里通过二分查找定位，也很快可以定位到数据应该在数据页8里，再进入数据页8里，就可以找到id=46的那行数据了。
假如你最顶层的那个索引页里存放的下层索引页的页号也太多了，怎么办呢？
有发现索引页不知不觉中组成了多个层级，搞的是不是有点像一棵树？ 这就是一颗B+树，属于数据结构里的一种树形数据结构，所以一直说MySQL的索引是用B+树来组成的。
主键索引来举例：
当你为一个表的主键建立起来索引之后，其实这个主键的索引就是一颗B+树，然后当你要根据主键来查数据的时候，直接就是从B+树的顶层开始二分查找，一层一层往下定位，最终一直定位到一个数据页里，在数据页内部的目录里二分查找，找到那条数据。 这就是索引最真实的物理存储结构，采用跟数据页一样的页结构来存储，一个索引就是很多页组成的一颗B+树。
6、更新数据的时候，自动维护的聚簇索引到底是什么？
假设我们要搜索一个主键id对应的行，此时你就应该先去顶层的索引页88里去找，通过二分查找的方式，很容易就定位到你应该去下层哪个索引页里继续找。
定位到了下层的索引页35里去继续找，此时在索引页35里也有一些索引条目的，分别都是下层各个索引页（20，28，59）和他们里面最小的主键值，此时在索引页35的索引条目里继续二分查找，很容易就定位到，应该再到下层的哪个索引页里去继续找。
可能从索引页35接着就找到下层的索引页59里去了，此时索引页59里肯定也是有索引条目的，这里就存放了部分数据页页号（比如数据页2和数据页8）和每个数据页里最小的主键值。此时就在这里继续二分查找，就可以定位到应该到哪个数据页里去找。
比如进入了数据页2，里面就有一个页目录，都存放了各行数据的主键值和行的实际物理位置 此时在这里直接二分查找，就可以快速定位到你要搜索的主键值对应行的物理位置，然后直接在数据页2里找到那条数据即可了。
索引页自己内部，对于一个层级内的索引页，互相之间都是基于指针组成双向链表的。这个同一层级内的索引页组成双向链表，就跟数据页自己组成双向链表是一样的。
假设你把索引页和数据页综合起来看，他们都是连接在一起的，看起来就如同一颗完整的大的B+树一样，从根索引页88开始，一直到所有的数据页，其实组成了一颗巨大的B+树。在这颗B+树里，最底层的一层就是数据页，数据页也就是B+树里的叶子节点了！
7、针对主键之外的字段建立的二级索引，又是如何运作的？
聚簇索引：innodb存储引擎默认给我们创建的一套基于主键的索引结构，而且我们表里的数据就是直接放在聚簇索引里的，作为叶子节点的数据页。
- 一方面 会把完整数据插入到聚簇索引的叶子节点的数据页里去，同时维护好聚簇索引。
- 另一方面 会为你其他字段建立的索引，重新再建立一颗B+树。
8、插入数据时到底是如何维护好不同索引的B+树的？
- 开始建立一个表其实他就一个数据页，这个数据页就是属于聚簇索引的一部分，而且目前还是空的。此时你插入数据，就是直接在这个数据页里插入就可以了。
2、然后
这个初始的数据页其实就是一个根页，每个数据页内部默认就有一个基于主键的页目录，所以
此时你根据主键来搜索都是
ok
没有问题的，直接在唯一 一个数据页里根据页目录找就行了。
然后你表里的数据越来越多了，此时你的数据页满了，那么就会搞一个新的数据页，然后把你根页面里
的数据都拷贝过去，同时再搞一个新的数据页，根据你的主键值的大小进行挪动，让两个新的数据页根
据主键值排序，第二个数据页的主键值都大于第一个数据页的主键值。
3、此时根页就升级为索引页了，这个根页里放的是两个数据页的页号和他们里面最小的主键值，所以此时看起来如下图，根页就成为了索引页，引用了两个数据页。
5、数据页越来越多，那么根页指向的索引页也会不停分裂，分裂出更多的索引页，当你下层的索引页数量太多的时候，会导致你的根页指向的索引页太多了，此时根页继续分裂成多个索引页，根页再次往上提上去去一个层级。
9、一个表里是不是索引搞的越多越好？那你就大错特错了！
- 空间上而言：你要是给很多字段创建很多的索引，那你必须会有很多棵索引B+树，每一棵B+树都要占用很多的磁盘空间！所以你要是搞的索引太多了，是很耗费磁盘空间的。
- 时间上：搞了很多索引，那么你在进行增删改查的时候，每次都需要维护各个索引的数据有序性， 因为每个索引B+树都要求页内是按照值大小排序的，页之间也是有序的，下一个页的所有值必须大于上一个页的所有值！
- 所以你不停的增删改查，必然会导致各个数据页之间的值大小可能会没有顺序，比如下一个数据页里插入了一个比较小的值，居然比上一个数据页的值要小！此时就没办法了，只能进行数据页的挪动，维护页之间的顺序。
- 或者是你不停的插入数据，各个索引的数据页就要不停的分裂，不停的增加新的索引页，这个过程都是耗费时间的。所以你要是一个表里搞的索引太多了，很可能就会导致你的增删改的速度就比较差了，也许查询速度确实是可以提高，但是增删改就会受到影响，因此通常来说，我们是不建议一个表里搞的索引太多的。
10、深入理解联合索引查询原理以及全值匹配规则
然后你就直接找到索引指向的那个数据页就可以了，在数据页内部本身也是一个单向链表，你也是直接就做二分查找就可以了，先按1班这个值来找，你会发现几条数据都是1班，此时就可以按照张小强这个姓名来二分查找，此时会发现多条数据都是张小强，接着就按照科目名称数学来二分查找。很快定位到下图中的一条数据，1班的张小强的数学科目，他对应的数据的id是127。
11、几个最常见和最基本的索引使用规则
- 等值匹配规则：就是你where语句中的几个字段名称和联合索引的字段完全一样，而且都是基于等号的等值匹配，那百分百会用上我们的索引。
- select * from student_score where class_name='1班' and student_name='张小强' and
subject_name=' 数学 ' 。
- select * from student_score where class_name='1班' and student_name='张小强' and
- 最左侧列匹配：假设我们联合索引是KEY(class_name, student_name, subject_name)，那么不一定必须要在where语句里根据三个字段来查，其实只要根据最左侧的部分字段来查也可。
- select * from student_score where class_name='' and student_name=''，就查某个学生所有科目的成绩，这都是没有问题的。
- select * from student_score where subject_name=''，那就不行了，因为联合索引的B+树里，是必须先按class_name查，再按student_name查，不能跳过前面两个字段，直接按最后一个subject_name查的。
- select * from student_score where class_name='' and subject_name=''，那么只有class_name的值可以在索引里搜索，剩下的subject_name是没法在索引里找的。
- 最左前缀匹配原则：即如果你要用like语法来查，比如select * from student_score where class_name like '1%'，查找所有1打头的班级的分数，那么也是可以用到索引的。因为你的联合索引的B+树里，都是按照class_name排序的，所以你要是给出class_name的确定的最左前缀就是1，然后后面的给一个模糊匹配符号，那也是可以基于索引来查找的，这是没问题的。但是你如果写class_name like '%班'，在左侧用一个模糊匹配符，那他就没法用索引了，因为不知道你最左前缀是什么，怎么去索引里找啊？
- 范围查找规则：我们可以用select * from student_score where class_name>'1班' and class_name<'5班'这样的语句来范围查找某几个班级的分数。 此时也会用到索引，因为我们的索引的最下层的数据页都是按顺序组成双向链表的，所以完全可以先找到'1班'对应的数据页，再找到'5班'对应的数据页，两个数据页中间的那些数据页，就全都是在你范围内的数据了！
- select * from student_score where class_name>'1班' and class_name<'5班' and student_name>''，这里只有class_name是可以基于索引来找的，student_name的范围查询是没法用到索引的！
- 这也是一条规则，就是你的where语句里如果有范围查询，那只有对联合索引里最左侧的列进行范围查询才能用到索引！
- 等值匹配+范围匹配的规则：如果你要是用select * from student_score where class_name='1班' and student_name>'' and subject_name<''，那么此时你首先可以用class_name在索引里精准定位到一波数据，接着这波数据里的student_name都是按照顺序排列的，所以student_name>''也会基于索引来查找，但是接下来的subject_name<''是不能用索引的。
12、当我们在SQL里进行排序的时候，如何才能使用索引？
- 如果都是升序排列，直接就从索引树里最小的开始读取一定条数就可以了。
- 如果都是降序排列，就是从索引树里最大的数据开始读取一定的条数就可以了。
- 你不能order by语句里有的字段升序有的字段降序，那是不能用索引的。
- 要是你order by语句里有的字段不在联合索引里，或者是你对order by语句里的字段用了复杂的函数，这些也不能使用索引去进行排序了。
13、当我们在SQL里进行分组的时候，如何才能使用索引？
总结
平时设计表里的索引的时候，必须充分考虑到后续你的SQL语句要怎么写，大概会根据哪些字段来进行where语句里的筛选和过滤？大概会根据哪些字段来进行排序和分组？然后在考虑好之后，就可以为表设计两三个常用的索引，覆盖常见的where筛选、order by排序和group by分组的需求，保证常见的SQL语句都可以用上索引，这样你真正系统跑起来，起码是不会有太大的查询性能问题了。
- 一个是你索引别太多，索引太多了，更新的时候维护很多索引树肯定是不行的；
- 一个是可能会涉及到一些锁等待和死锁的问题；
- 一个就是可能会涉及到MySQL连接池、写redo log文件之类的问题。
14、回表查询对性能的损害以及覆盖索引是什么？
不管是单列索引还是联合索引，其实一个索引就对应着一颗独立的索引B+树，索引B+树的节点仅仅包含了索引里的几个字段的值以及主键值。
- 有时MySQL的执行引擎甚至可能会认为，类似select * from table order by xx1,xx2,xx3的语句相当于是得把联合索引和聚簇索引，两个索引的所有数据都扫描一遍了，那还不如就不走联合索引了，直接全表扫描得了，这样还就扫描一个索引而已。
- select * from table order by xx1,xx2,xx3 limit 10这样的语句，那执行引擎就知道了，你先扫描联合索引的索引树拿到10条数据，接着对10条数据在聚簇索引里查找10次就可以了，那么就还是会走联合索引的。
15、设计索引的时候，我们一般要考虑哪些因素呢？（上）
设计索引的时候，我们通常应该考虑哪些因素，给哪些字段建立索引，如何建立索引，建立好索引之后应该如何使用才是最合适的。
- 首先，我们在针对业务需求建立好一张表的结构之后，就知道这个表有哪些字段，每个字段是什么类型的，会包含哪些数据。
- 接着，设计好表结构之后就要设计表的索引，要考虑第一点就是未来我们对表进行查询的时候，大概会如何来进行查询？
- 此时，我们完全可以在表结构设计完毕之后，先别急着设计索引，因为此时你根本不知道要怎么查询表。
- 接着，我们就可以进入系统开发的环节，也就是说根据需求文档逐步逐步的把你的Java业务代码给写好。在写代码的过程中，现在一般我们都是用MyBatis作为数据持久层的框架的，你肯定会写很多的 MyBatis的DAO和Mapper以及SQL吧？
- 当系统差不多开发完毕了，功能都跑通了，此时你就可以来考虑如何建立索引了，因为你的系统里所有的MyBatis的SQL语句都已经写完了，你完全知道对每一张表会发起些什么样的查询语句。
- 第一个索引设计原则：针对你的SQL语句里的where条件、order by条件以及group by条件去设计索引 。也就是说，你的where条件里要根据哪些字段来筛选数据？order by要根据哪些字段来排序？group by要根据哪些字段来分组聚合？ 此时可以设计一个或者两三个联合索引，每一个联合索引都尽量去包含上你的where、order by、group by里的字段，接着你就要仔细审查每个SQL语句，是不是每个where、order by、group by后面跟的字段顺序，都是某个联合索引的最左侧字段开始的部分字段？
- 比如你有一个联合索引是INDEX(a,b,c)，此时你一看发现有三个SQL，包含了where a=? and b=?，order by a,b，group by a这些部分，那么此时where、order by、group by后续跟的字段都是联合索引的最左侧开始的部分字段，这就可以了，说明你的每个SQL语句都会用上你的索引了。
16、设计索引的时候，我们一般要考虑哪些因素呢？（中）
首先一个就是字段基数问题，举个例子，有一个字段他一共在10万行数据里有10万个值。这个10万值，要不然就是0要不然就是1，那么他的基数就是2，为什么？因为这个字段的值就俩选择，0和1。假设你要是针对上面说的这种字段建立索引的话，那就还不如全表扫描了，因为你的索引树里就仅仅包含0和1两种值，根本没法进行快速的二分查找，也根本就没有太大的意义了，所以这种时候，选用这种基数很低的字段放索引里意义就不大了。
- 一般建索引，尽量使用那些基数比较大的字段，就是值比较多的字段，那么才能发挥出B+树快速二分查找的优势来。
- 其次，尽量对那些字段的类型比较小的列来设计索引，比如说什么tinyint之类的，因为他的字 段类型比较小，说明这个字段自己本身的值占用磁盘空间小，此时你在搜索的时候性能也会比较好一点。 很多时候你就是要针对varchar(255)这种字段建立索引，哪怕多占用一些磁盘空间，那你也得去设计这样的索引，比较关键的其实还是尽量别把基数太低的字段包含在索引里，因为意义不是太大。
- 如果真的有那种varchar(255)的字段，可能里面的值太大了，你觉得都放索引树里太占据磁盘空间了，此时你仔细考虑了一下，发现完全可以换一种策略，也就是仅仅针对这个varchar(255)字段的前20个字符建立索引，就是说，对这个字段里的每个值的前20个字符放在索引树里而已。
- 此时你建立出来的索引其实类似于KEY my_index(name(20),age,course)，就这样的一个形式，假设name是varchar(255)类型的，但是在索引树里你对name的值仅仅提取前20个字符而已。
- 此时你在where条件里搜索的时候，如果是根据name字段来搜索，那么此时就会先到索引树里根据name字段的前20个字符去搜索，定位到之后前20个字符的前缀匹配的部分数据之后，再回到聚簇索引提取出来完整的name字段值进行比对就可以了。
- 但是假如你要是order by name，那么此时你的name因为在索引树里仅仅包含了前20个字符，所以这个排序是没法用上索引了！group by也是同理的。
- 对于那种字段基数很低的列尽量别包含到索引里去，没多大用；
- 对于那种比较长的字符串类型的列，可以设计前缀索引，仅仅包含部分字符到索引树里去，where查询还是可以用的 ，但是order by和group by就用不上了。
17、设计索引的时候，我们一般要考虑哪些因素呢？（下）
- 插入数据肯定有主键，那有主键就得更新聚簇索引树，你插入一条数据肯定会包含索引里各个字段的值，那你的联合索引的B+树也要更新。你不停的增删改数据，就会不停的更新你的索引树。
- 所以因为你插入的数据值可能根本不是按照顺序来的，很可能会导致索引树里的某个页就会自动分裂。
- 这个页分裂的过程就很耗费时间，因此一般让大家设计索引别太多，建议两三个联合索引就应该覆盖掉表的全部查询了。
- 否则索引太多必然导致你增删改数据的时候性能很差，因为要更新多个索引树。另外很关键一点，建议大家主键一定是自增的，别用UUID之类的，因为主键自增，那么起码你的聚簇索引不会频繁的分裂，主键值都是有序的，就会自然的新增一个页而已，但是如果你用的是UUID，那么也会导致聚簇索引频繁的页分裂。
18、案例实战：陌生人社交APP的MySQL索引设计实战（一）
案例背景：
针对社交APP的用户信息表user_info进行筛选，该表会包含你的地区（你在哪个省份、哪个城市，这个很关键，否则不在一个城市，可能线上聊的好， 线下见面的机会都没有），性别，年龄，身高，体重，兴趣爱好，性格特点，还有照片，最近一次在线时间（否则半年都不上线APP了，你把他搜出来干什么呢？），另外如果支持交友过程中让其他人对他进行评价，那么可能还需要包含这个人的一个综合评分。
需求：
除了select xx from user_info where xx=xx 有一系列的条件之外，APP肯定得支持分页展示，所以肯定还得跟上limit xx,xx的分页语句。 总得根据一定的规则对筛选出来的结果进行一个排序，把最符合你的条件和期望的用户排列在最上面。
最终SQL 语句可能类似： select xx from user_info where xx=xx order by xx limit xx,xx。
19、案例实战：陌生人社交APP的MySQL索引设计实战（二）
问题1:
在where和order by出现索引设计冲突，到底是针对where去设计索引，还是针对order by设计索引？到底是让where去用上索引，还是让order by用上索引？问题本质是要让where语句先基于联合索引去进行一个筛选，筛选出来一部分用户指定的数据，接着再把数据加载到内存或者是基于临时磁盘文件去进行指定条件的排序，最后用limit 语句拿到一页数据吗？还是说要让order by语句按照你的索引的顺序去找，找的过程中基于where里的条件筛选出来指定的数据，然后再根据limit语句拿出来一页数据？
一般这种时候往往都是让where条件去使用索引来快速筛选出来一部分指定的数据，接着再进 行排序，最后针对排序后的数据拿出来一页数据。 因为基于索引进行where筛选往往可以最快速度筛选出你要的少部分数据，如果筛选出来的数据量不是太大的话，那么后续排序和分页的成本往往不会太大！
问题2:
搜索潜在好友的时候，一般会用上哪些条件呢？到底要把哪些字段包含到索引里去？到底在联合索引里，字段的顺序要如何排列呢？
之前说过基数太低的字段最好别放到索引里去，那省份、城市和性别都是基数非常小的几个字段，可选的值就那么几个，为什么要放到索引里去？
假设因为省份、城市和性别几个字段的基数太小了，此时就不把他们几个包含到联合索引里去，那么你实际查询的时候都要基于这几个字段去搜索，此时你就只能把这几个字段放在where条件的最后，那么最后每次查询都必须要先用联合索引查询出来一部分数据，接着数据加载到内存里去，再根据where条件最后的省份、城市和性别几个字段进行过滤筛选。 所以与其如此，还不如就把省份、城市和性别三个字段，放在联合索引的最左侧，这样跟其他字段组合联合索引后，让大部分的查询都可以直接通过索引树就可以把where条件指定的数据筛选出来了。
20、案例实战：陌生人社交APP的MySQL索引设计实战（三）
目前的联合索引为（province, city, sex）
问题3:
假设查询的时候，不指定性别，就指定了省份，城市和年龄，也就是说where province=xx and city=xx and age between xx and xx，那么此时怎么办呢？因为age不在索引里，所以就根本没法通过age去在索引里进行筛选了。
where province=xx and city=xx and sex in ('female', 'male') and age >=xx and age<=xx。
where province=xx and city=xx and sex in(xx, xx) and hobby in (xx, xx, xx, xx) and character=xx and age>=xx and age<=xx
- 比如上面的语句where province=xx and city=xx and sex in(xx, xx) and hobby in (xx, xx, xx, xx) and character=xx and age>=xx and age<=xx，他们完全是按照联合索引最左侧开始的，province、city、sex、hobby、character都是联合索引最左侧开始的多个字段，他们都是等值匹配，然后最后一个age字段使用的是范围匹配，这种就是可以完全用上索引的。
- 假设你要是在联合索引里把age放在中间的位置，设计一个类似（province, city, sex, age, hobby,character）的联合索引，接着SQL写成where province=xx and city=xx and sex in(xx, xx) and age>=xx and age<=xx and hobby in (xx, xx, xx, xx) and character=xx的话，此时只有province, city, sex, age几个字段可以用上索引。
21、案例实战：陌生人社交APP的MySQL索引设计实战（四）
目前的联合索引为（province, city, sex, hobby, character, age）
关键是要让最频繁查询的一些条件都放到索引里去，然后在查询的时候如果有些字段是不使用的，可以用in (所有枚举值)的方式去写，这样可以让所有查询条件都用上你的索引，同时对范围查询的age字段必须放在最后一个，这样保证范围查询也能用上索引。
问题6:
登录过APP的用户，那么实际上可能你的用户表里有这么一个字段latest_login_time，你要是在where条件里加入这么一个latest_login_time <= 7天内语句，肯定这个是没法用上索引了。
- 因为这里必然会用一些计算或者是函数，才能进行一些时间的比对。
- 而且假设你的查询里还有age进行范围查询，并且范围查询时也就只有第一个范围查询是可以用上索引的，第一个范围查询之后的其他范围查询是用不上索引的。
- 也就是说，即使你索引设计成这样：（province, city, sex, hobby, character, age, latest_login_time），然后where语句写成这样：where xx xxx and age>=xx and age<=xxx and latest_login_time>=xx，虽然age和latest_login_time都在联合索引里，但是按照规则，只有age范围查询可以用到索引，latest_login_time始终是用不到索引的。
select xx from user_info where sex='female' order by score limit xx,xx
9405
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
