g u t x .com.tr İpek yolu - Çin'i anlamaya götürürüm

Mysql optimizasyon ipuçları

Veri hacmi ve gereksinimler

Veri miktarı:
Tek bir tabloda 10 milyondan fazla kayıt
İddia:
Tek bir SQL sorgu süresi 1 saniyeyi geçmez

Optimizasyon becerileri

Önce her deneyimi buraya kaydedin ve ardından deneyleri tek tek doğrulamak için yapın.

Toplam veri sayısını sorgularken, toplam sayım için count (*) yerine max (id) kullanın.

Tabii ki, öncül, kimliğin 1'den kendiliğinden artması ve hiçbir satırın silinmemiş olmasıdır.

Sık kullanılan sorgu alanları için dizinler oluşturun.

Endekslemenin hız avantajı açıktır. Dizin oluşturulmadığında, tam tablo sorgusu doğrusaldır.

Tam tablonun alınmasını önlemek için limit kullanın.

Bazı sorgular yalnızca bir sonuç olacağını bilir, sınır 1'i kullanın. Sorgu sonuçlarının sayfalarda görüntülenmesi gerekiyorsa, limit ve sorgulamayı birden çok kez kullanmak isteyebilirsiniz.

Limit ofseti büyük olduğunda, sınırlamak için önce indeksi kullanın

Sınır, 5000000,1 kullanıcı sınırından * seç gibi büyük olduğunda, aramadan önce geçişin yarısına eşdeğer olan ve fiili duruma göre optimize edilmesi gereken 5 milyonluk bir ofset gerçekleştirilecektir.

Doğru veri türünü kullanın

Örneğin, telefonu genellikle char (11) olarak saklıyoruz, bu yüzden sorgulama yaparken numara yerine dize türü kullanmamız gerekiyor. (Mysql ondan kaçacak olsa da, bu yine de sorgu süresini artıracaktır)

Dizine eklenmemiş sorgu koşulları için, en sona çoğu kaydı filtreleyebilen nerede koşulunu koyun.

Telefon = '10000000' ve create_time = '2018-11-0503:22:56' her ikisi de sorgu koşuluysa ve telefon = '10000000' daha fazla kaydı filtreleyebiliyorsa, bunları en sağa yazın.

create_time = '2018-11-0503:22:56' ve telefon = '10000000' olan kullanıcılardan * seçin;

Aynı alandaki koşullar olduğunda, veya yerine in kullanın

Verimliliği veya O (n) 'ye yakın iken, girişin verimliliği O (Log n)

Deney hazırlığı

mysql sürümü: 5.7.23

mysql sürümü

Bir masa oluşturun

Çok yaygın bir kullanıcılar tablosu oluşturun

`Homestead`.`users` (` id` INT (11) NOT NULL AUTO_INCREMENT, `name` VARCHAR (45) NOT NULL DEFAULT'Username ',` phone` CHAR (11) NOT NULL, `status` TINYINT (1) NULL DEFAULT '0', "create_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, BİRİNCİL ANAHTAR ("id");

Test verilerini girin

Hızı sezgisel olarak hissetmek için önce bir saklı yordam yazın ve tabloya 10 milyon (on milyon) kayıt ekleyin.

CREATE DEFINER = "homestead" @ "%" PROCEDURE "create_user` () BEGINDECLARE i INT; İŞLEMİ BAŞLAT; SET i = 1; BEN < = 10000000 DO INSERT INTO "homestead`.`users` (" isim`, "telefon`) DEĞERLER ('test kullanıcısı', i); SET i = i + 1; KIRKEN SON; COMMIT; SON

Create_user (); çağrısı ile saklı yordamı çağırmak makinede iki dakikadan fazla sürdü.

Depolanan yordamı yürüt

deney prosedürü

1. Toplam veri sayısını sorgulayın

Yaygın sorgu yöntemi doğrudan say işlevini kullanmaktır, ancak veri miktarı çok büyük olduğunda hız yeterince hızlı değildir.

tablodan sayımı (*) seçin;

Count (id), count (*), count (1) ile hesaplanır, hız çok farklı değil ve yeterince hızlı değiller

Count () işlevine göre sorgu

Genellikle tablo tasarımının başlangıcında, kendi kendini artırma kimliği genellikle 1'den büyümeye başlar ve her veri satırı doğrudan silinmemelidir, bu nedenle maksimum kimlik toplam sayıdır, böylece maksimum kimliği doğrudan da sorgulayabilirsiniz.

tablodan max (id) seçin;

Toplam tutarı kimliğe göre hesaplayın

Buna karşılık, hız büyük ölçüde iyileştirildi.

2. Sık kullanılan alanların endekslenmesi

mysql için Birincil anahtar otomatik olarak bir dizin oluşturacaktır , İndeksli alanlardaki sorgu hızı çok hızlı hale gelecektir.

Örneğin, hızlarını karşılaştırmak için id (indeksli) ve telefon (indekssiz) üzerinde bir sorgulama yapıyoruz.

Endeks karşılaştırması

Kimlik indekslenir ve 0.01 saniye bile olmadan sorgulanabilir. Telefon indekslenmediği için 3 saniye sürdü. Bu, dizinin sorgu hızı üzerinde büyük bir etkiye sahip olduğunu göstermektedir.

3. Tam tablo indeksini önlemek için limit kullanın

Tam tablo sorgularından kaçınmak, sorgu hızını büyük ölçüde artırabilir. Bazen yalnızca bir kayıt olabileceğini biliyoruz, bu yüzden bunu 1 sınırla sınırlıyoruz. Mysql yürütüldüğünde, Koşulları karşılayan bir kayıt bulunduğunda, sınıra ulaşıldığında arama durur ve hemen geri döner.

kontrastı sınırla

4. Küçük bölüm: dizinsiz tam tablo geçiş modu

Önceki denemelerde bunu bulduk Daha küçük kimliğe sahip satır her zaman daha hızlı sorgulanabilirken, daha büyük kimliğe sahip satır daha yavaştır . Bundan, MySQL'in indekslenmemiş alanlarda sorgulama yaparken birincil anahtarın sırasına göre geçtiğini tahmin ediyoruz. Örneğin, aşağıdaki zaman karşılaştırması:

Sorgu süresinde doğrusal artış

Zamanın id ile değiştiği, uzadıkça uzadığı ve id maksimum değere ulaştığında tam tablo arama zamanıyla hemen hemen aynı olduğu görülebilir.

Maksimum id ve tam tablo alma karşılaştırması

5. Doğru veri türünü kullanın

Sayıların dizgi eşleştirmesi için mysql hatasız otomatik olarak dönüştürülür, ancak bu yine de sorgu süresini artıracaktır. Kullanıcılar veri tablosundaki telefon alanı char (11) 'de saklanır, bu nedenle sorgulama sırasında kesinlikle string kullanılmalıdır. Aşağıdaki karşılaştırma sorgu süresini göstermektedir: uygun olmayan veri türleri, daha uzun sorgu süresine yol açar.

Doğru veri türünü kullanın

6. İfadenin sonuna daha fazla alanı filtrelemek için nerede koşulunu yazın

İndekslenmemiş birden fazla yer koşulu için, mysql'nin yürütme sırası sağdan sola doğrudur.

Phone = '10000000' koşulunu karşılayan tek bir kayıt vardır, ancak create_time = '2018-11-0503:22:56' koşulunu karşılayan çok sayıda kayıt vardır. Bu nedenle, phone = '10000000' daha fazla kaydı filtreleyebilir ve en çok sağ.

create_time = '2018-11-0503:22:56' ve telefon = '10000000' olan kullanıcılardan * seçin;

image.png

Dizine alınmış koşullar için, mysql otomatik olarak optimize edecek ve önce dizinlenmiş alanları sorgulayacaktır.

Örneğin, id = 10000000 ve telefon = '10000000' ve create_time = '2018-11-0503:22:56' olan kullanıcılardan * öğesini seçin. Bu ifade, id = 10000000 en solda yazılsa bile, ancak sorgu hala ilk Geri alma için, ifade yürütme süresi 1 ms'den azdır.