数据库

mysql

索引失效情况

回表概念

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
1.聚集索引的B+树，性能最优，叶子节点存储的数据是整行的所有字段数据(主键索引)
2.非聚集索引的B+树，非聚集索引列可能是一列，也可能是多列（联合索引），
  叶子节点存储的数据是非聚集索引列（1列或多列）的数据和聚集索引列


用户user表4列（id, userCode, userName, userSex）
id是主键（聚集索引）；userCode 是非聚集索引，此时会创建2个索引的B+树

聚集索引的B+树，叶子节点保存了4列（id, userCode, userName, userSex）的数据
非聚集索引的B+树，叶子节点保存了2列（id, userCode）的数据

不回表
走主键索引不回表，因为挂载的是整列数据
select * from user where id = 1 
走非聚集索引不回表，因为叶子结点挂载了非聚集索引和聚集索引的值
select id,userCode from user where userCode = 1 


回表
因为查询的列除了主键id和非聚集索引userCode还有
userName, userSex，这两个叶子节点没有存数据，会通过主键索引id回表来
查询userName, userSex的值，因为主键索引id挂载的整列的值
select id,userCode，userSex from user where userCode = 1
select id,userCode，userName from user where userCode = 1
select * from user where userCode = 1

常用sql元数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
#指定数据库的详细信息
SELECT 
  TABLE_SCHEMA AS '数据库',
  TABLE_NAME AS '表名',
  TABLE_ROWS AS `行数`,
  ROUND( (DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024, 2) AS '总大小(MB)',
  ROUND(DATA_LENGTH / 1024 / 1024, 2) AS '数据大小(MB)',
  ROUND(INDEX_LENGTH / 1024 / 1024, 2) AS '索引大小(MB)',
  TABLE_ROWS AS '数据行数（估算值）'
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'y_back'  -- 替换为你的数据库名
ORDER BY (DATA_LENGTH + INDEX_LENGTH) DESC;  -- 按总大小排序



#数据库表的行数
SELECT 
  TABLE_NAME AS '表名',
  TABLE_ROWS AS '估算行数',
  (SELECT COUNT(*) FROM y_back.t_work) AS '精确行数'  -- 替换为实际表名
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'y_back';

#all
SELECT
	table_schema AS '数据库',
	sum( table_rows ) AS '记录数',
	sum(
	TRUNCATE ( data_length / 1024 / 1024, 2 )) AS '数据容量(MB)',
	sum(
	TRUNCATE ( index_length / 1024 / 1024, 2 )) AS '索引容量(MB)' 
FROM
	information_schema.TABLES 
GROUP BY
	table_schema 
ORDER BY
	sum( data_length ) DESC,
	sum( index_length ) DESC;

#过滤元数据库
SELECT
  table_schema AS '数据库',
  SUM(table_rows) AS '记录数',
  TRUNCATE(SUM(data_length) / 1024 / 1024, 2) AS '数据容量(MB)',  -- 先求和再转换单位
  TRUNCATE(SUM(index_length) / 1024 / 1024, 2) AS '索引容量(MB)'
FROM
  information_schema.TABLES
WHERE
  table_schema NOT IN (
    'information_schema', 
    'mysql', 
    'performance_schema', 
    'sys'
  )
GROUP BY
  table_schema
ORDER BY
  SUM(data_length) DESC,
  SUM(index_length) DESC;

ANALYZE TABLE - 更新统计信息
OPTIMIZE TABLE - 表优化重组俗称清理碎片

#造数据
CREATE TABLE `t_work_db01` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `name` varchar(256) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL,
  `age` int DEFAULT NULL,
  `sex` char(1) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL,
  `money` float DEFAULT NULL COMMENT '金额',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=100001 DEFAULT CHARSET=utf8mb3 ROW_FORMAT=DYNAMIC


DELIMITER $$
CREATE PROCEDURE InsertDummyData()
BEGIN
    DECLARE i INT DEFAULT 0;
    WHILE i < 100000 DO
        INSERT INTO t_work (NAME, age, sex, money)
        VALUES (
            -- 随机生成用户名（示例：User_XXXX）
            CONCAT('User_', SUBSTRING(MD5(RAND()) FROM 1 FOR 8)),
            -- 随机年龄 18~65 岁
            FLOOR(18 + RAND() * 48),
            -- 随机性别（M/F）
            IF(RAND() < 0.5, 'M', 'F'),
            -- 随机金额 1000~10000（保留两位小数）
            ROUND(1000 + RAND() * 9000, 2)
        );
        SET i = i + 1;
    END WHILE;
END$$
DELIMITER ;

-- 执行存储过程
CALL InsertDummyData();

DROP PROCEDURE IF EXISTS InsertDummyData;  -- 强制删除（如果存在）

mysql读写分离

github

ProxySql

MaxScale

mysql分表分库

mycat

pgsql

PostgreSQL psql 常用命令

概念

1
2
3
4
5
6
7
8
9
10
11
12
13
14
PUBLIC 是 PostgreSQL 数据库中一个特殊的角色组，在元数据表（pg_roles）中都查不到该角色，
数据库中所创建的角色都可以理解为是 PUBLIC 角色组成员。
而且对 PUBLIC 权限的继承完全不受 NOINHERIT 的控制，
一旦创建了一个拥有 login 权限的角色，它会立即继承 PUBLIC 角色组拥有的权限，
此时如果想通过 revoke（比如 revoke connect on database）来回收的话不会成功，
只能从 PUBLIC 组回收相关权限（比如 revoke connect on database from PUBLIC）

REVOKE CONNECT ON DATABASE test FROM PUBLIC;
--这样普通用户就无法自由切换数据库，默认数据库下面有一个public的scheme

MySQL 的 datadir ≈ PostgreSQL 的默认表空间 pg_default
PostgreSQL 的 pg_default 表空间对应默认数据目录（由参数 data_directory 配置），类似于 MySQL 的 datadir。
PostgreSQL 的数据库 ≈ MySQL 的实例（但更轻量）。
PostgreSQL 的模式 ≈ MySQL 的数据库

基本使用命令

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
docker run -id --name=pgsql -v postgre-data:/var/lib/postgresql/data -p 54222:5432 -e POSTGRES_PASSWORD=123456 -e LANG=C.UTF-8 bitnami/postgresql

docker exec -it -uroot pgsql bash
psql -U postgres -W -n
cat .psql_history 
psql -h host -p port -d dbname -U  user -W

使用反斜线作为命令前缀.
  postgres=# \db
# 输出的信息如下：
       List of tablespaces
    Name    |  Owner   | Location 
------------+----------+----------
 pg_default | postgres | 
 pg_global  | postgres | 
(2 rows)

退出    \q
 列出所有的数据库      \l
 列出所有的数据库的大小      \l+
 更改当前连接的数据库       \c
 列出当前数据库的连接信息    \connect
 列出当前数据库和连接的详细信息 \conninfo
 查看当前数据库里面的表和拥有者和表大小         \dt+
 展示所有用户           \dg
 ​模式 \dn
 查看所有表名的列表             \d
 获取表结构                   \da
 展示所有用户               \du
 查看t_sms表的结构      \d t_sms 
 展示数据库里面的所有的表         \dt
 列出所有的数据库的详细信息（包括数据库大小和字符格式）         \l+
 显示用户访问权限。                            \z或\dp
 显示所有可设置的访问权限                     \h GRAN
 显示用户的对所有数据库表的详细访问权限     \dp或者\z
 确认当前连接的用户为超级用户postgres，且该用户后创建角色和数据库的权限等     #select current_user;
 在超级用户连接postgres后，设置不允许普通用户a连接数据库         #alter role a nologin;
 ​
 使用普通用户a连接数据库正常                   #\c highgo a



快速查看当前所有用户：\du
查看详细用户信息：select * from pg_user;
查看详细角色信息：select * from pg_roles;
查看当前登录用户：select user;
一般建议先创建用户然后使用这个用户去创建数据库模式，因为数据库那个用户创建的默认Owner就是这个用户
创建用户：CREATE USER $user_name PASSWORD '$password';
创建角色：CREATE ROLE $role_name;
 
修改用户与角色：ALTER USER[ROLE] $user_name 
        e.g.
        //修改用户名：ALTER USER U2 RENAME TO U22;
        //修改用户的密码：ALTER USER U22 PASSWORD'U22;
        //修改用户的权限：ALTER USER u22 CREATEROLE;
        //修改数据库 testdb中的参数重设为默认值：ALTER USER u22 IN DATABASE testdb RESET all1;
        //修改角色的名字：ALTER ROLE dev RENAME TO dev1;
        //修改角色的权限：ALTER ROLE dev1 SUPERUSER;
        //修改角色的权限：ALTER ROLE dev1 LOGIN;
 
删除用户与角色：DROP USER[ROLE] [IF EXISTS] $user_name
 
授权用户某个角色:GRANT $role_name TO $user_name;    (授权后set role $role_name启用生效）
 

create user test with password 'rong ';
CREATE DATABASE testdb OWNER test;
GRANT ALL PRIVILEGES ON DATABASE testdb TO test;
alter user qh with password '123';
\password qh;  //需要输入两次密码（推荐）


manggodb

详细使用教程

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
docker run -itd --name mongo -v /docker_volume/mongodb/data:/data/db -p 27017:27017 mongo:4.4 --auth
–auth：需要密码才能访问容器服务；
mongodb安装好后第一次进入是不需要密码的，也没有任何用户，通过shell命令可直接进入
use admin 使用admin数据库并进行验证，如果不验证，是做不了任何操作的
db.auth("root","123456")  返回1表示成功 
验证之后还是做不了操作，因为root只有用户管理权限，下面创建用户，用户都跟着库走
use mydb 
db.createUser({user: "admin",pwd: "123456",roles: [{ role: "readWrite", db: "mydb" }]}) 
通过admin用户增删改查


docker exec -it mongo mongo admin


db.createUser({ user:'root',pwd:'123456',roles:[ { role:'userAdminAnyDatabase', db: 'admin'},'readWriteAnyDatabase']});
【role:‘userAdminAnyDatabase’】：只在admin数据库中可用，赋予用户所有数据库的userAdmin权限
【db: ‘admin’】：可操作的数据库
【‘readWriteAnyDatabase’】：赋予用户读写权限
mongoDB 没有无敌用户root，只有能管理用户的用户 userAdminAnyDatabase 


SQL 术语/概念	MongoDB 术语/概念   解释/说明
database	    database	       数据库
table	        collection	       数据库表/集合
row	            document	       数据记录行/文档
column	        field	           数据字段/域
index	        index	           索引
table joins	 	表连接,            MongoDB不支持
primary key	    primary key	       主键,MongoDB自动将_id字段设置为主键

sqlserver

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
docker run -d \
  --name sqlserver --user=root \
  -e "ACCEPT_EULA=Y" \
  -e "SA_PASSWORD=Testing@123"  -p 1433:1433  -v /data/sqlserver:/var/opt/mssql \
  --cap-add SYS_PTRACE mcr.microsoft.com/mssql/server:2019-latest

  /opt/mssql-tools18/bin/sqlcmd -S localhost -U SA -P "Testing@123" -C
  
-- 数据库级
  SELECT name, type_desc FROM sys.database_principals WHERE type IN ('S', 'U', 'G');
  select * from master.dbo.SysDatabases
  --服务器级
  SELECT name, type_desc FROM sys.server_principals WHERE type IN ('S', 'U', 'G');
  go
  SELECT DB_NAME() AS [CurrentDatabase];
  USE master; SELECT name FROM sys.schemas
  USE master; SELECT name FROM sys.tables
  SELECT name, USER_NAME(principal_id) FROM sys.schemas;

CREATE TABLE Users (    UserID INT PRIMARY KEY IDENTITY(1,1),     UserName NVARCHAR(50) NOT NULL,   Email NVARCHAR(100) NOT NULL, RegistrationDate DATETIME DEFAULT GETDATE(), IsActive BIT DEFAULT 1);
INSERT INTO Users (UserName, Email) VALUES     ('王五', 'wangwu@example.com'),    ('赵六', 'zhaoliu@example.com'),    ('孙七', 'sunqi@example.com');

--cdc
use y_test
EXEC sys.sp_cdc_enable_db;
go
 SELECT name AS [y_test],    is_cdc_enabled AS [CDCEnabled]FROM   sys.databases WHERE     name = DB_NAME();