在多数时候我们都会对数据库建立索引,然后在SQL中尽可能的用上索引中使用的字段,但是我们这样做后,确实用到了这个索引了吗?事实是未必的,比如当SQL中Where条件值类型和字段定义的类型不一致时,这个索引就不会被用上。
定义如下表结构:
CREATE TABLE users (
phone varchar(21),
stat varchar(4),
KEY phonestat (stat, phone)
);
插入n多的数据后来做测试,首先查看插入的数据记录数。
SELECT count(*) FROM users;
+----------+
| count(*) |
+----------+
| 5925 |
+----------+
1 row in set (1.85 sec)
我们写两个SQL,一个
SELECT SQL_NO_CACHE count(*) FROM users WHERE stat = 0;
+----------+
| count(*) |
+----------+
| 3640 |
+----------+
1 row in set (0.05 sec)
再看另一个SELECT SQL_NO_CACHE count(*) FROM users WHERE stat = '0';
+----------+
| count(*) |
+----------+
| 3566 |
+----------+
1 row in set (0.00 sec)
为什么前面一种写法执行所需要时间较长并且所选择出的记录数多一些呢?
我们使用EXPLAIN看一下对索引的使用情况。
EXPLAIN SELECT SQL_NO_CACHE count(*) FROM users WHERE stat = 0;
+----+-------------+-----------+-------+---------------+-----------+---------+------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------+-------+---------------+-----------+---------+------+------+--------------------------+
| 1 | SIMPLE | users | index | phonestat | phonestat | 75 | NULL | 5925 | Using where; Using index |
+----+-------------+-----------+-------+---------------+-----------+---------+------+------+--------------------------+
1 row in set (0.00 sec)
看rows 5925,实际进行了一次全表检索,索引没有用上。
再看第二种
EXPLAIN SELECT SQL_NO_CACHE count(*) FROM users WHERE stat = '0';
+----+-------------+-----------+------+---------------+-----------+---------+-------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------+------+---------------+-----------+---------+-------+------+--------------------------+
| 1 | SIMPLE | users | ref | phonestat | phonestat | 12 | const | 2968 | Using where; Using index |
+----+-------------+-----------+------+---------------+-----------+---------+-------+------+--------------------------+
1 row in set (0.00 sec)
为什么会这样呢,原因就在于所使用的条件的字段值类型和数据库定义的字段类型不一致时,MySQL就会在内部做数据转化,它的处理行为就会和我们期望的有些不一样,在这里当我们使用整数来作为定义中的字符串字段做比较时,MySQL会自动将数据库中的记录转化为整数做比较,而且将空值转化为整数0做比较,所以选出来的记录数多一些。
由此得到当MySQL的Where值类型和数据库定义的字段类型不一致时会发生:
1. 建立的索引实际没有被用上,导致查询效率低下。
2. 由于数据库对一些值的默认转化,导致查询的结果偏离实际的期望。
分享到:
相关推荐
列设置为AUTO_INCREMENT时,如果在表中达到最大值,会发生什么情况?** 当`AUTO_INCREMENT`列达到最大值时,插入新的行将会失败,并提示错误。可以考虑增加列的宽度或将数据迁移到新的表中。 **14. 怎样才能找出...
触发器是一种特殊的存储过程,当特定事件发生时自动执行,如插入、更新或删除操作。其作用包括: - **数据一致性**:确保数据符合预设规则,如检查新插入的数据是否符合业务逻辑。 - **自动日志记录**:自动记录修改...
例如,在更新操作中,为了避免两个事务同时修改同一份数据而导致的数据不一致问题,可以使用锁来锁定正在被修改的数据行,直到事务完成。 #### 问题7:谈谈你对索引的理解 索引是一种特殊的数据结构,用于提高...
当实体类中的属性名和表中的字段名不一样,怎么办?** 可以通过`<resultMap>`标签定义映射关系,或者在`<select>`标签中使用`alias`别名来解决。 **8. 模糊查询like语句该怎么写?** 示例:`SELECT * FROM table ...
- **悲观锁**:假设数据会发生冲突,因此在数据被修改前就进行加锁。 17. **行锁和表锁的区别** - **行锁**:锁定具体的行,减少锁争用,适用于事务处理。 - **表锁**:锁定整个表,可能会导致更多的锁等待。 ...
在使用Mybatis进行数据查询时,有时可能会遇到一个棘手的问题,即查询返回的结果为NULL,而使用`SELECT COUNT(*) FROM xxx`统计行数时却能正常返回数据。这种问题通常会让开发者感到困惑,因为这表明数据库中确实...
如果不一致就会出错,这一章主要在上一讲基于接口编程的基础上完成如下事情: 1. 用 mybatis 查询数据,包括列表 2. 用 mybatis 增加数据 3. 用 mybatis 更新数据. 4. 用 mybatis 删除数据. 查询数据,前面已经讲过...
- **redirect**:服务器端重定向,客户端浏览器地址栏中的URL会发生变化,不能直接传递请求参数。 **11. servlet中doGet()和doPost()的区别。** - **解析:** `doGet()`和`doPost()`方法分别处理HTTP GET和POST...