【从删库到跑路】MySQL数据库的索引(二)——索引的使用和选择-Toy模板网

这篇具有很好参考价值的文章主要介绍了【从删库到跑路】MySQL数据库的索引(二)——索引的使用和选择。希望对大家有所帮助。如果存在错误或未考虑完全的地方，请大家不吝赐教，您也可以点击"举报违法"按钮提交疑问。

🎊专栏【MySQL】
🍔喜欢的诗句：更喜岷山千里雪三军过后尽开颜。
🎆音乐分享【The Right Path】
🥰欢迎并且感谢大家指出小吉的问题

🍔索引的使用

⭐验证索引效率

🎈没有创建索引时，执行SQL语句，查看SQL的耗时
【从删库到跑路】MySQL数据库的索引(二)——索引的使用和选择,MySQL,数据库,mysql

🎈创建索引后，执行SQL语句，查看SQL的耗时

【从删库到跑路】MySQL数据库的索引(二)——索引的使用和选择,MySQL,数据库,mysql
比较发现，时间减少了

🎈细节

创建索引相当于在构建B+Tree
所以创建索引也需要花时间

⭐最左前缀法则

最左前缀法则就是查询从索引的最左列开始，并且不跳过索引中的列
如果索引了多列(联合索引)，要遵循最左前缀法则

🏳️‍🌈查询索引的时候，必须包含最左边的一列，否则不满足最左前缀法则，索引失效
【从删库到跑路】MySQL数据库的索引(二)——索引的使用和选择,MySQL,数据库,mysql

查询的时候，如果
跳过了最左边的列，索引全部失效
跳过了其中的某一列(不是最左边的)，索引部分失效

⭐范围查询

联合查询在出现范围查询(>，<)时，范围查询右侧的列索引失效
【从删库到跑路】MySQL数据库的索引(二)——索引的使用和选择,MySQL,数据库,mysql

在业务允许的情况下，尽量使用>=这样子的查询

⭐索引失效的情况

🏳️‍🌈在索引列上使用运算操作(例如substr函数)，索引将失效
【从删库到跑路】MySQL数据库的索引(二)——索引的使用和选择,MySQL,数据库,mysql

🏳️‍🌈字符串类型的字段使用时，不加引号，索引将失效
【从删库到跑路】MySQL数据库的索引(二)——索引的使用和选择,MySQL,数据库,mysql

🏳️‍🌈模糊查询
如果仅仅是尾部模糊匹配，索引不会失效
【从删库到跑路】MySQL数据库的索引(二)——索引的使用和选择,MySQL,数据库,mysql

如果是头部模糊匹配，索引会失效
如果在查询大数据的情况下，一定要规避这种情况
【从删库到跑路】MySQL数据库的索引(二)——索引的使用和选择,MySQL,数据库,mysql
🏳️‍🌈or连接的条件
使用or分割开的条件，如果or前的条件中列有索引，而后面的列没有索引，那么涉及的索引不会被用到
如下面的例子，由于age没有索引，所以即使id，phone有索引，索引也会失效
【从删库到跑路】MySQL数据库的索引(二)——索引的使用和选择,MySQL,数据库,mysql

为了解决这个问题，我们要针对age建立索引
【从删库到跑路】MySQL数据库的索引(二)——索引的使用和选择,MySQL,数据库,mysql
🏳️‍🌈数据分布影响
如果MySQL 的优化器认为使用索引比全表更慢，那么不使用索引
(是MySQL自己进行评估认为的结果)
比如进行查询一个数据
用户：查询什么什么东西
MySQL：找一个更快的方式，比如使用全表扫描比索引快，那么采用全表扫描了
【从删库到跑路】MySQL数据库的索引(二)——索引的使用和选择,MySQL,数据库,mysql

使不使用索引，是由表中的数据决定的，而不是固定的

🌺SQL提示

SQL提示是指在使用SQL语言编写数据库查询或操作时，数据库管理系统（如MySQL、Oracle、SQL Server等）提供的辅助功能。这些提示可以帮助用户更快地完成SQL语句，减少错误，并提供有关数据库结构和数据的相关信息。
简单来说，就是在SQL语句里面添加一些人为的提示来达到优化操作的目的

🎈告诉数据库使用什么索引(use)

这个是建议MySQL使用这个索引，但是是否要使用，MySQL还是要自己去权衡一下

explain select * from 表名 use index(索引名) where 条件;

🎈告诉数据库不使用什么索引(ignore)

explain select * from 表名 use index(索引名) where 条件;

🎈告诉数据库必须使用什么索引(force)

explain select * from 表名 force index(索引名) where 条件;

🏳️‍🌈前缀索引

当字段类型为字符串(varchar,text)时，有时候需要索引很长的字符串，会使索引变得很大，查询时，浪费大量的磁盘IO，影响查询效率
这个时候我们就可以使用前缀索引，仅对字符串的一部分前缀，建立索引，这样子可以大大节约索引空间，从而提高索引效率

create index 索引名 on 表名(字段名(前缀长度n));

🎈前缀长度n

可以根据索引的选择性来使用，而选择性是指不重复的索引值(基数)和数据表的记录总数的比值，索引选择性越高，查询效率越高
唯一索引的选择性是1，是最好的索引选择性，性能也是最好的

🎈获取索引选择性的方法

可以使用下面的公式来实现

select count(distant 字段名)/count(*) from 表名;

【从删库到跑路】MySQL数据库的索引(二)——索引的使用和选择,MySQL,数据库,mysql
可以分步计算，康康过程

✨如果是针对前缀索引进行计算，那么公式里面要加上substring

select count(distant substring(字段名,起始字母,截取的字母个数))/count(*) from 表名;

【从删库到跑路】MySQL数据库的索引(二)——索引的使用和选择,MySQL,数据库,mysql
可以通过多次计算，找到最合适的前缀长度

🎈查询过程
【从删库到跑路】MySQL数据库的索引(二)——索引的使用和选择,MySQL,数据库,mysql

🎄覆盖索引

MySQL的覆盖索引是一种特殊的索引类型，它包含了查询所需的所有列，从而使得查询可以直接通过索引本身来完成，而无需再去访问实际的数据行。通常，数据库查询需要根据索引定位到相应的数据行，然后再从数据行中获取所需的列值。但是，使用覆盖索引，MySQL可以在索引中找到所有需要的列，避免了额外的数据行查找，尽量减少select *的使用，从而提高了查询性能。