写在前面,下面的文章写的很好,我的内容大多是根据其总结出来再加上自己的理解,大家参考可以优先看一下原作者的内容。
我必须得告诉你的MySQL优化原理1
我必须得告诉你的MySQL优化原理2
我必须得告诉你的MySQL优化原理3
mysql大致逻辑
首先要理解当你执行一条语句,mysql内部都干了什么才能进行对应的优化。
1.客户端向MySQL服务器发送一条查询请求
2.服务器首先检查查询缓存,如果命中缓存,则立刻返回存储在缓存中的结果。否则进入下一阶段
3.服务器进行SQL解析、预处理、再由优化器生成对应的执行计划
4.MySQL根据执行计划,调用存储引擎的API来执行查询
5.将结果返回给客户端,同时缓存查询结果
客户端与服务器之间通信
- 客户端用一个单独的数据包将查询请求发送给服务器,所以当查询语句很长的时候,需要设置max_allowed_packet参数。但是需要注意的是,如果查询实在是太大,服务端会拒绝接收更多数据并抛出异常。
- 只返回自己需要的列(不使用SELECT *),使用LIMIT。
原因:只返回自己需要的列,使用LIMIT可以减少服务器到客户端的传输数据量
查询缓存
- 查询缓存是有一个哈希值索引,这个哈希值通过查询本身、当前要查询的数据库、客户端协议版本号等一些可能影响结果的信息计算得来。所以两个SQL语句在任何字符上的不同(例如:空格、注释),都会导致缓存不会命中。
- 如果查询中包含任何用户自定义函数、存储函数、用户变量、临时表、mysql库中的系统表,其查询结果
都不会被缓存。比如函数NOW()或者CURRENT_DATE()会因为不同的查询时间,返回不同的查询结果,再比如包含CURRENT_USER或者CONNECION_ID()的查询语句会因为不同的用户而返回不同的结果,将这样的查询结果缓存起来没有任何的意义。 - 查询缓存会因为对表的改动而失效,那怕仅仅改变了一行的值。对查询缓存做失效处理时会消耗大量系统资源,而且缓存越大,消耗越大。所以查询缓存适用于相同查询密集,很少读写时使用。
- 所以具体使用查询缓存与否需要结合实际情况自己测试!!!
SQL语句解析
- 编写程序时使用prepare()函数+bindvalue()+execBatch(),因为这样相同的语句就只会解析一次,会节省很多时间。而使用for循环的方式执行多条拼接SQL语句,每次都会重新解析。
- 使用EXPLAIN语句,分析自己的SQL语句是否使用索引等,然后进行对应的优化。explain使用方法戳我
- 合理使用索引。
索引基础知识
索引内部数据结构实现
其他优化技巧
琐碎知识
-
通常来说把可为NULL的列改为NOT NULL不会对性能提升有多少帮助,只是如果计划在列上创建索引,就应该将该列设置为NOT NULL。
对整数类型指定宽度,比如INT(11),没有任何卵用。INT使用32位(4个字节)存储空间,那么它的表示范围已经确定,所以INT(1)和INT(20)对于存储和计算是相同的。 -
UNSIGNED表示不允许负值,大致可以使正数的上限提高一倍。比如TINYINT存储范围是-128 ~ 127,而UNSIGNED TINYINT存储的范围却是0 - 255。
-
通常来讲,没有太大的必要使用DECIMAL数据类型。即使是在需要存储财务数据时,仍然可以使用BIGINT。比如需要精确到万分之一,那么可以将数据乘以一百万然后使用BIGINT存储。这样可以避免浮点数计算不准确和DECIMAL精确计算代价高的问题。
-
TIMESTAMP使用4个字节存储空间,DATETIME使用8个字节存储空间。因而,TIMESTAMP只能表示1970 - 2038年,比DATETIME表示的范围小得多,而且TIMESTAMP的值因时区不同而不同。
大多数情况下没有使用枚举类型的必要,其中一个缺点是枚举的字符串列表是固定的,添加和删除字符串(枚举选项)必须使用ALTER TABLE(如果只只是在列表末尾追加元素,不需要重建表)。 -
schema的列不要太多。原因是存储引擎的API工作时需要在服务器层和存储引擎层之间通过行缓冲格式拷贝数据,然后在服务器层将缓冲内容解码成各个列,这个转换过程的代价是非常高的。如果列太多而实际使用的列又很少的话,有可能会导致CPU占用过高。
-
大表ALTER TABLE非常耗时,MySQL执行大部分修改表结果操作的方法是用新的结构创建一个张空表,从旧表中查出所有的数据插入新表,然后再删除旧表。尤其当内存不足而表又很大,而且还有很大索引的情况下,耗时更久。当然有一些奇技淫巧可以解决这个问题,有兴趣可自行查阅。
MySQL 使用SHOW STATUS LIKE ‘’ 查询一些性能优化参数
- Connections:连接MySQL服务器的次数
- Uptime:MySQL服务器的上线时间。
- Slow_queries:慢查询的次数。
- Com_select:查询操作的次数。
- Com_insert:插入操作的次数。
- Com_update:更新操作的次数。
- Com_delete: 删除操作的次数。
使用PROFILE来分析自己的查询
大批量插入优化
- 禁用唯一性检查
set unique_checks =0
set unique_checks =1 - 禁用外键检查
set foreign_key_checks=0;
set foreign_key_checks=1; - 禁止自动提交
关于这一点,在另一篇文章中有详细讲述。
戳我戳我 - 使用prepare()+bindvalue+execBatch()代替for循环;
- 使用insert values(),(),()
服务器配置优化
这是我之前自己不断尝试出来的,本地mysql配置中的内容,注释有中有详细解释。
戳我看各参数详情
[client]
port=3306
socket =/data/mysqldata/mysql.sock
[mysql]
default-character-set=utf8
[mysqld]
init_connect=‘SET autocommit=0’
user=mysql
port=3306
character-set-server=utf8
#设置默认数据库引擎
default-storage-engine=INNODB
#设置sql模式:禁止grant创建密码为空得用户,如果需要的存储引擎被禁用或未编译,那么抛出错误,一种严格得select查询GROUP BY操作,详细可参考网络上得解释
sql-mode=“NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,ONLY_FULL_GROUP_BY”
#最大连接数
max_connections=300
#指定查询结果缓存大小
query_cache_size=32M
#指定表的高速缓存,每打开一个表,表都会放入这里,可以加速访问,此值设置可以参考open_tables的值,若两者相等,则此值应该增加
table_open_cache=512
#设置线程缓存数,此值小的话,MySQL频繁创建线程,会消耗资源,配置的值参考:1G 8 、2G 16 、3G 32 。。。
thread_cache_size=38
#如果临时文件会变得超过索引,不要使用快速排序索引方法来创建一个索引
myisam_max_sort_file_size=1G
#MyISAM设置恢复表之时使用的缓冲区,REPAIR TABLE或用CREATE INDEX创建索引或ALTER TABLE过程中排序 MyISAM索引分配的缓冲区
myisam_sort_buffer_size=64M
#设置索引块的缓冲区大小
key_buffer_size=290M
#读查询操作的缓冲区大小
read_buffer_size = 1M
#设置随机读缓冲区大小
read_rnd_buffer_size = 8M
#设置MySQL执行排序的时候使用的大小
sort_buffer_size = 1M
#如下注解
innodb_flush_log_at_trx_commit=2
#innodb日志缓冲区大小,建议为1-8M
innodb_log_buffer_size=4M
#innodb缓冲区大小,此值越大,可以减少磁盘IO,一般设置为内存的80%
innodb_buffer_pool_size=2G
#设置innodb的日志文件大小,过大的话,将来做数据恢复会很慢
innodb_log_file_size=512M
#并发数限制,设置为0则表示不限制并发
innodb_thread_concurrency=18
#设置innodb为独立表空间模式,也就是每个表单独使用一个表空间,易于维护,
innodb_file_per_table = 1
#设置innodb文件格式
innodb_file_format = Barracuda
#交互式连接的超时时间,单位为秒,默认8小时
interactive_timeout = 86400
#非交互式连接的超时时间
wait_timeout = 2147482
#最大允许的包大小,
max_allowed_packet = 12M
#不使用DNS对连接进行解析
skip_name_resolve
#以下两个选项用来设置读写IO的线程数,根据CPU核数来设置
innodb_write_io_threads = 4
innodb_read_io_threads = 4