首页  思维导图  详情

Mysql

2023-12-04 17:48:19   0  举报





AI智能生成

java

作者其他创作

大纲/内容

锁

全局锁

对整个数据库实例加锁。13;MySQL提供加全局读锁的方法：Flush tables with read lock(FTWRL)

使用场景：全库逻辑备份。

风险

如果在主库备份，在备份期间不能更新，业务停摆

如果在从库备份，备份期间不能执行主库同步的binlog，导致主从延迟

表级锁

表锁

表锁的语法是 lock tables … read/write

可以用unlock tables主动释放锁，也可以在客户端断开时自动释放

元数据锁（MDL)

MDL 不需要显式使用，在访问一个表的时候会被自动加上

读读不互斥，读写/写写互斥

行级锁

InnoDB支持，MyISAM不支持

InnoDB 事务中，行锁在需要时才加上的，但并不是不需要了就立刻释放，而是要等到事务结束时才释放。这个就是两阶段锁协议

事务中需要锁多个行，要把最可能造成锁冲突、最可能影响并发度的锁尽量往后放

死锁检测

直接进入等待，直到超时。这个超时时间可以通过参数 innodb_lock_wait_timeout 来设置。（等待时间过长，不推荐）

发起死锁检测，发现死锁后，主动回滚死锁链条中的某一个事务，让其他事务得以继续执行。将参数 innodb_deadlock_detect 设置为 on，表示开启这个逻辑。

索引

概念

索引是什么

MySQL官方对索引的定义为：索引(Index)是帮助MySQL高效获取数据的数据结构。可以得到索引的本质：索引是数据结构

分支主题

你可以简单理解为"索引是排好序的用于快速查找数据的数据结构"。所以，索引会影响SQL语句中的where查找和order by 排序。

详解

分支主题

特定查找算法：B树索引

分支主题

类似于折半查找，如果我们想要找72这个数字：首先判断72是在30左边，还是30右边。然后再进一步判断，是在40左边，还是在40右边。就这样一层一层的查找。

说明

索引是如何加快Col2的查找的？比如查找91：select * from xxx where Col2=91首先判断，91是在34左边还是右边，因为91是在34右边，因此找到了89；然后判断91是在89的左边还是右边，因为91是在89右边，因此就找到了91。然后拿着91这个节点对应的物理地址，从而就找到了Col2中91对应的数据。

左边是数据表，这个表在内存中，所以它对应的有物理地址。最右边的二叉树是这个表对应的索引。每个索引树上的节点都分别对应着数据表中的一条数据和该条数据的物理地址。

如果对数据表里面的数据进行了修改或者删除，那么最右边的索引树种，有的节点就会失效。此时就需要重建索引。

结论

数据本身之外，数据库还维护着一个满足特定查找算法的数据结构，这些数据结构以某种方式指向数据，这样就可以在这些数据结构的基础上实现高级查找算法，这种数据结构就是索引。

索引存储位置

一般来说索引本身也很大，不可能全部存储在内存中，因此索引往往以文件形式存储在硬盘上

索引分类

我们平时所说的索引，如果没有特别指明，都是指B树(多路搜索树，并不一定是二叉树)结构组织的索引。其中聚集索引，次要索引，覆盖索引，复合索引，前缀索引，唯一索引默认都是使用B+树索引，统称索引。当然,除了B+树这种类型的索引之外，还有哈希索引(hash index)等。

一句话：优化SQL首先优化索引。索引对SQL性能的提高非常非常大。

优势

类似大学图书馆建书目索引，提高数据检索效率，降低数据库的IO成本

通过索引列对数据进行排序，降低数据排序成本，降低了CPU的消耗

劣势

实际上索引也是一张表，该表保存了主键和索引字段，并指向实体表的记录，所以索引列也是要占用硬盘空间的。

虽然索引大大提高了查询速度，同时却会降低更新表的速度,如果对表INSERT,UPDATE和DELETE。因为更新表时，MySQL不仅要不存数据，还要保存一下索引文件每次更新添加了索引列的字段，都会调整因为更新所带来的键值变化后的索引信息

索引需要额外的维护成本；因为索引文件是单独存在的文件,对数据的增加,修改,删除,都会产生额外的对索引文件的操作,这些操作需要消耗额外的IO,会降低增/改/删的执行效率;

索引只是提高效率的一个因素，如果你的MySQL有大数据量的表，就需要花时间研究建立优秀的索引，或优化查询语句

注意：优秀的索引是需要程序员或者DBA花时间研究才能建立的，删了建，建了删，逐步优化出来的。

分类

单值索引

唯一索引

复合索引

覆盖索引

基本语法

创建

方式一：CREATE [UNIQUE] INDEX indexName ON mytable(columnname(length));

如果是CHAR,VARCHAR类型，length可以小于字段实际长度；如果是BLOB和TEXT类型，必须指定length。

[UNIQUE] ：中括号代表可以省略。如果加上这个字段，代表创建唯一索引。

如果table后面只写了一个字段，就是单值索引，如果写了多个字段，就是复合索引。

方式二：ALTER mytable ADD [UNIQUE] INDEX [indexName] ON(columnname(length));

删除

DROP INDEX [indexName] ON mytable;

查看

SHOW INDEX FROM table_name;

使用ALTER命令

索引结构

B树索引

Hash索引

全文索引

R-Tree 索引

数据结构

二叉查找树

查找耗时与树的深度相关的，最坏时间复杂度会退化成O(n)

平衡二叉树

左右子树深度差绝对值不能超过 1

插入/删除节点，需要频繁调整树结构，性能较差

树的深度过深，磁盘IO开销较大

红黑树

叶子节点全黑，一红带两黑，弱平衡性

适用于删除插入操作较多的情况

B树

叶子节点，非叶子节点，都存储数据

中序遍历，可以获得所有节点

B+树

非叶子节点不再存储数据，数据只存储在同一层的叶子节点上

叶子之间，增加了链表，获取所有节点，不再需要中序遍历

性能分析

mysql 查询优化器

常见瓶颈

CPU

explain

能做什么

表的读取顺序

数据读取操作的操作类型

可能用到哪些索引

实际用到哪些索引

表之间的引用

每张表有多少行被优化器查询

列名解析

id相同

id 不同

ID相同又不同

select_type

Simple

Primary

SubQuery

Derived

Union

Union result

table

type

system

const

equ_ref

ref

range

index

all

possible_keys

key

key_len

ref

rows

Extra

Using filesort

Using temporary

Using index

using where

using join buffer

impossible where

select table optimized away

distinct

索引创建场景

哪些情况需要创建索引

1.主键自动建立唯一索引

2.频繁作为查询的条件的字段应该创建索引

3.查询中与其他表关联的字段，外键关系建立索引

4.频繁更新的字段不适合创建索引

因为每次更新不单单是更新了记录还会更新索引，加重IO负担

5.Where条件里用不到的字段不创建索引

6.单一索引/复合索引的选择问题，平时选择哪一个？who？（在高并发下倾向创建组合索引）

7.查询中排序的字段，排序字段若通过索引去访问将大大提高排序的速度

比如你创建复合索引（name,age,address），那么排序的时候如果还按照name,age,address排序，速度会非常快。

8.查询中统计或者分组字段

哪些情况不要创建索引

1.表记录太少

mysql虽然官方说能撑得住500到800万，但是实际上，300万条数据，性能就开始下降。

2.经常增删改的表

3.数据重复且分布平均的表字段，因此应该只为经常查询和经常排序的数据列建立索引。

分支主题

比如国籍，省市县，男女，这样的数据重复率高，这样的就不适合建索引。

注意，如果某个数据列包含许多重复的内容，为它建立索引就没有太大的实际效果。

查询截取分析

查询优化

永远是小表驱动大表

order by

若排序的字段不在索引列上，13;则mysql会启用两种filesort算法

双路排序

单路排序

优化策略

增大sort_buffer_size参数

增大 max_length_for_sort_data 参数

group by

慢查询日志

开启 set global slow_query_log=1;

阀值 long_query_time

日志分析工具 mysqldumpslow

相关参数

s 按照何种方式排序

c 访问次数

l 锁定时间

r 返回记录

t 查询时间

al 平均锁定时间

ar 平均返回记录数

at 平均查询时间

g 后面搭配一个正则表达式

show profile

分析步骤

1. 查看数据库是否支持

2. 运行 sql

3. show profiles; 查看执行的语句

4. 诊断 sql , show profile cpu , block io for query N

需要注意的结论

converting heap to myisam 13;表示查询结果太大，内存不够用改用磁盘

creating tmp table

copying to tmp table on disk 13;把内存中的临时表复制到磁盘

locked

批量插入数据脚本

建表

dept表

emp表

订单表

大数据插入注意事项

创建函数

随机字符串函数

随机部门编号

随机状态数字

随机指定位数数字字符串

随机用户ID

创建存储过程

部门表dept

员工表emp

订单表

执行存储过程

索引优化分析

性能下降SQL查询慢

慢分为两方面

SQL执行时间长

SQL等待时间长

原因

SQL查询语句写的烂

各种子查询，各种连接导致没建索引，或者没有用上索引

索引失效

原因：索引失效的原因是建立了索引，但是没用上。

分类

单值索引

分支主题

复合索引

分支主题

关联查询太多join(设计缺陷或不得已的需求)

服务器调优及各个参数设置(缓冲\线程数等)，设置不合理也会导致性能下降变慢

保存数据库的硬盘空间不足

常见通用的join查询

SQL执行顺序

程序员写的SQL语句（人写）

分支主题

MySQL软件理解的SQL语句（机读）

分支主题

MySQL软件拿到程序员写的SQL语句后，是按照这个顺序来执行的.它是先从FROM 开始执行，先找到用到了哪些表，然后对表进行join连接，最后才查询具体的字段。【记住】

总结

分支主题

这是个鱼骨图

1、MySQL服务器，先从from开始执行，然后再执行on。【注意：from和on是有前后顺序的】

2、然后同时执行join和on，然后依次执行，最后直到limit

join图

分支主题

join原理

mysql中使用Nested Loop Join来实现join；A JOIN B：通过A表的结果集作为循环基础，一条一条的通过结果集中的数据作为过滤条件到下一个表中查询数据，然后合并结果；

建表SQL

7种Join

1、内连接

分支主题

2、外链接

1、左外连接

分支主题

2、右外连接

分支主题

3、全外连接

分支主题

因为mysql不支持全外连接，所以可以通过这种方式来实现全外连接的效果。

索引优化

索引分析

单表

建表SQL

案例

需求

查询category_id为1，且comments大于1的情况下，views（阅读量）最多的article_id。

SQL

select id,author_id from article where category_id=1 and comments>1 order by views desc limit 1;

优化

没有优化之前

分支主题

结论

很显然，type是ALL ，即全表扫描，这是最坏的情况。Extra里面还出现了Using filesort，这也是最坏的情况。优化是必须的。

第一次优化

优化：建立索引

根据查询的3个条件建立索引：category_id、comments和views，取首字母简称cvv 建立索引：create index idx_article_cvv on article(category_id,comments,views);

查看建立的索引

查看建立的索引：show index from article;

分支主题

查看优化结果

分支主题

type这一次是Range，而不再是ALL了，这是可以忍受的，解决了全表扫描的问题。但是，仍然存在Using filesort 文件内排序的问题，仍然无法忍受。

为什么本次优化有问题

原因：我们已经根据3个查询条件建立索引了啊，怎么还会有Using filesort问题呢？难道建立的索引有什么问题吗？这是因为按照BTree索引的工作原理：先排序category_id，如果遇到相同的category_id，则会再根据comments排序，如果遇到相同的comments，则再排序views。当comments字段在复合索引里处于中间位置时候，因为comments>1条件是一个范围值（所谓range）MySQL无法利用索引再对comments后面的views部分进行检索，即range类型的查询字段后面的索引无效。也即是，cvv 索引中，只有第一个字段起作用，最后一个字段无效了。

试一下：我们把where后面的查询条件comments>1 改为comments=1，再次explain，看看结果explain select id,author_id from article where category_id=1 and comments=1 order by views desc limit 1;#结论：我们发现，此时，type 变为了ref，而Using filesort也没有了，完美。这说明，该SQL完美的利用了我们建立的idx_article_cvv。这也说明我们在前面的结论成立。

分支主题

第二次优化

首先删除原来建立的索引

drop index idx_article_cvv on article;

重新建立新索引

根据category_id和views两个字段，重新建立索引：create index idx_article_cv on article(category_id,views);

再次查看优化结果

分支主题

结论

可以看到，type变为了ref，Extra中的 Using filesort也消失了，结果非常理想。这就完美了。

两表

建表SQL

案例

SQL：左连接

select * from class left join book on class.card = book.card;

优化

没优化之前

分支主题

结论：两个type都是ALL，都是全表扫描，不可接受。且查询行数是20*20行，笛卡尔积，必须优化。

第一次优化

尝试：左连接，索引建在左表上，即class上建立索引：alter table class add index Y(`card`);

分支主题

结论：虽然id=1 的type变成了index，但是查询行数仍然是20*20，仍然是笛卡尔积，优化不成功。

第二次优化

删除上面建立的无效索引：drop index Y from class;

尝试：左连接，索引建在右表上，即book上建立索引： alter table book add index X(`card`);

分支主题

结论：type变成了ref，而且rows的优化效果非常明显。

结论

分析

这是左连接的特性决定的，因为左连接查询的结果是：左表的全部，和右表的部分。即，left join右边的条件，决定如何从右表搜索结果。这就是说，左连接右表才算关键。右表决定了最终查询的结果。所以左连接要把索引建立在右表上。

分支主题

结论：左连接，索引建在右表上。右连接，索引建在左表上。即，左右连接，相反建。并且，要把索引建在join条件字段上。

三表

建表SQL

案例

SQL

select * from class left join book on class.card = book.card left join phone on book.card = phone.card;

没优化之前

分支主题

优化规则

按照两表连接优化规则进行优化即可。即，在book和phone两个表上建索引即可。alter table book add index X(`card`);alter table phone add index Z(`card`);

分支主题

结论：后2行的type都是ref，然后rows优化的效果也很好。

总结

join语句的优化：1、尽可能减少join语句中的NestedLoop的循环总次数：“永远用小结果集驱动大的结果集”。2、优先优化NestedLoop的内层循环。因为内层是大结果集，内层的性能高了，整体的查询性能就高了。3、保证join语句中被驱动表上join字段已经被索引。4、当无法保证被驱动表的join条件字段被索引且内存资源充足的前提下，不要太吝啬MySQL配置文件中JoinBuffer的设置。5、不管你有多少个索引,一次查询至多采用一个索引;(索引和索引之间是独立的)。为了解决这个问题，开发中尽量使用复合索引。在实际应用中,基本上都使用复合索引;

JoinBuffer说明

A表连接B表形成一个中间表，B表连接C表又形成一个中间表，这些中间表在连接的过程中都是放到缓存中。如果join buffer越大，所有的中间表都可以放到内存中，所有的查询都可以在内存中完成。如果join buffer小，连的表大，中间表的数据放不下，就会把连接的数据精简掉一些数据，如果你要查询的数据刚好是被精简掉的数据，那么select还是要跑到数据库中重新查询一次，这样性能就比较低

案例（索引失效）

1、全值匹配我最爱

全值匹配

分支主题

这3中情况都是很好的使用了索引。

总结：where后面查询的字段个数和顺序，都和索引中的字段个数和顺序一致。这就是全值匹配。

2、最佳左前缀法则

如果索引了多例，要遵守最左前缀法则。指的是查询从索引的最左前列开始“并且不跳过索引中的列”。

口诀

1、带头大哥不能死。

分支主题

带头大哥死了，索引就全失效了。

2、中间兄弟不能断。

分支主题

解读

正常情况下，2个索引的key_len应该是78，而且ref后面应该有两个const。所以，可以看出来，只用到了name的索引。pos的索引没有用到，失效了。

就好比，建立的一个梯子，用来连接1楼，2楼，3楼，你不能把2楼的梯子抽走，否则就去不了3楼了。这个时候，只能用到了1楼的索引，3楼的索引就失效了。

3、不在索引列上做任何操作（计算、函数、（自动or手动）类型转换），会导致索引失效而转向全表扫描

分支主题

4、存储引擎不能使用索引中范围条件右边的列

分支主题

最后一条SQL：where name ='z4' and age>11 and pos='manager'这里，索引只用到了name和age两个字段，而pos失效了。name：用到索引了，用于查找；age：用到索引了，用于排序。

5、尽量使用覆盖索引（只访问索引的查询（索引列和查询列一致）），减少select*

口诀：范围之后全失效

分支主题

解读

第1、2两条SQL覆盖索引比select * 在Extra中多了一个using index，这说明覆盖索引性能更优秀。

第3条SQL当select后面是覆盖索引的时候，如果where语句中，使用了范围age>25，那么age和pos全失效。只有name一个索引有用

最后2条SQL要查的字段，最好在建的索引列之内，或者和索引列等同，这个时候Extra出现Using index，性能将非常优秀。

6、mysql在使用不等于（！=或者<>）的时候无法使用索引，会导致全表扫描

分支主题

在开发中，有时候为了业务需要，即使是索引失效，也要写这样的SQL。具体问题具体分析。不要怕会导致索引失效，而束手束脚。所以，你心里知道有这回事就行，开发中为了业务的需要，该怎么写就怎么写。

7、is null,is not null 也无法使用索引

分支主题

既然知道有这个规则，开发中，我们在数据表中就尽量为每个字段设置一个default默认值，尽量不要在表数据中出现NULL。

8、like以通配符开头（'%abc...'）mysql索引失效会变成全表扫描操作

详解

分支主题

%加在左边，有问题。会导致索引失效，且全表扫描。%加在右边，没问题。并且like 是个range，即范围

问题

解决like'%字符串%'索引不被使用的方法？？即like中%导致索引失效的问题，如何解决？

建表SQL

建立索引

CREATE INDEX idx_user_nameAge on tbl_user(name,age);

解决方式：使用覆盖索引

id因为是主键，自带索引，自带光环。explain select id from tbl_user where name like '%aa%'explain select name from tbl_user where name like '%aa%'explain select id,name from tbl_user where name like '%aa%'explain select id,name,age from tbl_user where name like '%aa%'explain select name,age from tbl_user where name like '%aa%'

分支主题

口诀

百分% like 加右边。如果两边都有百分，使用覆盖索引解决失效。

9、字符串不加单引号索引失效。

varchar类型，绝对不能省去单引号。【不写单引号，开发中是重罪】

分支主题

如果不写单引号，就会导致自动类型转换，违背索引失效：第3条禁忌，会导致索引失效且全表扫描。

10、少用or，用它连接时会索引失效

分支主题

11、小总结

分支主题

like KK%相当于=常量 %KK和%KK% 相当于范围