ALTER TABLE a ADD unique INDEX aaa(b)
1.索引原理
索引是一种数据结构。在InnoDB 里面，索引类型有三种，普通索引、唯一索引（主键索引是特殊的唯一索引）、全文索引。
普通（Normal）：也叫非唯一索引，是最普通的索引，没有任何的限制。
唯一（Unique）：唯一索引要求键值不能重复。另外需要注意的是，主键索引是一种特殊的唯一索引，它还多了一个限制条件，要求键值不能为空。主键索引用primaykey创建。
全文（Fulltext）：针对比较大的数据，比如我们存放的是消息内容，有几KB的数据的这种情况，如果要解决like查询效率低的问题，可以创建全文索引。只有文本类型的字段才可以创建全文索引，比如char、varchar、text。
B+tree。B+树是一颗多路平衡查找树，所有节点称为页，页就是一个数据块，里面可以放数据（行），页是固定大小的，在InnoDB中是16kb。
在计算机中磁盘存储数据最小单元是扇区，一个扇区的大小是512字节，而文件系统（例如XFS/EXT4）他的最小单元是块，一个块的大小是4k，而对于我们的InnoDB存储引擎也有自己的最小储存单元——页（Page），一个页的大小是16K。[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-e0jNOrfO-1639825106143)(.\mysql.assets\最小存储单元.png)]
所有数据按照主键排序后，一页即一个节点，所有分页行程的双向链表数据量太大的情况，查询效率会太低，基于跳表的思想，所有的分页是一个叶子结点，非叶子节点存放的是地址。
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-8XNBwzH4-1639825106148)(mysql.assets/B+Tree.png)]
为什么B+Tree树的深度不大
通常一棵B+树可以存放多少行数据？
这里我们先假设B+树高为2，即存在一个根节点和若干个叶子节点，那么这棵B+树的存放总记录数为：根节点指针数*单个叶子节点记录行数。
上文我们已经说明单个叶子节点（页）中的记录数=16K/1K=16。（这里假设一行记录的数据大小为1k，实际上现在很多互联网业务数据记录大小通常就是1K左右）。
那么现在我们需要计算出非叶子节点能存放多少指针，其实这也很好算，我们假设主键ID为bigint类型，长度为8字节，而指针大小在InnoDB源码中设置为6字节，这样一共14字节，我们一个页中能存放多少这样的单元，其实就代表有多少指针，即（16X1024）16384/14=1170。那么可以算出一棵高度为2的B+树，能存放117016=18720条这样的数据记录。
根据同样的原理我们可以算出一个高度为3的B+树可以存放：1170x1170x16=21902400条这样的记录。所以在InnoDB中B+树高度一般为1-3层，它就能满足千万级的数据存储。在查找数据时一次页的查找代表一次IO，所以通过主键索引查询通常只需要1-3次IO操作即可查找到数据。
2.聚簇索引
聚集------数据聚集
1 主键索引
InnoDB表数据文件本身就是一个索引结构，树的叶子节点data域保存了完整的数据记录（行数据），这种索引叫做聚集索引。
主键索引 B+tree 叶子节点有数据
name 建索引，唯一索引，普通索引等 —b+tree，
辅助索引：除了主键都是辅助，区别在于叶子节点没有保存数据，保存得是主键
innodb 主键是聚簇索引 myiasm非聚簇索引
3.回表
在非聚集索引的查找过程是：
先在非聚集索引树找到指定key，同时能得到主键key，拿着主键key到聚集索引里找到对应的行。在非聚集索引中拿着主键key到聚集索引找行的过程称为回表
尽量 避免select * from …
4.%在前
对name列创建索引，
排序—字符串，先以第一个字母排序，在以第二个字母，。。。。。
abc acd baa
select name from xxx where name like ‘%zhang’ 能不能用索引?
不可以，----
www.cdqf.com
www.qq.com
select * from xxx where url like ‘%.com’
反过来存
com.qq.www
com.cdqf.www
select * from xxx where url like ‘com.%’
实在不行就用es
5.最左原则
多个列一起建索引
select * from xxx where a=xx and b=xxx-------a要建索引 b要建索引 2个索引
索引数据结构在空间中保存，一定会占空间，空间换时间
index(b,c,d)
先只对b这一个列来说：排序—字符串，先以第一个字母排序，在以第二个字母，。。。。。
bcd三个列 先以第一列排序 再以第二列，在第三列
EXPLAIN SELECT * FROM a WHERE c=‘e’ AND d=‘c’ 用不到索引
6.其它原因
隐式转换：字符串你不加引号 name=zhangsan
select * from a where id-1>1:用不到索引
select * from a where id>1+1
不要再索引列做计算，函数substr(name,2,5)
尽量使用覆盖索引：不用回表
当然即使我们都遵守得这些规则，mysql用不用索引，还得看查询优化器得计算成本
最终还是mysql 查询优化器：自己选
7.成本计算
https://blog.csdn.net/cblstc/article/details/117413036
https://www.cnblogs.com/zhuwenjoyce/p/14968183.html
写了sql语句----生成解析树-----分析sql语句----预估----
SHOW TABLE STATUS like 'xxx'
mysql把区间成本，页成本看成1.0
每次回表1.0
cpu0.2
确定走不走索引
强制
force index(ctime):索引
EXPLAIN FORMAT=json SELECT * FROM salaries WHERE salary>100000
EXPLAIN SELECT * FROM salaries WHERE salary>100000
ALTER TABLE salaries ADD INDEX aaa(salary)
SELECT COUNT(*) FROM salaries
8.慢查询定位/慢sql
slow_query_log = ON #开启慢查询
slow_query_log_file =D://my_log.log #日志位置
long_query_time =1 #慢查询实际 这儿超过一秒就被认为是慢查询，mysql默认10秒
增删改查 超过1秒就是慢查询
druid中定位
# 通过connectProperties属性来打开mergeSql功能；慢SQL记录 慢查询：增删改查 慢查询
spring.datasource.druid.connection-properties=druid.stat.mergeSql=true;
spring.datasource.druid.filter.stat.slow-sql-millis=1
9.explain分析sql
https://segmentfault.com/a/1190000008131735
10.视图触发器存储过程
11.存储引擎
3.1.1MyISAM
Mysql 5.5版本之前的默认的存储引擎
1.不支持事务、不支持外键
2.只支持表级锁(后面介绍)
3.没有事务日志，故障恢复数据较麻烦
4.分区存放文件，平均分配IO，不用花费资源去处理事务，效率较高
3.1.2InnoDB
1.支持事务、支持外键
2.支持行级锁与表级锁
3.花费资源去处理事务，效率比不上MyISAM
4.有事务日志，恢复数据较方便
12.mysql得查询优化器自带优化
https://blog.csdn.net/weixin_39160689/article/details/111995389?spm=1001.2014.3001.5501
等价变换策略
A:5=5 and a>5 改成 a > 5 去除恒成立条件
a < b and a=5 改成b>5 and a=5
基于联合索引，调整条件位置等
优化count、min、max等函数
InnoDB引擎min函数只需要找索引最左边
InnoDB引擎max函数只需要找索引最右边
MyISAM引擎count(*)，不需要计算，直接返回
提前终止查询
使用了limit查询，获取limit所需的数据，就不在继续遍历后面数据
in的优化
MySQL对in查询，会先进行排序，再采用二分法查找数据。比如where id in (2,1,3)，变
成 in (1,2,3)
B:join优化：inner join/join 自动优化小表驱动大表，left right没法优化
select from a join b 10万个 20万--40万
select from b join a 1000个 2000-4000
a表大，b表小，基于主键索引，每条数据查询的IO次数2-4
13.join优化
select * from a join b on a.id=b.id where a.id>1000
先扫描a表，拿出所有id 循环跟b表比较
A数据页---拿出一条 去比较一条
B数据页----
mysql join buffer（缓冲区）12345全部那出来放在缓冲区----根据情况调大
a表1000条 b 10w条
小表：循环次数 驱动大表
mysql只能优化 inner join 就是join
select * from b left join a on a.id=b.id where a.id>1000 mysql不会优化
先join 再查询is null
for(Object i:list){list:a表拿出得数据
for(Object j:list2){
if(i==j)
}
}
14.分库分表读写分离
数据量太大了，再怎么优化都没用了
pxc集群：提高查询效率(高并发)，高可用
强一致性：写入任何一台，都会等到最终同步数据---内部起线程(中继日志)----同步完了才能访问
A B C D E
任何一台都可以读写，vip----haproxy做负载均衡（nginx做http得负载均衡，haproxy做tcp）
java不需要做什么----还是那个链接 ----haproxy
读写分离：高并发
1主2从-----主服务器负责写
从服务器负责读
2从做了负载均衡
弱一致性---主写入得时候，在从服务器同步期间依然可用查询
1.mycat中间件(配置)，java层面不用做 链接mycat
需要在java做事情：2.sharding-jdbc 3.自己写
数据量大到一个表放不下:
分库分表:mycat sharding-jdbc
横向 订单----
纵向
15.回答
mysql索引
mysql优化
首先mysql优化器-----
实际业务也要自己创建索引------为什么能提高效率
举例：失效% 最左
—慢查询定位----explain
16.mysql事务原理
隔离级别：
读未提交（read-uncommitted）:脏读，读取到未提交得事务
不可重复读（read-committed） A事务读取班级表数据 B事务修改了班级表 A事务再次读取 不一致
可重复读（repeatable-read） 避免不可重复度 幻读：A事务修改所有班级的地址为肖家河 B事务增加了一条数据
A事务查看发现有一条没改，mvcc触发当前读
串行化（serializable）：锁住，A事务必须做完了 B事务才能操作
https://blog.csdn.net/m0_37752084/article/details/79942196
RC RR解决机制：锁 和MVCC。并发情况：读读（不会有安全问题）、读写（MVCC）、写写（加锁）
MVCC ：多版本并发控制，解决并发场景的读写问题
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-3l5Gn1Kj-1639825106150)(mysql.assets/image-20210826200033932.png)]
快照读：select * from table where id=1;
当前读：select * from table where id=1 for update;
MVCC的实现原理有三部分实现：mysql数据库隐藏字段、undo日志、readview
主要隐藏字段：
DB_TRX_ID：创建或最近修改该记录的事务ID
**DB_ROLL_PTR：回滚指针，**指向这条记录的上一个版本
**DB_ROW_ID：**隐藏主键，默认6字节（如果数据表没有主键，InnoDB会自动以DB_ROW_ID产生一个聚簇索引）
spring事务传播机制：
//REQUIRED:增删改
//Propagation.REQUIRED:test方法需要事务 选择REQUIRED 如果调用我的时候已经有事务 加入事务 否则就新建
//REQUIRES_NEW：不管有没有事务 开启一个新的 把原事务挂起 ,原事务抛异常 新事务不会回滚
//NOT_SUPPORTED:test1有事务 调用test方法 test会把事务挂起 自己非事务运行
//MANDATORY: 当前方法必须要在有事务方法中运行，不然就抛出异常；如果有事务，就加入当前事务。
//SUPPORTS:有事务 就加进去 没有就自己玩 查询
//nested:test1事务A test事务B A称为外部事务 B内部嵌套事务
NEVER:
// A事务回滚B事务跟着回滚 B事务回滚A事务不影响
有异常就回滚：自定义哪些异常不回滚
transactionManager
ACID:
原子性：一个事务多个数据库操作，要么同时成功 要么同时失败 -----undo日志保证
最终 undo日志会形成一个链表，链首是最新的旧记录，链尾是最早的旧记录。
如果是insert操作，则是在提交之后就删除，delete/update操作则在回滚不涉及该日志或快照读不使用时才删除
隔离性：事务之间相互隔离，隔离级别-----mvcc
持久性：一旦事务提交或者回滚对数据的操作是永久的，哪怕停电依然提交回滚 ---redo日志保证（两阶段提交）
一致性：因为上面三个特性----保证一致性,约束一致性 业务一致性
update student set name=zhangsan where id=1----undo(速度快)
mvcc:保证了事务隔离性 mvcc还可用读写共享,虽然提高了效率 但是数据短暂不一致
1提交 2 3 trx_list[2,3]
buffer 1 ---事务读取1《2，3 放入buffer这个值 是最近事务 4----一行数据
trx_list[1,2] 3提交了 4---读取到3
事务4开启的之前 先要读取该读的值----buffer
https://blog.csdn.net/weixin_39160689/article/details/111995389?spm=1001.2014.3001.5501
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-cRpgXZNS-1639825106156)(mysql.assets/image-20210826202312431.png)]
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-DXHL8XMz-1639825106163)(mysql.assets/image-20210826202538516.png)]
mvcc:隔离性，读写互斥
A事务来修改这个记录----------1 zhsangn lisi
锁：A事务加了锁，另外的事务没法读取。
间隙锁：RR隔离级别下。必须要索引才有效,减少幻读
1 zhangsan 10
5 lisi 20
8 wangwu 30
id主键 age普通索引
（-无穷，1] (1,5](5,8][8,正无穷]
select * from student where id=3 for update; ---(1,5] insert into 4
select * from student where id=5 for update;根据主键查询退化为行级锁 id=5
17.mysql三大日志
redo undo binary-log(集群同步)
MySQL中有六种日志文件，
分别是：重做日志（redo log）、回滚日志（undo log）、二进制日志（binlog）、错误日志（errorlog）、慢查询日志（slow query log）、一般查询日志（general log），中继日志（relay log）。
3万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
