Tuesday, June 29, 2010

Add index to table in MySQL for faster query

  • ประเด็นไม่มีไรมาก คือได้ไปมีส่วนร่วม เพิ่ม page ให้ระบบงานระบบหนึ่ง
  • ซึ่งระบบนั้นใช้ PHP + Apache2 + MySQL + CentOS
  • page ที่เราทำเพิ่มก็ไม่มีไรมาก query ข้อมูลจาก table ออกมาแสดง
  • ซึ่งมีอยู่ 3 table ที่ต้อง join กัน งานแค่เนี๋ย ทำไม่กี่ชั่วโมงก็เสร็จ
  • สำหรับคนไม่มีพื้น PHP อย่างตัวคนเขียนเนี๋ย
  • จะทำอาไรสักคำสั่งก็ต้อง google เพื่อหาการใช้งาน function นั้นๆ ว่าต้องทำอย่างไร
  • จึงค่อนข้างเสียเวลาหว่า เล็กน้อยถึงค่อนข้างมาก เลยปาเข้าไป เป็นชั่วโมง
  • แต่สำหรับท่านที่พอมี พื้น PHP บ้างน่าจะทำไม่เกิน 10 นาที
  • หลังจากนั่งงมโค้ด PHP + MySQL อยู่ตั้นนาน ก็เสร็จ
  • ปัญหาต่อมาหลังจาก page นั้นใช้งานได้แล้ว แต่...มันช้ามากๆ
  • คือ ข้อมูลแค่ 5 พันกว่า record ทำการ join แค่ 3 table แต่ใช้เวลา query หลายนาทีเลยอ่ะ
  • หาข้อมูลใน google ต่อว่าจะทำอย่างไรให้ query ได้เร็ว
  • ตอนแรกคิดว่า ลองสร้าง view ดูดีมั้ยหว่า แต่คิดว่ามันคงไม่เร็วขึ้นหรอกหว่า (คิดเอาเอง ยังไม่ได้ลอง)
  • และความพยายามก็เป็นผล คือ การสร้าง index ให้ column ใน table สามารถทำให้ query ได้เร็วหว่า
  • หรือ การทำ cache ให้ MySQL ก็สามารถเพิ่มความเร็วในการ query ได้เช่นกัน

เพิ่มความเร็ว query ด้วยการ สร้าง index ใน MySQL

Query ด้วยคำสั่ง
select a.chat
, a.pid
, a.title
, c.title_n
, a.fname
, a.lname
, a.birthdate
, a.sex
, b.hospsub_n
, b.mooban_n
from all_null_update a
, mastercupid b
, titlename c
where a.chat = b.mastercup_id
and a.title = c.title_c


Befor

  • จำนวน 5711 record เชื่อม 3 table
  • ใช้เวลาไป 6 นาที 17 วินาที เลยอ่ะ คนเปิดเว็บใครจะรอไหวเค้าคงนึกว่ามันค้างมากกว่าน้อ
  • อันนี้ขนาด query บนเครื่องตัวเองยัง ช้าขนาดนี้ แล้ว connect ไปที่ server จะช้าขนาดใหน

After
  • ทดสอบสร้าง index ให้แต่ละ column เราที่เราใช้เป็นเงื่อนไขบ่อยหลัง where หรือ ใช้ใน join table อ่ะ
  • คำสั่งสร้าง index ของตัวอย่างนี้เป็นดังนี้
ALTER TABLE a ADD KEY (chat);
ALTER TABLE a ADD KEY (title);
ALTER TABLE b ADD KEY (mastercup_id);
ALTER TABLE c ADD KEY (title_c);
  • หลังจากเราได้เพิ่ม index เข้าไปแหละ เราใช้เวลา query แค่ 318 มิลลิวินาทีเองหว่า
  • ไม่ถึงนาทีด้วยซ้ำ เร็วขึ้นโค้ดๆ

ผลกระทบเมื่อทำ index

  • การ insert, update ทำได้ช้าเพระต้องไปสร้าง index ให้กับ column นั้นๆ ของ record ใหม่ด้วย
  • พื้นที่บน database ก้อนนั้นๆ มีการใช้มากขึ้น ง่ายๆ ก็คือ มีการใช้พื้นที่เพิ่มขึ้นในการเก็บสร้าง index

เพิ่มเติม

  • index อาจมีข้อจำกัดอยู่บ้าง เมื่อ query นั้นมี in หรือ like มาเกี่ยวข้อง เค้าว่ามา
  • เราสามารถลบ index ของ column ออกด้วยคำสั่ง
ALTER TABLE items DROP KEY item_descr;
  • เพิ่ม index จะมี ( ) ครอบชื่อ column ที่ต้องการทำ index
  • ส่วน การลบ สังเกตุว่าจะไม่มี ( ) ครอบชื่อ column ที่ต้องการถอน index ออกนะครับพี่น้อง
  • จำได้ว่า SQL Server เมื่อเราทำการสร้าง table column ที่เรากำหนดเป็น PK จะมีการสร้าง index ให้เลย ไม่จำเป็นต้องสร้างเองสำหรับ PK ใน table นั้นๆ
  • แต่ MySQL ไม่สร้างให้ หรืออย่างไรงง

ปล.

  • ทดสอบสร้าง index แค่ table เดียว
ALTER TABLE b ADD KEY (mastercup_id);
  • เวลาในการ query ใช้ได้เลยหว่า นึกว่าต้องสร้าง index กับทุก table ที่ join ซะอีกนะเนี๋ย
  • แต่มีปัญหากับ การแสดง row number dynamic ซะงั้น งง ต้องเพิ่มงี้ด้วยถึงหายหว่า
ALTER TABLE c ADD KEY (title_c);
  • สรุปง่ายก็ add index ให้ table ที่เราทำการ join ด้วยนั่นแหละ ไม่ต้อง add ให้ table หลักก็ได้
  • ในที่นี้ table หลักคือ a เอาไป join กับ b และ c เราก็ add index ให้ FK ของ b และ c ก็พอ
  • ถ้าผลลัพธ์เรามี rank dynamic ด้วยมันจะ เรียงลำดับให้ถูกต้องด้วยแหละ
  • แต่ถ้าเราไม่ join กับ table อื่นๆ เราก็เพิ่ม index ให้กับ column ที่เราใช้ตามหลัง where บ่อยๆ

อ้างอิง

  • http://spalinux.com/2008/06/indexing_mysql_database
  • http://spalinux.com/2009/04/speed_up_select_with_mysql_query_cache
  • http://www.thaisapclub.com/forums/showthread.php?t=128
  • http://www.narisa.com/forums/index.php?showtopic=11909
  • http://www.narisa.com/forums/index.php?showtopic=11830
  • http://stackoverflow.com/questions/127156/how-do-i-check-if-index-exists-on-a-table-field-in-mysql
  • http://www.tizag.com/mysqlTutorial/mysql-index.php
  • http://www.narisa.com/forums/index.php?showtopic=14734
  • http://www.narisa.com/forums/index.php?showtopic=5778

No comments:

Post a Comment