概述
为什么要优化
系统的吞吐量瓶颈往往出现在数据库的访问速度上
随着应用程序的运行, 数据库的中的数据会越来越多, 处理时间会相应变慢
数据是存放在磁盘上的, 读写速度无法和内存相比
如何优化
设计数据库时: 数据库表, 字段的设计, 存储引擎
利用好 MySQL 自身提供的功能, 如索引等
横向扩展: MySQL 集群, 负载均衡, 读写分离
SQL 语句的优化(收效甚微)
字段设计
字段类型的选择, 设计规范, 范式, 常见设计案例
原则: 尽量使用整型表示字符串
存储 IP
- INET_ATON(str),address to number
- INET_NTOA(number),number to address
MySQL 内部的枚举类型 (单选) 和集合 (多选) 类型
但是因为维护成本较高因此不常使用, 使用关联表的方式来替代 enum
原则: 定长和非定长数据类型的选择
decimal 不会损失精度, 存储空间会随数据的增大而增大. double 占用固定空间, 较大数的存储会损失精度. 非定长的还有 varchar,text
金额
对数据的精度要求较高, 小数的运算和存储存在精度问题(不能将所有小数转换成二进制)
定点数 decimal
price decimal(8,2)有 2 位小数的定点数, 定点数支持很大的数(甚至是超过 int,bigint 存储范围的数)
小单位大数额避免出现小数
元 ->分
字符串存储
定长 char, 非定长 varchar,text(上限 65535, 其中 varchar 还会消耗 1-3 字节记录长度, 而 text 使用额外空间记录长度)
原则: 尽可能选择小的数据类型和指定短的长度
原则: 尽可能使用 not null
非 null 字段的处理要比 null 字段的处理高效些! 且不需要判断是否为 null.
null 在 MySQL 中, 不好处理, 存储需要额外空间, 运算也需要特殊的运算符. 如 select null = null 和 select null <> null(<>为不等号)有着同样的结果, 只能通过 is null 和 is not null 来判断字段是否为 null.
如何存储? MySQL 中每条记录都需要额外的存储空间, 表示每个字段是否为 null. 因此通常使用特殊的数据进行占位, 比如 int not null default 0,string not null default ''
原则: 字段注释要完整, 见名知意
原则: 单表字段不宜过多
二三十个就极限了
原则: 可以预留字段
在使用以上原则之前首先要满足业务需求
关联表的设计
外键 foreign key 只能实现一对一或一对多的映射
一对多
使用外键
多对多
单独新建一张表将多对多拆分成两个一对多
一对一
如商品的基本信息 (item) 和商品的详细信息(item_intro), 通常使用相同的主键或者增加一个外键字段(item_id)
范式 Normal Format
数据表的设计规范, 一套越来越严格的规范体系(如果需要满足 N 范式, 首先要满足 N-1 范式).N
第一范式 1NF: 字段原子性
字段原子性, 字段不可再分割.
关系型数据库, 默认满足第一范式
注意比较容易出错的一点, 在一对多的设计中使用逗号分隔多个外键, 这种方法虽然存储方便, 但不利于维护和索引(比如查找带标签 java 的文章)
第二范式: 消除对主键的部分依赖
即在表中加上一个与业务逻辑无关的字段作为主键
主键: 可以唯一标识记录的字段或者字段集合.
course_name | course_class | weekday(周几) | course_teacher |
---|---|---|---|
MySQL | 教育大楼 1525 | 周一 | 张三 |
Java | 教育大楼 1521 | 周三 | 李四 |
MySQL | 教育大楼 1521 | 周五 | 张三 |
依赖: A 字段可以确定 B 字段, 则 B 字段依赖 A 字段. 比如知道了下一节课是数学课, 就能确定任课老师是谁. 于是周几和下一节课和就能构成复合主键, 能够确定去哪个教室上课, 任课老师是谁等. 但我们常常增加一个 id 作为主键, 而消除对主键的部分依赖.
对主键的部分依赖: 某个字段依赖复合主键中的一部分.
解决方案: 新增一个独立字段作为主键.
第三范式: 消除对主键的传递依赖
传递依赖: B 字段依赖于 A,C 字段又依赖于 B. 比如上例中, 任课老师是谁取决于是什么课, 是什么课又取决于主键 id. 因此需要将此表拆分为两张表日程表和课程表(独立数据独立建表):
id | weekday | course_class | course_id |
---|---|---|---|
1001 | 周一 | 教育大楼 1521 | 3546 |
course_id | course_name | course_teacher |
---|---|---|
3546 | Java | 张三 |
这样就减少了数据的冗余(即使周一至周日每天都有 Java 课, 也只是 course_id:3546 出现了 7 次)
存储引擎选择
早期问题: 如何选择 MyISAM 和 Innodb?
现在不存在这个问题了, Innodb 不断完善, 从各个方面赶超 MyISAM, 也是 MySQL 默认使用的.
存储引擎 Storage engine:MySQL 中的数据, 索引以及其他对象是如何存储的, 是一套文件系统的实现.
功能差异
show engines
Engine | Support | Comment |
---|---|---|
InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys |
MyISAM | YES | MyISAM storage engine |
存储差异
MyISAM | Innodb | |
---|---|---|
文件格式 | 数据和索引是分别存储的,数据. MYD,索引. MYI | 数据和索引是集中存储的,.ibd |
文件能否移动 | 能,一张表就对应. frm、MYD、MYI3 个文件 | 否,因为关联的还有 data 下的其它文件 |
记录存储顺序 | 按记录插入顺序保存 | 按主键大小有序插入 |
空间碎片(删除记录并 flush table 表名之后,表文件大小不变) | 产生。定时整理:使用命令 optimize table 表名实现 | 不产生 |
事务 | 不支持 | 支持 |
外键 | 不支持 | 支持 |
锁支持(锁是避免资源争用的一个机制,MySQL 锁对用户几乎是透明的) | 表级锁定 | 行级锁定、表级锁定,锁定力度小并发能力高 |
锁扩展
表级锁(table-level lock):lock tables <table_name1>,<table_name2>... read/write,unlock tables <table_name1>,<table_name2>.... 其中 read 是共享锁, 一旦锁定任何客户端都不可读; write 是独占 / 写锁, 只有加锁的客户端可读可写, 其他客户端既不可读也不可写. 锁定的是一张表或几张表.
行级锁(row-level lock): 锁定的是一行或几行记录. 共享锁: select * from <table_name> where <条件> LOCK IN SHARE MODE;, 对查询的记录增加共享锁; select * from <table_name> where <条件> FOR UPDATE;, 对查询的记录增加排他锁. 这里值得注意的是: innodb 的行锁, 其实是一个子范围锁, 依据条件锁定部分范围, 而不是就映射到具体的行上, 因此还有一个学名: 间隙锁. 比如 select * from stu where id < 20 LOCK IN SHARE MODE 会锁定 id 在 20 左右以下的范围, 你可能无法插入 id 为 18 或 22 的一条新纪录.
选择依据
如果没有特别的需求, 使用默认的 Innodb 即可.
MyISAM: 以读写插入为主的应用程序, 比如博客系统, 新闻门户网站.
Innodb: 更新 (删除) 操作频率也高, 或者要保证数据的完整性; 并发量高, 支持事务和外键保证数据完整性. 比如 OA 自动化办公系统.
索引
关键字与数据的映射关系称为索引(== 包含关键字和对应的记录在磁盘中的地址 ==). 关键字是从数据当中提取的用于标识, 检索数据的特定内容.
索引检索为什么快?
关键字相对于数据本身,== 数据量小 ==
关键字是 == 有序 == 的, 二分查找可快速确定位置
图书馆为每本书都加了索引号(类别 - 楼层 - 书架), 字典为词语解释按字母顺序编写目录等都用到了索引.
MySQL 中索引类型
普通索引(key), 唯一索引(unique key), 主键索引(primary key), 全文索引(fulltext key)
三种索引的索引方式是一样的, 只不过对索引的关键字有不同的限制:
普通索引: 对关键字没有限制
唯一索引: 要求记录提供的关键字不能重复
主键索引: 要求关键字唯一且不为 null
索引管理语法
查看索引
show create table 表名:
desc 表名
创建索引
创建表之后建立索引
- create TABLE user_index(
- id int auto_increment primary key,
- first_name varchar(16),
- last_name VARCHAR(16),
- id_card VARCHAR(18),
- information text
- );
-- 更改表结构
alter table user_index
-- 创建一个 first_name 和 last_name 的复合索引, 并命名为 name
add key name (first_name,last_name),
-- 创建一个 id_card 的唯一索引, 默认以字段名作为索引名
add UNIQUE KEY (id_card),
-- 鸡肋, 全文索引不支持中文
- add FULLTEXT KEY (information);
- show create table user_index:
创建表时指定索引
- CREATE TABLE user_index2 (
- id INT auto_increment PRIMARY KEY,
- first_name VARCHAR (16),
- last_name VARCHAR (16),
- id_card VARCHAR (18),
- information text,
- KEY name (first_name, last_name),
- FULLTEXT KEY (information),
- UNIQUE KEY (id_card)
- );
删除索引
根据索引名删除普通索引, 唯一索引, 全文索引: alter table 表名 drop KEY 索引名
- alter table user_index drop KEY name;
- alter table user_index drop KEY id_card;
- alter table user_index drop KEY information;
删除主键索引: alter table 表名 drop primary key(因为主键只有一个). 这里值得注意的是, 如果主键自增长, 那么不能直接执行此操作(自增长依赖于主键索引):
需要取消自增长再行删除:
alter table user_index
-- 重新定义字段
- MODIFY id int,
- drop PRIMARY KEY
但通常不会删除主键, 因为设计主键一定与业务逻辑无关.
执行计划 explain
- CREATE TABLE innodb1 (
- id INT auto_increment PRIMARY KEY,
- first_name VARCHAR (16),
- last_name VARCHAR (16),
- id_card VARCHAR (18),
- information text,
- KEY name (first_name, last_name),
- FULLTEXT KEY (information),
- UNIQUE KEY (id_card)
- );
- insert into innodb1 (first_name,last_name,id_card,information) values ('张','三','1001','华山派');
我们可以通过 explain selelct 来分析 SQL 语句执行前的执行计划:
由上图可看出此 SQL 语句是按照主键索引来检索的.
执行计划是: 当执行 SQL 语句时, 首先会分析, 优化, 形成执行计划, 在按照执行计划执行.
索引使用场景(重点)
where
上图中, 根据 id 查询记录, 因为 id 字段仅建立了主键索引, 因此此 SQL 执行可选的索引只有主键索引, 如果有多个, 最终会选一个较优的作为检索的依据.
-- 增加一个没有建立索引的字段
alter table innodb1 add sex char(1);
-- 按 sex 检索时可选的索引为 null
EXPLAIN SELECT * from innodb1 where sex='男';
可以尝试在一个字段未建立索引时, 根据该字段查询的效率, 然后对该字段建立索引(alter table 表名 add index(字段名)), 同样的 SQL 执行的效率, 你会发现查询效率会有明显的提升(数据量越大越明显).
order by
当我们使用 order by 将查询结果按照某个字段排序时, 如果该字段没有建立索引, 那么执行计划会将查询出的所有数据使用外部排序(将数据从硬盘分批读取到内存使用内部排序, 最后合并排序结果), 这个操作是很影响性能的, 因为需要将查询涉及到的所有数据从磁盘中读到内存(如果单条数据过大或者数据量过多都会降低效率), 更无论读到内存之后的排序了.
但是如果我们对该字段建立索引 alter table 表名 add index(字段名), 那么由于索引本身是有序的, 因此直接按照索引的顺序和映射关系逐条取出数据即可. 而且如果分页的, 那么只用取出索引表某个范围内的索引对应的数据, 而不用像上述那取出所有数据进行排序再返回某个范围内的数据.(从磁盘取数据是最影响性能的)
join
对 join 语句匹配关系 (on) 涉及的字段建立索引能够提高效率
索引覆盖
如果要查询的字段都建立过索引, 那么引擎会直接在索引表中查询而不会访问原始数据(否则只要有一个字段没有建立索引就会做全表扫描), 这叫索引覆盖. 因此我们需要尽可能的在 select 后 == 只写必要的查询字段 ==, 以增加索引覆盖的几率.
这里值得注意的是不要想着为每个字段建立索引, 因为优先使用索引的优势就在于其体积小.
语法细节(要点)
在满足索引使用的场景下(where/order by/join on 或索引覆盖), 索引也不一定被使用
字段要独立出现
比如下面两条 SQL 语句在语义上相同, 但是第一条会使用主键索引而第二条不会.
- select * from user where id = 20-1;
- select * from user where id+1 = 20;
like 查询, 不能以通配符开头
比如搜索标题包含 MySQL 的文章:
select * from article where title like '%mysql%';
这种 SQL 的执行计划用不了索引(like 语句匹配表达式以通配符开头), 因此只能做全表扫描, 效率极低, 在实际工程中几乎不被采用. 而一般会使用第三方提供的支持中文的全文索引来做.
但是 关键字查询 热搜提醒功能还是可以做的, 比如键入 MySQL 之后提醒 MySQL 教程, MySQL 下载, MySQL 安装步骤等. 用到的语句是:
select * from article where title like 'mysql%';
这种 like 是可以利用索引的(当然前提是 title 字段建立过索引).
复合索引只对第一个字段有效
建立复合索引:
alter table person add index(first_name,last_name);
其原理就是将索引先按照从 first_name 中提取的关键字排序, 如果无法确定先后再按照从 last_name 提取的关键字排序, 也就是说该索引表只是按照记录的 first_name 字段值有序.
因此 select * from person where first_name = ? 是可以利用索引的, 而 select * from person where last_name = ? 无法利用索引.
那么该复合索引的应用场景是什么?== 组合查询 ==
比如对于 select * person from first_name = ? and last_name = ?, 复合索引就比对 first_name 和 last_name 单独建立索引要高效些. 很好理解, 复合索引首先二分查找与 first_name = ? 匹配的记录, 再在这些记录中二分查找与 last_name 匹配的记录, 只涉及到一张索引表. 而分别单独建立索引则是在 first_name 索引表中二分找出与 first_name = ? 匹配的记录, 再在 last_name 索引表中二分找出与 last_name = ? 的记录, 两者取交集.
or, 两边条件都有索引可用
一但有一边无索引可用就会导致整个 SQL 语句的全表扫描
状态值, 不容易使用到索引
如性别, 支付状态等状态值字段往往只有极少的几种取值可能, 这种字段即使建立索引, 也往往利用不上. 这是因为, 一个状态值可能匹配大量的记录, 这种情况 MySQL 会认为利用索引比全表扫描的效率低, 从而弃用索引. 索引是随机访问磁盘, 而全表扫描是顺序访问磁盘, 这就好比有一栋 20 层楼的写字楼, 楼底下的索引牌上写着某个公司对应不相邻的几层楼, 你去公司找人, 与其按照索引牌的提示去其中一层楼没找到再下来看索引牌再上楼, 不如从 1 楼挨个往上找到顶楼.
如何创建索引
建立基础索引: 在
where,order by,join
字段上建立索引.
优化, 组合索引: 基于业务逻辑
如果条件经常性出现在一起, 那么可以考虑将多字段索引升级为 == 复合索引 ==
如果通过增加个别字段的索引, 就可以出现 == 索引覆盖 ==, 那么可以考虑为该字段建立索引
查询时, 不常用到的索引, 应该删除掉
前缀索引
语法: index(field(10)), 使用字段值的前 10 个字符建立索引, 默认是使用字段的全部内容建立索引.
前提: 前缀的标识度高. 比如密码就适合建立前缀索引, 因为密码几乎各不相同.
== 实操的难度 ==: 在于前缀截取的长度.
我们可以利用 select count(*)/count(distinct left(password,prefixLen));, 通过从调整 prefixLen 的值 (从 1 自增) 查看不同前缀长度的一个平均匹配度, 接近 1 时就可以了(表示一个密码的前 prefixLen 个字符几乎能确定唯一一条记录)
索引的存储结构
BTree
btree(多路平衡查找树)是一种广泛应用于 == 磁盘上实现索引功能 == 的一种数据结构, 也是大多数数据库索引表的实现.
以 add index(first_name,last_name)为例:
BTree 的一个 node 可以存储多个关键字, node 的大小取决于计算机的文件系统, 因此我们可以通过减小索引字段的长度使结点存储更多的关键字. 如果 node 中的关键字已满, 那么可以通过每个关键字之间的子节点指针来拓展索引表, 但是不能破坏结构的有序性, 比如按照 first_name 第一有序, last_name 第二有序的规则, 新添加的韩香就可以插到韩康之后. 白起 < 韩飞 < 韩康 < 李世民 < 赵奢 < 李寻欢 < 王语嫣 < 杨不悔. 这与二叉搜索树的思想是一样的, 只不过二叉搜索树的查找效率是 log(2,N)(以 2 为底 N 的对数), 而 BTree 的查找效率是 log(x,N)(其中 x 为 node 的关键字数量, 可以达到 1000 以上).
从 log(1000+,N)可以看出, 少量的磁盘读取即可做到大量数据的遍历, 这也是 btree 的设计目的.
B+Tree 聚簇结构
聚簇结构 (也是在 BTree 上升级改造的) 中, 关键字和记录是存放在一起的.
在 MySQL 中, 仅仅只有 Innodb 的 == 主键索引为聚簇结构 ==, 其它的索引包括 Innodb 的非主键索引都是典型的 BTree 结构.
哈希索引
在索引被载入内存时, 使用哈希结构来存储.
查询缓存
缓存 select 语句的查询结果
在配置文件中开启缓存
Windows 上是 my.INI,Linux 上是 my.cnf
在 [mysqld] 段中配置 query_cache_type:
0: 不开启
1: 开启, 默认缓存所有, 需要在 SQL 语句中增加 select sql-no-cache 提示来放弃缓存
2: 开启, 默认都不缓存, 需要在 SQL 语句中增加 select sql-cache 来主动缓存(== 常用 ==)
更改配置后需要重启以使配置生效, 重启后可通过 show variables like 'query_cache_type'; 来查看:
- show variables like 'query_cache_type';
- query_cache_type DEMAND
在客户端设置缓存大小
通过配置项 query_cache_size 来设置:
- show variables like 'query_cache_size';
- query_cache_size 0
- set global query_cache_size=64*1024*1024;
- show variables like 'query_cache_size';
- query_cache_size 67108864
将查询结果缓存
select sql_cache * from user;
重置缓存
reset query cache;
缓存失效问题(大问题)
当数据表改动时, 基于该数据表的任何缓存都会被删除.(表层面的管理, 不是记录层面的管理, 因此失效率较高)
注意事项
应用程序, 不应该关心 query cache 的使用情况. 可以尝试使用, 但不能由 query cache 决定业务逻辑, 因为 query cache 由 DBA 来管理.
缓存是以 SQL 语句为 key 存储的, 因此即使 SQL 语句功能相同, 但如果多了一个空格或者大小写有差异都会导致匹配不到缓存.
分区
一般情况下我们创建的表对应一组存储文件, 使用 MyISAM 存储引擎时是一个. MYI 和. MYD 文件, 使用 Innodb 存储引擎时是一个. ibd 和. frm(表结构)文件.
当数据量较大时(一般千万条记录级别以上),MySQL 的性能就会开始下降, 这时我们就需要将数据分散到多组存储文件,== 保证其单个文件的执行效率 ==.
最常见的分区方案是按 id 分区, 如下将 id 的哈希值对 10 取模将数据均匀分散到 10 个. ibd 存储文件中:
- create table article(
- id int auto_increment PRIMARY KEY,
- title varchar(64),
- content text
- )PARTITION by HASH(id) PARTITIONS 10
查看 data 目录:
== 服务端的表分区对于客户端是透明的 ==, 客户端还是照常插入数据, 但服务端会按照分区算法分散存储数据.
MySQL 提供的分区算法
== 分区依据的字段必须是主键的一部分 ==, 分区是为了快速定位数据, 因此该字段的搜索频次较高应作为强检索字段, 否则依照该字段分区毫无意义
hash(field)
相同的输入得到相同的输出. 输出的结果跟输入是否具有规律无关.== 仅适用于整型字段 ==
key(field)
和 hash(field)的性质一样, 只不过 key 是 == 处理字符串 == 的, 比 hash()多了一步从字符串中计算出一个整型在做取模操作.
- create table article_key(
- id int auto_increment,
- title varchar(64),
- content text,
PRIMARY KEY (id,title) -- 要求分区依据字段必须是主键的一部分
)PARTITION by KEY(title) PARTITIONS 10
range 算法
是一种 == 条件分区 == 算法, 按照数据大小范围分区(将数据使用某种条件, 分散到不同的分区中).
如下, 按文章的发布时间将数据按照 2018 年 8 月, 9 月, 10 月分区存放:
- create table article_range(
- id int auto_increment,
- title varchar(64),
- content text,
created_time int, -- 发布时间到 1970-1-1 的毫秒数
PRIMARY KEY (id,created_time) -- 要求分区依据字段必须是主键的一部分
- )charset=utf8
- PARTITION BY RANGE(created_time)(
- PARTITION p201808 VALUES Less than (1535731199), -- select UNIX_TIMESTAMP('2018-8-31 23:59:59')
- PARTITION p201809 VALUES Less than (1538323199), -- 2018-9-30 23:59:59
- PARTITION p201810 VALUES Less than (1541001599) -- 2018-10-31 23:59:59
- );
注意: 条件运算符只能使用 ==Less than==, 这以为着较小的范围要放在前面, 比如上述 p201808,p201819,p201810 分区的定义顺序依照 created_time 数值范围从小到大, 不能颠倒.
insert into article_range values(null,'MySQL 优化','内容示例',1535731180);
flush tables; -- 使操作立即刷新到磁盘文件
由于插入的文章的发布时间 1535731180 小于 1535731199(2018-8-31 23:59:59), 因此被存储到 p201808 分区中, 这种算法的存储到哪个分区取决于数据状况.
list 算法
也是一种条件分区, 按照列表值分区(in (值列表)).
- create table article_list(
- id int auto_increment,
- title varchar(64),
- content text,
status TINYINT(1), -- 文章状态: 0 - 草稿, 1 - 完成但未发布, 2 - 已发布
PRIMARY KEY (id,status) -- 要求分区依据字段必须是主键的一部分
- )charset=utf8
- PARTITION BY list(status)(
PARTITION writing values in(0,1), -- 未发布的放在一个分区
PARTITION published values in (2) -- 已发布的放在一个分区
- );
- insert into article_list values(null,'mysql 优化','内容示例',0);
- flush tables;
分区管理语法
range/list
增加分区
前文中我们尝试使用 range 对文章按照月份归档, 随着时间的增加, 我们需要增加一个月份:
- alter table article_range add partition(
- partition p201811 values Less than (1543593599) -- select UNIX_TIMESTAMP('2018-11-30 23:59:59')
- -- more
- );
删除分区
alter table article_range drop PARTITION p201808
注意:== 删除分区后, 分区中原有的数据也会随之删除!==
key/hash
新增分区
alter table article_key add partition partitions 4
销毁分区
alter table article_key coalesce partition 6
key/hash 分区的管理不会删除数据, 但是每一次调整 (新增或销毁分区) 都会将所有的数据重写分配到新的分区上.== 效率极低 ==, 最好在设计阶段就考虑好分区策略.
分区的使用
当数据表中的数据量很大时, 分区带来的效率提升才会显现出来.
只有检索字段为分区字段时, 分区带来的效率提升才会比较明显. 因此,== 分区字段的选择很重要 ==, 并且 == 业务逻辑要尽可能地根据分区字段做相应调整 ==(尽量使用分区字段作为查询条件).
水平分割和垂直分割
水平分割: 通过建立结构相同的几张表分别存储数据
垂直分割: 将经常一起使用的字段放在一个单独的表中, 分割后的表记录之间是一一对应关系.
分表原因
为数据库减压
分区算法局限
数据库支持不完善(
5.1
之后 MySQL 才支持分区操作)
id 重复的解决方案
借用第三方应用如 memcache,Redis 的 id 自增器
单独建一张只包含 id 一个字段的表, 每次自增该字段作为数据记录的 id
由于简书篇幅原因, 下篇是接连上文往下阅读的长文: 一篇解决面试常问的 MySQL 性能优化下篇(建议收藏)!, 希望这篇文章能帮助到那些有需要的朋友!
来源: http://www.jianshu.com/p/53a16729f796