Pembelajaran Lanjutan MySQL: Penjelasan terperinci tentang cara mencipta indeks yang cekap dan sesuai

Artikel ini adalah kajian lanjutan tentang MySQL Ia akan memberi anda pemahaman terperinci tentang cara membuat indeks yang lebih sesuai.

Tidak tahu kepentingan pengindeksan apabila lebih banyak data dalam perpustakaan, apatah lagi mengetahui indeks yang sesuai apabila lebih banyak data dalam perpustakaan. Kepentingan Indeks. Artikel ini memperkenalkan cara mencipta indeks yang cekap dan sesuai . [Cadangan berkaitan: tutorial video mysql]

1 Apabila menggunakan lajur indeks untuk membuat pertanyaan, cuba jangan gunakan ungkapan dan letakkan pengiraan dalam lapisan perniagaan dan bukannya lapisan pangkalan data

Seperti yang ditunjukkan dalam rajah di bawah, keputusan kedua-dua SQL adalah sama, tetapi rancangan pelaksanaan kedua-dua SQL adalah berbeza Kecekapan indeks dalam jenis adalah jauh kurang daripada actor_id 4 dalam keadaan di mana . Ungkapan mempengaruhi pelan pelaksanaan, dan maksud perwakilan jenis Sila rujuk terangkan untuk penjelasan terperinci

2. Cuba gunakan pertanyaan kunci utama bukannya indeks lain, pertanyaan kunci utama tidak akan menyebabkan pertanyaan pulangan jadual.

Semua jadual kami pada asasnya mempunyai kunci utama, jadi dalam pembangunan biasa, gunakan indeks jika ia boleh digunakan dan gunakan indeks kunci primer jika ia boleh digunakan.

3. Gunakan indeks awalan

Banyak kali indeks kami sebenarnya rentetan, dan rentetan panjang pasti akan muncul, yang akan menyebabkan indeks mengambil terlalu banyak ruang dan mengurangkan kecekapannya. Terutamanya untuk lajur panjang seperti gumpalan, teks dan varchar. Pada masa ini, cara untuk menanganinya bukanlah dengan menggunakan nilai penuh medan sebagai indeks, tetapi hanya mengambil separuh masa pertama (selektiviti indeks awalan yang dipilih adalah hampir dengan keseluruhan lajur). Ini boleh mengurangkan ruang indeks, sekali gus meningkatkan kecekapan Kelemahannya ialah ia mengurangkan selektiviti indeks.

Selektiviti indeks: nisbah nilai indeks unik kepada jumlah bilangan rekod jadual data (#T), antara 1/#T hingga 1. Semakin tinggi selektiviti indeks, semakin tinggi kecekapan pertanyaan, kerana data sangat dibezakan dan lebih banyak baris boleh ditapis keluar. Selektiviti indeks unik ialah 1 dan prestasinya adalah yang terbaik.

Sebagai contoh, dalam medan e-mel jadual pekerja syarikat, akhiran e-mel syarikat adalah sama, seperti xxxx@qq.com Malah, satu-satunya bahagian yang sah dalam menggunakan e-mel sebagai indeks ialah xxxx, kerana @qq.com adalah kedua-duanya Begitu juga, ia tidak bermakna untuk pengindeksan Jelas sekali hanya menggunakan xxxx sebagai indeks mempunyai selektiviti yang sama dengan keseluruhan nilai, tetapi menggunakan xxxx sebagai indeks jelas akan mengurangkan ruang indeks.

Di bawah ini kami mengambil jadual pekerja sebagai contoh (lihat akhir artikel untuk struktur jadual dan data)

Kami mengambil medan e-mel sebagai contoh untuk mengindeks:

Alamat e-mel data ini sebenarnya adalah nombor telefon bimbit @qq.com Sebagai contoh, sebenarnya, 11 digit pertama dan yang berikut adalah sama. Saya menggunakan sql berikut untuk melihat pengiraan selektiviti data ini (ambil 10, 11, 12 yang pertama masing-masing).

-- 当是11个前缀的时候选择性是1，在增加字段长度，选择性也不会变化
select count(distinct left(email,10))/count(*) as e10, count(distinct left(email,11))/count(*) as e11,      count(distinctleft(email,12))/count(*) as e12 from employee;

Daripada rajah di atas kita dapat melihat bahawa selektiviti 10 teratas, 11 teratas dan 12 teratas adalah masing-masing 0.14, 1.0, 1.0 dan indeks berada pada kedudukan ke-11 Pemilihan adalah 1 tertinggi, jadi tidak perlu menggunakan semua sebagai indeks, yang meningkatkan ruang indeks.

-- 创建前缀索引
alter table employee add key(email(11));

Kita juga boleh menggunakan kiraan untuk mengira kekerapan bagi statistik (semakin sedikit kejadian, semakin rendah kadar pengulangan dan semakin besar selektiviti)

-- 查找前缀出现的频率
select count(*) as cnt,left(email,11) as pref from employee group by pref order by cnt desc limit 10;

4. Gunakan imbasan indeks untuk mengisih

Kami selalunya perlu mengisih, menggunakan tertib mengikut, tetapi tertib mengikut lebih mempengaruhi prestasi Ia mengisih data ke dalam memori terlalu besar untuk disimpan dalam ingatan, jadi ia hanya boleh diproses dalam berbilang kelompok. Walau bagaimanapun, indeks itu sendiri dipesan, dan lebih mudah untuk melengkapkan pengisihan terus melalui indeks.

Mengimbas indeks itu sendiri adalah pantas kerana anda hanya perlu beralih dari satu rekod indeks ke rekod seterusnya, tetapi jika indeks tidak dapat merangkumi semua lajur yang diperlukan untuk pertanyaan, anda perlu mengimbas indeks setiap kali rekod dikembalikan ke jadual dan baris yang sepadan disoal sekali. Ini pada dasarnya adalah IO rawak. Oleh itu membaca data dalam susunan indeks biasanya lebih perlahan daripada imbasan jadual penuh berurutan.

MySQL boleh menggunakan indeks yang sama untuk mengisih dan mencari baris. Sila pertimbangkan untuk membuat indeks sedemikian jika boleh.

Hanya apabila susunan lajur indeks benar-benar konsisten dengan susunan tertib mengikut klausa, dan arah pengisihan (tertib ke belakang atau ke hadapan) semua lajur adalah sama, MySQL boleh menggunakan indeks untuk menyusun keputusan. Jika pertanyaan perlu dikaitkan dengan berbilang jadual, pengisihan indeks hanya boleh digunakan apabila medan dalam susunan mengikut klausa semuanya daripada jadual pertama. Susunan mengikut pertanyaan juga perlu memenuhi awalan paling kiri indeks gabungan, jika tidak pengisihan indeks tidak boleh digunakan.

Sebenarnya, terdapat dua perkara utama yang perlu diberi perhatian semasa pembangunan:

• Medan dalam keadaan where dan medan mengikut tertib boleh digabungkan indeks dan memenuhi awalan paling kiri.
• Susunan medan mengikut susunan perlu konsisten Desc dan asc tidak boleh wujud.

5 Kesatuan semua, dalam, atau semua boleh menggunakan indeks, tetapi disyorkan untuk digunakan dalam

Seperti di atas, kesatuan semua akan dilaksanakan dua kali, Dan dalam dan atau hanya berlaku sekali. Pada masa yang sama, kita dapat melihat bahawa rancangan pelaksanaan atau dan dalam adalah sama,

tetapi kita melihat pada masa pelaksanaannya. Seperti yang ditunjukkan di bawah, gunakan set profiling=1 untuk melihat masa terperinci dan gunakan show profiles untuk melihat masa tertentu. Rajah berikut menunjukkan bahawa masa atau ialah 0.00612000 dan masa masuk ialah 0.00022800 Jurang masih sangat besar (data jadual ujian hanya mempunyai 200 baris)

.

union all: query Ia terbahagi kepada dua peringkat Malah, terdapat juga kesatuan Apabila kesatuan mesti digunakan dalam pembangunan harian, adalah disyorkan untuk menggunakan kesatuan semua, kerana kesatuan mempunyai langkah tambahan penyahduplikasian yang berbeza. Jadi cuba gunakan kesatuan semua.

6. Lajur julat boleh menggunakan indeks

syarat julat: >,>=,<,<=,antara

lajur julat Indeks boleh digunakan, tetapi lajur yang mengikuti lajur julat tidak boleh menggunakan indeks (indeks boleh digunakan untuk paling banyak satu lajur julat)

Sebagai contoh, nama indeks gabungan jika syarat pertanyaan ialah where age>18 and name="纪", nama selepas Indeks tidak dapat ditemui.

Saya pernah ditanya dalam temu bual sama ada saya boleh mengikuti indeks tertentu atau tidak berjaya menjawabnya akhir artikel untuk kesimpulan.

7. Penukaran jenis paksa akan mengimbas keseluruhan jadual

Saya mentakrifkan medan mobile dalam jadual pekerja sebagai jenis varchar dan menetapkan indeks yang saya gunakan pertanyaan masing-masing.

Lihat hasil: kedua-dua jenis adalah berbeza dan hanya rentetan menggunakan indeks.

Jika jenis nilai keadaan tidak konsisten dengan yang ditakrifkan dalam jadual, maka mysql akan memaksa penukaran jenis, tetapi hasilnya tidak akan diindeks semasa pembangunan indeks, kita perlukan untuk memasukkan jenis yang sepadan mengikut jenis yang ditakrifkan oleh kita sendiri.

8 Pembezaan data tidak tinggi dan medan yang kerap dikemas kini tidak sesuai untuk pengindeksan

• Kemas kini lajur indeks akan berubah B-tree, yang kerap dikemas kini akan mengurangkan prestasi pangkalan data.
• Serupa dengan jantina (hanya lelaki dan perempuan, atau tidak diketahui), data tidak boleh ditapis dengan berkesan.
• Secara amnya, indeks boleh dibuat apabila perbezaan melebihi 80%. lajur indeks Null tidak dibenarkan, dan anda mungkin mendapat hasil yang tidak dijangka

Iaitu, medan yang diindeks tidak boleh kosong sebanyak mungkin, tetapi dalam kerja sebenar, tidak mungkin ia tidak akan kosong. Jadi kendalikan ia mengikut perniagaan sebenar dan cuba elakkan situasi ini.

10 Apabila sambungan jadual diperlukan, sebaiknya jangan melebihi tiga jadual

Sambungan jadual sebenarnya ialah padanan bersarang gelung bagi berbilang jadual, yang menjejaskan prestasi dan memerlukan data medan bergabung. jenis mesti konsisten untuk meningkatkan kecekapan pertanyaan. Mari tulis artikel khas tentang prinsip sambungan jadual kemudian.

11 Cuba gunakan had apabila anda boleh.

Fungsi had bukan hanya untuk paging, tetapi fungsi pentingnya adalah untuk mengehadkan output.

had sebenarnya merentasi data pertanyaan satu demi satu Jika hanya satu keping data diperlukan untuk menambah had

, maka penunjuk indeks akan berhenti selepas mencari data yang memenuhi syarat, dan tidak akan. terus menilai ke bawah, tetapi akan kembali secara langsung. Jika tiada had, penghakiman akan diteruskan.

Tetapi jika anda mengambil 5 item selepas 10,000 item dengan paging limit 1, anda perlu berhati-hati melintasi 10,000 item dan kemudian mengekstrak 5 item, yang sangat tidak cekap. Petua: Jika kunci utama adalah berjujukan, anda boleh mendapatkan data bernombor secara terus melalui kunci utama.

12 Cuba kawal bilangan indeks jadual tunggal dalam 5 limit 10000,10005

Mencipta/mengekalkan indeks juga memerlukan wang dan mengambil ruang. Lebih banyak indeks, lebih baik Indeks mesti digunakan secara rasional.

13 Bilangan medan dalam indeks gabungan tunggal tidak boleh melebihi 5

Semakin banyak medan, semakin besar indeks dan semakin banyak ruang storan yang akan diduduki.

Lebih banyak indeks lebih baik, dan indeks tidak perlu direka bentuk apabila anda mula membina jadual tidak akan menjadi indeks yang cekap. Anda perlu memahami perniagaan dan berdasarkan yang berkaitan Sql perniagaan mesti ditimbang secara statistik sebelum membina indeks yang berkaitan Dengan cara ini, pertimbangan akan lebih komprehensif dan indeks yang ditubuhkan akan lebih berkesan dan cekap.

Di atas adalah butiran kecil yang sepadan dengan pengoptimuman indeks. Saya harap ia dapat membantu anda menulis swish sql >Mengenai isu sama ada untuk mengindeks atau tidak

结论：只有主键会走，唯一键和普通索引都不会走。

我在employee表中建了唯一索引employee_num和联合索引employee_num+name，结果就是下图的执行情况。

---

employee表结构

CREATE TABLE `employee`  (  
`employee_id` bigint(20) NOT NULL AUTO_INCREMENT, 
`employee_num` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '员工编码',
`name` varchar(60) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '员工姓名',  
`email` varchar(60) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL COMMENT '电子邮件', 
`mobile` varchar(60) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL COMMENT '移动电话', 
`gender` tinyint(1) NOT NULL COMMENT '性别, 0: 男 1: 女',  PRIMARY KEY (`employee_id`) USING BTREE, 
INDEX `email`(`email`(11)) USING BTREE,  INDEX `employee_u1`(`employee_num`, `name`) USING BTREE,
UNIQUE INDEX `employee_u2`(`employee_num`) USING BTREE,  INDEX `employee_u3`(`mobile`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 0 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci COMMENT = '员工表' ROW_FORMAT = Dynamic;

employee数据如下：

INSERT INTO `sakila`.`employee`(`employee_id`, `employee_num`, `name`, `email`, `mobile`, `gender`) VALUES (10, '001', '员工A', '15500000001@qq.com', '15500000001', 1);
INSERT INTO `sakila`.`employee`(`employee_id`, `employee_num`, `name`, `email`, `mobile`, `gender`) VALUES (11, '002', '员工B', '15500000002@qq.com', '15500000002', 0);
INSERT INTO `sakila`.`employee`(`employee_id`, `employee_num`, `name`, `email`, `mobile`, `gender`) VALUES (12, '003', '员工C', '15500000003@qq.com', '15500000003', 0);
INSERT INTO `sakila`.`employee`(`employee_id`, `employee_num`, `name`, `email`, `mobile`, `gender`) VALUES (13, '004', '员工D', '15500000004@qq.com', '15500000004', 0);
INSERT INTO `sakila`.`employee`(`employee_id`, `employee_num`, `name`, `email`, `mobile`, `gender`) VALUES (14, '005', '员工E', '15500000005@qq.com', '15500000005', 1);
INSERT INTO `sakila`.`employee`(`employee_id`, `employee_num`, `name`, `email`, `mobile`, `gender`) VALUES (15, '006', '员工F', '15500000006@qq.com', '15500000006', 1);
INSERT INTO `sakila`.`employee`(`employee_id`, `employee_num`, `name`, `email`, `mobile`, `gender`) VALUES (16, '007', '员工G', '15500000007@qq.com', '15500000007', 0);
INSERT INTO `sakila`.`employee`(`employee_id`, `employee_num`, `name`, `email`, `mobile`, `gender`) VALUES (17, '008', '员工H', '15500000008@qq.com', '15500000008', 1);
INSERT INTO `sakila`.`employee`(`employee_id`, `employee_num`, `name`, `email`, `mobile`, `gender`) VALUES (18, '009', '员工I', '15500000009@qq.com', '15500000009', 1);
INSERT INTO `sakila`.`employee`(`employee_id`, `employee_num`, `name`, `email`, `mobile`, `gender`) VALUES (19, '010', '员工J', '15500000010@qq.com', '15500000010', 1);
INSERT INTO `sakila`.`employee`(`employee_id`, `employee_num`, `name`, `email`, `mobile`, `gender`) VALUES (20, '011', '员工K', '15500000011@qq.com', '15500000011', 1);
INSERT INTO `sakila`.`employee`(`employee_id`, `employee_num`, `name`, `email`, `mobile`, `gender`) VALUES (21, '012', '员工L', '15500000012@qq.com', '15500000012', 1);
INSERT INTO `sakila`.`employee`(`employee_id`, `employee_num`, `name`, `email`, `mobile`, `gender`) VALUES (22, '013', '员工M', '15500000013@qq.com', '15500000013', 0);
INSERT INTO `sakila`.`employee`(`employee_id`, `employee_num`, `name`, `email`, `mobile`, `gender`) VALUES (23, '014', '员工N', '15500000014@qq.com', '15500000014', 1);

更多编程相关知识，请访问：编程视频！！

Atas ialah kandungan terperinci Pembelajaran Lanjutan MySQL: Penjelasan terperinci tentang cara mencipta indeks yang cekap dan sesuai. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!