MySQL进阶-在linux里面的实践（权限管理、索引、集群）

nkion
2024年 11月 19日

MySQL的基础操作学习起来较为简单，但随着业务量的增加，性能瓶颈逐渐显现。今天，我总结一下MySQL的进阶知识，重点包括权限管理、索引的优化和集群的配置与应用。

1.权限管理

MySQL的权限系统基于用户账户来分配权限，用户账户通常由用户名和主机名组成。权限控制涉及用户的创建、授权、撤销权限、查看权限等操作。以下是MySQL权限管理的详细内容。

1. MySQL 权限管理基础

1.1 用户账户的格式

MySQL的用户账户由两个部分组成：

用户名：如user_name。
主机名：如localhost或%（表示任意主机）。例如，user_name@localhost表示该用户只能从本地连接，而user_name@%表示该用户可以从任何主机连接。

1.2 用户账户的创建

可以使用CREATE USER语句创建一个新用户，语法如下：

CREATE USER 'username'@'host' IDENTIFIED BY 'password';

1.3 查看现有用户

查看当前MySQL服务器中的所有用户：

SELECT User, Host FROM mysql.user;

2. 授予权限

MySQL的权限可以精确到数据库、表甚至列。常见的权限类型包括：

ALL PRIVILEGES：授予所有权限。
SELECT：允许查询数据。
INSERT：允许插入数据。
UPDATE：允许更新数据。
DELETE：允许删除数据。
CREATE：允许创建数据库或表。
DROP：允许删除数据库或表。
GRANT OPTION：允许将权限授予其他用户。

2.1 授予权限

使用GRANT语句授予用户权限，语法如下：

GRANT privilege_type ON database.table TO 'username'@'host';

例如：

GRANT SELECT, INSERT ON mydb.* TO 'john'@'localhost';

这将允许用户john从localhost主机上对数据库mydb进行SELECT和INSERT操作。

2.2 授予所有权限

如果要授予用户所有权限，可以使用ALL PRIVILEGES：

GRANT ALL PRIVILEGES ON mydb.* TO 'john'@'localhost';

此命令授予john用户在mydb数据库上的所有权限。

2.3 授予权限并允许用户授予权限

如果希望john用户能够将自己拥有的权限授予给其他用户，可以加上GRANT OPTION：

GRANT ALL PRIVILEGES ON mydb.* TO 'john'@'localhost' WITH GRANT OPTION;

3. 撤销权限

如果需要撤销某个用户的权限，可以使用REVOKE语句，语法如下：

REVOKE privilege_type ON database.table FROM 'username'@'host';

例如：

REVOKE INSERT ON mydb.* FROM 'john'@'localhost';

这条语句将会回收Joh用户的插入权限。

如果想撤销所有权限，可以使用ALL PRIVILEGES：

REVOKE ALL PRIVILEGES ON mydb.* FROM 'john'@'localhost';

4. 刷新权限

MySQL在执行GRANT、REVOKE或SET PASSWORD等操作后，权限并不会立即生效，必须使用FLUSH PRIVILEGES来刷新权限：

FLUSH PRIVILEGES;

这个命令会重新加载权限表，以确保权限的更改立刻生效。
注意：这个命令一般在修改权限后立即执行。

5. 删除用户

如果不再需要某个用户，可以使用DROP USER语句删除该用户：

DROP USER 'john'@'localhost';

此命令会删除john用户及其所有权限。

6.权限查看

6.1 查看当前用户的权限

可以通过SHOW GRANTS命令查看某个用户的权限：

SHOW GRANTS FOR 'john'@'localhost';

该命令返回用户john的所有权限.

6.2 查看所有用户的权限

可以通过查询mysql.user表查看所有用户的权限：

SELECT User, Host, Select_priv, Insert_priv, Update_priv, Delete_priv FROM mysql.user;

7. 常见权限管理操作

7.1 创建用户并授予权限

创建一个新用户alice，并授予SELECT权限：

CREATE USER 'alice'@'localhost' IDENTIFIED BY 'password';
GRANT SELECT ON mydb.* TO 'alice'@'localhost';
FLUSH PRIVILEGES;

7.2 修改用户的密码

可以使用SET PASSWORD命令修改用户的密码：(不推荐)

SET PASSWORD FOR 'alice'@'localhost' = PASSWORD('newpassword');

或者：（推荐）

ALTER USER 'alice'@'localhost' IDENTIFIED BY 'newpassword';

7.4 为特定主机授予权限

如果希望授予一个用户在特定主机上的权限（例如localhost和远程主机%），可以像这样：

GRANT ALL PRIVILEGES ON mydb.* TO 'john'@'localhost';
GRANT ALL PRIVILEGES ON mydb.* TO 'john'@'%';
FLUSH PRIVILEGES;

总结

MySQL的权限管理非常重要，它确保了数据库的安全性和合理的资源使用。通过创建用户、授予权限、撤销权限和查看权限等操作，可以有效管理用户的访问权限。权限的精确管理不仅能防止不必要的数据泄露，还能保证数据库在多人操作环境中的安全性。

2. 索引

2.1 索引的基本概念

索引是一种数据结构，帮助MySQL快速定位某些数据。简单来说，索引就像是一本书的目录，可以直接跳到书中某个章节，而不需要从第一页翻到最后一页。对于数据库来说，索引的作用就是加速查询，特别是当表的数据量非常大时，索引的作用尤为显著。

2.2 索引的工作原理

MySQL中常见的索引类型是B+树索引。B+树是一种平衡树，所有的数据都存储在树的叶子节点中，并且这些叶子节点通过链表连接。每当查询时，MySQL通过遍历B+树来找到所需的数据，而不是扫描整个表，这大大加速了查询。

2.3 创建索引的例子

假设有一个users表，包含以下字段：

CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(100),
    age INT
);

如果经常根据name字段进行查询，可以为name字段创建一个索引：

CREATE INDEX idx_name ON users(name);

这条命令会创建一个名为idx_name的索引，在查询时，MySQL会利用该索引来加速name字段的查找。

2.4 索引的使用场景

2.4.1 WHERE子句中的查询条件

假设需要根据age查询用户：

SELECT * FROM users WHERE age = 25;

如果age字段没有索引，MySQL会进行全表扫描，检查每一行数据。而如果为age字段创建了索引，MySQL就能直接定位到符合条件的行，从而加速查询：

CREATE INDEX idx_age ON users(age);

这样查询效率大大提高，尤其是在users表数据量很大的情况下。

2.4.2 JOIN操作

假设有两个表：orders和users，分别记录了订单信息和用户信息，希望根据user_id来查询订单信息。可以通过创建索引来加速连接查询。

CREATE INDEX idx_user_id ON orders(user_id);

然后执行如下查询：

SELECT u.name, o.order_id
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE u.age > 30;

由于user_id在orders表中有索引，MySQL能够更高效地通过索引找到与users表匹配的行，从而加速查询。

2.4.3 ORDER BY和GROUP BY

如果需要对查询结果进行排序，创建索引也能显著提高性能。例如，如果需要按age字段排序：

SELECT * FROM users ORDER BY age;

如果已经为age字段创建了索引，MySQL就可以直接使用索引来加速排序，而不是先读取所有数据再进行排序。

CREATE INDEX idx_age ON users(age);

同理，对于GROUP BY操作：

SELECT age, COUNT(*) FROM users GROUP BY age;

有了索引后，MySQL可以通过索引直接获取分组所需的字段，而不需要扫描所有行。

2.5 索引优化策略

2.5.1 选择性高的列适合建立索引

选择性高的列是指该列的值有很大差异。例如，user_id通常是唯一的，而age可能有很多重复值。在这种情况下，user_id的选择性较高，更适合创建索引。

例如，假设有以下查询：

SELECT * FROM users WHERE user_id = 123;

如果user_id列有索引，MySQL可以迅速定位到user_id = 123这一行数据。反之，如果查询的是age，由于可能有很多用户年龄相同，创建索引的效果就不如user_id明显。

2.5.2 避免创建过多的索引

虽然索引加速了查询，但它们也会增加数据库的负担，特别是在插入、更新和删除数据时。每当数据发生变更，相关的索引也需要更新。因此，要避免为每个查询条件都创建索引，而应该根据查询的频率和重要性来选择合适的字段建立索引。

例如，假设有一个logs表，包含id、level、message和timestamp等字段。如果经常根据level字段查询日志，可以创建索引。但如果message字段用于模糊查询（如LIKE '%error%'），则创建索引可能效果不佳，因为模糊查询无法利用B+树索引。

2.5.3 使用复合索引

当查询涉及多个字段时，使用复合索引比单独为每个字段创建索引更高效。例如，如果经常根据age和name字段进行查询，可以创建一个包含这两个字段的复合索引：

CREATE INDEX idx_age_name ON users(age, name);

复合索引的顺序也非常重要，应该按照查询中WHERE子句的字段顺序来创建索引。例如，如果查询的是：

SELECT * FROM users WHERE age = 25 AND name = 'John';

那么(age, name)的复合索引就会非常高效。但如果查询是：

SELECT * FROM users WHERE name = 'John' AND age = 25;

此时，虽然查询条件顺序不同，但(age,name)的复合索引也能使用，但是由于查询条件的顺序是name在前，它会按照name字段进行过滤，然后在name匹配的结果中再根据age来过滤。这样，索引依然能加速查询，但效率会略低一些。

2.5.4 避免对低基数列建立索引

低基数列是指取值较少的列，例如gender（假设只有“男”和“女”两种值）。对于这类列，建立索引往往效果不明显，因为索引并没有太多不同的值可以优化查询。对于低基数列，索引的建立反而可能会降低查询效率。

2.6 常见索引的类型

2.6.1 主键索引（Primary Key）

主键索引是表中唯一且非空的索引。通常情况下，每个表都有一个主键。

CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(100)
);

在创建表时，id列已经隐式地创建了一个主键索引。

2.6.2 唯一索引（Unique Index）

唯一索引确保索引列中的值唯一，但允许NULL值存在。

CREATE UNIQUE INDEX idx_email ON users(email);

此索引确保每个email值都是唯一的。

2.6.3 普通索引（Normal Index）

普通索引是最常用的索引类型，它允许列的值重复。

CREATE INDEX idx_name ON users(name);

2.6.4 全文索引（Full-text Index）

全文索引用于加速文本搜索，特别是对TEXT或VARCHAR类型的数据进行全文检索时。

CREATE FULLTEXT INDEX idx_message ON logs(message);

这种索引非常适合做LIKE查询，尤其是当需要通过关键词搜索时。

2.6.5 哈希索引（Hash Index）

哈希索引通过哈希表加速查找，但不支持范围查询。通常只有在使用MEMORY存储引擎时才会使用哈希索引。

2CREATE INDEX idx_hash ON users USING HASH(name);

2.6.6 复合索引（Composite Index）

复合索引是由多个列组合而成的索引，它适用于查询涉及多个列的情况。当查询的条件是多个字段组合时，复合索引可以大大提高查询性能。这个前面已经举过例子。

2.7 索引类型的性能排序

从性能角度来看，各种索引类型的使用场景和效率不同，选择索引时要根据查询的实际需求来进行权衡。以下是按性能影响排序的一些常见索引类型（按一般情况来排序，实际效果还取决于查询的复杂性、表的数据量以及MySQL的配置）：

主键索引（Primary Key）
- 性能最优：主键索引是唯一的且非空的，它不仅保证了数据的一致性，还能够加速基于主键字段的查询。
- 性能理由：主键索引是MySQL的聚簇索引（Clustered Index），意味着数据在物理磁盘上的存储顺序和主键索引一致。这样，查询时不需要额外的磁盘访问，可以直接获取数据。
- 使用场景：主键字段查询。
复合索引（Composite Index）
- 性能优：当查询条件包含多个列时，复合索引可以显著提高查询效率，因为它结合了多个字段的条件进行查询。
- 性能理由：复合索引减少了MySQL需要遍历的数据量，比多个单列索引效率高。
- 使用场景：多个字段的组合查询，如WHERE name = 'John' AND age = 25。
唯一索引（Unique Index）
- 性能良好：唯一索引除了保证数据唯一性外，通常也会加速查询，尤其是当查询条件涉及唯一字段时。
- 性能理由：唯一索引实际上与主键索引类似，但它不要求列值为非NULL，并且有时不一定是聚簇索引。
- 使用场景：当需要保证某列的唯一性，并且该列也常用于查询时。
普通索引（Normal Index）
- 性能一般：普通索引是MySQL中最常见的索引类型，它对单列或多列的查询提供加速作用，但没有唯一性要求，性能不如主键或唯一索引。
- 性能理由：普通索引有较低的性能要求，但不会像聚簇索引那样优化数据存储。它仍然有助于加速查询，但相对较为基础。
- 使用场景：用于加速单列查询，特别是那些查询条件重复的字段。
全文索引（Full-text Index）
- 性能适中：全文索引适合用于全文搜索，尤其是对TEXT或VARCHAR类型的字段进行关键词搜索时。它非常适用于LIKE '%keyword%'类查询，能够加速匹配关键词的搜索。
- 性能理由：全文索引采用倒排索引技术，能非常高效地搜索文本数据，但它只适用于部分类型的查询。
- 使用场景：需要对大文本字段进行关键词搜索的场景。
哈希索引（Hash Index）
- 性能一般：哈希索引通常只在内存存储引擎（如MEMORY引擎）中使用，对于查找操作非常高效，但不支持范围查询。
- 性能理由：哈希索引是通过计算哈希值进行查找，适合精确查找，但不适合区间查询（比如BETWEEN、>等操作）。
- 使用场景：适用于内存数据库以及需要频繁进行等值查询的场景，但不适用于范围查询。

2.7 常见索引操作与优化

1. 创建唯一索引

单独创建唯一索引

CREATE UNIQUE INDEX index_name ON tbl_student(stu_name);

2.使用ALTER语句创建唯一索引

ALTER TABLE tbl_student ADD UNIQUE INDEX index_name(stu_name);

3.创建表的同时创建唯一索引

CREATE TABLE demo (
    id INT,
    name VARCHAR(20),
    PRIMARY KEY (id),  -- 添加主键索引
    UNIQUE (name)      -- 添加唯一索引
);

2. 查看表中的索引

组合索引是由多个字段构成的索引，它在查询条件包含多个字段时非常有用。

CREATE INDEX index_name_sex ON tbl_student(stu_name, stu_sex);

3. 创建唯一索引

SHOW INDEXES FROM tbl_student;

4. 查询索引信息的字段含义

SHOW INDEXES命令返回的字段及其含义:

Table：索引所在的表名。
Non_unique：如果是唯一索引，值为0；如果是普通索引，值为1。
Key_name：索引名称。
Seq_in_index：索引列在索引中的位置（单列索引为1，复合索引则依次为1, 2, 3…）。
Column_name：索引列的名称。
Collation：索引列的排序方式，A为升序，D为降序，NULL表示无特定排序。
Cardinality：估计索引列中的唯一值的数目，值越大表示数据的唯一性越高。
Sub_part：索引列中实际参与索引的字符数量，如果是完整列则为NULL。
Packed：表示是否使用压缩，NULL表示未压缩。
Null：索引列是否允许NULL值，YES表示允许，NO表示不允许。
Index_type：索引使用的存储结构，如BTREE、HASH等。
Comment：备注信息。
Index_comment：索引的额外备注信息。
Visible：索引是否可见。
Expression：表达式索引。

5. 删除索引

删除索引的语法如下：

DROP INDEX index_name ON tbl_student;

示例：

DROP INDEX index_name_sex ON tbl_student;

EXPLAIN SELECT * FROM tbl_student WHERE stu_name = 'sdsd';

EXPLAIN的字段说明：

id：查询的执行顺序。如果多个子查询存在，id值较大的子查询会先执行。
select_type：查询类型，包括：
- SIMPLE：简单查询；
- PRIMARY：主查询；
- SUBQUERY：子查询；
- DERIVED：临时表查询（如FROM子句中存在子查询）。
table：查询的表名。
type：查询类型（访问类型），常见的值包括：
- ALL：全表扫描；
- index：索引扫描；
- range：范围扫描；
- ref：非唯一索引扫描；
- eq_ref：唯一索引扫描；
- const：常量扫描；
- system：表中只有一行数据，类似于const。
possible_keys：可能使用的索引（不一定被实际使用）。
key：实际使用的索引。
key_len：索引字段的最大长度。
ref：连接条件，当使用主键时值为const。
rows：MySQL估计的需要扫描的行数，值越小查询效率越高。
filtered：筛选条件过滤后剩余记录的百分比。
Extra：额外的执行信息，常见的包括：
- Using index：直接使用索引。
- Using where：使用WHERE条件过滤。
- Using filesort：排序时无法使用索引，使用了外部排序。
- Using temporary：使用临时表。
- Using index condition：使用索引中的条件过滤。
- Backward index scan：反向索引扫描。

7. 查询优化

根据EXPLAIN的输出，可以了解查询的性能瓶颈，并采取措施优化查询：

Avoid ALL scans：全表扫描通常意味着查询效率低下，应该尽量避免。
Use Using index：当查询中使用到索引时，查询会更高效。确保查询条件与索引列顺序匹配。
Check Using filesort：如果查询涉及排序且没有使用索引进行排序，MySQL会使用外部排序，可能影响性能。
Examine rows：查询时估计扫描的行数越少越好，尽量通过索引减少扫描的行数。

1.8 总结

索引的合理使用能显著提高MySQL数据库的查询性能，但不当的使用会带来性能下降。我们需要根据具体的查询需求来选择合适的字段创建索引，并考虑索引的类型、顺序及数量，以达到最佳的查询效率。

2. Mysql集群

MySQL的主从复制技术是一种常见的数据库分布式架构，广泛用于数据的备份、负载均衡和高可用性设计。接下来，将深入了解不同的主从复制模式及其配置。

2.1 主从复制的基本概念

主从复制的核心思想是：有一个主数据库（Master），它负责所有数据的写操作；多个从数据库（Slave）会复制主数据库的数据并提供只读服务。主从复制通过二进制日志（binlog）进行数据同步。每当主库有数据更改时，这些更改会被记录到binlog中，并通过复制协议传递给从库。

2.2 一主一从

配置步骤：

配置主库：

在主库中修改my.cnf，启用二进制日志（binlog）。

   [mysqld]
   log-bin=mysql-bin
   server-id=1

重启主库MySQL服务。

创建复制账号：

   CREATE USER 'replica_user'@'%' IDENTIFIED BY 'password';
   GRANT REPLICATION SLAVE ON *.* TO 'replica_user'@'%';
   FLUSH PRIVILEGES;

配置从库：

在从库的my.cnf中设置唯一的server-id，并指向主库的地址。

   [mysqld]
   server-id=2

启动从库，连接到主库并开始复制。

   CHANGE MASTER TO
     MASTER_HOST='master_ip',
     MASTER_USER='replica_user',
     MASTER_PASSWORD='password',
     MASTER_LOG_FILE='mysql-bin.000001',
     MASTER_LOG_POS=  154;
   START SLAVE;

验证复制状态：
在从库中运行：

   SHOW SLAVE STATUS\G

如果Slave_IO_Running和Slave_SQL_Running都显示为Yes，说明复制配置成功。

2.3 一主多从

在一主多从架构中，多个从库可以用来分担主库的读取压力，实现读写分离。主库依然负责数据的写入操作，而从库则主要用于处理查询请求。可以通过负载均衡器将查询分配到多个从库。

配置思路：

主库配置与一主一从一致。
每个从库都要配置独立的server-id和复制账号。
从库的配置类似一主一从，只不过可以有多个从库连接主库。

2.4 互为主从（双主复制）

双主复制指的是两个MySQL实例互为主从，数据会在两个数据库之间双向同步。这种模式通常用于实现高可用性，在一个主库发生故障时，另一个主库可以接管。

配置步骤：

配置两个MySQL实例，并分别配置独立的server-id。
在my.cnf中都开启log-bin。
在两个实例上创建互相复制的账号。
在每个主库中配置CHANGE MASTER TO指向对方。

注意： 双主复制容易产生数据冲突，尤其是两个实例都有写入操作时。如果没有合适的冲突解决机制，可能会导致数据丢失或不一致。因此，一般建议在应用层进行严格的写入控制。

2.5 多主多从

多主多从复制架构适用于高流量、高可用场景，特别是在数据中心分布式部署时。每个节点都可以作为主库，负责写入操作，且所有主库之间都进行数据同步。同时，多个从库负责读取操作。

配置步骤：

每个主库配置独立的server-id，并且所有主库之间需要建立双向复制关系。
配置多个从库，分担读操作。

注意： 这种架构的困难在于，如何避免不同主库之间的数据冲突。通常需要使用分布式事务或最终一致性方案来解决。

4. 总结

MySQL的索引和主从复制是数据库优化和高可用性架构的核心。通过合理配置索引和选择合适的主从复制模式，可以显著提升数据库性能并保证系统的高可用性。在生产环境中，数据库的配置需要根据具体的业务需求来调整，并不断优化。后面将会总结关于备份的相关知识。

没有评论在学术类其他

欢迎你

nkion 的个人博客

MySQL进阶-在linux里面的实践（权限管理、索引、集群）

1.权限管理

1. MySQL 权限管理基础

1.1 用户账户的格式

1.2 用户账户的创建

1.3 查看现有用户

2. 授予权限

2.1 授予权限

2.2 授予所有权限

2.3 授予权限并允许用户授予权限

3. 撤销权限

4. 刷新权限

5. 删除用户

6.权限查看

6.1 查看当前用户的权限

6.2 查看所有用户的权限

7. 常见权限管理操作

7.1 创建用户并授予权限

7.2 修改用户的密码

7.4 为特定主机授予权限

总结

2. 索引

2.1 索引的基本概念

2.2 索引的工作原理

2.3 创建索引的例子

2.4 索引的使用场景

2.4.1 WHERE子句中的查询条件

2.4.2 JOIN操作

2.4.3 ORDER BY和GROUP BY

2.5 索引优化策略

2.5.1 选择性高的列适合建立索引

2.5.2 避免创建过多的索引

2.5.3 使用复合索引

2.5.4 避免对低基数列建立索引

2.6 常见索引的类型

2.6.1 主键索引（Primary Key）

2.6.2 唯一索引（Unique Index）

2.6.3 普通索引（Normal Index）

2.6.4 全文索引（Full-text Index）

2.6.5 哈希索引（Hash Index）

2.6.6 复合索引（Composite Index）

2.7 索引类型的性能排序

2.7 常见索引操作与优化

1. 创建唯一索引

2. 查看表中的索引

3. 创建唯一索引

4. 查询索引信息的字段含义

5. 删除索引

7. 查询优化

1.8 总结

2. Mysql集群

2.1 主从复制的基本概念

2.2 一主一从

配置步骤：

2.3 一主多从

配置思路：

2.4 互为主从（双主复制）

配置步骤：

2.5 多主多从

配置步骤：

4. 总结