mysql索引问题
来源:互联网 发布:三角形测试用例java 编辑:程序博客网 时间:2024/05/18 00:14
一,索引的存储分类
索引是在mysql的存储引擎层中实现的,而不是在服务器层实现的。所以每种存储引擎的索引都不一定完全相同,也不是所有的存储引擎都支持所有的索引。mysql目前提供了4种索引
(1)B-Tree索引:最常见的索引类型,大部分引擎支持B树索引。
(2)HASH索引:只有memory引擎支持,使用场景简单。
(3)R-Tree索引(空间索引):空间索引是mysam的一个特殊索引类型,主要用于地理空间数据类型,通常使用较少,不做特别介绍。
(4)Full-text(全文索引):全文索引也是myisam的一个特殊索引类型,主要用于全文索引,innodb目前提供对全文索引的支持。
mysql目前不支持函数索引,但是能对列的前面某一部分进行索引,如:title字段,可以只取前面的10个字符进行索引,这个特性可以大大缩小索引文件的大小,但是前缀索引也有缺点,在排序(order by)和分组(group by)操作的时候无法使用。
下面我们看一下各类存储引擎对索引的支持情况:
比较常用到的索引就是B-Tree索引和HASH索引。HASH相对简单,只有memory引擎支持,其使用于key-value,不适用于范围查询,如< ,> ,<= ,>=这类操作。
扫描性能:
all < index < range < ref < eq_ref < const , system < null
all:全表扫描 ,遍历整个表
index :索引扫描 ,遍历整个索引
range :索引范围扫描,常见于< ,> ,<= ,>= ,between
ref : 使用非唯一索引扫描或唯一索引的前缀扫描,返回匹配某个单独值的记录行
eq_ref : 类似ref,区别在使用的索引是唯一索引,对于每个索引值,表中只有一条记录匹配;就是多表连接使用primary key 或unique index关联条件
const/system : 单表中最多只有一个匹配行
null : 不用访问表或索引,如select 1 from t1 where 1;
二,B-Tree索引
B-Tree索引是最常见的索引,构造类型二叉树,能根据键值提供一行或者一个行集的快速访问,通常只需要很少的读操作就可以找到正确的行。不过,需要注意B-Tree索引中的B不代表二叉树,而是代表平衡树
我们先创建表t17 ,并插入数据
mysql> create table t17(id int auto_increment not null primary key,name varchar(63),age int,intro varchar(63));Query OK, 0 rows affected (0.32 sec)mysql> insert into t17(name,age,intro)values('fzy1',1,'intro1'),('fzy2',2,'intro2'),('fzy3',3,'intro3');Query OK, 3 rows affected (0.05 sec)Records: 3 Duplicates: 0 Warnings: 0mysql> select * from t17;+----+------+------+--------+| id | name | age | intro |+----+------+------+--------+| 1 | fzy1 | 1 | intro1 || 2 | fzy2 | 2 | intro2 || 3 | fzy3 | 3 | intro3 |+----+------+------+--------+3 rows in set (0.00 sec)
1,匹配全值,对索引中所有列都指定具体值,即对索引中的所有列都有等值匹配的条件(关于各explain下各值的参考我们可以看一下:http://blog.csdn.net/fuzhongyu2/article/details/52741253)
例子:
##创建全值索引mysql> create index idx_full on t17(name,age,intro);Query OK, 0 rows affected (0.29 sec)Records: 0 Duplicates: 0 Warnings: 0##执行语句mysql> desc select * from t17 where name='fzy1' and age=1 and intro='intro1' \G; *************************** 1. row *************************** id: 1 select_type: SIMPLE table: t17 partitions: NULL type: refpossible_keys: idx_full key: idx_full key_len: 137 ref: const,const,const rows: 1 filtered: 100.00 Extra: Using index1 row in set, 1 warning (0.01 sec)ERROR: No query specified
由上,我们可以看到查询的时候使用了索引idx_full进行优化扫描
2,匹配值的范围查询,对索引的值能够进行范围查找。
<pre name="code" class="java">##删除全值索引mysql> drop index idx_full on t17;Query OK, 0 rows affected (0.17 sec)Records: 0 Duplicates: 0 Warnings: 0##对age字段建立索引mysql> create index idx_age on t17(age);Query OK, 0 rows affected (0.33 sec)Records: 0 Duplicates: 0 Warnings: 0##查询mysql> desc select * from t17 where age>1 and age<3 \G;*************************** 1. row *************************** id: 1 select_type: SIMPLE table: t17 partitions: NULL type: rangepossible_keys: idx_age key: idx_age key_len: 5 ref: NULL rows: 1 filtered: 100.00 Extra: Using index condition1 row in set, 1 warning (0.00 sec)ERROR: No query specified
3,匹配最左端前缀,仅仅使用索引中的最左端进行查询
就我们在上面讲述到的全值索引为例,我们能够用name,age组合,name,intro组合,name,age,intro组合,name组合使用复合索引,但不能用age组合,intro组合,age,into组合,换句话说不是name开头的就不会利用到复合索引,这个就是最左匹配原则。
mysql> drop index idx_age on t17;Query OK, 0 rows affected (0.18 sec)Records: 0 Duplicates: 0 Warnings: 0mysql> create index idx_full on t17(name,age,intro);Query OK, 0 rows affected (0.31 sec)Records: 0 Duplicates: 0 Warnings: 0mysql> desc select * from t17 where name='fzy1' and intro='intro1' \G;*************************** 1. row *************************** id: 1 select_type: SIMPLE table: t17 partitions: NULL type: refpossible_keys: idx_full key: idx_full key_len: 66 ref: const rows: 1 filtered: 33.33 Extra: Using where; Using index1 row in set, 1 warning (0.00 sec)ERROR: No query specifiedmysql> desc select * from t17 where age='1' and intro='intro1' \G;*************************** 1. row *************************** id: 1 select_type: SIMPLE table: t17 partitions: NULL type: indexpossible_keys: NULL key: idx_full key_len: 137 ref: NULL rows: 3 filtered: 33.33 Extra: Using where; Using index1 row in set, 1 warning (0.00 sec)ERROR: No query specified
从上面我们看到了possible_keys的的差别,就是我们所说的最左原则。但实际上这是一个覆盖索引,最终执行的时候还是用到了idx_full这个索引。
4,仅仅对索引进行查询,当查询的列都在索引的字段中时,查询的效率更高。
mysql> desc select age from t17 where age=1 \G;*************************** 1. row *************************** id: 1 select_type: SIMPLE table: t17 partitions: NULL type: refpossible_keys: idx_age key: idx_age key_len: 5 ref: const rows: 1 filtered: 100.00 Extra: Using index1 row in set, 1 warning (0.00 sec)ERROR: No query specified
5,匹配列前缀,仅仅使用索引中的第一列,并且只包含索引第一列的开头一部分进行查找。
mysql> create index idx_name on t17(name(2));Query OK, 0 rows affected (0.35 sec)Records: 0 Duplicates: 0 Warnings: 0mysql> insert into t17(name,age,intro)values('hsq',4,'intro4');Query OK, 1 row affected (0.04 sec)mysql> desc select * from t17 where name like'hs%' \G;*************************** 1. row *************************** id: 1 select_type: SIMPLE table: t17 partitions: NULL type: rangepossible_keys: idx_name key: idx_name key_len: 5 ref: NULL rows: 1 filtered: 100.00 Extra: Using where1 row in set, 1 warning (0.00 sec)ERROR: No query specified
6,能够实现索引匹配部分精确而其他部分进行范围匹配
mysql> desc select * from t17 where name like 'hs%' and id>2 \G;*************************** 1. row *************************** id: 1 select_type: SIMPLE table: t17 partitions: NULL type: rangepossible_keys: PRIMARY,idx_name key: idx_name key_len: 9 ref: NULL rows: 1 filtered: 100.00 Extra: Using index condition; Using where1 row in set, 1 warning (0.00 sec)ERROR: No query specified
7,如果列名是索引,则使用name is null 就会使用索引
mysql> desc select name from t17 where name is null \G;*************************** 1. row *************************** id: 1 select_type: SIMPLE table: t17 partitions: NULL type: refpossible_keys: idx_name key: idx_name key_len: 5 ref: const rows: 1 filtered: 100.00 Extra: Using where1 row in set, 1 warning (0.00 sec)ERROR: No query specified
二,存在索引但不能被使用的典型场景
1,以%开头的like查询不能够利用B-Tree索引
mysql> desc select * from t17 where name like '%hs' \G;*************************** 1. row *************************** id: 1 select_type: SIMPLE table: t17 partitions: NULL type: ALLpossible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 4 filtered: 25.00 Extra: Using where1 row in set, 1 warning (0.00 sec)ERROR: No query specified
2,数据类型出现隐式转换的时候也不会使用索引,特别是当列类型是字符串,那么一定记得在where条件中把字符串常量用引号引起来,否则即便这个列上有索引,mysql也不会用到的,因为mysql默认把输入的常量值进行转换后才进行检索。
3,复合索引情况下,假如查询条件不包含索引列最左边部分,即不满足最左原则,是不会使用索引的(个人在实验的时候,并不是这样,上面有例子,如有能解释原因的请务必留言)
4,如果mysql估计使用索引比全表扫描慢,则不使用索引。
5,用or分割开的条件,如果or前的条件中的列有索引,而后面的列中没有索引,那么涉及的索引都不会被用到,innodb存储引擎用了or就不能用索引。
- mysql 建索引问题
- mysql索引问题
- MySQL index索引问题
- mysql索引问题
- mysql索引问题
- Mysql一个索引问题
- mysql索引问题
- MySQL数据库索引问题
- MYSQL---索引问题汇总
- mysql索引问题
- Mysql中的索引问题
- mysql索引优化问题
- Mysql索引问题
- MySQL索引的索引长度问题
- Mysql 索引问题-日期索引使用
- MySQL索引的索引长度问题
- MySQL索引长度问题(转)
- mysql索引的效率问题
- redis
- JZOJ 4810. 道路规划
- 【CodeForces 676】C - Vasya and String
- exit(0)与exit(1)、return区别
- Unity
- mysql索引问题
- 【Spring AOP】学习记录(二)
- javax.servlet.jsp.JspException cannot be resolved to a type
- 优先读者的读者/写者问题的算法设计
- CancelIO的作用:防止为发送的数据丢失
- JZOJ 4810 【NOIP2016提高A组五校联考1】道路规划
- Java的反射基本概念、语法和应用
- 剑指offer_整数中1出现的次数
- Linux嵌入式开发常见面试题