写在前面，下面的文章写的很好，我的内容大多是根据其总结出来再加上自己的理解，大家参考可以优先看一下原作者的内容。
我必须得告诉你的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
519
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
