MySQL indeksleri nelerdir? Nasıl optimize edilir?
İndeksleme, bir üniversite kütüphanesinde bir bibliyografik indeks oluşturmaya benzer, bu da veri alma verimliliğini artırabilir ve veritabanının IO maliyetini azaltabilir. MySQL'in performansı yaklaşık 3 milyon kayıttan kademeli olarak düşmeye başladı.Resmi belge 500 ~ 800w kayıt belirtmesine rağmen, büyük miktarda veriyi indekslemek çok gerekli. MySQL, SQL yürütme hakkında ayrıntılı bilgileri görüntülemek için kullanılan ve dizinleri optimize edebilen Explain'i sağlar.
1. SQL yürütmenin yavaş olmasının nedenleri
1. Donanım sorunu. Yavaş ağ hızı, yetersiz bellek, düşük G / Ç verimi, tam disk alanı vb.
2. Dizin veya dizin hatası yok. (Genellikle bir İnternet şirketinde DBA, gecenin ortasında masayı kilitler ve dizini yeniden oluşturur, çünkü bir veri parçasını sildiğinizde, dizin ağacı yapısı eksiktir. Bu nedenle İnternet şirketinin verileri yanlışlıkla silinir. Biri veri analizi yapmak, diğeri ise dizini yok etmemek)
3. Çok fazla veri (alt veritabanı ve alt tablo)
4. Sunucu ayarı ve çeşitli parametre ayarları (my.cnf'yi ayarlayın)
2. Sebepleri incelerken giriş noktasını bulmalıyız
1. Önce gözlemleyin, yavaş sorgu günlüğünü açın, ilgili eşiği ayarlayın (örneğin, 3 saniyeden fazla ise yavaş SQL) ve üretim ortamında bir gün sonra hangi SQL'in yavaş olduğunu görün.
2. SQL analizini açıklayın ve yavaşlatın. Örneğin, SQL ifadeleri kötü yazılmış, dizinler eksik veya geçersiz, çok fazla ilgili sorgu (bazen tasarım kusurları veya kabul edilemez gereksinimler nedeniyle) vb.
3. Profili Göster, yürütme ayrıntılarına Açıklamadan bir adım daha yakındır, her SQL'in ne yaptığını ve kaç saniye sürdüğünü sorgulayabilirsiniz.
4. MySQL sunucusunun parametrelerini optimize etmek için bir DBA veya işlem ve bakım bulun.
3. İndeks nedir?
MySQL'in resmi dizin tanımı şudur: Dizin, MySQL'in verimli bir şekilde veri elde etmesine yardımcı olan bir veri yapısıdır. Bunu basitçe şu şekilde anlayabiliriz: sıralı bir veri yapısını hızlıca bulun. Mysql indeksinin iki ana yapısı vardır: B + Ağaç indeksi ve Hash indeksi. Genellikle bahsettiğimiz indeks, belirtilmediği takdirde, genellikle B-ağaç yapısı (B + Ağaç indeksi) tarafından düzenlenen indeksi ifade eder. Dizin gösterildiği gibidir:
En dıştaki açık mavi disk bloğu 1'de veri 17, 35 (koyu mavi) ve işaretçiler P1, P2, P3 (sarı) vardır. P1 göstergesi, 17'den küçük disk bloklarını, P2'nin 17-35 arasında olduğunu ve P3, 35'ten büyük disk bloklarını gösterir. Gerçek veriler kotiledon düğümünde bulunur, bu alt katman 3, 5, 9, 10, 13 ... Yaprak olmayan düğümler gerçek verileri depolamaz, yalnızca 17, 35 gibi arama yönünü yönlendiren veri öğelerini depolar.
Arama işlemi: Örneğin, 28 veri öğesini aramak için, önce disk bloğu 1'i belleğe yükleyin, bir G / Ç meydana gelir ve P2 işaretçisini belirlemek için ikili aramayı kullanın. Daha sonra 28'in 26 ile 30 arasında olduğu ve disk bloğu 3'ün P2 işaretçisinin adresi aracılığıyla belleğe yüklendiği ve ikinci G / Ç'nin gerçekleştiği bulundu. Disk bloğu 8'i aynı şekilde bulun ve üçüncü G / Ç meydana gelir.
Gerçek durum, üstteki üç katmanın B + Ağacı'nın milyonlarca veriyi temsil edebilmesi ve milyonlarca verinin milyonlarca G / Ç yerine yalnızca üç G / Ç'ye sahip olmasıdır. Zaman iyileştirmesi çok büyük.
Dört, analizi açıklayın
Önceki makaleyi bitirdikten sonra, pratik kısmı girin, önce test için gereken verileri girin:
TABLO OLUŞTUR "user_info" (
`id` BIGINT (20) NULL DEĞİL AUTO_INCREMENT,
`isim` VARCHAR (50) BOŞ VARSAYILAN DEĞİL '',
`age` INT (11) VARSAYILAN NULL,
BİRİNCİL ANAHTAR ("kimlik"),
ANAHTAR "isim_dizin" ("isim")
) MOTOR = InnoDB VARSAYILAN KARAKTER = utf8;
INSERT INTO user_info (ad, yaş) VALUES ('xys', 20);
INSERT INTO user_info (ad, yaş) VALUES ('a', 21);
INSERT INTO user_info (ad, yaş) VALUES ('b', 23);
INSERT INTO user_info (ad, yaş) VALUES ('c', 50);
INSERT INTO user_info (ad, yaş) VALUES ('d', 15);
INSERT INTO user_info (ad, yaş) VALUES ('e', 20);
INSERT INTO user_info (ad, yaş) VALUES ('f', 21);
INSERT INTO user_info (ad, yaş) VALUES ('g', 23);
INSERT INTO user_info (ad, yaş) VALUES ('h', 50);
INSERT INTO user_info (ad, yaş) VALUES ('i', 15);
TABLO OLUŞTUR "order_info" (
`id` BIGINT (20) NULL DEĞİL AUTO_INCREMENT,
`user_id` BIGINT (20) VARSAYILAN NULL,
`product_name` VARCHAR (50) BOŞ VARSAYILAN DEĞİL '',
`üretici` VARCHAR (30) VARSAYILAN NULL,
BİRİNCİL ANAHTAR ("kimlik"),
KEY `user_product_detail_index` (" user_id`, "product_name`," üretici`)
) MOTOR = InnoDB VARSAYILAN KARAKTER = utf8;
INSERT INTO order_info (kullanıcı_kimliği, ürün_adı, üretici) DEĞERLER (1, 'p1', 'WHH');
INSERT INTO order_info (kullanıcı_kimliği, ürün_adı, üretici) VALUES (1, 'p2', 'WL');
INSERT INTO order_info (kullanıcı_kimliği, ürün_adı, üretici) DEĞERLER (1, 'p1', 'DX');
INSERT INTO order_info (kullanıcı_kimliği, ürün_adı, üretici) DEĞERLER (2, 'p1', 'WHH');
INSERT INTO order_info (kullanıcı_kimliği, ürün_adı, üretici) DEĞERLER (2, 'p5', 'WL');
INSERT INTO order_info (kullanıcı_kimliği, ürün_adı, üretici) VALUES (3, 'p3', 'MA');
INSERT INTO order_info (kullanıcı_kimliği, ürün_adı, üretici) DEĞERLER (4, 'p1', 'WHH');
INSERT INTO order_info (kullanıcı_kimliği, ürün_adı, üretici) DEĞERLER (6, 'p1', 'WHH');
INSERT INTO order_info (kullanıcı_kimliği, ürün_adı, üretici) VALUES (9, 'p8', 'TE');
İlk deneyim, uygulamanın etkisi Açıklayın:
İndeks kullanımı, olası anahtarlar, anahtar ve key_len'den oluşan üç sütun içindedir.Sonra, soldan sağa açıklayacağız.
1.id
--id aynı, yürütme sırası yukarıdan aşağıya doğru
user_info'dan u. *, o. * seçimini açıklayın u, order_info o u.id = o.user_id;
--id farklıdır, değer ne kadar büyükse, ilk çalıştırılacak
user_info'dan * seçimini açıklayın, burada id = (order_info'dan user_id'yi seçin, burada product_name = 'p8');
2.select_type
İd'nin uygulanmasını görebilirsiniz, aşağıdaki türlerin toplamı vardır:
BASİT: Bu sorgunun UNION sorguları veya alt sorgular içermediğini gösterir
BİRİNCİL: Bu sorgunun en dıştaki sorgu olduğunu gösterir
SUBQUERY: alt sorgudaki ilk SELECT
BİRLİK: Bu sorgunun, UNION'un ikinci veya sonraki sorgusu olduğunu gösterir
BAĞIMLI BİRLİK: Dış sorguya bağlı olarak UNION'daki ikinci veya sonraki sorgu ifadesi
UNION RESULT, UNION sonucu
BAĞIMLI ALT Sorgu: Alt sorgudaki ilk SEÇİM dış sorguya, yani alt sorgu dış sorgunun sonucuna bağlıdır.
TÜREV: Türev, türetilmiş tablonun SELECT'i temsil eden (FROM yan tümcesinin alt sorgusu)
3. masa
tablo, sorguya dahil olan tabloyu veya türetilmiş tabloyu gösterir:
açıklamak tt. * from (user_info u'dan u. * seçin, order_info o u.id = o.user_id ve u.id = 1) tt
kimlik 1 < türetilmiş2 > Gösterim, id 2'ye sahip u ve o tablolarından elde edilir.
4. tür
Tür alanı daha önemlidir ve sorgunun verimli olup olmadığına karar vermek için önemli bir temel sağlar. Tür alanı aracılığıyla, bu sorgunun tam tablo taraması mı yoksa dizin taraması mı olduğuna karar veriyoruz.
Yaygın olarak kullanılan tür değerleri şunlardır:
system: Tabloda sadece bir parça veri vardır Bu tip özel bir const tipidir.
const: Birincil anahtarın veya dizinin eşdeğer sorgu taraması için en fazla yalnızca bir veri satırı döndürülür. Const sorgusu çok hızlıdır çünkü yalnızca bir kez okunması gerekir. Örneğin, aşağıdaki sorgu bir birincil anahtar dizini kullanır, bu nedenle tür, const türündedir: açıkla select * from user_info nerede id = 2;
eq_ref: Bu tür genellikle birden çok tablonun birleştirme sorgularında görünür; bu, önceki tablonun her sonucunun, sonraki tablonun sonucunun yalnızca bir satırıyla eşleşebileceği anlamına gelir. Ve sorgunun karşılaştırma işlemi genellikle =, sorgu verimliliği daha yüksektir. Örneğin: user_info'dan * seçimini açıklayın, order_info burada user_info.id = order_info.user_id;
ref: Bu tür genellikle birden çok tablonun birleştirme sorgularında, birincil olmayan veya birincil olmayan anahtar dizinleri veya en soldaki önek kural dizinini kullanan sorgular için görünür. Örneğin, aşağıdaki örnekte, bir ref türü sorgusu kullanılmaktadır: açıkla kullanıcı_bilgisinden seçim *, sipariş_bilgisi, burada user_info.id = order_info.user_id VE order_info.user_id = 5
Aralık: Dizin alanı aralığı aracılığıyla tablodaki bazı veri kayıtlarını elde etmek için dizin aralığı sorgusunun kullanılacağını belirtir. Bu tür genellikle =, < > , > , > =, < , < =, NULL, < = > , ARASI, IN () işlemi. Örneğin, aşağıdaki örnek bir aralık sorgusudur: açıkla user_info'dan * seçimini yapın; burada id 2 ile 8 arasındadır;
dizin: ALL türünün bir tam tablo taraması olması dışında ALL türüne benzer bir tam dizin taramasını (tam dizin taraması) temsil eder, ancak dizin türü yalnızca verileri taramadan tüm dizinleri tarar. İndeks türü genellikle şurada görünür: sorgulanacak veriler, veriler taranmadan doğrudan indeks ağacında elde edilebilir. Böyle bir durumda Ekstra alanında İndeks kullanımı gösterilecektir.
TÜMÜ: Tam bir tablo taramasını temsil eder. Bu tür sorgu, en kötü performans gösteren sorgulardan biridir. Genel olarak konuşursak, sorgumuz TÜM sorgu türünde görünmemelidir, çünkü böyle bir sorgu, veri miktarı büyük olduğunda veritabanının performansı için büyük bir felakettir. Bir sorgu TÜM tipi bir sorguysa, ilgili alana bir dizin eklenerek genellikle önlenebilir.
Genel olarak konuşursak, farklı türlerin performans ilişkileri aşağıdaki gibidir:
HERŞEY < indeks < aralık ~ index_merge < ref < eq_ref < sabit < sistemi
ALL türü bir tam tablo taraması olduğundan, aynı sorgu koşulları altında en yavaş olanıdır. Dizin türü sorgusu tam bir tablo taraması olmasa da, tüm dizinleri tarar, bu nedenle ALL türünden biraz daha hızlıdır. İkinci türler verileri sorgulamak için dizinleri kullanır, böylece verilerin bir kısmını veya çoğunu filtreleyebilirsiniz. Bu nedenle, sorgu verimliliği nispeten yüksektir.
5.possible_keys
Mysql'in sorgulama sırasında kullanabileceği dizini temsil eder. Olası anahtarlarda bazı dizinler görünse bile, bu dizinin gerçekten mysql tarafından kullanılacağı anlamına gelmediğini unutmayın. Mysql'in sorgulama sırasında hangi dizinleri kullanacağı anahtar alanına göre belirlenir.
6. anahtar
Bu alan, mysql tarafından mevcut sorguda gerçekte kullanılan dizindir. Örneğin, bir akşam yemeği için, olası anahtarlar kaç kişinin alınması gerektiğidir ve anahtar aslında kaç kişinin olduğudur. Bir dizin oluşturmadığımızda:
o.product_name = 'p1' ve o.productor = 'whh' order_info o.
order_info (üretici) üzerinde idx_name_productor indeksi oluşturun;
order_info üzerine idx_name_productor indeksini bırak;
Bileşik bir dizin oluşturduktan sonra sorgu:
7. key_len
Sorgu iyileştirici tarafından kullanılan dizinin bayt sayısını gösterir Bu alan, birleşik dizinin tamamen kullanılıp kullanılmadığını değerlendirebilir.
8. ref
Bu, mümkünse dizinin hangi sütununun kullanıldığını gösterir, sabittir. Yukarıda belirtilen type özniteliğinde de ref var, aradaki farka dikkat edin.
9. sıralar
Satırlar da önemli bir alandır.Mysql sorgu optimize edici, istatistiksel bilgilere dayanarak sonuç kümesini bulmak için SQL'in taraması ve okuması gereken veri satırı sayısını tahmin eder.Bu değer, SQL'in verimliliğini çok sezgisel olarak gösterir. Prensipte, ne kadar az satır olursa o kadar iyidir. Anahtardaki örneği karşılaştırabilirsiniz, birinin endekslenmiş parası yoktur, satırlar 9'dur ve indekslemeden sonra satırlar 4'tür.
10. ek
Açıklamadaki birçok ekstra bilgi, ekstra alanda görüntülenecektir, yaygın olanlar şunlardır:
filesort kullanma: mysql'nin ek sıralama işlemleri gerektirdiğini ve sıralama etkisinin dizin sırasına göre elde edilemeyeceğini belirtir. Genellikle, bu tür sorgular büyük miktarda cpu kaynağı tükettiğinden, dosya sıralaması kullanmanın optimize edilmesi ve kaldırılması önerilir.
İndeks kullanma: Kapsam indeks taraması; bu, sorgunun, genellikle iyi performansı gösteren tablo veri dosyasını taramadan indeks ağacında gerekli verileri bulabileceği anlamına gelir.
geçici kullanma: Sorguda genellikle sıralama, gruplama ve çoklu tablo birleştirme durumlarında oluşan geçici tablolar kullanılır.Sorgu verimliliği yüksek değildir ve optimizasyon önerilir.
kullanma nerede: Tablo adı, nerede filtrelemeyi kullanır.
Beş, optimizasyon örneği
user_info'dan u. *, o. * seçimini açıklayın u LEFT JOIN order_info o u.id = o.user_id;
Yürütme sonucu, türün TÜMÜ var ve dizini yok:
Optimize etmeye başlayın, ilişkili sütunda bir dizin oluşturun ve açık bir şekilde tür sütununun TÜMÜ'nün ref olduğunu ve dizinin kullanıldığını ve satırların 9 satırı taramaktan 1 satıra değiştiğini görün:
Burada genel bir kural vardır: sol bağlantı dizini sağ tabloya ve sağ bağlantı dizini sol tabloya eklenir.
6. Bir dizin oluşturmam gerekiyor mu?
Dizin sorgu hızını çok verimli bir şekilde artırabilse de, tablonun güncellenme hızını da azaltacaktır. Aslında, dizin aynı zamanda birincil anahtar ve dizin alanlarını depolayan ve varlık tablosunun kayıtlarına işaret eden bir tablodur, bu nedenle dizin sütunu da yer kaplar.
