摘要：索引应建立在查询频繁、更新较少的字段上。避免对经常更新的表创建过多索引。多列索引要注意最左前缀原则。全文索引适用于文本搜索，但需要注意字符集和排序规则。定期分析查询语句，使用 EXPLAIN 查看索引使用情况。
索引使用建议：
索引应建立在 查询频繁、更新较少 的字段上。避免对经常更新的表创建过多索引。多列索引要注意 最左前缀原则 。全文索引适用于文本搜索，但需要注意字符集和排序规则。定期分析查询语句，使用 EXPLAIN 查看索引使用情况。索引是对数据库表中一列或多列的值进行排序的一种数据结构，使用索引可以 快速访问 数据库表中的特定信息，从而 加快对表中记录的查找或排序 。
数据结构可视化网站： www.cs.usfca.edu/~galles #技术分享/vis…
| 索引类型 | 说明 | | ---
| 普通索引 | 最基本的索引，无任何限制，可在任何数据类型上创建 | | 唯一索引 | 使用 UNIQUE 约束，索引值必须唯一，主键是特殊的唯一索引 | | 全文索引 | 使用 FULLTEXT，只能用于 CHAR/varchar/TEXT 类型，适用于大文本搜索 | | 单列索引 | 只针对一个字段建立的索引，可以是上述任意类型 | | 多列索引 | 针对多个字段建立的索引，查询时必须遵循最左前缀原则 | | 空间索引 | 使用 SPATIAL，只能用于空间数据类型，仅 MyISAM 引擎支持，且字段不能为空 |
SELECT * FROM MySQL.InnoDB_index_stats;SELECT *WHERE database_name = 'test_auth' AND table_name LIKE '%log%';五、创建索引索引创建最佳实践：
设计阶段 ：根据业务查询模式设计合适的索引开发阶段 ：使用 EXPLAIN 验证索引使用情况测试阶段 ：通过性能测试验证索引效果生产环境 ：定期监控和优化索引性能维护阶段 ：定期清理无用索引，重建碎片化索引CREATE INDEX index_name ON table_name(column_name);CREATE UNIQUE INDEX index_name ON table_name(column_name);注意事项：
只有 CHAR 、 VARCHAR 、 TEXT 类型字段可以建立全文索引在数据量较大时，先插入数据再创建全文索引比先创建索引再插入数据效率更高使用前需确认 MySQL 版本、存储引擎和字符集支持全文索引CREATE FULLTEXT INDEX index_name ON table_name(column_name);查看全文索引配置：
SHOW VARIABLES LIKE '%ft%';重点关注：
创建联合索引时一定要注意位置，遵循 最左前缀法则
CREATE INDEX index_name ON table_name(col1, col2, col3);最左前缀原则示例：
CREATE INDEX idx_phone_name ON test_auth.sys_user (phone, username);SELECT *SELECT *SELECT *SELECT *SELECT * FROM sys_user WHERE phone = 'xxx' OR username = 'xxx';CREATE SPATIAL INDEX index_name ON table_name(column_name);ALTER TABLE table_name ADD INDEX index_name(column_name);ALTER TABLE table_name ADD UNIQUE INDEX index_name(column_name);ALTER TABLE table_name ADD FULLTEXT INDEX index_name(column_name);当字段类型为字符串时(varchar、text)，有时需要索引很长的字符串，这会让索引变得很大。此时可以只将字符串的一部分前缀建立索引，节约索引空间。
前缀长度选择原则选择性越高越好，最高为1（即100%），表示每个截取后的前缀都是唯一的，和完整的列一样好
但不代表就一定要选择第一个为1（即100%）
根据索引的选择性决定，选择性是不重复的索引值（基数）和数据表记录总数的比值。
计算方法：
SELECT COUNT(DISTINCT email) / COUNT(*) FROM sys_user;SELECT COUNT(DISTINCT SUBSTRING(email, 1, 15)) / COUNT(*) FROM sys_user;最佳实践选择第一个达到或超过 0.95（或 0.9） 的长度。
如果 selectivity_14 = 0.93 , selectivity_15 = 1.0 ，那么 选择15 是更稳妥的，因为它保证了唯一性。如果 selectivity_14 = 0.98 , selectivity_15 = 1.0 ，那么需要权衡：选14 ： 索引更小，写入更快。牺牲了2%的选择性（即每100行有2个可能的前缀冲突），在数万行的表中可能影响不大。选15 ： 索引最大程度有效，但比14长1个字节。对于超大型表，这1个字节的累积开销也需要考虑。选择性分析示例| 前缀长度 | 选择性 | 性价比评价 | | ---
| 8 | 87.80% | 太低 | | 9 | 94.55% | 接近标准 | | 10 | 98.09% | ✅ 高性价比 | | 11 | 99.41% | ✅ 优秀 | | 12 | 99.82% | 很好 | | 13 | 99.93% | 最佳推荐 | | 14 | 99.98% | 近乎完美 | | 15 | 100% | 完美 |
创建前缀索引--CREATE INDEX idx_email_prefix ON table_name(email(13));选择合适的数据类型 ：使用较小的数据类型，整数类型比字符类型索引效率更高避免过度索引 ：每个额外的索引都会增加写操作的开销使用前缀索引 ：对于长字符串字段，可以只索引前几个字符CREATE INDEX index_name ON table_name(column_name(10));定期分析索引使用情况 ：使用性能监控工具监控索引效果考虑索引选择性 ：选择性高的字段（唯一值多的字段）更适合建索引根据输出结果可以判断当前数据库是以查询还是增删改为主，方便我们知道是否要对其做优化。
SHOW GLOBAL STATUS LIKE 'Com_______';返回结果说明：
Com_select ：SELECT查询执行次数Com_insert ：INSERT语句执行次数Com_update ：UPDATE语句执行次数Com_delete ：DELETE语句执行次数Com_begin ：事务开始次数Com_commit ：事务提交次数Com_rollback ：事务回滚次数Com_change_db ：数据库切换次数配置文件位置：/etc/my.cnf 或 /etc/mysql/my.cnf
slow_query_log = 1long_query_time = 2 slow_query_log_file = /var/log/mysql/slow.log查看慢查询日志位置：
SHOW VARIABLES LIKE 'slow_query_log_file';检查是否支持 profiling：
SELECT @@have_profiling;开启 profiling：
SET profiling = 1查看所有查询的性能分析：
SHOW PROFILES查看特定查询的详细分析：
SHOW PROFILE FOR QUERY [query_id]查看 CPU 耗费情况：
SHOW PROFILE CPU FOR QUERY [query_id]其他分析选项：
SHOW PROFILE ALL FOR QUERY [query_id]SHOW PROFILE BLOCK IO FOR QUERY [query_id]SHOW PROFILE CONTEXT SWITCHES FOR QUERY [query_id]SHOW PROFILE IPC FOR QUERY [query_id]SHOW PROFILE MEMORY FOR QUERY [query_id]SHOW PROFILE PAGE FAULTS FOR QUERY [query_id]SHOW PROFILE SOURCE FOR QUERY [query_id]SHOW PROFILE SWAPS FOR QUERY [query_id]EXPLAIN 输出字段详解：
idselect 查询的序列号，表示查询中执行的 select 子句或者是操作表的顺序。
id相同：执行顺序从上到下id不同：值越大，越先执行表示 SELECT 的类型，常见取值：
SIMPLE ：简单查询，不使用表连接或者子查询PRIMARY ：主查询，即外层的查询UNION ：UNION中的第二个或者后面的查询语句SUBQUERY ：SELECT/WHERE之后包含了子查询DERIVED ：派生表的SELECTUNION RESULT ：UNION的结果✅ 最优级别
| type | 说明 | 性能 | | ---
| system | 系统表，只有一行数据 | 最佳 | | const | 通过主键或唯一索引查询，最多返回一行 | 最佳 |
优秀级别
| type | 说明 | 性能 | | ---
| eq_ref | 关联查询时使用主键或唯一索引 | ⭐ 优秀 | | ref | 使用普通索引查询 | ⭐ 良好 | | fulltext | 全文索引查询 | ⭐ 良好 |
⚠️ 可接受级别
| type | 说明 | 性能 | | ---
| ref_or_null | 类似 ref，但包含 NULL 值查询 | 一般 | | index_merge | 索引合并优化 | 一般 | | unique_subquery | 子查询中使用唯一索引 | 一般 | | index_subquery | 子查询中使用普通索引 | 一般 |
需要优化级别
| type | 说明 | 性能 | | ---
| range | 范围扫描索引 | ⚠️ 需关注 | | index | 全索引扫描 | ⚠️ 需优化 |
❌ 最差级别
| type | 说明 | 性能 | | ---
| ALL | 全表扫描 | ❌ 急需优化 |
可能用到的索引，一个或多个。
key实际使用的索引，如果为 NULL，则表示没有使用索引。
key_len表示索引中使用的字节数，该值为索引字段最大可能长度，并非实际使用长度，在不损失精确性的前提下，长度越短越好。
rowsMySQL 认为必要执行查询的行数，在 InnoDB 引擎中是一个预估值。
filtered表示返回结果的行数占需要读取行数的百分比，filtered 的值越大越好。
额外的执行信息，常见值：
NULL ：回表查询Using index ：使用覆盖索引Using where ：使用WHERE过滤Using temporary ：使用临时表Using filesort ：使用文件排序Using join buffer ：使用连接缓冲如果索引引了多列（联合索引），要遵循最左法则。查询从索引最左列开始，并且不跳过索引中的列。
CREATE INDEX idx_profession_age_status ON tb_user(profession, age, status);EXPLAIN SELECT *EXPLAIN SELECT *EXPLAIN SELECT *EXPLAIN SELECT *EXPLAIN SELECT *EXPLAIN SELECT * FROM tb_user WHERE profession = '软件工程' AND status = 'O';范围查询影响联合索引中，出现范围查询()，范围查询右侧的列索引失效
EXPLAIN SELECT *EXPLAIN SELECT * FROM tb_user WHERE profession = '软件工程' AND age >= 30 AND status = 'O';运算操作导致失效不要在索引列上进行运算操作，索引将失效。
EXPLAIN SELECT *EXPLAIN SELECT * FROM tb_user WHERE phone LIKE '';字符串类型字段使用时，不加引号，索引失效。
EXPLAIN SELECT *EXPLAIN SELECT * FROM tb_user WHERE phone = '17799990015';模糊查询影响尾部模糊匹配：索引不会失效头部模糊匹配：索引失效EXPLAIN SELECT *EXPLAIN SELECT *EXPLAIN SELECT * FROM tb_user WHERE phone LIKE '�99%';OR连接条件影响如果使用 OR 进行分割条件，只有所有条件都有索引时才会使用索引。
EXPLAIN SELECT *EXPLAIN SELECT * FROM tb_user WHERE phone = '17799990017' OR profession = '软件工程';数据分布影响如果 MySQL 评估使用索引比全表扫描慢，则不使用索引。
EXPLAIN SELECT *EXPLAIN SELECT *SQL 提示是优化数据库的一个重要手段，通过在 SQL 语句中加入人为提示来达到优化目的。
EXPLAIN SELECT *WHERE is_deleted = 0 AND username = 'tsuism'; EXPLAIN SELECT *WHERE is_deleted = 0 AND username = 'tsuism'; EXPLAIN SELECT *WHERE is_deleted = 0 AND username = 'tsuism';当查询的列不在索引中时，MySQL 需要根据索引找到主键，再通过主键回表查询完整数据行，这个过程称为回表查询。
覆盖索引覆盖索引是指查询使用了索引，并且需要返回的列在该索引中已经全部能够找到，不需要回表查询。
EXPLAIN 中的 Extra 字段说明：
每个数据库的版本不同所以看到的信息也可能不同，下面只是参考。
Using where; Using index 性能更优
Using index condition ：查找使用了索引，但需要回表查询Using where; Using index ：查找使用了索引，且所有数据都在索引列中，不需要回表查询表结构：(id, username, password, status)
优化 SQL：SELECT id, username, password FROM tb_user WHERE username = 'xxx';
解决方案： 为 username 和 password 建立联合索引
CREATE INDEX idx_username_password ON tb_user(username, password);这样查询时只需要使用索引就能获取所有需要的数据，避免回表查询。
OPTIMIZE TABLE table_name;统计信息过期 ：使用 ANALYZE TABLE 更新统计信息ANALYZE TABLE table_name;针对于数据量较大，且查询比较频繁的表建立索引。针对于常作为查询条件（where）、排序（order by)、分组（group by）操作的字段建立索引。尽量选择区分度高的列作为索引，尽量建立唯一索引，区分度越高，使用索引的效率越高。尽量不要使用 gender 、 status 这种因为区分度不高如果是字符串类型的字段，字段的长度较长，可以针对于字段的特点，建立前缀索引。尽量使用联合索引，减少单列索引，查询时，联合索引很多时候可以覆盖索引，节省存储空间，避免回表，提高查询效率。要控制索引的数量，索引并不是多多益善，索引越多，维护索引结构的代价也就越大，会影响增删改的效率。比如当前表只涉及插入不涉及查询，那么就不要建立索引，相反如果建立越多的索引增删改效率反而降低，还会浪费磁盘空间如果索引列不能存储NULL值，请在创建表时使用NOTNULL约束它。当优化器知道每列是否包含NULL值时，它可以更好地确定哪个索引最有效地用于查询。来源：墨码行者
