深入理解MySQL系列之优化

查询语句优化

1. 选取最适用的字段属性。设置合适表中字段的宽度，尽量把字段设置为NOTNULL，这样在将来执行查询的时候，数据库不用去比较NULL值。对于某些文本字段，例如“省份”或者“性别”，我们可以将它们定义为ENUM类型。因为在MySQL中，ENUM类型被当作数值型数据来处理，而数值型数据被处理起来的速度要比文本类型快得多

2. 使用连接（JOIN）来代替子查询

3. 使用联合(UNION)来代替手动创建的临时表

4. 使用事务保证数据一致性和完整性

5. 锁定表

6. 使用外键

7. 使用索引

8. 应尽量避免在 where 子句中使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描。

9. 应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描，如：select id from t where num is null。可以在num上设置默认值0，确保表中num列没有null值，然后这样查询：select id from t where num=0

10. 很多时候用 exists 代替 in 是一个好的选择

11. 用Where子句替换HAVING 子句 因为HAVING 只会在检索出所有记录之后才对结果集进行过滤

12. 先进行排序再使用limit进行排序(https://mp.weixin.qq.com/s/F5g0tKo__63ykVZfez38Dw)

13. 注意隐式转换，函数作用于表字段，会使得索引失效。

14. 不能利用索引进行混合排序

索引优化

建议创建索引列

• 在经常需要搜索的列上，可以加快搜索的速度；

• 在作为主键的列上，强制该列的唯一性和组织表中数据的排列结构；

• 在经常用在连接的列上，这些列主要是一些外键，可以加快连接的速度；

• 在经常需要根据范围进行搜索的列上创建索引，因为索引已经排序，其指定的范围是连续的；在经常需要排序的列上创建索引，因为索引已经排序，这样查询可以利用索引的排序，加快排序查询时间；

• 在经常使用在WHERE子句中的列上面创建索引，加快条件的判断速度。

不建议创建索引列

• 对于那些在查询中很少使用或者参考的列不应该创建索引。这是因为，既然这些列很少使用到，因此有索引或者无索引，并不能提高查询速度。相反，由于增加了索引，反而降低了系统的维护速度和增大了空间需求。

• 对于那些只有很少数据值的列也不应该增加索引。这是因为，由于这些列的取值很少，例如人事表的性别列，在查询的结果中，结果集的数据行占了表中数据行的很大比例，即需要在表中搜索的数据行的比例很大。增加索引，并不能明显加快检索速度。

• 对于那些定义为text, image和bit数据类型的列不应该增加索引。这是因为，这些列的数据量要么相当大，要么取值很少。

• 当修改性能远远大于检索性能时，不应该创建索引。这是因为，修改性能和检索性能是互相矛盾的。当增加索引时，会提高检索性能，但是会降低修改性能。当减少索引时，会提高修改性能，降低检索性能。因 此，当修改性能远远大于检索性能时，不应该创建索引。

索引优化：

• 最左前缀匹配原则，上面讲到了
  主键外检一定要建索引
  对 where,on,group by,order by 中出现的列使用索引
  尽量选择区分度高的列作为索引,区分度的公式是count(distinct col)/count(*)，表示字段不重复的比例，比例越大我们扫描的记录数越少，唯一键的区分度是1，而一些状态、性别字段可能在大数据面前区分度就是0

• 对较小的数据列使用索引,这样会使索引文件更小,同时内存中也可以装载更多的索引键

• 索引列不能参与计算，保持列“干净”，比如from_unixtime(create_time) = ’2014-05-29’就不能使用到索引，原因很简单，b+树中存的都是数据表中的字段值，但进行检索时，需要把所有元素都应用函数才能比较，显然成本太大。所以语句应该写成create_time = unix_timestamp(’2014-05-29’);
  为较长的字符串使用前缀索引

• 尽量的扩展索引，不要新建索引。比如表中已经有a的索引，现在要加(a,b)的索引，那么只需要修改原来的索引即可

• 不要过多创建索引, 权衡索引个数与DML之间关系，DML也就是插入、删除数据操作。这里需要权衡一个问题，建立索引的目的是为了提高查询效率的，但建立的索引过多，会影响插入、删除数据的速度，因为我们修改的表数据，索引也需要进行调整重建

• 对于like查询，”%”不要放在前面。
  SELECT * FROM table WHERE uname LIKE’xx%’ – 走索引
  SELECT * FROM table WHERE uname LIKE “%xx%” – 不走索引

• 查询where条件数据类型不匹配也无法使用索引
  字符串与数字比较不使用索引;
  CREATE TABLEa(achar(10));
  EXPLAIN SELECT * FROMaWHEREa=“1” – 走索引
  EXPLAIN SELECT * FROM a WHERE a=1 – 不走索引
  正则表达式不使用索引,这应该很好理解,所以为什么在SQL中很难看到regexp关键字的原因

数据库结构优化

1. 范式优化： 比如消除冗余（节省空间。。）

2. 反范式优化：比如适当加冗余等（减少join）

3. 拆分表： 垂直拆分和水平拆分。对于字段较多的表,如果某些字段使用频率较低,此时应当,将其分离出来从而形成新的表,

4. 增加中间表： 对于将大量连接查询的表可以创建中间表,从而减少在查询时造成的连接耗时.

5. 增加冗余字段：
   类似于创建中间表,增加冗余也是为了减少连接查询.

分析表、检查表、优化表

分析表主要是分析表中关键字的分布,检查表主要是检查表中是否存在错误,优化表主要是消除删除或更新造成的表空间浪费.

1.分析表: 使用 ANALYZE 关键字,如ANALYZE TABLE user;

2.检查表: 使用 CHECK关键字,如CHECK TABLE user [option](option 只对MyISAM有效)

3.优化表:使用OPTIMIZE关键字,如OPTIMIZE [LOCAL|NO_WRITE_TO_BINLOG] TABLE user;

LOCAL|NO_WRITE_TO_BINLOG都是表示不写入日志.,优化表只对VARCHAR,BLOB和TEXT有效,通过OPTIMIZE TABLE语句可以消除文件碎片,在执行过程中会加上只读锁.

服务器硬件优化

1. 硬件服务器优化

• 配置多核心和频率高的cpu,多核心可以执行多个线程
• 配置大内存,提高内存,即可提高缓存区容量,因此能减少磁盘I/O时间,从而提高响应速度.
• 配置高速磁盘或合理分布磁盘:高速磁盘提高I/O,分布磁盘能提高并行操作的能力.

2. 优化数据库参数

优化数据库参数可以提高资源利用率,从而提高MySQL服务器性能.MySQL服务的配置参数都在my.cnf或my.ini,下面列出性能影响较大的几个参数.

key_buffer_size:索引缓冲区大小
table_cache:能同时打开表的个数
query_cache_size和query_cache_type:前者是查询缓冲区大小,后者是前面参数的开关,0表示不使用缓冲区,1表示使用缓冲区,但可以在查询中使用SQL_NO_CACHE表示不要使用缓冲区,2表示在查询中明确指出使用缓冲区才用缓冲区,即SQL_CACHE.
sort_buffer_size:排序缓冲区

3. 分库分表
   对系统做分库分表 + 读写分离，也就是把一个库拆分为多个库，部署在多个数据库服务上，这时作为主库承载写入请求。然后每个主库都挂载至少一个从库，由从库来承载读请求。

4. 缓存集群

在写数据库的时候同时写一份数据到缓存集群里，然后用缓存集群来承载大部分的读请求。

explain 执行计划

慢查询分析

https://mp.weixin.qq.com/s/1r6lFQE4pxeo0zsV3yVkXg

mysql> show variables like 'long%';

long_query_time | 10.000000


mysql> set long_query_time=1;


mysql> show variables like 'slow%';

slow_launch_time    | 2

slow_query_log      | ON

slow_query_log_file | /tmp/slow.log

mysql> set global slow_query_log='ON'

日志分析工具mysqldumpslow

得到返回记录集最多的10个SQL。

mysqldumpslow -s r -t 10 /database/mysql/mysql06_slow.log

得到访问次数最多的10个SQL

mysqldumpslow -s c -t 10 /database/mysql/mysql06_slow.log

得到按照时间排序的前10条里面含有左连接的查询语句

mysqldumpslow -s t -t 10 -g “left join” /database/mysql/mysql06_slow.log

另外建议在使用这些命令时结合 | 和more 使用 ，否则有可能出现刷屏的情况。

mysqldumpslow -s r -t 20 /mysqldata/mysql/mysql06-slow.log | more

其他一些性能排除命令

1. top查看内存、cpu

2. 线程堆栈信息

printf “%x\n” pid
jstack pid|grep -A 30 “nid=0x1df9”

3. 检查数据库线程
   show full processlist

4. 查看存储引擎状态：show engine InnoDB status;

5. 查看最大连接数与响应最大连接数

show variables like ‘%max_connections%’;
show variables like ‘%max_user_connections%’;

更全面维度思考优化

https://mp.weixin.qq.com/s/-RpDVsOVU5Wyqmy7XWsEow

https://mp.weixin.qq.com/s/Puh-JGb5Y3kd_Dlmga1zVw
https://mp.weixin.qq.com/s/F5g0tKo__63ykVZfez38Dw
https://mp.weixin.qq.com/s/-RpDVsOVU5Wyqmy7XWsEow
https://mp.weixin.qq.com/s/ZBgv-s-_ojWMiTZYTVx-iQ