peteryuanpan
commented
3 years ago 引导
- MySQL索引
- 最左前缀原则
- InnoDB中一棵B+树能存多少行数据
- 聚集索引、辅助索引、覆盖索引、联合索引
- B树与B+树原理
- 为什么用B+树
- 一个B+树的一个节点大小为多大
- 为什么一个节点为1页
- SQL优化
- 分库分表
- 分库：多数据源
- 分表：垂直分表 水平分表
- 分库分表后面临的问题
- 事务隔离级别
- MVCC机制
- 锁机制
Open peteryuanpan opened 3 years ago
peteryuanpan
commented
3 years ago 引导
peteryuanpan
commented
3 years ago 实战InnoDB索引效果
创建表
CREATE TABLE `peter`.`t1` (
`a` INT NOT NULL,
`b` INT NULL,
`c` INT NULL,
`d` INT NULL,
`e` INT NULL,
`f` VARCHAR(45) NULL,
PRIMARY KEY (`a`));批量插入数据
pom.xml
<dependencies>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.20</version>
</dependency>
</dependencies>代码
package jdbc;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.UUID;
public class InsertSQL {
private static final String driver = "com.mysql.cj.jdbc.Driver";
private static final String url = "jdbc:mysql://localhost:3306/peter?useUnicode=true&characterEncoding=UTF-8&useLegacyDatetimeCode=false&serverTimezone=UTC";
private static final String username = "root";
private static final String password = "123456";
public static void main(String[] args) {
final long start = System.currentTimeMillis();
Connection connection = null;
PreparedStatement ps = null;
try {
Class.forName(driver);
connection = DriverManager.getConnection(url, username, password);
connection.setAutoCommit(false);
String sql = "insert ignore into t1(`a`, `b`, `c`, `d`, `e`, `f`) values(?,?,?,?,?,?)";
ps = connection.prepareStatement(sql);
for (int i = 1; i <= 1000000; i ++) {
ps.setInt(1, i);
ps.setInt(2, i);
ps.setInt(3, i);
ps.setInt(4, i);
ps.setInt(5, i);
ps.setString(6, UUID.randomUUID().toString());
ps.addBatch();
if (i % 100000 == 0) {
System.out.println("execute " + i);
ps.executeBatch();
}
}
connection.commit();
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
if (ps != null)
ps.close();
if (connection != null)
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
System.out.println("duration: " + (System.currentTimeMillis() - start));
}
}
}输出结果
execute 100000
execute 200000
execute 300000
execute 400000
execute 500000
execute 600000
execute 700000
execute 800000
execute 900000
execute 1000000
duration: 146291用同样的方法再创建一个 t2 表
然后建立索引，用EXPLAIN分析语句
USE peter;
CREATE INDEX idx_ab ON t1(a,b);
CREATE INDEX idx_ab ON t2(a,b);
CREATE INDEX idx_de ON t1(d,e);
CREATE INDEX idx_de ON t2(d,e);
EXPLAIN SELECT t1.a,t1.b,t2.a,t2.b FROM t1 LEFT JOIN t2 ON t1.a = t2.a AND t1.b = t2.b LIMIT 10000;
EXPLAIN SELECT t1.b,t1.c,t2.b,t2.c FROM t1 LEFT JOIN t2 ON t1.b = t2.b AND t1.c = t2.c LIMIT 10000;
EXPLAIN SELECT t1.d,t1.e,t2.d,t2.e FROM t1 LEFT JOIN t2 ON t1.d = t2.d AND t1.e = t2.e LIMIT 10000;输出结果
# id, select_type, table, partitions, type, possible_keys, key, key_len, ref, rows, filtered, Extra
'1', 'SIMPLE', 't1', NULL, 'index', NULL, 'idx_ab', '9', NULL, '1000000', '100.00', 'Using index'
'1', 'SIMPLE', 't2', NULL, 'eq_ref', 'PRIMARY,idx_ab', 'PRIMARY', '4', 'peter.t1.a', '1', '100.00', 'Using where'
# id, select_type, table, partitions, type, possible_keys, key, key_len, ref, rows, filtered, Extra
'1', 'SIMPLE', 't1', NULL, 'ALL', NULL, NULL, NULL, NULL, '1000000', '100.00', NULL
'1', 'SIMPLE', 't2', NULL, 'ALL', NULL, NULL, NULL, NULL, '1000000', '100.00', 'Using where; Using join buffer (Block Nested Loop)'
# id, select_type, table, partitions, type, possible_keys, key, key_len, ref, rows, filtered, Extra
'1', 'SIMPLE', 't1', NULL, 'index', NULL, 'idx_de', '10', NULL, '1000000', '100.00', 'Using index'
'1', 'SIMPLE', 't2', NULL, 'ref', 'idx_de', 'idx_de', '10', 'peter.t1.d,peter.t1.e', '1', '100.00', 'Using index'用java执行sql
package jdbc;
import com.mysql.cj.jdbc.result.ResultSetImpl;
import com.mysql.cj.protocol.ResultsetRows;
import com.mysql.cj.result.Row;
import java.sql.*;
public class SelectSQL {
private static final String driver = "com.mysql.cj.jdbc.Driver";
private static final String url = "jdbc:mysql://localhost:3306/peter?useUnicode=true&characterEncoding=UTF-8&useLegacyDatetimeCode=false&serverTimezone=UTC";
private static final String username = "root";
private static final String password = "123456";
static Connection connection = null;
static PreparedStatement ps = null;
static void print(ResultSetImpl resultSet) {
ResultsetRows rows = resultSet.getRows();
Row row = rows.get(0);
byte[] bytes = row.getBytes(0);
System.out.println(new String(bytes));
}
static void select(String sql) throws SQLException {
long start = System.currentTimeMillis();
System.out.println(sql);
ps = connection.prepareStatement(sql);
ResultSetImpl resultSet = (ResultSetImpl) ps.executeQuery();
System.out.println("duration: " + (System.currentTimeMillis() - start));
}
public static void main(String[] args) {
try {
Class.forName(driver);
connection = DriverManager.getConnection(url, username, password);
connection.setAutoCommit(false);
select("SELECT t1.a,t1.b,t2.a,t2.b FROM t1 LEFT JOIN t2 ON t1.a = t2.a AND t1.b = t2.b LIMIT 10000;");
select("SELECT t1.b,t1.c,t2.b,t2.c FROM t1 LEFT JOIN t2 ON t1.b = t2.b AND t1.c = t2.c LIMIT 10000;");
select("SELECT t1.d,t1.e,t2.d,t2.e FROM t1 LEFT JOIN t2 ON t1.d = t2.d AND t1.e = t2.e LIMIT 10000;");
connection.commit();
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
if (ps != null)
ps.close();
if (connection != null)
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}输出结果
SELECT t1.a,t1.b,t2.a,t2.b FROM t1 LEFT JOIN t2 ON t1.a = t2.a AND t1.b = t2.b LIMIT 10000;
duration: 85
SELECT t1.b,t1.c,t2.b,t2.c FROM t1 LEFT JOIN t2 ON t1.b = t2.b AND t1.c = t2.c LIMIT 10000;
duration: 10007
SELECT t1.d,t1.e,t2.d,t2.e FROM t1 LEFT JOIN t2 ON t1.d = t2.d AND t1.e = t2.e LIMIT 10000;
duration: 31很明显，走索引与不走索引的查询耗时有很大区别
两种业务场景，OLTP（Online Transaction Processing，在线事务处理），查询的数据很少，可以走索引；OLAP（Online Analytical Processing，在线分析处理），往往要查询大量的数据，提供查询结果于决策者，比如统计用户月消费情况，销售额同比环比增长情况等，此时SQL一般需要多表联接（JOIN），因此走索引也是有意义的。但重点是，DBA或开发人员要认证地仔细地研究自己的应用，是否合适走索引
peteryuanpan
commented
3 years ago mysql主从分离
操作步骤
[client]
#password = your_password
port = 3306
socket = /tmp/mysql.sock[mysqld] port = 3306 socket = /tmp/mysql.sock datadir = /www/server/data default_storage_engine = InnoDB performance_schema_max_table_instances = 400 table_definition_cache = 400 skip-external-locking key_buffer_size = 8M max_allowed_packet = 100G table_open_cache = 32 sort_buffer_size = 256K net_buffer_length = 4K read_buffer_size = 128K read_rnd_buffer_size = 256K myisam_sort_buffer_size = 4M thread_cache_size = 4 query_cache_size = 4M tmp_table_size = 8M sql-mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
explicit_defaults_for_timestamp = true open_files_limit = 65535
binlog_format=mixed expire_logs_days = 10 slow_query_log=1 slow-query-log-file=/www/server/data/mysql-slow.log long_query_time=3
early-plugin-load = ""
innodb_data_home_dir = /www/server/data innodb_data_file_path = ibdata1:10M:autoextend innodb_log_group_home_dir = /www/server/data innodb_buffer_pool_size = 16M innodb_log_file_size = 5M innodb_log_buffer_size = 8M innodb_flush_log_at_trx_commit = 1 innodb_lock_wait_timeout = 50 innodb_max_dirty_pages_pct = 90 innodb_read_io_threads = 1 innodb_write_io_threads = 1
server-id = 1 log_bin = master-bin log_bin-index = master-bin.index skip-name-resolve basedir = /www/server/mysql datadir = /www/server/data max_connections = 200 max_connect_errors = 10 character-set-server = utf8 default-storage-engine = INNODB default_authentication_plugin = mysql_native_password
[mysqldump] quick max_allowed_packet = 500M
[mysql] no-auto-rehash
[myisamchk] key_buffer_size = 20M sort_buffer_size = 20M read_buffer = 2M write_buffer = 2M
[mysqlhotcopy] interactive-timeout
- 从服务器配置文件如下[client]
port = 3306 socket = /tmp/mysql.sock
[mysqld] port = 3306 socket = /tmp/mysql.sock datadir = /www/server/data default_storage_engine = InnoDB performance_schema_max_table_instances = 400 table_definition_cache = 400 skip-external-locking key_buffer_size = 8M max_allowed_packet = 100G table_open_cache = 32 sort_buffer_size = 256K net_buffer_length = 4K read_buffer_size = 128K read_rnd_buffer_size = 256K myisam_sort_buffer_size = 4M thread_cache_size = 4 query_cache_size = 4M tmp_table_size = 8M sql-mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
explicit_defaults_for_timestamp = true open_files_limit = 65535
binlog_format=mixed expire_logs_days = 10 slow_query_log=1 slow-query-log-file=/www/server/data/mysql-slow.log long_query_time=3
early-plugin-load = ""
innodb_data_home_dir = /www/server/data innodb_data_file_path = ibdata1:10M:autoextend innodb_log_group_home_dir = /www/server/data innodb_buffer_pool_size = 16M innodb_log_file_size = 5M innodb_log_buffer_size = 8M innodb_flush_log_at_trx_commit = 1 innodb_lock_wait_timeout = 50 innodb_max_dirty_pages_pct = 90 innodb_read_io_threads = 1 innodb_write_io_threads = 1
server-id = 2 relay-log-index = slave-relay-bin.index relay-log = slave-relay-bin log-bin = mysql-bin log-slave-updates = 1 basedir = /www/server/mysql datadir = /www/server/data max_connections = 200 max_connect_errors = 10 character-set-server = utf8
[mysqldump] quick max_allowed_packet = 500M
[mysql] no-auto-rehash
[myisamchk] key_buffer_size = 20M sort_buffer_size = 20M read_buffer = 2M write_buffer = 2M
[mysqlhotcopy] interactive-timeout
- 主服务器上执行操作mysql -u root -p GRANT REPLICATION SLAVE ON . TO 'root'@'%'; flush privileges;
show master status;
+-------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +-------------------+----------+--------------+------------------+-------------------+ | master-bin.000002 | 502 | | | | +-------------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec)
- 从服务器执行操作
```mysql
#登录从服务
mysql -u root -p;
#设置同步主节点
CHANGE MASTER TO MASTER_HOST='<remote_ip>', MASTER_PORT=3306, MASTER_USER='root', MASTER_PASSWORD='<password>',
MASTER_LOG_FILE='master-bin.000002', MASTER_LOG_POS=502;
#开启 slave
# 有时需要 reset slave; stop slave;
start slave;
#查看主从同步状态
show slave status\G;
# 查询结果
# 观察Last_IO_Error、Last_SQL_Error是否有报错，然后解决之
show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: <remote_ip>
Master_User: root
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master-bin.000002
Read_Master_Log_Pos: 502
Relay_Log_File: slave-relay-bin.000002
Relay_Log_Pos: 321
Relay_Master_Log_File: master-bin.000002
Slave_IO_Running: Yes
Slave_SQL_Running: 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: 502
Relay_Log_Space: 528
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: 1
Master_UUID: f7ef46ac-5802-11eb-80fd-525400de6cde
Master_Info_File: /www/server/data/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
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
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)
mysql> create database peter;
Query OK, 1 row affected (0.00 sec)mysql> use peter; Database changed mysql> create table test1(a int, b int, c int); Query OK, 0 rows affected (0.05 sec)
mysql> insert into test1 values(1,2,3); Query OK, 1 row affected (0.01 sec)
- 从服务器查询数据mysql> use peter; Database changed
mysql> show tables; +-----------------+ | Tables_in_peter | +-----------------+ | test1 | +-----------------+ 1 row in set (0.00 sec)
mysql> select * from test1; +------+------+------+ | a | b | c | +------+------+------+ | 1 | 2 | 3 | +------+------+------+ 1 row in set (0.00 sec)
这样每次主服务器更新数据，就会同步于从服务器，具体原理与binlog有关
实战InnoDB事务隔离级别REPEATABLE-READ
注意：以下均为INNODB引擎，在MyISAM引擎中不适用
可以通过 show create table t 查看表所用引擎 可以通过 alter table t engine=INNODB; 修改存储引擎
表结构如下
mysql> show create table t1;
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t1 | CREATE TABLE `t1` (
`a` int(11) NOT NULL,
`b` int(11) DEFAULT NULL,
`c` int(11) DEFAULT NULL,
`d` int(11) DEFAULT NULL,
`e` int(11) DEFAULT NULL,
`f` varchar(45) DEFAULT NULL,
PRIMARY KEY (`a`),
KEY `idx_ab` (`a`,`b`),
KEY `idx_de` (`d`,`e`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)查询事务隔离级别
mysql> show variables like '%tx_isolation%';
+---------------+-----------------+
| Variable_name | Value |
+---------------+-----------------+
| tx_isolation | REPEATABLE-READ |
+---------------+-----------------+
1 row in set, 1 warning (0.00 sec)REPEATABLE-READ下的可重复读
session A
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> use peter;
Database changed
session B
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> use peter;
Database changed
session A
mysql> select * from t1 where a = -2;
+----+------+------+------+------+------+
| a | b | c | d | e | f |
+----+------+------+------+------+------+
| -2 | -2 | -2 | -2 | -2 | -23 |
+----+------+------+------+------+------+
1 row in set (0.00 sec)
session B
mysql> select * from t1 where a = -2;
+----+------+------+------+------+------+
| a | b | c | d | e | f |
+----+------+------+------+------+------+
| -2 | -2 | -2 | -2 | -2 | -23 |
+----+------+------+------+------+------+
1 row in set (0.00 sec)
session A
mysql> update t1 set f = "-24" where a = -2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from t1 where a = -2;
+----+------+------+------+------+------+
| a | b | c | d | e | f |
+----+------+------+------+------+------+
| -2 | -2 | -2 | -2 | -2 | -24 |
+----+------+------+------+------+------+
1 row in set (0.00 sec)
session B
mysql> select * from t1 where a = -2;
+----+------+------+------+------+------+
| a | b | c | d | e | f |
+----+------+------+------+------+------+
| -2 | -2 | -2 | -2 | -2 | -23 |
+----+------+------+------+------+------+
1 row in set (0.00 sec)
session A
commit
session B
mysql> select * from t1 where a = -2;
+----+------+------+------+------+------+
| a | b | c | d | e | f |
+----+------+------+------+------+------+
| -2 | -2 | -2 | -2 | -2 | -23 |
+----+------+------+------+------+------+
1 row in set (0.00 sec)可以看出，REPEATABLE-READ 下，session A 的修改对 session B 是不可见的（即使session A commit了），session B第一次读到的结果是什么，后续的结果也就是什么，这就叫做可重复读
REPEATABLE-READ下的幻读
session A
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> use peter;
Database changed
mysql> select * from t1 where a = -3;
Empty set (0.00 sec)
session B
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> use peter;
Database changed
mysql> select * from t1 where a = -3;
Empty set (0.00 sec)
session A
mysql> insert into t1 values(-3,-3,-3,-3,-3,"-33");
Query OK, 1 row affected (0.00 sec)
mysql> select * from t1 where a = -3;
+----+------+------+------+------+------+
| a | b | c | d | e | f |
+----+------+------+------+------+------+
| -3 | -3 | -3 | -3 | -3 | -33 |
+----+------+------+------+------+------+
1 row in set (0.00 sec)
session B
mysql> select * from t1 where a = -3;
Empty set (0.00 sec)
mysql> update t1 set f = "-34" where a = -3;
（等待获取锁中...）
session A
commit;
session B
mysql> update t1 set f = "-34" where a = -3;
Query OK, 1 row affected (24.05 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from t1 where a = -3;
+----+------+------+------+------+------+
| a | b | c | d | e | f |
+----+------+------+------+------+------+
| -3 | -3 | -3 | -3 | -3 | -34 |
+----+------+------+------+------+------+
1 row in set (0.00 sec)可以看出，REPEATABLE-READ下，session A insert 了一条新数据，在未commit的情况下，session B 要修改这条数据，需要先互获取到锁，在 session A commit 了后，session B 可以修改这条数据，并查到最新的数据结果，这就是幻读问题
幻读问题的具体解释可见：一文读懂MySQL的事务隔离级别及MVCC机制
peteryuanpan
commented
3 years ago 实战InnoDB死锁
session A
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from t1 where a = 1 for update;
+---+------+------+------+------+------+
| a | b | c | d | e | f |
+---+------+------+------+------+------+
| 1 | 1 | 1 | 1 | 1 | 15 |
+---+------+------+------+------+------+
1 row in set (2.49 sec)
session B
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from t1 where a = 2 for update;
+---+------+------+------+------+--------------------------------------+
| a | b | c | d | e | f |
+---+------+------+------+------+--------------------------------------+
| 2 | 2 | 2 | 2 | 2 | 38bfcb95-abfc-4c08-bbd9-4d5fb68e5500 |
+---+------+------+------+------+--------------------------------------+
1 row in set (0.00 sec)
session A
mysql> select * from t1 where a = 2 for update;
（等待获取排他锁...）
session B
mysql> select * from t1 where a = 1 for update;
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
session A
mysql> select * from t1 where a = 2 for update;
+---+------+------+------+------+--------------------------------------+
| a | b | c | d | e | f |
+---+------+------+------+------+--------------------------------------+
| 2 | 2 | 2 | 2 | 2 | 38bfcb95-abfc-4c08-bbd9-4d5fb68e5500 |
+---+------+------+------+------+--------------------------------------+
1 row in set (11.43 sec)select ... for update 会获取一个排他锁，session A 获取了 a = 1 行级别的排他锁后，session B 再次获取需要等待 session A 释放该锁，而 session B 已经获取了一个 a = 2 行级别的排他锁了，此时 session A 也获取该排他锁，就会出现死锁现象
对于死锁现象，InnoDB引擎中，会自动检测到，并抛错，以此方式解决死锁
关于排他锁，更多资料可见：数据库：Mysql中“select ... for update”排他锁分析
参考（优秀）
参考（普通）
鲁班学院VIP课程
总结