mysql 索引及其优化

索引相关理解：
1、索引是为了提高查询效率而出现的，像书的目录一样，是在存储引擎层实现的

2、索引的常见模型：哈希表，有序数组，搜索树。
2.1 哈希表适用于等值查询，因为不是有序的所以新增一个数时会比较快，查询一个范围时就必须全部扫描一遍
2.2 有序数组和哈希表不同，查询一个具体的值/范围比较方便 二分法可快速查找，插入一个数值时就比较慢，需要移动数据，适用于静态存储引擎
2.3 搜索树 有二叉搜索树和多叉搜索树，由于其特点查找和更新的时间复杂度都是O(log(N)), 对于数据量大的节点可采用N叉树，子节点大小由左向右依次递增，N叉树的N取决于数据块的大小

3、InnoDB中表是根据主键顺序以索引方式存放，称为索引组织表，
3.1 InnoDB中使用了B+树的索引模型，所以每个索引都对应一棵B+树，
3.2 根据树的叶子节点的内容分为主键索引（聚簇索引）和非主键索引（二级索引）。主键索引中存储的是对应的数据行 非主键索引中存储的是主键的id值
3.3 主键查询和非主键查询的区别是 只需要查主键这棵B+树就可以。非主键查询需要查到主键对应的id再用id从主键的索引树中查对应的行，此过程称为回表

4、索引的维护：
4.1 一般会采用自增主键 特殊情况会采用业务字段作为主键，如 表中就一个字段时
4.2 不用自增主键会出现的问题：业务字段较长其他索引存储主键 总体所占的空间会比较大。 插入数据时 可能会出现页分裂 影响数据页的利用
4.3

5、覆盖索引：使用覆盖索引可以减少树的搜索次数，优化查询语句，是一个常用的性能优化，即索引中包含想要返回的字段
– 遇到以下情况执行计划不会选择覆盖查询
1、select选择的字段中含有不在索引中的字段，即索引没有覆盖全部 的列
2、where条件中不能含有对索引进行like的操作

6、索引的设计：遵循最左前缀原则，建立联合索引时要根据左前缀原则考虑如何安排，索引内字段的顺序，如果可以少维护一个字段那么要优先考虑

7、索引下推：Mysql5.6之前找到联合索引中的第一个时就会回表查。 5.6之后会根据索引下推对索引包含的字段先做判断，过滤不满足条件的行。减少回表的次数

8、索引可能因为删除或者页分裂等原因 导致很多页空洞， 重建索引的过程中会创建一个新的索引，把数据按顺序插入，这样页面利用率就变得更高

重建索引：alter table T drop index k; alter table T add index(k);
重建主键的方式 alter table T engine=InnoDB

9、InnoDB索引和MyISAM索引区别：
9.1: 实现上都采用B+树做为索引结构，
9.2. InnoDB的数据文件本身就是索引文件；MyISAM索引文件和数据文件是分离的，其索引文件只保存数据记录的地址，InnoDB数据文件本身就是索引文件，这棵树的叶子节点的data域保存的是完整的数据记录，索引的key是表的主键
9.3. InnoDB的非聚集索引存储的data域存的是主键的值 而不是地址

10、explain extra中出现using filesort:
– mysql官方给到的解释：
If the Extra column of EXPLAIN output contains Using filesort, the index is not used and a filesort is performed（如果EXPLAIN输出的Extra列包含正在使用文件排序，则不使用索引，而是执行文件排序）
– 并不是说extra包含using filesort就会影响性能，对于有的order by没有命中filesort的慢查询，可以适当减小max_length_for_sort_data来使其命中filesort从而缩减慢查询时间；不过一般情况下还是建议优化掉using filesort因为没有命中引擎层的索引导致了外部排序

顺便了解下Innodb和Myisam引擎的区别：
1、事务支持，myisam强调性能，每次查询具有原子性速度更快，但是不提供事务支持；Innodb提供事务支持具有事务、回滚和崩溃修复的能力
2、表锁差异：my是只支持表级锁，crud是会自动加上表锁；in可支持行锁，不过当where条件为主键时才会支持
3、表主键：my允许没有任何索引和主键的存在；in必须有主键，如果没有指定会自动生成一个
4、表的行数：my可以直接从变量中取；in取行数需要扫表
5、crud操作：select 多可用my; update和insert多可选用in
6、外键支持：my不支持；in支持
7、可移植性备份及恢复：my中数据是以文件存储，跨平台数据转移比较方便；in可以拷贝数据文件，备份binlog，不过数据量比较大时就很不方便