2亿条数据库 mysql mysql 10亿数据

转载

charlesc 2023-11-04 20:34:24

文章标签 2亿条数据库 mysql mysql 字段 SQL MySQL 文章分类 MySQL 数据库

这篇文章是针对MySQL中十万级数据量的一些常见sql语句优化。本人作为一名准大三计科专业学生，对此理解得不深，也更没有多少实际优化经验，如有错误之处，希望各位及时指正。

一，使用索引来优化SQL语句

1.创建索引前后执行结果对比
2.使用复合索引的原则

二，杜绝对索引使用计算，转型等处理
三，索引不要放在范围查询的右边
四，杜绝SELECT *的使用
四，在使用order by时，要注意索引的有序性

以下的所有操作均以该user表为例来叙述：

use test;

create table user(
id int primary key auto_increment comment "user表主键",
user_id varchar(36) not null comment "用户id",
user_name varchar(36) not null comment "用户名",
phone varchar(20) not null comment "电话号码",
region_id int(9) not null comment "区域id",
)engine innodb charset utf8mb4;

数据量：

2亿条数据库 mysql mysql 10亿数据_MySQL

一，使用索引来优化SQL语句

1.创建索引前后执行结果对比

在不使用索引的情况下执行该SQL

为了使时间更真实，采用sql_no_cache来查询

SELECT SQL_NO_CACHE id,user_id,user_name,phone,region_id FROM user WHERE user_id="58c57ebf5195488d" AND phone="15936831555" AND region_id=30;

执行结果：

2亿条数据库 mysql mysql 10亿数据_SQL_02

执行该SQL一共用掉了0.082s，即82ms

创建复合索引后执行上面的SQL

ALTER TABLE user ADD INDEX idx_uid_phone_regid(user_id,phone,region_id)

2亿条数据库 mysql mysql 10亿数据_SQL_03

再次执行：

2亿条数据库 mysql mysql 10亿数据_SQL_04

效果立竿见影

2.使用复合索引的原则

如果建立的是复合索引，那么在执行的SQL中需要保持索引的字段和个数与索引建立时的字段和个数一致
例如：
创建索引 idx(a,b,c)
SQL中索引顺序：SELET * FROM table WHERE a AND b AND c;

按照上述原则执行：

2亿条数据库 mysql mysql 10亿数据_字段_05

上述SQL执行过程中使用了idx_uid_phone_regid这个索引(key字段)；总共使用了3个常量来查找索引列上的值(ref字段)，即user_id，phone和region_id；MySQL认为它执行查询时必须检查的行数是1行(row字段)

如果按照其他情况执行：

2亿条数据库 mysql mysql 10亿数据_字段_06

执行该SQL过程中，MySQL使用非唯一索引或非唯一索引前缀进行的查找扫描(type字段)，过程使用的索引，常量数，查找行等等可以清楚的看见。其中常量数为2，说明user_id和phone字段均用到了索引。

2亿条数据库 mysql mysql 10亿数据_2亿条数据库 mysql_07

可见，执行该SQL过程中，MySQL对全表进行了扫描(type字段)；过程没有使用任何索引(key字段)；常量数为0，说明phone与region_id都没有使用索引；MySQL认为他执行查找是必须查找的行数是252241(rows字段)。

我举一个例子说明这种情况：
将文章开头所建的索引视为一座桥，查询过程就是从桥的一头到另一头，其中user_id为桥头，phone为桥身，region_id为桥尾。在使用三个索引时，就相当于在两地搭建了一座完整的桥，非常快就可以到河对面；如果使用user_id和phone就相当于与只有桥头和桥身，速度相对较慢；如果只使用region_id，那你就规矩的游过去吧。

(如果有对explain命令不清楚的朋友，请移步查阅)

二，杜绝对索引使用计算，转型等处理

以下用法会导致索引失效

计算，比如+ - * / != <> or等等
函数，如sum()，round()等等
手动/自动类型转化，如：id=“1” ，本来是数字，却将他转化为字符串(5.7以上不存在该问题)

我就以其中的几种情况说明：

2亿条数据库 mysql mysql 10亿数据_字段_08

所用到的联接类型为index，说明除了索引树被扫描之外其他都与ALL类型相同，理论查询的行数为252241行

2亿条数据库 mysql mysql 10亿数据_2亿条数据库 mysql_09

现在，我使用OR来查询该字段，explain给出的反馈是：MySQL查询过程没有使用任何索引，且对全表进行了扫描。

三，索引不要放在范围查询的右边

比如复合索引：idx(x,y)，当where x > 1 and y = “”，这时只能用到x，y字段用不到索引。

下边是测试结果：

2亿条数据库 mysql mysql 10亿数据_SQL_10

四，杜绝SELECT *的使用

1，数据库知道 * 表示所有，查数据时会增大开销（记录数据库和应用程序元数据的目录）
2，多出一些不用的列，这些列可能正好不在索引的范围之内（索引的好处不再多说）select * 杜绝了索引覆盖的可能性，而索引覆盖又是速度极快，效率极高荐的查询方式。
3，不需要的字段会增加数据传输的时间，即使 MySQL 服务器和客户端是在同一台机器上，使用的协议还是 tcp，通信也是需要额外的时间。
4，大字段，例如很长的 varchar，blob，text。准确来说，长度超过 728 字节的时候，会把超出的数据放到另外一个地方，因此读取这条记录会增加一次 I/O 操作。
5，影响数据库自动重写优化SQL（类似 Java 中编译 class 时的编译器自动优化）。----Oracle
6，select * 数据库需要解析更多的对象，字段，权限，属性相关，在 SQL 语句复杂，硬解析较多的情况下，会对数据库造成沉重的负担
7，额外的I/O，内存和 CPU 的消耗，因为多取了不必要的列。
8，用 SELECT * 需谨慎，因为一旦列的个数或顺序更改，就有可能程序执行失败