2020 Mysql学习总结 #177
Labels
Comments
|
引导
|
Closed
|
实战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));
}
}
}输出结果 用同样的方法再创建一个 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;输出结果 用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();
}
}
}
}输出结果 很明显，走索引与不走索引的查询耗时有很大区别 两种业务场景，OLTP（Online Transaction Processing，在线事务处理），查询的数据很少，可以走索引；OLAP（Online Analytical Processing，在线分析处理），往往要查询大量的数据，提供查询结果于决策者，比如统计用户月消费情况，销售额同比环比增长情况等，此时SQL一般需要多表联接（JOIN），因此走索引也是有意义的。但重点是，DBA或开发人员要认证地仔细地研究自己的应用，是否合适走索引 |
|
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)
这样每次主服务器更新数据，就会同步于从服务器，具体原理与binlog有关
|
|
实战InnoDB事务隔离级别REPEATABLE-READ 注意：以下均为INNODB引擎，在MyISAM引擎中不适用 可以通过 show create table t 查看表所用引擎 表结构如下 查询事务隔离级别 REPEATABLE-READ下的可重复读 可以看出，REPEATABLE-READ 下，session A 的修改对 session B 是不可见的（即使session A commit了），session B第一次读到的结果是什么，后续的结果也就是什么，这就叫做可重复读 REPEATABLE-READ下的幻读 可以看出，REPEATABLE-READ下，session A insert 了一条新数据，在未commit的情况下，session B 要修改这条数据，需要先互获取到锁，在 session A commit 了后，session B 可以修改这条数据，并查到最新的数据结果，这就是幻读问题 幻读问题的具体解释可见：一文读懂MySQL的事务隔离级别及MVCC机制 |
|
实战InnoDB死锁 select ... for update 会获取一个排他锁，session A 获取了 a = 1 行级别的排他锁后，session B 再次获取需要等待 session A 释放该锁，而 session B 已经获取了一个 a = 2 行级别的排他锁了，此时 session A 也获取该排他锁，就会出现死锁现象 对于死锁现象，InnoDB引擎中，会自动检测到，并抛错，以此方式解决死锁 关于排他锁，更多资料可见：数据库：Mysql中“select ... for update”排他锁分析 |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
参考（优秀）
参考（普通）
鲁班学院VIP课程
总结
The text was updated successfully, but these errors were encountered: