MySQL设计规范与性能优化

引言

MySQL是目前使用最为广泛的关系型数据库之一，如果使用得当，可支撑企业级高并发、高可靠服务，使用不当甚至连并发量略高的个人网站都难以支撑；

就算使用了缓存，大量的数据库访问依旧在所难免，即使设置了较长的缓存有效期，而且缓存命中率较理想，但缓存的创建和过期后的重建都是需要访问数据库的； 

本文主要从MySQL表结构设计规范和MySQL自身性能优化两方面来讨论该如何对MySQL数据库进行优化；

MySQL表结构设计规范

1. 数据库设计命名规范

（1）数据库,数据表一律使用前缀，前缀名称一般不超过5字；

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

# 正式数据库名使用小写英文以及下划线组成,尽量说明是哪个应用或者系统在使用的； mcs_webim ops_portal    # 备份数据库名使用正式库名加上备份时间组成； ops_portal_20150621 mcs_webim_20150622    # 相关应用的数据表使用同一前缀，前缀名称一般不超过5字； webim_user webim_group    # 备份数据表名使用正式表名加上备份时间组成； webim_user_20150620 webim_group_20150620

（2）字段名称使用单词组合完成，首字母小写，后面单词的首字母大写（驼峰式），最好是带表名前缀；

1 2 3 4 5 6

# 如表webim_user的字段 userId userName userPassword # 表与表之间的相关联字段要用统一名称； # 用尽量少的存储空间来存储一个字段的数据；

2. 数据库规范化设计

（1）范式化设计

实际关系模式设计中，一般遵循第三范式——在一个数据表中，非主键字段之间不能存在依赖关系；

具体可参考：规范化—数据库设计原则

（2）反范式化设计

举例：在页面显示我的好友列表；
1.遵循第三范式

1 2	(用户ID, 好友ID) (用户ID, 用户昵称, 用户邮箱, 注册时间, 联系电话)

 

2.反范式化设计

1 2	(用户ID, 好友ID, 好友昵称) (用户ID, 用户昵称, 用户邮箱, 注册时间, 联系电话)

 

反范式化问题：
1. 数据冗余；
2. 更新导致数据不一致问题，可通过定期同步的手段来修改不一致数据；

反范式化优势：
减少读取数据的开销，这点非常重要，需要根据不同场景来适当使用反范式化设计；

 MySQL自身性能优化

MySQL自身优化主要从如下几方面来介绍：

1. Query语句优化；
2. 慢查询优化；
3. MySQL锁机制分析优化；
4. 参数配置优化；

Query语句优化

1. MySQL状态报告

1 2	show status; show engine innodb status;

一些更加友好的第三方工具：mysqlreport（下载地址），mysqltunner，mytop等，可展示更加友好的状态报告；

2. 正确使用索引

如果索引使用不当，其他任何优化将毫无意义；

索引目的

索引类似于书的目录，通过不断的缩小想要获得数据的范围来筛选出最终想要的结果，加快查询速度；

索引类型

索引分单列索引和组合索引，组合索引的一个索引包含多列；
MySQL索引类型包括：

（1）普通索引——没有任何限制

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20

# 直接创建索引 create index indexName on tableName(columnName(length)); # 如果是char，varchar类型，length长度可以小于字段实际长度； # 如果是blob和text类型，必须指定length；   # 修改表结构 alter tableName add index indexName on (columnName(length));   # 创建表时直接指定 create table tableName(     id int not null,     username varchar(16) not null,     index indexName (columnName(length)) );   # 删除索引 drop index indexName on tableName;   # 查看索引 show index from tableName;

（2）唯一索引——索引列的值必须唯一，允许有空值，如果是组合索引，则列值的组合必须唯一；

1 2 3 4 5 6 7 8 9 10 11 12

# 直接创建索引 create unique index indexName on tableName(columnName(length));   # 修改表结构 alter tableName add unique indexName on (columnName(length));   # 创建表时直接指定 create table tableName(     id int not null,     username varchar(16) not null,     unique indexName columnName(length) );

（3）主键索引——是唯一索引的一种，但不允许有空值，一般是建表的时候直接创建主键索引；

1 2 3 4 5

create table tableName(     id int not null,     username varchar(16) not null,     primary key(id) );

（4）组合索引——为多列添加索引；

1 2 3 4

# 假设数据表中name, age, address, zip等多个字段，需要为name, age, zip建立组合索引； create index indexName on tableName(name(10), age, zip); # 或 alter table tableName add index indexName (name(10), age, zip);

对于varchar类型字段，如果长度过长，最好限制一下索引的长度，可以加快索引查询速度，减少索引文件的大小；

最左前缀匹配

如上面的name，age，zip的组合索引，如下的组合都会用到该索引，可使用explain来进行分析：

1 2 3 4 5 6 7 8 9 10 11

select * from tableName where name="lee" and age=20 and zip="050000"; select * from tableName where name="lee" and age=20; select * from tableName where name="lee";   # 组合索引对于包含order by和group by的查询也可发挥作用，同样遵循最左前缀原则（对于Hash索引，对order by无效）； select * from tableName order by name, age, zip; select * from tableName where name="lee" order by age, zip;   # 对于group by，一般需要先将记录分组后放在新的临时表中，然后分别进行函数运算，如count()，sum()，max()等； # 如有恰当的索引，可使用索引来取代创建临时表； select count(id) from tableName where sex='m' group by age, zip;

查询优化器会自动调整条件表达式的顺序，以匹配组合索引；
建立索引时一定要注意顺序，(key1, key2)和(key2, key1)完全不同；

建立索引的时机

一般来说，在where和join中出现的列需要建立索引，mysql只对<，<=，=，>，>=，between，in以及某些时候（不以通配符%和_开头的查询）的like才会使用索引；

1 2	select a.name from table1 as a left join table2 b on a.name=b.username where b.age=20 and b.zip='053000'; # 此时，需要对username，age和zip建立索引；

索引的不足之处

索引有很大优势，但是不能滥用，需要根据实际情况来决定到底使不使用索引，该为哪些字段建索引，一般在查询量占比较多的表才会建立索引；

• 索引会降低更新表的速度，如insert，update，delete操作，更新表时不仅需要保存数据，还要保存索引文件；
• 过多的组合索引会大大加剧索引文件的膨胀速度，引起磁盘空间存储的问题，一个包含多个字段的组合索引的尺寸可能已经超过了数据本身，而且索引过多，可也能会使MySQL选择不到要使用的最好的索引（可使用use index(key_list)来指定查询时使用的索引）；
• 对于唯一值的列，索引效果最好，对于具有多个重复值的列，如年龄或性别，建立索引不是好办法；

使用索引注意事项

1. 索引不会包含有NULL值的列，在数据库设计时尽量不要让字段的默认值为NULL，否则无法建立相关字段的索引；
2. 使用短索引，对varchar类型字段建索引时最好指定长度，只要保证前n个字符多数值是唯一的即可，提高查询速度，节省磁盘空间，降低I/O操作；
3. MySQL查询只是用一个索引，因此如果一条查询语句中有多个字段需要建索引，最好按照最左前缀匹配原则建立组合索引；
4. like语句一般不鼓励使用，在数据量大的情况下，非常容易造成性能问题，如果非使用，通配符%一定要放到后面，如like "abc%"；
5. 不要在列上进行运算，如select * from users where YEAR(datetime)<2015;，会导致索引失效，进行全表扫描；
6. 不要使用NOT IN和IN；

索引的原理

主要参考：MySQL索引原理及慢查询优化

3. join语句优化

join语句分为内连接和外连接；

内连接：

1 2 3

select * from a inner join b on a.id = b.id; # 等价于 select * from a,b where a.id = b.id;

内连接就是检索出与连接条件完全匹配的数据行；

外连接：

1	select id, name, action from user as u left join user_action a on u.id = a.user_id;

外连接保留了所有驱动表的数据，匹配表中无法匹配的数据则以NULL输出；

外连接工作原理

从左表读取一条记录，选出所有与on中条件匹配的右表记录的（n条）数据，进行连接，形成n条记录（包括重复的行），如果右边没有与on条件匹配的记录，那连接的字段都是null，继续读下一条；

找出所有在左表而不在右表中的记录：

1 2 3 4 5 6 7

# 注意：a.user_id必须声明为NOT NULL，如果a，u两表连接条件中的两个列具有相同的名字，可使用using(col); select id, name, action from user as u     left join user_action a on u.id = a.user_id     where a.user_id is NULL; # 查询时手动指定索引 select * from table1 use index (key1, key2) where key1=1 and key2=2 and key3=3; select * from table1 ignore index (key3) where key1=1 and key2=2 and key3=3;

慢查询优化

开启慢查询日志：

1 2 3

# 在my.cnf中增加如下配置： long_query_time = 1 log-slow-queries = /var/log/mysql/mysql_slow.log

# 将所有没有使用索引的查询记录也记录下来（根据需要决定是否开启）： log-queries-not-using-indexes

慢查询工具mysqlsla，可使用此工具对慢查询日志进行分析；

1	# mysqlsla -lt slow /var/log/mysql/mysql_slow.log

大多数慢查询都是因为索引使用不当造成的，使用索引时一定要谨慎，其他原因还有查询语句本身太过复杂（多表联合查询），数据表记录数太多等；

锁机制分析与优化

锁机制是影响查询性能的另一个重要因素；

查询的时间开销主要包括两部分：

1. 查询本身的计算时间；（主要受索引影响） 
2. 查询开始前的等待时间；（受锁机制影响）

减少表锁定等待

MyISAM类型表提供了表级别锁定，可使用mysqlreport来查看等待表锁定查询所占的比例；

MyISAM的表锁定允许多线程同时读取数据，如select查询，无需锁等待；

对于更新操作，如update、insert、delete操作，会排斥对当前表的所有查询，并且更新操作有着默认的更高优先级，即当表锁释放后，更新操作将先获得锁定，全部执行完毕后，才轮到读取操作，应尽量避免在有大量查询请求时，批量更改数据表，否则非常容易造成慢查询；

可使用如下命令监视所有线程的状态：

1	show processlist\G;

结论：

1 2	对于以查询操作为主，并且更新操作耗时较低的应用，将不会存在太多的锁等待，可以使用MyISAM存储引擎； 对于有频繁数据更新并且查询请求量也不低的站点，必须使用提供行锁定功能的Innodb存储引擎；

行锁定

Innodb存储引擎提供了行锁定的支持；

行锁定优势：在select和update混合的情况下，行锁定可以解决读和写互斥的问题，由于update操作和select操作来自不同的线程，并且针对的是不同行的记录，可以并发进行；

行锁定并不一定总是好的：

1. 行锁定的开销并不比表锁定小；

2. 在全部都是更新操作的场景下，行锁定耗时可能会更长，虽然表锁定每次只有一个线程处于Updating状态，而行锁定所有线程都是Updating状态，但锁定只是一种逻辑层面的约束，即使全部线程都是Updating状态，但是磁盘的物理写操作还是串行执行的；

3. 对于全部查询的场景，行锁定也需要更多额外的开销，速度相对表锁定略慢；

存储引擎查看

1 2	show table status from DataBaseName where name='TableName'; alter table tableName type=myisam;

参数配置优化

事务性表性能优化

Innodb存储引擎除了支持行锁定，外键以及其易于修复的特性，另一个优势就是其支持事务（ACID），当然，事实上大多数站点都不需要事务级别的保障；
Innodb是通过预写日志（WAL）方式来实现事务的，即当有事务提交时，首先写入内存中的事务日志缓冲区，随后当事务日志写入磁盘时，Innodb才更新实际的数据和索引；
如果选择使用事务，那事务日志何时写入磁盘，就是一个优化点了；

1 2 3 4 5 6 7 8 9 10

# 事务提交时立即将事务日志写入磁盘，数据和索引也立即更新，符合持久性原则； innodb_flush_log_at_trx_commit = 1   # 事务提交时不立即写入磁盘，每隔1S写入磁盘文件一次，并刷新到磁盘，同时更新数据和索引； # 如果mysql崩溃，事务日志缓冲区中最近1秒内的数据永久性丢失； innodb_flush_log_at_trx_commit = 0   # 事务提交时立即写入磁盘文件，但间隔1S才会刷新磁盘，同时更新数据和索引； # 操作系统崩溃才会造成数据损失； innodb_flush_log_at_trx_commit = 2

注意：

“写入磁盘文件”只是将数据写入位于物理内存中的内核缓冲区，“刷新到磁盘“是将内核缓冲区中的数据真正写入到磁盘；

将innodb_flush_log_at_trx_commit设置为0，可以获得最好的性能，同时数据丢失的可能性也最大；如果希望尽量避免数据丢失，可设置为2；

1 2	# 设置Innodb数据和索引的内存缓冲池大小，一般可设置为服务器物理内存的80%； innodb_buffer_pool_size = 12G

使用查询缓存

目的：将select的查询结果缓存在内存中，以供下次直接获取；

1 2 3

query_cache_type = 1 query_cache_size = 64M query_cache_limit = 1M

对于缓存过期策略，MySQL采用的机制是：当一个表有更新操作后，涉及这个表的所有查询缓存都会失效；

这个看场景，对于密集select操作且很少更新的表，比较适合使用查询缓存；对于select和update混合的应用，不适合使用查询缓存；

临时表

目的：在磁盘上创建临时表非常耗时，开销大，需要降低在磁盘上创建临时表的次数；

1 2	# 尽量给临时表设置较大的内存空间，当内存空间不够时，MySQL将会启用磁盘来保存临时表； tmp_table_size = 512M

线程池

MySQL采用多线程来处理并发连接，如果每次都新建连接，都要创建新的线程，在系统繁忙的时候，也会增加MySQL的开销；

1 2 3

# 尽量使用持久连接，减少线程的重复创建； thread_cache_size = 100 # 可以使MySQL缓存100个线程；

参考

• http://c.biancheng.net/cpp/html/1468.html
• http://database.51cto.com/art/200910/156685.htm
• http://tech.meituan.com/mysql-index.html
• 《构建高性能Web站点》

 

 

【转自：http://www.cnblogs.com/pflee/p/4592647.html】