高性能mysql之一

一、影响数据库性能的因素

1、sql查询速度

I、超高的QPS（每秒钟处理的查询量）和TPS（系统吞吐量）

II、大量的并发和超高的CPU使用率

2、服务器硬件

3、网卡流量

I、减少从服务器的数量

II、进行分级缓存

III、避免使用select * 查询

IIII、分离业务网络和服务器网络

4、磁盘IO

5、大表

影响：修改表结构会长时间锁表、会造成长时间主从延迟、影响正常的数据操作

解决方法：I、分库分表（难点：分表主键的选择、分表后跨分区数据的查询和统计）

      II、历史数据归档减少对前后端业务的影响（难点：归档时间点的选择、如何进行归档操作）

6、大事物

事物的特性：原子性、一致性、隔离性、持久性

隔离性：

处理方法：I、避免一次性处理太多数据
II、移出不必要在事物中的select操作
二、myISAM
特性：
I、并发性与锁级别
myISAM表级锁，对读写混合并发性不好
II、表损坏修复
可能造成数据丢失
通过 check table tablename 对表进行检查
repair table tablename进行恢复
也可以通过命令行工具myisamcheck进行修复，前提条件是先停止mysql服务
III、支持全文索引、支持数据压缩
myisampack进行压缩,压缩只能进行可读操作
适用场景：
非事务型应用（不支持事物）=>报表类应用
只读类应用
空间类应用=>GPS
三、Innodb
Innodb使用表空间进行数据存储、支持事物
innodb_file_per_table进行查看
show variables like ‘innodb_file_per_table’;
ON ：独立的表空间 tablename.ibd
OFF：系统表空间 ibdataX（从1开始的数字）
set global innodb_file_per_table=off设置关闭

独立表空间和系统表空间
比较：I、系统表空间无法简单的收缩文件大小
独立表空间可以通过optimize_table命令收缩系统文件
II、系统表空间会产生IO瓶颈
III、独立表空间可以同时向多个文件刷新数据

将系统表空间中的表转移到独立表空间的方法
I、使用mysqldump导出所有数据库表数据
II、停止MySQL服务， 修改配置参数,并删除innodb相关文件
III、重启MySQL服务，重建innodb系统表空间
IIII、重新导入数据

特性
I、Innodb支持事务的ACID特性
II、Redo Log Undo Log
III、支持行级锁（行级锁支持最大程度并发、是有存储引擎层实现的）
锁：主要作用是管理共享资源的并发访问、用于实现事务的隔离性
锁的类型：
共享锁（读锁）
独占锁（写锁）=>排他锁
读锁与读锁兼容 其他情况不兼容

锁的粒度
表级锁：开销小，并发低
行级锁：innodb 默认为行级锁

阻塞和死锁
阻塞事务占用被阻塞事务所占用资源
当加锁后，外面无法访问，出现阻塞
lock table tablename write 进行加锁（此锁为表级锁）
unlock tables 解锁

死锁：多个事务之间相互占用对方等待的资源

innodb状态检查
show engine innodb status
pager more 可以使命令行显示更多

四、Archive存储引擎
文件系统存储引擎：
以zlib对表数据进行压缩，磁盘I/O更少
数据储存在ARZ为后缀的文件中
只支持insert和select操作
只运行在自增ID列上增加索引

适用场景：日志和数据采集类应用

五、Memory存储引擎
数据保存在内存中，I/O效率高
支持HASH索引(等值查找)和BTree索引（范围索引）
所有字段均为固定长度
不支持BOLG和TEXT大字段
存储引擎使用的是表级锁

六、Federated存储引擎
提供了访问远程MySQL服务器上表的方法
本地不存数据，数据全部放在远程服务器上
本地需要保存表结构和远程连接服务信息
默认禁止 启动时须增加Fedatated=1参数
mysql://user_name[:password]@host_name[:port_num]/db_name/tbl_name

选择正确存储引擎参考条件：
事物
备份
崩溃恢复
存储引擎特有特性

七、参数配置
命令行参数
mysqld_safe –datadir=/data/sql_data
配置文件