MySql创建索引的原则,知道这些,你再也不用担心面试了!

疯狂的代码 后端 数据库 2019-06-29

MySql创建索引的原则,知道这些,你再也不用担心面试了!

MySql索引的使用一直是工作中的重点,一个线上项目如果不能合理使用索引,那带来的危害可能是毁灭性的(数据库锁死、当机等)。

所以这一块也是面试中的重点,那么,MySql创建索引的原则有哪些呢?

一般来说,在mysql中使用索引的原则有以下几点:

1、 对于查询频率高的字段创建索引;

索引的目的就是提升查询速度,这一点毋庸置疑。所以对于查询频率高的字段要创建索引,如:用户表的手机号。

2、 对排序、分组、联合查询频率高的字段创建索引; 

3、 索引的数目不宜太多

  • 每创建一个索引都会占用相应的物理控件;
  • 过多的索引会导致insert、update、delete语句的执行效率降低;

4、若在实际中,需要将多个列设置索引时,可以采用多列索引

如:某个表(假设表名为Student),存在多个字段(StudentNo, StudentName, Sex, Address, Phone, BirthDate),其中需要对StudentNo,StudentName字段进行查询,对Sex字段进行分组,对BirthDate字段进行排序,此时可以创建多列索引

index index_name (StudentNo, StudentName, Sex, BirthDate);      #index_name为索引名

在上面的语句中只创建了一个索引,但是对4个字段都赋予了索引的功能。

创建多列索引,需要遵循BTree类型,即第一列使用时,才启用索引。

在上面的创建语句中,只有mysql语句在使用到StudentNo字段时,索引才会被启用,如:     

select * from Student where StudentNo = 1000;      #使用到了StudentNo字段,索引被启用。

可以使用explain检测索引是否被启用,如:

explain select * from Student where StudentNo = 1000;

MySql创建索引的原则,知道这些,你再也不用担心面试了!

附:EXPLAIN列的解释

  • table:显示这一行的数据是关于哪张表的
  • type:这是重要的列,显示连接使用了何种类型。从最好到最差的连接类型为const、eq_reg、ref、range、index和ALL
  • type显示的是访问类型,是较为重要的一个指标,结果值从好到坏依次是:system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL
    一般来说,得保证查询至少达到range级别,最好能达到ref。
  • possible_keys:显示可能应用在这张表中的索引。如果为空,没有可能的索引。可以为相关的域从WHERE语句中选择一个合适的语句
  • key: 实际使用的索引。如果为NULL,则没有使用索引。很少的情况下,MYSQL会选择优化不足的索引。这种情况下,可以在SELECT语句中使用USE INDEX(indexname)来强制使用一个索引或者用IGNORE INDEX(indexname)来强制MYSQL忽略索引
  • key_len:使用的索引的长度。在不损失精确性的情况下,长度越短越好
  • ref:显示索引的哪一列被使用了,如果可能的话,是一个常数
  • rows:MYSQL认为必须检查的用来返回请求数据的行数
  • Extra:关于MYSQL如何解析查询的额外信息。将在表4.3中讨论,但这里可以看到的坏的例子是Using temporary和Using filesort,意思MYSQL根本不能使用索引,结果是检索会很慢

5、选择唯一性索引

唯一性索引的值是唯一的,可以更快速的通过该索引来确定某条记录。例如,学生表中学号是具有唯一性的字段。为该字段建立唯一性索引可以很快的确定某个学生的信息。如果使用姓名的话,可能存在同名现象,从而降低查询速度。

6、尽量使用数据量少的索引

如果索引的值很长,那么查询的速度会受到影响。例如,对一个CHAR(100)类型的字段进行全文检索需要的时间肯定要比对CHAR(10)类型的字段需要的时间要多。

7、尽量使用前缀来索引

如果索引字段的值很长,最好使用值的前缀来索引。例如,TEXT和BLOG类型的字段,进行全文检索会很浪费时间。如果只检索字段的前面的若干个字符,这样可以提高检索速度。

8、删除不再使用或者很少使用的索引

表中的数据被大量更新,或者数据的使用方式被改变后,原有的一些索引可能不再需要。数据库管理员应当定期找出这些索引,将它们删除,从而减少索引对更新操作的影响。

评论