MySQL 高级--优化 —— 复合索引(多列索引、联合索引)的定义、区别、创建和理解

1、多列索引的定义与区别

索引的定义是 快速、高效地查询数据的数据结构。

索引的本质就是 数据结构。 可以理解为 排好序的、快速查找的数据结构

mysql 中,索引有 单列索引,也有 多列索引
(1)单列索引 就是常用的一个列字段的索引,常见的索引。
(2)多列索引 就是含有多个列字段的索引 。

多列索引,也叫 多列组合索引复合索引联合索引

2、创建多列索引的语法

创建:

create  index  indexName  on  tableName (a,b(length),c)  # length 是指定此列创建索引的长度,也可以不指定长度。
# 或者
alert  tableName  add  index  indexName on (a,b,c)

删除:

drop index indexName on tableName ;

查看索引:

show index from tableName ;

4、多列索引的理解

索引可以理解为 排好序的 快速查找的 数据结构

像新华字典的目录,按照a、b、c ... 这样排好序的;并且比如 a 中也是排好序的。

比如查找 中国字,拼音是 zhong

首先是定位到 目录的 z 开头部分,
然后 查询到 zh 开头的部分,
然后 是 zhozhonzhong

比如 索引 ( a , b, c) ,支持的索引有 aa,ba,b,c 3种索引 。

在任意一段a的下面b都是排好序的,任何一段b下面c都是排好序的

在这里插入图片描述

示例:

where a=3  and b=45 and c=5  # 这种三个索引顺序使用中间没有断点,全部发挥作用;
where a=3  and c=5           # 这种情况下,b就是断点,a有效,c无效
where b=3  and c=4           # 这种情况下,a就是断点,在a后面的索引全部无效
where b=45 and c=5 and a=3  # 这个跟第一个一样,全部有效, abc跟书写的顺序无关

4、通过示例加强理解:

前提:在 mytable 表中创建 ( a ,b ,c ) 三个列的复合索引。

注意, ( a ,b ,c ) 索引 和 ( a ,c ,b ) 是不同的索引。

SQL语句:

(1) select * from mytable where a=3 and b=5 and c=4;
# abc 三列都使用索引,而且都有效

(2) select * from mytable where  c=4 and b=6 and a=3;
# mysql没有那么笨,不会因为书写顺序而无法识辨索引。
# where里面的条件顺序在查询之前会被mysql自动优化,效果跟上一句一样。

(3) select * from mytable where a=3 and c=7;
# a 用到索引,sql中没有使用 b列,b列中断,c没有用到索引

(4) select * from mytable where a=3 and b>7 and c=3;
# a 用到索引,b也用到索引,c没有用到。
# 因为 b是范围索引,所以b处断点,复合索引中后序的列即使出现,索引也是无效的。

(5) select * from mytable where b=3 and c=4;
# sql中没有使用a列, 所以b,c 就无法使用到索引

(6) select * from mytable where a>4 and b=7 and c=9;
# a 用到索引, a是范围索引,索引在a处中断, b、c没有使用索引

(7) select * from mytable where a=3 order by b;
# a用到了索引,b在结果排序中也用到了索引的效果。前面说过,a下面任意一段的b是排好序的

(8) select * from mytable where a=3 order by c;
# a 用到了索引,sql中没有使用 b列,索引中断,c处没有使用索引,在 Extra列 可以看到 filesort

(9) select * from mytable where b=3 order by a;
# 此sql中,先b,后a,导致 b=3 索引无效,排序a也索引无效。

6、存储过程 :快速生成 100W 测试数据

快速生成 100W 测试数据。

创建测试表:

create table `ut_user` (
  `id` int(10) not null auto_increment,
  `name` varchar(20) default null comment '姓名',
  `gender` int(11) default '0' comment '0:男,1:女',
  `age` int(11) default null comment '年龄',
  `createtime` datetime default null,
  primary key (`id`)
) engine=innodb auto_increment=1001 default charset=utf8

存储过程:

存储过程中 开启事务的格式:

begin  
	set autocommit=0;    ## 取消自动提交
	start transaction;   ## 开启事务
	 
	要执行的操作
	
	commit;   ## 提交事务
end ;

完整的存储过程SQL:

delimiter $$
drop procedure if exists `addUtUserData`$$
create definer=`root`@`localhost` procedure `addUtUserData`(in num int)
begin
		
	declare v_cnt int(10) default 0 ;	
	set autocommit=0;
	start transaction;
	
	repeat	
		insert into ut_user values
		(null,genLName() ,floor(rand()*2),rand()*50 ,now() ),
		(null,genLName() ,floor(rand()*2),rand()*50 ,now() ),
		(null,genLName() ,floor(rand()*2),rand()*50 ,now() ),
		(null,genLName() ,floor(rand()*2),rand()*50 ,now() ),
		(null,genLName() ,floor(rand()*2),rand()*50 ,now() ),
		(null,genLName() ,floor(rand()*2),rand()*50 ,now() ),
		(null,genLName() ,floor(rand()*2),rand()*50 ,now() ),
		(null,genLName() ,floor(rand()*2),rand()*50 ,now() ),
		(null,genLName() ,floor(rand()*2),rand()*50 ,now() ),
		(null,genLName() ,floor(rand()*2),rand()*50 ,now() );	
			
		set v_cnt = v_cnt+10 ;			
		until v_cnt = num        
	end repeat;
	
    commit; ## 提交
end$$

delimiter ;

调用存储过程:

call  addData(100000);

说明: genLName() 函数,请参考: mysql 生成随机数

7、MySQL 存储引擎

show engines;
在这里插入图片描述

阿里巴巴开源的 AliSql 、AliRedis

存储引擎事务支持的索引结构不支持的索引结构
InnoDB支持事务行锁支持 B-tree、Full-text 等索引不支持 Hash 索引
MyISAM不支持事务表锁支持 B-tree、Full-text 等索引不支持 Hash 索引
Memory不支持事务表锁支持 B-tree、Hash 等索引不支持 Full-text 索引

索引种类

BTree索引、Hash索引、full-text全文索引、R-Tree索引

Btree类型的索引:b+树,层数越多,数据量指数级增长(我们就用它,因为innodb默认支持它)
Hash类型的索引:查询单条快,范围查询慢

8、适合建索引情况:

  1. 主键自动建立唯一索引
  2. 频繁作为查询条件的字段应该创建索引
  3. 与其它表关联的字段,外键关系建立索引
  4. 单键/组合索引的选择问题,(在高并发下,倾向创建组合索引)
  5. 排序的字段
    排序字段若通过索引去访问将大大提高排序速度
  6. 统计、分组的字段

9、不适合建索引情况:

  1. Where条件里用不到的字段不创建索引。
  2. 频繁更新的 字段 不适合建索引 (每次更新记录,还要更新索引)
  3. 经常增删改的表。
    索引可以提高了查询速度,同时却会降低更新表的速度,如对表进行INSERT、UPDATE和DELETE。
    因为更新表时,mysql不仅要保存数据,还要保存一下索引文件,加重了IO负担)
  4. 数据重复且分布平均的表字段。
    因此应该只为最经常查询和最经常排序的数据列建立索引。
    如果某个数据列包含许多重复的内容,为它建立索引就没有太大的实际效果。
    比如一个字段的值只 有0、1 表示男女,完全不需要建索引。
  5. 表记录太少。
©️2020 CSDN 皮肤主题: 精致技术 设计师:CSDN官方博客 返回首页