Mysql查询优化（二）：数据库及表查询配置优化

除了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 操作会暂时锁住表,而且数据量越大,耗费的时间也越长,一般根据实际情况，只需要每周或者每月整理一次即可。