共计 2461 个字符，预计需要花费 7 分钟才能阅读完成。
自动写代码机器人，免费开通
这篇文章将为大家详细讲解有关 MySQL 大数据查询性能优化的示例，丸趣 TV 小编觉得挺实用的，因此分享给大家做个参考，希望大家阅读完这篇文章后可以有所收获。
MySQL 性能优化包括表的优化与列类型选择，表的优化可以细分为什么？ 1、定长与变长分离；2、常用字段与不常用字段要分离； 3、在 1 对多，需要关联统计的字段上添加冗余字段。
一、表的优化与列类型选择
表的优化：
1、定长与变长分离
如 id int, 占 4 个字节，char(4) 占 4 个字符长度，也是定长，time 即每一单元值占的字节是固定的。
核心且常用字段，宜建成定长，放在一张表。
而 varchar,text,blob 这种变长字段，适合单放一张表，用主键与核心表关联起来。
2、常用字段与不常用字段要分离
需要结合网站具体的业务来分析，分析字段的查询场景，查询频率低的字段，单拆出来。
3、在 1 对多，需要关联统计的字段上添加冗余字段。
看如下的效果：
每个版块里，有 N 条帖子，在首页显示了版块信息和版块下的帖子数。
这是如何做的
如果 board 表只有前 2 列，则需要取出版块后，
再查 post 表，select count(*) from post group by board_id，得出每个版块的帖子数。
二、列类型选择
1、字段类型优先级
整型 date
time enum
char varchar blob,text
整型：定长，没有国家 / 地区之分，没有字符集的差异。比如：
tinyint 1,2,3,4,5 — char(1) a,b,c,d,e
从空间上，都占 1 个字节，但是 order by 排序，前者快。原因，或者需要考虑字符集与校对集（就是排序规则）；
time 定长，运算快，节省空间。考虑时区，写 sql 时不方便 where `2018-08-08`;
enum, 能起到约束的目的，内部用整型来存储，但与 cahr 联查时，内部要经历串与值的转化；
char 定长，考虑字符集和（排序）校对集；
varchar 不定长，要考虑字符集的转换与排序时的校对集，速度慢；
text/blob 无法使用内存临时表（排序等操作只能在磁盘上进行）
附：关于 date/time 的选择，大师的明确意见，直接选 int unsgined not null, 存储时间戳。
例如：
性别：以 utf8 为例
char(1) ,3 个字长字节
enum(男 , 女)；内部转成数字来存，多一个转换过程
tinyint()，定长 1 个字节
2、够用就行，不要慷慨（如 smallint varchar(N)）
原因：大的字节浪费内存，影响速度。
以年龄为例 tinyint unsigned not null，可以存储 255 岁，足够。用 int 浪费了 3 个字节；
以 varchar(10),varchar(300) 存储的内容相同，但在表联查时 varchar(300) 要花更多内存。
3、尽量避免用 NULL()
原因：NULL 不利于索引，要用特殊的字符来标注。
在磁盘上占据的空间其实更大（MySQL5.5 已对 null 做的改进，但查询仍是不便）
三、索引优化策略
1、索引类型
1.1 B-tree 索引
名叫 btree 索引，大的方面看，都用的平衡树，但具体的实现上，各引擎稍有不同，比如，严格的说，NDB 引擎，使用的是 T -tree.
但抽象一下 B-tree 系统，可理解为“排好序的快速查询结构”。
1.2 hash 索引
在 memory 表里默认是 hash 索引，hash 的理论查询时间复杂度为 O(1)。
疑问：既然 hash 的查找如此高效，为什么不都用 hash 索引？
回答：
1、hash 函数计算后的结果，是随机的，如果是在磁盘上放置数据，以主键为 id 为例，那么随着 id 的增长，id 对应的行，在磁盘上随机放置。
2、无法对范围查询进行优化。
3、无法利用前缀索引，比如在 btree 中，field 列的值“helloworld”, 并加索引查询 x=helloworld 自然可以利用索引，x=hello 也可以利用索引（左前缀索引）。
4、排序也无法优化。
5、必须回行，就是说通过索引拿到数据位置，必须回到表中取数据。
2、btree 索引的常见误区
2.1 在 where 条件常用的列上加索引，例如：
where cat_id = 3 and price 查询第三个栏目，100 元以上的商品。
误区：cat_id 上和 price 上都加上索引。
错：只能用上 cat_id 或 price 索引，因为是独立的索引，同时只能用一个。
2.2 在多列上建立索引后（联合索引），查询哪个列，索引都会将发挥作用
误区：多列索引上，索引发挥作用，需要满足左前缀要求。
以 index(a,b,c) 为例，（注意和顺序有关）
四、索引实验
例如：select * from t4 where c1=3 and c2 = 4 and c4 5 and c3=2;
用到了哪些索引：
explain select * from t4 where c1=3 and c2 = 4 and c4 5 and c3=2 \G
如下：
注：（key_len : 4）
五、聚簇索引与非聚簇索引
Myisam 与 innodb 引擎，索引文件的异同
Myisam：由 news.myd 和 new.myi 两个文件，索引文件和数据文件是分开的，叫非聚簇索引。主索引和次索引都指向物理行（磁盘的位置）
innodb：索引和数据是聚在一起的，所以是聚簇索引。innodb 的主索引文件上直接存放该行数据，次索引指向对主键索引的引用。
注意：innodb 来说：
1、主键索引 即存放索引值，又在叶子中存储行的数据。
2、如果没有主键（primary key），则会 unique key 做主键。
3、如果没有 unique，则系统生成一个内部的 rowid 做主键。
4、像 innodb 中，主键的索引结构中，即存储了主键值又存储了行数据，这种结构称为聚簇索引。
聚簇索引
优势：根据主键查询条目比较少时，不用回行（数据就在主键节点下）
劣势：如果碰到不规则数据插入时，造成频繁的页分裂
关于“MySQL 大数据查询性能优化的示例”这篇文章就分享到这里了，希望以上内容可以对大家有一定的帮助，使各位可以学到更多知识，如果觉得文章不错，请把它分享出去让更多的人看到。
向 AI 问一下细节
