字段设计
- 优先选择符合存储需求的最小数据类型
字段越大,建索引需要的空间就越大,一页存储下的索引结点就越少,这样一次索引查找的io次数就会上升,导致索引性能变差
- 尽可能把所有列定义为not null
索引设计
索引的目的是,在磁盘io去提取数据之前,尽可能多地过滤不必要的数据,缩小磁盘io的“范围”
- 联合索引。一般区分度高的、使用最频繁的字段,放到联合索引的最左侧
- 前缀索引。索引列的字符串过长时,全部拿来建索引那么索引文件就会偏大,磁盘io上升。这时可以选择使用前n位建索引
存储引擎
- innodb使用B+tree索引
- 支持随机检索和顺序检索(顺序检索直接扫叶子结点)
- B+tree磁盘读写代价更低。因为其非叶子结点不存储指向具体信息的指针,在同样的磁盘占用下,B+tree能够存下更多的结点。当存储索引的磁盘块按页载入内存时,一页包含的结点更多,那么整个B+tree就更加矮胖。树的高度就是在一次索引上查找涉及的磁盘io次数,因此B+tree磁盘读写代价更低
- B+tree非常适合范围查找,遍历叶子结点即可
查询优化
- 如可以,尽量使用left join
or not exists
替换not in
,not in
通常不走索引
- 一条sql语句,where和order by都可以使用索引
- index_merge
. where子句中可能有多个条件(或者join)涉及到一张表的多个字段,它们之间进行 AND 或者 OR,就有可能会使用到 index merge 。index merge 其实就是对一张表的多个索引分别进行条件扫描,然后将它们各自的结果进行合并(intersect/union)
The Index Merge access method retrieves rows with multiple range
scans and merges their results into one. This access method merges index scans from a single table only, not scans across multiple tables. The merge can produce unions, intersections, or unions-of-intersections of its underlying scans
- 推荐使用union替代or
- IN clause.
expr
IN (value
,...)
optimization of IN expr:
- Common IN expr: The values the list are sorted and the search for
expr
is done using a binary search, which makes theIN()
operation very quick. - if expr is a specific column with a index, then 将根据in的范围进行索引扫描。the list are sorted,因此扫描边界很好判断
网友评论