# 0 复习 union all 在sql中可以使用 union all 来实现多个结果集的拼接,最终合并多个结果集为一个结果集 语法： \~\~\~ select .. from 表 where ... union all select .. from 表 where ... union all ...... 备注： 1 必须保证多个sql查询的列顺序、数量、类型一致 2 union all 是无脑的合并，如果要去重，则使用 union UNION 功能：合并两个或多个SELECT语句的结果集，并自动去除重复行 SELECT column_list FROM table1 UNION SELECT column_list FROM table2; 特点： 去重：自动去除重复行 排序：结果自动排序（基于第一个SELECT的列） 性能：较低（因去重和排序操作） 适用场景：需要合并结果集并去除重复数据的情况 UNION ALL 功能：合并两个或多个SELECT语句的结果集，保留所有行，包括重复行13。 SELECT column_list FROM table1 UNION ALL SELECT column_list FROM table2; 特点： 不去重：直接返回所有行，包括重复行 排序：不进行默认排序，需显式使用ORDER BY 性能：较高（直接合并结果） 适用场景：需要保留所有记录或已知无重复数据的情况 注意事项 列数一致：所有SELECT语句的列数必须相同 数据类型兼容：对应列的数据类型需兼容 列名规则：最终结果集的列名以第一个SELECT的列名为准 排序与别名：若需最终排序，仅在最后一个SELECT后加ORDER BY 通过合理选择UNION或UNION ALL，可以有效提高查询性能和数据准确性。 \~\~\~ # 1 索引概念 在没有索引的查询时数据库都是采取的全表扫描，从第一条扫描到最后一条，如果数据量大，则查询速度就慢,为了提高查询速度，我们可以采用索引 1 索引是帮助mysql高效获取数据的 有序的数据结构 2 索引能够增加查询和排序的效率 3 创建了索引后，索引会占有mysql一定的空间(空间换时间),并且每次更新数据都要对索引进行维护 4索引结构为b+ tree （二叉树---\>红黑树---\>B树---\>B+树） 查找规则：比节点小,走左边，比节点大，走右边 !\[image-20250820220326033\](https://woniumd.oss-cn-hangzhou.aliyuncs.com/web/wangyongcheng/20250820220326.png) # 2 建立索引的条件： 空间换时间 1 频繁作为查询条件的字段 -email \~\~\~ SELECT \* FROM users WHERE email = 'example@email.com'; \~\~\~ 2 经常用于连接查询的字段-user_id \~\~\~ SELECT u.name, o.order_date FROM users u JOIN orders o ON u.id = o.user_id; \~\~\~ 3 经常用于排序的字段-price \~\~\~ SELECT \* FROM products ORDER BY price DESC; \~\~\~ 4 经常用于分组的字段-category \~\~\~ SELECT category, COUNT(\*) FROM products GROUP BY category; \~\~\~ 5 具有唯一性的字段-比如身份证、电话、邮箱等 6 主键自带索引 # 3 不该建立索引的情况 1 数据量很小的表--维护成本超过查询成本 2 经常进行增删改操作的字段--每次数据变更都需要维护索引 3 区分度低的字段-sex 4 大文本字段 # 4 常见的索引 1 主键索引，当设置为主键时（primary key）自然会生成主键索引 2 唯一索引(unique) 3 常规索引(单值索引)：该索引只包含一个列 4 复合索引：该索引包含多个列（重点） # 5创建索引 习惯： \~\~\~ 索引名：idx_xxx CREATE TABLE employees ( id INT AUTO_INCREMENT PRIMARY KEY, first_name VARCHAR(50), last_name VARCHAR(50), department VARCHAR(50) ); -- 为 last_name 列创建索引 CREATE INDEX idx_last_name ON employees(last_name); 这段代码首先创建了一个名为employees的表，然后在这个表上为last_name列创建了一个名为idx_last_name的索引。 \~\~\~ 创建索引 create \[unique\] index 索引名 on 表 (列1,列2...) 查看索引 SHOW INDEXES FROM 表 删除索引 drop index 索引名 on 表 （不能删掉主键索引） # 6 存储过程添加100万条数据 \~\~\~ CREATE TABLE test_index( id INT PRIMARY KEY AUTO_INCREMENT, NAME VARCHAR(20) ); DELIMITER $$ CREATE PROCEDURE p1() BEGIN DECLARE i INT DEFAULT 1; WHILE i\<=1000000 DO INSERT INTO test_index VALUES(NULL,CONCAT('用户',i)); SET i=i+1; END WHILE; END; CALL p1; \~\~\~ 验证： \~\~\~ -- 大概 0.2S左右 select \* from test_index where name ='用户500000' -- 主键索引：大概 0.01S左右 select \* from test_index where id ='500000' -- 为name新建索引 create index idx_name on test_index(name) -- 查看新建的所有 show index from test_index -- 再次根据name查询 0.01 S左右 select \* from test_index where name ='用户500000' \~\~\~ # 7 工作中用得最多的索引：复合索引 因为前端的条件一般不指一个，可能会同时传递多个条件，则此时应该用复合索引来提升查询性能 例子： 比如前端传递 birth日期时间范围 + name姓名查询+age年龄查询 后端sql： select \* from user where birth between ? and ? and name =? and age =? 此时通过复合索引来提升速度： create index idx_where on user (birth,name,age) 【注意：复合索引的顺序要和sql语句中where后的顺序保持一致，否则可能导致索引没走成功】 \`\`\`\`java 复合索引（也称为组合索引或多列索引）是指在一个索引中包含两个或多个列。这样可以显著提高涉及这些列的查询性能，尤其是在WHERE子句中同时使用多个列进行过滤的情况下。 下面是一个在MySQL中创建复合索引的例子。假设我们有一个名为\`employees\`的表，并且想要为\`department\`和\`last_name\`这两列创建一个复合索引。 CREATE TABLE employees ( id INT AUTO_INCREMENT PRIMARY KEY, first_name VARCHAR(50), last_name VARCHAR(50), department VARCHAR(50) ); -- 为 department 和 last_name 列创建复合索引 CREATE INDEX idx_department_last_name ON employees(department, last_name); 在这段代码中，我们首先创建了一个名为\`employees\`的表，然后在这个表上为\`department\`和\`last_name\`这两列创建了一个名为\`idx_department_last_name\`的复合索引。 ### 复合索引的优点： 1. \*\*提高查询效率\*\*：当查询条件涉及到索引中的所有列时，复合索引可以显著提高查询速度。 2. \*\*减少磁盘I/O\*\*：通过减少需要扫描的数据量，复合索引可以降低磁盘I/O操作的数量。 ### 注意事项： - \*\*顺序很重要\*\*：复合索引的列顺序非常重要。优化器会按照索引定义的顺序来使用索引。通常将选择性最高的列放在最前面。 - \*\*维护成本\*\*：虽然复合索引可以提高查询性能，但也会增加插入、更新和删除操作的成本，因为每次数据变更都需要更新索引。 \`\`\`\` # 8 工作中如何查看sql到底有没有走索引呢 查看sql语句执行计划（慢查询），语法： \~\~\~ explain sql查询语句 比如：explain select \* from 表 慢查询 \~\~\~ 执行后我们会看到一个结果集，需要通过观察其中的字段来排查 其中最最重要的字段是： \~\~\~ id type key rows extra \~\~\~ 1 id:操作表的顺序(有连接查询时会有多行记录) 如果id值相等，则按照从上至下顺序操作表 如果id值不同，则id值越大，优先级越高，越先被执行 2 select_type：操作类型 1 simple: 简单查询（不包含子查询或union） 2 primary: 查询中如果包含复杂的子查询，最外层标记为该标识 3 subquery: where列表中包含子查询 3 type：查询性能 查询性能从最好到最差依次是:const\>eq_ref\>ref\>range\>index\>all 至少要保证type为range或ref，说明查询是走了索引的 const：通过索引一次就找到了，说明查询条件是主键或者唯一键 eq_ref：使用主外键关联查询，关联查询出来的记录只有一条 ref：单表查询：查询非唯一性索引（常规索引） ；关联查询：关联查询出来的记录有多条 range：代表含有范围查询（where之后出现between 、 \> 、 \< 、in 等操作） index：查询整个索引树 all：查询整张表(全表扫描) 4 possible_keys、key ： possible_keys：可能会用到的索引 key ：实际用到的索引 5 key_len ：索引字段可能最大使用的字节数（mysql计算，非字段长度），越短越好 6 ref:显示哪些列或常量被用于查找索引列上的值 \~\~\~ 比如 name和age都有索引，那么： where name='xx' --\>ref中会显示const where name='xx'and age='xx' --\>--\>ref中会显示两个const 如果是range类型，则ref为空 \~\~\~ 7 Extra：显示额外重要信息 Using temporary(临时表)：使用了临时表保存了中间结果，遇到则需要优化（性能不高） \~\~\~ order by 和 group by 后的字段如果没有加索引，就会用临时表保存中间结果 比如 order by name，如果没有为name添加索引，则mysql可能需要先获取所有匹配的行放入临时表中，再进行排序，如果有索引，则直接利用索引排序，order by 也是如此 \~\~\~ Using where：索引未完全覆盖条件,会回表-----》性能一般 Using index ：索引覆盖了条件，不会回表----》性能好 # 9 覆盖索引 ## 聚簇索引（聚集索引） 其实就是主键索引：将数据和主键绑在一起，当查询到主键时，那么对应的行记录也都查询到了 ## 非聚簇索引（非聚集索引/二级索引） 将数据和主键分开存储，会涉及到回表的问题 ![image-20250821005019678](https://woniumd.oss-cn-hangzhou.aliyuncs.com/web/wangyongcheng/20250821005019.png) 回表：先从二级索引查找到主键，然后再从聚集索引根据主键查找对应的row（数据） 因此如果要优化，则需要减少回表操作（减少I/O操作） !\[image-20250821005040154\](https://woniumd.oss-cn-hangzhou.aliyuncs.com/web/wangyongcheng/20250821005040.png) 例子： \~\~\~ drop table if exists myuser; create table myuser ( id INT PRIMARY KEY AUTO_INCREMENT, name varchar(20), birth datetime, age int, address varchar(20) ); insert into myuser (name,birth,age,address) VALUES ('aa','1991-03-03 12:32:44',17,'重庆'), ('bb','1992-03-03 12:32:44',18,'北京'), ('aa','1993-03-03 12:32:44',17,'湖南'), ('cc','1994-03-03 12:32:44',17,'重庆'); select \* from myuser \~\~\~ 回回表的操作： 复合索引：create index idx_where1 on myuser(birth,name,age) explain select address from myuser where 1=1 and birth between '1990-01-01 00:00:00' and '1995-01-01 00:00:00' and name ='aa' and age =17 虽然查询走了复合索引，但复合索引中没有address，因此mysql会从条件找到对应的主键id（二级索引），然后再回到聚集索引中根据主键找到对应的row，其中row就包含了address 如何解决：直接在复合索引中的末尾添加address: 这里的索引后添加address不会是因为要查询address，而是减少回表操作，二级索引找到了就找到了,但是要注意，前面3个brith、name、age肯定是不动的，因为要和查询条件顺序匹配 \~\~\~ create index idx_where1 on myuser(birth,name,age,address) \~\~\~ # 10 强制使用自定义索引 就算我们新建了自己的复合索引后，mysql在真正执行sql时会自己计算最优解，导致不走我们的索引，最终导致查询很慢，因此，我们可以在 from 表 后添加 FORCE INDEX (索引名) \~\~\~ SELECT \* FROM users FORCE INDEX (idx_name_age) WHERE name = 'John' AND age = 25; \~\~\~ # 11 索引的优化 1 最佳左前缀法则 \~\~\~ 带头大哥不能死，中间兄弟不能断 \~\~\~ 2 不要在索引列上做任何操作（计算、函数、类型转换），比如 \~\~\~ SELECT \* FROM student WHERE SUBSTR(NAME,1,3)='绿巨人'; \~\~\~ 3 范围之后全失效： \~\~\~ SELECT \* FROM student WHERE NAME='绿巨人' AND age\>18 AND address='重庆'; 就算建立了 name、age、address的复合索引，那么真正用到的索引只有name和age，而address失效了 因此我们建立复合索引时尽量把范围查找的字段写在后面，比如： create index idx_where on myuser (NAME,address,age),那么将来的sql的顺序就是： SELECT \* FROM student WHERE NAME='绿巨人' AND address='重庆' AND age\>18 \~\~\~ 4 尽量使用索引覆盖（using index），减少回表操作 5 查询的条件如果是字符串就要添加引号。比如name是varchar类型，数据中有人名字叫9527，那么我们这样写： select \* from xx where name= 9627,mysql会正常查询，但是它执行过程会悄悄的转化类型，导致索引失效 但是如果类型是int，而我们的查询添加了引号，那么也会走索引(特殊) # 12 总结索引优化： 带头大哥不能死，中间兄弟不能断 索引列上无计算 like百分加右边 select \* from 表 where name like '李%' 范围之后全失效 varchar引号不能丢 \~\~\~ \~\~\~ \~\~\~ \~\~\~ 1.增删改索引 2.哪些列建议添加索引 哪些列不建议 3.慢查询 得到的结果分析id type key rows extra 4.复合索引顺序