王振2020-6-23笔记

# ### sql语句的优化

msq是TCP协议的,其中含有多线程
客户端:
	发送连接请求,然后发送增删改查sql语句进行执行
服务端:
	连接层:提供和客户端连接的服务,在TCP协议下提供多线程并发技术,让多个用户登录mysql
	
	服务器:提供了各种接口(增删改查),分析器组件会解析用户的sql语句,如果发现语句执行效率低,
		   会提交给优化器组件进行优化,然后再执行
		   查询缓存:把上次搜索的数据,或者提前存储的数据直接返回,加快效率
		   优化器: mysql query optimizer
	存储引擎:
		   存储或者提取数据
		   innodb: 支持事务处理,支持行锁,支持高并发
		   myisam: 支持表锁,不支持高并发
	日志文件:
		   产生binlog日志 (二进制文件)

# 了解:
create table ceshi_table1(
id int primary key auto_increment,
name varchar(255)
)engine = myisam auto_increment=3 charset=utf8;

# (2) sql 卡顿原因

硬盘读写数据,io延迟高,sql语句性能低,导致sql执行的时间漫长
表中的数据没有任何索引,并且数据量较大,也会造成sql语句查询速度慢

编写:
select ... from .. join on where .. group by ... having .. order by .. limit

解析:
from .. join on  where group by having  select  order by limit .. 

# (3) 索引

# 索引(index)概念:
	是一个树状的数据结构,即(B树结构,分支节点>2)
	相当于字典的目录,功效是加快查询速度
	常用树:B树(banlance-tree),二叉树,红黑树,hash树
	
# 树节点概念:
	根节点(最顶级节点)
	分支节点(父节点,子节点)
	叶子节点(最后一层存储数据的节点)
	树的高度(树的层级,理想情况下三级,任何数据最多需要3次查到,支持百万级别的数据查询,追求树的矮胖结构.)

[b+] : 在相邻的叶子节点上,加入双向链表(指针),当前叶子节点不但保存了数据,还保存了上下两个节点的地址[小范围数据中,加快查询数据]
[b*] : 在相连的分支节点上,加入双向链表(指针),当前叶子节点不但保存了数据,还保存了上下两个节点的地址[大范围数据中,加快查询数据]

(磁盘块 block 数据页 16k)
myisam和innodb 都是b+树结构

# (4) innodb 和 myisam 的索引结构
	(1) 聚集索引[innodb存储引擎的特点,myisam不支持]
		如果有主键,自动以主键创建聚集索引的数据结构(树状结构)
		如果没有主键,选择唯一键
		都没有,自动生成隐藏的聚集索引,也会分出一个字段占用6个字节长整型;
		
		叶子节点上面直接存储真实数据(索引和数据捆绑在一起)
		分支节点存储的是索引的最小值,用来划分范围
		在数据量变大的时候,尽量在树层级高度不变的情况下,横向发展,好处:查询次数少,提升效率,减少io阻塞;
		
	(2) 非聚集索引(辅助索引,二级索引,普通索引)
		先对创建索引的该字段划分区间进行排序,把索引值分布在叶子节点上
		存储的是该字段的值以及对应映射出的主键id(primary key ),没有存真实数据
		通过主键id,再去从其他文件中找数据..
		
	(3) 两者区别
		myisam 和 innodb 使用的索引结构都是b+树,但是叶子节点存储的数据不同
		innodb文件结构中只有frm和ibd 直接把数据存在叶子节点上
		myisam文件结构中有frm,myi,myd,叶子节点上存储的索引值,通过索引找id,通过id找数据.


	(4) 性能优化:
		利用索引查询时,可以增快查询速度,但是增删改速度变慢,会改变树状结构
		追求尽量让叶子节点存储的数据类型小一点,让高度变矮,让数据页变少.
		
<---------------------------------------------------------------------------------------------->
# ### part2 : 索引
# 1.常用索引
	单个字段索引
		-主键索引 primary key : 非空且唯一
		-唯一索引 unique      : 唯一
		-普通索引 index       : 单纯加个索引,为了提升查询效率
	联合索引
		primary key(字段1,字段2..) :联合主键索引
		unique(字段1,字段2..)      :联合唯一索引
		index(字段1,字段2..)       :联合普通索引

# 2.应用场景
编号:int
姓名:varchar(255)
身份证号:char(18)
电话char(11)
地址varchar(255)
备注:text
姓: varchar(10)
名: varchar(10)

编号: 主键
姓名: 普通索引(注意在区分度高的字段上加)
身份证:unique
电话:unique
备注:全文索引 , 借助第三方软件sphinx来运行
姓和名:联合索引 , 联合在一起查,加快速度

# 3.不同的存储引擎支持的数据结构
innodb : 支持b-tree  fulltext 不支持hash类型索引结构
myisam : 支持b-tree  fulltext 不支持hash类型索引结构
memory : 支持b-tree hash类型 不支持fulltext索引

hash类型索引: 数据放内存中,通过键来获取到纸,单条数据查询快,一个范围内的数据慢
b-tree      : 最理想的三层结构,理论上课支撑百万条数据的查询;

# 4.建立索引
# (1) 方法1,建表的时候,直接创建索引 index 索引名(索引字段)
create table t1(
	id int primary key,
	name char(10),
	index index_name(name)
);

# (2) 方法2,建表之后,创建索引 create index 索引名 on 表名(索引字段)
create table t2(
	id int primary key,
	name char(10)
);
create index index_name on t2(name);

# (3) 方法3,改字段变索引  alter table 表名 add index 索引名(索引字段)
create table t3(
	id int primary key,
	name char(10)
);

alter table t3 add index index_name(name);
# (4) 删除索引
drop index index_name on t3;

# 5.正确使用索引
alter table s1 add index index_id(id);
select * from s1 where id = 5;
# 发现加索引和不加索引速度差别巨大,
# 加了索引之后,ibd文件变大

# (1) 把频繁作为搜索条件的字段作为索引,查单条数据,如果查询的是一个大范围中的数据,不能命中索引
# 表达范围的符号: > < >= <= != like between and .. in  
select * from s1 where id > 5;  
select * from s1 where id < 5; # 表达一个小范围内的数据可以命中.  

# (2) 选一个区分度较高的字段作索引
选区分度低的字段做了索引,在查询数据的时候,先走索引建好的树状结构,在把数据搜出来
因为树状结构中有大量的重复数据,会增加树的高度,反而速度不快,冗余数据过多
默认系统会把主键或者unique标识的约束,自动创建索引,因为区分度较高,没有冗余数据;

# 不推荐把区分度不高的字段加索引

# (3) 在搜索条件中,不能让索引字段参与计算,不能命中索引
select * from s1 where id = 1000;
select * from s1 where id*3 = 3000; # id = 1000

# (4) 条件当中含有and , sql语句会通过优化器进行优化
# 1.如果有and 相连,找到第一个有索引的 并且树的高度最矮的字段进行优化
select count(*) from s1 where email = "xboyww1000@oldboy"
select count(*) from s1 where email = "xboyww1000@oldboy" and id = 1000;
select count(*) from s1 where email = "xboyww1000@oldboy" and name =  "xboyww";
select count(*) from s1 where email = "xboyww1000@oldboy" and name =  "xboyww" and id = 1000;

# 2.如果有or相连,没有优化,所有语句从左到右执行,让索引失去意义
select count(*) from  s1 where id = 1000 or email = "xboyww1000@oldboy";

# (5) 联合索引 : 遵循最左前缀原则 index(字段1,字段2, ....)
drop index index_id on s1;
drop index index_name on s1;
create index union_index on s1(first_name,last_name);

select count(*) from s1 where first_name = "王6" and last_name="文6"  # 命中索引
select count(*) from s1 where last_name="文6" and  first_name = "王6" # 命中索引
select count(*) from s1 where last_name="文6" # 不能命中索引
select count(*) from s1 where first_name="王6" and gender="man";
select count(*) from s1 where first_name="王6" and gender="man" and name="xboyww";
# 最左前缀原则:被标记成MUL这个字段,必须存在在搜索条件中,就命中索引
first_name + .... (必须该字段存在) 联合索引会更加精确的命中想要的数据.数据结构更合理;

# (6) 其他
# 数据类型不匹配,不能命中索引
select count(*) from s1 where first_name = 100
# 使用了函数不能命中
select count(*) from s1 where reverse(first_name) = "6王";