MYSQL索引

索引定义

使用索引可快速访问数据库表中的特定信息。索引是对数据库表中一列或多列的值进行排序的一种结构。类似于书籍的目录。

约束和索引有什么区别？
约束是对表数据的一种规范，保证数据完整性。包括①主键约束 ②外键约束 ③唯一约束 ④默认约束 ⑤检查约束等。
索引是为了提高查询效率的一种结构。约束和索引是两种不同概念的东西。
唯一索引允许多个NULL值吗？
唯一索引允许NULL，且允许多个NULL值。
NULL 的定义 ，是指未知值。 所以多个 NULL，都是未知的，不能说它们是相等的，也不能说是不等，就是未知的。所以多个NULL的存在是不违反唯一约束的。

InnoDB 和 MyISAM

1、InnoDB 支持事务，MyISAM 不支持事务。这是 MySQL 将默认存储引擎从 MyISAM 变成 InnoDB 的重要原因之一
2、InnoDB 支持外键，而 MyISAM 不支持。对一个包含外键的 InnoDB 表转为 MYISAM 会失败
3.、InnoDB 是聚集索引，MyISAM 是非聚集索引。聚簇索引的文件存放在主键索引的叶子节点上，因此 InnoDB 必须要有主键，通过主键索引效率很高。但是辅助索引需要两次查询，先查询到主键，然后再通过主键查询到数据。因此，主键不应该过大，因为主键太大，其他索引也都会很大。而 MyISAM 是非聚集索引，数据文件是分离的，索引保存的是数据文件的指针。主键索引和辅助索引是独立的
4、InnoDB 最小的锁粒度是行锁，MyISAM 最小的锁粒度是表锁。一个更新语句会锁住整张表，导致其他查询和更新都会被阻塞，因此并发访问受限。这也是 MySQL 将默认存储引擎从 MyISAM 变成 InnoDB 的重要原因之一
5、InnoDB 不保存表的具体行数，执行 select count(*) from table 时需要全表扫描。而MyISAM 用一个变量保存了整个表的行数。
6、MyISAM支持全文类型索引，而InnoDB不支持全文索引
7、MyISAM相对简单，效率上要优于InnoDB，小型应用可以考虑使用MyISAM
8、MyISAM表保存成文件形式，跨平台使用更加方便

聚簇索引和非聚簇索引
通俗的讲：
● 聚簇索引：将数据存储与索引放到了一块，找到索引也就找到了数据
● 非聚簇索引：将数据存储于索引分开结构，索引结构的叶子节点指向了数据的对应行
聚簇索引就是按照每张表的主键构造一颗B+树，叶子节点中存放该行数据。
（1）每张表只能拥有一个聚簇索引。
（2）Innodb聚簇索引默认是主键，如果没有定义主键，innodb会选择第一个非空且唯一索引代替。如果没有，innodb会隐式的定义一个主键来作为聚簇索引。

innodb聚簇索引和辅助索引

MyISAM主键索引和普通索引

为了更形象说明这两种索引的区别，我们假想一个表如下图存储了4行数据。其中Id作为主索引，Name作为辅助索引。图示清晰的显示了聚簇索引和非聚簇索引的差异。

主键索引是聚集索引还是非聚集索引？
在Innodb下主键索引是聚集索引，在Myisam下主键索引是非聚集索引。
为什么主键推荐使用int自增？
用非单调的字段作为主键在InnoDB中不是个好主意，因为InnoDB数据文件本身是一颗B+Tree，非单调的主键会造成在插入新记录时数据文件为了维持B+Tree的特性而频繁的分裂调整，十分低效，而使用自增字段作为主键则是一个很好的选择。
为什么不建议使用过长字段作为主键？
InnoDB 使用聚簇索引。所有辅助索引都会会包含主键列，所以如果主键定义的比较大，其他索引也将很大。如果一个表有很多索引，尽量把主键定义得小一些。
主键用完了怎么办？
会报错Duplicate entry ‘2147483647’ for key ‘PRIMARY’，自增主键用完之后，无法继续新增数据。
可以使用bigint类型，超级大，不太可能用完。

辅助索引（二级索引）
在聚簇索引之上创建的索引称之为辅助索引，辅助索引访问数据总是需要二次查找。辅助索引叶子节点存储的是主键值，通过主键值找到对应的数据。

几种特殊索引机制

回表和覆盖索引
辅助索引叶子节点存储主键的值，使用辅助索引搜索数据会先从辅助索引取到主键的值，再使用主键的值去主键索引上查询，直到找到叶子节点上的数据返回。——这个过程称之为回表

如辅助索引上已经存在我们需要的数据,那么引擎就不会去主键上去搜索数据了。 ——这就是覆盖索引
explain时Extra显示"Using index"，说明使用了覆盖索引。

索引下推
mysql5.6优化，针对组合索引，可以在索引遍历过程中，对索引中包含的字段先做判断，过滤掉不符合条件的记录，减少回表次数。
explain时Extra显示"Using index condition"，说明使用了索引下推。

SELECT * from user where  name like '陈%' and age=20

5.6之前

5.6及以后

压缩索引
● MyISAM 中使用了前缀压缩技术，会减少索引的大小，可以在内存中存储更多的索引，这部分优化默认也是只针对字符串的，但是可以自定义对整数做压缩
● 这个优化在一定情况下性能比较好，但是对于某些情况可能会导致更慢，因为前缀压缩决定了每个关键字都必须依赖于前面的值，所以无法使用二分查找等，只能顺序扫描，所以如果查找的是逆序那么性能可能不佳

索引和锁
● InnoDB 支持行锁和表锁，默认使用行锁，而 MyISAM 使用的是表锁，所以使用索引可以让查询锁定更少的行，这样也会提升查询的性能，如果查询中锁定了1000行，但实际只是用了100行，那么在 5.1 之前都需要提交事务之后才能释放这些锁，5.1 之后可以在服务器端过滤掉行之后就释放锁，不过依然会导致一些锁冲突

数据碎片
● 首先我们需要了解一下为什么会产生碎片，比如 InnoDB 删除数据时，这一段空间就会被留空，如果一段时间内大量删除数据，就会导致留空的空间比实际的存储空间还要大，这时候如果进行新的插入操作时，MySQL 会尝试重新使用这部分空间，但是依然无法彻底占用，这样就会产生碎片
● 产生碎片带来的后果当然是，降低查询性能，因为这种情况会导致随机磁盘访问
● 可以通过 OPTIMIZE TABLE 或者重新导入数据表来整理数据

索引失效