在varchar字段上联表查询没用上索引的问题

最新推荐文章于 2023-03-02 17:29:55 发布
原创 最新推荐文章于 2023-03-02 17:29:55 发布 · 364 阅读 · 0 ·
CC 4.0 BY-SA版权
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
文章标签：

#mysql

本文介绍了一个关于数据库联表查询的问题，由于两个表的字符集不同导致索引未被利用，进而影响查询效率。通过将表的字符集统一为utf8后，成功解决了索引不被使用的问题，显著提升了查询速度。

  今天刚遇到的一个问题，两个有主从关系的表，经常需要做关联查询，数据量也不大，测试环境，大概只有1000条不到的数据，但是关联查询比较慢需要1.7s左右。通过执行explain +sql 发现联表查询并没有用到索引，而是全表扫描的，即便使用USE INDEX 命令给出对应的索引，索引也没被用到。找了很久的原因，最后发现问题出在两个表的字符集不同。差别在于一个用的utf8，一个用的utf8mb4。通过以下语句就可以对整个表的字符集进行转换。转了之后索引就会被正确的使用。查询速度以下快了很多查询时间只要1s不到。

ALTER TABLE logtest CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;

MySQL——varchar上的索引必须要指定索引长度
庄小焱
03-27 1939
虽然在阿里的Java开发手册中，是强制要求varchar字段上建立索引必须要指定索引长度，但通过我们的分析也能看出，并不是索引的字段都适用的，比如区分度不高的、或者可以大量运用覆盖索引的实现的，我认为，开发手册强调的普适场景，具体如何运用应该是我们通过了解原理之后，自行笃定。看过阿里Java开发手册的同学，应该有注意到在MySQL索引规约中，有这么一条强制性规范：”在 varchar 字段上建立索引时，必须指定索引长度，没必要对全字段建立索引，根据实际文本区分度决定索引长度“。一、指定索引长度的原理。
mysql索引没有被使用,这个查询为什么没有使用索引？该如何处理
weixin_30921373的博客
03-18 202
SQL codeCreate Table: CREATE TABLE `sina_weibo_status` (`sid` bigint(20) NOT NULL DEFAULT '0',`text` varchar(255) DEFAULT NULL,`source` varchar(255) DEFAULT NULL,`truncated` int(1) DEFAULT NULL ,`thum...
SQL Server联合主键的索引查询时参数数据类型nvarchar与表结构varchar不符时的问题
先学再说
10-18 557
SET STATISTICS IO ON SET STATISTICS TIME ON EXEC sp_executesql N' SELECT * FROM dbo.Item WHERE id = @Id AND code = @Code', N'@Id varchar(36),@Code varchar(36)', @Id = '01', @Cod...
mysql字段类型与查询效率,mysql字段类型varchar和char的查询和索引效率问题
weixin_36324426的博客
03-23 933
测试了一把Mysql中的char和varchar在不使用索引和使用索引下的效率。硬件配置：windows主机，双核,4g内存，搭载mysql5.1。测试步骤一：创建两张表。。b2b和b2b2，，他们都具有两个字段，id自增长、主键、int类型，b2bname在b2b表中使用的是Varchar(255)，在b2b2表中使用的是char(255)。步骤二：创建插入大量数据的存储过程。步骤三：添加测试数...
mysql 索引欣失效的情况
杨先森的专栏
08-08 747
详细介绍mysql索引失效的几种情况，由其是类型转换，这里有些坑，平时开发时容易被忽视！
MySql数据类型都是字符串(varchar),数据类型一样,但是联表查询不走索引,是什么原因呢
热门推荐
吴名氏的博客
03-02 13万+
MySql数据类型都是字符串(varchar),数据类型一样,但是联表查询不走索引,是什么原因呢
VARCHAR字段指定索引长度
董大礼
12-27 5484
一、概述 在varchar字段上建立索引时，必须指定索引长度，没必要对全字段建立索引，根据实际文本区分度决定索引长度 --《阿里开发手册》 二、指定索引长度 2.1 建立测试表 建立一张my_user表 CREATE TABLE my_user ( id BIGINT UNSIGNED, user_name VARCHAR(50), PRIMARY KEY (id) ) 2.2 插入...
MySQL常用的建表、添加字段、修改字段、添加索引SQL语句写法总结
12-16
在MySQL数据库管理中，熟练掌握建表、修改字段、添加字段和创建索引的SQL语句是至关重要的。以下是对这些操作的详细说明： 1. **建表**： 使用`CREATE TABLE`语句来创建新的数据库表。例如，创建一个名为`bulletin...
MySQL数据库中把int转化varchar引发的慢查询
12-15
5. **优化查询条件**：确保查询条件能够有效利用索引，例如，避免在`varchar`字段上使用模糊匹配或非前缀查询。 此外，了解MySQL的优化工具也很重要，如`EXPLAIN`用于分析查询计划，`ANALYZE TABLE`用于更新统计...
MySQL对varchar型字段的索引，字段类型不同造成的隐式转换，导致索引失效
wangjianhua_love的博客
06-07 9765
MySQL索引使用：字段为varchar类型时，条件要使用”包起来，才能正常走索引 表user 里的id是BigInt类型，order表里关联到user表的id误建成userId varchar类型了，在ibatis上查询时，直接传入id Long类型，SQL查询非常慢，即使建立了userId索引，于是改成String 类型的userId去查询，SQL立马变的很快了。 http://ww...
char, varchar, nvarchar区别
Freeze
12-14 391
这篇文章有些问题，有待整理！！ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ 2009-12-03 Oracle CHAR，VARCHAR，VARCHAR2,nvarchar类型的区别与使用 一 varchar,nvarchar, 四个类型都属于变长字符类型， varchar和varchar2的区别在与后者把所有字符都占两字节，前者只对汉字和全角...
【Oracle】全文检索不支持NVARCHAR
12-30 578
全文索引
mysql left join关联字段的类型不一致导致索引失效
CaptainCats的博客
09-29 2277
t1和t2通过order_id关联，order_id在t2建有索引order_detail_index_order_id，原因是order_id字段在t1中是int类型，在t2中是varchar类型，导致用不到索引。我们用另外一种办法，在查询中通过函数将它们转换为一样的类型和字符集。不过修改字段类型有时候会可能遇到一些阻碍。一种办法是修改字段类型让它们保持一致，看下这条sql的explain。但是这里却没有用到。
你真的懂join吗？解决联表查询不使用索引问题
qq_65377318的博客
09-19 3412
小弟最近在开发一个项目时遇到了有点困扰我的问题，很有意思，而且也值得记录一下，希望对大家有用场景：我们有两个表，一个订单表表示t1，一个是订单的明细表t2，t2表中包含用户购买的各个产品，他们是根据订单编号关联的，当我用t1作为驱动表left join 连接t2表时没用到索引，但是用t2表连接t1表时，就用到了全文检索，很奇怪！
关于mysql varchar类型索引失效问题。
choulei0193的博客
04-22 1814
记一次mysql varchar类型索引失效问题： left join 时候，两个varchar类型的编码不一致，会导致全表扫描。这时候需要更改表字段的编码 ，保持一致就好了。 ALTER TABLE `tablename` CHANGE `dd` `dd` VARCHAR( 45 ...
mysql字段加索引查询没有使用_数据库字段明明有加索引，却查询效率这么低，教你避免采坑的五大方法...
weixin_33231849的博客
02-04 426
前提：数据准备drop table if exists t1; /* 如果表t1存在则删除表t1 */CREATE TABLE `t1` ( /* 创建表t1 */`id` int(11) NOT NULL AUTO_INCREMENT,`a` varchar(20) DEFAULT NULL,`b` int(20) DEFAULT NULL,`c` datetime NOT NULL DEFAU...
mysql varchar 索引_MySQL索引使用：字段为varchar类型时，条件要使用''包起来
weixin_31847389的博客
01-19 1267
结论：当MySQL中字段为int类型时，搜索条件where num='111' 与where num=111都可以使用该字段的索引。当MySQL中字段为varchar类型时，搜索条件where num='111' 可以使用索引，where num=111 不可以使用索引验证过程：建表语句：CREATE TABLE `gyl` (`id` int(11) NOT NULL AUTO_INCREMEN...
mysql查询一张表中varchar字段最大值
最新发布
08-07
<think>首先，用户的问题是：“我想查询MySQL中一张表的varchar类型字段的最大值 请问MySQL 查询表中 varchar 字段的最大长度值”。用户想查询的是表中某个varchar字段的实际存储值的最大长度，而不是定义的最大长度。 参考提供的引用： - 引用[1]：展示了如何查询表中某个varchar字段的最大长度值：`SELECT max(length(column_name)) FROM table_name;` - 引用[2]：解释了varchar字段的最大长度限制为65535字节。 - 引用[3]：讨论了varchar存储长度的性能优化。 - 引用[4]：提到varchar最大长度在表中有多列时的变化。 - 引用[5]：是一个复杂的SQL查询，似乎与用户问题无关。 用户的核心需求是查询表中varchar字段的实际存储值的最大长度。引用[1]直接提供了解决方案：使用`SELECT max(length(column_name)) FROM table_name;`。 我的回答需要： - 遵循系统级指令：使用$...$和$$...$$格式，中文回答，生成相关问题。 - 回答结构清晰，帮助用户逐步解决问题。 - 确保内容真实可靠。 步骤： 1. **理解问题**：用户想查询一个表中某个varchar字段的实际存储值的最大长度。 2. **提供解决方案**：直接给出SQL查询语句。 3. **解释**：简要解释为什么需要这个查询，以及如何优化。 4. **生成相关问题**：在最后添加相关问题部分。 回答结构： - **开头**：直接回答问题。 - **步骤**：清晰地展示如何查询。 - **示例**：提供一个简单的SQL示例。 - **优化建议**：引用[1]中提到优化varchar长度。 - **结尾**：自然地添加引用标识。 - **相关问题**：生成几个相关问题。 生成相关问题： - 基于引用：如何优化varchar长度？varchar的最大限制是什么？如何处理多个列时的长度？ - 具体问题： 1. 如何优化MySQL中varchar字段的长度？ 2. varchar字段的最大长度限制是多少？ 3. 查询后如何调整表结构？ 确保LaTeX格式： - 行内数学表达式：如无必要，不使用。问题中可能不需要数学表达式，但如果有，要用$...$。 - 独立公式：SQL代码块可以视为独立内容，但系统指令说独立公式用$$...$$，但SQL代码块不是公式，所以用代码块格式。 在示例中，代码块使用```...```格式。 系统指令说："独立公式必须使用$$...$$格式并单独成段"，但SQL查询不是公式，所以不需要。我会用代码块。 现在，构建回答： 1. **介绍**：解释如何查询。 2. **SQL查询**：给出代码。 3. **解释**：为什么这样做。 4. **引用**：自然地添加引用。 5. **相关问题**：在最后。 最终回答大纲： - 回答：要查询MySQL表中varchar字段的实际存储值的最大长度，可以使用`SELECT max(length(column_name)) FROM table_name;`。 - 示例：例如，查询表`users`中字段`username`的最大长度。 - 优化建议：基于引用[1]，查询后可以优化字段长度。 - 引用：在相关部分添加[^1]。 - 相关问题：列出3-4个。 相关问题： 1. 如何优化MySQL中varchar字段的长度？ 2. varchar字段的最大长度限制是多少？ 3. 查询后如何调整表结构？ 4. 在表中有多列时，varchar长度如何影响性能？ 引用标识： - 引用[1]：在解释优化时添加。 - 引用[2]：在讨论最大长度限制时添加。 - 但用户问题主要是查询，所以引用[1]最相关。 确保回答真实可靠：查询语句是标准的MySQL语法。</think>### 查询MySQL表中varchar字段的最大长度值 在MySQL中，查询表中某个varchar类型字段的实际存储值（非定义长度）的最大长度，是一个常见的优化需求。这有助于了解数据分布，并为后续调整字段长度提供依据。以下是详细步骤和解释： #### 步骤1: 使用SQL查询最大长度 要查询表中特定varchar字段的实际存储值的最大长度，可以使用MySQL内置函数 `LENGTH()` 和 `MAX()`。基本语法如下： ```sql SELECT MAX(LENGTH(column_name)) AS max_length FROM table_name; ``` - **`LENGTH(column_name)`**: 计算字段中每个值的字节长度（对于多字节字符集如UTF-8，需注意字符与字节的差异）。 - **`MAX()`**: 返回这些长度中的最大值。 - **结果**: 查询会返回一个整数，表示该字段在表中所有行的最大存储长度。 例如，查询表 `users` 中字段 `username` 的最大长度： ```sql SELECT MAX(LENGTH(username)) AS max_username_length FROM users; ``` #### 步骤2: 考虑字符集的影响 - 如果表使用多字节字符集（如UTF-8），一个字符可能占用多个字节。此时，`LENGTH()` 返回字节长度，而 `CHAR_LENGTH()` 返回字符长度。根据需求选择： - 字节长度：`SELECT MAX(LENGTH(column_name)) FROM table_name;` - 字符长度：`SELECT MAX(CHAR_LENGTH(column_name)) FROM table_name;` - 示例（UTF-8字符集）： ```sql -- 查询字节长度最大值 SELECT MAX(LENGTH(username)) AS max_byte_length FROM users; -- 查询字符长度最大值 SELECT MAX(CHAR_LENGTH(username)) AS max_char_length FROM users; ``` #### 步骤3: 优化建议 查询后，如果发现最大长度远小于字段定义的长度（如定义 `VARCHAR(255)` 但实际最大长度只有50），可以优化表结构以减少存储空间并提升性能： - **缩短字段长度**：使用 `ALTER TABLE` 修改字段定义。例如： ```sql ALTER TABLE users MODIFY username VARCHAR(50); ``` - **原则**：尽量将字段长度设置为略大于实际最大值（如最大值50，则设置为 `VARCHAR(60)`），避免未来数据扩展问题。如果定义过长，会浪费存储并降低查询效率；定义过短可能导致数据截断[^1]。 - **注意**：修改前需备份数据，并确保新长度能容纳所有现有值。 #### 为什么需要这个查询？ - 在数据库设计中，`VARCHAR` 字段的长度直接影响存储效率和性能。MySQL 对 `VARCHAR` 的总长度限制为 65535 字节（包括所有列），超过会导致错误[^2]。 - 通过查询实际最大长度，可以避免过度分配空间，减少磁盘占用和I/O开销，从而优化查询速度[^3]。 #### 示例场景 假设表 `products` 有字段 `description`（类型为 `VARCHAR(500)`），你想知道实际存储值的最大长度： ```sql SELECT MAX(LENGTH(description)) AS max_desc_length FROM products; ``` 如果返回值为 120，说明最大长度仅为120字节，建议优化为 `VARCHAR(130)` 以节省空间。 通过以上步骤，你可以高效地获取并利用varchar字段的长度信息，为数据库优化提供数据支持[^1][^3]。
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值