Home Database Mysql Tutorial MySQL中,为什么索引失效?

MySQL中,为什么索引失效?

Jun 07, 2016 pm 04:42 PM
mysql Why Invalid condition index

1、 如果条件中有or,即使其中有条件带索引也不会使用(这也是为什么尽量少用or的原因); 2、 对于多列索引,不是使用的第一部分,则不会使用索引; 3、 like查询是以%开头; 4、 如果列类型是字符串,那一定要在条件中将数据使用引号引用起来,否则不使用索引

1、如果条件中有or,即使其中有条件带索引也不会使用(这也是为什么尽量少用or的原因);

2、对于多列索引,不是使用的第一部分,则不会使用索引;

3、like查询是以%开头;

4、如果列类型是字符串,那一定要在条件中将数据使用引号引用起来,否则不使用索引;

5、如果mysql估计使用全表扫描要比使用索引快,则不使用索引;

6、对索引列进行进行计算或用函数加以处理时;

7、条件中有not in, not exist;


此外,查看索引的使用情况

show status like ‘Handler_read%’;
大家可以注意:
handler_read_key:这个值越高越好,越高表示使用索引查询到的次数

handler_read_rnd_next:这个值越高,说明查询低效

Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn

Hot Article

Hot Article

Hot Article Tags

Notepad++7.3.1

Notepad++7.3.1

Easy-to-use and free code editor

SublimeText3 Chinese version

SublimeText3 Chinese version

Chinese version, very easy to use

Zend Studio 13.0.1

Zend Studio 13.0.1

Powerful PHP integrated development environment

Dreamweaver CS6

Dreamweaver CS6

Visual web development tools

SublimeText3 Mac version

SublimeText3 Mac version

God-level code editing software (SublimeText3)

How to fine-tune deepseek locally How to fine-tune deepseek locally Feb 19, 2025 pm 05:21 PM

How to fine-tune deepseek locally

deepseek why can't you log in deepseek login portal deepseek why can't you log in deepseek login portal Feb 19, 2025 pm 05:00 PM

deepseek why can't you log in deepseek login portal

Why can't the Bybit exchange link be directly downloaded and installed? Why can't the Bybit exchange link be directly downloaded and installed? Feb 21, 2025 pm 10:57 PM

Why can't the Bybit exchange link be directly downloaded and installed?

Free market software app website Free market software app website Mar 05, 2025 pm 09:03 PM

Free market software app website

Why is Bittensor said to be the 'bitcoin' in the AI ​​track? Why is Bittensor said to be the 'bitcoin' in the AI ​​track? Mar 04, 2025 pm 04:06 PM

Why is Bittensor said to be the 'bitcoin' in the AI ​​track?

gateio exchange app old version gateio exchange app old version download channel gateio exchange app old version gateio exchange app old version download channel Mar 04, 2025 pm 11:36 PM

gateio exchange app old version gateio exchange app old version download channel

Bitcoin: The 'barometer' of global liquidity? Bitcoin: The 'barometer' of global liquidity? Mar 04, 2025 pm 06:39 PM

Bitcoin: The 'barometer' of global liquidity?

Cryptocurrency Investment Guide for 2025! Novice can learn it Cryptocurrency Investment Guide for 2025! Novice can learn it Feb 21, 2025 pm 06:48 PM

Cryptocurrency Investment Guide for 2025! Novice can learn it

See all articles