mysql在线上建索引,mysql 5.6在线DDL建索引测试

基本信息：

mysql版本：

(product)root@localhost [(none)]> select @@version;

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

| @@version  |

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

| 5.6.29-log |

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

1 row in set (0.00 sec)

表payment的记录数:

(product)root@localhost [sakila]> select count(*) from payment;

Connection id:    24

Current database: sakila

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

| count(*) |

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

|    16049 |

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

1 row in set (0.00 sec)

事务隔离级别：

(product)root@localhost [(none)]> show variables like '%isolation%';

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

| Variable_name | Value          |

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

| tx_isolation  | READ-COMMITTED |

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

1 row in set (0.00 sec)

场景一：

测试在payment_date字段利用ALGORITHM=COPY,LOCK=EXCLUSIVE方式建立索引所需时间，并监控表是否有加锁?

1.会话1先执行每秒监控查询表payment是否有锁，并记录到txt文件

#!/bin/bash

while : ;do

date>>/data/mysql/mysql3376/lock.txt &&

mysql -uroot -psafe_2016 -S /tmp/mysql3376.sock -e "show open tables from sakila where In_use>0">>/data/mysql/mysql3376/lock.txt

sleep 1

done

2.会话2执行建索引脚本

(product)root@localhost [(none)]> alter table sakila.payment add index payment_date_ix1 (payment_date),ALGORITHM=COPY,LOCK=EXCLUSIVE;

Query OK, 16049 rows affected (1.08 sec)

Records: 16049  Duplicates: 0  Warnings: 0

3.查看lock.txt

cat lock.txt

Sun Apr 17 20:11:17 HKT 2016

Database        Table   In_use  Name_locked

sakila  payment 1       0

4.drop索引，并重启mysql，以清空buffer pool缓存数据

alter table sakila.payment drop index payment_date_ix1;

并重启mysql。

小结：监控到表sakila.payment add index在执行建索引过程中有锁表，且建索引时间为1.08秒。

场景二：

测试在payment_date字段利用ALGORITHM=COPY,LOCK=SHARED方式建立索引所需时间，并监控表是否有加锁?

1.会话1先执行每秒监控查询表payment是否有锁，并记录到txt文件

#!/bin/bash

while : ;do

date>>/data/mysql/mysql3376/lock.txt &&

mysql -uroot -psafe_2016 -S /tmp/mysql3376.sock -e "show open tables from sakila where In_use>0">>/data/mysql/mysql3376/lock.txt

sleep 1

done

2.会话2执行建索引脚本

(product)root@localhost [(none)]> alter table sakila.payment add index payment_date_ix1 (payment_date),ALGORITHM=COPY,LOCK=SHARED;

Query OK, 16049 rows affected (1.57 sec)

Records: 16049  Duplicates: 0  Warnings: 0

3.查看lock.txt

cat lock.txt

Sun Apr 17 20:19:40 HKT 2016

Database        Table   In_use  Name_locked

sakila  payment 1       0

4.drop索引，并重启mysql，以清空buffer pool缓存数据

alter table sakila.payment drop index payment_date_ix1;

小结：监控到表sakila.payment add index在执行建索引过程中有锁表，且建索引时间为1.08秒。

场景三：

测试在payment_date字段利用ALGORITHM=COPY,LOCK=NONE方式建立索引所需时间，并监控表是否有加锁?

1.会话1先执行每秒监控查询表payment是否有锁，并记录到txt文件

#!/bin/bash

while : ;do

date>>/data/mysql/mysql3376/lock.txt &&

mysql -uroot -psafe_2016 -S /tmp/mysql3376.sock -e "show open tables from sakila where In_use>0">>/data/mysql/mysql3376/lock.txt

sleep 1

done

2.会话2执行建索引脚本

(product)root@localhost [(none)]> alter table sakila.payment add index payment_date_ix1 (payment_date),ALGORITHM=COPY,LOCK=NONE;

ERROR 1846 (0A000): LOCK=NONE is not supported. Reason: COPY algorithm requires a lock. Try LOCK=SHARED.

小结：ALGORITHM=COPY,LOCK=NONE两个参数不能同时组合使用，copy方式必需要加锁。

场景四：

测试在payment_date字段利用ALGORITHM=INPLACE,LOCK=EXCLUSIVE方式建立索引所需时间，并监控表是否有加锁?

1.会话1先执行每秒监控查询表payment是否有锁，并记录到txt文件

#!/bin/bash

while : ;do

date>>/data/mysql/mysql3376/lock.txt &&

mysql -uroot -psafe_2016 -S /tmp/mysql3376.sock -e "show open tables from sakila where In_use>0">>/data/mysql/mysql3376/lock.txt

sleep 1

done

2.会话2执行建索引脚本

(product)root@localhost [(none)]> alter table sakila.payment add index payment_date_ix1 (payment_date),ALGORITHM=INPLACE,LOCK=EXCLUSIVE;

Query OK, 0 rows affected (0.32 sec)

Records: 0  Duplicates: 0  Warnings: 0

3.查看lock.txt

cat lock.txt

Sun Apr 17 20:29:26 HKT 2016

Sun Apr 17 20:29:27 HKT 2016

Sun Apr 17 20:29:28 HKT 2016

4.drop索引，并重启mysql，以清空buffer pool缓存数据

alter table sakila.payment drop index payment_date_ix1;

小结：监控到表sakila.payment add index在执行建索引过程中无锁表，且建索引时间为0.32秒。

场景五：

测试在payment_date字段利用ALGORITHM=INPLACE,LOCK=SHARED方式建立索引所需时间，并监控表是否有加锁?

alter table sakila.payment add index payment_date_ix1 (payment_date),ALGORITHM=INPLACE,LOCK=SHARED;

1.会话1先执行每秒监控查询表payment是否有锁，并记录到txt文件

#!/bin/bash

while : ;do

date>>/data/mysql/mysql3376/lock.txt &&

mysql -uroot -psafe_2016 -S /tmp/mysql3376.sock -e "show open tables from sakila where In_use>0">>/data/mysql/mysql3376/lock.txt

sleep 1

done

2.会话2执行建索引脚本

(product)root@localhost [(none)]> alter table sakila.payment add index payment_date_ix1 (payment_date),ALGORITHM=INPLACE,LOCK=SHARED;

Query OK, 0 rows affected (0.23 sec)

Records: 0  Duplicates: 0  Warnings: 0

3.查看lock.txt

cat lock.txt

Sun Apr 17 20:37:07 HKT 2016

Sun Apr 17 20:37:08 HKT 2016

Sun Apr 17 20:37:09 HKT 2016

4.drop索引，并重启mysql，以清空buffer pool缓存数据

alter table sakila.payment drop index payment_date_ix1;

小结：监控到表sakila.payment add index在执行建索引过程中无锁表，且建索引时间为0.23秒。

场景六：

测试在payment_date字段利用ALGORITHM=INPLACE,LOCK=NONE方式建立索引所需时间和是否锁住表上dml操作?

1.会话1先执行每秒监控查询表payment是否有锁，并记录到txt文件

#!/bin/bash

while : ;do

date>>/data/mysql/mysql3376/lock.txt &&

mysql -uroot -psafe_2016 -S /tmp/mysql3376.sock -e "show open tables from sakila where In_use>0">>/data/mysql/mysql3376/lock.txt

sleep 1

done

2.会话2执行建索引脚本

(product)root@localhost [(none)]> alter table sakila.payment add index payment_date_ix1 (payment_date),ALGORITHM=INPLACE,LOCK=NONE;

Query OK, 0 rows affected (0.17 sec)

Records: 0  Duplicates: 0  Warnings: 0

3.查看lock.txt

cat lock.txt

Sun Apr 17 20:39:56 HKT 2016

Sun Apr 17 20:39:57 HKT 2016

Sun Apr 17 20:39:58 HKT 2016

4.drop索引，并重启mysql，以清空buffer pool缓存数据

alter table sakila.payment drop index payment_date_ix1

小结：监控到表sakila.payment add index在执行建索引过程中无锁表，且建索引时间为0.17秒。;

场景七：

测试在payment_date字段在默认方式建立索引所需时间和是否锁表?

1.会话1先执行每秒监控查询表payment是否有锁，并记录到txt文件

#!/bin/bash

while : ;do

date>>/data/mysql/mysql3376/lock.txt &&

mysql -uroot -psafe_2016 -S /tmp/mysql3376.sock -e "show open tables from sakila where In_use>0">>/data/mysql/mysql3376/lock.txt

sleep 1

done

2.会话2执行建索引脚本

(product)root@localhost [(none)]> alter table sakila.payment add index payment_date_ix1 (payment_date);

Query OK, 0 rows affected (0.21 sec)

Records: 0  Duplicates: 0  Warnings: 0

3.查看lock.txt

cat lock.txt

Sun Apr 17 20:44:25 HKT 2016

Sun Apr 17 20:44:26 HKT 2016

Sun Apr 17 20:44:27 HKT 2016

4.drop索引，并重启mysql，以清空buffer pool缓存数据

alter table sakila.payment drop index payment_date_ix1;

小结：监控到表sakila.payment add index在执行建索引过程中无锁表，且建索引时间为0.21秒。

总结：

1.ALGORITHM=INPLACE和默认方式在线建索引都不会锁表，而copy方式必需锁表

2.ALGORITHM=COPY,LOCK=NONE两个参数不能同时组合使用，因为copy方式必需要加锁。

3.ALGORITHM=INPLACE方式比ALGORITHM=COPY方式建索引时间至少减少70%。

4.重启mysql方式是否有比较干净清空buffer pool中缓存数据，需进一步验证确认。