mysql执行语句（SQL优化实例）

创建数据表：

1. CREATE TABLE `test1` (    
2.     `id` int(11) NOT NULL AUTO_INCREMENT,    
3.     `firstname` TINYBLOB NOT NULL COMMENT '用户名/支持表情符号255字节',/* 修改字段类型 */  
4.     `question_id` int(11) NOT NULL DEFAULT '0' COMMENT '问题ID',    
5.     `customer_id` int(11) NOT NULL DEFAULT '0' COMMENT '用户ID',    
6.     `approved` tinyint(1) NOT NULL DEFAULT '0' COMMENT '字段备注',    
7.     `customer_name` varchar(32) NOT NULL DEFAULT '' COMMENT '用户姓名',    
8.     `customer_phone` varchar(11) NOT NULL DEFAULT '' COMMENT '用户手机号',    
9.     `value` text NOT NULL COMMENT '值',    
10.     `order_total` decimal(15,4) NOT NULL DEFAULT '0.0000',    
11.     `status` enum('new','invalid','valid','transfered') NOT NULL DEFAULT 'new',    
12.     `date_added` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',    
13.     PRIMARY KEY (`id`),    
14.     KEY `question_id` (`question_id`)    
15. ) ENGINE=MyISAM DEFAULT CHARSET=utf8;    

1. 标准命令：mysql -u数据库用户名 -p数据库密码 数据库名 < 数据库文件名.sql  
2. 示范案例：mysql -ulaozuo -plaozuo.org laozuo < laozuo.sql  

SQL添加一个字段

1. ALTER TABLE `test1` ADD COLUMN `description` varchar(255) NOT NULL DEFAULT '' COMMENT '备注';   

SQL添加一个枚举类型的字段（节省数据库资源）

1. ALTER TABLE `mcc_service_question_options` ADD COLUMN `if_temp` ENUM(  'use_temp',  'no_temp') NOT NULL DEFAULT  'no_temp' COMMENT '回复是否有模板';  

SQL添加索引

1. ALTER TABLE `test1` ADD INDEX (`question_id`);  

添加字段：
alter table 表名
Add column 字段名 字段类型 默认值 AFTER 字段名 (在哪个字段后面添加)

例子：
alter table appstore_souapp_app_androidmarket
Add column getPriceCurrency varchar(50) default null AFTER getPrice

修改字段：
alter table表名
change 字段名 新字段名 字段类型 默认值

例子：
alter table appstore_souapp_app_androidmarket change hasPrice hasPrice varchar(10) null;

删除字段：
alter table 表名 drop column 字段名

例子：
alter table appstore_souapp_app_androidmarket
drop column getPriceCurrency

调整字段顺序：
alter table 表名
change 字段名 新字段名 字段类型 默认值 after 字段名(跳到哪个字段之后)

例子：
alter table appstore_souapp_app_androidmarket
change getPriceCurrency getPriceCurrency varchar(50) default null AFTER getPrice

增：

1. INSERT INTO `test1` (`value`, `status`) VALUES ('值测试', 'new');  

删：

1. DELETE FROM `test1` where `id`='1';  

改：

1. UPDATE `test1` SET `question_id` = '10' WHERE `id` = '2';  

查：

1. SELECT * FROM `test1` WHERE id = 2  

表的记录数：

1. SELECT COUNT(*) AS `total` FROM `test1`  

keyboard的存储格式是：1,3,4的格式存储
我们的$tagId 假设是4，那么我们需要找到 keyboard中含有4的条数，那么就是FIND_IN_SET的出场了

1. // 查询新闻表中，keyboard字段中包含 tagid  
2.   
3. select * from {$dbtbpre}ecms_news where FIND_IN_SET('".$tagId."',keyboard) group by id order by id desc limit 15  

场景一：

（一对多，以多条件为基准，查询一）

要求： 查询 biz_order 订单表的时候，关联查询 biz_order_goods 商品表（一对多）的结束时间：course_end_time， 查询订单列表，如果有其中一条关联商品的结束时间，大于当前时间，那么不展示该条订单

这里的id为1 的商品，有一条商品的结束时间在7月份，设当前是6月份，那么订单1，就应该是不展示的，看一下sql：

1. SELECT  
2.     t.*, t2.maxVal  
3. FROM  
4.     `biz_order` t  
5. JOIN (  
6.     SELECT  
7.         order_id,  
8.         MAX(course_end_time) maxVal  
9.     FROM  
10.         `biz_order_goods`  
11.     GROUP BY  
12.         order_id  
13. ) t2 ON t.id = t2.order_id WHERE t.id in (1,2,3) and t2.maxVal < '2017-06-07 9:54';  

这里的datetime会有点问题，可以在sql中转换一下

1. SELECT  
2.     t.id,t.order_no, t2.maxVal  
3. FROM  
4.     `biz_order` t  
5. JOIN (  
6.     SELECT  
7.         order_id,  
8.         MAX( UNIX_TIMESTAMP(course_end_time) ) maxVal  
9.     FROM  
10.         `biz_order_goods`  
11.     GROUP BY  
12.         order_id  
13. ) t2 ON t.id = t2.order_id WHERE t.id in (1,2,3) and t2.maxVal < '1496801089';  

同一张表中，查询符合两条记录的记录，示意图：

需要查询出，同时符合，filter_id = 5，并且符合filter_id = 6的数据

1. SELECT *  
2. FROM `hzb_article_filter`  
3. WHERE filter_id IN (  
4.     SELECT filter_id  
5.     FROM `hzb_article_filter`  
6.     WHERE filter_id IN(5,6)  
7.     GROUP BY article_id  
8.     HAVING COUNT(*) > 1  
9. )  

结果，article_id=25的已经没有了

 （如果是多个ID：select * from hzb_article_filter WHERE filter_id in (select filter_id from hzb_article_filter WHERE filter_id in(2,5,11,13) GROUP BY article_id HAVING COUNT(*) > 3)）

上面的有问题，另一种方式，join：

1. SELECT  
2.     `t1`.*  
3. FROM  
4.     `hzb_article_filter` `t1`  
5. JOIN `hzb_article_filter` `t2` ON t1.article_id = t2.article_id  
6. AND t2.filter_id = 5  
7. JOIN `hzb_article_filter` `t3` ON t2.article_id = t3.article_id  
8. AND t3.filter_id = 8  
9. JOIN `hzb_article_filter` `t4` ON t3.article_id = t4.article_id  
10. AND t4.filter_id = 13  
11. WHERE  
12.     t1.filter_id = 3  

关于SQL的一些优化及注意点：

由于系统的瓶颈大多出现在数据库层面，所以对数据库的操作需要提出一些开发原则，这些开发原则在前期可能会增加工作量，并且对系统性能的提升并不大，反而某些复杂语句可能会影响性能，但是在数据量急剧膨胀后，会带来性能上的极大提升，并且为sql语句的调优，以及系统的拆分打下了良好的基础，开发人员需要严格遵守。

1.禁止两张表以上的关联查询。

在数据量较大时，连表查询会严重拖慢数据库性能，将大sql拆分成多个子sql，在程序中拼接数据。

可以使用left join, innerjoin, 等，但不要两张以上的数据表联查，可以在查询出来以后做foreach再继续查询，分割为小SQL，在数据量小的情况下可能还会慢一点，但在数据量大的情况下，就是翻倍的提高效率

2.禁止使用存储过程、函数、触发器、外键约束。

后期带来的坏处比好处多，互联网公司不这么玩。

3.禁止使用in子查询和not in。

不走索引

对于连续的数值，能用 between 就不要用 in 了，

如：

1. select id from t where num between 1 and 3  
2. select num from a where num in(select num from b)  

用下面的语句替换：exists与in产生同样的结果

1. select num from a where exists(select 1 from b where num=a.num)  

4.禁止select *，select count(*)

如果需要使用count，请在括号中加上索引字段

5.尽量避免在 where 子句中使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描，改用=代替

6.尽量避免在 where 子句中使用 or 来连接条件，否则将导致引擎放弃使用索引而进行全表扫描，如：

1. select id from t where num=10 or num=20  

可以这样查询：

1. select id from t where num=10 unsion all select id from t where num=20  

7.应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描，如：

在建立字段的时候默认值不要为null，为空

1. select id from t where num is null  

8.避免使用前置百分号的模糊查询，不走索引，可以使用后百分号，避免使用前百分号

1. select id from t where name like ‘%abc%’  
2. select id from t where name like ‘%abc’  

9.不要在 where 子句中的“=”左边进行函数、算术运算或其他表达式运算，否则系统将可能无法正确使用索引。

1. select id from t where num/2=100  

应改为:

1. select id from t where num=100*2  

10、索引

索引的最左前缀匹配原则，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的顺序可以任意调整，mysql优化器会优化成索引可以识别的形式，也就是说尽量将=条件写在最前面，最后再使用范围查询

 签名生成规则

必填参数：time，sign

1.过去所有业务参数键集合，在此基础上增加一个time参数为发起接口的unix时间戳。

2.将接口参数键集合做升序排列 ，按顺序取出键对应的值，通过=相连。

3.最后，将上述键值对相连，在最后加上scretkey形成一个字符串，将形成的字符串通过md5算法，获取签名值，完成签名。

例如：

某接口业务参数为

a=1，b=2，time对应 的时间戳为3，内部的scretkey为4

sign=md5(a=1b=2time=34)

接口请求

http://xx.com?b=2&a=1&time=3&sign=xx