MySQL 인덱스-모범 사례는 무엇입니까?

MySQL 인덱스-모범 사례는 무엇입니까?

---

나는 지금까지 MySQL 데이터베이스에서 인덱스를 사용해 왔지만 그것에 대해 제대로 배우지 못했습니다 . 일반적으로 WHERE절을 사용하여 검색하거나 선택할 필드에 색인을 넣지 만 때로는 흑백으로 보이지 않는 경우가 있습니다.

MySQL 인덱스에 대한 모범 사례는 무엇입니까?

상황 / 딜레마 예 :

테이블에 6 개의 열이 있고 모두 열을 검색 할 수 있으면 열을 모두 색인화해야합니까 아니면 전혀 색인화하지 않아야합니까?

.

인덱싱의 부정적인 성능 영향은 무엇입니까?

.

내 사이트의 일부에서 검색 할 수있는 VARCHAR 2500 열이있는 경우 색인을 작성해야합니까?

---

인덱싱에 대해 읽는 데 시간을 투자해야하며 그것에 대해 많은 글이 있으며, 무슨 일이 일어나고 있는지 이해하는 것이 중요합니다.

대체로 인덱스는 테이블의 행에 순서를 부과합니다.

간단히하기 위해 테이블이 큰 CSV 파일이라고 가정 해보십시오. 행이 삽입 될 때마다 끝에 삽입 됩니다 . 따라서 테이블의 "자연스러운"순서는 행이 삽입 된 순서입니다.

매우 기본적인 스프레드 시트 응용 프로그램에 CSV 파일이로드되었다고 가정합니다. 이 스프레드 시트는 데이터를 표시하고 행에 순차적으로 번호를 매 깁니다.

이제 세 번째 열에서 "M"값을 가진 모든 행을 찾아야한다고 상상해보십시오. 사용 가능한 것을 감안할 때 하나의 옵션 만 있습니다. 각 행의 세 번째 열 값을 확인하여 테이블을 스캔합니다. 많은 행이있는 경우이 방법 ( "테이블 스캔")에 시간이 오래 걸릴 수 있습니다!

이제이 표 외에 색인이 있다고 가정하십시오. 이 특정 인덱스는 세 번째 열의 값 인덱스입니다. 색인은 세 번째 열의 모든 값을 의미있는 순서 (알파벳순)로 나열하고 각각에 대해 해당 값이 나타나는 행 번호 목록을 제공합니다.

이제 세 번째 열의 값이 "M"인 모든 행을 찾는 좋은 전략이 있습니다. 예를 들어, 이진 검색을 수행 할 수 있습니다 ! 테이블 스캔에서는 N 개의 행을보아야하지만 (여기서 N은 행 수임) 바이너리 검색에서는 최악의 경우 log-n 인덱스 항목 만보아야합니다. 와우, 훨씬 쉬워요!

물론이 인덱스가 있고 테이블에 행을 추가하는 경우 (결국 개념 테이블이 작동하는 방식이므로) 인덱스를 매번 업데이트해야합니다. 따라서 새로운 행을 작성하는 동안 약간의 작업을 수행하지만 무언가를 검색 할 때 많은 시간을 절약 할 수 있습니다.

따라서 일반적으로 인덱싱은 읽기 효율성과 쓰기 효율성 간의 균형을 유지합니다. 인덱스가 없으면 삽입 속도가 매우 빠를 수 있습니다. 데이터베이스 엔진은 테이블에 행을 추가하기 만합니다. 색인을 추가 할 때 엔진은 삽입을 수행하는 동안 각 색인을 업데이트해야합니다.

반면에 읽기는 훨씬 빨라집니다.

바라건대 첫 두 질문 (다른 사람들이 대답했듯이 올바른 균형을 찾아야 함)을 다루기를 바랍니다.

세 번째 시나리오는 조금 더 복잡합니다. LIKE를 사용하는 경우 인덱싱 엔진은 일반적으로 첫 번째 "%"까지 읽기 속도를 도와줍니다. 즉, 'foo % bar %'처럼 WHERE 열을 선택하는 경우 데이터베이스는 인덱스를 사용하여 열이 "foo"로 시작하는 모든 행을 찾은 다음 해당 하위 행 세트를 스캔하여 서브 세트를 찾습니다. "바"를 포함합니다. SELECT ... WHERE 열 LIKE '% bar %'은 (는) 인덱스를 사용할 수 없습니다. 왜 그런지 알 수 있기를 바랍니다.

마지막으로 두 개 이상의 열에서 인덱스에 대해 생각해야합니다. 개념은 동일하며 LIKE와 유사하게 작동합니다. 기본적으로 (a, b, c)에 색인이 있으면 엔진은 가능한 한 왼쪽에서 오른쪽으로 색인을 계속 사용합니다. 따라서 열 a에서 검색 할 때 (a, b)에서와 같이 (a, b, c) 색인을 사용할 수 있습니다. 그러나 b = 5 AND c = 1 인 곳을 검색하는 경우 엔진에서 전체 테이블 스캔을 수행해야합니다.

이 방법이 약간 도움이 되길 바랍니다. 그러나 이러한 내용을 자세히 설명하는 좋은 기사를 찾기 위해 몇 시간을 투자하는 것이 가장 좋습니다. 특정 데이터베이스 서버의 설명서를 읽는 것도 좋습니다. 쿼리 플래너가 인덱스를 구현하고 사용하는 방법은 매우 다양 할 수 있습니다.

---

인덱싱 기술 더 마스터 링 과 같은 프레젠테이션을 확인하십시오 .

2012 년 12 월 12 일 업데이트 : 인덱스를 디자인하는 방법 에 대한 새로운 프레젠테이션을 게시했습니다 . 2012 년 10 월 산타 클라라의 젠드 콘 (ZendCon)과 2012 년 12 월 퍼 코나 라이브 런던 (Percona Live London)에서 이것을 발표했습니다.

최상의 인덱스를 디자인하는 것은 앱에서 실행하는 쿼리와 일치해야하는 프로세스입니다.

인덱싱하기에 가장 적합한 열 또는 모든 열을 인덱싱해야하는지, 열이 없는지, 여러 열에 걸쳐있는 인덱스 등의 일반 규칙은 권장하기가 어렵습니다. 실행해야하는 쿼리에 따라 다릅니다.

예, 약간의 오버 헤드가 있으므로 불필요하게 인덱스를 생성하지 않아야합니다. 하지만 당신은 해야 빠르게 실행하는 데 필요한 쿼리에 혜택을주는 인덱스를 만들 수 있습니다. 지수의 오버 헤드는 일반적으로 그 이점보다 훨씬 중요합니다.

VARCHAR (2500) 인 열의 경우 FULLTEXT 색인 또는 접 두부 색인 을 사용하려고합니다 .

CREATE INDEX i ON SomeTable(longVarchar(100));

긴 varchar의 중간에있을 수있는 단어를 검색하는 경우 기존 색인이 도움이되지 않습니다. 이를 위해 전체 텍스트 색인을 사용하십시오.

---

다른 답변에서 좋은 조언을 반복하지는 않지만 다음을 추가합니다.

복합 지수

복합 인덱스 (여러 열을 포함하는 인덱스)를 만들 수 있습니다. MySQL은 이것을 왼쪽 에서 오른쪽으로 사용할 수 있습니다 . 당신이 가지고 있다면 :

Table A
Id
Name
Category
Age
Description

if you have a compound index that includes Name/Category/Age in that order, these WHERE clauses would use the index:

WHERE Name='Eric' and Category='A'

WHERE Name='Eric' and Category='A' and Age > 18

but

WHERE Category='A' and Age > 18

would not use that index because everything has to be used from left to right.

Explain

Use Explain / Explain Extended to understand what indices are available to MySQL and which one it actually selects. MySQL will only use ONE key per query.

EXPLAIN EXTENDED SELECT * from Table WHERE Something='ABC'

Slow Query Log

Turn on the slow query log to see which queries are running slow.

Wide Columns

If you have a wide column where MOST of the distinction happens in the first several characters, you can use only the first N characters in your index. Example: We have a ReferenceNumber column defined as varchar(255) but 97% of the cases, the reference number is 10 characters or less. I changed the index to only look at the first 10 characters and improved performance quite a bit.

---

If a table has six columns and all of them are searchable, should i index all of them or none of them

Are you searching on a field by field basis or are some searches using multiple fields? Which fields are most being searched on? What are the field types? (Index works better on INTs than on VARCHARs for example) Have you tried using EXPLAIN on the queries that are being run?

What are the negetive performance impacts of indexing

UPDATEs and INSERTs will be slower. There's also the extra storage space requirments, but that's usual unimportant these days.

If i have a VARCHAR 2500 column which is searchable from parts of my site, should i index it

No, unless it's UNIQUE (which means it's already indexed) or you only search for exact matches on that field (not using LIKE or mySQL's fulltext search).

Generally I put an index on any fields that i will be searching or selecting using a WHERE clause

I'd normally index the fields that are the most queried, and then INTs/BOOLEANs/ENUMs rather that fields that are VARCHARS. Don't forget, often you need to create an index on combined fields, rather than an index on an individual field. Use EXPLAIN, and check the slow log.

---

Load Data Efficiently: Indexes speed up retrievals but slow down inserts and deletes, as well as updates of values in indexed columns. That is, indexes slow down most operations that involve writing. This occurs because writing a row requires writing not only the data row, it requires changes to any indexes as well. The more indexes a table has, the more changes need to be made, and the greater the average performance degradation. Most tables receive many reads and few writes, but for a table with a high percentage of writes, the cost of index updating might be significant.

Avoid Indexes: If you don’t need a particular index to help queries perform better, don’t create it.

Disk Space: An index takes up disk space, and multiple indexes take up correspondingly more space. This might cause you to reach a table size limit more quickly than if there are no indexes. Avoid indexes wherever possible.

Takeaway: Don't over index

---

In general, indices help speedup database search, having the disadvantage of using extra disk space and slowing INSERT / UPDATE / DELETE queries. Use EXPLAIN and read the results to find out when MySQL uses your indices.

If a table has six columns and all of them are searchable, should i index all of them or none of them?

Indexing all six columns isn't always the best practice.

(a) Are you going to use any of those columns when searching for specific information?

(b) What is the selectivity of those columns (how many distinct values are there stored, in comparison to the total amount of records on the table)?

MySQL uses a cost-based optimizer, which tries to find the "cheapest" path when performing a query. And fields with low selectivity aren't good candidates.

What are the negetive performance impacts of indexing?

Already answered: extra disk space, lower performance during insert - update - delete.

If i have a VARCHAR 2500 column which is searchable from parts of my site, should i index it?

Try the FULLTEXT Index.

---

1/2) Indexes speed up certain select operations but they slow down other operations like insert, update and deletes. It can be a fine balance.

3) use a full text index or perhaps sphinx

참고URL : https://stackoverflow.com/questions/3049283/mysql-indexes-what-are-the-best-practices