MySql相关优化分享

文章目录

前言

现在在网上搜索，有很多类似文章，mysql优化大全mysql 模糊搜索 优化，mysql最强总结等等，部分文章存在一些错误。在这里个人总结整理的一些点，希望对大家有所帮助。

本篇从数据库、表、sql语句几个维度来说优化，如果文章有误之处欢迎指正~

一、数据库优化 1.选择合适存储引擎

MySQL5.5版本开始，InnoDB已经成为Mysql的默认引擎(之前是MyISAM)，说明其优势是有目共睹的。如果你不知道用什么存储引擎，那就用InnoDB，至少不会差。

如何选择呢：

是否要支持事务，如果要请选择 InnoDB，如果不需要可以考虑 MyISAM；如果表中绝大多数都只是读查询，可以考虑 MyISAM，如果既有读，写也挺频繁，请使用InnoDB。系统奔溃后，MyISAM恢复起来更困难，能否接受，不能接受就选 InnoDB； 2.选择合适的连接池

1：性能方面 hikariCP>druid>tomcat-jdbc>dbcp>c3p0 。hikariCP的高性能得益于最大限度的避免锁竞争。

2：druid功能最为全面，sql拦截等功能，统计数据较为全面，具有良好的扩展性。

3：HikariCP因为细节方面优化力度较大，性能方面强于Druid

4：综合性能，扩展性等方面，可考虑使用druid或者hikariCP连接池。

注：SpringBoot 2.0以后默认连接池是hikariCP。

3.分库分表

在数据量增长和增长速度越来越高的情况下，单库可能在容量、IO、并发性能上都无法支撑，这个时候就要对业务进行切分或数据库进行扩展，数据库的扩展也就是分库分表。

分库分表的方式有垂直拆分和水平拆分。

垂直拆分是根据业务进行拆分，这种拆分不能解决单业务点数据量大的问题。

水平拆分是根据某一列进行拆分（如id，userId），拆分后的每个库结构一致。

4.主从同步

一般部署架构为一台 Master 和 n 台 Slave，Master 的主责为写，并将数据同步至 Slave，Slave 主要提供查询功能。

可以使用数据库中间件，例如MyCat来实现。MyCat的读写分离是建立在MySQL主从复制基础之上实现的，Mycat读写分离和自动切换机制，需要mysql的主从复制机制配合。

二、表优化 1.表中的字段选择合适的数据类型

1、当一个列可以选择多种数据类型时，应该优先考虑数字类型，其次是日期和二进制类型，最后是字符类型。

2、对于相同级别的数据类型，应该优先选择占用空间小的数据类型。

2.适当添加索引 3.表中适当保留冗余数据

具体做法是： 在概念数据模型设计时遵守第三范式，降低范式标准的工作放到物理数据模型设计时考虑。降低范式就是增加字段，减少了查询时的关联，提高查询效率。

4.增加中间表

对于需要经常联合查询的表，可以建立中间表以提高查询效率。通过建立中间表，把需要经常联合查询的数据插入到中间表中，然后将原来的联合查询改为对中间表的查询，以此来提高查询效率。

5.字段很多的表分解成多个表

对于字段比较多的表，如果有些字段的使用频率很低，可以将这些字段分离出来形成新表。因为当一个表的数据量很大时，会由于使用频率低的字段的存在而变慢。

6.添加适当存储过程

一个存储过程是一个可编程的函数，它在数据库中创建并保存，一般由 SQL语句和一些特殊的控制结构组成。当希望在不同的应用程序或平台上执行相同的特定功能时，存储过程尤为合适。

存储过程与SQL语句如何抉择：

架构设计没有绝对，只有在当前的场景下最合适的。

普通的项目开发中，不建议大量使用存储过程，对比SQL语句，存储过程适用于业务逻辑复杂，比较耗时，同时请求量较少的操作，例如后台大批量查询、定期更新等。

（1）当一个事务涉及到多个SQL语句时或者涉及到对多个表的操作时可以考虑应用存储过程

（2）在一个事务的完成需要很复杂的商业逻辑时可以考虑应用存储过程

（3）比较复杂的统计和汇总可以考虑应用后台存储过程

三、SQL语句优化 1.尽量使用表的别名,减少解析

当在SQL语句中连接多个表时, 使用表的别名并把别名前缀于每个Column上，这样一来，就可以减少解析的时间并减少那些由Column歧义引起的语法错误。

2.select子句中避免使用*号 3.将where中用的比较频繁的字段建立索引，避免全表扫描

1.普通索引：这是最基本的索引类型，而且它没有唯一性之类的限制。

2.唯一索引：和普通索引基本相同，只是索引列的所有值都只能出现一次，即必须唯一。

3.主键索引：就是 唯一 且 不能为空。主键索引是一种特殊的唯一索引。必须指定为“PRIMARY KEY”。

4.联合索引：多列值组成一个索引，专门用于组合搜索。

5.全文索引：用于在一篇文章中，检索文本信息的，适合在进行模糊查询的时候使用。

提示点：

4.避免索引失效情况

索引失效情况

1、like查询以“%”开头；(这个范围非常大，所以没有使用索引的必要了)

2、or查询左右都没有使用索引；(or可以使用unint)

3、联合索引中没有使用第一列索引；(为遵循最左匹配原则)

4、在where中索引列上使用“not”，“”，“!=”；(不等于操作符可能不会用到索引的，产生全表扫描)

5、在where中索引列上使用函数或进行计算操作，索引失效。(更改字段导致失效)

6、如果mysql觉得全表扫描更快时（数据少时）

7、在索引列上使用“IS NULL”或“IS NOT NULL”操作，索引可能失效（如果列上全部数据不为空，索引会失效，但是如果有空值，索引不会失效）

…

5.当只需要一条数据的时候可以使用limit 1

这是为了使EXPLAIN中type列达到const类型

6.调整Where字句中的连接顺序

采用自下而上的顺序解析where字句，根据这个原理表连接最好写在其他where条件之前，那些可以过滤掉最大数量记录。

7.小表驱动大表

SQL中使用in：

如果sql语句中包含了in关键字，则它会优先执行in里面的子查询语句，然后再执行in外面的语句。所以假如in里面的数据量很少，作为条件查询速度更快。

SQL中使用exists：

如果sql语句中包含了exists关键字，它会优先执行exists左边的语句（即主查询语句）。然后把它作为条件，去跟右边的语句匹配。

如果匹配上，则可以查询出数据。如果匹配不上，数据就被过滤掉了。

这个需求中，如果order表有10000条数据，而user表有100条数据。order表是大表，user表是小表。如果order表在左边，则用in关键字性能更好。

总结一下：

in 适用于左边大表，右边小表。

exists 适用于左边小表，右边大表。

8.善用 EXPLAIN 查看SQL执行计划

EXPLAIN select  column_name from table_name;

type列，访问类型。一个好的sql语句至少要达到range(范围)级别。杜绝出现all级别。

ALL、index、range、 ref、eq_ref、const、system、NULL（从左到右，性能从差到好）key列，使用到的索引名。如果没有选择索引，值是NULL。可以采取强制索引方式。key_len列，索引长度。rows列，扫描行数。该值是个预估值 。extra列，详细说明。注意常见的不太友好的值有：Using filesort, Using temporary。

具体的优化步骤：

1、首先要避免全表扫描，检查是否有索引。

2、查看索引是否生效。

3、sql结构的优化。

4、数据库表设计的优化。

（编辑：成都站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!