MySQL索引性能优化

DarzenWong

已于 2022-01-24 16:26:41 修改

阅读量2k

点赞数 1

文章标签： mysql 性能优化数据库

于 2022-01-24 16:25:34 首次发布

本文链接：https://blog.csdn.net/DarzenWong/article/details/122668926

版权

01、MySQL：性能优化方案

在应用开发的过程中，由于前期数据量少，开发人员编写的SQL语句或者数据库整体解决方案都更重视在功能上的实现，但是当应用系统正式上线后，随着生成数据量的急剧增长，很多SQL语句和数据库整体方案开始逐渐显露出了性能问题，对生产的影响也越来越大，此时MySQL数据库的性能问题成为系统应用的瓶颈，因此需要进行MySQL数据库的性能优化。

1.1、性能下降的表现

执行时间长（MySQL处理sql语句的时间）
等待时间长（网络可能会存在一定的延迟，带宽）

1.2、性能下降的原因

未建立合适的索引
数据量越来越大sql变慢，sql语句不合理引造成索引失效等
服务器调优及配置参数不合理
系统架构的问题

1.3、常见优化方案【重点】

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-W2FHFnDI-1643010387333)(assets/1592233924746.png)]

索引优化: 添加适当索引(index）
Sql优化: 写出高质量的sql，避免索引失效
设计优化: 表的设计合理化(符合3NF，有时候要进行反三范式操作)
配置优化: 对mysql配置优化 [配置最大并发数my.ini, 调整缓存大小 ]
硬件优化: 服务器的硬件优化
架构优化：读写分离、分库分表、负载均衡

02、MySQL：索引概念

2.2、索引

索引就是一种数据结构；可以大大提高MySQL的查询效率。

在MySQL中它是一种B+Tree(二叉树)，索引是Mysql高效获取数据的数据结构，类似新华字典的索引目录，可以通过索引目录快速查到你想要的字，实现快速查找数据。

索引是解决SQL性能问题的重要手段之一，使用索引可以帮助用户解决大多数的SQL性能问题。

2.3、原理

执行sql的过程
- 去硬盘文件中搜索‘第一条数据：发起一次磁盘IO。
- 如果条件不满足就会不停的发起磁盘IO。
- 怎么解决这个问题呢？没错就是优化表，对表的数据进行重新编排和建立目录映射。其实就是优化数据的存储结构，就是建立索引。
小结
- 索引是一种提高数据库获取数据效率的数据结构。
- 建立索引的目的: 减少磁盘IO的次数，提升查询效率。

03、MySQL：索引底层实现

不管是B-tree 还是 B+tree 都是通过最原始的数据的结构 二叉树 演变而来。

3.1 二叉树

参考网站: https://www.cs.usfca.edu/~galles/visualization/BST.html

在这里插入图片描述

为了加快数据的查找,可以维护二叉查找树, 每个节点分别包含索引键值和一个指向对应数据记录的物理地址的指针,这样就可以运用二叉查找在一定的复杂度内获取相应的数据,从而快速的检索出符合条件的记录

左边节点的值小于根节点的值
右边节点的值大于根节点的值

从二叉树的查找过程了来看，最坏的情况下磁盘IO的次数由树的高度来决定。从前面分析情况来看，减少磁盘IO的次数就必须要压缩树的高度，让瘦高的树尽量变成矮胖的树，所以B-Tree强势登场。··

缺点说明

二叉查找树可以任意构造，但是可能有些构造情况可能导致查找效率低。如果想让二叉树查询效率尽可能的高，需要二叉树是平衡的，所以有AVL平衡二叉树。

二叉树是一种偏向树，会造成层级过深，给查询其实并没用提高性能。
二叉树也只能存两个节点，层级越来越大越来越深，发生磁盘的IO会越频繁。

3.2 B-Tree

参考网站: https://www.cs.usfca.edu/~galles/visualization/BTree.html

B-tree树即B树，B即Balanced(平衡的意思)，B-Tree又称为多路平衡查找树。B-Tree是为磁盘等待外存设备设计的一种平衡查找树。每个节点包含key和data。

B树是一种多路平衡搜索树，它类似普通的二叉树，但是Btree允许每个节点有更多的子节点。
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-bDPG274z-1643010387340)(assets/7361383-b4226ba0e306bd27.webp)]

完整示意图:

在这里插入图片描述

模拟查找关键字29的过程:

根据根节点找到磁盘块1，读入内存。【磁盘I/O操作第1次】
比较关键字29在区间（17,35），找到磁盘块1的指针P2。
根据P2指针找到磁盘块3，读入内存。【磁盘I/O操作第2次】
比较关键字29在区间（26,30），找到磁盘块3的指针P2。
根据P2指针找到磁盘块8，读入内存。【磁盘I/O操作第3次】
在磁盘块8中的关键字列表中找到关键字29。

分析上面过程，发现需要3次磁盘I/O操作，和3次内存查找操作。由于内存中的关键字是一个有序表结构，可以利用二分法查找提高效率。而3次磁盘I/O操作是影响整个B-Tree查找效率的决定因素。B-Tree相对于二叉树缩减了节点个数，使每次磁盘I/O取到内存的数据都发挥了作用，从而提高了查询效率。

特点说明

**平衡查询树，它对数据会进行自我平衡，**它比二叉树的层级要低，所以查询的性能要比二叉树高很多。
和二叉树一样比父节点大的数据存储在右边，小的存储在左边。
度（degree)节点的数据存储个数。度越深代表存储的数据越密，树的层级和高度就越低。越利于搜索和存储数据。评价一个索引的好坏一定是进入索引的次数越小越快。
节点中数据key从左到右递增排列

缺点说明

Btree数据是存储到每个节点自己，所以每次查询的和维护的时候就会维护索引值又维护了数据，这样会就是造成内存的浪费和性能的消耗。这也是B+TREE优化的地方。
为了提升度的长度，还需要对这种数据结构进行优化，所以它的升华版B+Tree诞生了。

3.3 B+Tree

参考网站: https://www.cs.usfca.edu/~galles/visualization/BPlusTree.html

在B-Tree基础上进行优化，使其更适合实现外存储索引结构。InnoDB就是存储引擎就是用B+Tree。

在B-Tree中每一个节点存储空间有限，如果data数据较大，会导致每个节点key太少，当数据量很大时也会导致B-Tree深度较大，增大查询的磁盘IO次数，影响查询效率。

在B+Tree中，所有数据记录节点都是按照键值大小顺序存放在同一层叶子节点上，而非叶子节点上只存储key值信息，可以大大增大每个节点存储的key值的数量，降低B+Tree的高度。

B+树是Btree的变体，也是一种多路平衡查找树，B+树的示意图为:

在这里插入图片描述

特点说明

非叶子节点不存储data，只存储key，可以增大度
叶子节点不存储指针
顺序访问指针，提高区间访问能力

B+Tree索引的性能分析

一般使用磁盘I/O次数评价索引结构的优劣

预读：磁盘一般会顺序向后读取一定长度的数据(页的整数倍)放入内存
局部性原理：当一个数据被用到时，其附近的数据也通常会马上被使用

B+Tree的度一般会超过100，因此h非常小 (一般为3到5之间)，性能就会非常稳定
B+Tree叶子节点有顺序指针，更容易做范围查询

04、MySQL：存储引擎【重点】

我们可以通过命令查看MySQL数据库有9种数据存储引擎: show engines

在这里插入图片描述

注意: MySQL5.x默认的存储引擎是InnoDB。

4.1 MyISAM：非聚簇索引

非聚簇索引是指: 非聚簇索引就是指B+Tree的叶子节点上的data，并不是数据本身，而是数据存放的地址。主键索引和非主键索引没区别。

在这里插入图片描述

说明：在mysql5.7版本中xxx.frm 表结构文件、xxx.MYD 表数据文件、xxx.MYI 表索引文件。mysql8.0版本略有不同。

4.2 InnoDB ：聚簇索引

所谓聚簇索引，就是指主索引文件和数据文件为同一份文件
在该索引实现方式如果是主键索引：B+Tree的叶子节点上key为主键，data就是数据本身，key为主键；如果是一般索引的话，key为索引键值，data便会指向对应的主键，如下图所示：

在这里插入图片描述

InnoDB在Mysq.5.7版本中没有.MYD和.MYI；表结构在.frm文件中，数据和索引对应于.ibd`文件中。mysql8.0版本略有不同：所有数据都存在.ibd文件中。
InnoDB在实际的开发中一定要建立主键，因为索引都是通过主键索引获取和查找的。在开发过程中如果你使用的是Innodb引擎一定要建立一个主键列，如果你不建立一般在开发中直接提示警告或者错误。

05、MySQL：索引优劣势&注意事项

5.1 索引优势

建立索引，可以大大提高检索的数据的效率，以及减少表的检索行数。
在表连接的连接条件创建索引，可以加速表与表直接的连接。

5.2 索引劣势

创建和维护索引需要时间，索引文件会占用物理空间,除了数据表需要占用物理空间之外,每一个索引还会占用一定的物理空间。
当对表的数据进行INSERT,UPDATE,DELETE 的时候,索引也要动态的维护,这样就会降低数据的维护速度,(建立索引会占用磁盘空间的索引文件。一般情况这个问题不太严重，但如果你在一个大表上创建了多种组合索引，索引文件的会膨胀很快)。

5.3 注意事项【重点】

建立索引后需要注意的问题?

1. 增，删，改都会触发索引重建，并且会产生锁。
2. 删除尽量逻辑删除，不要物理删除，防止索引重建（is_delete=0 未删除  1 删除）
   什么叫逻辑删除: update table where is_delete =0 where id =1
3. 更新尽量不要更新索引的列。

哪些列适合建立索引?
- 不经常变动的列: 主键、电话、手机号码、邮箱、订单编号、UUID、身份证。

哪些列不适合建立索引?
- 经常变动的列: 用户名。
- 数据比例种类太少的列: 年龄、性别、状态。

公式: 用当前表中某列的去重数/表总记录数 == 1(接近1)，那么这种列就特别适合建立索引。

06、MySQL：索引的分类

普通索引: index(id)
主键索引: primary key（不为空且唯一）
唯一索引: unique index（唯一）
联合索引(组合索引，聚合索引):
- primary key(id,name): 联合主键索引
- unique index(id,name): 联合唯一索引
- index(id,name): 联合普通索引
全文索引: fulltext index(note) 用于搜索很长一篇文章的时候，效果比较好。

说明: 最好还是用全文搜索服务Elasticsearch、solr来解决。

07、MySQL：索引的操作

7.1 创建索引

语法

-- 创建主键索引: alter table 表名 add primary key(列名)

-- 创建唯一索引: CREATE UNIQUE INDEX 索引名 ON 表名(列名)

-- 创建普通索引: CREATE INDEX 索引名 ON 表名(列名)

-- 创建全文索引: CREATE FULLTEXT INDEX 索引名 ON 表名(列名)

查询对比

没有使用索引查询

-- 导入 资料 中的所有sql文件到mysql数据库中；

-- 没有使用索引；# 时间: 0.249s
select cus_id from testemployee where cus_id=5

在这里插入图片描述

使用索引查询

-- 为cusid创建一个索引
create index idx_cusid on testemployee(cus_id)

-- 再次查询耗时
select cus_id from testemployee where cus_id=5

在这里插入图片描述

7.2 查看索引

语法

-- SHOW INDEX FROM 表名
show index from testemployee;

在这里插入图片描述

Table: 表的名称。
Non_unique: 如果索引不能包括重复词则为0，如果可以重复则为1。
Key_name: 索引的名称。
Seq_in_index: 索引中的序列号，从1开始。
Column_name: 列名称。
Collation: 列以什么方式存储在索引中。在MySQL中，有值‘A’(升序)或NULL(无分类)。
Cardinality: 索引中唯一值的数量估计值。通过运行[ANALYZE TABLE 表名]可以更新。基数越大，当进行查询时，MySQL使用该索引的机会就越大。
Sub_part: 如果列只是被部分地编入索引，则为被编入索引的字符的数目。如果整列被编入索引，则为NULL。
Packed: 关键字如何被压缩。如果没有被压缩，则为NULL。
Null: 如果列含有NULL，则含有YES。如果没有，则该列含有NO。
Index_type: 索引存储数据结构（BTREE, FULLTEXT, HASH, RTREE）。
Comment: 说明。

7.3 删除索引

语法

-- 删除主键索引(非自增长): 
alter table 表名 drop primary key

-- 删除唯一索引: 
alter table 表名 drop index 索引名
-- 删除普通索引: 
DROP INDEX 索引名 ON 表名

-- 如：
drop index idx_cusid on testemployee

08、MySQL：执行计划Explain【重点】

8.1 作用

通过explain可以知道SQL语句的执行顺序。
通过explain可以知道SQL查询语句是否命中索引。
通过explain可以分析查询语句或表结构的性能瓶颈。

8.2 语法

-- 使用Explain关键字 放到sql语句前
explain select cus_id from testemployee where cus_id > 10

在这里插入图片描述

8.3 列名说明

列名	说明
id	查询标识符，SQL执行的顺序标识符，SQL从大到小的执行。
select_type	显示本行是简单或复杂select。如果查询有任何复杂的子查询，则最外层标记为PRIMARY（DERIVED、UNION、UNION RESUlT）
table	访问哪张表
partitions	在分区数据库中，一个表可以分布在一个或多个数据库分区中
type	访问类型（ALL、index、range、ref、eq_ref、const、system）
possible_keys	显示mysql可能采用哪些索引来优化查询
key	显示mysql决定采用哪个索引来优化查询
key_len	显示索引所使用的字节数
ref	显示查询时所引用到的索引(关联查询)
rows	粗略估算整个查询需要检查的行数(如果值越大，查询速度就越慢)
filtered	过滤行的百分比
Extra	额外信息（用了where条件、用了排序、用了分组）等

8.3.1 id 查询标识符【重点】

id查询标识符，SQL执行的顺序的标识，SQL从大到小的执行。

id值相同，由上到下执行。

-- id值相同
EXPLAIN SELECT * from employee a,department b,customer c where a.dep_id = b.id and a.cus_id = c.id;

在这里插入图片描述

id值不同，由大到小执行。

-- id值不同	
EXPLAIN SELECT * from department WHERE id = (SELECT id from employee WHERE id=(SELECT id from customer WHERE id = 1))

在这里插入图片描述

id值相同不同，不同的由大到小执行，相同的由上到下执行。

-- id值相同 不同都存在 deriverd 衍生出来的虚表
EXPLAIN select * from department d, (select dep_id from employee group by dep_id) t where d.id = t.dep_id;

在这里插入图片描述

8.3.2 select_type 查询类型

查询类型: 主要用来区分是普通查询、联合查询、子查询等。

查询类型	说明
SIMPLE	简单查询，查询中不包含子查询或者union
PRIMARY	查询中如果包含子查询或union,最外层查询则被标记为primary
SUBQUERY	查询时包含了子查询
DERIVED	from列表中包含了子查询就会标记为derived(派生表)，查询结果放在临时表中
UNION	查询时包含了union查询
UNION RESULT	UNION合并的结果集

-- union联合查询
EXPLAIN select * from employee e LEFT JOIN department d on e.dep_id = d.id
UNION 
select * from employee e RIGHT JOIN department D ON e.dep_id = D.id

在这里插入图片描述

union: 取并集，过滤重复。

union all: 取并集，不过滤重复。

8.3.3 table 表

这个查询是访问哪张表

8.3.4 type 访问类型【重点】

衡量SQL语句性能好坏的参考指标，以消除ALL为己任，如果出现ALL代表没有命中索引，是全表查询。
在这里插入图片描述

system: 主键、唯一索引扫描(表中只有一行数据)（5.7版本和8.0版本会有区别）
```
EXPLAIN SELECT HOST FROM mysql.proxies_priv WHERE HOST = 'localhost'
```

在这里插入图片描述

const: 命中主键(primary key)或者唯一(unique)索引，被连接的部分是一个常量(const)值；

– 表示通过索引一次就找到了，const用于primary key 或者 unique索引。
EXPLAIN select id from testemployee where id=1000



![在这里插入图片描述](https://img-blog.csdnimg.cn/eb00e5e1b889424aa7370f9afe8a1248.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBARGFyemVuV29uZw==,size_20,color_FFFFFF,t_70,g_se,x_16)

+ **eq_ref**: 主键、唯一索引扫描(关联查询比较时用)

```sql
-- 常见于主键或唯一索引扫描，一对一的情况下会出现比较多
EXPLAIN select * from employee e, department d where e.id = d.id

在这里插入图片描述

ref: 非唯一索引扫描(index)(关联查询比较时用)

-- ref 非唯一性索引扫描,返回匹配某个单独值的所有行,本质上也是一种索引访问,它返回所有匹配某个单独值的行可能会找到多个符合条件的行,所以它应该属于查找和扫描的混合体
create index idx_dept on employee(dep_id)

EXPLAIN select e.id,e.dep_id,d.id from employee e,department d where e.dep_id = d.id

在这里插入图片描述

range: 范围索引扫描(between、in、>=、like)等操作

-- range 只检索给定范围的行,使用一个索引来选择行 一般就是在你的where语句中出现between  < > in等查询,这种范围扫描索引比全表扫描要好,因为它只需要开始于索引的某一点.而结束语另一点,不用扫描全部索引
explain select * from employee where id>2

在这里插入图片描述

index: 全索引扫描，遍历整个索引树

-- index index与All区别为index类型只遍历索引树,通常比All要快,因为索引文件通常比数据文件要小all和index都是读全表,但index是从索引中读取,all是从硬盘当中读取
explain select id from employee

在这里插入图片描述

ALL: 全表扫描，MySQL将遍历全表数据直至找到匹配的行(不走索引)

-- 全表进行扫描，从硬盘当中读取数据，如果出现了All 数据量非常大, 一定要去做优化。
explain select * from employee

在这里插入图片描述

说明: 一般来说，保证查询至少达到range级别，最好能达到ref。

注意: 数据很少的情况下，mysql优化器可能会直接走表查询而不会走索引查询。因为mysql优化器觉得走表或走索引是差不多的。

8.3.5 possible_keys

显示可能应用在这张表中的索引,一个或者多个

8.3.6 key 采用索引【重点】

实际使用的索引,如果为NULL,则没有使用索引，查询中若使用了覆盖索引 ,则该索引仅出现在key列表

覆盖索引查询的字段和建立索引的字段刚好吻合,这种我们称为覆盖索引

explain select * from employee where id = 1

在这里插入图片描述

8.3.7 key_len 索引长度

-- 表示索引所使用的字节数，可以通过该列计算出使用的索引长度

8.3.8 ref 引用索引

显示查询时所引用到的索引。

explain select e.dep_id from employee e, department d, customer c where e.dep_id = d.id and e.cus_id = c.id and e.name = '鲁班'

在这里插入图片描述

8.3.9 rows 检查行数

粗略估算整个查询需要检查的行数(如果值越大，查询速度就越慢)

它体现建立索引以后，优化后的扫描行数，越小速度越快，如果你建立索引，type=ref并且也命中到了，但是rows还很大，那说明该表已经没有建立索引的意义了，已经优化到了极限(索引不是万能的)，你需要通过其它技术手段来优化。

比如: 分库分表、缓存、把数据迁移到es/slor。

create index idx_age on employee(age)

explain select * from employee where age > 20

在这里插入图片描述

8.3.10 filtered

按表条件过滤的行百分比，注意是百分比，不是具体记录数 . 值越大越好，filtered列的值依赖统计信息，并不十分准确

Explain select e.dep_id from employee e,department d where e.dep_id = d.id

在这里插入图片描述

8.3.11 Extra 额外信息【重点】

（5.7版本和8.0版本会有区别）

额外信息（用了where条件、用了排序、用了分组）等

	/* 
	Using filesort (需要优化)
		说明mysql会对数据使用一个外部的索引排序,
		而不是按照表内的索引顺序进行
		Mysql中无法利用索引完成排序操作称为"文件排序"
	*/	
explain select * from employee where dep_id =1 ORDER BY cus_id

在这里插入图片描述

	/* 
	Using temporary (需要优化)
		使用了临时表保存中间结果,Mysql在对查询结果排序时, 使用了临时表,
		常见于排序orderby 和分组查询group by
	*/
explain select cus_id from employee where dep_id in (1,2,3) GROUP BY cus_id

在这里插入图片描述

其它在Extra中出现的内容说明：

	using where
		表明使用了where过滤
	useing index
		表示相应的select中使用了覆盖索引,避免访问了表的数据行, 效率很好
	using index & using where
		查找使用了索引，但是需要的数据都在索引列中能找到，所以不需要回表查询数据
	using join buffer
		使用了连接缓存, 比较少见
	using index condition
		查找使用了索引，但是需要回表查询数据

09、MySQL：优化-避免索引失效【重点】

9.1 全部匹配

-- 删除其它索引，避免干扰组合索引测试
drop index idx_dept on employee
drop index idx_age on employee
-- 创建组合索引
create index idx_name_dep_id_age on employee(name,dep_id, age)

-- 索引列全部用上(命中索引)
explain select * from employee where name = '鲁班' and dep_id = 1 and age = 10

在这里插入图片描述

9.2 最左匹配原则

-- 去掉name条件(索引失效)
explain select * from employee where dep_id = 1 and age = 10

在这里插入图片描述

-- 去掉dep_id(命中索引)
explain select * from employee where name = '鲁班' and age = 10

在这里插入图片描述

-- where中的条件顺序错乱不会影响最左匹配(命中索引)
explain select * from employee where dep_id = 1 and age = 10 and name = '鲁班'

9.3 索引列使用函数

-- 在name列上加去除空格的函数(索引失效)
explain select * from employee where TRIM(name) = '鲁班' and dep_id = 1 and age = 10

在这里插入图片描述

9.4 范围条件

-- 范围条件(索引有效)
explain select * from employee where  name = '鲁班' and dep_id > 1 and age = 10

在这里插入图片描述

9.5 比较运算符

比较运算符，如 != <>、>、>=索引失效


-- 使用不等于(!=或者<>)索引失效
explain select * from employee where age != 10

在这里插入图片描述

9.6 is not null（mysql8.0索引不会失效）

-- is not null 索引失效
explain select * from employee where name is not NULL

在这里插入图片描述

9.7 like

-- %开头 (索引失效)
explain select * from employee where name like '%鲁'
-- %xx%开头与结尾 (索引失效)
explain select * from employee where name like '%鲁%'

-- x%结尾(命中索引) 推荐写法
explain select * from employee where name like '鲁%'

在这里插入图片描述

9.8 字符串不加引号

-- 字符串不加单引号，索引失效
explain select * from employee where name = 200

-- 正确写法
-- explain select * from employee where name = '200'

在这里插入图片描述

9.9 使用or

-- 使用or；索引失效
explain select * from employee where name = '鲁班' or age > 10

在这里插入图片描述

9.10 覆盖索引

explain select * from employee where name = '鲁班' or age>10

-- 上面情况会触发全表扫描，不过若使用了覆盖索引，则会只扫描索引文件
-- 覆盖索引: 要查询的字段全部是索引字段
explain select name,dep_id,age from employee where name = '鲁班' or age>10

在这里插入图片描述

小结

联合索引，采用最左匹配原则。
索引列，最好不要出现(!=、<>、>、>=、or、is not null、内置函数、数据类型要正确)。
如果采用like查询，%分号放后面的原则。

10、MySQL：排序与分组优化

排序和分组并不会影响where条件的命中索引，但是它影响响应速度，分组内部会引发排序同时产出临时结果集。

10.1 order by排序

-- 如果select * 语句未使用到索引，会出现 filesort 可使用覆盖索引解决 或 主键索引
-- 组合索引不满足最左原则 会出现 filesort
-- 组合索引顺序不一致(order by的后面) 会出现 filesort
-- 当索引出现范围查找时 可能会出现 filesort
-- 排序使用一升一降会造成filesort

-- 没有使用索引排序，服务器需要额外的为数据进行排序的处理
-- 如果select语句未使用到索引，会出现 filesort
-- 解决方案：让select语句命中索引
explain select * from employee  order by name,dep_id,age

在这里插入图片描述


-- 组合索引不满足最左原则（order by 不是 name开头） 会出现 filesort
-- 解决方案：让组合索引满足最左原则（让order by 以 name开头）
explain select name,dep_id,age from employee  order by dep_id,age

在这里插入图片描述


-- 组合索引顺序不一致(order by的后面) 会出现 filesort
-- 解决方案：让组合索引顺序一直（order by的后面）
explain select * from employee where name='鲁班' order by age,dep_id

在这里插入图片描述


-- 当索引出现范围查找时 可能会出现 filesort
-- 解决方案：组合索引包含排序字段且保持顺序
explain select * from employee where name='鲁班' and dep_id>1 order by age

在这里插入图片描述


-- 排序使用一升一降会造成filesort
-- 解决方案：使用同种方式排序
explain select * from employee where name='鲁班' order by dep_id desc,age

在这里插入图片描述

10.2 group by分组

-- 同order by情况类似， 分组可能使用临时表

-- 组合索引不满足最左原则 会出现 temporary
-- 组合索引顺序不一致(group by的后面) 会出现 temporary
-- 当索引出现范围查找时 可能会出现 temporary

注意: order by本身是不会走索引，如果你想要order by走索引，那么必须前面的where一定要有索引，并且遵循最左匹配原则。

11、MySQL：大数据量分页优化

分页是我们经常使用的功能，在数据量少时单纯的使用limit m,n 不会感觉到性能的影响，但我们的数据达到成百上千万时，就会明显查询速度越来越低。

-- 导入了 testemployee.sql文件的话；下面的创建数据的语句都不需要执行。

-- 使用存储过程导入数据
-- 查看是否开启函数功能
show variables like 'log_bin_trust_function_creators';

-- 设置开启函数功能
set global log_bin_trust_function_creators=1;

-- 创建函数用于生成随机字符串
delimiter $$
 create function rand_string(n int) returns varchar(255)
 begin
   declare chars_str varchar(100) default 'qwertyuiopasdfghjklzxcvbnm';
   declare return_str varchar(255) default '';
   declare i int default 0;
   while i<n do
   set return_str=concat(return_str,substring(chars_str,floor(1+rand()*52),1));
   set i=i+1;
   end while;
   return return_str;
 end $$

-- 创建存储过程用于插入数据
delimiter $$
create procedure insert_emp(in start int(10),in max_num int(10))
begin
 declare i int default 0;
 /*把autocommit设置成0*/
 set autocommit= 0;
 repeat
 set i=i+1;
 insert into testemployee(name,dep_id,age,salary,cus_id)
 values(rand_string(6),'2',24,3000,6);
 until i=max_num end repeat;
commit;
end $$

-- 调用存储过程插入数据
call insert_emp(1,1000000);

-- 测试一下大数据量分页查询
-- limit 0,20 时间: 0.003s
select * from testemployee limit 0,20
-- limit 10000,20 时间: 0.017s
select * from testemployee limit 10000,20
-- limit 100000,20 时间: 0.054s
select * from testemployee limit 100000,20
-- limit 500000,20 时间: 0.166s
select * from testemployee limit 500000,20
-- limit 900000,20 时间: 0.324s
select * from testemployee limit 900000,20

11.1 子查询优化

-- 通过explain发现，之前我们没有利用到索引，这次我们利用索引查询出对应的所有ID
-- 在通过关联查询，查询出对应的全部数据，性能有了明显提升(失去了默认的按主键id排序)
-- limit 900000,20 时间:  0.324s -> 时间: 0.175s
explain select * from testemployee u,(select id from testemployee limit 900000,20) t where u.id = t.id

11.2 使用id限定

-- 使用id限定方案，将上一页的ID传递过来，根据id范围进行分页查询
-- 通过程序的设计，持续保留上一页的ID，并且ID保证自增
-- limit 900000,20 时间: 0.324s -> 时间: 0.001s (使用条件有些苛刻 但效率非常高)
explain select * from testemployee where id > 900000 limit 20

12、MySQL：慢查询日志分析

12.1 慢查询日志介绍

MySQL的慢查询日志是MySQL提供的一种日志记录，它用来记录在MySQL中响应时间超过阈值的语句，MySQL 的日志是跟踪MySQL性能瓶颈的最快和最直接的方式了，系统性能出现瓶颈的时候，首先要打开慢查询日志，进行跟踪，尽快的分析和排查出执行效率较慢的SQL ,及时解决避免造成不好的影响。

12.2 开启慢查询日志

-- 查看慢查询日志变量
show variables like '%slow_query_log%'
show variables like '%slow_query_log_file%'
show variables like '%long_query_time%'

-- 开启方式一: 只对当前数据库生效，MySQL重启后，会失效 0=OFF 1=ON ；
set global slow_query_log=1;

-- 开启方式二: 想永久生效，提供配置文件my.ini	
slow_query_log=1
slow_query_log_file=日志文件存储路径
# 设置慢查询的阈值(默认: 10s)
long_query_time=10;

-- 查看慢查询阈值
show variables like 'long_query_time'

-- 设置慢查询阈值为4s；设置之后需要将navicat的连接断开后重连或者重新打开navicat
    set global long_query_time=4;

在这里插入图片描述

-- 使用这种方式测试慢查询
set global slow_query_log=1;
-- 设置慢查询阈值为4s；设置之后需要将navicat的连接断开后重连
set global long_query_time=4;

-- 查询4s
select sleep(4)

show global status like '%slow_queries%';

查看慢日志文件：

在这里插入图片描述

13、MySQL锁

13.1、锁概述

锁是计算机协调多个进程或线程并发访问某一资源的机制（避免争抢）。

在数据库中，除传统的计算资源（如 CPU、RAM、I/O 等）的争用以外，数据也是一种供许多用户共享的资源。如

何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题，锁冲突也是影响数据库并发访问性能的

一个重要因素。从这个角度来说，锁对数据库而言显得尤其重要，也更加复杂。

13.2、锁分类

从对数据操作的粒度分：

表锁：操作时，会锁定整个表。
行锁：操作时，会锁定当前操作行。
页面锁：锁定一张表的多条数据。

从对数据操作的类型分：

读锁（共享锁）：针对同一份数据，多个读操作可以同时进行而不会互相影响。
写锁（排它锁）：当前操作没有完成之前，它会阻断其他写锁和读锁。

从思想层面分：

乐观锁：对数据进行操作时，总认为不会产生并发问题（数据不会被修改）。

实现方式：给数据进行版本号控制。
悲观锁：对数据进行操作时，总认为会产生并发问题（数据会被修改）。

实现方式：给数据加排他锁即可实现。

不同的存储引擎支持不同的锁机制：

在这里插入图片描述

13.3、InnoDB 的行锁模式

InnoDB 实现了以下两种类型的行锁。

共享锁（S）：又称为读锁，简称S锁，共享锁就是多个事务对于同一数据可以共享一把锁，都能访问到数

据，但是只能读不能修改。
排他锁（X）：又称为写锁，简称X锁，排他锁就是不能与其他锁并存，如一个事务获取了一个数据行的排他

锁，其他事务就不能再获取该行的其他锁，包括共享锁和排他锁，但是获取排他锁的事务是可以对数据就行

读取和修改。

对于UPDATE、DELETE和INSERT语句，InnoDB会自动给涉及数据集加排他锁（X)；

对于普通SELECT语句，InnoDB不会加任何锁；

可以通过以下语句显示给记录集加共享锁或排他锁：

共享锁（S）：SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE 
排他锁（X) ：SELECT * FROM table_name WHERE ... FOR UPDATE

14、MySQL：常见面试题总结

问题1: 下面查询语句，索引的使用情况?

-- 建立联合索引(a,b,c)，请说出下列条件的索引使用情况
select * from table where a=4 
 	使用到索引a
select * from table where a=4 and b=6 
	使用到了索引a,b
select * from table where a=4 and c=5 and b=6
	使用到了索引a,b,c
select * from table where b=4 or b=5 
	没使用到索引
select * from table where a=4 and c=6 
	使用到索引a
select * from table where a=4 order by a,b,c
	使用到索引a 不会产生Using FileSort
select * from table where b=5 order by a
	没使用索引 产生Using Filesort
select * from table where b=5 order by c
	没使用索引 产生Using Filesort

问题2: 什么是索引?

数据库索引的本质是: 数据结构 是一种b+tree的数据结构，它有二叉树的特征，同时解决平衡和深度的问题，这种数据结构能够帮助我们快速的获取数据库中的数据。

问题3: 索引的作用?
```
提高数据查询的效率
```

问题4: 索引的分类?

1. 普通索引
2. 主键索引
3. 唯一索引
4. 联合索引（组合索引）
5. 全文索引

问题5: 索引的原理?

索引的实现本质上是为了让数据库能够快速查找数据，而单独维护的数据结构，mysql实现索引主要使用的两种数据结构: hash 和 B+Tree，我们比较常用的MyIsam和innoDB存储引擎都是基于B+Tree的。

B+树:b+tree是(mysql使用最频繁的一个索引数据结构)数据结构以平衡树的形式来组织,因为是树型结构,所以更适合用来处理排序,范围查找等功能.相对hash索引,B+树在查找单条记录的速度虽然比不上hash索引,但是因为更适合排序等操作,所以他更受用户的欢迎.毕竟不可能只对数据库进行单条记录的操作。

问题6: 索引的优点?

1. 可以通过建立唯一索引或者主键索引,保证数据库表中每一行数据的唯一性。
2. 建立索引,可以大大提高检索的数据,以及减少表的检索行数。
3. 建立索引,在表连接条件时,可以加速表与表直接的相连。
4. 建立索引,在分组和排序时,可以减少查询时分组和排序所消耗的时间。
5. 建立索引,在查询中使用索引可以提高性能。

问题7: 索引的缺点?

1. 在创建索引和维护索引时,会耗费时间,随着数据量的增加而增加。
2. 索引文件会占用物理空间,除了数据表需要占用物理空间之外,每一个索引还会占用一定的物理空间。
3. 当对表进行INSERT,UPDATE,DELETE的时候,索引也要维护,这样就会降低数据的维护速度(建立索引会占用磁盘空间的索引文件。一般情况这个问题不太严重，但如果你在一个大表上创建了多种组合索引，索引文件的会膨胀很快)。

问题8: 分析索引使用情况?

explain显示了MySQL如何使用索引来处理select语句以及连接表。可以帮助选择更好的索引和写出更优化的查询语句。简单讲，它的作用就是分析查询性能。explain关键字的使用方法很简单，就是把它放在select查询语句的前面。mysql查看是否使用索引，简单的看type类型就可以。如果它是all，那说明这条查询语句遍历了所有的行，并没有使用到索引。

问题9: 哪些字段适合加索引?

1. 在经常需要搜索的列上添加索引,可以加快搜索的速度。
2. 主键列上可以确保列的唯一性。
3. 在表与表的而连接条件上加上索引,可以加快连接查询的速度
4. 在经常需要排序(order by),分组(group by)和的distinct 列上加索引 可以加快排序查询的时间,

问题10: 哪些字段不适合加索引

1. 查询中很少使用到的列 不应该创建索引,如果建立了索引然而还会降低mysql的性能和增大了空间需求.
2. 很少数据的列也不应该建立索引,比如 一个性别字段 0或者1,在查询中,结果集的数据占了表中数据行的比例比较大,mysql需要扫描的行数很多,增加索引,并不能提高效率
3. 定义为text和image和bit数据类型的列不应该增加索引,
4. 当表的修改(UPDATE,INSERT,DELETE)操作远远大于检索(SELECT)操作时不应该创建索引,这两个操作是互斥的关系。

问题11: 哪些情况会造成索引失效?

1. 如果条件中有or，即使其中有条件带索引也不会使用(这也是为什么尽量少用or的原因)。
2. 索引字段的值不能有null值，有null值会使该列索引失效。
3. 对于多列索引，不是使用的第一部分，则不会使用索引（最左原则）。
4. like查询以%开头。
5. 如果列类型是字符串，那一定要在条件中将数据使用单引号引用起来,否则不使用索引。
6. 在索引的列上使用表达式或者函数会使索引失效。

问题12: 联合索引最左原则?

在mysql建立联合索引时会遵循最左前缀匹配的原则，即最左优先，在检索数据时从联合索引的最左边开始匹配，组合索引的第一个字段必须出现在查询组句中，这个索引才会被用到
如创建组合索引 a,b,c  那么查询条件中只使用  b 和 c是使用不到索引的

问题13: 聚簇索引和非聚簇索引?

1. MyISAM——非聚簇索引
   MyISAM存储引擎采用的是非聚簇索引，非聚簇索引的主索引和辅助索引几乎是一样的，只是主索引不允许重复，不允许空值，他们的叶子结点的key都存储指向键值对应的数据的物理地址。
非聚簇索引的数据表和索引表是分开存储的。

2. InnoDB——聚簇索引
   聚簇索引的主索引的叶子结点存储的是键值对应的数据本身，辅助索引的叶子结点存储的是键值对应的数据的主键键值。因此主键的值长度越小越好，类型越简单越好。
聚簇索引的数据和主键索引存储在一起。

问题14: in和exists区别?

若：第1个表为A表，第2个表为B表

1. 当A表数据多于B表中的数据时，使用in优于exists。
2. 当B表数据多于A表中的数据时，使用exists优于in。
3. 如果两张表数据量差不多，那么它们的执行性能差不多。

问题15: 我有三个表 A,B,C ,现在有一个select * from A,B,C, 你能告诉我？A,B,C三个表在查询的执行顺序是什么?

一定通过explain查询id得值。才能决定。如果排id相同那么至上而下运行。如果id不同，大得先执行，如果id有相同有不同，大限执行，同样的则至上而下运行，根据现在这种情况是属于id相同得情况。

问题16: like查询中哪些会走索引哪些不会走索引？

-- b 建立了一个索引
select * from table where b like '%xxxx%' -- 不会
select * from table where b like 'xxxx%'  -- 会
select * from table where  b like '%xxxx' -- 不会

问题17: MySQL事务隔离级别?

在这里插入图片描述

问题18: MySQL中锁的分类?

1. 按操作分: 读锁(共享锁)、写锁(排它锁)
2. 按粒度分: 表锁、行锁、页锁
3. 思想的层面分: 悲观锁、乐观锁

问题19: mysql中有几种连接查询?

1. 内连接(inner join): 只有两个元素表相匹配的才能在结果集中显示。
	
2. 外连接：
   2.1 左外连接(left join): 左边为驱动表，驱动表的数据全部显示，匹配表的不匹配的不会显示。
   2.2 右外连接(right join): 右边为驱动表，驱动表的数据全部显示，匹配表的不匹配的不会显示。
   2.3 全外连接(full join): 连接的表中不匹配的数据全部会显示出来。

问题20: MySQL如何综合性优化?

1.表的设计优化(选择表合适存储引擎): 
  myisam: 以读和插入操作为主，只有少量的更新和删除，并且对事务的完整性，并发性要求不是很高的。
  Innodb: 事务处理，以及并发条件下要求数据的一致性。除了插入和查询外，包括很多的更新和删除。

2.索引优化: 
  -- 表的主键、外键必须有索引。
  -- 数据量大的表应该有索引。
  -- 经常与其他表进行连接的表，在连接字段上应该建立索引。
  -- 经常出现在Where子句中的字段，特别是大表的字段，应该建立索引。
  -- 索引应该建在选择性高的字段上（sex 性别这种就不适合）。
  -- 索引应该建在小字段上，对于大的文本字段甚至超长字段，不要建索引。
  -- 频繁进行数据操作的表，不要建立太多的索引。
  -- 删除无用的索引，避免对执行计划造成负面影响。

3.sql语句优化: 
  -- SELECT语句务必指明列的名称（避免直接使用select * ）
  -- SQL语句要避免造成索引失效的写法
  -- SQL语句中IN包含的值不应过多
  -- 如果排序字段没有用到索引，就尽量少排序
  -- 如果限制条件中其他列没有索引，尽量少用or
  -- 尽量用union all代替union
  -- 避免在where子句中对字段进行null值判断
  -- 不建议使用%前缀模糊查询
  -- 避免在where子句中对列进行表达式操作
  -- Join优化能用innerjoin 就不用left join right join 小表驱动大表

4.缓存优化:
   为了提高查询速度，我们可以通过不同的方式去缓存我们的结果从而提高响应效率。当我们的数据库打开了Query Cache（简称QC）功能后，数据库在执行SELECT语句时，会将其结果放到QC中，当下一次处理同样的SELECT请求时，数据库就会从QC取得结果，而不需要去数据表中查询。如果缓存命中率非常高的话，有测试表明在极端情况下可以提高效率238%。

5.读写分离:
  如果数据库的使用场景读的操作比较的时候，为了避免写的操作所造成的性能影响 可以采用读写分离的架构，读写分离，解决的是，数据库的写入，影响了查询的效率。读写分离的基本原理是让主数据库处理事务性增、改、删操作（INSERT、UPDATE、DELETE），而从数据库处理SELECT查询操作。 数据库复制被用来把事务性操作导致的变更同步到集群中的从数据库。

6.mysql的分库分表:
  数据量越来越大时，单体数据库无法满足要求，可以考虑分库分表
  两种拆分方案：
     垂直拆分:（分库）业务表太多？将业务细化 不同的小业务专门用一个库来维护
     水平拆分:（分表）单个表存的数据太多，装不下了？ 将该表查分成多个
              分库分表常用中间件: MyCat、Sharding-JDBC

DarzenWong

关注

1
点赞
踩
5

收藏

觉得还不错? 一键收藏
0
评论
MySQL索引性能优化

MySQL索引，性能优化01、MySQL：性能优化方案在应用开发的过程中，由于前期数据量少，开发人员编写的SQL语句或者数据库整体解决方案都更重视在功能上的实现，但是当应用系统正式上线后，随着生成数据量的急剧增长，很多SQL语句和数据库整体方案开始逐渐显露出了性能问题，对生产的影响也越来越大，此时MySQL数据库的性能问题成为系统应用的瓶颈，因此需要进行MySQL数据库的性能优化。1.1、性能下降的表现执行时间长（MySQL处理sql语句的时间）等待时间长（网络可能会存在一定的延迟，带宽）1
复制链接

扫一扫