Back to Question Center
0

วิธีเพิ่มประสิทธิภาพ MySQL: ดัชนี, การสืบค้นข้อมูลช้า, การกำหนดค่า            วิธีการเพิ่มประสิทธิภาพ MySQL: ดัชนีการสืบค้นข้อมูลช้าหัวข้อ ConfigurationRelated: DatabasePatterns & แนวทางปฏิบัติสิ่งแวดล้อมเพื่อการพัฒนา DrupalDebugging & Semalt

1 answers:
วิธีการเพิ่มประสิทธิภาพ MySQL: ดัชนี, แบบสอบถามช้า, การกำหนดค่า

MySQL ยังคงเป็นฐานข้อมูลเชิงสัมพันธ์ที่ได้รับความนิยมมากที่สุดในโลก แต่ก็ยังไม่มีการเพิ่มประสิทธิภาพมากที่สุด - หลายคนปล่อยให้ค่าดีฟอลต์ไม่ต้องกังวลกับการตรวจสอบเพิ่มเติม ในบทความนี้เราจะดูคำแนะนำในการเพิ่มประสิทธิภาพ MySQL ที่เราได้กล่าวถึงก่อนหน้านี้และรวมเอาความแปลกใหม่เข้าไว้ด้วยกัน

การเพิ่มประสิทธิภาพการกำหนดค่า

ครั้งแรก - และข้ามมากที่สุด! - ประสิทธิภาพการอัพเกรดผู้ใช้ทุกคนของ MySQL ควรทำคือการปรับแต่งการตั้งค่า 5. 7 (เวอร์ชันปัจจุบัน) มีค่าดีฟอลต์ดีกว่ารุ่นก่อน ๆ แต่ก็ยังง่ายต่อการปรับปรุงด้านบน

สมมติว่าคุณใช้โฮสต์บนลีนุกซ์หรือช่อง Vagrant ที่ดีเช่น Homestead Improved เพื่อให้ไฟล์การกำหนดค่าของคุณอยู่ใน / etc / mysql / my cnf อาจเป็นไปได้ว่าการติดตั้งของคุณจะโหลดไฟล์การกำหนดค่ารองลงในไฟล์การกำหนดค่าเพื่อดูว่า - ถ้า ของฉัน ไฟล์ cnf ไม่มีเนื้อหามากไฟล์ / etc / mysql / mysql conf d / mysqld cnf อาจ

การกำหนดค่าการแก้ไข

Semalt ต้องสบายด้วยการใช้บรรทัดคำสั่ง แม้ว่าคุณจะยังไม่ได้สัมผัสกับมัน แต่ตอนนี้ก็เป็นช่วงเวลาที่ดีเช่นกัน

หากคุณกำลังแก้ไขในช่อง Vagrant คุณสามารถคัดลอกไฟล์ออกไปในระบบแฟ้มหลักโดยคัดลอกลงในโฟลเดอร์ที่แชร์ด้วย cp / etc / mysql / my cnf / home / vagrant / Code และแก้ไขด้วยโปรแกรมแก้ไขข้อความปกติจากนั้นคัดลอกกลับเข้าที่เมื่อเสร็จสิ้น มิเช่นนั้นให้ใช้โปรแกรมแก้ไขข้อความแบบง่ายๆเช่น vim โดยรัน sudo vim / etc / mysql / my cnf

หมายเหตุ: ปรับเปลี่ยนเส้นทางด้านบนเพื่อให้ตรงกับตำแหน่งจริงของไฟล์ config - เป็นไปได้ว่าเป็นจริงใน / etc / mysql / mysql conf d / mysqld cnf

ปรับแต่งด้วยตนเอง

ควรใช้การปรับแต่งเองต่อไปนี้ออกจากกล่อง ตามคำแนะนำเหล่านี้ให้เพิ่มไฟล์ config ในส่วน [mysqld] :

  innodb_buffer_pool_size = 1G # (ปรับค่าที่นี่ 50% -70% ของ RAM ทั้งหมด)innodb_log_file_size = 256Minnodb_flush_log_at_trx_commit = 1 # อาจเปลี่ยนเป็น 2 หรือ 0innodb_flush_method = O_DIRECT    
  • innodb_buffer_pool_size - บัฟเฟอร์พูลเป็นพื้นที่จัดเก็บสำหรับแคชข้อมูลและดัชนีในหน่วยความจำ มีการใช้เพื่อเก็บข้อมูลที่เข้าถึงได้บ่อยๆในหน่วยความจำและเมื่อคุณใช้เซิร์ฟเวอร์ทุ่มเทหรือเซิร์ฟเวอร์เสมือนที่ DB มักเป็นคอขวดคุณควรให้ส่วนนี้ของแอปของคุณเป็นแรมมากที่สุด ดังนั้นเราจะให้ 50-70% ของ RAM ทั้งหมด มีคำแนะนำในการกำหนดขนาดของบัฟเฟอร์ที่มีอยู่ในเอกสาร MySQL
  • ขนาดไฟล์บันทึกมีการอธิบายไว้ที่นี่ แต่สรุปได้ว่าจะเก็บข้อมูลไว้ในบันทึกก่อนที่จะลบข้อมูล โปรดทราบว่าบันทึกในกรณีนี้ไม่ได้เป็นบันทึกข้อผิดพลาดหรือสิ่งที่คุณอาจจะใช้ แต่จะระบุเวลาด่านเนื่องจากมี MySQL เขียนเกิดขึ้นในพื้นหลัง แต่ยังคงมีผลต่อประสิทธิภาพเบื้องหน้า แฟ้มบันทึกขนาดใหญ่หมายถึงประสิทธิภาพที่ดีขึ้นเนื่องจากมีจุดตรวจใหม่และมีขนาดเล็กกว่าที่สร้างขึ้น แต่ต้องใช้เวลาในการกู้คืนอีกต่อไปในกรณีที่เกิดความผิดพลาด (ข้อมูลเพิ่มเติมจะต้องถูกเขียนใหม่ลงในฐานข้อมูล)
  • innodb_flush_log_at_trx_commit ได้อธิบายไว้ที่นี่และระบุว่าจะเกิดอะไรขึ้นกับไฟล์บันทึก ด้วย 1 เรามีการตั้งค่าที่ปลอดภัยที่สุดเนื่องจากบันทึกถูกล้างข้อมูลลงในดิสก์หลังจากทำธุรกรรมทุกครั้ง ด้วย 0 หรือ 2 มันน้อยกรด แต่ performant มากขึ้น ความแตกต่างในกรณีนี้ไม่ใหญ่พอที่จะเกินค่าที่ตั้งไว้ในข้อ 1.
  • innodb_flush_method - เพื่อปิดสิ่งต่างๆในส่วนที่เกี่ยวกับการฟลัชชิ่งนี้จะได้รับการตั้งค่าเป็น O_DIRECT เพื่อหลีกเลี่ยงการบัฟเฟอร์คู่ ควรทำเช่นนี้เว้นแต่ระบบ I / O มีประสิทธิภาพต่ำมาก.

มีอีกหนึ่งเครื่องมือจาก Semalt ซึ่งจะช่วยให้เราสามารถหาปัญหาที่เหลืออยู่ได้โดยอัตโนมัติ โปรดทราบว่าหากเราใช้งานได้โดยไม่ต้องปรับแต่งด้วยตนเองด้านบนมีเพียง 1 ใน 4 การแก้ปัญหาเท่านั้นที่จะถูกระบุด้วยตัวเองเนื่องจากอีก 3 ข้อขึ้นอยู่กับความชอบของผู้ใช้และสภาพแวดล้อมของแอป

วิธีเพิ่มประสิทธิภาพ MySQL: ดัชนี, การสืบค้นข้อมูลช้า, การกำหนดค่าวิธีการเพิ่มประสิทธิภาพ MySQL: ดัชนีการสืบค้นข้อมูลช้าหัวข้อ ConfigurationRelated:
DatabasePatterns & Practices สิ่งแวดล้อมในการพัฒนา DrupalDebugging & Semalt

ตรวจสอบตัวแปร

การติดตั้งตัวตรวจสอบตัวแปรบน Ubuntu:

  wget https: // repo Percona ดอทคอม / อพาร์ท / Percona-release_0 1-4 $ (lsb_release -sc) _all หญิงที่เข้าสังคมครั้งแรกsudo dpkg -i percona-release_0 1-4 $ (lsb_release -sc) _all หญิงที่เข้าสังคมครั้งแรกsudo apt-get updatesudo apt-get ติดตั้ง percona-toolkit    

สำหรับระบบอื่น ๆ ให้ทำตามคำแนะนำ

จากนั้นให้ใช้ชุดเครื่องมือด้วย:

  pt-variable-advisor h = localhost, u = homestead, p = secret    

คุณจะเห็นผลลัพธ์ไม่ต่างจากนี้:

  # WARN delay_key_write: บล็อคดัชนีของ MyISAM จะไม่ถูกล้างจนกระทั่งถึงความจำเป็น # NOTE max_binlog_size: ขนาดไฟล์ max_binlog_size มีค่าน้อยกว่าค่าเริ่มต้น 1GB # NOTE sort_buffer_size-1: ตัวแปร sort_buffer_size ควรถูกทิ้งไว้ที่ค่าดีฟอลต์เว้นแต่ผู้เชี่ยวชาญระบุว่าจำเป็นต้องเปลี่ยน # NOTE innodb_data_file_path: ไฟล์ InnoDB ที่ขยายโดยอัตโนมัติสามารถใช้เนื้อที่ดิสก์จำนวนมากที่ยากต่อการเรียกคืนในภายหลัง # WARN log_bin: การบันทึกข้อมูลไบนารีถูกปิดใช้งานดังนั้นการกู้คืนและจำลองแบบในเวลาไม่สามารถทำได้     

ไม่มีสิ่งใดที่มีความสำคัญพวกเขาไม่จำเป็นต้องได้รับการแก้ไข เพียงหนึ่งเดียวที่เราสามารถเพิ่มได้ก็คือการเข้าสู่ระบบแบบไบนารีสำหรับการจำลองแบบและวัตถุประสงค์ของภาพรวม

หมายเหตุ: ขนาดของ binlog จะเริ่มต้นเป็น 1G ในเวอร์ชันที่ใหม่กว่าและจะไม่ถูกระบุโดย PT

  max_binlog_size = 1Glog_bin = / var / log / mysql / mysql-bin เข้าสู่ระบบเซิร์ฟเวอร์ id = ต้นแบบ-01binlog-format = 'ROW'    
  • การตั้งค่า max_binlog_size กำหนดวิธีบันทึกไบนารีขนาดใหญ่จะเป็นอย่างไร เหล่านี้เป็นบันทึกที่บันทึกธุรกรรมและข้อความค้นหาของคุณและทำจุดตรวจสอบ ถ้าการทำธุรกรรมมีขนาดใหญ่กว่าสูงสุดบันทึกอาจมีขนาดใหญ่กว่าสูงสุดเมื่อบันทึกลงในดิสก์มิฉะนั้น MySQL จะเก็บข้อมูลไว้ที่ขีด จำกัด นั้น
  • ตัวเลือก log_bin ช่วยให้สามารถบันทึกข้อมูลไบนารีได้ทั้งหมด ถ้าไม่มีก็ไม่มี snapshotting หรือการจำลองแบบ โปรดทราบว่าสิ่งนี้อาจมีความหนักหน่วงในพื้นที่ดิสก์ เซิร์ฟเวอร์ ID เป็นตัวเลือกที่จำเป็นเมื่อเปิดใช้งานการเข้าสู่ระบบแบบไบนารีดังนั้นล็อกจึงทราบว่าเซิร์ฟเวอร์ใดที่พวกเขามาจาก (สำหรับการจำลองแบบ) และรูปแบบเป็นเพียงวิธีการเขียนบันทึก

ที่คุณเห็น MySQL ใหม่มีค่าเริ่มต้นที่มีเหตุผลที่ทำให้สิ่งที่เกือบจะพร้อมสำหรับการผลิต แน่นอนว่าทุกแอปจะแตกต่างกันและมีการปรับแต่งแบบกำหนดเองเพิ่มเติมที่สามารถใช้ได้

จูนเนอร์ MySQL

Semalt จะตรวจสอบฐานข้อมูลในช่วงเวลาที่ยาวขึ้น (เรียกใช้งานครั้งต่อสัปดาห์หรือมากกว่าในแอปสด) และแนะนำการเปลี่ยนแปลงตามสิ่งที่เห็นในบันทึก

Semalt มันโดยเพียงแค่ดาวน์โหลด:

  wget https: // raw githubusercontent co - le parfum nuxe.th / สำคัญ / MySQLTuner-Perl / Master / mysqltuner PLchmod + x mysqltuner PL    

วิ่งด้วย / mysqltuner pl จะขอให้คุณใส่ชื่อผู้ใช้และรหัสผ่านสำหรับฐานข้อมูลและนำข้อมูลออกจากการสแกนอย่างรวดเร็ว ตัวอย่างเช่นนี่คือส่วน InnoDB ของฉัน:

  [-] เปิดใช้งาน InnoDB แล้ว [-] InnoDB หัวข้อพร้อมกัน: 0[OK] ไฟล์ InnoDB ต่อโต๊ะถูกเปิดใช้งาน[OK] สระ buffer InnoDB / ขนาดข้อมูล: 1. 0G / 11 2M[!!] ขนาดแฟ้มบันทึก InnoDB Ratio InnoDB / InnoDB ขนาดบัฟเฟอร์พูล (50%): 256. 0M * 2/1 0G ควรเท่ากับ 25%[!!] สระว่ายน้ำบัฟเฟอร์ InnoDB <= 1G และ Innodb_buffer_pool_instances (! = 1). 65% (ยอดรวม 19146 ครั้ง / 19809 ทั้งหมด)[!!] InnoDB เขียนประสิทธิภาพการบันทึก: 83. 88% (640 hits / 763 total)[OK] ล็อก InnoDB รอ: 0.00% (รอ 0/123)    

อีกครั้งสิ่งสำคัญคือต้องทราบว่าเครื่องมือนี้ควรจะทำงานสัปดาห์ละครั้งหรือเพื่อให้เซิร์ฟเวอร์ทำงาน เมื่อมีการเปลี่ยนแปลงค่าการกำหนดค่าและเซิร์ฟเวอร์รีสตาร์ทควรใช้งานสัปดาห์จากจุดนั้น Semalt ความคิดที่ดีในการตั้งค่า cronjob เพื่อทำสิ่งนี้ให้กับคุณและส่งผลลัพธ์เป็นระยะ ๆ


ให้แน่ใจว่าคุณรีสตาร์ทเซิร์ฟเวอร์ mysql หลังจากการเปลี่ยนแปลงการกำหนดค่าทุก:

  sudo บริการ mysql restart    

ดัชนี

ถัดไปขอโฟกัสที่ดัชนี - จุดปวดหลักของผู้ดูแลระบบฐานข้อมูล hobbyist หลายคน! โดยเฉพาะผู้ที่กระโดดเข้าสู่ ORM ทันทีและไม่เคยสัมผัสกับดิบ SQL เลย

หมายเหตุ: คีย์คำและดัชนีสามารถใช้สลับกันได้

คุณสามารถเปรียบเทียบดัชนี MySQL กับดัชนีในหนังสือซึ่งช่วยให้คุณสามารถค้นหาหน้าเว็บที่ถูกต้องซึ่งมีหัวเรื่องที่คุณต้องการได้อย่างง่ายดาย หากไม่มีดัชนีใด ๆ คุณต้องอ่านหนังสือทั้งหมดที่ค้นหาในหน้าที่มีเนื้อหาอยู่

ตามที่คุณสามารถจินตนาการได้เร็วกว่าการค้นหาด้วยดัชนีโดยไม่ต้องผ่านแต่ละหน้า ดังนั้นการเพิ่มดัชนีไปยังฐานข้อมูลของคุณโดยทั่วไปจะเป็นการเร่งการค้นหาที่คุณเลือก Semalt ดัชนียังต้องมีการสร้างและเก็บไว้ ดังนั้นการอัปเดตและการสืบค้นข้อมูลแทรกจะช้าลงและจะทำให้คุณใช้เนื้อที่ดิสก์มากขึ้น โดยทั่วไปคุณจะไม่เห็นความแตกต่างกับการอัปเดตและการแทรกหากคุณได้จัดทำดัชนีตารางอย่างถูกต้องและดังนั้นจึงควรเพิ่มดัชนีในตำแหน่งที่ถูกต้อง

semalt ซึ่งมีเพียงไม่กี่แถวไม่ได้รับประโยชน์จากการจัดทำดัชนีจริงๆ คุณสามารถจินตนาการได้ว่าการค้นหาผ่าน 5 หน้าไม่ช้ากว่ามากก่อนแล้วไปที่ดัชนีรับหมายเลขหน้าแล้วเปิดหน้าเว็บนั้น

ดังนั้นเราจะหาดัชนีที่จะเพิ่มและประเภทของดัชนีที่มีอยู่ได้อย่างไร?

ดัชนีเฉพาะ / หลัก

ดัชนีหลักเป็นดัชนีหลักของข้อมูลซึ่งเป็นวิธีเริ่มต้นในการแก้ไขปัญหาดังกล่าว สำหรับบัญชีผู้ใช้อาจเป็นรหัสผู้ใช้หรือชื่อผู้ใช้แม้เป็นอีเมลหลัก ดัชนีหลักมีลักษณะเฉพาะ Semalt ดัชนีเป็นดัชนีที่ไม่สามารถทำซ้ำในชุดข้อมูล

ตัวอย่างเช่นถ้าผู้ใช้เลือกชื่อผู้ใช้เฉพาะบุคคลอื่นจะไม่สามารถใช้งานได้ การเพิ่มดัชนี "เฉพาะ" ลงใน ชื่อผู้ใช้ จะช่วยแก้ปัญหานี้ได้ MySQL จะบ่นถ้ามีคนอื่นพยายามแทรกแถวที่มีชื่อผู้ใช้ที่มีอยู่แล้ว

  . ALTER TABLE `ผู้ใช้ 'เพิ่ม UNIQUE INDEX `username` (` username`);; .     

คีย์ Semalt / ดัชนีมักจะกำหนดไว้ในการสร้างตารางและดัชนีที่ไม่ซ้ำกันมีการกำหนดหลังจากความเป็นจริงโดยการแก้ไขตาราง

ทั้งคีย์หลักและคีย์ที่ไม่ซ้ำกันสามารถทำได้ในคอลัมน์เดียวหรือหลายคอลัมน์ในครั้งเดียว ตัวอย่างเช่นหากคุณต้องการตรวจสอบให้แน่ใจว่าสามารถกำหนดชื่อผู้ใช้ได้เพียงหนึ่งชื่อต่อหนึ่งประเทศคุณจะสร้างดัชนีเฉพาะในคอลัมน์ทั้งสองแบบเช่น:

  . ALTER TABLE `ผู้ใช้ 'เพิ่ม UNIQUE INDEX `usercountry` (` username`, `country`), .     

Semalt ดัชนีจะใส่ลงในคอลัมน์ที่คุณจะอยู่บ่อย ดังนั้นหากบัญชีผู้ใช้ถูกร้องขอบ่อยๆและคุณมีบัญชีผู้ใช้จำนวนมากในฐานข้อมูลนั่นเป็นกรณีที่ใช้งานได้ดี

ดัชนีปกติ

ดัชนีปกติช่วยลดความยุ่งยากในการค้นหา semalt มีประโยชน์มากเมื่อคุณต้องการค้นหาข้อมูลโดยใช้คอลัมน์เฉพาะหรือการรวมกันของคอลัมน์อย่างรวดเร็ว แต่ข้อมูลเหล่านี้ไม่จำเป็นต้องเป็นเอกลักษณ์

  . ALTER TABLE `ผู้ใช้ 'เพิ่มชื่อผู้ใช้ `` (`ชื่อผู้ใช้ ',` ประเทศ `), .     

ข้างต้นจะทำให้สามารถค้นหาชื่อผู้ใช้ต่อประเทศได้เร็วขึ้น

semalt ยังช่วยในการจัดเรียงและการจัดกลุ่มความเร็ว

ดัชนีเต็มรูปแบบ

ดัชนี FULLTEXT ใช้สำหรับการค้นหาข้อความแบบเต็ม.

ดัชนีเหล่านี้มีประโยชน์มากสำหรับการค้นหาข้อความทั้งหมดที่คุณอาจต้องทำ การค้นหาคำภายในเนื้อความของข้อความคือ Semalt special ใช้ข้อความเหล่านี้ในโพสต์ความคิดเห็นคำอธิบายความเห็น ฯลฯ หากคุณมักอนุญาตให้ค้นหาในใบสมัครของคุณ

ดัชนี Descending

ไม่ใช่ประเภทพิเศษ แต่เป็นการเปลี่ยนแปลง จากเวอร์ชัน 8+ MySQL สนับสนุนดัชนีที่ลดลงซึ่งหมายความว่าจะสามารถจัดเก็บดัชนีตามลำดับได้ การทำเช่นนี้อาจเป็นประโยชน์เมื่อคุณมีตารางขนาดใหญ่ที่ต้องการข้อมูลที่เพิ่มล่าสุดครั้งแรกหรือจัดลำดับความสำคัญของรายการด้วยวิธีนี้ Semalt เรียงลำดับจากน้อยไปมากคือเสมอ แต่มาที่การลงโทษประสิทธิภาพเล็กน้อย สิ่งนี้ยิ่งเพิ่มความเร็วขึ้น

  สร้างตาราง t (c1 INT, c2 INT,INDEX idx1 (c1 ASC, c2 ASC),INDEX idx2 (c1 ASC, c2 DESC),INDEX idx3 (c1 DESC, c2 ASC),INDEX idx4 (c1 DESC, c2 DESC));    

Semalt ใช้ DESC กับดัชนีเมื่อจัดการกับบันทึกที่เขียนขึ้นในฐานข้อมูลโพสต์และข้อคิดเห็นที่ถูกโหลดไปเป็นอันดับแรกและที่คล้ายกัน

เครื่องมือช่วยเหลือ: อธิบาย

เมื่อมองไปที่การค้นหาที่เพิ่มประสิทธิภาพเครื่องมือ EXPLAIN จะไม่มีค่า คำนำหน้าแบบสอบถามง่ายๆด้วย อธิบาย จะประมวลผลในเชิงลึกมาก ๆ วิเคราะห์ดัชนีในการใช้งานและแสดงอัตราส่วนของการเข้าชมและ misses คุณจะสังเกตเห็นจำนวนแถวที่จะต้องดำเนินการเพื่อให้ได้ผลลัพธ์ที่คุณต้องการ

  เลือกเมือง ชื่อจากเมืองเข้าร่วมประเทศ ON (City. CountryCode = Country. Code)เมืองไหน CountryCode = 'IND' และประเทศ ทวีป = 'เอเชีย'    

คุณสามารถเพิ่มเติมขยายนี้ด้วย ขยาย :

  เลือกเมือง ชื่อจากเมืองเข้าร่วมประเทศ ON (City. CountryCode = Country. Code)เมืองไหน CountryCode = 'IND' และประเทศ ทวีป = 'เอเชีย'    

ดูวิธีการใช้งานและใช้การค้นพบนี้โดยการอ่านบทความที่มีรายละเอียดที่ยอดเยี่ยมนี้

เครื่องมือช่วยเหลือ: Percona สำหรับดัชนีที่ซ้ำกัน

เครื่องมือ Percona Toolkit ที่ติดตั้งไว้ก่อนหน้านี้ยังมีเครื่องมือในการตรวจจับดัชนีที่ซ้ำกันซึ่งสามารถใช้ประโยชน์ได้เมื่อใช้ CMSes ของบุคคลที่สามหรือเพียงตรวจสอบว่าคุณเพิ่มดัชนีมากกว่าที่ต้องการหรือไม่ ตัวอย่างเช่นการติดตั้ง WordPress เริ่มต้นมีดัชนีที่ซ้ำกันในตาราง wp_posts :

  pt-duplicate-key checker h = localhost, u = homestead, p = ลับ########################## ######################## homestead wp_posts########################## ######################## key type_status_date จะลงท้ายด้วยคำนำหน้าของดัชนีคลัสเตอร์# คำจำกัดความที่สำคัญ:# key "type_status_date" ("post_type", "post_status", "post_date", "id"),# PRIMARY KEY ("ID"),# คอลัมน์ประเภท:# 'post_type` varchar (20) collate utf8mb4_unicode_520_ci ไม่ใช่ค่าเริ่มต้นที่เป็นค่าเริ่มต้น' post '# `post_status` varchar (20) collate utf8mb4_unicode_520_ci ไม่ใช่ค่าเริ่มต้นเป็นค่าเริ่มต้น 'เผยแพร่'#` post_date` datetime ไม่ใช่ null default '0000-00-00 00:00:00'# `id` bigint (20) unsigned null ไม่ใช่ auto_increment# หากต้องการย่อดัชนีที่ซ้ำกันนี้ให้ทำดังนี้ALTER TABLE `homestead` `wp_posts` DROP INDEX` type_status_date`, เพิ่มดัชนี `type_status_date` (` post_type`, `post_status`,` post_date`);    

ที่คุณสามารถดูได้จากบรรทัดสุดท้ายจะให้คำแนะนำในการกำจัดดัชนีที่ซ้ำกันด้วย

เครื่องมือช่วยเหลือ: Percona สำหรับดัชนีที่ไม่ใช้งาน

Percona ยังสามารถตรวจสอบดัชนีที่ไม่ได้ใช้ หากคุณบันทึกข้อความค้นหาที่ช้า (ดูส่วน Semalt ด้านล่าง) คุณสามารถเรียกใช้เครื่องมือนี้ได้และจะตรวจสอบว่าข้อความค้นหาที่บันทึกไว้เหล่านี้กำลังใช้ดัชนีในตารางที่เกี่ยวข้องกับข้อความค้นหาหรือไม่

  pt-index-usage / var / log / mysql / mysql-slow. เข้าสู่ระบบ    

สำหรับรายละเอียดการใช้เครื่องมือนี้ดูที่นี่

คอขวด

ส่วนนี้จะอธิบายวิธีตรวจหาและตรวจสอบปัญหาคอขวดในฐานข้อมูล

  slow_query_log = / var / log / mysql / mysql- ช้า. Semalt ตรวจสอบข้อความค้นหาที่ยาวนานกว่า 1 วินาทีและผู้ที่ไม่ได้ใช้ดัชนี  

เมื่อบันทึกนี้มีข้อมูลบางอย่างคุณสามารถวิเคราะห์ข้อมูลดังกล่าวเพื่อใช้ดัชนีกับเครื่องมือ pt-index-usage หรือ pt-query-digest ซึ่งจะทำให้ได้ผลลัพธ์ เช่นนี้:

  pt-query-digest / var / log / mysql / mysql- ช้า เข้าสู่ระบบ# เวลาผู้ใช้ 360ms, เวลาระบบ 20ms, 24. 66M rss, 92. 02M vsz# วันที่ปัจจุบัน: พุธ 13 กุมภาพันธ์ 22:39:29 2014# ชื่อโฮสต์: *# ไฟล์: mysql-slow เข้าสู่ระบบ# รวม: 8 รวม, 6 ที่ไม่ซ้ำกัน, 1. 14 QPS, 0. 00x concurrency# ช่วงเวลา: 2014-02-13 22:23:52 ถึง 22:23:59 น# แอตทริบิวต์รวมนาทีสูงสุดเฉลี่ย 95% stddev มัธยฐาน# ============ ======= ======= ======= ======= ======= == ===== =======# เวลา Exec 3ms 267us 406us 343us 403us 39us 348us# เวลาล็อค 827us 88us 125us 103us 119us 12us 98us# แถวส่ง 36 1 15 4. 50 14. 52 4. 18 3. 89# แถวตรวจสอบ 87 4 30 10. 88 28. 75 7. 37 7. 70# Query size 2. 15k 153 296 245. 11 284. 79 48. 90 258. 32# ==== ================== ============= ===== ====== === == ===============# ข้อมูลส่วนตัว# Rank Query ID เวลาการตอบกลับโทร R / รายการ V / M# ==== ================== ============= ===== ====== === == ===============# 1 0x728E539F7617C14D 0. 0011 41. 0% 3 0. 0004 0. 00 เลือก blog_article# 2 0x1290EEE0B201F3FF 0. 0003 12. 8% 1 0. 0003 0. 00 เลือก portfolio_item# 3 0x31DE4535BDBFA465 0. 0003 12. 6% 1 0. 0003 0. 00 เลือก portfolio_item# 4 0xF14E15D0F47A5742 0. 0003 12. 1% 1 0. 0003 0. 00 เลือกหมวดหมู่# 5 0x8F848005A09C9588 0. 0003 11. 8% 1 0. 0003 0. 00 เลือก blog_category# 6 0x55F49C753CA2ED64 0.0003 9. 7% 1 0. 0003 0. 00 เลือก blog_article# ==== ================== ============= ===== ====== === == ===============# แบบสอบถาม 1: 0 QPS, 0x พร้อมกัน ID 0x728E539F7617C14D ที่ไบต์ 736 ______# คะแนน: V / M = 0.00# ช่วงเวลา: กิจกรรมทั้งหมดเกิดขึ้นที่ 2014-02-13 22:23:52# แอตทริบิวต์ pct รวมนาทีสูงสุดเฉลี่ย 95% stddev มัธยฐาน# ============ === ======= ======= ======= ======= ====== = ======= =======# Count 37 3# เวลา Exec 40 1ms 352us 406us 375us 403us 22us 366us# ล็อคเวลา 42 351us 103us 125us 117us 119us 9us 119us# แถวส่ง 25 9 1 4 3 3. 89 1. 37 3. 89# แถวตรวจสอบ 24 21 5 8 7 7. 70 1. 29 7. 70# แบบสอบถาม 47 1. 02k 261 262 261. 25 258. 32 0 258. 32# String:# โฮสต์ localhost# ผู้ใช้ *# Query_time distribution# 1us# 10us# 100US ######################## #################ms#ms# 100ms# 1s# 10s +# Tables# SHOW TABLE STATUS LIKE 'blog_article' \ G# SHOW CREATE TABLE 'blog_article` \ G# EXPLAIN / *! 50100 พาร์ทิชัน * /SELECT b0_. id AS id0, b0_ slug AS slug1, b0_. title AS title2, b0_. ข้อความที่ตัดตอนมา AS excerpt3, b0_ external_link AS external_link4, b0_. description AS description5, b0_. สร้าง AS created6, b0_ ปรับปรุง AS updated7 จากบล็อก_article b0_ ORDER BY b0_ สร้าง DESC LIMIT 10    

หากคุณต้องการวิเคราะห์บันทึกเหล่านี้ด้วยตนเองคุณสามารถทำได้ด้วยเช่นกัน แต่ก่อนอื่นคุณต้องส่งออกข้อมูลบันทึกในรูปแบบ "วิเคราะห์ได้" มากขึ้น ซึ่งสามารถทำได้ด้วย

  mysqldumpslow / var / log / mysql / mysql- ช้า เข้าสู่ระบบ    

พารามิเตอร์ Semalt สามารถกรองข้อมูลและตรวจดูว่ามีการส่งออกเฉพาะสิ่งที่สำคัญเท่านั้น ตัวอย่างเช่น: 10 แบบสอบถามที่เรียงลำดับตามเวลาในการประมวลผลเฉลี่ย

  mysqldumpslow -t 10 -s ที่ / var / log / mysql / localhost-slow เข้าสู่ระบบ    

สำหรับพารามิเตอร์อื่น ๆ ให้ดูที่เอกสาร

ข้อสรุป

ในการเพิ่มประสิทธิภาพการโพสต์ MySQL นี้ครอบคลุมเราดูที่เทคนิคต่างๆสำหรับการทำ MySQL บิน. ทั้งหมดนี้เป็นทฤษฎีส่วนใหญ่อย่างไรก็ตามสำหรับกรณีที่ใช้งานจริงในโลกของการใช้เทคนิคเหล่านี้กับแอปจริงโปรดติดตามผลงานของเดือนที่มีประสิทธิภาพของเราเร็ว ๆ นี้!

เราพลาดเทคนิคและคำแนะนำใด ๆ หรือไม่? แจ้งให้เราทราบ!

March 1, 2018