javaguide学习-数据库
参考《剖析面试最常见问题之MySQL》
索引的区分
聚簇索引和非聚簇索引
MyISAM 引擎中，B+Tree 叶节点的 data 域存放的是数据记录的地址。在索引检索的时候，首先按照 B+Tree 搜索算法搜索索引，如果指定的 Key 存在，则取出其 data 域的值，然后以 data 域的值为地址读取相应的数据记录。这被称为“非聚簇索引”。
InnoDB 引擎中，其数据文件本身就是索引文件。相比 MyISAM，索引文件和数据文件是分离的，其表数据文件本身就是按 B+Tree 组织的一个索引结构，树的叶节点 data 域保存了完整的数据记录。这个索引的 key 是数据表的主键，因此 InnoDB 表数据文件本身就是主索引。这被称为“聚簇索引（或聚集索引）”，而其余的索引都作为辅助索引，辅助索引的 data 域存储相应记录主键的值而不是地址，这也是和 MyISAM 不同的地方。在根据主索引搜索时，直接找到 key 所在的节点即可取出数据；在根据辅助索引查找时，则需要先取出主键的值，在走一遍主索引。 因此，在设计表的时候，不建议使用过长的字段作为主键，也不建议使用非单调的字段作为主键，这样会造成主索引频繁分裂。
聚集索引和非聚集索引
聚集索引即索引结构和数据一起存放的索引。主键索引属于聚集索引。
非聚集索引即索引结构和数据分开存放的索引。
二级索引属于非聚集索引。
二级索引又称为辅助索引，是因为二级索引的叶子节点存储的数据是主键。也就是说，通过二级索引，可以定位主键的位置。
唯一索引，普通索引，前缀索引等索引属于二级索引。
创建索引的注意事项
1.选择合适的字段创建索引：
- 不为 NULL 的字段 ：索引字段的数据应该尽量不为 NULL，因为对于数据为 NULL 的字段，数据库较难优化。如果字段频繁被查询，但又避免不了为 NULL，建议使用 0,1,true,false 这样语义较为清晰的短值或短字符作为替代。
- 被频繁查询的字段 ：我们创建索引的字段应该是查询操作非常频繁的字段。
- 被作为条件查询的字段 ：被作为 WHERE 条件查询的字段，应该被考虑建立索引。
- 频繁需要排序的字段 ：索引已经排序，这样查询可以利用索引的排序，加快排序查询时间。
- 被经常频繁用于连接的字段 ：经常用于连接的字段可能是一些外键列，对于外键列并不一定要建立外键，只是说该列涉及到表与表的关系。对于频繁被连接查询的字段，可以考虑建立索引，提高多表连接查询的效率。
2.被频繁更新的字段应该慎重建立索引。
虽然索引能带来查询上的效率，但是维护索引的成本也是不小的。
如果一个字段不被经常查询，反而被经常修改，那么就更不应该在这种字段上建立索引了。
3.尽可能的考虑建立联合索引而不是单列索引。
因为索引是需要占用磁盘空间的，可以简单理解为每个索引都对应着一颗 B+树。如果一个表的字段过多，索引过多，那么当这个表的数据达到一个体量后，索引占用的空间也是很多的，且修改索引时，耗费的时间也是较多的。如果是联合索引，多个字段在一个索引上，那么将会节约很大磁盘空间，且修改数据的操作效率也会提升。
4.注意避免冗余索引 。
冗余索引指的是索引的功能相同，能够命中索引(a, b)就肯定能命中索引(a) ，那么索引(a)就是冗余索引。如（name,city ）和（name ）这两个索引就是冗余索引，能够命中前者的查询肯定是能够命中后者的 在大多数情况下，都应该尽量扩展已有的索引而不是创建新索引。
5.考虑在字符串类型的字段上使用前缀索引代替普通索引。
前缀索引仅限于字符串类型，较普通索引会占用更小的空间，所以可以考虑使用前缀索引带替普通索引。
使用索引的一些建议
- 对于中到大型表索引都是非常有效的，但是特大型表的话维护开销会很大，不适合建索引
- 避免 where 子句中对字段施加函数，这会造成无法命中索引。
- 在使用 InnoDB 时使用与业务无关的自增主键作为主键，即使用逻辑主键，而不要使用业务主键。
- 删除长期未使用的索引，不用的索引的存在会造成不必要的性能损耗 MySQL 5.7 可以通过查询 sys 库的 schema_unused_indexes 视图来查询哪些索引从未被使用
- 在使用 limit offset 查询缓慢时，可以借助索引来提高性能
扩展
ps：图示innnodb和myisam的差异，并讲解为什么要使用自增主键
总结
-
InnoDB使用聚集索引，数据记录本身被存于主索引的叶子节点上，这就要求同一个叶子节点内的各条数据记录按主键顺序存放，因此每当一条新的记录插入时，MySQL会根据其主键将其插入适当的节点和位置，如果页面达到装载因子，则开辟一个新的页（节点）如果表使用自增主键，那么每次插入新的记录时，记录就会顺序添加到当前索引节点后续位置，当一页写满，就会自动开辟一个新的页。这样就就会形成一个紧凑的索引结构，近似顺序填满，由于每次插入时也不需要移动所有数据，因此效率很高，也不会增加很多额外的开销维护索引。
-
如果使用非自增主键，由于每次插入主键的值近乎于随机，因此每次新纪录都要被插到现有索引页的中间某个位置，此时MySQL不得不为了将新纪录插到合适位置而移动数据，甚至目标页面可能已经被写到磁盘而从缓存中清除，这增加了很多额外开销，同时频繁的移动，分页造成了大量的碎片，得到不够紧凑的索引结构，后续不得不通过OPTIMIZE TABLE来重建并优化填充页面。
-
由于MySQL从磁盘读取数据时一块一块来读取的，同时，根据局部性原理，MySQL引擎会选择预读一部分和你当前读数据所在内存相邻的数据块，这个时候这些相邻数据块的数据已经存在于内存中。由于数据库大部分是查询操作，这个时候，如果主键是自增的话，数据存储都是紧凑地存储在一起的，那么对于局部性原理利用和避免过多地I/O操作都有着巨大的促进作用
区别度较低的字段作为索引有什么问题？
尽量选择区分度高的列作为索引，区分度的公式是count(distinct col)/count(*)，表示字段不重复的比例，比例越大我们扫描的记录数越少，唯一键的区分度是1，而一些状态、性别字段可能在大数据面前区分度就是0，那可能有人会问，这个比例有什么经验值吗？使用场景不同，这个值也很难确定，一般需要join的字段我们都要求是0.1以上，即平均1条扫描10条记录。
关键应该还是 区分度小，就是加了索引，需要扫描的行数还是很多，效率不高，但是建索引是需要代价的。
MySQL索引原理及慢查询优化
ps：通俗易懂，值得阅读
从实践的角度讲解 磁盘io在b+树的查找过程，访问磁盘的成本大概是访问内存的十万倍左右
单调自增主键可以有效的利用磁盘读取的局部性原理，减少io次数
索引字段越小，每个磁盘块存储的记录就越多，那么io次数越少
null的情况索引为什么失效？？？？
面试官：说说MySQL 中NULL和空值的区别？
03 总结提升
如果你可以从上面的几个方面和面试官进行一个沟通，即使回答的不是那么的完美，但总比 “这两个都用过，具体有啥区别就不知道了” 这样的回答能好那么一点点。
1、空值不占空间，NULL值占空间。当字段不为NULL时，也可以插入空值。
2、当使用 IS NOT NULL 或者 IS NULL 时，只能查出字段中没有不为NULL的或者为 NULL 的，不能查出空值。
3、判断NULL 用IS NULL 或者 is not null,SQL 语句函数中可以使用IFNULL()函数来进行处理，判断空字符用 =''或者<>''来进行处理。
4、在进行count()统计某列的记录数的时候，如果采用的NULL值，会别系统自动忽略掉，但是空值是会进行统计到其中的。
5、MySql中如果某一列中含有NULL，那么包含该列的索引就无效了。这一句不是很准确。
6：实际到底是使用NULL值还是空值('')，根据实际业务来进行区分。个人建议在实际开发中如果没有特殊的业务场景，可以直接使用空值。
以上就是我的对此问题的整理和思考，希望可以在面试中帮助到你。如果你对此话题有自己的思考和理解，也欢迎留言一起探讨！
ps：结论值得关注
由此，只要列中包含有NULL值都将不会被包含在索引中，复合索引中只要有一列含有NULL值，那么这一列对于此复合索引就是无效的。所以我们在数据库设计时不要让字段的默认值为NULL。这句的前半句是不对的（可参考官网说明： https://dev.mysql.com/doc/refman/5.7/en/is-null-optimization.html），但是后半句的结论确是可以采纳的。
虽然MySQL可以在含有null的列上使用索引，但不代表null和其他数据在索引中是一样的。不建议列上允许为空，最好限制 not null ，并设置一个默认值，比如0和''空字符串等，如果是datetime类型，可以设置成'1970-01-01 00:00:00'这样的值。对MySQL来说，null 是一个特殊的值，Conceptually, NULL means “a missing unknown value” and it is treated somewhat differently from other values。 对null做算术运算的结果都是null，count时不会包括null行，null 比空字符串需要更多的存储空间等。
附：上面说到可用通过 key_len 看出使用了索引列的个数，a,b 都是 int 类型，4 byte，为什么 key_len 是 5 byte 和 10 byte 呢？是因为如果索引列定义时允许NULL，其key_len还需要再加 1 bytes. 参考好友王的文章，可以移步我们的站点查看详
