嗯，犯了一個(gè)很低級(jí)的錯(cuò)誤，最近暴露出來(lái)了。html

背景：mysql

1. 內(nèi)部平臺(tái)，接口間斷性無(wú)返回，查詢?nèi)罩咀⒁獾绞r(shí)，接口耗時(shí)達(dá)到4000+(正常狀態(tài)：100+ms)git

2. 增長(zhǎng)日志打點(diǎn)，在關(guān)鍵步驟插入時(shí)間戳，發(fā)現(xiàn)單步insert 和 select操做耗時(shí)1000ms+github

3. 查看數(shù)據(jù)庫(kù)表數(shù)據(jù)，查詢表數(shù)據(jù)量已積累到400w+，天天新增數(shù)據(jù)4W+，在字符串上創(chuàng)建了索引(以前埋下的很傻行為，無(wú)想法的加了索引，功能實(shí)現(xiàn)未考慮性能)算法

解決：sql

1. 考慮了數(shù)據(jù)的必要性，增長(zhǎng)事件按期刪除過(guò)時(shí)數(shù)據(jù)數(shù)據(jù)庫(kù)

1. 查看事件開(kāi)關(guān)并開(kāi)啟

SHOW VARIABLESLIKE 'event_scheduler';set global event_scheduler = on

2. 具體執(zhí)行須要調(diào)用的存儲(chǔ)過(guò)程，刪除數(shù)據(jù)

DELIMITER//

CREATE PROCEDURE delete_data_n_day_ago(IN n int)BEGIN

delete from test_record where time < date_sub(curdate(), INTERVAL n day) ;END

//DELIMITER ;3. 寫(xiě)事件，調(diào)用存儲(chǔ)過(guò)程CREATEEVENT del_dataON SCHEDULE EVERY 1 DAY

ONCOMPLETION PRESERVE

DO CALL delete_data_n_day_ago (3);

附：事件標(biāo)準(zhǔn)語(yǔ)法segmentfault

CREATE

[DEFINER = { user | CURRENT_USER }]EVENT[IF NOT EXISTS]event_nameONSCHEDULE schedule[ON COMPLETION [NOT]PRESERVE][ENABLE | DISABLE | DISABLE ON SLAVE]

[COMMENT 'string']DO event_body;

schedule:

ATtimestamp [+ INTERVAL interval]...|EVERY interval[STARTS timestamp [+ INTERVAL interval]...][ENDS timestamp [+ INTERVAL interval]...]

interval:

quantity {YEAR | QUARTER | MONTH | DAY | HOUR | MINUTE |WEEK| SECOND | YEAR_MONTH | DAY_HOUR | DAY_MINUTE |DAY_SECOND| HOUR_MINUTE | HOUR_SECOND |MINUTE_SECOND}

詳見(jiàn)：mysql手冊(cè)：https://dev.mysql.com/doc/refman/5.7/en/create-event.html

注釋：

event_name ：建立的event名字(惟一肯定的)。ONSCHEDULE：計(jì)劃任務(wù)。

schedule: 決定event的執(zhí)行時(shí)間和頻率(注意時(shí)間必定要是未來(lái)的時(shí)間，過(guò)去的時(shí)間會(huì)出錯(cuò))，有兩種形式 AT和EVERY。[ON COMPLETION [NOT] PRESERVE]： 可選項(xiàng)，默認(rèn)是ON COMPLETION NOT PRESERVE 即計(jì)劃任務(wù)執(zhí)行完畢后自動(dòng)drop該事件；ONCOMPLETION ?PRESERVE則不會(huì)drop掉。[COMMENT 'comment']：可選項(xiàng)，comment 用來(lái)描述event；至關(guān)注釋，最大長(zhǎng)度64個(gè)字節(jié)。[ENABLE | DISABLE]：設(shè)定event的狀態(tài)，默認(rèn)ENABLE：表示系統(tǒng)嘗試執(zhí)行這個(gè)事件， DISABLE：關(guān)閉該事情，能夠用alter修改

DO event_body: 須要執(zhí)行的sql語(yǔ)句(能夠是復(fù)合語(yǔ)句)。CREATEEVENT在存儲(chǔ)過(guò)程當(dāng)中使用時(shí)合法的。---------------------

做者：jesseyoung

來(lái)源：CSDN

原文：https://blog.csdn.net/JesseYoung/article/details/35257527版權(quán)聲明：本文為博主原創(chuàng)文章，轉(zhuǎn)載請(qǐng)附上博文連接！

2. 循環(huán)調(diào)用 insert單條語(yǔ)句修改成批量性能優(yōu)化

這一點(diǎn)也是網(wǎng)上不少文章提示的，修改成insert into table(keys) values (values1), (values2)...... ，但可能是實(shí)驗(yàn)證實(shí)，沒(méi)有原理，直至找到mysql手冊(cè)數(shù)據(jù)結(jié)構(gòu)

實(shí)際減小了下面提到的connecting + sending query to server? + inserting indexes

3. 修改索引設(shè)置，增長(zhǎng)自增字段做為主鍵

接下來(lái)就是作的很蠢的事情了，不知當(dāng)初出于什么考慮，在URL字段增長(zhǎng)了索引，壓根沒(méi)有考慮到性能問(wèn)題，發(fā)現(xiàn)以后感受太對(duì)不起老師。。。。

不少文章也提到最佳實(shí)踐，不要在字符串創(chuàng)建索引，本身沒(méi)有跑具體數(shù)據(jù)，借用網(wǎng)文數(shù)聽(tīng)說(shuō)明，好比：https://foio.github.io/mysql-stridx/

問(wèn)題一：在字符串上建索引為何會(huì)慢呢？

1. 關(guān)于索引使用數(shù)據(jù)結(jié)構(gòu)，不一樣引擎內(nèi)部實(shí)現(xiàn)存在不一致，手冊(cè)中涉及索引部分是說(shuō)使用B- tree(雖然，網(wǎng)文不少是說(shuō)B+ )?更正下，B-樹(shù)實(shí)際就是B樹(shù)，B+樹(shù)能夠理解為一種特殊的B樹(shù)

2. B+ 和B-的區(qū)別在因而否存數(shù)據(jù)非葉子節(jié)點(diǎn)不保存關(guān)鍵字記錄的指針，數(shù)據(jù)均存在葉子節(jié)點(diǎn)

3. 數(shù)據(jù)庫(kù)索引選擇使用B樹(shù)，而不是紅黑樹(shù)/二叉樹(shù)之類

磁盤(pán)I/O操做耗時(shí)比內(nèi)存久，計(jì)算機(jī)會(huì)預(yù)讀磁盤(pán)，以頁(yè)為單位讀取，選取標(biāo)準(zhǔn)：磁盤(pán)I/O操做盡可能少

每一個(gè)節(jié)點(diǎn)做為一頁(yè)，查詢深度越小，磁盤(pán)讀取操做越少，二叉樹(shù)之類每一個(gè)節(jié)點(diǎn)分為2路，相同數(shù)據(jù)深度更大，故不選取

4. 每一個(gè)節(jié)點(diǎn)可分的路數(shù)越多，深度越小，讀取的IO次數(shù)越少，索引字段選取int 和 長(zhǎng)字符串大小差別，就致使了在字符串上建索引會(huì)慢

insert過(guò)程也伴隨寫(xiě)索引，復(fù)雜度參考B樹(shù)插入數(shù)據(jù)

4. 確認(rèn)其余查詢語(yǔ)句，新增索引

explain select * from tableName where columnName=2;

id| select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

+----+-------------+------------------------------+------+---------------+------+---------+------+--------+-------------+

| 1 | SIMPLE |tableName | ALL | NULL | NULL | NULL | NULL | 437081 | Using where |

ALTER table tableName ADD INDEX indexName(columnName)

后續(xù)：代碼寫(xiě)以前，先確認(rèn)SQL語(yǔ)句，explain確認(rèn)步驟，盡可能避免all類型 和 filesorting

參考：

總結(jié)

以上是生活随笔為你收集整理的mysql 批量加索引_mysql优化：按期删数据 + 批量insert + 字符串加索引为何很傻的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問(wèn)題。

如果覺(jué)得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。