MySql优化指南

一、背景

之前一致想写一个关于mysql优化的文章，由于各种原因一致被搁浅了。趁着最近做sql改造的时机就把这个坑给填了吧。

二、优化思路，应该从何处入手

2.1、数据量处理

你的操作数据已经很庞大了吗

2.2、数据库字段定义

你定义的字段是最优的吗

2.3、索引

你定义了那些索引，你的sql使用到索引了吗

2.4、sql语句优化

你的sql有雷区吗

三、针对上述的四点我们做一一描述

3.1、数据量处理

这里引用阿里巴巴开发手册中的一段描述：

14. 【推荐】单表行数超过 500 万行或者单表容量超过 2GB，才推荐进行分库分表。

说明：如果预计三年后的数据量根本达不到这个级别，请不要在创建表时就分库分表。

这里对分库分表做下简单描述（因为我也是没有操作过呀）

①为什么要分

随着业务量的增加，单表，单库的数据会越来越庞大的。相应的数据的增删改查的开销也会越来越大。

	分库分表前	分库分表后
并发情况	没有分布式部署，单机性能支持不了高并发	数据库集群，并发及数据吞吐上限提升
硬件设备	一台设备总会有瓶颈，磁盘等负荷越来越大	压力分摊
数据库性能	随着数据量的增加，sql越来越慢	单表数量少，压力分摊，性能提升

②怎么分

垂直拆分

.例如根据不同的业务进行拆分的，拆分成不同的数据库，比如会员数据库、订单数据库、支付数据库、消息数据库等，

垂直拆分在大型电商项目中使用比较常见。

水平拆分

.把同一张表中的数据拆分到不同的数据库中进行存储、或者把一张表拆分成 n 多张小表。

③会引发问题

事务问题

跨库跨表数据关联查询问题

数据同步问题

...

3.2、数据表字段定义

①在字段定义时遵循一个原则，尽量简单、占用空间最小。如：

如果长度能够满足，整型尽量使用tinyint、smallint、medium_int而非int。

            如果字符串长度确定，采用char类型。
            如果varchar能够满足，不采用text类型。
            精度要求较高的使用decimal类型，也可以使用BIGINT，比如精确两位小数就乘以100后保存。
            尽量采用timestamp而非datetime。

②尽量避免null值，text字段能容纳大量数据，但会印象到其他字段的查询性能（可以抽取到子表中）

3.3、索引

①索引类型

普通索引：最基本的索引。

            组合索引：多个字段上建立的索引，能够加速复合查询条件的检索。
            唯一索引：与普通索引类似，但索引列的值必须唯一，允许有空值。
            组合唯一索引：列值的组合必须唯一。
            主键索引：特殊的唯一索引，用于唯一标识数据表中的某一条记录，不允许有空值，一般用primary key约束。
            全文索引：用于海量文本的查询，MySQL5.6之后的InnoDB和MyISAM均支持全文索引。由于查询精度以及扩展性不佳，

更多的企业选择Elasticsearch。

②那些情况下索引会失效

.条件中有 or

.对于多列索引，不是使用的第一部分(第一个)，则不会使用索引

.like查询是以%开头

.如果列类型是字符串，那一定要在条件中将数据使用引号引用起来,否则不使用索引

.查询条件使用函数在索引列上，或者对索引列进行运算，运算包括(+，-，*，/，! 等)

.not in ,not exist.

3.4、sql优化

① <>的优化

<>无法使用索引，建议使用 != 或者 union all 关联

② or的优化

or无法使用索引，同样建议使用 union all 关联

③ in的优化

IN适合主表大子表小，EXIST适合主表小子表大。可以使用 join 关联查询

④ 不做列运算

⑤ 不做 select *

⑥ like 优化

避免使用 %开头的模糊查询，或者直接使用全文索引

⑦ join的优化

join查询是将前面的结果及作为循环数据，然后再后面的join中查询数据的。

尽量多匹配 on 的条件，少使用where 约束

⑧limit的优化

limit 分页越往后性能越差。

可以缩小其查询的范围

select * from orders where id > (select id from orders order by id desc limit 1000000, 1) order by id desc limit 0,10；

四、后记

本文章部分来源于网络，不正之处敬请斧正。同时也会持续更新完善 [坏笑]

更多精彩请关注guangmuhua.com