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高效获取数据的数据结构。可以得到索引的本质：索引是数据结构
类似于折半查找，如果我们想要找72这个数字：首先判断72是在30左边，还是30右边。然后再进一步判断，是在40左边，还是在40右边。就这样一层一层的查找。
特定查找算法：B树索引
索引是如何加快Col2的查找的？比如查找91：select * from xxx where Col2=91首先判断，91是在34左边还是右边，因为91是在34右边，因此找到了89；然后判断91是在89的左边还是右边，因为91是在89右边，因此就找到了91。 然后拿着91这个节点对应的物理地址，从而就找到了Col2中91对应的数据。
左边是数据表，这个表在内存中，所以它对应的有物理地址。最右边的二叉树是这个表对应的索引。每个索引树上的节点都分别对应着数据表中的一条数据和该条数据的物理地址。
如果对数据表里面的数据进行了修改或者删除，那么最右边的索引树种，有的节点就会失效。此时就需要重建索引。
说明
详解
数据本身之外，数据库还维护着一个满足特定查找算法的数据结构，这些数据结构以某种方式指向数据，这样就可以在这些数据结构的基础上实现高级查找算法，这种数据结构就是索引。
结论
你可以简单理解为"索引是排好序的用于快速查找数据的数据结构"。所以，索引会影响SQL语句中的where查找和order by 排序。
一般来说索引本身也很大，不可能全部存储在内存中，因此索引往往以文件形式存储在硬盘上
索引存储位置
索引分类
一句话：优化SQL首先优化索引。索引对SQL性能的提高非常非常大。
索引是什么
类似大学图书馆建书目索引，提高数据检索效率，降低数据库的IO成本
通过索引列对数据进行排序，降低数据排序成本，降低了CPU的消耗
优势
实际上索引也是一张表，该表保存了主键和索引字段，并指向实体表的记录，所以索引列也是要占用硬盘空间的。
注意：优秀的索引是需要程序员或者DBA花时间研究才能建立的，删了建，建了删，逐步优化出来的。
索引只是提高效率的一个因素，如果你的MySQL有大数据量的表，就需要花时间研究建立优秀的索引，或优化查询语句
劣势
概念
单值索引
唯一索引
覆盖索引
复合索引
[UNIQUE] ：中括号代表可以省略。如果加上这个字段，代表创建唯一索引。
如果table后面只写了一个字段，就是单值索引，如果写了多个字段，就是复合索引。
方式一：CREATE [UNIQUE] INDEX indexName ON mytable(columnname(length));
方式二：ALTER mytable ADD [UNIQUE] INDEX [indexName] ON(columnname(length));
创建
DROP INDEX [indexName] ON mytable;
删除
SHOW INDEX FROM table_name;
查看
使用ALTER命令
基本语法
分类
B树索引
Hash索引
全文索引
R-Tree 索引
索引结构
对于分组、排序、范围查找的查询哈希型的索引，时间复杂度会13;退化为O(n)，而树型的“有序”特性，依然能够保持O(log(n)) 的高效率
查找耗时与树的深度相关的，最坏时间复杂度会退化成O(n)
二叉查找树
左右子树深度差绝对值不能超过 1
插入/删除节点，需要频繁调整树结构，性能较差
树的深度过深，磁盘IO开销较大
平衡二叉树
叶子节点全黑，一红带两黑，弱平衡性
适用于删除插入操作较多的情况
红黑树
叶子节点，非叶子节点，都存储数据
中序遍历，可以获得所有节点
B树
非叶子节点不再存储数据，数据只存储在同一层的叶子节点上
叶子之间，增加了链表，获取所有节点，不再需要中序遍历
B+树
超链接
数据结构
1. 范围查找，定位min与max之后，中间叶子节点，就是结果集，不用中序回溯
2. 叶子节点存储实际记录行，记录行相对比较紧密的存储，适合大数据量13;磁盘存储；非叶子节点存储记录的PK，用于查询加速，适合内存存储；
3. 非叶子节点，不存储实际记录，而只存储记录的KEY的话，那么在相同13;内存的情况下，B+树能够存储更多索引；
对比
mysql 查询优化器
CPU
IO
常见瓶颈
表的读取顺序
数据读取操作的操作类型
可能用到哪些索引\t
实际用到哪些索引
表之间的引用
每张表有多少行被优化器查询
能做什么
id相同
id 不同
ID相同又不同
id
Simple
Primary
SubQuery
Derived
Union
Union result
select_type
table
system
const
equ_ref
ref
range
index
all
type
possible_keys
key
key_len
rows
Using filesort
Using temporary
Using index
using where
using join buffer
impossible where
select table optimized away
distinct
Extra
列名解析
explain
性能分析
1.主键自动建立唯一索引
2.频繁作为查询的条件的字段应该创建索引
3.查询中与其他表关联的字段，外键关系建立索引
因为每次更新不单单是更新了记录还会更新索引，加重IO负担
4.频繁更新的字段不适合创建索引
5.Where条件里用不到的字段不创建索引
6.单一索引/复合索引的选择问题，平时选择哪一个？who？（在高并发下倾向创建组合索引）
7.查询中排序的字段，排序字段若通过索引去访问将大大提高排序的速度
8.查询中统计或者分组字段
哪些情况需要创建索引
mysql虽然官方说能撑得住500到800万，但是实际上，300万条数据，性能就开始下降。
1.表记录太少
2.经常增删改的表
比如国籍，省市县，男女，这样的数据重复率高，这样的就不适合建索引。
注意，如果某个数据列包含许多重复的内容，为它建立索引就没有太大的实际效果。
3.数据重复且分布平均的表字段，因此应该只为经常查询和经常排序的数据列建立索引。
哪些情况不要创建索引
索引创建场景
永远是小表驱动大表
双路排序
单路排序
若排序的字段不在索引列上，13;则mysql会启用两种filesort算法
增大sort_buffer_size参数
增大 max_length_for_sort_data 参数
优化策略
order by
group by
查询优化
开启 set global slow_query_log=1;
阀值 long_query_time
s 按照何种方式排序
c 访问次数
l 锁定时间
r 返回记录
t 查询时间
al 平均锁定时间
ar 平均返回记录数
at 平均查询时间
g 后面搭配一个正则表达式
相关参数
日志分析工具 mysqldumpslow
慢查询日志
1. 查看数据库是否支持
2. 运行 sql
3. show profiles; 查看执行的语句
分析步骤
converting heap to myisam 13;表示查询结果太大，内存不够用改用磁盘
creating tmp table
copying to tmp table on disk 13;把内存中的临时表复制到磁盘
locked
需要注意的结论
show profile
dept表
emp表
订单表
建表
大数据插入注意事项
随机字符串函数
随机部门编号
随机状态数字
随机指定位数数字字符串
随机用户ID
创建函数
部门表dept
员工表emp
创建存储过程
执行存储过程
批量插入数据脚本
查询截取分析
概要
SQL执行时间长
SQL等待时间长
慢分为两方面
各种子查询，各种连接导致没建索引，或者没有用上索引
SQL查询语句写的烂
原因：索引失效的原因是建立了索引，但是没用上。
索引失效
关联查询太多join(设计缺陷或不得已的需求)
服务器调优及各个参数设置(缓冲\\线程数等)，设置不合理也会导致性能下降变慢
保存数据库的硬盘空间不足
原因
性能下降SQL查询慢
程序员写的SQL语句（人写）
MySQL软件拿到程序员写的SQL语句后，是按照这个顺序来执行的.它是先从FROM 开始执行，先找到用到了哪些表，然后对表进行join连接，最后才查询具体的字段。【记住】
MySQL软件理解的SQL语句（机读）
这是个鱼骨图
1、MySQL服务器，先从from开始执行，然后再执行on。【注意：from和on是有前后顺序的】
2、然后同时执行join和on，然后依次执行，最后直到limit
总结
SQL执行顺序
join图
mysql中使用Nested Loop Join来实现join；A JOIN B：通过A表的结果集作为循环基础，一条一条的通过结果集中的数据作为过滤条件到下一个表中查询数据，然后合并结果；
join原理
建表SQL
1、内连接
1、左外连接
2、右外连接
因为mysql不支持全外连接，所以可以通过这种方式来实现全外连接的效果。
3、全外连接
2、外链接
7种Join
常见通用的join查询
建表SQL
查询category_id为1，且comments大于1的情况下，views（阅读量）最多的article_id。
需求
SQL
很显然，type是ALL ，即全表扫描，这是最坏的情况。Extra里面还出现了Using filesort，这也是最坏的情况。优化是必须的。
没有优化之前
查看建立的索引：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 索引中，只有第一个字段起作用，最后一个字段无效了。
为什么本次优化有问题
第一次优化
drop index idx_article_cvv on article;
首先删除原来建立的索引
重新建立新索引
再次查看优化结果
可以看到，type变为了ref，Extra中的 Using filesort也消失了，结果非常理想。这就完美了。
第二次优化
优化
案例
单表
select * from class left join book on class.card = book.card;
SQL：左连接
结论：两个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条件字段上。
两表
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优化的效果也很好。
优化规则
A表连接B表形成一个中间表，B表连接C表又形成一个中间表，这些中间表在连接的过程中都是放到缓存中。如果join buffer越大，所有的中间表都可以放到内存中，所有的查询都可以在内存中完成。如果join buffer小，连的表大，中间表的数据放不下，就会把连接的数据精简掉一些数据，如果你要查询的数据刚好是被精简掉的数据，那么select还是要跑到数据库中重新查询一次，这样性能就比较低
JoinBuffer说明
三表
索引分析
这3中情况都是很好的使用了索引。
全值匹配
总结：where后面查询的字段个数和顺序，都和索引中的字段个数和顺序一致。这就是全值匹配。
1、全值匹配我最爱
如果索引了多例，要遵守最左前缀法则。指的是查询从索引的最左前列开始“并且不跳过索引中的列”。
带头大哥死了，索引就全失效了。
1、带头大哥不能死。
正常情况下，2个索引的key_len应该是78，而且ref后面应该有两个const。所以，可以看出来，只用到了name的索引。pos的索引没有用到，失效了。
就好比，建立的一个梯子，用来连接1楼，2楼，3楼， 你不能把2楼的梯子抽走，否则就去不了3楼了。这个时候，只能用到了1楼的索引，3楼的索引就失效了。
解读
2、中间兄弟不能断。
口诀
2、最佳左前缀法则
3、不在索引列上做任何操作（计算、函数、（自动or手动）类型转换），会导致索引失效而转向全表扫描
最后一条SQL：where name ='z4' and age>11 and pos='manager'这里，索引只用到了name和age两个字段，而pos失效了。name：用到索引了，用于查找；age：用到索引了，用于排序。
4、存储引擎不能使用索引中范围条件右边的列
口诀：范围之后全失效
第1、2两条SQL覆盖索引比select * 在Extra中多了一个using index，这说明覆盖索引性能更优秀。
第3条SQL当select后面是覆盖索引的时候，如果where语句中，使用了范围age>25，那么age和pos全失效。只有name一个索引有用
最后2条SQL要查的字段，最好在建的索引列之内，或者和索引列等同，这个时候Extra出现Using index，性能将非常优秀。
5、尽量使用覆盖索引（只访问索引的查询（索引列和查询列一致）），减少select*
在开发中，有时候为了业务需要，即使是索引失效，也要写这样的SQL。具体问题具体分析。不要怕会导致索引失效，而束手束脚。所以，你心里知道有这回事就行，开发中为了业务的需要，该怎么写就怎么写。
6、mysql在使用不等于（！=或者<>）的时候无法使用索引，会导致全表扫描
既然知道有这个规则，开发中，我们在数据表中就尽量为每个字段设置一个default默认值，尽量不要在表数据中出现NULL。
%加在左边，有问题。会导致索引失效，且全表扫描。%加在右边，没问题。并且like 是个range，即范围
建立索引
解决方式：使用覆盖索引
解决like'%字符串%'索引不被使用的方法？？即like中%导致索引失效的问题，如何解决？
问题
百分% like 加右边。如果两边都有百分，使用覆盖索引解决失效。
8、like以通配符开头（'%abc...'）mysql索引失效会变成全表扫描操作
varchar类型，绝对不能省去单引号。【不写单引号，开发中是重罪】
如果不写单引号，就会导致自动类型转换，违背索引失效：第3条禁忌，会导致索引失效且全表扫描。
9、字符串不加单引号索引失效。
10、少用or，用它连接时会索引失效
like KK%相当于=常量 %KK和%KK% 相当于范围
11、小总结
全值匹配我最爱，最左前缀记心间。带头大哥不能死，中间兄弟不能断。索引列上不计算，like百分%最右边。范围之后全失效，字符串要加引号。多用覆盖不写星，不等!=（<>）、NULL、OR全完蛋。
12、优化总口诀
案例（索引失效）
对于单键索引，尽量选择针对当前query过滤性更好的索引
在选择组合索引的时候，当前Query中过滤性最好的字段在索引字段顺序中，位置越靠前（即靠左）越好。
在选择组合索引的时候，尽量选择可以能包含当前query中的where子句中更多字段的索引
尽可能通过分析统计信息和调整query的写法来达到选择合适索引的目的
一般性建议
索引优化
索引优化分析
索引
连接池
连接器
查询缓存的失效非常频繁，只要有对一个表的更新，这个表上所有的查询缓存都会被清空。(Mysql8.0直接删除掉功能)
查询缓存
词法分析/语法分析，生成“解析树”
分析器
生成执行计划
优化器
执行器
Server 层
InnoDB、MyISAM、Memory
MyISAM和InnoDB
存储引擎
默认开启，show variables like "%log_error%"
Error log
记录一般查询语句，show variables like "%general%"
General query log
记录所有执行时间超时的sql，默认十秒
show query log
只记录修改语句，用于数据库恢复和主从复制，默认关闭
show variables like "%log_bin%"
记录模式
写入机制
bin log(服务层)
redo log(innodb引擎)
逻辑日志，事务开始之前，修改的记录存到undo log
实现事务的原子性
实现多版本并发控制（MVCC）
作用
采用段(segment)的方式来记录的，每个undo操作在记录的时候占用一个undo log segment
undo log
日志系统
Mysql基础架构
事务是一个完整的操作，各步操作是不可分的（原子的）
原子性(atomicity)
当事务完成时，数据必须处于一致状态。
一致性(consistency)
读未提交：一个事务还没提交时，它做的变更就能被别的事务看到
读提交：一个事务提交之后，它做的变更才会被其他事务看到
可重复读：一个事务执行过程中看到的数据，总是跟这个事务在启动时看到的数据一致。当然未提交变更对其他事务也是不可见的
串行化：读写锁串行
隔离性(isolation)
事务完成后，它对数据库的修改被永久保持，事务日志能够保持事务的永久性。
持久性(durability)
脏读（读未提交）：一个事务读取到另外一个事务还没有提交的数据
不可重复读（读提交）：在同一个事务内，两次相同的查询返回了不同的结果（修改）
幻读（可重复读）：同一个事务内多次查询返回的结果集不一样（新增/删除）
事务问题
事务
Mysql
0 条评论
回复 删除
下一页
