우아한 SQL 네이티브 문을 작성하는 방법

서문:

이전 글에서 MySQL의 기본 아키텍처에 대해 이야기할 때 "MySql 아키텍처에서 SQL 쿼리문은 어떻게 실행되는가?"로 시작했습니다. 포괄적인 설명이 제공되었습니다. MySql 아키텍처에서 sql 쿼리문의 구체적인 실행 과정을 알고 있지만, sql문을 더 좋고 빠르게 작성하기 위해서는 sql문에 포함된 각 절의 실행 순서를 아는 것이 매우 필요하다고 생각합니다. 이전 글을 읽은 친구들은 SQL 문 끝의 각 절의 실행이 실행기에서 완료되어야 하며 스토리지 엔진이 실행기에게 데이터 읽기 및 쓰기 인터페이스를 제공한다는 것을 알아야 합니다. 이제 연구를 시작하겠습니다

문의 각 절에 대한 전체 실행 순서 요약(순서 번호에 따라 실행됨)

1. from(참고: from의 하위 명령문도 포함됩니다)

2. join

3. on

4. where

5. group by(select에서 별칭 사용을 시작하면 후속 명령문에서 사용할 수 있음)

6. avg,sum.... 및 기타 집계 함수

7. having

8. select

9. distinct

10. order by

11. limit

모든 쿼리 문은 실행 과정에서 시작하여 실행됩니다. 다음 단계에서는 다음 실행 단계의 입력 역할을 할 가상 테이블을 생성합니다.

1. from

form은 쿼리문의 시작입니다.

테이블인 경우 이 테이블이 직접 작동됩니다.
이 from 뒤에 하위 쿼리가 오면 하위 쿼리의 내용이 먼저 실행되고 하위 쿼리의 결과가 첫 번째 가상이 됩니다. 테이블 T1. (참고: 하위 쿼리의 실행 프로세스도 이 문서에 설명된 순서대로 진행됩니다.)
테이블을 연결해야 하는 경우 Join을 사용하세요. 2, 3을 참조하세요.
2. Join

다음에 여러 테이블이 있는 경우 Join Association은 먼저 처음 두 테이블에 대해 데카르트 곱을 수행합니다. 이때 첫 번째 가상 테이블 T1이 생성됩니다(참고: 여기서는 상대적으로 작은 테이블이 기본 테이블로 선택됩니다).

3.on

가상 테이블 T1에 대해 ON 필터링을 수행하고 일치하는 행만 수행합니다. 가상 테이블 T2에 기록됩니다. (여기에 연결된 세 번째 테이블이 있는 경우 T2와 세 번째 테이블의 데카르트 곱을 사용하여 T3 테이블을 생성하고 T4 테이블을 생성하는 단계에서 3. 단계를 계속 반복하지만 다음은 여기서는 T3 및 T4의 경우 테이블 연결 쿼리 T2)

4에서 계속 진행합니다. 여기서

가상 테이블 T2에 대해 WHERE 조건 필터링을 수행합니다. 일치하는 레코드만 가상 테이블 T3에 삽입됩니다.

5.group by

group by 절은 내부의 고유한 값을 그룹으로 결합하여 가상 테이블 T4를 얻습니다. 그룹화 기준이 적용되면 이후의 모든 단계는 T4 열에서만 작동하거나 6. 집계 기능(count, sum, avg 등)을 수행할 수 있습니다. (참고: 그룹화 후 최종 결과 집합에는 각 그룹의 한 행만 포함되기 때문입니다. 그렇지 않으면 여기서 많은 문제가 발생하며 다음과 같은 코드 오해가 구체적으로 언급됩니다.)

6. ... 집계 함수를 기다리는 중

집계 함수는 합산, 통계량 등과 같은 원하는 집계 값을 얻기 위해 그룹화된 결과에 대해 일부 처리만 수행할 뿐 가상 테이블을 생성하지는 않습니다.

7.Having

Hing 필터를 적용하여 T5를 생성합니다. HAVING 절은 주로 GROUP BY 절과 함께 사용됩니다. 갖는 필터는 그룹화된 데이터에 적용되는 최초이자 유일한 필터입니다.

8. select

select 작업을 수행하고 지정된 열을 선택하여 가상 테이블 T6에 삽입합니다.

9. 뚜렷이

T6에서 기록을 중복 제거합니다. 동일한 행을 제거하여 가상 테이블 T7을 생성합니다. (참고: 실제로 group by 절을 적용하면 구별이 중복됩니다. 그 이유도 그룹화할 때 해당 열의 고유 값이 하나의 그룹으로 그룹화되기 때문입니다. 각 그룹에 대해서만 한 행의 레코드를 반환하므로 모든 레코드가 다릅니다)

10.order by

order by 절을 적용합니다. order_by_condition에 따라 T7을 정렬합니다. 이때 가상 테이블 대신 커서가 반환됩니다. SQL은 집합 이론을 기반으로 합니다. 집합은 행을 미리 정렬하지 않으며 구성원의 논리적 모음일 뿐이며 구성원의 순서는 관련이 없습니다. 테이블을 정렬하는 쿼리는 특정 물리적 순서로 논리적 구성을 포함하는 개체를 반환할 수 있습니다. 이 개체를 커서라고 합니다.
order by에 대한 참고 사항

• order by의 반환 값은 커서이므로 order by 절을 사용한 쿼리는 테이블 표현식에 적용할 수 없습니다.

• Order by sorting은 비용이 많이 듭니다.

• order by의 두 매개변수는 asc(오름차순) desc(내림차순)

11.limit

지정된 행의 레코드를 검색하여 가상 테이블 T9를 생성하고 결과를 반환합니다.

limit 뒤의 매개변수는 한계 m 또는 한계 mn일 수 있습니다. 이는 m번째 데이터부터 n번째 데이터까지를 의미합니다.

(참고: 많은 개발자가 페이징 문제를 해결하기 위해 이 명령문을 사용하기를 좋아합니다. 작은 데이터의 경우 LIMIT 절을 사용해도 문제가 없습니다. 데이터의 양이 매우 클 경우 LIMIT n, m을 사용하는 것은 매우 비효율적입니다. 왜냐하면 LIMIT 매번 처음부터 스캔하는 방식입니다. 600,000번째 행부터 3개의 데이터를 읽어야 하는 경우 먼저 600,000번째 행을 스캔하여 찾은 다음 읽어야 합니다. 따라서 빅 데이터 처리를 위해서는 애플리케이션 계층에서 특정 캐싱 메커니즘을 구축하는 것이 매우 필요합니다)

SQL 조각 개발

SELECT `userspk`.`avatar` AS `user_avatar`, 
`a`.`user_id`, 
`a`.`answer_record`, 
 MAX(`score`) AS `score`FROM (select * from pkrecord  order by score desc) as a 
INNER JOIN `userspk` AS `userspk` ON `a`.`user_id` = `userspk`.`user_id`WHERE `a`.`status` = 1 AND `a`.`user_id` != 'm_6da5d9e0-4629-11e9-b5f7-694ced396953' GROUP BY `user_id`ORDER BY `a`.`score` DESC LIMIT 9;

쿼리 결과:

• 이야기해 봅시다. 먼저 간단히 문의하고 싶은 내용은 다음과 같습니다.

想要查询pk记录表中分数最高的9个用户记录和他们的头像。

• 通过这段sql实际想一遍sql各字句的执行顺序

pk记录表的数据结构设计，每个用户每天每个馆下可能会有多条记录，所以需要进行分组，并且查询结果只想拿到每个分组内最高的那条记录。

这段sql的一些说明：

1. 可能有些同学会认为子查询没有必要 直接查询pk记录表就可以，但是并不能拿到预期的结果，因为分组后的每个组结果是不进行排序的，而且max拿到的最高分数肯定是对应的该分组下最高分数，但是其它记录可能就不是最高分数对应的那条记录。所以子查询非常有必要，它能够对原始的数据首先进行排序，分数最高的那条就是第一条对应的第一条记录。

看一下代码和执行结果与带有子查询的进行比较，就能理解我上面说的一段话：

//不使用子查询SELECT `userspk`.`avatar` AS `user_avatar`, 
`pkrecord`.`user_id`, 
`pkrecord`.`answer_record`, 
`pkrecord`.`id`, 
 MAX(`score`) AS `score`FROM pkrecordINNER JOIN `userspk` AS `userspk` ON `pkrecord`.`user_id` = `userspk`.`user_id`WHERE `pkrecord`.`status` = 1 AND `pkrecord`.`user_id` != 'm_6da5d9e0-4629-11e9-b5f7-694ced396953' GROUP BY `user_id`ORDER BY `pkrecord`.`score` DESC LIMIT 9;

查询结果

2. 在子查询中对数据已经进行排序后，外层排序方式如果和子查询排序分数相同，都是分数倒序，外层的排序可以去掉，没有必要写两遍。

sql语句中的别名

别名在哪些情况使用

在 SQL 语句中，可以为表名称及字段（列）名称指定别名

• 表名称指定别名

同时查询两张表的数据的时候： 未设置别名前：

SELECT article.title,article.content,user.username FROM article, userWHERE article.aid=1 AND article.uid=user.uid

设置别名后：

SELECT a.title,a.content,u.username FROM article AS a, user AS u where a.aid=1 and a.uid=u.uid

好处：使用表别名查询，可以使 SQL 变得简洁而更易书写和阅读，尤其在 SQL 比较复杂的情况下

• 查询字段指定别名

查询一张表，直接对查询字段设置别名

SELECT username AS name,email FROM user

查询两张表

好处：字段别名一个明显的效果是可以自定义查询数据返回的字段名；当两张表有相同的字段需要都被查询出，使用别名可以完美的进行区分，避免冲突

SELECT a.title AS atitle,u.username,u.title AS utitle FROM article AS a, user AS u where a.uid=u.uid

• 关联查询时候，关联表自身的时候，一些分类表，必须使用别名。

• 别名也可以在group         by与having的时候都可使用

• 别名可以在order by排序的时候被使用

  查看上面一段sql

• delete ， update MySQL都可以使用别名，别名在多表（级联）删除尤为有用

delete t1,t2 from t_a t1 , t_b t2 where t1.id = t2.id

• 子查询结果需要使用别名

  查看上面一段sql

别名使用注意事项

• 虽然定义字段别名的 AS 关键字可以省略，但是在使用别名时候，建议不要省略 AS 关键字

书写sql语句的注意事项

书写规范上的注意

• 字符串类型的要加单引号

• select后面的每个字段要用逗号分隔，但是最后连着from的字段不要加逗号

• 使用子查询创建临时表的时候要使用别名，否则会报错。

为了增强性能的注意

• 不要使用“select * from ……”返回所有列，只检索需要的列，可避免后续因表结构变化导致的不必要的程序修改，还可降低额外消耗的资源

• 不要检索已知的列

select  user_id,name from User where user_id = ‘10000050’

• 使用可参数化的搜索条件，如=, >, >=, , !=, !>, !

• 当需要验证是否有符合条件的记录时，使用exists，不要使用count(*)，前者在第一个匹配记录处返回，后者需要遍历所有匹配记录

• Where子句中列的顺序与需使用的索引顺序保持一致，不是所有数据库的优化器都能对此顺序进行优化，保持良好编程习惯（索引相关）

• 不要在where子句中对字段进行运算或函数（索引相关）

1. 如where  amount / 2 > 100，即使amount字段有索引，也无法使用，改成where amount > 100 * 2就可使用amount列上的索引

2. 如where substring( Lastname, 1, 1) = ‘F’就无法使用Lastname列上的索引，而where Lastname like ‘F%’或者where Lastname >= ‘F’ and Lastname

• 在有min、max、distinct、order by、group by操作的列上建索引，避免额外的排序开销（索引相关）

• 小心使用or操作，and操作中任何一个子句可使用索引都会提高查询性能，但是or条件中任何一个不能使用索引，都将导致查询性能下降，如where member_no = 1 or provider_no = 1，在member_no或provider_no任何一个字段上没有索引，都将导致表扫描或聚簇索引扫描（索引相关）

• Between一般比in/or高效得多，如果能在between和in/or条件中选择，那么始终选择between条件，并用>=和=和

• 성능을 최적화하려면 조인 작업의 순서를 조정하세요. 조인 작업은 하향식입니다. 성능을 향상하려면 더 작은 결과 집합이 있는 두 테이블의 연결을 앞에 두세요. (가입 관련) 참고: 색인 생성과 연관에 대해 자세히 설명하기 위해 두 개의 개별 기사를 꺼내겠습니다. 이 노트에서는 이에 대해 간략하게만 언급하겠습니다.

더 많은 MySQL 관련 기술 기사를 보려면 MySQL Tutorial 칼럼을 방문하여 알아보세요!

위 내용은 우아한 SQL 네이티브 문을 작성하는 방법의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!