定义:索引是在数据库表的字段上添加的,是为了提高查询效率存在的一种机制。一张表的一个字段可以添加一个索引,当然,多个字段联合起来也可以添加索引。索引相当于一本书的目录,是为了缩小扫描范围而存在的一种机制。
第一种方式:全表扫描 第二种方式:根据索引检索。 注意:在实际中,汉语字典前面的目录是排序的,按照a b c d e f....排序, 为什么排序呢?因为只有排序了才会有区间查找这一说!(缩小扫描范围其实就是扫描某个区间罢了!) 在mysql数据库当中索引也是需要排序的,并且这个所谓的排序和TreeSet数据结构相同。TreeSet(TreeMap)底层是一个自平衡的二叉树!在mysql当中索引是一个B-Tree数据结构。(binary-tree) 遵循左小又大原则存放。采用中序遍历方式遍历取数据。
假设有一张用户表:t_user id(PK) name 每一行记录在硬盘上都有物理存储编号 ---------------------------------------------------------------------------------- 100 zhangsan 0x1111 120 lisi 0x2222 99 wangwu 0x8888 88 zhaoliu 0x9999 101 jack 0x6666 55 lucy 0x5555 130 tom 0x7777 提醒1:在任何数据库当中主键上都会自动添加索引对象,id字段上自动有索引, 因为id是PK。另外在mysql当中,一个字段上如果有unique约束的话,也会自动 创建索引对象。 提醒2:在任何数据库当中,任何一张表的任何一条记录在硬盘存储上都有 一个硬盘的物理存储编号。 提醒3:在mysql当中,索引是一个单独的对象,不同的存储引擎以不同的形式 存在,在MyISAM存储引擎中,索引存储在一个.MYI文件中。在InnoDB存储引擎中 索引存储在一个逻辑名称叫做tablespace的当中。在MEMORY存储引擎当中索引 被存储在内存当中。不管索引存储在哪里,索引在mysql当中都是一个树的形式 存在。(自平衡二叉树:B-Tree)
什么条件下,我们会考虑给字段添加索引呢? 条件1:数据量庞大(到底有多么庞大算庞大,这个需要测试,因为每一个硬件环境不同) 条件2:该字段经常出现在where的后面,以条件的形式存在,也就是说这个字段总是被扫描。 条件3:该字段很少的DML(insert delete update)操作。(因为DML之后,索引需要重新排序。)
建议不要随意添加索引,因为索引也是需要维护的,太多的话反而会降低系统的性能。
建议通过主键查询,通过unique约束的字段进行查询,效率是比较高的。
创建索引: mysql> create index emp_ename_index on emp(ename); 给emp表的ename字段添加索引,起名:emp_ename_index 删除索引: mysql> drop index emp_ename_index on emp; 将emp表上的emp_ename_index索引对象删除。
mysql> explain select * from emp where ename = 'KING'; #扫描14条记录:说明没有使用索引。type=ALL
id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
---|---|---|---|---|---|---|---|---|---|
1 | SIMPLE | emp | ALL | NULL | NULL | NULL | NULL | 14 | Using where |
mysql> create index emp_ename_index on emp(ename); mysql> explain select * from emp where ename = 'KING';
id | table | select_type | type | possible_keys | key | key_len | ref | rows | Extra |
---|---|---|---|---|---|---|---|---|---|
1 | emp | SIMPLE | ref | emp_ename_index | 同前 | 33 | const | 1 | Using where |
# 本5例中ename添加了索引 失效的第1种情况: select * from emp where ename like '%T'; ename上即使添加了索引,也不会走索引,为什么? 原因是因为模糊匹配当中以“%”开头了! 尽量避免模糊查询的时候以“%”开始。 这是一种优化的手段/策略。 失效的第2种情况: 使用or的时候会失效,如果使用or那么要求or两边的条件字段都要有索引,才会走索引,如果其中一边有一个字段没 有索引,那么另一个字段上的索引也会实现。所以这就是为什么不建议使用or的原因。(union不会失效) mysql> explain select * from emp where ename = 'KING' or job = 'MANAGER'; 失效的第3种情况: 使用复合索引的时候,没有使用左侧的列查找,索引失效 什么是复合索引? 两个字段,或者更多的字段联合起来添加一个索引,叫做复合索引。 create index emp_job_sal_index on emp(job,sal); mysql> explain select * from emp where job = 'MANAGER'; # 有效 mysql> explain select * from emp where sal = 800; # 失效 失效的第4种情况: 在where当中索引列参加了运算,索引失效。 mysql> create index emp_sal_index on emp(sal); explain select * from emp where sal = 800; # 有效 mysql> explain select * from emp where sal+1 = 800; # 失效 失效的第5种情况: 在where当中索引列使用了函数 explain select * from emp where lower(ename) = 'smith'; #单行处理函数
索引是各种数据库进行优化的重要手段。优化的时候优先考虑的因素就是索引。
索引在数据库当中分了很多类? 单一索引:一个字段上添加索引。 复合索引:两个字段或者更多的字段上添加索引。
主键索引:主键上添加索引。 唯一性索引:具有unique约束的字段上添加索引。 .....
注意:唯一性比较弱的字段上添加索引用处不大。