mysql优化

---

优化流程

• 表设计,使用E-R图理清实体关系,使用三大范式,反范式优化表结构,选取合适的字段类型.
• 存储引擎,写多,一致性要求高的使用支持事务的InnoDB存储引擎.MyISAM读取性能更好.
• 索引优化,根据搜索,排序,分组字段设置索引,主键,外键,唯一索引,组合索引.
• 配置优化,最大连接数(max_connections),InnoDB缓冲池(innodb_buffer_size),查询缓存(query_cache_type)
• 语句优化,根据业务优化语句逻辑

表设计

三大范式,第一范式:字段原子性,第二范式:字段依赖于主键,第三范式:字段不存在传递依赖,反范式:冗余提交查询效率.

理清实体间的关系,从而决定是否需要使用关联表.

使用合适的字段类型,可以有效的减少存储空间,也提高了I/O执行效率.

数据量大,表垂直拆分,列数尽可能的小.

操作系统对大文件的操作会慢很多,可以大表转小表,分区或者分表.

可以明确的字段建议使用默认值,少用null,因为null有独立的语句操作会复杂一点,也会对索引有影响.

分库,根据业务不同把相关的表切分到不同的数据库中.

分表,垂直分表多字段改成多个表,水平分表一个表分成多个表原有结构不变.

分区(partition),避免进行物理分表的替代方案,有垂直分区和水平分区.

存储引擎

InnoDB

• 支持事务(transaction)
• 行级锁(gap locks)
• 缓冲池(innodb_buffer_size)
• 支持外键约束
• 支持全文索引(mysql5.6以后)

MyISAM

• 不支持事务
• 表级锁
• 读写互相阻塞,不仅会在写入的时候阻塞读取,MyISAM还会再读取的时候阻塞写入,但读本身并不会阻塞另外的读
• 读取速度较快
• 不支持外键约束,支持全文索引

Memory

• 将数据存在内存中,每一个表和一个磁盘文件关联.
• 容易丢失数据

CSV

csv是用逗号进行字段分隔的文件,这种表不支持索引.

csv它的做点在于其他的外部程序能直接读取csv格式的数据,因此csv作为一种数据交换机制使用.

索引优化

为什么要使用索引

索引可以理解为已经排好序的记录,这样就可以通过算法如二分查找,快速定位记录和跳出查找.
不使用索引,单表无序状态下要查找一条记录,可能需要扫描整张表.
对于连表操作,会产生笛卡尔积,这会成指数增长,性能严重下降.

索引,会降低写入语句的速度,会占用更多的磁盘空间.

• 索引和数据在关闭表共享后,可以单独的存放在两个独立的表空间(innodb_file_per_table=OFF)
• 聚簇索引(通常为主键)它的叶子结点包括整行记录;二级索引(非聚簇索引),要拿自己记录的聚簇索引的值去取记录,二级索引要经过二次查询.
• 创建索引原则,在区分度大的字段上建索引,在频繁更新的字段上少建索引
• 组合索引,最左匹配原则,注意顺序
• 短索引,比较操作速度更快,I/O消耗小,节省存储空间

配置优化

公共参数优化

// 同时处理最大连接数,推荐设置最大连接数是上限连接数的80%左右   
max_connections = 151
// 查询排序时缓冲区大小,只对order by和group by起作用,可增大此值为16M
sort_buffer_size = 2M
// 打开文件数限制,如果show global status like 'open_files'查看的值等于或者大于open_files_limit值时,程序会出现无法连接数据库或卡死
open_files_limit = 1024 

InnoDB参数优化

SET GLOBAL innodb_buffer_pool_instances = 1

// 索引和数据缓冲区大小,一般设置为物理内存的60%~70%
innodb_buffer_pool_size = 128M
// 缓冲池实例个数,推荐设置为4个或8个
innodb_buffer_pool_instances = 1    
// 关键参数
// 0代表大约每秒写入到日志并同步到磁盘
// 1代表每执行一条语句后就写入到日志并同步到磁盘,I/O开销大,安全性高,效率低.
// 2代表只把日志写入到系统缓存区,再每秒同步到磁盘,效率很高,如果服务器故障才会丢失事务数据.
innodb_flush_log_at_trx_commit = 1  
// 默认是共享表空间,推荐开启独立表空间模式,每个表的索引和数据都在自己独立的表空间中,可以实现单表在不同数据库中移动,也方便以后分区.
innodb_file_per_table = OFF  
// 日志缓冲区大小,由于日志最长每秒种刷新一次,所以一般不用超过16M
innodb_log_buffer_size = 8M  

linux系统优化

vim /etc/sysctl.conf

// 默认是60s,time_wait超时时间,tcp四次挥手中主动关闭的一方需要等待2MSL
net.ipv4.tcp_fin_timeout = 30
// 默认是0关闭,1表示开启,允许将状态为time_wait的socket重新用于新的tcp连接(端口复用),一个socket占用一个端口
net.ipv4.tcp_tw_reuse = 1
// 1表示开启,time_wait状态的socket快速回收
net.ipv4.tcp_tw_recycle = 1  
// 系统保持time_wait状态的socket最大数量,超出则随机清除一些time_wait状态的socket并打印警告信息
net.ipv4.tcp_max_tw_buckets = 4096   
// 进入syn同步等待队列最大长度,加大队列长度可以容纳更多的等待连接
net.ipv4.tcp_max_syn_backlog = 4096

too many files open

vim /etc/security/limits.conf

* soft nofile 65535
* hard nofile 65535

// *代表所有用户
// 立刻生效
ulimit -SHn 65535

读写分离

开启binlog日志,即可以做集群,也可以做热备.

语句优化

开启慢查询日志

mysql> set global slow-query-log=on  #开启慢查询功能
mysql> set global slow_query_log_file='/var/log/mysql/mysql-slow.log';  #指定慢查询日志文件位置
mysql> set global log_queries_not_using_indexes=on;   #记录没有使用索引的查询
mysql> set global long_query_time=1;   #只记录处理时间1s以上的慢查询
mysqldumpslow -t 3 /var/log/mysql/mysql-slow.log    #查看最慢的前三个查询

• 单列排序,少用多列排序,在where子句中使用了索引则order by中的列是不会使用索引的
• 后通配符搜索,%word%将无法使用索引,而word%可以使用索引
• 闭区间查询,用查询的条件尽可能是明确的值或区间,not in,>,<,无法使用索引
• 少用函数,在字段上使用函数处理会导致索引失效
• 多用数字运算,少用字符串运算
• 较小类型够用时,就不用较大类型
• 把数据列声明为not null,不用额外判断该值是否为null,可以写出更简单的查询语句
• explain分析语句的使用情况
• 条件字段加索引
• 外键字段加索引
• 避免全表扫描
• 调整锁定策略
• 排序,分组中的列最好是第一张表的列
• 组合索引,注意索引的顺序,最左匹配原则
• 使用in(),而不使用not in(),现在的范围查询可以使用索引

如果有一个ORDER BY子句和一个不同的GROUP BY子句， 或者如果 ORDER BY或GROUP BY 包含来自连接队列中第一个表以外的表的列， 则会创建一个临时表。