Pemahaman mendalam tentang cara pengoptimuman indeks MySQL berfungsi

Artikel ini membawa anda pengetahuan yang berkaitan tentang mysql, yang terutamanya memperkenalkan kandungan yang berkaitan tentang prinsip kerja pengoptimum indeks, termasuk komposisi Pelayan MySQL dan pemilihan indeks oleh Pengoptimum MySQL dan analisis kos SQL, dan akhirnya meringkaskan keseluruhan proses pertanyaan melalui pertanyaan pilihan Mari kita lihat bersama-sama saya harap ia akan membantu semua orang.

Pembelajaran yang disyorkan: tutorial video mysql

1 Cara pengoptimum MySQL memilih indeks

Mari kita lihat jadual ini Medan SUB_ODR_ID telah mencipta dua indeks yang berkaitan Menurut apa yang telah kami pelajari sebelum ini, kami mencipta indeks kunci utama auto-increment (ID), LOG_ID) Tetapkan kepada indeks bersama dan indeks unik, dan tetapkan dua indeks untuk dua kali CREATE_TIME dan UPDATE_TIME masing-masing. SUB_ODR_ID

CREATE TABLE `***`  (
  `ID` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键id',
  `LOG_ID` varchar(32) NOT NULL COMMENT '交易流水号',
  `ODR_ID` varchar(32) NOT NULL COMMENT '父单号',
  `SUB_ODR_ID` varchar(32) NOT NULL COMMENT '子单号',
  `CREATE_TIME` datetime(0) NOT NULL COMMENT '创建时间',
  `CREATE_BY` varchar(32) NOT NULL COMMENT ' 创建人',
  `UPDATE_TIME` datetime(0) NOT NULL DEFAULT CURRENT_TIMESTAMP(0) ON UPDATE CURRENT_TIMESTAMP(0) COMMENT '更新时间',
  `UPDATE_BY` varchar(32) NOT NULL COMMENT '更新人',
  PRIMARY KEY (`ID`) USING BTREE,
  UNIQUE INDEX `UNQ_LOG_SUBODR_ID`(`LOG_ID`, `SUB_ODR_ID`) USING BTREE,
  INDEX `IDX_ODR_ID`(`ODR_ID`) USING BTREE,
  INDEX `IDX_SUB_ID`(`SUB_ODR_ID`) USING BTREE,
  INDEX `IDX_CREATE_TIME`(`CREATE_TIME`) USING BTREE,
  INDEX `IDX_UPDATE_TIME`(`UPDATE_TIME`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 SET = utf8 COLLATE = utf8_general_ci COMMENT = '分摊业务明细表' ROW_FORMAT = Dynamic;

Dalam medan pertanyaan SUB_ODR_ID, tiga indeks berkaitan boleh digunakan secara teori: UNQ_LOG_SUBODR_ID, IDX_SUB_ID Bagaimanakah pengoptimum MySQL memilih daripada ketiga-tiga indeks ini?

Dalam pangkalan data hubungan, B-tree hanyalah struktur data yang digunakan untuk penyimpanan.

Cara anda menggunakannya bergantung pada pengoptimum pangkalan data. Pengoptimum menentukan pemilihan indeks tertentu, yang dikenali sebagai pelan pelaksanaan. Pemilihan pengoptimum adalah berdasarkan kos, dengan lebih rendah kos, lebih tinggi indeks keutamaan.

1. Komposisi pangkalan data MySQL

Pangkalan data MySQL terdiri daripada lapisan Pelayan (pelayan) dan lapisan Enjin (enjin).

Lapisan Serve mempunyai penganalisis SQL, pengoptimum SQL dan pelaksana SQL, yang bertanggungjawab untuk proses pelaksanaan khusus pernyataan SQL.

Lapisan Enjin bertanggungjawab untuk menyimpan data tertentu, seperti enjin storan InnoDB yang paling biasa digunakan dan enjin TempTable yang digunakan untuk menyimpan set hasil sementara dalam ingatan.

Pengoptimum SQL akan menganalisis semua kemungkinan rancangan pelaksanaan dan memilih pelaksanaan kos terendah. Pengoptimum ini dipanggil CBO (pengoptimum berasaskan kos).

2. Pengiraan kos pangkalan data MySQL

Dalam MySQL, pengiraan kos pengiraan SQL mudah difahami, iaitu capaian Pangkalan data (halaman pangkalan data, cakera) Memproses data.

Kos CPU, yang mewakili kos pengiraan, seperti perbandingan nilai kunci indeks, perbandingan nilai rekod dan pengisihan set hasil. Operasi ini diselesaikan pada lapisan pelayan

kos IO, yang mewakili kos IO peringkat enjin MySQL 8.0 boleh mengira secara berasingan kos memori membaca IO dan cakera IO dengan membezakan sama ada data jadual berada dalam ingatan. .

Cost  = Server Cost + Engine Cost  = CPU Cost + IO Cost

Pengoptimum MySQL percaya bahawa jika sekeping SQL perlu mencipta jadual sementara berasaskan cakera, maka kos pada masa ini adalah yang terbesar, iaitu 20 kali ganda daripada jadual sementara berasaskan memori . Kos untuk membandingkan nilai dan rekod kunci indeks adalah sangat rendah, tetapi jika terdapat banyak rekod untuk dibandingkan, kosnya boleh menjadi sangat tinggi.

Pengoptimum MySQL percaya bahawa kos membaca dari cakera adalah 4 kali ganda kos memori (kosnya tidak statik dan akan berbeza-beza bergantung pada perkakasan).

2. Kos pertanyaan MySQL

Semak nilai setiap kos dan prinsip kerja pengoptimum MySQL Kami melaksanakan baris pernyataan SQL berikut dan menganalisis pelaksanaan proses. Indeks MySQL Pemilihan adalah berdasarkan kos pelaksanaan SQL

EXPLAIN FORMAT=json 
select * from test.fork_business_detail f where f.sub_odr_id = ''

kos_baca mewakili kos bacaan daripada enjin storan InnoDB

kos_eval mewakili kos CPU lapisan pelayan

prefix_cost mewakili SQL Jumlah kos

data_read_per_join mewakili jumlah bilangan bait dalam rekod baca.

{
	"query_block": {
		"cost_info": {
			"query_cost": "1.20"
		},
		"table": {
			"access_type": "ref",
			"possible_keys": [
				"IDX_SUB_ID"
			],
			"key": "IDX_SUB_ID",
			"used_key_parts": [
				"SUB_ODR_ID"
			],
			"key_length": "98",
			"ref": [
				"const"
			],
			"cost_info": {
				"read_cost": "1.00",
				"eval_cost": "0.20",
				"prefix_cost": "1.20",
				"data_read_per_join": "1K"
			},
			"used_columns": [
				"ID",
				"LOG_ID",
				"ODR_ID",
				"SUB_ODR_ID",
				"CREATE_TIME",
				"CREATE_BY",
				"UPDATE_TIME",
				"UPDATE_BY"
			]
		}
	}
}

3. PILIH proses pelaksanaan

Bagaimana untuk meningkatkan prestasi pertanyaan MySQL? Pertama, anda perlu memahami keseluruhan proses pemprosesan SQL oleh pengoptimum pertanyaan. Proses pelaksanaan SELECT SQL diambil sebagai contoh, seperti yang ditunjukkan dalam rajah di bawah:

Pelanggan menghantar pertanyaan SELECT kepada pelayan terlebih dahulu menyemak pertanyaan cache. Jika cache terkena, hasil yang disimpan dalam cache akan dikembalikan serta-merta. Jika tidak, masukkan peringkat seterusnya;

Pelayan melakukan penghuraian dan prapemprosesan SQL, dan pengoptimum pertanyaan menjana pelan pelaksanaan yang sepadan dengan API enjin storan untuk melaksanakan pertanyaan berdasarkan pelan pelaksanaan yang dihasilkan oleh pengoptimum; hasilnya akan dikembalikan kepada klien dan dimasukkan ke dalam cache pertanyaan pada masa yang sama.

Pembelajaran yang disyorkan:

tutorial video mysql

Atas ialah kandungan terperinci Pemahaman mendalam tentang cara pengoptimuman indeks MySQL berfungsi. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!