浅谈数据库优化

灬沙师弟

发布于 2024-02-22 13:26:30

1170

发布于 2024-02-22 13:26:30

文章被收录于专栏：Java面试教程Java面试教程

浅谈数据库优化

面试官： 平时项目中用到了哪些数据库优化的方法？

了不起： 建索引

面试官： 除了建索引呢？

优化策略

数据类型优化

数据类型的优化主要是指选取什么类型。需要遵循“小而简单”的原则。因为这样的数据类型占用的内存、磁盘更低，CPU处理时间也更少。举个常见的例子。

1、日期类型选择。MySQL中关于时间类型，MySQL中最小精度是秒。有Datetime、Timestamp和int三种类型类型来存储时间，个人推荐使用Datetime。Datetime时间类型存储的范围比Timestamp更大，而且Timestamp因时区不同而不同，int时间显示不够直观。

2、指定最大显示宽度，不会改变存储空间。显示宽度与存储大小或类型包含的值的范围无关。也就是说对存储和计算来讲，指定了长度的int(1)和int(12)是相同的。

3、 unsigned(无符号)属性不允许有负值，这可以使正数存储范围扩大一倍，比如UNSIGNED TINYINT存储的范围是0 - 255，TINYINT的范围是-128 ~ 127。所以在没有用到负数的情况下，建议用unsigned(无符号)。

4、存储IP地址时最好使用无符号整数，而不是字符串，这样可以节省存储空间，Inet_ATON()将带点儿的IP转为数字，而Inet_NTOA可将数字转为IP。

5、对一些精度要求比较高的数据，有人建议使用DECIMAL,decimal需要额外的空间和计算开销。建议使用BIGINT，在需要精确到千分之一的时候，可以先乘以1000，再用BIGINT存。

6、通常情况下列最好为NOT NULL, NULL 会使得索引失效。

7、当数据量比较大的时候，不推荐使用alter table。因为alter table 会创建一个新结构的表，并把老表中的数据插入到新表中。

8、不推荐使用Enum。因为枚举类型是固定的字符串列表，添加和删除的时候需要使用alter table命令。

索引优化

MySQL优化最重要建立索引，建立好的索引可以起到事半功倍的效果。

索引结构与算法

首先要介绍索引的结构。索引数据结构与原理可以参考这篇文章：MySQL索引背后的数据结构及算法原理。这里不做过多的解释。看过这篇文章之后抛出一个问题：为什么不用Hash索引，而是使用B-Tree索引结构，理论上Hash索引的查询时间复杂度只有O(1)。这里主要有以下5点原因：1、hash函数计算后的结果，是随机的,如果是在磁盘上放置数据。比主键为id为例, 那么随着id的增长, id对应的行,在磁盘上随机放置. 2、不法对范围查询进行优化。3、无法利用前缀索引. 比如在btree中, field列的值“hellopworld”,并加索引。查询 xx=helloword,自然可以利用索引, xx=hello,也可以利用索引，(左前缀索引)。因为hash(‘helloword’),和hash(‘hello’),两者的关系仍为随机。4、排序也无法优化。5、必须回行.就是说通过索引拿到的只是数据位置,而不是数据值，必须回到表中取数据。

索引注意点

理想的索引应该具有下面几点：1: 查询频繁 2: 区分度高 3: 长度小 4: 尽量能覆盖常用查询字段。
哪些列不应该建立索引呢？1、更新非常频繁的字段不适合建立索引。2、唯一性太差的字段不适合单独创建索引。3、不会出现在where子句中的字段不应该创建索引。
索引覆盖: 索引覆盖是指如果查询的列恰好是索引的一部分,那么查询只需要在索引文件上进行,不需要回行到磁盘再找数据. 这种查询速度非常快,称为”索引覆盖”
不要用UUID或者随机字符串作为主键值，尽量用连续增长的值对于innodb而言,因为节点下有数据文件,因此节点的分裂将会比较慢。对于innodb的主键,尽量用整型,而且是递增的整型。如果是无规律的数据,将会产生页的分裂，影响速度。关于UUID与自增主键的比较参看这篇文章

索引不是创建的越多越好。过多的索引不可能一次性读取到内存，索引是以索引文件的形式存储在磁盘上。过多的索引是会产生磁盘I/O消耗，从而影响性能。

特定语句优化

Count优化

MyIsam的count()比较快，原因是MyIsam对行数进行了存储。一旦有条件的查询, 速度就不再快了，尤其是where条件的列上没有索引。假如,id<100的商家都是我们内部测试的,我们想查查真实的商家有多少? select count(*) from lx_com where id>=100; (1000多万行用了6.X秒) 小技巧:

select count(*) from lx_com; 快
select count(*) from lx_com where id<100; 快

Join优化

Join语句经常听到“小结果集驱动大结果集”。为什么会有这句话？这和Mysql的关联查询原理有关。Mysql的关联查询是取第一张表的一行数据去遍历第二张表的所有数据找到匹配的行，依次遍历第一张表的数据。有人会问MN和NM结果不是一样的吗？“小结果集驱动大结果集”，这句话的前提是连接字段建立了索引。具体可以看下面的例子：

SELECT A.xx,B.yy 
FROM A INNER JOIN B USING(c)
WHERE A.xx IN (5,6)

上述代码的执行可以参照下面的伪代码：

outer_iterator = SELECT A.xx,A.c FROM A WHERE A.xx IN (5,6);
outer_row = outer_iterator.next;
while(outer_row) {
    inner_iterator = SELECT B.yy FROM B WHERE B.c = outer_row.c;
    inner_row = inner_iterator.next;
    while(inner_row) {
        output[inner_row.yy,outer_row.xx];
        inner_row = inner_iterator.next;
    }
    outer_row = outer_iterator.next;
}

假设我们在A和B表的c列都建立了索引，可以看到外层循环无法用到索引一定会遍历M次，但是内层循环可以利用索引减少内存循环的次数，如果B的数据量大的话，优化效果还是非常可观的。

group by优化

Group by的实质是先排序然后分组。所以建议在group by中利用索引，这样可以减少临时表的创建以及文件排序。order by的列要和group by的一致，否则也会引起临时表（原因是因为group by 和 order by 都需要排序，如果2者的列不一致，那必须经过至少1次排序）。以A，B表连接为例 ,主要查询A表的列, 那么 group by ,order by 的列尽量相同,而且列应该显示声明为A的列。

select A.id,A.cat_id from A inne join B group by A.cat_id order by A.cat_id

Limit优化

当表的数据非常多的时候，limit的分页优化可以用延迟索引。比如我们要查询5000000后的10条记录，用下面的语句效率是非常低的。

select id,name from lx_com limit 5000000,10;

这是因为limit offset,N, 当offset非常大时, 效率极低, 可以先在子查询语句里利用覆盖索引扫描，然后再做一个关联查询，这种技术就是延迟索引。SQL语句如下：

select id,name from lx_com inner join (select id from lx_com limit 5000000,10) as tmp using(id);

Union优化

建议使用union all 而不是union。union all 不过滤效率提高,如非必须,请用union all。因为 union去重的代价非常高, Mysql会把各个查询结果插入到临时表中，然后做唯一性检查。所以请放在程序里去重。

性能分析工具

开启慢查询日志

MySQL 慢查询的相关参数解释：

slow_query_log ：是否开启慢查询日志，1表示开启，0表示关闭。

log-slow-queries ：旧版（5.6以下版本）MySQL数据库慢查询日志存储路径。可以不设置该参数，系统则会默认给一个缺省的文件host_name-slow.log

slow-query-log-file：新版（5.6及以上版本）MySQL数据库慢查询日志存储路径。可以不设置该参数，系统则会默认给一个缺省的文件host_name-slow.log

long_query_time ：慢查询阈值，当查询时间多于设定的阈值时，记录日志。

使用profile语句

打开profile分析语句

查看分析列表

查看单条语句的执行过程

使用explain语句

如果要定量分析查询语句涉及到了多少行，可以使用explain。explain语句是非常重要的分析工具。提测之前使用explain分析一下SQL语句是一种美德。explain可以显示如下字段：

这里会选择比较重要几个字段的值通过列子进行讲解。

select_type 查询类型

simple语句中没有子查询或者union

select field from table;

dependent subquery 子查询中的第一个select语句，依赖外部查询结果集

select * from test.tabname where id in(select id from test.tabname2 where name='love');

以上语句有个错误的理解是认为按照下面两个结果执行

select group_concat(id) from test.tabname2 where name='love';
--内层查询结果：1,3,5,7,9,11,13,15,17,1
 
select * from test.tabname where id in(1,3,5,7,9,11,13,15,17,19);

通过explain发现其实他是先根据关联外部tabname, 而不是先去执行这个子查询。可以通过连接查询来优化上面的语句。

select tabname. * from test.tabname inner join test.tabname2 using(id) where tabname2.name='love';

可以看到查询类型变成了simple简单查询。

primary最外层的select, 例子参见dependent subquery

type：很重要,显示了连接使用了哪种类别,有无使用索引。type代表查询执行计划（QEP）中指定的表使用的连接方式

从最好到最差依次为：：system，const，eq_ref，ref，fulltext，ref_or_null，unique_subquery，index_subquery，range，index_merge，index，ALL

system: const的一个特例，表中只有一条记录
const: where条件是以常量为单位，表中最多一条记录匹配。

eq_ref：最多只会有一条匹配结果，一般是通过主键或是唯一索引来访问。一般会出现在连接查询的语句中。通过索引列,直接引用某1行数据

ref: 它返回所有匹配某个单个值的行。它用在关联操作只使用了索引的最左前缀，或者索引不是UNIQUE和PRIMARY KEY。连接查询中被驱动的表索引引用查询.。通过索引列,可以直接引用到某些数据行

range: 引用范围扫描，见上面的例子
all: 全表扫描效率最低

possible key 可能用到哪些索引进行查询
key 实际用到的索引
key_len 实际用到的索引字节数 ref 列出是通过哪个字段来进行连接查询，或者是否是通过常量（const） extra 是EXPLAIN输出中另外一个很重要的列，该列显示MySQL在查询过程中的一些详细信息

重点关注下面几个值：

Using filesort：文件排序(文件可能在磁盘,也可能在内存)。需要注意的是，尽管名为 "filesort"，但并不一定意味着排序操作会在磁盘文件中进行。如果排序的数据量小于 sort_buffer_size 和 read_rnd_buffer_size 参数设置的值，排序操作可以在内存中完成。只有当数据量太大，无法放入内存时，才会在磁盘上进行。
Using temporary：是指用上了临时表, group by 与order by 不同列时,或group by ,order by 别的表的列.
Using index：表示使用索引，如果只有 Using index，说明他没有查询到数据表，只用索引表就完成了这个查询，这个叫覆盖索引，效率非常高。
Using index condition：意味着MySQL 使用了“索引条件推送下”（Index Condition Pushdown，ICP）的优化策略。例如，假设我们有一个包含 id 和 name 两个列的表，id 列有一个索引。如果我们执行以下查询：

SELECT * FROM table WHERE id > 1000 AND name = 'John';

在这个例子中，id 列的条件可以直接使用索引，而 name 列的条件则不能。在没有 ICP 的情况下，MySQL 需要先从索引中找出所有 id > 1000 的行，然后对每一行检查 name 是否等于 'John'。而在使用 ICP 的情况下，MySQL 可以在检索索引时就过滤掉 name 不等于 'John' 的行，从而减少需要处理的数据量。