mysql 批量加索引_mysql优化：按期删数据 + 批量insert + 字符串加索引为何很傻

嗯，犯了一个很低级的错误，最近暴露出来了。html

背景：mysql

1. 内部平台，接口间断性无返回，查询日志注意到失败时，接口耗时达到4000+(正常状态：100+ms)git

2. 增长日志打点，在关键步骤插入时间戳，发现单步insert 和 select操做耗时1000ms+github

3. 查看数据库表数据，查询表数据量已积累到400w+，天天新增数据4W+，在字符串上创建了索引(以前埋下的很傻行为，无想法的加了索引，功能实现未考虑性能)算法

解决：sql

1. 考虑了数据的必要性，增长事件按期删除过时数据数据库

1. 查看事件开关并开启

SHOW VARIABLESLIKE 'event_scheduler';set global event_scheduler = on

2. 具体执行须要调用的存储过程，删除数据

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. 写事件，调用存储过程CREATEEVENT del_dataON SCHEDULE EVERY 1 DAY

ONCOMPLETION PRESERVE

DO CALL delete_data_n_day_ago (3);

附：事件标准语法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}

详见：mysql手册：https://dev.mysql.com/doc/refman/5.7/en/create-event.html

注释：

event_name ：建立的event名字(惟一肯定的)。ONSCHEDULE：计划任务。

schedule: 决定event的执行时间和频率(注意时间必定要是未来的时间，过去的时间会出错)，有两种形式 AT和EVERY。[ON COMPLETION [NOT] PRESERVE]： 可选项，默认是ON COMPLETION NOT PRESERVE 即计划任务执行完毕后自动drop该事件；ONCOMPLETION  PRESERVE则不会drop掉。[COMMENT 'comment']：可选项，comment 用来描述event；至关注释，最大长度64个字节。[ENABLE | DISABLE]：设定event的状态，默认ENABLE：表示系统尝试执行这个事件， DISABLE：关闭该事情，能够用alter修改

DO event_body: 须要执行的sql语句(能够是复合语句)。CREATEEVENT在存储过程当中使用时合法的。---------------------

做者：jesseyoung

来源：CSDN

原文：https://blog.csdn.net/JesseYoung/article/details/35257527版权声明：本文为博主原创文章，转载请附上博文连接！

2. 循环调用 insert单条语句修改成批量性能优化

这一点也是网上不少文章提示的，修改成insert into table(keys) values (values1), (values2)...... ，但可能是实验证实，没有原理，直至找到mysql手册数据结构

实际减小了下面提到的connecting + sending query to server  + inserting indexes

3. 修改索引设置，增长自增字段做为主键

接下来就是作的很蠢的事情了，不知当初出于什么考虑，在URL字段增长了索引，压根没有考虑到性能问题，发现以后感受太对不起老师。。。。

不少文章也提到最佳实践，不要在字符串创建索引，本身没有跑具体数据，借用网文数听说明，好比：https://foio.github.io/mysql-stridx/

问题一：在字符串上建索引为何会慢呢？

1. 关于索引使用数据结构，不一样引擎内部实现存在不一致，手册中涉及索引部分是说使用B- tree(虽然，网文不少是说B+ ) 更正下，B-树实际就是B树，B+树能够理解为一种特殊的B树

2. B+ 和B-的区别在因而否存数据非叶子节点不保存关键字记录的指针，数据均存在叶子节点

3. 数据库索引选择使用B树，而不是红黑树/二叉树之类

磁盘I/O操做耗时比内存久，计算机会预读磁盘，以页为单位读取，选取标准：磁盘I/O操做尽可能少

每一个节点做为一页，查询深度越小，磁盘读取操做越少，二叉树之类每一个节点分为2路，相同数据深度更大，故不选取

4. 每一个节点可分的路数越多，深度越小，读取的IO次数越少，索引字段选取int 和 长字符串大小差别，就致使了在字符串上建索引会慢

insert过程也伴随写索引，复杂度参考B树插入数据

4. 确认其余查询语句，新增索引

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)

后续：代码写以前，先确认SQL语句，explain确认步骤，尽可能避免all类型 和 filesorting

参考：