mysql执行语句（SQL优化） - 后端笔记

创建数据表：

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

SELECT AVG(column_name) FROM table_name

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

UPDATE Person SET Address = 'Zhongshan 23', City = 'Nanjing'
WHERE LastName = 'Wilson'

mysql中replace函数直接替换mysql数据库中某字段中的特定字符串，不再需要自己写函数去替换，用起来非常的方便，mysql 替换函数replace()
Update `table_name` SET `field_name` = replace (`field_name`,’from_str’,'to_str’) Where `field_name` LIKE ‘%from_str%’

实例：把'病假' 替换为 '--'：UPDATE users SET username=REPLACE(username,'病假','--') WHERE username LIKE '%病假%';
说明：
table_name —— 表的名字
field_name —— 字段名
from_str —— 需要替换的字符串
to_str —— 替换成的字符串

查：

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的出场了

SQL Code复制内容到剪贴板

// 查询新闻表中，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';

场景：全年级有20个班我现在要给每个班的第一名做一个排行榜. 现有表 class_user

比如

name class_num score

小明 1 100

小红 1 95

大东 2 30

大民 2 50

小力 2 99

要求按成绩排名返回 name class_num score

SQL Code复制内容到剪贴板

select a.name, b.class_num, b.score
from class_user a, (
select class_num, max(score) as score from class_user group by class_num
) b
where a.class_num=b.class_num and a.score=b.score

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

QQ图片20171208142749.png

需要查询出，同时符合，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的已经没有了

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

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

"
SELECT `id` , `title` , `titlepic` , `classid` , `dizhi` , `price` , `goup` , `startime` , `endtime` , `likenum` , `area` , `date` , `zxid` , `plnum`
FROM phome_ecms_news
WHERE classid =6
AND ( {$currentTime} > startime
AND {$currentTime} < endtime )
OR {$currentTime} < startime
LIMIT 0 , 30
"

sql中多处理一个时间格式的：

SQL Code复制内容到剪贴板

SELECT
EventId,
EmployeeID,
EventTime,
ControlText,
EventType,
EventText,
MAX(DATE_FORMAT(EventTime, "%Y%m%d%H%i")) maxVal
FROM
`widom_monitor_door_tevent`
WHERE EventType IN (10, 11)
GROUP BY EmployeeID, DATE_FORMAT(EventTime, "%Y%m%d%H%i")
ORDER BY DATE_FORMAT(EventTime, "%Y%m%d%H") DESC

QQ截图20181128133932.png

时间转换函数（mysql）

FROM_UNIXTIME(EventData.DataTime,'%Y-%m-%d %H:%i:%s') as EventTime,

或者

DATE_FORMAT(EventTime, "%Y%m%d%H%i")

时间字段存为float格式：

QQ截图20190116154750.png

在access库转换成时间格式：

Format(EventData.DataTime, "dd mmmm yyyy") as EventTime,

Format(EventData.DataTime, "yyyy-mm-dd hh:nn:ss") as EventTime,

在sqlserver中转换成时间格式：

convert(datetime,EventData.DataTime,120) as EventTime,

在mysql中转换成时间格式：

FROM_UNIXTIME(created_at,'%Y-%m-%d %H:%i:%S')

PHP Code复制内容到剪贴板

select created_at, FROM_UNIXTIME(created_at,'%Y-%m-%d %H:%i:%S') as created FROM `ningwang_enewsuser_profile`

如果是需要将日期转为时间戳再格式化：

PHP Code复制内容到剪贴板

from_unixtime(UNIX_TIMESTAMP(pay_time), '%Y-%c-%d') as time

这里会出一个问题就是mysql的时区很可能不对，可以进入mysql查看当前时间对不对，如果是以下这种情况，当前时间是2020-12-17 17:02，那么表示时区有问题，需要在mysql中配置时区

mysql> select now();

+---------------------+

| now() |

+---------------------+

| 2020-12-17 09:00:55 |

+---------------------+

1 row in set (0.00 sec)

mysql> show VARIABLES like '%time_zone%';

+------------------+--------+

| Variable_name | Value |

+------------------+--------+

| system_time_zone | UTC |

| time_zone | SYSTEM |

+------------------+--------+

2 rows in set (0.01 sec)

修改配置文件

找到my.ini, 在mysqld 下增加 default-time-zone = '+8:00'

必须放到mysqld 下,放入其它位置无效

WX20200705-215308@2x.png

根据数据库中的EmployeeID人员ID和EventType刷卡动作过滤，过滤掉同一时间（Y-m-d H:i）的记录

bug：应该调3947的那条数据

QQ截图20181128140908.png

SQL Code复制内容到剪贴板

SELECT
EventId,
EmployeeID,
EventTime,
ControlText,
EventType,
EventText,
max(EventId)
FROM
`widom_monitor_door_tevent`
WHERE EventType IN (10, 11)
GROUP BY EmployeeID, DATE_FORMAT(EventTime, "%Y%m%d%H%i")
ORDER BY EventTime DESC

case when

SQL Code复制内容到剪贴板

select u.id,u.name,u.sex,
(case u.sex
when 1 then '男'
when 2 then '女'
else '空的'
end
)性别
from users u;

结果中的"sex"是用代码表示的，希望将代码用中文表示。可在语句中使用case语句

access数据库不支持case when，可以使用switch替代：

SQL Code复制内容到剪贴板

SELECT USERID,
CHECKTYPE,
SWITCH(
CHECKTYPE = 'I', '签到',
CHECKTYPE = 'O', '签退',
True,'') AS 行为
from CHECKINOUT

三张表，cut,label,map，通过一条sql查询其多条label，以逗号隔开

cut表

WX20181229-111057@2x.png

label表

WX20181229-110905@2x.png

map关联表

WX20181229-111235@2x.png

SQL Code复制内容到剪贴板

SELECT
c.*,
GROUP_CONCAT(l.label_name SEPARATOR ', ') AS label
FROM
`yy_cut` c
INNER JOIN `yy_cut_map` m
ON m.`cut_id` = c.`id`
INNER JOIN
`yy_cut_label` l
ON m.`label_id` = l.`id`
AND m.`cut_id` = 1

SQL Code复制内容到剪贴板

SELECT
c.*,
GROUP_CONCAT(l.id SEPARATOR ', ') AS label_id,
GROUP_CONCAT(l.label_name SEPARATOR ', ') AS label
FROM
`yy_cut` c
INNER JOIN `yy_cut_map` m
ON m.`cut_id` = c.`id`
INNER JOIN
`yy_cut_label` l
ON m.`label_id` = l.`id`
AND m.`cut_id` = 1

label_id:label_name.png

查询指定用户参与的全部项目下的，所有用户id，过滤掉相同用户id：

SQL Code复制内容到剪贴板

SELECT
DISTINCT user_id
FROM
block_project_user
WHERE
project_id IN (
SELECT
DISTINCT project_id
FROM
block_project_user
WHERE
user_id = 28
)

WX20200714-180447@2x.png

四张表（组织成员表、用户主表、用户副表、职务表），查询指定组织下的所有成员，及其职务等，并且按职务查询

QQ20200822-232652@2x.png

组织成员表

QQ20200822-232917@2x.png

用户主表

QQ20200822-233051@2x.png

用户副表（多个职务，以逗号隔开）

QQ20200822-233145@2x.png

职务表

条件：查询所有org_id = 1，并且职务 = 理事长的用户数据

sql 注意job_id 是放在left join里面，不放在where里面：

SQL Code复制内容到剪贴板

SELECT
`yii2_common_organization_member`.*
FROM
`yii2_common_organization_member`
INNER JOIN `yii2_enewsuser` ON `yii2_common_organization_member`.`user_id` = `yii2_enewsuser`.`id`
LEFT JOIN `yii2_enewsuser_profile` ON `yii2_enewsuser`.`id` = `yii2_enewsuser_profile`.`user_id`
LEFT JOIN `yii2_enewsuser_job` ON ( `yii2_enewsuser_profile`.`job_id` = `yii2_enewsuser_job`.`id` )
AND ( `yii2_enewsuser_job`.`id` = '14' )
WHERE
`org_id` = '11'
GROUP BY
`user_id`
ORDER BY
`sort`,
`id`

yii2的关联写法：

PHP Code复制内容到剪贴板

// inner join：理解为“有效连接”，两张表中都有的数据才会显示
$query = OrganizationMember::find()->innerJoinWith("user")->joinWith([
"user.profile",
"user.profile.job" => function ($query) use ($job_id) {
if ($job_id) {
return $query->onCondition([Job::tableName().".id" => $job_id]);
}
}
])->andFilterWhere(["org_id" => $org_id])->groupBy("user_id")->orderBy("sort asc,id asc");

关于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.避免使用前置百分号的模糊查询，不走索引，可以使用后百分号，避免使用前百分号

PHP Code复制内容到剪贴板

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

explain分析sql语句执行效率

例如：explain select * from news;

输出：

+----+-------------+-------+-------+-------------------+---------+---------+-------+------
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+-------------------+---------+---------+-------+------

下面对各个属性进行了解：

1、id：这是SELECT的查询序列号

2、select_type：select_type就是select的类型，可以有以下几种：

SIMPLE：简单SELECT(不使用UNION或子查询等)

PRIMARY：最外面的SELECT

UNION：UNION中的第二个或后面的SELECT语句

DEPENDENT UNION：UNION中的第二个或后面的SELECT语句，取决于外面的查询

UNION RESULT：UNION的结果。

SUBQUERY：子查询中的第一个SELECT

DEPENDENT SUBQUERY：子查询中的第一个SELECT，取决于外面的查询

DERIVED：导出表的SELECT(FROM子句的子查询)

3、table：显示这一行的数据是关于哪张表的

4、type：这列最重要，显示了连接使用了哪种类别,有无使用索引，是使用Explain命令分析性能瓶颈的关键项之一。

结果值从好到坏依次是：

system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

一般来说，得保证查询至少达到range级别，最好能达到ref，否则就可能会出现性能问题。

5、possible_keys：列指出MySQL能使用哪个索引在该表中找到行

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

7、key_len：显示MySQL决定使用的键长度。如果键是NULL，则长度为NULL。使用的索引的长度。在不损失精确性的情况下，长度越短越好

8、ref：显示使用哪个列或常数与key一起从表中选择行。

9、rows：显示MySQL认为它执行查询时必须检查的行数。

10、Extra：包含MySQL解决查询的详细信息，也是关键参考项之一。

Distinct
一旦MYSQL找到了与行相联合匹配的行，就不再搜索了

Not exists
MYSQL 优化了LEFT JOIN，一旦它找到了匹配LEFT JOIN标准的行，

就不再搜索了

Range checked for each

Record（index map:#）
没有找到理想的索引，因此对于从前面表中来的每一个行组合，MYSQL检查使用哪个索引，并用它来从表中返回行。这是使用索引的最慢的连接之一

Using filesort
看到这个的时候，查询就需要优化了。MYSQL需要进行额外的步骤来发现如何对返回的行排序。它根据连接类型以及存储排序键值和匹配条件的全部行的行指针来排序全部行

Using index
列数据是从仅仅使用了索引中的信息而没有读取实际的行动的表返回的，这发生在对表的全部的请求列都是同一个索引的部分的时候

Using temporary
看到这个的时候，查询需要优化了。这里，MYSQL需要创建一个临时表来存储结果，这通常发生在对不同的列集进行ORDER BY上，而不是GROUP BY上

Using where
使用了WHERE从句来限制哪些行将与下一张表匹配或者是返回给用户。如果不想返回表中的全部行，并且连接类型ALL或index，这就会发生，或者是查询有问题

其他一些Tip：

当type 显示为 “index” 时，并且Extra显示为“Using Index”，表明使用了覆盖索引。

本文来自于：http://www.yoyo88.cn/note/backend/45.html

上一篇 php JSON数据格式化方法

下一篇点击加载更多 by helloweba