sql语句优化

十二、SQL语句优化

1、SQL语句优化

1、mysql的执行流程

客户端：

​ 发送连接请求，然后发送增删改查sql语句执行操作

服务端：

​ （1）连接层：提供和客户端连接的服务，在tcp协议下，提供多线程的技术，让多个用户登录到mysql中

​ 可使用命令：show processlist; 查看所有登录到mysql的用户进程

​ （2）服务器：提供各种接口（增删改查等操作）分析器组件会解析用户的sql语句；

​ 如果发现sql语句执行效率较低，会提交给优化器组件进行优化，然后执行

​ （查询缓存：把上次搜索过的数据，或者提前存储的数据直接返回，效率加快）

​ （优化器：mysql query optimizer）

​ （3）存储引擎：存储或者提取数据

​ innoDB：支持事务处理，支持行级锁，支持高并发

​ myisam：支持表级锁，不支持高并发

​ （4）日志文件：产生binlog日志（二进制文件）

创建表的时候可以在语句最后面设置引擎和编码集，还可以设置起始的id

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树

树节点的概念：

​ 根节点（最顶级的节点）

​ 分支节点（两种状态，父节点，子节点）

​ 叶子节点（最后一层存储数据的节点）

​ 树的高度（树的层级）

B树【b-tree】理想状态下三级，任何数据最多三次查到，支持百万级别的数据查询，追求树的矮胖结构

B+树【b+tree】：在相邻的叶子节点上，加入双向链表（指针），当前叶子节点不但保存了数据，还保存了上下两个节点的地址【小范围数据中，加快查询数据】

B*树【b*或b++ tree】在相邻的分支节点（包含叶子节点）上，加入双向链表（指针），当前分支节点（包含叶子节点）不但保存了数据，还保存了上下两个节点的地址【大范围数据中，加快查询数据】

注：磁盘块 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）性能优化：

​ 利用索引查询时，可以增快查询速度，但是增删改速度变慢，会改变树状结构

​ 追求尽量让叶子节点存储的数据类型小一点，让高度变矮，让数据页变少

2、索引

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）建表的时候，直接船家女索引index索引名（索引字段）

create table t1()
	id int primary key,
	name char(10),
	index index_name(name)
);

（2）创建表之后，创建索引 create index 索引名on 表名（索引字段）

create table t2(
	id int primary key,
	name char(10)
);
create index index_name on t2(name);

（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、正确使用索引

加索引和不加索引的速度差别巨大，加了索引之后，ibd文件变大

（1）把频繁作为搜索条件的字段作为索引，查询单条数据，如果查询的是一个大范围中的数据，不能命中索引

​ 表达范围的符号：> < >= <= != <> like between and in

select * from s1 where id > 5;  
select * from s1 where id < 5; # 表达一个小范围内的数据可以命中.

（2）选一个区分度较高的作为索引

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

（3）在搜索条件中，不能让索引字段参与计算，不能命中索引

select * from s1 where id = 1000;
select * from s1 where id*3 = 3000; # id = 1000

（4）当条件中含有and，sql语句会通过优化器进行优化

• 如果有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;
多个and条件依然成立

• 如果有or相连，没有优化，所有语句从左到右执行，让索引失去意义

select count(*) from  s1 where id = 1000 or email = "xboyww1000@oldboy";

（5）联合索引：遵循最左原则index（字段1，字段2，…）

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;  # first_name 是字符串
  

• 使用了函数不能命中索引

  select count(*) from s1 where reverse(first_name) = "6王";  # 字符串反转函数reverse()