MySQL学习笔记(中)

7.存储引擎

MySQL体系结构

存储引擎简介

存储引擎是存储数据、建立索引、更新、查询数据等技术的实现方式。存储引擎是基于表的，而不是基于库的，所以也可被称为表类型。

默认存储引擎：InnoDB

1.建表时指定存储引擎

CREATE TABLE 表名{
    字段1 字段1类型[COMMENT 字段1注释],
    字段2 字段2类型[COMMENT 字段2注释],
    字段3 字段3类型[COMMENT 字段3注释],
    ......
    字段n 字段n类型[COMMENT 字段n注释],
}ENGINE=INNODB [COMMENT 表注释];

2.查看当前数据库支持的引擎

SHOW ENGINE;

存储引擎特点

InnoDB介绍

特点

1.DML操作遵循ACID模型，支持事务。

2.行级锁，提高并行访问性能

3.支持外键约束，保证数据完整性

文件

xxx.idb：xxx代表表名，innoDB的每张表都会对应一个表空间文件，存储该表的表结构(frm，sdi)、数据和索引。

MyISAM

特点

1.不支持事务

2.支持表锁，不支持行锁

3.访问速度快

文件

xxx.sdi 存储表结构信息

xxx.MYD 存储数据

xxx.MYI 存储索引

Memory

特点

1.内存存放

2.hash索引

文件

xxx.sdi

存储引擎选择

8.索引

索引概述

帮助MySQL高效获取数据的数据结构。这些数据结构以某种方式引用(指向)数据，可以在这些数据结构上实现高级查找算法，这种数据结构就是索引。

索引结构

数据库更倾向于使用B+树而不是其他数据结构

1. 磁盘读写效率：数据库通常需要处理大量数据，这些数据无法全部加载到内存中，而需要存储在磁盘上。B+树相较于其他树形结构，具有更低的树高度，从而减少了磁盘I/O操作的次数。因为B+树的非叶子节点不存储数据，只存储索引，这使得每个磁盘块能够容纳更多的索引，进一步减少了磁盘读写的次数。

2. 查询效率稳定：B+树的所有关键字查询路径长度相同，这意味着每个数据的查询效率相当。相较于其他树形结构，B+树提供了更稳定的查询性能。

3. 范围查询效率高：B+树的数据都存储在叶子节点中，且叶子节点之间通过指针相连，形成了一个有序的链表结构。这使得范围查询变得非常高效，只需要找到范围的最小元素，然后沿着叶子节点链表遍历即可。这在数据库中是非常常见的操作。

4. 适合数据插入和删除：B+树的平衡性较好，当插入或删除数据时，只需对局部节点进行调整即可，无需对整个树进行重构。这保证了数据库在频繁进行数据更新时仍能保持较高的性能。

索引分类

按索引的存储形式

聚集索引选取规则

1.如存在主键，主键索引就是聚集索引。

2.如果不存在主键，将使用第一个唯一索引作为聚集索引。

3.如果表没有主键，或没有合适的唯一索引，则InnoDB会自动生成一个rowid作为隐藏的聚集索引。

回表查询：先在二级索引中找到对应主键值，再到聚集索引中拿到对应的行数据，因为数据存在聚集索引中

第一句效率更高，因为第二句需要回表查询

select * from user where id=10;
select * from user where name= 'arm';

索引语法

创建索引

CREATE [UNIQUE|FULLTEXT] INDEX index_name ON table_name (index_col_name,...);

查看索引

SHOW INDEX FROM table_name

删除索引

DROP INDEX index_name ON table_name;

SQL性能分析

SQL执行频率

查看执行频率

SHOW GLOBAL STATUS LIKE 'Com______';

慢查询日志

慢查询日志记录了索引执行时间超过指定参数的所有SQL语句的日志。

# 开启MySQL慢日志查询开关
slow_query_log=1
#设置慢日志的时间为两秒
long_query_time=2

profile详情

show profiles能够在做SQL优化时帮助我们了解时间都耗费到哪里去了。通过have_profiling参数，能够看到当前MySQL是否支持profile操作。

SELECT @@hava_profiling
​
#打开开关
SET profiling=1;

profile详情

#查看数据
show profiles；
​
#查看指定query_id的SQL语句各个阶段的耗时情况
show profile for query query_id;
​
#查看指定语句的CPU使用情况
show profile cpu for query query_id;

explain

explain或desc命令获取MySQL如何执行SELECT语句的信息，包括SELECT语句执行过程中表如何连接和链接的顺序。

#直接在语句前加上desc或explain
EXPLAIN SELECT 字段列表 FROM 表名 WHERE 条件;

重点：type、possible_key、key、key_len

索引使用

验证索引效率

未建立索引时查看效率

最左前缀法则

范围查询

范围查询用>=右侧索引不会失效

索引列运算

不要再索引上进行运算操作，索引将失效

explain select * from tb_user where substring(phone,10,2)= '15';

字符串不加引号

字符串类型不加引号时，索引失效

select *from tb_user where phone = 17778392917;

模糊查询

尾部模糊查询，索引不会失效，前部模糊查询，索引失效

select * from tb_user where profession like '软件%';  #尾部模糊

or连接的条件

用or分开，如果or前面列有索引，后面列没有索引，那么涉及的索引都不会被用到。

explain select * from tb_user where id=10 or age=23; #age没有索引，两边都不会用索引

数据分布影响

如果MySQL评估用索引比全表查询漫，就不用索引

SQL提示

在SQL语句中加入一些人为的提示来达到优化的目的。

覆盖索引

尽量使用覆盖索引（查询使用了索引，需要返回的列，在该索引中已经全部能够找到），减少select *

超出覆盖查询要回表查询

前缀索引

当字段类型为字符串时，有时需要索引很长的字符串，这会让索引变得很大，浪费磁盘IO，影响查询效率。此时可以只将字符串的一部分前缀，建立索引，大大节约索引空间，提高索引效率。

create index idx_xxx on table_name(column(n))

前缀长度

可以根据索引的选择性来决定，而选择性是指不重复的索引值(基数)和数据表的记录总数的比值，索引选择性越高则查询效率越高，唯一索引的选择性是1，这是最好的索引选择性，性能也是最好的。

select count(distinct substring(email,1,10))/count(*) from tb_user;

单列索引和联合索引

索引设计原则

9.SQL优化

插入数据

主键优化

order by优化

extra中的Using filesort和Using index

创建索引时默认是升序排序

全升或全降都会用索引 (Using index)

一升一降，如果和索引顺序不一样，会使用filesort，一样不用

group by 优化

limit优化

MySQL中不支持在in中使用limit

select * from tb_sku where id in(select id from tb_sku order by id limit 900000,10);

可以将limit的结果看成一张表，通过多表联查实现

select * from tb_sku s,(select * from tb_sku where id in(select id from tb_sku order by id limit 900000,10)) a where s.id=a.id;

count优化

update优化

10.视图/存储过程/触发器

视图

检查选项

CASCADED

如果为视图指定了CASCADED选项，那么在操作该视图时会检查该视图的条件，并递归检查该视图依赖的所有视图的条件。

如果依赖的视图没有添加CASCADED选项，则会自动给它们添加，以确保所有视图的检查条件都生效。

LOCAL

如果为视图指定了LOCAL选项，那么会检查该视图的条件，并尝试递归检查该视图依赖的所有视图的条件。

如果依赖的视图没有添加LOCAL选项，则不会检查它们的视图条件，即不会给它们强制添加视图检查选项。

更新及作用

11.存储过程

基本语法

在命令行，识别到分号会认为结束。可以使用delimiter指定结束符号

系统变量

用户定义变量

局部变量

if判断

参数

case

while循环

repeat

loop

cursor

handler

存储函数

能够使用存储函数的地方也能使用存储过程

12.触发器

insert型

update型

delete型