阅读 202

MySQL-索引

MyIsam与InnoDB的区别

①从存储方面

MyISAM以紧密格式的压缩表存储数据。

InnoDB把所有数据划分为若干个16KB大小的页,并且以页作为磁盘与内存交互的单位。所有每个页中有若干记录,而每条记录中有个属性表示了它在页中的偏移量,InnoDB会根据记录的偏移量,也就是按照主键的大小,由小到大把记录串联成一个单向链表,所以一个页中存放一个记录链表。InnoDB又把一个页中的记录划分成几个组,然后把每组中最后一条记录的地址偏移量提取出来作为槽,按顺序存储在页的尾部,组成页目录。我们进行无索引查找时,就是通过页目录进行二分查找。

②从无索引查询方面

MyISAM只能遍历列表查询数据。

InnoDB把所有数据划分为若干个16KB大小的页,并且以页作为磁盘与内存交互的单位。所有每个页中有若干记录,而每条记录中有个属性表示了它在页中的偏移量,InnoDB会根据记录的偏移量,也就是按照主键的大小,由小到大把记录串联成一个单向链表,所以一个页中存放一个记录链表。InnoDB又把一个页中的记录划分成几个组,然后把每组中最后一条记录的地址偏移量提取出来作为槽,按顺序存储在页的尾部,组成页目录。每条记录的头信息中有n_owned属性,代表它的分组中有几条记录。所以我们在根据主键进行查询时,对页目录进行二分查找,定位到我们主键值所在的键值区间,也就是得到了最近的那个槽对应的分组,然后遍历该分组中的各个记录得到即可。若是根据非主键查找,则只能遍历单链表,无法对页目录进行二分查找。

③从索引方面

MyISAM

MyISAM聚簇索引和辅助索引存储的都是聚簇索引键值+数据地址

MyISAM使用.MYD文件存储数据行,使用.MYI文件存储索引树。

InnoDB:

InnoDB索引B+树节点... InnoDB使用.rbd文件存储数据行与索引树。

④从锁方面

MyISAM只支持表级锁。

InnoDB还支持行级锁,记录锁、间隙锁、临建锁

InnoDB支持事务、支持MVCC

为什么要使用B+树作为索引结构呢?

索引结构有很多,比如哈希表、有序数组、搜索树,但MySQL选择了B+树。

首先,哈希表以数组存在,以链式哈希解决哈希冲突,查找速度还可以。但是,它的链表插入时是按照先后顺序插入的,即不是有序的,所以无法做到范围查找!而有序数组可以用二分法解决范围查找呀,但是数组插入不方便,它比较适合静态存储。再说二叉树,我们的索引是存储在磁盘中的,也就是说每次往下遍历一层 拿取这一层上的某个二叉树节点就要一次IO交互,而二叉树的每个节点只有一个key,导致树太高,不适配磁盘访问;而且二叉树高度太高,增加了IO次数。所以我们选择了更矮、每个节点中有多个key的n叉树,即B+树。

MySQL的B+树一般有多高?最大可以存放多少数据?

若B+树中元素总量为n,每个叶子节点能够存放的元素量为m,则高度为logn m。

InnoDB的pageSize默认值为16K,所以每个节点能够存放的元素量为 16k/(key+point) = 16/(8+4)*8 = 171。所以若有3000w的元素,则树高为log2^25 171 = 3.38,所以树高一般为3或4。

索引

【使用索引的查找过程】:

InnoDB把所有数据划分为若干个16KB大小的页,并且以页作为磁盘与内存交互的单位。所有每个页中有若干记录,而每条记录中有个属性表示了它在页中的偏移量,InnoDB会根据记录的偏移量,也就是按照主键的大小,由小到大把记录串联成一个单向链表,所以一个页中存放一个记录链表。InnoDB又把一个页中的记录划分成几个组,然后把每组中最后一条记录的地址偏移量提取出来作为槽,按顺序存储在页的尾部,组成页目录。我们进行无索引查找时,就是通过页目录进行二分查找。

那么我们的问题也就聚焦在了如何便利地定位到页。页是不连续的,页与页之间通过双向链表相连。如果想根据主键值快速定位他所在的页,我们就需要像记录的页目录一样给页做一个目录,这个目录的key为页中的最小主键值,value为页号。即索引的概念。

InnoDB把多个索引放在一个页中存储,即把索引当成记录,并通过记录头信息的record_type属性来与普通记录区别。同样的,InnoDB也会为索引页生成页目录。但当索引量也来越大,快速定位索引也变得困难,而且占用的连续空间也很大。所以我们想到这样一个解决方案:我们把索引页的页目录对页号向上冗余形成一个树形结构。所以InnoDB把页目录中的槽转化为页号,把每个页号附带上页信息不断向上冗余成了一个B+树。也就是说,B+树的每个节点对应一个页,每个节点中有一个链表,非叶子节点的链表节点为键值+页号,叶子节点的链表节点为键值+页号 或 键值+用户数据。而且叶子节点之间也会以双向链表相连,用于范围查询等。(对于叶子节点中的索引页,也满足页的特性,即可以二分查找槽 得到索引记录)

对于聚簇索引:我们在没有显示定义索引时,InnoDB的数据实际上就是存储在聚簇索引中的(也就是无索引查询时,那个B+树实际上就是默认的聚簇索引)。它的非叶子节点存储多个主键值和页号,它们以链表形式相连。聚簇索引的叶子节点就存储了完整的用户记录。在根据主键值在聚簇索引上查找时,InnoDB会根据传入的主键值得到页号,根据页号向下不断搜索B+树直至叶子节点(页),然后在这个节点中遍历链表定位到主键值,从而得到这条用户记录。

对于辅助索引:它的非叶子节点存储了多个非主键值和页号,它们以链表形式相连。辅助索引的叶子节点存储了非主键值和的主键值。在根据非主键值查找时,innoDB会根据传入的非主键值得到页号,根据页号不断向下搜索B+树直至到叶子节点(页),然后在这个节点中遍历链表定位到非主键值,从而得到记录的主键值。然后再拿着主键值去表的聚簇索引中,以同样的形式往下搜索B+树,最终得到用户数据。

对于联合索引:它的非叶子节点中的每个链表节点存储了一个页号和多个键值。叶子节点中的每个链表节点存储了页号+多个键值+它们对应的主键值。在遍历联合索引B+树节点中的链表时,对于每个链表节点都存储了多个键值,所以InnoDB会按照顺序对比每一个键值是否符合条件。【联合索引可以用于构造覆盖索引】

覆盖索引:查询列都使用了索引,也就是说我们需要的键值在索引树中都有冗余。所以我们在搜索索引树的过程中就可以拿到所有需要的键值,即不需要回表了。

唯一索引:在叶子节点链表中进行二分查找,查找到第一个满足条件的记录后就立即返回。而普通索引会继续往后二分查找到第一个不满足条件的记录才返回。但是MySQL存储引擎是以页为单位,将16KB大小的数据整体读到内存中的。所以当找到满足条件的记录时,MySQL会把这整个数据页读入内存中,所以唯一索引和普通索引的性能差别微乎其微。(当然结果跨页另说)

change buffer:一部分在内存,一部分在系统表空间(系统表空间用于存储数据字典等,对应磁盘文件ibdata1,;数据表空间对应表数据文件,对应磁盘文件.ibd)。

当需要更新一个数据页时,如果数据页在内存中就直接更新;而如果这个数据页没有在内存中,InnoDB就会把这个更新操作缓存在change buffer中(属于buffer pool),等到下一次需要访问这个数据页时,将数据读到内存,然后执行change buffer中缓存的操作。所以如果数据不在内存中,普通索引的change buffer避免了数据从磁盘读到内存中的随机IO访问,性能会提升(但是对于读多写少,change buffer反而增加了额外的维护代价,所以若更新后立即查询,则要关闭change buffer)。所以Change Buffer实际上是减少了随机读磁盘的IO消耗

而唯一索引是无法使用change buffer的。因为唯一索引在更新数据时会先判断表中此数据的唯一性,那就意味着要拉入内存中,既然都读入内存了,就没必要使用change buffer,直接更新内存即可了。所以如果出现大量数据插入过慢,就可能是唯一索引的原因。

前缀索引

前缀索引生成的索引树,树节点存储的key只是前缀,占用的空间小,所以树更加矮胖。但会额外增加扫描次数,因为前缀匹配成功后要回表判断key是否相同,可能会导致额外的回表。所以我们在使用前缀索引时要注重截取长度,尽量增大区分度。而且使用前缀索引就用不上覆盖索引了,因为真实数据在聚簇索引中,需要回表。

索引删除、页分裂等会导致数据页空洞,所以在一定程度时我们需要重建索引(alter table T engine=InnoDB),也就是创建新索引并按顺序重新导入,提高空间利用率。

但是不允许重建主键索引哦,修改主键索引,会导致整个表重建。

索引删除时,InnoDB只是标记已删除而不是真正删除,然后InnoDB的Purge线程会异步地清理这些索引,但这些空间并没有释放给操作系统使用,所以索引删除会导致数据页空洞。

索引的缺点:

需要维护成本,当表结构变化时索引也得变化;可能会有页分裂等造成数据空洞,需要重建索引。

索引优化

联合索引:

建立联合索引时,要遵循索引的复用能力来建立联合索引,尽量少维护单独索引。若索引前后字段的查询频率相似,那么就遵循空间大小,让小字段单独建立索引。 索引条件下推(ICP):

遍历索引的过程中,MySQL会提前对所有联合索引字段做where判断,直接过滤掉不满足条件的记录,减少回表的次数。如果没有索引条件下推,那么我们用联合索引的一部分匹配成功后,就不得不回表,然后在聚簇索引的用户数据中比较字段值。

explain

image.png id:id越大,执行优先级越高。

type:all全表遍历、index覆盖遍历、ref普通索引、eq ref唯一索引、const只匹配一次索引

key:实际上使用到的所以

key_len:使用到的索引长度。

extra:额外信息(using where;using index)

在查询条件中包含全部索引键值,但顺序不对,MySQL会自动重排

Order by:

若前置索引列为降序排序,则后续列无法使用索引。

因为叶节点中存储的数据默认升序,降序之后目标索引列的顺序性失效,在叶子节点链表中无法二分查找,MySQL就会放弃使用改列索引。

Group by与Order By一致,它本质是先排序后分组。

limit分页查询优化

select * from e order by name limit 90000,5不会走索引。

因为它本质是查找出90005条记录,然后舍弃前90000条。MySQL若使用索引则需要大量的回表,所以会舍弃索引而选择全表扫描。

可以使用覆盖索引:select * from e inner join (select id from employees order by name limit 90000,5) d on e.id = d.id

join关联查询优化:RE极客

count(*)优化

在MySQL5.7之前,count(id)走主键索引,叶节点存放全部数据,遍历消耗大

在MySQL5.7之后,count(1)、count(id)、count(name)、count()都是走辅助索引,性能差别不大。而且count()不是取出所有列的数据,它只是取一条用于判断此行是否存在。

无法使用索引:索引列有函数(因为where中经过函数计算,=后面的值不会在B+树中,与节点中的有序链表就对应不上了,即破坏了有序性,那优化器就决定走全索引扫描)、隐式类型转换、关联两表的字符集不同(若驱动表的字符集大,需要用convert(xxxID)函数转换成小字符集。而若驱动表的字符集小,则只转换=后面的值,就可以走索引)、大小于、is null、%k(使用覆盖索解决)

不一定会使用索引:in、or(小范围主键判断可能会走主键索引)

索引设计原则、失效场景

对查询效率高的字段设置索引、不在区分度低的字段上加索引、尽量使用联合索引、尽量使用唯一索引、最左前缀匹配。

因为如果区分度低的索引是辅助索引,则可能会产生大量的回表。

索引列使用函数 / 隐式条件转换、like使用不当、is null、in子表的数据少

in和exists的区别

in是先查询子表,得到子表的结果集,然后用外表对结果集进行一一匹配。

exist

其他

数据库三范式(每级范式是在上级范式的基础上)

①确保每列的原子性,数据库表中字段都是不可再分的。为了减少冗余,使用方便且提高数据库性能,避免更新异常。

②确保表中的每列都和候选键有完全依赖关系,不能仅仅依赖一部分候选键的属性(后续键即联合主键,能够唯一标识行的元组)。数据库表中非主键字段都完全依赖于主键字段。可以通过主键获取表中的所有记录,方便查询。

③确保表中的每列都与候选键不存在传递依赖关系。也就是要用外键代替本表中的传递依赖。

反范式:减少了跨表查询造成的额外消耗,会有数据之间或者表与表之间的冗余。

什么是外键约束

外键:一个表的主键字段被当做另一个表的非主键字段,那么这个非主键字段就是外键。

外键约束:这个外键的值必须在 对应主表的主键值区间内。

什么是触发器:定义在表上监视表,满足条件时触发。

为什么尽量使用自增主键

首先,自增主键可以保证有序插入,而业务逻辑字段往往不能保证有序插入,所以它可能会导致页分裂,即某个数据页已满时,新来的数据正好落在此数据页上,则需要创建一个新的数据页并把后面的数据挪过去,消耗性能与空间利用率。

其次,自增主键一般是整型数据,占用空间小,继而一个叶节点可以存放更多的数据。

MySQL服务端与客户端的连接

建立客户端与数据库连接器的连接是很复杂的,所以我们尽量使用长链接。但是,MySQL在执行过程中临时使用的内存是管理在连接对象里的,所以如果长连接一直不释放,会导致内存占用太大,导致OOM。解决:可以定时断开长链接或者程序里判断执行过一个占用内存的大查询后断开长连接,之后执行查询时再重连。

SQL外连接

左外连接:查询左表中的所有行 + 右表中的匹配行,右表中不匹配的行,列为空值。

右外连接:与左外连接相反。

外连接:查询左表和右表中的所有行。若某个行在另一个表中没有匹配行,则该行的列为空值

SQL注入

SQL注入就是黑客会构造一些恶意的输入参数到URL或Web表单请求中,在应用拼接SQL语句时,就会篡改正常的SQL语义,也就是说黑客注入了一段SQL代码到应用中,造成数据泄露。

案例:①在SQL入参中添加or 1=1 ②在SQL入参中添加1;drop table users

如何防护SQL注入:

①使用PreparedStatement:它的原理是把SQL语句的解析和执行分离开来,那么黑客提交的非参数指令就无法被解析,所以在执行过程中只会使用到用户传入的SQL参数,避免了SQL注入的发生。

②验证输入:可以对整型的SQL参数进行Integer.parseInt()验证。

SQL语句执行顺序

select count(money) from country (join on where name='china') group by name having name like '%ina' order by money limit 0,1

MySQL海量数据(如何优化MySQL?)

使用索引

内存:只要给出内存地址,就可以直接访问该内存取出数据,即随机访问。

磁盘:磁盘的最小读写单位是4K的扇区,而操作系统一次会读写多个扇区,也就是一个块。所以对于大批量的顺序读写,操作系统一次操作可以读出多个扇区的数据,使用磁盘的效率要比随机读写高。

而如果仅仅是把大批量数据存入磁盘中,那检索数据时我们无法准确地得知数据在哪个块中,所以会无谓地增加对磁盘的访问次数。所以我们需要使用索引,将索引与数据分离从而减少磁盘访问次数。

海量数据的索引也是巨大的,所以也要放入磁盘中。而MySQL的B+树就非常巧妙地利用了重装系统的特性。我们知道操作系统是以块为最小单位来读写数据的,对应B+树的每个节点大小就等于磁盘中一个块的大小,节点中存储一个装有很多元素的数组,那么这样就可以完全利用一次磁盘读写的数据,间接地减少了磁盘访问次数。而且MySQL的B+树只有叶子节点存储数据,所以非叶子节点中的数组存储的key更多,所以B+树更矮,查询效率就更高。

小表驱动大表

select * from A where id in (...):先得到子表的结果集,再使用子表结果集过滤主表,所以B表是小表-驱动表。

select * from A where exists(select 1 from B where B.id=A.id):先得到主表的结果集,再逐条结果进行条件匹配。所以A表是小表-驱动表。

当子表结果集很大时,exists > in;当子表结果集很小时,in > exists。 读写分离

数据库服务器搭建主从集群,主服务器负责写操作,并把数据全量同步到多从上。从服务器负责读操作。读写分离适用于单机并发量过大,且读请求更多时。我们进行读写服务器分离,对于写服务器减少索引来提高写入速度,对于从服务器进行索引优化来提高读取服务。

分库分表

分库还是分表?

对于数据量太大(1000w)而引发查询慢的问题,我们可以通过分表只读缓存 或 读写分离来解决 或 使用分表减少每次查询的数据总量也可以解决。

对于高并发的问题,单个数据库实例撑不住,就需要把请求分散到多个数据库中。所以使用分库。但一般情况都是要同时分库分表的哦。

Sharding Key水平分表依据

就是要参考我们的业务是如何访问数据库的。我们在选择Sharding Key时,要尽量去兼容业务,也就是让单次查询尽量落在一个分片中。但比如说对于订单,用户可能会根据订单ID查、根据用户ID查、根据店铺ID查,那其实就很难去规定这个订单按照怎样的分片逻辑来进行分表,就只能对订单ID进行hash运算,然后平均地分到每个分片上了。

普通哈希:hash(x) % len

一致性哈希:hash(X) & 2^32-1来对表进行分片,同时使用虚拟节点,对表进行多次hash,每个计算节点都放一个虚拟节点。记录到来时,使用记录ID进行相同的hash()函数,然后沿环顺时针查找,遇到的第一个节点就是目标分片。

当然上面只是说的横向拆分,还有纵向拆分,就是把一个表的多列拆开。这种一般就是在数据量大了之后会有多列查询的性能问题,所以可以把经常一起使用的列放到同一个表中,提高效率。

分库分表如何保证ID自增不重复:雪花算法(高位随机数+机器码)、Redis分布式锁生成ID、部署独立的服务生成ID。

冷热数据

我们可以把热数据放在内存中,把冷数据放在磁盘中存储。通过LRU算法来进行冷热数据的匹配,若冷数据变热则重新放入内存中。

如何实现MySQL高可用

①一主一从:主服务器提供读写,从服务器只做热备。两者均开启binlog,从服务器时刻为代替主服务器做准备。 ②一主一从:读写分离 ③双主架构:双主都可以读写,互为主备。默认一台复制写,同步到另一台。

那么MySQL如何进行主从数据同步呢?

①从库创建IO线程,连接主库并请求主库发送binlog请求。

②主库创建binlog输出线程,把binlog内容发送给从库。

③从库读取主库binlog输出线程发送的binlog日志,拷贝到本地文件relay log中。

④从库创建SQL线程,读取relay log中的更新事件并执行。、

MySQL池化技术 MySQL连接池是为了管理数据库连接,实现连接复用。因为创建一个MySQL连接需要TCP三次握手 + 服务端校验客户端密码(加密报文+OK确认报文),比较耗时。

数据库连接池中维护了一个最小连接数和最大连接数。连接池会一直把现有连接数保持在这一区间,一般最小连接数在10左右,最大连接数在20-30左右。

但如果数据库的IP发生变更,或者MySQL连接到达最大存活时间wait_timeout,此时旧连接不不可用,但连接池是不感知的。所以对于C3P0会用一个线程定期对连接池中的所有连接发送“select 1”,判断连接是否存活。

文章分类
后端
文章标签