MySQL indexoptimization

1. indexoverview

index is MySQLin用于improvingqueryperformance important tool. 它 is adatastructure, helpingMySQL high 效地finddata, class似于书籍 Table of Contents.

1.1 index 作用

• improvingquery速度: throughindex, MySQL可以直接定位 to datawhere 位置, 而不需要全表扫描
• 加速sort and group: index可以helpingMySQL fast 速sort and groupdata
• 保证data唯一性: 唯一index可以确保data 唯一性
• optimization连接query: 连接列 on index可以加速表之间 连接operation

1.2 index working principles

MySQL index基于B+treedatastructure, 这 is a平衡treestructure, 具 has 以 under 特点:

• 所 has data都store in 叶子node on
• 叶子node之间 has 指针相连, 形成 has 序链表
• 非叶子node只storeindex键值, 不storedata
• query时, from 根node开始, through比较键值 fast 速定位 to 叶子node

1.3 index Pros and Cons

优点	缺点
improvingquery速度	占用额 out store空间
加速sort and group	减 slow 插入, update and deleteoperation
保证data唯一性	增加indexmaintenance成本
optimization连接query	过 many index会降 low performance

2. indexclass型

MySQLsupport many 种class型 index, 每种index都 has 其specific 用途 and 适用场景.

2.1 主键index (PRIMARY KEY)

主键index is a特殊 唯一index, 用于唯一标识表in 每一行data. 一个表只能 has 一个主键index.

-- creation表时添加主键index
CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL
);

-- modify表添加主键index
ALTER TABLE users ADD PRIMARY KEY (id);

2.2 唯一index (UNIQUE)

唯一index确保index列 值 is 唯一 , 但允许NULL值 (最 many 一个NULL值) .

-- creation表时添加唯一index
CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) UNIQUE,
    email VARCHAR(100) UNIQUE
);

-- modify表添加唯一index
ALTER TABLE users ADD UNIQUE (username);

-- creation唯一index
CREATE UNIQUE INDEX idx_user_email ON users(email);

2.3 普通index (INDEX)

普通index is 最basic indexclass型, 没 has 唯一性约束, 用于improvingquery速度.

-- creation表时添加普通index
CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL,
    INDEX idx_user_name (name)
);

-- modify表添加普通index
ALTER TABLE users ADD INDEX idx_user_name (name);

-- creation普通index
CREATE INDEX idx_user_name ON users(name);

2.4 复合index (COMPOSITE)

复合index is 基于 many 个列creation index, 适用于 many 列query 场景.

-- creation表时添加复合index
CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    INDEX idx_name (first_name, last_name)
);

-- modify表添加复合index
ALTER TABLE users ADD INDEX idx_name (first_name, last_name);

-- creation复合index
CREATE INDEX idx_name ON users(first_name, last_name);

2.5 全文index (FULLTEXT)

全文index用于全文搜索, 适用于 big 文本字段 搜索场景.

-- creation表时添加全文index
CREATE TABLE articles (
    id INT PRIMARY KEY AUTO_INCREMENT,
    title VARCHAR(200) NOT NULL,
    content TEXT,
    FULLTEXT INDEX idx_content (title, content)
);

-- modify表添加全文index
ALTER TABLE articles ADD FULLTEXT INDEX idx_content (title, content);

-- creation全文index
CREATE FULLTEXT INDEX idx_content ON articles(title, content);

-- using全文搜索
SELECT * FROM articles WHERE MATCH(title, content) AGAINST('MySQL index');

2.6 空间index (SPATIAL)

空间index用于地理空间dataclass型, such asPOINT, LINESTRING, POLYGONetc..

-- creation表时添加空间index
CREATE TABLE locations (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    position POINT,
    SPATIAL INDEX idx_position (position)
) ENGINE=InnoDB;

-- modify表添加空间index
ALTER TABLE locations ADD SPATIAL INDEX idx_position (position);

-- creation空间index
CREATE SPATIAL INDEX idx_position ON locations(position);

3. index creation and management

MySQLproviding了 many 种method来creation, 查看 and deleteindex.

3.1 creationindex

-- method1: CREATE TABLE时creation
CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL,
    INDEX idx_name (name),
    UNIQUE INDEX idx_email (email)
);

-- method2: ALTER TABLE添加
ALTER TABLE users ADD INDEX idx_name (name);
ALTER TABLE users ADD UNIQUE INDEX idx_email (email);
ALTER TABLE users ADD PRIMARY KEY (id);

-- method3: CREATE INDEXcreation
CREATE INDEX idx_name ON users(name);
CREATE UNIQUE INDEX idx_email ON users(email);
CREATE FULLTEXT INDEX idx_content ON articles(content);

3.2 查看index

-- 查看表 所 has index
SHOW INDEX FROM users;
SHOW INDEXES FROM users;

-- 查看creation表 SQL语句 (package含index) 
SHOW CREATE TABLE users;

-- 查看datalibraryin所 has 表 index
SELECT * FROM information_schema.statistics WHERE table_schema = 'database_name';

3.3 deleteindex

-- method1: ALTER TABLEdelete
ALTER TABLE users DROP INDEX idx_name;
ALTER TABLE users DROP UNIQUE INDEX idx_email;
ALTER TABLE users DROP PRIMARY KEY;

-- method2: DROP INDEXdelete
DROP INDEX idx_name ON users;
DROP INDEX idx_email ON users;

4. indexdesignprinciples

合理 indexdesign is improvingMySQLperformance 关键. 以 under is 一些indexdesign basicprinciples:

4.1 适合creationindex 场景

• 经常用于query条件 列: WHERE子句in频繁using 列
• 用于连接 列: JOINoperationinusing 列
• 用于sort 列: ORDER BY子句inusing 列
• 用于group 列: GROUP BY子句inusing 列
• 唯一性约束 列: 需要保证唯一性 列

4.2 不适合creationindex 场景

• data量 small 表: small 表 全表扫描可能比indexfind更 fast
• 频繁update 列: updateoperation会导致index重建, 影响performance
• low 基数 列: such as性别, statusetc.只 has few 数几个值 列
• TEXT, BLOBetc. big 字段: 这些字段不适合creation普通index
• queryin不using 列: creation不using index会浪费store空间

4.3 复合index design

复合index design需要遵循"最 left before 缀principles", 即query时using 列顺序必须 and index 列顺序一致.

-- creation复合index
CREATE INDEX idx_name_age ON users(first_name, last_name, age);

--  has 效using (遵循最 left  before 缀principles) 
SELECT * FROM users WHERE first_name = 'John';
SELECT * FROM users WHERE first_name = 'John' AND last_name = 'Doe';
SELECT * FROM users WHERE first_name = 'John' AND last_name = 'Doe' AND age = 30;

-- 无效using (不遵循最 left  before 缀principles) 
SELECT * FROM users WHERE last_name = 'Doe';
SELECT * FROM users WHERE age = 30;
SELECT * FROM users WHERE last_name = 'Doe' AND age = 30;

5. indexoptimizationtechniques

以 under is 一些improvingindexefficiency optimizationtechniques:

5.1 usingindex覆盖query

index覆盖query is 指query 所 has 列都package含 in indexin, MySQL不需要回表querydata, 直接 from indexin获取结果.

-- creation覆盖index
CREATE INDEX idx_user_name_email ON users(name, email);

-- index覆盖query (只queryindexin 列) 
SELECT name, email FROM users WHERE name LIKE 'A%';

-- 非index覆盖query (需要回表querydata) 
SELECT id, name, email FROM users WHERE name LIKE 'A%';

5.2 避免index失效

以 under circumstances可能导致index失效, 应尽量避免:

• usingfunctionoperationindex列: SELECT * FROM users WHERE YEAR(create_time) = 2023;
• using不etc.于operation符: SELECT * FROM users WHERE age != 18;
• usingIS NULL or IS NOT NULL: SELECT * FROM users WHERE email IS NULL;
• usingLIKE以%开头: SELECT * FROM users WHERE name LIKE '%John';
• usingOR连接条件: SELECT * FROM users WHERE name = 'John' OR age = 18;
• class型转换: SELECT * FROM users WHERE id = '123'; (id is INTclass型)

5.3 using before 缀index

for 于较 long string列, 可以using before 缀index来reducingindex store空间 and improvingquery速度.

-- creation before 缀index
CREATE INDEX idx_user_email ON users(email(10));

-- modify表添加 before 缀index
ALTER TABLE users ADD INDEX idx_user_email (email(10));

5.4 定期重建index

随着data 插入, update and delete, index可能会变得碎片化, 影响queryperformance. 定期重建index可以improvingindexefficiency.

-- 重建表 所 has index
ALTER TABLE users ENGINE=InnoDB;

-- optimization表 (重建index) 
OPTIMIZE TABLE users;

6. 执行计划analysis

usingEXPLAIN语句可以analysisquery 执行计划, UnderstandMySQLsuch as何processingquery, from 而找出performance瓶颈并foroptimization.

6.1 EXPLAIN语句 using

-- analysis simple query
EXPLAIN SELECT * FROM users WHERE name = 'John';

-- analysis complex query
EXPLAIN SELECT u.id, u.name, o.order_date, o.total
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE u.name = 'John'
ORDER BY o.order_date DESC;

-- analysis带group query
EXPLAIN SELECT department, COUNT(*) AS employee_count
FROM employees
GROUP BY department
ORDER BY employee_count DESC;

6.2 执行计划 关键字段

字段	describes	important 性
type	访问class型 (ALL, index, range, ref, eq_ref, const, system)	high
key	using index	high
rows	估计需要扫描 行数	high
Extra	额 out information (Using index, Using where, Using temporary, Using filesort)	high
possible_keys	可能using index	in
key_len	using index long 度	in

6.3 执行计划analysisexample

-- creationexample表
CREATE TABLE employees (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL,
    department VARCHAR(50) NOT NULL,
    salary DECIMAL(10, 2) NOT NULL,
    hire_date DATE NOT NULL
);

-- 插入testdata
INSERT INTO employees (name, department, salary, hire_date) VALUES
('张三', 'techniques部', 9000.00, '2023-01-15'),
('李四', 'techniques部', 10000.00, '2023-02-20'),
('王五', '销售部', 8000.00, '2023-03-10'),
('赵六', '销售部', 8500.00, '2023-04-05'),
('钱七', '人力resource部', 7000.00, '2023-05-18');

-- analysis没 has index query
EXPLAIN SELECT * FROM employees WHERE department = 'techniques部';

-- 添加index
CREATE INDEX idx_emp_dept ON employees(department);

-- analysis has index query
EXPLAIN SELECT * FROM employees WHERE department = 'techniques部';

-- analysissortquery
EXPLAIN SELECT * FROM employees ORDER BY hire_date DESC;

-- 添加sortindex
CREATE INDEX idx_emp_hire_date ON employees(hire_date);

-- analysis has sortindex query
EXPLAIN SELECT * FROM employees ORDER BY hire_date DESC;

7. indexoptimization实战

以 under is 一些practical场景in indexoptimizationexample, helping你更 good 地understandingsuch as何usingindeximprovingperformance.

7.1 场景一: userlogin

userlogin时, 需要根据user名 and passwordqueryuserinformation.

-- optimization before : 没 has index
SELECT * FROM users WHERE username = 'admin' AND password = '123456';

-- optimization after : 添加复合index
CREATE INDEX idx_user_login ON users(username, password);
SELECT * FROM users WHERE username = 'admin' AND password = '123456';

7.2 场景二: 订单query

query指定user 订单, 按订单日期sort.

-- optimization before : 没 has index
SELECT * FROM orders WHERE user_id = 1 ORDER BY order_date DESC;

-- optimization after : 添加复合index
CREATE INDEX idx_order_user_date ON orders(user_id, order_date);
SELECT * FROM orders WHERE user_id = 1 ORDER BY order_date DESC;

7.3 场景三: 范围query

query指定日期范围 in 订单.

-- optimization before : 没 has index
SELECT * FROM orders WHERE order_date BETWEEN '2023-01-01' AND '2023-01-31';

-- optimization after : 添加index
CREATE INDEX idx_order_date ON orders(order_date);
SELECT * FROM orders WHERE order_date BETWEEN '2023-01-01' AND '2023-01-31';

7.4 场景四: many 表连接

queryuser及其订单information.

-- optimization before : 没 has index
SELECT u.name, o.order_date, o.total
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE u.name = '张三';

-- optimization after : 添加index
CREATE INDEX idx_user_name ON users(name);
CREATE INDEX idx_order_user ON orders(user_id);
SELECT u.name, o.order_date, o.total
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE u.name = '张三';

8. 完整example

-- creationexampledatalibrary
CREATE DATABASE IF NOT EXISTS ecommerce_db;
USE ecommerce_db;

-- creationuser表
CREATE TABLE IF NOT EXISTS users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) UNIQUE NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    password VARCHAR(100) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_user_email (email)
);

-- creation商品表
CREATE TABLE IF NOT EXISTS products (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(200) NOT NULL,
    category VARCHAR(100) NOT NULL,
    price DECIMAL(10, 2) NOT NULL,
    stock INT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_product_category (category),
    INDEX idx_product_price (price)
);

-- creation订单表
CREATE TABLE IF NOT EXISTS orders (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    order_date DATETIME DEFAULT CURRENT_TIMESTAMP,
    total DECIMAL(10, 2) NOT NULL,
    status VARCHAR(50) NOT NULL,
    FOREIGN KEY (user_id) REFERENCES users(id),
    INDEX idx_order_user (user_id),
    INDEX idx_order_date (order_date),
    INDEX idx_order_status (status)
);

-- creation订单商品表
CREATE TABLE IF NOT EXISTS order_items (
    id INT PRIMARY KEY AUTO_INCREMENT,
    order_id INT NOT NULL,
    product_id INT NOT NULL,
    quantity INT NOT NULL,
    price DECIMAL(10, 2) NOT NULL,
    FOREIGN KEY (order_id) REFERENCES orders(id),
    FOREIGN KEY (product_id) REFERENCES products(id),
    INDEX idx_order_item_order (order_id),
    INDEX idx_order_item_product (product_id)
);

-- 插入testdata
INSERT INTO users (username, email, password) VALUES
('user1', 'user1@example.com', 'password1'),
('user2', 'user2@example.com', 'password2'),
('user3', 'user3@example.com', 'password3');

INSERT INTO products (name, category, price, stock) VALUES
('iPhone 14', '手机', 5999.00, 100),
('MacBook Pro', '电脑', 12999.00, 50),
('AirPods Pro', '配件', 1999.00, 200),
('iPad Air', '平板', 4799.00, 80),
('Apple Watch', '手表', 2999.00, 150);

INSERT INTO orders (user_id, total, status) VALUES
(1, 7998.00, '已completion'),
(1, 12999.00, '已completion'),
(2, 1999.00, '已completion'),
(3, 4799.00, 'processingin'),
(3, 2999.00, '待付款');

INSERT INTO order_items (order_id, product_id, quantity, price) VALUES
(1, 1, 1, 5999.00),
(1, 3, 1, 1999.00),
(2, 2, 1, 12999.00),
(3, 3, 1, 1999.00),
(4, 4, 1, 4799.00),
(5, 5, 1, 2999.00);

-- 1. queryuser 所 has 订单 (optimization before ) 
EXPLAIN SELECT o.id, o.order_date, o.total, o.status
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE u.username = 'user1';

-- 2.  for user名添加index
CREATE INDEX idx_user_username ON users(username);

-- 3. queryuser 所 has 订单 (optimization after ) 
EXPLAIN SELECT o.id, o.order_date, o.total, o.status
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE u.username = 'user1';

-- 4. query指定classification 商品 (optimization before ) 
EXPLAIN SELECT * FROM products WHERE category = '手机' AND price < 6000;

-- 5. 添加复合index
CREATE INDEX idx_product_category_price ON products(category, price);

-- 6. query指定classification 商品 (optimization after ) 
EXPLAIN SELECT * FROM products WHERE category = '手机' AND price < 6000;

-- 7. query订单商品详情 (optimization before ) 
EXPLAIN SELECT o.id AS order_id, o.order_date, p.name AS product_name, oi.quantity, oi.price
FROM orders o
JOIN order_items oi ON o.id = oi.order_id
JOIN products p ON oi.product_id = p.id
WHERE o.user_id = 1;

-- 8. optimization after  query
EXPLAIN SELECT o.id AS order_id, o.order_date, p.name AS product_name, oi.quantity, oi.price
FROM orders o
JOIN order_items oi ON o.id = oi.order_id
JOIN products p ON oi.product_id = p.id
WHERE o.user_id = 1;

-- 9. analysis执行计划, 查看indexusingcircumstances
SHOW INDEX FROM users;
SHOW INDEX FROM products;
SHOW INDEX FROM orders;
SHOW INDEX FROM order_items;