系列文章目錄
- 1.初始化測試資料
-
- 1.初始化表格（user表）
- 2.初始化表格（product表）
- 3.初始化表格（user表資料初始化）
- 4.初始化表格（product表函數）
- 5.初始化表格（product表資料初始化）
- 2.連接配接類型
-
- 1.all
- 2.index
- 3.range
- 4.ref
- 5.ref_eq
- 6.const
1.初始化測試資料
1.初始化表格（user表）
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
`id` int(18) NOT NULL AUTO_INCREMENT,
`product_no` varchar(30) DEFAULT NULL,
`name` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `product_no_index` (`product_no`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8;
2.初始化表格（product表）
DROP TABLE IF EXISTS `xxx_audit_order`;
CREATE TABLE `xxx_audit_order` (
`product_no` varchar(30) NOT NULL,
`xxx_channel_code` int(30) DEFAULT NULL,
`business_no` varchar(30) DEFAULT NULL,
`xxx_product_id` int(30) DEFAULT NULL,
`xxx_product_name` varchar(30) DEFAULT NULL,
`xxx_audit_no` varchar(30) DEFAULT NULL,
`xxx_audit_status` int(30) DEFAULT NULL,
`inspection_report_no` varchar(30) DEFAULT NULL,
`audit_report_no` varchar(30) DEFAULT NULL,
`re_audit_count` int(30) DEFAULT NULL,
`inspector_id` int(30) DEFAULT NULL,
`remark` varchar(30) DEFAULT NULL,
`delete_dt` int(30) DEFAULT NULL,
`create_by` int(18) DEFAULT NULL,
`create_dt` int(18) DEFAULT NULL,
`update_by` int(11) DEFAULT NULL,
`update_dt` datetime DEFAULT NULL,
`xxx_link` varchar(30) DEFAULT NULL,
`service_standard_no` varchar(30) DEFAULT NULL,
`depth_inspection` int(30) DEFAULT NULL,
`execute_channel` int(30) DEFAULT NULL,
`seller_type` int(30) DEFAULT NULL,
PRIMARY KEY (`product_no`),
KEY `xxx_audit_no_index` (`xxx_audit_no`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
3.初始化表格（user表資料初始化）
INSERT INTO `test`.`user` (`id`, `product_no`, `name`) VALUES ('1', 'C0512201907191455553490', '王樂');
INSERT INTO `test`.`user` (`id`, `product_no`, `name`) VALUES ('12', NULL, 'wqe');
4.初始化表格（product表函數）
建立函數生成100萬條資料，便于測試查詢性能。
DROP PROCEDURE if EXISTS BatchInsert;
delimiter $$
CREATE PROCEDURE BatchInsert(IN initId INT, IN loop_counts INT)
BEGIN
DECLARE Var INT;
DECLARE ID INT;
SET Var = 0;
SET ID = initId;
set autocommit=0; -- 關閉自動送出事務，提高插入效率
WHILE Var < loop_counts DO
INSERT INTO `xxx_audit_order` (`product_no`,`xxx_channel_code`,`business_no`,`xxx_product_id`,`xxx_product_name`,`xxx_audit_no`,`xxx_audit_status`,`inspection_report_no`,`audit_report_no`,`re_audit_count`,`inspector_id`,`remark`,`delete_dt`,`create_by`,`create_dt`,`update_by`,`update_dt`,`xxx_link`,`service_standard_no`,`depth_inspection`,`execute_channel`,`seller_type`)
VALUES (CONCAT('20220704', 100000000000 + ID),106,'RS20190719143225916727',26958,'榮耀 Play',CONCAT('C0', 512201907191454553491 + ID),FLOOR(RAND()*10) % 4,'R1152109544189558784','R1152216911870734336',2,0,null,0,6532,UNIX_TIMESTAMP() + ID ,0,Now(),FLOOR(RAND()*10) % 3,'',0,1,null);
SET ID = ID + 1;
SET Var = Var + 1;
END WHILE;
COMMIT;
END$$;
delimiter ; -- 界定符複原為預設的分号
5.初始化表格（product表資料初始化）
CALL BatchInsert(1, 1000000);
2.連接配接類型
當使用explain執行計劃的時候，會生成類似查詢結果的資料。其中的type列尤為重要，在MySQL官網中稱之為join type，連接配接類型。表示查詢語句在MySQL引擎中查詢的掃描類型，如全表掃描等，是sql優化的一個重要名額。
1.all
表示全表掃描，表示查詢語句在查詢資料的時候會将一個表裡面的全部資料都輪詢一遍，然後取出所需要的資料，也就是sql語句其實隻是起到一個篩選的作用。在我們需要查詢一張表裡面的全部的資料的時候這樣做是可以的，但在做除此之外的所有的查詢的時候全表掃描的可優化場景是相當之多的。
EXPLAIN SELECT business_no from xxx_audit_order WHERE xxx_audit_no = "20220704100000000009"
2.index
隻是另外一種類型的全表掃描。這種查詢在大多數情況下效率比全表掃描低，因為走完索引之後然後會回表查詢。但是在排序的情況下，index可能比all搞笑，但前提是排序是根據表主鍵排序。因為按照索引掃描全表的資料是有序的
如果一定要比效率，隻需要擷取這個表的資料并且排序便可以看出來誰比誰效率高了。
-- 排序字段為非主鍵字段
SELECT * from xxx_audit_order ORDER BY xxx_audit_no
-- 排序字段為主鍵字段
SELECT * from xxx_audit_order ORDER BY product_no
3.range
range指的是有範圍的索引掃描，相對于index的全索引掃描，它有範圍限制，是以要優于index。關于range比較容易了解，需要記住的是出現了range，則一定是基于索引的。同時除了顯而易見的between，and以及’>‘,’<'外，in和or也是索引範圍掃描
4.ref
查找條件列使用了索引而且不為主鍵和unique。其實，意思就是雖然使用了索引，但該索引列的值并不唯一，有重複。這樣即使使用索引快速查找到了第一條資料，仍然不能停止，要進行目标值附近的小範圍掃描。但它的好處是它并不需要掃全表，因為索引是有序的，即便有重複值，也是在一個非常小的範圍内掃描。下面為了示範這種情形，給employee表中的name列添加一個普通的key（值允許重複）
5.ref_eq
使用了主鍵或者唯一性索引進行查找的情況。一般來說，ref_eq類型多半會表示為const，這個不重要
6.const
主鍵放置到where後面作為條件查詢。效率最高