Mysql拓展

目录

存储引擎

索引

索引分类

SQL性能分析

索引使用规则

SQL优化

插入数据

主键优化

order by 优化

group by 优化

limit 优化

count 优化

update 优化

视图

锁

---

存储引擎

概念：存储引擎就是存储数据、建立索引、更新／查询数据等技术的实现方式。存储引擎不一样，事务支持、锁定级别，索引类型等存在差异。

存储引擎是基于表的，也就是说每张表都可以设置不同的存储引擎。

InnoDB（默认）：支持事务、行级锁、索引，且每一张表在磁盘中对应一个 .idb 文件。

索引

概念：索引是一种数据结构，能够让我们高效率查询，但是会影响增删改的效率。

InnoDB使用的索引是优化的B+树结构。(二叉树、红黑树、B树、B+树)

为什么要选用优化的 B+树结构作为InnoDB引擎的索引结构？

1. 有序性(高效查询)：B+树是一种平衡树结构，它能够保持索引的有序性。在B+树中，所有叶子节点都按照键值的顺序连接在一起，这使得范围查询非常高效。
2. 高度平衡：B+树是一种自平衡的树结构，确保树的高度相对较低。这意味着即使在大型数据库表中，查找数据的时间复杂度仍然是O(log n)，其中n是索引中的数据项数目。
3. 支持非唯一索引：B+树支持非唯一索引，这允许多个行具有相同的索引键值。
4. 支持聚簇索引：InnoDB存储引擎的B+树还支持聚簇索引，这意味着数据行的物理存储顺序与B+树的索引顺序一致，提供了进一步的性能优势。

索引分类

主键索引：针对表中主键创建的索引，默认自动创建且只能有 1 个。(primary)

唯一索引：规避同一表中某列中数据重复，可以有多个。(unique)

常规索引：快速定位特定数据。

全文索引：全文索引查找的是文本中的关键字，而不是比较索引中的值，可以有多个。(fulltext)

在InnoDB引擎中，根据索引的存储形式，又可以分为两种：

聚集索引：将数据存储与索引放到一起，索引结构的叶子节点保存行数据(叶子节点是最下面的一行)。必须有，且只有 1 个。

二级索引(非聚集索引)：将数据与索引分开存储，索引结构的叶子结点关联的是对应的值。可以有多个。

聚集索引选取规则：

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

如果不存在主键，将使用唯一索引作为聚集索引

如果没有主且没有唯一索引，将自动生成一个rowid作为隐藏的聚集索引。

回表查询

索引语法操作

创建索引
create [unique] index indexName on tableName(列名1，...)


查看索引
show index from tableName;


删除索引
drop index indexName on tableName;

SQL性能分析

SQL执行顺序

FROM子句： 查询从FROM子句中指定的表中获取数据。这是查询开始的地方，它指定了要从哪个表中检索数据。

WHERE子句： WHERE子句用于过滤从表中检索的行。它指定了要满足的条件，只有符合条件的行才会被包含在结果中。

GROUP BY子句： GROUP BY子句用于将结果集按照指定的列进行分组。通常与聚合函数（如SUM、COUNT、AVG等）一起使用，以对每个组应用聚合操作。

HAVING子句： HAVING子句类似于WHERE子句，但它用于过滤组而不是行。它筛选GROUP BY创建的组。

SELECT子句： SELECT子句用于指定要在结果集中包含哪些列。它可以包括列名、表达式、聚合函数等。

ORDER BY子句： ORDER BY子句用于按照指定的列对结果集进行排序。它可以按升序（ASC）或降序（DESC）排序。

LIMIT / OFFSET子句： 这些子句用于限制结果集的数量或指定开始返回结果的位置，通常用于分页。

方法一：

1.查看表状态

语法：show global status like 'Com_______'; 展示当前表执行了多少次增删改查，为SQL优化提供依据。

2.慢查询日志(MySQL默认是关闭的),在etc/my.cnf文件中增加一下配置开启并设置时间为2秒

slow_query_log=1

long_query_time=2

通过 show varizbles like 'slow_query_log'; 查看是否开启慢查询日志

最后，查看慢日志文件中记录的信息 /var/lib/mysql/localhost-slow.log

方法二：

profile关键字查看每一条SQL语句的执行时间

通过have_profiling参数，能够看到当前MySQL是否支持profile操作：
SELECT @@have_profiling ;


可以通过set语句在session/global级别开启profiling
SET profiling = 1;

查看每一条SQL的耗时基本情况
show profiles;
查看指定query_id的SQL语句各个阶段的耗时情况
show profile for query query_id;
查看指定query_id的SQL语句CPU的使用情况
show profile cpu for query query_id;

方法三：

explain执行计划

使用方法：在select 语句前家 explain 关键字

各字段含义：

字段	含义
id	select 查询的序列号，表示查询中执行 select 子句或者是操作表的顺序 (id 相同，执行顺序从上到下； id 不同，值越大，越先执行 ) 。
select_type	表示 SELECT 的类型，常见的取值有 SIMPLE （简单表，即不使用表连接 或者子查询）、 PRIMARY （主查询，即外层的查询）、 UNION （ UNION 中的第二个或者后面的查询语句）、 SUBQUERY （ SELECT/WHERE 之后包含了子查询）等
type	表示连接类型，性能由好到差的连接类型为 NULL 、 system 、 const 、 eq_ref 、 ref 、 range 、 index 、 all 。
possible_key	显示可能应用在这张表上的索引，一个或多个。
key	实际使用的索引，如果为 NULL ，则没有使用索引。
key_len	表示索引中使用的字节数， 该值为索引字段最大可能长度，并非实际使用长 度，在不损失精确性的前提下， 长度越短越好 。
rows	MySQL 认为必须要执行查询的行数，在 innodb 引擎的表中，是一个估计值， 可能并不总是准确的。
filtered	表示返回结果的行数占需读取行数的百分比， filtered 的值越大越好。

索引使用规则

最左前缀原则

在使用联合索引查询时，where的查询条件中必须要有联合索引中最左边的索引作为条件，该条件可以写在where语句的最后面，但是一定要有，否则索引失效。同样，查询语句中必须要有联合索引中的第二个索引作为条件，否则该条件索引和后面索引失效，以此类推。

范围查询原则

 在联合索引中，出现范围查询即 '>' ,' < ' 符号，范围查询右侧索引失效。可以使用 '>=','<=' 规避

索引列运算

在索引列上进行运算操作或者使用函数，索引会失效。

字符串不加引号

where条件中字符串类型不加引号，索引会失效

模糊查询

如果仅仅是尾部模糊查询，索引不失效。如果是头部模糊查询，索引会失效。

or 连接的条件

用 or 连接的条件，如果前面条件有索引，后面条件没有索引，那么涉及到的索引都失效。只有两侧都有索引，索引才会有效。可以添加索引解决。

数据分布影响

如果MySQL评估使用索引比全表扫描更慢(表中绝大部分数据都满足条件)，则不会使用索引。

覆盖索引：查询的字段，通过二级索引找不到信息，会进行回表查询，导致性能降低。

前缀索引：对于值内容过长的数据，用它的开头部分作为索引。

create index indexName on tableName(column(n));

单例索引与联合索引

如果有两个单例索引，且都是查询条件。MySQL在使用索引查询时，会进行评估，选择其中一个较合适的索引查询并不会用全部的索引，会导致回表查询。所以，业务场景中最好建立和使用联合索引。

联合索引的数据结构与单例索引数据结构一致，区别是key值是由两个字段组成的。

SQL优化

插入数据

1、尽量使用 批量插入，因为每一次插入都会创建和销毁连接。

2、手动控制事务，避免事务的多次开启和提交。

3、大批量插入数据使用 load 指令

主键优化

1、索引数据结构的叶子节点是有序的，主键按顺序插入可以避免额外的操作。

2、尽量降低主键的长度

并发量低的情况下可以使用yyMMddHHmmSS0001作为主键。设定取号器。

order by 优化

order by排序条件可以设置成单例或者联合索引。order by 多字段排序也要满足最左前缀原则

创建索引：create index indexName on table(age asc,name desc)

group by 优化

where 条件和 group by 条件加起来也要满足最左前缀原则

limit 优化

limit 1000000,10；此时mysql会排序前1000010条记录，但只返回10条记录，其他的丢弃，这样查询排序的代价很大。

优化思路：通过创建 覆盖索引加子查询的方法进行优化。子查询的结果作为一张表。

select a.* from table_a a,(select id from table_b order by id limit 1000000,10) b where a.id = b.id;

count 优化

InnoDB引擎中，count(*)会查询每一条记录，行行判断，满足条件(字段值不为null)就累加。

1. COUNT(*):

   • COUNT(*) 会计算表中的所有行，包括包含 NULL 值的行。
   • 因为它要遍历所有的行，所以在大型表上可能会更耗时。

2. COUNT(1):

   • COUNT(1) 也会计算表中的所有行，但不考虑行的内容，只关注行的存在。
   • 由于它不需要检查行的内容，所以在某些情况下可以比 COUNT(*) 更快。

性能差异通常不太明显，特别是在小型表上。然而，在大型表上，COUNT(1) 可能稍微快一些，因为它不需要检查行的内容，只需要检查行是否存在。不过这种性能差异通常不是非常显著，而且在大多数数据库管理系统中，优化器会尝试优化 COUNT(*) 查询以提高性能。

update 优化

开启事务时，update更新的条件最好是索引，这样是给表记录加行锁。如果索引失效或者不是索引，则加的是表锁，会影响并发性能。

视图

概念：基于一个或多个表创建出来的虚拟表，并不是真实存在的。他是一个SQL逻辑。

对视图的增删改其实操作的是基表。

create or replace view viewName as select id,name from table;

锁

全局锁：锁数据库中全部的表。一般做数据库的逻辑备份。主从复制的数据库不能用该方法。

读锁(共享锁)：阻止其他用户更新数据，但允许他们读取数据。

写锁(排它锁)：阻止其他用户读取和更新数据，

共享锁适用于读取操作，它允许多个事务同时读取同一资源；而排它锁适用于写入操作，它只允许一个事务独占地对资源进行操作。

表级锁：锁数据库中的一张表

触发表锁：alter table 、 drop table、lock tables tableName read/write

解锁：unlock tables;

行级锁：锁表中的一行数据。行锁必须在开启事务时使用

触发行锁情景：
1.SELECT .. FOR UPDATE:这种查询会对选定的行添加一个排他锁(X锁)，这意味着其他事务不能修改这些行，也不能对这些行添加共享锁。
2.SELECT .. LOCK IN SHARE MODE:这种查询会对选定的行添加一个共享锁(S锁)，这意味着其他事务不能修改这些行，但可以对这些行添加共享锁。
3.INSERT:插入操作会对新添加的行添加一个排他锁(X锁)
4.UPDATE:更新操作会对被更新的行添加一个排他锁(X锁)
5.DELETE:删除操作会对被删除的行添加一个排他锁(X锁)

间隙锁：给指定范围加锁，解决幻读问题

select ... from ... where id >1 and id < 4 for update;

元数据锁(meta data lock，MDL)

意向锁