MySQL-5.6+MySQL-Proxy构建主从复制与读写分离

首頁

資料庫

mysql教程

MySQL-5.6+MySQL-Proxy构建主从复制与读写分离_MySQL

WBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWB

Jun 01, 2016 pm 01:16 PM

mysql使用者

环境：

系统：CentOS-6.5

MySQL-master

IP:192.168.1.10

MySQL-slave

IP:192.168.1.11

Proxry

IP:192.167.1.12

——安装MySQL

1）创建mysql用户

[root@mater ~]# mysql -M -s /sbin/nologin mysql

[root@master ~]# yum install gcc gcc-c++ autoconf automake ncurses-devel libtool-ltdl-devel* libaio libaio-devel -y

3）解压安装MySQL [这里下载的是免安装版本]

[root@master linux]# tar fzvx mysql-5.6.16-linux-x86_64.tar.gz -C /usr/local/

[root@master linux]# mv mysql-5.6.16-linux-x86_64 mysql

[root@master local]# cd mysql/

[root@master mysql]# chown -R root:mysql .

[root@master mysql]# chown -R mysql:mysql data/

[root@master mysql]# ./scripts/mysql_install_db --user=mysql /

> --basedir=/usr/local/mysql/ /

> --datadir=/usr/local/mysql/data/

WARNING: The host 'master' could not be looked up with /usr/local/mysql//bin/resolveip.

This probably means that your libc libraries are not 100 % compatible

with this binary MySQL version. The MySQL daemon, mysqld, should work

normally with the exception that host name resolving will not work.

This means that you should use IP addresses instead of hostnames

when specifying MySQL privileges !

Installing MySQL system tables...2014-04-26 19:57:06 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).

2014-04-26 19:57:06 1258 [Note] InnoDB: Using atomics to ref count buffer pool pages

2014-04-26 19:57:06 1258 [Note] InnoDB: The InnoDB memory heap is disabled

2014-04-26 19:57:06 1258 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins

2014-04-26 19:57:06 1258 [Note] InnoDB: Compressed tables use zlib 1.2.3

2014-04-26 19:57:06 1258 [Note] InnoDB: Using Linux native AIO

2014-04-26 19:57:06 1258 [Note] InnoDB: Using CPU crc32 instructions

2014-04-26 19:57:06 1258 [Note] InnoDB: Initializing buffer pool, size = 128.0M

2014-04-26 19:57:07 1258 [Note] InnoDB: Completed initialization of buffer pool

2014-04-26 19:57:07 1258 [Note] InnoDB: The first specified data file ./ibdata1 did not exist: a new database to be created!

2014-04-26 19:57:07 1258 [Note] InnoDB: Setting file ./ibdata1 size to 12 MB

2014-04-26 19:57:07 1258 [Note] InnoDB: Database physically writes the file full: wait...

2014-04-26 19:57:07 1258 [Note] InnoDB: Setting log file ./ib_logfile101 size to 48 MB

2014-04-26 19:57:11 1258 [Note] InnoDB: Setting log file ./ib_logfile1 size to 48 MB

2014-04-26 19:57:15 1258 [Note] InnoDB: Renaming log file ./ib_logfile101 to ./ib_logfile0

2014-04-26 19:57:15 1258 [Warning] InnoDB: New log files created, LSN=45781

2014-04-26 19:57:15 1258 [Note] InnoDB: Doublewrite buffer not found: creating new

2014-04-26 19:57:15 1258 [Note] InnoDB: Doublewrite buffer created

2014-04-26 19:57:15 1258 [Note] InnoDB: 128 rollback segment(s) are active.

2014-04-26 19:57:15 1258 [Warning] InnoDB: Creating foreign key constraint system tables.

2014-04-26 19:57:16 1258 [Note] InnoDB: Foreign key constraint system tables created

2014-04-26 19:57:16 1258 [Note] InnoDB: Creating tablespace and datafile system tables.

2014-04-26 19:57:16 1258 [Note] InnoDB: Tablespace and datafile system tables created.

2014-04-26 19:57:16 1258 [Note] InnoDB: Waiting for purge to start

2014-04-26 19:57:16 1258 [Note] InnoDB: 5.6.16 started; log sequence number 0

2014-04-26 19:57:16 1258 [Note] RSA private key file not found: /usr/local/mysql/data//private_key.pem. Some authentication plugins will not work.

2014-04-26 19:57:16 1258 [Note] RSA public key file not found: /usr/local/mysql/data//public_key.pem. Some authentication plugins will not work.

2014-04-26 19:57:17 1258 [Note] Binlog end

2014-04-26 19:57:17 1258 [Note] InnoDB: FTS optimize thread exiting.

2014-04-26 19:57:17 1258 [Note] InnoDB: Starting shutdown...

2014-04-26 19:57:19 1258 [Note] InnoDB: Shutdown completed; log sequence number 1625977

Filling help tables...2014-04-26 19:57:19 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).

2014-04-26 19:57:19 1281 [Note] InnoDB: Using atomics to ref count buffer pool pages

2014-04-26 19:57:19 1281 [Note] InnoDB: The InnoDB memory heap is disabled

2014-04-26 19:57:19 1281 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins

2014-04-26 19:57:19 1281 [Note] InnoDB: Compressed tables use zlib 1.2.3

2014-04-26 19:57:19 1281 [Note] InnoDB: Using Linux native AIO

2014-04-26 19:57:19 1281 [Note] InnoDB: Using CPU crc32 instructions

2014-04-26 19:57:19 1281 [Note] InnoDB: Initializing buffer pool, size = 128.0M

2014-04-26 19:57:19 1281 [Note] InnoDB: Completed initialization of buffer pool

2014-04-26 19:57:19 1281 [Note] InnoDB: Highest supported file format is Barracuda.

2014-04-26 19:57:19 1281 [Note] InnoDB: 128 rollback segment(s) are active.

2014-04-26 19:57:19 1281 [Note] InnoDB: Waiting for purge to start

2014-04-26 19:57:19 1281 [Note] InnoDB: 5.6.16 started; log sequence number 1625977

2014-04-26 19:57:19 1281 [Note] RSA private key file not found: /usr/local/mysql/data//private_key.pem. Some authentication plugins will not work.

2014-04-26 19:57:19 1281 [Note] RSA public key file not found: /usr/local/mysql/data//public_key.pem. Some authentication plugins will not work.

2014-04-26 19:57:19 1281 [Note] Binlog end

2014-04-26 19:57:19 1281 [Note] InnoDB: FTS optimize thread exiting.

2014-04-26 19:57:19 1281 [Note] InnoDB: Starting shutdown...

2014-04-26 19:57:21 1281 [Note] InnoDB: Shutdown completed; log sequence number 1625987

To start mysqld at boot time you have to copy

support-files/mysql.server to the right place for your system

PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !

To do so, start the server, then issue the following commands:

/usr/local/mysql//bin/mysqladmin -u root password 'new-password'

/usr/local/mysql//bin/mysqladmin -u root -h master password 'new-password'

Alternatively you can run:

/usr/local/mysql//bin/mysql_secure_installation

which will also give you the option of removing the test

databases and anonymous user created by default. This is

strongly recommended for production servers.

See the manual for more instructions.

You can start the MySQL daemon with:

cd . ; /usr/local/mysql//bin/mysqld_safe &

You can test the MySQL daemon with mysql-test-run.pl

cd mysql-test ; perl mysql-test-run.pl

Please report any problems at http://bugs.mysql.com/

The latest information about MySQL is available on the web at

http://www.mysql.com

Support MySQL by buying support/licenses at http://shop.mysql.com

New default config file was created as /usr/local/mysql//my.cnf and

will be used by default by the server when you start it.

You may edit this file to change server settings

WARNING: Default config file /etc/my.cnf exists on the system

This file will be read by default by the MySQL server

If you do not want to use this, either remove it, or use the

--defaults-file argument to mysqld_safe when starting the server.

4）复制配置文件并设置启动脚本

[root@node1 mysql]# cp my.cnf /etc/my.cnf

cp：是否覆盖"/etc/my.cnf"？ y

[root@node1 mysql]# cp support-files/mysql.server /etc/init.d/mysqld[root@master mysql]# chmod +x /etc/init.d/mysqld

[root@master mysql]# chkconfig --add mysqld

[root@master mysql]# chkconfig mysqld on

[root@master mysql]# mkdir /usr/local/mysql/logs

[root@master mysql]# chown -R mysql:mysql /usr/local/mysql/logs/

[root@master mysql]# vi /etc/my.cnf

[mysqld]

#开启二进制日志

log-bin = /usr/local/mysql/logs/sql-bin

#唯一编号 [一般取IP的后两位]

server_id = 10

[root@master mysql]# vi /etc/profile.d/mysql.sh

PATH=$PATH:/usr/local/mysql/bin:

[root@master mysql]# source /etc/profile

[root@master mysql]# service mysqld start

Starting MySQL............ SUCCESS!

[root@master mysql]# netstat -antup |grep 3306

tcp 0 0 :::3306 :::* LISTEN 1768/mysqld

#salve步骤一样，这里省略...

1）在master上的MySQL创建相关用户

[root@node1 mysql]# mysql -u root

Welcome to the MySQL monitor. Commands end with ; or /g.

Your MySQL connection id is 1

Server version: 5.6.16-enterprise-commercial-advanced MySQL Enterprise Server - Advanced Edition (Commercial)

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 replication slave on *.* to 'repl'@'192.168.1.11' identified by '12345';

Query OK, 0 rows affected (1.49 sec)

mysql> show master status;

+----------------+----------+--------------+------------------+-------------------+

+----------------+----------+--------------+------------------+-------------------+

| sql-bin.000001 | 120 | | | |

+----------------+----------+--------------+------------------+-------------------+

1 row in set (0.12 sec)

[root@slave ~]# mysql -u root

Welcome to the MySQL monitor. Commands end with ; or /g.

Your MySQL connection id is 3

Server version: 5.5.25-log Source distribution

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> change master to[root@slave1 ~]# mysql -u root

Welcome to the MySQL monitor. Commands end with ; or /g.

Your MySQL connection id is 3

Server version: 5.5.25-log Source distribution

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> change master to

-> master_host='192.168.1.254',

-> master_user='repl',

-> master_password='12345',

-> master_log_file='sql-bin.000001',

-> master_log_pos=120;

Query OK, 0 rows affected (0.25 sec)

4）启动slave检测是否同步成功

mysql> start slave;

Query OK, 0 rows affected (0.00 sec)

mysql> show slave status/G;

*************************** 1. row ***************************

Slave_IO_State: Waiting for master to send event

Master_Host: 192.168.1.10

Master_User: repl

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: sql-bin.000001

Read_Master_Log_Pos: 120

Relay_Log_File: node2-relay-bin.000002

Relay_Log_Pos: 281

Relay_Master_Log_File: sql-bin.000001

Slave_IO_Running: Yes #为Yes说明成功

Slave_SQL_Running: Yes #为Yes说明成功

Replicate_Do_DB:

Replicate_Ignore_DB:

Replicate_Do_Table:

Replicate_Ignore_Table:

Replicate_Wild_Do_Table:

Replicate_Wild_Ignore_Table:

Last_Errno: 0

Last_Error:

Skip_Counter: 0

Exec_Master_Log_Pos: 120

Relay_Log_Space: 454

Until_Condition: None

Until_Log_File:

Until_Log_Pos: 0

Master_SSL_Allowed: No

Master_SSL_CA_File:

Master_SSL_CA_Path:

Master_SSL_Cert:

Master_SSL_Cipher:

Master_SSL_Key:

Seconds_Behind_Master: 0

Master_SSL_Verify_Server_Cert: No

Last_IO_Errno: 0

Last_IO_Error:

Last_SQL_Errno: 0

Last_SQL_Error:

Replicate_Ignore_Server_Ids:

Master_Server_Id: 10

Master_UUID: 8c55a12b-cd22-11e3-bc81-0050563f4084

Master_Info_File: /usr/local/mysql/data/master.info

SQL_Delay: 0

SQL_Remaining_Delay: NULL

Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it

Master_Retry_Count: 86400

Master_Bind:

Last_IO_Error_Timestamp:

Last_SQL_Error_Timestamp:

Master_SSL_Crl:

Master_SSL_Crlpath:

Retrieved_Gtid_Set:

Executed_Gtid_Set:

Auto_Position: 0

1 row in set (0.05 sec)

ERROR:

No query specified

1）在master创建一个数据表

mysql> create database user;

Query OK, 1 row affected (0.10 sec)

mysql> create table user.test1(id int);

Query OK, 0 rows affected (0.80 sec)

mysql> insert into user.test1 values(1);

Query OK, 1 row affected (0.12 sec)

mysql> insert into user.test1 values(2);

Query OK, 1 row affected (0.15 sec)

mysql> select * from user.test1;

+------+

| id |

+------+

| 1 |

| 2 |

+------+

2 rows in set (0.02 sec)

mysql> show databases;

+--------------------+

| Database |

+--------------------+

| information_schema |

| mysql |

| performance_schema |

| test |

| user |

+--------------------+

5 rows in set (0.34 sec)

mysql> select * from user.test1;

+------+

| id |

+------+

| 1 |

| 2 |

+------+

2 rows in set (0.04 sec)

#说明同步完成。

1）创建相关用户和组

[root@Proxy ~]# groupadd -g 3306 mysql-proxy

[root@Proxy ~]# useradd -u 3306 -g mysql-proxy -s /sbin/nologin -M mysql-proxy

2）编译安装mysql-proxy

[root@Proxy ~]# cd /Linux/

[root@Proxy Linux]# tar zfvx mysql-proxy-0.8.3-linux-rhel5-x86-64bit.tar.gz -C /usr/local/

[root@Proxy local]# mv mysql-proxy-0.8.3-linux-rhel5-x86-64bit/ mysql-proxy

[root@Proxy local]# cd mysql-proxy/

[root@Proxy mysql-proxy]# chown -R root:mysql-proxy .

3）设置mysql-proxy环境变量

[root@Proxy mysql-proxy]# vi /etc/profile.d/mysql-proxy.sh

export PATH=$PATH:/usr/local/mysql-proxy/bin

[root@Proxy mysql-proxy]# source /etc/profile

[root@Proxy mysql-proxy]# mysql-proxy --daemon --log-level=debug /

--log-file=/var/log/mysql-proxy.log --plugins="proxy" /

--proxy-backend-addresses="192.168.1.10:3306" /

--proxy-read-only-backend-addresses="192.168.1.11:3306"

5）查看是否启动[默认端口4040]

[root@Proxy mysql-proxy]# netstat -antup|grep 4040

tcp 0 0 0.0.0.0:4040 0.0.0.0:* LISTEN 1326/mysql-proxy

6）客户端测试连接[Proxy IP 地址]

[root@slave ~]# mysql -uuser -p -h192.168.1.12 --port=4040

Enter password:

Welcome to the MySQL monitor. Commands end with ; or /g.

Your MySQL connection id is 5

Server version: 5.5.25-log Source distribution

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> show databases;

+--------------------+

| Database |

+--------------------+

| information_schema |

| mysql |

| performance_schema |

| test |

| user |

+--------------------+

[root@Proxy mysql-proxy]# killall mysql-proxy

[root@Proxy mysql-proxy]# netstat -antup |grep 4040

[root@Proxy mysql-proxy]# cd /usr/local/mysql-proxy

[root@Proxy mysql-proxy]# cp lib/mysql-proxy/lua/admin.lua share/doc/mysql-proxy/

——启动mysql-proxy

[root@Proxy mysql-proxy]# mysql-proxy --daemon --log-level=debug /

--log-file=/var/log/mysql-proxy.log --plugins="proxy" /

--proxy-backend-addresses="192.168.1.10:3306"

--proxy-read-only-backend-addresses="192.168.1.11:3306" /

--proxy-lua-script="/usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua" /

--plugins=admin --admin-username="admin" /

--admin-password="admin" /

--admin-lua-script="/usr/local/mysql-proxy/share/doc/mysql-proxy/admin.lua"

[root@Proxy mysql-proxy]# mysql-proxy --daemon --log-level=debug /

--log-file=/var/log/mysql-proxy.log --plugins="proxy" /

--proxy-backend-addresses="192.168.1.252:3306"

--proxy-read-only-backend-addresses="192.168.1.254:3306" /

--proxy-lua-script="/usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua" /

--plugins=admin --admin-username="admin" /

--admin-password="admin" /

--admin-lua-script="/usr/local/mysql-proxy/share/doc/mysql-proxy/admin.lua"

客户端执行

[root@node1 /]# mysql -uuser -p12345 -h192.168.1.10 --port=4040 -e "select user from mysql.user;"

[root@node2 /]# mysql -uuser -p12345 -h192.168.1.11 --port=4040 -e "create database user1;"

10）优化配置启动脚本，并修连接端口

[root@Proxy mysql-proxy]# vi /etc/sysconfig/mysql-proxy

ADMIN_USER="admin"

ADMIN_PASSWORD="admin"

ADMIN_ADDRESS=""

ADMIN_LUA_SCRIPT="/usr/local/mysql-proxy/share/doc/mysql-proxy/admin.lua"

PROXY_ADDRESS="0.0.0.0:3306"

PROXY_USER="mysql-proxy"

PROXY_OPTIONS="--daemon --log-level=info --log-file="/var/log/mysql-proxy.log"

--plugins=proxy --plugins=admin --proxy-backend-addresses=192.168.1.12:3306

--proxy-read-only-backend-addresses=192.168.1.13:3306

--proxy-lua-script=/usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua"

[root@Proxy mysql-proxy]# vi /etc/init.d/mysql-proxy

#!/bin/bash

# mysql-proxy This script starts and stops the mysql-proxy daemon

# chkconfig: - 78 30

# processname: mysql-proxy

# description: mysql-proxy is a proxy daemon for mysql

# Source function library.

. /etc/rc.d/init.d/functions

prog="/usr/local/mysql-proxy/bin/mysql-proxy"

# Source networking configuration.

if [ -f /etc/sysconfig/network ]; then

. /etc/sysconfig/network

# Check that networking is up.

[ ${NETWORKING} = "no" ] && exit 0

# Set default mysql-proxy configuration.

ADMIN_USER="admin"

ADMIN_PASSWD="admin"

ADMIN_LUA_SCRIPT="/usr/local/mysql-proxy/share/doc/mysql-proxy/admin.lua"

PROXY_OPTIONS="--daemon"

PROXY_PID=/var/run/mysql-proxy.pid

PROXY_USER="mysql-proxy"

# Source mysql-proxy configuration.

if [ -f /etc/sysconfig/mysql-proxy ]; then

. /etc/sysconfig/mysql-proxy

RETVAL=0

start() {

echo -n $"Starting $prog: "

daemon $prog $PROXY_OPTIONS --pid-file=$PROXY_PID

--proxy-address="$PROXY_ADDRESS" --user=$PROXY_USER

--admin-username="$ADMIN_USER" --admin-lua-script="$ADMIN_LUA_SCRIPT"

--admin-password="$ADMIN_PASSWORD"

RETVAL=$?

echo

if [ $RETVAL -eq 0 ]; then

touch /var/lock/subsys/mysql-proxy

}

stop() {

echo -n $"Stopping $prog: "

killproc -p $PROXY_PID -d 3 $prog

RETVAL=$?

echo

if [ $RETVAL -eq 0 ]; then

rm -f /var/lock/subsys/mysql-proxy

rm -f $PROXY_PID

}

# See how we were called.

case "$1" in

start)

start

;;

stop)

stop

;;

restart)

stop

start

;;

condrestart|try-restart)

if status -p $PROXY_PIDFILE $prog >&/dev/null; then

stop

start

;;

status)

status -p $PROXY_PID $prog

;;

echo "Usage: $0 {start|stop|restart|reload|status|condrestart|try-restart}"

RETVAL=1

;;

esac

exit $RETVAL

MySQL Proxy 的详细介绍：请点这里
MySQL Proxy 的下载地址：请点这里

陳述

本文內容由網友自願投稿，版權歸原作者所有。本站不承擔相應的法律責任。如發現涉嫌抄襲或侵權的內容，請聯絡admin@php.cn

MySQL：世界上最受歡迎的數據庫的簡介Apr 12, 2025 am 12:18 AM

MySQL是一種開源的關係型數據庫管理系統，主要用於快速、可靠地存儲和檢索數據。其工作原理包括客戶端請求、查詢解析、執行查詢和返回結果。使用示例包括創建表、插入和查詢數據，以及高級功能如JOIN操作。常見錯誤涉及SQL語法、數據類型和權限問題，優化建議包括使用索引、優化查詢和分錶分區。

MySQL的重要性：數據存儲和管理Apr 12, 2025 am 12:18 AM

MySQL是一個開源的關係型數據庫管理系統，適用於數據存儲、管理、查詢和安全。 1.它支持多種操作系統，廣泛應用於Web應用等領域。 2.通過客戶端-服務器架構和不同存儲引擎，MySQL高效處理數據。 3.基本用法包括創建數據庫和表，插入、查詢和更新數據。 4.高級用法涉及復雜查詢和存儲過程。 5.常見錯誤可通過EXPLAIN語句調試。 6.性能優化包括合理使用索引和優化查詢語句。

為什麼要使用mysql？利益和優勢Apr 12, 2025 am 12:17 AM

選擇MySQL的原因是其性能、可靠性、易用性和社區支持。 1.MySQL提供高效的數據存儲和檢索功能，支持多種數據類型和高級查詢操作。 2.採用客戶端-服務器架構和多種存儲引擎，支持事務和查詢優化。 3.易於使用，支持多種操作系統和編程語言。 4.擁有強大的社區支持，提供豐富的資源和解決方案。

描述InnoDB鎖定機制（共享鎖，獨家鎖，意向鎖，記錄鎖，間隙鎖，下一鍵鎖）。Apr 12, 2025 am 12:16 AM

InnoDB的鎖機制包括共享鎖、排他鎖、意向鎖、記錄鎖、間隙鎖和下一個鍵鎖。 1.共享鎖允許事務讀取數據而不阻止其他事務讀取。 2.排他鎖阻止其他事務讀取和修改數據。 3.意向鎖優化鎖效率。 4.記錄鎖鎖定索引記錄。 5.間隙鎖鎖定索引記錄間隙。 6.下一個鍵鎖是記錄鎖和間隙鎖的組合，確保數據一致性。

MySQL查詢性能差的常見原因是什麼？Apr 12, 2025 am 12:11 AM

MySQL查询性能不佳的原因主要包括没有使用索引、查询优化器选择错误的执行计划、表设计不合理、数据量过大和锁竞争。1.没有索引导致查询缓慢，添加索引后可显著提升性能。2.使用EXPLAIN命令可以分析查询计划，找出优化器错误。3.重构表结构和优化JOIN条件可改善表设计问题。4.数据量大时，采用分区和分表策略。5.高并发环境下，优化事务和锁策略可减少锁竞争。

您什麼時候應該使用複合索引與多個單列索引？Apr 11, 2025 am 12:06 AM

在數據庫優化中，應根據查詢需求選擇索引策略：1.當查詢涉及多個列且條件順序固定時，使用複合索引；2.當查詢涉及多個列但條件順序不固定時，使用多個單列索引。複合索引適用於優化多列查詢，單列索引則適合單列查詢。

如何識別和優化MySQL中的慢速查詢？（慢查詢日誌，performance_schema）Apr 10, 2025 am 09:36 AM

要優化MySQL慢查詢，需使用slowquerylog和performance_schema：1.啟用slowquerylog並設置閾值，記錄慢查詢；2.利用performance_schema分析查詢執行細節，找出性能瓶頸並優化。

MySQL和SQL：開發人員的基本技能Apr 10, 2025 am 09:30 AM

MySQL和SQL是開發者必備技能。 1.MySQL是開源的關係型數據庫管理系統，SQL是用於管理和操作數據庫的標準語言。 2.MySQL通過高效的數據存儲和檢索功能支持多種存儲引擎，SQL通過簡單語句完成複雜數據操作。 3.使用示例包括基本查詢和高級查詢，如按條件過濾和排序。 4.常見錯誤包括語法錯誤和性能問題，可通過檢查SQL語句和使用EXPLAIN命令優化。 5.性能優化技巧包括使用索引、避免全表掃描、優化JOIN操作和提升代碼可讀性。

See all articles