MySQL5.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中缓存数据，需进一步验证确认。