Mysql优化和配置
一. Mysql配置
一次写入, 多次查询, 若mysql写入时间过长, 会影响客户体验. 故mysql读写时间都需要优化.
1, 大的缓冲池可以减小多次磁盘I/O访问相同的表数据 innodb_buffer_pool_size = 8G;
2, 缓存 SELECT 的结果并且在下一次同样查询的时候不再执行直接返回结果query_cache_size = 256M.
此份配置需长期优化, 以下为配置文件.
!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/mysql.conf.d/
[mysqld]
transaction-isolation = READ-COMMITTED
# Disabling symbolic-links is recommended to prevent assorted security risks;
# to do so, uncomment this line:
# symbolic-links = 0
[myisamchk]
# 关键词缓冲的大小, 一般用来缓冲 MyISAM 表的索引块
key_buffer_size = 32M
# 在导出非常巨大的表时需要此项
max_allowed_packet = 32M
# 线程使用的堆大小. 此容量的内存在每次连接时被预留.
thread_stack = 256K
# 在 cache 中保留的线程数用于重用
thread_cache_size = 64
# 只有小于此设定值的结果才会被缓冲
# 此设置用来保护查询缓冲,防止一个极大的结果集将其他所有的查询结果都覆盖.
query_cache_limit = 8M
# 用来缓冲 SELECT 的结果并且在下一次同样查询的时候不再执行直接返回结果.
query_cache_size = 256M
# 控制查询缓存功能的开启和关闭, 0表示关闭, 1表示打开
query_cache_type = 1
# MySQL 服务所允许的同时会话数的上限
max_connections = 550
#expire_logs_days = 10
#max_binlog_size = 100M
#log_bin should be on a disk with enough free space. Replace '/var/lib/mysql/mysql_binary_log'
#with an appropriate path for your system
#and chown the specified folder to the mysql user.
log_bin=/var/lib/mysql/mysql_binary_log
server_id=23233
bind-address=0.0.0.0
# For MySQL version 5.1.8 or later. For older versions, reference MySQL documentation for configuration help.
binlog_format = mixed
# MyISAM 表全表扫描的缓冲大小.
read_buffer_size = 4M
# 当在排序之后,从一个已经排序好的序列中读取行时,行数据将从这个缓冲中读取来防止磁盘寻道.
# 若增高此值, 可以提高很多 ORDER BY 的性能.
read_rnd_buffer_size = 16M
# 此缓冲当 MySQL 需要在 REPAIR, OPTIMIZE, ALTER 以及 LOAD DATA INFILE 到一个空表中引起重建索引时被分配.
# 这在每个线程中被分配.所以在设置大值时需要小心.
sort_buffer_size = 4M
# 如果应用中,很少出现join语句, 可不设置
join_buffer_size = 4M
# InnoDB settings
innodb_file_per_table = 1
innodb_flush_log_at_trx_commit = 2
# 足够大的日志缓冲区来保存脏数据在被写入到日志文件之前
innodb_log_buffer_size = 128M
# 大的缓冲池可以减小多次磁盘I/O访问相同的表数据
innodb_buffer_pool_size = 8G
二. 存储引擎
MyISAM引擎查询性能略好, 但在使用MyISAM做写入测试时(pymysql中的cursor.executemany方法),
1500w条数据写入时间大于1小时, 使用InnoDb写入时间仅需23分钟, 故最终使用InnoDb存储引擎
三. 索引
根据查询条件, 建单列索引和组合索引
注意最左匹配原则
参考1. Sql为 :
建表同时建索引
CREATE TABLE `flow_all` (
`id` int(11) NOT NULL,
`month` varchar(7) NOT NULL,
`date` datetime(6) NOT NULL,
`bankcode` varchar(20) NOT NULL,
`isinput` tinyint(1) NOT NULL,
`flow` float NOT NULL,
`balance` float NOT NULL,
`username` varchar(50) NOT NULL,
`classify` varchar(50) NOT NULL,
`remark` varchar(300) NOT NULL,
PRIMARY KEY (`id`),
KEY `index_month` (`month`),
KEY `index_date` (`date`),
KEY `index_bankcode` (`bankcode`),
KEY `index_isinput` (`isinput`),
KEY `index_username` (`username`),
KEY `index_classify` (`classify`),
KEY `index_month_username` (`month`,`username`),
KEY `index_date_username` (`date`,`username`)
) ENGINE=InnoDB AUTO_INCREMENT=120368 DEFAULT CHARSET=utf8 COMMENT='流水数据';
参考2. sql为 :
创建组合索引index_classify_username
alter table `flow_all` add index index_classify_username(`bankcode`,`classify`,`username`);
四. 注意事项
1, 设计方面,考虑最优字段,尽量不设空值
2, 使用事务或锁定表,来保证数据库数据完整性
3, 使用索引,索引能够更有效率的查询数据
4, 当数据量变大时
可以考虑做RAID
可以考虑分区和分表
若MyISAM, 可考虑数据文件和索引文件挂在不同磁盘上
5, 查询方面
* 尽量使用子查询完成多个逻辑的SQL操作,多使用JOIN,对于临时表 多使用联合查询来解决,这样能够保证数据库整齐性
* select * 改用 select 字段名,
* where条件下的以下语句会使索引失效, 慎用: null, !=, <>, or, in, not in, like 等.
* 在有索引的字段尽量不要使用函数操作,
* 尽量不要使用like进行模糊查询,影响系统性能
* in和not in 可用exist替代