Mysql学习笔记
1.用来存储文本的字段：
（1）char 和 varchar 的区别 ： char是固定长度，如果长度不够，会在后面补上空格。取出时使用trim() 去除空格。 varchar长度可变， 需要额外1-2byte去存当前字节的长度。
（2）存大文本：文本类型text 二进制：blob.
use database ; show tables ; show tables from database; select database(); desc table;
查询版本： select version(); mysql -version 或者 mysql -V mysql不区分大小写， 关键字建议大写
DQL:
基本查询、：
（1）列名与关键字重复时，使用`desc` 标识
（2）查询常量值：select 100 ; select 'join'; 查询表达式：select 100*98; 查询函数： select version();
(3) 给字段起别名：使用as或者空格
（4）去重：distinct
(5) "+" 的作用： 不能用来连接字符串，只能用来当运算符。 如果select '123'+90; → 试图将字符转换成数字，进行相加。 如果失败会认为字符代表着0. null + 任何 都为null
(6)CONCAT('1','b','c')拼接字符串。 其中如果有null , 结果为null .
条件查询、:条件运算符 ： > >= < <= != <> 逻辑运算符：&&，｜｜，！ 。 模糊查询：like , between and , in , is null .
like 通配符：_ 任一个 %： 0个或者多个。 如果查询第二个是‘_’ , 需要使用转译。 like '_\_%' ESCAPE $ 或者通过ESCAPE指定转译字符代替‘\’
between : 包含临界值，不要调换顺序。
in : in (5,6,7);
is null : != 和 = 都不能判断null
<=> 安全等于 ： 可以判断null值 也可以 判断数值。 但是可读性差。
select department ,salary*12*(1+IFNULL(serial , 0) ) as 奖金 –> 如果serial是null 就变为0；
排序查询、order by 列名 desc 降序 默认是asc 升序。 可以按照别名排序。
常见函数、：
（1）单行函数：
字符 ： 1。 length 获取字节。 2. 拼接 concat 3. 大小写：upper和lower 4.截取 substr和substring 索引从1开始。 substr("你好不是哈喽"，3) →"不是哈喽" 5.instr("你好不是哈喽","不是") →3 (返回在大字符中的开始索引) 7.trim() → 去掉空格。 8.Lpad ( "你好"，10，“*”) -> 指定了一共多长，以及长度不够时用什么来填充。
数学：1. round() 四舍五入 2 cell()向上取整 3floor()向下取整 4. truncate(1.7999,1) → 1.7 阶段为一个小数。 5.mod(a,b);
日期:now（） CURDATE() CURTIME()
concat(), length(), ifnull() ;
（2）分组函数： 又叫做聚合函数，统计函数。 sum() avg() max() min() count() 计算个数 忽略null值。
在分组的基础之上可以进行过滤筛选： having group_function(column) > 2
连接查询、：
（1）内连接，：等值连接，非等值，自连接 内连接 与 外连接的 区别 ？
（2）sql99 内连接 外连接，左外连接，右外连接，全外连接。
（3）交叉连接。
子查询、
分类：
（1）按照出现的位置：
select ： 支持标量子查询
from： 支持表子查询
where或者having后面： 支持标量子查询， 支持列子查询， 也支持行子查询。
exists后面
（2）按照结果集的行列数不同：
标量子查询：结果集中只有一行一列
列子查询： 结果集中只有一列多行
行子查询： 结果集中只有一行多列
表子查询： 结果集一般为多行多列
标量子查询：
列子查询： 使用 in not in, any some , all
select 后面只支持标量， 一行一列。
分页查询、： limit 0, 5
union连接查询
删除语句： truncate drop 和 delete 的区别 ？
delete 可以删除部分数据（不会释放内存，所以只是不显示了 ？ ），可以回滚。
truncate 删除整个表中的数据
drop ,删除表定义等信息。
DML：插入、修改、删除
DDL：建库、建表、库和表的管理。
TCL:事务控制语言
视图： 保存了sql逻辑，但是不保存查询结果。
数据库优化思路：
（1）、根据服务层面：配置mysql性能优化参数；
（2）、从系统层面增强mysql的性能：优化数据表结构、字段类型、字段索引、分表，分库、读写分离等等。
（3）、从数据库层面增强性能：优化SQL语句，合理使用字段索引。
（4）、从代码层面增强性能：使用缓存和NoSQL数据库方式存储，如MongoDB/Memcached/Redis来缓解高并发下数据库查询的压力。
（5）、减少数据库操作次数，尽量使用数据库访问驱动的批处理方法。
（6）、不常使用的数据迁移备份，避免每次都在海量数据中去检索。
（7）、提升数据库服务器硬件配置，或者搭建数据库集群。
存储过程和函数
流程控制活动
索引：
建立表时使用 index 或者key或者unique(唯一索引). key idx_user_name("name"); 单值索引 与 复合索引
非建表时，create index idx_user_name on user(name) ; create index idx_user_name_age on user(name , age);
alter table add index idx_name on name
drop index on table
覆盖索引，select的数据列只用从索引中就能够取得，不必读取数据行，换句话说查询列要被所建的索引覆盖
-
普通索引：仅加速查询
-
唯一索引：加速查询 + 列值唯一（可以有null）
-
主键索引：加速查询 + 列值唯一（不可以有null）+ 表中只有一个
-
组合索引：多列值组成一个索引，专门用于组合搜索，其效率大于索引合并
-
全文索引：对文本的内容进行分词，进行搜索
Explain能干嘛？
id: id相同执行顺序由上到下。
id: id 不同数值大的优先被执行。 第三种是相同id和不相同id同时存在。
2. select_type : simple ,primary , subquery , derived, union , union result .
3.type类型
system是只有一行记录，const 是根据索引直接一次就可以查到该条记录。
eq_ref : 和const一样，也是只匹配一条记录。 不同点（猜） ： eq_ref是在连接表的时候发生，根据外键到外键所对应的表中去查找， 刚好找到一条记录。
ref : 根据一个索引来查询 = 某值时。 ， 返回了多条记录。
range : 当where语句中出现了between in > < 等范围查询时。
index :从索引表中 读取了全表
all： 从磁盘中读取了全表。
4. possible key (理论上)和 key（实际上） .
5.key_len : 同样的查询结果下 数值越小越好。
6.ref : 索引引用的值。 库.表.字段。
7.rows : 大致估计出找到所需记录需要扫描的行数。
8.Extra ： 包含一些十分重要的信息。
使用group by时注意使用索引：
using index 覆盖索引。
- 如果建立索引category_id, comments, views ,, 如果category_id =1 and comments =22 order by views desc 这样会使用索引
category_id =1 and comments 》 22 order by views desc 这样在comments索引就中断了。 如果必须使用’>‘来查询。 → 应该改为将comments从索引中去掉，这样索引不会中断。 索引type可以保证ref.using filesort 也消失了。
2. 左连接时要给右表加索引。 三表时给右边两个加。 小表驱动大表 ？
3.索引会失
（4）使用范围后，右边的列不能再索引。
（5）varchar 要加单引号
小表驱动大表。 连接两个表时。
exists 替代 in.
exists如何使用： exists翻译为， 如果自查询找到则提交。 Exists首先执行外层循环， 然后执行内层循环， 与in相反。
如果外层id有100行， 内层有1000行数据， 用id去遍历1000次拿到一次结果。 100 * 1000
orderBy 默认是升序 。
group by: 先排序 后分组，按照最佳左前缀原则。
show profiles;查看结果 ； show profile cpu block io for query 10; 执行 show profiles 查询最近的 15条sql
全局查询日志。
（1）set global general_log=1, 只要开启会记录所有执行过的sql , 一般用与测试环境。
（2）set global log_output='TABLE'
可以根据这张表中的sql来复现问题。
Mysql 中redo log undo log 与 binlog 有什么区别 ？
https://blog.csdn.net/u010002184/article/details/88526708 关于redo undo 日志
undo log :事务开始之前，根据数据库当前版本生成日志
redo log : 在事务执行过程中就会记录到磁盘中， 开始写入到磁盘中。 是物理日志， 记录的是事务层面的，保证了数据库的持久性。
binlog : 在事务提交之后开始向binlog中写， 开始写到磁盘中。 是逻辑日志，记录的是数据库层面。 还原数据根据redo log 更快。
数据库中redo log 与 binlog的两阶段提交：
如果去更改一条数据， 不会立刻写到磁盘，先写到缓存，等量够了再统一写到数据磁盘中 ？
为什么B树 改为 B+树。 → 页内存是16k, 节点上不存数据，每页能存更多的索引地址， 这样减少了层数。
为什么MyIsam 读取的速度要快于 InnoDB
InnoDB存储引擎中的索引：
1.哈希索引：自适应，认为创建不了
2.B+树索引：平常创建的都是B+树索引 ，， B+树索引只能找到数据所在的页，把页读到内存中，通过二分查找找到对应的完整数据行。
（1）聚集索引：叶子节点存放的是完整的数据行。
如果创建了一个联合索引 （b, c），同时a是主键索引的话 , b,c这棵索引树是辅助索引， 查询出的主键id需要回表操作才可以
问题：主键一定是聚集索引吗 ？ 答：不一定。 在InnoDB存储引擎会默认主键是聚集索引( 但是在创建主键时可以指定为非聚集索引（todo 这个在mysql中可以吗- 》 不可以）)，没有主键，则唯一非空索引是聚集索引。都没有，会自动以（隐藏）一列创建一个聚集索引（6字节）。
innoDB索引文件与数据文件在一起。 如果通过主键直接查询，比较快。 如果先从辅助索引树中查询主键，再到聚集索引中查找对应的数据，这样就二次查询，没有myisam快。
在MYISAM中主键不是聚集索引。 myisam索引文件与数据文件分开存，通过索引数的叶子节点找到对应的数据文件的物理地址，之后在文件中找到相应的数据。
在sql server中，在创建主键时可以指定是不是聚集索引，默认是聚集索引。
#一张表中只能有一个聚集索引，create cluster index ids_id(id);
（2）非聚集索引（辅助索引）：叶子节点存放 包含了键值和一个书签，书签告诉哪里可以找到索引对应的数据行。（从辅助索引中找到聚集索引的位置，再拿着键值在聚集索引中找到对应的完整数据行）
(3)覆盖索引：查询的列建立好了索引，从非聚集索引中就可以拿到结果，不需要再到聚集索引中查找完整的行，查询的比较快。 比如查询b 和 c 在辅助索引树中就可以拿到值， 不需要回表操作。
3.全文索引：
锁：
在数据库中有3张表用来查看当前的锁定情况; 测试并发时 set autocommit = 0 , 先置为手动提交事务。
@@innodb_lock_wait_timeout 参数 等待超时时间。
@@innodb_rollback_on_timeout 参数 设定是否回滚。 在一次会话中，第一条插入成功，插入第二条时等待锁超时了，这个时候第一条要不要回滚。 （默认时off,不会回滚）
select * from information_schema.innodb_trx 具体的事务信息
select * from inforamtion_schema.innodb_locks 具体的锁信息
select * from information_schema_lock_waits 可以看出哪个编号的锁和事务，阻塞了哪个事务和锁
mvcc: select @@tx_isolation 查看当前的事务隔离级别， 只有在read_commit 和 repeatable_read 事务级别下才有mvcc
每次针对这行记录进行操作sql都会在undo中生成一个历史版本。 select读取的是哪一个快照需要根据当前的事务隔离级别来判断
多版本并发控制：每次进入一个事务就是一个版本，当commit之后，再次进入事务才是下一个版本。
读取的数据总是历史版本，但是在不同隔离级别下表现不同，在read_commit下能够读到最新的版本。 而在repeatable下只能读到历史版本。
幻读: 如果select * from t where a > 2 , 如果此时只能查到一条记录5， 没有提交事务。 此时另一个线程插入了一条4。 这时同样的语句查出的结果变为，4和5。 同样的查询语句查询出的结果却不一样是幻读。
如何解决幻读：在READ_COMMIT隔离级别下是无法解决幻读现象的，因为这种隔离级别下使用的行锁。 在repeatable_read隔离级别下可以解决幻读现象，因为这种级别下使用的锁是next-key lock
next-key lock: 对于唯一索引使用的是行锁，对于非唯一索引会锁定前面的一段范围，同时还会加上下一个键值的一段范围。 （这种给范围加锁 叫做 间隙锁 gap lock）
使用锁限定了不同的事务不会同时操作一条记录：但是可能带来3种问题 ？
1.脏读： 读到未提交的数据。 当事务隔离级别为read_unconmmitted时
2不可重复读（幻读）： 看上面幻读解决办法。
3.丢失更新：比如并发下同时查询出10000，一个减1，一个减9000，如果都能成功会出问题。 使用for update.
默认是runtimeException异常才会回滚。 在括号中加入 rollbackFor = Exception.class 则什么异常都会回滚。:
InnoDB事务隔离级别是如何来实现的: ru, rm , rr, 串行化， 4种隔离级别。 利用MVCC + 锁来实现。
隔离级别 必须选择 rm或者 rr, 当串行化时，通过锁来实现数据隔离， 但是事务不能并发，不存在事务隔离。
当选择ru事务隔离级别， 存在脏读问题， 能够读到还没有提交的数据， 因此不能保证事务的隔离级别。
在rm 和 rr 隔离级别下，利用MVCC多行版本并发控制来实现事务隔离级别。 在rm级别下 能读到最新提交的记录。 在rr级别下 读到的一直是最初读到的那个历史版本数据
mvcc的实现： 通过undo日志中的版本链条 + readView来实现。 readView中维护了一个未提交的事务id的数组， 通过readView判断机制一个一个从undo版本链的开始判断， 当前能够读取到哪一条记录。
rm级别下， 每有一个事务提交， readView 都会发生改变。 rr级别下， 即使有事务提交， readView,也不会发生改变。
mysql innodb存储引擎行锁： 分为共享锁 和 排他锁
1.select语句加锁吗？ 这个需要看是使用当前读（for update, lock in share mode, update, delete, insert） 还是mvcc快照读 ， 在 rc 和 rr级别下， 不会加锁， 读取的是快照。
2.主键索引 ， 唯一索引， 非唯一索引， 不使用索引。
3.特殊点。 如果使用非唯一索引加锁， 隔离级别是rr, 会使用间隙锁， 将前后两个返回加上锁， 防止幻读。 主键索引和唯一索引因为能够确定唯一一条记录， 因此只锁定当前行。
4.不使用索引的话， 会全表扫描， 所有记录都会加锁， 但是不符合条件的记录加锁后会很快释放掉。
5， 索引只会锁定满足条件的， 但是在server层回表过滤的会锁定所有的记录 ？
浙公网安备 33010602011771号