Mysql 高级学习
1、Mysql索引和索引优化
1、修改mysql 修改mysql的字符集
查看目前数据库的字符集
show variables like 'character_set%';
show variables like 'collation%';
此时在myql的客户端查看数据结果是乱码,但是在navicat中不是乱码
可以发现数据库服务端字符集是latin1
docker 安装的mysql 配置文件路径 cd /etc/mysql
https://www.cnblogs.com/NyanKoSenSei/p/13737000.html 参考地址
yum install supervisor
apt-get update
apt-get install nano
nano my.cnf
[mysql]
default-character-set=utf8mb4
[mysqld]
character_set_server=utf8mb4
init_connect='SET NAMES utf8'
lower_case_table_names = 1
查看修改配置文件后数据库的字符集
show variables like 'character_set%';
show variables like 'collation%';
2、mysql数据库主要的配置文件说明
mysql的配置文件
/etc/mysql/my.cnf
存放表索引的文件
/var/lib/mysql/mysql/x.myi文件
存放表数据的文件
/var/lib/mysql/mysql/x.myd文件
存放表结构
/var/lib/mysql/mysql/x.frm文件
3、mysql的逻辑架构说明
总体概览
我们业务和mysql建立连接到发送sql到mysql返回数据，mysql内部是怎么处理的呢，mysql也是一个程序，它的架构设计是怎么样的呢？
Connectors：指的是不同语言中与SQL的交互。
Connection Pool：连接池，是为解决资源的频繁分配、释放所造成的问题而为数据库建立的一个“缓冲池”。原理：预先在缓冲池中放入一定数量的连接，当需要建立数据库连接时，只需从“缓冲池”中取出一个，使用完毕后再放回去。它的作用是进行身份验证、线程重用、连接限制、管理用户的连接、线程处理等需要缓存的需求
SQL Interface(SQL接口)：接受用户的SQL命令，并且返回用户需要查询的结果，DDL,DML,存储过程等sql
Parser解析器：验证和解析SQL命令，主要通过语法规则来验证和解析，比如是否使用了错误的关键字或者关键字的顺序是否正确等.
英[ˈɒptɪmaɪzə] 可优化的
Optimizer查询优化器：SQL语句在查询执行之前，会使用查询优化器对查询进行优化，得出一个最优的策略。
Cache和Buffer：将客户端提交给Mysql的select类query请求的返回结果集缓存到内存中，与该query的一个hash值做一个对应。如果查询缓存有命中的查询结果，查询语句就可以直接去查询缓存中取数据。这个缓存机制是由一系列的小缓存组成，比如，表缓存、记录缓存、key缓存、权限缓存等。
Pluggable Storage Engines：可插拔存储引擎。 英[ˈplʌgəbl] 可插的 存储引擎层，存储引擎真正的负责了MySQL中数据的存储和提取，服务器通过API与存储引擎进行通信，主要MyISAM和InnoDB
File System：数据存储在运行于裸设备的文件系统上，支持的文件类型有EXT3、EXT4 、NTFS、NFS。
存储引擎
INNODB 的查询有哪些
show engines
查询当前的存储引擎
show variables like '%storage_engine';
MyISAM和InnoDB的区别
1、MyISAM 不支持外键，InnoDB支持
2、MyISAM 不支持事务,InnoDB支持
3、MyISAM 支持表锁，即使操作一条记录也会锁住整个表，不适合高并发的操作，InnoDB支持 支持的是行锁，适合高并发的操作
4、MyISAM 只缓存索引，不缓存真实的数据，InnoDB 不仅缓存索引还要缓存真实的数据，对内存要求较高。
4、SQL执行性能下降的原因，执行时间长，等待时间长
常见的原因: 查询语句写的烂,索引失效,关联太多的join,数据库链接相关的参数设置(缓存,连接池参数)
5、索引
1、索引是什么
索引(index) 是帮助Mysql 高效获取数据的数据结构,索引的本质 数据结构。
可以简单理解为‘排好序的能快速查找数据的数据结构’ 排序加查找
2、索引提高查询效率的原因
数据库除了存储数据，还维护着一个满足特定查找算法的数据结构，以这些数据结构实现一个高级查找算法
，这个数据结构具体的落地是b+tree;
如果某个列加了索引
一般来说索引本身也很大,不可能全部存储在内存中，因此索引往往以索引文件的形式存储在磁盘上
3、索引的优势和劣势
优势:提高数据检索的效率，降低数据库的IO成本减少了和磁盘交互的次数,降低数据排序的成本，降低了CPU的消耗
劣势:虽然索引大大提高了查询数据，同时却会降低更新表的数据
4、索引分类
单值索引,唯一索引、复合索引
唯一索引索引列的值必须唯一，但允许有空值，可以有多个
复合索引:即一个索引有多个列组成
创建的方式
1、create index 索引名称 on 表(表字段)
create INDEX myindex ON employees(job_id)
如果是一个字段就是单值索引、如果是多个字段就是复合索引
2、删除索引
drop index 索引名称 on 表
drop index myindex on employees;
3、查看索引
show index from 表名
show index FROM employees;
5、BTREE 索引原理
数据库数据在物理存储上有段、区、块之分。
BTREE 又叫多路平衡搜索树，一颗m叉的BTREE特性如下
1、树中每个节点最多包含m个孩子
2、除根节点和叶子节点外，每个节点至少[ceil(m/2)]个孩子 意思是 m/2向上取整
3、若根节点不是叶子节点，则至少有两个孩子
4、所有的叶子节点都在同一层
5、每个非叶子节点由n个key和n+1个指针组成，其中[ceil(m/2)-1] <= n<=m-1
以 5叉Btree为例，key的数量: 公式推导[ceil(m/2)-1] <= n <= m-1.所以 2<=n <=4,当 n > 4时
，中间节点分裂到父节点，两边节点分裂
插入 C N G A H E K Q M F W L T Z D P R X Y S数据为例
BTREE
1插入前4个字母
此时第一个块节点1，最多可以插 4个字母 按照 英文字母排序
依次是 A C G N ，指针有 5个
2 插入H 时 ，时第一个块节点 n > 4了 , H 在 G和N之间，中间元素G字母向上分裂到新的块节点1,AC,HN 分裂出块节点
2和3
3、插入EKQ 不需要分裂
E插入 块节点 2 (ACE)，KQ 插入块节点 3 (HKNQ)
4、插入M 中间元素M字母向上分裂到父节点
此时块节点1 有两个元素GM,块节点3 分裂成 4(HK) 和 5(NQ)
5、插入FWLT不需要分裂
此时不需要分裂 每个块节点 的key 不超过 4个
6、插入Z
Z在块节点1 判断要在 M后面 通过指针指向，
结论
1、通过这个比较发现在增删改的时候需要调整索引的数据块结构里面的值所以 增删改影响效率
2、Btree相对于二叉树来说查询效率更高，因为对于相同的数量来说，BTREE的层级结构比二叉树小，
因此搜索速度快，二叉树IO次数多
B+TREE
B+TREE 是BTREE的变种,和BTREE的区别
1、n叉B+tree 每个块节点最多n个 key,而btree 最多n-1个key
2、B+Tree 的叶子节点保存所有的key信息和对应的行data，依key大小顺序排列
3、所有的非叶子节点都可以看作是key的索引部分。
理解:
我们根据索引进行查找时，从根节点开始找，根节点存的数据先判断指针走那个
子节点然后在判断指针走那个，最后走到叶子节点里面的key，然后根据key找到对应的值。
我们用到的B+树不会超过4层. 节点层越高I/O 次数越多
B+TREE 和 Btree的对比
1、B+Tree 只有叶子节点保存key对应的每一行data，查询任何key都要从root走到叶子。所以B+Tree的查询效率更加稳定。
2、Btree 每一个节点存储key值、指针、key对应的每一行数据，B+Tree 除了叶子节点外存储的是key值、指针
二叉树是每个节点有两个分叉，B树是每个节点最多M个分叉
6、那些情况下可以建索引，那些不建议加索引
1、建议添加索引的情况
1、主键自带了索引
2、频繁作为查询条件的字段应该创建索引，比如说银行账号，电信系统的手机号，微信号
3、查询中与其他表关联的字段，外键关系建立索引，例如员工表中的department_id
4、查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度
假如创建了一个复合索引 分别是 name、age、email 查询的结果按照 name、age、email来展示，排序的时候也最好按照
这个顺序来 例如order by name 而不是order by email
5、在高并发情况下倾向于创建组合索引 为什么？因为涉及到了索引选择性
6、查询中统计或者分组字段 也要和索引的顺序来
2、不建议添加索引的情况
1、频繁更新的字段不适合创建索引，因为每次更新不单单是更新表数据，还会更新索引的数据结构
2、where条件用不到的字段不创建索引
3、表记录太少 （一两万的数据以内）三百万以后 考虑建索引。
4、如果某个字段有太多的重复数据，那么建了索引没有特别的效果
3、索引的选择性
定义: 指索引列中不同值的数目与表中记录数相比，如果一个表中有2000条数据，索引列有1980 个不同的值，那么索引的选择性就是
1980 / 2000 =0.99 。一个索引的选择性越接近于1 那么索引的效率越高。
这里就说明了为什么复合索引比单值索引号
7、SQL 的性能分析
1、explain 是什么
解释 说明 阐明 的意思
是一个模拟器然后出报告
2、怎么用
explain + sql 语句
返回的结果一个执行结果 也是一个表格
id select_type table type possible_key key key_len ref rows extra
id:
select 查询的序列号、包含一组数字，表示查询中执行select子句或操作表的顺序。
1）如果是子查询 id的序号会递增，id 值越大，查询优先级越高 查询越先被执行
2）explain 的结果如果存在id 相同和不同的情况，同样也是id越大查询越先被执行，优先级越高，如果id是相同的，查询
按照顺序来执行
select_type:
SQL查询的类型，是普通查询、联合查询还是子查询等的复杂查询。值有 SIMPLE、PRIMARY、SUBQUERY、DERIVED、UNION
SIMPLE: 简单的select 查询,查询中不包含子查询或者UNION
PRIMARY: 查询sql 最后执行的表 一般是有子查询
SUBQUERY: 表示查的表是在子查询的部分
DERIVED: 衍生表，意思是 FROM 后面的 有一个表子查询，把这个表子查询放到 临时表里
UNION: 若第二个select 出现在UNION之后，责备标记为UNION
type:
这个值反应出我们的SQL是否优化过是否是最佳状态息息相关
主要的值有 all index range ref eq_ref const,system null
SQL查询的效率是否 最好 根据查询使用了何种type有关系，从最好到最差依次是
system > const > eq_ref > range > index > all
如果你的数据有 四百万以上，查询的类型是all ，说明是全表扫描可以沟通是否加索引了，数据量少就无所谓了
一般来说，的保证查询至少达到rangge 级别，最好能达到ref。
system: 忽略不计
const: 表示通过索引一次就找到了，const 用于比较 primary key 或者unique 索引，因为只匹配一行数据，所以很快
常见的情况 如 where id = 10,根据id 查所以快
ref:
根据索引 查到的结果有多条，常见的情况是 创建了一个复合索引，使用时 where 后面就一个字段
用到了索引查出了多个值
eq_ref:
根据索引查到的结果就一条数据，用到了索引 查询的结果就一个值
range:
使用索引来检索给定范围的行，当使用 =、<>、>、>=、<、<=、IS NULL、<=>、BETWEEN 或者 IN 操作符，用常量比较关键字列时，则会使用 rang
index:
全索引扫描而不是全表扫描，从索引里面获取数据例如 select id fro t1,select count(*) from t1
all:
全表扫描从硬盘上读取数据，经过优化后，type 从 all 提升到了range
posssible_keys 和 key
根据这两个字段判断 1、是否使用到了索引及来判断索引是否失效2、在多个索引竞争的情况下mysql到底用了那个索引。
posssible_keys:
例如我在一个表上创建了 4个字段的复合索引，当前sql 经过mysql 优化器判断后可能会用到几个
或者一个也没用，但是可能用的索引字段，再实际查询时不一定用的上。
key:
实际使用的索引,如果 是null 表示没有使用索引或者就没有建索引
这两个一起判断
当前sql 理论上可能用到的索引是那个，实际上用的索引是那个,此时就可以进行优化了
覆盖索引:
select 后面查询的字段和建的复合索引的列个数和顺序一样，此时数据就可以从索引上取，不用全表扫描了
这时候就说查询覆盖索引，又叫覆盖索引
这里不要用select *; 注意 查询的字段 顺序 和建索引的字段顺序一致或者覆盖住。
这里显示的索引是索引的名称，不是字段
rows
根据表统计信息及索引选用情况，大致估算出找到所需的记录所需要读取的行数，也就是说，用的越少越好
ref
显示索引的那一列被使用了，如果可能的话,是一个常数。那些列或常量被用于查找索引列上的值
例如创建了一个复合索引，具体用的时候，用到了 复合索引中的一个字段A和另一个字段B，B字段是
一个常量。
例如 where col2 = 'ss' ref 显示的是 常量.
如果涉及到了join departments.department_id = employees.department_id
此时 type是 ref,ref列显示employees.department_id
extra
这个很重要 常见的显示的值有 如下的情况
using filesort: 文件排序
表示 mysql 无法利用索引直接完成排序（排序的字段不是索引字段），此时会用到缓冲空间（内存或者磁盘）来进行排序；一般出现该值，则表示 SQL 要进行优化了，它对 CPU 的消耗是比较大的 如下的sql
EXPLAIN SELECT * FROM tbl_user ORDER BY sex DESC;
sex 一般不会加索引的，如果用sex排序就会加大cpu的消耗
EXPLAIN select col1 from t1 where col1 = 'ac' order by col3
我先给t1 创建了一个复合索引 (col1,col2,col3)
这个sql 执行的时候 根据 col1来查找 但是排序的时候没有按照 col1 col2 col3 顺序来排序，导致出现useing filesort
此时就需要优化了。如果order by col2 就不会出现这样的问题了
此时sql一定要进行优化了
using temporary 使用临时表
此时sql一定要进行优化了
使用了临时表保存中间结果，Mysql 在对查询结果排序时使用临时表，常见于order by 和 group by
explain select col2 from t1 where col1 in ('ac','ab','aa') group by col2;
这里特别的注意啊，group by 后面的字段应该是 col1,col2,col3的顺序来，和order by有点区别
如果这样的sql多来几个很容易影响系统的性能
using index
出现这个就安心了，表示sql 用的不错，用到了覆盖索引 而不用回表去查询数据，性能非常不错 。
常见的sql 如下
select col1,col2 from t1; 此时 col1,col2 是建立复合索引的字段
此时通过索引就能查到数据，不用去磁盘里面找数据
3、能干什么
能看到如下的信息
1、表的读取顺序 ---- 通过id来分析
2、数据读取操作的操作类型 ---- 通过select_type来分析
3、那些索引可以使用
4、那些索引被实际使用
5、表之间的引用
6、每张表有多少行被优化器查询
根据这些信息，我们可以找出 SQL 慢的原因，并做针对性的优化。
最常见的就是提升 type的类型，避免是all
4、 总结
EXPLAIN不会告诉你关于触发器、存储过程的信息或用户自定义函数对查询的影响情况
EXPLAIN不能显示MySQL在执行查询时所作的优化工作
部分统计信息是估算的，并非精确值
8、索引优化
1、单表查询的索引优化
需求:
查询 category_id 为1 且 comments 大于1 的情况下 view 最多 的 article_id;
第一次
EXPLAIN select id,author_id from article where category_id = 1 and comments > 1 ORDER BY views desc LIMIT 1;
结果发现 没用用到索引，type 是 all , extra 是useing filesort 所以要优化了
尝试建索引
1、根据where 后面的条件 加索引
create index idx_article_ccv on article(category_id,comments,views);
category_id,comments,views 三个字段组成了复合索引
2、第二次 explain
EXPLAIN select id,author_id from article where category_id = 1 and comments > 1 ORDER BY views desc LIMIT 1;
此时发现type 是range ,解决了type是 all的问题，但是 extra是 filesort的问题 还没有解决，发现当前的索引 不
是最优的
原因:
根据btree 索引的工作原理
如果遇到相同的category_id 则再排序 comments,如果遇到相同的comments
则再排序views,当coments 字段在联合索引里处于中间位置时，因comments>1
条件是一个范围值(range)，Mysql无法利用索引再对后面的view 部分进行检索
即，rang类型查询字段后面的索引无效
3、删掉 刚才的索引
drop index idx_article_ccv on article;
4、此时 考虑 另建一个索引，用的字段是 category_id 和view
create index idx_article_cv on article(category_id,views);
5、第三次 explain
发现type是 ref extra里面没有 useing filesort 了，至此优化完毕
排序应用用到了覆盖索引，所以排序的效率最高。
2、两表查询的索引优化
两个表关联查询，索引应该加在哪里
需求:
查询所有员工的信息和所在的部门名称 这里使用左连接
left join 条件用于确定如何从右表搜索行，左边的数据一定要全部展示
所以右边是我们的关键点，右边的表一定要建立索引
rigtht join 左边的表一定要建立索引
3、三个表的关联查询索引优化
三个表通过left join 来 join 添加 索引时，跟left join时规则一样。
4、join 语句的优化
1、尽可能减少join 或者嵌套join,永远用小结果驱动大的结果集
2、保证join的字段已经加了索引
3、鸡蛋壳、鸡蛋白、鸡蛋黄 确保 内层的数据先优化
4、当无法保证被驱动表的join条件被索引且内存资源充足的情况下，不要吝啬JoinBuffer的设置
在my.cnf的配置文件中设置
5、索引应该怎么避免失效
例如建了三个字段的索引,name age pos
1、全值匹配我最爱
select * from emp where name ='';
select * from emp where name ='' and age = '';
select * from emp where name ='' and age = '' and pos = '';
这三种情况都会用到索引,其中第三种情况最好
select * from emp where age = '' and pos = '';
select * from emp where pos = '';
这两种情况没有用到索引，违背了最佳左前缀法则
select * from emp where name ='' and pos = '';
这样的情况不建议导致 我们的第二个列 pos 这个索引不起作用
---- 带头大哥不能少 不代表where 后面的字段 必须按照 name、age、pos来写，可以按照 age、pos、name 来
mysql的优化器会进行优化的，建议我们怎么建索引怎么来写
---- 中间的列不能跳过，否则 第三个列的索引就用不到了
2、不在索引上做任何的操作(计算，函数、类型转换)，会导致索引失效转向全表扫描
explain select * from emp where left(name,4) = 'july';
此时发现name 索引失效，type是 all
一般是在where 后面不要 进行计算、函数、类型转换
3、如果索引中的字段在查询中 做了范围判断 如 > < like between and ，那么该索引字段之后的索引字段就失效了
select * from emp where name ='' and age > 25 and pos = '';
此时的type 为range, name字段用上了，但是pos这个索引字段没有用上
4、减少select * 的使用
尽量使用覆盖索引，只查询索引的字段，减少select *
select name,age,pos from emp where name ='' and age > 25 and pos = '';
此时的type是ref,虽然age 后面的字段 没有起到索引的作用但是比 使用* 效果要好
5、如果对索引字段使用!= 或者<> 的时候，索引字段会失效，导致全表扫描
explain select * from emp wehre name != 'july';
type 是 all
6、如果对索引字段使用is null 或者 is not null的时候索引字段会失效，导致全表扫描
explain select * from emp wehre name is not null
type 是 all
7、like 以通配符开头(%abc..) mysql 索引失效变成全表的扫描
explain select * from employees where last_name like '%july%'
type 是 all last_name 索引字段失效
explain select * from employees where last_name like '%july'
type 是 all last_name 索引字段失效
explain select * from employees where last_name like 'july%'
type 是 range
---一般%写在右边
但是实际上呢如何解决like'%字符串%' 两边都有%时索引失效的问题呢
必须使用覆盖索引
查询的字段必须是主键+复合索引的字段，这样才能保证type是index
如果查询的字段有非复合索引的字段，那么type是all
8、varchar类型的字段不加单引号导致索引失效
mybatis 中使用#{参数} 传入会加上单引号，sql语句解析时会加上‘’
这里注意字段的类型转换
9、少用or 用它来链接会导致索引失效
这里可以考虑用union来测试
9、索引面试题总结
需求:
一个表创建了四个索引 c1,c2,c3,c4 判断下面的sql 是否用到了索引
1、explain select * from test03 where c1='' and c2 ='' and c4 = '' order by c3 ;
这里涉及到order by 使用的单路复用算法和双路复用算法
这里要特别的理解索引的两大功能 排序+查找
这里用到了c1、c2 c3的索引，只不过c3时用来对结果进行排序了，c4 没有用到
2、explain select * from test03 where c1='' and c2 ='' order by c3;
结果和1的结果一样
3、explain select * from test03 where c1='' and c2 ='' order by c4;
结果是用到了索引来查找 按照 创建索引的顺讯来的
但是在排序的时候 没有按照创建索引的顺序来的，extra 字段 导致出现了 using filesort
他俩是整体来看的，不是单独来看的
4、explain select * from test03 where c1='' and c5 ='' order by c2,c3;
结果用到了c1 这个字段的索引,c2,c3 用于排序 无 filesort 出现
5、explain select * from test03 where c1='' and c5 ='' order by c3,c2;
结果用到了c1 这个字段的索引,c2,c3 用于排序 有 filesort 出现,原因是 我们创建的索引是
c1,c2,c3,c4 但是排序的时候是c3,c2 所以出现这里要特别的注意啊
6、explain select * from test03 where c1='' and c2 ='' order by c2,c3;
这里没有什么问题，查找和排序都按照顺序来的
7、explain select * from test03 where c1='' and c2 ='' c5 = '' order by c2,c3;
这里没有什么问题，和6 一样 c5 时用来迷惑的
8、explain select * from test03 where c1='' and c2 = '' and c5 ='' order by c3,c2;
这里注意对比和5的区别,5是没有c2 出现了 filesort,而这里有c2 没有出现filesort
原因是什么呢，c2是一个常量，根据常量排序没有效果,所以按照c3来排序,
此时复合索引创建的顺序。所以没有出现这样的问题。
9、explain select * from test03 where c1='' and c4 = '' and c5 ='' group by c2,c3;
此时 c4 字段的索引没有用上，别的没有问题
10、explain select * from test03 where c1='' and c4 = '' and c5 ='' group by c3,c2;
此时 c4 字段的索引没有用上,extra字段出现了 filesort 和 temporary
这里要注意啊，group by 分组之前必排序，group by优化的方式和order by一样的
一般性的建议
对于单值索引，尽量选择针对当前query过滤性能更好的索引
在选择组合索引的时候，当前query 中过滤性最好的字段在索引字段顺序中，位置越靠前越好
在选择组合索引的时候，尽量选择可以能够包含当前query 中的where 子句中更多字段的索引
尽可能通过分析统计信息和调整query的写法来达到选择合适索引的目的
需求:
假设 inde(a,b,c)
1、explain select * from test03 where a ='' and b like 'july%' and c='';
这个sql 用到了 a b c 三个索引, type 是range
2、explain select * from test03 where a ='' and b like '%july' and c='';
这个sql 用到了 a,b和c没有用到 type 是ref
3、explain select * from test03 where a ='' and b like '%july%' and c='';
这个sql 用到了 a,b和c没有用到 type 是ref
4、explain select * from test03 where a ='' and b like 'K%july%' and c='';
这个sql 用到了 a,b和c type 是range
10、查询优化
1、永远小表驱动大表
小的数据集驱动大的数据集
这里涉及到的一般是in和exists两个关键字的使用
例如 select * from employees where id in(select id from departments)
这里就是 小表驱动大表，因为部门的数据少。
当departments 的数据必须小于 employees 时 此时用in 优于 exists
select * from A where exists(select 1 from B where B.id = A.id);
当A表的数据集小于B表的数据集时，用 exists 优于in.
注意 A表和B表的ID字段应建立索引
exists 可以理解为:将主查询的每一行的数据，在子查询中使用主查询的字段来验证，根据验证结果来决定主查询当前行的数据结果
是否显示。这里终于将exists来讲明白了
2、ORDER BY 关键字的优化
学ORDER BY 后面的字段，避免出现filesort的问题。
例如创建了两个字段的复合索引 a ,b
如下的sql 是否会出现 filesort,这里以 > 号来说明的，没有按照 = 号来说明，他两个还是有一定区别的
explain select * from tblA where a > 20 order by a,b
不会出现filesort，当我们排序的索引顺序和索引创建的顺序一致时不会出现filesort
explain select * from tblA where a > 20 order by b
会出现file sort,我们排序的索引顺序和索引创建的顺序不一致时会出现filesort，
这里排序的时候没有用到a
explain select * from tblA where a > 20 order by b,a
会出现file sort,我们排序的索引顺序和索引创建的顺序不一致时会出现filesort，
explain select * from tblA order by b
会出现file sort,我们排序的索引顺序和索引创建的顺序不一致时会出现filesort，
explain select * from tblA where b > 20 order by b
会出现file sort,我们排序的索引顺序和索引创建的顺序不一致时会出现filesort，
explain select * from tblA where b > 20 order by a
不会出现filesort，当我们排序的索引顺序和索引创建的顺序一致时不会出现filesort
explain select * from tblA order by a,b Desc;
会出现file sort,我们排序的索引顺序和索引创建的顺序不一致时会出现filesort，
这里特别的注意:我们根据字段创建索引时,默认根据字段的每一行值，维护一个升序的索引表，
如果此时
总结:
order by 在以下两种情况下extra 字段里面不会出现file sort .
key a_b_c(a,b,c)
1、单独 出现 order by 语句时,order by 语句使用索引最左前列排序
order by a
order by a,b
order by a,b,c
order by a desc,b desc,c desc
2、where 子句 与order by 字句中两个条件列组合 满足 索引最左前列时。
where a = '' order by b,c
where a = '' and b= '' order by c
where a = '' and b > '' order b,c
3、会出线filesort 的情况
order by a asc,b desc,c desc 排序不一致
where g = ‘’ order by b,c 丢失a 索引
where a = '' order by c 丢失b 索引
where a = '' order by a,d d不是索引的一部分
where a in(...) order by b,c 范围查询，丢失a,如果是in 里面是一个值 是没有问题的
这张表 就是order by 写排序 写系统里面统计分析查询模块，查的快的唯一依据。
11、键值的理解
索引的字段值
我们一般不会把原始数据集排序，而是把每条记录的键值和这条记录在数据集中的位置，按键值次序做成一个规模较小的数据集，这也就是索引表了
2、Mysql 锁的整理和理解
2.1 全局锁
锁的定义 锁是计算机协调多个进程或线程并发访问某一资源的机制。在数据库中，除传统的计算资源(CPU,RAM,I/O)的争用以外，数据也是一种供许多用户共享的资源。如何保证数据并发访问的 一致性，有效性是所有数据库必须解决的一个问题，锁冲突也是影响数据库并发访问性能的一个重要的因素。从这个角度来说，锁对数据库而言显得尤其重要，也更加复杂。 MySQL中锁的分类 按照锁的粒度分为三类 全局锁:锁定数据库中的所有表。 表级锁:每次操作锁住整张表。 行及锁:每次操作锁住当前行数据。 全局锁 具体的定义 全局锁就是对整个数据库实例加锁，加锁后整个实例就处于只读的状态，后续的DML,DDL语句 以及更新操作的事务提交语句都被阻塞。 常见的业务场景 数据库的物理备份，给数据库加一个全局锁，然后使用mysqldump的命令进行数据的导出--> xx.sql。此时数据库不能进行更新操作，可以进行查询。 加解锁的命令 flush tables with read lock; --->加全局锁 只能是read lock 没有 write lock unlock tables; --->释放全局锁 示例: 给数据库mybatis加全局读锁 flush tables with read lock; 此时在进行更新和插入时数据库报错 execute the query because you have a conflicting read lock 全局锁实现数据的备份 1、加全局锁 flush tables with read lock; 2、mysql 服务器里面执行 备份的命令 mysqldump -h localhost -uroot -proot mybatis > ./sql/mydb.sql; mysqldump -h 地址 -u 用户名 -p 密码 数据库名字 > 文件路径/xx.sql; 3、释放全局锁 unlock tables; 问题和解决办法 1、如果在主库上备份，那么在备份期间都不能执行更新,业务基本上就的停摆 2、如果在从库上更新，那么在备份期间从库不能执行主库同步过来的二进制日志(binlog),会导致主从的延迟 不加锁实现数据的备份 mysqldump --single-transaction -h localhost -uroot -proot mybatis > ./sql/mydb1.sql;
2.2 表级锁
表级锁 表级锁,每次操作锁住整张表。锁粒度大，发生锁冲突的概率最高，并发度最低。应用在MyISAM,InnoDB,等存储引擎中。 表级锁分类 表锁 元数据锁 意向锁 表锁的具体理解 表锁分类 表共享锁(read lock) 表加读锁后，当前客户端连接能读数据，不能写数据，别的数据库连接也是不能写数据，但是能读数据。 表独占锁(write lock) 当前连接加了写锁后，既能写又能读，别的连接不能读也不能写 表锁语法 加锁 lock tables 表名 read /write 释放锁 unlock tables; 示例 给score表加读锁 lock tables score read; 给表score 释放锁 unlock tables; 示例 给score表加写锁 lock tables score write; 给score表释放写锁 unlock tables; 元数据锁 元数据锁加锁过程是系统自动控制，无需显示使用(不用手动加锁)，在访问一张表的时候会自动加上。元数据锁住要作用是维护表元数据的数据一致性 ，在表上有活动事务的时候，不可以对元数据进行写入操作. 元数据的理解 元数据就是表的结构 特点: 当一个数据库客户端链接有未提交的事务时，另一个数据库连接就修改不了表结构 前提: 有事务存在,修改表结构会阻塞。 作用: 避免DML与DDL冲突，保证数据的正确性。
2.3 元数据锁的示例
. 
客户端1 开启了一个事务 ，此时另一个客户端修改表结构，此时发现 客户端 2 的新增字段在阻塞中。这就是元数据锁的作用
2.4 行锁的理解
【推荐】国内首个AI IDE，深度理解中文开发场景，立即下载体验Trae
【推荐】编程新体验，更懂你的AI，立即体验豆包MarsCode编程助手
【推荐】抖音旗下AI助手豆包，你的智能百科全书，全免费不限次数
【推荐】轻量又高性能的 SSH 工具 IShell：AI 加持，快人一步
· TypeScript + Deepseek 打造卜卦网站：技术与玄学的结合
· Manus的开源复刻OpenManus初探
· AI 智能体引爆开源社区「GitHub 热点速览」
· 三行代码完成国际化适配，妙~啊~
· .NET Core 中如何实现缓存的预热？