mysql 数据库与sql优化

MySQL是一个小型关系型数据库管理系统,MySQL被广泛地应用在Internet上的中小型网站中。由于其体积小、速度快、总体拥有成本低，尤其是开放源码这一特点，许多中小型网站为了降低网站总体拥有成本而选择了MySQL作为网站数据库。

本文将就以下三个问题进行展开：

1. 数据库-分库分表

2. 数据优化-sql优化

3. 误操作、程序 bug 时怎么办

一、库表设计

1.1 引擎选择

在 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 分库分表方法

在数据库表使用过程中，为了减小数据库服务器的负担、缩短查询时间，常常会考虑做分表设计。分表分两种，一种是纵向分表（将本来可以在同一个表的内容，人为划分存储在为多个不同结构的表）和横向分表（把大的表结构，横向切割为同样结构的不同表），一般根据业务的不同，会分为不同的数据库。

1.2.1其中，纵向分表常见的方式有根据活跃度分表、根据重要性分表等。其主要解决问题如下：

◆ 把活跃字段（常用）、惰性字段（不常用）分开。

◆ 表与表之间资源争用问题；

◆ 锁争用机率小；

◆ 实现核心与非核心的分级存储。

◆ 解决了数据库同步压力问题。

1.2.2 横向分表是指根据某些特定的规则来划分大数据量表

横向拆分优点
◆ 表关联基本能够在数据库端全部完毕；

◆ 不会存在某些超大型数据量和高负载的表遇到瓶颈的问题；

◆ 应用程序端总体架构修改相对较少；

◆ 事务处理相对简单；

◆ 仅仅要切分规则能够定义好。基本上较难遇到扩展性限制；

水平拆分的缺点

◆ 切分规则相对更为复杂，非常难抽象出一个能够满足整个数据库的切分规则；

◆ 后期数据的维护难度有所添加，人为手工定位数据更困难；

◆ 应用系统各模块耦合度较高，可能会对后面数据的迁移拆分造成一定的困难。

拆分后中间件可使用 mycal,《MyCat》是代替昂贵的oracle的MySQL集群中间件。

具体使用可参考 http://www.funblogs.cn/articles/2018/05/12/1526098791932.html

1.3 索引问题

索引是对数据库表中一个或多个列的值进行排序的结构，建立索引有助于更快地获取信息。 mysql 有四种不同的索引类型：

1. 主键索此 (PRIMARY)

2. 唯一索引 (UNIQUE)

3. 普通索引 (INDEX)

4. 全文索引（FULLTEXT , MYISAM 及 mysql 5.6 以上的 Innodb ）

建立索引的目的是加快对表中记录的查找或排序，索引也并非越多越好，因为创建索引是要付出代价的：一是增加了数据库的存储空间，二是在插入和修改数据时要花费较多的时间维护索引。

在设计表或索引时，常出现以下几个问题：

1. 少建索引或不建索引。这个问题最突出，建议建表时 DBA 可以一起协助把关。

2. 索引滥用。滥用索引将导致写请求变慢，拖慢整体数据库的响应速度（5.5 以下的 mysql 只能用到一个索引 )。

3. 从不考虑联合索引。实际上联合索引的效率往往要比单列索引的效率更高。

4. 非最优列选择。低选择性的字段不适合建单列索引，如 status 类型的字段。

二、数据优化-SQL优化

2.1 导致慢 SQL 的原因

在遇到慢 SQL 情况时，不能简单的把原因归结为 SQL 编写问题 (虽然这是最常见的因素)，实际上导致慢 SQL 有很多因素，甚至包括硬件和 mysql 本身的 bug。根据出现的概率从大到小，列如下：

1. SQL 编写问题

2. 锁

3. 业务实例相互干绕对 IO/CPU 资源争用

4. 服务器硬件

5. MYSQL BUG

2.2 由 SQL 编写导致的慢 SQL 优化

针对 SQL 编写导致的慢 SQL，优化起来还是相对比较方便的。正如上一节提到的正确的使用索引能加快查询速度，那么我们在编写 SQL 时就需要注意与索引相关的规则：

2.2.1创建索引 对于查询占主要的应用来说，索引显得尤为重要。很多时候性能问题很简单的就是因为我们忘了添加索引而造成的，或者说没有添加更为有效的索引导致。如果不加索引的话，那么查找任何哪怕只是一条特定的数据都会进行一次全表扫描，如果一张表的数据量很大而符合条件的结果又很少，那么不加索引会引起致命的性能下降。但是也不是什么情况都非得建索引不可，比如性别可能就只有两个值，建索引不仅没什么优势，还会影响到更新速度，这被称为过度索引。
2.2.2、复合索引 比如有一条语句是这样的：select * from users where area=‘beijing’ and age=22;
如果我们是在area和age上分别创建单个索引的话，由于mysql查询每次只能使用一个索引，所以虽然这样已经相对不做索引时全表扫描提高了很多效率，但是如果在area、age两列上创建复合索引的话将带来更高的效率。如果我们创建了(area, age, salary)的复合索引，那么其实相当于创建了(area,age,salary)、(area,age)、(area)三个索引，这被称为最佳左前缀特性。因此我们在创建复合索引时应该将最常用作限制条件的列放在最左边，依次递减。
2.2.3、索引不会包含有NULL值的列 只要列中包含有NULL值都将不会被包含在索引中，复合索引中只要有一列含有NULL值，那么这一列对于此复合索引就是无效的。所以我们在数据库设计时不要让字段的默认值为NULL。
2.2.4、使用短索引对串列进行索引，如果可能应该指定一个前缀长度。例如，如果有一个CHAR(255)的 列，如果在前10 个或20 个字符内，多数值是惟一的，那么就不要对整个列进行索引。短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作。
2.2.5、排序的索引问题 mysql查询只使用一个索引，因此如果where子句中已经使用了索引的话，那么order by中的列是不会使用索引的。因此数据库默认排序可以符合要求的情况下不要使用排序操作；尽量不要包含多个列的排序，如果需要最好给这些列创建复合索引。
2.2.6、like语句操作 一般情况下不鼓励使用like操作，如果非使用不可，如何使用也是一个问题。like “%aaa%” 不会使用索引而like “aaa%”可以使用索引。
2.2.7、不要在列上进行运算 select * from users where YEAR(adddate)<2007;
将在每个行上进行运算，这将导致索引失效而进行全表扫描，因此我们可以改成
select * from users where adddate<‘2007-01-01’;

2.2.8、不使用NOT IN和<>操作 NOT IN和<>操作都不会使用索引将进行全表扫描。NOT IN可以NOT EXISTS代替，id<>3则可使用id>3 or id<3来代替。
**2.2.9 or的使用、 ** or 的查询尽量用 union 代替 （Innodb）；

除了上述索引使用规则外，SQL 编写时还需要特别注意一下几点：

1. 尽量规避大事务的 SQL，大事务的 SQL 会影响数据库的并发性能及主从同步；

2. 分页语句 limit 的问题；

3. 删除表所有记录请用 truncate，不要用 delete；

4. 不让 mysql 干多余的事情，如计算；

5. 输写 SQL 带字段，以防止后面表变更带来的问题，性能也是比较优的 (涉及到数据字典解析，请自行查询资料)；

6. 在 Innodb 上用 select count(*)，因为 Innodb 会存储统计信息；

三、分析诊断工具

在日常开发工作中，我们可以做一些工作达到预防慢 SQL 问题，比如在上线前预先用诊断工具对 SQL 进行分析。常用的工具有：

1. mysqldumpslow

2. mysql profile

3. mysql explain

具体使用及分析方法在此就不赘述，网上有丰富的资源可以参考。

四、误操作、程序 bug 时怎么办

提出这个问题显然主要是针对刚开始工作的年轻同行们……实际上误操作和程序 bug 导致数据误删或者混乱的问题并非少见，但是刚入行的开发工作者会比较紧张。一个成熟的企业往往会有完善的数据管理规范和较丰富的数据恢复方案（初创公司除外），会进行数据备份和数据容灾。当你发现误操作或程序 bug 导致线上数据被误删或误改动时，一定不能慌乱，应及时与 DBA 联系，第一时间进行数据恢复（严重时直接停止服务），尽可能减少影响和损失。对于重要数据（如资金）的操作，在开发时一定要反复进行测试，确保没有问题后再上线。