Mysql大表加索引

最近大后台查看一些数据统计的时候，很慢，甚至会有超时情况，前端设置的超时时间是20秒。
后来通过查看日志和慢查询，发现一条sql语句执行时间超过18秒，基本都19秒左右。

大表加索引

select (*) from tb_name where create_time > xxx;
最终得知是因为这个表数据行数已经超过 一千万了，然后create_time字段又没有索引 。

那解决办法肯定是加索引喽。

但是这个表是一直在线上运行，很重要和业务部分。如果给千万级的大表在线加索引 ，肯定会卡死。

然后就搜罗了一大筐解决方案，比如 在线无锁加索引使用

ALTER TABLE tbl_name ADD PRIMARY (column), ALGORITHM=INPLACE, LOCK=NONE;
后来才发现，这个特性是Mysql 5.6以后才支持，然而我们的mysql用的是5.5版本

最后在 《高性能Mysql》一书中看到，可在通过 “影子拷贝”来解决-弊端没有考虑新增加和update 数据，

就是 先创建一张和源表无关的新表，然后通过重命名和删表操作交换两张表；

#操作步骤：
#1、创建一张和原表结构一样的空表，只是表名不一样
    create table tb_name_tmp like tb_name;

#2、把新建的空表非主键索引都删掉，因为这样在往新表导数据的时候效率会很快（因为除了必要的主键以外，不用再去建立其它索引数据了）
    alter tb_name_tmp drop index index_name;

#3、从旧表往主表里导数据，如果数据太大，建议分批导入，只需确保无重复数据就行，因为导入数据太大，会很占用资源(内存，磁盘io, cpu等)，可能会影响旧表在线上的业务。我是每批次100万条数据导入，基本上每次都是在 20s左右
    insert into tb_name_tmp select * from tb_name where id between start_id and end_id;

#4、数据导完后，再对新表进行添加索引
     create index index_name on tb_name_tmp(column_name);

#5、当大部分数据导入后，索引也建立好了，但是旧表数据量还是会因业务的增长而增长，这时候为了确保新旧表的数据一至性和平滑切换，建议写一个脚本，判断当旧表的数据行数与新表一致时，就切换。我是以 max(id)来判断的。
     table tb_name to tb_name_tmp1;
     table tb_name_tmp to tb_name;

当给新表加完索引后，最上面那条查询直接就是0.0002s

加索引卡死

场景：在给一张有几万条记录的表添加索引时，进度非常慢，导致其它查询无法进行

处理方式：
使用Navicat的命令行模式，执行以下命令：

show processlist;
这时会看到有哪些线程正在执行，也可以查看锁表的线程。你会发现alter table * add key ****那个线程状态是Waiting for table metadata lock，后面有个这个表的所有操作都是这个状态，很明显是这条加索引的语句把表给锁了。
查看线程ID，执行

kill 线程ID
这样被锁住的表就能立即被使用了。

由此得出一个结论，当一张表数据量很大时，不要轻易添加索引，会导致表被锁死！如果非要添加，那么应该先把数据表进行备份，然后进行空表添加索引。

Mysql online DDL 线上无锁添加索引

只能通过ALTER TABLE不能create index

ALTER TABLE tbl_name ADD PRIMARY KEY (column), ALGORITHM=INPLACE, LOCK=NONE;

参数说明：

ALGORITHM=INPLACE
更优秀的解决方案，在当前表加索引，步骤：
1.创建索引(二级索引)数据字典
2.加共享表锁，禁止DML，允许查询
3.读取聚簇索引，构造新的索引项，排序并插
入新索引
4.等待打开当前表的所有只读事务提交
5.创建索引结束
 
ALGORITHM=COPY
通过临时表创建索引，需要多一倍存储，还有更多的IO，步骤：
1.新建带索引（主键索引）的临时表
2.锁原表，禁止DML，允许查询
3.将原表数据拷贝到临时表
4.禁止读写,进行rename，升级字典锁
5.完成创建索引操作
 
LOCK=DEFAULT：默认方式，MySQL自行判断使用哪种LOCK模式，尽量不锁表
LOCK=NONE：无锁：允许Online DDL期间进行并发读写操作。如果Online DDL操
作不支持对表的继续写入，则DDL操作失败，对表修改无效
LOCK=SHARED：共享锁：Online DDL操作期间堵塞写入，不影响读取
LOCK=EXCLUSIVE：排它锁：Online DDL操作期间不允许对锁表进行任何操作

给1E数据库在线加索引

数据库在线加索引
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MySQL connection id is 7995759
Server version: 5.7.25-log Source distribution
 
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
 
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
 
MySQL [(none)]> use sdorica_exp
 
MySQL [sdorica_exp]> show index from gashapon_outcome_records;
Empty set (0.00 sec)
 
MySQL [sdorica_exp]> select count(1) from gashapon_outcome_records;
+-----------+
| count(1)  |
+-----------+
| 111579926 |
+-----------+
1 row in set (1 min 10.13 sec)
 
MySQL [sdorica_exp]> ALTER TABLE gashapon_outcome_records ADD INDEX idx_roll_gashapon_record_id (roll_gashapon_record_id) , ALGORITHM=INPLACE, LOCK=NONE;
 
 
Query OK, 0 rows affected (15 min 34.16 sec)
Records: 0  Duplicates: 0  Warnings: 0
 
 
MySQL [sdorica_exp]> show index from gashapon_outcome_records;
+--------------------------+------------+-----------------------------+--------------+-------------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table                    | Non_unique | Key_name                    | Seq_in_index | Column_name             | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+--------------------------+------------+-----------------------------+--------------+-------------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| gashapon_outcome_records |          1 | idx_roll_gashapon_record_id |            1 | roll_gashapon_record_id | A         |    51825872 |     NULL | NULL   | YES  | BTREE      |         |               |
+--------------------------+------------+-----------------------------+--------------+-------------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.01 sec)
 
MySQL [sdorica_exp]>