从四个方面来复习一下数据库的优化

  1. 表引擎优化：建表时选择最合适的引擎。
  2. 表结构优化：建表时选择最合适的字段类型，让表尽量轻量。
  3. 索引优化：为表创建高效索引，以提高表的查询速度。
  4. SQL 优化：编写高效的 SQL 语句，以加快查询速度。

    表引擎优化

    1
    2
    --查看表引擎
    show engines

    InnoDB

    InnoDB是官方默认的引擎，特点：
  5. 支持事务
  6. 支持外键约束
  7. 行级锁（在并发修改数据时效率更高）
  8. 可以从灾难中恢复（更加安全）
  9. 新版的 InnoDB 性能也非常好
  10. 每个表会在硬盘上生成两个文件：.frm 文件（保存表结构），.ibd（保存数据）

    MyISAM

    MyISAM 引擎是 MySQL 最早支持的一种引擎，它的特点是：
  11. 读、写速度非常快
  12. 表级锁
  13. 不支持事务
  14. 不支持外键约束
  15. 无法自动从灾难中恢复
  16. 每个表会在硬盘上生成三个文件：.frm 文件（保存表结构），.MYI（保存索引），MYD（保存数据）

    MRG_MyISAM (MERGE)

    MRG_MyISAM 引擎的表用来对多个结构相同的表进行数据的合并、汇总，可以用来实现 MyISAM 引擎的水平分表功能。

创建这种类型的表时会在硬盘上生成两个文件：.frm（保存表结构），.MRG（保存所有分表名称）

  1. 创建 n 个 MyISAM 引擎的表并保存分表的数据
  2. 创建 1 个 MRG_MyISAM 引擎的表用来汇总这 n 个引擎中的数据，一般这个表是只读的
    注意：这个引擎只帮助我们解决读数据汇总的问题，写数据的问题需要我们自己用程序来实现。

创建汇总表

1
2
3
4

create table xxx{
    ...
} engine=MRG_MyISAM union=(分表列表) INSERT_METHOD=插入类型;

INSERT_METHOD可用的值为：

0：不能插入（默认）

first：插入到第一个表中

last：插入到最后一个表中

示例

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
create table t1(
    id int,
    name varchar(10)
)engine=MyISAM;

create table t2(
    id int,
    name varchar(10)
)engine=MyISAM;

create table t3(
    id int,
    name varchar(10)
)engine=MyISAM;

create table t(
    id int,
    name varchar(10)
)engine=MRG_MyISAM
union=(t1,t2,t3) INSERT_METHOD=0;

总结：

1、这个引擎只支持 MyISAM 引擎的水平分表

2、这个引擎只能帮助我们进行读操作汇总，而写入的逻辑需要我们自己写程序实现

3、所有表的结构必须相同

4、这个引擎中不保存任何数据，它的数据都是由分片表中取出来

扩展1：写入数据时如何选择应该写入到哪个表？

1、平均放数据到不同的表。

​ 我们可以直接使用记录的 ID 对表的数量取模

​ 流程：1. 先穝将要插入数据的 ID ，2. 再然后ID对表的数量取模，得到表的编号 ，3.通过编号拼出SQL 语句 （INSERT INTO table_$n)

2、根据业务来分

​ 根据实际业务需要，我们可以将表按以下方式分：

​ a. 按月份分12个表，1月份下的单保存到第1个表中，2月份 下的单保存到第2个表中…

​ b. 按省份分表

​ c. 按分类分表，比如，手机分类的商品放到一个表，电脑分类的商品放另一个表

扩展2：如何生成唯一的ID。

​ 分表中一般不能使用自增长的ID，因为每个表的自增 ID 都是独立增长的，这就会导致多个分表中出现重复的自增ID。

解决办法：

  1. 在 Redis 中定义一个计数器

    每次要向分表中插入数据时先到 Redis 中把计数自增得到一个ID做这条记录的ID 插入到分表中。

  2. 单独创建一张表，表中只有一个自增ID字段，用来维护最大的ID

    每次插入数据时，先向这个维护最大ID的表中表中插入一个记录，得到这条记录的ID，然后用这个ID做为要插入的记录的ID插入到分表中。

Archive

特点：以 zlib 对表数据进行压缩，磁盘 IO 更少

只支持 insert 和 select 操作

只能在自增ID上建立索引。

适用于数据量很大、不经常修改的数据，比如：日志类的数据，采集数据等。

总结：

  1. 使用它可以节省硬盘空间
  2. 日志类的数据，采集数据
  3. 做历史数据的归档（冷、热数据分离）

    Memory/heap

    内存表使用哈希散列索引把数据保存在内存中，因此具有极快的速度，适合缓存中小型数据库。

特点：

  1. 数据保存在内存中，服务器重启后，表中数据会丢失，但表结构还在
  2. 内存表占用的内存量不能走过 max_heap_table_size 值
  3. 在内存表中所有字段都是固定长度，所以 char 和 varchar 没有区别，都是固定长度
  4. 不支持 BLOG 和 TEXT 等大字段
  5. 不支持事务，而且是表锁，当修改频繁时，性能会下降

定义内存表：

1
create table (...)  engine = memory/heap ;  (一般使用memory，heap用得少了)

类似的软件：Redis。

FEDERATED

这种引擎的表用来 操作远程数据库。

比如我们现在有两个项目，项目A和项目B，现在我们希望这两个项目共享同一张用户表，这时我们就可以在项目B中创建一个 federated 引擎的表，然后让这个表去操作项目A中的用户表，这样两个项目就共同用户表了。

federated 引擎表的特点：

  1. 本地只存储表的结构信息（只有一个 .frm 文件保存表结构），不存储数据，数据都保存在远程数据库上
  2. 对这张表的增、删、改、查等操作，实际上是操作远程表

注意：本地表的结构要和远程的表结构相同。

  • 开启方法

在 my.cnf 中的 [mysqld] 中添加 federated：

1
2
3
4
[mysqld]
...
federated
...

然后重启 MySQL 。

  • 建表语法
1
2
3
create table xx
(..)engine=federated
connection='mysql://用户名:密码@主机IP:端口号/数据库名/表名';

实战案例：创建两个数据库，让两个数据库能够共享某一张表。

操作步骤：

  1. 新建两个 MySQL 数据库的容器

  2. 创建第一个数据库上创建保存数据的库和表

  3. 开启第二个数据库上的 federated 引擎

  4. 创建 federated 引擎表连接第一个库中的表

  5. 测试是否可以使用这个表操作远程表

1、创建启动两个 MySQL 容器的编排文件

docker-compose.yml

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
version: "3"

services:

  mysql01:
    image: mysql:5.7.24
    ports:
      - "13306:3306"
    environment:
      - MYSQL_ROOT_PASSWORD=123123

  mysql02:
    image: mysql:5.7.24
    ports:
      - "23306:3306"
    depends_on:
      - mysql01
    links:
      - "mysql01:mysql_remote"
    environment:
      - MYSQL_ROOT_PASSWORD=123123

2、创建之后启动容器

1
docker-compose up -d

3、进入第一个数据库，并创建一个数据库和表（用来保存数据）

a. 进入容器

1
docker exec -t 第一个容器名称 bash

b.连接mysql

1
mysql -u root -p密码

c.建库和表

1
2
3
4
5
6
create database test_fed;
use test_fed;
create table test_fed(
   id int,
   name varchar(10)
);

4、进入第二个数据库配置远程表

a. 进入容器

1
docker exec -t 第二个容器名称 bash

b. 启动 federated 引擎

1
echo federated >> /etc/mysql/mysql.conf.d/mysqld.cnf

重启 MySQL 服务器。

c.连接mysql

1
mysql -u root -p密码

d.建库和表

1
2
3
4
5
6
7
create database test_fed;
use test_fed;
create table fed(
   id int,
   name varchar(10)
)engine=FEDERATED
connection='mysql://root:123123@mysql_remote:3306/test_fed/test_fed';

配置成功，现在操作这个表其实就是在操作远程的表。

如何查看数据是否保存到了本地？

每个 InnoDB 引擎的表在创建之后，就会在本地生成两个文件：.frm（保存表结构）和 .ibd（保存表中数据）。只要看本地是否有 .ibd 文件即可：

  1. 在 mysql 执行以下指令以查看 mysql 中文件保存的目录

    show variables like '%data%'

  2. 然后到目录中查看是否存在 .ibd ，我们会发现远程表没有 .ibd 文件，说明数据没有保存在本地

只有一个 .frm 文件：

总结：

1、应用场景：这种引擎主要用来多个项目中数据库中表的共享

2、在创建时要和远程表结构相同

3、这个引擎中不保存任何数据，它都是操作远程数据库中的数据

BLACKHOLE

BlackHole ：黑洞引擎，写入的任何数据都会消失，用于记录 binlog 做复制的中继存储！

使用BLACKHOLE存储引擎的表不存储任何数据，但如果 mysql 启用了二进制日志，SQL 语句被写入日志（并被复制到从服务器）。

总结：

  1. 不保存数据，但是会记录到 binlog 中（主从复制的原理就是 binlog）
  2. 主从速度极快，对主服务器的负载比较慢
  3. 主要用来做为主从服务器中中转站，以减少主服务器压力

表结构优化

总的原则：建表是为表中的字段选择合适的字段类型
基本原则是：

  1. 优先考虑数字类型，其次是日期类型，最后是字符串类型
  2. 优先选择占用空间小并且够用的类型

    数字

    类型 范围 适用
    tinyint unsigned 0~255 只有几个可选值时使用，比如：支付状态、是否上架、年龄、性别等的字段
    smallint unsigned 0~65535 用户积分、评论数等
    mediumint unsigned 0 ~ 16777215 表自增ID
    int unsigned 0~4294967295 表自增ID、IP地址（INET_ATON、INET_NTOA）
    bigint unsigned 0~18446744073709551615 手机号码

tinyint unsigned 和 `tinyint signed’ 的区别。

unsigned：没有负数，所以： tinyint unsigned ：0~255

signed（默认）：有负数：所次 tinyint signed ：-127 ~ 128

总结：如果数字中不包含负数就使用 unsigned 。

字符穿

字符串类型常用的有以下几种：

类型 长度 适用
char 0~255个 字符 长度固定的字符串，如经过加密的密码。
varchar 0~65535个字节 长度不固定的字符串，如姓名，商品名称、描述等。
tinytintext 0~255个字节 文本内容。
text 0~65535个字节 文本内容。
mediumtext 0~16,777,215个字节 文本内容。
longtext 0~4,294,967,295个字节 文本内容。

原则：一般我们尽量选择使用 char 和 varchar ，只有表中需要保存 多个大文本字段 导致 超出表限制 （后面介绍）时选择 text 类型。

char 和 varchar 的区别

1、最大容量不同（char 是 255 字符，varchar 是 65535 字节）

2、char 是定长：实际占用硬盘的尺寸等于建表时指定的尺寸，无论里面数据的长度是多·

3、varchar 是变长： 实际占用硬盘的尺寸主要由保存的数据的长度+1决定的。

如何选择？

  1. 如果字段中的值长度是固定的就选择 char 类型，如：密码（laravel 中加密的字符串都是60位）。
  2. 其它不固定的长度就用 varchar 类型，比如：姓名、账号、标题等

字符和字节什么关系？

utf8：1字符 = 3字节

gbk：1字符 = 2字节

注意字符和字节对类型实际的影响：

char(255) ：最多能保存255个字符。

表 utf8 编码时：

​ varchar(65535/3) ==> （最多能保存 2 万多个字符）

表 gb2312 编码时：

​ varchar(65535/2) ==》（最多能保存 3 万多个字符）

时间字段

保存时间时可以用 datetime 和 timestamp 类型。

datetime 占8个字节，范围：1000-01-01 00:00:00 ~ 9999-12-31 23:59:59

timestamp 占4个字节，范围：1970-01-01 ~ 2038-01-19（MySQL5.6+才开始支持）

not null

因为 null 类型的字段需要进行额外的处理，所以我们应该尽量指定所有的字段都为 not null 。

表的限制

1、每个表最多有 4096 个字段

2、每个表所有字段（不包含 text、blog 类型字段）加起来不能超过 65535 个 字节

3、因为 text 类型的数据是额外单独存储的，所以性能比较慢，所以要选择时应该首先考虑 char 和 varchar ，只有在超过 65535 这个限制时才考虑使用 text 类型

当表使用不同的编码时，一个字符占用的字节数是不同的，它们的关系是：

  • utf8编码：1字符=3字节
  • gb2312：1字符=2字节

问题：如果我希望一个表中有5个20000个字符的字段怎么办？

使用：text 类型，因为 text 类型中的数据实际上是保存表外面的，只占用几个字节的表空间。注意：它的性能慢，慎用 。

适当的冗余

我们在设计表结构时，应该尽量让我们的表结构简洁、明晰。但有些时候我们为了能够得到更好的查询性能，我们也需要适当的添加一些冗余字段。

什么是冗余？

冗余就是重复的字段。

比如：用户名称、头像、商品评论数、销量等。

索引优化

索引是优化数据库最有效的方式之一 。

BTREE 和 HASH 索引

索引从它的实现原理来分，可以分为：BTree 和 HASH 两种。

简单的说：

HASH：性能虽高，但是只支持 =、IN、<> 查询，而且不能避免排序，不能避免全表扫描等问题。

BTree：支持范围查询、可以避免排序、可以避免全表扫描等。

所以综上，我们更加需要 BTree 类型的索引，所以大多数引擎都支持 BTree索引，只有少数引擎支持HASH索引：

索引的类型

我们在创建索引时可以选择以下几种类型的索引：

主键索引（primary key）：加快查询速度，索引字段的值不能重复，不能为空，一个表中只能有一个主键

唯一索引（unique）：加快查询速度，索引字段的值不能重复

普通索引（key)：加快查询速度 ，没有其它功能

全文索引（fulltext）：对大文本进行 分词 索引，但 是MySQL 默认的全文索引对中文支持不好，所以基本不用

联合索引：把多个字段联合起来创建一个索引

前缀索引：只为字符串的前 N 个字符创建索引

索引的操作

可以使用以下 SQL 为表添加、删除索引：

指令 说明
show index from 表名 \G 查看索引
alter table 表名 add primary key (字段名) 添加主键索引
alter table 表名 add unique 索引名称 (字段名) 添加唯一索引
alter table 表名 add index 索引名称 (字段名) 添加普通索引
drop index 索引名称 on 表名 ; 删除普通/唯一索引
alter table table_name drop primary key ; 删除主键索引

为哪些字段创建索引

表中索引越多越好吗？

答案是否定的。

索引会加速查询的速度，但同时也会降低添加、修改、删除等写操作的速度，所以索引并不是越多越好的。我们一般建索引的原则是：

  1. 经常与其他表进行连接的表，在连接的字段上应该建立索引（外键必须有索引）
  2. 经常出现在 where 子句中的字段，特别是大表的字段
  3. 索引应该建在选择性高的字段上
  4. 索引应该建在小字段上（数字、日期）
  5. 频繁修改的表，不要建立太多的索引

联合索引

所以我们就可以把多个字符合并在一起创建一个索引：

1
2
3
4
5
6
7
create table xxx(
	...
	a int unsigned  not null,
	b int unsigned not null,
	c int unsigned not null,
	key a_b_c(a,b,c)
);

说明：

  1. 左原则：建了一个(a,b,c)的复合索引，那么实际等于建了(a),(a,b),(a,b,c)三个索引
  2. 当同时使用这三个列进行查询、排序操作时，复合索引效率更高
  3. 如果要单独对某个列进行查询，那么一定要放在最前面，如果多个列都需要单独查询，那么不能创建联合索引

前缀索引

避免使用以 % 开头的模糊查询，因为将无法使用任何索引

查询以 XXX 开头时，我们可以使用前缀索引来进行优化

添加前缀索引语法：

1
ALTER TABLE table_name ADD KEY(column_name(prefix_length));

如：为前10个字符添加索引：

1
ALTER TABLE table_name ADD KEY(column_name(10));

InnoDB 和 MySQL 引擎前缀索引的限制

对于INNODB存储引擎而言，默认前缀长度最大能支持767字节，在而开启innodb_large_prefix属性值的情况下，最大能支持3072字节。

对于MyISAM的存储引擎而言，前缀长度限制为1000字节。

SQL 优化

SQL 语句是操作数据库的主要手段，SQL 语句写的好坏直接影响查询的速度，我们应该让我们的 SQL 语句在执行时尽量使用上合适的索引。

定位慢SQL

要优化 SQL ，首先我们需要先找到有性能问题的 SQL 语句。Mysql 给我们提供了 慢日志 功能，慢日志会把所有执行的比较慢的 SQL 语句记录下来，这时我们就可以针对有问题的 SQL 语句进行分析和优化了。

  • 查看慢日志是否启用

可以使用 show global variables 指令查看慢日志是否已经启用：

1
SHOW GLOBAL VARIABLES like '%slow%';

执行结果：

  • 启用慢日志

我们有两种方式启用/禁用慢日志。

1、修改配置文件

1
2
3
4
slow_query_log ：是否启用
slow_query_log_file：慢日志保存的文件路径
long_query_time：设置慢日志的伐值，单位是秒
log_queries_not_using_indexes ：是否记录未使用索引的SQL

2、通过 SQL 语句启动

我们可以使用 SET GLOBAL 指令修改这几个参数。

1
2
3
SET GLOBAL slow_query_log="on"
SET GLOBAL long_query_time=0.001     // 1毫秒
set global slow_query_log_file="/var/lib/mysql/slow.log"
  • 查看慢日志内容

我们可以打开慢日志文件查看里面的内容：

在实际项目中，慢日志文件中的内容会非常的多，如果直接查看这个文件中的内容是非常不方便的，所以我们可以使用一些工具来查看这个日志。

mysqldumpslow 是 MySQL 官方提供的查看慢日志的工具

用法：

1
mysqldumpslow -s 排序方式 -t 前几条 日志文件

参数说明：

-s：排序方式，可用值（c,t,l,r,at,al,ar）

​ c：总次数
​ t：总时间
​ l：锁的时间
​ r：总数据行
​ at,al,ar：a,l,r平均数

-t：取前 t 条

例、取出返回记录数最多的前10个

1
mysqldumpslow -s r -t 10 日志文件

执行计划分析

我们可以使用 explain 和 desc 两个指令来分析一条 SQL 语句的执行细节，然后我们就可以根据结果来优化我们的 SQL 语句 。

我们在 SQL 语句前面加上 explain 就是分析这条 SQL 语句：

1
explain select * from privilege

分析之后得到的结果是：

接下来我们来说明一下每个字段的含义：

  • id：执行顺序 ，值相同时由上至下执行，值不同的，大的值先执行
  • table：显示这一行的数据是关于哪张表的
  • type：常用的类型有（性能从好到差）system>const>eq_ref>ref>range>index>all，如果是大表，一定要避免出现 all 的情况（全表扫描）
  • rows：表示MySQL根据表统计信息及索引选用情况，估算的找到所需的记录所需要读取的行数
  • filtered：返回结果的行数占所需读取行数的百分比，值越大越好
  • key：实际使用的索引

查询列上不要使用表达式或者函数

  • 函数

在查询的字段上不要写表达式和函数，否则无法使用索引。

比如有这样一条 SQL ：查询出30天之内下的订单：

1
SELECT * FROM orders WHERE to_days(current_date) - to_days(created_at) <= 30

可以改造成：

1
SELECT * FROM orders WHERE created >= date_sub(current_date,interval 30 day)
  • 表达式
1
SELECT … FROM DEPT WHERE SAL > 25000/12

应该改成

1
SELECT … FROM DEPT WHERE SAL * 12 > 25000

模糊查询

不要使用以 %开头的 like 查询，因为这种查询无法使用上索引，如：WHERE goods_name LIKE '%xxx%'

不要使用 select *

在我们执行 SQL 语句时，经常为了方便而使用 select * 来查询，这是一个非常不好的习惯，因为当表中存在大字段时（大文本字段），查询速度将会非常的慢。

所以我们在平时写SQL语句时，只避免出现 SELECT *，应该只选择我们需要使用的字段。

分页查询

我们平时实现翻页都是使用 limit 来实现的，比如：

1
SELECT * FROM goods LIMIT 0,10

但是当数据量比较大时，翻页会比较慢，比如，翻页到第1000页。

1
SELECT * FROM goods LIMIT 10000,10

解决办法：

1、不要查询大字段（不要使用*）（大本文本字段）

2、网站不提供太多的翻页，比如百度最多到76页。

3、修改 SQL 语句为：

1
2
3
4
5
SELECT * FROM goods WHERE id IN (
    SELECT a.id FROM (
       SELECT id FROM goods LIMIT 10000,10
    ) a
)

覆盖索引

索引包含（亦称覆盖）所有需要查询的字段的值，我们称这种索引为覆盖索引

当发起一个被索引覆盖的查询时，在explain的Extra列可以看到 Using index 的标识。

回想一下，如果查询只需要扫描索引而无须回表，将带来诸多好处。

（1）索引条目通常远小于数据行大小，如果只读取索引，MySQL就会极大地减少数据访问量。

（2）索引按照列值顺序存储，对于I/O密集的范围查询会比随机从磁盘中读取每一行数据的I/O要少很多。

（3）InnoDB的辅助索引（亦称二级索引）在叶子节点中保存了行的主键值，如果二级索引能够覆盖查询，则可不必对主键索引进行二次查询了。

​ 覆盖索引就是从索引中直接获取查询结果，要使用覆盖索引需要注意select查询列中包含在索引列中；where条件包含索引列或者复合索引的前导列；查询结果的字段长度尽可能少。


 上一页

PHP OOP

 评论


种一棵树最好的时间是十年前，其次是现在

本站使用 Material X 作为主题 ， 总访问量为 次 。
隐藏