mysql索引使用整理


索引分为主键索引、普通索引、联合索引、唯一索引、全文索引五种

索引优化:

1.索引字段能用数字尽量用数字,因为数字使用的存储空间更小
2.唯一索引与主键索引的区别在于主键不能为空
3.创建一个(a,b,c)的联合索引相当于创建了三个索引(a),(a,b),(a,b,c)
4.如果对于类似char(100)的索引,尽可能制定一个前缀长度
添加语法:ALTER TABLE table_name ADD KEY(column_name(prefix_length));
在MySQL中,前缀长度最大值为255字节。对于存储引擎为MyISAM或InnoDB的数据表,前缀最长为1000字节。如果超过这个限制,创建索引就会报错了 
可以通过计算选择性来确定前缀索引的选择性,计算方法如下
全列选择性:
SELECT COUNT(DISTINCT column_name) / COUNT(*) FROM table_name;
某一长度前缀的选择性

SELECT COUNT(DISTINCT LEFT(column_name, prefix_length)) / COUNT(*) FROM table_name;
当前缀的选择性越接近全列选择性的时候,索引效果越好(尽量保持前缀索引选择性/全列索引选择性在(0.75--0.85)之间)
5.如果全列索引的选择性太低,不建议使用索引
6.mysql查询只使用一个索引,因此如果where子句中已经使用了索引的话,那么order by中的列是不会使用索引的。因此数据库默认排序可以符合要求的情况下不要使用排序操作;尽量不要包含多个列的排序,如果需要最好给这些列创建复合索引。

7.对于索引列使用like的话必须使用“11%” 使用“%11”或者“%11%”是不走索引的(可以使用覆盖索引优化)
8.不要在索引的列上使用函数或计算   id+1=4  mouth(date)=12   这样是不走索引的

9.哈希索引适用于重复率低的列值精确查找,它的实质是对索引键值进行哈希计算,查找时根据哈希碰撞查询一次即可查到结果,b+树索引是把索引数据存在b+树上,使用二分查找查询的(聚集索引时b+树的叶子节点存的是列值,非聚集索引存的是列值对应的存储位置)

 

<think>好的,我现在需要回答用户关于如何在MySQL使用索引优化查询性能的问题。根据用户提供的引用内容,我需要整理出最佳实践,并且确保回答结构清晰,符合用户的要求。 首先,我需要回顾用户提供的引用内容,看看有哪些关键点可以提取。引用[1]提到选择合适的索引列,引用[2]提到使用EXPLAIN分析查询,引用[3]强调合理选择和设计索引的重要性,引用[4]指出使用索引可以优化临时表的创建,引用[5]则提到了21个最佳实践,包括设计表结构时的注意事项。 接下来,我需要将这些信息组织成逻辑连贯的步骤或建议。可能的结构是按重要性排序,或者分点列出不同的最佳实践。比如,先讲选择合适的索引列,然后如何设计索引,接着使用工具分析,最后避免常见的错误。 另外,用户要求生成相关问题,我需要根据内容提出相关的问题,比如如何选择索引列、如何分析索引效果、索引的维护等。同时,必须注意使用中文回答,并正确应用LaTeX格式,比如在提到数学表达式时用$符号包裹。 需要注意的还有,引用标识要自然地添加到相关段落的末尾。比如,当提到选择合适的索引列时,添加[^1],提到使用EXPLAIN时添加[^2],等等。 现在,我需要检查是否有遗漏的关键点。例如,覆盖索引索引顺序、避免过多索引、字符串索引前缀这些点是否在引用中有提到。引用[5]提到设计表结构时的考虑,可能涉及到索引的顺序和选择性高的列。引用[4]提到使用索引来避免临时表,所以在优化排序和分组时可以应用这一点。 另外,注意不要使用Markdown格式,使用自然的中文段落,用列表形式分点说明。每个要点需要简明扼要,同时结合引用内容进行解释。 最后,生成的相关问题应该围绕索引的最佳实践,比如如何选择索引列、优化方法、维护策略、分析工具等。确保问题与回答内容紧密相关,帮助用户进一步深入了解。 总结步骤: 1. 整理引用中的关键点。 2. 分点列出最佳实践,每个点结合引用内容。 3. 添加引用标识到对应的位置。 4. 生成相关问题,覆盖回答中的各个部分。 5. 检查LaTeX格式是否正确,数学表达式是否正确使用$符号。 6. 确保语言流畅,结构清晰,符合用户要求。</think>### 如何在MySQL使用索引优化查询性能 以下是优化查询性能的**8个关键实践**,结合MySQL特性与实际案例分析: 1. **优先为高频查询条件与连接字段创建索引** 对`WHERE`、`JOIN`、`ORDER BY`子句中的列建立索引。例如,查询`SELECT * FROM orders WHERE user_id=100`时,应为`user_id`创建索引。 选择**区分度高**的列(如唯一值比例高的列),如身份证号比性别更适合建索引。 2. **使用复合索引时注意列顺序** 复合索引遵循**最左前缀原则**。例如索引$(a,b,c)$可支持`WHERE a=1`或`WHERE a=1 AND b=2`,但无法单独使用$b$或$c$。 将**高频查询条件**放在左侧,且范围查询列尽量靠后。例如$(status, create_time)$适合`WHERE status=1 AND create_time > '2023-01-01'`[^5]。 3. **利用覆盖索引减少IO** 若索引包含查询所需的所有列(如$(user_id, name)$),则无需回表查数据文件。例如: ```sql SELECT name FROM users WHERE user_id=100 -- 使用覆盖索引 ``` 4. **优化排序与分组操作** 对`ORDER BY`或`GROUP BY`的列建索引可避免文件排序。例如: ```sql CREATE INDEX idx_time ON logs(create_time); SELECT * FROM logs ORDER BY create_time DESC LIMIT 10 -- 避免Using filesort ``` 通过索引可减少临时表生成[^4]。 5. **使用EXPLAIN分析执行计划** 通过`EXPLAIN`查看是否命中索引、扫描行数等关键指标: ```sql EXPLAIN SELECT * FROM products WHERE category_id=5; ``` 关注`type`列(应出现`ref`或`range`)、`rows`列(扫描行数越少越好)[^2]。 6. **避免索引滥用** - **删除冗余索引**:如已存在$(a,b)$,则单独索引$a$通常冗余 - **限制索引数量**:每个表建议不超过5-7个索引,避免写操作性能下降 - **谨慎使用长字符串索引**:用前缀索引优化,如`INDEX(email(20))` 7. **字符串索引优化技巧** ```sql -- 使用前缀索引(前20个字符) CREATE INDEX idx_name ON users(name(20)); -- 对哈希值建立索引(如存储URL) ALTER TABLE pages ADD url_hash CHAR(32), ADD INDEX(url_hash); ``` 8. **定期维护索引** 通过`OPTIMIZE TABLE`重建索引碎片: ```sql OPTIMIZE TABLE orders; -- 每月执行一次 ``` 使用`SHOW INDEX`检查索引基数: ```sql SHOW INDEX FROM users; -- 查看Cardinality值 ``` ### 案例分析 某订单表查询缓慢: ```sql SELECT * FROM orders WHERE status='shipped' AND create_time BETWEEN '2023-01-01' AND '2023-06-30' ORDER BY total_price DESC; ``` 优化步骤: 1. 创建复合索引$(status, create_time, total_price)$ (覆盖查询条件和排序) 2. 验证执行计划是否使用索引扫描 3. 添加`total_price`倒序索引以优化排序: ```sql CREATE INDEX idx_status_time_price ON orders(status, create_time, total_price DESC); ```
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值