mysql数据库开发常见问题及优化
mysql 数据库是被⼴泛应⽤的关系型数据库，其体积⼩、⽀持多处理器、开源并免费的特性使其在
Internet 中⼩型⽹站中的使⽤率尤其⾼。在使⽤ mysql 的过程中不规范的 SQL 编写、⾮最优的策略选择都可能导致系统性能甚⾄功能上的缺陷。
开发过程中 mysql 数据库设计及使⽤的常见问题，并提出相关优化⽅案。进⾏了⼀次⼩结，结合⾃⼰的⼯作经历及理解形成此⽂以供分享，希望能有助于各位同⾏解决⼯作中的相关问题。
本⽂将就以下三个问题进⾏展开：
1.库表设计
2.慢SQL 问题
3.误操作、程序 bug 时怎么办
库表设计
引擎选择
在 mysql 5.1 中，引⼊了新的插件式存储引擎体系结构，允许将存储引擎加载到正在运新的 mysql 服务器中。使⽤ mysql 插件式存储引擎体系结构，允许数据库专业⼈员或者设计库表的软件开发⼈员为特定的应⽤需求选择专门的存储引擎，完全不需要管理任何特殊的应⽤编码要求，也⽆需考虑所有的底层实施细节。因此，尽管不同的存储引擎具有不同的能⼒，应⽤程序是与之分离的。此外，使⽤者可以在服务器、数据库和表格三个层级中存储引擎，提供了极⼤的灵活性。
mysql 常⽤的存储引擎包括 MYISAM、Innodb 和 Memory，其中各⾃的特点如下：
1.MYISAM : 全表锁，拥有较⾼的执⾏速度，⼀个写请求请阻塞另外相同表格的所有读写请求，并发性能差，占⽤空间相对较
⼩，mysql 5.5 及以下仅 MYISAM ⽀持全⽂索引，不⽀持事务。
2.Innodb：⾏级锁（SQL 都⾛索引查询），并发能⼒相对强，占⽤空间是 MYISAM 的 2.5 倍，不⽀持全⽂索引（5.6 开始⽀
持），⽀持事务
3.Memory : 全表锁，存储在内存当中，速度快，但会占⽤和数据量成正⽐的内存空间且数据在 mysql 重启时会丢失。
基于以上特性，建议绝⼤部份都设置为 innodb 引擎，特殊的业务再考虑选⽤ MYISAM 或 Memory ，如全⽂索引⽀持或极⾼的执⾏效率等。
分表⽅法
在数据库表使⽤过程中，为了减⼩数据库服务器的负担、缩短查询时间，常常会考虑做分表设计。分表分两种，⼀种是纵向分表（将本来可以在同⼀个表的内容，⼈为划分存储在为多个不同结构的表）和横向分表（把⼤的表结构，横向切割为同样结构的不同表）。
其中，纵向分表常见的⽅式有根据活跃度分表、根据重要性分表等。其主要解决问题如下：
1.表与表之间资源争⽤问题；
2.锁争⽤机率⼩；
3.实现核⼼与⾮核⼼的分级存储，如UDB登陆库拆分成⼀级⼆级三级库
4.解决了数据库同步压⼒问题。
横向分表是指根据某些特定的规则来划分⼤数据量表，如根据时间分表。其主要解决问题如下：
1.单表过⼤造成的性能问题；
2.单表过⼤造成的单服务器空间问题。
索引问题
索引是对数据库表中⼀个或多个列的值进⾏排序的结构，建⽴索引有助于更快地获取信息。 mysql 有四种不同的索引类型：
1.主键索此 ( PRIMARY )
2.唯⼀索引 ( UNIQUE )
3.普通索引 ( INDEX )
4.全⽂索引（FULLTEXT , MYISAM 及 mysql
5.6 以上的 Innodb ）
建⽴索引的⽬的是加快对表中记录的查或排序，索引也并⾮越多越好，因为创建索引是要付出代价的：⼀是增加了数据库的存储空间，⼆是在插⼊和修改数据时要花费较多的时间维护索引。
在设计表或索引时，常出现以下⼏个问题：
1.少建索引或不建索引。这个问题最突出，建议建表时 DBA 可以⼀起协助把关。
2.索引滥⽤。滥⽤索引将导致写请求变慢，拖慢整体数据库的响应速度（5.5 以下的 mysql 只能⽤到⼀
个索引)。
3.从不考虑联合索引。实际上联合索引的效率往往要⽐单列索引的效率更⾼。
4.⾮最优列选择。低选择性的字段不适合建单列索引，如 status 类型的字段。
慢 SQL 问题
导致慢 SQL 的原因
在遇到慢 SQL 情况时，不能简单的把原因归结为 SQL 编写问题(虽然这是最常见的因素)，实际上导致慢 SQL 有很多因素，甚⾄包括硬件和 mysql 本⾝的 bug。根据出现的概率从⼤到⼩，罗列如下：
1.SQL编写问题
2.锁
3.业务实例相互⼲绕对 IO/CPU 资源争⽤
4.服务器硬件
5.MYSQL BUG
由 SQL 编写导致的慢 SQL 优化
sql优化的几种方式针对SQL编写导致的慢 SQL，优化起来还是相对⽐较⽅便的。正如上⼀节提到的正确的使⽤索引能加快查询速度，那么我们在编写 SQL 时就需要注意与索引相关的规则：
1.字段类型转换导致不⽤索引，如字符串类型的不⽤引号，数字类型的⽤引号等，这有可能会⽤不到索引导致全表扫描；
3.不要在字段前⾯加减运算；
4.字符串⽐较长的可以考虑索引⼀部份减少索引⽂件⼤⼩，提⾼写⼊效率；
5.like % 在前⾯⽤不到索引；
6.根据联合索引的第⼆个及以后的字段单独查询⽤不到索引；
7.不要使⽤ select *；
8.排序请尽量使⽤升序 ;
< 的查询尽量⽤ union 代替 （Innodb）；
10.复合索引⾼选择性的字段排在前⾯；
除了上述索引使⽤规则外，SQL 编写时还需要特别注意⼀下⼏点：
1.尽量规避⼤事务的 SQL，⼤事务的 SQL 会影响数据库的并发性能及主从同步；
2.分页语句 limit 的问题；
3.删除表所有记录请⽤ truncate，不要⽤ delete；
4.不让 mysql ⼲多余的事情，如计算；
5.输写 SQL 带字段，以防⽌后⾯表变更带来的问题，性能也是⽐较优的 ( 涉及到数据字典解析，请⾃⾏查询资料)；
6.在 Innodb上⽤ select count(*)，因为 Innodb 会存储统计信息；
7.慎⽤ Order by rand()。
分析诊断⼯具
在⽇常开发⼯作中，我们可以做⼀些⼯作达到预防慢 SQL 问题，⽐如在上线前预先⽤诊断⼯具对 SQL 进⾏分析。常⽤的⼯具有：
具体使⽤及分析⽅法在此就不赘述，⽹上有丰富的资源可以参考。
误操作、程序 bug 时怎么办
提出这个问题显然主要是针对刚开始⼯作的年轻同⾏们……实际上误操作和程序 bug 导致数据误删或者混乱的问题并⾮少见，但是刚⼊⾏的开发⼯作者会⽐较紧张。⼀个成熟的企业往往会有完善的数据管理规范和较丰富的数据恢复⽅案（初创公司除外），会进⾏数据备份和数据容灾。当你发现误操作或程序 bug 导致线上数据被误删或误改动时，⼀定不能慌乱，应及时与 DBA 联系，第⼀时间进⾏数据恢复（严重时直接停⽌服务），尽可能减少影响和损失。对于重要数据（如资⾦）的操作，在开发时⼀定要反复进⾏测试，确保没有问题后再上线。

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。