软件开发公司在哪里,企业seo解决方案,网站开发亿码酷负责,做冷饮的网站一)什么是AQS#xff1f; 1)AQS也被称之为是抽象同步队列#xff0c;它是JUC包底下的多个组件的底层实现#xff0c;Lock#xff0c;CountDownLatch和Semphore底层都使用到了AQS AQS的核心思想就是给予一个等待队列和同步状态来实现的#xff0c;它的内部使用一个先进先出… 一)什么是AQS 1)AQS也被称之为是抽象同步队列它是JUC包底下的多个组件的底层实现LockCountDownLatch和Semphore底层都使用到了AQS AQS的核心思想就是给予一个等待队列和同步状态来实现的它的内部使用一个先进先出的队列管理来获取同步资源的线程每一个线程在竞争同步资源的时候会先尝试获取同步资源如果获取不到那么会被封装成一个节点加入到阻塞队列中 2)在底层的AQS提供了两种锁机制分别是共享锁和排他锁 排它锁就是存在多线程竞争同一共享资源时同一时刻只允许一个线程访问该共享资源也就是多个线程中只能有一个线程获得锁资源比如 Lock 中的 ReentrantLock 可重入锁实现就是用到了 AQS 中的排它锁功能 共享锁也称为读锁就是在同一时刻允许多个线程同时获得锁资源比如 CountDownLatch 和Semaphore都是用到了AQS中的共享锁功能 AQS是抽象同步队列就是一个抽象类Reentranlock和信号量和CountDownLatch在底层都是基于AQS来实现的就是实现这些产品的一个公共的方法无论是锁是需要竞争那一把锁的信号量也是需要得到停车位的计数器-1计时器也是有多个线程来竞争同一把锁计数器-1多个产品是具有一个公共的功能的于是就把这个公共的功能封装起来实现了一个抽象类 3)设计整个AQS体系需要解决的三个问题就是: 3.1)互斥变量的设计以及多线程同时更新互斥变量时候的安全性 a)AQS采用了int类型的互斥变量来记录竞锁的一个状态0表示没有任何线程获取到锁资源大于等于1表示已经有线程获取到了锁资源并持有锁 b)如果是无锁状态竞争锁的线程则把这个state更新成1表示占用到锁此时如果多个线程进行同样的操作会造成线程安全问题AQS采用了CAS机制来保证互斥变量state的原子性 3.2)为竞争到的锁的线程等待以及竞争到的锁资源的线程释放锁之后的唤醒 c)未获取到锁资源的线程通过Unsafe类中的park方法对线程进行阻塞把阻塞的线程按照先进先出的原则加入到一个双向链表的结构中当获得锁资源的线程释放锁之后会从双向链表的头部去唤醒下一个等待的线程再去竞争锁 3.3)锁竞争的公平性和非公平性 另外关于公平性和非公平性问题AQS的处理方式是在竞争锁资源的时候 公平锁需要判断双向链表中是否有阻塞的线程如果有则需要去排队等待 非公平锁不管双向链表中是否存在等待锁的线程都会直接尝试更改互斥变量state去竞争锁 二)AQS的工作流程: 1)线程请求同步资源:当一个线程请求某一个同步资源也就是尝试进行加锁的时候AQS会尝试使用CAS来操作修改同步状态如果成功获取到了锁该线程可以继续执行 2)获取同步状态失败:如果说当前同步状态已经被其他线程占用了锁被其他线程获取了那么当前线程就需要将等待AQS就会将该线程封装成一个节点加入到双向链表中 3)自旋和阻塞:在等待队列中的线程会不断地进行自旋尝试获取到锁如果自旋一定次数还是获取不到锁那么就进入到阻塞状态等待被唤醒 4)线程释放锁:当线程完成了对资源的操作需要释放锁的时候这个线程就会调用AQS方法中的release方法这个线程会使用CAS来修改同步状态并唤醒等待队列中的一个线程或者是多个线程 5)等待唤醒线程:AQS在释放资源以后会从队列中选择一个或者是多个线程并将其唤醒被唤醒的线程会尝试再次去获取同步状态如果获取成功那么继续执行如果获取失败那么继续进入自旋或者是阻塞状态 Thread.sleep()方法可以让线程进入到阻塞状态让出CPU的执行权这个方法可以传入一定的参数让线程休眠指定的时间让CPU的执行权给到其他线程或者是进程操作系统底层会设置一个定时器当定时器的时间到了以后操作系统会再次唤醒这个线程Thread.sleep(0)虽然没有传递睡眠时长但是还是会触发线程调度的切换当前线程会从运行状态切换到就绪状态然后操作系统会根据优先级选择一个线程来执行如果有优先级更高的线程来等待时间片那么这个线程就会得到执行如果没有就会可能立即选择刚刚进入到就绪状态的这个线程来执行具体的调度策略取决于操作系统底层的调度算法 CAS保证多线程环境下共享变量操作的一个原子性 三)获取到AQS的同步状态就相当于是获取到了锁吗 在大多数情况下AQS中获取到同步状态确实是表示获取到了锁资源但是某些情况下获取到同步状态表示获取到了某一些条件而不是锁资源 1)当使用Reentranlock的时候AQS的子类会确保在获取到同步状态的时候该线程获取到了锁并且可以继续执行临界区的代码这种情况下获取到了同步状态确实是获取到了锁资源 2)但是对于AQS来说他还可以实现一些其它类型的同步器比如说信号量和CountDownLatch在这些场景下获取到同步状态并不是代表着获取到了锁资源而是获取到了特定类型的同步器所提供的信号或者是等待条件 索引是存储引擎中用于快速找到数据记录的一种数据结构就是类似于教科书中的目录部分可以通过目录快速的找到文章所对应的页码MYSQL也是一样的道理在进行数据查找的时候首先进行判断这个条件是否命中索引如果是就通过索引查找相关数据如果没有那么就需要进行全表扫描一条一条地将数据加载到磁盘中进行比对 1)如上图所示数据库在没有索引的情况下数据本身又分散在硬盘上面的不同位置在进行读取数据的时候摆臂需要前后摆动来进行查询数据这样在磁盘上查找数据非常消耗时间即使数据是按照顺序进行存放的那么也是需要按顺序从磁盘上从1位置到6位置进行读取还要将数据加载到磁盘上面6次这样的IO操作仍然是非常浪费时间 2)如果不借助任何数据结构来来帮助快速定位数据的话查找操作就是逐行查找比较从Col 2 34 开始进行比较发现不是继续下一行当前的表只有不到10行数据但如果表很大的话有上千万条数据就意味着要做很多很多次硬盘I/0才能找到 3)CPU必须先去磁盘中去查找这条记录找到之后加载到内存中再来针对于数据进行处理这个过程最好费时间的就是磁盘IO涉及到磁盘的旋转时间和磁头的寻道时间 如果没有索引:就需要加数据加载到内存中一个一个加载到磁盘里面进行比对遍历所有的数据将所有的数据加载到磁盘中我们先把第一条数据从磁盘加载到内存中依次进行数据比对相当于是顺序查找 有了索引之后减少IO次数B树里面每一个节点都是一个数据页要找77先加载根节点34到内存中发现77比34大那么直接向右走直接砍掉了一半的数据然后再加载89到内存中然后直接向左走最多3次IO从磁盘将数据加载在内存中是很浪费时间的 四)说说索引的优缺点: 优点:合理的增加索引可以提高数据的查询效率减少数据的查询时间 缺点: 1)创建索引和维护索引需要消耗一定的时间 2)索引需要一定的物理空间 3)对创建索引的表进行新增删除和修改的时候也需要同步动态维护索引会造成性能的影响 五)MYSQL不适合创建索引: 1)数据量太小:即使不创建索引查询的速率也是比较快的这个是或创建索引反而会增加维护的成本和查询时间查询的时间可能都比不上遍历索引的时间 2)数据区分度不高有大量重复数据的列不要建立索引: 比如说年龄性别这样的列构建索引反而会降低检索效率 3)查询条件包含函数操作索引会失效 4)频繁变更的表:经常要进行更新删除和插入操作 a)频繁进行更新的字段不需要创建索引因为更新数据的时候也是需要更新索引的如果索引太多那么更新索引的时候也会造成负担从而影响速率 b)如果对表创建的索引过多虽然提高了查询速度也会降低更新表的速度 六)InnoDB和MYSIM有什么区别 存储引擎是定义数据的存储方式和实现数据读取的实现逻辑 不同的存储引擎就决定了底层的文件系统中文件的物理结构存储引擎表示表的类型 每一张表都会对应着一个存储引擎每一个存储引擎来负责表结构在底层存数据到底是一个什么样子的结构 show vaiables like %storage_engines%查看默认存储引擎 show create table表名字显示出表的存储引擎 create table user(id int) engineinnodb造表的时候指定存储引擎 show create user; alter table 表名字 engineMYSIM MYSIM存储引擎数据和索引是分离的.MYD文件D代表Data是MYSIM的数据文件存放的是数据记录.MYI文件I代表index是MYSIM的索引文件MYSIM索引和数据是分离的 InnoDB只有一个IBD文件索引和数据是结合在一起的 1)数据存储的方式不同:MYSIM是将索引和数据存储到连两个不同的文件里面而InnoDB索引即数据数据即索引 2)InnoDB支持外键Innodb可以在表和表之间建立关联关系来保证数据的完整性但是MYSIM不支持外键约束 3)支持行级锁提高了程序的并发访问性能多个事务可以访问不同的行避免了锁定整张表的情况锁的粒度更细但是MYSIM只是支持表锁当一个事务对表进行修改操作的时候其他食物无法对表进行操作会出现性能瓶颈 4)InnoDB支持崩溃修复和自增列可以在数据库崩溃后进行数据恢复保证数据的一致性 InnoDB支持崩溃修复和自增列的崩溃修复本身是依靠日志来实现的底层是依靠日志Redolog重写日志重写日志就可以实现崩溃修复就是数据信息还没有刷盘到MYSQL的磁盘里面MYSQL崩溃了此时MYSQL就可以使用Redlog来实现崩溃修复 InnoDB依靠readdolog重写日志数据还没有进行落盘还没有放入磁盘里面突然掉电了突然发生意外事故了此时有redolog就可以崩溃修复了但是MSIM崩溃之后就需要人工手动恢复操作可能会导致数据的丢失和数据完整性的不一致问题 5)Innodb支持事务:innodb有ACID四大特性MYSIM针对于数据统计有额外的常数存储因此count(*)的查询效率比较高 Memory存储引擎:不支持事务不支持外键它是一种内存性的存储引擎所有的数据都存储在内存中不支持事务不支持外键本身支持hash索引和B树索引 七)说一说数据库的三范式: 第一范式:第一范式规定数据表中的每一个列是不可分割的最小单元 存储地址尽量分割成几个字段去填淘宝的京东在进行填写货物的地址的时候先让你进行填写省接下来让你选择市区接下来是详细地址为什么不把用户的地址分割成一个字段呢将地址分割几个成几个字段每一个字段都被拆分成不可分割的最小单元假设有一天某一个行政单位发生改变有一天口琴村变成XX村了此时如果都写到一块此时字段就不好修改替换的时候还会影响其他的如果表中的字段都是不可分割的最小单元那么此时就很方便的进行替换了还不会影响其他的字段 第二范式:存在非主属性对于主键的部分函数依赖 一个表当存在联合主键有两个主键字段充当整张表的联合主键(一个主键)不能说有一个非主键字段只依赖于联合主键中的一个而不依赖另一个联合主键不能存在非主键字段对于部分主键的依赖一定要对联合主键都依赖 第三范式:消除非主属性对于逐渐的传递函数依赖 表中的列不存在对非主键列的传递函数依赖一个非主键列3推出非主键列2非主键列2推导出主键列1从而非主键列3推出主键列1 八)内连接和外连接有什么区别 内连接和外连接是关系型数据库常见的连接操作: 内连接:两个表中都存在的字段最终才会包含在结果集中 左外连接:左外连接直接返回左表中的所有记录以右表中满足连接条件的匹配记录如果右表中没有匹配的记录那么右表的记录值就为null 右外连接:直接返回右表中的所有记录以及左表中满足要求的所有记录如果左表中没有符合要求的纪录那么左表的记录值就是null 九)MYSQL中索引的分类: 索引的分类: 一)按照字段特性进行分类: 1)主键索引:数据列不允许重复不允许为null在一张表只能有一个主键 2)唯一索引:数据列不允许重复况且允许为null值在一张表中允许多个列创建唯一索引 3)普通索引:基本的索引类型没有唯一性约束也允许为null值 4)全文索引:对文本的内容进行分词搜索 二)按照物理存储进行分类:聚簇索引和非聚簇索引 三)按照索引数量进行分类: 1)单列索引:针对表中的某一列创建的索引可以根据该列的值快速定位到所对应的记录单列索引适用于针对于单个列进行频繁的查询排序和过滤的场景比如说可以针对于用户ID列创建索引以便根据用户ID快速的进行查询 2)联合索引:针对于表中的多个字段进行建立索引也被称之为是复合索引或者是组合索引 聚簇索引并不是一种单独的索引类型而是一种数据存储的方式所有的用户记录都存储在了叶子节点上面数据行和相邻键值是存储在一起的B树分成聚簇索引和非聚簇索引 数据自动添加的时候底层的B树就已经自动创建了一个聚簇索引 1)每一个页中的记录按照主键值的大小顺序组成了一个单向链表 2)各个存放用户记录的页也是根据记录的主键顺序大小组成了一个双向链表 3)放目录项记录的页分为不同的层次在同一层次中的页也是根据页中目录项记录的主键大小顺序排成一个双向链表所有的用户记录都会存放在聚簇索引的叶子节点处; 4)这种聚簇索引并不需要我们在MySQL语句中显式的使用INDEX 语句去创建 InnDB 存储引擎会 自动 的为我们创建聚簇索引由于对于MYSQL数据库来说数据物理存储排序方式只能有一种所以每一个MYSQL的表中只能有一个聚簇索引一般情况下就是该表的主键 5)InnoDB的主键尽量选择有序的顺序ID而不建议使用无序的ID比如说UUIDMD5字符串作为主键无法保证索引的顺序增长 聚簇索引的缺点: 1)聚簇索引的插入速度严重依赖于插入顺序按照主键的插入顺序是最快的插入顺序否则会出现页分裂会严重影响到性能所以对于Innodb存储引擎来说一般选择自增的ID作为主键 2)对于聚簇索引来说主键的更新代价很大因为可能会导致被更新的行进行移动对于Innodb表来说一般定义为表不可更新 上面建立的聚簇索引都是只能是主键作为搜索条件的时候才可以发挥作用因为B树中的数据都是按照主键进行排序的那么如果想要以别的列作为搜索条件怎么办呢肯定是不能从头到尾按照链表全部遍历一遍答案是可以创建多个B树不同的B树采用不同的排序规则 这个时候c2列在叶子节点是按照升序来进行排列的c2列下面还会存放主键ID 如果每一个二级索引都存放完整数据那么就太浪费空间了 B树作为索引的注意事项: 1)根节点位置万年不动: 1.1)一开始现在只有一个页page1就是来存放一条一条的记录的假设一个页能够存放三条记录现在这个第一个页中已经存放三条记录了这个时候再去像这个页中添加数据不是新创建一个页来存放第四条数据 1.2)此时会创建一个新的目录页page2将原来第一个页中的三条数据放到这个新的page2中因为此时又新增了一条数据但是此时page2也放不下此时又会开辟一个新的页page3来存放新的记录假设这条记录的主键值比page2的主键值都大如果不是那么就在进行调整此时会将page2和page3中的最小值存放到page1里面此时page1就变成了目录页 1.3)当为某一张表创建B树索引的时候聚簇索引不是人为创建的默认就有都会为这个索引创建一个根节点页面最开始表中没有数据的时候B树的索引对应的根节点既没有用户记录也没有目录项记录随后向表中插入数据的时候数据会记录在根节点中 1.4)当根的可用空间用完之后向这个表中插入记录此时会将根结点的所有记录复制到一个新开辟的页中比如说页a然后针对于这个页进行页分裂的操作得到一个新的页页B此时新插入的记录根据主键值的大小就会被分配到页a或者是页b中而根节点是作为存储目录项记录中的页 这个过程需要特别注意的是一个B树的索引的根节点自诞生开始就不会再进行移动只要针对于某一张表建立了索引那么它的根节点的页号就会被移动到某一个地方从今以后凡是InnoDB引擎在使用到这张表的时候都会从固定的地方取出根节点的页号从而来访问索引 2)目录项记录的唯一性 3)一个页中至少存放两条记录 十)聚簇索引和非聚簇索引有什么区别? 1)叶子节点上存储的东西不同:聚簇索引的叶子节点上存放的是完整的数据而非聚簇索引叶子节点上存放的是主键的ID 2)查询效率不同:聚簇索引的查询效率要比非聚簇索引效率要高 3)数量限制不同:聚簇索引通常是主键索引而主键一张表只能有一个但是非聚簇索引表中是可以有多个的 回表查询:非聚簇索引的叶子节点存放的并不是真实的数据而是聚簇索引的主键ID所以当时使用到普通索引查询的时候需要先查询到主键索引然后再从主键索引中查询到真实的数据这个过程就是回表查询 1)所以说在InnoDB存储引擎中不建议使用过长的字段来作为主键因为所有的二级索引的叶子节点都是主键值过长的主键索引会使二级索引变得很大 2)使用费单调性的字段作为索引不是一个好主意因为InnoDB的数据文件本身就是一颗B树而非单调性的主键在进行插入新记录的时候数据文件会维持B树的特性而进行分裂调整十分低效所以使用自增字段作为主键是一个不错的选择 十一)MYSQL索引选择顺序结构的合理性: 全表扫描:将所有的索引依次加载到内存中加载一个数据需要一次磁盘IO数据库的查询本身就是查询索引的操作当数据量比较大的时候索引都是存放在磁盘上面的当使用索引进行查询的时候不可能将整个索引全部加载到内存里面而是用到谁加载谁 1)Hash索引:只有Memory存储引擎支持哈希索引下面是哈希索引的缺点: 1.1)哈希索引只能支持等于不等于还有in查询如果进行范围查询哈希索引的时间复杂度会退化成O(N) 1.2)哈希索引本身并没有顺序无法支持order by 1.3)针对于联合索引无能为力 1.4)一般来说针对于等值查询来说哈希索引的效率要更高不过就是有一种情况索引的重复值如果很多那么哈希索引的效率就会降低这是因为当遇到哈希冲突的时候一般使用链式法来解决哈希冲突链表的查询效率过低所以说不建议hash索引建在重复列比较多的字段比如说姓名年龄性别 但是InnoDB支持自适应哈希索引什么情况下才会使用到自适应哈希索引呢就是当某一个数据经常访问当满足一定条件的时候就会将这个数据页的地址存放到哈希表中这样子下一次进行查询的时候就可以直接找到这个页面的所在位置 innodb默认使用常驻哈希索引是不需要进行指定的 使用自适应哈希索引目的是为了方便根据SQL的查询条件很快的定位到叶子节点尤其是当B树比较深的时候使用哈希索引可以快速的定位到叶子节点可以加快数据检索效率 mysql show variables like %adaptive_hash_index; 2)二叉搜索树: 1)二叉搜索树可能退化成单分支的树退化成链表 2)二叉搜索树一个节点只能存储一个值进行一次磁盘IO只能比较一次 3)树的高度比较高就算不退化成链表磁盘IO也很高 3)AVL树:和二叉搜索树类似树的高度太高了每访问一个节点就需要进行一次磁盘IO操作虽然AVL树相比于二叉搜索树来说维持了自平衡的特性相比于B树一点优势都没有 4)红黑树:原因和不使用B树差不多甚至还不如B树 1)红黑树不如B树更矮胖红黑树高度更高要进行多次磁盘IO 2)红黑树一个节点只能存放一条数据一次IO只能进行一次比较而B树一次磁盘IO可以进行多次比较 3)红黑树插入节点不仅要改变频繁节点颜色有时候左旋右旋插入性能非常低老保证红黑树的特征插入删除不如B树B树有冗余节点插入和删除效率更高 4)处理范围查询不方便红黑树也要进行深度优先遍历才能得到范围内的数据 5)红黑树和B树非叶子节点即要存数据又要存放ID没有冗余节点没有冗余字段所以需要的页非常多每一个页中存放的ID是非常少的只有B树叶子节点全部是完整的数据 红黑树本质上是二叉树插入很麻烦频繁左旋右旋一个节点值只能存放一个数据是可以要满足平衡二叉树的性质需要大量的旋转和更改颜色来维持红黑树的特性增删效率都很低 B树: 1)N个关键字有N1个分支 2)数据分布在整个B树中B树的叶子节点和非叶子节点都存放数据整个数据的搜索可能在非叶子节点结束它的搜索相当于是做一次二分查找只能通过中序遍历来排序 B树和B树的区别: 1)B树K个节点就有K个关键字但是B树K个节点有K1个关键字 2)B树非叶子节点的关键字也会出现在叶子节点中并且是作为子节点中的最大值或者是最小值 3)B树的非叶子节点仅仅只是用做索引而不保存最终的完整的数据记录但是B树中非叶子节点及保存索引也保存数据的完整记录 4)B树的所有关键字都在叶子节点出现叶子节点构成一个有序链表并且叶子节点也是按照从小到大的顺序来进行排序的 十二)为什么索引选择B树而不选择B树 1)B树的查询效率更稳定: 所有的数据都存储在叶子节点上但是B树可能查询终止在叶子节点上 2)B树处理范围查询更方便:B树只能通过中序遍历来处理范围查询而B树可以直接通过截取链表中的一部分进行处理范围查询 3)B树插入和删除性能更好: B树有大量的冗余节点所有的非叶子节点都是冗余索引这些冗余索引使得B树在进行插入删除结点相比于B树的效率更高 4)B树的IO次数更少: B树的非叶子节点不存放具体的实际的记录数据而是存放索引B树的所有结点既存放用户的数据又存放索引因此当数据量相同的情况下B树的非叶子节点可以存放更多的索引因此在查询的时候IO查询次数更少效率更高16K的页只存ID是可以存储很多ID的但是如果这个16K页既存储数据又存储ID在同一个页中存储的ID就会比B树同等的叶子节点少很多B树的非叶子节点一次IO可以得到更多的ID就可以进行更多的比较那么在同等查询下遍历B树的非叶子节点的个数就要B树少比B树遍历IO次数就会很少效率会很高MYSQL的根节点是常驻内存的B树的一个页存储的目录项要比B树的存储的目录项少所以B树更矮胖 十三)B树的存储能力如何为什么说只需要一次磁盘IO或者是三次磁盘IO 1)InnoDB存储引擎中的页的大小是16KB假设BIGINT占用8个字节指针类型占用8个字节也就是说一个页中大概存储16KB/(8B8B)1000个键值也就是说深度是3的B树可以存放10^3*10^3*10^310亿条记录这里面是假定一个数据页可以存储10^3条行记录数据了 2)但是实际情况中可能每一个节点可能不能完全填充满因此在数据库中B树的高度一般都是在2-4层MYSQL的存储引擎在设计的时候是将根节点常驻内存的也就是说再进行查找某一个键值的行记录最多只是需要1-3次磁盘IO操作的 十四)一条SQL语句的执行流程: 简单来说 MySQL主要分为 Server 层和存储引擎层Server层主要包括连接器、查询缓存、分析器、优化器、执行器等还有一个通用binlog日志模块(用于整个数据库操作记录主从复制的关键)存储引擎层主要负责数据的存储和读取 1)连接器 客户端想要对数据库进行操作时连接器就是用来负责跟客户端建立连接、获取权限、维持和管理连接的连接器支持短连接也支持长连接同时为了避免频繁创建和销毁连接造成性能损失可选择利用连接池进程管理 2)查询缓存: 2.1)查询缓存主要用来缓存我们所执行的 select语句以及该语句的结果集如果开启了查询缓存执行查询语句的时候会先查询缓存如果缓存 key 被命中就会直接返回给客户端在数据变换频繁的表中是不推荐使用的当一张表的数据发生变化其所有缓存都将清空 2.2)一般MYSQL的缓存尽量在静态表中进行使用就是很少更新的表MYSQL服务器如果在查询缓存中发现了这一条SQL语句(缓存的SQL--key结果----value) 那么就会直接将结果返回给客户端如果没有就直接进入到解析器由于MYSQL命中缓存的概率非常低所以MYSQL8.0就将这个功能给删除了 2.3)SQL语句作为key查询结果是String也是value 1.1)SQL有空格 1.2)函数调用NOW()函数虽然两个SQL相同但是NOW()函数的结果不一样 1.3)缓存失效比如说缓存成功的缓存了一条SQL语句但是userID的记录我在原表中进行删除了再次查询就查不到了数据进行修改的时候再次调用结果就不正确了但是缓存只是适用于不经常修改的表中 3)分析器:语法分析语义分析知道SQL要做什么 3.1)词法分析:因为输入的命令是是由多个字符串和空格组成的一条SQL语句MYSQL需要进行识别里面的字符串都是代表的什么含义就比如MYSQL把你输入的这个select这个关键字识别出来这是一个查询语句也要把字符串user识别成表名把userID识别成userID 3.2)语法分析:把提取出的关键词转换为抽象语法树后进行检验。主要就是判断你输入的 sql 是否正确是否符合 MySQL 的语法并会提示you have an error in your syntax 4)优化器: 一条查询可能有很多种执行方式最后都返回相同的结果优化器就是在其中找到最好的执行计划优化器会根据IO和CPU成本选出代价最小的索引进行执行 4.1)逻辑查询优化:索引优化在优化器中就会确定出SQL的执行路径比如说是全表检索还是索引检索还有可能说就是这个字段有多个索引那么优化器会最终判断到底要走哪一条索引最终生成执行执行计划 4.2)语法查询优化:比如说子查询改成多表连接就是通过SQL的等价变换来提升查询效率直白一点说就是换一种写法可能查询效率更高改变各个表的连接顺序 select * from user where username张三 and gender男 五)执行器: 执行器会判断你是否具有相关操作的权限没有权限就会发生报错如果具备权限就执行SQL进行返回如果设置了查询缓存那么就会将查询结果进行缓存最终会调用存储引擎的API也就是一些接口调用存储引擎之后再去调用文件系统实现对数据的查询 执行引擎根据执行计划查询数据并把结果集返回客户端 六)BufferPool BufferPool起到一个缓存的作用,MySQL 的数据最终是存储在磁盘中的如果没有 Buffer Pool那么每次的数据库请求都会磁盘中查找这样必然会存在 IO 操作但是有了 Buffer Pool只有第一次在查询的时候会将查询的结果存到 Buffer Pool 中这样后面再有请求的时候就会先从缓冲池中去查询如果没有再去磁盘中查找然后在放到 Buffer Pool 中 如果没有索引查询那么MYSQL会从第一个数据页开始从磁盘加载到执行引擎的缓存池中 https://blog.csdn.net/qq_43618881/article/details/118657040 innodb数据引擎的更新操作: 1)innodb存储引擎首先先去BufferPool中查找id1的记录没找到就会从磁盘中进行查找如果查找到就会把这一条记录加载到缓冲池bufferPool中由于是更新操作innodb会诊对于这一条记录加锁 2)SQL语句执行前默认是开启事务的考虑到更新失败后的数据回滚把更新前的数据写入undolog中 3)更新BufferPool中的数据 4)此时内存中的数据已经更改此时磁盘上面的数据还没有修改但是为了防止数据的丢失需要先将更新后的值写入到redo log buffer中 5)此时就可以进行事务的提交了事务提交的同时会按照一定的策略将redo log buffer中的数据刷入到磁盘中从而避免了数据的丢失然后更新binlog 十五)如何创建索引删除索引 一)创建索引: 1)在创建表的时候构建索引: 1.1)使用create table创建表时除了可以定义列的数据类型外定义主键约束、外键约束或者唯一性约束而不论创建哪种约束在定义约束的同时相当于在指定列上创建了一个索引,没有给索引起名字那么就直接使用字段名当索引 1.2)可以在创建表的时候构建索引: 2)创建表以后创建索引: --创建索引,具体格式是 index 索引名字(表中的字段名字)
create table user(
userID int,
username varchar(30),
index idx_username(username)
);
--1.命令查看索引
show create table user;
--2.查看索引
show index from user;
--3.创建唯一索引,下面的username就不能有相同的值
create table user(
userID int,
username varchar(30),
unique index idx_username(username)
);
--4.删除主键索引,不能有auto_increment
alter table user drop primary key;
--5.创建联合索引
create table user(
userID int,
username varchar(30),
index idx_username(userID,username)
);
--6.创建全文索引,只会拿前20个字符作为索引
create table user(
userID int,
username varchar(30),
fulltext index text_username(username(20))
); 十六)哪些情况适合创建索引呢 1)针对字段有唯一性的限制:索引本身是可以起到约束的作用的例如唯一索引和主键索引都是可以起到约束作用的因此在数据表中如果某一个字段是唯一的就可以创建唯一性索引或者是主键索引这样就可以快速地根据索引确定该条记录例如学生表中学号是唯一的字段针对该字段建立唯一索引就可以快速地确定学生的信息 2)频繁做Where查询的字段:某一个字段经常在Select语句中经常被使用到那么就需要给这个字段建立索引了尤其是数据量比较大的情况下创建普通索引就可以大幅度提升数据查询的效率假设现在学生表中有1000条数据查询name张三的信息就可以建立索引 针对于多表的join的连接字段要创建索引where的条件要创建索引不能连接过多的表 3)经常group by和order by的列:然后针对于order by来说如果存在索引那么这个索引已经排好序了于是就节省了排序的时间同理group by是相同的字段组合成一组同理索引已经是默认排好序的那么排好序之后相同的字段挨在一起了; 先考虑student_id在考虑create_time创建联合索引效率更高 单独使用group by你就针对该字段建立一个索引order by也是同理如果既要进行group by又要进行order by那么首先将group by放在前面 4)经常要update和delete的条件列: 对数据按照某个条件进行查询后再进行 UPDATE 或 DELETE 的操作如果对 WHERE 字段创建了索引就能大幅提升效率,原理是因为我们需要先根据 WHERE 条件列检索出来这条记录然后再对它进行更新或删除,如果进行更新的时候更新的字段是非索引字段提升的效率会更明显这是因为非索引字段更新不需要对索引进行维护 5)针对于distinct的字段需要建立索引:select distinct(studentid) from user 因为在对去重的字段建立索引的时候去重的字段本身就挨着对于紧挨着的字段进行去重本身就简单很多因为索引本身就是排好序的 6)针对于列的类型小的字段建立索引: 假设说现在有一个字段叫做ID那么这个ID类型上可以在考虑满足实际要求的情况下尽量选择类型小的创建表以后添加这个表中数据的时候占用表空间比较少一些另一种情况就是当我们给这个字段添加索引的情况下假设如果针对id建立索引id占用的空间比较大那么在非聚簇索引中一个页中存放的数据项就会相对于来说比较少如果id本身占据的数据相比较小那么一个页中能够存放的数据项就比较多那么此时一个页中放的数据更多那么这颗B树也就会变得越扁平此时IO次数就会更少 假设极端情况下这个ID是一个主键此时ID所占的大小此时影响的就不光是一个聚簇索引了而是会影响所有的非聚簇索引 7)针对于字符串的前缀创建索引: 当字段类型是字符串类型的时候(varchartext等)有的时候需要索引长度很长的字符串这就会使得索引占用的空间很大查询的时候浪费大量的磁盘IO影响查询效率此时就可以之将字符串的一部分前缀作为索引这样可以大大的节省空间从而提升索引效率 create index 索引名字 on 表名(字符串的列(前几个字符作为索引)) 如何选择截取的字符的大小呢 如果截取字符截取的过多那么会达不到节省空间的目的如果截取字符截取的太少那么字段的离散度和选择度就会变得很低那么如何进行计算不同长度的选择性呢 1)首先先观察一下字段在全部数据中的选择度: select count(distinct(address))/count(*) from user 2)通过不同长度去计算和全表的选择性进行对比:count(distinct(left(address10)))/count(*) 这个值越接近1越好说明越有区分度 elect count(distinct left(address,10)) / count(*) as sub10, -- 截取前10个字符的选择度
count(distinct left(address,15)) / count(*) as sub11, -- 截取前15个字符的选择度
count(distinct left(address,20)) / count(*) as sub12, -- 截取前20个字符的选择度
count(distinct left(address,25)) / count(*) as sub13 -- 截取前25个字符的选择度
from shop; count(distinct(left(address10))/count(*)1说明截取前10个字符也是不重复的 假设现在使用到了前缀索引只是把address列的前12个字符放到了二级索引中下面的这个查询就有点尴尬了: select * from user order by address limit 12 但是这个二级索引不包含完整的address列信息所以无法对前12个字符相同后面字符不同的记录进行排序所以使用前缀索引不支持索引排序因为唯一索引的选择性是1这是最好的索引选择性 假设一张表中有50条记录现在针对adress列的前12个字符建立了前缀索引那么此时就会出现问题假设50条记录中前49条中前12个字符都是都是相同的那么此时针对于order by进行排序或者是group by进行排序麻烦索引使用前缀索引无法支持索引排序 8)选择区分度高的字段作为索引 select count(要计算区分度的字段)/count(*) from 表名 假设现在有100万条数据假设根据性别建立索引男生50W女生50W那么此时基数就是2所谓的区分度非常差劲这个时候针对gender创建索引就不太好的 9)使用最频繁的列放在联合索引的左侧: 十六)如何排查慢SQL 在MYSQL中排查慢SQL通过开启慢查询日志来开启排查慢SQL然后分析和解决慢SQL 慢查询是MYSQL提供的一种慢查询日志具体指运行时间超过long_query_time的SQL那么会被记录到慢查询日志中这个参数默认是10意思就是说运行时间超过10s以上的语句 默认情况下MYSQL是不会开启慢查询日志况且需要我们手动来设置这个参数如果不是条有需要的话一般是不建议开启这个参数的因为开启慢查询日志会给MYSQL服务器带来一定的性能影响慢查询日志支持将日志记录写入到文件也支持将日志记录写入到数据库表 通过下面这个命令就可以来进行查询慢日志是否开启: 如果要开启慢查询日志:但是下面这个命令只会对当前数据库生效如果MYSQL重启也会消失如果要想永久生效就必须修改MYSQL的配置文件my.cnf 修改阈值: 查询慢查询日志条数: SHOW GLOBAL STATUS LIKE %Slow_queries%; 找到慢SQL以后再执行Explain执行计划来查询慢SQL然后分析慢SQL的原因 数据量过大加缓存来减少数据查询的次数或者是分库分表要进行垂直分割和水平分割 没有加索引直接加索引就好了 SQL语句写法问题导致没有触发索引从而来调整查询SQL 十七)explain执行计划: 1)table:查询出来的每一条记录都对应着一张单表因为结果集可能出现多条记录 2)id:最终的结果最上面的记录称之为驱动表下面的记录称之为是被驱动表有时候优化器会针对驱动表和被驱动表做一个替换表示在一个大的查询语句中每一个select关键字都对应着一个唯一的id查询语句中有两个表那么就一共有两条记录但是select关键字一共只有一个那么id也就只有一个一层嵌套一个select那么就代表有一个id是外查询有一个id是内查询内查询的id比外查询的大 1)查询优化器可能对涉及到子查询的查询语句进行重写转化成多表查询的操作 2)union去重内部需要创建临时表来去重unionall不去重 3)select_type:可以确定select关键字对应的查询的类型确定小查询在大查询中扮演了一个什么样子普通查询和连接查询都是simple类型(不包含union不包含子查询) 1)查询语句中不包含UNION或者子查询的查询都算作是SIMPLE类型 2)union前面是primary 后面是union 3)对于子查询来说最左边就是primary该子查询的第一个SELECT关键字代表的那个查询的select_type就是SUBQUERY,该子查询没有转化成多表连接 4)最终转化成了多表连接: 4)type: 1)system:当表中只有一条记录况且存储引擎是MYSIM的时候因为统计记录时精确的查询就是sysyem越在前面效果越好MYSIM存储引擎内部维护一个变量来记录表中字段的个数但是在innodb中它统计数据就不是精确的了没有维护内部变量此时即使是数据中只一条记录也是走的是全表扫描 2)const:当根据主键或者是唯一二级索引与常数进行等值匹配的时候对于单表的访问方法就是const假设现在User表中的userID是主键username是唯一索引此时针对于这两个字段进行等值匹配的时候type的类型是const create table user(userID int,username varchar(30),primary key(userID),unique index username_index(username)); 3)eq_ref:再进行连接查询的时候如果被驱动表是通过主键或者是唯一二级索引等值匹配的方式来进行访问的如果该主键或者是唯一二级索引是联合索引的话所有的索引列都必须进行等值比较此是被驱动表的访问方法就是eq_ref select * from user where username123此时这个字符串没有加双引号此时就会使用到函数此时就会索引失效 select * from s1 inner join s2 on s1.ids2.id 在s2表中找到值和s1.id进行相同的记录此时的查询过程是在s1中取出每一个id值然后去s2表中去查找有没有s2的id和s1相同的所以针对s1是全表扫描针对于s2是使用到了索引 4)ref:当通过普通的二级索引和常量进行等值匹配的时候来查询这张表那么此时的查询结果就是ref 5)ref_or_null:当对普通二级索引进行等值匹配的时候该索引列的值也可以是null的时候那么对该表的访问方法就是ref_or_null 6)index merage:当时用到or的时候 7)unique_subquery:驱动表是全表扫描 8)range:如果使用索引来获取某些范围区间内的记录那么就有可能使用到range访问方法 9)index:可以使用到覆盖索引但是需要扫描全部的索引记录的时候就会使用到index ref使用非唯一索引扫描或唯一索引前缀扫描返回单条记录常出现在关联查询中eq_ref 类似ref区别在于使用的是唯一索引使用主键的关联查询 5)key:真实使用到的索引 6)possible keys:可能是用到的索引 7)key_len:实际使用到的索引长度主要针对于联合索引有参考意义 MYSQLint占用四个字节如果说int类型的变量可以是null那么实际使用到的索引长度会多一个字节针对于字符串类型MYSQL中utf-8编码字符类型占用三个字节null的情况1个字节还要使用2个字节来记录实际长度因为varchar长度是不确定的针对于联合索引来说联合索引使用到的索引长度越长那么查询效果越好如果没有使用到索引那么key_len长度是0 8)ref:当时用到索引或者是等值查询的时候与索引列进行等值查询的匹配的对象信息比如说只是一个常数或者是某一个列 rows和fiter可以联合在一起进行查看: rows:预估的需要读取的记录数值越少越好值越少页越少 fiter:rows中能够查询到记录的概率的百分比百分比越高越好 1)这个SQL语句表示的是382条数据满足key1z其中只有百分之10的记录满足common_fielda 2)下面中的这个SQL语句代表的是一共有9895条记录中只有10%的数据满足s1.common_fielda然后MYSQL再拿着10%的驱动表的记录去匹配被驱动表进行连表查询 10)extra: 1)no tables used:没有任何表被使用 2)imposble where 3)using where:没有使用到任何的索引此时针对于s1没有任何的索引就是当使用全表扫描的时候完成针对于某一张表的查询并且该where与具有搜索条件但是还没有索引 4)using index:使用覆盖索引建议使用覆盖索引联合索引使用覆盖索引比较好 key1本身有索引这里就是用到了覆盖索引 5)using condition:有些搜索条件虽然使用到了索引列但是却不能够使用索引索引下推是再进行索引遍历过程中对索引中的字段先做判断直接过滤掉不满足要求的纪录从而减少回表的次数 1)先找key1z的所有记录然后进行回表查询此时再从主键索引中筛选key1 like a%的记录此时回表次数比较多 2)下面这种情况针对于key1z使用到了索引索引遍历过程中然后再根据key1进行过滤掉不满足要求的纪录甚至此时经过过滤完成之后符合两个条件的记录一个主键ID值都没有此时就不用再进行回表查询了此时回表次数相比于第一次来说比较少很多 6)再进行连接查询的过程中当被驱动表不能有效地利用索引来加快访问速度MYSQL会为其分配一块名字是join buffer的内存块来加快查询速度 下面common field没有索引 7)not exists:此时的id字段是一个主键主键是不可能是null的 8) zero limt: 9)using intersect 10)using filesort:不能使用已经排好序的B树了显然性能很低下当我们出现这个字段的时候是很有必要给这个字段加上一个索引所以说如果某一次查询需要使用到文件排序是在的的方式来进行查询那么此时就会在执行计划中的Extra列显示using filesort 下面的字段针对于common_field字段是用排序会使用到文件排序 11)using tempory:比如说要进行去重操作的时候如果没有使用到索引group by distinct 是使用到临时表有时候是用到临时表进行去重但是索引本身已经是有序的的 十八)SQL优化: 一)关联查询优化: 左外连接: 在explain执行计划中上面是驱动表下面是被驱动表 1)外层循环是驱动表内层循环是被驱动表是从外层循环的驱动表中取一条数据然后根据连接条件然后去被驱动表中去查找匹配关系正常来说是从驱动表中取一条数据在被驱动表中去进行遍历根据指定的条件进行筛选然后再从驱动表中再去一条数据假设驱动表中有20条记录被驱动表中有30条记录那么一共要链接的次数就是20*30 假设A表中的个数是A个B表中的个数是B个从下面的公式来看A越小越好所以选择小表驱动大表 2)此时两张表中的字段都没有都没有索引从这里面可以看出使用joinbuffer将被驱动表的数据存储起来 3)此时尝试给被驱动表添加索引:可以提升查询速度 create index classID_index on class(classID); 内连接:优化其实有权利决定谁是驱动表谁是被驱动表优先选择有索引的作为被驱动表对于内连接来说如果表的连接条件只能有一个字段作为索引那么有索引的字段所在的表会被作为被驱动表对于内连接来说查询优化器是可以决定谁作为驱动表谁作为被驱动表出现的给被驱动表加索引如果表的连接条件中只能有一个字段有索引那么有索引的这个字段将会作为被驱动表出现 对于内连接来说在两个表的连接条件都存在索引的情况下会选择小表(数据量小的表)作为驱动表出现小表驱动大表多表关联查询时小表在前大表在后还要针对于大表建立索引 join的底层实现原理: 简单循环嵌套算法执行效率太慢所以要给被驱动表添加索引当每一次取出驱动表的的一条记录的时候就不需要再去循环扫描被驱动表了而是直接根据索引和查询条件来确定被驱动表中的正确的数据 但是这个时候对于简单循环嵌套算法来说join原理每一次取出驱动表中的数据的时候都需要将被驱动表中的数据记载到内存中假设A表也就是驱动表中的数据有A条那么就需要加载被驱动表A次此时磁盘IO吃满 1)尽量将驱动表和被驱动表都加载进来尽可能多加载A并加载B的所有记录 2)如果可以一次性的将A全部加载到joinbuffer中那最好了B只需要加载一次就可以了 3)此时假设将A加载到joinbuffer中的次数是K次直接将B加载到内存K次也可以 4)在这里面缓存的不仅仅是查询条件还有select后面要展示的字段所以说尽量不要使用select *无用的列会占用join buffer的空间join buffer中存放驱动表的条目数变少 5)有N个join关联的sql会被分配N-1个join buffer所以查询的时候尽量减少不必要的字段可以让joinbuffer存放更多的列 used_column_size是A表中每一条的大小小表驱动大表是为了减少外层循环的趟数 十八)索引覆盖: 覆盖索引是指在某一次查询里面某个索引的值已经包含了所有的查询需求此时不需要再次进行回表查询了假设下面的查询针对于age建立了索引
