主頁 > 知識(shí)庫(kù) > MySQL索引失效的典型案例

MySQL索引失效的典型案例

熱門標(biāo)簽:400電話辦理服務(wù)價(jià)格最實(shí)惠 呂梁外呼系統(tǒng) 催天下外呼系統(tǒng) 大豐地圖標(biāo)注app 南太平洋地圖標(biāo)注 400電話變更申請(qǐng) 北京金倫外呼系統(tǒng) html地圖標(biāo)注并導(dǎo)航 武漢電銷機(jī)器人電話

典型案例

有兩張表,表結(jié)構(gòu)如下:

CREATE TABLE `student_info` (
  `id` int(11) NOT NULL,
  `name` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

CREATE TABLE `student_score` (
  `id` int(11) NOT NULL,
  `name` varchar(10) DEFAULT NULL,
  `score` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

其中一張是info表,一張是score表,其中score表比info表多了一列score字段。

插入數(shù)據(jù):

mysql> insert into student_info values (1,'zhangsan'),(2,'lisi'),(3,'wangwu'),(4,'zhaoliu');
Query OK, 4 rows affected (0.01 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> insert into student_score values (1,'zhangsan',60),(2,'lisi',70),(3,'wangwu',80),(4,'zhaoliu',90);
Query OK, 4 rows affected (0.01 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> select * from student_info;
+----+----------+
| id | name     |
+----+----------+
|  2 | lisi     |
|  3 | wangwu   |
|  1 | zhangsan |
|  4 | zhaoliu  |
+----+----------+
4 rows in set (0.00 sec)

mysql> select * from student_score ;
+----+----------+-------+
| id | name     | score |
+----+----------+-------+
|  1 | zhangsan |    60 |
|  2 | lisi     |    70 |
|  3 | wangwu   |    80 |
|  4 | zhaoliu  |    90 |
+----+----------+-------+
4 rows in set (0.00 sec)

當(dāng)我們進(jìn)行下面的語句時(shí):

mysql> explain select B.* 
       from 
       student_info A,student_score B 
       where A.name=B.name and A.id=1;
+----+-------------+-------+------------+-------+------------------+---------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys    | key     | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+------------------+---------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | A     | NULL       | const | PRIMARY,idx_name | PRIMARY | 4       | const |    1 |   100.00 | NULL        |
|  1 | SIMPLE      | B     | NULL       | ALL   | NULL             | NULL    | NULL    | NULL  |    4 |   100.00 | Using where |
+----+-------------+-------+------------+-------+------------------+---------+---------+-------+------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)

為什么B.name上有索引,但是執(zhí)行計(jì)劃里面第二個(gè)select表B的時(shí)候,沒有使用索引,而用的全表掃描???

解析:

該SQL會(huì)執(zhí)行三個(gè)步驟:

1、先過濾A.id=1的記錄,使用主鍵索引,只掃描1行LA

2、從LA這一行中找到name的值“zhangsan”,

3、根據(jù)LA.name的值在表B中進(jìn)行查找,找到相同的值z(mì)hangsan,并返回。

其中,第三步可以簡(jiǎn)化為:

select * from student_score  where name=$LA.name

這里,因?yàn)長(zhǎng)A是A表info中的內(nèi)容,而info表的字符集是utf8mb4,而B表score表的字符集是utf8。

所以

在執(zhí)行的時(shí)候相當(dāng)于用一個(gè)utf8類型的左值和一個(gè)utf8mb4的右值進(jìn)行比較,因?yàn)閡tf8mb4完全包含utf8類型(長(zhǎng)字節(jié)包含短字節(jié)),MySQL會(huì)將utf8轉(zhuǎn)換成utf8mb4(不反向轉(zhuǎn)換,主要是為了防止數(shù)據(jù)截?cái)?.

因此,相當(dāng)于執(zhí)行了:

select * from student_score  where CONVERT(name USING utf8mb4)=$LA.name

而我們知道,當(dāng)索引字段一旦使用了隱式類型轉(zhuǎn)換,那么索引就失效了,MySQL優(yōu)化器將會(huì)使用全表掃描的方式來執(zhí)行這個(gè)SQL。

要解決這個(gè)問題,可以有以下兩種方法:

a、修改字符集。

b、修改SQL語句。

給出修改字符集的方法:

mysql> alter table student_score modify name varchar(10)  character set utf8mb4 ;
Query OK, 4 rows affected (0.03 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> explain select B.* from student_info A,student_score B where A.name=B.name and A.id=1;
+----+-------------+-------+------------+-------+------------------+----------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type  | possible_keys    | key      | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+-------+------------------+----------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | A     | NULL       | const | PRIMARY,idx_name | PRIMARY  | 4       | const |    1 |   100.00 | NULL  |
|  1 | SIMPLE      | B     | NULL       | ref   | idx_name         | idx_name | 43      | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+-------+------------------+----------+---------+-------+------+----------+-------+
2 rows in set, 1 warning (0.01 sec)

修改SQL的方法,大家可以自己嘗試。

附:常見索引失效的情況

一、對(duì)列使用函數(shù),該列的索引將不起作用。

二、對(duì)列進(jìn)行運(yùn)算(+,-,*,/,! 等),該列的索引將不起作用。

三、某些情況下的LIKE操作,該列的索引將不起作用。

四、某些情況使用反向操作,該列的索引將不起作用。

五、在WHERE中使用OR時(shí),有一個(gè)列沒有索引,那么其它列的索引將不起作用。

六、隱式轉(zhuǎn)換導(dǎo)致索引失效.這一點(diǎn)應(yīng)當(dāng)引起重視.也是開發(fā)中經(jīng)常會(huì)犯的錯(cuò)誤。

七、使用not in ,not exist等語句時(shí)。

八、當(dāng)變量采用的是times變量,而表的字段采用的是date變量時(shí).或相反情況。

九、當(dāng)B-tree索引 is null不會(huì)失效,使用is not null時(shí),會(huì)失效,位圖索引 is null,is not null 都會(huì)失效。

十、聯(lián)合索引 is not null 只要在建立的索引列(不分先后)都會(huì)失效。

以上就是MySQL索引失效的典型案例的詳細(xì)內(nèi)容,更多關(guān)于MySQL索引失效的資料請(qǐng)關(guān)注腳本之家其它相關(guān)文章!

您可能感興趣的文章:
  • mysql回表致索引失效案例講解
  • 解決mysql模糊查詢索引失效問題的幾種方法
  • mysql索引失效的幾種情況分析
  • MySQL索引失效的幾種情況詳析
  • MySQL索引失效的幾種情況匯總
  • mysql索引失效的五種情況分析
  • Mysql索引會(huì)失效的幾種情況分析
  • mysql索引失效的十大問題小結(jié)

標(biāo)簽:西寧 自貢 徐州 迪慶 無錫 南充 麗水 龍巖

巨人網(wǎng)絡(luò)通訊聲明:本文標(biāo)題《MySQL索引失效的典型案例》,本文關(guān)鍵詞  MySQL,索引,失效,的,典型案例,;如發(fā)現(xiàn)本文內(nèi)容存在版權(quán)問題,煩請(qǐng)?zhí)峁┫嚓P(guān)信息告之我們,我們將及時(shí)溝通與處理。本站內(nèi)容系統(tǒng)采集于網(wǎng)絡(luò),涉及言論、版權(quán)與本站無關(guān)。
  • 相關(guān)文章
  • 下面列出與本文章《MySQL索引失效的典型案例》相關(guān)的同類信息!
  • 本頁收集關(guān)于MySQL索引失效的典型案例的相關(guān)信息資訊供網(wǎng)民參考!
  • 企业400电话

    智能AI客服机器人
    15000

    在线订购

    合计11份范本:公司章程+合伙协议+出资协议+合作协议+股权转让协议+增资扩股协议+股权激励+股东会决议+董事会决议

    推薦文章