면접관: SQL 최적화에 대해 잘 알고 계시나요? 제가 아는 종류는 20가지 뿐인데 훨씬 더 많아요...

"최적화 전략"

1. 쿼리를 최적화하려면 먼저 WHERE 및 ORDER BY에 포함된 열에 대한 인덱스 생성을 고려해야 합니다.

2. WHERE 절의 필드에 대한 NULL 값 판단을 피하십시오. 테이블 생성 시 기본값은 NULL이지만 대부분 NOT NULL을 사용하거나 0, -1과 같은 특수 값을 사용해야 합니다. 기본값으로.

3. WHERE 절에 != 또는 <> 연산자를 사용하지 마세요. MySQL은 <, <=, =, >, >=, BETWEEN, IN, 때로는 LIKE 연산자에 대해서만 인덱스를 사용합니다.

4. 조건을 연결하기 위해 WHERE 절에 OR을 사용하지 마세요. 그렇지 않으면 엔진이 인덱스 사용을 포기하고 전체 테이블 스캔을 수행합니다.

select id from t where num=10 union all select id from t where num=20。

5. IN 또한 주의해서 사용해야 합니다. 그렇지 않으면 전체 테이블 스캔이 발생합니다. 연속 값의 경우 BETWEEN을 사용할 수 있으면 IN을 사용하지 마십시오:

select id from t where num between 1 and 3。

6. 다음 쿼리도 전체 테이블 스캔을 발생시킵니다.

select id from t where name like‘%abc%’

또는

select id from t where name like‘%abc’

효율성을 높이려면 전체 텍스트 검색을 고려할 수 있습니다. 그리고

select id from t where name like‘abc%’

才用到索引。

7、如果在 WHERE 子句中使用参数，也会导致全表扫描。

8、应尽量避免在 WHERE 子句中对字段进行表达式操作，应尽量避免在 WHERE 子句中对字段进行函数操作。

9、很多时候用 EXISTS 代替 IN 是一个好的选择：

select num from a where num in(select num from b)

用下面的语句替换：

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

10、索引固然可以提高相应的 SELECT 的效率，但同时也降低了 INSERT 及 UPDATE 的效。因为 INSERT 或 UPDATE 时有可能会重建索引，所以怎样建索引需要慎重考虑，视具体情况而定。一个表的索引数最好不要超过 6 个，若太多则应考虑一些不常使用到的列上建的索引是否有必要。

11、应尽可能的避免更新 clustered 索引数据列， 因为 clustered 索引数据列的顺序就是表记录的物理存储顺序，一旦该列值改变将导致整个表记录的顺序的调整，会耗费相当大的资源。若应用系统需要频繁更新 clustered 索引数据列，那么需要考虑是否应将该索引建为 clustered 索引。

12、尽量使用数字型字段，若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，并会增加存储开销。

13、尽可能的使用 varchar, nvarchar 代替 char, nchar。因为首先变长字段存储空间小，可以节省存储空间，其次对于查询来说，在一个相对较小的字段内搜索效率显然要高些。

14、最好不要使用返回所有：select from t ，用具体的字段列表代替 “*”，不要返回用不到的任何字段。

15、尽量避免向客户端返回大数据量，若数据量过大，应该考虑相应需求是否合理。

16、使用表的别名（Alias）：当在 SQL 语句中连接多个表时，请使用表的别名并把别名前缀于每个 Column 上。这样一来，就可以减少解析的时间并减少那些由 Column 歧义引起的语法错误。

17、使用“临时表”暂存中间结果 ：

简化 SQL 语句的重要方法就是采用临时表暂存中间结果。但是临时表的好处远远不止这些，将临时结果暂存在临时表，后面的查询就在 tempdb 中了，这可以避免程序中多次扫描主表，也大大减少了程序执行中“共享锁”阻塞“更新锁”，减少了阻塞，提高了并发性能。

18、一些 SQL 查询语句应加上 nolock，读、写是会相互阻塞的，为了提高并发性能。对于一些查询，可以加上 nolock，这样读的时候可以允许写，但缺点是可能读到未提交的脏数据。

使用 nolock 有3条原则：

• 查询的结果用于“插、删、改”的不能加 nolock；
• 查询的表属于频繁发生页分裂的，慎用 nolock ；
• 使用临时表一样可以保存“数据前影”，起到类似 Oracle 的 undo 表空间的功能，能采用临时表提高并发性能的，不要用 nolock。

19、常见的简化规则如下：

不要有超过 5 个以上的表连接（JOIN），考虑使用临时表或表变量存放中间结果。少用子查询，视图嵌套不要过深，一般视图嵌套不要超过 2 个为宜。

20、将需要查询的结果预先计算好放在表中，查询的时候再Select。这在SQL7.0以前是最重要的手段，例如医院的住院费计算。

21、用 OR 的字句可以分解成多个查询，并且通过 UNION 连接多个查询。他们的速度只同是否使用索引有关，如果查询需要用到联合索引，用 UNION all 执行的效率更高。多个 OR 的字句没有用到索引，改写成 UNION 的形式再试图与索引匹配。一个关键的问题是否用到索引。

22、在IN后面值的列表中，将出现最频繁的值放在最前面，出现得最少的放在最后面，减少判断的次数。

23、尽量将数据的处理工作放在服务器上，减少网络的开销，如使用存储过程。

存储过程是编译好、优化过、并且被组织到一个执行规划里、且存储在数据库中的 SQL 语句，是控制流语言的集合，速度当然快。反复执行的动态 SQL，可以使用临时存储过程，该过程（临时表）被放在 Tempdb 中。

24、当服务器的内存够多时，配制线程数量 = 最大连接数+5，这样能发挥最大的效率；否则使用配制线程数量< 最大连接数，启用 SQL SERVER 的线程池来解决，如果还是数量 = 最大连接数+5，严重的损害服务器的性能。

25、查询的关联同写的顺序 ：

select a.personMemberID, * from chineseresume a,personmember b where personMemberID = b.referenceid and a.personMemberID = 'JCNPRH39681' （A = B, B = '号码'） 
select a.personMemberID, * from chineseresume a,personmember b where a.personMemberID = b.referenceid and a.personMemberID = 'JCNPRH39681' and b.referenceid = 'JCNPRH39681' （A = B, B = '号码', A = '号码'） 
select a.personMemberID, * from chineseresume a,personmember b where b.referenceid = 'JCNPRH39681' and a.personMemberID = 'JCNPRH39681' （B = '号码', A = '号码'）

26、尽量使用 EXISTS 代替 select count(1) 来判断是否存在记录。count 函数只有在统计表中所有行数时使用，而且 count(1) 比 count(*) 更有效率。

27、尽量使用 “>=”，不要使用 “>”。

28、索引的使用规范：

索引的创建要与应用结合考虑，建议大的 OLTP 表不要超过 6 个索引；尽可能的使用索引字段作为查询条件，尤其是聚簇索引，必要时可以通过 index index_name 来强制指定索引；避免对大表查询时进行 table scan，必要时考虑新建索引；在使用索引字段作为条件时，如果该索引是联合索引，那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引，否则该索引将不会被使用；要注意索引的维护，周期性重建索引，重新编译存储过程。

29、下列 SQL 条件语句中的列都建有恰当的索引，但执行速度却非常慢：

SELECT * FROM record WHERE substrINg(card_no, 1, 4) = '5378' --13秒 
SELECT * FROM record WHERE amount/30 < 1000 --11秒 
SELECT * FROM record WHERE convert(char(10), date, 112) = &#39;19991201&#39; --10秒

分析：

WHERE 子句中对列的任何操作结果都是在 SQL 运行时逐列计算得到的，因此它不得不进行表搜索，而没有使用该列上面的索引。

如果这些结果在查询编译时就能得到，那么就可以被 SQL 优化器优化，使用索引，避免表搜索，因此将 SQL 重写成下面这样：

SELECT * FROM record WHERE card_no like &#39;5378%&#39; -- < 1秒 
SELECT * FROM record WHERE amount < 1000*30 -- < 1秒 
SELECT * FROM record WHERE date = &#39;1999/12/01&#39; -- < 1秒

30、当有一批处理的插入或更新时，用批量插入或批量更新，绝不会一条条记录的去更新。

31、在所有的存储过程中，能够用 SQL 语句的，我绝不会用循环去实现。

例如：列出上个月的每一天，我会用 connect by 去递归查询一下，绝不会去用循环从上个月第一天到最后一天。

32、选择最有效率的表名顺序（只在基于规则的优化器中有效）：

Oracle 的解析器按照从右到左的顺序处理 FROM 子句中的表名，FROM 子句中写在最后的表（基础表 driving table）将被最先处理，在 FROM 子句中包含多个表的情况下，你必须选择记录条数最少的表作为基础表。

如果有 3 个以上的表连接查询，那就需要选择交叉表（intersection table）作为基础表，交叉表是指那个被其他表所引用的表。

33、提高 GROUP BY 语句的效率，可以通过将不需要的记录在 GROUP BY 之前过滤掉。下面两个查询返回相同结果，但第二个明显就快了许多。

低效：

SELECT JOB, AVG(SAL) 
FROM EMP 
GROUP BY JOB 
HAVING JOB = 'PRESIDENT' 
OR JOB = 'MANAGER'

高效：

SELECT JOB, AVG(SAL) 
FROM EMP
WHERE JOB = 'PRESIDENT' 
OR JOB = 'MANAGER' 
GROUP BY JOB

34、SQL 语句用大写，因为 Oracle 总是先解析 SQL 语句，把小写的字母转换成大写的再执行。

35、别名的使用，别名是大型数据库的应用技巧，就是表名、列名在查询中以一个字母为别名，查询速度要比建连接表快 1.5 倍。

36、避免死锁，在你的存储过程和触发器中访问同一个表时总是以相同的顺序；事务应经可能地缩短，在一个事务中应尽可能减少涉及到的数据量；永远不要在事务中等待用户输入。

37、避免使用临时表，除非却有需要，否则应尽量避免使用临时表，相反，可以使用表变量代替。大多数时候（99%），表变量驻扎在内存中，因此速度比临时表更快，临时表驻扎在 TempDb 数据库中，因此临时表上的操作需要跨数据库通信，速度自然慢。

38、最好不要使用触发器：

触发一个触发器，执行一个触发器事件本身就是一个耗费资源的过程；如果能够使用约束实现的，尽量不要使用触发器；不要为不同的触发事件（Insert、Update 和 Delete）使用相同的触发器；不要在触发器中使用事务型代码。

39、索引创建规则：

테이블의 기본 키와 외래 키에는 인덱스가 있어야 하며, 데이터 볼륨이 300개 이상인 테이블에는 WHERE 절에 자주 나타나는 연결 필드에 인덱스가 있어야 합니다. 특히 인덱스는 큰 테이블의 필드에 구축되어야 하며, 인덱스는 작은 필드에 구축되어야 하며, 복합 인덱스 설정에는 주의가 필요합니다. 대신 단일 필드 인덱스를 사용해 보십시오. 일반적으로 선택성이 더 좋은 필드인 복합 인덱스의 기본 열 필드가 AND 모드의 WHERE 절에 자주 나타납니다. 동시에? 단일 필드 쿼리가 거의 없거나 전혀 없나요? 그렇다면 복합 색인을 작성할 수 있습니다. 그렇지 않으면 복합 색인에 포함된 필드가 WHERE 절에 단독으로 나타나는 경우 복합 색인에 다음보다 많은 항목이 포함되어 있으면 이를 여러 개의 단일 필드 색인으로 나눕니다. 3개의 필드가 있는 경우 필요성을 신중하게 고려하고 복합 필드 수를 줄이는 것을 고려하십시오. 이러한 필드에 단일 필드 인덱스와 복합 인덱스가 모두 있는 경우 일반적으로 데이터를 자주 수행하는 테이블을 너무 많이 만들지 마십시오. 실행 계획에 부정적인 영향을 주지 않도록 쓸모 없는 인덱스를 삭제합니다. 테이블에 생성된 각 인덱스는 스토리지 오버헤드를 증가시키며 인덱스는 삽입, 삭제 및 업데이트 작업에 대한 처리 오버헤드도 증가시킵니다. 또한 단일 필드 인덱스가 있는 경우 일반적으로 너무 많은 복합 인덱스는 가치가 없습니다. 반대로 데이터를 추가하고 삭제할 때 성능이 저하되며, 특히 자주 업데이트되는 테이블의 경우 부정적인 영향이 더욱 커집니다. . 중복된 값이 많이 포함된 데이터베이스의 필드를 색인화하지 마십시오.

40. MySQL 쿼리 최적화 요약:

느린 쿼리 로그를 사용하여 느린 쿼리를 발견하고, 실행 계획을 사용하여 쿼리가 정상적으로 실행되고 있는지 확인하고, 쿼리가 최적으로 실행되고 있는지 항상 테스트하세요.

성능은 시간이 지나면서 항상 변합니다. 전체 테이블에서 count(*)를 사용하지 마세요. 전체 테이블이 잠길 수 있습니다. 후속 유사한 쿼리가 쿼리 캐시를 사용할 수 있도록 쿼리 일관성을 유지하고, 적절한 상황에서는 GROUP BY를 사용하고 대신 DISTINCT의 경우 WHERE, GROUP BY 및 ORDER BY 절에 인덱스 열을 사용하고 인덱스를 단순하게 유지하며 여러 인덱스에 동일한 열을 포함하지 않습니다.

때때로 MySQL이 잘못된 인덱스를 사용하는 경우가 있습니다. 이 경우 USE INDEX를 사용하여 SQL_MODE=STRICT 사용 문제를 확인하세요. 레코드가 5개 미만인 인덱스 필드의 경우 UNION에서 LIMIT를 사용하면 OR이 아닙니다.

업데이트 전에 SELECT를 피하려면 INSERT ON DUPLICATE KEY 또는 INSERT IGNORE를 사용하세요. UPDATE를 사용하지 말고 MAX를 사용하지 마세요. 인덱스 필드와 ORDER BY 절을 사용하면 M, N이 실제로 쿼리 속도를 늦출 수 있습니다. 드물게 사용하고, 하위 쿼리 대신 WHERE 절에서 UNION을 사용하고, MySQL을 다시 시작하기 전에 데이터가 메모리에 있고 쿼리가 빠른지 확인하기 위해 데이터베이스를 워밍업하는 것을 잊지 마십시오. 오버헤드를 줄이기 위해 다중 연결 대신 영구 연결을 고려하십시오.

서버 부하 사용을 포함한 벤치마크 쿼리 서버 부하가 증가할 때 간단한 쿼리가 다른 쿼리에 영향을 줄 수 있습니다. 개발 환경에서 생성된 미러링된 데이터에서 느리고 문제가 있는 쿼리를 보려면 SHOW PROCESSLIST를 사용하세요. 의심스러운 쿼리.

41. MySQL 백업 프로세스:

보조 복제 서버에서 백업합니다. 데이터 종속성과 외래 키 제약 조건의 불일치를 방지하려면 MySQL을 완전히 중지하고 데이터베이스 파일에서 백업하세요. 바이너리 로그 파일 – 복제가 중단되지 않도록 합니다. 향후 문제를 일으킬 수 있는 데이터 불일치가 발생할 수 있으므로 LVM 스냅샷을 신뢰하지 마십시오. 데이터가 있는 경우 테이블 단위로 데이터를 내보냅니다. 격리된 다른 테이블과 다릅니다. mysqldump를 사용할 때 –opt를 사용하십시오. 백업하기 전에 테이블을 확인하고 최적화하여 가져오기 중에 외래 키 제약 조건을 일시적으로 비활성화하십시오. ; 더 빠른 가져오기를 위해 가져오는 동안 고유성 감지를 일시적으로 비활성화합니다. 각 백업 후 데이터베이스, 테이블 및 인덱스의 크기를 계산하여 데이터 크기 증가를 더 잘 모니터링하고 정기적으로 백업을 수행합니다.

42. 쿼리 버퍼는 공백을 자동으로 처리하지 않습니다. 따라서 SQL 문을 작성할 때 공백 사용을 최소화해야 합니다. 특히 SQL 시작 부분과 끝 부분의 공백은 쿼리 캐시가 자동으로 공백을 가로채지 않기 때문입니다. 시작과 끝).

43. 회원은 mid를 기준으로 테이블을 테이블로 나누어 쉽게 쿼리할 수 있나요? 일반적인 비즈니스 요구 사항에서는 기본적으로 사용자 이름을 쿼리 기반으로 사용합니다. 일반적으로 사용자 이름은 테이블을 분할하는 해시 모듈러스로 사용해야 합니다.

테이블 분할과 관련하여 MySQL의 파티션 기능은 이를 수행하며 코드에 투명합니다. 이를 코드 수준에서 구현하는 것은 불합리해 보입니다.

44. 데이터베이스의 각 테이블에 대한 기본 키로 ID를 설정해야 하며 가장 좋은 것은 INT 유형(UNSIGNED 권장)이며 자동으로 증가되는 AUTO_INCREMENT 플래그를 설정합니다.

45. 모든 저장 프로시저 및 트리거의 시작 부분에 SET NOCOUNT ON을 설정하고 끝 부분에 SET NOCOUNT OFF를 설정합니다. 저장 프로시저 및 트리거의 각 문 후에 클라이언트에 DONE_IN_PROC 메시지를 보낼 필요가 없습니다.

46. MySQL 쿼리는 고속 쿼리 캐시를 활성화할 수 있습니다. 이는 데이터베이스 성능을 향상시키는 효과적인 MySQL 최적화 방법 중 하나입니다. 동일한 쿼리를 여러 번 실행하는 경우 캐시에서 데이터를 가져와 데이터베이스에서 직접 반환하는 것이 훨씬 빠릅니다.

47. EXPLAIN SELECT 쿼리는 보기 효과를 추적하는 데 사용됩니다.

EXPLAIN 키워드를 사용하면 MySQL이 SQL 문을 처리하는 방법을 알 수 있습니다. 이는 쿼리 문이나 테이블 구조의 성능 병목 현상을 분석하는 데 도움이 될 수 있습니다. EXPLAIN 쿼리 결과는 인덱스 기본 키가 사용되는 방법과 데이터 테이블이 검색 및 정렬되는 방법도 알려줍니다.

48. 데이터 행이 하나만 있는 경우 LIMIT 1을 사용하세요.

때때로 테이블을 쿼리할 때 결과가 단 하나라는 것을 이미 알고 있지만 커서를 가져와야 할 수도 있고, 반환 레코드 수를 확인하십시오.

이 경우 LIMIT 1을 추가하면 성능이 향상될 수 있습니다. 이러한 방식으로 MySQL 데이터베이스 엔진은 레코드와 일치하는 다음 데이터 조각을 계속 검색하는 대신 데이터 조각을 찾은 후 검색을 중지합니다.

49. 테이블에 적합한 스토리지 엔진을 선택하세요:

myisam: 이 애플리케이션은 주로 읽기 및 삽입 작업에 중점을 두고 있으며 약간의 업데이트 및 삭제만 수행되며 요구 사항이 높지 않습니다. 트랜잭션 무결성 및 동시성이 높습니다. InnoDB: 동시 조건에서 트랜잭션 처리 및 데이터 일관성이 필요합니다. 삽입 및 쿼리 외에도 많은 업데이트 및 삭제도 포함됩니다. (InnoDB는 삭제 및 업데이트로 인한 잠금을 효과적으로 줄입니다.) 트랜잭션을 지원하는 InnoDB 유형 테이블의 경우 속도에 영향을 미치는 주된 이유는 AUTOCOMMIT의 기본 설정이 켜져 있고 프로그램이 트랜잭션을 시작하기 위해 BEGIN을 명시적으로 호출하지 않아 각 삽입이 자동으로 제출되어 심각한 영향을 미치기 때문입니다. 속도. SQL을 실행하기 전에 start를 호출하면 여러 SQL이 하나의 형태를 이루므로(자동 커밋이 켜져 있는 경우에도) 성능이 크게 향상됩니다. myisam：应用时以读和插入操作为主，只有少量的更新和删除，并且对事务的完整性，并发性要求不是很高的。InnoDB：事务处理，以及并发条件下要求数据的一致性。除了插入和查询外，包括很多的更新和删除。（InnoDB 有效地降低删除和更新导致的锁定）。对于支持事务的 InnoDB类 型的表来说，影响速度的主要原因是 AUTOCOMMIT 默认设置是打开的，而且程序没有显式调用 BEGIN 开始事务，导致每插入一条都自动提交，严重影响了速度。可以在执行 SQL 前调用 begin，多条 SQL 形成一个事物（即使 autocommit 打开也可以），将大大提高性能。

50、优化表的数据类型，选择合适的数据类型：

原则

"지방" 또는 "성별"과 같은 일부 텍스트 필드의 경우 ENUM 유형으로 정의할 수 있습니다. 왜냐하면 MySQL에서는 ENUM 유형이 숫자 데이터로 처리되고, 숫자 데이터는 텍스트 유형보다 훨씬 빠르게 처리되기 때문입니다. 이런 방식으로 데이터베이스의 성능을 향상시킬 수 있습니다.

51. 문자열 데이터 유형: char, varchar, text를 선택하세요.

52. 열에 대한 모든 작업은 데이터베이스 함수, 계산 표현식 등을 포함하는 테이블 스캔으로 이어집니다. 쿼리할 때 작업은 가능한 한 등호 오른쪽으로 이동해야 합니다.

「요약」

이 글에서는 총 52개의 SQL 최적화 전략을 설명하고 있습니다. 10개 이상을 꼽을 수 있다면 면접관은 기본적으로 기다리지 않을 것입니다. 계속 이야기하면 이미 매우 인상적입니다. 이때 면접관의 인상은 크게 좋아집니다.