MySQL_优化

0 参考列表

思否:MySQL大表优化方案https://segmentfault.com/a/1190000006158186

1 语句优化

1.1 单表

1.1.1 where过滤

        ①区分度大的筛选字段先执行.

        ②不在筛选字段上使用函数和表达式.

        

1.1.2 分组

        ①使用窗口函数时,排序键区分度大的放在前面.

        ②数据量大时,用group by替代distinct.

1.1.3 其它

        ①列裁剪:不读取不需要的列,减少IO消耗.读取操作有select,where,join,group by,sort by等.

        ②大语句拆分成小语句.

1.2 索引

        ①不在索引上使用函数和表达式,避免null值判断;

        ②用union all代替or;

        ③不在字段开头模糊查询(%a);

        ④避免不等判断;

        ⑤避免隐式类型转换.    

1.3 多表

        ①多表关联查询时,小表在前,大表在后

        ②多表关联时,能减少数据量的表先关联.

        ③join子句的关联键区分度大的先执行.

2 结构优化

2.1 字段

        ①单表不要有太多字段,建议在20以内.

        ②整数少用int,而用tinyint、smallint、mwdium_int代替;如果整数是非负的,则加上unsigned.

        ③使用枚举或者整数代替字符串类型.

        ④varchar 的长度只分配需要的空间.

        ⑤尽量用timestamp代替datetime.

        ⑥避免使用null字段.

2.2 索引

        ①针对性创建索引,考虑 where 过滤的列和 order by 的列;

        ②字符字段最好不要做主键,或者使用前缀索引;

        ③值分布很少的字段不适合建立索引.

3 高阶优化

3.1 数据分片(分库分表)

3.1.1 垂直分片

        根据数据库里面的数据表的相关性进行拆分

        ①专库专用,按照业务将表进行归类,分布到不同的数据库或表中.

        ②把一个多字段的大表按常用字段和非常用字段,拆分到不同表中,每个表的记录数相同,只是字段不一样,使用主键关联.

3.1.2 水平分片

        通过某种策略将数据分片来存储,分库内分表和分库两部分,每片数据会分散到不同的MySQL表或库.分片策略有范围分片、时间分片、字段冷热拆分.如表分区.

3.2 读写分离

        为了确保数据库的稳定性,很多数据库拥有双机热备功能.即一台数据库服务器提供增删改业务,一台数据库服务器提供查询业务.

3.3 使用缓存

        ①MySQL内部:参数调优;

        ②数据访问层:比如MyBatis针对SQL语句做缓存;

        ③应用服务层:通过编程手段对缓存做到更精准的控制和更多的实现策略;

        ④Web层:针对web页面做缓存;

        ⑤浏览器客户端:用户端的缓存.