有一个查询很慢，27s，查询了一下mysql的状态：
mysql> SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool%';
+-------------------------------------------+--------------------------------------------------+
| Variable_name | Value |
+-------------------------------------------+--------------------------------------------------+
| Innodb_buffer_pool_dump_status | Dumping of buffer pool not started |
| Innodb_buffer_pool_load_status | Buffer pool(s) load completed at 241109 21:58:30 |
| Innodb_buffer_pool_resize_status | |
| Innodb_buffer_pool_resize_status_code | 0 |
| Innodb_buffer_pool_resize_status_progress | 0 |
| Innodb_buffer_pool_pages_data | 7168 |
| Innodb_buffer_pool_bytes_data | 117440512 |
| Innodb_buffer_pool_pages_dirty | 5 |
| Innodb_buffer_pool_bytes_dirty | 81920 |
| Innodb_buffer_pool_pages_flushed | 152905174 |
| Innodb_buffer_pool_pages_free | 1007 |
| Innodb_buffer_pool_pages_misc | 17 |
| Innodb_buffer_pool_pages_total | 8192 |
| Innodb_buffer_pool_read_ahead_rnd | 0 |
| Innodb_buffer_pool_read_ahead | 34590772215 |
| Innodb_buffer_pool_read_ahead_evicted | 46737241 |
| Innodb_buffer_pool_read_requests | 718733851921 |
| Innodb_buffer_pool_reads | 1798889354 |
| Innodb_buffer_pool_wait_free | 6345356 |
| Innodb_buffer_pool_write_requests | 506818935 |
+-------------------------------------------+--------------------------------------------------+
20 rows in set (0.26 sec)问了下Grok，它说我的默认配置太低了，数据库服务器是16G内存，数据库占用的很少，大部分是被一个java程序占用了，查了一下原来是当时安装了并未使用的apache-dolphinscheduler，这个东西很占资源，于是干掉。再改以下配置：
innodb_buffer_pool_size = 8G
innodb_buffer_pool_instances = 4
innodb_buffer_pool_dump_at_shutdown = 1
innodb_buffer_pool_load_at_startup = 1重启mysql以后：
mysql> SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool%';
+-------------------------------------------+--------------------------------------------------+
| Variable_name | Value |
+-------------------------------------------+--------------------------------------------------+
| Innodb_buffer_pool_dump_status | Dumping of buffer pool not started |
| Innodb_buffer_pool_load_status | Buffer pool(s) load completed at 250528 22:42:35 |
| Innodb_buffer_pool_resize_status | |
| Innodb_buffer_pool_resize_status_code | 0 |
| Innodb_buffer_pool_resize_status_progress | 0 |
| Innodb_buffer_pool_pages_data | 231482 |
| Innodb_buffer_pool_bytes_data | 3792601088 |
| Innodb_buffer_pool_pages_dirty | 0 |
| Innodb_buffer_pool_bytes_dirty | 0 |
| Innodb_buffer_pool_pages_flushed | 1455 |
| Innodb_buffer_pool_pages_free | 292740 |
| Innodb_buffer_pool_pages_misc | 66 |
| Innodb_buffer_pool_pages_total | 524288 |
| Innodb_buffer_pool_read_ahead_rnd | 0 |
| Innodb_buffer_pool_read_ahead | 226612 |
| Innodb_buffer_pool_read_ahead_evicted | 0 |
| Innodb_buffer_pool_read_requests | 4875046 |
| Innodb_buffer_pool_reads | 4713 |
| Innodb_buffer_pool_wait_free | 0 |
| Innodb_buffer_pool_write_requests | 4724 |
+-------------------------------------------+--------------------------------------------------+
20 rows in set (0.00 sec)
不过查询依然很慢，分析了一下请求来自laravel的一个多态关联表，4000W+行数据，两个关键字段是able_type和able_id，于是给这两个字段做了一个联合索引，idx_able，索引创建花了8分钟，索引创建过程中可能有锁，避开访问高峰时操作，完成以后查询降到了300ms以内。
总结：
laravel的多态表可以给able_type和able_id建联合索引。
发表回复