SELECT count(id) เร็วกว่า count(*) จริง ๆ เหรอ

Arnon Puitrakul -

เราไปเจอความเชื่อนึงเกี่ยวกับ SQL Query มาว่า เนี่ยนะ ถ้าเราจะ Count หรือนับแถว เราอย่าไปใช้ count(*) นะ ให้เราใช้ count(id) หรือด้านในเป็น Primary Key ใน Table นั้น ๆ จะทำให้ Query ได้เร็วกว่า วันน้ีเรามาทดลองกันดีกว่า ว่ามันเป็นแบบนั้นจริง ๆ หรือไม่ และเดี๋ยวจะมาบอกว่า ทำไมด้วย

การทดลอง

เราเริ่มจากทดลองให้ดูกันก่อน โดยเราจะใช้ Database ขนาดใหญ่ ๆ หน่อย เป็น Database ที่เก็บ State ของ Home Assistant ในบ้านเราเอง น่าจะใหญ่เต็มไม้เต็มมือสำหรับการทดลองได้ดีเลยละ ใน Table นั้นมีข้อมูลอยู่ทั้งหมด 29,403,762 rows

CREATE TABLE `states` (
  `state_id` int NOT NULL AUTO_INCREMENT,
  `domain` varchar(64) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `entity_id` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `state` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `attributes` longtext COLLATE utf8mb4_unicode_ci,
  `event_id` int DEFAULT NULL,
  `last_changed` datetime(6) DEFAULT NULL,
  `last_updated` datetime(6) DEFAULT NULL,
  `created` datetime(6) DEFAULT NULL,
  `old_state_id` int DEFAULT NULL,
  `attributes_id` int DEFAULT NULL,
  `origin_idx` int DEFAULT NULL,
  `context_id` varchar(36) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `context_user_id` varchar(36) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `context_parent_id` varchar(36) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `last_updated_ts` double DEFAULT NULL,
  `last_changed_ts` double DEFAULT NULL,
  `context_id_bin` tinyblob,
  `context_user_id_bin` tinyblob,
  `context_parent_id_bin` tinyblob,
  `metadata_id` int DEFAULT NULL,
  PRIMARY KEY (`state_id`),
  KEY `ix_states_old_state_id` (`old_state_id`),
  KEY `ix_states_attributes_id` (`attributes_id`),
  KEY `ix_states_last_updated_ts` (`last_updated_ts`),
  KEY `ix_states_context_id_bin` (`context_id_bin`(16)),
  KEY `ix_states_metadata_id_last_updated_ts` (`metadata_id`,`last_updated_ts`),
  CONSTRAINT `states_ibfk_2` FOREIGN KEY (`old_state_id`) REFERENCES `states` (`state_id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE=InnoDB AUTO_INCREMENT=1631572343 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

ด้านบนนี้เป็น DDL ของ Table ชื่อว่า State ซึ่งเป็น Table ที่ Home Assistant สร้างขึ้นมาเพื่อเก็บ State หรือค่าจาก Sensor ต่าง ๆ ในบ้านของเราเอง ถ้าเราอ่าน DDL ออก เราจะเห็นว่า เขามีการสร้าง Key ทั้งหมด 6 ตัวด้วยกัน คือ Primary Key เป็น state_id ก็ Make Sense ใช่มะ และอีก 5 Secondary Index ที่เหลือ เขาสร้างเป็น Index Key เอาไว้ ดูจากการใช้งาน เราก็พอจะเข้าใจนะว่า ทำไมต้องสร้างเช่น ix_states_last_attributes_id น่าจะสำหรับเวลาเราจะดึง Attributes ที่อยู่ในแต่ละ State ออกมาเรียงเป็นกราฟแสดงผล Index ชุดนี้จะช่วยทำให้การ Query มันทำได้เร็วขึ้นนั่นเอง

1. select count(state_id) from states;
2. select count(*) from states;

งั้นเรามาลอง Query กันดีกว่า เราขอแบ่งการทดลองออกเป็น 2 ส่วนตามที่เราสงสัย อันแรกเป็นอันที่ทุกคนบอกให้เราทำ คือ การใส่ Primary Key ของ Table ลงไปใน Count และอีกอันเป็นอันที่เราบอกว่า ใช้ไปเถอะคือ การใส่ Star ลงไปใน Count เลย ในการทดลอง เราจะทำการสั่ง Query แต่ละ Expression ทั้งหมด 10 ครั้ง และ เอามาหาค่าเฉลี่ยกัน มาลองดูกันว่ามันจะเป็นอย่างไร

เอาไปหามาละ หากเราใช้คำสั่งแบบที่ 1 เวลาที่ใช้โดยเฉลี่ยอยู่ที่ 4.0296 วินาที และ วิธีที่ 2 อยู่ที่ 3.8275 วินาที อ้าวไหงกลายเป็นว่าแบบที่ 2 เร็วกว่าละ แต่ ๆๆๆๆ มันห่างกัน 5.28% เท่านั้นเอง ทำให้เอาเข้าจริงแล้ว เราเรียกว่า มันไม่มีนัยสำคัญขนาดนั้นเลย

มันเกิดอะไรขึ้น ?

explain select count(state_id) from states;

เพื่อให้เข้าใจที่มา เราขอให้ MySQL มันบอกวิธีการหน่อยว่า มันจะสั่ง Query ยังไง ผ่านคำสั่งชื่อว่า EXPLAIN

id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE states null index null ix_states_old_state_id 5 null 29308619 100 Using index

ผลด้านบน เกิดจากการสั่งให้ EXPLAIN Query ตัวที่ 1 นั่นคือ ตัวที่ใส่ Primary Key เข้าไปใน Count เราจะเห็นว่า ถึงเราจะให้มันนับ Key ที่ชื่อว่า state_id แต่พอ Explain ออกมา อ้าว กลายเป็นไปนับจาก Key ที่ชื่อว่า ix_states_old_state_id แทนแล้วละ

id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE states null index null ix_states_old_state_id 5 null 29308619 100 Using index

เปรียบเทียบกับ Query 2 ที่เราใส่ Star เข้าไปแทน จะเห็นว่า เมื่อเราสั่ง Explain ออกมา ตัว Key ที่มันใช้ มันใช้ตัวเดียวกันเลยนิหว่า ใช่ฮะ นั่นคือ เหตุว่าทำไมเราถึงบอกว่า แก ๆๆๆๆ มันเหมือนกัน แต่เดี๋ยวก่อน ๆๆ ใจเย็น ๆ ทุกคน อ่านต่อไปก่อน มันอาจจะไม่เหมือนกันก็เป็นได้ ~

ทำไมถึงเป็นแบบนี้ ?

สมมุติว่า ถ้าเรามีข้อมูลอยู่ตารางนึง เราอยากรู้ว่า ในตารางนี้ประกอบด้วยข้อมูลทั้งหมดกี่ Row เราจะทำอย่างไร

วิธีการที่ง่ายที่สุดคือ การไล่นับ ๆ ลงไปเรื่อยจากบนลงล่าง มันดูเป็นวิธีที่ง่ายมาก ๆ ไม่น่ามีปัญหาอะไร แต่ข้อเสียคือ มันช้าไปใช่มั้ย เช่น ถ้าเรามีสัก 10 rows ชิว ๆ มันจบ แต่หากเรามาเล่นกับเคสที่เราทำกันในบทความนี้หลัก 29M rows ยังไม่นับพวก Transactional Database ขนาดใหญ่ ๆ พวกนั้นหลัก 1,000M rows ได้เลย การนับแบบนี้ไม่น่าจะรอด วิธีนี้เราจะเรียกว่า Full-Table Scan เป็นสิ่งที่พวก DBMS พยายามหลีกเลี่ยงให้มากที่สุดเท่าที่จะเป็นไปได้ เช่นใน MySQL เขาจะมีบอกอยู่ว่า ตัวมันเองจะเริ่มใช้ Full-Table Scan เมื่อไหร่ใน Documentation ของ MySQL

งั้นเราเปลี่ยนวิธีใหม่ เรามาใช้ประโยชน์ของ Key กันดีกว่า เริ่มจากใช้ Primary Key ในเมื่อเราแบ่งข้อมูลเป็น Block เราเรียกวิธีนี้ว่า Clustered Index งั้น เราก็ไล่นับทีละ Block แน่นอนว่า จำนวน Block มันค่อย ๆ เยอะขึ้นเรื่อย ๆ เมื่อเราเพิ่มจำนวนข้อมูลเข้าไปเรื่อย ๆ

ความอร่อยมันเพิ่มขึ้นเมื่อ เราจำเป็นต้องเข้าถึงข้อมูลหลาย Block มากขึ้นเรื่อย ๆ เนื่องจากมันจำเป็นต้องอ่านข้อมูลหลายรอบมากขึ้น เช่น เรามี 10 Blocks การจะนับ เราจะต้องค่อย ๆ เอาทีละ Block เข้ามานับไปเรื่อย ๆ และ แต่ละ Block เราต้องหยิบมาทุก Column อีก ใหญ่มาก ๆ ยิ่งถ้าเราใช้พวก Magnetics Disk อย่าง HDD การอ่านในลักษณะนี้ไม่น่าจะดีเท่าไหร่

เมื่อเทียบกับการใช้ Secondary Key ทั้งหลาย มันเก็บแค่ Key ที่มันต้องเก็บเท่านั้น จำนวน Block ไม่เท่ากับพวก Primary Key ทำให้การไล่ไปตามพวก Secondary Key ทำได้เร็วกว่าการไล่ใน Primary Key มาก ๆ

เมื่อเป็นแบบนี้ ทำให้ใน MySQL มันป้องกันพวกสู่รู้ หรือมั่วทำพลาดด้วยการที่ ถ้ามันรู้ว่า เราจะทำการนับจำนวน Row เฉย ๆ มันจะ Ignore สิ่งที่อยู่ใน Count แล้วเปลี่ยนเป็น Star ให้หมด เพราะยังไงผลก็เหมือนกัน แถมทำงานได้เร็วกว่าแน่นอนด้วย ใช่แล้วฮะ ภายใน DBMS สมัยใหม่ นอกจากที่เราจะต้องมาทำ Query Optimisation เองแล้ว ภายในตัวมันเองยังมีกลไกที่เข้ามาช่วยด้วยเช่นกัน

สรุป

ไม่ว่า เราจะ Count Star หรือ Primary Key ก็ตาม ยังไง ๆ ผลที่เราจะได้มันเหมือนกันหมด เพราะ MySQL หรือ DBMS บางตัวเขาเขียนมาดัก และ Optimise Query ให้เราอยู่เบื้องหลังอยู่แล้ว และในทางทฤษฏีเอง การที่เรานับจาก Secondary Key เอง ยังไง ๆ ก็เร็วกว่าการที่เรานั่งนับผ่าน Primary Key ในเคสนี้อย่างแน่นอน เลยไม่แปลกเท่าไหร่ที่ DBMS จะทำแบบนี้ เพื่อให้เราได้ Performance ที่ดีที่สุดนั่นเอง