mysql的基础用法&索引&分表

直接上干货，你要的这里都有

一、基础操作与常用写法

1. ​SELECT 查询​

-- 查询所有字段  
SELECT * FROM users;  

-- 查询特定字段（推荐明确指定字段）  
SELECT name, email FROM users;  

-- 条件查询（WHERE）  
SELECT * FROM orders WHERE total_amount > 100;  

-- 排序（ORDER BY）  
SELECT * FROM products ORDER BY price DESC;  

-- 去重（DISTINCT）  
SELECT DISTINCT city FROM customers;  

-- 分页（LIMIT + OFFSET）  
SELECT * FROM products ORDER BY id LIMIT 10 OFFSET 20; -- 第3页（每页10条）  

-- 联合查询（UNION）  
SELECT 'active' AS status, COUNT(*) FROM users WHERE last_login > '2023-01-01'  
UNION  
SELECT 'inactive', COUNT(*) FROM users WHERE last_login <= '2023-01-01';  

​场景​：数据检索、分页展示、合并统计结果。

2. ​INSERT 插入​

-- 插入单条数据  
INSERT INTO users (name, email) VALUES ('John', 'john@example.com');  

-- 插入多条数据  
INSERT INTO products (name, price) VALUES  
('Laptop', 999.99),  
('Phone', 599.99);  

-- 插入子查询结果  
INSERT INTO user_backup (id, name)  
SELECT id, name FROM users WHERE created_at < '2020-01-01';  

​场景​：用户注册、批量导入数据、数据归档。

3. ​UPDATE 更新​

-- 更新单个字段  
UPDATE users SET email = 'new@example.com' WHERE id = 1;  

-- 更新多个字段  
UPDATE orders SET status = 'shipped', updated_at = NOW() WHERE id = 100;  

-- 关联更新（JOIN）  
UPDATE users u  
JOIN orders o ON u.id = o.user_id  
SET u.last_order_time = o.order_time  
WHERE o.status = 'completed';  

​场景​：用户信息修改、订单状态同步。

4. ​DELETE 删除​

-- 删除符合条件的记录  
DELETE FROM logs WHERE created_at < '2023-01-01';  

-- 清空表（谨慎使用）  
TRUNCATE TABLE temp_data;  

-- 关联删除（JOIN）  
DELETE u FROM users u  
LEFT JOIN orders o ON u.id = o.user_id  
WHERE o.id IS NULL; -- 删除无订单的用户  

​场景​：清理过期数据、删除无效记录。

5. ​JOIN 连接​

-- INNER JOIN（关联订单和用户信息）  
SELECT orders.id, users.name  
FROM orders  
INNER JOIN users ON orders.user_id = users.id;  

-- LEFT JOIN（查找没有下单的用户）  
SELECT users.name, orders.id  
FROM users  
LEFT JOIN orders ON users.id = orders.user_id  
WHERE orders.id IS NULL;  

-- 自连接（上下级关系查询）  
SELECT e.name AS employee, m.name AS manager  
FROM employees e  
LEFT JOIN employees m ON e.manager_id = m.id;  

​场景​：关联数据查询、数据缺失分析、层级结构展示。

6. ​子查询​

-- WHERE 子查询（筛选工资高于平均值的员工）  
SELECT * FROM employees  
WHERE salary > (SELECT AVG(salary) FROM employees);  

-- FROM 子查询（统计部门平均工资）  
SELECT dept_id, AVG(salary) AS avg_salary  
FROM (SELECT * FROM employees WHERE hire_year = 2023) AS new_employees  
GROUP BY dept_id;  

-- EXISTS 子查询（查找下单用户）  
SELECT * FROM users u  
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);  

​场景​：复杂条件筛选、中间结果复用。

7. ​聚合与分组​

-- 统计总数（COUNT）  
SELECT COUNT(*) FROM orders WHERE status = 'completed';  

-- 求和（SUM）  
SELECT user_id, SUM(amount) AS total_spent  
FROM payments  
GROUP BY user_id;  

-- 最大值/最小值（MAX/MIN）  
SELECT department, MAX(salary), MIN(salary) FROM employees GROUP BY department;  

-- 分组筛选（HAVING）  
SELECT user_id, COUNT(*) AS order_count  
FROM orders  
GROUP BY user_id  
HAVING order_count > 10;  

​场景​：报表统计、用户行为分析。

8. ​事务处理​

START TRANSACTION;  
UPDATE accounts SET balance = balance - 100 WHERE id = 1;  
UPDATE accounts SET balance = balance + 100 WHERE id = 2;  
COMMIT;  

-- 错误时回滚  
ROLLBACK;  

​场景​：转账、库存扣减等需要原子性的操作。

二、索引优化

1. ​索引类型与创建​

-- 单列索引（高频查询字段）  
CREATE INDEX idx_email ON users(email);  

-- 联合索引（多条件查询优化）  
CREATE INDEX idx_user_status ON orders(user_id, status);  

-- 唯一索引（防重复）  
CREATE UNIQUE INDEX idx_username ON users(username);  

-- 全文索引（文本搜索）  
CREATE FULLTEXT INDEX idx_content ON articles(content);  

2. ​覆盖索引优化​

-- 索引包含查询所需字段（无需回表）  
CREATE INDEX idx_order_user_date ON orders(user_id, order_date, total_amount);  

-- 查询时直接使用索引  
SELECT user_id, order_date, total_amount  
FROM orders  
WHERE user_id = 100 AND order_date > '2023-01-01';  

3. ​索引失效与规避​

-- 错误写法（索引失效）  
SELECT * FROM users WHERE YEAR(created_at) = 2023;      -- 对索引列使用函数  
SELECT * FROM users WHERE email LIKE '%@example.com';    -- 前导模糊查询  
SELECT * FROM users WHERE CAST(id AS CHAR) = '100';     -- 隐式类型转换  

-- 正确优化写法  
SELECT * FROM users WHERE created_at BETWEEN '2023-01-01' AND '2023-12-31';  
SELECT * FROM users WHERE email LIKE 'john@%';  
SELECT * FROM users WHERE id = 100;  

4. ​索引监控与维护​

-- 查看索引使用情况  
SHOW INDEX FROM users;  

-- 分析查询执行计划  
EXPLAIN SELECT * FROM orders WHERE user_id = 100;  

-- 重建索引（优化碎片化）  
ALTER TABLE orders REBUILD INDEX idx_user_status;  

三、分库分表

1. ​分片策略​

2. ​分库分表示例​

-- 水平分表（按 user_id 哈希分16张表）  
CREATE TABLE orders_0 (  
  order_id BIGINT PRIMARY KEY,  
  user_id INT,  
  amount DECIMAL(10,2),  
  INDEX idx_user (user_id)  
);  
-- ... 其他分表 orders_1 到 orders_15  

-- 分片查询路由  
SELECT * FROM orders_${user_id % 16} WHERE user_id = 100;  

3. ​分库分表中间件配置（ShardingSphere示例）​​

dataSources:  
  ds_0:  
    url: jdbc:mysql://db1:3306/db0  
  ds_1:  
    url: jdbc:mysql://db2:3306/db1  

shardingRules:  
  tables:  
    orders:  
      actualDataNodes: ds_${0..1}.orders_${0..15}  
      databaseStrategy:  
        inline:  
          shardingColumn: user_id  
          algorithmExpression: ds_${user_id % 2}  
      tableStrategy:  
        inline:  
          shardingColumn: user_id  
          algorithmExpression: orders_${user_id % 16}  

4. ​分库分表挑战与解决​

• ​全局唯一ID​：使用雪花算法（Snowflake）、Redis自增或数据库号段。

  -- 雪花算法生成ID（Java示例）  
  long id = Snowflake.generateId();  

• ​跨分片查询​：
  • 汇总统计：通过中间件自动合并结果（如 SELECT COUNT(*) FROM orders）。
  • 排序分页：需从所有分片查询后聚合（性能较低，需业务折中）。
• ​分布式事务​：使用 Seata、XA协议或最终一致性方案。

四、典型场景完整示例

1. ​电商订单分库分表查询​

-- 查询用户最近订单（自动路由到对应分片）  
SELECT * FROM orders  
WHERE user_id = 100  
ORDER BY order_time DESC  
LIMIT 5;  

-- 统计月度销售额（需合并所有分片数据）  
SELECT DATE_FORMAT(order_time, '%Y-%m') AS month, SUM(amount)  
FROM orders  
GROUP BY month;  

2. ​社交平台用户关系​

-- 通过覆盖索引优化好友查询  
CREATE INDEX idx_user_friend ON friendships(user_id, friend_id, status);  

SELECT friend_id FROM friendships  
WHERE user_id = 100 AND status = 'active';  

五、总结

1. ​基础操作​：涵盖 SELECT/INSERT/UPDATE/DELETE/JOIN 等高频语句，注意明确字段和分页优化。
2. ​索引优化​：
   • 优先为 WHERE、ORDER BY、GROUP BY 字段创建索引。
   • 使用覆盖索引减少回表，监控索引使用率。
3. ​分库分表​：
   • 单表数据量超过 ​500万​ 时考虑分片。
   • 选择合适的分片键（如 user_id），避免跨分片查询。
4. ​工具推荐​：
   • 性能分析：EXPLAIN、pt-query-digest
   • 分库分表：ShardingSphere、Vitess