浅谈索引及其使用

1、MySQL 为什么建议使用自增主键？

InnoDB 引擎是基于主键聚集的，数据记录是被存在于索引树的叶子结点上，这就要求索引树的叶子结点的各条记录按照主键排序，因此当有一个新的记录需要插入的时候，需要根据其主键的值插入叶子结点，如果叶子结点已满则开辟另一个数据页作为新的叶子结点。当我们使用自增主键时，记录按顺序被一条条加入索引树叶子结点中，形成紧凑的索引结构，每次插入记录时，不需要移动数据的开销，效率很高。 当我们使用非自增主键的时候，比如业务中的id,我们可以认为此时的主键id值为随机的，这样在插入数据的时候，需要不停的移动数据，甚至有的数据已经写入磁盘，又需要重新读磁盘再改写，同时该页面的缓存也失效，增加了开销，另一个缺点是不停的插入随机主键记录，使得页面分页形成大量的碎片页面，页面利用率降低。

2、索引的类型？

主键索引

唯一索引：唯一索引可以对单列或者多列（唯一组合索引）创建。

普通索引：普通索引也可以对单列或多列（普通组合索引）创建。

全文索引：这是搜索引擎常用的技术。

3、建立索引的原则

最左匹配原则：mysql 会一直向右匹配直到遇见范围查询（< > like）停止匹配,比如where a = 0 and b = 0 and c > 0 and d =0;会使得d = 0 无法匹配，所以无法利用到（a,b,c,d)组合索引，而如果是（a,b,d,c)是可以用到该组合索引的，a,b，d顺序是可以调整。

查询较多的字段建立索引

更改频繁的字段不要建立索引

尽量扩展索引 而不是新建索引

4、索引为什么使用B+树而不是使用B树？

B树节点不仅包括索引指针而且包涵数据部分，导致每个节点存储的索引相关数据的个数变少，从而索引树节点的出度变少，即B+树会比B树形成的索引树更加“矮胖”，所以B+树更适合做索引。同时B+树叶子结点形成单链表，适合范围查询。

5、MySQL 的InnoDB 和MyISAM,有什么区别？

InnoDB 支持事务，表锁行锁，由于InnoDB 是主键聚集的，所以索引树的叶子结点会直接包含整条数据记录，这样数据文件和索引结合在一起了。辅助索引叶子结点会包含主键，从而回表查询记录，这也就告诉我们主键最好是自增的，而不是自定义的大主键，因为所有普通索引都会在叶子结点包含主键，主键过大使得整个索引文件变大。⚠️InnoDB行锁是实现在索引上，而不是物理记录上，只有命中索引时才会加行锁，否则加表锁。InnoDB 不存储表的具体行数，因为不同事务看到的行数是不一样的，只能实时统计数。

MyISAM不支持事务，也不支持行锁，索引文件和数据文件分离，也就是说索引文件的叶子结点是指向数据的指针。MyISAM记录表的全局行数，所以select count(*) from xx ;操作会很快。

6、索引有哪些优缺点？

优点：提高检索记录的效率，同时对于order by group by join 等操作有加速作用。

缺点：删除、修改、增加等操作都需要动态维护索引树，同时索引的存储也会占用存储空间。

7、索引的数据结构？

InnoDB索引的数据结构有B+树和哈希表。

MyISAM 索引B+树。

8、三大范式

列的原子性

非主属性完全依赖主属性。消除部分依赖

非主属性只依赖于主属性，而不是依赖于其他非主属性。消除传递依赖
9 left join right join inner join？
不仅仅是 表的保留列 的问题 ，还有驱动表和被驱动表的问题，比如 A left join B on xxx 则A是作为驱动表，B是作为被驱动表， A right join B 则相反 ,A inner join B属于是mysql 通过数据量以及数据大小 进行驱动表的选择（小表）。总体来说驱动表是小表，需要加载到内存中控制循环在大表中查找相应符合条件数据的次数。
10 应该对哪些字段创建索引？
区分度较高的字段
尽量不要在text字段等数据单元比较大的字段上创建索引（理由是索引树会很占空间）
对于经常出现的where 查询条件的字段 设置索引