文章目录

  • mysql 优化
  • SQL优化
  • 服务器优化
  • 连接——配置优化
  • 优化器——SQL 语句分析与优化
  • SHOW PROCESSLIST


mysql 优化

SQL优化

服务器优化

连接——配置优化

从服务端来说，我们可以增加服务端的可用连接数。

  • show variables like ‘max_connections’; – 修改最大连接数，当有多个应用连接的时候

或者及时释放不活动的连接

  • show global variables like ‘wait_timeout’;默认8小时

Druid 的默认最大连接池大小是8。Hikari 的默认最大连接池大小是10。

建议是机器核数乘以2 加1。也就是说，4 核的机器，连接池维护9 个连接就够了

内存参数配置

每一个connection内存参数配置：
sort_buffer_size connection排序缓冲区大小
建议256K(默认值)-> 2M之内
当查询语句中有需要文件排序功能时，马上为connection分配配置的内存大小

join_buffer_size connection关联查询缓冲区大小
建议256K(默认值)-> 1M之内
当查询语句中有关联查询时，马上分配配置大小的内存用这个关联查询，所以有可能在一个查询语句中会分配很多个关联查询缓冲区

上述配置4000连接占用内存： 4000*(0.256M+0.256M) = 2G

wait_timeout

innodb_open_files

innodb_write_io_threads/innodb_read_io_threads

innodb_lock_wait_timeout

优化器——SQL 语句分析与优化

慢查询日志分析 –

客户端连接数和服务端连接池

mysql> show variables like 'thread%';
+-------------------+---------------------------+
| Variable_name     | Value                     |
+-------------------+---------------------------+
| thread_cache_size | 28                        |
| thread_handling   | one-thread-per-connection |
| thread_stack      | 262144                    |
+-------------------+---------------------------+
3 rows in set (0.00 sec)

mysql> show global status like '%Thread%';
+------------------------------------------+-------+
| Variable_name                            | Value |
+------------------------------------------+-------+
| Delayed_insert_threads                   | 0     |
| Performance_schema_thread_classes_lost   | 0     |
| Performance_schema_thread_instances_lost | 0     |
| Slow_launch_threads                      | 0     |
| Threads_cached                           | 0     |
| Threads_connected                        | 1     |
| Threads_created                          | 1     |
| Threads_running                          | 1     |
+------------------------------------------+-------+
8 rows in set (0.01 sec)

Threads_cached : 当前线程池中缓存有多少空闲线程
Threads_connected : 当前的连接数 ( 也就是线程数 )
Threads_created : 已经创建的线程总数
Threads_running : 当前激活的线程数 ( Threads_connected 中的线程有些可能处于休眠状态 )

  • **Threads_created：**创建过的线程数，如果发现Threads_created值过大的话，表明MySQL服务器一直在创建线程，这也是比较耗资源，可以适当增加配置文件中 thread_cache_size 值

SHOW PROCESSLIST

显示用户正在运行的线程

SHOW FULL PROCESSLIST;
select * from information_schema.processlist;

按客户端 IP 分组，看哪个客户端的链接数最多

SELECT
    client_ip,
    COUNT(client_ip) AS client_num
FROM
    (SELECT
        SUBSTRING_INDEX(HOST, ':', 1) AS client_ip
    FROM
        information_schema.PROCESSLIST) AS connect_info
GROUP BY client_ip
ORDER BY client_num DESC;