mysql 查询笔记

Mysql查询笔记

• 导出

mysqldump -u username -p dbname --ignore-table=dbname.table1 --ignore-table=dbname.table2 > backup.sql

• 导入mysql数据

mysql -u username -p dbname < file.sql

• 查询表大小

SELECT table_name AS "Table", 
ROUND(((data_length + index_length) / 1024 / 1024), 2) AS "Size (MB)" 
FROM information_schema.TABLES 
WHERE table_schema = "your_database_name" 
ORDER BY (data_length + index_length) DESC;

• 列出当前正在执行的查询

SHOW PROCESSLIST;
KILL 123;

• EXPLAIN 命令

EXPLAIN SELECT * FROM <table_name> WHERE <condition>;
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | users |

---

id: 查询标识符，每个 SELECT 查询都会分配一个唯一的标识符

select_type: 查询类型，包括 SIMPLE、PRIMARY、UNION、SUBQUERY、DERIVED 等

table: 查询涉及的表名

type: 查询使用的索引类型，包括 const、eq_ref、ref、range、index、ALL 等

possible_keys: 可能使用的索引列表

key: 实际使用的索引

key_len: 使用的索引长度

ref: 列与索引之间的关系

rows: 扫描的行数

Extra: 其他信息，例如使用的临时表、排序方式等

mysql 分区

MySQL 分区是一种将大表拆分为多个小表的技术，可以提高查询性能和管理效率。MySQL 提供了多种分区方式，包括 RANGE、LIST、HASH 和 KEY 四种方式。

以下是一个简单的示例，展示如何创建一个 RANGE 分区表：

1. 创建一个包含分区键的表

CREATE TABLE mytable (
  id INT NOT NULL,
  name VARCHAR(50) NOT NULL,
  age INT NOT NULL,
  created_at DATETIME NOT NULL,
  PRIMARY KEY (id, created_at)
) PARTITION BY RANGE (YEAR(created_at)) (
  PARTITION p0 VALUES LESS THAN (2015),
  PARTITION p1 VALUES LESS THAN (2016),
  PARTITION p2 VALUES LESS THAN (2017),
  PARTITION p3 VALUES LESS THAN (2018),
  PARTITION p4 VALUES LESS THAN (2019),
  PARTITION p5 VALUES LESS THAN (2020)
);

这个表包含四个字段：id、name、age 和 created_at，其中 id 和 created_at 组成了联合主键。表的分区键是 created_at 字段的年份，即按照年份对表进行分区。在创建表时，通过 PARTITION BY 子句指定了分区方式，这里采用了 RANGE 分区方式，并且分为了六个分区，每个分区对应一个年份。

2. 插入数据
   可以向分区表中插入数据，MySQL 会自动根据分区键将数据分配到相应的分区中：

INSERT INTO mytable (id, name, age, created_at) VALUES
(1, 'Alice', 25, '2016-05-01'),
(2, 'Bob', 30, '2017-07-01'),
(3, 'Charlie', 35, '2018-09-01'),
(4, 'Dave', 40, '2019-11-01');

3. 查询数据

SELECT * FROM mytable WHERE created_at BETWEEN '2017-01-01' AND '2018-12-31';