MySQL索引设计需要考虑哪些因素？

索引小知识

篇幅有限，索引的基本知识我们就不赘述了，在此，我们尝试说明其中的一个小点-----b+树与b树的区别到底是什么。

innodb是使用b+树来实现其索引功能的。在b+树中，内节点（非叶子节点）存储了行数据的键，而叶子节点存储了所有的行数据，而b树的每个节点都存储了真实的数据。这种数据结构，决定了两者有以下不同点：

（1）非叶子节点能存放指针的数据量。因为b树的非叶子节点存放的是整行的数据，占用了较多的空间，所以能存放指针就相对较少，因此整个b树的层数就变高。当数据量比较大时，插入更新会导致维护代价也是比较大的，而且层数越高，搜索的性能就会越低。而b+树的内节点存放的是相对短很多的键值，就克服了b树遇到的问题。

（2）从数据结构上来看，b树的查询效率与数据所在的位置有关。即如果所要搜索的数据节点，在树上的位置，越靠近根节点，查询返回结果越快，最差的就是数据位于叶子节点上，不同的节点位置，其性能不均衡；而b+树，完整的数据都是在叶子节点上，其查询效率是固定的。插入、删除操作同样的原理，在b树中，其复杂度明显增加，而b+树相对简单的多。例如，b+树，在插入过程中，只需要通过在每一层搜索一个节点，依次找到节点之后，在节点处插入即可（节点满，则分裂）。

（3）b树中，所有的数据只存储了一份；而b+树，除了存储了所有数据的叶子节点外，还要在内节点存储了键值。所以，在空间占用方面，b+树会比b树多些。

（4）在一个表中，聚族索引占用的空间肯定是最大的，因为它存储了全部数据，而二级索引是建立在某几个经常查询的列上（还有用来“回表”的指针），所以，二级索引的占用空间都会比聚族索引小很多。

索引设计原则

（1）mysql 表主键设计

innodb 以主键排序存储；聚集索引只能是主键；存储所有数据；二级索引包含主键键值。

如果表没有定义主键，会选择第一个唯一索引（非空）作为聚集索引主键。如果唯一索引也没有，mysql后台会自动生成rowid。

字符类型字段最好不要做主键；常见的主键有两种：自增列和uuid。

自增：  顺序存储，索引维护成本低，索引效率高；

uuid：非顺序增长，随机io严重。

（2）索引并不是越多越好，要根据查询，有针对性的创建，考虑在where和order by命令上涉及的列建立索引，可根据explain来查看是使用了索引还是全表扫描；

（3）应尽量避免在where子句中对字段进行null值判断，否则将导致引擎放弃使用索引而进行全表扫描；

（4）值分布很稀少的字段不适合建索引，例如“性别”这种只有两三个值的字段；

（5）不用外键，由程序保证约束；

（6）尽量不用unique，由程序保证约束；

（7）使用多列索引时主意顺序和查询条件保持一致，同时删除不必要的单列索引。

（8）排序时，排序字段需要注意index， 尤其是关联查询排序时，尽可能使用小表的字段进行排序

sql 优化 原则

（1）避免属性隐试转换 ， 如定义moblie varchar  where moblie =198989888会导致全表扫描；

（2）where子句中条件字段本身避免使用函数；

（3）使用获取的必要字段代替select *；

（4）批量插入，使用insert into table (col1,col2,...) values (value1, value2,...),(value1, value2,...); 插入多条数据只有一次提交；

（5）避免使用长事务；

（6）禁止负向查询： not、！=、<>、！<、！>、not in、not like，会导致全表扫描；

（7）大表之间的join，尽量缩小结果集之后再join，否则会消耗较多的内存和cpu；

（8）搜索严禁左模糊或者全模糊（like %xx, 或like %xx%），会导致全表扫描。