除了SQL语句的优化,从开发和运维层面来讲,硬件配置、MySQL配置、数据表设计、索引优化才是数据查询优化的重要手段。业务查询通常会要求将查询时间控制到0.5s之内,但是实际作为分析师来讲,“慢查询”是常态,但是也得尽量了解数据库优化原理,降低查询速度,也能提升产出效率。
- 表设计的合理化
- 添加适当的索引
- 分表技术(水平分割、垂直分割)
- 读写分离[写: update/delete/add]分离
- 存储过程[模块化编程,可以提高速度]
- 对mysql配置优化
- 定期清除碎片数据
表设计合理化
表的设计一般都要求是符合3NF (范式),即1NF:列的原子性;2NF:记录的唯一性;3NF:表不出现冗余
产品研发一般设计的表的时候都是符合3范式的,但是数据分析的时候一般不从业务库中直接取数,而是从数仓中取数,数仓的很多表数据存在大量冗余,但这个恰恰是我们所需要的,业务库中的表可允许适当冗余,但是数仓中则要求表能对业务尽量描述完整,冗余字段是很有必要的,能大大降低查询时间。
添加适当索引
索引(在MySQL中也叫“键key”)是存储引擎快速找到记录的一种数据结构。
四种索引(主键索引/唯一索引/全文索引/普通索引)
- 主键索引
当一张表,把某个列设为主键的时候,则该列就是主键索引。如果用auto_increment 设置了自增列,这个自增列必须为主键,否则会报错。一个表只能有一个主键。
--建表的时候添加主键
create table aaa(
id int unsigned primary key auto_increment ,
name varchar(32) not null defaul ‘’
);
--建表之后添加主键
alter table 表名 add primary key (列名);
- 唯一索引
一般来说,普通索引的创建,是先创建表,然后在创建普通索引,允许有空值。
create table ccc(
id int unsigned,
name varchar(32)
)
create index 索引名 on 表 (列1,列名2);
- 全文索引
全文索引,主要是针对对文件,文本的检索, 比如文章, 全文索引针对MyISAM有用.
CREATE TABLE articles (
id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
title VARCHAR(200),
body TEXT,
FULLTEXT (title,body)
)engine=myisam charset utf8;
如何使用全文索引:语法 match(字段名..) against(‘关键字’)
--错误用法
select * from articles where body like ‘%mysql%’; 【不会使用到全文索引】
--正确用法
select * from articles where match(title,body) against(‘database’); 【可以】
- 普通索引
①当表的某列被指定为unique约束时,这列就是一个唯一索引
--建表时创建唯一字段
create table ddd(
id int primary key auto_increment ,
name varchar(32) unique
);
--建表后创建唯一字段
create unique index 索引名 on 表名 (列表..);
name 列就是一个唯一索引.unique字段可以为NULL,并可以有多个NULL, 但是如果是具体内容,则不能重复.
主键字段,不能为NULL,也不能重复.
索引的代价:
- 1. 占用磁盘空间
- 2. 对dml操作有影响,变慢
什么时候创建索引:
- 肯定在where条经常使用
- 该字段的内容不是唯一的几个值(sex)
- 字段内容不是频繁变化.
复合索引
--索引作用在两列上
alter table dept add index my_ind (dname,loc); // dname 左边的列,loc就是右边的列
--复合索引的使用
select * from dept where adname = 'aaa' and loc='aaa'\G --使用到
select * from dept where loc='aaa'\G --使用不到
mysql中复合索引遵循索引最左匹配原则,复合索引要想使用第二个索引,必须先使用第一个索引,而且第一个索引必须是等值匹配。
查看索引使用情况
show status like ‘Handler_read%’;
- handler_read_key:这个值越高越好,越高表示使用索引查询到的次数。
- handler_read_rnd_next:这个值越高越差,说明查询低效。数据文件中读取下一行的请求数,如果正在进行大量的表扫描,值将较高,则说明索引利用不理想。
分表技术
- 水平分表:核心思想:把一个大表,分割N个小表,小表和大表结构一样,只是把数据分散到不同的表中。
- 垂直分表:如果一张表某个字段,信息量大,但是我们很少查询,则可以考虑把这些字段,单独的放入到一张表中,这种方式称为垂直分割.
Mysql本身没有对限制表的最大记录数,它取决于操作系统对文件大小的限制。
读写分离
读写分离,主数据库处理事务性增、改、删操作(INSERT、UPDATE、DELETE),而从数据库处理SELECT查询操作。
一般来说都是通过 主从复制(Master-Slave)的方式来同步数据,再通过读写分离(MySQL-Proxy)来提升数据库的并发负载能力 这样的方案来进行部署与实施的。
对mysql配置优化
- max_connections:MySQL实例的最大连接数,上限值是16384
- max_user_connections:每个数据库用户的最大连接数。
MySQL会为每个连接提供缓冲区,意味着消耗更多的内存。如果连接数设置太高硬件吃不消,太低又不能充分利用硬件。一般要求两者比值超过10%,计算方法如下:
max_used_connections / max_connections * 100% = 3/100 *100% ≈ 3%
--查看最大连接数与响应最大连接数:
show variables like '%max_connections%';
show variables like '%max_user_connections%';
--在配置文件my.cnf中修改最大连接数
max_connections = 100
max_used_connections = 20
定期清除碎片数据
查询数据库中大于磁盘碎片大于0的表,data_free 磁盘碎片占用的空间
SELECT TABLE_SCHEMA DB, TABLE_NAME,DATA_FREE,ENGINE FROM information_schema.TABLES
WHERE TABLE_SCHEMA NOT IN ('INFORMATION_SCHEMA','MYSQL') AND DATA_FREE>0;
- 清理Myisam的磁盘碎片
--碎片优化
OPTIMIZE TABLE data.emp;
--优化结果查询
select TABLE_NAME,ENGINE,data_length,data_free FROM TABLES WHERE TABLE_NAME='emp';
- innodb引擎碎片清理
--碎片优化
alter table 表名 engine=InnoDB
OPTIMIZE 操作会暂时锁住表,而且数据量越大,耗费的时间也越长,一般根据实际情况,只需要每周或者每月整理一次即可。