我们在开始一个项目之前, 一般先进行需求分析, 根据项目需求建立对应的数据库, 再进行代码编写. 而数据库建立的好坏也会影响到系统的运行, 因此在设计数据库的时候就应该考虑进行优化.
本人才疏学浅, 在设计数据库中用到的优化方式主要有以下几种:
- 适当设置冗余
- 创建索引
- 利用 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 > allsystem: 表只有一行记录, const 的特例;const: 使用唯一索引等价查询，仅能匹配到一条数据, 如使用主键查询;eq_ref: 使用唯一索引作为关联条件，匹配多条不重复数据ref: 普通索引查询range: 检索给定范围的索引 ， > 、< 、>= 、<=、between andindex: 扫描全部索引, 与 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分析查询语句, 对语句进行优化或建立索引, 尽量让查询语句走索引;
其他优化方式:
- 子查询优化, 使用连接查询代替子查询, (子查询会产生临时表);
- 尽可能只查询需要的字段;
- 利用覆盖索引进行查询操作,避免回表, 即建立多个查询字段的联合索引;
- 避免使用左模糊或全模糊查询;
如有错误, 欢迎指正;
参考文章:
扫一扫
952
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
