mysql - SQL过程使用/临时表优化

Question

我创建了一个mysql数据库，它存储了几个类似树结构的信息。我为此使用了嵌套集方法。由于我创建了多个过程来创建子节点、检索特定项目的子节点等，为了处理多个表，这些过程使用表名称进行参数化。您知道，一个程序可以为多个表提供服务。

无论如何，有一个表来存储磁盘文件夹结构:

mysql> show columns from folder;
    +------------+---------------------+------+-----+---------+----------------+
    | Field      | Type                | Null | Key | Default | Extra          |
    +------------+---------------------+------+-----+---------+----------------+
    | id         | bigint(20) unsigned | NO   | PRI | NULL    | auto_increment |
    | priviledge | int(10) unsigned    | NO   |     | NULL    |                |
    | name       | char(255)           | NO   |     | NULL    |                |
    | L          | bigint(20)          | NO   | UNI | NULL    |                |
    | R          | bigint(20)          | NO   | UNI | NULL    |                |
    +------------+---------------------+------+-----+---------+----------------+

伴随索引定义如下:

mysql> show indexes in folder;
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table  | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| folder |          0 | id       |            1 | id          | A         |        3247 |     NULL | NULL   |      | BTREE      |         |               |
| folder |          0 | R        |            1 | R           | A         |        3247 |     NULL | NULL   |      | BTREE      |         |               |
| folder |          0 | L        |            1 | L           | A         |        3247 |     NULL | NULL   |      | BTREE      |         |               |
| folder |          1 | LR       |            1 | L           | A         |        3247 |     NULL | NULL   |      | BTREE      |         |               |
| folder |          1 | LR       |            2 | R           | A         |        3247 |     NULL | NULL   |      | BTREE      |         |               |
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

有一点问题的查询检索所选父节点的子节点:

SELECT node.id, node.name, (COUNT(parent.id)-(sub_tree.depth + 1)) AS depth, node.r!=node.l+1 AS haschild
FROM <table_name> AS node, <table_name> AS parent, <table_name> AS sub_parent,
(
    SELECT node.id, node.name, (COUNT(parent.name) - 1) AS depth
    FROM <table_name> AS node, <table_name> AS parent
    WHERE node.L BETWEEN parent.L AND parent.R
    AND node.id = <parent_id>
    GROUP BY node.id
) AS sub_tree
WHERE node.L BETWEEN parent.L AND parent.R
AND node.L BETWEEN sub_parent.L AND sub_parent.R
AND sub_parent.id = sub_tree.id
GROUP BY node.id
HAVING depth = 1;


+----+-------------+------------+--------+---------------+------+---------+-------+------+----------------------------------------------------+
| id | select_type | table      | type   | possible_keys | key  | key_len | ref   | rows | Extra                                              |
+----+-------------+------------+--------+---------------+------+---------+-------+------+----------------------------------------------------+
|  1 | PRIMARY     | <derived2> | system | NULL          | NULL | NULL    | NULL  |    1 | Using temporary; Using filesort                    |
|  1 | PRIMARY     | sub_parent | const  | id,R,L,LR     | id   | 8       | const |    1 | NULL                                               |
|  1 | PRIMARY     | parent     | index  | R,L,LR        | LR   | 16      | NULL  | 3247 | Using index                                        |
|  1 | PRIMARY     | node       | ALL    | id,R,L,LR     | NULL | NULL    | NULL  | 3247 | Using where; Using join buffer (Block Nested Loop) |
|  2 | DERIVED     | node       | const  | id,L,LR       | id   | 8       | const |    1 | NULL                                               |
|  2 | DERIVED     | parent     | range  | R,L,LR        | L    | 8       | NULL  |    2 | Using where                                        |
+----+-------------+------------+--------+---------------+------+---------+-------+------+----------------------------------------------------+

现在上述过程主体如下所示:

BEGIN
  SET @table_name = table_name;
  SET @row_id = row_id;

  SET @sql_text = concat(
'CREATE TEMPORARY TABLE IF NOT EXISTS _childs(INDEX(name)) ENGINE MEMORY AS SELECT node.id, node.name, (COUNT(parent.name)-(sub_tree.depth + 1)) AS depth, node.r!=node.l+1 AS haschild
FROM ',@table_name,' AS node, ',@table_name,' AS parent, ',@table_name,' AS sub_parent,
(
    SELECT node.id, node.name, (COUNT(parent.name) - 1) AS depth
    FROM ',@table_name,' AS node,',@table_name,' AS parent
    WHERE node.L BETWEEN parent.L AND parent.R
    AND node.id = ',@row_id,'
    GROUP BY node.id
) AS sub_tree
WHERE node.L BETWEEN parent.L AND parent.R
AND node.L BETWEEN sub_parent.L AND sub_parent.R
AND sub_parent.id = sub_tree.id
GROUP BY node.id
HAVING depth = 1;
');

  PREPARE stmt FROM @sql_text;
  DROP TEMPORARY TABLE IF EXISTS _childs;
  EXECUTE stmt;
  DEALLOCATE PREPARE stmt;
END

我正在创建临时表作为进一步选择语句的解决方法，因为在 mysql select 中无法从返回行的过程中获取数据。有趣的是，有一次我能够在几分钟内注入(inject)有关 10000 个文件的信息，但无法重新创建它以供生产使用。现在添加文件非常慢，使用上面提供的结构遍历文件路径每个文件需要几秒钟。不使用过程运行查询要快得多。

我已经优化了堆/内存磁盘的使用。而且临时表的创建似乎对性能没有影响。如何进一步优化mysql安装？

Answer 1

你好像用的是InnoDB？将 innodb_buffer_pool_size 设置为可用 RAM 的 70% 对于 InnoDB 是有利的。