mysql 建视图索引吗_数据库建立视图索引吗

Re回4楼cyb的帖子

distinct 可以认为是group by的特例。

SELECT DISTINCT

LoginId,

SubId

FROM

TB

WHERE 1

ORDER BY Visit ASC

LIMIT 8888, 10

这个SQL等价于: select

LoginId, SubId

from TB where 1 group by

LoginId, SubId

ORDER BY Visit ASC

LIMIT 8888, 10

单纯就这条SQL而言，

ORDER BY Visit ASC 是没必要的，因为select选出的字段中没有

Visit字段，建议添加组合索引(

LoginId，

SubId

)。

假如SQL是 select

LoginId，SubId，Visit

from TB where 1 group by

LoginId, SubId

ORDER BY Visit ASC

LIMIT 8888, 10

这里可以建议组合索引(

LoginId，

SubId) 或者(

LoginId，

SubId，Visit)

将

Visit字段添加到索引中，仍旧避免不了排序，上面SQL的执行过程是：

1.

select

LoginId，SubId，Visit from TB where 1 group by LoginId, SubId

2.

ORDER BY Visit ASC

LIMIT 8888, 10

第一步中的group by (或者distinct)可以利用到索引(

LoginId，

SubId

)避免临时表，排序

下面是测试案例：

mysql> show create table tb\G

*************************** 1. row ***************************

Table: tb

Create Table: CREATE TABLE `tb` (

`id` int(11) DEFAULT NULL,

`LoginId` int(11) DEFAULT NULL,

`SubId` int(11) DEFAULT NULL,

`vist` int(11) DEFAULT NULL,

KEY `idx1` (`id`),

KEY `login_subId` (`LoginId`,`SubId`),

KEY `login_subId_vist` (`LoginId`,`SubId`,`vist`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8

1 row in set (0.00 sec)

mysql> explain select LoginId, SubId, vist from tb where 1 group by LoginId,SubId ;

+----+-------------+-------+-------+------------------------------+-------------+---------+------+------+-------+

| id | select_type | table | type  | possible_keys                | key         | key_len | ref  | rows | Extra |

+----+-------------+-------+-------+------------------------------+-------------+---------+------+------+-------+

|  1 | SIMPLE      | tb    | index | login_subId,login_subId_vist | login_subId | 10      | NULL |    9 | NULL  |

+----+-------------+-------+-------+------------------------------+-------------+---------+------+------+-------+

1 row in set (0.00 sec)

第二步中将步骤1中的结果集按照

Visit 字段排序，这一步的排序不可避免。

上面提到了将visit字段加入到组合索引中，是为了构建覆盖索引。避免返回到数据行去查询。

对于您提到的问题，

排序方式有多种，Visit 、ID、Count等，是不是有一种排序就要建一个“覆盖索引”？

distinct/group by的优化，关键是要利用索引，避免distinct/group by时的创建临时表，排序。

将order by的字段加入到组合索引中，目的是为了直接从二级索引字段获取到结果集，避免再去查数据行。

加了覆盖索引，索引字段变长，查询性能变好，同时也会导致占用空间、插入性能变慢。

所以是否去创建覆盖索引，还是需要依旧您的具体业务而定的。

还需要单列给Visit 、ID、Count、SubId、LoginId建索引吗？

不需要给单列

Visit 、ID、Count、SubId、LoginId建索引，这里在order by 的字段上建立索引，还是不能避免排序。

-------------------------

Re回5楼cyb的帖子

普通limit M，N的翻页写法，往往在越往后翻页的过程中速度越慢，原因

mysql会读取表中的前M+N条数据，M越大，性能就越差：

select  *  from t where sellerid=100 limit 100000，20

优化写法：

select t1.* from  t t1,

(select id from t  sellerid=100 limit 100000，20) t2

where t1.id=t2.id;

优化后的翻页写法，先查询翻页中需要的N条数据的主键id，在根据主键id

回表查询所需要的N条数据，此过程中查询N条数据的主键ID在索引中完成

这种优化的根本出发点，是减少在数据页中的扫描量。

覆盖索引，也是一种优化思路，出发点就是直接从二级索引中直接获取查询结果。

您也可以将您的业务SQL发一下，一起来看看如何优化。

-------------------------

回15楼cyb的帖子

T_Query 是一个视图对吧？

可否将T_Query的建表语句发一下呢？

-------------------------

Re回15楼cyb的帖子

SELECT DISTINCT

LoginId,

SubId

FROM

T_Query

WHERE 1

ORDER BY fenshu DESC

LIMIT 61630, 10

这条SQL从实现的功能而言，其实没必要加ORDER BY fenshu DESC 的。

distinct只能返回它的目标字段，而无法返回其它字段。 所以在SELECT DISTINCT LoginId, SubId FROM T_Query中取出的是 LoginId，SubId不重复的行。也就是说，必须LoginId和SubId都相同才会被排除。

做个测试：

mysql> select * from tb;

+------+---------+-------+------+

| id   | LoginId | SubId | vist |

+------+---------+-------+------+

|    1 |     123 |    21 |   78 |

|    2 |      43 |    71 |   78 |

|    3 |      43 |    21 |   78 |

|    2 |      43 |    71 |   78 |

|    3 |      43 |    21 |   78 |

|    2 |      43 |    71 |   78 |

|    5 |      73 |    21 |   78 |

|    2 |      55 |    67 |   78 |

|    1 |      98 |    21 |   78 |

+------+---------+-------+------+

9 rows in set (0.01 sec)

mysql> select distinct LoginId,SubId from tb ;

+---------+-------+

| LoginId | SubId |

+---------+-------+

|      43 |    21 |

|      43 |    71 |

|      55 |    67 |

|      73 |    21 |

|      98 |    21 |

|     123 |    21 |

+---------+-------+

6 rows in set (0.00 sec)

mysql> select distinct LoginId,SubId from tb where 1 order by vist;

+---------+-------+

| LoginId | SubId |

+---------+-------+

|      43 |    21 |

|      43 |    71 |

|      55 |    67 |

|      73 |    21 |

|      98 |    21 |

|     123 |    21 |

+---------+-------+

6 rows in set (0.00 sec)

这里如果建了(LoginId，SubId)即可避免distinct的创建临时表，避免排序。

mysql> explain select distinct LoginId,SubId from tb;

+----+-------------+-------+-------+------------------------------+-------------+---------+------+------+-------------+

| id | select_type | table | type  | possible_keys                | key         | key_len | ref  | rows | Extra       |

+----+-------------+-------+-------+------------------------------+-------------+---------+------+------+-------------+

|  1 | SIMPLE      | tb    | index | login_subId,login_subId_vist | login_subId | 10      | NULL |    9 | Using index |

+----+-------------+-------+-------+------------------------------+-------------+---------+------+------+-------------+

1 row in set (0.00 sec)

所以，对于您给出的SQL，我的建议是改写SQL为：

SELECT DISTINCT

LoginId,

SubId

FROM

T_Query

LIMIT 61630, 10

并添加索引( LoginId, SubId)，这里一方面可以利用到索引避免临时表、排序；另一方面其实也是覆盖索引。

如果您发现去掉ORDER BY fenshu DESC 不符合您的业务需求，那么就需要考虑一下distinct的用法是否正确？ select出来的结果集是否是您真实需要的。

另外需要提到一点：

您发给我的这张表，索引用法有点问题，建了很多不必要的索引。

假如建了(A)，(A,B)，(A,B,C)三个索引，其实(A)，(A,B)都是不需要的。

-------------------------

Re回20楼华夏一剑的帖子

在您给的例子中，select id, title from tb_news where title like '%mal%'; 是可以走上索引的，并且是覆盖索引。

innodb表的二级索引上存储了主键值，上面的SQL语句只需要查询id(主键字段)和title，所以扫描二级索引字段就可以获取到结果，不要再返回主键索引读取数据了。

mysql> explain select id, title from tb_news where title like '%mal%';

+----+-------------+---------+-------+---------------+---------------+---------+------+------+--------------------------+

| id | select_type | table   | type  | possible_keys | key           | key_len | ref  | rows | Extra                    |

+----+-------------+---------+-------+---------------+---------------+---------+------+------+--------------------------+

|  1 | SIMPLE      | tb_news | index | NULL          | tb_news_title | 203     | NULL |    5 | Using where; Using index |

+----+-------------+---------+-------+---------------+---------------+---------+------+------+--------------------------+

1 row in set (0.00 sec)

而类似这种，select * from tb_news where title like '%mal%';会走全表扫描。

mysql> explain select * from tb_news where title like '%mal%';

+----+-------------+---------+------+---------------+------+---------+------+------+-------------+

| id | select_type | table   | type | possible_keys | key  | key_len | ref  | rows | Extra       |

+----+-------------+---------+------+---------------+------+---------+------+------+-------------+

|  1 | SIMPLE      | tb_news | ALL  | NULL          | NULL | NULL    | NULL |    5 | Using where |

+----+-------------+---------+------+---------------+------+---------+------+------+-------------+

1 row in set (0.00 sec)

通过覆盖索引可以获得性能上的一定优化，但是在数据量特别大，请求频繁的业务场景下不要在数据库进行模糊查询；

非得使用数据库的话 ，建议不要在生产库进行查询，可以在只读节点进行查询，避免查询造成主业务数据库的资源消耗完，导致故障；

可以使用MySQL自带的全文检索，或者一些开源的搜索引擎技术，比如sphinx.