主键、外键和索引相关
定义
-
主键：
唯一标识一条记录，不能有重复的，不允许为空。
主键只能有一个(组成主键的整体，复合主键算一个主键)。 -
外键：
表的外键是另一表的主键, 外键可以有重复的, 可以是空值。
一个表可以有多个外键。 -
索引：
该字段没有重复值，但可以有一个空值。
一个表可以有多个唯一索引。
作用：
-
主键：
用来保证数据完整性。 -
外键：
用来和其他表建立联系用的。 -
索引：
是提高查询排序的速度。
索引是一种特殊的文件(InnoDB数据表上的索引是表空间的一个组成部分)，它们包含着对数据表里所有记录的引用指针。
普通索引(由关键字KEY或INDEX定义的索引)的唯一任务是加快对数据的访问速度。
普通索引允许被索引的数据列包含重复的值。如果能确定某个数据列将只包含彼此各不相同的值，在为这个数据列创建索引的时候就应该用关键字UNIQUE把它定义为一个唯一索引。也就是说，唯一索引可以保证数据记录的唯一性。
主键，是一种特殊的唯一索引，在一张表中只能定义一个主键索引，主键用于唯一标识一条记录，使用关键字 PRIMARY KEY 来创建。
索引可以覆盖多个数据列，如像INDEX(columnA, columnB)索引，这就是联合索引。
索引可以极大的提高数据的查询速度，但是会降低插入、删除、更新表的速度，因为在执行这些写操作时，还要操作索引文件。
索引的目的
快速访问数据表中的特定信息，提高检索速度。
创建唯一性索引，保证数据库表中每一行数据的唯一性。
加速表和表之间的连接。
使用分组和排序子句进行数据检索时，可以显著减少查询中分组和排序的时间。
索引对数据库系统的负面影响
负面影响：
创建索引和维护索引需要耗费时间，这个时间随着数据量的增加而增加；
索引需要占用物理空间，不光是表需要占用数据空间，每个索引也需要占用物理空间；
当对表进行增、删、改、的时候索引也要动态维护，这样就降低了数据的维护速度。
为数据表建立索引的原则
在最频繁使用的、用以缩小查询范围的字段上建立索引。
在频繁使用的、需要排序的字段上建立索引
什么情况下不宜建立索引
对于查询中很少涉及的列或者重复值比较多的列，不宜建立索引。
对于一些特殊的数据类型，不宜建立索引，比如文本字段（text）等。
索引基数
基数是数据列所包含的不同值的数量，例如，某个数据列包含值1、3、7、4、7、3，那么它的基数就是4。
索引的基数相对于数据表行数较高（也就是说，列中包含很多不同的值，重复的值很少）的时候，它的工作效果最好。
如果某数据列含有很多不同的年龄，索引会很快地分辨数据行；如果某个数据列用于记录性别（只有“M”和“F”两种值），那么索引的用处就不大；如果值出现的几率几乎相等，那么无论搜索哪个值都可能得到一半的数据行。
在这些情况下，最好根本不要使用索引，因为查询优化器发现某个值出现在表的数据行中的百分比很高的时候，它一般会忽略索引，进行全表扫描。惯用的百分比界线是“30%”。
索引失效原因
-
对索引列运算。
运算包括（+、-、*、/、！、<>、%、like’%_'（%放在前面）。 -
类型错误。
如字段类型为varchar，where条件用number。 -
对索引应用内部函数。
这种情况下应该要建立基于函数的索引。
例如 select * from template t where ROUND (t.logicdb_id) = 1，此时应该建ROUND (t.logicdb_id)为索引，MySQL8.0开始支持函数索引，5.7可以通过虚拟列的方式来支持，之前只能新建一个ROUND (t.logicdb_id)列然后去维护。 -
条件有or。
如果条件有or，即使其中有条件带索引也不会使用（这也是为什么建议少使用or的原因），如果想使用or，又想索引有效，只能将or条件中的每个列加上索引；(数据类型出现隐式转化,如:varchar不加单引号的话可能会自动转换为int型）。 -
如果列类型是字符串，那一定要在条件中数据使用引号，否则不使用索引。
-
B-tree索引 is 不会走，is not 会走，位图索引 is ，is not 都会走。
-
组合索引遵循最左原则。
索引的建立
- 最重要的肯定是根据业务经常查询的语句；
- 尽量选择区分度高的列作为索引，区分度的公式是 COUNT(DISTINCT col) / COUNT(*)，表示字段不重复的比率，比率越大我们扫描的记录数就越少；
- 如果业务中唯一特性最好建立唯一键，一方面可以保证数据的正确性，另一方面索引的效率能大大提高。
提高性能的特性
-
索引覆盖(covering index)：
需要查询的数据在索引上都可以查到不需要回表 EXTRA列显示using index。 -
ICP特性(Index Condition Pushdown)：
本来index仅仅是data access的一种访问模式，存数引擎通过索引回表获取的数据会传递到MySQL Server层进行where条件过滤。5.6版本开始当ICP打开时，如果部分where条件能使用索引的字段，MySQL Server会把这部分下推到引擎层，可以利用index过滤的where条件在存储引擎层进行数据过滤。EXTRA显示using index condition。需要了解MySQL的架构图分为Server和存储引擎层。 -
索引合并(index merge)：
对多个索引分别进行条件扫描，然后将它们各自的结果进行合并(intersect/union)。一般用OR会用到，如果是AND条件，考虑建立复合索引。EXPLAIN显示的索引类型会显示index_merge，EXTRA会显示具体的合并算法和用到的索引。
extra字段
- using filesort：
说明MySQL会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。MySQL中无法利用索引完成的排序操作称为“文件排序”，其实不一定是文件排序，内部使用的是快排； - using temporary：
使用了临时表保存中间结果，MySQL在对查询结果排序时使用临时表。常见于排序order by和分组查询group by； - using index：
表示相应的SELECT操作中使用了覆盖索引（Covering Index），避免访问了表的数据行，效率不错； - impossible where：
WHERE子句的值总是false，不能用来获取任何元组； - select tables optimized away：
在没有GROUP BY子句的情况下基于索引优化MIN/MAX操作或者对于MyISAM存储引擎优化COUNT(*)操作，不必等到执行阶段再进行计算，查询执行计划生成的阶段即完成优化； - distinct：
优化distinct操作，在找到第一匹配的元组后即停止找同样值的操作。
using filesort、using temporary这两项出现时需要注意下，这两项是十分耗费性能的，在使用group by的时候，虽然没有使用order by，如果没有索引，是可能同时出现using filesort，using temporary的，因为group by就是先排序在分组，如果没有排序的需要，可以加上一个order by 来避免排序，这样using filesort就会去除，能提升一点性能。
type字段
- system：
表只有一行记录（等于系统表），这是const类型的特例，平时不会出现； - const：
如果通过索引依次就找到了，const用于比较主键索引或者unique索引。因为只能匹配一行数据，所以很快。如果将主键置于where列表中，MySQL就能将该查询转换为一个常量； - eq_ref：
唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描； - ref：
非唯一性索引扫描，返回匹配某个单独值的所有行。本质上也是一种索引访问，它返回所有匹配某个单独值的行，然而它可能会找到多个符合条件的行，所以它应该属于查找和扫描的混合体； - range：
只检索给定范围的行，使用一个索引来选择行。key列显示使用了哪个索引，一般就是在你的where语句中出现between、<、>、in等的查询，这种范围扫描索引比全表扫描要好，因为只需要开始于缩印的某一点，而结束于另一点，不用扫描全部索引； - index：
Full Index Scan ，index与ALL的区别为index类型只遍历索引树，这通常比ALL快，因为索引文件通常比数据文件小，也就是说虽然ALL和index都是读全表，但index是从索引中读取的，而ALL是从硬盘读取的； - all：
Full Table Scan，遍历全表获得匹配的行。
