Mysql 数据库性能调优手段

Author： Mr.Pat
发布时间：February 9, 2020
1786 views
No comments
2718 words
Categories： Database

# Mysql 数据库性能调优手段

---

## 根据需求选择使用数据库引擎

### 1. MyISAM 引擎采用原则

* R/W > 100 且 update 相对较少
* 并发不高，不需要事务
* 表数据量小
* 硬件资源有限

### 2. InnoDB 引擎采用原则

* R/W 比较少，频繁更新大字段
* 表数据量超过 1000w，并发高
* 安全性和可用性要求高

### 3. 采用 Memory 引擎

* 有足够的内存
* 对数据的一致性不高，如在线人数和 session 等应用
* 需要定期归档数据

### 4. MyISAM 和 InnoDB 之间的不同

* InnoDB支持事务，MyISAM不支持；
* InnoDB数据存储在共享表空间，MyISAM数据存储在文件中；
* InnoDB支持行级锁，MyISAM只支持表锁；
* InnoDB支持崩溃后的恢复，MyISAM不支持；
* InnoDB支持外键，MyISAM不支持；
* InnoDB不支持全文索引，MyISAM支持全文索引；

## 服务器调整优化策略

### 1)关闭不必要的二进制日志和慢查询日志，仅在内存足够或开发调试时打开他们

```mysql
#查看慢查询是否开启
show variables like '%slow%'

#查看慢查询的条数，方便调试
show global status like '%slow%'
```

### 2)适度使用 Query Cache

### 3)增加 Mysql 允许的最大连接数

```mysql
#查看最大连接数
show variables like 'max_connections'
```

### 4)对于 MyISAM 表适当增加 key_buffer_size

```mysql
show global status like 'key_read%'
#key_cache_miss_rate = Key_reads / Key_read_requests * 100%

当 key_cache_miss_rate 值大于 1% 时就需要适当增加 key_buffer_size
```

### 5)对 MyISAM 表注注意设置 table_cache

> 当 table_cache 不够的时候，Mysql 会采用 LRU 算法踢掉最长时间没有使用的表
> 如果 table_cache 设置过小，Mysql 就会反复打开和关闭 FRM 文件，造成一定性能的损失
> 如果 table_cache 设置过大，Mysql 将消耗很多 CPU 资源去除了 table_cache 算法
> 设置 table_cache 可以参考 Opened_table 参数的值，如果这个值在增加，就要适当增加 table_cache
> InnoDb 则要注意 innodb_buffer_poll_size 参数

### 6) 从表中删除大量数据，可运行 OPTIMIZE TABLE TABLENAME 进行碎片整理

## 数据库分区

> 所谓的分区，就是把一个数据表的文件的索引分散存储在不同的物理文件中。
> 分区类型有 Range、list、Hash、Key

### a)查看是否支持分区

```mysql
show variables like '%partition%'
```

### b)创建分区

```mysql
create table foo(
id INT NOT NULL ATUO_INCREMENT,
created DATETIME,
PRIMARY KEY(id,create)
)ENGINE = INNODB PARTITION BY RANGE (TO_DAYS(created)) (
PARTITION foo_1 VALUES LESS THAN (TO_DAYS('2009-01-01')),
PARTITION foo_2 VALUES LESS THAN (TO_DAYS('2010-01-01'))
)
```

### C)后期还可以继续加

```mysql
ALTER TABLE foo ADD PARTITION(
PARTITION foo_3 VALUES ;ESS THAN (TO_DAY('2011-01-01'))
)
```

### d)删除一个分区

```mysql
ALTER TABLE FOO DROP PARTITION foo_3
```

### e)查看分区信息

```mysql
SELECT * FROM PARTITIONS WHERE PARTITION_NAME IS NOT NULL

#
SHOW VARIABLES LIKE 'datadir'

# in InnoDb
foo#p#foo_1.ibd
foo#p#foo_2.ibd

# in MyISAM
foo#P#foo_1.MYD
foo#P#foo_1.MYD
foo#P#foo_2.MYD
foo#P#foo_2.MYD
```

### f)使用哪种分区

> 主从结构，主服务器很少使用 select 那么使用 range 就没什么意义，可以使用 hash
> 会根据 id 将数据平均分散到各个分区

```mysql
PARTITION BY HASH(id) PARTITION 10
```

### g)根据什么进行分区

> 通常情况下，使用时间进行分区
> 不过还是按需求进行，如果使用主从结构，那么使用一个从服务器进行时间分区，一个使用用户进行分区，当执行查询的时候就选择正确的从服务器查询，写个 Mysql Proxy 脚本可以通过透明实现。

## 分表

> 分表是把原先的表分成几个表，进行分表查询时候，可以 union 或者做一个视图

### 场景一 会员数据对 5 取模，放在 5 个数据库中，如何查询会员数据？

> 可能根据名字查找一条数据，要查找 5 次，但是效率不比查找多数据的一个表差

```php
<?php
$member_tabel = 'member'.uid%5;
$sql = 'select * from {member_tabel}';

$tables = array('member0','member1','member2','member3','member4');

foreach($tables as $table){
    $sql.='select * from {$table} union';
}

$sql = substr($sql,0,-5);
```

### 场景二 在一个监控系统，搞流量，可以根据按天分表

* 查询一天，查询当天的表
* 查询一个星期，汇总7 天的数据到 week 表，查询 week 表
* 查询一个月，汇总一个月的数据到 month 表，查询 month 表
* 3 个月归档一次，超过三个月不能查询，中国移动也是最多保存半年的数据

## 瓶颈及应对措施

* 增加配置中的 buffer 和 cache，增加内存和 cpu 数量
* 使用第三方引擎或其他版本，如 MariaDB 或者 TokuDB
* 迁移到其他数据库，如 PostgreSQL 或者 Oracle
* 对数据库进行分区分表操作，减少表体积
* 使用 Nosql 等辅助解决方案，如 Memcached Redis
* 使用中间件做数据拆分和分布部署，这方面的典型案例有阿里巴巴对外开源的数据中间件 Cobar
* 使用数据库连接池技术。Mysql 是线程模型，可以支持更多的并发连接数，能支持远比 oracle 和 postgreSQL 更多的连接数。使用数据库连接池技术，在并发大的情况下爱，让连接进行排队和复用，一定程度上可以缓解高并发下的连接压力。

If you think my article is useful to you, please feel free to appreciate

Mysql 数据库性能调优手段

Mr.Pat • 2020 年 02 月 09 日

# Mysql 数据库性能调优手段

---

## 根据需求选择使用数据库引擎

### 1. MyISAM 引擎采用原则

* R/W > 100 且 update 相对较少
* 并发不高，不需要事务
* 表数据量小
* 硬件资源有限

### 2. InnoDB 引擎采用原则

* R/W 比较少，频繁更新大字段
* 表数据量超过 1000w，并发高
* 安全性和可用性要求高

### 3. 采用 Memory 引擎

* 有足够的内存
* 对数据的一致性不高，如在线人数和 session 等应用
* 需要定期归档数据

### 4. MyISAM 和 InnoDB 之间的不同

## 服务器调整优化策略

### 1)关闭不必要的二进制日志和慢查询日志，仅在内存足够或开发调试时打开他们

```mysql
#查看慢查询是否开启
show variables like '%slow%'

#查看慢查询的条数，方便调试
show global status like '%slow%'
```

### 2)适度使用 Query Cache

### 3)增加 Mysql 允许的最大连接数

```mysql
#查看最大连接数
show variables like 'max_connections'
```

### 4)对于 MyISAM 表适当增加 key_buffer_size

```mysql
show global status like 'key_read%'
#key_cache_miss_rate = Key_reads / Key_read_requests * 100%

当 key_cache_miss_rate 值大于 1% 时就需要适当增加 key_buffer_size
```

### 5)对 MyISAM 表注注意设置 table_cache

### 6) 从表中删除大量数据，可运行 OPTIMIZE TABLE TABLENAME 进行碎片整理

## 数据库分区

> 所谓的分区，就是把一个数据表的文件的索引分散存储在不同的物理文件中。
> 分区类型有 Range、list、Hash、Key

### a)查看是否支持分区

```mysql
show variables like '%partition%'
```

### b)创建分区

### C)后期还可以继续加

```mysql
ALTER TABLE foo ADD PARTITION(
PARTITION foo_3 VALUES ;ESS THAN (TO_DAY('2011-01-01'))
)
```

### d)删除一个分区

```mysql
ALTER TABLE FOO DROP PARTITION foo_3
```

### e)查看分区信息

```mysql
SELECT * FROM PARTITIONS WHERE PARTITION_NAME IS NOT NULL

#
SHOW VARIABLES LIKE 'datadir'

# in InnoDb
foo#p#foo_1.ibd
foo#p#foo_2.ibd

# in MyISAM
foo#P#foo_1.MYD
foo#P#foo_1.MYD
foo#P#foo_2.MYD
foo#P#foo_2.MYD
```

### f)使用哪种分区

> 主从结构，主服务器很少使用 select 那么使用 range 就没什么意义，可以使用 hash
> 会根据 id 将数据平均分散到各个分区

```mysql
PARTITION BY HASH(id) PARTITION 10
```

### g)根据什么进行分区

## 分表

> 分表是把原先的表分成几个表，进行分表查询时候，可以 union 或者做一个视图

### 场景一 会员数据对 5 取模，放在 5 个数据库中，如何查询会员数据？

> 可能根据名字查找一条数据，要查找 5 次，但是效率不比查找多数据的一个表差

```php
<?php
$member_tabel = 'member'.uid%5;
$sql = 'select * from {member_tabel}';

$tables = array('member0','member1','member2','member3','member4');

foreach($tables as $table){
    $sql.='select * from {$table} union';
}

$sql = substr($sql,0,-5);
```

### 场景二 在一个监控系统，搞流量，可以根据按天分表

## 瓶颈及应对措施

Mysql 数据库性能调优手段

Leave a Comment Cancel reply
使用cookie技术保留您的个人信息以便您下次快速评论，继续评论表示您已同意该条款

PHP XDebug Usage

Using Nginx and cloudFlare to deploy v2ray (websocket + TLS)

前缀、中缀、后缀表达式

PVE 安装 DSM 群晖系统

IP,broadcast,subnet mask 的关系

Redis 异步锁的分析与解决方案

PHP 对象克隆（浅拷贝和深拷贝）

Mysql 数据库常用的 SQL 语句优化

PHP XDebug Usage

Web 常见的几种攻击和防范措施

Mysql 数据库性能调优手段

Leave a Comment Cancel reply 使用cookie技术保留您的个人信息以便您下次快速评论，继续评论表示您已同意该条款

Mysql 数据库性能调优手段

Leave a Comment Cancel reply
使用cookie技术保留您的个人信息以便您下次快速评论，继续评论表示您已同意该条款