数据库设计之冗余、索引以及查询优化

我们在开始一个项目之前, 一般先进行需求分析, 根据项目需求建立对应的数据库, 再进行代码编写. 而数据库建立的好坏也会影响到系统的运行, 因此在设计数据库的时候就应该考虑进行优化.

本人才疏学浅, 在设计数据库中用到的优化方式主要有以下几种:

  • 适当设置冗余
  • 创建索引
  • 利用 explain 分析优化查询

适当设置冗余

设置冗余是指在一个数据表中添加另一个数据表的关联字段, 减少join连接查询. 适当牺牲部分存储换取查询效率.

例如: 有一个学生信息表 student(id, sno, name, major_id) 和 专业信息表 major (id, major_name), 当经常需要查询学生详细的时候, 我们这样写 sql 语句:

select s.id, sno, name, major_name from student as s join major as m on s.major_id = m.id; 

但是如果我们在student表中添加冗余字段major_name时, 学生信息表就变成了 student(id, sno, name, major_id, major_name), 查询语句就是

select id, sno, name, major_name from student;

这样就不需要再通过连接查询了, 可以直接查出学生的专业名.

注意: 设置冗余字段的时候需要考虑数据同步问题, 即当专业名修改后, 所有的冗余专业名也必须修改.

所以设置的冗余字段需要满足以下条件:

  • 该字段必须不会频繁修改
  • 不是varchar超长字段, (阿里开发手册, 出于存储空间的考虑)

建立索引

通过建立索引能够使数据库根据索引指向位置, 快速定位到数据在磁盘上的位置, 而不是扫描一条一条数据.(索引上会保存当前列的值)

数据库索引主要有两种:

  • 聚集索引: 索引直接指向数据在磁盘中的位置, 一张表只能有一个聚集索引, 一般是主键索引.
  • 稀疏索引: (二级索引), 索引不仅存储了索引列的值, 还存储主键的值. 查询非索引列则需要再通过主键索引查找.

概括地有点不太好, 再通过具体栗子解释一下:

有学生信息表:

create table `student`(
    `id` int unsigned not null auto_increment,
    `sno` char(10) not null,    -- 学号长度固定, 可以使用 char
								-- 索引列不能为 null, 否则不会走索引
    `name` varchar(32) not null,
    `major_id` smallint unsigned not null,
    `major_name` varchar(32) not null,  -- 设置的冗余字段
    primary key (`id`),
    unique key `uk_sno` (`sno`)       	-- 第一个是索引名字, 括号内是设置的索引列
);

在这张表中, 有主键索引(聚集索引) id 和 在sno建立的非聚集索引.
有两条查询语句:

select * from student where id = 1;

select * from student where sno= 'xxxxxxxxxx';

其中第一条语句根据主键id直接找到数据在磁盘上的位置, 而第二条语句则通过 ukx_sno 索引找到sno为xxxxxxxxxx的id的值, 再通过主键找到数据的位置.

联合索引

上述例子的索引只包含一列, 而联合索引是多个字段按照一定顺序建立的索引.

如以下建表语句:

drop table if exists `student`;
create table `student`(
    `id` int unsigned not null auto_increment,
    `sno` char(10) not null,
    `name` varchar(32) not null,
    `academy_id` smallint unsigned not null,
    `major_id` smallint unsigned not null,
    `clazz_id` smallint unsigned not null comment '班级id',      -- class 是java中的关键字, 所以直接用clazz了
    primary key (`id`),
    unique key `uk_sno` (`sno`),
    index `idx_academy_major_clzzs_sno` (`academy_id`, `major_id`, `clazz_id`, `sno`)
);

索引idx_academy_major_clzzs_sno就是先按照academy_id字段顺序组织, academy_id相同则按major_id组织, 依次类推.

最左匹配原则: 查询数据时, 从联合索引的最左边开始匹配, 遇到范围查询(>, <, between, like)就会停止匹配.

select sno from student where academy_id = 1 and clazz_id = 1 and major_id > 1 ;
# where 后面的顺序不影响匹配顺序, mysql 会自动优化

clazz_id用不到索引.

联合索引还有一个好处, 就是如果你需要查询的字段恰好就在索引列里面, 就不会再使用到主键索引去查数据表 (回表), 而是直接返回.

: 建立索引的字段也是要保证不会经常修改, 否则索引也会被经常修改, 降低性能;

使用 explain 分析优化查询

优化查询, 我们需要了解一个工具explain, 用来分析sql语句性能的命令, 可以帮助优化查询语句.

用法: explain + 查询语句,如

explain select * from student where id = 1;

结果如下:

           id: 1
  select_type: SIMPLE
        table: student
   partitions: NULL
         type: const
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: const
         rows: 1
     filtered: 100.00
        Extra: NULL

相关解释:

  • id: 查询的序列号, 表示执行select子句的顺序, 如果是子查询, id序号递增, id越大优先级越高;
  • select_type: 查询类型, 有以下几种
    • SIMPLE: 简单的 select 查询
    • PRIMARY: 查询中包含任何复杂的子部分, 最外层为 PRIMARY
    • SUBQUERY: 包含的子查询
    • DERIVED: 衍生查询, 一般是产生的临时表, 如在 from 列表中包含的子查询
    • UNION: 联合查询, t1 UNION t2 中的 t2
    • UNION RESULT: 联合结果
  • table: 当前执行的表
  • type: 查询使用了哪种类型, 最好到最差依次是:
    system > const > eq_ref > ref > range > index > all
    
    • system: 表只有一行记录, const 的特例;
    • const: 使用唯一索引等价查询,仅能匹配到一条数据, 如使用主键查询;
    • eq_ref: 使用唯一索引作为关联条件,匹配多条不重复数据
    • ref : 普通索引查询
    • range: 检索给定范围的索引 , > 、< 、>= 、<=、between and
    • index: 扫描全部索引, 与 all 的区别是 index 只遍历索引树;
    • all: 遍历全表找到匹配的行;
  • possible_keys: 显示可能用到的索引;
  • key: 实际使用到的索引, NULL 表示没有用到索引(可能没有建立或索引失效);
  • key_len: 表示索引中使用的字节数, 可计算查询中使用的索引长度, 在不损失精确性的情况下, 长度越短越好;
  • ref: 实现索引的哪一列被使用了;
  • row: 大致估算找到数据所需要读取的行数, 越小越好;
  • Extra: 包括不适合在其他列中显示但比较重要的额外信息
    • Using filesort: 性能消耗大,需要额外一次排序(查询)
    • Using temporary:: 性能消耗大,用到临时表,常见于排序和分组查询
    • Using index: 性能提升,索引覆盖,此查询仅查询索引不需要回表查询
    • Using where: 在查找使用索引的情况下,需要回表去查询所需的数据
    • Using join buffer:连接缓存
    • Using where; Using index:查找使用了索引,但是需要的数据都在索引列中能找到,所以不需要回表查询数据

一般查询级别至少达到range级别, 要求是ref;

在优化查询的时候, 可以先通过explain分析查询语句, 对语句进行优化或建立索引, 尽量让查询语句走索引;

其他优化方式:

  • 子查询优化, 使用连接查询代替子查询, (子查询会产生临时表);
  • 尽可能只查询需要的字段;
  • 利用覆盖索引进行查询操作,避免回表, 即建立多个查询字段的联合索引;
  • 避免使用左模糊或全模糊查询;

如有错误, 欢迎指正;

参考文章:

数据库索引创建与优化
MySQL的EXPLAIN解释器

已标记关键词 清除标记
相关推荐
©️2020 CSDN 皮肤主题: 编程工作室 设计师:CSDN官方博客 返回首页