mysql执行语句(SQL优化实例)

2018-07-05 10:47:16 围观 :

创建数据表:

SQL Code复制内容到剪贴板
  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;    

 

 

PHP Code复制内容到剪贴板
  1. 标准命令:mysql -u数据库用户名 -p数据库密码 数据库名 < 数据库文件名.sql  
  2. 示范案例:mysql -ulaozuo -plaozuo.org laozuo < laozuo.sql  

 

 

 

SQL添加一个字段

SQL Code复制内容到剪贴板
  1. ALTER TABLE `test1` ADD COLUMN `description` varchar(255) NOT NULL DEFAULT '' COMMENT '备注';   

 

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

SQL Code复制内容到剪贴板
  1. ALTER TABLE `mcc_service_question_options` ADD COLUMN `if_temp` ENUM(  'use_temp',  'no_temp') NOT NULL DEFAULT  'no_temp' COMMENT '回复是否有模板';  

 

SQL添加索引

SQL Code复制内容到剪贴板
  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

 

增:

SQL Code复制内容到剪贴板
  1. INSERT INTO `test1` (`value`, `status`) VALUES ('值测试', 'new');  

删:

SQL Code复制内容到剪贴板
  1. DELETE FROM `test1` where `id`='1';  

改:

SQL Code复制内容到剪贴板
  1. UPDATE `test1` SET `question_id` = '10' WHERE `id` = '2';  

查:

SQL Code复制内容到剪贴板
  1. SELECT * FROM `test1` WHERE id = 2  

 

表的记录数:

SQL Code复制内容到剪贴板
  1. SELECT COUNT(*) AS `total` FROM `test1`  

 

 

1.jpg 

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

SQL Code复制内容到剪贴板
  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:

 

SQL Code复制内容到剪贴板
  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';  

3.jpg

 

 

 

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

SQL Code复制内容到剪贴板
  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';  

5.jpg

 


 

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

 QQ图片20171208142749.png

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

SQL Code复制内容到剪贴板
  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的已经没有了

 QQ截图20171208142940.png

 (如果是多个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:

SQL Code复制内容到剪贴板
  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 了,

如:

PHP Code复制内容到剪贴板
  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产生同样的结果

PHP Code复制内容到剪贴板
  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 来连接条件,否则将导致引擎放弃使用索引而进行全表扫描,如:

PHP Code复制内容到剪贴板
  1. select id from t where num=10 or num=20  

可以这样查询:

PHP Code复制内容到剪贴板
  1. select id from t where num=10 unsion all select id from t where num=20  

 

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

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

PHP Code复制内容到剪贴板
  1. select id from t where num is null  


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

PHP Code复制内容到剪贴板
  1. select id from t where name like ‘%abc%’  
  2. select id from t where name like ‘%abc’  

 

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

PHP Code复制内容到剪贴板
  1. select id from t where num/2=100  

应改为:

PHP Code复制内容到剪贴板
  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

    匿名评论
  • 评论
人参与,条评论

相关文章

    无相关信息
网站分类