MySQL索引分析
2020-03-23 本文已影响0人
雨后桥前
一、什么是索引、为什么要使用索引
在关系数据库中，索引是对表中的一列或多列的值进行排序的一种存储结构，它是表中一列或多列值的集合，而且其中包含了对应表中记录的引用指针。索引的作用相当于图书的目录，可以根据目录快速检索到所需内容。
二、索引的类型
- FULLTEXT全文索引：目前只有Myisam，可以在char、varchar、text列上创建全文索引，诞生的原因是可以更好地解决 like %word%类似的
- NORMAL普通索引：使用字段关键字建立索引，主要是为了提高查询效率
- SPATIDX空间索引： 只有Myisam支持并且空间类型的字段不能为空
- UNIQUE唯一索引：字段的数据是唯一的
三、索引的分类
Myisam和Innodb存储引擎：只支持BTREE索引，大多数情况下都是用B+TREE
- 单列索引：一个索引只包含单个列，但一个表中可包含多个单列索引
- 普通索引：纯粹是为了提高查询效率
- 唯一索引：索引列中的值必须为一，但允许位空值
- 主键索引：是一种特殊的唯一索引，不允许为空值
- 组合索引：在表中的多个字段组合上创建的索引，只有在查询条件中使用了最左侧的索引时，索引才会生效
- 全文索引：只有在Myisam存储引擎上才能使用，模糊查询 like %word%
- 空间索引：Myisam存储引擎特有
四、操作索引
-
创建索引
- 创建普通索引
CREATE INDEX index_name ON table_name(col_name);
- 创建唯一索引
CREATE UNIQUE INDEX index_name ON table_name(col_name);
- 创建普通组合索引
CREATE INDEX index_name ON table_name(col_name_1,col_name_2);
- 创建唯一组合索引
CREATE UNIQUE INDEX index_name ON table_name(col_name_1,col_name_2);
-
通过修改表结构创建索引
ALTER TABLE table_name ADD INDEX index_name(col_name);
-
创建表时直接指定索引
CREATE TABLE table_name (
ID INT NOT NULL,col_name VARCHAR (16) NOT NULL,INDEX index_name (col_name)
);
-
删除索引
- 直接删除索引
DROP INDEX index_name ON table_name;
- 修改表结构删除索引
ALTER TABLE table_name DROP INDEX index_name;
五、实例探究
1. 创建员工表employ
CREATE TABLE `employ` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '员工ID号',
`name` varchar(50) NOT NULL DEFAULT '' COMMENT '员工姓名',
`age` int(3) NOT NULL DEFAULT '0' COMMENT '员工年龄',
`salary` decimal(6,2) NOT NULL DEFAULT '0.00' COMMENT '薪水',
`address` varchar(255) NOT NULL DEFAULT '' COMMENT '员工地址',
`tel` varchar(11) NOT NULL DEFAULT '' COMMENT '手机号',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2. 创建存储过程插入100W条数据(进行了近10分钟)
#基本语法结构
drop procedure if exists pro_say;
#1、首先重新定义分隔符；
delimiter $$
#2、创建存储过程
create procedure pro_say() #定义存储过程
begin
#开始标志
declare name varchar(20); #随机姓名
declare age int(3) default 0; #年龄
declare address varchar(100); #随机地址
declare salary decimal(5,2) default 0.00; #薪水
declare tel varchar(11) default ''; #年龄
declare i int(11) default 1; #初始值
while i <= 1000000 do #循环插入100W条数据
set name=substring(md5(rand()),1,5); #随机生成姓名
set age=ceil(rand()*100); #年龄
set address=md5(rand()); #地址
set salary=round(rand()*1000,2); #薪水
set tel=concat("1",ceil(rand()*10000000000)); #手机号
insert into employ(`name`,`age`,`salary`,`address`,`tel`) values(name,age,salary,address,tel);
set i=i+1;
end while;
end #结束标志
$$ #结束分隔符
delimiter ; #切换分隔符
#3、调用存储过程
call pro_say; #调用存储过程
3. 主键搜索 TYPE 为 const
select * from employ where id = 99999;
主键搜索 耗时：0.00sec
4. 普通搜索 TYPE 为 ALL
select * from employ where name= '7fa1a';
不使用索引 耗时 0.38 sec
5. 普通索引搜索 TYPE 为 ref 当name的值为整数时不使用索引
create index name_index on employ(name); #创建普通索引
使用普通索引 耗时 0.00 sec
name类型为varcahr 使用整数值进行搜索不使用索引
6. 普通索引 LIKE 搜索 LIKE模糊查询 当%在前可以使用到索引，否则使用不到索引
image.png
7. 组合索引 必须使用到第一个字段索引 否则索引不生效
create index name_tel_age on employ(name,tel,age); #创建组合索引
同时使用name和age 使用到索引 type 为 ref
只使用age字段 索引不生效 TYPE为 ALL
7. 使用OR连接
其中有一个字段没创建索引 整体索引不生效
两个字段都有所引 索引生效
8. 其他情况
1)没有查询条件，或者查询条件没有建立索引
2) 在查询条件上没有使用引导列
3) 查询的数量是大表的大部分，应该是30％以上。
4) 索引本身失效
5) 查询条件使用函数在索引列上，或者对索引列进行运算，运算包括(+，-，*，/，! 等) 错误的例子：select * from test where id-1=9; 正确的例子：select * from test where id=10;
6) 对小表查询
7) 提示不使用索引
8) 统计数据不真实
9) CBO计算走索引花费过大的情况。其实也包含了上面的情况，这里指的是表占有的block要比索引小。
10)隐式转换导致索引失效.这一点应当引起重视.也是开发中经常会犯的错误. 由于表的字段tu_mdn定义为varchar2(20),但在查询时把该字段作为number类型以where条件传给Oracle,这样会导致索引失效. 错误的例子：select * from test where tu_mdn=13333333333; 正确的例子：select * from test where tu_mdn=‘13333333333’;
12) 1,<> 2,单独的>,<,(有时会用到，有时不会)
13）like “%_” 百分号在前.
14）表没分析.
15）单独引用复合索引里非第一位置的索引列.
16）字符型字段为数字时在where条件里不添加引号.
17）对索引列进行运算.需要建立函数索引.
18）not in ,not exist.
19）当变量采用的是times变量，而表的字段采用的是date变量时.或相反情况。
20）B-tree索引 is null不会走,is not null会走,位图索引 is null,is not null 都会走
21）联合索引 is not null 只要在建立的索引列（不分先后）都会走, in null时 必须要和建立索引第一列一起使用,当建立索引第一位置条件是is null 时,其他建立索引的列可以是is null（但必须在所有列 都满足is null的时候）,或者=一个值； 当建立索引的第一位置是=一个值时,其他索引列可以是任何情况（包括is null =一个值）,以上两种情况索引都会走。其他情况不会走。
