mysql upper后还走索引_mysql 索引 使用注意细节

在查询时，如果使用到LIKE关键字，就要注意有没有使用到索引；

没有使用索引前：

mysql> explain select * from employees where first_name='Georgi'\G;

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

id: 1

select_type: SIMPLE

table: employees

type: ALL

possible_keys: NULL

key: NULL

key_len: NULL

ref: NULL

rows: 294382

Extra: Using where

1 row in set (0.00 sec)

添加索引: create index first_index on employees (first_name);

索引后：

mysql> explain select * from employees where first_name='Georgi'\G;

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

id: 1

select_type: SIMPLE

table: employees

type: ref

possible_keys: first_index

key: first_index

key_len: 16

ref: const

rows: 253

Extra: Using where

1 row in set (0.00 sec)

查询中使用LIKE：

mysql> explain select * from employees where first_name like '%Georgi'\G;

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

id: 1

select_type: SIMPLE

table: employees

type: ALL

possible_keys: NULL

key: NULL

key_len: NULL

ref: NULL

rows: 294382

Extra: Using where

1 row in set (0.05 sec)

mysql> explain select * from employees where first_name like '%Georgi%'\G;

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

id: 1

select_type: SIMPLE

table: employees

type: ALL

possible_keys: NULL

key: NULL

key_len: NULL

ref: NULL

rows: 294382

Extra: Using where

1 row in set (0.00 sec)

mysql> explain select * from employees where first_name like 'Georgi%'\G;

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

id: 1

select_type: SIMPLE

table: employees

type: range

possible_keys: first_index

key: first_index

key_len: 16

ref: NULL

rows: 253

Extra: Using where

1 row in set (0.00 sec)

可以发现只有在查询关键字后面的加%才可以用到索引；

还有使用了is not null,<>都不可以用到索引:

mysql> explain select * from employees where first_name is not null\G;

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

id: 1

select_type: SIMPLE

table: employees

type: ALL

possible_keys: first_index

key: NULL

key_len: NULL

ref: NULL

rows: 294382

Extra: Using where

1 row in set (0.00 sec)

mysql> explain select * from employees where first_name <> 'Georgi'\G;

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

id: 1

select_type: SIMPLE

table: employees

type: ALL

possible_keys: first_index

key: NULL

key_len: NULL

ref: NULL

rows: 294382

Extra: Using where

1 row in set (0.00 sec)

如果在索引的列上面用了函数或运算都不能引用到索引的：

mysql> explain select * from employees where UPPER(first_name)='Ult'\G;

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

id: 1

select_type: SIMPLE

table: employees

type: ALL

possible_keys: NULL

key: NULL

key_len: NULL

ref: NULL

rows: 294382

Extra: Using where

1 row in set (0.00 sec)

在使用复合索引的时候也要注意第一列有没有在WHERE中；

正确使用索引的：

mysql> explain select * from employees where first_name='Georgi' and last_name='

Facello'\G;

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

id: 1

select_type: SIMPLE

table: employees

type: ref

possible_keys: name_index

key: name_index

key_len: 34

ref: const,const

rows: 2

Extra: Using where

1 row in set (0.00 sec)

mysql> explain select * from employees where first_name='Georgi'\G;

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

id: 1

select_type: SIMPLE

table: employees

type: ref

possible_keys: name_index

key: name_index

key_len: 16

ref: const

rows: 253

Extra: Using where

1 row in set (0.00 sec)

没有使用到索引的:

mysql> explain select * from employees where last_name='Facello'\G;

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

id: 1

select_type: SIMPLE

table: employees

type: ALL

possible_keys: NULL

key: NULL

key_len: NULL

ref: NULL

rows: 300252

Extra: Using where

1 row in set (0.00 sec)

mysql> explain select * from employees where last_name='Facello' orfirst_name='

Georgi'\G;

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

id: 1

select_type: SIMPLE

table: employees

type: ALL

possible_keys: name_index

key: NULL

key_len: NULL

ref: NULL

rows: 300252

Extra: Using where

1 row in set (0.00 sec)

第二个例子因为用了OR所以WHERE中也只是对first_name单独检索；