基本信息:
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中缓存数据,需进一步验证确认。