1、外键必须加上索引

外键是有必要加上索引的,可以提升主表子表查询性能。

MySQL requires indexes on foreign keys and referenced keys so that foreign key checks can be fast and not require a table scan. In the referencing table, there must be an index where the foreign key columns are listed as thefirst columns in the same order. Such an index is created on the referencing table automatically if it does not exist. This index might be silently dropped later, if you create another index that can be used to enforce the foreign key constraint.index_name, if given, is used as described previously.
MySQL需要外键和引用键上的索引,因此外键检查可以快速,而不需要表扫描。在引用表中,必须有一个索引,其中外键列以相同的顺序作为第一个列列出。如果引用表不存在,则自动在引用表上创建这样的索引。如果您创建了另一个可用于强制外键约束的索引,则该索引可能会在稍后被静默地删除。如果给出了Index_name,就像前面描述的那样使用它。

2、索引与联合索引

索引主要是为了提升查询效率(会消耗一定的存储空间和插入时间)。
在创建索引时,多个索引字段,有区分度的字段应该放在前面(有区分度的靠前的索引字段能大大减少后面的检索数据量,从而提升效率)
联合索引:联合索引时会遵循最左前缀匹配的原则,即最左优先,在检索数据时从联合索引的最左边开始匹配示例:

create table test
(
    id       bigint auto_increment primary key,
    column_1 bigint null,
    column_2 bigint null,
    column_3 bigint null
);

create index test_column_1_column_2_column_3_index
    on test(column_1, column_2, column_3);

比如上面的test表,我们建立了联合索引index test_column_1_column_2_column_3_index on test (column_1, column_2, column_3);当我们进行查询的时候,按照最左前缀的原则,当查询(column_1)、(column_1,column_2)(column_1,column_2,column_3)这三种组合是可以用到我们定义的联合索引的。如果我们查询(column_1,column_3)就只能用到column_1的索引了(可以再创建一个索引避免该问题:create index idx_ext on test(column_2, column_3, column_1);),联合索引中如果条件中是单个字段(column_1或 column_2或 column_3)同样不会走索引。我们不用太关心索引的先后顺序,什么意思呢?比如使用(column_1,column_2)和(column_2,column_1)的效果是一样的,数据库的查询优化器会自动帮助我们优化我们的sql,看哪个执行的效率最高,最后才生成最后执行的sql。
参考文档:
https://www.cnblogs.com/xfeiyun/p/15912238.html

3、分区、分表、分库

分区的底层虽然也是基于分表的原理实现的,即有多个底层表实现,但分区依然是在单库下进行的,在一些需要提高并发的场景中的优化空间非常有限,且一个表最多只能支持 1024个分区。面对日益增长的海量数据,优化存储能力有限。不过在一些非海量数据的大表中,我们可以考虑使用分区来优化表性能,使用分区还有个好处就是无需考虑分页问题(而分库和分表要考虑)。

4、同为索引也各自存在效率问题

比如:tlocation表两个联合索引,deviceGuid和createTime都各自有第一字段的索引(deviceGuid是一个32位的字符串,createTime是年月时分秒字符串)
image.png
通过deviceGuid查询:

select SQL_NO_CACHE count(deviceGuid) from tlocation t where deviceGuid ='1999888';

image.png
通过createTime查询:

select SQL_NO_CACHE count(createTime) from tlocation t where createTime >= '2022-01-01 00:00:00' and createTime <= '2022-10-01 23:59:59';

image.png
用deviceGuid查询话了114ms,用createTime查询要13.3s,明显deviceGuid要比createTime速度快得多。虽然两者都有索引,但由于createTime的取值是一个范围的区间值(大于/小于/等于),而deviceGuid只是一个等于,所以两则会很大速度区别。在业务设计或系统设计时尽量使用区有分度的字段,减少范围查询或模糊查询的情况。

最后修改:2022 年 09 月 22 日
如果觉得我的文章对你有用,请随意赞赏