sql优化

基数

单个列唯一键的数量叫做基数, 基数的高低影响列的数据分布 .

MariaDB [test]> select count(distinct gender),count(distinct phone) from test;
+------------------------+-----------------------+
| count(distinct gender) | count(distinct phone) |
+------------------------+-----------------------+
|                      2 |                     7 |
+------------------------+-----------------------+
1 row in set (0.00 sec)

test表的总行数是7, gender列的基数是2, 说明gender列里面有大量的重复值, phone列的基数等于总行数, 说明phone列没有重复值.

MariaDB [test]> select gender,count(*) from test group by gender order by 2 desc;
+--------+----------+
| gender | count(*) |
+--------+----------+
|      1 |        4 |
|      2 |        3 |
+--------+----------+
2 rows in set (0.00 sec)

gender列的分布如上, gender为1有4条数据, 从7条数据里查询4条数据, 返回表中超过50%的数据.

有一个结论: 如果一个列基数很低, 该列数据分布不均衡, 由于该列数据分布极不均衡, 会导致SQL查询可能走索引, 也可能走全表扫描.

在做SQL优化时, 如果怀疑该列数据分布不均衡, 我们可以使用select列, count(*) from 表 group by 列 order by 2 desc来查看列的数据分布.

 

选择性

基数与总行数的比值再乘100%就是一个列的选择性. 当一个列出现在where条件中, 该列没有创建索引并且选择性大于20%时, 那么该列必须创建索引, 从而提升SQL查询性能.

只有在大表才会产生性能问题.

回表

当对一个列创建索引之后, 索引会包含该列的键值及键值对应行所在的rowid. 通过索引中记录的rowid访问表中的数据就叫回表. 回表一般是单块读, 回表次数太多会严重影响sql性能, 如果回表次数太多, 就不应该走索引扫描, 应该直接走全表扫描.

当要查询的列也包含在索引中, 这个时候就不需要回表, 所以我们往往会建立组合索引来消除回表, 从而提升性能.

当一个sql有多个过滤条件但是只有一个列或者部分列建立索引, 这个时候会出现回表再过滤, 也需要创建组合索引, 进而消除回表再过滤, 从而提升性能.