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

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

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

适当设置冗余
创建索引
利用 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

Wu_Shang001