前几篇我们已经完成了数据库的表设计。
接下来问题是如何对MySQL进行调优,怎么优化SQL以及MySQL的配置,才能发挥MySQL的性能?
一.SQL语句的优化
对SQL语句的优化主要体现在对索引的使用
书写SQL的时候要注意,某些特殊的语句是没有办法使用索引的。
- 使用模糊查询的时候,%如果写在前面,是没有办法通过索引检索的。
- Order By 语句如果是对索引的属性排序,速度会很快。
- 使用is null 或者 is not null ,MySQL查询的时候会放弃使用索引,而是使用遍历检索。可以设置一些逻辑上不可能出现的值来表示NULL
- 因为索引是按照大小排列的,因此不要使用!=这样的符号来进行索引,可以使用同时满足小于和大于的条件来检索。
- 少使用OR运算符,这样第一个OR条件可能会使用索引,但是第二个是不会使用索引检索的,可以通过UNION ALL 把两个检索条件合并。(最左分配原则)
- 避免运算符出现在表达式的左侧,这样同样不会使用索引检索。
二.MySQL的参数调优
在MySQL数据库中,我们可以通过设置参数来优化MySQL的配置,主要的形式是修改my.cnf,然后重启MySQL。
MySQL的重要参数有以下几个:
1. max-connections 最大连接数 。
- 表示MySQL的最大并发连接数,默认值是151,MySQL允许的最大连接上限是16000多。
- 实际连接数是最大连接数的85%比较合适。
- 注意不要盲目地调大max_connections,因为每一个连接都会占用一个缓冲池。
- show variable like 'max_connection' 用于查看最大连接数值
- show status like 'max_used_connections' 用于查看曾经出现过的最大连接数
2. back_log 请求堆栈
- 连接数到达最大连接数之后,数据库会把多余的请求放在堆栈中,back_log的值就是指堆栈中允许的最多请求数量,默认值是50。
- back_log为max_connections的30%比较恰当。
3. innodb_thread_concurrency 并发线程数
- 指MySQL允许的最大线程数量,默认值为0,表示不设上限。
- 因为线程数变多之后,线程的调度同样要耗费大量的资源。
- 最佳值是CPU核心的2倍。
4. innodb_buffer_pool_size 指 InnoDB的缓存容量
- 数据库的查询缓存是缓存select结果集,当某个数据删改之后,会造成大面积的缓存失效。
- 与数据库的查询缓存不同,innoDB不会把查询的结果集缓存在内存上,而是缓存一部分数据以及索引,提升查询的效率。
- 默认值为128M,最佳容量是内存的70%。
三.查看慢查询日志
MySQL提供了慢查询日志,帮我们记录SQL中花费时间超过规定的时间的语句。
show variables like ‘slow_query%’
可以查询出当前的慢查询日志是否开启,以及日志的存放目录。
同样的在my.cnf中去修改参数
slow_query_log = on 开启慢日志
log_query_time =1 把规定的时间设置为1S
然后按照之前的慢查询日志的目录,我们就可以看到执行较慢的语句的具体信息。
可以通过explain去分析执行较慢语句的执行情况。