mysql优化效率_MySQL效率优化的一般思路

优化手段

基本上分成四个手段，数据库表结构优化，SQL语句优化，数据库参数配置的优化，硬件和系统级别的优化。其中最后一个是要花钱的，这里先不讨论。

数据库表结构和设计的优化

常见的数据库表一级的优化有如下一些方法：

1) 分库分表，读写分离的操作，对于数据量要有基本的预期，一般的单表超过千万记录就需要考虑分库分表的方案。

分库分表常用的框架有Sharding Sphere和MyCat，其中Sharding Sphere里面又分为Sharding JDBC，Sharding Proxy，Sharding SideCar三个子集，Sharding JDBC直接作用于Dao层，相当于对原JDBC操作的封装改造，Sharding Proxy相当于在数据库和Dao层中间加了一个代理层，和MyCat作用的位置相似，Sharding SideCar现在还没实现，先不用关注。下个主题会介绍一个基于Sharding JDBC做的分库分表的一个实验。

2)为字段选取合适的数据类型，原则是保留数据扩展能力情况下，优先选择小的数据结构。

3)对于字段过多的表可以做拆分，具体多少字段需要看实际的执行效率，必要时可以增加中间表。字段过多还会有本身维护复杂度的问题，比如三四十个或者更多的字段的表通常也会认为过于复杂不便于维护。

4)适度使用反范式，也就是某些场景可以不严格遵守三范式的要求，这样做可以提高查询效率，这种方式慎重使用，如果对查询效率提示不大就不用考虑了。

5)正确的使用索引，可以为经常作为查询条件的字段加索引，创建联合索引要考虑最左原则提供的索引复用能力，避免重复建索引，对唯一性可以保证的字段创建唯一索引等等。

6)字段尽量设置为NOT NULL，因为MySQL需要为空字段做很多特殊的处理，MySQL的优化器也很难对空字段做优化，空字段需要的存储空间也更多。

SQL语句优化

常见的优化有如下一些方法：

1)首先需要分析慢查询日志，找到需要优化的语句，或者是执行频率非常高的语句。

2)利用分析工具：explain、profile，其中explain用来分析执行计划的，主要看是否使用了索引，使用哪个索引，扫描了多少记录(这里主要看扫描的记录是否符合预期)。profile命令分析某个语句执行过程中分步的耗时。

3)避免使用SELECT *，只取需要的列，这样做一个是避免拿到多余的字段，二是可以避免查询列字段的元信息。

4)使用preparedstatement，一个是他性能更好，一个是可以防止SQL注入。

5)尽量在有索引的字段上进行排序操作，这样排序的时候会用上索引扫描。

数据库配置文件的优化

数据库配置文件的优化一般不需要普通开发人员掌握，经常用到的就是根据具体业务场景调整各种缓冲区的大小，属于DBA的工作范畴。配置文字是在windows环境的my.ini文件，linux环境的my.conf文件，这里我们简单了解一下配置文件里面的内容：

port=3306 //默认端口

default-character-set=utf8 //默认字符集

basedir="D:/MySql/" //安装地址

datadir="C:/ProgramData/MySQL/MySQL Server 5.5/Data/" //数据库的根地址

character-set-server=utf8 //服务端默认字符集

default-storage-engine=INNODB //默认存储引擎

sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION" //数据库模式

max_connections=100 //服务端允许的最大连接数，这个也是经常会根据业务场景修改提升性能的，不过也不能无限制改大还是要根据机器性能来

query_cache_size=0 //查询缓存大小，建议是读非常多，很少写的时候可以开启提升性能，其他的情况不建议使用

table_cache=256 //每个线程能够处理的表数上限

tmp_table_size=18M //临时表的大小限制

thread_cache_size=8 //线程池中的线程数

/*myisam引擎特有的配置*/

myisam_max_sort_file_size=100G //重建索引时myisam引擎的临时文件允许的大小

myisam_sort_buffer_size=35M //建索引时缓冲区大小

key_buffer_size=25M //主键缓冲区的大小

read_buffer_size=64K //全表扫描myisam表的时候的缓冲池大小

read_rnd_buffer_size=256K

sort_buffer_size=256K //重建索引的时候分配的缓冲区大小

/*innodb特有的配置*/

innodb_additional_mem_pool_size=2M //innodb用于存放元信息的内存池大小

innodb_flush_log_at_trx_commit=1 //设置1会让innodb在每次事务提交的时候刷事务日志

innodb_log_buffer_size=1M //innodb缓存日志数据的大小

innodb_buffer_pool_size=47M //innodb使用了缓冲池来缓存索引和行数据，这个值设的越大磁盘IO就越少，通常用作MySQL的服务器该值可以设置到物理内存的60%到80%

innodb_log_file_size=24M //每个innodb日志文件得大小

innodb_thread_concurrency=10 //innodb引擎允许的线程数，依赖操作系统硬件资源等

/*binlog相关配置*/

binlog-format=ROW //开启Binlog，并指定模式为ROW

log-bin=mysqlbinlog //指定log-bin名字为mysqlbinlog

对于配置文件里面的内容，有一些选择不依赖于复杂场景判断的其实我们也可以掌握，比如innodb_buffer_pool_size这种参数，只需要知道我们机器总的内存大小和机器的主要用途(是否还有其他必须要占用大量内存的操作)就可以准确的设置他的值了。

我的MySQL环境8.0.13的版本没有找到my.ini文件了，应该是在其他地方设置了，暂时没有找到地方，5.x.x的版本应该都是有这个文件的。

MySQL的执行顺序

这里要顺带说一下MySQL的执行顺序，不是按照书写顺序执行的。实际上Sql语句在分析器阶段就会形成一个解析树，就是一个语法树，关键词都挂在树节点上，SQL执行是逐个遍历树的节点实现的。之所以这样设计，主要的作用就是上一个阶段的执行结果返回可以提供给下一个阶段使用。

基本关键词的执行顺序像这样：from-> on-> join-> where-> group by-> having-> select-> distinct-> order by->limit。