MySQL性能管理及架构设计指南

 一、什么影响了数据库查询速度

1.1 影响数据库查询速度的四个因素

1.2 风险分析

QPS：Queries Per Second意思是“每秒查询率”，是一台服务器每秒能够相应的查询次数，是对一个特定的查询服务器在规定时间内所处理流量多少的衡量标准。

TPS：是TransactionsPerSecond的缩写，也就是事务数/秒。它是软件测试结果的测量单位。客户机在发送请求时开始计时，收到服务器响应后结束计时，以此来计算使用的时间和完成的事务个数。

注：最好不要在主库上数据库备份，大型活动前取消这样的备份计划。

1. 效率低下的 sql：会产生超高的QPS与TPS。

2. 大量的并发：数据连接数被占满（max_connection默认100，一般把连接数设置得大一些）。 
   并发量:同一时刻数据库服务器处理的请求数量

3. 超高的CPU使用率：CPU资源耗尽出现宕机。

4. 磁盘IO：磁盘IO性能突然下降、大量消耗磁盘性能的计划任务。解决：更快磁盘设备、调整计划任务、做好磁盘维护。

1.3 网卡流量：

如何避免无法连接数据库的情况？

1. 减少从服务器的数量（从服务器会从主服务器复制日志）

2. 进行分级缓存（避免前端大量缓存失效）

3. 避免使用select * 进行查询

4. 分离业务网络和服务器网

1.4 大表带来的问题（重要）

1.4.1 大表的特点

1. 记录行数巨大，单表超千万

2. 表数据文件巨大，超过10个G

1.4.2 大表的危害

1. 慢查询：很难在短时间内过滤出需要的数据 
   查询字区分度低 -> 要在大数据量的表中筛选出来其中一部分数据会产生大量的磁盘io -> 降低磁盘效率
2. 对DDL影响： 
   建立索引需要很长时间：

• MySQL -v<5.5建立索引会锁表

• MySQL -v>=5.5建立索引会造成主从延迟（mysql建立索引，先在组上执行，再在库上执行）

修改表结构需要长时间的锁表：会造成长时间的主从延迟

1.4.3 如何处理数据库上的大表

分库分表把一张大表分成多个小表 
难点： 
a. 分表主键的选择 
b. 分表后跨分区数据的查询和统计

1.5 大事务带来的问题（重要）

1.5.1 什么是事务

ACID属性

1、原子性（atomicity)：全部成功，全部回滚失败。银行存取款。 
2、一致性（consistent)：银行转账的总金额不变。 
3、隔离性（isolation)： 

隔离性等级：

• 未提交读(READ UNCOMMITED) 脏读,两个事务之间互相可见；

• 已提交读(READ COMMITED)符合隔离性的基本概念,一个事务进行时，其它已提的事物对于该事务是可见的，即可以获取其它事务提交的数据。

• 可重复读(REPEATABLE READ) InnoDB的默认隔离等级。事务进行时，其它所有事务对其不可见，即多次执行读，得到的结果是一样的！

• 可串行化（SERIALIZABLE） 在读取的每一行数据上都加锁，会造成大量的锁超时和锁征用，严格数据一致性且没有并发是可使用。

查看系统的事务隔离级别：show variables like '%iso%'; 
修改事物的隔离级别：set session tx_isolation='read-committed'; 
4、持久性(DURABILITY)：从数据库的角度的持久性，磁盘损坏就不行了

 redo log机制保证事务更新的一致性和持久性

1.5.3 大事务

运行时间长，操作数据比较多的事务； 
风险：锁定数据太多，回滚时间长，执行时间长

锁定太多数据，造成大量阻塞和锁超时；

回滚时所需时间比较长，且数据仍然会处于锁定；

如果执行时间长，将造成主从延迟，因为只有当主服务器全部执行完写入日志时，从服务器才会开始进行同步，造成延迟。

解决思路：

避免一次处理太多数据，可以分批次处理；

移出不必要的SELECT操作，保证事务中只有必要的写操作。

二、什么影响了MySQL性能（非常重要）

2.1 影响性能的几个方面

1. 服务器硬件。

2. 服务器系统（系统参数优化）。

3. 存储引擎。 
   MyISAM： 不支持事务，表级锁。 
   InnoDB: 支持事务，支持行级锁，事务ACID。

4. 数据库参数配置。

5. 数据库结构设计和SQL语句。（重点优化）

2.2 MySQL体系结构

分三层：客户端->服务层->存储引擎 

1. MySQL是插件式的存储引擎，其中存储引擎分很多种。只要实现符合mysql存储引擎的接口，可以开发自己的存储引擎!

2. 所有跨存储引擎的功能都是在服务层实现的。

3. MySQL的存储引擎是针对表的，不是针对库的。也就是说在一个数据库中可以使用不同的存储引擎。但是不建议这样做。

2.3 InnoDB存储引擎

MySQL5.5及之后版本默认的存储引擎：InnoDB。

2.3.1 InnoDB使用表空间进行数据存储

show variables like 'innodb_file_per_table'; 
如果innodb_file_per_table 为 ON 将建立独立的表空间，文件为tablename.ibd；

如果innodb_file_per_table 为 OFF将数据存储到系统的共享表空间，文件为ibdataX（X为从1开始的整数）； 
.frm ：是服务器层面产生的文件，类似服务器层的数据字典，记录表结构。

2.3.2 (MySQL5.5默认)系统表空间与(MySQL5.6及以后默认)独立表空间

1. 系统表空间无法简单的收缩文件大小，造成空间浪费，并会产生大量的磁盘碎片。

2. 独立表空间可以通过optimeze table 收缩系统文件，不需要重启服务器也不会影响对表的正常访问。

3. 如果对多个表进行刷新时，实际上是顺序进行的，会产生IO瓶颈。

4. 独立表空间可以同时向多个文件刷新数据。

强烈建立对Innodb使用独立表空间，优化什么的会更方便可控。

2.3.3 系统表空间的表转移到独立表空间中的方法

1、使用mysqldump 导出所有数据库数据（存储过程、触发器、计划任务一起都要导出 ）可以在从服务器上操作。

2、停止MYsql 服务器，修改参数（my.cnf加入innodb_file_per_table），并删除Inoodb相关文件（可以重建Data目录）。

3、重启MYSQL，并重建Innodb系统表空间。

4、 重新导入数据。

或者 Alter table 同样可以的转移，但是无法回收系统表空间中占用的空间。

2.4.2 特性二：支持行级锁

1、InnoDB支持行级锁。

2、行级锁可以最大程度地支持并发。

3、行级锁是由存储引擎层实现的。

2.5 什么是锁

2.5.1 锁

2.5.2 锁类型

2.5.3 锁的粒度

MySQL的事务支持不是绑定在MySQL服务器本身，而是与存储引擎相关

将table_name加表级锁命令：lock table table_name write; 写锁会阻塞其它用户对该表的'读写’操作，直到写锁被释放：unlock tables；

1、锁的开销越大，粒度越小，并发度越高。

2、表级锁通常是在服务器层实现的。

3、行级锁是存储引擎层实现的。innodb的锁机制，服务器层是不知道的

2.5.4 阻塞和死锁

参考条件：

1、事务

2、备份(Innobd免费在线备份)

3、崩溃恢复

4、存储引擎的特有特性 
总结:Innodb大法好。 
注意:尽量别使用混合存储引擎，比如回滚会出问题在线热备问题。

sort_buffer_size #定义了每个线程排序缓存区的大小，MySQL在有查询、需要做排序操作时才会为每个缓冲区分配内存（直接分配该参数的全部内存）；
join_buffer_size #定义了每个线程所使用的连接缓冲区的大小，如果一个查询关联了多张表，MySQL会为每张表分配一个连接缓冲，导致一个查询产生了多个连接缓冲；
read_buffer_size #定义了当对一张MyISAM进行全表扫描时所分配读缓冲池大小，MySQL有查询需要时会为其分配内存，其必须是4k的倍数；
read_rnd_buffer_size #索引缓冲区大小，MySQL有查询需要时会为其分配内存，只会分配需要的大小。

select sum(index_length) from information_schema.tables where engine='myisam';

PRIMARY KEY（主键索引）  ALTER TABLE `table_name` ADD PRIMARY KEY ( `column` ) 
UNIQUE(唯一索引)     ALTER TABLE `table_name` ADD UNIQUE (`column`)
INDEX(普通索引)     ALTER TABLE `table_name` ADD INDEX index_name ( `column` ) 
FULLTEXT(全文索引)      ALTER TABLE `table_name` ADD FULLTEXT ( `column` )
组合索引   ALTER TABLE `table_name` ADD INDEX index_name ( `column1`, `column2`, `column3` ) 

 语法： ALTER TABLE table_name ADD KEY(column_name(prefix_length))

insert into students_1 values (1,'aaaabbcbdaaavvsdfasdfgasdfasdfasdfasdfasdfgasdfgadsf',1,1,now()),
(2,'aaacbbcbdaaavvsdfasdfgasadfasdfasdfsadfsdfgasdfgadsf',1,1,now()),
(3,'aaaebbcbdaaavvsdfasdfasdfaasdfasdfsdfgasdfgasdfgadsf',1,1,now()),
(4,'aaagbbcbdaaavvsdfasdfgasdasdfasdfasdfasdffgasdfgadsf',1,1,now());

SELECT COUNT(DISTINCT LEFT(sname, 1))/COUNT(*) AS sel3,
COUNT(DISTINCT LEFT(sname, 2))/COUNT(*) AS sel4,
COUNT(DISTINCT LEFT(sname, 3))/COUNT(*) AS sel5,
COUNT(DISTINCT LEFT(sname, 4))/COUNT(*) AS sel6,
COUNT(DISTINCT LEFT(sname, 5))/COUNT(*) AS sel7
FROM students_1;

slow_query_log # 启动停止记录慢查日志，慢查询日志默认是没有开启的可以在配置文件中开启(on)
slow_query_log_file # 指定慢查日志的存储路径及文件，日志存储和数据从存储应该分开存储

long_query_time # 指定记录慢查询日志SQL执行时间的阀值默认值为10秒通常,对于一个繁忙的系统来说,改为0.001秒(1毫秒)比较合适
log_queries_not_using_indexes #是否记录未使用索引的SQL

pt-query-digest --explain h=127.0.0.1,u=root,p=p@ssWord  slow-mysql.log

SELECT id,user,host,DB,command,time,state,info
FROM information_schema.processlist
WHERE TIME>=60

query_cache_type # 设置查询缓存是否可用
query_cache_size # 设置查询缓存的内存大小
query_cache_limit # 设置查询缓存可用的存储最大值（加上sql_no_cache可以提高效率）
query_cache_wlock_invalidate # 设置数据表被锁后是否返回缓存中的数据
query_cache_min_res_unit # 设置查询缓存分配的内存块的最小单

set profiling = 1; #启动profile,这是一个session级的配制执行查询

show profiles; # 查询每一个查询所消耗的总时间的信息

show profiles for query N; # 查询的每个阶段所消耗的时间

use performance_schema;
update setup_instruments set enabled='YES',TIME = 'YES' WHERE NAME LIKE 'stage%';

update set_consumbers set enabled='YES',TIME = 'YES' WHERE NAME LIKE 'event%';

alter table sbtest4 modify c varchar(150) not null default ''