本文将就以下两个问题进行展开:
库表设计
慢 SQL 问题
一、库表设计
1、引擎选择
mysql 常用的存储引擎包括 MYISAM、Innodb 和 Memory,其中各自的特点如下:
1)MYISAM : 全表锁,拥有较高的执行速度,一个写请求请阻塞另外相同表格的所有读写请求,并发性能差,占用空间相对较小,mysql 5.5 及以下仅 MYISAM 支持全文索引,不支持事务。
2)Innodb:行级锁(SQL 都走索引查询),并发能力相对强,占用空间是 MYISAM 的 2.5 倍,不支持全文索引(5.6 开始支持),支持事务。
3)Memory : 全表锁,存储在内存当中,速度快,但会占用和数据量成正比的内存空间且数据在 mysql 重启时会丢失。
基于以上特性,建议绝大部份都设置为 innodb 引擎,特殊的业务再考虑选用 MYISAM 或 Memory ,如全文索引支持或极高的执行效率等。
2、分表方法
在数据库表使用过程中,为了减小数据库服务器的负担、缩短查询时间,常常会考虑做分表设计。分表分两种,一种是纵向分表(将本来可以在同一个表的内容,人为划分存储在为多个不同结构的表)和横向分表(把大的表结构,横向切割为同样结构的不同表)。
其中,纵向分表常见的方式有根据活跃度分表、根据重要性分表等。其主要解决问题如下:
1)表与表之间资源争用问题;
2)锁争用机率小;
3)实现核心与非核心的分级存储,如UDB登陆库拆分成一级二级三级库;
4)解决了数据库同步压力问题。
横向分表是指根据某些特定的规则来划分大数据量表,如根据时间分表。其主要解决问题如下:
1)单表过大造成的性能问题;
2)单表过大造成的单服务器空间问题。
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 问题
1、导致慢 SQL 的原因
在遇到慢 SQL 情况时,不能简单的把原因归结为 SQL 编写问题(虽然这是最常见的因素),实际上导致慢 SQL 有很多因素
1)SQL编写问题
2)锁
3)业务实例相互干绕对 IO/CPU 资源争用
4)服务器硬件
5)MYSQL BUG
2、由 SQL 编写导致的慢 SQL 优化
针对SQL编写导致的慢 SQL,优化起来还是相对比较方便的。正如上一节提到的正确的使用索引能加快查询速度,那么我们在编写 SQL 时就需要注意与索引相关的规则:
1)字段类型转换导致不用索引,如字符串类型的不用引号,数字类型的用引号等,这有可能会用不到索引导致全表扫描;
2)mysql 不支持函数转换,所以字段前面不能加函数,否则这将用不到索引;
3)不要在字段前面加减运算;
4)字符串比较长的可以考虑索引一部份减少索引文件大小,提高写入效率;
5)like % 在前面用不到索引;
6)根据联合索引的第二个及以后的字段单独查询用不到索引;
7)不要使用 select *;
8)排序请尽量使用升序 ;
9)or 的查询尽量用 union 代替 (Innodb);
10)复合索引高选择性的字段排在前面;
11)order by / group by 字段包括在索引当中减少排序,效率会更高。
除了上述索引使用规则外,SQL 编写时还需要特别注意以下几点:
1)尽量规避大事务的 SQL,大事务的 SQL 会影响数据库的并发性能及主从同步;
2)分页语句 limit 的问题;
3)删除表所有记录请用 truncate,不要用 delete;
4)不让 mysql 干多余的事情,如计算;
5)输写 SQL 带字段,以防止后面表变更带来的问题,性能也是比较优的 ( 涉及到数据字典解析,请自行查询资料);
6)在 Innodb 上用 select count(*),因为 Innodb 会存储统计信息;