索引设计场景总结
| 原则编号 | 原则描述 | 补充说明与示例 |
|---|---|---|
| 1 | 针对数据量较大且查询频繁的表建立索引。 | - 小表数据量少时，全表扫描效率可能更高。 - 当表超过 1000 条记录且频繁查询时，建议建立索引。 |
| 2 | 针对常作为查询条件 (WHERE)、排序 (ORDER BY)、分组 (GROUP BY) 的字段建立索引。 | - 示例：查询 SELECT * FROM orders WHERE customer_id = 1，建议在 customer_id 上建索引。- 对于 ORDER BY create_time 的场景，索引可优化排序性能。 |
| 3 | 尽量选择区分度高的列作为索引，建议建立唯一索引。区分度越高，索引效率越高。 | - 区分度公式：COUNT(DISTINCT column) / COUNT(*)。示例： gender 的区分度低（仅2种值），不适合作索引；而 email 通常区分度高，适合建立索引。 |
| 4 | 对于长字符串字段，建议使用前缀索引。 | - 示例：假设 email 字段存储大量长字符，可使用前缀索引 CREATE INDEX idx_email ON users(email(10))，只索引前10个字符。 |
| 5 | 使用联合索引减少单列索引，提升查询效率。 | - 示例：对于 SELECT * FROM users WHERE first_name = 'John' AND last_name = 'Doe'，可建立联合索引 CREATE INDEX idx_name ON users(first_name, last_name)。 |
| 6 | 控制索引数量，避免过多索引增加维护开销。 | - 原则：不要为每个字段都建索引，通常为常用查询字段建索引。 - 示例：为单表仅建立 3-5 个重要索引，避免影响增删改性能。 |
| 7 | 索引列若不能存储 NULL 值，需使用 NOT NULL 约束。 | - 示例：对于 product_code 字段，若建索引则建议使用 NOT NULL，这样优化器能更高效地利用索引。 |
常见场景索引策略
| 场景 | 字段类型 | 索引策略 |
|---|---|---|
| 用户表登录查询 | 唯一字段（如邮箱、手机号） | 建立唯一索引提高查询效率。 |
| 模糊搜索（前缀匹配） | 长字符串 | 建立前缀索引，如 CREATE INDEX idx_name ON users(name(10))。 |
| 多条件组合查询 | 多字段组合查询 | 使用联合索引，如 (first_name, last_name)。 |
| 频繁更新的表 | 数据更新频繁 | 控制索引数量，减少更新索引的性能开销。 |
| 排序和分组 | 排序字段、分组字段 | 对 ORDER BY 和 GROUP BY 字段建立索引，如 CREATE INDEX idx_date ON orders(date)。 |
黑马索引案例sql语句汇总
create database itcase;
show databases;
use itcast;
-- 建表
CREATE TABLE tb_users(id INT PRIMARY KEY,name VARCHAR(255),phone VARCHAR(20),email VARCHAR(255),profession VARCHAR(255),age INT,gender INT,status INT,createtime DATETIME
);INSERT INTO tb_users (id, name, phone, email, profession, age, gender, status, createtime)
VALUES (1, '吕布', '17799990000', 'lvbu666@163.com', '软件工程', 23, 1, 6, '2001-02-02 00:00:00'),(2, '曹操', '17799990001', 'caocao666@qq.com', '通讯工程', 33, 1, 0, '2001-03-05 00:00:00'),(3, '赵云', '17799990002', '177999900@139.com', '英语', 34, 1, 2, '2002-03-02 00:00:00'),(4, '孙悟空', '17799990003', '177999900@sina.com', '工程造价', 54, 1, 0, '2001-07-02 00:00:00'),(5, '花木兰', '17799990004', '19980729@sina.com', '软件工程', 23, 2, 1, '2001-04-22 00:00:00'),(6, '大乔', '17799990005', 'daqiao666@sina.com', '舞蹈', 22, 2, 0, '2001-02-07 00:00:00'),(7, '露娜', '17799990006', 'luna_love@sina.com', '应用数学', 24, 2, 0, '2001-02-08 00:00:00'),(8, '程咬金', '17799990007', 'chengyaojin@163.com', '化工', 38, 1, 0, '2001-05-23 00:00:00'),(9, '项羽', '17799990008', 'xiaoyu666@qq.com', '金属材料', 43, 1, 1, '2001-09-18 00:00:00'),(10, '白起', '17799990009', 'baiqi666@163.com', '机械工程及其自动化', 27, 1, 2, '2001-08-16 00:00:00'),(11, '韩信', '17799990010', 'hanxin520@163.com', '无机非金属材料工程', 27, 1, 0, '2001-06-12 00:00:00'),(12, '荆轲', '17799990011', 'jingke123@163.com', '会计', 29, 1, 0, '2001-05-11 00:00:00'),(13, '兰陵王', '17799990012', 'lanlinwang666@126.com', '工程造价', 44, 1, 1, '2001-04-09 00:00:00'),(14, '狂铁', '17799990013', 'kuangtie@sina.com', '应用数学', 43, 1, 2, '2001-04-10 00:00:00'),(15, '貂蝉', '17799990014', '8495848374@qq.com', '软件工程', 40, 2, 1, '2001-02-12 00:00:00'),(16, '妲己', '17799990015', '2782382939@qq.com', '软件工程', 31, 2, 0, '2001-01-30 00:00:00'),(17, '半月', '17799990016', 'xiaomin2001@sina.com', '工业经济', 35, 2, 0, '2000-05-03 00:00:00'),(18, '赢政', '17799990017', '8839434342@qq.com', '化工', 38, 1, 1, '2001-08-08 00:00:00'),(19, '狄仁杰', '17799990018', 'jujiamlm816@163.com', '国际贸易', 30, 1, 1, '2007-03-12 00:00:00'),(20, '安琪拉', '17799990019', 'jdodmlh@126.com', '城市规划', 51, 2, 0, '2001-08-15 00:00:00'),(21, '典韦', '17799990020', 'ycauanjian@163.com', '城市规划', 52, 1, 0, '2000-04-12 00:00:00'),(22, '廉颇', '17799990021', 'lianpo321@126.com', '土木工程', 19, 1, 3, '2002-07-18 00:00:00'),(23, '后羿', '17799990022', 'altycj2000@139.com', '城市园林', 20, 1, 0, '2002-03-10 00:00:00'),(24, '姜子牙', '17799990023', '3748384@qq.com', '工程造价', 29, 1, 4, '2003-05-26 00:00:00');-- 表操作
select * from tb_users;
describe table tb_users;-- 索引
show index from tb_users;
# show index from tb_users\G;-- 1.name字段可能重复，为该字段创建索引
create index idx_user_name on tb_users(name);
show index from tb_users;-- 2.phone字段，非空，且唯一，为该字段创建唯一索引
create index idx_user_phone on tb_users(phone);
-- 创建错了索引，只能先删除，再新建
drop index idx_user_phone on tb_users;
create unique index idx_user_phone on tb_users(phone);
show index from tb_users;-- 3.为profession, age, status 创建联合索引
create index idx_user_pro_age_sta on tb_users(profession, age, status);
show index from tb_users;-- 4.为email创建合适索引提升查询效率
create index idx_user_email on tb_users(email);
show index from tb_users;-- sql 性能分析------------------
-- 1.查看当前查询频次 到底是select多还是什么多，针对select做优化
-- session当前会话
show session status like 'Com_______';
-- global全局
show global status like 'Com_______';-- 2.慢查询日志
-- 慢查询日志记录了所有执行时间超过指定参数(long_query_time,单位：秒，默认10秒)的所有SQL语句的日志。
-- MySQL的慢查询日志默认没有开启，需要在MySQL的配置文件(/etc/my.cnf)中配置如下信息：/*
#开启MySOL慢日志查询开关
slow_query_log=1
#设置慢日志的时间为2秒，SQL语句执行时间超过2秒，就会视为慢查询，记录慢查询日志
long_query_time=2*/
-- 1.查看慢查询有没有被开启
show variables like 'slow_query_log'; -- default -- OFF
-- 2.开启
-- 3.查看配置 cd /var/lib/mysql
-- cat localhost-slow.log 通过查看这个日志里的超时操作来针对性优化-- 3.show profiles
select @@have_profiling; -- 查看是否支持
select @@profiling; -- 查看有没有打开，0没打开
set profiling = 1; -- 开启/*
执行一系列的业务SQL的操作，然后通过如下指令查看指令的执行耗时：
#查看每一条SQL的耗时基本情况
show profiles;
#查看指定query id的SQL语句各个阶段的耗时情况
show profile for query query_id;
#查看指定query id的SQL语句CPU的使用情况
show profile cpu for query query_id;*/select * from tb_users;
select * from tb_users where id = 1;
select * from tb_users where name = '白起';show profiles; -- 查看所有操作耗时
show profile for query 44; -- 查看指定
show profile cpu for query 44;-- 4.explain:执行计划 ！！！
-- EXPLAIN或者DESC命令获取MySQL如何执行SELECT语句的信息，包括在SELECT语句执行过程中表如何连接和连接的顺序。select * from tb_users where id = 1;
desc select * from tb_users where id = 1;
explain select * from tb_users where id = 1;-- explain 各项解释
-- id select查询的序列号，表示查询中执行select子句或者是操作表的顺序(id相同，执行顺序从上到下；id不同，值越大，越先执行)
explain select s.*, c.* from student s, course c, student_course sc where s.id = sc.studentid and c.id = sc.courseid;-- 查询选mysql的学生
explain select s.name from student s left join student_course sc on s.id = sc.studentid where courseid = (select id from course where name = 'MySQL');-- 先执行内部select
explain select s.name from student s where s.id in (select studentid from student_course sc where sc.courseid = (select id from course c where c.name = 'MySQL'));-- select type
-- 表示SELECT的类型，常见的取值有SIMPLE(简单表，即不使用表连接或者子查询)、PRIMARY(主查询，即外层的查询)、
-- UNION(UNION中的第二个或者后面的查询语句)、SUBQUERY(SELECT/WHERE之后包含了子查询)等-- type ！！！重要指标，null最好，all最差
-- 表示连接类型，性能由好到差的连接类型为NULL、system、const、eq_ref、ref、range、index、all。
# explain select 'A';select * from tb_users;
-- 对于unique的键，查询是const
explain select * from tb_users where phone = '17799990002';
-- 对于非unique ，ref
explain select * from tb_users where name = '白起';/*possible_key
显示可能应用在这张表上的索引，一或多个。Key
实际使用的索引，如果为NULL,则没有使用索引。rows
MySQL认为必须要执行查询的行数，在innodb引擎的表中，是一个估计值，可能并不总是准确的。filtered
表示返回结果的行数占需读取行数的百分比，filtered的值越大越好。*/select * from tb_users;
show index from tb_users;
-- 联合查询，遵循最左原则，序号为1的必须存在，且中间不能跳过
explain select * from tb_users where profession = '软件工程' and age = 31 and status = '0';
-- 这也是联合查询
explain select * from tb_users where profession = '软件工程' and age = 31;
explain select * from tb_users where profession = '软件工程';-- 不满足联合查询，不满足最左前缀法则
explain select * from tb_users where age = 31 and status = '0';
explain select * from tb_users where status = '0';-- 部分满足联合查询，部分不满足
explain select * from tb_users where profession = '软件工程' and status = '0';
-- 最左前缀法则指的是查询从索引的最左列开始，并且不跳过索引中的列。如果跳跃某一列，索引将部分失效（后面的字段索引失效）
-- 可以互换位置，sql会自动优化顺序
explain select * from tb_users where age = 31 and profession = '软件工程' and status = '0';-- 在联合查询中，如果出现范围查询（>,<）的话，范围查询右侧的列索引失效，所以最好用>=,<=
explain select * from tb_users where age < 31 and profession = '软件工程' and status = '0';
explain select * from tb_users where age <= 31 and profession = '软件工程' and status = '0';-- 如果对索引列进行运算，那么索引失效
explain select * from tb_users where substring(phone, 10, 2) = '15';-- sub起始索引从1开始
-- 字符串类型不加''也会索引失效
-- 对于模糊查询 尾部模糊索引不会失效，头部模糊，则索引失效
explain select * from tb_users where profession like '软件%';-- 可以用索引
explain select * from tb_users where profession like '%软件';-- 不可以
explain select * from tb_users where profession regexp '^软件';-- 不可以-- 用or连接的条件，只有当or两边都有索引时，索引才会生效，否则都不会生效
explain select * from tb_users where id = 10 or age = 23; -- 不会用到
explain select * from tb_users where id = 10 or profession = '软件工程'; -- 用到
explain select * from tb_users where phone = '17799990017' or age = 23; -- 不会create index idx_user_age on tb_users(age);
explain select * from tb_users where id = 10 or age = 23; -- 创建age索引后可以用到了-- 数据分布也会影响索引使用。如果mysql评估使用索引比全表更慢，则不用索引
select * from tb_users;
explain select * from tb_users where phone >= '17799990017'; -- 索引
explain select * from tb_users where phone >= '17799990000'; -- 直接全表扫描
explain select * from tb_users where phone >= '17799990010'; -- 直接全表扫描
explain select * from tb_users where phone >= '17799990015'; -- 索引-- 这一点也同样适用于null与not null-- 因为sql判断在一对非空的里面找空的，用索引快，而找非空时，因为全表都非空，那就直接扫全表
explain select * from tb_users where profession is null; -- 索引
explain select * from tb_users where profession is not null; -- 全表-- 如果一个列是联合索引的最左，又给他新建了一个索引，那么sql自动使用联合索引，但是可以手动提示使用哪个索引
create index idx_user_pro on tb_users(profession);
explain select * from tb_users where profession = '软件工程';-- 手动指定 在from 表名后加 use index (索引名)
-- use 是建议sql使用这个索引
explain select * from tb_users use index (idx_user_pro) where profession = '软件工程';
-- ignore
explain select * from tb_users ignore index (idx_user_pro) where profession = '软件工程';
-- force 强制使用
explain select * from tb_users force index (idx_user_pro) where profession = '软件工程';-- 尽量使用覆盖索引，覆盖索引不需要回表（查询使用了索引，并且，需要返回的列在该索引中已经全部能够找到）尽量减少select *
show index from tb_users;
drop index idx_user_email on tb_users;
drop index idx_user_pro on tb_users;
drop index idx_user_age on tb_users;-- using index
explain select id, tb_users.profession, tb_users.age, tb_users.status from tb_users where profession = '软件工程' and age = 31 and status = '0';-- 用到了回表查询，先二次查询，然后回去聚集查询
explain select id, tb_users.profession, tb_users.age, tb_users.status, tb_users.name from tb_users where profession = '软件工程' and age = 31 and status = '0';
explain select id, tb_users.profession, tb_users.age, tb_users.status, tb_users.name, tb_users.gender from tb_users where name = '妲己';-- 例子：如果要查select id, username, password from tb_user where username = 'itcast';
-- 那么需要对username, password 一起建立联合索引，那么 在使用联合索引时就一起查到了username和password，id就不需要回表操作了。-- 前缀索引
select count(*) from tb_users;
select count(tb_users.email) email不为空的字段数 from tb_users;
select count(distinct tb_users.email) email不重复的字段数 from tb_users;-- email字段的选择性
-- 选择性为1，说明选择性是最好的，可以针对该字段建立前缀索引，降低索引复杂性、体积
select count(distinct tb_users.email) / count(*) email选择性 from tb_users;-- 通过试截取字符串的长度，找到相同选择性时最小的长度，可以以此构建前缀索引
select count(distinct substring(tb_users.email, 1, 5)) / count(*) email选择性 from tb_users;-- 前缀索引
create index idx_email_5 on tb_users(email(5));
show index from tb_users;explain select * from tb_users where email = 'daqiao666@sina.com';explain select * from tb_users where email = '177999900@139.com';
explain select * from tb_users where email = '177999900@sina.com';-- 单列索引，一个索引包含单个列
-- 联合索引，一个索引包含多列
show index from tb_users;
-- 单列索引
-- Extra = null 表示触发回表查询了，而且可以看到，它使用的是phone的辅助索引查询，那么肯定查不到name所以回表了
explain select id, phone, name from tb_users where phone = '17799990010' and name = '韩信';-- 先建立一个phone和name的联合索引
drop index idx_user_pho_nam on tb_users;
create unique index idx_user_pho_nam on tb_users(phone, name);
-- 这里默认他不会用联合索引，可以强制使用
explain select id, phone, name from tb_users where phone = '17799990010' and name = '韩信';
-- 使用联合索引
-- 可以看到这里Extra就变为了 Using index，没有回表操作了
explain select id, phone, name from tb_users use index(idx_user_pho_nam) where phone = '17799990010' and name = '韩信';
-- 也就是说，如果涉及到多个查询条件，则考虑对多个查询字段建立联合索引，而非单列索引
