SQL/MySQL--索引的优点/缺点/创建索引的原则

我就是我 2022-09-13 05:20 213阅读 0赞

原文网址：[SQL/MySQL--索引的优点/缺点/创建索引的原则\_IT利刃出鞘的博客-CSDN博客][SQL_MySQL--_IT_-CSDN]

# **索引优点** #

索引优点就是提高了查询性能，主要从以下几个方面

1.  索引大大减少了服务器需要扫描的数据量。
2.  索引可以帮助服务器避免排序和临时表。
3.  索引可以将随机I/O 变为顺序I/O

# **索引缺点** #

1.  降低了数据写入的效率。
    
    1.  原因：当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护
2.  索引增加了查询优化器的选择时间。
    
    1.  查询优化器在对一条sql语句进行分析时，会结合一系列的分析计算出一条最优的查询sql。
    2.  添加了索引之后，相当于是在原来的基础上，添加了对索引因素的分析，若在很多字段上创建了索引，会增加这个选择的时间。
3.  索引需要占物理空间。除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果要建立聚簇索引，那么需要的空间就会更大。

# 创建索引的原则 #

<table> 
 <tbody> 
 <tr> 
 <td style="width:101px;"> 索引原则 </td> 
 <td style="width:588px;"> 说明/示例 </td> 
 </tr> 
 <tr> 
 <td style="width:101px;"> 对查询频率高的字段创建索引 </td> 
 <td style="width:588px;"> &nbsp; &nbsp; &nbsp; &nbsp; 查询频率高的字段有：作为查询条件的字段（where子句中的列）、连接子句中指定的列 </td> 
 </tr> 
 <tr> 
 <td style="width:101px;"> 为经常需要排序、分组和联合操作的字段建立索引 </td> 
 <td style="width:588px;"> &nbsp; &nbsp; &nbsp; &nbsp; 经常需要ORDER BY、GROUP BY、DISTINCT和UNION等操作的字段，排序操作会浪费很多时间。 &nbsp; &nbsp; &nbsp; &nbsp; 如果为其建立索引，可以有效地避免排序操作。 </td> 
 </tr> 
 <tr> 
 <td style="width:101px;"> 使用唯一索引 </td> 
 <td style="width:588px;"> &nbsp; &nbsp; &nbsp; &nbsp; 唯一性索引的值是唯一的，可以更快速的通过该索引来确定某条记录。唯一值少的列上不适合建立索引或者建立索引效率低，因为创建索引会对那一列做自动的排序。例如，学生表中学号是具有唯一性的字段。为该字段建立唯一性索引可以很快的确定某个学生的信息。若使用姓名的话，可能存在同名现象，从而降低查询速度。 &nbsp; &nbsp; &nbsp; &nbsp; 主键索引和唯一键索引，在查询中使用是效率最高的。 </td> 
 </tr> 
 <tr> 
 <td style="width:101px;"> 使用短索引 </td> 
 <td style="width:588px;"> &nbsp; &nbsp; &nbsp; &nbsp; 如果索引的值很长，那么查询的速度会受到影响。例如，对一个CHAR(100)类型的字段进行全文检索需要的时间肯定要比对CHAR(10)类型的字段需要的时间要多。 </td> 
 </tr> 
 <tr> 
 <td style="width:101px;"> 使用前缀来索引 </td> 
 <td style="width:588px;"> &nbsp; &nbsp; &nbsp; &nbsp; 如果索引字段的值很长，最好使用值的前缀来索引。例如，TEXT和BLOG类型的字段，进行全文检索会很浪费时间。如果只检索字段的前面的若干个字符，这样可以提高检索速度。 </td> 
 </tr> 
 <tr> 
 <td style="width:101px;"> 索引的数目不要太多 </td> 
 <td style="width:588px;"> 1. 每创建一个索引都会占用相应的物理空间； 2. 过多的索引会导致insert、update、delete语句的执行效率降低； &nbsp; &nbsp; &nbsp; &nbsp; 在修改表的内容时，索引必须进行更新，有时可能需要重构，因此，索引越多，所花的时间越长。若一个索引很少利用或从不使用，那么会不必要地减缓表的修改速度。此外，MySQL 在生成一个执行计划时，要考虑各个索引，这也要费时间。创建多余的索引给查询优化带来了更多的工作。索引太多，也可能会使MySQL 选择不到所要使用的最好索引。只保持所需的索引有利于查询优化。如果想给已索引的表增加索引，应该考虑所要增加的索引是否是现有多列索引的最左索引。如果是，则就不要费力去增加这个索引了，因为已经有了。 </td> 
 </tr> 
 <tr> 
 <td style="width:101px;"> 避免索引失效 </td> 
 <td style="width:588px;"> </td> 
 </tr> 
 </tbody> 
</table>

# **不推荐使用索引的情况** #

<table> 
 <tbody> 
 <tr> 
 <td>情况</td> 
 <td>说明</td> 
 </tr> 
 <tr> 
 <td>查询不频繁的字段</td> 
 <td>既然这些列很少使用到，因此有索引或者无索引，并不能提高查询速度。相反，由于增加了索引，反而降低了系统的维护速度和增大了空间需求。</td> 
 </tr> 
 <tr> 
 <td>数据唯一性差（一个字段的取值只有几种时）的字段不要使用索引</td> 
 <td>比如性别，只有两种可能数据。意味着索引的二叉树级别少，多是平级。这样的二叉树查找无异于全表扫描。</td> 
 </tr> 
 <tr> 
 <td>修改频率远远大于查询频率</td> 
 <td> 比如login count（登录次数），频繁变化导致索引也频繁变化，增大数据库工作量，降低效率。 &nbsp; &nbsp; &nbsp; &nbsp; 修改性能和检索性能是互相矛盾的。当增加索引时，会提高检索性能，但是会降低修改性能。当减少索引时，会提高修改性能，降低检索性能。因此，当修改性能远远大于检索性能时，不应该创建索引。 </td> 
 </tr> 
 </tbody> 
</table>

# **外键** #

**其他网址**

> [mysql 外键（foreign key）的详解和实例\_数据库\_差不多先生-CSDN博客][mysql _foreign key_-CSDN]  
> [MySQL中外键的定义、作用、添加和删除\_数据库\_不当初-CSDN博客][MySQL_-CSDN]

## 简介 ##

在父表上进行update/delete以更新或删除在子表中有一条或多条对应匹配行的候选键时，父表的行为取决于：在定义子表的外键时指定的on update/on delete子句。

例：alter table tb1 add constraint FK\_ID foreign key(\`gid\`) references tb2(\`id\`) on delete update;

<table> 
 <tbody> 
 <tr> 
 <td> 关键字 </td> 
 <td> 含义 </td> 
 </tr> 
 <tr> 
 <td> CASCADE </td> 
 <td> 在父表上update/delete记录时，同步update/delete掉子表的匹配记录 </td> 
 </tr> 
 <tr> 
 <td> SET NULL </td> 
 <td> 在父表上update/delete记录时，将子表上匹配记录的列设为null 要注意子表的外键列不能为not null </td> 
 </tr> 
 <tr> 
 <td> RESTRICT </td> 
 <td> 如果子表中有匹配的记录,则不允许对父表对应候选键进行update/delete操作。(这是默认设置，也是最安全的设置) </td> 
 </tr> 
 <tr> 
 <td> NO ACTION </td> 
 <td> 与RESTRICT相同。 </td> 
 </tr> 
 <tr> 
 <td> SET DEFAULT </td> 
 <td> 父表有变更时，子表将外键列设置成一个默认的值 但Innodb不能识别。 </td> 
 </tr> 
 </tbody> 
</table>

## 不要用外键索引 ##

**其他网址**

[【数据库外键】为什么不推荐使用物理外键?\_corleone\_4ever的博客-CSDN博客][corleone_4ever_-CSDN]  
[MySQL - 外键使用详解\_小小默：进无止境-CSDN博客][MySQL - _-CSDN]  
[QL中外键的作用与使用场景\_爱喝咖啡的程序猫的博客-CSDN博客][QL_-CSDN]

[SQL_MySQL--_IT_-CSDN]: https://knife.blog.csdn.net/article/details/120520636
[mysql _foreign key_-CSDN]: https://blog.csdn.net/qq_34306360/article/details/79717682
[MySQL_-CSDN]: https://blog.csdn.net/lk142500/article/details/83590862
[corleone_4ever_-CSDN]: https://blog.csdn.net/corleone_4ever/article/details/106540079
[MySQL - _-CSDN]: https://blog.csdn.net/J080624/article/details/72899020
[QL_-CSDN]: https://blog.csdn.net/weixin_40922494/article/details/105956230