Mysql基础2

索引

N叉搜索树（减少树的高度，减少IO，Mysql用的，也叫B+树，平衡搜索树）

一个树的叉为N个，N是多少：和数据页有关（一次读取一个数据页），一个数据页16k能放多少索引值有关

Mysql 用between 还没有用到叶子之间的链表，还是从上到下一个一个搜索，因为要照顾到所有搜索引擎，有的没有链表

主键ID

也叫聚簇索引

k值索引

二级索引、辅助索引

先进行普通索引查询，搜索k索引树，得到主键ID，在用ID索引树搜索一次，称为回表（就是在普通索引上查一次，查到主键id，然后再用主键id去查数据）

辅助索引的叶子里依然有主键ID

联合索引（覆盖索引，不用回表）

把经常查到列索引建在一起，索引会连在一起，比较大小也是比较连在一起的列（比如Tom15和Tom20,就会比较后面的15和20，并且建立索引时候，数字需要转化成字符串）

建立了name，age的联合索引，select name，age from 就不用回表查主键树的数据了

只查询联合索引的其中一个，或有通配符，就相当于全表扫描了，联合索引就没用了

最左原则

只有联合索引的最左边的列在条件里，后面的条件才能用到索引，不然索引就没用	

硬解析（Mysql）、软解析（Oracle都有）

区别：有无优化器-执行计划，没有就直接给CPU硬解析，Mysql不缓存执行计划

Innodb以聚簇索引方式存储为聚簇表，不要求非空且唯一列，没有会自动添加一个隐藏的

Innodb buffer pool以LRU链表方式存储数据：近期最少使用链表

Innodb buffer pool 缓存数据和索引

redo的commit，参数为1:commit一次，刷一次到日志；0:1s刷一次到日志；2:commit一次刷到操作系统缓冲区，1s再提交一次

每个用户都会有自己的join、sort、multi range（MRR）缓存

char_length：直接数多少个字符
length：多少个字符*utf8的字节数/字符

user（）返回当前用户，显示当前登陆的用户名与它对应的host
current_user（） 找出当前登陆用户是用user表中的哪一个

curdate（）：当前日期
date：一个类型

统计信息

存在.frm里

收集统计信息：analyze table test；
看统计信息：show index from teset；

普通索引比唯一索引多做了一次内存搜索和判断，性能相差微乎其微

change buffer（innodb buffer里面）：

把sql要做的修改都放进去，满了就统一进行操作，把操作统计下，同一个磁盘的一起操作，节省磁头的不断移动次数，如果列上有唯一索引就立即操作，change buffer就没用了，普通索引可以用，change buffer也不适用立即写立即用，唯一索引不可以使用change buffer。

执行过程的优化

联结表时候，mysql会自动把有索引的放到驱动表上，join前面的要做全表扫描，放到内存，走索引就只扫描一行数据，索引会有数据结构。给每个用户分一个join buffer，放到内存，缓存驱动表。

联结的表都没有索引，那就是N*M的扫描量

需知名词

表联结方式：性能：bka-》nlj-》bni-〉nlj
index NLJ 有索引
block nested-loop join（BNI）驱动表缓存到内存
Simple NLJ 没索引
BKA	NLJ+MRR

bnl和bka会用到join buffer（驱动表缓存到内存）

sql强制走索引：from 表 force（索引名称）

！=
is null
is not null 都不走索引

MRR
用读辅助索引扫描时，先把辅助索引连带着的主键id存起来，按id排序，最后一起读一页一页的，这样存在于一个页上的id可以一次被读取出来，减少io，参数是read_rnd_buffer_size，set optimizer_switch=‘mrr=on，mrr_cost_based=on’，优化的是二级扫描的慢扫描。id是存到join buffer的

使用bka要打开batch_based_space和mrr，using batched key access就是使用了bka

Mysql函数

字符串函数

char_length：字符：获得整个字符串多长，数出来多长就多长
length：字节：中文占两个，英文一个，unicode：2
concat：连接函数
concat_ws：用第一个字符串连接后面的所有
left：取左取几个
right：从右边取几个

substring_index（1，2，3），用2位置做分隔，3位置是1就截取2位置前面所有，-1就截取后面所有，正负理解为正着数，反着数
trim（‘x’ from ‘xxxxabcxxxx’）去掉所有x

时间函数
now（）：YYYY-MM-DD
time：HH:MM:SS
datetime：YYYY-MM-DD HH:MM:SS 8位
date_add（‘’，interval 1 day），增加一天

round（）四舍五入
truncate（）不考虑四舍五入就是截断
ceiling/ceil 获取大于其的最小整数

select current_user 返回创建用户时候，记录在数据库表user表里的记录
select user会返回当前用户，返回的ip会是真实ip

group_concat

select a，group_concat （name） from x group by a；按a聚合并且把a的name显示出来

执行计划
sql前面加explain：打出执行计划

完全复制一个表和数据

create table a as select * from b；

null和任何字符做concat都是空

！= ，not ，is null， %放在最前面的匹配，不符合最左原则都不会走索引

锁
全局锁，用的少
表锁
行锁

元数据锁MDL：增删改查时候不能改表结构

lock table 和mdl会锁表

排序

每个用户来了都会给分配一个sort buffer，256k，用的快速排序，如果是10g的数据，都是一个个小的先排序好放在磁盘，用堆排序对所有的排好。

统计信息 analyze table a
查看统计信息 show index from a
change buffer ，mrr是innodb特有

innodb特点
最左原则
二级索引要回表
支持索引扫描

innodb的索引
聚簇索引
唯一
普通
前缀：截取前几位作为索引

唯一索引和普通索引的区别：
只有普通索引能用change buffer

普通索引适合频繁写入，但不常读取

看执行计划explain里走不走索引：走：type：ref，range

key-len：
int-4，如果允许空值就加一位
char（20）-20
varchar（20）-22，变长占2位
datetime-8
date-3

varchar（20）-utf8：62
varchar（20）-latin1:22 变长都占2位

二级索引的范围查询-MRR

bka和bnl会用join buffer

执行计划的order by：using filesort只说明用了sort buffer

优化：order by后面加索引，注意select 后面的对应

合并

union all：合并不去重
union：去重合并：temperary buffer ，临时表空间

group by mysql会自动排序，不用就order by null，耗费的是cpu

temporary buffer：group by，distinct，union会用到，比较然后去重

主从复制
1-3
3个从库：读写分离，备份，什么都不干（用于切换成故障的主库）

binlogcache：每个用户的所有对数据库的log
commit后会把数据持久化：进入binlog，以各个用户的commit顺序记录，是主从复制的关键
进入binlog的三种办法（本来是1秒落一次）：sync_binlog=1，commit一次落一次，0:1s一次，大于2的任意次N，commitN次才落盘。
sync_binlog落完传到从库是写到relaylog，再读到从库里
sync_binlog和innodb_flush_log_at_trx_commit参数是两个commit落盘的保障，俗称双1模式，就是最安全的模式。
主库备份到从库的io时间无法控制，如果要求写入即读，只能才用单实例，主库读主库写。

半同步复制：mysql5.6后推出
从库收到binlog后，才给用户返回成功，此时binlog和redo日志都有记录，防止主从复制数据丢失。

MHA架构：
主从之外新增加一个检测的设备，主库挂了，设备把离主库最近的从库的数据同步到其他所有从库，就是一个自动切换的功能。

LVS+keepalived：双主，互相检测心跳，用一个虚拟vip来切换路由地址。

分布式：
PXC:多个数据库同时工作，一个有修改，把log传到其他的就算成功，而不是写入才算成功，不然就延迟太长了，无法及时读取，还有个点就是乐观锁：一个做了修改，乐观的认为别的数据库都没修改，一旦冲突第一个修改的不动，后面修改的数据库倒霉。

mysql数据库只能存大概5000万条，如果超过怎么办？
比如是快递信息，就可以加入地址，loc=‘北京’这样的，利用中间件解析成不同的路由放入不同的数据库，限定区域，中间件：mycat（ali开源）DBLE（乐普生）

分布式事务不要在不同数据库（分片）上做，不能保证都成功，破坏事务的原子性，要么都成功要么都失败。事务不要在多个节点上执行。

查询和修改不要跨分片（也就是不同节点或者数据库）做。

question

辅助索引？
统计信息？

mysql 34

牛客做题

外模式/模式映像保证了数据库系统中的数据能具有较高的逻辑独立性：数据和应用程序的独立
模式/内模式映像保证了数据库系统中的数据具有较高的物理独立性：数据库存储结构的变化不会造成影响

索引：
	大大加快数据的检索速度，这也是创建索引的最主要的原因；
	加速表和表之间的连接；举例：外键
	在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间；
	通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性；
	

关系模型中，一个关键字是 可由一个或多个其值能惟一标识该关系模式中任何元组的属性组成

null是一种状态，不能用=来判断，只能用is或is not

范式
1范式，原子性，每一属性都是不可分隔的；2范式，无非主属性对码的部分函数依赖；第3范式，无非主属性对码的传递函数依赖；BCNF，无主属性对码的部分函数依赖和传递函数依赖。
对于各种范式之间的关系如下：
5NF⊂ 4NF⊂ BCNF ⊂3NF ⊂ 2NF⊂ 1NF（包含于），1范式的范围最大

在关系模型的概念中，一个关系就是一张二维表，每个关系有一个关系名。在Visual FoxPro中，一个关系存储为一个文件，文件名扩展名为.dbf，称为“表”。关系数据库管理系统所管理的关系是若干二维表。

关系数据库管理系统所管理的关系是若干二维表

函数依赖
如Y函数依赖X，也就是X可以推出Y，Y=f（X），官方记法：X→ Y，一对一就是A<->B

数据库权限给予
to  是给某个用户赋权，from 是收回某个用户的权限
grant to
revoke from
Grant和Revoke可在几个层次上控制访问权限：
1、整个服务器： 使用GRANT ALL 和REVOKE ALL；
2、整个数据库：使用ON database.*；
3、特定的表：使用ON database.table;

数据库系统的三级模式结构是指数据库系统由外模式、模式和内模式3级构成。

外模式也称子模式或用户模式，它是数据库用户(包括应用程序员和最终用户)能够看见和使用的局部数据的逻辑结构和特征的描述，是数据库用户的数据视图，是与某一应用有关的数据的逻辑表示。
模式也称逻辑模式、概念模式，是数据库中全体数据的逻辑结构和特征的描述，是所有用户的公共数据视图。它是数据库模式结构的中间层，既不涉及数据的物理存储细节和硬件环境，也与具体的应用程序、开发工具和语言无关。
内模式也称存储模式，它是数据物理结构和存储方式的描述，是数据在数据库内部的表示方法。
数据库管理系统在三级模式之间提供了以下两层映像来保证数据库系统中的数据具有较高的逻辑独立性和物理独立性。
外模式/模式映像：当模式改变时，由数据库管理员对各个外模式/模式的映像做相应的改变，可以使外模式保持不变。应用程序是依据数据的外模式编写的，从而应用程序不必修改，保证了数据与程序的逻辑独立性，简称数据逻辑独立性。
模式/内模式映像：当数据库的存储结构改变时，由数据库管理员对模式/内模式映像做相应的改变，可以使模式保持不变，从而应用程序也不必改变，保证了数据与程序的物理独立性，简称数据物理独立性。

数据库设计过程主要包括需求分析、概念结构设计、逻辑结构分析、数据库物理设计、数据库实施、数据库运行和维护阶段。


投影有两个功效，一个是消除原关系中的某些列，还有一个是额外性质即消除投影后重复的元组，因此元组也有变少的可能

共享锁【S锁】
又称读锁，若事务T对数据对象A加上S锁，则事务T可以读A但不能修改A，其他事务只能再对A加S锁，而不能加X锁，直到T释放A上的S锁。这保证了其他事务可以读A，但在T释放A上的S锁之前不能对A做任何修改。

排他锁【X锁】
又称写锁。若事务T对数据对象A加上X锁，事务T可以读A也可以修改A，其他事务不能再对A加任何锁，直到T释放A上的锁。这保证了其他事务在T释放A上的锁之前不能再读取和修改A。


数据库中的“域”，即：列的取值范围；
比如学生表（学号，班号，年龄），班号和年龄这两条不同的列就可以出自同一“域”。

数据库数据模型
层次模型
网状模型
关系模型

触发器不是响应select而自动执行的mysql语句

delete是删除某些行的数据，不是动表结构

删除表
drop database 表

正则表达式

转义符：\\
*：0或多个匹配
+：1或多个匹配
？：0或1个匹配

innodb不支持全文本搜索

decimal是可变精度浮点值

视图是提供查看和存放数据的另一种途径

union必须连接两个相同的列

数据表是由表名、表中的字段和表的记录三个部分组成的。



通配符
%：用来表示任意多个字符，包含0个字符

     _  : 用来表示任意单个字符

搜索时候使用通配符%，并且%在前面，就不会走索引

事务：
脏读：事务读取到别动事务没有提交的数据
不可重复读：事务两次读取同一个数据得到不同结果
幻读：两次读，第二次读取发现数据多了

读未提交：事务A和事务B同时运行，B修改了数据，还没提交A就能读取到了
读已提交：B修改了数据，B提交后，A才能读取到，问题：不可重复读，事务A没结束，读到的数据不一致
可重复读：事务A没结束，读的都是一样的，问题：幻读，事务A想插入一个不存在的数据，事务B在期间插入了此数据，A因为主键冲突就插入不了，跟失了智一样，又看不到又插入不了。

mysql选择题试卷
https://www.cnblogs.com/hmswt/p/9181273.html

https://wenku.baidu.com/view/a945b8f40b12a21614791711cc7931b765ce7bba.html?fixfr=dmlX6%252BdstrgspinO7qgZUA%253D%253D&fr=income1-aladdin-search

问题：
mysql的锁机制是基于索引的，如果修改了一个普通列，那就是表锁？

9. 要得到最后一句SELECT查询到的总行数，可以使用的函数是( )

A．FOUND_ROWS

B．LAST_ROWS

C．ROW_COUNT

D．LAST_INSERT_ID

答案：A

20． 以下哪种操作能够实现实体完整性( )

A．设置唯一键

B．设置外键

C．减少数据冗余

D．设置主键

21. SQL语言中，删除一个视图的命令是( )

A．REMOVE

B．CLEAR

C．DELETE

D．DROP

23．在全文本搜索的函数中，用于指定被搜索的列的是( )

A．MATCH()

B．AGAINST()

C．FULLTEXT()

D．REGEXP()

答案：A

27．删除用户账号命令是( )

A．DROP USER

B．DROP TABLE USER

C．DELETE USER

D．DELETE FROM USER

答案：A

28．以下语句错误的是( )

A． alter table emp delete column addcolumn;

B． alter table emp modify column addcolumn char(10);

C．alter table emp change addcolumn addcolumn int;

D． alter table emp add column addcolumn int;

答案：A

36．以下删除记录正确的( )

A．delete from emp where name=‘dony’;

B．Delete * from emp where name=‘dony’;

C．Drop from emp where name=‘dony’;

D．Drop * from emp where name=‘dony’;

答案：A

39．可以在创建表时用( )来创建唯一索引，也可以用( )来创建唯一索引

A．Create table，Create index

B．设置主键约束，设置唯一约束

C．设置主键约束，Create index

D．以上都可以

答案：C

61．( )表示一个新的事务处理块的开始

A．START TRANSACTION

B．BEGIN TRANSACTION

C．BEGIN COMMIT

D．START COMMIT

答案：A

94．使用SELECT语句随机地从表中挑出指定数量的行，可以使用的方法是( )

A．在LIMIT子句中使用RAND()函数指定行数，并用ORDER BY子句定义一个排序规则

B．只要使用LIMIT子句定义指定的行数即可，不使用ORDER BY子句

C．只要在ORDER BY子句中使用RAND()函数，不使用LIMIT子句

D．在ORDER BY子句中使用RAND()函数，并用LIMIT子句定义行数

答案：D

101．DECIMAL是( )数据类型

A．可变精度浮点值

B．整数值

C．双精度浮点值

D．单精度浮点值

答案：A

102．视图是一种常用的数据对象，它是提供( )和( )数据的另一种途径，可以简化数据库操作

A．插入，更新

B．查看，检索

C．查看，存放

D．检索，插入

答案：C

4． 以下说法正确的是( )

A． 字符型既可用单引号也可用双引号将串值括起来

B．字符型的87398143不参与计算的

C．87398143 不能声明为数值型

D． 数值型的87398143将参与计算

答案：A,D,B

5．关于主键下列说法正确的是（）

A．可以是表中的一个字段，

B．是确定数据库中的表的记录的唯一标识字段，

C．该字段不可为空也不可以重复

D．可以是表中的多个字段组成的。

答案：B,A,D,C

10．下面对 union 的描述正确的是( )

A．union 只连接结果集完全一样的查询语句

B．union 可以连接结果集中数据类型个数相同的多个结果集

C．union 是筛选关键词，对结果集再进行操作

D．任何查询语句都可以用 union 来连接

答案：D,A,C

13．下列语句错误的是( )

A．select * from orders where ordername is not null;

B．select * from orders where ordername<>null;

C．select * from orders where ordername is null;

D．select * from orders where ordername not is null;

答案：D,B

18．下面关于使用UPDATE语句，正确的是( )

A．被定义为NOT NULL的列不可以被更新为NULL

B．不能在一个子查询中更新一个表，同时从同一个表中选择

C．不能把ORDER BY或LIMIT与多表语法的UPDATE语句同时使用

D．如果把一列设置为其当前含有的值，则该列不会更新

答案：D,C,B

19． 关于Excel 数据库应用的描述正确的有( )。

A．是一个数据清单

B．是按一定组织方式存储在一起的相关数据的集合

C．是一个数组

D．是程序化的电子表格

答案：B,A

21．关于游标，下列说法正确的是( )

A．声明后必须打开游标以供使用

B．结束游标使用时，必须闭游标

C．使用游标前必须声明它

D．游标只能用于存储过程和函数

答案：D,C,A,B

22．下列说法正确的是( )

A．在MySQL中，不允许有空表存在，即一张数据表中不允许没有字段。

B．在MySQL中，对于存放在服务器上的数据库，用户可以通过任何客户端进行访问。

C．数据表的结构中包含字段名、类型、长度、记录。

D．字符型数据其常量标志是单引号和双引号，且两种符号可以混用。

答案：B,A

40．下列说法正确的是（）
A．alter table user drop column sex;

B．alter table user add sex varchar(20);

C．alter table user drop sex;
D．alter table user modify id int primary key;

答案：D,B,A,C

42．在算术运算符、比较运算符、逻辑运算符，这三种符号中，它们的优先级排列不正确的是( )

A．算术/逻辑/比较

B．比较/逻辑/算术

C．比较/算术/逻辑

D．算术/比较/逻辑
答案：A,C,B

52．在mysql提示符下可以输入一个SQL语句，并以( )结尾，然后按回车执行该语句( )

A．“\G”

B．“。”

C．“\g”

D．“;”

答案：D,C,A

54． 23 关系数据模型哪些优点？( )

A．A）结构简单

B． C）有标准语言

C． B）适用于集合操作

D． D）可表示复杂的语义

答案：A,C,B

55．对某个数据库使用记录单 , 可以进行的记录操作有( )

A．删除

B．新建

C．还原

D．插入

答案：B,A,C

57．在字符串比较中，下列哪些是不正确的( )

A．所有标点符号比数字大

B．所有数字都比汉字大

C．所有英文比数字小

D．所有英文字母都比汉字小

答案：B,C,A

汉字>英文>数字>标点

58．数据库信息的运行安全采取的主措施有( )。

A．备份与恢复

B．应急

C．风险分析

D．审计跟踪

答案：C,D,A,B

6．( )UPDATE语句可以有WHERE子句和LIMIT子句。

答案：Y

13．( )用union上下连接的各个select都可以带有自己的order by子句。

答案：N

14．( )ALTER TABLE语句可以修改表中各列的先后顺序。

答案：Y

21．( )结构化查询语言只涉及查询数据的语句，并不包括修改和删除数据的语句。

答案：N

25．( )所有TIMESTAMP列在插入NULL值时，自动填充为当前日期和时间。

答案：Y

1.string和int比较时，是string迁就int，字符串转化成数字进行比较的；

2.字符串转化规则：只会截取最前面的数字部分（即如果第一个遇到的是字母，则直接返回0）；

select “123”+1;

结果为124

select “1a23”+1 ;

结果为2

select “aa23”+1;

结果为1；

select “aa” = 0 -------------true;大坑，切记

select “1aa”=0--------------false;

null <=> null 结果是1

1 is null 结果是0

B+树的叶子节点可以储存整行数据，也可以存储主键的值

聚簇索引和非聚簇索引

在 InnoDB 里，索引B+ Tree的叶子节点存储了整行数据的是主键索引，也被称之为聚簇索引。而索引B+ Tree的叶子节点存储了主键的值的是非主键索引，也被称之为非聚簇索引

select * from table for update :加排他锁，其他事务不能加任何锁

select * from tabn lock in share mode:加共享锁，其他事务可以加共享锁不能加排他锁。

insert update delete 自动加排他锁

select * from table for update 排他锁

select * from tabn lock in share mode 共享锁

MyISAM:表锁

BDB:页锁 表锁

InnoDB：行锁 表锁

DDL
create drop alter 数据库和表

create drop索引

DML
增删改查数据 select delete update insert into

DCL
权限
grant to/revoke from
事务
commit/rollback

创建索引三种方式
create table 里
index indexname（column）

以建表里添加索引
	create index indexname on table （column）

以修改表的方式添加索引
	alter table tablename add index indexname（column）

innodb中的数据文件
.frm：表相关结构信息
.ibd：独享表空间的数据和索引信息，一个表对应一个idb文件
.idbdata：共享表空间的数据和索引信息，所有表共同使用一个/多个idbdata文件

MyISAM的数据文件
.frm：表相关结构信息
.myd：存储表数据
.myi：存储表数据文件中任何索引的数据树