mysql 中一些不常用的用法

SELECT 
[ALL | DISTINCT | DISTINCTROW ]  

        [HIGH_PRIORITY]  

[STRAIGHT_JOIN]  

[SQL_SMALL_RESULT] 

        [SQL_BIG_RESULT]

        [SQL_BUFFER_RESULT]  

[SQL_CACHE | SQL_NO_CACHE]

        [SQL_CALC_FOUND_ROWS]  

        select_expr [, select_expr ...]  
FROM table_references  
[WHERE where_condition]  
[GROUP BY {col_name | expr | position}  
[ASC | DESC], ... [WITH ROLLUP]]  
[HAVING where_condition]  
[ORDER BY {col_name | expr |position}  
[ASC | DESC], ...]  
[LIMIT {[OFFSET,] row_count | row_count{ROWS|ROW } {FETCH NEXT }{ROWS|ROW } {only}}]  
[PROCEDURE procedure_name(argument_list)]  
[ INTO OUTFILE 'file_name' export_options  |     INTO DUMPFILE 'file_name'     |    INTO var_name [, var_name] ]  

[FOR UPDATE | LOCK IN SHARE MODE]

      ] 

         
       今天在熟悉我们的项目的时候，sql中看到FORCE INDEX，额，都不知道什么东东，有什么特别用处吗？ 查了下资料，原来是这样啊，发现一般不搞复杂sql，而且复杂sql DBA也不允许，但优化的sql 还是有必要了解下。（一般来说也不太会使用，总被DBA pk了）
        对于经常使用oracle的朋友可能知道，oracle的hint功能种类很多，对于优化sql语句提供了很多方法。同样，在mysql里，也有类似的hint功能。下面介绍一些常用的。

      1。强制索引 FORCE INDEX

SELECT * FROM TABLE1 FORCE INDEX (FIELD1) …

以上的SQL语句只使用建立在FIELD1上的索引，而不使用其它字段上的索引。

       2。忽略索引 IGNORE INDEX

SELECT * FROM TABLE1 IGNORE INDEX (FIELD1, FIELD2) …

在上面的SQL语句中，TABLE1表中FIELD1和FIELD2上的索引不被使用。

       3。关闭查询缓冲 SQL_NO_CACHE

SELECT SQL_NO_CACHE field1, field2 FROM TABLE1;

有一些SQL语句需要实时地查询数据，或者并不经常使用（可能一天就执行一两次）,这样就需要把缓冲关了,不管这条SQL语句是否被执行过，服务器都不会在缓冲区中查找，每次都会执行它。

      4。强制查询缓冲 SQL_CACHE

SELECT SQL_CALHE * FROM TABLE1;

如果在my.ini中的query_cache_type设成2，这样只有在使用了SQL_CACHE后，才使用查询缓冲。

     5。优先操作 HIGH_PRIORITY

HIGH_PRIORITY可以使用在select和insert操作中，让MYSQL知道，这个操作优先进行。

SELECT HIGH_PRIORITY * FROM TABLE1;

     6。滞后操作 LOW_PRIORITY

LOW_PRIORITY可以使用在insert和update操作中，让mysql知道，这个操作滞后。

update LOW_PRIORITY table1 set field1= where field1= …

    7。延时插入 INSERT DELAYED

INSERT DELAYED INTO table1 set field1= …

INSERT DELAYED INTO，是客户端提交数据给MySQL，MySQL返回OK状态给客户端。而这是并不是已经将数据插入表，而是存储在内存里面等待排队。当mysql有空余时，再插入。另一个重要的好处是，来自许多客户端的插入被集中在一起，并被编写入一个块。这比执行许多独立的插入要快很多。坏处是，不能返回自动递增的ID，以及系统崩溃时，MySQL还没有来得及插入数据的话，这些数据将会丢失。

     8 。强制连接顺序 STRAIGHT_JOIN

SELECT TABLE1.FIELD1, TABLE2.FIELD2 FROM TABLE1 STRAIGHT_JOIN TABLE2 WHERE …

由上面的SQL语句可知，通过STRAIGHT_JOIN强迫MySQL按TABLE1、TABLE2的顺序连接表。如果你认为按自己的顺序比MySQL推荐的顺序进行连接的效率高的话，就可以通过STRAIGHT_JOIN来确定连接顺序。

    9。强制使用临时表 SQL_BUFFER_RESULT

SELECT SQL_BUFFER_RESULT * FROM TABLE1 WHERE …

当我们查询的结果集中的数据比较多时，可以通过SQL_BUFFER_RESULT.选项强制将结果集放到临时表中，这样就可以很快地释放MySQL的表锁（这样其它的SQL语句就可以对这些记录进行查询了），并且可以长时间地为客户端提供大记录集。

   10。分组使用临时表 SQL_BIG_RESULT和SQL_SMALL_RESULT

SELECT SQL_BUFFER_RESULT FIELD1, COUNT(*) FROM TABLE1 GROUP BY FIELD1;

一般用于分组或DISTINCT关键字，这个选项通知MySQL，如果有必要，就将查询结果放到临时表中，甚至在临时表中进行排序。SQL_SMALL_RESULT比起SQL_BIG_RESULT差不多，很少使用。

11。DISTINCT忽略所选字段中包含重复数据的记录。

只有 SELECT 语句中列出的每个字段的值是唯一的，记录才会包括在查询结果中。例如，Employees（雇员）表中列出的多个雇员可能具有相同的姓氏。如果两个记录的 LastName（姓氏）字段中都包含 Smith，则以下 SQL 语句只返回一个包含 Smith 的记录：SELECT DISTINCT LastName FROM Employees;

如果无 DISTINCT，则此查询返回两个包含 Smith 的记录。

如果 SELECT 子句中包含多个字段，则对于结果中包含的特定记录，其所有字段的值组合必须是唯一的。

如果查询使用 DISTINCT，则其输出不可更新，且无法体现其他用户所做的后续更改

   12。DISTINCTROW根据整个重复记录而不是某些重复字段来忽略数据。

例如，您可以创建一个查询，根据 CustomerID（客户 ID）字段将 Customers（客户）表和 Orders（订单）表联接起来。Customers（客户）表中无重复的 CustomerID（客户 ID）字段，但 Orders（订单）表中包含重复字段，因为每个客户可以有多个订单。以下 SQL 语句显示了如何使用 DISTINCTROW 来生成至少具有一个订单的公司列表，但不包含有关订单的任何细节：

SELECT DISTINCTROW CompanyName FROM Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID ORDER BY CompanyName;

如果无 DISTINCTROW，则此查询将为具有多个订单的每个公司生成多行。

仅当从查询中所用的某些表，而不是所有表中选择字段时，DISTINCTROW 才有效。

如果查询只包含一个表，或者如果输出所有表的字段，则 DISTINCTROW 将被忽略。

   13。GROUP BY|ORDER BY {col_name | expr | position}  

   expr表示表达式,可以参考我的 http://blog.csdn.net/changliangwl/article/details/42777551

   position表示 select col_name1,col_name2,…… from 所查询字段的所在位置（非表中字段的排序位置！）

例如：

      SELECT col_name1,col_name2,……  from tabtest a GROUP BY 2 表示 GROUP BY  col_name2

    14。分页OFFSET 

TOP选项是一个非常实用的筛选类型，但它有两个缺陷——不是标准SQL，且不支持跳过功能。标准SQL定义的TOP类似筛选称为OFFSET-FETCH，支持跳过功能，这对针对特定页面的查询非常有用。SQL Server2012引入了对OFFSET-FETCH筛选的支持。

SQL Server 2012中的OFFSET-FETCH筛选被视为ORDER BY子句的一部分，通常用于实现按顺序显示效果。OFFSET子句指定要跳过的行数，FETCH子句指定在跳过的行数后要筛选的行数。请思考一下下面的查询示例。

    SELECT orderid, orderdate, custid, empid

         FROM Sales.Orders ORDER BY orderdate, orderid

   OFFSET 50 ROWS FETCH NEXT 25 ROWS ONLY;

此查询按orderdate、orderid顺序（订单日期从最远到最近，并添加了决胜属性（tiebreaker）orderid）排序Orders表中的行。基于此顺序，OFFSET子句跳过前50行，由FETCH子句仅筛选下面的25行。

请注意，使用OFFSET-FETCH的查询必须具有ORDER BY子句。此外，FETCH子句不支持没有OFFSET子句。如果你不想跳过任何行，但是希望使用FETCH筛选，你应当使用OFFSET 0 ROWS来表示。不过，没有FETCH的OFFSET是允许的，这种情况是跳过指定的行数，并返回查询结果中所有剩余行。

OFFSET-FETCH语法有一些有趣的语言方面需要注意。单数格式ROW和复数格式ROWS是可以互换的，此举是让你能够以直观的类似英语方式来描述筛选。例如，假设你仅希望获取一行，如果你指定了FETCH 1 ROWS，虽然这在语法上是有效的，不过看上去会很怪。因此，你可以使用FETCH 1 ROW格式。此互换同样适用于OFFSET子句。另外，如果你不希望跳过任何行（OFFSET 0 ROWS），你可能觉得“first”比“next”更合适，因此，FIRST 和NEXT格式是可以互换的。

如你所见，从支持跳过功能看，OFFSET-FETCH子句比TOP子句更灵活。不过，OFFSET-FETCH 不支持PERCENT和WITH TIES选项，而TOP支持。由于OFFSET-FETCH是标准的，而TOP不是，我建议使用OFFSET-FETCH作为你的默认选择，除非你需要TOP支持且OFFSET-FETCH不支持的功能。

MySQL里对LIMIT OFFSET的处理方式是，取出OFFSET+LIMIT的所有数据，然后去掉OFFSET，返回底部的LIMIT。
所以，在OFFSET数值较大时，MySQL的查询性能会非常低（可以分库分表）。

SELECT * FROM table_name ORDER BY id ASC LIMIT 10 OFFSET 10

MySQL会取出20条数据，只返回后10条。

SELECT * FROM table_name WHERE id > 10 ORDER BY id ASC LIMIT 10

利用id索引，MySQL只取出10个结果，返回。这种做法却是要求连续翻页，不能跳页，受约束。（这种方式比较大的缺点是，如果在浏览中有插入/删除操作，翻页不会更新，而总页数可能仍然是根据新的count(*) 来计算，最终可能会产生某些记录访问不到。为了修补这个问题，可以继续引入当前页码以及在上次翻页以后是否有插入/删除等影响总记录数的操作并进行缓存）

    15。WITH ROLLUP

使用 GROUP BY 的 WITH ROLLUP 字句可以检索出更多的分组聚合信息，它不仅仅能像一般的 GROUP BY 语句那样检索出各组的聚合信息，还能检索出本组类的整体聚合信息。可参考http://blog.csdn.net/id19870510/article/details/6254358