昨天一个同事做数据迁移引起一个故障,原因是2张表字符集一个为gbk,一个为utf8,并且join key为vachar类型,导致不能正确索引,导致数据库超时,修改字符集编码后正常。本地重现了一下:
一、搞测试数据,文章最后又脚本
二、建索引
--删除多余索引
drop INDEX index_student_s_age on student ;
drop INDEX index_student_s_no on student ;
drop INDEX index_score_point on score ;
drop INDEX index_score_c_id on score ;
--建索引,建标语句中有,可忽略
CREATE INDEX index_student_s_age on student (s_age);
CREATE INDEX index_student_s_no on student (s_no);
CREATE INDEX index_score_point on score (point);
CREATE INDEX index_score_c_id on score (c_id);
三、测试
1、
引用
explain select a.*,b.* from student a , score b
where a.s_no=b.c_id and a.s_no=11
1 SIMPLE b const PRIMARY PRIMARY 4 const 1
1 SIMPLE a ref index_student_s_no index_student_s_no 5 const 1 Using where
可以走到索引
2、修改表编码:仍然可以走到索引,说明join key 都是数值仍然可以走到索引
ALTER TABLE student CONVERT TO CHARACTER SET utf8;
explain select a.*,b.* from student a , score b
where a.s_no=b.c_id and a.s_no=14
1 SIMPLE b const PRIMARY PRIMARY 4 const 1
1 SIMPLE a ref index_student_s_no index_student_s_no 5 const 1 Using where
3、修改关联字段类型为不同类型,不能正确走到索引
ALTER TABLE score MODIFY c_id VARCHAR(32);
--ALTER TABLE student MODIFY s_no VARCHAR(32);
结论,因为字段类型,编码不同都回造成不能正确走到索引,如果都是数值类型的应该就没问题:
1、2表编码不同, join字段数值类型不同, 不能正常走索引,即使编码相同???
2、2表编码相同,join key都是数值类型,正确走到索引
3、2表编码相同,join key数值类型不同,不能正确走到索引
相关数据:
CREATE TABLE `student` (
`s_no` int(11) DEFAULT NULL,
`s_name` varchar(500) DEFAULT NULL,
`s_age` int(11) DEFAULT NULL,
`s_sex` varchar(10) DEFAULT NULL,
KEY `index_student_s_no` (`s_no`),
KEY `index_student_s_age` (`s_age`)
) ENGINE=InnoDB DEFAULT CHARSET=gbk;
CREATE TABLE `score` (
`c_id` int(11) NOT NULL,
`c_name` varchar(255) DEFAULT NULL,
`point` int(11) DEFAULT NULL,
KEY `index_course_point` (`point`),
KEY `index_course_c_id` (`point`),
KEY `index_score_point` (`point`),
KEY `index_score_c_id` (`c_id`)
) ENGINE=InnoDB DEFAULT CHARSET=gbk;
INSERT INTO `student` VALUES (1, '张无忌', 18, '男');
INSERT INTO `student` VALUES (2, '周芷若', 19, '女');
INSERT INTO `student` VALUES (3, '杨过', 19, '男');
INSERT INTO `student` VALUES (4, '赵敏', 18, '女');
INSERT INTO `student` VALUES (5, '小龙女', 17, '女');
INSERT INTO `student` VALUES (6, '张三丰', 18, '男');
INSERT INTO `student` VALUES (7, '令狐冲', 19, '男');
INSERT INTO `student` VALUES (8, '任盈盈', 20, '女');
INSERT INTO `student` VALUES (9, '岳灵珊', 19, '女');
INSERT INTO `student` VALUES (10, '韦小宝', 18, '男');
INSERT INTO `student` VALUES (11, '康敏', 17, '女');
INSERT INTO `student` VALUES (12, '萧峰', 19, '男');
INSERT INTO `student` VALUES (13, '黄蓉', 18, '女');
INSERT INTO `student` VALUES (14, '郭靖', 19, '男');
INSERT INTO `student` VALUES (15, '周伯通', 19, '男');
INSERT INTO `student` VALUES (16, '瑛姑', 20, '女');
INSERT INTO `student` VALUES (17, '李秋水', 21, '女');
INSERT INTO `student` VALUES (18, '黄药师', 18, '男');
INSERT INTO `student` VALUES (19, '李莫愁', 18, '女');
INSERT INTO `student` VALUES (20, '冯默风', 17, '男');
INSERT INTO `student` VALUES (21, '王重阳', 17, '男');
INSERT INTO `student` VALUES (22, '郭襄', 18, '女');
INSERT INTO `score` VALUES (1, '企业管理', 2);
INSERT INTO `score` VALUES (10, '线性代数', 17);
INSERT INTO `score` VALUES (11, '计算机基础', 13);
INSERT INTO `score` VALUES (12, 'AUTO CAD制图', 15);
INSERT INTO `score` VALUES (13, '平面设计', 11);
INSERT INTO `score` VALUES (14, 'Flash动漫', 1);
INSERT INTO `score` VALUES (15, 'Java开发', 9);
INSERT INTO `score` VALUES (16, 'C#基础', 2);
INSERT INTO `score` VALUES (17, 'Oracl数据库原理', 10);
INSERT INTO `score` VALUES (2, 'max, 8);
INSERT INTO `score` VALUES (3, 'UML', 6);
INSERT INTO `score` VALUES (4, '数据库', 7);
INSERT INTO `score` VALUES (5, '逻辑电路', 6);
INSERT INTO `score` VALUES (6, '英语', 3);
INSERT INTO `score` VALUES (7, '电子电路', 5);
INSERT INTO `score` VALUES (8, 'maozedong思想概论', 4);
INSERT INTO `score` VALUES (9, '西方哲学史', 12);
分享到:
相关推荐
如果`OR`涉及到复合索引的不同部分,比如`WHERE (col1, col2) = ('value1', 'value2') OR (col1, col2) = ('value3', 'value4')`,MySQL可能无法有效利用这个索引。在这种情况下,考虑创建单独的索引来匹配每个条件...
索引用于快速找出在某个列中有一特定值的行,不使用索引,MySQL必须从第一条记录开始读完整个表,直到找出相关的行,表越大,查询数据所花费的时间就越多,如果表中查询的列有一个索引,MySQL能够快速到达一个位置去...
mysql查询,通过explain 分析,没有利用到索引,查询效率不高等出现的问题。
### MySQL的or、in、union与索引优化 在数据库查询优化中,索引的使用至关重要,它能显著提升查询速度。本文将基于一个具体的业务场景来探讨在MySQL中使用`union all`、`in`、`or`以及负向查询(如`!=`)时如何有效...
注意:需转换的类型必须是left join 后表的字段,否则不走索引 因为联表字段类型不一致,所以不走索引 select t.* from A tleft join B t1 on t.id = t1.id 第一种转换类型 select t.* from A tleft join B t1 ...
索引下推(Index Condition Pushdown,简称 ICP)是 MySQL 5.6 版本的新特性,可以在对联合索引遍历过程中,对索引中包含的所有字段先做判断,过滤掉不符合条件的记录之后再回表,能有效的减少回表次数。 5. 索引...
MySQL的`ORDER BY`和`GROUP BY`子句在执行查询时,对于性能的影响是显著的,尤其是在大数据量的表中。为了提高查询效率,索引优化显得至关重要。本文将详细介绍如何利用索引来优化`ORDER BY`操作。 首先,MySQL在...
varchar(50) 中的 50 表示字段最多存放 50 个字符。int(20) 和 char(20) 以及 varchar(20) 的区别在于,int(20) 表示字段是 int 类型,char(20) 表示字段是固定长度字符串,varchar(20) 表示字段是可变长度字符串。 ...
当查询涉及分组或`DISTINCT`操作,且预期结果集很大或很小,可以使用这些选项指导MySQL使用临时表: ```sql SELECT SQL_BUFFER_RESULT FIELD1, COUNT(*) FROM TABLE1 GROUP BY FIELD1; ``` `SQL_BIG_RESULT`...
- 当使用`LIKE %keyword%`时,即查询模式中前后都有通配符,MySQL无法使用索引,因为这种模式需要全表扫描。即使表中有针对该字段的索引,也不会被利用。 - `LIKE keyword%`的情况则不同,索引通常是有效的,因为...
在上述案例中,问题出现在一个名为`rosterusers`的表上,该表中的`username`字段有一个基于前缀的联合唯一索引`i_rosteru_user_jid`,只索引了每个`username`的前75个字符。当执行`SELECT * FROM rosterusers ORDER ...
在DELETE操作上,无论是走索引还是不走索引,Oracle的执行速度均优于MySQL。对于SELECT操作,当谓词利用索引时,MySQL几乎是即时完成,Oracle则需要0.28秒;当没有利用索引时,MySQL用时0.26秒,Oracle用时0.32秒。...
比如我们常见的,当一个 where 子句中的一列有索引时去走索引。 CBO 方式:它是看语句的代价(Cost),这里的代价主要指 Cpu 和内存。优化器在判断是否用这种方式时,主要参照的是表及索引的统计信息。统计信息给出表...
当用户提交一个SQL查询给Oracle数据库时,Oracle会根据内部优化器生成一个执行计划。执行计划是指定如何最有效地执行SQL语句的具体策略,其中包括但不限于: - 全表扫描(Full Table Scan):遍历整个表以找到匹配的...
如果一个表中没有合适的索引,数据库在执行查询操作时可能需要全表扫描,这会消耗大量的系统资源,尤其是磁盘I/O资源。而通过在适当的数据列上建立索引,可以将全表扫描转化为索引扫描,从而减少所需访问的数据量,...
很多开发人员在使用MySQL时经常会在部分列上进行函数计算等,导致无法走索引,在数据量大的时候,查询效率低下。针对此种情况本文从MySQL5.7 及MySQL8.0中分别进行不同方式的优化。 1、 MySQL5.7 MySQL5.7版本中不...
- 当`Extra`显示`Using index`时,表示MySQL能够完全基于索引完成查询,无需回表查询原始数据,这在性能上是非常理想的。 总结: - 对于索引字段,=通常优于LIKE,因为它可以直接命中索引,提高查询速度。 - 如果...