11 Feb 2018

Panduan dan Tips Menetapkan Indeks pada Jadual MySQL

Indeks pada Jadual MySQL

MySQL akan membuat carian baris demi baris setiap kali kita membuat carian menggunakan arahan WHERE. Masa untuk carian ini akan menjadi lebih panjang setelah jadual kita menjadi semakin besar dan data semakin banyak.
Fungsi indeks digunakan untuk mengurangkan masa carian dan mengelakkan MySQL daripada membuat carian keseluruh baris dalam jadual (full table scan) kerana ianya akan memakan masa, memori dan pemprosesan yang tinggi.
Indeks akan mengambil data daripada kolum di jadual anda, menyusun semula mengikut susuan huruf atau nombor secara menaik mengikut jenis data.
MySQL akan membuat carian pada indeks terlebih dahulu, kemudian barulah ia mengambil baris yang spesifik pada jadual sebenar. Baris yang lain, diabaikan

Apa tujuan kita meletakkan indeks di jadual MySQL?

  • Carian lebih laju
  • Baris yang tidak berkenaan, diabaikan
  • Jika indeks ada banyak kolum, mana mana satu gabungan kolum dengan kolum pertama boleh dijadikan satu indeks.
  • Contoh:
indeks : | col a | col b | col c | col d |
1 : | col a | col b |
2 : | col a | col c |
3 : | col a | col d |
  • mySQL JOINS lebih cepat. Lebih efektif jika indeks pada kedua-dua jadual yang melakukan JOINS adalah daripada jenis dan saiz yang sama.
  • Pencarian MIN() dan MAX() sesuatu kolum yang lebih cepat menyusun (SORT) dan menumpulkan (GROUP) dengan lebih cepat

Bagaimana cara melakukan Indeks?

Yang ni aku akan unjuk berdasarkan contoh.
  • Jika anda menggunakan framework seperti laravel, django atau Ruby on Rails, sila ikuti panduan framework masing-masing untuk meletakkan indeks pada kolum.
Berikut adalah cara-cara dengan menggunakan pernyataan SQL
Katakan kita ada jadual Patients (pesakit):
Jadual patients:
idmrnfirst_namelast_namenrictown_id
1A219323AhmadAbu2011250365511
2A219324LisaIsmail3411250365512
3A219325KarenHart4411250365511
4A219326Ahmad KhairiJalil5411250365511
CREATE TABLE `patients` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `mrn` varchar(7) NOT NULL,
  `first_name` varchar(255) DEFAULT NULL,
  `last_name` varchar(255) DEFAULT NULL,
  `nric` varchar(15) NOT NULL,
  `town_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB
Sistem kita akan membuat carian ke atas jadual ini menggunakan SQL berikut:
  • carian pesakit menggunakan id
  • carian pesakit menggunakan mrn
  • carian pesakit menggunakan first_name
  • carian pesakit menggunakan nric
  • carian pesakit mengikut bandar (untuk statistik)
Setiap pernyataan SQL ini membuat carian menggunakan kolum tunggal, maka kita kena pastikan
 >  1. letak indeks pada kolum yang kita lakukan carian
Carian yang menggunakan kolum id tidak perlu dilakukan apa-apa tindakan kerana telah sebarang carian dengan kueri id = "nilai" adalah khusus kepada baris tersebut sahaja. Ini adalah cara membuat carian yang paling optimum dan terbaik.
Kolum kedua, ketiga, kelima dan keenam masih belum dikalifikasikan sebagai indeks.
Cara untuk menetapkan kolum tersebut sebagai indeks:
CREATE INDEX by_mrn ON patients (`mrn`);
CREATE INDEX by_first_name ON patients (`first_name`);
CREATE INDEX by_nric ON patients (`nric`);
CREATE INDEX by_town_id ON patients (`town_id`);
dengan ini, setiap carian yang menggunakan kolum tersebut akan menjadi lebih cepat dan efisyen.

Bagaimana pula dengan carian banyak kolum?

seperti contoh di atas tu, katakanlah sql nya macam ni:
select * from patients where first_name like ("Muhd%") and town_id = 1
Pernyataan tersebut akan menyenaraikan semua pesakit yang namanya bermula dengan Muhd dan tinggal di bandar 1, katakanlah town_id=1 adalah kota bharu.
Oleh kerana mungkin terdapat ramai pesakit yang bernama tersebut di Kota Bharu, maka carian ini akan menjadi perlahan. Oleh itu, kita boleh gabungkan beberapa kolum untuk menjadi indeks.
DROP INDEX by_town_id ON patients;
CREATE INDEX by_town_id_and_first_name ON patients (town_id, first_name);
atau
DROP INDEX by_first_name ON patients;
CREATE INDEX by_first_name_and_town_id ON patients (first_name, town_id);
Kita boleh buang indeks tunggal pada kolum town_id kerana sebarang indeks gabungan kolum akan turut berfungsi sebagai indek tunggal mengikut kolum yang paling kiri.
Kenapa kita tak buang indeks first_name? Kerana MySQL hanya akan mengenali indeks mengikut nama kolum yang pertama.
Jadi, indeks dibezakan mengikut nama kolum pertama. Jika anda ada banyak indeks pada jadual tapi indeks tersebut bermula dengan kolum yang sama, anda hanya perlu tinggalkan satu sahaja yang dapat merangkumi semua.

Jadual Terhubung (JOINS)

SELECT p.*, t.name
FROM `patients` p 
LEFT JOIN `towns` t on p.town_id = t.id
WHERE p.first_name like ("Ahmad%") and p.town_id = 2
oleh kerana kita telah membuat carian mengikut town_id dalam contoh sebelum ini, kita telahpun meletakkan indeks pada kolum tersebut.
Satu contoh lain penggunaan indeks adalah apabila kita menggabungkan carian dengan jadual lain menggunakan pernyataan JOINS
setiap kolum yang kita gunakan dalam carian JOINS perlulah diindekskan

Jadi, kolum mana biasanya kita indeks?

  1. Kolum yang menjadi rujukan kepada jadual lain (biasanya diakhiri oleh _id)
  2. kolum untuk carian seperti nama, nostaf, no pendaftaran, no kereta, no pelajar, nama jenama dll.
  3. mana-mana kolum yang digunakan dalam URL.

Peningkatan Prestasi Pangkalan Data apabila menggunakan indeks

  • menulis (write) menjadi lebih laju
  • carian lebih laju
  • join lebih laju
Sekian dahulu, semoga bermanfaat


4 Feb 2018

Banyak Pangkalan Data MySQL dalam Satu Komputer Pelayan

Pasang 2 atau lebih Pangkalan Data MySQL dalam Satu Komputer Pelayan

Prolog: Persekitaran

  1. OS: Debian 8.9
  2. MySQL: Versi 5.5
  3. kebenaran super user

Peringatan! sebelum mengubah apa-apa tetapan

salin dulu fail tetapan asal untuk backup
me@local$:\>sudo cp /etc/mysql/my.cnf /etc/mysql/my.cnf.backup

konfigurasi

mysqld_multi --example
Contoh lengkap untuk konfigurasi mysql multi adalah dengan arahan di atas
me@local$\>sudo mysqld_multi --example
# This is an example of a my.cnf file for mysqld_multi.
# Usually this file is located in home dir ~/.my.cnf or /etc/my.cnf
#
# SOME IMPORTANT NOTES FOLLOW:
#
# 1.COMMON USER
#
#   Make sure that the MySQL user, who is stopping the mysqld services, has
#   the same password to all MySQL servers being accessed by mysqld_multi.
#   This user needs to have the 'Shutdown_priv' -privilege, but for security
#   reasons should have no other privileges. It is advised that you create a
#   common 'multi_admin' user for all MySQL servers being controlled by
#   mysqld_multi. Here is an example how to do it:
#
#   GRANT SHUTDOWN ON *.* TO multi_admin@localhost IDENTIFIED BY 'password'
#
#   You will need to apply the above to all MySQL servers that are being
#   controlled by mysqld_multi. 'multi_admin' will shutdown the servers
#   using 'mysqladmin' -binary, when 'mysqld_multi stop' is being called.
#
# 2.PID-FILE
#
#   If you are using mysqld_safe to start mysqld, make sure that every
#   MySQL server has a separate pid-file. In order to use mysqld_safe
#   via mysqld_multi, you need to use two options:
#
#   mysqld=/path/to/mysqld_safe
#   ledir=/path/to/mysqld-binary/
#
#   ledir (library executable directory), is an option that only mysqld_safe
#   accepts, so you will get an error if you try to pass it to mysqld directly.
#   For this reason you might want to use the above options within [mysqld#]
#   group directly.
#
# 3.DATA DIRECTORY
#
#   It is NOT advised to run many MySQL servers within the same data directory.
#   You can do so, but please make sure to understand and deal with the
#   underlying caveats. In short they are:
#   - Speed penalty
#   - Risk of table/data corruption
#   - Data synchronising problems between the running servers
#   - Heavily media (disk) bound
#   - Relies on the system (external) file locking
#   - Is not applicable with all table types. (Such as InnoDB)
#     Trying so will end up with undesirable results.
#
# 4.TCP/IP Port
#
#   Every server requires one and it must be unique.
#
# 5.[mysqld#] Groups
#
#   In the example below the first and the fifth mysqld group was
#   intentionally left out. You may have 'gaps' in the config file. This
#   gives you more flexibility.
#
# 6.MySQL Server User
#
#   You can pass the user=... option inside [mysqld#] groups. This
#   can be very handy in some cases, but then you need to run mysqld_multi
#   as UNIX root.
#
# 7.A Start-up Manage Script for mysqld_multi
#
#   In the recent MySQL distributions you can find a file called
#   mysqld_multi.server.sh. It is a wrapper for mysqld_multi. This can
#   be used to start and stop multiple servers during boot and shutdown.
#
#   You can place the file in /etc/init.d/mysqld_multi.server.sh and
#   make the needed symbolic links to it from various run levels
#   (as per Linux/Unix standard). You may even replace the
#   /etc/init.d/mysql.server script with it.
#
#   Before using, you must create a my.cnf file either in /usr/my.cnf
#   or /root/.my.cnf and add the [mysqld_multi] and [mysqld#] groups.
#
#   The script can be found from support-files/mysqld_multi.server.sh
#   in MySQL distribution. (Verify the script before using)
#

[mysqld_multi]
mysqld     = /usr/bin/mysqld_safe
mysqladmin = /usr/bin/mysqladmin
user       = multi_admin
password   = my_password

[mysqld2]
socket     = /tmp/mysql.sock2
port       = 3307
pid-file   = /var/lib/mysql2/hostname.pid2
datadir    = /var/lib/mysql2
language   = /usr/share/mysql/mysql/english
user       = unix_user1

[mysqld3]
mysqld     = /path/to/mysqld_safe
ledir      = /path/to/mysqld-binary/
mysqladmin = /path/to/mysqladmin
socket     = /tmp/mysql.sock3
port       = 3308
pid-file   = /var/lib/mysql3/hostname.pid3
datadir    = /var/lib/mysql3
language   = /usr/share/mysql/mysql/swedish
user       = unix_user2

[mysqld4]
socket     = /tmp/mysql.sock4
port       = 3309
pid-file   = /var/lib/mysql4/hostname.pid4
datadir    = /var/lib/mysql4
language   = /usr/share/mysql/mysql/estonia
user       = unix_user3

[mysqld6]
socket     = /tmp/mysql.sock6
port       = 3311
pid-file   = /var/lib/mysql6/hostname.pid6
datadir    = /var/lib/mysql6
language   = /usr/share/mysql/mysql/japanese
user       = unix_user4
Kita akan dapat melihat satu contoh konfigurasi fail yang lengkap untuk memasang banyak pelayan pangkalan data mysql dalam satu PC / Komputer pelayan.
berikut adalah contoh konfigurasi yang aku gunakan (betul punya ni).

Jadikan tetapan ini adalah tetapan baru dalam /etc/mysql/my.cnf

[mysqld_multi]
mysqld          = /usr/bin/mysqld_safe
mysqladmin      = /usr/bin/mysqladmin
user            = multi_admin
password        = MultiAdminPassword

[mysqld]
user            = mysql
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
port            = 3306
datadir         = /var/lib/mysql

[mysqld2]
socket          = /tmp/mysql.sock2
port            = 3307
pid-file        = /var/lib/mysql2/hostname.pid2
datadir         = /var/lib/mysql2
user            = multi_admin


[mysqld3]
socket          = /tmp/mysql.sock3
port            = 3308
pid-file        = /var/lib/mysql3/hostname.pid3
datadir         = /var/lib/mysql3
user            = multi_admin


[mysqld4]
socket          = /tmp/mysql.sock4
port            = 3309
pid-file        = /var/lib/mysql4/hostname.pid4
datadir         = /var/lib/mysql4
user            = multi_admin


[mysqld5]
socket          = /tmp/mysql.sock5
port            = 3310
pid-file        = /var/lib/mysql5/hostname.pid5
datadir         = /var/lib/mysql5
user            = multi_admin



basedir         = /usr
bindir          = /usr/bin
bind-address    = 0.0.0.0


[mysqld_safe]
syslog
Mulakan semula servis mysql anda
me@local:$\> sudo service mysql restart
Cuba lihat proses yang sedang berjalan dalam komputer pelayan anda.
me@local:$\>$\> ps axf | grep mysql
30043 pts/5    S+     0:00  |   \_ grep mysql
18147 pts/5    S      0:00 /bin/sh /usr/bin/mysqld_safe --socket=/tmp/mysql.sock2 --port=3307 --pid-file=/var/lib/mysql2/hostname.pid2 --datadir=/var/lib/mysql2 --user=mysql
18516 pts/5    Sl     0:01  \_ /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql2 --plugin-dir=/usr/lib/mysql/plugin --user=mysql --log-error=diman.err --pid-file=/var/lib/mysql2/hostname.pid2 --socket=/tmp/mysql.sock2 --port=3307
18168 pts/5    S      0:00 /bin/sh /usr/bin/mysqld_safe --socket=/tmp/mysql.sock4 --port=3309 --pid-file=/var/lib/mysql4/hostname.pid4 --datadir=/var/lib/mysql4 --user=mysql
18544 pts/5    Sl     0:01  \_ /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql4 --plugin-dir=/usr/lib/mysql/plugin --user=mysql --log-error=diman.err --pid-file=/var/lib/mysql4/hostname.pid4 --socket=/tmp/mysql.sock4 --port=3309
18182 pts/5    S      0:00 /bin/sh /usr/bin/mysqld_safe --socket=/tmp/mysql.sock5 --port=3310 --pid-file=/var/lib/mysql5/hostname.pid5 --datadir=/var/lib/mysql5 --user=mysql
18547 pts/5    Sl     0:01  \_ /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql5 --plugin-dir=/usr/lib/mysql/plugin --user=mysql --log-error=diman.err --pid-file=/var/lib/mysql5/hostname.pid5 --socket=/tmp/mysql.sock5 --port=3310
23621 pts/5    S      0:00 /bin/sh /usr/bin/mysqld_safe --socket=/tmp/mysql.sock3 --port=3308 --pid-file=/var/lib/mysql3/hostname.pid3 --datadir=/var/lib/mysql3 --user=mysql
23819 pts/5    Sl     0:01  \_ /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql3 --plugin-dir=/usr/lib/mysql/plugin --user=mysql --log-error=diman.err --pid-file=/var/lib/mysql3/hostname.pid3 --socket=/tmp/mysql.sock3 --port=3308
26504 ?        S      0:00 /bin/sh /usr/bin/mysqld_safe
26640 ?        Sl     0:01  \_ /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql --pid-file=/var/run/mysqld/mysqld.pid --socket=/var/run/mysqld/mysqld.sock --port=3306
26641 ?        S      0:00  \_ logger -t mysqld -p daemon.error
Nampaknya semua servis mysql telah berjalan.

Bina jadual asas dalam setiap pangakalan data tadi

untuk setiap pangkalan data yang telah dipasang, kita perlu isikan / install jadual-jadual asas untuk mysql
me@local$\> sudo mysql_install_db --user=mysql --datadir=/var/lib/mysql2
me@local$\> sudo mysql_install_db --user=mysql --datadir=/var/lib/mysql3
me@local$\> sudo mysql_install_db --user=mysql --datadir=/var/lib/mysql4
me@local$\> sudo mysql_install_db --user=mysql --datadir=/var/lib/mysql5

Kemudian, kita perlu setkan password untuk root

me@local$\> /usr/bin/mysqladmin -u root password "mysql3307" --port=3307 --socket=/tmp/mysql.sock2

me@local$\> /usr/bin/mysqladmin -u root password "mysql3308" --port=3308 --socket=/tmp/mysql.sock3

me@local$\> /usr/bin/mysqladmin -u root password "mysql3309" --port=3309 --socket=/tmp/mysql.sock4

me@local$\> /usr/bin/mysqladmin -u root password "mysql3310" --port=3310 --socket=/tmp/mysql.sock5

!CUBA sambung ke server

me@local$\> mysql -u root -p --socket=/tmp/mysql.sock2
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 27
Server version: 5.5.57-0+deb8u1 (Debian)

Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

Seterusnya, untuk setiap pelayan mysql, kita perlu setkan satu nama pengguna khas untuk kegunaan mysqld_multi sepertimana yang kita setkan dalam /etc/mysql.mysq.cnf

untuk setiap server
  1. log in ke server
$\> mysql -u root -p --port=3307 --socket=/tmp/mysql.sock2
$\> mysql -u root -p --port=3308 --socket=/tmp/mysql.sock3
$\> mysql -u root -p --port=3309 --socket=/tmp/mysql.sock4
$\> mysql -u root -p --port=3310 --socket=/tmp/mysql.sock5
  1. tambah pengguna multi_admin dan berikan kebenaran untuk hentikan servis kepada pengguna tersebut
GRANT SHUTDOWN ON *.* TO 'multi_admin'@'localhost' IDENTIFIED BY 'multi_admin_multipass';
contoh lengkap:
$\> mysql -u root -p --port=3307 --socket=/tmp/mysql.sock2
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 31
Server version: 5.5.57-0+deb8u1 (Debian)

Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> GRANT SHUTDOWN ON *.* TO 'multi_admin'@'localhost' IDENTIFIED BY 'multi_admin_multipass';
  • kesannya, kita boleh gunakan arahan mysqld_multi untuk hentikan server.

mysql juga ada arahan khusus untuk ini iaitu mysqld_multi

Jom cuba.
# status / laporan
me@local:$\>sudo mysqld_multi report
Reporting MySQL servers
MySQL server from group: mysqld2 is running
MySQL server from group: mysqld3 is running
MySQL server from group: mysqld4 is running
MySQL server from group: mysqld5 is running

# laporan khusus
me@local:$\> sudo mysqld_multi report 2
Reporting MySQL servers
MySQL server from group: mysqld2 is running

# mula servis
me@local:$\> sudo mysqld_multi start

# matikan servis
me@local:$\> sudo mysqld_multi stop

# matikan servis server khusus
me@local:$\> sudo mysqld_multi stop 2
me@local:$\> sudo mysqld_multi stop 2
me@local:$\> sudo mysqld_multi report
Reporting MySQL servers
MySQL server from group: mysqld2 is not running
MySQL server from group: mysqld3 is running
MySQL server from group: mysqld4 is running
MySQL server from group: mysqld5 is running

# mula servis server tertentu
me@local:$\> sudo mysqld_multi start 2

me@local:$\> sudo mysqld_multi report
Reporting MySQL servers
MySQL server from group: mysqld2 is running
MySQL server from group: mysqld3 is running
MySQL server from group: mysqld4 is running
MySQL server from group: mysqld5 is running

Tips tambahan: PHPMYADMIN

Dalam tetapan phpmyadmin, iaitu di /var/www/html/phpmyadmin/config.inc.php
tambah dalam bilangan server
/*
 * localhost
 */

$cfg['Servers'][$i]['auth_type'] = 'cookie';
$cfg['Servers'][$i]['host'] = 'localhost';
$cfg['Servers'][$i]['connect_type'] = 'tcp';
$cfg['Servers'][$i]['verbose'] = 'localhost';
$cfg['Servers'][$i]['extension'] = 'mysql';

/*
 * localhost2
 */

$cfg['Servers'][$i]['auth_type'] = 'cookie';
$cfg['Servers'][$i]['host'] = '127.0.0.1';
$cfg['Servers'][$i]['port'] = '3307';
$cfg['Servers'][$i]['connect_type'] = 'tcp';
$cfg['Servers'][$i]['verbose'] = 'localhost:3307';
$cfg['Servers'][$i]['extension'] = 'mysql';

/*
 * localhost3
 */
$cfg['Servers'][$i]['auth_type'] = 'cookie';
$cfg['Servers'][$i]['host'] = '127.0.0.1';
$cfg['Servers'][$i]['port'] = '3308';
$cfg['Servers'][$i]['connect_type'] = 'tcp';
$cfg['Servers'][$i]['verbose'] = 'localhost:3308';
$cfg['Servers'][$i]['extension'] = 'mysql';

/*
 * localhost2
 */
$cfg['Servers'][$i]['auth_type'] = 'cookie';
$cfg['Servers'][$i]['host'] = '127.0.0.1';
$cfg['Servers'][$i]['port'] = '3309';
$cfg['Servers'][$i]['connect_type'] = 'tcp';
$cfg['Servers'][$i]['verbose'] = 'localhost:3309';
$cfg['Servers'][$i]['extension'] = 'mysql';

/*
 * localhost2
 */
$cfg['Servers'][$i]['auth_type'] = 'cookie';
$cfg['Servers'][$i]['host'] = '127.0.0.1';
$cfg['Servers'][$i]['port'] = '3310';
$cfg['Servers'][$i]['connect_type'] = 'tcp';
$cfg['Servers'][$i]['verbose'] = 'localhost:3310';
$cfg['Servers'][$i]['extension'] = 'mysql';


Untuk apakah ini semua?


1. Aku sedang test mysql multi master replication
2. test HAproxy
3. hari ni terjumpa http://www.proxysql.com/compare yang memang nampak menarik dan tertarik.. jadi aku rasa nak cuba. Nanti kalau ada masa aku buat tutorial untuk setting server proxysql ni.


Itu sahaja. Moga-moga bermanfaat.