mysql - MySQL 中有序 View 的性能问题

Question

我是 MySQL 性能调优的新手，需要您的帮助来了解稍后将在我们的设计中替换表的 View 。

要替换的表名为users，具有以下属性: users2 View 具有以下属性:

当我对两个对象执行正常的SELECT时，它们同时响应:

SELECT * 
FROM `users`

SELECT * 
FROM `users2`

但是这些查询的有序版本会导致不同的性能:表有点慢(花费不到两秒)，这次 View 需要大约十倍:

SELECT * 
FROM `users`
ORDER BY `lastName`, `firstName`

SELECT * 
FROM `users2`
ORDER BY `lastName`, `firstName`

为了找出原因，我让解释这两条评论:

显然，属性 Countries_ID 上的表“a”(地址)上的ALL会造成麻烦，所以我做了以下内容:

ALTER TABLE addresses ADD INDEX (Countries_ID);

这个索引根本没有改变任何东西。因此，我征求您的意见，哪些方面可以做得更好。

注意1:有没有办法在临时列Countries_ID_2上创建索引？注意 2:users2 View 是使用以下 SQL 查询创建的:

CREATE OR REPLACE VIEW users2 AS
(SELECT p.username
    , p.password
    , p.firstName
    , p.lastName
    , p.eMail AS email
    , a.settlement AS city
    , s.name AS country
    , pl.languages
    , p.description
    , p.ID AS ID
    , p.phone1
    , p.phone2
    , CONCAT_WS(' ', a.street, a.addition) AS address
    , p.status
    , p.publicMail
    , ad.name AS Betreuer
FROM addresses a
    INNER JOIN addresses_have_persons ap ON a.ID = ap.Addresses_ID
    INNER JOIN countries c ON a.Countries_ID = c.ID
    INNER JOIN persons p ON a.ID = p.addressID
        AND ap.Persons_ID = p.ID
    INNER JOIN states s ON a.States_ID = s.ID
    INNER JOIN persons_language pl ON p.ID = pl.ID
LEFT JOIN advisors ad ON p.advisorID = ad.ID
-- LEFT JOIN titles t ON t.ID = ad.titleID
);

注意3:虽然persons表中很多字段都是NULL，但是没有一行这些字段都是NULL。

编辑:

CREATE OR REPLACE VIEW persons_language AS
(SELECT lp.Persons_ID AS ID
    , GROUP_CONCAT(DISTINCT l.name ORDER BY l.name SEPARATOR ', ') AS languages
FROM languages l
    , languages_have_persons lp
WHERE l.ID = lp.Languages_ID
GROUP BY lp.Persons_ID);

如果没有ORDER BY，语言名称不会按字母顺序排列，这是我目前想要的。也许，我们可以决定以任何顺序获取它们，但我们会拭目以待。

目前，我做了以下修改，没有任何性能改进:

ALTER TABLE addresses ADD INDEX (Countries_ID);
ALTER TABLE addresses ADD INDEX (States_ID);
ALTER TABLE addresses_have_persons ADD INDEX (Addresses_ID);
ALTER TABLE languages ADD INDEX (name);
ALTER TABLE persons ADD INDEX (addressID);
ALTER TABLE persons ADD INDEX (address2ID);
ALTER TABLE persons ADD INDEX (address3ID);
ALTER TABLE persons ADD INDEX (advisorID);

编辑2:

我也在另一个网站上讨论了这个问题。那里的讨论让我做了以下更改以更接近第三范式:

CREATE OR REPLACE TABLE accounts
(ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY
, username VARCHAR(50) NOT NULL UNIQUE
, password VARCHAR(255) NOT NULL
, eMail VARCHAR(100) NOT NULL
, Persons_ID INT NOT NULL
);

INSERT INTO accounts (username, password, eMail, Persons_ID)
SELECT username, password, eMail, ID
FROM persons;

表persons确实只包含最必要的内容，现在具有以下结构:

新表persons_information包含所有附加信息:

我使用以下命令重新创建了users2:

CREATE OR REPLACE VIEW users2 AS
(SELECT ac.username
    , ac.password
    , p.firstName
    , p.lastName
    , ac.eMail AS email
    , adr.settlement AS city
    , s.name AS country
    , pl.languages
    , pi.description
    , ac.Persons_ID AS ID
    , pi.phone1
    , pi.phone2
    , CONCAT_WS(' ', adr.street, adr.addition) AS address
    , p.status
    , pi.publicMail
    , adv.name AS Betreuer
FROM accounts ac 
    INNER JOIN persons p ON ac.Persons_ID = p.ID
    INNER JOIN persons_information pi ON p.ID = pi.ID
    INNER JOIN addresses adr ON adr.ID = pi.addressID
    INNER JOIN addresses_have_persons ap ON adr.ID = ap.Addresses_ID
        AND ap.Persons_ID = p.ID
    INNER JOIN countries c ON adr.Countries_ID = c.ID
    INNER JOIN states s ON adr.States_ID = s.ID
    INNER JOIN persons_language pl ON p.ID = pl.ID
LEFT JOIN advisors adv ON pi.advisorID = adv.ID
-- LEFT JOIN titles t ON t.ID = adv.titleID
);

SELECT _ FROM users2 速度很快，但如果我添加 ORDER BY lastName, firstName，大约需要 25 秒才能获得响应。

以下是 *EXPLAIN SELECT * FROM users2* 命令的结果:

这里是另一个命令:

我还(重新)创建了以下索引:

ALTER TABLE addresses ADD INDEX (Countries_ID);
ALTER TABLE addresses ADD INDEX (States_ID);
ALTER TABLE addresses_have_persons ADD INDEX (Persons_ID);
ALTER TABLE languages ADD INDEX (name);
ALTER TABLE persons_information ADD INDEX (addressID);
ALTER TABLE persons_information ADD INDEX (address2ID);
ALTER TABLE persons_information ADD INDEX (address3ID);
ALTER TABLE persons_information ADD INDEX (advisorID);

我认为问题的原因之一是创建的 Person_language View 如下:

CREATE OR REPLACE VIEW persons_language AS
    (SELECT lp.Persons_ID AS ID
        , GROUP_CONCAT(DISTINCT l.name ORDER BY l.name SEPARATOR ', ') AS languages
    FROM languages l
    INNER JOIN languages_have_persons lp ON l.ID = lp.Languages_ID
GROUP BY lp.Persons_ID);

编辑3:对于那些感兴趣的人，我为 persons_language View 添加 EXPLAIN:

编辑4:今天的数据库 session 结束后，我们决定删除与地址信息相关的所有对象并使用

重新创建 View

CREATE OR REPLACE VIEW `users2` AS
(SELECT ac.username
    , ac.password
    , p.firstName
    , p.lastName
    , ac.eMail AS email
    , pl.languages
    , pi.description
    , ac.Persons_ID AS ID
    , pi.phone1
    , pi.phone2
    , p.status
    , pi.publicMail
    , adv.name AS Betreuer
FROM accounts ac 
    INNER JOIN persons p ON ac.Persons_ID = p.ID
    INNER JOIN persons_information pi ON p.ID = pi.ID
    INNER JOIN persons_language pl ON p.ID = pl.ID
    INNER JOIN advisors adv ON pi.advisorID = adv.ID
WHERE ac.password IS NOT NULL
);

我还创建了一个索引

CREATE INDEX LanguagesPersonsIndex ON `languages_have_persons` (`Languages_ID`, `Persons_ID`);

EXPLAIN 命令显示新索引正在使用中，并且 SELECT 和带有新索引的 ORDER BY 子句之后的延迟，较小的 View 约为18 s。这是新结果: 我的问题是:我还可以做些什么来提高性能？

Answer 1

关键故障一定是问题所在。但根据连接表上的数据量，它无论如何都会变慢。尝试:

• 对用于建立关系的所有属性实现 KeyIndex。 (ap.Addresses_ID、a.Countries_ID、p.addressID、ap.Persons_ID、a.States_ID、p.advisorID)。
• 声明所有“ID”列的 PK。
• 请勿在 View 构造中使用 ORDER 或 GROUP。
• 为搜索、排序或分组中最常用的属性声明关键索引。

Tip: The 'INNER' (INNER JOIN) isn't necessary. Is the same of 'JOIN'

您的 VIEW“persons_language”会更好，如下所示:

SELECT lp.Persons_ID AS ID, GROUP_CONCAT(DISTINCT l.name ORDER BY l.name SEPARATOR ', ') AS languages
FROM languages_have_persons lp 
JOIN languages l ON l.ID = lp.Languages_ID
GROUP BY lp.Persons_ID;

这更合适，因为子句“FROM”和“JOIN”在“WHERE”子句之前处理。

您可以增加您的 mysql 内存和缓存配置。查看我的 mysql 服务器的配置(运行带有权重表和 View 的 ERP):

join_buffer_size= 256M
key_buffer = 312M 
key_buffer_size = 768M
max_allowed_packet = 160M 
thread_stack = 192K 
thread_cache_size = 8
query_cache_limit = 64M
innodb_buffer_pool_size = 1512M
table_cache = 1024M 
read_buffer_size = 4M
query_cache_size = 768M
query_cache_limit = 128M