俞月_个人页

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 的字段上建立索引，还是不能避免排序。

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

普通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发一下，一起来看看如何优化。

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

T_Query 是一个视图对吧？
可否将T_Query的建表语句发一下呢？

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

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）都是不需要的。

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

在您给的例子中，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.  

这里涉及到两部分的优化，多表关联和Limit分页。


SQL优化依赖于表中的数据分布，explain得到的执行计划中的rows可以大致反映出问题所在。由于现在只能看到您提供的SQL，没有具体的数据信息，我就说一下，拿到这条SQL后，我的优化思路：


多表关联的优化原则是小结果集驱动大结果集。这个阶段的优化非常重要，一般按照下面的步骤进行分析：
1. 确定驱动表，通过explain查看执行计划，可以看到优化器选择的驱动表。explain结果中，第一行出现的表就优化器选择的驱动表。


SELECT
    c0.c_custnum AS cCustnum,
    c0.c_username AS cUsername,

        c10.cust_rank AS custRank,
        jifen30.rank_name AS rankName,
        c10.c_accdate AS cAccdate,
        c10.c_custname AS cCustname

      FROM
    capf00 AS c0
      LEFT JOIN capf10 AS c10 ON c0.c_custnum = c10.c_custnum
      LEFT JOIN capf30 AS c30 ON (c10.c_custnum = c30.c_custnum AND c30.is_del = 'N')
      LEFT JOIN capf120 ON capf120.cust_no = c10.c_custnum
      LEFT JOIN jifen30 ON c10.cust_rank = jifen30.cust_rank
     LEFT JOIN capf101 ON capf101.cust_no = c0.c_custnum

     WHERE
   capf120.create_time IS NULL
   AND c0.is_del = 'N'
   AND c10.is_del = 'N'


一般，习惯性认为left join驱动顺序是固定的，left join左边的表为驱动表，右边为被驱动表。
其实这也不是绝对的，当left join跟inner join等价的时候，MySQL优化器就会自己选择驱动表。
MySQL对表连接至今只支持nested loop join，通过驱动表的结果集作为循环基础数据，然后一条一条地通过该结果集中的数据作为过滤条件到下一个表中查询数据，然后合并结果集。优化的目标是尽可能减少关联查询中nested loop的循环次数，也就是说尽量缩小驱动表的结果集。


2. 理清各表之间的关联关系，注意被驱动表的关联字段上需要建立索引
假设在第1步中，最后选取的驱动表是c0， 那么根据表关联关系，可以理出驱动顺序为
c0 -> c10 ->c30 ;  c10->capf120;  c10 ->jfen30 ;c0 ->capf101；
那么在被驱动表的关联字段上，是需要建立索引的，这里包括：
c10.c_custnum
c30.c_custnum
capf120.cust_no
jifen30.cust_rank
capf101.cust_no


3. 综合考虑join字段和where字段,那么：
c0建立索引 (is_del)
c10建立组合索引 (c_custnum,is_del)
c30建立组合索引 (c_custnum,is_del)
capf120建立组合索引 (cust_no,create_time)
jifen30建立索引 (cust_rank)
capf101建立索引 (cust_no)

limit 分页的优化：
可以利用到覆盖索引，使得在查询时，直接从二级索引中获取结果，避免返回数据行查找；
4. 所以结合到 select 字段，进一步优化索引：
SELECT
    c0.c_custnum AS cCustnum,
    c0.c_username AS cUsername,

        c10.cust_rank AS custRank,
        jifen30.rank_name AS rankName,
        c10.c_accdate AS cAccdate,
        c10.c_custname AS cCustname

结合上面的几个步骤，最后给出的索引建议是：（这里依赖了前文中的一个假设条件，具体情况还是要依赖实际数据）
c0 建立组合索引(is_del，c_custnum，c_username)
c10 建立组合索引(c_custnum,is_del，cust_rank，c_accdate，c_custname)
c30 建立组合索引 (c_custnum,is_del)
capf120 建立组合索引(cust_no,create_time)
jifen30 建立组合索引(cust_rank，rank_name)
capf101 建立索引(cust_no)

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

业务逻辑，是通过SQL来实现的。驱动表是MySQL优化器去选择的。随着建立的索引不同，数据分布的不同，对于同样的SQL语句，MySQL优化器可能选择不同的驱动表。

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

对的，只需要添加索引就行了。

另外，看到您的SQL语句其实只查询了3个表（c0，c10，jifen30）中的字段，但是查询了6个表中的关联。
您可以确认下，是否其他的几个表关联也都是业务上必要的。
如果不需要，可以减少表关联，可以提高查询效率的。

如果你在那个时候就用到了RDS，就不用担心数据丢失了。
RDS提供了恢复到7天之内的任意时间点功能，只需要在控制台上点点按钮，就能恢复到数据丢失前的时间点。
其原理是这样的：
假如要恢复到4月22日中午12：00, 先会拿到该时间点之前最近的一个物理备份集，恢复出全量的数据；再应用从物理备份的时间点到4月22日中午12：00之间的binlog。
所以为了数据安全，建议大家尽量选择每天都做一次物理备份，这些备份集都是免费存在阿里云OSS上的，不占用户空间的。

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

账号权限开放、支持binlog备份的付费服务，都在产品的排期之内的。RDS团队还在加紧开发。

RDS open API支持查询、下载binlog的。OPEN API文档中有相关接口的说明。

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

先可以看查询所返回的结果集，通常如果返回的结果集很少，是有信心进行优化的。
需要在过滤性强的字段上建立合适的索引。

使用like进行模糊查询，由于like '%XXXX%'不符合前缀匹配的规则，所以用不上索引，需要全表扫描。
比如在下面的表结构下查询：
       Table: tunning_missindex
Create Table: CREATE TABLE `test1` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `custins_id` int(10) unsigned NOT NULL DEFAULT '0',
  `db_name` varchar(128) DEFAULT NULL ,
  `table_name` varchar(128) DEFAULT NULL ,
   ...........
   ...........
  `gmt_created` datetime NOT NULL ,
  `gmt_modified` datetime NOT NULL ,
  `creator` int(11) unsigned DEFAULT '0' ,
  `modifier` int(11) unsigned DEFAULT '0' ,
  `tuning_advice` text,
  PRIMARY KEY (`id`),
  KEY `idx_tablename` (`table_name`)
) ENGINE=InnoDB AUTO_INCREMENT=12749 DEFAULT CHARSET=utf8

mysql> explain select * from test1 where table_name like '%test%';
+----+-------------+-------------------+------+---------------+------+---------+------+-------+-------------+
| id | select_type | table             | type | possible_keys | key  | key_len | ref  | rows  | Extra       |
+----+-------------+-------------------+------+---------------+------+---------+------+-------+-------------+
|  1 | SIMPLE      | test1 | ALL  | NULL          | NULL | NULL    | NULL | 10382 | Using where |
+----+-------------+-------------------+------+---------------+------+---------+------+-------+-------------+
1 row in set (0.00 sec)

InnoDB表是按照聚簇索引组织的。InnoDB通过主键聚簇数据，如果没有定义主键，会选择一个唯一的非空索引代替，如果没有这样的索引，会隐式定义个主键作为聚簇索引。
对于聚簇索引表来说，表数据是和主键一起存储的，主键索引的叶结点存储行数据，二级索引的叶结点存储行的主键值。
所以，理想情况应该是这个流程
1)       遍历table_name索引，从中读取和过滤所有table_name中匹配like条件的id
2)       用id到聚簇索引中读数据。

对于 select count(*) from test1 where table_name like '%test%'的优化，可以利用到覆盖索引。这样扫描二级索引就可以得到结果，避免再到数据行去查询。
mysql> explain select count(*) from test1 where table_name like '%test%';
+----+-------------+-------------------+-------+---------------+---------------+---------+------+-------+--------------------------+
| id | select_type | table             | type  | possible_keys | key           | key_len | ref  | rows  | Extra                    |
+----+-------------+-------------------+-------+---------------+---------------+---------+------+-------+--------------------------+
|  1 | SIMPLE      | test1 | index | NULL          | idx_tablename | 387     | NULL | 10382 | Using where; Using index |
+----+-------------+-------------------+-------+---------------+---------------+---------+------+-------+--------------------------+
1 row in set (0.00 sec)

通过覆盖索引可以获得性能上的一定优化，但是在数据量特别大，请求频繁的业务场景下不要在数据库进行模糊查询；  
非得使用数据库的话 ，建议不要在生产库进行查询，可以在只读节点进行查询，避免查询造成主业务数据库的资源消耗完，导致故障.  
可以使用一些开源的搜索引擎技术，比如sphinx.  

sql优化的时候遵循T=S/V的原则：
S指SQL所需访问的资源总量，V指SQL单位时间所能访问的资源量，T自然就是SQL执行所需时间了;我们为了获得SQL最快的执行时间，可以根据公式定义上去反推：

在S不变的情况下，我们可以提升V来降低T：通过适当的索引调整，我们可以将大量的速度较慢的随机IO转换为速度较快的顺序IO；通过提升服务器的内存，使得将更多的数据放到内存中，会比数据放到磁盘上会得到明显的速度提升；

在V不变的情况下，我们可以减小S来降低T：这是SQL优化中非常核心的一个环节，在减小S环节上，我们可以做的可以有很多，通常可以在查询条件中建立适当的索引，来避免全表扫描；有时候可以改写SQl，添加一些适当的提示符，来改变SQL的执行计划，使SQL以最少的扫描路径完成查询。

买实例时，最大连接数就是根据实例规格来定的；
没有权限修改的。