Mysql篇索引分为哪几大类按结构划分BTree索引默认、Hash索引、全文索引、RTree空间索引 按功能划分 主键索引一张表只能有一个主键索引主键索引列不能有空值和重复值 唯一索引唯一索引不能有相同值但允许为空 普通索引允许出现重复值 组合索引对多个字段建立一个联合索引需要遵循最左匹配原则 全文索引myisam引擎支持也就是倒排索广泛用于搜索引擎MyIsAm和InnoDB的区别 *1.InnoDB支持事务、外键、行级锁这些都是MyIsAm不支持的MyIsAm还是表级锁2.MyIsAm的索引和数据分开存储非聚集索引InnoDB的索引和数据一体化聚集索引叶子节点直接存整行数据3.MyIsAm的访问速度一般InnoDB快聚簇索引和非聚簇索引的区别聚簇索引索引和数据一体化叶子节点保存了完整的行数据非叶子节点存储记录到主键和页号 非聚簇索引辅助索引将数据与索引分开存储叶子节点指向了数据对应的位置不是完整的行数据;也就是叶子节点存主键id在根据主键id查聚簇索引找到最终的行数据。一个表中没有创建索引那么还会创建B树吗会的如果有主键会根据主键创建聚簇索引如果没有主键也会创建聚簇索引也就是隐式索引rowid二叉树、平衡二叉树、红黑树、B树、B树的区别二叉树每个节点最多有2个子节点分为一左一右但很容易形成单链表因为不平衡 平衡二叉树每个节点最多有2个子节点左右的子树高度差不超过1缺点是当数据多查询变慢树的层高 红黑树是一种自平衡的二叉树左右的子树的高度差不超过2倍每个节点都是有颜色属性通过颜色规则旋转保持平衡在新增修改更加高效所以有了红黑树在jdk8的hashmap底层有用到B树每个页有多个子节点每个节点都存完整数据查询到了就直接返回B树跟B树结构一样但只有叶子节点存完整数据非叶子节点只存索引所以树的层次不高所有的叶子节点通过链表连接记录前一个节点和后一个节点的位置方便范围查询一个b树中大概存放多少条索引记录默认的页可以存16k已经很大了3层大概有16*1600*16004096,0000什么是自适应的哈希索引快速访问数据库中数据的索引结构这个是InnoDB引擎的功能当某个索引使用频繁底层会自动创建一个哈希索引我们都知道hash查询速度快时间复杂度o1。为什么官方建议使用自增主键作为索引字符串索引与自增索引区别1自增主键的数据类型是数值占用空间小而字符串是通过UUID占用空间大2数据最终会插入某个区域的磁盘中磁盘读写分为顺序读写和随机读写自增主键是顺序插入顺序读都非常快而字符串是随机读写我们买机械磁盘是分顺序读写速度和随机读写速度是不一样的3读取由B树的二分查找法顺序有序方便快速查询像uuid这种字符串就是在磁盘中随机写也随机读容易出现页分裂。使用int自增主键最大id是10删除9,10那么再添加一条记录最后的id是id是11如果重启Mysql的话是9没有重启是11如果重启取目前表最大的id值索引的缺点索引过多都会创建B树结构占用磁盘空间过多如果新增修改频繁也有时间消耗更新索引结构导致写操作变慢。如果是大段文本内容如何优化1字段拆分将一个大字段拆分多个小字段分为前中后三部分2主副表分离将文本内容拆出去形成副表主表只存摘要部分提升查询效率3使用Es创建索引进行分词引用大段文本内容非聚簇索引为什么不存数据地址而存主键因为有聚簇索引我们通过主键就可以使用聚簇索引找到对应的行数据 如果非聚簇索引也存数据地址当我们删除数据的时候数据地址也会删除我们也要更新非聚簇索引按之前的只更新聚簇索引就可以。什么是回表操作 *因为有非聚簇索引非聚簇索引没有保持完整的行数据查询的时候先通过非聚簇索引中找到主键再根据主键查询聚簇索引找到对应的行数据这个过程就是回表。什么是覆盖索引 *覆盖索引不会进行回表操作就是想要查询到字段值在此索引中有 例子select name from 表 where nameabc;如果我们表的主键id删除了没有主键那如何回表?我们底层会有个隐式索引rowid如果没有主键会根据rowid进行回表查询什么是联合索引、组合索引、复合索引根据多个字段进行创建的索引按照当前列的顺序生成B树索引遵从最左原则什么是唯一索引会影响性能嘛唯一索引必须唯一但允许有空值 会影响性能但影响到新增的速度可以忽略不计如果没有根据墨菲定律必有脏数据产生。如果使用先查后查的方式必须有事务想避免脏数据产生就需要锁表并发数量就少。 alter table 表 add primary key 表(字段);--主键索引CREATEunique index 索引名 on 表(字段);--唯一性索引CREATEindex 索引名 on 表(字段);--普通索引CREATEindex 索引名 on 表(字段1,字段2);--复合索引什么时候适合创建索引什么时候不适合创建索引 *适合1.频繁操作where条件语句查询数据量过多2.关键字段、主键、外键3.排序字段、统计字段 不适合1.频繁更改的字段2.表数据量少3.有使用函数的字段4.索引数量尽量不超过5个什么是索引下推目的就是提升索引的利用率就是多使用复合索引减少回表的次数。哪些情况会导致索引失效 *1.计算函数2.like查询以%开始3.不等于!范围查询4.is notnull和 isnull是否走索引取决null数据占的比例 可使用explain 查询SQL执行计划看是否走索引为什么like查询以%开始索引会失效并不一定会失效如果name字段设置索引select name from 表 where name like “%xxx”;这种使用覆盖索引不需要回表查询完整数据这样索引会生效。如何查看一个表的索引1show index from 表2也可以使用explain 查询SQL执行计划查看能否查看索引选择的逻辑可以需要开启这个权限然后执行SQL语句再使用optimizer_trace进行观察SETsession)OPTIMIZER_TRACEenabledon,END_MARKERS_IN_JSONon;执行要观察的SQL语句...SETsession)optimizer_traceenabledoff;就可以看到json格式的逻辑什么是单路排序和双路排序 *单路排序一次性取出所有字段进行排序内存不够采用磁盘 双路排序取出排序字段进行排序排序完再回表查询想要的数据 单路排序更快但消耗内存资源更多双路排序慢会产生回表 当我们max_lengh_for_sort_data的单行所有字段限制超过设置的数启用双路排序如果表中有字段为null又经常查询需要建索引吗如果经常被查询需要建索引null也是一种值只是在索引树下有很多相同的值也就是范围匹配 range有字段为null索引会失效吗不一定可以看SQL执行计划观察看null的占比比例当null值比例高底层会放弃使用索引最好我们给字段设置默认值Mysql内部支持缓存查询吗5.7之前是支持的但在8之后废弃了。 之前的缓存就有一个hashMapkey是查询的SQL语句value是具体的值如果第二次查询根据同一个key可以很快查询出对应的value查询快 缺点就是命中的概率低还有缓存需要大量的缓存区存储浪费内存空间。Mysql内部缓存不够灵活然后我们在应用层有Redis和ehcached来代替。Mysql内部有哪些核心模块组成 *1.第一部分是连接层客户端进行连接Mysql这里有连接池负责连接2.第二部分是解析和优化层拿到该SQL语句字符串先判断有没有缓存但在8版本之后取消缓存。然后拿SQL语句进行语法解析判断要干嘛该一步也进行语法验证 然后语法正确后进行优化操作判断使用哪些索引3.第三部就是存储引擎判断是什么存储引擎进行去磁盘中读取数据进行返回给客户端负责表的数据管理。4.第四部是物理存储负责管理数据文件和日志文件和索引文件与磁盘交互。一条SQL语句执行的过程1.首先客户端连接上Mysql然后通过网络端口把SQL语句发送给Mysql服务器2.然后Mysql判断有没有权限3.有权限后查询缓存如果有缓存直接返回数据但在8版本之后取消缓存4.没有缓存直接进入解析和优化层先判断SQL语句语法是否正确会生成树语句结构正确之后进行优化操作判断使用哪些索引5.然后再进入存储引擎判断是什么存储引擎进行去磁盘中读取数据进行返回给客户端。mysql有哪些存储引擎InnoDBMysql默认的存储引擎支持事务、外键、行级锁MyISAM 不支持事务、外键、行级锁优势查询快缺点就是一旦崩溃数据找不回来Archive 不常见适合存压缩包Blackhole它适合记录日志比如一个SQL语句查询它只记录该SQL语句不记录SQL语句查询到结果CSV用于数据交换如excel的读取Federated用于跨库关联查询但我们也不用跨库我们会使用中间件 默认是InnoDB我们可以通过show engines;命令去查看能否为一张表设计单独的存储引擎可以 设置全表的存储引擎SETDEFAULT_STORAGE_ENGINEMyISAM;(或者通过配置文件设置)设置单独表的存储引擎CREATETABLE表名(建表语句;)ENGINE存储引擎名称;ALTERTABLE表名ENGINE存储引擎名称;mysql事务特性 *原子性一个事务内的操作统一成功或失败 一致性事务前后的数据总量不变 隔离性事务与事务之间相互不影响 持久性事务一旦提交发生的改变不可逆并发事务所产生的一些问题有哪些 *脏读脏读是指一个事务读取到其他事务未提交的数据 不可重复读不可重复读是指在同一次事务中前后查询不一致的问题。 例子一个A事务查询结果另一个B事务修改了对应的值提交事务B事务A又查询结果与上次查询结果不一致 幻读幻读跟不可重复读相似多次查询查询条数不一致。 例子一个A事务查询结果另一个B事务新增一条数据提交事务B事务A又查询结果与上次查询结果不一致事务的隔离级别 *并发事务会产生脏读、不可重复读、幻读问题这时需要用隔离级别来控制。 读未提交 允许读取未提交的事务数据可能出现脏读、不可重复读、幻读问题。 读已提交 只允许读取已提交的事务数据解决脏读可能出现不可重复读、幻读问题。 可重复读 确保同一字段多次读取结果一致可能出现幻读。 可串行化 所有事务逐次执行没有并发InnoDB默认隔离级别是 可重复读级别分为快照度和当前读通过间隙锁解决了幻读问题。Mysql事务的隔离是如何实现的Mysql默认隔离级别是 可重复读然后InnoDB支持读写锁有MVCC版本控制。什么是一致性非锁定读和锁定读锁定读就是读写锁多个事务同时更新数据我们需要加锁 有行锁解决对多个事务更新一行数据 有间隙锁解决对多个事务更新多行数据 分别有insert、update、delete、 select...forupdate;非锁定读:就是MVCC解决通过多版本控制不需要加锁MVCC是什么*MVCC是多版本并发控制为每次事务生成一个新版本数据每个事务都由自己的版本解决每个事务进行读写不冲突。MVCC主要依赖 隐藏字段 有一个字段是创建或者修改最后一次事务id标记删除或者修改并不是真的物理上的操作 有一个字段是回滚指针 有一个字段是唯一的索引就是行id如果有主键索引会使用主键索引 undolog日志 用来回滚数据的还可以不加锁的读就是快照读用于读老版本的数据就是一个事务还没提交读之前版本的数据。 readView 不同的事务会对数据有变更在不同的事务隔离级别来解决哪些事务是可见和不可见的每个事务的操作都会有版本号它会标记好访问对应的版本号数据。Mysql有哪些日志 *undolog日志1.用于回滚Mysql的原子性一致性通过该日志实现的2.用于MVCC读老版本的数据 redolog日志1.Mysql的持久性通过该日志实现的如果我们电脑重启可以通过该日志恢复数据该日志是记录在磁盘中2.该日志记录操作之后的数据用于MVCC读当前版本的数据 binlog日志1.做主从复制的日志2.每次创建表、索引都会记录增删改的日志可以数据库的恢复 error日志错误日志一般Mysql启动错误写入该日志中 慢查询日志一般慢SQL写入该日志中在配置文件中配置long_query_time,超过该时间会记录该日志中表级锁和行级锁的区别行级锁是锁行该行上锁 表级锁是锁表隔离级别是串行化的时候是表级锁什么是共享锁和排它锁共享锁读锁S锁 排它锁写锁独占锁一个事务有排它锁其他事务不能再有该锁就是同一行。当前读和快照读的区别当前读就代表当前事务加了锁读最新版本的数据一般使用行锁或者间隙锁保证当前读数据的过程中其他事务是不能修改操作 快照读不加锁的读就是快照读通过MVCC多版本控制实现是否使用过select for update *一般我们查询语句不会上锁但必须在事务中使用selectforupdate是在查询中上锁避免超卖现象出现 例子一个事务中先查询库存100个然后再修改库存-1操作如果其他事务也查询库存100个直接修改减库存-100很容易减多 selectforupdate就是在查询的时候上锁避免超卖现象 使用 select*from 表forupdate;会等行锁释放在返回结果 select*from 表forupdate nowait;不等待直接返回锁冲突不返回结果 select*from 表forupdate wait5;等待5秒若没释放行锁直接返回锁冲突不返回结果 select*from 表forupdate skip locked;返回结果忽略有行锁的记录mysql的死锁的处理 *避免1.上线前的压力测试2.拆分SQL禁止大事务3.利用索引,优化索引减少锁的范围 # 查询innodb锁的状态 show statusLIKEinnodb_row_lock% 结果Innodb_row_lock_current_waits当前正在等待锁定的数量Innodb_row_lock_time从系统启动到现在锁定总时间 单位毫秒Innodb_row_lock_time_avg每次等待所花平均时间Innodb_row_lock_time_max从系统启动到现在等待最长的一次时间Innodb_row_lock_waits从系统启动到现在总共等待次数 # 查询是否锁表 showOPENTABLES;showOPENTABLESwhereIn_use0;# 解锁表 unlock tables;# 正在运行的任务 show fullPROCESSLIST;# 杀死进程一般这样就解锁 kill id;# 查看正在锁的事务SELECT*FROMINFORMATION_SCHEMA.INNODB_LOCKS;SELECT*FROMperformance_schema.data_locks;8版本之后是该命令 # 查看等待锁的事务SELECT*FROMINFORMATION_SCHEMA.INNODB_LOCK_WAITS;SELECT*FROMperformance_schema.data_lock_waits;8版本之后是该命令 #XA模式如果事务 try_mysql_thread_id 为0需要手动回滚事务不能通过kill 删除该id xa recover;# 回滚XA事务格式 xa rollback left(data,gtrid_length), substr(data,gtrid_length1,bqual_length),formatID;xa rollback 192.168.0.102:8091:7935837828044361737, -7935837828044361738,9752;Mysql集群同步时为什么选择binlog如果同步二进制文件一旦同步过程中有中断不好缺点哪些数据同步失败和成功 优点就是如果中断了还可以继续支持增量同步 binlog是Mysql日志没有存储引擎限制Mysql是如何存储文件附件如ppt1.当我们文件大小比较小查询频率比较高后期维护性不高如原先4k现在2K对之前数据维护我们可以存在数据库中可以使用blob数据类型二进制的字符类型2.一般我们上传到云平台数据库只记录地址 上传过程的问题1.SQL过大需要调整2.主从同步慢3.占用网络带宽4.使用二进制浏览器很难使用到缓存如何存储ip地址1.可以使用字符串但缺点范围查询需要like影响性能2.整型支持范围查询某个段的查询可使用inet_aton()、inet_ntoa()--插入INSERTINTO表(ID,IP)VALUES(1,inet_aton(192.168.2.12));--查询 select id,inet_ntoa(ip)as ip from 表;日期和时间如何存储TIMESTAMP跟时区有关字节较小DATETIME字节较大 不建议使用字符串因为不好范围查询char和varchar区别char是固定长度varchar弹性的长度char对于更新频繁操作的字段读取速度更快但缺点就是浪费空间浮点类型如何选择如果有计算保持精度使用decimal 如果只展示可以使用字符串、float、double预编译的SQL好处预编译的SQL会被缓存下来防止SQL注入 例子select*from 表 where 条件 该只是参数带进来整个SQL语句会缓存下来只有参数不同。join与子查询哪个效率高一般是join的效率高因为子查询在底层会创建临时表然后再删除临时表多了一步操作 建议join查询不超过3张表嵌套越多占用内存空间越大优化join查询 *1.可以设置冗余字段避免关联查询2.小表驱动大表3.关键字段需要加索引是否有过Mysql调优分为1.SQL调优 根据SQL的执行计划来分析SQL是否增加索引进行索引优化 减少函数的使用2.表结构优化 表结构优化-尽量字段使用最小空间进行存储元素 根据业务考虑是否有冗余字段快速查询减少关联查询 根据业务考虑对于大数据是否冷热数据分离3.数据库参数调优 从硬件考虑-硬盘读写速度优先考虑尽量磁盘单独给数据库使用避免使用Docker部署把磁盘分给其他服务 网络方面-提升带宽 更改数据库的配置文件加大内存缓存大小方便快速排序是有单路排序 事务优化避免大事务。尽量行锁不用升级到表锁 对应数据安全-数据集群化配置索引是如何分析和调优 *我们可以通过explan查询SQL执行计划进行分析 如果id相同从上往下走id不同从大往小走 然后查询type字段避免是all进行全表扫码 然后key目前使用到的索引然后旁边还有该表有哪些索引 然后row影响的行数旁边还有百分比比如行100百分比是10%100行有10%是有用的Mysql数据库CPU飙升如何处理 *首先我们先排除是不是其他因素造成的因为我们现在几乎部署都是云环境下先看一下容器有没有问题再看一下当前系统有没有跑其他的程序 然后再看Mysql一般都是大SQL造成使用show fullPROCESSLIST;进行分析但这种如果并发量比较大需要不断的刷新很难看出 我们可以使用pidstat-u 查看一下CPU使用率 例子pidstat-u1可以看到pid 线程id pidstat-t-p24319线程id可以看到cpu高的tid 然后再去SQL命令行执行SELECT*FROMperformance_schema.threadsWHERETHREAD_OS_ID63938tidORTHREAD_OS_ID63939tid有没有进行过分库分表 *一般我们先不建议分库分表如果进行了SQL复杂度也上升能不分就不分看看是否使用其他中间件可以解决不 优先1.查看SQL性能很难再优化再考虑2.细化业务增加缓存解决3.读写分离进行解决4.先分库5.再分表我们先进行冷热数据分离 垂直分库我们把一个数据库拆分多个数据库按业务化分 水平分表把一张大表拆分多个表例a表存id前100数据b表存id200-300数据c表存id300后数据实现分库分表的思路我们可以使用中间件如Mycat一个伪装成Mysql服务做一个代理然后通过配置Mycat路由的规则再访问对应的数据库 优点使用方便开发者连接数据库只连接Mycat就可以效率没那么高。 然后可以通过代码的形式如AOP进行拦截处理框架有Sharding-Sidecar、Sharding-JDBC分库分表引发的问题 *1.执行效率原先是一个库现在是多个库的查询2.表结构很难调整如每次调整可能造成数据迁移3.分布式id问题事务的一致性复杂4.产生跨库join效率会底5.如果使用中间件如Mycat大家都请求这台机器这个机器的性能带宽问题为什么使用视图视图就是虚拟表并不会提示性能会提升代码可读性SQL复用性会提高 屏蔽数据库的复杂性用户不需要考虑表是如何关联的 屏幕敏感数据提升安全性什么是存储过程就是通过SQL编写复杂逻辑它优点提升SQL执行效率一般的SQL执行一次编译一次存储过程的SQL它只编译一次效率会高 但现在很少有公司写存储过程了缺点不利于维护不方便做数据迁移存储过程调试不方便当有并发量下会给数据库增加性能瓶颈 一般什么情况会使用就是核心代码比如核心业务逻辑这个开发人只管理该业务逻辑写存储过程其他开发人员写代码访问数据库没有权限看到核心业务逻辑的存储过程只有使用权有一定的安全性。但这种问题不好调试就是存储过程改动一点这些开发人员并不知情导致有问题想不到是存储过程引起的。什么是外键就是一个表和另一个表进行关联字段 现在不建议使用外键因为不适合做分布式高并发集群每次插入影响速度因为要级联耦合度高 如单机并发低的外键比较适合where和having的区别having是使用聚合函数进行过滤的where是正常的过滤MySQL插入百万级的数据如何优化 *1可以通过存储过程先去掉索引再导入数据再创建索引不推荐如果有集群不推荐使用存储过程2分批提交一次性提交一部分数据3可启用多线程进行导入有个表有千万数据查询慢如何优化前端优化:1.避免无效的刷新页面就是如果是机器人刷新需要限制2.减少网络交互避免重复查询减少查询 增加缓存1.热点数据放入Redis中2.高频查询双写一致性是否考虑 数据库优化:1.字段类型是否合适如字符串char还是varchar2.可以查看SQL执行计划有没有用到索引进行优化SQL3.然后SQL优化不好了进行优化业务是否可以分段查询先干什么再干什么例子第一步操作第二部操作...如果join表过多是否允许有冗余字段 然后冷热数据分离再细化分微微服务再考虑读写分离、分表分库count列名和count*区别count(*)会统计null的数据count(列名)会过滤掉如果超大分页如何处理因为分页是limit比如查询1000,10并不是跳过1000还需要查1000条1可以通过延迟关联,就是先通过分页找出对应主键id然后再关联查询完整数据 例子select*from 表 a,select id from 表 where1000,10b where b.ida.id2可以通过排序字段优化一般都是先排序在进行limit分页 一般大数据前几页都是从缓存查询出来后几页进行查询数据mysql服务器无规律异常重启解决排查问题1.运行内容占用率是不是OOM问题解决增加物理内存2.查看mysql的buffer调整合适的值3.查看mysql的连接数是否控制连接数 如果出现该问题先看日志在看相关配置参数是否改变配置参数万能解决这些都是通过花钱能解决的加内存。mysql上线修改表结构有哪些风险产生问题可能会锁表导致表的读写问题 一般我们会创建新表然后导入数据再重命名