1:mysql的索引原理和数据结构能介绍下吗?
a)存储引擎
数据在磁盘上的不同组织形式
innodb:数据和索引是放到一起的(.frm .ibd)/支持事务/支持外键/支持表锁和行锁
myisam:数据和索引是分开的(.frm .MYD .MYI)/不支持事务/不支持外键/只支持表锁
但是两种索引的数据结构都是用的B+tree
hash表也可以作为索引的数据结构,但是必须是memory存储结构
数据结构
索引的分类
索引设计到的常问的几个名词
执行计划
索引优化
2:b+树和b树的区别
a)为什么innodb不用hash表(上面是数组下面是链表)
1:使用hash表必须要保证良好的hash算法,如果算法不合适的话会造成hash冲突和hash碰撞,会导致数据散列不均匀,有可能退化成一个链表
2:使用hash表的时候不支持范围查询需要挨个对比效率太低
3:需要大量的内存结构
b)二叉树/AVL树/红黑树 为什么都不行?
1:分支有且只有两个,会导致层数变高,IO查询次数增多(内存和磁盘交互)
b树:每个节点中既包含数据又包含指针数值
b+树:只在叶子节点方全量数据,非叶子节点放key值
mysql的索引一般3到4层就可以支持千万级别的数据量
怎么算的:innodb每次读取默认16kb的数据(非叶子节点只保存key值,会存储大量的key值所以叶子节点的数据平铺开来会比较大)
会引申出一个问题:创建索引时使用int类型好还是使用varchar类型好?
应该越短越好,因为着每个磁盘块存储的key越多(小于4用varchar大于用int)
如果一个字段id要不要让他自增:是要的,避免页分裂(例如:一个磁盘块四个数据 11,12,13,15满了 此时来一个14 会进行页分裂 他的上层也会变化 频繁的页分裂会影响效率,变为自增后每个索引都顺序排列 减少了页分裂) 或者页合并,b+树是的叶子节点是链表形式,可以对于主键的范围查找和分页查找,另一种是从根节点开始,进行随机查找
3:聚簇索引和非聚簇索引的区别
innodb有两个数据文件:.frm .ibd(数据文件和索引文件放在一起)
注意:a)Innodb是通过b+树结构对主键创建索引,然后叶子节点中存储记录,如果没有主键那么会选择唯一键,如果没有唯一键那么会生成一个
6字节的row_id作为主键
b)如果创建索引的键是其他字段,那么在叶子节点中存储的是该记录的主键,然后再通过主键索引找到对应的记录叫做回表
一个表中可以包含多个索引列,那么数据文件会存储几份?
数据仅仅存储一份,不会造成多份数据的数据冗余
数据跟某一个索引列是绑定到一起的,那么其他索引列是如何检索数据呢?
会将跟数据绑定到一起的索引列的值放到其他索引的叶子节点
引申出聚簇索引和非聚簇索引:数据跟索引放到一起的叫做聚簇索引,反之
例如:id:聚簇索引 name:非聚簇索引
在innodb中既存在聚簇索引又存在非聚簇索引,那么在myisam呢?
在myisam为非聚簇索引没有聚簇索引
每个索引是一颗b+树还是所有索引共用一颗b+树?
一个索引一颗b+树
4:使用索引都有哪些原则
表:id,name,gender,age四个列,id主键,name普通索引
a)回表:从某一个索引的叶子节点中获取聚簇索引的id值,根据id再去聚簇索引中获取全量记录(尽量减少回表,回表需要遍历两次b+树)
eg:select * from table where name = 'zhangsan';
b)索引覆盖:在那么这个b+树上name和id都能取到(从索引的叶子节点中能获取到全量查询列的过程)
eg:select id,name from table where name = 'zhangsan';
表:id,name,gender,age四个列,id主键,name,age组合索引
c)最左匹配(1,2,4可以使用组合索引,4中name和age换顺序对最终结果没影响,mysql优化器会优化选择合适的顺序执行)
eg:select * from table where name = 'zhangsan' and age = 10;
eg:select * from table where name = 'zhangsan';
eg:select * from table where age = 10;
eg:select * from table where age = 10 and name = 'zhangsan';
d)索引下推
eg:select * from table where name = 'zhangsan' and age = 10;
client
server:连接器(管理连接,验证权限),分析器(词法分析,语法分析),优化器(优化执行过程),执行器(跟执行引擎交互使用,执行具体的sql)
存储引擎
先根据name去存储引擎拿到全量的数据,将数据读取到server层,然后到server层进行数据过滤
索引下推之后:
根据name,age两个列去存储引擎筛选数据,将最终的结果返回给客户端
5:mysql的组合索引的机构是什么样的?
把一个key值换成两个就可以了
6:mysql索引如何进行优化
当表中的全部字段都是索引列的时候,无论进行什么样的查询都会用到索引
索引并不是越多越好,也并不是在全部列上添加索引
7:海量数据下如何分局执行计划调优sql
explain:
id:只sql的执行顺序(id相同，执行顺序由上至下,id不同，如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行)
select_type:查询类型
SIMPLE 简单的select查询，查询中不包含子查询或者UNION
PRIMARY 查询中若包含任何复杂的子部分，最外层查询则被标记为PRIMARY
SUBQUERY 在SELECT或WHERE列表中包含了子查询
DERIVED 在FROM列表中包含的子查询被标记为DERIVED（衍生），MySQL会递归执行这些子查询，把结果放在临时表中
UNION 若第二个SELECT出现在UNION之后，则被标记为UNION：若UNION包含在FROM子句的子查询中，外层SELECT将被标记为：DERIVED
UNION RESULT 从UNION表获取结果的SELECT
table:表名
partitions:是否有分区
type:查询类型(*)一般来说，得保证查询至少达到range级别，最好能达到ref
a)system 表只有一行记录（等于系统表），这是const类型的特列，平时不会出现，这个也可以忽略不计
b)const 表示通过索引一次就找到了，const用于比较primary key 或者unique索引。因为只匹配一行数据，
所以很快。如将主键置于where列表中，MySQL就能将该查询转换为一个常量。
c)eq_ref 唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描
d)ref 非唯一性索引扫描，返回匹配某个单独值的所有行，本质上也是一种索引访问，它返回所有匹配某个单独值的行，
然而，它可能会找到多个符合条件的行，所以他应该属于查找和扫描的混合体。
e)range 只检索给定范围的行，使用一个索引来选择行，key列显示使用了哪个索引，一般就是在你的where语句中出现between、< 、>、in等的查询，
这种范围扫描索引比全表扫描要好，因为它只需要开始于索引的某一点，而结束于另一点，不用扫描全部索引。
f)index Full Index Scan，Index与All区别为index类型只遍历索引树。这通常比ALL快，因为索引文件通常比数据文件小。
（也就是说虽然all和Index都是读全表，但index是从索引中读取的，而all是从硬盘读取的）
g)all Full Table Scan 将遍历全表以找到匹配的行
possible_keys:显示可能应用在这张表中的索引，一个或多个。查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询实际使用
key:(*)实际使用的索引，如果为NULL，则没有使用索引。（可能原因包括没有建立索引或索引失效）
key_len:
ref:显示索引的那一列被使用了，如果可能的话，最好是一个常数。哪些列或常量被用于查找索引列上的值
rows:根据表统计信息及索引选用情况，大致估算出找到所需的记录所需要读取的行数，也就是说，用的越少越好
filtered:
Extra(*):包含不适合在其他列中显式但十分重要的额外信息
Using filesort（九死一生）:说明mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。MySQL中无法利用索引完成的排序操作称为“文件排序”。
Using temporary（十死无生）使用了用临时表保存中间结果，MySQL在对查询结果排序时使用临时表。常见于排序order by和分组查询group by。
Using index（发财了）表示相应的select操作中使用了覆盖索引（Covering Index），避免访问了表的数据行，效率不错。
如果同时出现using where，表明索引被用来执行索引键值的查找；如果没有同时出现using where，表明索引用来读取数据而非执行查找动作。
Using index condition:索引下推
Using join buffer:表明使用了连接缓存,比如说在查询的时候，多表join的次数非常多，那么将配置文件中的缓冲区的join buffer调大一些。
impossible where:where子句的值总是false，不能用来获取任何元组(select * from table where id = 1 and id = 2)
select tables optimized away:在没有GROUPBY子句的情况下，基于索引优化MIN/MAX操作或者对于MyISAM存储引擎优化COUNT(*)操作，不必等到执行阶段再进行计算，查询执行计划生成的阶段即完成优化。
准备:组建一个自己的应用场景作为备用
8:mysql索引体系如何应对海量数据存储
a)索引的底层是b+树,mysql的数据文件存在于磁盘,mysql的索引文件也存在于磁盘,会把我们的文件从磁盘中加载到内存当中去
b)当查询的时候,数据分块读取磁盘的内容到内存中
c)磁盘预读:内存和磁盘进行交互的时候,有一个基本的逻辑单位也被称之为页,一般为4k或8k,我们在进行数据读取的时候一般读取的是页的整数倍
d)局部性原理:
时间局部性:之前访问过的数据,有可能很快再次被访问
空间局部性:数据和程序都有聚集成群的倾向,具备某些特征的数据可以放到一起
使用什么样的数据结构存储(格式:K-V)
hash,树
hash的存在价值是使数据均匀的散列开,hash算法不太好的话会导致有得格子中没数据,有得链表会特别长(查询效率低)
b树每个块放三种类型的值:key值, 数据, 指针(指向下一个磁盘块)
b+树只有key值和指针(指向下一个磁盘块),数据都放在了叶子节点,每页的数据存储的key值更多,所以key值越短越好int为4个字节 varchar可以为1,2,3...
小于4用varchar大于用int
一般情况下3到4层的b+树足以支撑千万级别的数据量
*数据迁移的时候把索引关掉,等数据迁移完了再重建索引,避免在迁移过程中索引不断的重建,效率极低
索引的分类:
a)主键索引:主键字段
b)唯一索引:唯一值
c)普通索引:非主键非唯一
d)全文索引:做全文检索
e)组合索引:表中的多个字段值可以共同构成索引
索引并不是越多越好:
a)索引维护麻烦
b)占用的存储空间变大,会导致io增多
在innodb存储引擎中,数据在进行插入的时候需要跟某一个索引列绑定在一起,这个索引列如果有主键,那么使用主键,如果没有主键那么会选择唯一键
,如果没有唯一键那么使用6字节的row_id
9:如何针对特定的场景对sql进行优化
表:cityName(Varchar) 有得城市有5个字符有的可能2个,如何建立索引?
只选择一定的前缀创建索引 --> 到底选择前几位做索引?
看区分度,变化不大时创建索引
hive和mysql的区别?
是不同的技术栈
10:投简历:九点/十点 发完默认话术简述自己一下
11:海量数据下必知必会的分布式集群
a)主从复制:(百度主从复制原理进行补充)
db1 db2,保证两个数据库同步,用到mysql的binlog日志,从库的线程IO Thread会把做过修改的日志读取回来进行落盘操作为)relay log(中继日志
会有另外的线程SQL Thread读取中继日志落盘数据到从库
中间过程存在的问题:会有延迟
多个IO Thread在写一个IO Thread在读效率不能保证
生成binlog日志是顺序写入的,生成的中继日志也是顺序的,但是从库修改数据是随机写入的,也会有效率或性能或延迟问题
问题解决:mysql5.7后的解决方案:MTS
a)组提交(深入研究)
b)读写分离
一台机器支撑写的需求,其他几台机器负责读(相当于做了负载均衡)
mycat
shardingsphere:建议用这个 现在的生态比较完善,现在是阿帕奇的顶级项目
c)分库分表
a)垂直切分(把不同的字符分开放入不同的表中)
b)水平切分
分片键
12:海量数据下如何保证不同事务的数据一致性
ACID:
原子性:要么全部成功要么全部失败,通过undolog实现(记录相反的操作而已)
a)undolog是为了实现事务的原子性,在mysql数据库innodb存储引擎中,还用undolog来实现多版本并发控制(MVCC)
b)在操作任何数据之前,首先将数据备份到一个地方(这个存储数据备份的地方称为undo log)然后进行数据的修改,如果出现了错误或用户执行了
rollbak语句,系统可以利用undo log中备份的数据恢复到数据开始之前的状态
c)undolog是逻辑日志,可以理解为:
当delete一条记录,undo log中会记录一条对应的insert记录
当insert一条记录,undo log中会记录一条对应的delete记录
当update一条记录,undo log中会记录一条对应的update记录
一致性:
隔离性:MVCC锁实现
读未提交
读已提交(oracle)
可重复读(mysql默认)
串行化
读写:会有线程安全问题,需要进行并发控制(锁)MVCC
读读:会有线程安全问题,需要进行并发控制(锁)MVCC
MVCC:多版本并发控制,解决数据并发读写问题
快照读:读取的是对应数据的历史版本-->select
当前读:读取的最新的数据结果select lock in share mode (select for update/insert/delete/update)
MVCC多版本并发控制指的是维持一个数据的多个版本,使得读写操作没有冲突,快照读是mysql为了实现MVCC的一个非阻塞读功能
实现原理:
隐藏字段:
DB_TRX_ID:最近修改事务id,记录创建当前记录或者最后一个修改的事务id
DB_ROLL_PTR:回滚指针,指向这条记录的上一个版本
DB_ROW_ID:隐藏主键
undolog会形成一个链表,链首存储的是最新的旧记录,链尾放的是最旧的旧记录
undolog不会无限膨胀下去,会存在一个后台线程,purge线程,当发现当前记录不需要回滚且不需要参与MVCC的时候,就会把数据给清理掉
readview:当事务在进行快照读的时候会生成一个读视图来进行可见性判断,可见性判断是由可见性算法来确定的
持久性:
redolog实现-->WAL(write ahead log 预写日志)实际的数据没有写成功,但是只要日志存在就可以根据日志来恢复数据
binlog:归属于mysql server
与redolog的区别:
a)redolog是innodb独有的,binlog是所有引擎都可以使用的
b)redo是物理日志,记录的是在某个数据页上做了什么修改,binlog是逻辑日志,记录的是这个语句的原始逻辑
c)redo是循环写的,空间会用玩binlog是可以追写的,不会覆盖之前的日志信息
undolog:归属于 mysql innodb存储引擎
redolog:归属于 mysql innodb存储引擎
redolog二阶段提交之数据的更新流程:
a)执行器先从引擎中找到数据,如果在内存中直接返回,如果不在内存中查询后返回
b)执行器拿到数据后会先修改数据,然后调用引擎接口重新写入数据
c)引擎将数据更新到内存,同时写数据到redolog,此时处于prepare阶段,并通知执行器执行完成随时可以操作
d)执行器生成这个操作的binlog
e)执行器调用引擎的事务提交接口,引擎把刚刚写完的redo该为commit状态,更新完成
先写redolog在写binlog:
假设在redolog写完,binlog还没有写完的时候,mysql进程异常重启,由于我们前面说过的,redolog写完之后,系统及时崩溃,
仍然能够把数据恢复回来,所以恢复后这一行c的值是1,但是由于binlog没写完就crash了,这时binlog里面就没有记录这个语句
因此之后备份日志的时候,存起来的binlog里面就没有这条语句.然后你会发现,如果需要这个binlog来恢复临时库的话由于这个语句的
binlog丢失,这个临时库就会少了这一次的更新,恢复出来的这一行的值就是0,与原库值不同
先写binlog后写redolog:
如果在binglog写完之后crash,由于redolog还没写完,系统崩溃后这个事务无效,所以崩溃后这一行c的值是0,但是binlog里面
记录了把0变为1的日志,所以在之后用binlog来恢复的时候就多出来一个事务,恢复出来的这一行的值就是1,与原库值不同所以就有了prepare阶段进行最终提交的校验保证数据的一致
锁的分类:
粒度:行锁,表锁
读写:共享锁/排它锁
实际是否加锁:乐观锁/悲观锁
mysql乐观锁解决并发:
UPDATE items SET name = 'new_name', price = 99.99, version = version + 1 WHERE id = 1 AND version = old_version;
扫一扫
2754
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
