Mysql高级使用

已于 2024-07-11 15:54:44 修改
阅读量439 收藏
点赞数
分类专栏： 一图总结 文章标签： mysql 数据结构 golang
于 2023-03-10 17:36:26 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/qq_36652517/article/details/129447505
版权

Mysql高级使用

Mysql索引

mysql为什么选择b+树？

MySQL选择B+树作为索引结构的原因有以下几点：

  • B+树的空间利用率更高，因为它的内部节点只存储键值和指针，而不存储数据，所以每个节点可以存储更多的键值，从而减少树的高度和磁盘I/O次数。
  • B+树的查询效率更稳定，因为它的所有数据都存储在叶子节点上，而且叶子节点之间有链表连接，所以无论查询哪个键值，都需要走相同数量的路径。
  • B+树更适合范围查询和顺序访问，因为它的叶子节点按照键值顺序排列，并且包含了所有的数据，所以可以很方便地进行范围扫描和顺序遍历。

相比之下，B树、红黑树等其他树结构可能会有以下缺点：

  • B树空间利用率较低，因为它的每个节点都存储了数据，所以每个节点能存储的键值数量较少，从而增加了树的高度和磁盘I/O次数。
  • B树查询效率不稳定，因为它的数据分布在各个层级上，所以不同键值可能需要走不同数量的路径。
  • B树不适合范围查询和顺序访问，因为它的数据没有按照键值顺序排列，并且分散在各个节点上，所以需要多次跳转和查找。
  • 红黑树是一种自平衡二叉查找树，在插入和删除操作时会进行旋转调整来保持平衡。但是这样也会导致红黑树在频繁修改时性能下降，并且增加了实现复杂度。
  • 红黑树也不适合范围查询和顺序访问，因为它没有链表连接叶子节点，并且没有按照键值顺序排列数据。

mysql数据如何通过b+数存储示意图?

MySQL数据如何通过B+树存储示意图是一种用来展示MySQL如何使用B+树结构来存储和查询数据的图形。

  • B+树是一种多路平衡搜索树，它将数据按照索引的顺序存储在叶子节点上，而内部节点只存储索引的键值和指针。
  • B+树的优点是可以减少磁盘I/O次数，提高查询效率，支持范围查询和顺序访问。

下面是一个MySQL数据如何通过B+树存储示意图的例子：

    +-----------------+
    |   10   |   20  |
    +-----------------+
   /                  \
+-----------------+  +-----------------+
|   5    |   8   |  |   15   |   18   |
+-----------------+  +-----------------+
/    |     \       \ /      |     \
A    B      C       D       E      F

A: (1, a), (2, b), (3, c), (4, d)
B: (5, e), (6, f), (7, g)
C: (8, h), (9, i)
D: (10, j), (11, k), (12, l)
E: (15, m), (16, n), (17,o)
F: (18,p), (19,q)

这是一个阶数为4的B+树，每个节点最多有4个子节点，最多有3个键值。叶子节点存储了真实的数据行，内部节点只存储了键值和指针。

mysql的聚簇索引原理与图片?

  • 聚簇索引是一种将数据按照索引的顺序存储的索引，也就是说索引项的顺序与表中记录的物理顺序一致。
  • 聚簇索引通常是用B+树实现的，叶子节点存储了真实的数据行，不再有另外单独的数据页。
  • 聚簇索引的优点是查询速度快，因为找到索引就找到了数据；缺点是插入和更新操作可能导致数据移动和索引重建，影响性能。

下面是一个聚簇索引的示意图1：

+-----------------+
|  索引  |  数据  |
+-----------------+
|   1   |  A,B,C |
+-----------------+
|   2   |  D,E,F |
+-----------------+
|   3   |  G,H,I |
+-----------------+

mysql非聚簇索引原理与图片？

  • 非聚簇索引是一种将数据与索引分开存储的索引，也就是说索引项的顺序与表中记录的物理顺序不一致。
  • 非聚簇索引通常是用B+树实现的，叶子节点存储了数据行的指针，而不是真实的数据。
  • 非聚簇索引的优点是插入和更新操作不会导致数据移动和索引重建，节省空间和时间；
    缺点是查询速度慢，因为需要通过索引找到数据指针，再通过指针找到数据。

下面是一个非聚簇索引的示意图1：

+-----------------+
|  索引  |  指针  |
+-----------------+
|   1   |   P1   |
+-----------------+
|   2   |   P2   |
+-----------------+
|   3   |   P3   |
+-----------------+

+-----------------+
|  指针  |  数据  |
+-----------------+
|   P1  |  A,B,C |
+-----------------+
|   P2  |  D,E,F |
+-----------------+
|   P3  |  G,H,I |
+-----------------+

mysql 的不同log

mysql 的 bin log、red log 和 undo log 有何不同?

mysql 的 bin log、red log 和 undo log 是三种不同的日志类型，它们各自有不同的作用和使用场景。

  • bin log 是 mysql server 层记录的归档日志，它记录了所有对数据库的修改操作，
    如 insert、update、delete 等。bin log 可以用于数据恢复、主从复制和审计等功能。

  • red log 是 innodb 存储引擎层记录的重做日志，它记录了对数据页的物理修改操作，如分裂页、合并页等。
    red log 用于保证 mysql 的崩溃恢复能力，当 mysql 实例崩溃后，可以通过重放 red log 来恢复数据。

  • undo log 是 innodb 存储引擎层记录的回滚日志，它记录了对数据行的逻辑修改操作的逆向操作，如 insert 对应 delete，
    update 对应 update。undo log 用于实现事务的原子性和一致性，当事务需要回滚时，可以通过执行 undo log 来撤销之前的修改

golang如何使用Mysql事务与索引

MySQL的事务做到了什么？

MySQL是一个关系型数据库管理系统，它可以支持事务（transaction）功能。事务是一组原子性的SQL语句，
要么全部执行成功，要么全部回滚（rollback）。事务可以做到以下几点：

  1. 一致性（consistency），即在事务开始和结束时，数据都符合预期的规则和约束。
  2. 隔离性（isolation），即在事务执行过程中，其他并发的事务不能看到或影响到该事务的中间状态。
  3. 持久性（durability），即在事务提交（commit）后，对数据所做的修改会永久保存在数据库中，
    即使发生系统故障也不会丢失。
  4. 原子性（atomicity），即在事务执行过程中，如果发生任何错误或异常，该事务会被完全撤销（abort），
    恢复到执行前的状态。

mysql sql 如何使用行锁?

MySQL支持两种类型的锁：表锁（table lock）和行锁（row lock）。表锁是对整个表进行加锁，
行锁是对表中的某一行或多行进行加锁。行锁可以提高并发性能，但也会增加开销和死锁的风险。

要使用行锁，您需要满足以下条件：

  1. 表必须使用InnoDB存储引擎，因为只有InnoDB支持行级别的锁定。
  2. SQL语句必须使用主键或唯一索引来定位要更新或删除的记录，否则MySQL会自动升级为表锁。
  3. SQL语句中使用FOR UPDATE或LOCK IN SHARE MODE子句来显式地请求行锁。例如：
    SELECT * FROM table_name WHERE id = 1 FOR UPDATE;
    这条语句会对id为1的记录加上排他性的行锁（exclusive row lock），阻止其他事务修改或删除该记录，直到当前事务提交或回滚。

golang如何使用mysql事务?

package main

import (
	"database/sql"
	"fmt"
	"log"

	_ "github.com/go-sql-driver/mysql"
)

func main() {
	// 创建数据库连接池对象
	db, err := sql.Open("mysql", "user:password@tcp(localhost:3306)/dbname")
	if err != nil {
		log.Fatal(err)
	}
	defer db.Close()

	// 开启事务
	tx, err := db.Begin()
	if err != nil {
		log.Fatal(err)
	}

	// 执行SQL语句
	res, err := tx.Exec("INSERT INTO users(name, age) VALUES(?, ?)", "Alice", 18)
	if err != nil {
		tx.Rollback() // 回滚事务
		log.Fatal(err)
	}

	id, err := res.LastInsertId()
	if err != nil {
		tx.Rollback() // 回滚事务
		log.Fatal(err)
	}

	fmt.Println("Inserted user id:", id)

	res, err = tx.Exec("UPDATE users SET age = ? WHERE name = ?", 19, "Bob")
	if err != nil {
		tx.Rollback() // 回滚事务
		log.Fatal(err)
	}

	rowsAffected, err := res.RowsAffected()
	if err != nil {
		tx.Rollback() // 回滚事务
		log.Fatal(err)
	}

	fmt.Println("Updated rows:", rowsAffected)

	err = tx.Commit() // 提交事务
	if err != nil {
		log.Fatal(err)
	}

	fmt.Println("Transaction completed.")
}

golang如何使用mysql表锁?

package main

import (
	"database/sql"
	"fmt"
	"log"

	_ "github.com/go-sql-driver/mysql"
)

func main() {
	// 创建数据库连接池对象
	db, err := sql.Open("mysql", "user:password@tcp(localhost:3306)/dbname")
	if err != nil {
		log.Fatal(err)
	}
	defer db.Close()

	// 对users表加写锁
	_, err = db.QueryRow("LOCK TABLES users WRITE")
	if err != nil {
		log.Fatal(err)
	}

	fmt.Println("Locked users table.")

	// 执行SQL语句
	res, err := db.Exec("UPDATE users SET age = ? WHERE name = ?", 20, "Alice")
	if err != nil {
		log.Fatal(err)
	}

	rowsAffected, err := res.RowsAffected()
	if err != nil {
		log.Fatal(err)
	}

	fmt.Println("Updated rows:", rowsAffected)

	// 对users表解锁
	_, err = db.QueryRow("UNLOCK TABLES")
	if err != nil {
		log.Fatal(err)
	}

	fmt.Println("Unlocked users table.")
}

mysql如何使用读写分离或主从复制等技术来减轻数据库压力?

读写分离或主从复制是MySQL的两种常用技术，可以提高数据库的性能和可用性。

读写分离是指将数据库的读操作和写操作分别交给不同的服务器处理，从而减少单个服务器的负载和延迟。

一般来说，读操作比写操作更频繁，所以可以使用一个主服务器负责写操作，多个从服务器负责读操作，形成一个主从集群。
这样，当有大量的读请求时，可以通过负载均衡将请求分发到不同的从服务器上，并且可以实现故障转移和备份。

主从复制是指将主服务器上的数据变化实时地同步到从服务器上，保证数据的一致性。这样，当主服务器出现故障时，
可以快速地切换到一个从服务器作为新的主服务器，并且可以利用从服务器进行数据恢复和备份。

要使用读写分离或主从复制技术，您需要以下几个步骤：

  1. 在主服务器上配置MySQL Server，并开启二进制日志（binlog）功能。
  2. 在从服务器上配置MySQL Server，并设置好复制用户和密码。
  3. 在从服务器上执行CHANGE MASTER TO语句，指定主服务器的地址、端口、用户、密码、日志文件名和位置。
  4. 在从服务器上执行START SLAVE语句，开始复制过程。
  5. 在应用程序中根据不同的业务需求选择合适的数据库连接字符串。

下面是一个简单的示例配置文件：

# 主服务器my.cnf
[mysqld]
server-id=1
log-bin=mysql-bin
binlog-format=row

# 从服务器my.cnf
[mysqld]
server-id=2
read-only=1

# 从服务器mysql命令行
mysql> CHANGE MASTER TO MASTER_HOST='10.0.1.99', MASTER_PORT=3306, MASTER_USER='replica', MASTER_PASSWORD='replica', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=4;
mysql> START SLAVE;

# 应用程序连接字符串
write_db = "user:password@tcp(10.0.1.99:3306)/dbname"
read_db = "user:password@tcp(10.0.1.100:3306)/dbname"

Mysql用什么方式可以避免幻读？

幻读是指在一个事务中，多次查询同一个范围的数据，发现有新增或删除的行。这种现象会破坏事务的隔离性和一致性。

MySQL是通过以下两种方式来避免幻读的：

  1. 使用多版本并发控制（MVCC）实现快照读（snapshot read），也就是一致性读（consistent read）。
    这种方式是在读取数据时，不加锁，而是根据数据行的版本号（row version）和事务的版本号（transaction id）来判断数据是否可见。
    这样，每个事务都可以看到自己开始时刻的数据快照，不受其他事务影响。

  2. 使用间隙锁（gap lock）或者next-key lock实现当前读（current read），也就是锁定读（locking read）。
    这种方式是在读取数据时，加上共享锁或者排他锁，并且锁定索引记录之间的间隙。这样，可以防止其他事务在该范围内插入或删除新的行。

一般来说，在可重复读（REPEATABLE READ）隔离级别下，默认使用快照读来避免幻读；在串行化（SERIALIZABLE）隔离级别下，默认使用当前读来避免幻读。

golang如何使mysql可以避免幻读？

Golang可以使用 database/sql 包和 go-sql-driver/mysql 驱动来连接MySQL数据库，并执行原生SQL语句。
要使MySQL可以避免幻读，您需要注意以下几点：

在连接字符串中指定隔离级别为可重复读（REPEATABLE READ）或者串行化（SERIALIZABLE），
例如：user:password@tcp(localhost:3306)/dbname?parseTime=true&loc=Local&tx_isolation='REPEATABLE-READ'

在执行查询时，使用sql.DB的Query或QueryRow方法来进行快照读（snapshot read），
或者使用sql.DB的BeginTx方法来开启一个事务，并使用sql.Tx的Query或QueryRow方法来进行当前读（current read）。

在处理查询结果时，使用sql.Rows的Scan方法来获取每一行的数据，并根据需要进行解析。
下面是一个简单的示例代码：

package main

import (
	"database/sql"
	"fmt"
	"log"

	_ "github.com/go-sql-driver/mysql"
)

func main() {
	// 连接数据库
	db, err := sql.Open("mysql", "user:password@tcp(localhost:3306)/dbname?parseTime=true&loc=Local&tx_isolation='REPEATABLE-READ'")
	if err != nil {
		log.Fatal(err)
	}
	defer db.Close()

	// 执行快照读
	rows, err := db.Query("SELECT * FROM users WHERE age > ?", 20)
	if err != nil {
		log.Fatal(err)
	}
	defer rows.Close()

	for rows.Next() {
		var id int
		var name string
		var age int
		err = rows.Scan(&id, &name, &age) // 获取每一行的数据
		if err != nil {
			log.Fatal(err)
		}
		fmt.Printf("id: %d, name: %s, age: %d\n", id, name, age) // 打印数据
	}

	// 开启一个事务
	tx, err := db.BeginTx(ctx, &sql.TxOptions{Isolation: sql.LevelSerializable})
	if err != nil {
		log.Fatal(err)
	}
	defer tx.Rollback()

	// 执行当前读
	row := tx.QueryRow("SELECT * FROM users WHERE id = ?", 1)
	var id int
	var name string
	var age int
	err = row.Scan(&id, &name, &age) // 获取一行数据
	if err != nil {
		log.Fatal(err)
	}
	fmt.Printf("id: %d, name: %s, age: %d\n", id, name, age) // 打印数据

}

golang如何调用mysql的MVCC？

MVCC 是多版本并发控制的缩写，它是一种用来解决读写冲突的无锁并发控制机制。MVCC 的核心思想是为每个数据修改保存一个版本，
版本与事务时间戳相关联，读操作只读取该事务开始前的数据库快照。

golang 如何使用 mysql 的 mvcc 呢？其实，golang 只需要通过标准的 sql 接口来操作 mysql 数据库，不需要关心 mvcc 的具体实现细节。

只要设置好事务的隔离级别，就可以利用 mvcc 来保证数据的一致性和并发性。

package main

import (
	"database/sql"
	"fmt"

	_ "github.com/go-sql-driver/mysql"
)

func main() {
	// 连接 mysql 数据库
	db, err := sql.Open("mysql", "root:123456@tcp(localhost:3306)/test")
	if err != nil {
		panic(err)
	}
	defer db.Close()

	// 开启一个事务
	tx, err := db.Begin()
	if err != nil {
		panic(err)
	}
	defer tx.Rollback()

	// 设置事务隔离级别为可重复读
	tx.Exec("SET TRANSACTION ISOLATION LEVEL REPEATABLE READ")

	// 查询 id 为 1 的记录
	row := tx.QueryRow("SELECT name FROM users WHERE id = ?", 1)
	var name string
	err = row.Scan(&name)
	if err != nil {
		panic(err)
	}
	fmt.Println(name) // Alice

	// 在另一个终端中修改 id 为 1 的记录的 name 为 Bob
	// 再次查询 id 为 1 的记录
	row = tx.QueryRow("SELECT name FROM users WHERE id = ?", 1)
	err = row.Scan(&name)
	if err != nil {
		panic(err)
	}
	fmt.Println(name) // Alice

	// 提交事务
	tx.Commit()
}

这段代码中，我们开启了一个可重复读隔离级别的事务，并且两次查询了同一条记录。
在第一次查询之后，我们在另一个终端中修改了这条记录的 name 字段。但是在第二次查询时，我们仍然能够看到原来的值 Alice。

这就说明了 mvcc 能够保证在同一个事务中看到相同的数据快照。

ether-lin
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值