MYSQL 学习笔记_mycat 联合主键问题-CSDN博客

本文链接：https://blog.csdn.net/qq_16320025/article/details/105600721

b树和b+树区别：b树节点存储数据，叶子节点不相连，B+非叶子节点不存数据，存数据的叶子节点相连

MyISUM的B+树索引和Innodb的B+树索引区别前者的索引都是非聚集的末尾节点存的是数据地址，后者的主键索引是聚集索引叶子节点存完整数据

B+树索引推荐使用整形自增原因：比字符串占地少，比较快，自增有序保证了已有的页不会经常重构

AHI 自适应hash索引，innodb不支持直接指定hash索引，只能自适应为hash索引

0.隔离级别 ACID

1.索引下推

2.前缀索引(降低索引选择性) 组合索引最左匹配 where order使用组合索引时机

3.A join B on A.id=B.id原理

1关联条件存在索引mysql自动优化将有索引的表作为被关联的表，如果是主键索引则还能省去回表

2.没有索引的话，将小表缓存在内存(阈值可设置，放不下走步骤三)，每次将内存中小表的所有数据与磁盘中大表的一条数据进行比较，得到结果

3.以上都不满足则双层嵌套循环A,B一条条对比

4. limit 3 找到符合条件的三条就不在查询直接返回； select a,b,c,d,e from t order by id limit 1000000,5这种如何优化问题？参考order by的单次排序，因为大多数时候查询字段加排序字段超不过阈值所以优化器会使用单次排序，造成参与排序的单条数据占用空间过大，内存一定的情况下可排序的行数不多造成排序过慢，此时可以可以先(select id from t order by id limit 1000000,5) tmp 然后再用t表关联tmp临时表查询想要的数据就好了

5.迁移大量数据时，现将索引关掉，迁移过去之后在创建索引

6.辅助索引的范围查询时，检索引擎会权衡是直接全表扫描来的快，还是走辅助索引找到然后回表获得数据快，如果他认为是前者快，则索引失效，如果此时非得用索引可以强制使用

6.5 explain 看查询类型是不是子查询；使用的索引类型：system表里只一行、const扫描一条索引就定位了【唯一或主键索引】、ref 非唯一索引等值查询、range索引范围、index扫描所有的索引、all扫描整个表

7. order by排序

1.两次排序，先按照排序字段排序，同时排序的数目大，在通过随机io的方式去拉取对应一行的值适合limit 10000000,5的情况（排序之后取很远一页的几个数据）

2.单次排序，排序字段跟要查询的字段同时拿出来排序，排完序直接能拿到想要的行字段适合获取整体或大量数据的排序，或者limit限制的前几页数据

通过参数可以控制max_length_for_sort_data 查询字段加order by字段的加和默认1024字节可以调大牺牲空间换取时间：

8.子查询优化尽量使用关联查询，因为子查询会建立临时表，造成多余的io操作

9.用户变量使用：

1.排名问题举例

set @i :=1,@rank :=1,@beforval :='_';-- 设置累加器i，排名初始值rank，beforval用于记录前一个值用于对比默认_(因为我这个例子排序字段是个字符串，可能实际中是个分数吧，可根据自己业务修改)
SELECT 
CASE WHEN @beforval=UPDATE_by then @rank ELSE @rank:=@i END rank, -- 如果跟前一个值一样,则排名不变；如果变化了则改用累加器的值作为排名
UPDATE_by,
@beforval:=UPDATE_by,-- 记录前一个比较值
@i:=@i+1 ljq -- 累加器每次都加一
from sys_user where UPDATE_by is not null ORDER BY UPDATE_by;

执行结果：

2.因为用户变量是连接回话内有效的所以在mybatis中可以这么写

/**
	 * setTepPara 设置用户变量举例
	 * getTepPara 使用用户变量参与查询语句举例
	 * 因为是回话有效所以二者可以再同一个service方法中顺序调用来使用
	 * eg：
	 * public List<User> testGetSetTepPara() {
	 * 		userMapper.setTepPara();
	 * 		List<User> list = userMapper.getTepPara();
	 * 		return list;
	 * }
	 * */
	@Select("<script>  set @i :=1,@rank :=1,@beforval :='_'  </script>")
	void setTepPara();
	@Select("<script>  SELECT CASE WHEN @beforval=UPDATE_by then @rank ELSE @rank:=@i END rank,UPDATE_by,@beforval:=UPDATE_by,@i:=@i+1 ljq  " +
	"from sys_user where UPDATE_by is not null ORDER BY UPDATE_by; </script>")
	List<User> getTepPara();

10.分区表的一些限制（主键，或者联合主键中的部分以及唯一键必在分区算法中等）所以如果想要再有主键的表中使用其他字段分区可参考，和各种分区使用场景

11.分表

12.bin log 记录数据库操作记录，可用于数据恢复, 有三种模式行模式、语句模式、混合模式。类似redis的aof,默认是关闭的