SQL �Ż�������ǿ��ָ�����ӳ����ض������ӡ����Ը������һ���Ż���ȥʹ���ض��ķ��������ӣ� �������Խ�����������л����������״̬�仯���⡣
SQL hints �ǻ��� SQL ���ġ���Ϊ SQL ע�ͻᱻ SQL ִ�������ԣ� ����������� MySQL ��������MySQL proxy �����κη���ǽ�Ĺ�����
���֧�� SQL hints��MYSQLND_MS_MASTER_SWITCH
hints
����ָ������� master �����У�MYSQLND_MS_SLAVE_SWITCH
����ָ���� slave �����У�MYSQLND_MS_LAST_USED_SWITCH
����ָ���������һ�����ִ�е� slave �����С�
�����ɨ���������ݣ���ѯ�Ƿ��� SQL hints����ֻ�������Ŀ�ͷ��ʶ��
Example #1 ����һ�� master ��һ�� slave �IJ��
{ "myapp": { "master": { "master_0": { "host": "localhost", "socket": "\/tmp\/mysql.sock" } }, "slave": { "slave_0": { "host": "192.168.2.27", "port": "3306" } } } }
Example #2 SQL hints ��ֹ�����л�
<?php
$mysqli = new mysqli("myapp", "username", "password", "database");
if (mysqli_connect_errno())
/* Of course, your error handling is nicer... */
die(sprintf("[%d] %s\n", mysqli_connect_errno(), mysqli_connect_error()));
/* ���� 1������һ�� SQL �û�������û�� SELECT ���������� master �� */
if (!$mysqli->query("SET @myrole='master'")) {
printf("[%d] %s\n", $mysqli->errno, $mysqli->error);
}
/* ���� 1����Ϊָ���� SQL hint */
if (!($res = $mysqli->query(sprintf("/*%s*/SELECT @myrole AS _role", MYSQLND_MS_LAST_USED_SWITCH)))) {
printf("[%d] %s\n", $mysqli->errno, $mysqli->error);
} else {
$row = $res->fetch_assoc();
$res->close();
printf("@myrole = '%s'\n", $row['_role']);
}
$mysqli->close();
?>
�������̻������
@myrole = 'master'
����ķ���ʹ�� MYSQLND_MS_LASTER_USED_SWITCH
����ֹ�� master
�� slave ֮������л�����ʹ������ SELECT ��ͷ����䡣
SQL hints Ҳ���������� master ������ SELECT ��䡣 �⾭�������� slave �е���������� master������ϣ����ȡ��ǰ���ݵ�ʱ��
�� 1.2.0 �汾�У������˷���ĸ�����ڴ���ʱ���ݻ�ȡ�����⡣ ʹ�÷��𣬿��Խ��Ͷ��������Ĺ�ע����� SQL hints ��ʹ�á� �����ڷ����һ���Ե��½����ҵ������˵����
Example #3 ��ͬ���ӳٶ���
<?php
$mysqli = new mysqli("myapp", "username", "password", "database");
if (!$mysqli)
/* Of course, your error handling is nicer... */
die(sprintf("[%d] %s\n", mysqli_connect_errno(), mysqli_connect_error()));
/* Force use of master, master has always fresh and current data */
if (!$mysqli->query(sprintf("/*%s*/SELECT critical_data FROM important_table", MYSQLND_MS_MASTER_SWITCH))) {
printf("[%d] %s\n", $mysqli->errno, $mysqli->error);
}
?>
��ʹ���У�slave ��Ӧ����ͬ���ı�����û�и��� SQL hints ��ʱ����ڷ���
CREATE INSERT ��䵽 master ��������
����ͨ���趨 MYSQLND_MS_SLAVE_SWITCH
�����������
slave �����С����罨��һ����ʱ����
Example #4 Table creation on a slave
<?php
$mysqli = new mysqli("myapp", "username", "password", "database");
if (!$mysqli)
/* Of course, your error handling is nicer... */
die(sprintf("[%d] %s\n", mysqli_connect_errno(), mysqli_connect_error()));
/* ǿ���� slave ���� */
if (!$mysqli->query(sprintf("/*%s*/CREATE TABLE slave_reporting(id INT)", MYSQLND_MS_SLAVE_SWITCH))) {
printf("[%d] %s\n", $mysqli->errno, $mysqli->error);
}
/* ʹ�øո�ʹ�õ� slave ���� */
if (!$mysqli->query(sprintf("/*%s*/INSERT INTO slave_reporting(id) VALUES (1), (2), (3)", MYSQLND_MS_LAST_USED_SWITCH))) {
printf("[%d] %s\n", $mysqli->errno, $mysqli->error);
}
/* ��ʹ�� MYSQLND_MS_SLAVE_SWITCH �����л�������һ̨ slave */
if ($res = $mysqli->query(sprintf("/*%s*/SELECT COUNT(*) AS _num FROM slave_reporting", MYSQLND_MS_LAST_USED_SWITCH))) {
$row = $res->fetch_assoc();
$res->close();
printf("There are %d rows in the table 'slave_reporting'", $row['_num']);
} else {
printf("[%d] %s\n", $mysqli->errno, $mysqli->error);
}
$mysqli->close();
?>
SQL hint MYSQLND_MS_LAST_USED
��ֹ�л����ӣ�
����ʹ����һ��ʹ�õ����ӡ�