脚本宝典收集整理的这篇文章主要介绍了MySQL优化，脚本宝典觉得挺不错的，现在分享给大家，也给大家做个参考。

MySQL-思维导图

一.索引的概述

1.为什么要使用索引

在海量数据中提升性能需要要看查询的字段是否有索引关系,加索引查询新能快

2.索引是什么

查字典的方式>通过目录快速定位到目标所在的页码

没用索引时:

使用索引

3.索引存放位置

C:PRogramDataMySQLMySQL Server 8.0Datalibrary@002dmanager@002dSystem

• .ibd索引和数据

• .MYD数据

• .MYI索引

InnoDB存储引擎:将索引和数据放在同个文件下

MyISAM存储引擎:将索引和数据放开两个文件存储

3.索引的分类和创建

1. 主键索引

主键自带索引,通过主键查询 表中的记录,性能是非常好的

2. 普通索引

create index 索引名称 on 表名(列名)
create index idx_bid on Book(book_id)

3. 唯一索引

在普通索引基础上,列的值时唯一的

create unique index idx_bname on book(book_name)

4. 联合索引

在一个索引中包含了多个列

create  index idx_bauthor_bpublish on book(book_author,book_publish)

5. 全文索引

搜索引擎是用到.不会用MySql做全文索引,只有MyISAM支持

二.数据结构

1.线性表

顺序表存储结构,链式存储结构,单向链表,双向链表

• 单向链表

• 双向链表

2.栈和队列

顺序栈,链栈,顺序队列,链式队列

• 栈

• 队列

3.串

定义串,动态串

4.数组和广义表

• 广义表:表中可以放具体的元素,也可以放另外一张表,类似多维数组,比多维数组节省空间

5.树

二叉树,平衡二叉树,完全二叉树,红黑树,B树,B+树

• 二叉树

• 满二叉树:都放满了

• 完全二叉树:排序树,先放左边

• 二叉排序树:左边的节点值小于右边的

• 平衡二叉树(AVL树):每棵树的左子树与右子树深度不超过一,每颗子树也是平衡二叉树,查找性能是最好的

转换:

右旋:

左旋:

双向旋转:

练习网站:https://www.cs.usfca.edu/~galles/Visualization/AlgorIThms.htML

• 红黑树:

红黑树是一种含有红黑结点并能自平衡的二叉查找树。它必须满足下面性质：

1. 性质1：每个节点要么是黑色，要么是红色。

2. 性质2：根节点是黑色。

3. 性质3：每个叶子节点（NIL）是黑色。

4. 性质4：每个红色结点的两个子结点一定都是黑色。

5. 性质5：任意一结点到每个叶子结点的路径都包含数量相同的黑结点。

红黑树并不是一个完美平衡二叉查找树，从图1可以看到，根结点P的左子树显然比右子树高，但左子树和右子树的黑结点的层数是相等的，也即任意一个结点到到每个叶子结点的路径都包含数量相同的黑结点(性质5)。所以我们叫红黑树这种平衡为黑色完美平衡。

• B树

一个节点可以存放多个数(节点的度)

如果节点度比较大,,那么存放的数据节点的个数就比较多,那么整棵树的深度就比较浅,整颗树的查询性能就很好

• B+树

• hash表

  查找性能比B+树好,但不支持区间访问

6.图

连通图,有向图

三.InnoDB和MyISAM的区别

MyISAM:非聚集索引

支持全文检索,支持表锁

InnoDB:聚集索引

普通索引

冗余放相同数据会造成空间浪费

推荐主键整型自增,不规律的主键会造成多次自旋影响性能

如果忘记了创建主键,MySQL也会创建临时主键

四.联合索引

存储:一个索引存储了多个列

create  index idx_bauthor_bpublish on book(book_author,book_publish)

最左前缀法则:

创建了联合索引以后,在使用SQL查询时,能否命中索引,就看查询条件是否遵循了

最左前缀法则.就不会全表扫描

联合索引是name和age,查询条件一定要包含name,遵循左前缀,就能走索引

例子:

create  index idx_a_b_c on tb_tmp(a,b,c)
select * From tb_tmp where a=1 and b=2 and c=3   可以
select * from tb_tmp where a=1 and b=2   可以
select * from tb_tmp where b=2 and c=3   不可以
select * from tb_tmp where a=1 and c=3   只命中a的索引,再在所有a中查询
select * from tb_tmp where b=2 and c=3 and a=1   可以  sql会优化

五.SQL优化

即使我们在创建数据库是为表创建了索引,但我们在实际使用的sql语句却没有命中索引 造成了慢查询,会导致整个接口的性能非常差.

sql优化

• 防止慢查询,导致接口性能差.接口性能在1秒以内,甚至在200ms内

• 数据库的名称不规范,表的结构不规范,导致使用时出现了问题,对生产环境造成了影响

基础规范

• 存储引擎使用Innodb

• 表的字符集默认使用utf-8 通用,无乱码风险,汉字3字节,英文1字节

• 必要的时候使用utf8mb4 是utf8的超集,有存储4字节例如表情时使用

• 禁止使用存储过程,视图,触发器,Event

• 禁止在数据库中存储大文件

• 禁止在线上进行数据库压力测试

• 测试开发线上数据库环境必须隔离

命名规范

• 库名,表名,列名必须用小写,采用下划线分隔 tb_book

• 库名,表名,列名必须见名知意,疮毒不超过32字符

• 库备份必修一bak为前缀,以日期为后缀

• 从库必须以-s为后缀

• 备库必须以-ss为后缀

表的设计与规范

• 单实例表的个数在1024个以内

• 单表分表的个数在1024个以内

• 表必须有主键,推荐使用unsigned整数位主键

• 禁止使用外键

• 建议将大字段,访问频率低的字段拆封到单独的表存储

列设计规范

• 根据业务区分使用tinyint/int/Bigint,分别占用1/4/8字节

• 根据业务区分使用char(字段长度固定时)/vArchar

• 根据业务区分datetime/timetamp 前者占5个字节,后者占4哥字节,储存年用year,储存日期用date,储存时间用datetime

• 必须把字段定义成not null,并设置默认值

• 使用int unsigned存储ipv4,不要用char(15)

• 使用VARchar(20)存储手机号,不要用整数

• 使用TINYINT代替ENUM,ENUM增加新值要进行DDL操作

索引规范

• 唯一索引使用uniq_(字段名)来命名
• 非唯一索引使用idx._(字段名)来命名
• 单张表索引数建议控制在5个 如果5个字段还不能极大缩小row范围,八成是设计有问题
• 不建议在频繁更新的字段上建立索引(写多读少)
• 非必要不要进行json查询,如果要进行json查询,被json的字段必须类型相同,并建立索引

SQL规范

• 禁止使用select * 防止表结构改变时,不能使用
• insert必须指定字段,禁止使用insert into T values()
• 隐式类型会是索引失效,导致全表扫描
• 禁止在where条件列使用函数或表达式
• 禁止负向查询以及%的开头模糊查询
• 禁止大表JSON和子查询
• 同一个字段上的or必须改写为IN,IN的值必须少于50个
• 应用程序必须捕获SQL异常

总结:

• 存储引擎使用Innodb
• 创建表一定要创建主键,主键用无符号的整数自增主键
• 如果查询的列是普通列,又想提高查询性能,那么给这一列加上索引
• 一张表中的5个索引列
• 索引的应用场景是读多写少

Explain工具

1.介绍

在SQL语句前添加EXplain关键字,查询当前SQL语句的执行性能

explain select * from employees where name like "customer100%"

2.MySQL内部优化器

explain select * from tb_book where id=1;
show warnings;

执行上面语句时,MySQL内部优化器会吧语句转化成下面的这条SQL,性能更好(直接从索引里拿数据

* select#1 */  select `1` AS `id`,`zhangsan` AS `name` from `db_mysql_pro`.`tb_book` where true

3.Explain中每个列各个细节

#关闭衍生表的合并优化,将显示衍生表
set session optimizer_switch=`derived_merge=off`

@H_512_593@SELECT 查询的标识符. 每个 SELECT 都会自动分配一个唯一的标识符. @H_126_663@

select_type列

• simple 表示简单查询，其中不包含连接查询和子查询。
• Primary 表示主查询，或者是最外面的查询语句。
• derived 在from后面进行的子查询,会产生衍生表
• subquery 在from前进行的子查询
• union 进行的联合查询

来自简单的查询

explain select * from tb_book where id=1;

来自复杂查询

from后会放入衍生表

table列

• 表示该SQL正在访问那张表,也可以看出正在访问的衍生表

type列

可以看出SQL查询性能从大到小排列

null>system>const>eq_ref>range>index>All

• null 查询时使用了聚合函数,直接从索引树里获取数据,不查表

explain select min(id) from tb_book;

• const 进行查询时,使用了主键或者唯一索引值与常量比较

• system 是const的特殊情况,在衍生表里,直接匹配一条记录

• eq_ref 在进行连接查询时,连接的查询条件中使用了本表的主键进行关联

explain select * from tb_book_author left join tb_book on tb_book_author.book_id=tb_book.id;

• ref 进行连接查询时,连接查询的条件中使用了本表的联合索引列

explain select book_id from tb_book left join tb_book_author on tb_book.id=tb_book_author.book_id

• range 索引列上使用了范围查找

 explain select *from tb_author where id>1

• index 在查询表中的所有记录,所有的记录可以直接从索引树上获取

explain select  * from tb_book

id和name时索引列

• ALL 全表扫描

id列 SELECT 查询的标识符. 每个 SELECT 都会自动分配一个唯一的标识符.

在查询结果中,多条sql语句,谁的id大,谁先执行,若果多条sql语句一样大,谁在上面谁执行

possible_keys列

第一次查询可能用到的索引

如果使用索引查找的性能并没有全表扫描好,内部优化器会选择全表扫描

explain select *from employees where name 'like custome%'

key列

内部优化器最终选择是否使用索引来进行查找

key_len列

通过查看这一列的数值,推断出本sql选择了联合索引的那几列

影响索引长度因素：

-- 1. 索引列为字符串类型的情况

1. 列长度：

2. 列是否为空： NULL(+1)，NOT NULL(+0)

3. 字符集： 如 utf8mb4=4,utf8=3,gbk=2,latin1=1

4. 列类型为字符： 如 varchar(+2), char(+0)

计算公式：key_len=(表字符集长度) * 列长度 + 1(null) + 2(变长列)

extra

• using index 使用了索引覆盖
• using where

mysql事务(内存):

• 原子(本次事务不能参杂其他事务,独立,唯一)

• 持久(内存写到硬盘)

• 一致

• 隔离(在内存里保证数据不被别人读取)

隔离

1. 读未提交:---->脏读

2.读已提交:---->解决了脏读 出现不可重复读 oracle默认

3. 可重复读 ---> 解决了脏读 和 不可重复读 mysql默认

读之前读的数据

4. 序列化读:--->锁全表,我读时别不能读

脚本宝典总结

以上是脚本宝典为你收集整理的MySQL优化全部内容，希望文章能够帮你解决MySQL优化所遇到的问题。

如果觉得脚本宝典网站内容还不错，欢迎将脚本宝典推荐好友。

本图文内容来源于网友网络收集整理提供，作为学习参考使用，版权属于原作者。
如您有任何意见或建议可联系处理。小编QQ：384754419，请注明来意。

列名	含义
id
select_tyPE	SELECT 查询的类型.
table	查询的是哪个表
partitions	匹配的分区
type	join 类型
possible_keys	此次查询中可能选用的索引
key	此次查询中确切使用到的索引.
key_len	此次查询中使用到的索引的字节长度
ref	哪个字段或常数与 key 一起被使用
rows	显示此查询一共扫描了多少行. 这个是一个估计值.
filtered	表示此查询条件所过滤的数据的百分比
extra	额外的信息