Mysql SQL优化

mysql数据库SQL优化

1.Mysql逻辑分层,及存储引擎

连接层：提供与客户端连接的服务；

服务层：

1. 提供各种用户使用的接口(select.)
2. 提供SQL优化器(Mysql Query Optimizer)

引擎层：提供了各种存储数据的方式(InnoDB、MyISAM)；

存储层：存储数据；

常用执行引擎有:

InnoDB(默认)：事务优先(适合高并发操作，行锁)

MyISAM：性能优先（表锁）

查询数据库中有哪些执行引擎

show engines;

2.指定数据库对象的引擎:

create table tb(
id int(4) auto_ increment
，
name varchar (5),
dept varcahr (5)
，
primary key(id)
) ENGINE=MyISAM AUTO_INCREMENT=1 charset=utf8;
# ENGINE 执行引擎
# AUTO_INCREMENT 自增量
# charset 字符编码

3.SQL解析过程、B树、索引

**SQL优化：**主要就是在优化索引

**索引：**相当于书的目录，在数据库中的关键字是index,index是帮助Mysql高效获取数据的数据结构。索引是数据结构(树：B树(默认)，Hash树…)

索引的弊端：

1. 索引本身很大，可以存放在内存/硬盘(通常为硬盘);

2. 索引不是所有情况均适用：

   a.少量数据的时候不适合用索引

   b.频繁更新的字段不适合用索引

   c.很少使用的字段

3. 索引虽然可以提高查询效率，但会降低增删改

优势：

1. 提高查询效率 (降低IO使用率)

2. 降低CPU使用率（B树索引本身就是一个好排序的结构）

4.索引

• 分类

  • 主键索引：不能重复，id不能为null

  • 单值索引：单例，一个表可以多个单值索引；

  • 唯一索引：不能重复，id可以为null；

  • 复合索引：多个列构成的索引（相当于二级目录），不能跨列（最佳左前缀）

• 创建索引：

• 创建方式一:

  • create 索引类型 索引名 on 表(字段)

  • 单值索引：

    • create index  name_index on tb_student(name)
      

  • 唯一索引:

    • create unique index  name_index on tb_class(name)
      

  • 复合索引:

    • create index dept_name_index on tb(dept,name)
      

• 创建方式二:

  • alter table 表名 add index 索引名(添加到的字段名)

  • 单值索引：

    • alter table tb_class index name_index(name);
      

  • 唯一索引:

    • alter table tb_student unique index student_index(gourp)
      

  • 复合索引:

    • alter table tb_age index age_index(dept,name);
      

• 删除索引

  • drop index 索引名 on 表名;

  • drop index name_index on tb_name;
    

• 查询索引

  • show index from 表名;

5.explain 执行计划详解

使用EXPLAIN关键字可以模拟优化器执行SQL查询语句，从而知道MySQL是如何处理你的SQL语句的。分析你的查询语句或是表结构的性能瓶颈。 通过explain我们可以获得以下信息：

• 表的读取顺序
• 数据读取操作的操作类型
• 哪些索引可以使用
• 哪些索引被实际使用
• 表之间的引用
• 每张表有多少行被优化器查询

查询执行计划：

• explain + sql语句

• explain SELECT * FROM user
  

查询结果：

概要描述：

1. id：选择标识符
   • id值越大越先执行
   • id相同则从上往下执行
2. select_type：表示查询的类型。
   • SIMPLE：简单的select查询，查询中不包含子查询或者union
   • PRIMARY：查询中包含任何复杂的子部分，最外层查询则被标记为primary
   • SUBQUERY：在select 或 where列表中包含了子查询
   • DERIVED：在from列表中包含的子查询被标记为derived（衍生），mysql或递归执行这些子查询，把结果放在零时表里
   • UNION：若第二个select出现在union之后，则被标记为union；若union包含在from子句的子查询中，外层select将被标记为derived
   • UNION RESULT：从union表获取结果的sele
3. table：输出结果集的表
4. partitions：匹配的分区
5. type：表示表的连接类型（也可以叫索引类型）
   • system > const > eq_ref > ref > range > index > ALL
   • system：只有一条数据的系统表或衍生表只有一条数据的主查询；
   • const：仅仅能查到一条数据的SQL，用于primary key或unique索引；
   • eq_ref：唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键 或 唯一索引扫描；
   • ref：非唯一性索引扫描，返回匹配某个单独值的所有行。本质是也是一种索引访问，它返回所有匹配某个单独值的行，然而他可能会找到多个符合条件的行，所以它应该属于查找和扫描的混合体；
   • range：只检索给定范围的行，使用一个索引来选择行。key列显示使用了那个索引。一般就是在where语句中出现了bettween、<、>、in等的查询。这种索引列上的范围扫描比全索引扫描要好。只需要开始于某个点，结束于另一个点，不用扫描全部索引；
   • index：Full Index Scan，index与ALL区别为index类型只遍历索引树。这通常为ALL块，应为索引文件通常比数据文件小。（Index与ALL虽然都是读全表，但index是从索引中读取，而ALL是从硬盘读取）
   • ALL：Full Table Scan，遍历全表以找到匹配的行
6. possible_keys：表示查询时，可能使用的索引
7. key：表示实际使用的索引
8. key_len：索引字段的长度
9. ref：列与索引的比较
10. rows：扫描出的行数(估算的行数)
11. filtered：按表条件过滤的行百分比
12. Extra：执行情况的描述和说明
    • using filesort：性能消耗大；需要额外的一次排序(查询)；
    • using temporary：性能消耗大，一般出现在group by语句中，已经有表了但不使用又开了一张表(临时表)；
    • using index：性能提升，索引覆盖。原因：不读取原文件，只从索引文件中读获取数据，只要使用到的列全部都在索引中，就是索引覆盖；
    • using where：需要回表查询，如果查询列不包含索引将回原表查询；
    • impossible where：where子句永远为false;
    • using join buffer：mysql使用了连接缓存；

6.优化示例

1.单表优化

创建Test表，创建表时添加复合索引

create table test(
	id int PRIMARY KEY comment'编号',
	`name` varchar(30) null comment'姓名',
	`age` VARCHAR(30) null comment'年级',
	`sex` VARCHAR(30) null comment'性别',
	index name_age_sex_index(`name`,age,sex)
)

使用复合索引查询（复合索引查询不能跨列查询，否则会回表查询）

explain select * from test where `name`='' and age = '' and sex=''

查询结果

注意事项：

in查询可能会导致索引失效，所以会回表查询

explain select * from test where `name`='' and age = '' and sex in(1,2,3,7)

结果：

2.双表优化

连接查询通常是小表驱动大表，这样能大大提升系统效率，例如左连接：小表在左，大表在右，这样能最大提升sql效率；

create table student(
	id int(4) PRIMARY KEY auto_increment comment'编号',
	`name` VARCHAR(30) comment'学生名字',
	`age` VARCHAR(30) comment'学生年龄',
	`sex` VARCHAR(30) comment'学生性别',
	 gid int(4) comment'外键连接',
		# 给id添加一个单值索引
	 index id_index(id)
)

insert into student(`name`,`age`,`sex`,gid) value('周高帆','20','男',1);
insert into student(`name`,`age`,`sex`,gid) value('何启立','25','男',2);
insert into student(`name`,`age`,`sex`,gid) value('奥利给','19','男',3);
insert into student(`name`,`age`,`sex`,gid) value('小刘','24','男',4);

# 创建年级表
create table grade(
	gid int(2) PRIMARY KEY auto_increment,
	grade_name VARCHAR(20)
)

insert into grade(grade_name) value('小学');
insert into grade(grade_name) value('初中');
insert into grade(grade_name) value('高中');
insert into grade(grade_name) value('大学');
insert into grade(grade_name) value('博士');

# 解析执行
EXPLAIN select grade.gid from grade  left join student on grade.gid=student.gid

现在来看grade表效率已经提升，再来看看student表，执行了回表查询和sql缓存

#给student表中需要展示的数据添加复合索引
create index studnet_index on student(`name`,age,sex)

# 解析执行
EXPLAIN select student.`name`,student.age,student.sex
from grade  left join student on grade.gid=student.gid 
where student.name = '' and student.age = '' and student.sex ='' 

很明显，添加索引之后效率明细提升，多表优化和单表优化差不多，遵循小表驱动大表以及给需要展示的列添加索引；

3.避免索引失效的一些原则

1. 复合索引，不要跨列或无序使用(最佳左前缀a、b、c)；

2. 复合索引，尽量使用全索引匹配

3. 复合索引，不要在索引列上进行任何操作(计算、函数、类型转换等…)

4. 复合索引不能使用不等于(!= ，<>)或 is null(is not null)，否则自身及右边索引全部失效；

5. SQL优化，是一种概率层面的优化；至于是否实际优化了还需通过explain测试;

6. like尽量以 “常量”开头，不要以%开头，否则索引失效

   select  *  from xx where name like  ’ %x%’ --name索引失效
   

7. 尽量不要使用类型转换(显示、隐式)，否则索引失效；

8. 尽量不要使用 or ，否则索引失效；

7.锁机制(MyISAM表锁)

锁：用来解决因资源共享而造成的并发问题；

分类：

• 操作类型：

  • a. 读锁（共享锁）：对同一个数据，多个读操作可以同时进行，互不干扰；
  • b.写锁 （互斥锁又叫排他锁）：如果当前写操作没有完毕，则无法进行其他的读写；

• 操作范围：

  • a.表锁：一次性对一张表整体加锁，如MyISAM存储引擎使用表锁，开销小，加锁快；无死锁；
  • b.行锁：一次性对一条数据加锁；如InnoDB存储引擎使用行锁，开销大，加锁慢，容易出现死锁，锁的范围小，不易发生锁冲突，并发度高（很小概率发生高并发问题）；
  • c.页锁

• 查看加锁的表：

show open tables

1.读锁的使用

**语法：**lock table 表名 read;

# read为读锁，write为写锁；
lock table tb_name read;

创建一个表进行测试：

create table lock_test(
	id int(4) PRIMARY KEY auto_increment,
	`name` VARCHAR(20) DEFAULT''
)
#添加几条数据
insert into lock_test(`name`) VALUES('666'),('7777')

为lock_test添加读锁：

lock table lock_test read
#查看是否添加成功 0:表示没有加锁，1表示加锁
show open tables

当前会话：

加读锁之后在当前会话只能对加锁的表进行读（查看），不能写（删、改、查）；

其他会话：

能对当前数据库所有表进行读取（查看），写（增、删、改）的话会一直等待直到锁取消；

释放锁，语法：

unlock tables;

2.写锁的使用

**语法：**lock table 表名 write;

lock table tb_name write;

当前会话：

可以对加了写锁的表进行任何写操作(增删改查)；但是不能对其他表（没加写锁的表）进行写操作；

其他会话：

可以对加写锁的表进行增删改查，但前提是：必须等添加写锁的会话释放写锁；

3.MySQL表级锁的锁模式

MyISAM在执行查询语句(SELECT) 前，会自动给涉及的所有表加读锁，在执行更新操作(DML) 前，会自动给涉及的表加写锁。所以对MyISAM表进行操作，会有以下情况:
a、对MyISAM表的读操作(加读锁)，不会阻塞其他进程(会话)对同一表的读请求，
但会阻塞对同一表的写请求。只有当读锁释放后，才会执行其它进程的写操作。
b、对MyISAM表的写操作(加写锁)，会阻塞其他进程(会话)对同一表的读和写操作，
只有当写锁释放后，才会执行其它进程的读写操作。

8.锁机制(InnoDB行锁)

行锁(InnoDB默认采用行锁)：

• 行锁缺点：

  • 比表锁性能损耗大；

• 行锁优点：

  • 并发能力强，效率高；

因此建议，高并发用InnoDB，否则用MyISAM；

为了研究行锁，暂时将自动commit关闭， 以后需要通过手动commit；

关闭自动提交语法：

set autocommit =0
#或
start transaction

1. 如果会话对某条数据a进行DML操作（关闭mysql自动提交），则其他会话必须等待会话结束才能执行DML操作；

2. 表锁通过unlock tables解锁，行锁是通过事务解锁；

3. 不同会话操作不同数据互不干扰，例如会话0操作第三行数据，会话1操作第四行数据；

4. 没有索引行锁转为表锁；

查询可以加锁吗？

通过for update对query语句进行加锁；

select * from 表名 for update