MySQL은 인덱스를 사용하여 쿼리 최적화를 구현합니다._MySQL

인덱스의 목적은 쿼리 효율성을 향상시키는 것인데, 이는 사전과 비교할 수 있습니다. "mysql"이라는 단어를 찾으려면 반드시 m 문자를 찾은 다음 맨 아래에서 y 문자를 찾아야 합니다. 맨 아래로 이동한 다음 나머지 SQL을 찾으세요. 색인이 없으면 원하는 것을 찾기 위해 모든 단어를 살펴봐야 할 수도 있습니다.

1. 인덱스의 장점

인덱싱되지 않은 세 개의 테이블 t1, t2 및 t3이 있다고 가정합니다. 각 테이블에는 각각 데이터 열 i1, i2 및 i3이 포함되어 있고 각 테이블에는 1부터 1000까지의 일련 번호가 있는 1000개의 데이터 행이 포함되어 있습니다. 특정 값과 일치하는 데이터 행의 조합을 찾는 쿼리는 다음과 같습니다.

SELECT t1.i1, t2.i2, t3.i3
FROM t1, t2, t3
WHERE t1.i1 = t2.i2 AND t2.i1 = t3.i3;

이 쿼리의 결과는 1000개 행이어야 하며 각 데이터 행에는 3개의 동일한 값이 포함됩니다. 인덱스 없이 이 쿼리를 처리하는 경우 모든 테이블을 스캔하지 않고는 어떤 행에 어떤 값이 포함되어 있는지 알 수 없습니다. 따라서 WHERE 조건과 일치하는 레코드를 찾으려면 모든 조합을 시도해야 합니다. 가능한 조합의 수는 1000 x 1000 x 1000(10억!)이며 이는 일치하는 레코드 수의 백만 배입니다. 이것은 많은 작업을 낭비합니다. 이 예에서는 인덱스를 사용하지 않으면 테이블의 레코드가 증가함에 따라 이러한 테이블 간의 조인을 처리하는 데 소요되는 시간이 더 빠르게 증가하여 성능이 저하된다는 것을 보여줍니다. 인덱스를 사용하면 쿼리를 다음과 같이 처리할 수 있으므로 이러한 데이터 테이블을 인덱스하면 속도가 크게 향상될 수 있습니다.

　1． 테이블 t1의 첫 번째 행을 선택하고 해당 데이터 행의 값을 확인합니다.

　2． 테이블 t2의 인덱스를 사용하여 t1의 값과 일치하는 데이터 행을 직접 찾습니다. 마찬가지로 테이블 t3의 인덱스를 사용하여 테이블 t2의 값과 일치하는 데이터 행을 직접 찾습니다.

　3. 테이블 t1의 다음 행을 처리하고 이전 프로세스를 반복합니다. 이 작업은 t1의 모든 데이터 행이 검사될 때까지 수행됩니다.

이 경우에도 테이블 t1에서 전체 스캔을 수행하지만 t2 및 t3에서 인덱스 조회를 수행하여 해당 테이블에서 직접 행을 가져올 수 있습니다. 위의 쿼리를 이런 방식으로 실행하는 것은 이론적으로 백만 배 더 빠릅니다. 물론 이 예는 결론을 도출하기 위해 인위적으로 설정한 예이다. 그러나 이것이 해결하는 문제는 실제이며, 인덱스가 없는 테이블에 인덱스를 추가하면 성능이 크게 향상되는 경우가 많습니다.
-

2. 색인 비용

첫째, 인덱스는 검색 속도를 높이지만 삽입 및 삭제 속도는 물론 인덱스 데이터 열의 값 업데이트 속도도 느려집니다. 즉, 인덱스는 쓰기와 관련된 대부분의 작업 속도를 저하시킵니다. 이러한 현상이 나타나는 이유는 레코드를 쓸 때 데이터 행을 써야 할 뿐만 아니라 모든 인덱스를 변경해야 하기 때문이다. 데이터 테이블에 인덱스가 많을수록 더 많은 수정이 필요하고 평균 성능 저하도 커집니다. 이 문서의 "효율적인 데이터 로드" 섹션에서는 이러한 현상을 자세히 살펴보고 이를 처리하는 방법을 알아봅니다.

둘째, 인덱스는 디스크 공간을 소비하며, 그에 따라 여러 인덱스가 더 많은 디스크 공간을 소비하게 됩니다. 이로 인해 데이터 테이블 크기 제한에 더 빨리 도달할 수 있습니다.

· MyISAM 테이블의 경우 인덱싱을 자주 수행하면 인덱스 파일이 데이터 파일보다 더 빨리 최대 제한에 도달할 수 있습니다.

· 데이터와 인덱스 값을 동일한 파일에 함께 저장하는 BDB 테이블의 경우 인덱스를 추가하면 해당 테이블이 최대 파일 제한에 더 빨리 도달하게 됩니다.

InnoDB의 공유 테이블스페이스에 할당된 모든 테이블은 동일한 공통 공간 풀을 사용하기 위해 경쟁하므로 인덱스를 추가하면 테이블스페이스의 스토리지가 더 빨리 소모됩니다. 그러나 MyISAM 및 BDB 테이블에서 사용되는 파일과 달리 InnoDB 공유 테이블스페이스는 여러 파일을 사용하도록 구성할 수 있으므로 운영 체제의 파일 크기에 제한을 받지 않습니다. 추가 디스크 공간을 사용할 수 있는 한 새 구성 요소를 추가하여 테이블 공간을 확장할 수 있습니다.

별도의 테이블스페이스를 사용하는 InnoDB 테이블은 데이터와 인덱스 값이 하나의 파일에 저장되기 때문에 BDB 테이블과 동일한 제약을 받는다.

이러한 요소의 실제 의미는 다음과 같습니다. 쿼리 실행 속도를 높이기 위해 특수 인덱스가 필요하지 않은 경우 인덱스를 생성하지 마세요.

3. 인덱스 선택

이미 인덱싱 구문을 알고 있다고 가정하지만, 구문은 데이터 테이블을 어떻게 인덱싱해야 하는지 알려주지 않습니다. 이를 위해서는 데이터 테이블이 사용되는 방식을 고려해야 합니다. 이 섹션에서는 인덱싱할 후보 데이터 열을 식별하는 방법과 인덱스를 가장 잘 구축하는 방법을 안내합니다.

검색, 정렬, 그룹화에 사용되는 인덱스 데이터 열은 단지 출력 표시만을 위한 것이 아닙니다. 즉, 인덱싱에 가장 적합한 후보 데이터 열은 WHERE 절, 조인 절, ORDER BY 또는 GROUP BY 절에 나타나는 열입니다. 출력 데이터 열 목록에서 SELECT 키워드 뒤에만 나타나는 데이터 열은 좋은 후보 열이 아닙니다.

SELECT
col_a <- 不是备选列
FROM
tbl1 LEFT JOIN tbl2
ON tbl1.col_b = tbl2.col_c <- 备选列
WHERE
col_d = expr; <- 备选列

물론 표시되는 데이터 열과 WHERE 절에 사용된 데이터 열도 동일할 수 있습니다. 요점은 출력 목록의 데이터 열이 본질적으로 인덱싱에 적합하지 않다는 것입니다.

　　Join子句或WHERE子句中类似col1 = col2形式的表达式中的数据列都是特别好的索引备选列。前面显示的查询中的col_b和col_c就是这样的例子。如果MySQL能够利用联结列来优化查询，它一定会通过减少整表扫描来大幅度减少潜在的表－行组合。

　　考虑数据列的基数（cardinality）。基数是数据列所包含的不同值的数量。例如，某个数据列包含值1、3、7、4、7、3，那么它的基数就是4。索引的基数相对于数据表行数较高（也就是说，列中包含很多不同的值，重复的值很少）的时候，它的工作效果最好。如果某数据列含有很多不同的年龄，索引会很快地分辨数据行。如果某个数据列用于记录性别（只有”M”和”F”两种值），那么索引的用处就不大。如果值出现的几率几乎相等，那么无论搜索哪个值都可能得到一半的数据行。在这些情况下，最好根本不要使用索引，因为查询优化器发现某个值出现在表的数据行中的百分比很高的时候，它一般会忽略索引，进行全表扫描。惯用的百分比界线是”30%”。现在查询优化器更加复杂，把其它一些因素也考虑进去了，因此这个百分比并不是MySQL决定选择使用扫描还是索引的唯一因素。

　　索引较短的值。尽可能地使用较小的数据类型。例如，如果MEDIUMINT足够保存你需要存储的值，就不要使用BIGINT数据列。如果你的值不会长于25个字符，就不要使用CHAR(100)。较小的值通过几个方面改善了索引的处理速度：

　　· 较短的值可以更快地进行比较，因此索引的查找速度更快了。

　　· 较小的值导致较小的索引，需要更少的磁盘I/O。

　　· 使用较短的键值的时候，键缓存中的索引块（block）可以保存更多的键值。MySQL可以在内存中一次保持更多的键，在不需要从磁盘读取额外的索引块的情况下，提高键值定位的可能性。

　　对于InnoDB和BDB等使用聚簇索引（clustered index）的存储引擎来说，保持主键（primary key）短小的优势更突出。聚簇索引中数据行和主键值存储在一起（聚簇在一起）。其它的索引都是次级索引；它们存储主键值和次级索引值。次级索引屈从主键值，它们被用于定位数据行。这暗示主键值都被复制到每个次级索引中，因此如果主键值很长，每个次级索引就需要更多的额外空间。

　　索引字符串值的前缀（prefixe）。如果你需要索引一个字符串数据列，那么最好在任何适当的情况下都应该指定前缀长度。例如，如果有CHAR(200)数据列，如果前面10个或20个字符都不同，就不要索引整个数据列。索引前面10个或20个字符会节省大量的空间，并且可能使你的查询速度更快。通过索引较短的值，你可以获得那些与比较速度和磁盘I/O节省相关的好处。当然你也需要利用常识。仅仅索引某个数据列的第一个字符串可能用处不大，因为如果这样操作，那么在索引中不会有太多的唯一值。

　　你可以索引CHAR、VARCHAR、BINARY、VARBINARY、BLOB和TEXT数据列的前缀。

　　使用最左（leftmost）前缀。建立多列复合索引的时候，你实际上建立了MySQL可以使用的多个索引。复合索引可以作为多个索引使用，因为索引中最左边的列集合都可以用于匹配数据行。这种列集合被称为”最左前缀”（它与索引某个列的前缀不同，那种索引把某个列的前面几个字符作为索引值）。

　　假设你在表的state、city和zip数据列上建立了复合索引。索引中的数据行按照state/city/zip次序排列，因此它们也会自动地按照state/city和state次序排列。这意味着，即使你在查询中只指定了state值，或者指定state和city值，MySQL也可以使用这个索引。因此，这个索引可以被用于搜索如下所示的数据列组合：

state, city, zip
state, city
state

　　MySQL不能利用这个索引来搜索没有包含在最左前缀的内容。例如，如果你按照city或zip来搜索，就不会使用到这个索引。如果你搜索给定的state和具体的ZIP代码（索引的1和3列），该索引也是不能用于这种组合值的，尽管MySQL可以利用索引来查找匹配的state从而缩小搜索的范围。

　　不要过多地索引。不要认为”索引越多，性能越高”，不要对每个数据列都进行索引。我们在前面提到过，每个额外的索引都会花费更多的磁盘空间，并降低写操作的性能。当你修改表的内容的时候，索引就必须被更新，甚至可能重新整理。如果你的索引很少使用或永不使用，你就没有必要减小表的修改操作的速度。此外，为检索操作生成执行计划的时候，MySQL会考虑索引。建立额外的索引会给查询优化器增加更多的工作量。如果索引太多，有可能（未必）出现MySQL选择最优索引失败的情况。维护自己必须的索引可以帮助查询优化器来避免这类错误。

　　如果你考虑给已经索引过的表添加索引，那么就要考虑你将增加的索引是否是已有的多列索引的最左前缀。如果是这样的，不用增加索引，因为已经有了（例如，如果你在state、city和zip上建立了索引，那么没有必要再增加state的索引）。

　　让索引类型与你所执行的比较的类型相匹配。在你建立索引的时候，大多数存储引擎会选择它们将使用的索引实现。例如，InnoDB通常使用B树索引。MySQL也使用B树索引，它只在三维数据类型上使用R树索引。但是，MEMORY存储引擎支持散列索引和B树索引，并允许你选择使用哪种索引。为了选择索引类型，需要考虑在索引数据列上将执行的比较操作类型：

　　· 对于散列（hash）索引，会在每个数据列值上应用散列函数。生成的结果散列值存储在索引中，并用于执行查询。散列函数实现的算法类似于为不同的输入值生成不同的散列值。使用散列值的好处是散列值比原始值的比较效率更高。散列索引用于执行=或<=>操作等精确匹配的时候速度非常快。但是对于查询一个值的范围效果就非常差了：

id < 30
weight BETWEEN 100 AND 150

　　· B树索引可以用于高效率地执行精确的或者基于范围（使用操作<、<=、=、>=、>、<>、!=和BETWEEN）的比较。B树索引也可以用于LIKE模式匹配，前提是该模式以文字串而不是通配符开头。

　　如果你使用的MEMORY数据表只进行精确值查询，散列索引是很好的选择。这是MEMORY表使用的默认的索引类型，因此你不需要特意指定。如果你希望在MEMORY表上执行基于范围的比较，应该使用B树索引。为了指定这种索引类型，需要给索引定义添加USING BTREE。例如：

CREATE TABLE lookup
(
id INT NOT NULL,
name CHAR(20),
PRIMARY KEY USING BTREE (id)
) ENGINE = MEMORY;

　　如果你希望执行的语句的类型允许，单个MEMORY表可以同时拥有散列索引和B树索引，即使在同一个数据列上。

　　有些类型的比较不能使用索引。如果你只是通过把值传递到函数（例如STRCMP()）中来执行比较操作，那么对它进行索引就没有价值。服务器必须计算出每个数据行的函数值，它会排除数据列上索引的使用。

　　使用慢查询（slow-query）日志来识别执行情况较差的查询。这个日志可以帮助你找出从索引中受益的查询。你可以直接查看日志（它是文本文件），或者使用mysqldumpslow工具来统计它的内容。如果某个给定的查询多次出现在”慢查询”日志中，这就是一个线索，某个查询可能没有优化编写。你可以重新编写它，使它运行得更快。你要记住，在评估”慢查询”日志的时候，”慢”是根据实际时间测定的，在负载较大的服务器上”慢查询”日志中出现的查询会多一些。

*4.建索引的几大原则*

4.1.最左前缀匹配原则，非常重要的原则，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的顺序可以任意调整。

4.2.=和in可以乱序，比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序，mysql的查询优化器会帮你优化成索引可以识别的形式

4.3.尽量选择区分度高的列作为索引,区分度的公式是count(distinct col)/count(*)，表示字段不重复的比例，比例越大我们扫描的记录数越少，唯一键的区分度是1，而一些状态、性别字段可能在大数据面前区分度就是0，那可能有人会问，这个比例有什么经验值吗？使用场景不同，这个值也很难确定，一般需要join的字段我们都要求是0.1以上，即平均1条扫描10条记录

4.4. 인덱스 열은 계산에 참여할 수 없습니다. 예를 들어 from_unixtime(create_time) = '2014-05-29'인 경우 인덱스를 사용할 수 없는 이유는 매우 간단합니다. b+ 트리에서는 데이터 테이블의 필드 값에 있지만 검색할 때 비교할 모든 요소에 함수를 적용해야 하는데 이는 비용이 너무 많이 듭니다. 따라서 명령문은 create_time = unix_timestamp('2014-05-29');

로 작성되어야 합니다.

4.5. 인덱스를 최대한 확장하고 새 인덱스를 생성하지 마세요. 예를 들어, 테이블에 이미 a라는 인덱스가 있고 이제 (a, b)라는 인덱스를 추가하려는 경우 원래 인덱스만 수정하면 됩니다.

위 내용은 쿼리 최적화를 위한 MySQL의 인덱스 사용에 대한 편집자의 소개입니다. 질문이 있는 경우 메시지를 남겨주시면 편집자가 제 시간에 답변해 드리겠습니다. 홈페이지에 대한 귀하의 지원에 진심으로 감사드립니다!