如何去寫一手好SQL? [ SQL高效率定義/ SQL高效率方法 ] 

資料來源: https://mp.weixin.qq.com/s?__biz=MzAwMjk5Mjk3Mw==&mid=2247487280&idx=1&sn=bf82ccd54f7d6128fdcd4d38a29e0b38&chksm=9ac0b112adb73804db2dffa6ba29a99e2c342a77aa74de71e4de7a8c5d34a581b19a5e81c73d&scene=0&xtrack=1&key=9c9b27d9170e2ef25c4dd4b49543e056b1ae5d37596845df39469934372ad745d4ca4b3e8967669c6d54adad93c6d23f6e5ac63625ba825e61f46b088bb98a1d69ed0f4b4b89147e11d312f1a81ade50&ascene=1&uin=MjIwODk2NDgxNw%3D%3D&devicetype=Windows+10&version=62070152&lang=zh_TW&pass_ticket=E6sbncWD6EL5ImfusgU3Dri1eFM%2B8oo1l%2F6LfyN2yNXsHIzKajhojpnFEBO7oaeC

01.查詢耗時0.5秒內

    建議將單次查詢耗時控制在0.5秒以內，0.5秒是個經驗值，源於用戶體驗的3秒原則。如果用戶的操作3秒內沒有響應，將會厭煩甚至退出。響應時間=客戶端UI渲染耗時+網絡請求耗時+應用程序處理耗時+查詢數據庫耗時，0.5秒就是留給數據庫1/6的處理時間。

02.資料庫設計原則

    ◇ 充分利用但不濫用索引，須知索引也消耗磁盤和CPU。
        ▽ 普通索引：最基本的索引。
        ▽ 組合索引：多個字段上建立的索引，能夠加速復合查詢條件的檢索。
        ▽ 唯一索引：與普通索引類似，但索引列的值必須唯一，允許有空值。
        ▽ 組合唯一索引：列值的組合必須唯一。
        ▽ 主鍵索引：特殊的唯一索引，用於唯一標識數據表中的某一條記錄，不允許有空值，一般用primary key約束。
        ▽ 全文索引：用於海量文本的查詢，MySQL5.6之後的InnoDB和MyISAM均支持全文索引。由於查詢精度以及擴展性不佳，更多的企業選擇Elasticsearch。
        ▽ 分頁查詢很重要，如果查詢數據量超過30%，MYSQL不會使用索引。
        ▽ 單表索引數不超過5個、單個索引字段數不超過5個。
        ▽ 字符串可使用前綴索引，前綴長度控制在5-8個字符。
        ▽ 字段唯一性太低，增加索引沒有意義，如：是否刪除、性別。        

    ◆ 不推薦使用數據庫函數格式化數據，交給應用程序處理。

    ◇ 不推薦使用外鍵約束，用應用程序保證數據準確性。

    ◆ 寫多讀少的場景，不推薦使用唯一索引，用應用程序保證唯一性。

    ◇ 適當冗餘字段，嘗試創建中間表，用應用程序計算中間結果，用空間換時間。

    ◆ 不允許執行極度耗時的事務，配合應用程序拆分成更小的事務。

    ◇ 預估重要數據表（比如訂單表）的負載和數據增長態勢，提前優化。

    ◆ 避免空值

03.SQL優化

    ◇ 分批處理

    ◆ 操作符<>優化[少用!=]

select id from orders where amount != 100;
替換
(select id from orders where amount > 100)
 union all
(select id from orders where amount < 100 and amount > 0) 

    ◇ OR優化

select id，product_name from orders where mobile_no = '13421800407' or user_id = 100;
替換
(select id，product_name from orders where mobile_no = '13421800407')
 union
(select id，product_name from orders where user_id = 100);

    ◆ IN / EXIST 優化

select id from orders where user_id in (select id from user where level = 'VIP');
替換
select o.id from orders o left join user u on o.user_id = u.id where u.level = 'VIP';

    ◇ 不做列運算

select id from order where date_format(create_time，'%Y-%m-%d') = '2019-07-01';
替換
select id from order where create_time between '2019-07-01 00:00:00' and '2019-07-01 23:59:59';

    ◆ Like優化

SELECT column FROM table WHERE field like '%keyword%';
替換
SELECT column FROM table WHERE field like 'keyword%';

    ◇ Join優化

        -滿足ON的條件而少用Where
        -少用left join

    ◆ Limit優化

select * from orders order by id desc limit 100000,10  #0.4s
select * from orders order by id desc limit 1000000,10 #5.2s
替換
select * from orders where id > (select id from orders order by id desc  limit 1000000, 1) order by id desc limit 0,10 #0.5s, 先篩選出ID縮小查詢範圍
select id from orders where id between 1000000 and 1000010 order by id desc #0.3s,如果查詢條件僅有主鍵ID

#LIMIT 建議只先找出 id，不要每次都用全選欄位
select * from test where val=4 limit 300000,5; #5 rows in set (15.98 sec)
替換
select * from test a inner join (select id from test where val=4 limit 300000,5) b on a.id=b.id; #5 rows in set (0.38 sec)

PS. 自己 實驗/驗證 IN 和 Join 執行優化規則後的 結果紀錄 

SELECT * FROM door WHERE controller_id IN (SELECT sn FROM controller WHERE name='205') #  Showing rows 0 - 11 (12 total, 查詢花費 0.0004 秒) [controller_id: 19290001 - 19290001]

SELECT * FROM door AS d LEFT JOIN controller AS c ON d.controller_id = c.sn WHERR c.name='205'; # 0 - 11 (12 total, 查詢花費 0.0004 秒)

SELECT d.* FROM door AS d INNER JOIN controller AS c ON d.controller_id = c.sn WHERE c.name='205'; #  Showing rows 0 - 11 (12 total, 查詢花費 0.0003 秒)



---------------

實驗數據:

CREATE TABLE IF NOT EXISTS `controller` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `sn` int(11) DEFAULT NULL,
  `model` int(11) DEFAULT NULL,
  `name` varchar(255) DEFAULT NULL,
  `alias` varchar(255) DEFAULT NULL,
  `pic` longtext,
  `sydm_id` int(11) NOT NULL DEFAULT '0',
  `identifier` varchar(255) DEFAULT NULL,
  `controller_map_identifier` varchar(255) DEFAULT NULL,
  `state` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id` (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=4 ;

INSERT INTO `controller` (`id`, `sn`, `model`, `name`, `alias`, `pic`, `sydm_id`, `identifier`, `controller_map_identifier`, `state`) VALUES
(1, 19050051, 1, '228', '', NULL, 1, '1B5AE465A358463B', '1A099D0E79EEA7A4', 2),
(2, 19290001, 4, '205', '', NULL, 1, '6EE57E650894698B', 'C8D7219D4E9791C8', 2),
(3, 18486145, 4, '218', '', NULL, 1, 'A7AF9998CCDF7243', 'E7FAC037B2E38AFF', 2);

CREATE TABLE IF NOT EXISTS `door` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `controller_id` int(11) DEFAULT NULL,
  `controller_door_index` int(11) NOT NULL DEFAULT '0',
  `identifier` varchar(255) DEFAULT NULL,
  `door_map_identifier` varchar(255) DEFAULT NULL,
  `state` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id` (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=26 ;

INSERT INTO `door` (`id`, `name`, `controller_id`, `controller_door_index`, `identifier`, `door_map_identifier`, `state`) VALUES
(1, 'door01(19050051)', 19050051, 1, '', 'B2EB66ED6D2BB1B2', 2),
(2, 'door01(19290001)', 19290001, 1, '86F7F8FA4AA76345', '7B7F8B46DB8A1312', 2),
(3, 'door02(19290001)', 19290001, 2, 'F9B0FE99AB5D2184', '951A6032B2962383', 2),
(4, 'door03(19290001)', 19290001, 3, 'A1A378342189FCD3', '458DF818A38BC46A', 2),
(5, 'door04(19290001)', 19290001, 4, '0C018A09A7BB7A9B', '34B0289C92E8B18E', 2),
(6, 'door05(19290001)', 19290001, 5, '', '6B1997D7257E3CDD', 2),
(7, 'door06(19290001)', 19290001, 6, 'AB53DDA9D78C179C', '96CCEDCB3918CF60', 2),
(8, 'door07(19290001)', 19290001, 7, 'EA372543C3B77775', '1334DF4137FCC02A', 2),
(9, 'door08(19290001)', 19290001, 8, '69492C81C66B87DB', '2DF50BCE661B0C92', 2),
(10, 'door09(19290001)', 19290001, 9, '', 'D414BA0D8E5C16A6', 2),
(11, 'door10(19290001)', 19290001, 10, '', '7F95D0A69CB16643', 2),
(12, 'door11(19290001)', 19290001, 11, '', 'B3EEC498D0D8DD5F', 2),
(13, 'door12(19290001)', 19290001, 12, '', '7780DE9FA206C088', 2),
(14, 'door01(18486145)', 18486145, 1, '', '33C3F98CC17C0AD2', 2),
(15, 'door02(18486145)', 18486145, 2, '', '5501952DC8ABE6EE', 2),
(16, 'door03(18486145)', 18486145, 3, '626E404C0E0CBA0C', '74AC0B62A48A0C05', 1),
(17, 'door04(18486145)', 18486145, 4, '', '464C224AD9E80B92', 2),
(18, 'door05(18486145)', 18486145, 5, '', '845985BA2612696D', 2),
(19, 'door06(18486145)', 18486145, 6, '', '7838F3FBC4C6A62C', 2),
(20, 'door07(18486145)', 18486145, 7, '', 'A76BB037E4BAE0B9', 2),
(21, 'door08(18486145)', 18486145, 8, '', 'DF181C41F2773DB2', 2),
(22, 'door09(18486145)', 18486145, 9, '', 'A4703871BDF25D66', 2),
(23, 'door10(18486145)', 18486145, 10, '', '8F62D6E2F54B119B', 2),
(24, 'door11(18486145)', 18486145, 11, '', '37DBC0408BDD4969', 2),
(25, 'door12(18486145)', 18486145, 12, '', '7298E334D0A9E77D', 2);