fullpage网站,网页制作教程教程,网站平台建设什么意思,做淘宝好还是自建网站好1. 主键索引#xff08;PRIMARY KEY#xff09;主键索引通常在创建表时定义#xff0c;确保字段唯一且非空#xff1a;-- 建表时直接定义主键
CREATE TABLE users (id INT NOT NULL,name VARCHAR(50),PRIMARY KEY (id) -- 单字段主键
);-- 复合主键#xff08;多字段组合…1. 主键索引PRIMARY KEY主键索引通常在创建表时定义确保字段唯一且非空
-- 建表时直接定义主键
CREATE TABLE users (id INT NOT NULL,name VARCHAR(50),PRIMARY KEY (id) -- 单字段主键
);-- 复合主键多字段组合唯一
CREATE TABLE order_details (order_id INT NOT NULL,product_id INT NOT NULL,quantity INT,PRIMARY KEY (order_id, product_id) -- 多字段组合主键
);2. 唯一索引UNIQUE确保字段值唯一允许 NULL但 NULL 只能出现一次
-- 建表时创建唯一索引
CREATE TABLE users (id INT PRIMARY KEY,phone VARCHAR(11) NOT NULL,email VARCHAR(32) NOT NULL,UNIQUE INDEX idx_email (email) -- 为 email 字段创建唯一索引
);-- 表已存在时添加唯一索引
CREATE UNIQUE INDEX idx_phone ON users(phone); -- 为 phone 字段创建唯一索引3. 普通索引INDEX最基础的索引无唯一性约束仅用于加速查询
-- 建表时创建普通索引
CREATE TABLE articles (id INT PRIMARY KEY,title VARCHAR(200),content TEXT,create_time DATETIME,INDEX idx_create_time (create_time) -- 为创建时间创建普通索引
);-- 表已存在时添加普通索引
CREATE INDEX idx_title ON articles(title); -- 为标题创建普通索引4. 复合索引多列索引基于多个字段组合创建遵循 “最左前缀匹配原则”
-- 为 user_id 和 status 组合创建复合索引
CREATE TABLE orders (id INT PRIMARY KEY,user_id INT,status TINYINT,create_time DATETIME,INDEX idx_user_status (user_id, status) -- 复合索引
);-- 表已存在时添加复合索引
CREATE INDEX idx_name_age ON users(name, age); -- 为 name 和 age 组合创建索引
复合索引的 “最左前缀匹配原则” 是指当使用复合索引多字段组合索引时数据库会优先匹配索引中最左侧的字段只有当左侧字段被有效使用时索引才会被部分或完全启用。具体规则如下从左到右匹配复合索引 (a, b, c) 会优先匹配字段 a再匹配 ab最后匹配 abc。例如对于索引 (name, age, gender)能触发索引的查询条件WHERE name ?WHERE name ? AND age ?WHERE name ? AND age ? AND gender ?无法触发索引的查询条件WHERE age ?跳过了最左的 nameWHERE age ? AND gender ?缺少最左的 name中间字段不连续时仅匹配到连续的左侧字段对于索引 (a, b, c)如果查询条件是 WHERE a ? AND c ?则只有 a 字段会使用索引c 字段无法利用索引因为跳过了 b。范围查询会中断后续匹配如果最左字段使用范围查询, , BETWEEN 等则后续字段无法利用索引。例如对于索引 (name, age)WHERE name ? AND age ?name 全匹配age 范围查询索引有效。WHERE name ? AND age ?name 是范围查询age 无法利用索引。示例
假设有复合索引 (user_id, order_time)以下查询的索引使用情况SELECT * FROM orders WHERE user_id 100 → 索引完全生效SELECT * FROM orders WHERE user_id 100 AND order_time 2023-01-01 → 索引生效user_id 全匹配order_time 范围匹配SELECT * FROM orders WHERE order_time 2023-01-01 → 索引失效跳过最左的 user_id总结创建复合索引时应将查询频率最高、区分度最高的字段放在左侧且查询条件需从左到右使用索引字段才能最大限度利用复合索引的性能优势。
5. 全文索引FULLTEXT用于大文本字段的全文检索仅支持 CHAR、VARCHAR、TEXT 类型
-- 建表时创建全文索引
CREATE TABLE articles (id INT PRIMARY KEY,title VARCHAR(200),content TEXT,FULLTEXT INDEX idx_content (content) -- 为 content 字段创建全文索引
);-- 表已存在时添加全文索引
CREATE FULLTEXT INDEX idx_title_content ON articles(title, content); -- 多字段组合全文索引使用方式查询时需用 MATCH() AGAINST() 语法SELECT * FROM articles
WHERE MATCH(title, content) AGAINST(数据库 索引 IN BOOLEAN MODE);6. 空间索引SPATIAL针对空间数据类型如 POINT、GEOMETRY 等的索引
-- 建表时创建空间索引字段必须为 NOT NULL
CREATE TABLE locations (id INT PRIMARY KEY,position POINT NOT NULL, -- 空间类型字段SPATIAL INDEX idx_position (position) -- 空间索引
);-- 表已存在时添加空间索引
CREATE SPATIAL INDEX idx_geo ON locations(position);
注意事项索引名如 idx_email建议遵循 idx_字段名 格式便于识别。复合索引的字段顺序会影响查询效率应将查询频率高的字段放在前面。过多索引会降低插入 / 更新 / 删除的性能需根据业务查询频率权衡。
