MYSQL数据库索引设计的原则

索引说明

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%），会导致全表扫描。