mysql性能调优与架构设计

一.调优思路：

1.数据库设计与规划--以后再修该很麻烦，估计数据量，使用什么存储引擎
2.数据的应用--怎样取数据，sql语句的优化
3.mysql服务优化--内存的使用，磁盘的使用
4.操作系统的优化--内核、tcp连接数量
5.升级硬件设备

二.磁盘io规划

raid技术：raid0[xfs]
swap分区：最好使用raid0
磁盘分区：一个库放到一个分区上或一个磁盘上
物理分区
create table t1(id int,name char(20)) data directory='/data/' index directory ='/data';
mkdir /data
chown mysql.mysql /data

mysql> show variables like '%part%';

三.操作系统的优化

网卡bonding技术，
tcp连接数量限制
优化系统打开文件的最大限制
关闭操作系统不必要的服务

 

四.mysql服务优化

show status  看系统的资源
show variables  看变量，在my.cnf配置文件里定义的
show warnings   查看最近一个sql语句产生的错误警告，看其他的需要看.err日志
show processlist显示系统中正在运行的所有进程。
show errors

 

五.启用mysql慢查询：

---分析sql语句，找到影响效率的 SQL
log-slow-queries=/var/lib/mysql/slow.log 这个路径对mysql用户具有可写权限
long_query_time=2   查询超过2秒钟的语句记录下来
上面的 2 是查询的时间，即当一条 SQL 执行时间超过5秒的时候才记录，/var/lib/mysql/slow.log 是日志记录的位置。
然后重新启动MySQL服务

 

六.对查询进行缓存

query_cache_size 使用多大内存来缓存查询语句[+8M]
mysql> show variables like'%query%'
query_cache_size=8M
[root@st mysql]# vim /etc/my.cnf
mysql> show status like '%Qcache%';
Qcache_free_blocks：说明缓存太大了。缓存中相邻内存的个数。数目大说明可能有碎片。FLUSH QUERY CACHE会对缓存中的碎片进行整理，从而得到一个空闲块。[+8M]
Qcache_free_memory缓存中的空闲内存
Qcache_hits每次查询在缓存中命中时就增大
Qcache_inserts每插入一个查询时就增大。命中次数除以插入次数就是命中率。
Qcache_lowmen_prunes缓存出现内存不足并且必须要进行清理以便为更多查询提供空间的次数。这个数字最好长时间看；如果这个数字在不断增长就表示可能碎片非常严重，或者内存很少
Qcache_hits/Qcache_inserts 命中率

关键字缓冲区
mysql> show status like '%key%';
mysql> show variables like'key_buffer_size';

key_buffer_size  指定索引缓冲区的大小，它决定索引处理的速度，尤其是索引读的速度。[+8M]
key_read_requests  请求总数
key_reads   代表命中磁盘的请求个数
(key_read_requests-key_read)/key_read_requests：命中率
key_buffer_size只对MyISAM表起作用。即使你不使用MyISAM表，但是内部的临时磁盘表是MyISAM表，也要使用该值。可以使用检查状态值created_tmp_disk_tables得知详情。
对于1G内存的机器，如果不使用MyISAM表，推荐值是16M（8-64M）。

临时表空间大小：order by和group by时把数据放到临时表里。
tmp_table_size   占的是内存的大小，如果太小在排序时会出错
created_tmp_tables  创建临时表的数量
max_tmp_tables=32
tmpdir=/tmp  硬盘上临时表所在的位置

七.innodb表：

创建表空间文件
[mysqld]
innodb_data_file_path=ibdata1:10M:autoextend
这个设置配置一个可扩展大小的尺寸为10MB的单独文件，名为ibdata1。没有给出文件的位置，所以默认的是在MySQL的数据目录内。
如果你对最后的数据文件指定autoextend选项。如果数据文件耗尽了表空间中的自由空间，InnoDB就扩展数据文件。扩展的幅度是每次8MB。
要为一个自动扩展数据文件指定最大尺寸，请使用max属性。下列配置允许ibdata1涨到极限的500MB：
[mysqld]
innodb_data_file_path=ibdata1:10M:autoextend:max:500M
InnoDB默认地在MySQL数据目录创建表空间文件。要明确指定一个位置，请使用innodb_data_home_dir选项。比如，要使用两个名为ibdata1和ibdata2的文件，但是要把他们创建到/ibdata，像如下一样配置InnoDB：
[mysqld]
innodb_data_home_dir = /ibdata
innodb_data_file_path=ibdata1:50M;ibdata2:50M:autoextend

mysql> show variables like 'innodb_buffer_pool_size';
innodb_buffer_pool_size
对于InnoDB表来说，innodb_buffer_pool_size的作用就相当于key_buffer_size对于MyISAM表的作用一样。InnoDB使用该参数指定大小的内存来缓冲数据和索引。对于单独的MySQL数据库服务器，最大可以把该值设置成物理内存的80%。
根据MySQL手册，对于2G内存的机器，推荐值是1G（50%）。

mysql> show variables like 'innodb_%per%';［建议打开］
innodb_file_per_table =1 为每一个表单独创建一个表空间文件。
其他参数
skip-locking
取消文件系统的外部锁，减少出错几率增强稳定性

skip-name-resolve
关闭mysql的dns反查功能。这样速度就快了!
选项就能禁用DNS解析，连接速度会快很多。不过，这样的话就不能在MySQL的授权表中使用主机名了而只能用ip格式。

wait_timeout=10 终止空闲时间超过10秒的链接，避免长连接［默认8个小时］

max_connect_errors=10 //10次连接失败就锁定，使用flush hosts 解锁，
或mysqladmin flush-hosts解锁

 

八.SQL语句调优：

explain命令：查询select。
.type
这列很重要,显示了连接使用了哪种类别,有无使用索引
从最好到最差的连接类型为const、eq_reg、ref、range、indexhe和ALL