? ?隱式轉(zhuǎn)換導(dǎo)致索引失效.這一點(diǎn)應(yīng)當(dāng)引起重視.也是開發(fā)中經(jīng)常會(huì)犯的錯(cuò)誤. 由于表的字段tu_mdn定義為varchar2(20),但在查詢時(shí)把該字段作為number類型以where條件傳給mysql,這樣會(huì)導(dǎo)致索引失效. 錯(cuò)誤的例子：select * from test where tu_mdn=13333333333; 正確的例子：select * from test where tu_mdn='13333333333';

? ?看一下下面的案例，這個(gè)案例是開發(fā)過程中經(jīng)常犯的一個(gè)錯(cuò)誤，這種索引在大表的查詢中是很致命的，直接能把數(shù)據(jù)庫(kù)拖死：

mysql> show create table shipping\G;

*************************** 1. row ***************************

? ? ? Table: shipping

Create Table: CREATE TABLE `shipping` (

?`id` int(11) unsigned NOT NULL AUTO_INCREMENT,

?`shipping_no` int(11) NOT NULL,

?PRIMARY KEY (`id`),

?UNIQUE KEY `shipping_no` (`shipping_no`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1

1 row in set (0.00 sec)

mysql> explain SELECT ?shipping_no FROM `shipping` ?WHERE ?`shipping_no` ?IN (62487941,62653594,62952180,63556576,63684186,99097538006,100433005006,100433006006);

+----+-------------+----------------------+-------+---------------+-------------+---------+------+------+-------------+

| id | select_type | table ? ? ? ? ? ? ? ?| type ?| possible_keys | key ? ? ? ? | key_len | ref ?| rows | Extra ? ? ? |

+----+-------------+----------------------+-------+---------------+-------------+---------+------+------+-------------+

| ?1 | SIMPLE ? ? ?| shipping ? ? ? ? ? ? | range | shipping_no ? | shipping_no | 4 ? ? ? | NULL | ? ?6 | Using where |

+----+-------------+----------------------+-------+---------------+-------------+---------+------+------+-------------+

1 row in set (0.00 sec)

mysql> explain SELECT ?shipping_no ?FROM `shipping` ?WHERE ?(`shipping_no` ?IN ('62487941','62653594','62952180','63556576','63684186','99097538006','100433005006','100433006006'));

+----+-------------+----------------------+------+---------------+------+---------+------+----------+-------------+

| id | select_type | table ? ? ? ? ? ? ? ?| type | possible_keys | key ?| key_len | ref ?| rows ? ? | Extra ? ? ? |

+----+-------------+----------------------+------+---------------+------+---------+------+----------+-------------+

| ?1 | SIMPLE ? ? ?| shipping ? ? ? ? ? ? | ALL ?| shipping_no ? | NULL | NULL ? ?| NULL | 12803696 | Using where |

+----+-------------+----------------------+------+---------------+------+---------+------+----------+-------------+

1 row in set (0.00 sec)

? ?很蛋疼的東西，希望開發(fā)者在開發(fā)的時(shí)候注意字段不要越界，最主要的是不要使用隱形轉(zhuǎn)換，有些是轉(zhuǎn)換不了的，DBA的同行們注意這種隱形轉(zhuǎn)換帶來的危害，一定要給開發(fā)者提供規(guī)范。

總結(jié)

以上是生活随笔為你收集整理的mysql隐式转换造成索引失效的事故总结的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。