澳门在线威尼斯官方 > 电脑数据库 > 百万级数据库优化方案,案例分析

原标题:百万级数据库优化方案,案例分析

浏览次数:176 时间:2019-09-25

项目背景

有三张百万级数据表

知识点表(ex_subject_point)9,316条数据

试题表(ex_question_junior)2,159,519条数据 有45个字段

知识点试题关系表(ex_question_r_威尼斯澳门在线 ,knowledge)3,156,155条数据

测试数据库为:mysql (5.7)

项目背景

有三张百万级数据表

知识点表(ex_subject_point)9,316条数据

试题表(ex_question_junior)2,159,519条数据 有45个字段

知识点试题关系表(ex_question_r_knowledge)3,156,155条数据

测试数据库为:mysql (5.7)

 

7、在 where 子句中使用参数,是不会导致全表扫描。

1.对查询进行优化,要尽量避免全表扫描,首先应考虑在 where 及 order by 涉及的列上建立索引。

案例分析

威尼斯澳门在线 1

案例分析:

SELECT ex_question_junior.QUESTION_ID
FROM ex_question_junior 
WHERE ex_question_junior.GRADE_ID=15

执行时间:17.609s (多次执行,在17s左右徘徊)

优化后:给GRADE_ID字段添加索引后

执行时间为:11.377s(多次执行,在11s左右徘徊)

备注:我们一般在什么字段上建索引?

这是一个非常复杂的话题,需要对业务及数据充分分析后再能得出结果。主键及外键通常都要有索引,其它需要建索引的字段应满足以下条件:

  a、字段出现在查询条件中,并且查询条件可以使用索引;

  b、语句执行频率高,一天会有几千次以上;

  c、通过字段条件可筛选的记录集很小,那数据筛选比例是多少才适合?

这个没有固定值,需要根据表数据量来评估,以下是经验公式,可用于快速评估:

小表(记录数小于10000行的表):筛选比例<10%;

大表:(筛选返回记录数)<(表总记录数*单条记录长度)/10000/16

单条记录长度≈字段平均内容长度之和+字段数*2

以下是一些字段是否需要建B-TREE索引的经验分类:

威尼斯澳门在线 2

8、在 where 子句中对字段进行表达式操作,是不会导致全表扫描。不过查询速度会变慢,所以尽量避免使用。

2、应尽量避免在 where 子句中对字段进行 null 值判断,否则将导致引擎放弃使用索引而进行全表扫描

select id from t where num is null

最好不要给数据库留NULL,尽可能的使用 NOT NULL填充数据库.

备注、描述、评论之类的可以设置为 NULL,其他的,最好不要使用NULL。

不要以为 NULL 不需要空间,比如:char(100) 型,在字段建立时,空间就固定了, 不管是否插入值(NULL也包含在内),都是占用 100个字符的空间的,如果是varchar这样的变长字段, null 不占用空间。

可以在num上设置默认值0,确保表中num列没有null值,然后这样查询:

select id from t where num = 0

案例分析

威尼斯澳门在线 3

执行时间是1.064s

案例分析:

在mysql数据库中对字段进行null值判断,是不会放弃使用索引而进行全表扫描的。

威尼斯澳门在线 4

SELECT ex_question_junior.QUESTION_ID
FROM ex_question_junior 
WHERE IS_USE is NULL

执行时间是:11.729s

威尼斯澳门在线 5

SELECT ex_question_junior.QUESTION_ID
FROM ex_question_junior 
WHERE IS_USE =0

执行时间是12.253s

时间几乎一样。

优化方案

SELECT ex_question_junior.QUESTION_ID
FROM ex_question_junior 
WHERE ex_question_junior.QUESTION_CHANNEL_TYPE =4/2;

执行时间是0.012s

3、应尽量避免在 where 子句中使用 != 或 <> 操作符,否则将引擎放弃使用索引而进行全表扫描。

9、应尽量避免在where子句中对字段进行函数操作,这将导致引擎放弃使用索引而进行全表扫描。

案例分析:

在mysql数据库中where 子句中使用 != 或 <> 操作符,引擎不会放弃使用索引。

EXPLAIN
SELECT ex_question_junior.QUESTION_ID
FROM ex_question_junior 
WHERE ex_question_junior.GRADE_ID !=15

威尼斯澳门在线 6

执行时间是:17.579s

威尼斯澳门在线 7

执行时间是:16.966s

案例分析

威尼斯澳门在线 8

4.应尽量避免在 where 子句中使用 or 来连接条件,如果一个字段有索引,一个字段没有索引,将导致引擎放弃使用索引而进行全表扫描

本文由澳门在线威尼斯官方发布于电脑数据库,转载请注明出处:百万级数据库优化方案,案例分析

关键词:

上一篇:没有了

下一篇:没有了