Mysql之索引优化
指定索引当一个字段上既有单列索引，又有复合索引时，我们可以通过以下的SQL提示来要求该SQL语句执行时采用哪个索引：
[*] use index(索引名称)：建议使用该索引，只是建议，底层mysql会根据实际效率来考虑是否使用你推荐的索引。
[*] ignore index(索引名称)：忽略该索引
[*] force index(索引名称)：强行使用该索引
应用场景：
[*]当数据库执行计划没有主动选择最优的索引时，开发者可以手动指定使用特定的索引。尤其是在有多个索引的情况下，MySQL 可能选择了一个并不理想的索引，而通过指定索引可以确保查询执行的效率。
/*
指定索引
use index（索引的名称）： 建议使用该索引
ignore index（索引名称） ：某个索引
force index（索引名称） ：强行使用某个索引
*/
show index from t_customer;
create index idx_t_customer_name on t_customer(name);
explain select *from t_customer where name= 'zhangsan' ; #优先使用复合索引
# 如何建议使用单列索引idx_name：
explain select *from t_customer use index(idx_t_customer_name) where name= 'zhangsan' ; #建议使用单列索引
#如何忽略使用复合索引 idx_name_age_gender：
explain select * from t_customer ignore index(idx_name_age_gender) where name='zhangsan';
#如何强行使用单列索引idx_name：
explain select * from t_customer force index(idx_t_customer_name) where name='zhangsan'; 覆盖索引
覆盖索引强调的是：在select后面写字段的时候，这些字段尽可能是索引所覆盖的字段，这样可以克制回表查询。尽可能克制使用 select *，因为select * 很容易导致回表查询。（本质就是：能在索引上检索的，就不要再次回表查询了。）
覆盖索引是指在一次查询中，所有需要查询的列都能从索引中获取，不需要再访问表中的实际数据行。也就是说，数据库只需要从索引中读取数据即可返回结果，不再需要回表（访问表中的实际数据），从而大大提高了查询效率。
应用场景：
[*]查询所需的列全部包含在索引中，不需要访问数据表的记载，这种查询能够通过索引直接返回结果，从而大幅提升性能。
/*
覆盖索引:
覆盖索引的核心概念就是通过查询所需的所有列都位于索引中，避免回表操作，从而提高查询效率。
*/
CREATE INDEX idx_emp_name_sal ON t_emp(name, sal);
/*
MySQL 可以直接从 idx_emp_name_sal 这个索引中返回结果，而不需要访问表中的数据行，
因为 name 和 sal 都包含在索引中。这就是覆盖索引。
*/
show index from t_emp;
EXPLAIN SELECT name, sal FROM t_emp WHERE name = '张三'; # 使用覆盖索引
EXPLAIN SELECT name, sal, age FROM t_emp WHERE name = '张三'; # 不使用索引：
/*
此时，MySQL 会在索引中找到 name 和 sal，然后还需要访问数据表获取 age 列的值，这被称为 回表 操作
*/
DROP INDEX idx_emp_name_sal ON t_emp;
https://i-blog.csdnimg.cn/direct/4d8724d259e349aabeaa2471cbe3b0aa.png
extra为null表示回表了 ！！！
面试题：
t_user表字段如下：id,name,password,realname,birth,email。表中数据量500万条，请针对以下SQL语句给出优化方案：
select id,name,realname from t_user where name='鲁智深'; 假如只给name添加索引，底层会举行大量的回表查询，效率较低，建议给name和realname两个字段添加团结索引，这样大大减少回表操纵，提高查询效率。
前缀索引
前缀索引（Prefix Index）是指对字符型列的前部门字符举行索引，而不是对整列创建索引。这种索引可以减少索引的大小，节省空间，同时还能提高查询的效率，特殊是在需要对长文本或字符串列举行索引时。前缀索引广泛应用于需要索引长字符串列的场景，例如 VARCHAR 或 TEXT 范例的列。
使用前缀索引的场景
前缀索引适用于以下情况：
[*]列的数据较长，而且前几个字符就具有较强的区分度。
[*]不需要精确匹配整个字段，而只关心字段的前部门字符的匹配情况。
前缀索引的语法
CREATE INDEX 索引名 ON 表名(列名(前缀长度));
示例
假设有一张 users 表，包含用户的电子邮件地址（email 列），由于电子邮件可能非常长，我们可以通过前 10 个字符创建前缀索引：
CREATE TABLE users (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(255),
email VARCHAR(255)
);
-- 创建前缀索引，只对 email 列的前 10 个字符建立索引
CREATE INDEX idx_email ON users(email(10));
此时，MySQL 只对 email 列的前 10 个字符举行索引，而不是对整个 email 列举行索引。当我们执行以下查询时：
SELECT * FROM users WHERE email LIKE 'abc@example%';
MySQL 会利用前 10 个字符的前缀索引来优9查询。这个查询中的 email LIKE 'abc@example%' 会匹配 email 列前 10 个字符中的内容。
怎样选择符合的前缀长度？
使用前缀索引时，需要通过以下公式来确定使用前几个字符作为索引：
select count(distinct substring(ename,1,前几个字符)) / count(*) from emp4; 以上查询结果越靠近1，表示索引的结果越好。（原理：做索引值的话，索引值越具有唯一性效率越高）
/*
前缀索引
截取前几个字符作为前缀索引呢?使用以下公式
select count(distinct substring(ename,1,前几个字符))/count(*)from emp
这个查询结果越接近1，越具有唯一性
*/
select count(distinct substring(name,1,2))/count(*)from t_emp
总结
[*]指定索引 适合开发者手动介入优化查询。
[*]覆盖索引 能够在频繁查询特定列时，克制回表，提升查询效率，是性能优化的重要手段。
[*]前缀索引 适用于长文本字段，能在包管索引效率的同时，减少存储开销。
单列索引和复合索引怎么选择：
当查询语句的条件中有多个条件，建议将这几个列创建为复合索引，因为创建单列索引很容易回表查询。
1. 单列索引（Single-column Index）
单列索引是对表中的单个列创建的索引。每个索引只针对一个列举行加速。
使用单列索引的场景：
[*] 查询条件只涉及单列：
[*]当查询条件只包含一个列时，单列索引是最有效的选择。此时创建复合索引没故意义。
[*]多个单列索引用于差别的查询条件：
[*]假如表中的列经常被单独查询，不会经常团结查询，那么为每个列分别创建单列索引更为符合。
[*]频繁更新的表：
[*]在一个数据频繁更新的表中，单列索引相对复合索引更新代价较低。假如查询性能可以通过单列索引满足，那么单列索引通常会对性能更加友好。
2. 复合索引（Composite Index）
复合索引（也称为团结索引）是对多个列组合创建的索引。通过复合索引，查询引擎可以在多个列上同时加速查询。
使用复合索引的场景：
[*] 查询条件涉及多个列：
[*]假如查询条件经常包含多个列，而且这些列频繁团结出现，那么复合索引是最佳选择。复合索引能够同时加速多个列的查询。
[*]遵循最左前缀原则：
[*]在复合索引中，查询条件需要从索引的最左边开始使用。假如查询只包含最左边的列，复合索引仍然有效，但假如跳过了左边的列，索引将失效。 主字段而且具有很强唯一性的字段建议排在第一位。
[*]范围查询需要放在复合索引的末了：
[*]假如查询条件中包含范围查询（如 >, <, BETWEEN），通常将范围列放在复合索引的末了，因为范围查询会限定复合索引的使用范围。否则会导致索引失效。
[*]例如，假如你有一个查询同时涉及 name 和 age，而且 age 是一个范围查询： SELECT * FROM t_emp WHERE name = '张三' AND age > 20;
索引创建原则
1. 表数据量庞大，通常超过百万条数据
[*]解释：当表的数据量非常庞大（如超过百万条记载）时，查询性能会显著下降。没有索引的情况下，数据库必须举行全表扫描，这会导致查询速率非常慢。为了解决这一问题，创建适当的索引非常关键。索引可以极大地减少扫描的行数，从而提高查询性能。
2. 经常出如今 WHERE、ORDER BY、GROUP BY 后面的字段建议添加索引
[*]解释：WHERE、ORDER BY 和 GROUP BY 通常是 SQL 查询中最耗时的部门，因为它们会对表中的大量数据举行筛选、排序或分组。为这些字段添加索引可以加速数据的查找、排序和分组操纵。
[*]建议：假如某个字段经常出如今 WHERE、ORDER BY 或 GROUP BY 中，应该为该字段添加索引，以提高查询性能。
3. 创建索引的字段只管具有很强的唯一性
[*]解释：索引的效率取决于字段的选择性（区分度）。假如一个字段的值是高度唯一的（如主键或身份证号），索引能够快速缩小查询范围，查找性能更高。而对低选择性的字段（如性别，只有 "男" 和 "女" 两个值）创建索引，作用不大，因为即使使用索引，数据库仍然需要扫描大量数据。
[*]建议：优先为具有高唯一性、选择性强的字段创建索引，例如用户 ID、订单号等。
4. 假如字段存储文本，内容较大，一定要创建前缀索引
[*]解释：对于长文本字段（如 VARCHAR 或 TEXT），假如直接对整个字段创建索引，会消耗大量的存储空间并降低索引的效率。因此，可以创建前缀索引，即只索引文本字段的前 N 个字符。这样既节省空间，又能够保持较高的索引效率。
[*]建议：对于存储较长文本的字段（如邮箱、URL、文章标题等），建议使用前缀索引。前缀长度应根据数据分布和区分度公道选择。
5. 只管使用复合索引，使用单列索引容易回表查询
[*]解释：复合索引是对多个列组合起来创建的索引，可以在涉及多个列的查询中显著提高性能。当查询涉及多个条件时，使用复合索引可以克制回表操纵（从索引查找到数据后，还需要访问数据表自己）。单列索引只能加速单一条件的查询，对于多条件查询，数据库可能需要多次访问数据表，这会降低查询性能。
[*]建议：对于多列查询，优先考虑复合索引，以提高查询效率和减少回表操纵。
6. 假如一个字段中的数据不会为 NULL，建议建表时添加 NOT NULL 束缚，这样优化器就知道使用哪个索引列更加有效
[*]解释：假如某个字段的数据永久不会为 NULL，在表设计时应该明确设置 NOT NULL 束缚。这不仅能够保持数据的完整性，还可以帮助查询优化器更好地使用索引。因为当字段允许 NULL 值时，索引布局中可能需要额外的逻辑来处理 NULL 值，影响性能。
[*]例如 CREATE TABLE users (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(255),
email VARCHAR(255) NOT NULL,
phone VARCHAR(15) NOT NULL
);
[*]此时，数据库优化器不再需要考虑 NULL 值的情况，因为这些列不能存储 NULL 值。因此，在使用索引时，优化器会更高效地利用这些列的索引。
7. 不要创建太多索引，当对数据举行增删改的时候，索引需要重新排序
[*]解释：虽然索引可以提升查询性能，但它们会增加增删改操纵的开销。当对表举行增删改时，索引必须更新，这会导致性能下降。特殊是假如表上有大量的索引，每次修改数据时都需要更新这些索引，可能会导致性能问题。
8. 假如很少的查询，经常的增删改不建议加索引
[*]解释：假如表的数据重要是用来频繁增删改，而查询操纵很少，那么创建索引的收益可能不大，甚至会影响性能。因为索引的重要作用是加速查询，而在增删改操纵中，每次修改数据都需要更新索引，这会增加额外的开销。
[*]建议：在以频繁写操纵为主的表中（如日志表、事务表），假如查询操纵很少，不建议添加索引。除非某些查询需要优化，否则应优先考虑写入性能。
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。
页:
[1]