要想深入理解mysql索引？这16个点你必须要了解！

男娘i 2023-06-14 13:57 13阅读 0赞

# 前言 #

MySQL索引的建立对于MySQL的高效运行是很重要的，索引可以大大提高MySQL的检索速度。

打个比方，如果合理的设计且使用索引的MySQL是一辆兰博基尼的话，那么没有设计和使用索引的MySQL就是一个人力三轮车。

拿汉语字典的目录页（索引）打比方，我们可以按拼音、笔画、偏旁部首等排序的目录（索引）快速查找到需要的字。

索引分单列索引和组合索引。单列索引，即一个索引只包含单个列，一个表可以有多个单列索引，但这不是组合索引。组合索引，即一个索引包含多个列。

创建索引时，你需要确保该索引是应用在 SQL 查询语句的条件(一般作为 WHERE 子句的条件)。

实际上，索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录。

上面都在说使用索引的好处，但过多的使用索引将会造成滥用。因此索引也会有它的缺点：虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件。

建立索引会占用磁盘空间的索引文件。

![format_png][]

# #

# 1、索引是做什么的? #

索引用于快速找出在某个列中有一特定值的行。不使用索引，mysql必须从第1条记录开始然后读完整个表直到找出相关的行。 表越大，花费的时间越多。如果表中查询的列有一个索引，mysql能快速到达一个位置去搜寻到数据文件的中间，没有必要看所有数据。 大多数mysql索引(PRIMARY KEY、UNIQUE、INDEX和FULLTEXT)在B树中存储。只是空间列类型的索引使用R-树，并且MEMORY表还支持hash索引。

# #

# 2、索引好复杂，我该怎么理解索引，有没一个更形象点的例子？ #

有，想象一下，你面前有本词典，数据就是书的正文内容，你就是那个cpu，而索引，则是书的目录

# #

# 3、索引越多越好？ #

大多数情况下索引能大幅度提高查询效率，但：

数据的变更（增删改）都需要维护索引，因此更多的索引意味着更多的维护成本

更多的索引意味着也需要更多的空间 （一本100页的书，却有50页目录？）

过小的表，建索引可能会更慢哦 ：（读个2页的宣传手册，你还先去找目录？）

# #

# 4、索引的字段类型问题 #

text类型，也可建索引（需指定长度）

myisam存储引擎索引键长度综合不能超过1000字节

用来筛选的值尽量保持和索引列同样的数据类型

# #

# 5、like 不能用索引？ #

尽量减少like，但不是绝对不可用，"xxxx%" 是可以用到索引的，

想象一下，你在看一本成语词典，目录是按成语拼音顺序建立，查询需求是，你想找以 "一"字开头的成语（"一%"），和你想找包含一字的成语（"%一%"）

除了like，以下操作符也可用到索引：

<，<=，=，>，>=，BETWEEN，
    IN<>，not in ，！=则不行

# #

# 6、什么样的字段不适合建索引？ #

一般来说，列的值唯一性太小（如性别，类型什么的），不适合建索引（怎样叫太小？一半说来，同值的数据超过表的百分之15，那就没必要建索引了）

太长的列，可以选择只建立部分索引，（如：只取前十位做索引）

更新非常频繁的数据不适宜建索引（怎样叫非常？意会）

![format_png 1][]

# #

# 7、一次查询能用多个索引吗? #

不能

# #

# 8、多列查询该如何建索引? #

一次查询只能用到一个索引，所以 首先枪毙 a，b各建索引方案 a还是b？谁的区分度更高（同值的最少），建谁！ 当然，联合索引也是个不错的方案，ab，还是ba，则同上，区分度高者，在前

# #

# 9、联合索引的问题? #

where a = "xxx" 可以使用 AB 联合索引 where b = "xxx" 则不可 （再想象一下，这是书的目录？） 所以，大多数情况下，有AB索引了，就可以不用在去建一个A索引了

# #

# 10、哪些常见情况不能用索引? #

like "%xxx"
    
    not in ， !=

对列进行函数运算的情况（如 where md5(password) = "xxxx"）

WHERE index=1 OR A=10

存了数值的字符串类型字段（如手机号），查询时记得不要丢掉值的引号，否则无法用到该字段相关索引，反之则没关系

也即

select * from test where mobile = 13711112222;

可是无法用到mobile字段的索引的哦（如果mobile是char 或 varchar类型的话）

btw，千万不要尝试用int来存手机号（为什么？自己想！要不自己试试）

# #

# 11、覆盖索引(Covering Indexes)拥有更高效率 #

索引包含了所需的全部值的话，就只select 他们，换言之，只select 需要用到的字段，如无必要，可尽量避免select \*

# #

# 12、NULL 的问题 #

NULL会导致索引形同虚设，所以在设计表结构时应避免NULL 的存在（用其他方式表达你想表达的NULL，比如 -1？）

![format_png 2][]

# #

# 13、如何查看索引信息，如何分析是否正确用到索引? #

show index from tablename;
    
    explain select ……;

关于explain，改天可以找个时间专门写一篇入门帖，在此之前，可以尝试 google

# #

# 14、了解自己的系统，不要过早优化! #

过早优化，一直是个非常讨厌而又时刻存在的问题，大多数时候就是因为不了解自己的系统，不知道自己系统真正的承载能力。欢迎大家关注我的公种浩【程序员追风】，整理了2019年多家公司java面试题资料100多页pdf文档，文章都会在里面更新，整理的资料也会放在里面。

比如：几千条数据的新闻表，每天几百几千次的正文搜索，大多数时候我们可以放心的去like，而不要又去建一套全文搜索什么的，毕竟cpu还是比人脑厉害太多

# #

# 15、分享个小案例： #

曾经有个朋友找板子，说：大师帮看看，公司网站打不开 板子笑了笑：大师可不敢当啊，待我看看再说 板子花了10分钟分析了下：中小型企业站，量不大（两三万pv每天），独立服务器，数据量不大（100M不到），应该不至于太慢 某个外包团队做的项目，年久失修，彻底改造？不现实！ 于是，板子花了20分钟给可以加索引的字段都加上了索引，于是，世界安静了 朋友说：另外一个哥们说，优化至少得2w外包费，你只用30分钟，看来，大师你是当之无愧了，选个最好的餐馆吧 板子：那就来点西餐吧，常熟路地铁站肯德基等你！

# #

# 16、最后：永远别忘记的关键词 sql注入 #

希望本文所述对大家mysql数据库程序设计有所帮助。

# 最后 #

欢迎大家一起交流，喜欢文章记得点个赞哟，感谢支持！

[format_png]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly91cGxvYWQtaW1hZ2VzLmppYW5zaHUuaW8vdXBsb2FkX2ltYWdlcy8xODY4ODkyNS1kNjNjNWRjOGYwYTAwMTk2LnBuZw?x-oss-process=image/format,png
[format_png 1]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly91cGxvYWQtaW1hZ2VzLmppYW5zaHUuaW8vdXBsb2FkX2ltYWdlcy8xODY4ODkyNS1hYzhlMWM1Yzg5ODdlNGRlLnBuZw?x-oss-process=image/format,png
[format_png 2]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly91cGxvYWQtaW1hZ2VzLmppYW5zaHUuaW8vdXBsb2FkX2ltYWdlcy8xODY4ODkyNS01M2VlMjJlMDkwMjc2Zjc4LnBuZw?x-oss-process=image/format,png