差異處
這裏顯示兩個版本的差異處。
兩邊的前次修訂版 前次修改 下次修改 | 前次修改 | ||
tech:mysqltips [2020/08/20 12:41] – [2. 設定與查詢週期定時執行程序] jonathan_tsai | tech:mysqltips [2022/08/03 15:24] (目前版本) – jonathan | ||
---|---|---|---|
行 1: | 行 1: | ||
- | ====== MySQL 相關技巧彙整 ====== | + | ====== MySQL/ |
===== 1. 資料表效能優化 ===== | ===== 1. 資料表效能優化 ===== | ||
* 參考 - https:// | * 參考 - https:// | ||
行 8: | 行 8: | ||
where round(data_free/ | where round(data_free/ | ||
order by data_free_mb; | order by data_free_mb; | ||
- | </ | + | </ |
+----------------+----------------+--------------+ | +----------------+----------------+--------------+ | ||
| table_name | | table_name | ||
行 17: | 行 17: | ||
+----------------+----------------+--------------+ | +----------------+----------------+--------------+ | ||
3 rows in set (0.006 sec) | 3 rows in set (0.006 sec) | ||
- | </xtermrtf> | + | </cli> |
- 針對呈現出來的 Table 進行優化 <code sql> | - 針對呈現出來的 Table 進行優化 <code sql> | ||
OPTIMIZE TABLE dwg_files, pdm_to_erp_log, | OPTIMIZE TABLE dwg_files, pdm_to_erp_log, | ||
- | </ | + | </ |
+--------------------------+----------+----------+-------------------------------------------------------------------+ | +--------------------------+----------+----------+-------------------------------------------------------------------+ | ||
| Table | Op | Msg_type | Msg_text | | Table | Op | Msg_type | Msg_text | ||
行 32: | 行 32: | ||
+--------------------------+----------+----------+-------------------------------------------------------------------+ | +--------------------------+----------+----------+-------------------------------------------------------------------+ | ||
6 rows in set (31.340 sec) | 6 rows in set (31.340 sec) | ||
- | </xtermrtf> | + | </cli> |
- 查看 / | - 查看 / | ||
行 39: | 行 39: | ||
- 查看現有排程 <code sql> | - 查看現有排程 <code sql> | ||
show events; | show events; | ||
- | </ | + | </ |
+-----------+-------------------------------------------------------------+----------------+-----------+-----------+------------+----------------+----------------+---------------------+---------------------+---------+------------+----------------------+----------------------+--------------------+ | +-----------+-------------------------------------------------------------+----------------+-----------+-----------+------------+----------------+----------------+---------------------+---------------------+---------+------------+----------------------+----------------------+--------------------+ | ||
| Db | Name | Definer | | Db | Name | Definer | ||
行 46: | 行 46: | ||
| abc| 刪除1個月以上的curl資料 | | abc| 刪除1個月以上的curl資料 | ||
: | : | ||
- | </xtermrtf> | + | </cli> |
- 顯示詳細排程內容 Exp. 刪除1個月以上的curl資料 <code sql> | - 顯示詳細排程內容 Exp. 刪除1個月以上的curl資料 <code sql> | ||
SELECT * FROM INFORMATION_SCHEMA.EVENTS WHERE EVENT_NAME=' | SELECT * FROM INFORMATION_SCHEMA.EVENTS WHERE EVENT_NAME=' | ||
- | </ | + | </ |
+---------------+--------------+----------------------------------+----------------+-----------+------------+-------------------------------------------------------------------------------------+------------+------------+----------------+----------------+-------------------------------------------------------------------------------------------+---------------------+---------------------+---------+---------------+---------------------+---------------------+---------------------+---------------+------------+----------------------+----------------------+--------------------+ | +---------------+--------------+----------------------------------+----------------+-----------+------------+-------------------------------------------------------------------------------------+------------+------------+----------------+----------------+-------------------------------------------------------------------------------------------+---------------------+---------------------+---------+---------------+---------------------+---------------------+---------------------+---------------+------------+----------------------+----------------------+--------------------+ | ||
| EVENT_CATALOG | EVENT_SCHEMA | EVENT_NAME | | EVENT_CATALOG | EVENT_SCHEMA | EVENT_NAME | ||
行 56: | 行 56: | ||
+---------------+--------------+----------------------------------+----------------+-----------+------------+-------------------------------------------------------------------------------------+------------+------------+----------------+----------------+-------------------------------------------------------------------------------------------+---------------------+---------------------+---------+---------------+---------------------+---------------------+---------------------+---------------+------------+----------------------+----------------------+--------------------+ | +---------------+--------------+----------------------------------+----------------+-----------+------------+-------------------------------------------------------------------------------------+------------+------------+----------------+----------------+-------------------------------------------------------------------------------------------+---------------------+---------------------+---------+---------------+---------------------+---------------------+---------------------+---------------+------------+----------------------+----------------------+--------------------+ | ||
1 row in set (0.001 sec) | 1 row in set (0.001 sec) | ||
- | </xtermrtf> | + | </cli> |
- 建立新的排程 Exp. 針對特定 Table 每周日中午 12:07 進行優化 <code sql> | - 建立新的排程 Exp. 針對特定 Table 每周日中午 12:07 進行優化 <code sql> | ||
CREATE EVENT 優化LOG型資料表 | CREATE EVENT 優化LOG型資料表 | ||
行 64: | 行 64: | ||
</ | </ | ||
SHOW events; | SHOW events; | ||
- | </ | + | </ |
+-----------+-------------------------------------------------------------+----------------+-----------+-----------+------------+----------------+----------------+---------------------+---------------------+---------+------------+----------------------+----------------------+--------------------+ | +-----------+-------------------------------------------------------------+----------------+-----------+-----------+------------+----------------+----------------+---------------------+---------------------+---------+------------+----------------------+----------------------+--------------------+ | ||
| Db | Name | Definer | | Db | Name | Definer | ||
行 71: | 行 71: | ||
| abc | 優化LOG型資料表 | | abc | 優化LOG型資料表 | ||
: | : | ||
- | </xtermrtf> | + | </cli> |
+ | ===== 3. 出現異常訊息 - [ERROR] mysqld: Table ' | ||
+ | * 參考 - https:// | ||
+ | * 對 database : stats 進行修復 <cli> | ||
+ | mysqlcheck -r --databases stats --use-frm | ||
+ | </ | ||
+ | : | ||
+ | : | ||
+ | 220803 15:11:48 [ERROR] mysqld: Table ' | ||
+ | 220803 15:13:10 [Note] Found 6676 of 0 rows when repairing ' | ||
+ | 220803 15:13:10 [Note] Found 813 of 0 rows when repairing ' | ||
+ | : | ||
+ | 220803 15:13:11 [Note] Found 517 of 0 rows when repairing ' | ||
+ | 220803 15:13:11 [Note] Found 1013 of 0 rows when repairing ' | ||
+ | </ | ||
{{tag> | {{tag> | ||