mysql 学习笔记

在使用多列作为主键时， 上述条件必须应用到所有列，所有列值的组合必须是唯一的（但单个列的值可以不唯一） 。
多条SQL语句必须以分号（； ） 分隔。
在选择多个列时， 一定要在列名之间加上逗号
DISTINCT关键字作用于所有的列， 不仅仅是跟在其后的那一列
返回特定数量的行：各种数据库中的这一SQL实现并不相同，sqlserver是top
通常， ORDER BY子句中使用的列将是为显示而选择的列。 但是， 实际上并不一定要这样， 用非检索的列排序数据是完全合法的
如果想在多个列上进行降序排序， 必须对每一列指定DESC关键字。
select name from tbl where price between 5 and 10;
判断字段是否没值：SELECT "UID", "CID"    FROM "BASEINFO"."MP_SYS" where NOT "GID"  is NULL;
10 or :在第一个条件满足时， 不管第二个条件是否满足， 相应的行都将被检索出来 
11 在where语句里可以使用圆括号： where (id=1 or id=3) and price=100
12 IN取一组由逗号分隔、 括在圆括号中的合法值 
13 否定条件用：NOT
14 模糊查询用 ：like ,子句WHERE prod_name LIKE '%'不会匹配产品名称为NULL的行 
15 %表示任何字符出现任意次数。 下划线的用途与%一样， 但它只匹配单个字符。方括号（[]） 匹配指定位置（通配符的位置） 的一个字符。 
16 别名 ：SELECT "GID" ,"WFID" as "haha" FROM "BASEINFO"."MP_SYS" where NOT "WFID"  is NULL;
17 算术运算： + - * /  ,例如  SELECT "V01"+"V02" AS "RET"  FROM "CNYB"
18 聚合函数：avg sum max min count
   使用COUNT(*)对表中行的数目进行计数， 不管表列中包含的是空值（NULL） 还是非空值。
   使用COUNT(column)对特定列中具有值的行进行计数， 忽略NULL值。 
19 算术和聚合混用：SELECT min("P0000"+"P0015") FROM "CNYB"
20  分组  ：  group by
     SELECT SUM("P1100"+"P1130"),"TYPE","ORG_ID" FROM "CNYB"."PRE_DQ_PLANT" WHERE "INDEX"=1 AND "TYPE"=1002 group by "TYPE","ORG_ID";
     除聚集计算语句外， SELECT语句中的每一列都必须在GROUP BY子句中给出 

       SELECT "P1100" ,"TYPE","ORG_ID" FROM "CNYB"."PRE_DQ_PLANT" WHERE AND "INDEX"=1 AND "TYPE"=1002 group by "TYPE","ORG_ID","P1100";  -执行成功

        SELECT "P1100" ,"TYPE","ORG_ID" FROM "CNYB"."PRE_DQ_PLANT" WHERE AND "INDEX"=1 AND "TYPE"=1002 group by "TYPE","ORG_ID";  -执行失败

21 WHERE过滤行， 而HAVING过滤分组。 WHERE在数据分组前进行过滤， HAVING在数据分组后进行过滤
22 排序：  order by
23 select 子句顺序： select from where group by having order by

24 作为子查询的SELECT语句只能查询单个列。 企图检索多个列将返回错误。
25 作为嵌套字段使用子查询：

SELECT cust_name,cust_state,
(SELECT COUNT(*) FROM Orders WHERE cust_id = cust_id) AS orders
FROM Customers ORDER BY cust_name;

26 在引用的列可能出现歧义时， 必须使用完全限定列名（用一个句点分隔表名和列名）
27 等值联结就是内联结

   等值联结：SELECT vend_name, prod_name, prod_price FROM Vendors, Products WHERE Vendors.vend_id = Products.vend_id;

   内联结： SELECT vend_name, prod_name, prod_price FROM Vendors INNER JOIN Products ON Vendors.vend_id = Products.vend_id;

28 左联结：left outer join 全外联结：full outer join

29 组合查询：利用union将多个select 语句的查询结果合并成一个  【UNION中的每个查询必须包含相同的列、 表达式或聚集函数 】

   在用UNION组合查询时， 只能使用一条ORDER BY子句， 它必须位于最后一条SELECT语句之后

   虽然ORDER BY子句似乎只是最后一条SELECT语句的组成部分， 但实际上DBMS将用它来排序所有SELECT语句返回的所有结果。

30 假如想把另一表中的顾客列合并到Customers表中,可以使用insert select  

 SELECT中列出的每一列对应于Customers表名后所跟的每一列 ,这个例子在INSERT和SELECT语句中使用了相同的列名。 但是， 不一定要求列名匹配。 事实上， DBMS一点儿也不关
 心SELECT返回的列名。 它使用的是列的位置， 因此SELECT中的第一列（不管其列名） 将用来填充表列中指定的第一列， 第二列将用来填充表列中指定的第二列， 如此等等
31 从一个表复制到另外一个表：select into  【不管从多少个表中检索数据， 数据都只能插入到一个表中 】

32 删除：DELETE FROM Customers WHERE cust_id = '1000000006';

  更新：UPDATE Customers SET cust_email = 'kim@thetoystore.com'，name="sdf" WHERE cust_id = '1000000005';
33 DELETE删除整行而不是删除列。 要删除指定的列， 请使用UPDATE语句。
34 在UPDATE或DELETE语句使用WHERE子句前， 应该先用SELECT进行测试， 保证它过滤的是正确的记录， 以防编写的WHERE子句不正确。
35 只有不允许NULL值的列可作为主键
36 NULL值是没有值， 不是空字符串
37 大多数约束是在表定义中定义的

38 主键是唯一的， 而且永不改动，也并能为NULL，每个表只允许一个主键
39 外键是表中的一列， 其值必须列在另一表的主键中。 外键是保证引用完整性的极其重要部分
40 在定义外键后， DBMS不允许删除在另一个表中具有关联行的行。 例如， 不能删除关联订单的顾客。 删除该顾客的唯一方法是首先删除相关的订单
41 取值不多的数据（如州） 不如具有更多可能值的数据（如姓或名），能通过索引得到那么多的好处

 =======索引=======

索引匹配的最左原则具体是说，假如索引列分别为A，B，C，顺序也是A，B，C：

• 那么查询的时候，如果查询【A】【A，B】 【A，B，C】，那么可以通过索引查询

• 如果查询的时候，采用【A，C】，那么C这个虽然是索引，但是由于中间缺失了B，因此C这个索引是用不到的，只能用到A索引

• 如果查询的时候，采用【B】 【B，C】 【C】，由于没有用到第一列索引，不是最左前缀，那么后面的索引也是用不到了

• 如果查询的时候，采用范围查询，并且是最左前缀，也就是第一列索引，那么可以用到索引，但是范围后面的列无法用到索引

因为索引虽然加快了查询速度，但索引也是有代价的：索引文件本身要消耗存储空间，同时索引会加重插入、删除和修改记录时的负担，另外，MySQL在运行时也要消耗资源维护索引，因此索引并不是越多越好

请尽量在InnoDB上采用自增字段做主键。

建立索引的常用技巧

1、最左前缀匹配原则，非常重要的原则，mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配，比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。

2、=和in可以乱序，比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序，mysql的查询优化器会帮你优化成索引可以识别的形式

3、尽量选择区分度高的列作为索引,区分度的公式是count(distinct col)/count(*)，表示字段不重复的比例，比例越大我们扫描的记录数越少，唯一键的区分度是1，而一些状态、性别字段可能在大数据面前区分度就是0，那可能有人会问，这个比例有什么经验值吗？使用场景不同，这个值也很难确定，一般需要join的字段我们都要求是0.1以上，即平均1条扫描10条记录

4、索引列不能参与计算，保持列“干净”，比如from_unixtime(create_time) = ’2014-05-29’就不能使用到索引，原因很简单，b+树中存的都是数据表中的字段值，但进行检索时，需要把所有元素都应用函数才能比较，显然成本太大。所以语句应该写成create_time = unix_timestamp(’2014-05-29’);

5、尽量的扩展索引，不要新建索引。比如表中已经有a的索引，现在要加(a,b)的索引，那么只需要修改原来的索引即可，当然要考虑原有数据和线上使用情况

我们常常用到explain这个命令来查看一个这些SQL语句的执行计划，查看该SQL语句有没有使用上了索引，有没有做全表扫描

==========

强制使用索引：
select * from acct_trans_log force index(idx_acct_id)
WHERE acct_id = 1000000000009000757
order by create_time desc limit 0,10

==========

在建立了联合索引idx_acct_id_create_time的情况下

快：select * from acct_trans_log WHERE  acct_id = 3095 order by create_time

慢：select * from acct_trans_log force index(idx_acct_id_create_time) WHERE  acct_id in(3095,1000000000009000757) order by create_time desc limit 0,10

问题出在了排序 上，查询结果集排序如下：

id1+time1

id1+time2

id1+time3

id2+time1

id2+time2

id2+time3

 索引出来的默认排序是这样的，id是有序的，时间是无序的，因为有2个id，优先按id排序，时间就是乱的了。

若只查询id1或id2，默认顺序已经排好了，所以会很快，所以将in改成union all 会变快

=========

使用explain 只需要在原有select 基础上加上explain关键字就可以了，如下：explain select * from servers;

explain各个字段的含义

table：显示这一行数据是关于哪张表的
type：显示使用了何种类型，从最好到最差的连接类型为const,eq_ref,ref,range,index,all
possible_keys：显示可能应用在这张表中的索引。如果为空，没有可能的索引
key：实际使用的索引，如果为null，则没有使用索引。
key_len：使用的索引的长度。在不损失精确性的情况下，长度越短越好
ref：显示索引的哪一列被使用了，如果可能的话，是一个常数
rows：mysql认为必须检查的用来返回请求数据的行数

id : 表示SQL执行的顺序的标识,SQL从大到小的执行

select_type：表示查询中每个select子句的类型

table：显示这一行的数据是关于哪张表的，有时不是真实的表名字

type：表示MySQL在表中找到所需行的方式，又称“访问类型”。常用的类型有：ALL, index, range, ref, eq_ref, const, system, NULL（从左到右，性能从差到好）

possible_keys：指出MySQL能使用哪个索引在表中找到记录，查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询使用

Key：key列显示MySQL实际决定使用的键（索引），如果没有选择索引，键是NULL。

key_len：表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度（key_len显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出的）

ref：表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值

rows：表示MySQL根据表统计信息及索引选用情况，估算的找到所需的记录所需要读取的行数，理论上行数越少，查询性能越好

Extra：该列包含MySQL解决查询的详细信息

• EXPLAIN不会告诉你关于触发器、存储过程的信息或用户自定义函数对查询的影响情况

• EXPLAIN不考虑各种Cache

• EXPLAIN不能显示MySQL在执行查询时所作的优化工作

• 部分统计信息是估算的，并非精确值

• EXPALIN只能解释SELECT操作，其他操作要重写为SELECT后查看执行计划。

mysql> explain select msg_id from circlemessage_idx_0 where  to_id = 113487 and circle_id=10019063  and msg_id>=6273803462253938690  and from_id != 113487 order by msg_id asc limit 30;
+----+-------------+---------------------+-------+-------------------------+---------+---------+------+--------+-------------+
| id | select_type | table               | type  | possible_keys           | key     | key_len | ref  | rows   | Extra       |
+----+-------------+---------------------+-------+-------------------------+---------+---------+------+--------+-------------+
|  1 | SIMPLE      | circlemessage_idx_0 | range | PRIMARY,idx_from_circle | PRIMARY | 16      | NULL | 349780 | Using where |
+----+-------------+---------------------+-------+-------------------------+---------+---------+------+--------+-------------+
1 row in set (0.00 sec)

mysql> explain select msg_id from circlemessage_idx_0 where  to_id = 113487 and circle_id=10019063   and from_id != 113487 order by msg_id asc limit 30;
+----+-------------+---------------------+-------+-----------------+---------+---------+------+------+-------------+
| id | select_type | table               | type  | possible_keys   | key     | key_len | ref  | rows | Extra       |
+----+-------------+---------------------+-------+-----------------+---------+---------+------+------+-------------+
|  1 | SIMPLE      | circlemessage_idx_0 | index | idx_from_circle | PRIMARY | 16      | NULL |   30 | Using where |
+----+-------------+---------------------+-------+-----------------+---------+---------+------+------+-------------+
1 row in set (0.00 sec)

通过上面两个执行计划可以发现当没有msg_id >= xxx这个查询条件的时候，检索的rows要少很多，并且两者查询的时候都用到了索引，而且用到的还只是主键索引。那说明索引应该是不合理的，没有发挥最大作用。

分析这个执行计划可以看到，当包含msg_id >= xxx 查询条件的时候，rows有34w多行，这种情况，说明检索太多，要么就是表里面确实有这么大，要么就是索引不合理没有用到索引，大都情况是没用合理用到索引。列中所用到的索引也是PRIMARY，那就可能是(msg_id,to_id)的其中一个，注意我们建立表的时候msg_id索引的顺序是在to_id前面的，因此MySQL查询一定会优先用msg_id索引，在使用了msg_id索引后，就已经检索出了34w行，并且由于msg_id的查询条件是大于等于，因此，再这个查询条件后，就不能再用到to_id的索引。

然后再看key_len长度为16，结合 key为PRIMARY，那么可以分析得知，只有一个主键索引被用到。

最后看看 type 值，是range，那么就说明这个查询要么是范围查询，要么就是多值匹配。

请注意，from_id != xxx这样的语句，是无法用到索引的。只有from_id = xxx就可以用到所以，因此from id 的索引其实可以不用，建立索引的时候就要考虑清楚