mysql执行语句（SQL优化实例）
2018-07-05 站长 站长日志
创建数据表：
SQL Code复制内容到剪贴板- CREATE TABLE `test1` (
- `id` int(11) NOT NULL AUTO_INCREMENT,
- `firstname` TINYBLOB NOT NULL COMMENT '用户名/支持表情符号255字节',/* 修改字段类型 */
- `question_id` int(11) NOT NULL DEFAULT '0' COMMENT '问题ID',
- `customer_id` int(11) NOT NULL DEFAULT '0' COMMENT '用户ID',
- `approved` tinyint(1) NOT NULL DEFAULT '0' COMMENT '字段备注',
- `customer_name` varchar(32) NOT NULL DEFAULT '' COMMENT '用户姓名',
- `customer_phone` varchar(11) NOT NULL DEFAULT '' COMMENT '用户手机号',
- `value` text NOT NULL COMMENT '值',
- `order_total` decimal(15,4) NOT NULL DEFAULT '0.0000',
- `status` enum('new','invalid','valid','transfered') NOT NULL DEFAULT 'new',
- `date_added` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
- PRIMARY KEY (`id`),
- KEY `question_id` (`question_id`)
- ) ENGINE=MyISAM DEFAULT CHARSET=utf8;
PHP Code复制内容到剪贴板
- 标准命令：mysql -u数据库用户名 -p数据库密码 数据库名 < 数据库文件名.sql
- 示范案例：mysql -ulaozuo -plaozuo.org laozuo < laozuo.sql
SQL添加一个字段
SQL Code复制内容到剪贴板- ALTER TABLE `test1` ADD COLUMN `description` varchar(255) NOT NULL DEFAULT '' COMMENT '备注';
SQL添加一个枚举类型的字段（节省数据库资源）
SQL Code复制内容到剪贴板- ALTER TABLE `mcc_service_question_options` ADD COLUMN `if_temp` ENUM( 'use_temp', 'no_temp') NOT NULL DEFAULT 'no_temp' COMMENT '回复是否有模板';
SQL添加索引
SQL Code复制内容到剪贴板- 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复制内容到剪贴板- INSERT INTO `test1` (`value`, `status`) VALUES ('值测试', 'new');
删：
SQL Code复制内容到剪贴板- DELETE FROM `test1` where `id`='1';
改：
SQL Code复制内容到剪贴板- UPDATE `test1` SET `question_id` = '10' WHERE `id` = '2';
查：
SQL Code复制内容到剪贴板- SELECT * FROM `test1` WHERE id = 2
表的记录数：
SQL Code复制内容到剪贴板- SELECT COUNT(*) AS `total` FROM `test1`
keyboard的存储格式是：1,3,4的格式存储
我们的$tagId 假设是4，那么我们需要找到 keyboard中含有4的条数，那么就是FIND_IN_SET的出场了
- // 查询新闻表中，keyboard字段中包含 tagid
- 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复制内容到剪贴板
- SELECT
- t.*, t2.maxVal
- FROM
- `biz_order` t
- JOIN (
- SELECT
- order_id,
- MAX(course_end_time) maxVal
- FROM
- `biz_order_goods`
- GROUP BY
- order_id
- ) t2 ON t.id = t2.order_id WHERE t.id in (1,2,3) and t2.maxVal < '2017-06-07 9:54';
这里的datetime会有点问题，可以在sql中转换一下
SQL Code复制内容到剪贴板- SELECT
- t.id,t.order_no, t2.maxVal
- FROM
- `biz_order` t
- JOIN (
- SELECT
- order_id,
- MAX( UNIX_TIMESTAMP(course_end_time) ) maxVal
- FROM
- `biz_order_goods`
- GROUP BY
- order_id
- ) t2 ON t.id = t2.order_id WHERE t.id in (1,2,3) and t2.maxVal < '1496801089';
同一张表中，查询符合两条记录的记录，示意图：
需要查询出，同时符合，filter_id = 5，并且符合filter_id = 6的数据
SQL Code复制内容到剪贴板- SELECT *
- FROM `hzb_article_filter`
- WHERE filter_id IN (
- SELECT filter_id
- FROM `hzb_article_filter`
- WHERE filter_id IN(5,6)
- GROUP BY article_id
- HAVING COUNT(*) > 1
- )
结果，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：
SQL Code复制内容到剪贴板- SELECT
- `t1`.*
- FROM
- `hzb_article_filter` `t1`
- JOIN `hzb_article_filter` `t2` ON t1.article_id = t2.article_id
- AND t2.filter_id = 5
- JOIN `hzb_article_filter` `t3` ON t2.article_id = t3.article_id
- AND t3.filter_id = 8
- JOIN `hzb_article_filter` `t4` ON t3.article_id = t4.article_id
- AND t4.filter_id = 13
- WHERE
- t1.filter_id = 3
关于SQL的一些优化及注意点：
由于系统的瓶颈大多出现在数据库层面，所以对数据库的操作需要提出一些开发原则，这些开发原则在前期可能会增加工作量，并且对系统性能的提升并不大，反而某些复杂语句可能会影响性能，但是在数据量急剧膨胀后，会带来性能上的极大提升，并且为sql语句的调优，以及系统的拆分打下了良好的基础，开发人员需要严格遵守。
1.禁止两张表以上的关联查询。
在数据量较大时，连表查询会严重拖慢数据库性能，将大sql拆分成多个子sql，在程序中拼接数据。
可以使用left join, innerjoin, 等，但不要两张以上的数据表联查，可以在查询出来以后做foreach再继续查询，分割为小SQL，在数据量小的情况下可能还会慢一点，但在数据量大的情况下，就是翻倍的提高效率
2.禁止使用存储过程、函数、触发器、外键约束。
后期带来的坏处比好处多，互联网公司不这么玩。
3.禁止使用in子查询和not in。
不走索引
对于连续的数值，能用 between 就不要用 in 了，
如：
PHP Code复制内容到剪贴板- select id from t where num between 1 and 3
- select num from a where num in(select num from b)
用下面的语句替换：exists与in产生同样的结果
PHP Code复制内容到剪贴板- 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复制内容到剪贴板- select id from t where num=10 or num=20
可以这样查询：
PHP Code复制内容到剪贴板- select id from t where num=10 unsion all select id from t where num=20
7.应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描，如：
在建立字段的时候默认值不要为null，为空
PHP Code复制内容到剪贴板- select id from t where num is null
8.避免使用前置百分号的模糊查询，不走索引，可以使用后百分号，避免使用前百分号
- select id from t where name like ‘%abc%’
- select id from t where name like ‘%abc’
9.不要在 where 子句中的“=”左边进行函数、算术运算或其他表达式运算，否则系统将可能无法正确使用索引。
PHP Code复制内容到剪贴板- select id from t where num/2=100
应改为:
PHP Code复制内容到剪贴板- 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
上一篇：帝国CMS常用调用标签