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

2019-01-02 14:16:48来源:cnblogs.com作者:东山絮柳仔人点击

分享

索引小知识

篇幅有限,索引的基本知识我们就不赘述了,在此,我们尝试说明其中的一个小点-----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%),会导致全表扫描。

相关文章

    无相关信息

最新文章

123

最新摄影

闪念基因

微信扫一扫

第七城市微信公众平台