MySQL中能够使用索引的典型场景
MySQL中能够使用索引的典型场景
(1)匹配全值;
(2)匹配值的范围查询,对索引的值能够进行范围查找;
(3)匹配最左前缀,仅仅使用索引中的最左边列进行查找;
(4)仅仅对索引进行查询,查询的列都在索引的字段中时,查询的效率较高;
(5)匹配列前缀,仅仅使用索引中的第一列,并且只包含索引第一列的开头一部分进行查找;
(6)能够实现索引匹配部分精确而其他部分进行范围查询;
(7)如果列名是索引,那么使用column_name is null 就会使用到索引(区别于oracle)
下面以t_user举例说明
create table t_user (id integer not null auto_increment primary key, name varchar(30), salary decimal(7, 2), bonus decimal(7,2), sex tinyint(1));
alter table t_user add index index_name_salary_bonus(name, salary, bonus);
(1)select * from t_user where name = ‘zhangsan‘;
mysql> explain select * from t_user where name = ‘zhangsan‘ \G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t_user
type: ref
possible_keys: index_name_salary_bonus
key: index_name_salary_bonus
key_len: 93
ref: const
rows: 1
Extra: Using where
1 row in set (0.00 sec)
ERROR:
No query specified
mysql> explain select * from t_user where name like ‘%zhangsan‘ \G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t_user
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 3
Extra: Using where
1 row in set (0.00 sec)
ERROR:
No query specified
(2)select * from t_user where name = ‘zhangsan‘ and salary between 1.2 and 2.4;
mysql> explain select * from t_user where name = ‘zhangsan‘ and salary between 1.2 and 2.4 \G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t_user
type: range
possible_keys: index_name_salary_bonus
key: index_name_salary_bonus
key_len: 98
ref: NULL
rows: 1
Extra: Using where
1 row in set (0.00 sec)
ERROR:
No query specified
(3)select * from t_user where salary between 1.2 and 2.4;
mysql> explain select * from t_user where salary between 1.2 and 2.4 \G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t_user
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 3
Extra: Using where
1 row in set (0.00 sec)
ERROR:
No query specified
(4)select name from t_user where salary between 1.2 and 2.4;
mysql> explain select name from t_user where salary between 1.2 and 2.4 \G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t_employee
type: range
possible_keys: idx_salary_bonus
key: idx_salary_bonus
key_len: 5
ref: NULL
rows: 1
Extra: Using where
1 row in set (0.00 sec)
ERROR:
No query specified
(5)select name from t_user where name like ‘zhang%‘;
mysql> explain select name from t_user where name like ‘zhang%‘ \G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t_employee
type: range
possible_keys: idx_salary_bonus
key: idx_salary_bonus
key_len: 5
ref: NULL
rows: 1
Extra: Using where
1 row in set (0.00 sec)
ERROR:
No query specified
(6)select * from t_user where name = ‘zhangsan‘ and sex like ‘%1%‘ ;
mysql> explain select * from t_user where name = ‘zhangsan‘ and sex like ‘%1%‘ \G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t_employee
type: ref
possible_keys: idx_salary_bonus
key: idx_salary_bonus
key_len: 5
ref: const
rows: 1
Extra: Using where
1 row in set (0.00 sec)
ERROR:
No query specified
(7)select * from t_user where name is null ;
mysql> explain select * from t_user where name is null \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t_employee
type: ref
possible_keys: idx_salary_bonus
key: idx_salary_bonus
key_len: 5
ref: const
rows: 1
Extra: Using where
1 row in set (0.00 sec)
郑重声明:本站内容如果来自互联网及其他传播媒体,其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享,并不代表本站赞同其观点和对其真实性负责,也不构成任何其他建议。