MariaDB 查看running queries,转换存储引擎,导出为HTML,XML
1.模拟一个非常慢的查询
ALTER TABLE title_relationships DROP KEY titles;
SELECT titles.title_id AS ID,
titles.title_title AS Title,
authors.author_legalname AS Name,
(SELECT COUNT(DISTINCT title_relationships.review_id) FROM title_relationships
FROM
WHERE
说明:数据量太大时可以通过LIMIT ROWS EXAMINED语句定义查询长度以节省资源,如,在上面的查询语句后加上
LIMIT ROWS EXAMINED 10000;
还可以在查询长度的基础上限制输入长度
LIMIT 100 ROWS EXAMINED 10000;
(jlive)[isfdb]>SELECT INFO, TIME,
ID, QUERY_ID
*************************** 1. row ***************************
titles.title_title AS Title,
authors.author_legalname AS Name,
(SELECT COUNT(DISTINCT title_relationships.review_id) FROM title_relationships
FROM
WHERE
QUERY_ID: 1483
*************************** 2. row ***************************
QUERY_ID: 103
(jlive)[isfdb]>SHOW EXPLAIN FOR 30;
+------+--------------------+---------------------+--------+-------------------+---------+---------+---------------------------------+--------+-------------+
| id
+------+--------------------+---------------------+--------+-------------------+---------+---------+---------------------------------+--------+-------------+
|
|
|
|
|
+------+--------------------+---------------------+--------+-------------------+---------+---------+---------------------------------+--------+-------------+
5 rows in set, 1 warning (0.00 sec)
4.KILL掉该查询进程
(jlive)[isfdb]>KILL QUERY ID 1483;
Query OK, 0 rows affected (0.00 sec)
5.还原KEY
(jlive)[isfdb]>ALTER TABLE title_relationships ADD KEY titles (title_id);
Query OK, 94824 rows affected (0.26 sec)
Records: 94824
(jlive)[isfdb]>SHOW WARNINGS;
Empty set (0.00 sec)
转换存储引擎
安装Cassandra存储引擎
INSTALL SONAME 'ha_cassandra';
或者
INSTALL PLUGIN Cassandra SONAME 'ha_cassandra';
SHOW PLUGINS;
SHOW STORAGE ENGINES;
卸载Cassandra
UNINSTALL SONAME 'ha_cassandra';
说明:mariadb-10.1的进抽包里默认没带cassandra的插件,这里先跳过
迁移表从MyISAM到Aria
(jlive)[isfdb]>SHOW TABLE STATUS LIKE 'authors'\G
*************************** 1. row ***************************
Max_data_length: 281474976710655
1 row in set (0.00 sec)
(jlive)[isfdb]>ALTER TABLE authors ENGINE=Aria;
Query OK, 136889 rows affected (0.98 sec)
Records:
136889
(jlive)[isfdb]>SHOW TABLE STATUS LIKE 'authors'\G
*************************** 1. row ***************************
Max_data_length: 17592186011648
1 row in set (0.00 sec)
(jlive)[isfdb]>ALTER TABLE awards ENGINE=InnoDB;
Query OK, 38496 rows affected (0.35 sec)
Records:
38496
(jlive)[isfdb]>SHOW TABLE STATUS LIKE 'awards'\G
*************************** 1. row ***************************
Max_data_length: 0
1 row in set (0.00 sec)
表存储引擎转换实际上是两个步骤
1.用新的存储引擎创建一张和原表属性完全相同的新表,仅存储引擎不同,并把数据全部copy到新表
2.删除原表,并装新表的名字改为原表的名字
导出为HTML,XML
1.导出为HTML
echo "SELECT * FROM authors LIMIT 100;" >isfdb-001.sql
mysql --html isfdb < isfdb-001.sql >isfdb-001.html
或用tidy对html加工下,使输出的html更规范标准
mysql --html isfdb < isfdb-001.sql | tidy -q -i -o isfdb-001.html
2.导出为XML
mysql
--xml