mysql 优化配置参考_Mysql优化和配置

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替代