mysql优化1、MYSQL优化次要分为以下四大方面：设计：存储引擎，字段类型，范式与逆范式性能：索引，缓存，分辨别表。架构：主从复制，读写拆散，负载平衡。正当SQL：测试，教训。~~~~ 优先思考的是表构造、抉择适合的字段、索引优化、联合 Redis缓存、主从拆散、（无可奈何才用 分区、分表、分库） mysql保留的数据格式是什么？ mysql如何编译成咱们能辨认的数据格式？ mysql索引在内存中以什么格局保留？ B+树是一个怎么的树状？为什么会这样？ 什么是事务？ 事务的隔离级别，锁，行锁，表锁，间隙锁，幻读、脏读，反复读？如何解决？
前言 最近在集中学习mysql源码，刚好分了几个主题，波及到词法解析、语法解析、查询器、优化器等。刚好把筹备的PPT内容摘出来整顿成相应的文章。 Mysql版本： 8.0.20 调试工具 ： lldb 零碎环境 ： MacOS 10.14.3 在理解词法解析之前，咱们带着几个问题来切入： （1）什么是词法解析？ （2）Mysql 8.0.20词法解析有什么优化？ （3）Mysql 8词法解析都有什么过程？ 1.什么是词法解析？ 词法剖析（lexical analysis）是计算机科学中将字符序列转换为单词（Token）序列的过程。进行词法剖析的程序或者函数叫作词法分析器（Lexical analyzer，简称Lexer），也叫扫描器（Scanner）。词法分析器个别以函数的模式存在，供语法分析器调用。 词法分析阶段是编译过程的第一个阶段，是编译的根底。这个阶段的工作是从左到右一个字符一个字符地读入源程序，即对形成源程序的字符流进行扫描而后依据构词规定辨认单词(也称单词符号或符号)。词法分析程序实现这个工作。词法分析程序能够应用Lex等工具主动生成。 词法剖析是编译程序的第一个阶段且是必要阶段；词法剖析的外围工作是扫描、辨认单词且对辨认出的单词给出定性、定长的解决；实现词法分析程序的罕用路径:主动生成,手工生成。 主动生成词法能够参照我之前写的一篇文章：https://blog.csdn.net/byxiaoyuonly/article/details/107851764 2.词法解析2.1 词法解析状态机 词法解析状态机是在词法解析的扫描阶段执行的过程，下图2-1-1是状态解析token的执行过程：<center>图2-1-1 状态机解析token过程</center> 状态机的主要用途就是解析token时的执行过程，比方MY_LEX_IDENT状态机会循环匹配字符后，解析字符并返回对应的token。 对应状态机备注MY_LEX_START开始解析tokenMY_LEX_CHAR解析单个字符例如*、:、;MY_LEX_IDENT解析字符串，匹配关键词，例如“table”、“select” 等MY_LEX_IDENT_SEP找到字符'.'MY_LEX_IDENT_START从'.'开始解析tokenMY_LEX_REAL不齐全实数MY_LEX_HEX_NUMBERhex字符串MY_LEX_BIN_NUMBERbin字符串MY_LEX_CMP_OP不齐全比拟运算符MY_LEX_LONG_CMP_OP不齐全比拟运算符MY_LEX_STRING字符串MY_LEX_COMMENTCommentMY_LEX_END完结MY_LEX_NUMBER_IDENT数字MY_LEX_INT_OR_REAL齐全整数或不齐全实数MY_LEX_REAL_OR_POINT解析.返回不齐全实数，或者字符'.'MY_LEX_BOOL布尔MY_LEX_EOL如果是eof，则设置状态end完结，MY_LEX_LONG_COMMENT长C正文MY_LEX_END_LONG_COMMENT备注完结MY_LEX_SEMICOLON分隔符;MY_LEX_SET_VAR查看:=MY_LEX_USER_END完结'@'MY_LEX_HOSTNAME解析hostnameMY_LEX_SKIP空格MY_LEX_USER_VARIABLE_DELIMITER引号字符MY_LEX_SYSTEM_VAR例如解析user@hostname，解析到@MY_LEX_IDENT_OR_KEYWORD判断返回字符串状态或者键盘键值MY_LEX_IDENT_OR_HEXhex-数字MY_LEX_IDENT_OR_BINbin-数字MY_LEX_IDENT_OR_NCHAR判断返回字符状态，或字符串状态MY_LEX_STRING_OR_DELIMITER判断返回字符串状态或者空格字符状态2.2 调试解析源码 咱们能够一起来跟一下源码，如果不会装置编译能够看一下我之前的文章《【Mysql源码剖析】MySQL为什么有时候会选错索引及成本计算》https://blog.csdn.net/byxiaoyuonly/article/details/107651106 咱们开始调试，首先要先启动下mysql8.0.20。而后筹备两个终端：一个终端用于操作mysql语句、另外一个终端用于调试应用,如图2-2-1。<center>图2-2-1 关上终端</center>能够用lldb进行调试： #lldb -p 过程ID<center>图2-2-2 词法解析调用过程</center> 依据图2-2-2咱们能够得悉，Mysql8.0.20会调用MYSQLlex办法进行词法解析、MYSQLlex中会调用lex_one_token进行单个token解析。如果咱们要调试能够对lex_one_token去下一个断点。 (lldb)b lex_one_token 下断点后在mysql操作终端操作做一条语句例如“select * from t1;”，此时调试终端会捕捉到断点，调试到图2-2-3。<center>图2-2-3 调试图</center> 依据上图咱们得悉第一个状态机为MY_LEX_START，执行状态机进入switch后，会通过yyPeek办法获取一个字符如图2-2-4。来判断这个字符是否为空格，不是空格后，通过“state = state_map[c];” 返回一个状态机。判断时通过state_map解析，state_map在2.3大节会介绍。 <center>图2-2-4 MY_LEX_START调试图</center> 因为获取单个字符是s，s对应state_map中的状态机是MY_LEX_IDENT，MY_LEX_IDENT状态机会去匹配对应的关键词返回token。 <center>图2-2-5 MY_LEX_START调试图2</center> 依据图2-2-5咱们得悉通过find_keyword办法能够匹配对应的token。第一次匹配后咱们失去一个token(748),748这个token对应SELECT_SYM，能够在/mysql-8.0.20/sql/sql_yacc.h文件中查找到。此时m_ptr参数值为“ * from t1”,该参数由返回前调用lip->yyUnget()进行左移。lip->next_state的状态再次设置为MY_LEX_START。<center>图2-2-6 MY_LEX_END_LONG_COMMENT调试图</center> 当咱们再次调用lex_one_token时，解决MY_LEX_START状态机时，会过滤调一个空格字符。继续执行获取，获取到“*”字符，又会将状态机设置为MY_LEX_END_LONG_COMMENT,而后执行状态机会设置为MY_LEX_CHAR，返回时下一次的状态设置成了MY_LEX_START。最初返回一个token(42)，其实这个42是ASCII到“*”。此时m_ptr参数值为“ from t1”。再次执行MY_LEX_START过程，会设置状态机为MY_LEX_IDENT，执行MY_LEX_IDENT状态机后会返回token(452),能够在/mysql-8.0.20/sql/sql_yacc.h文件中查找到。对应FROM。再次执行后会返回状态机IDENT_QUOTED,最终返回状态机MY_LEX_EOL，最终返回MY_LEX_END完结。<center>图2-2-7 调试解析残缺过程</center> ...
ORM（Object/Relational Mapping），即对象关系映射，它实现面向对象的编程语言到关系数据库的映射。ORM工具的惟一作用是：把长久化对象的保留、批改、删除等操作，转换成对数据库的操作。 ORM 根本映射关系：数据表映射类。数据表的行映射对象（实例）。数据表的列（字段）映射对象的属性。MyBatis 简介：MyBatis 是反对定制化 SQL、存储过程以及高级映射的优良的长久层框架。MyBatis 防止了简直所有的 JDBC 代码和手动设置参数以及对后果集的检索封装。MyBatis 能够对配置和原生 Map 应用简略的 XML 或注解，将接口和Java的POJOs（Plain Old Java Objects（一般的 Java 对象））映射成数据库中的记录。MyBatis 次要思维是将程序中的大量SQL语句抽取进去，配置在配置文件中，以实现SQL的灵便配置。MyBatis 并不齐全是一种ORM框架，它的设计思维和ORM类似，只是它容许间接编写SQL语句，使得数据库拜访更加灵便。MyBatis 提供了一种“半自动化”的ORM实现，是一种“SQL Mapping”框架。 MyBatis性能构造1. API接口层：提供给内部应用的接口API，开发人员通过这些本地API来操纵数据库。接口层接管到调用申请就会调用数据处理层来实现具体的数据处理。2. 数据处理层：负责具体的SQL查找、SQL解析、SQL执行和执行后果映射解决等。它次要的目标是依据调用的申请实现一次数据库操作。3. 根底撑持层：负责最根底的性能撑持，包含连贯治理、事务管理、配置加载和缓存解决，这些都是共用的货色，将他们抽取进去作为最根底的组件，为下层的数据处理层提供最根底的撑持。 MyBatis 框架结构1. 加载配置：MyBatis 应用程序依据XML配置文件加载运行环境，创立SqlSessionFactory, SqlSession，将SQL的配置信息加载成为一个个MappedStatement对象（包含了传入参数映射配置、执行的SQL语句、后果映射配置），存储在内存中。（配置来源于两个中央，一处是配置文件，一处是 Java 代码的注解）2. SQL解析：当API接口层接管到调用申请时，会接管到传入SQL的ID和传入对象（能够是Map、JavaBean或者根本数据类型），MyBatis 会依据SQL的ID找到对应的MappedStatement，而后依据传入参数对象对MappedStatment进行解析，解析后能够失去最终要执行的SQL语句和参数。3. SQL的执行：SqlSession将最终失去的SQL和参数拿到数据库进行执行，失去操作数据库的后果。4. 后果映射：将操作数据库的后果依照映射的配置进行转换，能够转换成HashMap、JavaBean或者根本数据类型，并将最终后果返回，用完之后敞开SqlSession。SqlSessionFactory：每个基于MyBatis的利用都是以一个SqlSessionFactory的实例为外围的。SqlSessionFactory是单个数据库映射关系通过编译后的内存映像。SqlSessionFactory的实例能够通过SqlSessionFactoryBuilder取得。而SqlSessionFactoryBuilder则能够从XML配置文件或一个事后定制的Configuration的实例构建出SqlSessionFactory的实例。SqlSessioFactory是创立SqlSession的工厂。SqlSession:SqlSession是执行长久化操作的对象，它齐全蕴含了面向数据库执行SQL命令所需的所有办法。能够通过SqlSession实例来间接执行已映射的SQL语句，在应用完SqlSession后咱们应该应用finally块来确保敞开它。Mybatis.xml 配置文件：配置文件包含：1. configuration 配置2. properties 配置可内部配置且动静替换。例如：在resource资源目录下配置config.properties配置文件，在Mybatis.xml中配置如下。<properties resource="config.properties"/>也能够通过properties的子元素传递值：<properties resource="config.properties"> <property name="username" value="root"/> <property name="password" value="123"/></properties>3. settings 设置settings 是 Mybatis.xml 文件中极其重要的设置，它们会扭转MyBatis的运行时行为，如开启二级缓存、开启提早加载等。4. typeAliases 类型别名typeAliases只和XML配置无关，用来缩小类齐全限定名的冗余。第一种配置办法：<typeAliases> <typeAlias alias="User" type="com.shiyanlou.mybatis.model.User"/> </typeAliases>这里将全门路的com.shiyanlou.mybatis.model.User（User是包com.shiyanlou.mybatis.mode下的办法）起一个别名User，在映射文件中parameterType和resultType就能够间接应用别名User，无需应用全门路。第二种配置办法：<typeAliases> <package name="com.shiyanlou.mybatis.model" /> </typeAliases>指定一个包名起别名，MyBatis 会在包名下搜寻须要的JavaBean，将Java类的类名作为类的类别名。5. typeHandlers 类型处理器typeHandlers的作用是实现JDBC类型和Java类型之间的转换，MyBatis中默认的类型处理器根本能满足日常需要。6. objectFactory 对象工厂7. plugins 插件8. environments 环境MyBatis 的环境配置理论是数据源的配置，MyBatis能够配置多个环境，帮忙你将SQL映射对应到多种数据库。留神：只管能够配置多个环境，每个SqlSessionFactory实例只能对应一个数据库，有几个数据库就须要创立几个SqlSessionFactory实例。接管环境配置的两个办法：SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(reader, environment); SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(reader, environment,properties);如果疏忽了环境参数，默认环境将会被加载：SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(reader); SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(reader,properties);9. transactionManager：事务管理器MyBatis中有两种事务管理器，即 type=”\[JDBC | MANAGED\]”；JDBC：间接应用JDBC的提交和回滚设置。MANAGED：让容器来治理事务的整个生命周期。10. dataSource ：数据源dataSource元素应用规范的JDBC数据源接口来配置JDBC连贯对象的资源。MyBatis 三种内建的数据源类型，即type=”\[UNPOOLED | POOLED | JNDI\]”;1. UNPOOLED ：不反对JDBC数据源连接池，实现的只是每次被申请时关上和敞开连贯。属性有： 1）driver ：JDBC驱动的Java类的齐全限定名，如 MySQL 的com.mysql.jdbc.Driver 2）url ：数据库的JDBC URL地址。 3）username ：数据库的用户名。 4）password ：数据库的明码。 5）defaultTransactionIsolationLevel ：默认的连贯事务隔离级别。2. POOLED ：反对JDBC数据源连接池，利用“池”的概念将JDBC连贯对象组织起来，防止了创立新的连贯实例时所必须的初始化和认证工夫。3. JNDI ：反对内部数据源连接池，它的实现是为了能在如EJB或应用服务器这类容器中应用。11. databaseIdProvider 数据库厂商标识12. mappers 映射器mappers 映射器用于援用曾经定义好的映射文件，通知MyBatis 去哪寻找映射SQL的语句。常见的办法有：1）通过resource加载单个映射文件<mappers><mapper resource="com/shiyanlou/mybatis/mapper/UserMapper.xml"/> </mappers>2）通过齐全限定资源定位符（绝对路径前加上 “file:///” ）加载单个映射文件。<mappers> <mapper url="file:///home/project/MyBatisTest/src/com/shiyanlou/mybatis/mapper/UserMapper.xml"/> </mappers>3） 通过mapper接口对象加载单个映射文件。<mappers> <mapper class="com.shiyanlou.mybatis.mapper.UserMapper"/> </mappers>4） 通过mapper接口包加载整个包的映射文件。<mappers> <package name="com.shiyanlou.mybatis.mapper" /> </mappers>以下是一个简略的 MyBatis.xml 的配置文件：<?xml version="1.0" encoding="UTF-8"?> <!DOCTYPE configuration PUBLIC "-[//mybatis.org//DTD](//mybatis.org//DTD) Config 3.0//EN" "[http://mybatis.org/dtd/mybatis-3-config.dtd](http://mybatis.org/dtd/mybatis-3-config.dtd)"><configuration> <!-- 为 JavaBean 起类别名 --> <typeAliases> <!-- 指定一个包名起别名，将包内的 Java 类的类名作为类的类别名 --> <package name="com.springMybatis.demo" /> </typeAliases> <!-- 配置 mybatis 运行环境 --> <environments default="development"> <environment id="development"> <!-- type="JDBC" 代表间接应用 JDBC 的提交和回滚设置 --> <transactionManager type="JDBC" /> <!-- POOLED 示意反对 JDBC 数据源连接池 --> <!\-\- 数据库连接池，由 Mybatis 治理，数据库名是 mybatis，MySQL 用户名 root，明码为空 --> <dataSource type="POOLED"> <property name="driver" value="com.mysql.jdbc.Driver" /> <property name="url" value="jdbc:mysql://localhost:3306/mybatis" / <property name="username" value="root" /> <property name="password" value="数据库明码" /> </dataSource></environment></environments><mappers><!-- 通过 mapper 接口包加载整个包的映射文件 --><package name="cn.dbOperation.demo" /></mappers></configuration>MyBatis 映射文件：映射文件是所有SQL语句搁置的中央，写好SQL语句映射文件后，须要在mybatis.xml配置文件的mappers标签中援用。映射文件蕴含的顶级元素：1. cache ：给定命名空间的缓存配置。2. cache-ref ：其余命名空间缓存配置的援用。3. resultMap ：形容如何从数据库后果集中来加载对象。4. sql ：可被其它语句援用的可重用语句块。5. insert ：映射插入语句。6. updata ：映射更新语句。7. delete ：映射删除语句。8. select ：映射查问语句。resultMap：在映射文件中也是最简单最弱小的，resultMap的设计就是简略语句不须要明确的后果映射，而很多简单语句的确须要形容它们的关系。resultMap的子元素包含：1. constructor ：用来将后果注入到一个实例化好的类的构造方法中。2. idArg ：ID参数，标记后果作为ID。3. arg ：注入到构造方法的一个一般后果。4. id ：一个ID后果，标记后果作为ID。5. result ：注入到字段或JavaBean属性的一般后果。6. association ：简单的类型关联，多个后果合成的类型。7. collection ：简单类型的集，也能够援用一个内部后果映射。8. discriminator ：应用后果值来决定应用哪个后果集。9. case ：根本一些值的后果映射。resultMap的属性包含：1. id ：以后命名空间中的一个惟一标识，用于标识一个resultMap2. type ：类的全限定名，或者一个类型别名。3. automapping ：为这个ResultMap开启或敞开主动映射，改属性会笼罩全局的属性autoMappingBehavior，默认值为：unset动静SQL语句：MyBatis 罕用的动静 SQL 元素包含：1. if2. choose(when, otherwise)3. trim(where, set)4. foreach5. bindifif 在 where 子句中做简略的条件判断；如果addres为空，则where id = #{id}。如果 addres 不为空， 则where id = #{id} and addres = #{addres};<select id="selectUserTest" parameterType="int" resultType="User"> select * from User where id = #{id} <if test="addres != null"> and addres = #{addres} </if></select>choose(when, otherwise):choose 的用法和 java 的switch相似。依照程序执行，当when中有条件满足时，则跳出choose，当所有when的条件都不满足时就输入otherwise的内容。<!\-\- select id="selectUserTest" parameterType="int" resultType="User">select * from User where id = #{id} <choose> <when test="username != null"> and username = #{username} </when> <when test="phone != null"> and phone = #{phone} </when> <otherwise> and addres = #{addres} </otherwise> </choose></select -->trim(where, set):trim 元素能够给本人蕴含的内容加上前缀(prefix)或加上后缀(suffix)。也能够把蕴含内容的首部(prefixOverrides)或尾部(suffixOverrides)某些内容移除；<select id="selectUserTest" parameterType="int" resultType="User"> select * from User <trim prefix="where" prefixOverrides="and | or"> <if test="id != null"> id = #{id} </if> <if test="addres != null"> and addres = #{addres} </if> </trim></select>where 元素晓得只有在一个以上的 if 条件满足的状况下才去插入 where 子句，而且可能智能地解决and和or条件；<select id="dynamicWhereTest" resultType="User"> select * from user <where> <if test="address != null"> address = #{address} </if> <if test="phone != null"> and phone like #{phone} </if> </where></select>set 元素能够被用于动静蕴含须要更新的列，而舍去其余的<update id="dynamicSetTest"> update User <set> <if test="phone != null">phone=#{phone},</if> <if test="address != null">address=#{address}</if> </set> where id=#{id}</update>foreach:foreach 元素罕用到须要对一个汇合进行遍历时，在in语句查问时特地有用：foreach元素的次要属性：1. item ：本次迭代获取的元素；2. index : 以后迭代的次数；3. open ：开始标记；4. separator ：每次迭代之间的分隔符；5. close ：完结标记；6. collection ：该属性必须指定（1、单参数且为List时，值为list。 2、单参数且为array时，值为array。 3、多参数需封装成一个Map，map的key就是参数名）<select id="selectUser" parameterType="int" resultType="User"> select * from User where id in <foreach collection="list" item="item" index="index" open = "(" separator="," close=")"> #{item} </foreach></select>bind:bind 元素能够从OGNL表达式中创立一个变量并将其绑定到上下文。<select id="selectUser" parameterType="int" resultType="User"> <bind name="pattern" value="'%' + _parameter.getPhone() + '%'"/> select * from User where phone like #{pattern}</select>MyBatis、JDBC、Hibernate的区别：MyBatis也是基于JDBC的，Java与数据库操作仅能通过JDBC实现。MyBatis也要通过JDBC实现数据查问、更新这些动作。MyBatis仅仅是在JDBC根底上做了 OO化、封装事务管理接口这些货色。MyBatis和Hibernate都屏蔽JDBC API的底层拜访细节，使咱们不必跟JDBC API打交道就能够拜访数据库。然而，Hibernate是全自动的ORM映射工具，能够主动生成SQL语句。MyBatis须要在xml配置文件中写SQL语句。因为Hibernate是主动生成SQL语句的，在写简单查问时，Hibernate实现比MyBatis简单的多。
前言当咱们数据库变得相对简单时，关联删除变得不切实际，这时咱们想到了应用软删除代替删除，然而软删除并没有删除数据，数据仍旧存在。当咱们表内有惟一字段时，例如用户表中的username字段，咱们删除原先的用户，雷同username的用户便无奈注册。这是咱们所期待的。最好的解决办法就是，再设置deleted_at字段，记录“删除”一条数据的工夫，将以username为惟一索引变为username与deleted_at独特为惟一索引。惟一索引就是不容许呈现username与çsername雷同的数据，当删除数据时，deleted_at扭转，username能够再次增加，并能够屡次删除。如何当“删除”数据时扭转deleted_at就是用到了咱们的触发器。 触发器触发器相似与咱们的切面编程。在一个特定的事件执行前后，会触发咱们提前设置好的触发器。咱们关上navicat，轻易点击一个数据表，点击设计表 triggers就是咱们的触发器，点击左上角加号创立一个触发器。name：轻易起一个名字就好Time：分为after和before，顾名思义，在什么时候触发。之前或者之后。事件：前面是一个三选一的选项，insert（减少），update（更新），delete（删除）。由哪一类事件触发。最上面的是触发的sql语句。 new和old该关键字，示意触发了触发器的那一行数据。 INSERT触发器中,NEW用来示意将要(BEFORE)或曾经(AFTER)插入的新数据。 UPDATE触发器中，OLD用来示意将要或曾经被批改的原数据，NEW用来示意将要或曾经批改为的新数据。 DELETE触发器中，OLD用来示意将要或曾经被删除的原数据。 另外，OLD 是只读的，而 NEW 则能够在触发器中应用 SET 赋值，这样不会再次触发触发器，造成循环调用。 触发事件回归咱们的需要，咱们心愿“删除”的时候触发触发器，使得deleted_at变为以后工夫。然而，咱们的“删除”并不是真正的删除，而是软删除，也就是更新这条数据的deleted字段为0。所以咱们应该抉择绑定update触发器，并抉择在before时触发 而后是设置触发sql语句。咱们不心愿只有执行更新操作就执行，只有在软删除时执行，所以咱们须要加一个判断语句触发执行。而后在依据报错提醒批改语法，最初是这样的 BEGINIF new.deleted = 1 THEN SET NEW.deleted_at = CURRENT_TIMESTAMP;END IF;END当deleted为1的时候触发。 验证成果如下如果语句这样写 BEGINIF new.deleted = 1 THEN UPDATE `tag` SET `deleted_at` = CURRENT_TIMESTAMP;END IF;END再去执行他会报错。大略意思就是你不能执行update操作因为他正在被触发器调用。这应该是mysql的一种爱护机制，避免触发器有限调用。然而我想用after触发器的时候遇到了困惑，两种写法都不行，new和old貌似对于after触发器来说不认可。这里没搞懂她的语法是什么。 总结数据库也蕴含着很大的学识。下学期也要学数据库了。JPA给了咱们很大便当，这也使得遇到原生的mysql就不会了。原本还想写一篇索引来着，然而有个问题始终没有解决，也写不进去什么货色。
前言当咱们数据库变得相对简单时，关联删除变得不切实际，这时咱们想到了应用软删除代替删除，然而软删除并没有删除数据，数据仍旧存在。当咱们表内有惟一字段时，例如用户表中的username字段，咱们删除原先的用户，雷同username的用户便无奈注册。这是咱们所期待的。最好的解决办法就是，再设置deleted_at字段，记录“删除”一条数据的工夫，将以username为惟一索引变为username与deleted_at独特为惟一索引。惟一索引就是不容许呈现username与çsername雷同的数据，当删除数据时，deleted_at扭转，username能够再次增加，并能够屡次删除。如何当“删除”数据时扭转deleted_at就是用到了咱们的触发器。 触发器触发器相似与咱们的切面编程。在一个特定的事件执行前后，会触发咱们提前设置好的触发器。咱们关上navicat，轻易点击一个数据表，点击设计表 triggers就是咱们的触发器，点击左上角加号创立一个触发器。name：轻易起一个名字就好Time：分为after和before，顾名思义，在什么时候触发。之前或者之后。事件：前面是一个三选一的选项，insert（减少），update（更新），delete（删除）。由哪一类事件触发。最上面的是触发的sql语句。 new和old该关键字，示意触发了触发器的那一行数据。 INSERT触发器中,NEW用来示意将要(BEFORE)或曾经(AFTER)插入的新数据。 UPDATE触发器中，OLD用来示意将要或曾经被批改的原数据，NEW用来示意将要或曾经批改为的新数据。 DELETE触发器中，OLD用来示意将要或曾经被删除的原数据。 另外，OLD 是只读的，而 NEW 则能够在触发器中应用 SET 赋值，这样不会再次触发触发器，造成循环调用。 触发事件回归咱们的需要，咱们心愿“删除”的时候触发触发器，使得deleted_at变为以后工夫。然而，咱们的“删除”并不是真正的删除，而是软删除，也就是更新这条数据的deleted字段为0。所以咱们应该抉择绑定update触发器，并抉择在before时触发 而后是设置触发sql语句。咱们不心愿只有执行更新操作就执行，只有在软删除时执行，所以咱们须要加一个判断语句触发执行。而后在依据报错提醒批改语法，最初是这样的 BEGINIF new.deleted = 1 THEN SET NEW.deleted_at = CURRENT_TIMESTAMP;END IF;END当deleted为1的时候触发。 验证成果如下如果语句这样写 BEGINIF new.deleted = 1 THEN UPDATE `tag` SET `deleted_at` = CURRENT_TIMESTAMP;END IF;END再去执行他会报错。大略意思就是你不能执行update操作因为他正在被触发器调用。这应该是mysql的一种爱护机制，避免触发器有限调用。然而我想用after触发器的时候遇到了困惑，两种写法都不行，new和old貌似对于after触发器来说不认可。这里没搞懂她的语法是什么。 总结数据库也蕴含着很大的学识。下学期也要学数据库了。JPA给了咱们很大便当，这也使得遇到原生的mysql就不会了。原本还想写一篇索引来着，然而有个问题始终没有解决，也写不进去什么货色。
InnoDB索引InnoDB的索引次要分为两大类一个是汇集索引，一个是一般索引。 汇集索引InnoDB汇集索引的的叶子结点存储行记录，因而，InnoDB必须要有且只有一个汇集索引 如果表定义了主键，则主键就是汇集索引如果表没有定义主键，则第一个not null unique列就是汇集索引最初，InnoDB会创立一个暗藏的row_id作为汇集索引一般索引InnoDB一般索引的叶子节点存储主键值。通常状况下须要扫码两遍索引树。 为什么不存储指针而是贮存主键值当数据须要更新的时候，二级索引不须要批改，只须要批改汇集索引，一个表只能有一个聚簇索引，其余的都是二级索引，这样只须要聚簇索引，不须要重建二级索引 explain中重要的列id 查问中select表的程序type 拜访的类型 ALL 全表扫描index 索引扫描range 范畴扫描ref 非惟一索引扫描eq_ref 惟一索引扫描const 常数table 该语句查问的表possible_keys 该查问语句，可能走的索引key 理论应用的索引rows 扫描的行数extra using where 应用where筛选using temprorary 应用长期表using filesort 应用文件排序索引生效like查问已 ‘%…’结尾，以’xxx%’结尾会持续应用索引where语句中应用 <>和 !=。 因为二级索引贮存的是主键值，所以生效。对于主键索引还是会应用的 or 操作索引列存在计算或应用函数联结索引中没有按程序，或者两头缺失了。隐式转换以上状况如果须要查问的列，能够间接索引在索引中失去，那么还是会应用索引的。（笼罩索引）没有应用索引的大部分状况是因为InnoDB一般索引的叶子节点存储主键值，通常状况下须要扫码两遍索引树。
InnoDB索引InnoDB的索引次要分为两大类一个是汇集索引，一个是一般索引。 汇集索引InnoDB汇集索引的的叶子结点存储行记录，因而，InnoDB必须要有且只有一个汇集索引 如果表定义了主键，则主键就是汇集索引如果表没有定义主键，则第一个not null unique列就是汇集索引最初，InnoDB会创立一个暗藏的row_id作为汇集索引一般索引InnoDB一般索引的叶子节点存储主键值。通常状况下须要扫码两遍索引树。 为什么不存储指针而是贮存主键值当数据须要更新的时候，二级索引不须要批改，只须要批改汇集索引，一个表只能有一个聚簇索引，其余的都是二级索引，这样只须要聚簇索引，不须要重建二级索引 explain中重要的列id 查问中select表的程序type 拜访的类型 ALL 全表扫描index 索引扫描range 范畴扫描ref 非惟一索引扫描eq_ref 惟一索引扫描const 常数table 该语句查问的表possible_keys 该查问语句，可能走的索引key 理论应用的索引rows 扫描的行数extra using where 应用where筛选using temprorary 应用长期表using filesort 应用文件排序索引生效like查问已 ‘%…’结尾，以’xxx%’结尾会持续应用索引where语句中应用 <>和 !=。 因为二级索引贮存的是主键值，所以生效。对于主键索引还是会应用的 or 操作索引列存在计算或应用函数联结索引中没有按程序，或者两头缺失了。隐式转换以上状况如果须要查问的列，能够间接索引在索引中失去，那么还是会应用索引的。（笼罩索引）没有应用索引的大部分状况是因为InnoDB一般索引的叶子节点存储主键值，通常状况下须要扫码两遍索引树。
一、分享内容摘要1）、随机音讯的显示——order by优先级队列排序 2）、order by排序计划总结 3）、入手GDB调试mysql源码 二、查看分享视频查看视频 查看分享视频 https://v.youku.com/v_show/id_XNDc4NTc3NDgyNA==.html
一、分享内容摘要1）、随机音讯的显示——order by优先级队列排序 2）、order by排序计划总结 3）、入手GDB调试mysql源码 二、查看分享视频查看视频 查看分享视频 https://v.youku.com/v_show/id_XNDc4NTc3NDgyNA==.html
一、分享内容摘要MySQL中select count(col) 底层实现摸索 次要包含： count(*) | count(1) | count(id) | count(a) 不同版本的实现状况 性能比拟（从源代码的角度） 二、查看分享视频查看视频查看分享视频 https://www.bilibili.com/video/BV1mK4y1v7ba/
在将测试库的新增表通过Navicat向阿里云的MySql数据库复制后，发现前端页面申请查问失败。 指标数据库的默认排序规定是utf8mb4_0900_ai_ci，已存在的表都应用了这个编码，而起源数据库的排序规定是utf8mb4_unicode_ci，新复制的表都用的是后者。 数据库进行多表关联查问时，如果两张表的字符集或者排序规定不统一，就会报错。 从Navicat里手动批改编码效率很低，若只是改一下表，也用不了多久，但问题是只改表是不行的，表内所有varchar的编码并不会跟着表走。 因而还是须要走批量操作的路子。 批量批改字段SELECT CONCAT( 'ALTER TABLE `', TABLE_NAME, '` MODIFY `', COLUMN_NAME, '` ', DATA_TYPE, '(', CHARACTER_MAXIMUM_LENGTH, ') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci', ( CASE WHEN IS_NULLABLE = 'NO' THEN ' NOT NULL' ELSE '' END ), ';' ) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = '数据库名' AND ( DATA_TYPE = 'varchar' OR DATA_TYPE = 'char')批量批改表SELECT CONCAT( 'ALTER TABLE ', TABLE_NAME, ' CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;' ) FROM information_schema.TABLES WHERE TABLE_SCHEMA = '数据库名';将以上SQL语句中的utf8mb4、utf8mb4_unicode_ci、数据库名别离改成本人须要的值，胜利执行后，将执行后果即SQL语句复制进去，再执行这些SQL语句即可。 ...
简介之前这篇文章MySQL配置根底简要阐明了MySQL的配置根底，包含配置文件的地位、配置项的分段、配置变量的失效、以及配置变量和状态变量的查看，对MySQL的配置有了一个根底。当初则会进一步理解更多底层原理，搞清楚更多配置的含意和作用。 本文为《高性能MySQL》读书笔记，配合文档查阅更佳： https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.htmlInnoDB IO配置InnoDB 事务日志：InnoDB在每个事务提交的时候，不会把缓冲池的内容立刻刷到磁盘。而是从缓冲池将事务记录到事务日志中，输入日志(长久化)，再由事务日志实现写磁盘的操作。事务日志会把数据文件的随机I/O转换成简直程序的I/O，而且把刷新到磁盘的操作转移到后盾，从而让查问更快。事务日志有固定的大小，采纳环形形式写（写到开端时跳转到结尾持续写）。定期将缓冲池传来的事务通过日志形式长久化，再将脏数据刷到磁盘中。 缓冲池 <--> 事务日志 <--> 磁盘先将事务变成日志，写入磁盘(长久化)，再缓缓依据日志内容写入磁盘。事务日志的配置：事务日志依赖innodb_log_file_size和innodb_log_files_in_groups这两个变量。前者申明每个日志文件的大小，后者申明日志文件的个数。InnoDB会应用多个日志文件作为循环日志（1号文件写完了写2号，2号写完了写1号） 默认是50M，2个文件。共100M。倡议增大单个文件的大小，依然应用2个文件。(本文应用MySQL 8.0.21 不同版本默认参数可能不统一)mysql> show variables where variable_name like "%inno%log_file%";+---------------------------+----------+| Variable_name | Value |+---------------------------+----------+| innodb_log_file_size | 52428800 || innodb_log_files_in_group | 2 |+---------------------------+----------+事务日志自身的写入缓存：事务日志，将事务写入日志文件的时候，也并不是间接写入的，而是应用写入缓存。先写入缓存中，再由缓存定期写入文件。由innodb_log_buffer_size 参数决定应用写入缓存大小。以下3个条件，满足任一条件就会刷新缓存到日志文件中。 每隔1秒写入缓存满有事务提交MySQL 8.0.21 默认大小为16M。因为最长每1秒会刷新一次写入缓存，所以这个参数不必设置的过大，只须要超过每秒产生的事务量即可。mysql> show variables where variable_name like "%inno%log_b%";+------------------------+----------+| Variable_name | Value |+------------------------+----------+| innodb_log_buffer_size | 16777216 |+------------------------+----------+InnoDB和文件系统的交互方式InnoDB和磁盘的读写交互都通过innodb_flush_method来抉择。次要有如下几种： fdatasyncO_DIRECTO_DSYNC1、fdatasync()：和fsync()相似，然而只刷新文件数据自身，不包含元数据。而且，这个选项会应用应用双重缓冲（包含操作系统这一层的缓存）2、O_DIRECT：仍然应用fsync()来刷新文件到磁盘，然而会敞开操作系统缓存，告知操作系统不要缓存且不要预读。（所有读写都间接达到存储设备，防止双重缓冲） 这个设置只会影响操作系统，不会影响RAID卡的预读。如果应用这个选项，最好应用带预读的RAID卡，且关上写回(write_back)3、O_DSYNC 这个选项会使所有写同步，或者说，只有数据确切写到磁盘后，写操作才会返回。每个write()或pwrite()操作都会函数实现前将数据同步到磁盘，且这个过程是阻塞的。[而fsync()容许积攒写操作到缓存，再一次性刷新数据。] https://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html#sysvar_innodb_flush_method更多可参考：https://www.cnblogs.com/CNty/p/10943626.htmlInnoDB I/O配置文档：https://dev.mysql.com/doc/refman/5.7/en/optimizing-innodb-diskio.htmlInnoDB 表空间配置【施工中】双写缓冲(DoubleWrite)配置【施工中】MySQL 高并发【施工中】呈现高并发时，如何发现问题？ 呈现高并发时，如何取得更好的性能？ 线程进入内核阶段-并发瓶颈； innodb_thread_concurrency：限度一次性能够有多少线程进入内核。（0示意不限度 推荐值：并发值 = CPU 数量 * 磁盘数量 * 2【理论倡议设置稍小的值，再行调整 ...
常常提到数据库的事务，那你晓得数据库还有事务隔离的说法吗，事务隔离还有隔离级别，那什么是事务隔离，隔离级别又是什么呢？明天咱们就找菊长去，请他帮大家梳理一下这些各具特色的事务隔离级别，咱走着~~~ 点击疾速传送门→
前言： 存储引擎是数据库的外围，对于 MySQL 来说，存储引擎是以插件的模式运行的。尽管 MySQL 反对品种繁多的存储引擎，但最罕用的当属 InnoDB 了，本篇文章将次要介绍 InnoDB 存储引擎相干常识。 1. InnoDB 简介MySQL 5.5 版本当前，默认存储引擎就是 InnoDB 了。 InnoDB 是一种兼顾了高可靠性和高性能的通用存储引擎。在 MySQL 5.7 中，除非你配置了其余默认存储引擎，否则执行 CREATE TABLE 不指定 ENGINE 的语句将创立一个 InnoDB 表。 # 查看MySQL反对的存储引擎mysql> show engines;+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+| Engine | Support | Comment | Transactions | XA | Savepoints |+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+| InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES || MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO || MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO || BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO || MyISAM | YES | MyISAM storage engine | NO | NO | NO || CSV | YES | CSV storage engine | NO | NO | NO || ARCHIVE | YES | Archive storage engine | NO | NO | NO || PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO || FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL |+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+# 查看默认存储引擎mysql> show variables like 'default_storage_engine';+------------------------+--------+| Variable_name | Value |+------------------------+--------+| default_storage_engine | InnoDB |+------------------------+--------+2. InnoDB 劣势InnoDB 之所以如此受宠，次要在于其性能方面的较多劣势。 ...
排查Mysql忽然变慢的一次过程 本文源地址：排查Mysql忽然变慢的一次过程上周客户说零碎忽然变得很慢，而且时不时的蹦出一个 404 和 500，弄得真的是很没面子，而凑巧出问题的时候正在深圳出差，所以始终没有工夫 看问题，始终到明天，才算是把问题起因找到。 定位问题刚开始失去是零碎慢的反馈，没有将问题点定位到数据库上，查了半天服务是否失常（因为之前有一次Dubbo内存透露）。 在将应用服务日志查看了一遍后，没有发现任何异样，只是打了几个正告的日志。 于是又查看了业务运行时的日志，看到日志都提醒了一个 Lock wait timeout exceeded; try restarting transaction 的异样。 这时还是没有将重心放到数据库上，认为是代码的起因，导致事务始终没有提交。 从新将代码审阅了一遍，感觉应该不是代码逻辑的问题，而这个时候， Lock wait timeout exceeded; try restarting transaction 这个异样的日志越来越多。 认为是数据库层面出了问题，开始排查数据库。 寻找起因因为咱们的数据库不是用的 云RDS版本，是在一台8核32G的AWS上的装置版本。 应用 top 命令，查看到 Mysql 占用的 CPU 使用率高达 90% 左右。 心里一慌，感觉不妙，这样子高负载的CPU使用率，搞不好服务器都要宕掉。 于是拿出了仅有的一点Mysql基本知识，基本上这次只应用到了上面几个语句： 查看以后Mysql所有的过程show processlist; 查看Mysql的最大缓存show global variables like "global max_allowed_packet" 查看以后正在进行的事务select * from information_schema.INNODB_TRX 查看以后Mysql的连接数show status like 'thread%' 解决依照下面的几个语句，一步一步跟踪定位下来。 show processlist; 下来，咱们就能够查看出以后所有的过程，并且失去最耗时的过程。在以后数据库中，看到处于 Sleep 状态的SQL十分多，而这也是占用CPU过高的重大起因，休眠线程太多，于是配置了一个 wait_time_out 为 600 秒的一个解决方案。 ...
前言在本周写的性能里，user和college两个实体关系由一对多转为多对多，之前在user表里存有college_id字段，如果实体关系转变，那么须要新建两头表，college_id字段也就没用了。 建表在SpringBoot中，jpa能够主动生成数据表，前提是要把字段定义好 /** * 学院 */ @ManyToMany(cascade = {CascadeType.REMOVE}) @JoinTable(name = "user_colleges", joinColumns = @JoinColumn(name = "users_id"), inverseJoinColumns = @JoinColumn(name = "colleges_id") ) @JsonView(CollegeJsonView.class) private List<College> colleges = new ArrayList<>();cascade = {CascadeType.REMOVE} 示意该实体领有对两头表数据进行删除操作的权限， @JoinTable外面各属性用以定义两头表名称，字段名称等。而后运行我的项目，能够发现两头表生成了： 数据迁徙因为没接触过太多SQL语句，所以只能求助谷歌，好在网上相似的需要很多，咱们要将user表里的 id, college_id 两个字段迁徙到user_colleges表里对应的 users_id, colleges_id 两个字段，对应的语句如下： INSERT INTO user_colleges (users_id, colleges_id) SELECT id, college_id FROM user;下面语句的意思是，在user表里遍历id, college_id 字段的数据并插入到user_colleges表的对应字段，语句写好了，而后执行命令，果然，报错了： 字段colleges_id不能为空，而后看一下为啥会报错，因为是从user表里迁徙，关上user表看一下，居然有的数据是空的：只能更改条件，既然有的是空的，那就以是否为空作为判断条件： INSERT INTO user_colleges (users_id, colleges_id) SELECT id, college_id FROM user WHERE college_id IS NOT NULL ;查找不是空的数据插入到相应的表中，执行：数据胜利迁徙，而后再迁徙是空的数据： ...
摘要：在数据库查问中,往往会须要查问多个表的数据,比方查问会员信息同时查问对于这个会员的订单信息,如果分语句查问的话,效率会很低,就须要用到join关键字来连表查问了。Join并行Join并行1. 多表join介绍2. 多表Join的形式不应用Join buffer应用Join buffer3. Join执行流程（老执行器） 1. 多表join介绍JOIN子句用于依据两个或多个表之间的相干列来组合它们。 例如： Orders： Customers： SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDateFROM OrdersINNER JOIN Customers ON Orders.CustomerID=Customers.CustomerID; 2. 多表Join的形式Hash join应用新执行器实现，在这里不做探讨 MySQL反对的都是Nested-Loop Join，以及它的变种。 不应用Join buffera) Simple Nested-Loop对r表的每一行，残缺扫描s表，依据r[i]-s[i]组成的行去判断是否满足条件，并返回满足条件的后果给客户端。 mysql> show create table t1;+-------+----------------------------------------------------------------------------------------------------------------+| Table | Create Table |+-------+----------------------------------------------------------------------------------------------------------------+| t1 | CREATE TABLE `t1` ( `id` int(11) NOT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |+-------+----------------------------------------------------------------------------------------------------------------+1 row in set (0.00 sec)mysql> show create table t3;+-------+--------------------------------------------------------------------------------------------------------------------+| Table | Create Table |+-------+--------------------------------------------------------------------------------------------------------------------+| t3 | CREATE TABLE `t3` ( `id` int(11) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |+-------+--------------------------------------------------------------------------------------------------------------------+1 row in set (0.00 sec)mysql> explain select /*+ NO_BNL() */ * from t1, t3 where t1.id = t3.id;+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+| 1 | SIMPLE | t1 | NULL | ALL | NULL | NULL | NULL | NULL | 2 | 100.00 | NULL || 1 | SIMPLE | t3 | NULL | ALL | NULL | NULL | NULL | NULL | 2 | 50.00 | Using where |+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+2 rows in set, 1 warning (0.00 sec)b) Index Nested-Loop对r表的每一行，先依据连贯条件去查问s表索引，而后回表查到匹配的数据，并返回满足条件的后果给客户端。 ...
十五、MySQL是怎么保障高可用的？失常状况下，只有主库执行更新生成的所有 binlog，都能够传到备库并被正确地执行，备库就能达到跟主库统一的状态，这就是最终一致性。 主备提早主备切换可能是一个被动运维动作，比方软件降级、主库所在机器按计划下线等，也可能是被动操作，比方主库所在机器掉电。与数据同步无关的工夫点次要包含以下三个： 主库 A 执行实现一个事务，写入 binlog，咱们把这个时刻记为 T1;之后传给备库 B，咱们把备库 B 接管完这个 binlog 的时刻记为 T2;备库 B 执行实现这个事务，咱们把这个时刻记为 T3。所谓主备提早，就是同一个事务，在备库执行实现的工夫和主库执行实现的工夫之间的差值，也就是 T3-T1。能够在备库上执行show slave status 命令，它的返回后果外面会显示seconds_behind_master，用于示意以后备库提早了多少秒。seconds_behind_master 的计算方法是这样的： 每个事务的 binlog 外面都有一个工夫字段，用于记录主库上写入的工夫；备库取出以后正在执行的事务的工夫字段的值，计算它与以后零碎工夫的差值，失去seconds_behind_master。如果主备库机器的零碎工夫设置不统一，会不会导致主备提早的值不准？不会的。因为，备库连贯到主库的时候，会通过执行 SELECT UNIX_TIMESTAMP() 函数来取得以后主库的零碎工夫。如果这时候发现主库的零碎工夫与本人不统一，备库在执行seconds_behind_master 计算的时候会主动扣掉这个差值。 须要阐明的是，在网络失常的时候，日志从主库传给备库所需的工夫是很短的，即 T2-T1的值是十分小的。也就是说，网络失常状况下，主备提早的次要起源是备库接管完 binlog和执行完这个事务之间的时间差。所以说，主备提早最间接的体现是，备库生产直达日志（relay log）的速度，比主库生产binlog 的速度要慢。 主备提早的起源备库所在机器的性能要比主库所在的机器性能差备库的压力大个别的想法是，主库既然提供了写能力，那么备库能够提供一些读能力。或者一些经营后盾须要的剖析语句，不能影响失常业务，所以只能在备库上跑。这种状况，咱们个别能够这么解决： 一主多从。除了备库外，能够多接几个从库，让这些从库来分担读的压力。通过 binlog 输入到内部零碎，比方 Hadoop 这类零碎，让内部零碎提供统计类查问的能力。其中，一主多从的形式大都会被采纳。因为作为数据库系统，还必须保障有定期全量备份的能力。而从库，就很适宜用来做备份。从库和备库在概念上其实差不多。 大事务因为主库上必须等事务执行实现才会写入 binlog，再传给备库。所以，如果一个主库上的语句执行 10 分钟，那这个事务很可能就会导致从库提早 10分钟。不要一次性地用 delete 语句删除太多数据。其实，这就是一个典型的大事务场景。 比方，一些归档类的数据，平时没有留神删除历史数据，等到空间快满了，业务开发人员要一次性地删掉大量历史数据。同时，又因为要防止在高峰期操作会影响业务（至多有这个意识还是很不错的），所以会在早晨执行这些大量数据的删除操作。 另一种典型的大事务场景，就是大表 DDL。 备库的并行复制能力可靠性优先策略在图 1 的双 M 构造下，从状态 1 到状态 2 切换的具体过程是这样的： 判断备库 B 当初的 seconds_behind_master，如果小于某个值（比方 5 秒）持续下一步，否则继续重试这一步；把主库 A 改成只读状态，即把 readonly 设置为 true；判断备库 B 的 seconds_behind_master 的值，直到这个值变成 0 为止；把备库 B 改成可读写状态，也就是把 readonly 设置为 false；把业务申请切到备库 B。这个切换流程，个别是由专门的 HA 零碎来实现的，咱们临时称之为可靠性优先流程。备注：图中的 SBM，是 seconds_behind_master 参数的简写。 ...
十五、MySQL是怎么保障高可用的？失常状况下，只有主库执行更新生成的所有 binlog，都能够传到备库并被正确地执行，备库就能达到跟主库统一的状态，这就是最终一致性。 主备提早主备切换可能是一个被动运维动作，比方软件降级、主库所在机器按计划下线等，也可能是被动操作，比方主库所在机器掉电。与数据同步无关的工夫点次要包含以下三个： 主库 A 执行实现一个事务，写入 binlog，咱们把这个时刻记为 T1;之后传给备库 B，咱们把备库 B 接管完这个 binlog 的时刻记为 T2;备库 B 执行实现这个事务，咱们把这个时刻记为 T3。所谓主备提早，就是同一个事务，在备库执行实现的工夫和主库执行实现的工夫之间的差值，也就是 T3-T1。能够在备库上执行show slave status 命令，它的返回后果外面会显示seconds_behind_master，用于示意以后备库提早了多少秒。seconds_behind_master 的计算方法是这样的： 每个事务的 binlog 外面都有一个工夫字段，用于记录主库上写入的工夫；备库取出以后正在执行的事务的工夫字段的值，计算它与以后零碎工夫的差值，失去seconds_behind_master。如果主备库机器的零碎工夫设置不统一，会不会导致主备提早的值不准？不会的。因为，备库连贯到主库的时候，会通过执行 SELECT UNIX_TIMESTAMP() 函数来取得以后主库的零碎工夫。如果这时候发现主库的零碎工夫与本人不统一，备库在执行seconds_behind_master 计算的时候会主动扣掉这个差值。 须要阐明的是，在网络失常的时候，日志从主库传给备库所需的工夫是很短的，即 T2-T1的值是十分小的。也就是说，网络失常状况下，主备提早的次要起源是备库接管完 binlog和执行完这个事务之间的时间差。所以说，主备提早最间接的体现是，备库生产直达日志（relay log）的速度，比主库生产binlog 的速度要慢。 主备提早的起源备库所在机器的性能要比主库所在的机器性能差备库的压力大个别的想法是，主库既然提供了写能力，那么备库能够提供一些读能力。或者一些经营后盾须要的剖析语句，不能影响失常业务，所以只能在备库上跑。这种状况，咱们个别能够这么解决： 一主多从。除了备库外，能够多接几个从库，让这些从库来分担读的压力。通过 binlog 输入到内部零碎，比方 Hadoop 这类零碎，让内部零碎提供统计类查问的能力。其中，一主多从的形式大都会被采纳。因为作为数据库系统，还必须保障有定期全量备份的能力。而从库，就很适宜用来做备份。从库和备库在概念上其实差不多。 大事务因为主库上必须等事务执行实现才会写入 binlog，再传给备库。所以，如果一个主库上的语句执行 10 分钟，那这个事务很可能就会导致从库提早 10分钟。不要一次性地用 delete 语句删除太多数据。其实，这就是一个典型的大事务场景。 比方，一些归档类的数据，平时没有留神删除历史数据，等到空间快满了，业务开发人员要一次性地删掉大量历史数据。同时，又因为要防止在高峰期操作会影响业务（至多有这个意识还是很不错的），所以会在早晨执行这些大量数据的删除操作。 另一种典型的大事务场景，就是大表 DDL。 备库的并行复制能力可靠性优先策略在图 1 的双 M 构造下，从状态 1 到状态 2 切换的具体过程是这样的： 判断备库 B 当初的 seconds_behind_master，如果小于某个值（比方 5 秒）持续下一步，否则继续重试这一步；把主库 A 改成只读状态，即把 readonly 设置为 true；判断备库 B 的 seconds_behind_master 的值，直到这个值变成 0 为止；把备库 B 改成可读写状态，也就是把 readonly 设置为 false；把业务申请切到备库 B。这个切换流程，个别是由专门的 HA 零碎来实现的，咱们临时称之为可靠性优先流程。备注：图中的 SBM，是 seconds_behind_master 参数的简写。 ...
十五、MySQL是怎么保障高可用的？失常状况下，只有主库执行更新生成的所有 binlog，都能够传到备库并被正确地执行，备库就能达到跟主库统一的状态，这就是最终一致性。 主备提早主备切换可能是一个被动运维动作，比方软件降级、主库所在机器按计划下线等，也可能是被动操作，比方主库所在机器掉电。与数据同步无关的工夫点次要包含以下三个： 主库 A 执行实现一个事务，写入 binlog，咱们把这个时刻记为 T1;之后传给备库 B，咱们把备库 B 接管完这个 binlog 的时刻记为 T2;备库 B 执行实现这个事务，咱们把这个时刻记为 T3。所谓主备提早，就是同一个事务，在备库执行实现的工夫和主库执行实现的工夫之间的差值，也就是 T3-T1。能够在备库上执行show slave status 命令，它的返回后果外面会显示seconds_behind_master，用于示意以后备库提早了多少秒。seconds_behind_master 的计算方法是这样的： 每个事务的 binlog 外面都有一个工夫字段，用于记录主库上写入的工夫；备库取出以后正在执行的事务的工夫字段的值，计算它与以后零碎工夫的差值，失去seconds_behind_master。如果主备库机器的零碎工夫设置不统一，会不会导致主备提早的值不准？不会的。因为，备库连贯到主库的时候，会通过执行 SELECT UNIX_TIMESTAMP() 函数来取得以后主库的零碎工夫。如果这时候发现主库的零碎工夫与本人不统一，备库在执行seconds_behind_master 计算的时候会主动扣掉这个差值。 须要阐明的是，在网络失常的时候，日志从主库传给备库所需的工夫是很短的，即 T2-T1的值是十分小的。也就是说，网络失常状况下，主备提早的次要起源是备库接管完 binlog和执行完这个事务之间的时间差。所以说，主备提早最间接的体现是，备库生产直达日志（relay log）的速度，比主库生产binlog 的速度要慢。 主备提早的起源备库所在机器的性能要比主库所在的机器性能差备库的压力大个别的想法是，主库既然提供了写能力，那么备库能够提供一些读能力。或者一些经营后盾须要的剖析语句，不能影响失常业务，所以只能在备库上跑。这种状况，咱们个别能够这么解决： 一主多从。除了备库外，能够多接几个从库，让这些从库来分担读的压力。通过 binlog 输入到内部零碎，比方 Hadoop 这类零碎，让内部零碎提供统计类查问的能力。其中，一主多从的形式大都会被采纳。因为作为数据库系统，还必须保障有定期全量备份的能力。而从库，就很适宜用来做备份。从库和备库在概念上其实差不多。 大事务因为主库上必须等事务执行实现才会写入 binlog，再传给备库。所以，如果一个主库上的语句执行 10 分钟，那这个事务很可能就会导致从库提早 10分钟。不要一次性地用 delete 语句删除太多数据。其实，这就是一个典型的大事务场景。 比方，一些归档类的数据，平时没有留神删除历史数据，等到空间快满了，业务开发人员要一次性地删掉大量历史数据。同时，又因为要防止在高峰期操作会影响业务（至多有这个意识还是很不错的），所以会在早晨执行这些大量数据的删除操作。 另一种典型的大事务场景，就是大表 DDL。 备库的并行复制能力可靠性优先策略在图 1 的双 M 构造下，从状态 1 到状态 2 切换的具体过程是这样的： 判断备库 B 当初的 seconds_behind_master，如果小于某个值（比方 5 秒）持续下一步，否则继续重试这一步；把主库 A 改成只读状态，即把 readonly 设置为 true；判断备库 B 的 seconds_behind_master 的值，直到这个值变成 0 为止；把备库 B 改成可读写状态，也就是把 readonly 设置为 false；把业务申请切到备库 B。这个切换流程，个别是由专门的 HA 零碎来实现的，咱们临时称之为可靠性优先流程。备注：图中的 SBM，是 seconds_behind_master 参数的简写。 ...
十二、MySQL中进步性能的办法短连贯风暴连贯到数据库后，执行很少的 SQL 语句就断开，下次须要的时候再重连。 先解决掉那些占着连贯然而不工作的线程。能够通过 kill connection 被动踢掉不工作的线程。这个行为跟当时设置wait_timeout 的成果是一样的。设置 wait_timeout 参数示意的是，一个线程闲暇 wait_timeout 这么多秒之后，就会被 MySQL 间接断开连接。缩小连贯过程的耗费。有的业务代码会在短时间内先大量申请数据库连贯做备用，如果当初数据库确认是被连贯行为打挂了，那么一种可能的做法，是让数据库跳过权限验证阶段。慢查问性能问题索引没有设计好这种场景个别就是通过紧急创立索引来解决。比拟现实的是可能在备库先执行。假如你当初的服务是一主一备，主库 A、备库 B，这个计划的大抵流程是这样的： 在备库 B 上执行 set sql_log_bin=off，也就是不写 binlog，而后执行 alter table 语句加上索引；执行主备切换；这时候主库是 B，备库是 A。在 A 上执行 set sql_log_bin=off，而后执行 alter table语句加上索引。**或者思考gh-ost。**SQL 语句没写好SQL 语句没写，导致语句没有应用上索引。MySQL 选错了索引应急计划就是给这个语句加上 force index。如何防止上述问题： 上线前，在测试环境，把慢查问日志（slow log）关上，并且把 long_query_time 设置成 0，确保每个语句都会被记录入慢查问日志；在测试表里插入模仿线上的数据，做一遍回归测试；察看慢查问日志里每类语句的输入，特地注意 Rows_examined 字段是否与预期统一。（咱们在后面文章中曾经屡次用到过 Rows_examined 办法了，置信你曾经入手尝试过了。如果还有不明确的，欢送给我留言，咱们一起探讨）。QPS 突增问题有时候因为业务忽然呈现顶峰，或者应用程序 bug，导致某个语句的 QPS 忽然暴涨，也可能导致 MySQL 压力过大，影响服务。最现实的状况是让业务把这个性能下掉，服务天然就会复原。 一种是由全新业务的 bug 导致的。假如你的 DB 运维是比拟标准的，也就是说白名单是一个个加的。这种状况下，如果你可能确定业务方会下掉这个性能，只是工夫上没那么快，那么就能够从数据库端间接把白名单去掉。如果这个新性能应用的是独自的数据库用户，能够用管理员账号把这个用户删掉，而后断开现有连贯。这样，这个新性能的连贯不胜利，由它引发的 QPS 就会变成 0。如果这个新增的性能跟主体性能是部署在一起的，那么咱们只能通过解决语句来限度。这时，咱们能够应用下面提到的查问重写性能，把压力最大的 SQL 语句间接重写成"select 1"返回。十三、MySQL是怎么保证数据不丢的？binlog 的写入机制事务执行过程中，先把日志写到 binlog cache，事务提交的时候，再把 binlog cache 写到 binlog 文件中。 ...
五、为什么表数据删除后，表文件大小不变？一个 InnoDB 表蕴含两局部，即：表构造定义和数据。在 MySQL 8.0 版本以前，表构造是存在以.frm 为后缀的文件里。而 MySQL 8.0 版本，则曾经容许把表构造定义放在零碎数据表中了。 表数据表数据既能够存在共享表空间里，也能够是独自的文件。这个行为是由参数innodb_file_per_table 管制的： 这个参数设置为 OFF 示意的是，表的数据放在零碎共享表空间，也就是跟数据字典放在一起；这个参数设置为 ON 示意的是，每个 InnoDB 表数据存储在一个以 .ibd 为后缀的文件中。不管应用 MySQL 的哪个版本，都将这个值设置为 ON。因为，一个表独自存储为一个文件更容易治理，而且在你不须要这个表的时候，通过 drop table 命令，零碎就会间接删除这个文件。而如果是放在共享表空间中，即便表删掉了，空间也是不会回收的。 数据删除流程InnoDB 引擎只会把要删除的记录标记为删除。如果之后要在这个地位插入一个记录时，可能会复用这个地位。然而，磁盘文件的大小并不会放大。如果咱们删掉了一个数据页上的所有记录，会怎么样？答案是，整个数据页就能够被复用了。然而，数据页的复用跟记录的复用是不同的。记录的复用，只限于合乎范畴条件的数据。比方下面的这个例子，ID=400 这条记录被删除后，如果插入一个 ID 是 400 的行，能够间接复用这个空间。而当整个页从 B+ 树外面摘掉当前，能够复用到任何地位。如果相邻的两个数据页利用率都很小，零碎就会把这两个页上的数据合到其中一个页上，另外一个数据页就被标记为可复用。 delete 命令其实只是把记录的地位，或者数据页标记为了“可复用”，但磁盘文件的大小是不会变的。也就是说，通过 delete 命令是不能回收表空间的。 如果数据是依照索引递增程序插入的，那么索引是紧凑的。但如果数据是随机插入的，就可能造成索引的数据页决裂。 重建表通过大量增删改的表，都是可能是存在空洞的。所以，如果可能把这些空洞去掉，就能达到膨胀表空间的目标。而重建表，就能够达到这样的目标。alter table A engine=InnoDB，用来重建表，原理是 Online DDL重建表的流程： 建设一个临时文件，扫描表 A 主键的所有数据页；用数据页中表 A 的记录生成 B+ 树，存储到临时文件中；生成临时文件的过程中，将所有对 A 的操作记录在一个日志文件（row log）中，对应的是图中 state2 的状态；临时文件生成后，将日志文件中的操作利用到临时文件，失去一个逻辑数据上与表 A 雷同的数据文件，对应的就是图中 state3 的状态；用临时文件替换表 A 的数据文件。alter 语句在启动的时候须要获取 MDL（元数据） 写锁，然而这个写锁在真正拷贝数据之前就进化成读锁了。为什么要进化呢？为了实现 Online，MDL 读锁不会阻塞增删改操作。那为什么不罗唆间接解锁呢？为了爱护本人，禁止其余线程对这个表同时做 DDL。 ...
五、为什么表数据删除后，表文件大小不变？一个 InnoDB 表蕴含两局部，即：表构造定义和数据。在 MySQL 8.0 版本以前，表构造是存在以.frm 为后缀的文件里。而 MySQL 8.0 版本，则曾经容许把表构造定义放在零碎数据表中了。 表数据表数据既能够存在共享表空间里，也能够是独自的文件。这个行为是由参数innodb_file_per_table 管制的： 这个参数设置为 OFF 示意的是，表的数据放在零碎共享表空间，也就是跟数据字典放在一起；这个参数设置为 ON 示意的是，每个 InnoDB 表数据存储在一个以 .ibd 为后缀的文件中。不管应用 MySQL 的哪个版本，都将这个值设置为 ON。因为，一个表独自存储为一个文件更容易治理，而且在你不须要这个表的时候，通过 drop table 命令，零碎就会间接删除这个文件。而如果是放在共享表空间中，即便表删掉了，空间也是不会回收的。 数据删除流程InnoDB 引擎只会把要删除的记录标记为删除。如果之后要在这个地位插入一个记录时，可能会复用这个地位。然而，磁盘文件的大小并不会放大。如果咱们删掉了一个数据页上的所有记录，会怎么样？答案是，整个数据页就能够被复用了。然而，数据页的复用跟记录的复用是不同的。记录的复用，只限于合乎范畴条件的数据。比方下面的这个例子，ID=400 这条记录被删除后，如果插入一个 ID 是 400 的行，能够间接复用这个空间。而当整个页从 B+ 树外面摘掉当前，能够复用到任何地位。如果相邻的两个数据页利用率都很小，零碎就会把这两个页上的数据合到其中一个页上，另外一个数据页就被标记为可复用。 delete 命令其实只是把记录的地位，或者数据页标记为了“可复用”，但磁盘文件的大小是不会变的。也就是说，通过 delete 命令是不能回收表空间的。 如果数据是依照索引递增程序插入的，那么索引是紧凑的。但如果数据是随机插入的，就可能造成索引的数据页决裂。 重建表通过大量增删改的表，都是可能是存在空洞的。所以，如果可能把这些空洞去掉，就能达到膨胀表空间的目标。而重建表，就能够达到这样的目标。alter table A engine=InnoDB，用来重建表，原理是 Online DDL重建表的流程： 建设一个临时文件，扫描表 A 主键的所有数据页；用数据页中表 A 的记录生成 B+ 树，存储到临时文件中；生成临时文件的过程中，将所有对 A 的操作记录在一个日志文件（row log）中，对应的是图中 state2 的状态；临时文件生成后，将日志文件中的操作利用到临时文件，失去一个逻辑数据上与表 A 雷同的数据文件，对应的就是图中 state3 的状态；用临时文件替换表 A 的数据文件。alter 语句在启动的时候须要获取 MDL（元数据） 写锁，然而这个写锁在真正拷贝数据之前就进化成读锁了。为什么要进化呢？为了实现 Online，MDL 读锁不会阻塞增删改操作。那为什么不罗唆间接解锁呢？为了爱护本人，禁止其余线程对这个表同时做 DDL。 ...
一、一般索引和惟一索引查问过程对于一般索引来说，查找到满足条件的第一个记录后，须要查找下一个记录，直到碰到第一个不满足 条件的记录。 对于惟一索引来说，因为索引定义了唯一性，查找到第一个满足条件的记录后，就会进行持续检索。 InnoDB 的数据是按数据页为单位来读写的。在InnoDB 中，每个数据页的大小默认是 16KB。对于整型字段，一个数据页能够放近千个 key。 更新过程当须要更新一个数据页时，如果数据页在内存中就间接更新， 而如果这个数据页还没有在内存中的话，在不影响数据一致性的前提下，InooDB 会将这些更新操作缓存在 changebuffer 中， 这样就不须要从磁盘中读入这个数据页了。在下次查问须要拜访这个数据页的时候，将数据页读入内存，而后执行 change buffer 中与这个页无关的操作。 将 change buffer 中的操作利用到原数据页，失去最新后果的过程称为 merge。除了拜访这个数据页会触发 merge 外，零碎有后盾线程会定期 merge。在数据库失常敞开（shutdown）的过程中，也会执行 merge 操作。惟一索引的更新操作都要先判断这个操作是否违反唯一性束缚。而这必须要将数据页读入内存能力判断。所以惟一索引的更新不能应用 change buffer，只有一般索引能够应用。 change buffer 用的是 buffer pool 里的内存，因而不能有限增大。change buffer 的大小，能够通过参数 innodb_change_buffer_max_size 来动静设置。这个参数设置为 50 的时候，示意 change buffer 的大小最多只能占用 buffer pool 的 50%。 如果插入一个新记录 (4,400) 的话： 这个记录要更新的指标页在内存中对于惟一索引来说，找到 3 和 5 之间的地位，判断到没有抵触，插入这个值，语句执行完结；对于一般索引来说，找到 3 和 5 之间的地位，插入这个值，语句执行完结。 这个记录要更新的指标页不在内存中对于惟一索引来说，须要将数据页读入内存，判断到没有抵触，插入这个值，语句执行完结；对于一般索引来说，则是将更新记录在 change buffer，语句执行就完结了。 change buffer 的应用场景对于写多读少的业务来说，页面在写完当前马上被拜访到的概率比拟小，此时change buffer 的应用成果最好。这种业务模型常见的就是账单类、日志类的零碎。反过来，假如一个业务的更新模式是写入之后马上会做查问，那么即便满足了条件，将更新先记录在 change buffer，但之后因为马上要拜访这个数据页，会立刻触发 merge 过程。这样随机拜访 IO 的次数不会缩小，反而减少了 change buffer 的保护代价。所以，对于这种业务模式来说，change buffer 反而起到了副作用。 ...
一、一般索引和惟一索引查问过程对于一般索引来说，查找到满足条件的第一个记录后，须要查找下一个记录，直到碰到第一个不满足 条件的记录。 对于惟一索引来说，因为索引定义了唯一性，查找到第一个满足条件的记录后，就会进行持续检索。 InnoDB 的数据是按数据页为单位来读写的。在InnoDB 中，每个数据页的大小默认是 16KB。对于整型字段，一个数据页能够放近千个 key。 更新过程当须要更新一个数据页时，如果数据页在内存中就间接更新， 而如果这个数据页还没有在内存中的话，在不影响数据一致性的前提下，InooDB 会将这些更新操作缓存在 changebuffer 中， 这样就不须要从磁盘中读入这个数据页了。在下次查问须要拜访这个数据页的时候，将数据页读入内存，而后执行 change buffer 中与这个页无关的操作。 将 change buffer 中的操作利用到原数据页，失去最新后果的过程称为 merge。除了拜访这个数据页会触发 merge 外，零碎有后盾线程会定期 merge。在数据库失常敞开（shutdown）的过程中，也会执行 merge 操作。惟一索引的更新操作都要先判断这个操作是否违反唯一性束缚。而这必须要将数据页读入内存能力判断。所以惟一索引的更新不能应用 change buffer，只有一般索引能够应用。 change buffer 用的是 buffer pool 里的内存，因而不能有限增大。change buffer 的大小，能够通过参数 innodb_change_buffer_max_size 来动静设置。这个参数设置为 50 的时候，示意 change buffer 的大小最多只能占用 buffer pool 的 50%。 如果插入一个新记录 (4,400) 的话： 这个记录要更新的指标页在内存中对于惟一索引来说，找到 3 和 5 之间的地位，判断到没有抵触，插入这个值，语句执行完结；对于一般索引来说，找到 3 和 5 之间的地位，插入这个值，语句执行完结。 这个记录要更新的指标页不在内存中对于惟一索引来说，须要将数据页读入内存，判断到没有抵触，插入这个值，语句执行完结；对于一般索引来说，则是将更新记录在 change buffer，语句执行就完结了。 change buffer 的应用场景对于写多读少的业务来说，页面在写完当前马上被拜访到的概率比拟小，此时change buffer 的应用成果最好。这种业务模型常见的就是账单类、日志类的零碎。反过来，假如一个业务的更新模式是写入之后马上会做查问，那么即便满足了条件，将更新先记录在 change buffer，但之后因为马上要拜访这个数据页，会立刻触发 merge 过程。这样随机拜访 IO 的次数不会缩小，反而减少了 change buffer 的保护代价。所以，对于这种业务模式来说，change buffer 反而起到了副作用。 ...
一、mysql 基础架构大体来说，MySQL 能够分为 Server 层和存储引擎层两局部。Server 层包含连接器、查问缓存、分析器、优化器、执行器等，涵盖 MySQL 的大多数外围服务性能，以及所有的内置函数（如日期、工夫、数学和加密函数等），所有跨存储引擎的性能都在这一层实现，比方存储过程、触发器、视图等。 连接器一个用户胜利建设连贯后，即便你用管理员账号对这个用户的权限做了批改，也不会影响曾经存在连贯的权限。批改实现后，只有再新建的连贯才会应用新的权限设置。建设连贯的过程通常是比较复杂的，所以我倡议你在应用中要尽量减少建设连贯的动作，也就是尽量应用长连贯。然而全副应用长连贯后，你可能会发现，有些时候 MySQL 占用内存涨得特地快，这是因为 MySQL 在执行过程中长期应用的内存是治理在连贯对象外面的。这些资源会在连贯断开的时候才开释。所以如果长连贯累积下来，可能导致内存占用太大，被零碎强行杀掉（OOM），从景象看就是 MySQL 异样重启了。怎么解决这个问题呢？你能够思考以下两种计划。 定期断开长连贯。应用一段时间，或者程序外面判断执行过一个占用内存的大查问后，断开连接，之后要查问再重连。如果你用的是 MySQL 5.7 或更新版本，能够在每次执行一个比拟大的操作后，通过执行mysql_reset_connection 来从新初始化连贯资源。这个过程不须要重连和从新做权限验证，然而会将连贯复原到刚刚创立完时的状态。查问缓存大多数状况下不要应用查问缓存 分析器词法剖析，谬误时会收到 "You have an error in your SQL syntax"，也会在这个阶段判断查问条件是否蕴含在这个表。在分析阶段判断语句是否正确，表是否存在，列是否存在等。 优化器优化器是在表外面有多个索引的时候，决定应用哪个索引；或者在一个语句有多表关联（join）的时候，决定各个表的连贯程序。 执行器开始执行的时候，要先判断一下你对这个表有没有执行查问的权限，如果没有，就会返回没有权限的谬误。如果有权限，就关上表继续执行。关上表的时候，执行器就会依据表的引擎定义，去应用这个引擎提供的接口。为什么不在执行器之前判断权限？ 有些时候，SQL语句要操作的表不只是SQL字面上那些。比方如果有个触发器，得在执行器阶段（过程中）能力确定。优化器阶段前是无能为力的 二、日志零碎redo log (InnoDB 引擎特有的日志)WAL技术，WAL 的全称是 Write-Ahead Logging，它的关键点就是先写日志，再写磁盘。当有一条记录须要更新的时候，InnoDB 引擎就会先把记录写到 redo log 外面，并更新内存，这个时候更新就算实现了。同时，InnoDB 引擎会在适当的时候，将这个操作记录更新到磁盘外面，而这个更新往往是在零碎比拟闲暇的时候做。redo log 用于保障 crash-safe 能力。innodb_flush_log_at_trx_commit 这个参数设置成1 的时候，示意每次事务的 redo log 都间接长久化到磁盘。工夫轮 binlog(server层的日志)这两种日志有以下三点不同。 redo log 是 InnoDB 引擎特有的；binlog 是 MySQL 的 Server 层实现的，所有引擎都能够应用。redo log 是物理日志，记录的是“在某个数据页上做了什么批改”；binlog 是逻辑日志，记录的是这个语句的原始逻辑，比方“给 ID=2 这一行的 c 字段加 1 ”。redo log 是循环写的，空间固定会用完；binlog 是能够追加写入的。“追加写”是指binlog 文件写到肯定大小后会切换到下一个，并不会笼罩以前的日志。redo log 记录数据页 “做了什么改变”，Binlog有两种模式，statement 格局的话是记sql语句， row格局会记录行的内容，记两条，更新前和更新后都有。浅色框示意是在 InnoDB 外部执行的，深色框是在执行器执行 ...
本文次要针对sql索引优化做出实际验证 一、常见的语句优化这里不做多的论述，因为都是些惯例的索引优化伎俩，想必大家都很分明以及确定如何应用1.like最左匹配准则2.用exists和not exists代替in和not in3.union all代替or4.不要在索引列上做任何操作，比方计算、函数、类型转换等，会导致索引生效5.尽量避免应用select *6.能用between and不必in7.尽量用>=代替> 二、联结索引的应用现有表mat_order订单表，联结索引由(rphone, order_id, money)组成针对这个联结索引，咱们测试一下，什么状况下会走索引，这里对explain的应用以及字段阐明不做过多解释，大家能够自行百度1.单个字段查问explain select * from mat_order where order_id like '20200804141149%';explain select * from mat_order where rphone like '1862%';explain select * from mat_order where money > 20000;以上explain后果得悉，只有第一个sql的order_id查问走了索引，与预期的统一，因为mysql的最左匹配准则，所以只有order_id的查问会走索引 2.多个字段查问这里用到联结索引的笼罩索引explain select * from mat_order where rphone like '1862%' and order_id like '20200804141149%';能够看到这里用了rphone和order_id的组合索引 explain select * from mat_order where order_id like '20200804141149%' and rphone like '1862%';将rphone和order_id的程序调整，仍然会走rphone和order_id的组合索引 explain select * from mat_order where order_id like '20200804141149%' and money > 20000;如果只有order_id和money字段查问，则不会走索引，根据最左准则，匹配不到 ...
dbForge Studioor MySQL是一个在Windows平台被宽泛应用的MySQL客户端，它可能使MySQL开发人员和管理人员在一个不便的环境中与别人一起实现创立和执行查问，开发和调试MySQL程序，自动化治理MySQL数据库对象等工作。 点击下载dbForge Studioor MySQL最新试用版 dbForge Studioor MySQL是用于MySQL和MariaDB数据库开发，治理和治理的通用GUI工具。它在2019年取得了DBTA读者抉择奖，并在2020年被评为IT治理产品50强。 以后，Devart的MySQL dbForge Studio是寰球最受欢迎的解决方案之一。在剖析了数百个用户的反馈后，咱们定义了对客户有意义的最重要的劣势。让咱们看看dbForge Studioor MySQL用户常常援用最便捷无效的选项： 1. MySQL调试器 存储过程和触发器的绝佳内置调试器。 在代码编辑器中，您能够设置断点，逐渐或残缺地执行过程，并在每次迭代中监督变量的值和查问执行的后果。 2. 查问生成器 立刻应用数据库，您只须要在工作区上搁置必要的实体，设置它们之间的关系，而后编辑字段参数即可。您能够从可视编辑器中唤起任何数据库元素，无论是表字段还是存储过程。当数据库准备就绪时，这可能十分不便，然而您须要疾速查找和编辑其元素之一。 3. 查问探查器 在查问分析器性能有助于在MySQL跟踪，从新创立，并解决问题。该工具使您能够剖析查问的性能，辨认生产力瓶颈，并通过优化占用大量工夫和资源的查问来打消它们。 4. 数据和架构比拟 这些功能强大的解决方案可轻松应答从生产环境到测试或开发环境的数据迁徙，并容许在一个不便易用的界面中实现数据库架构更改的完满同步。 5. SQL代码实现和格式化 dbForge Studio for MySQL提供了弱小的代码突出显示，语法补全和代码格式化性能。您能够创立和保留代码片段以备后用-将会依据您键入的内容来倡议它们。代码格式化配置文件使您能够使代码保持一致并达到要求的规范。 6.高级数据导入/导出 这些工具对于用内部源数据填充数据库以及在零碎之间迁徙数据至关重要。借助直观的导出向导，您能够抉择必要的行和列，从各种数据可视化类型中进行抉择，并将数据导出为多种受反对的格局之一，包含Text，MS Excel，MS Excel 2007，Google表格，MS Access，XML，CSV，DBF，ODBC，JSON。 7.可能同时连贯到多个不同的服务器 除了间接连贯，您还能够通过SSL，SSH或HTTP建设隧道连贯。轻松浏览连贯树将使您可能增加和删除连贯以及对其进行更改。 8.残缺的数据库用户治理和特权治理选项 应用Security Manager，您能够疾速轻松地创立和删除用户帐户，更改其设置以及治理拜访权限。图形用户界面通过在命令行中键入枯燥代码来授予对数据库安全保留的齐全控制权。应用“平安管理器”窗口的五个选项卡，您能够霎时查看帐户参数。 9.备份向导，容许打算数据库备份 配置无关如何备份数据库并生成备份查问以供当前应用的确切详细信息。或者，您能够应用命令行界面通过Windows Scheduler安顿备份过程。 10.存储会话状态 当您敞开IDE时，将保留工作会话的状态，因而下次关上它时，所有选项卡都将被复原而不会失落信息。 目前，实用于MySQL的dbForge Studio为35,000多个称心的用户提供服务，并面向更多用户。用 如果您正在寻找可提供多种性能的智能MySQL工具，dbForge Studio for MySQL肯定是你最佳的抉择。您还将取得专门的客户反对人员的业余帮忙。享受寰球应用和信赖的产品，这些产品受到《财产》 100强公司的青眼，其中包含雀巢，美国银行和宝马以及泛滥知名企业和集体。点击下载应用[收费试用版]
MySQL作为一种常见的数据库管理系统(DBMS)，其本身的各种配置项极大的影响了其性能。所以有必要进行理解和学习。 配置学习资源路径我最近在看《高性能Mysql》，其中第8章解说了对于配置的很多事项，都值得理解和学习。当然，官网也是最新信息查阅的重要渠道。 官网【可在5.1 TheMySQL Server找到配置解释。可在5.4找到BinLog相干内容。 】MySQL配置配置文件地位：一般来说，MySQL服务端配置文件的默认地位是：/etc/my.cnf 或者 /etc/mysql/my.cnf【也能够通过mysqld --verbose --help|grep -A 1 'Default option'确认配置文件的地位】 和nginx相似，也是分目录进行include，便于查看。 （比方 !includedir /etc/my.cnf.d配置批改根底：1、mysql配置文件是分段的，要留神配置项放在了正确的段里（比方服务器次要用 [mysqld] 这一段 1、副作用：一些配置项会产生副作用，长期批改配置须要十分小心。（比方变更 query\_cache\_size 会立刻删除所有查问的缓存，从新构建。2、变量的值：变量不是越大越好，可能会导致内存替换或者超出地址空间。须要跟进状况设定。2、配置项有不同的作用域，有的是全局，有的是会话。其次，还有动静变量，可在运行时批改。3、全局变量：批改后对以后会话及已存在的会话均不失效。可通过SHOW GLOBAL VARIABLES确认。4、变量的单位：配置时要留神单位，命令行或者配置文件，能够应用后缀指定单位（比方1M等，但要留神，应用SQL的SET指令时就不能应用单位。5、配置文件治理：最好应用git来进行版本治理，加上短缺的正文。能够防止不少问题。 如何创立一个靠谱的MySQL配置？1、一个好的配置，不是从学习配置项开始，也不是询问怎么设置或者怎么批改，更不是察看服务器行为和询问哪个配置能够晋升性能。【应该是从了解MySQL内核和行为开始】2、保障根底配置都正确（比方日志门路，缓存配置，端口号，数据库存储地位等。如非必要，尽量应用默认配置。（默认配置禁受过的测试是最多的。3、优先进行语句优化等其余优化，最初思考批改配置项。 一个最小配置示例一般来说，抉择尽量少的配置（或者说最小配置），如无必要，不必申明（不申明应用默认值）。 当然一些十分重要的配置项，即便应用默认配置，也最好申明进去。（比方default_storage_engine)见《高性能MySQL》P336 如何创立正当的MySQL配置文件？{上面的配置项根本都基于上述参考最小配置}1、Innodb的配置项： innodb_buffer_pool_size 【最外围配置，innodb重大依赖缓冲池。索引、行数据缓存、哈希索引，插入缓存、锁等。必须为innodb配置足够的缓冲池。[ 《高性能MySQL》P343innodb_log_file_size 【日志文件配置innodb_file_per_tableinnodb_flush_method2、MySQL配置项： thread_cache 【依据Thread_connected来判断，如果线程数较多，能够适当调大这个值。table_cache如何确认MySQL以后的状态变量？1、SHOW GLOBAL STATUS【或者能够通过SHOW、innotop等工具，确认innodb等的内存利用状况。2、mysqladmin extended-status -ri60每60秒查看状态变量的增值
作者：洪斌爱可生南区负责人兼技术服务总监，MySQL ACE，善于数据库架构布局、故障诊断、性能优化剖析，实践经验丰盛，帮忙各行业客户解决 MySQL 技术问题，为金融、运营商、互联网等行业客户提供 MySQL 整体解决方案。本文起源：转载自公众号-玩转MySQL*爱可生开源社区出品，原创内容未经受权不得随便应用，转载请分割小编并注明起源。MySQL Shell 8.0.21 减少了一种新的逻辑备份复原办法，有更快的备份复原效率，反对zstd实时压缩，反对分块并行导出，load data并行导入，还能备份到OCI的对象存储。 util.dumpInstance() 用于备份整个实例util.dumpSchemas() 用于备份指定schemautil.loadDump() 用于复原备份做了个比照测试，在零负载下mysql配置参数不变，备份/复原雷同schema，其中混合了大表和小表，看下这几种形式的实际效果如何。 比照后果 论断mysql shell应用默认参数zstd压缩+32M chunk并行导出，复原时单表能够并行load data，其备份和复原速度均优于非压缩+非分块。 测试中发现，若禁用压缩，也会禁用分块。 mysqldump备份和复原都是单线程执行，不压缩的备份效率更快，zstd的实时备份速度比gzip更快，复原速度最慢。 mysqlpump备份反对并行速度也很快，然而单线程复原是硬伤。 mydumper默认用gzip协定，备份速度与mysqldump根本一样，看来瓶颈在压缩上。 在非压缩非分块备份速度会更快。 复原速度中等，单表无奈并行。 综合上述测试后果，mysql shell新的备份复原形式是最快的，得益于应用了zstd实时压缩算法，备份复原均能够并行，对于单个大表也能够并行。 上面是局部测试过程供参考： MySQLShellutli.dumpSchemas/utli.loadDump备份Acquiring global read lockAll transactions have been startedLocking instance for backupGlobal read lock has been releasedWriting global DDL filesPreparing data dump for table `test`.`customer1`Writing DDL for schema `test`Writing DDL for table `test`.`sbtest1`Writing DDL for table `test`.`customer1`Writing DDL for table `test`.`sbtest10`Data dump for table `test`.`customer1` will be chunked using column `c_w_id`Preparing data dump for table `test`.`sbtest1`Data dump for table `test`.`sbtest1` will be chunked using column `id`Preparing data dump for table `test`.`sbtest10`Data dump for table `test`.`sbtest10` will be chunked using column `id`Preparing data dump for table `test`.`sbtest2`Data dump for table `test`.`sbtest2` will be chunked using column `id`Preparing data dump for table `test`.`sbtest4`Data dump for table `test`.`sbtest4` will be chunked using column `id`Preparing data dump for table `test`.`sbtest6`Data dump for table `test`.`sbtest6` will be chunked using column `id`Preparing data dump for table `test`.`sbtest8`Data dump for table `test`.`sbtest8` will be chunked using column `id`Running data dump using 4 threads.NOTE: Progress information uses estimated values and may not be accurate.Writing DDL for table `test`.`sbtest2`Writing DDL for table `test`.`sbtest4`Writing DDL for table `test`.`sbtest6`Writing DDL for table `test`.`sbtest8`Data dump for table `test`.`customer1` will be written to 3 filesData dump for table `test`.`sbtest10` will be written to 1 fileData dump for table `test`.`sbtest2` will be written to 1 fileData dump for table `test`.`sbtest4` will be written to 1 fileData dump for table `test`.`sbtest6` will be written to 1 fileData dump for table `test`.`sbtest8` will be written to 1 fileData dump for table `test`.`sbtest1` will be written to 160 files1 thds dumping - 98% (10.46M rows / ~10.62M rows), 589.52K rows/s, 115.55 MB/s uncompressed, 51.66 MB/s compressedDuration: 00:00:18sSchemas dumped: 1Tables dumped: 7Uncompressed data size: 2.06 GBCompressed data size: 922.35 MBCompression ratio: 2.2Rows written: 10464999Bytes written: 922.35 MBAverage uncompressed throughput: 109.46 MB/sAverage compressed throughput: 48.97 MB/s复原util.loadDump("test1")Loading DDL and Data from 'instance' using 4 threads.Target is MySQL 8.0.21. Dump was produced from MySQL 8.0.21Checking for pre-existing objects...Executing common preamble SQLExecuting DDL script for schema `test`Executing DDL script for `test`.`sbtest1`Executing DDL script for `test`.`sbtest4`Executing DDL script for `test`.`sbtest2`Executing DDL script for `test`.`sbtest8`Executing DDL script for `test`.`sbtest10`Executing DDL script for `test`.`sbtest6`Executing DDL script for `test`.`customer1`...[Worker000] test@sbtest1@158.tsv.zst: Records: 65736 Deleted: 0 Skipped: 0 Warnings: 0Executing common postamble SQL168 chunks (10.46M rows, 2.06 GB) for 7 tables in 1 schemas were loaded in 1 min 26 sec (avg throughput 23.97 MB/s)mysqldump备份/usr/bin/time mysqldump -umsandbox -pmsandbox -h127.0.0.1 -P8021 test | gzip > db.sql.gzmysqldump: [Warning] Using a password on the command line interface can be insecure. 169.40 real 24.65 user 1.34 sys复原 /usr/bin/time gzip -d < db.sql.gz | ./use test 257.11 real 9.74 user 0.55 sysmysqlpump备份/usr/bin/time mysqlpump --default-parallelism=4 -umsandbox -pmsandbox -h127.0.0.1 -P8021 test | gzip > db2.sql.gzDump progress: 6/7 tables, 10421749/10406264 rowsDump completed in 185352 185.50 real 31.18 user 6.34 sys复原/usr/bin/time gzip -d < db2.sql.gz | ./use test 121.17 real 9.66 user 0.76 sysmydumper/myloader备份/usr/bin/time mydumper -u msandbox -p msandbox -h 127.0.0.1 -P 8021 -B test -t 4 -v 3 -c -o dumper** Message: 21:44:55.958: Connected to a MySQL server** Message: 21:44:56.319: Started dump at: 2020-07-24 21:44:56** Message: 21:44:56.341: Written master status** Message: 21:44:56.420: Thread 1 connected using MySQL connection ID 22** Message: 21:44:56.537: Thread 2 connected using MySQL connection ID 23** Message: 21:44:56.651: Thread 3 connected using MySQL connection ID 24** Message: 21:44:56.769: Thread 4 connected using MySQL connection ID 25** Message: 21:44:56.878: Non-InnoDB dump complete, unlocking tables** Message: 21:44:56.878: Thread 4 dumping data for `test`.`sbtest10`** Message: 21:44:56.878: Thread 1 dumping data for `test`.`customer1`** Message: 21:44:56.878: Thread 3 dumping data for `test`.`sbtest1`** Message: 21:44:56.878: Thread 2 dumping data for `test`.`sbtest2`** Message: 21:44:57.139: Thread 2 dumping data for `test`.`sbtest4`** Message: 21:44:57.143: Thread 4 dumping data for `test`.`sbtest6`** Message: 21:44:57.396: Thread 2 dumping data for `test`.`sbtest8`** Message: 21:44:57.398: Thread 4 dumping schema for `test`.`customer1`** Message: 21:44:57.409: Thread 4 dumping schema for `test`.`sbtest1`** Message: 21:44:57.419: Thread 4 dumping schema for `test`.`sbtest10`** Message: 21:44:57.430: Thread 4 dumping schema for `test`.`sbtest2`** Message: 21:44:57.441: Thread 4 dumping schema for `test`.`sbtest4`** Message: 21:44:57.453: Thread 4 dumping schema for `test`.`sbtest6`** Message: 21:44:57.464: Thread 4 dumping schema for `test`.`sbtest8`** Message: 21:44:57.475: Thread 4 shutting down** Message: 21:44:57.636: Thread 2 shutting down** Message: 21:45:03.706: Thread 1 shutting down** Message: 21:47:40.297: Thread 3 shutting down** Message: 21:47:40.307: Finished dump at: 2020-07-24 21:47:40 164.54 real 167.58 user 2.28 sys注：应用的并行备份线程数与dumpSchema雷同。 ...
前言：策略模式和适配器模式很像 但前者策略的接口和相干类会裸露进去，并且每个策略的“计算内容”都不同【罕用于计算】。 一、钻研下echarts官网的重要配置 1.1 罕用项次要有title legend xAxis yAxis legend dataset series textStyle 如下图所示 ![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2ppa3VpY3VpNzQwMg_size_16_color_FFFFFF_t_70][] ![20200704092450556.png][] 二、建设Echarts的option类 我在此命名为EchartsOption 【注：前面个别option下间接属性 java命名前缀都是Echarts】 package com.ken.sys.common.entity.echarts.option;import com.ken.sys.common.entity.echarts.axis.EchartsXYAxis;import com.ken.sys.common.entity.echarts.dataset.EchartDataset;import com.ken.sys.common.entity.echarts.legend.EchartsLegend;import com.ken.sys.common.entity.echarts.series.EchartBaseSeries;import com.ken.sys.common.entity.echarts.textStyle.EchartsTextStyle;import com.ken.sys.common.entity.echarts.title.EchartsTitle;import java.util.List;/** * * @author swc * * @date 2020/7/3 0003 下午 13:43 */public class EchartsOption { //题目 private EchartsTitle title; //图例组件。 //图例组件展示了不同系列的标记(symbol)，色彩和名字。能够通过点击图例管制哪些系列不显示。 private EchartsLegend legend; //直角坐标系 grid 中的 x 轴， private EchartsXYAxis xAxis; //直角坐标系 grid 中的 y 轴，[x轴和y轴 属性是一样的] private EchartsXYAxis yAxis; //默认色彩值 全局 private Object color =new String[]{"#c23531","#2f4554", "#61a0a8", "#d48265", "#91c7ae","#749f83", "#ca8622", "#bda29a","#6e7074", "#546570", "#c4ccd3"}; //全局的字体款式。 private EchartsTextStyle textStyle; //系列列表。每个系列通过 type 决定本人的图表类型 private List<EchartBaseSeries> series; //留神 //Charts 4 开始反对了 数据集（dataset）组件用于独自的数据集申明， // 从而数据能够独自治理，被多个组件复用，并且能够自在指定数据到视觉的映射。这在不少场景下能带来应用上的不便。 private EchartDataset dataset; //省略get set办法}2.1 以title 为例子 写相干 的java类EchartsTitle ...
/****************************************************************************** * *****************************************************************************/ import java.util.regex.Matcher; import java.util.regex.Pattern; /** * <ul> * <li>Title: -RegExUtil</li> * <li>Description: TODO </li> * <li>Copyright: Copyright (c) 2018</li> * <li>Company: http://www.jiangqiaotech.com/</li> * </ul> * <url> * <name>Java正则表达式的语法与示例</name> * <value>https://www.cnblogs.com/lzq19...</value> * </url> * * @author swc * @date 2019/2/13 0013 下午 15:23 */ public class RegExUtil { // [\w-] 就是匹配任意字母和符号- (减号) // \. = 就是匹配符号. (点) // $ 代表完结 匹配输出字符串的完结地位 //? 匹配后面的子表达式零次或一次。例如，“do(es)?”能够匹配“do”或“does”中的“do”。?等价于{0,1}。 //+ 匹配后面的子表达式一次或屡次(大于等于1次）。例如，“zo+”能匹配“zo”以及“zoo”，但不能匹配“z”。+等价于{1,}。 //像\\s 和\\w这个之类匹配"-" 把多个后果合在一起 要用[]包裹起来 前面要加上?/+/{}这样匹配 不然就是0匹配 ...
本文源码：GitHub·点这里 || GitEE·点这里 一、高性能索引1、查问性能问题在MySQL应用的过程中，所谓的性能问题，在大部分的场景下都是指查问的性能，导致查问迟缓的根本原因是数据量的一直变大，解决查问性能的最常见伎俩是：针对查问的业务场景，设计正当的索引构造。 2、索引应用准则索引的应用并不是越多越好，而是针对业务下的查问场景，一直的改良和优化，例如电商零碎中用户订单的场景，假如存在如下表构造： CREATE TABLE `ds_user` ( `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键id', `user_name` varchar(20) DEFAULT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='用户表';CREATE TABLE `ds_order` ( `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键id', `user_id` int(11) NOT NULL COMMENT '用户ID', `order_no` varchar(60) NOT NULL COMMENT '订单号', `product_name` varchar(50) DEFAULT NULL COMMENT '产品名称', `number` int(11) DEFAULT '1' COMMENT '个数', `unit_price` decimal(10,2) DEFAULT '0.00' COMMENT '单价', `total_price` decimal(10,2) DEFAULT '0.00' COMMENT '总价', `order_state` int(2) DEFAULT '1' COMMENT '1待领取，2已领取,3已发货,4已签收', `order_remark` varchar(50) DEFAULT NULL COMMENT '订单备注', `create_time` datetime DEFAULT NULL COMMENT '创立工夫', PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='订单表';用户和订单治理表，在电商的业务中很常见，能够通过对该业务剖析，看看罕用的索引构造： ...
本文源码：GitHub·点这里 || GitEE·点这里 一、高性能索引1、查问性能问题在MySQL应用的过程中，所谓的性能问题，在大部分的场景下都是指查问的性能，导致查问迟缓的根本原因是数据量的一直变大，解决查问性能的最常见伎俩是：针对查问的业务场景，设计正当的索引构造。 2、索引应用准则索引的应用并不是越多越好，而是针对业务下的查问场景，一直的改良和优化，例如电商零碎中用户订单的场景，假如存在如下表构造： CREATE TABLE `ds_user` ( `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键id', `user_name` varchar(20) DEFAULT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='用户表';CREATE TABLE `ds_order` ( `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键id', `user_id` int(11) NOT NULL COMMENT '用户ID', `order_no` varchar(60) NOT NULL COMMENT '订单号', `product_name` varchar(50) DEFAULT NULL COMMENT '产品名称', `number` int(11) DEFAULT '1' COMMENT '个数', `unit_price` decimal(10,2) DEFAULT '0.00' COMMENT '单价', `total_price` decimal(10,2) DEFAULT '0.00' COMMENT '总价', `order_state` int(2) DEFAULT '1' COMMENT '1待领取，2已领取,3已发货,4已签收', `order_remark` varchar(50) DEFAULT NULL COMMENT '订单备注', `create_time` datetime DEFAULT NULL COMMENT '创立工夫', PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='订单表';用户和订单治理表，在电商的业务中很常见，能够通过对该业务剖析，看看罕用的索引构造： ...
前言提到数据库索引，大家必定很相熟，在日常工作中常常会接触到。这几天看了不少相干文章、书籍和课程。决定本人总结一篇文章，尽管我写的这篇文章必定不如网上各路大神的好文，然而本人总结一遍总归记得更牢固 ????。这应该也是一种好的学习习惯，他人写的字再丑陋都是他人的，本人写的字就算再潦草起码本人也能意识吧 ????。 索引是一种进步咱们查问效率的数据结构。就如同是字典的目录，一本几百页的字典，如果想疾速查问到某个字，总不能靠硬翻吧 ????。 索引构造论断MySQL 索引个别是哈希表或 B+ 树，罕用的 InnoDB 引擎默认应用的是 B+ 树来作为索引的数据结构。 为什么不必哈希表？如果应用 B+ 树作为索引数据结构，那么拜访或批改一条数据的工夫复杂度是 O(log n)，然而应用哈希表作为索引构造干这些活的时候，工夫复杂度 O(1)。如果只是查一条数据或者批改一条数据，用哈希表做索引必定给力呀！然而个别业务零碎不会这么简略。 在业务开发中，常常会遇到范畴查问、排序查问等需要。这个时候哈希表索引就没方法高效的解决这些需要了。它只能通过扫表来实现这些性能，扫表应该是数据库的噩梦吧 ????。 MySQL 应用 B+ 树数据结构非叶子节点只贮存键值，叶子节点会贮存数据或者是主键。并且在叶子节点中键是依照顺序存储的，使得范畴查问、排序查问等变得异样简略。 尽管哈希表索引在操作单列数据的时候非常高效，然而须要范畴查问、排序查问的时候，B+ 树数据结构显然更适合。在咱们业务开发中，不可能只操作一行数据。综合思考，还是 B+ 树更适宜作为索引的数据结构。 哈希表索引不反对范畴查问，不能利用索引来排序，不反对联结索引最左匹配准则，如果反复键值比拟多，还容易造成哈希碰撞导致效率进一步升高 ????。 为什么不必 B 树？B+ 树的非叶子节点上只贮存键值，而 B 树的非叶子节点上不仅贮存键值还贮存数据。在 MySQL 数据库中数据页的大小是固定的，Innodb 引擎数据页默认大小为 16 KB。B+ 树这种做法是为了让树的阶数更大，让树更矮胖。进行查问的时候，磁盘 IO 次数就会缩小，查问效率也会更快。 B+ 树的所有数据均贮存在叶子节点中，并且是按键值有序排列。然而 B 树的数据扩散在各个节点。进行范畴查问，排序查问的时候，B 树的效率必定不如 B+ 树。 B+ 树查找过程 磁盘块 1 中存储 17 和 35 数据项，还有 P1、P2、P3 指针，P1 示意数据项小于 17 的磁盘块，P2 示意数据项在 17 和 35 之间的数据项，P3 示意数据项大于 35 的数据项。非叶子节点不贮存数据，只贮存指引搜寻方向的数据项。 ...
一、MySQL优化概述 页面动态化，memcache是通过缩小对mysql操作来晋升访问速度。 然而一个网站总是要操作数据库，如何晋升对mysql的操作速度。 方针： 存储层：数据表"存储引擎"选取、字段类型选取、逆范式(三范式)设计层：索引、分区/分表、存储过程、sql语句的优化架构层：分布式部署(集群)(读写拆散)，须要减少硬件sql语句层：后果一样的状况下，要抉择效率高、速度快、节俭资源的sql语句执行二、存储引擎的抉择1、存储引擎介绍 相熟的存储引擎：Myisam、InnoDB、memory 1、什么是存储引擎数据表存储数据的一种格局。数据存储在不同的格局里边，改格局体现的个性也是不一样的。 例如： InnoDB存储引擎的个性有反对事务、反对行级锁。mysiam反对的个性有压缩机制等。 mysql中的数据是通过各种不同的技术(格局)存储在文件(或者内存)中的。技术和自身的个性就称为“存储引擎”。 2、存储引擎的了解现实生活中，楼房、平房就是具体存储人的存储引擎。楼房、平房有本人独特的技术个性。 例如楼房有楼梯、电梯、平房能够本人打井喝水等。 3、存储引擎所处的地位存储引擎，处于MySql服务器的最底层，间接存储数据，导致下层的操作，依赖于存储引擎的抉择。 客户端-》网络连接层-》业务逻辑层（编译，优化，执行SQL）-》存储引擎层 查看以后mysql反对的存储引擎列表：show engines 4、罕用存储引擎Myisam：表锁，全文索引Innodb：行(记录)锁，事务（回滚），外键Memory：内存存储引擎，速度快、数据容易失落2、innodb存储引擎 >=5.5 版本中默认的存储引擎，MySql举荐应用的存储引擎。提供事务，行级锁定，存储引擎。 事务平安型存储引擎，更加重视数据的完整性和安全性。 1、存储格局innodb存储引擎 每个数据表有独自的“构造文件” ——*.frm 数据，索引集中存储，存储于同一个表空间文件中——ibdata1。 ibdata1就是InnoDB表的共享存储空间，默认innodb所有表的数据都在一个ibdata1里。 例： -- 创立innodb表create table t1(id int,name varchar(32)) engine innodb charset utf8;.frm表构造文件。 innodb表空间文件：存储innodb的数据和索引。 默认，所有的 innodb表的数据和索引在同一个表空间文件中， 通过配置能够达到每个innodb的表对应一个表空间文件。 show variables like 'innodb_file_per_table%' 例： -- 开启该配置set global innodb_file_per_table=1; 创立一个innodbd的表进行测试应用。 create table t2(id int,name varchar(32)) engine innodb charset utf8;查看表对应的文件本人独立的“数据/索引”文件 ...
游标在后面的剖析中可知sql的检索操作返回的数据简直都是以整个汇合的模式，也就是说sql长于将多条查问记录集中到一起并返回，假使当初须要一行行地解决查问的后果，这对于sql语句来说的确是个难题，好在存在一种称为游标的技术能够解决这个问题，所谓的游标就就是能够将检索进去的数据汇合保留在内存中而后顺次取出每条数据进行解决，这样就解决了sql语句无奈进行行记录解决的难题，游标的读取图解如下： 原表： 批量（游标）取值： fetch 游标名称 into @变量1，@变量2，@变量3。。。//这里设置值跟查问语句的列一一对应。所以别写错地位了 mysql存储过程应用表名作为参数-----------实现动静游标 视图局部。 例如上述，我写的是利用游标，清空对应的表，但会呈现表“不存在”的状况。 存储过程语句: CREATE DEFINER=`root`@`%` PROCEDURE `sp_empty_table`() BEGIN declare flag int default 0;#定义标识变量用于判断是否退出循环 declare tmp varchar(40);#定义长期存储变量 declare cur cursor for select name from view_all_table where name not LIKE 'sys_%' ;#申明游标 declare continue handler for not found set flag = 1; #异样解决并设置flag=1 open cur; # 关上游标 while flag!=1 do fetch cur into tmp ;#从游标中取值并存放到tmp中 ...
触发器能够简略了解一种非凡的存储过程，之前存储过程的变量定义及流程语句同样适宜触发器，惟一不同的是咱们只须要定义触发器，而不必手动调用触发器。从事件触发的角度来说，触发器编写的过程就是触发事件定义的过程，因为触发器定义好后会随着数据库操作命令的执行而触发，这些具体的操作是INSERT/UPDATE/DELETE。比方能够在user表中删除记录执行后，通过定义一个触发器把删除的数据主动增加到历史表中保留以便当前能够进行其余操作。创立触发器的语法如下： CREATE TRIGGER trigger_name trigger_time trigger_event ON tbl_name FOR EACH ROW BEGIN trigger_stmt END 例如上面创立个触发器，当删除employee表一条数据就会执行一次 往history表外面插入一条记录的触发器，看到OLD这个了？这个就是原数据的对象，可取出原数据！ 本文来源于：宋文超super，专属平台有csdn、思否(SegmentFault)、 简书、 开源中国(oschina)，转载请注明出处。
1.数据库索引 ，知不知道数据库在查问的时候，数据类型会呈现隐式转化（如varchar不加单引号的话可能会主动转换为int型，索引会生效），那怎么防止隐式转换2.最左匹配准则说一下3.为什么索引底层用b+树，和红黑树的区别 ，和b树的区别4.笼罩索引晓得吗？mysql回表晓得吗？5.drop、truncate、 delete区别，他们那个最快，为什么
人不知;鬼不觉『MySQL技术』公众号曾经继续经营一年半了。想想本人能保持笔辍不耕写了一年多，也挺不容易的，为本人点个赞。尽管本号发文章并不算高产，但简直每周都会发原创技术文章，能保持下来离不开各位读者的反对。本篇文章咱们不聊技术，我想和你聊聊本人的感触和播种，文笔不好，还请见谅。不要走开，文末有彩蛋！ 一、首先介绍下本人吧，笔者姓王，能够叫我王同学。目前在杭州工作，已入职场三年，也不是啥大佬，和诸多读者一样是IT从业者。仔细的读者可能发现，本号是有「留言」性能的。哈哈，其实这个公众号是16年我在大学期间申请的，始终没更新过。大学毕业后，曾在CSDN等博客平台写过一些文章，直到18年底，忽然想起本人还有个祖传的公众号，何不经营公众号发表技术文章呢？这样会有一批稳固的读者群体，于是从那时起，本号就开始继续经营了。 一开始，本人也没多想，反正还有些存货文章，缓缓发呗。于是整顿了下原来写过的文章，把一些适宜发表的文章通过公众号发了进去。但不久后，我的存货就顾此失彼了，没方法，只能吭叽吭叽的开始写文章了。其实，纯技术类的公众号真的很难做，大部分的公众号都会写一些本人的生存感触和一些热点新闻，偶然发篇技术文章，但我写不来这些，那就只能写写技术文章啦。给本人定的指标是周更，即每周写一篇技术文章，这个指标看似简略，但保持下来也怪不容易的。有时候不晓得写啥、有时候也要思考读者感兴趣的内容、有时候忙的没工夫写文章，还要思考排版之类的。期间遭逢各种问题，庆幸本人保持了下来，也播种了一批读者，感触到了你们的反对。 二、这个公众号的定位是分享MySQL相干文章，写的技术文章中大部分都是MySQL相干的。MySQL数据库在互联网行业利用很宽泛，各类技术人员都会用到，目前高校也有开设数据库相干课程。在我的读者群体中，有各行各业的技术人员，也有一些仍在学校的学生，无论你从事哪种职业，置信各位读者关注此号的起因都是学习MySQL。可能每个人学习MySQL的目标都不一样，比如说经营人员可能是为了学习写SQL、开发测试人员可能是为了在工作中用好MySQL、运维人员则更关注数据库性能及稳定性等。不论你学习MySQL的目前是啥，心愿都能够在这里有所播种。 当前写文章，尽量从多个角度来写，多多关照到不同的读者群体。如果你有文章举荐或者投稿也能够分割我哈。写一篇文章容易，但保持每周写文章的确须要毅力，如果今后我保持不下来了，或者因为太忙断更了几周，还心愿大家体谅，不要取关哦。 三、最近两个月的确有发过一些恰饭广告文，在这里给大家说声道歉。有金主找，阐明这个号还是有价值的，也是承蒙各位读者反对，我能力接到一些广告。通过写文章失去支出，我才更有能源写下去。在这里向大家保障，我所接的推广都是技术相干，大多是一些收费的体验课或材料，对于推广文章，心愿大家能多多容纳????，帮忙点下浏览，对你有用的话棘手学习下就行。在能接到推广的状况下，一个月不会超过3篇推广文章，还是心愿本人能多发些技术文章。有了推广支出后，我会不定时送大家一些小福利，搞搞抽奖回馈大家。 能看到这里的读者都是真爱，哈哈。说了这么多，总之就是理性各位读者，有了你们的反对，我能力继续更新上来。我始终置信一句话：勤能补拙，只有坚持下去，生存会在不经意间给你惊喜。这句话同样送给各位，下方贴出了公众号二维码，有趣味和我聊聊或加群交换的同学能够关注我，当前我会持续和各位读者多交换，读完这篇文章，你有什么想说的，欢送留言哈。
前言又和大家见面了！又两周过来了，我的云笔记里又多了几篇写了一半的文章草稿。有的是因为品质没有达到预期还筹备再加点内容，有的则齐全是一个灵感而已，内容齐全木有。艳羡很多大佬们，一周能产出五六篇文章，给我两个肝我都不够。好了，不多说废话了... 最近在线上环境遇到了一次SQL慢查问引发的数据库故障，影响线上业务。通过排查后，确定起因是SQL在执行时，MySQL优化器抉择了谬误的索引（不应该说是“谬误”，而是抉择了理论执行耗时更长的索引）。在排查过程中，查阅了许多材料，也学习了下MySQL优化器抉择索引的基本准则，在本文中进行解决问题思路的分享。自己MySQL理解深度无限，如果谬误欢送感性探讨和斧正。 在这次事变中也能充沛看出深刻理解MySQL运行原理的重要性，这是遇到问题时是否独立解决问题的要害。 试想一个月黑风高的夜晚，公司线上忽然挂了，而你的共事们都不在线，就你一个人有条件解决问题，这时候如果被工程师的基本功把你卡住了，就问你尴不难堪... 本文的次要内容： 故障形容问题起因排查MySQL索引抉择原理解决方案思考与总结请大家多多反对我的原创技术公众号：后端技术漫谈注释故障形容在7月24日11点线上某数据库忽然收到大量告警，慢查问数超标，并且引发了连接数暴增，导致数据库响应迟缓，影响业务。看图表慢查问在顶峰达到了每分钟14w次，在平时失常状况下慢查问数仅在两位数以下，如下图： 连忙查看慢SQL记录，发现都是同一类语句导致的慢查问(隐衷数据例如表名，我曾经隐去)： select *from sample_tablewhere 1 = 1 and (city_id = 565) and (type = 13)order by id desclimit 0, 1看起来语句很简略，没什么特地的。然而每个执行的查问工夫达到了惊人的44s。 几乎骇人听闻，这曾经不是“慢”能形容的了... 接下来查看表数据信息，如下图： 能够看到表数据量较大，预估行数在83683240，也就是8000w左右，千万数据量的表。 大抵状况就是这样，上面进入排查问题的环节。 问题起因排查首先当然要狐疑会不会该语句没走索引，查看建表DML中的索引： KEY `idx_1` (`city_id`,`type`,`rank`),KEY `idx_log_dt_city_id_rank` (`log_dt`,`city_id`,`rank`),KEY `idx_city_id_type` (`city_id`,`type`)请疏忽idx_1和idx_city_id_type两个索引的反复，这都是历史遗留问题了。 能够看到是有idx_city_id_type和idx_1索引的，咱们的查问条件是city_id和type，这两个索引都是能走到的。 然而，咱们的查问条件真的只有思考city_id和type吗？（机智的小伙伴应该留神到问题所在了，先往下讲，留给大家思考） 既然有索引，接下来就该看该语句理论有没有走到索引了，MySQL提供了Explain能够剖析SQL语句。Explain 用来剖析 SELECT 查问语句。 Explain比拟重要的字段有： select_type : 查问类型，有简略查问、联结查问、子查问等key : 应用的索引rows : 预计须要扫描的行数更多具体Explain介绍能够参考：MySQL 性能优化神器 Explain 应用剖析 咱们应用Explain剖析该语句： select * from sample_table where city_id = 565 and type = 13 order by id desc limit 0,1失去后果： ...
前言又和大家见面了！又两周过来了，我的云笔记里又多了几篇写了一半的文章草稿。有的是因为品质没有达到预期还筹备再加点内容，有的则齐全是一个灵感而已，内容齐全木有。艳羡很多大佬们，一周能产出五六篇文章，给我两个肝我都不够。好了，不多说废话了... 最近在线上环境遇到了一次SQL慢查问引发的数据库故障，影响线上业务。通过排查后，确定起因是SQL在执行时，MySQL优化器抉择了谬误的索引（不应该说是“谬误”，而是抉择了理论执行耗时更长的索引）。在排查过程中，查阅了许多材料，也学习了下MySQL优化器抉择索引的基本准则，在本文中进行解决问题思路的分享。自己MySQL理解深度无限，如果谬误欢送感性探讨和斧正。 在这次事变中也能充沛看出深刻理解MySQL运行原理的重要性，这是遇到问题时是否独立解决问题的要害。 试想一个月黑风高的夜晚，公司线上忽然挂了，而你的共事们都不在线，就你一个人有条件解决问题，这时候如果被工程师的基本功把你卡住了，就问你尴不难堪... 本文的次要内容： 故障形容问题起因排查MySQL索引抉择原理解决方案思考与总结请大家多多反对我的原创技术公众号：后端技术漫谈注释故障形容在7月24日11点线上某数据库忽然收到大量告警，慢查问数超标，并且引发了连接数暴增，导致数据库响应迟缓，影响业务。看图表慢查问在顶峰达到了每分钟14w次，在平时失常状况下慢查问数仅在两位数以下，如下图： 连忙查看慢SQL记录，发现都是同一类语句导致的慢查问(隐衷数据例如表名，我曾经隐去)： select *from sample_tablewhere 1 = 1 and (city_id = 565) and (type = 13)order by id desclimit 0, 1看起来语句很简略，没什么特地的。然而每个执行的查问工夫达到了惊人的44s。 几乎骇人听闻，这曾经不是“慢”能形容的了... 接下来查看表数据信息，如下图： 能够看到表数据量较大，预估行数在83683240，也就是8000w左右，千万数据量的表。 大抵状况就是这样，上面进入排查问题的环节。 问题起因排查首先当然要狐疑会不会该语句没走索引，查看建表DML中的索引： KEY `idx_1` (`city_id`,`type`,`rank`),KEY `idx_log_dt_city_id_rank` (`log_dt`,`city_id`,`rank`),KEY `idx_city_id_type` (`city_id`,`type`)请疏忽idx_1和idx_city_id_type两个索引的反复，这都是历史遗留问题了。 能够看到是有idx_city_id_type和idx_1索引的，咱们的查问条件是city_id和type，这两个索引都是能走到的。 然而，咱们的查问条件真的只有思考city_id和type吗？（机智的小伙伴应该留神到问题所在了，先往下讲，留给大家思考） 既然有索引，接下来就该看该语句理论有没有走到索引了，MySQL提供了Explain能够剖析SQL语句。Explain 用来剖析 SELECT 查问语句。 Explain比拟重要的字段有： select_type : 查问类型，有简略查问、联结查问、子查问等key : 应用的索引rows : 预计须要扫描的行数更多具体Explain介绍能够参考：MySQL 性能优化神器 Explain 应用剖析 咱们应用Explain剖析该语句： select * from sample_table where city_id = 565 and type = 13 order by id desc limit 0,1失去后果： ...
装置环境:windows10 专业版在卡法的过程中,咱们大部分会应用到数据库,本次介绍mysql数据库的装置教程!从官网下载社区版服务器压缩包https://dev.mysql.com/downloads/mysql/在解压出的目录下新建my.ini文件,配置如下: [mysqld]# 设置3306端口port=3306# 设置mysql的装置目录basedir=G:\mysql-8.0.21-winx64# 设置mysql数据库的数据的寄存目录datadir=G:\mysql-8.0.21-winx64\data# 容许最大连接数max_connections=200# 容许连贯失败的次数。这是为了避免有人从该主机试图攻打数据库系统max_connect_errors=10# 服务端应用的字符集默认为UTF8character-set-server=utf8# 创立新表时将应用的默认存储引擎default-storage-engine=INNODB# 默认应用“mysql_native_password”插件认证default_authentication_plugin=mysql_native_password[mysql]# 设置mysql客户端默认字符集default-character-set=utf8[client]# 设置mysql客户端连贯服务端时默认应用的端口port=3306default-character-set=utf8执行 mysqld --initialize-insecure 指令进行配置，装置门路会默认生成一个data文件夹 如果你先操作的过程中呈现了以下谬误:解决办法:是装置上面图片的软件 //你就会发现这个谬误曾经解决了! 再而后输出mysqld --install装置mysql服务启动服务 net start mysql可能你会发现,mysql 服务无奈启动,此时,谬误如下: 遇到这个问题,解决办法如下: 而后在执行:mysqld --initialize-insecuremysqld --installnet start mysql//此时,你会发现mysql 曾经启动了! mysql -uroot -p;// 提醒输出明码,间接敲回车就能够了。 show databases; 能够看到默认的数据库user mysql;ALTER USER 'root'@'localhost' IDENTIFIED BY 'chen'; //批改明码 ,此时我把明码批改成了 chen ,大家能够依据需要进行批改!//到了这里本期的解说就完结啦,是不是很简略!很棒!让咱们一起提高,走向巅峰!
本标准旨在帮忙或领导RD、QA、OP等技术人员做出适宜线上业务的数据库设计。在数据库变更和解决流程、数据库表设计、SQL编写等方面予以标准，从而为公司业务零碎稳固、衰弱地运行提供保障设计规范以下所有标准会依照【高危】、【强制】、【倡议】三个级别进行标注，恪守优先级从高到低对于不满足【高危】和【强制】两个级别的设计，DBA有权力强制打回要求批改 库名1.【强制】库的名称必须管制在32个字符以内，相干模块的表名与表名之间尽量体现join的关系，如user表和user_login表 2.【强制】库的名称格局：业务零碎名称_子系统名，同一模块应用的库名尽量应用对立前缀 3.【强制】个别分库名称命名格局是库通配名_编号，编号从0开始递增，比方wenda_001以工夫进行分库的名称格局是“库通配名_工夫” 4.【强制】创立数据库时必须显式指定字符集，并且字符集只能是utf8或者utf8mb4。创立数据库SQL举例：create database db1 default character set utf8; 表构造1.【强制】表必须有主键，且设置id为自增主键 2.【强制】表禁止应用外键，如果要保障残缺下，应由程序端实现，外键使表之间互相耦合，影响update、delete等性能，有可能造成死锁，高并发环境下容易导致数据库性能瓶颈 3.【强制】表和列的名称必须管制在32个字符以内，表名只能应用字母、数字和下划线，一律小写。如表名过长能够采纳缩写等形式 4.【强制】创立表时必须显式指定字符集为utf8或utf8mb4 5.【强制】创立表时必须显式指定表存储引擎类型，如无非凡需要，一律为InnoDB。当须要应用除InnoDB/MyISAM/Memory以外的存储引擎时，必须通过DBA审核能力在生产环境中应用。因为Innodb表反对事务、行锁、宕机复原、MVCC等关系型数据库重要个性，为业界应用最多的MySQL存储引擎。而这是其余大多数存储引擎不具备的，因而首推InnoDB 6.【强制】建表必须有comment，表级别和字段级别都要有comment 7.【倡议】建表时对于主键：(1)强制要求主键为id，类型为int或bigint（为了当前延展性，这里要求新建表对立为bigint），且为auto_increment(2)标识表里每一行主体的字段不要设为主键，倡议设为其余字段如user_id，order_id等，并建设unique key索引。因为如果设为主键且主键值为随机插入，则会导致innodb外部page决裂和大量随机I/O，性能降落 8.【倡议】外围表（如用户表，金钱相干的表）必须有行数据的创立工夫字段create_time和最初更新工夫字段update_time，便于查问题 9.【倡议】表中所有字段必须都是NOT NULL default 默认值 属性，业务能够依据须要定义DEFAULT值。因为应用NULL值会存在每一行都会占用额定存储空间、数据迁徙容易出错、聚合函数计算结果偏差以及索引生效等问题 10.【倡议】倡议对表里的blob、text等大字段，垂直拆分到其余表里，仅在须要读这些对象的时候才去select 11.【倡议】反范式设计：把常常须要join查问的字段，在其余表里冗余一份。如user_name属性在user_account，user_login_log等表里冗余一份，缩小join查问 12.【强制】两头表用于保留两头后果集，名称必须以tmp_结尾。备份表用于备份或抓取源表快照，名称必须以bak_结尾。两头表和备份表定期清理 13.【强制】对于线上执行DDL变更，必须通过DBA审核，并由DBA在业务低峰期执行 列数据类型优化1.【倡议】表中的自增列（auto_increment属性），举荐应用bigint类型。因为无符号int存储范畴为-2147483648~2147483647（大概21亿左右），溢出后会导致报错 2.【倡议】业务中选择性很少的状态status、类型type等字段举荐应用tinytint或者smallint类型节俭存储空 3.【倡议】业务中IP地址字段举荐应用int类型，不举荐用char(15)。因为int只占4字节，能够用如下函数互相转换，而char(15)占用至多15字节。一旦表数据行数到了1亿，那么要多用1.1G存储空间。 SQL：select inet_aton('192.168.2.12'); select inet_ntoa(3232236044); PHP: ip2long(‘192.168.2.12’); long2ip(3530427185); 4.【倡议】不举荐应用enum，set。 因为它们节约空间，且枚举值写死了，变更不不便。举荐应用tinyint或smallint 5.【倡议】不举荐应用blob，text等类型。它们都比拟节约硬盘和内存空间。在加载表数据时，会读取大字段到内存里从而节约内存空间，影响零碎性能。倡议和PM、RD沟通，是否真的须要这么大字段 6.【倡议】存储金钱的字段，倡议用int，程序端乘以100和除以100进行存取。或者用decimal类型，而不要用double 7.【倡议】文本数据尽量用varchar存储。因为varchar是变长存储，比char更省空间。MySQL server层规定一行所有文本最多存65535字节 8.【倡议】工夫类型尽量选取datetime。而timestamp尽管占用空间少，然而有工夫范畴为1970-01-01 00:00:01到2038-01-01 00:00:00的问题 索引设计1.【强制】InnoDB表必须主键为id int/bigint auto_increment,且主键值禁止被更新 2.【倡议】惟一键以“uk_”或“uq_”结尾，一般索引以“idx_”结尾，一律应用小写格局，以字段的名称或缩写作为后缀 3.【强制】InnoDB和MyISAM存储引擎表，索引类型必须为BTREE；MEMORY表能够依据须要抉择HASH或者BTREE类型索引 4.【强制】单个索引中每个索引记录的长度不能超过64KB 5.【倡议】单个表上的索引个数不能超过5个 6.【倡议】在建设索引时，多思考建设联结索引，并把区分度最高的字段放在最后面。如列userid的区分度可由select count(distinct userid)计算出来 7.【倡议】在多表join的SQL里，保障被驱动表的连贯列上有索引，这样join执行效率最高 8.【倡议】建表或加索引时，保障表里相互不存在冗余索引。对于MySQL来说，如果表里曾经存在key(a,b)，则key(a)为冗余索引，须要删除 分库分表、分区表1.【强制】分区表的分区字段（partition-key）必须有索引，或者是组合索引的首列 2.【强制】单个分区表中的分区（包含子分区）个数不能超过1024 3.【强制】上线前RD或者DBA必须指定分区表的创立、清理策略 4.【强制】拜访分区表的SQL必须蕴含分区键 5.【倡议】单个分区文件不超过2G，总大小不超过50G。倡议总分区数不超过20个 6.【强制】对于分区表执行alter table操作，必须在业务低峰期执行 ...
之前搭建过一套主从同步的mysql集群，然而是基于新数据库，而这次线上环境要升级成主从同步的集群，记录一下降级过程和两头遇到的各种问题。 因为是间接对线上数据库进行批改，因而要保障对线上环境造成尽量小的影响。所以把之前的数据库当作主数据库，这样相应的服务不必批改配置文件。要做的就是装置一个从数据库并且实时同步主数据库的改变。 如果两个库在同一个服务器的话则装置mysql时须要扭转其端口。 如果在另一个服务器装置的话留神查看并卸载旧的mysql。 首先装置从库：装置mysql下载软件包： wget https://repo.mysql.com//mysql80-community-release-el7-1.noarch.rpm 本地装置： yum localinstall mysql80-community-release-el7-1.noarch.rpm 装置mysql： yum install mysql-community-server 设为开机启动： systemctl enable mysqld systemctl daemon-reload 启动mysql： systemctl start mysqld 以上步骤就装置好mysql8了。 获取mysql的长期明码： grep 'temporary password' /var/log/mysqld.log 登录mysql： mysql -uroot -p 会提醒输出明码，输出之前获取的长期明码即可登录。 此时须要批改mysql的明码，要不然之后的步骤也会强制提醒你须要批改明码： ALTER USER 'root'@'localhost' IDENTIFIED BY '121b33dAj934J1^Sj9aa'; mysql8默认对明码的强度有要求，须要设置简单一点，要不然也会提醒谬误。 刷新配置： FLUSH PRIVILEGES; 对于主服务器的相干配置设置server-id值并开启binlog参数 依据mysql的同步原理：关键因素就是binlog日志。 编辑/etc/my.cnf配置文件，批改和增加相干参数。 vi /etc/my.cnf [mysqld] # 同一局域网内留神要惟一 server-id = 1 log-bin = mysql-bin relay_log配置中继日志 relay_log=edu-mysql-relay-bin 批改配置后须要重启能力失效： service mysqld restart ...
之前搭建过一套主从同步的mysql集群，然而是基于新数据库，而这次线上环境要升级成主从同步的集群，记录一下降级过程和两头遇到的各种问题。 因为是间接对线上数据库进行批改，因而要保障对线上环境造成尽量小的影响。所以把之前的数据库当作主数据库，这样相应的服务不必批改配置文件。要做的就是装置一个从数据库并且实时同步主数据库的改变。 如果两个库在同一个服务器的话则装置mysql时须要扭转其端口。 如果在另一个服务器装置的话留神查看并卸载旧的mysql。 首先装置从库：装置mysql下载软件包： wget https://repo.mysql.com//mysql80-community-release-el7-1.noarch.rpm 本地装置： yum localinstall mysql80-community-release-el7-1.noarch.rpm 装置mysql： yum install mysql-community-server 设为开机启动： systemctl enable mysqld systemctl daemon-reload 启动mysql： systemctl start mysqld 以上步骤就装置好mysql8了。 获取mysql的长期明码： grep 'temporary password' /var/log/mysqld.log 登录mysql： mysql -uroot -p 会提醒输出明码，输出之前获取的长期明码即可登录。 此时须要批改mysql的明码，要不然之后的步骤也会强制提醒你须要批改明码： ALTER USER 'root'@'localhost' IDENTIFIED BY '121b33dAj934J1^Sj9aa'; mysql8默认对明码的强度有要求，须要设置简单一点，要不然也会提醒谬误。 刷新配置： FLUSH PRIVILEGES; 对于主服务器的相干配置设置server-id值并开启binlog参数 依据mysql的同步原理：关键因素就是binlog日志。 编辑/etc/my.cnf配置文件，批改和增加相干参数。 vi /etc/my.cnf [mysqld] # 同一局域网内留神要惟一 server-id = 1 log-bin = mysql-bin relay_log配置中继日志 relay_log=edu-mysql-relay-bin 批改配置后须要重启能力失效： service mysqld restart ...
背景由云上的一个服务返回异样触发的，因为最近服务代码未有改变，之前运行失常，所以首先到服务所在的服务器查看服务的状态： [root@manager-01 ~]# systemctl status <service-name>Jul 27 17:51:09 manager-01 node[14705]: { Error: ENOSPC: no space left on device, write errno: -28, code: 'ENOSPC', syscall: 'write' }Jul 27 17:51:11 manager-01 node[14705]: { Error: ENOSPC: no space left on device, write errno: -28, code: 'ENOSPC', syscall: 'write' }Jul 27 17:51:21 manager-01 node[14705]: { Error: ENOSPC: no space left on device, write errno: -28, code: 'ENOSPC', syscall: 'write' }能够发现报错信息很明确：空间有余，无奈写入备注：OS版本是：CentOS 7 ...
① 环境筹备本地装置两个mysql，或者应用虚拟机，或者应用docker装置，须要筹备两个mysql，本文应用docker装置，具体装置办法详见 https://www.jianshu.com/p/10769f985516 环境 宿主机 centos7 mysql:5.6 mysql1(master): 172.17.0.3:3307 mysql2(slave): 172.17.0.2:3308② mysql 配置文件配置mysql1(master): 172.17.0.3 my.cnf 配置文件设置，mysql #mysql master1 config [mysqld]server-id = 1 # 节点ID，确保惟一# log configlog-bin = mysql-bin #开启mysql的binlog日志性能sync_binlog = 1 #管制数据库的binlog刷到磁盘下来 , 0 不管制，性能最好，1每次事物提交都会刷到日志文件中，性能最差，最平安binlog_format = mixed #binlog日志格局，mysql默认采纳statement，倡议应用mixedexpire_logs_days = 7 #binlog过期清理工夫max_binlog_size = 100m #binlog每个日志文件大小binlog_cache_size = 4m #binlog缓存大小max_binlog_cache_size= 512m #最大binlog缓存大binlog-ignore-db=mysql #不生成日志文件的数据库，多个疏忽数据库能够用逗号拼接，或者 复制这句话，写多行auto-increment-offset = 1 # 自增值的偏移量auto-increment-increment = 1 # 自增值的自增量slave-skip-errors = all #跳过从库谬误mysql2(slave): 172.17.0.2 mysql.cnf 配置 [mysqld]server-id = 2log-bin=mysql-binrelay-log = mysql-relay-binreplicate-wild-ignore-table=mysql.%replicate-wild-ignore-table=test.%replicate-wild-ignore-table=information_schema.%重启两个mysql，让配置失效 ...
之前，咱们列举不少mysql自带的函数，然而有些时候自带函数并不能很好满足咱们的需要，此时就须要自定义存储函数了，存储函数与存储过程有些相似，简略来说就是封装一段sql代码，实现一种特定的性能，并返回后果。其语法如下： CREATE FUNCTION 函数([参数类型 数据类型[,….]]) RETURNS 返回类型 BEGIN SQL语句..... RETURN (返回的数据) END 与存储过程不同的是，存储函数中不能指定输入参数(OUT)和输入输出参数(INOUT)类型。存储函数只能指定输出类型而且不能带IN。同时存储函数能够通过RETURN命令将解决的后果返回给调用方。留神必须在参数列表后的RETURNS（ 该值的RETURNS多个S，务必注意）命令中预先指定返回值的类型。如下创立一个计算斐波那契数列的函数 这里命名存储函数时应用了【fn_】作为结尾，这样能够更容易辨别与【sp_】结尾的存储过程，从上述语句能够看出后面在存储过程剖析的流程语句也是能够用于存储函数的，同样的，DECLARE申明变量和SET设置变量也可用于存储函数，当然包含定义异样解决语句也是适应的,请留神执行存储函数应用的是select关键字，可同时执行多个存储函数，嗯，存储函数就这样定义，是不是跟存储过程很类似呢？但还是有区别的，这点留到前面剖析。ok~，为了进一步相熟存储函数，上面编写一个用于向user插入用户的存储函数： create function fn_get_bom_child(pid VARCHAR(10)) returns varchar(4000) BEGIN DECLARE temp VARCHAR(10000); DECLARE tempChild VARCHAR(4000); SET temp = ''; SET tempChild = pid; WHILE tempChild IS NOT NULL DO SET temp = CONCAT(temp,',',tempChild); SELECT GROUP_CONCAT(comp_material_id) into tempChild from t_bom_info where FIND_IN_SET(parent_material_id,tempChild); END WHILE; RETURN temp; ...
一、存在问题通过sql慢查问的优化，咱们零碎中发现了以下几种类型的问题: 1.未建索引：整张表没有建索引；2.索引未命中：有索引,然而局部查问条件下索引未命中；3.搜寻了额定的非必要字段，导致回表；4.排序，聚合导致慢查问；5.雷同内容屡次查询数据库；6.未消限度搜寻范畴或者限度的搜寻范畴在预期之外，导致全副扫描；二、解决方案1.优化索引，减少或者批改以后的索引； 2.重写sql；3.利用redis缓存，缩小查问次数；4.减少条件，防止非必要查问；5.减少条件，缩小查问范畴； 三、案例剖析（一）药材搜寻接口残缺sql语句在附录，为不便浏览和脱敏，局部常用字段采纳中文。 这儿次要讲一下咱们拿到Sql语句后的整个剖析过程，思考逻辑，而后进行调整的过程和最初解决的方法。 给大家提供一些借鉴，也心愿大家可能提出更好的倡议。 这个sql语句要求是依据医生搜寻的拼音或者中文，进行含糊查问，找到药材，而后依据医生抉择的药库，查找上面的供应商，而后依据供应商，进行药材匹配，排除掉供应商没有的药材，而后依据真名在前，别名在后，齐全匹配在前，局部匹配在后，附加医生最近半年的应用习惯，把药材排序进去。最初把不同名称的同一味药聚合起来，以真名(另名)的模式展示。 1．剖析sql（1）14-8第14排,id为8的explain后果剖析: ①Explain8,DERIVED,ssof,range,"ix_district,ix_供应商id",ix_district,8,NULL,18,Using where; Using index; Using temporary②SqlSELECT DISTINCT (ssof.供应商id) AS 供应商id FROM 药库供应商关系表 AS ssof WHERE ssof.药库id IN ( 1, 2, 8, 9, 10, 11, 12, 13, 14, 15, 17, 22, 24, 25, 26, 27, 31, 33) AND ssof.药方剂型id IN (1)③索引PRIMARY KEY (`id`), UNIQUE KEY `ix_district` ( `药库id`, `药方剂型id`, `供应商id` ) USING BTREE,KEY `ix_供应商id` (`供应商id`) USING BTREE④剖析应用了索引，建设了长期表，这个中央索引曾经齐全笼罩了，然而还有回表操作。 起因是用in，这个导致了回表。如果in能够被mysql 主动优化为等于，就不会回表。如果无奈优化，就回表。 长期表是因为有distinct，所以无奈防止。 同时应用in须要留神，如果外面的值数量比拟多，有几万个。即便区分度高，就会导致索引生效，这种状况须要屡次分批查问。 2． 12-7 （1）Explain7,DERIVED,<derived8>,ALL,NULL,NULL,NULL,NULL,18,Using temporary; Using filesort（2）SqlINNER JOIN (下面14-8长期表) tp ON tp.供应商id= ms.供应商id（3）索引无 （4）剖析对长期表操作，无索引，用了文件排序。 这一部分是对长期表和药材表进行关联操作的一部分，有文件排序是因为须要对药材表id进行group by 导致的。 1、默认状况下，mysql在应用group by之后，会产生长期表，而后进行排序（此处排序默认是快排），这会耗费的性能。 2、group by实质是先分组后排序【而不是先排序后分组】。 3、group by column 默认会依照column分组, 而后依据column升序排列; group by column order by null 则默认依照column分组,而后依据标的主键ID升序排列。 3． 13-7 （1）Explain7,DERIVED,ms,ref,"ix_title,idx_audit,idx_mutiy",idx_mutiy,5,"tp.供应商id,const",172,NULL（2）SqlSELECT ms.药材表id, max(ms.audit) AS audit, max(ms.price) AS price, max(ms.market_price) AS market_price,max(ms.is_granule) AS is_granule,max(ms.is_decoct) AS is_decoct, max(ms.is_slice) AS is_slice,max(ms.is_cream) AS is_cream, max(ms.is_extract) AS is_extract,max(ms.is_cream_granule) AS is_cream_granule, max(ms.is_extract_granule) AS is_extract_granule,max(ms.is_drychip) AS is_drychip, max(ms.is_pill) AS is_pill,max(ms.is_powder) AS is_powder, max(ms.is_bolus) AS is_bolus FROM 供应商药材表 AS ms INNER JOIN ( SELECT DISTINCT (ssof.供应商id) AS 供应商id FROM 药库供应商关系表 AS ssof WHERE ssof.药库id IN ( 1, 2, 8, 9, 10, 11, 12, 13, 14, 15, 17, 22, 24, 25, 26, 27, 31, 33 ) AND ssof.药方剂型id IN (1) ) tp ON tp.供应商id= ms.供应商id WHERE ms.audit = 1 GROUP BY ms.药材表id（3）索引 KEY `idx_mutiy` (`供应商id`, `audit`, `药材表id`)（4）剖析命中了索引，表间连接应用了供应商id，建设索引的程序是供应商id，where条件中audit，Group by 条件药材表id。 ...
前言： 在MySQL中，主从架构应该是最根底、最罕用的一种架构了。后续的读写拆散、多活高可用架构等大多都依赖于主从复制。主从复制也是咱们学习MySQL过程中必不可少的一部分，对于主从复制的文章有很多，笔者也来凑凑热闹，写写这方面的内容吧，同时分享下本人的教训和办法。 1.主从复制简介及原理主从复制（也称 AB 复制）是指一台服务器充当主数据库服务器，另一台或多台服务器充当从数据库服务器，主服务器中的数据主动复制到从服务器之中。对于多级复制，数据库服务器既可充当主机，也可充当从机。MySQL默认采纳异步复制形式。 主从复制的过程及原理能够总结如下： master服务器将数据的扭转记录二进制binlog日志，当master上的数据产生扭转时，则将其扭转写入二进制日志中。slave服务器会在肯定工夫距离内对master二进制日志进行探测其是否产生扭转，如果产生扭转，则开始一个I/OThread申请master二进制事件。同时主节点为每个I/O线程启动一个dump线程，用于向其发送二进制事件，并保留至从节点本地的中继日志中，从节点将启动SQL线程从中继日志中读取二进制日志，在本地重放，使得其数据和主节点的保持一致。 2.基于二进制文件地位配置主从复制基于二进制文件地位的主从复制又能够称为传统复制，即从服务器依赖于主服务器的binlog文件地位，当主库产生数据变更时，binlog pos位点会增长，从库会感应到变动来实现同步。 配置主从复制，咱们首先要筹备至多两台MySQL实例，一台充当主服务器、一台充当从服务器。因为主从复制依赖于binlog，所以主库必须开启binlog，且主从要配置不同的server_id，上面具体展现下配置过程： 2.1 确认主从库配置参数 MySQL主从服务器倡议有如下配置，能够先确认下，如果未配置，则须要批改配置文件而后重启。 # 主库参数配置 要有以下参数vim /etc/my.cnf [mysqld] log-bin = binlog //启用二进制日志server-id = 137 //服务器惟一ID，默认值是1，个别设置为IP地址的最初一段数字binlog_format = row //bilog设置为row模式 避免复制出错# 从库倡议配置以下参数vim /etc/my.cnf [mysqld] relay-log = relay-binserver-id = 1382.2 确定主库二进制地位，创立同步账号 若主从库都是刚刚初始化实现，且主库无操作时，从库可不必同步主库的数据，间接确定主库的binlog地位即可。 # 查看主库binlog文件地位show master status;# 主库创立同步账号create user 'repl'@'%' identified by '123456';grant replication slave on *.* to 'repl'@'%';若主库曾经运行了一段时间，有业务数据在，而从库刚刚初始化实现，此时则须要备份主库的数据，而后导入从库，使得主从数据统一。 # 主库创立同步账号create user 'repl'@'%' identified by '123456';grant replication slave on *.* to 'repl'@'%';# 全备主库数据mysqldump -uroot -pxxxx -A -R -E --single-transaction --master-data=2 > all_db.sql# 从库端复原mysql -uroot -pxxxx < all_db.sql# 从备份文件中能够找到主库的binlog地位2.3 进入从库，开启主从复制 ...
前言本篇文章记录我应用 docker-compose 以及 dockerfile 来构建基于 binlog 的 MySQL 主从环境。如果你严格依照文中的步骤进行配置，置信很快就能够搭建好一个根底的 MySQL 主从环境。 介绍MySQL 主从同步分为 3 个步骤： master 节点将数据的更新记录写到 binary log 中。slave 节点开启 IO 线程连贯 master 节点并把 master 节点的 binary log 读出来写到本人的 relay log 中。slave 节点开启 SQL 线程读取 relay log 并解析执行，执行实现后，更新同步的地位标记位。配置创立目录构造首先先搞定目录构造，我的目录构造如下，如果想依照本人的想法来组建目录，在下文中的 docker-compose.yaml 文件与 Dockerfile 文件要留神批改文件门路。 配置 docker-compose 模版文件version: "3"services: mysql-master: build: context: ./ dockerfile: mysql/master/Dockerfile container_name: mysql-master volumes: - ./mysql/master/data:/var/lib/mysql restart: always ports: - 3305:3306 links: - mysql-slave mysql-slave: build: context: ./ dockerfile: mysql/slave/Dockerfile container_name: mysql-slave volumes: - ./mysql/slave/data:/var/lib/mysql restart: always ports: - 3306:3306配置 master 节点的 cluster.cnf 文件以及 Dockerfile 文件[mysqld]server_id=100binlog-ignore-db=mysqllog-bin=replicas-mysql-binbinlog_cache_size=1Mbinlog_format=mixedslave_skip_errors=1062# 我的 MySQL 为 8.x，须要如下配置default_authentication_plugin=mysql_native_passwordcharacter-set-server=utf8mb4collation-server=utf8mb4_unicode_ciFROM mysql:latestADD ./mysql/master/cluster.cnf /etc/mysql/conf.d/cluster.cnfENV MYSQL_ROOT_PASSWORD=password配置 slave 节点的 cluster.cnf 文件以及 Dockerfile 文件[mysqld]server_id=101binlog-ignore-db=mysqlbinlog_cache_size=1Mbinlog_format=mixedslave_skip_errors=1062relay_log=replicas-mysql-relay-binlog_slave_updates=1read_only=1# 我的 MySQL 为 8.x，须要如下配置default_authentication_plugin=mysql_native_passwordcharacter-set-server=utf8mb4collation-server=utf8mb4_unicode_ciFROM mysql:latestADD ./mysql/slave/cluster.cnf /etc/mysql/conf.d/cluster.cnfENV MYSQL_ROOT_PASSWORD=password创立容器docker-compose up -d mysql-master mysql-slave运行上述命令进行容器创立，如果构建工夫过长，能够思考更换镜像源，例如上面几个国内优质镜像源： ...
获取SqlSession后，下一步就是执行sql. User user=sqlSession.selectOne("last.soul.mapper.UserMapper.selectById",map);DefaultSqlSession的次要性能就是实现增删改查性能，以及它们的重载办法。就查问来说，最初都会调用select办法，而后改装成selectOne,selectMap等，代码如下： /** * * @param statement sql语句ID=xxxMapper.xml文件中的namespace+sql标签的id. * 如：last.soul.mapper.UserMapper.selectById * @param parameter sql的参数 * @param rowBounds 分页信息 * @param <E> * @return */ @Override public <E> List<E> selectList(String statement, Object parameter, RowBounds rowBounds) { try { //sql相干音讯的包装对象，比方：sql语句，返回类型，是否应用缓存等 MappedStatement ms = configuration.getMappedStatement(statement); //执行sql return executor.query(ms, wrapCollection(parameter)/*包装汇合类型参数*/, rowBounds, Executor.NO_RESULT_HANDLER); } catch (Exception e) { throw ExceptionFactory.wrapException("Error querying database. Cause: " + e, e); } finally { ErrorContext.instance().reset(); } }由上文得悉executor是CachingExecutor,执行的是CachingExecutor的query办法。 ...
MySQL 的 DDL(Data Definition Language) 包含增减字段、增减索引等操作。在 MySQL 5.6 之前，MySQL 的 DDL 操作会依照原来的表复制一份，并做相应的批改，例如，对表 A 进行 DDL 的具体过程如下： 依照表 A 的定义新建一个表 B对表 A 加写锁在表 B 上执行 DDL 指定的操作将 A 中的数据拷贝到 B开释 A 的写锁删除表 A将表 B 重命名为 A在 2-4 的过程中，如果表 A 数据量比拟大，拷贝到表 B 的过程会耗费大量工夫，并占用额定的存储空间。此外，因为 DDL 操作占用了表 A 的写锁，所以表 A 上的 DDL 和 DML 都将阻塞无奈提供服务。 因而，MySQL 5.6 减少了 Online DDL，容许在不中断数据库服务的状况下进行 DDL 操作。 用法ALTER TABLE tbl\_name ADD PRIMARY KEY (column), ALGORITHM=INPLACE, LOCK=NONE;ALTER 语句中能够指定参数 ALGORITHM 和 LOCK 别离指定 DDL 执行的形式和 DDL 期间 DML 的兵法管制 ...
数据库索引，置信大家都不生疏吧。 索引是对数据库表中一列或多列的值进行排序的一种构造，应用索引可快速访问数据库表中的特定信息。作为辅助查问的工具，正当的设计索引能很大水平上加重db的查问压力，db咱们都晓得，是我的项目最外围也是最单薄的中央，如果压力太大很容易产生故障，造成难以预计的影响。所以，不论是日常开发还是面试，索引这一块常识体系都是必须把握的。 当然，虽说是必须把握，但索引的知识点很多，很多初学者常常会脱漏，这也是我为什么想写这篇知识点总结的起因，既是给读者的分享，也是给本人一次全面的温习，心愿对你们有所帮忙。 好了，废话不多说，进入正题。 首先申明一下，本文索引的知识点全副是基于MySQL数据库索引的优缺点长处： 1.大大放慢数据的查问速度 2.惟一索引能够保障数据库表每一行的唯一性 3.减速表连接时间 毛病： 1.创立、保护索引要消耗工夫，所以，索引数量不能过多。 2.索引是一种数据结构，会占据磁盘空间。 3.对表进行更新操作时，索引也要动静保护，升高了保护速度 索引的类型索引的呈现是为了进步查问效率，然而实现索引的形式却有很多种，所以这里也就引入了索引模型的概念。这里介绍三种罕用于索引的数据结构，别离是哈希表、有序数组和搜寻树。 哈希索引哈希表，也称散列表，次要设计思维是通过一个哈希函数， 把关键码映射的地位去寻找寄存值的中央 ，读取的时候也是间接通过关键码来找到地位并存进去，这种数据结构的均匀查找复杂度为O(1)。 比方咱们保护一张身份证信息和用户姓名的表，须要依据身份证号查问姓名，哈希索引大略是这样的： 这种索引构造长处在于随机增加或删除单个元素的效率高，毛病在于哈希表中的元素并不一定按顺序排列，所以如果想做区间查问的话是很慢的， 假如我想查找图中身份证号在[ID_card_n1, ID_card_n3]这个区间的所有用户的话，就必须全副扫描一遍了。 所以，哈希表这种构造实用于只有等值查问的场景 有序数组索引有序数组索引在等值查问和区间查问场景中的效率都很高，还是拿下面的图做例子，用有序数组实现的话是这样子的： 数组的元素按身份证号有序排列，要查问数据的时候，应用二分法就能够疾速失去，工夫复杂度为O(logN)，而且，因为是有序排列，查问某个区间内的数据也是十分的快。 当然，有序数组的毛病也很显著，就跟ArrayList一样，尽管搜寻快，但增加删除元素都有可能要挪动前面所有的元素，这是数组的人造缺点。所以，有序数组索引只实用于动态存储引擎，比方你要保留的是2017年某个城市的所有人口信息，这类不会再批改的数据。 搜寻树索引说到搜寻树，咱们最相熟的应该就是二叉搜寻树了，二叉搜寻树的特点是每个结点的左儿子小于父结点，父结点又小于右儿子，并且左右子树也别离为二叉搜寻树，均匀工夫复杂度是O(log2(n))。 它既有链表的疾速插入与删除操作的特点，又有数组疾速查找的劣势，同时，因为自身二叉搜寻树是有序的，所以也反对范畴查找 这么说起来，其实二叉搜寻树来做索引如同也是个不错的抉择，其实不然 首先咱们要明确的一点是，这棵树是存在于磁盘中，每次咱们都要从磁盘中读取出相应的结点，然而二叉搜寻树的结点在文件中是随机寄存的，所以可能读取一个结点就须要一个磁盘IO，恰好二叉搜寻树都会比拟高，如一棵一百万个元素的均衡二叉树就有十几层高度了，也就是大部分状况下检索一次数据就须要十几次磁盘IO，这个代价太高了，所以个别二叉搜寻树也不会被用来作索引。 为了让一个查问尽量少地读磁盘，就必须让查问过程拜访尽量少的数据块，也就是说，尽可能的让树的高度变低，也就是用多路搜寻树，而InnoDB存储引擎应用的就是这种多路搜寻树，也就是咱们常说的B+树。 InnoDB的索引构造InnoDB是MySQL中最罕用的搜索引擎，它的索引底层构造用的就是B+树，所有的数据都是存储在B+树中的。每一个索引在InnoDB中对应一颗B+树。 B+树的特点是： 所有的叶子结点中蕴含了全副元素的信息，及指向含这些元素记录的指针，且叶子结点自身依关键字的大小自小而大程序链接。所有的两头结点元素都同时存在于子结点，在子结点元素中是最大（或最小）元素。这种构造有两个长处： 能够使得繁多结点存储更多的元素，除了叶子结点，其余的结点只是蕴含了键，没有保留值，这样的话，树的高度就能无效升高，从而缩小查问的IO次数；同时，因为叶子结点蕴含了下个叶子结点的指针，所以范畴查问的时候如果搜寻到第一个叶子结点的话，就能依据指针指向查问前面的数据，不必再从根结点遍历了。这也是为什么很多大神倡议表的主键设计成自增长的好，因为这样范畴查问能提高效率索引的分类依照构造来分的话，数据库索引能够分为聚簇索引和非聚簇索引。 聚簇索引，也叫汇集索引，就是依照每张表的主键结构一颗B+树，同时叶子结点中寄存的就是整张表的行记录数据，简略点说，就是咱们常说的主键索引。在聚簇索引之上创立的索引称之为辅助索引，辅助索引拜访数据总是须要二次查找。 非聚簇索引，也叫非汇集索引，二级索引。这种索引是将数据与索引离开存储，索引构造的叶子结点指向了数据对应的地位。 聚簇索引InnoDB应用的是聚簇索引，将主键组织到一棵B+树中，而行数据就贮存在叶子节点上，咱们先假如一张用户表，这张表蕴含了id，name，company几个字段， 用图片示意InnoDB的索引构造大略是这样： 从图中就能够看出，如果咱们应用"where id = 14"这样的条件查找主键，则依照B+树的检索算法即可查找到对应的叶结点，之后取得行数据。 若对Name列进行条件搜寻，则须要两个步骤：第一步在辅助索引B+树中检索Name，达到其叶子节点获取对应的主键。第二步应用主键在主索引B+树种再执行一次B+树检索操作，最终达到叶子节点即可获取整行数据。（重点在于通过其余键须要建设辅助索引） 这是聚簇索引的构造，而非聚簇索引的代表是MyISM，这也是MySQL中常见的搜索引擎。 非聚簇索引非聚簇索引的两棵B+树看上去没什么不同，结点的构造完全一致只是存储的内容不同而已，主键索引B+树的节点存储了主键，辅助键索引B+树存储了辅助键。索引自身不存储数据，数据存储在独立的中央，这两颗B+树的叶子节点都应用一个地址指向真正的表数据。 看上去，如同非聚簇索引的效率要高于聚簇索引，因为不必查两次B+树，那为什么最罕用的InnoDB引擎还要用这种存储构造呢？它自身的劣势在哪？ 1、聚簇索引中，因为行数据和叶子结点存储在一起，同一页中会有多条行数据，拜访同一数据页不同行记录时，曾经把页加载到了Buffer中，再次拜访的时候，会在内存中实现拜访，不用拜访磁盘。这样主键和行数据是一起被载入内存的，找到叶子节点就能够立即将行数据返回了，所以，如果依照主键Id来组织数据，取得数据更快。 2、辅助索引应用主键作为"指针"而不是应用地址值作为指针的益处是，缩小了当呈现行挪动或者数据页决裂时辅助索引的保护工作，应用主键值当作指针会让辅助索引占用更多的空间，换来的益处是InnoDB在挪动行时毋庸更新辅助索引中的这个"指针"。也就是说行的地位（实现中通过16K的Page来定位）会随着数据库里数据的批改而发生变化（后面的B+树节点决裂以及Page的决裂），应用聚簇索引就能够保障不论这个主键B+树的节点如何变动，辅助索引树都不受影响。 3、聚簇索引适宜用在排序、范畴查问，非聚簇索引不适宜。 笼罩索引说到辅助索引，咱们还能够延长出另一种特地的索引，就是笼罩索引。 下面说了，聚簇索引中拜访数据要通过二次查找，就是先找到辅助键的叶子结点，失去主键对应的结点后再用主键索引查问数据，这样还是比较慢的，其实，如果咱们所需的字段第一次查找就能获取到的话，就不必再二次查找主键了，也就是不必“回表”。 就还是下面那张表有三个字段id，name，company的表来说，我给name加了索引，在查问数据的时候，我就这么写语句： select name from user where name like '张%';因为咱们的语句走了索引，并且返回的字段在叶子结点都存在，查问的时候就不会回表了，多好啊~~ ...
先理解一下两者都代表什么意思int(10)给 int 类型设置字节长度为 10，int 类型默认的值范畴大小是：-2147483648和2147483647。unsigned设置 int 类型不能为正数。创立 MySql 表进行演示创立 test-in 演示 int(10)CREATE TABLE `test-in` ( `id` int(10) NOT NULL AUTO_INCREMENT, PRIMARY KEY (`id`))向 test-in 表中插入数据。insert into `test-in` values(2147483647);insert into `test-in` values(-2147483648);查看表中数据。 尝试一下，在这两个区间之外进行插入数据，是否能够胜利插入。 下面图中能够看到两条 Sql 均都报出异样，插入的值超出了范畴，没方法进行插入数据，只能在 int 范畴区间内进行数据插入。创立 test-un 演示 int unsignedCREATE TABLE `test-un` ( `id` int unsigned NOT NULL AUTO_INCREMENT, PRIMARY KEY (`id`))创立后查问一下 sql 在 sql 语句中，我并没有指定 int 类型的字节长度，执行完 sql 当前，unsigned 会默认设置 int 字节长度为 10。上述中说过应用 unsigned 属性是没方法向表中插入正数的，这里尝试一下 ...
虽说当初这世道有些恋情是有价的，然而数据是无价的，数据备份是尤为的重要，能够在你将来的某一天不小心删库了，不必焦急跑路。 本片文章介绍的计划是利用Linux本身的crontab定时工作性能，定时执行备份数据库的脚本。 技术要点：数据库备份dump命令shell脚本Linux定时工作crontab数据备份dump数据库都有一个导出数据库内数据和构造的命令，就是备份。将备份的数据还原会将原来的数据中的表删了重建，再插入备份中的数据，这是复原。这一点须要留神，如果复原之前的数据比备份的多，复原后多的数据就没有了。 列出我罕用的两种数据库的备份和复原命令 postgresql:备份 pg_dump -h [ip] -U [用户名] [库名] >[导出的.sql 文件]复原 psql -s [库名] -f [导出.sql 文件] mysql:备份 mysqldump -h -u [用户名] -p [库名] > [导出的.sql 文件]复原 mysql -u [用户名] -p [库名] < [导出的.sql 文件] shell脚本要实现一个功能完善的备份计划，就须要shell脚本。咱们要让这个脚本备份到指定门路，并压缩寄存，最多30个，超过30个删除最早的，并记录操作日志。啥也不说了，话都在脚本里，干了！ #用户名username=root#明码password=nicai#将要备份的数据库database_name=l_love_you#保留备份文件最多个数count=30#备份保留门路backup_path=/app/mysql_backup#日期date_time=`date +%Y-%m-%d-%H-%M`#如果文件夹不存在则创立if [ ! -d $backup_path ]; then mkdir -p $backup_path; fi#开始备份mysqldump -u $username -p$password $database_name > $backup_path/$database_name-$date_time.sql#开始压缩cd $backup_pathtar -zcvf $database_name-$date_time.tar.gz $database_name-$date_time.sql#删除源文件rm -rf $backup_path/$database_name-$date_time.sql#更新备份日志echo "create $backup_path/$database_name-$date_time.tar.gz" >> $backup_path/dump.log#找出须要删除的备份delfile=`ls -l -crt $backup_path/*.tar.gz | awk '{print $9 }' | head -1`#判断当初的备份数量是否大于阈值number=`ls -l -crt $backup_path/*.tar.gz | awk '{print $9 }' | wc -l`if [ $number -gt $count ]then #删除最早生成的备份，只保留count数量的备份 rm $delfile #更新删除文件日志 echo "delete $delfile" >> $backup_path/dump.logfi给脚本起个顾名思义的丑陋名字 dump_mysql.sh给脚本赋予可执行权限 chmod +x dump_mysql.sh, 执行后脚本变绿了就是可履行文件执行办法：./加脚本名称 ...
前言： 在日常工作与学习中，无论是开发、运维、还是测试，对于数据库的学习是不可避免的，同时也是日常工作的必备技术之一。在互联网公司，开源数据库用得比拟多的当属MySQL了，置信各位小伙伴关注我的起因也是学习MySQL。学习MySQL的路径有很多，每个人的学习办法也各有不同，但最重要的还是要保持，找到适宜本人的学习办法。本篇文章我将举荐一些学习MySQL的资源，心愿各位能够找到适宜本人的并保持学习。 1.入门资源可能有些小伙伴还处于入门阶段，刚刚开始学习MySQL。对于这类同学，我的倡议是循序渐进一步步学习，比如说先理解下数据库的作用，再学习如何装置，之后再学习一些根底语句。上面举荐一些入门级资源： 菜鸟教程：https://www.runoob.com/mysql/mysql-tutorial.htmlC语言中文网：http://c.biancheng.net/mysql/ 菜鸟教程比拟适宜零根底的同学学习，该教程目录清晰，循序渐进，由浅入深，你能够按目录程序一步步学上来，如果你对某局部特地生疏，也能够独自学习某个章节。相似的还有C语言中文网出品的MySQL教程，我大略看了下，教程也是很具体的，比照菜鸟教程要略微深刻些。 实验楼：https://www.shiyanlou.com/courses/9 实验楼也出品了一个MySQL根底课程，同样适宜初学者学习。实验楼最大的劣势是能够边学边做，左侧学习，右侧能够同步敲命令练习。没有练习环境或者想体验Linux环境的同学能够体验下。 书籍：《MySQL必知必会》 喜爱读技术书籍的敌人能够读读《MySQL必知必会》，这本书籍侧重于根底内容，从零开始带你入门MySQL，适宜作为入门书籍，当然《SQL必知必会》、《深入浅出MySQL》等书籍也能够作为入门书籍浏览。 2.SQL练习有的同学学习MySQL的目标是纯熟写SQL，特地是从事开发、测试、数据分析等岗位的小伙伴，工作中会常常写各类SQL。其实笔者也不太会写SQL，在网上收罗出一些练习SQL的网站举荐给大家： XUESQL：http://xuesql.cn/leetcode：https://leetcode-cn.com/problemset/database/ XUESQL网站是一个练习SQL的网站是，适宜从根底开始练习，题目由浅入深，而且有配套B站视频。leetcode不仅能够刷算法题，还能够练习写SQL，而且能够在线测评，和评测算法题一样，也会让你很直观的看到本人所写的SQL的运行速度等。相对而言，leetcode中的SQL题目要简单些。其实，练习SQL最重要的还是要有理论场景，只靠网站练习可能在理论工作中用途不大，如果你日常工作常常遇到些SQL的场景，那么缓缓你的SQL程度就会晋升下来。 3.进阶资源对于想从事数据库相干行业的同学来说，学习MySQL就不应该只局限于增删改查这类操作了。更多的是要理解其背地的原理，保护数据库的稳固，解决业务需要。对于进阶资源，集体首推还是官网文档，能够很不便的找到本人想学的内容。除了官网文档，再举荐几个博客专栏，都是我珍藏多年的资源啊，哈哈。 MySQL团队博客：https://mysqlserverteam.com/Percona官网博客：https://www.percona.com/blog/淘宝月报：http://mysql.taobao.org/monthly/数据库内核专栏：https://zhuanlan.zhihu.com/c_206071340 以上内容大多是官网出品的一些博文，各类内容都有，不过有些内容比拟深刻哦。进阶书籍这里举荐《高性能MySQL》，这本书我就不必多介绍了吧，是MySQL畛域比拟经典的一本书，适宜作为进阶资源。除此之外，还有一些技术公众号写的不错，例如『MySQL技术』，哈哈，心愿大家继续关注。另外还有一些付费课程内容也很棒，例如极客工夫出品的「MySQL实战45讲」、掘金小册「MySQL是怎么运行的」等，这里不打广告，有趣味的小伙伴能够本人去理解。 总结： 本篇文章次要介绍了一些学习MySQL的资源，上面简略整顿总结下，须要的小伙伴能够多瞧一瞧哦。 入门资源:菜鸟教程：https://www.runoob.com/mysql/mysql-tutorial.htmlC语言中文网：http://c.biancheng.net/mysql/实验楼：https://www.shiyanlou.com/courses/9书籍：《MySQL必知必会》SQL练习网站：XUESQL：http://xuesql.cn/leetcode：https://leetcode-cn.com/problemset/database/进阶资源：官网文档：https://dev.mysql.com/doc/refman/5.7/en/MySQL团队博客：https://mysqlserverteam.com/Percona官网博客：https://www.percona.com/blog/淘宝月报：http://mysql.taobao.org/monthly/数据库内核专栏：https://zhuanlan.zhihu.com/c_206071340书籍：《高性能MySQL》笔者也整顿了一些MySQL相干材料，并不是那种几百页的含糊PDF哦，都是一些简短内容，让人更有趣味看上来。有本公众号文章原文、有相干PDF文档、还有业界大咖分享的材料！须要的小伙伴能够在公众号『MySQL技术』对话窗口回复 666 获取。其他同学有学习MySQL的相干资源或网站也能够留言分享。 本文由博客一文多发平台 OpenWrite 公布！
原文链接: 何晓东 博客 最直观区别：truncate drop 是 DDL 语句，有隐式提交，不可回滚，delete 是 DML 语句，能够回滚。truncatetruncate 会删除并从新创立表，比 delete 要快，尤其对于大型表。truncate 会导致隐式提交，因而无奈回滚。如果以后有沉闷的表锁，则无奈进行 truncate如果表或者 NDB 表的援用表的其余表有任何束缚，则他们的 truncate 会失败，而 delete 容许同一表的列之间的外键束缚。（没太了解，原文为：TRUNCATE TABLE fails for an table or NDB table if there are any constraints from other tables that reference the table. Foreign key constraints between columns of the same table are permitted. InnoDB FOREIGN KEY）truncate 不返回已删除的函数，通常是 “0行受影响”，应了解为 “无信息”。只有表定义无效，即便数据或索引文件已损坏，也能够将表从新创立为具备 truncate table 的空表。任何值都将重置为其开始值。即便对于并且通常不重用序列值也是如此。与分区表一起应用时,TRUNCATE TABLE保留分区。也就是说，在分区定义不受影响的状况下，将删除并从新创立数据和索引文件。truncate 不会调用触发器，delete 会调用触发器。truncate 设置反对 INNODB 引擎损坏的表。drop它删除表定义和所有表数据。如果对表进行了分区，则该语句将删除表定义，其所有分区，存储在这些分区中的所有数据以及与删除的表关联的所有分区定义。drop table 也会删除该表的所有触发器。drop table 是 DDL，也会导致隐式提交，不可回滚，在和 关键字 一起提交的状况下例外。drop table 不会调用触发器。deletedelete 是 DML，会记录日志，同时也能够回滚。delete 会对行加锁。被删除的数据行只是被标记删除，不会缩小占用空间，新增的数据能够间接复用标记为删除的记录的存储空间。参考链接： ...
下载mysql的repo源wget http://repo.mysql.com/mysql-community-release-el7-5.noarch.rpm装置mysql-community-release-el7-5.noarch.rpm包sudo rpm -ivh mysql-community-release-el7-5.noarch.rpm装置MySQLsudo yum install mysql-server重置MySQL明码mysql -u root报错：ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)起因：起因是/var/lib/mysql的拜访权限问题。执行以下命令受权 chown root /var/lib/mysql/重启MySQL服务service mysqld restart登陆设置明码mysql -u rootuse mysql;update user set password=password('123456') where user='root';exit;重启MySQL服务service mysqld restart设置Root账户近程连贯明码mysql -u root -pGRANT ALL PRIVILEGES ON *.* TO root@"%" IDENTIFIED BY "root";重启服务器service mysqld restart设置Root账户近程连贯明码mysql -u root -pGRANT ALL PRIVILEGES ON *.* TO root@"%" IDENTIFIED BY "root";重启服务器service mysqld restartMySQL主从复制配置主服务器节点vi /etc/my.cnf新增以下内容 server_id=177 ###服务器id (保障集群惟一)log-bin=mysql-bin ###开启日志文件重启mysql服务 ...
装置rpmyum install gnutls libaio.x86_64 libaio-devel.x86_64 novacom-server.x86_64 libedit -ywget https://downloads.mariadb.com/MaxScale/2.2.0/centos/7server/x86_64/maxscale-2.2.0-1.centos.7.x86_64.rpmrpm -ivh maxscale-2.2.0-1.centos.7.x86_64.rpmMaxScale服务器批改配置vi /etc/maxscale.cnf[maxscale]threads=1# 主服务器 3306[server1]type=serveraddress=192.168.91.108port=3306protocol=MySQLBackend# 配置从服务器 3306[server2]type=serveraddress=192.168.91.109port=3306protocol=MySQLBackend[MySQL Monitor]type=monitormodule=mysqlmonservers=server1,server2user=rootpasswd=123456monitor_interval=10000detect_stale_master=true[Read-Write Service]type=servicerouter=readwritesplitservers=server1,server2user=rootpasswd=123456max_slave_connections=100%use_sql_variables_in=masterenable_root_user=1max_slave_replication_lag=3600[MaxAdmin Service]type=servicerouter=cli[Read-Write Listener]type=listenerservice=Read-Write Serviceprotocol=MySQLClientport=3306[MaxAdmin Listener]type=listenerservice=MaxAdmin Serviceprotocol=maxscaledsocket=default启动maxscale服务maxscale --config=/etc/maxscale.cnfnetstat -ntelp #次要查问3306 端口是否监听查看maxscale 服务状态maxadmin> list serversServers.-------------------+-----------------+-------+-------------+--------------------Server | Address | Port | Connections | Status -------------------+-----------------+-------+-------------+--------------------server1 | 主服务器ip | 10336 | 0 | Master, Runningserver2 | 从服务器ip | 10336 | 0 | Slave, Running-------------------+-----------------+-------+-------------+--------------------至此，实现MaxScale中间件实现MySQL读写拆散。
创立表：CREATE TABLE <表名> ([表定义选项])表选项;增：增行：insert [into] <表名> (列名) values (列值)增表：select <新建表列名> into <新建表名> from <源表名>删：删行：delete from <表名> [where <删除条件>]删表：truncate table <表名>查：精准查：select <列名> from <表名> [where <查问条件表白试>] [order by <排序的列名>[asc或desc]]全副查：select * from <表名>含糊查：select * from a where name like '<name>%'改：update <表名> set <列名=更新值> [where <更新条件>]
我的项目背景：随着挪动互联网多年的疾速倒退，挪动互联网已进入下半场 ，不再依附用户红利来经营，倒退业务，辞别毛糙的/高老本企业倒退的形式，开始转而精细化治理，联合市场、渠道、用户行为等数据分析，对用户开展有针对性的经营流动，提供个性化、差异化的经营策略，以实现经营目标行为。本文利用SQL对淘宝用户行为数据进行剖析，通过用户行为剖析业务问题，提供针对性的经营策略。 剖析步骤： 提出问题数据了解数据荡涤构建模型总结与倡议一、提出问题1. 本次剖析的业务问题及实用指标本次剖析的目标是想通过对淘宝用户行为数据分析，为以下问题提供解释和改良倡议： 用户从浏览到最终购买的整个过程的散失状况，确定夹点地位，提出改善转化率的意见。在钻研的时间段里找出用户最沉闷的日期以及每天沉闷的时间段，理解用户的行为工夫模式。什么产品以及产品类目标购买率最高，找出最受欢迎的产品，优化产品销售。哪些用户购买次数最多，找出最外围的付费用户群，并且统计出这些用户购买的产品以及类目，针对这些用户的购买偏好推送个性化的产品销售计划。针对下面的业务问题，上面是实用的业务指标：2. 基于AARRR漏斗模型剖析用户行为本我的项目通过罕用的电商数据分析业务指标，采纳AARRR漏斗模型拆解用户进入APP后的每一步行为。AARRR模型是依据用户应用产品全流程的不同阶段进行划分的，针对每一环节的用户散失状况剖析出不同环节的优化优先级，次要通过以下个各阶段来进行剖析： 二、数据了解本我的项目数据来源于阿里云天池，可登陆阿里云天池下载数据，地址如下：User Behavior Data from Taobao for Recommendation本数据集蕴含了2017年11月25日至2017年12月3日之间，有行为的约一百万随机用户的所有行为（行为包含点击、购买、加购、喜爱）。数据集的组织模式和MovieLens-20M相似，即数据集的每一行示意一条用户行为，由用户ID、商品ID、商品类目ID、行为类型和工夫戳组成，并以逗号分隔。对于数据集中每一列的详细描述如下：留神到，用户行为类型共有四种，它们别离是：对于数据集大小的一些阐明如下： 三、数据荡涤1. 察看记录原数据集数据记录达到1亿条，数据量宏大，为了不便剖析与效率，本我的项目将选取了从500万行至800万的300万条记录进行剖析。2. 统一化解决原数据工夫戳应用的是epoch&unix timestamp格局，须要转换为规范可读的日期工夫模式。在原数据表减少3个新字段datetime、dates、hours，把转换好的日期工夫放进去。 ALTER TABLE userbehavior ADD COLUMN datetime TIMESTAMP(0) NULL;UPDATE userbehavior SET datetime=FROM_UNIXTIME(timestamps);ALTER TABLE userbehavior ADD COLUMN date CHAR(10) NULL;UPDATE userbehavior SET date=SUBSTRING(datetime FROM 1 FOR 10);ALTER TABLE userbehavior ADD COLUMN hour CHAR(2) NULL;UPDATE userbehavior SET hour=SUBSTRING(datetime FROM 12 FOR 2);3. 异样值解决查看日期是否在规定范畴内（2017年11月25日至2017年12月3日），将不符合规定的数据删除。 SELECT MAX(timestamps), MIN(timestamps), MAX(datetime), MIN(datetime)FROM userbehavior; DELETE FROM userbehaviorWHERE datetime<'2017-11-25 00:00:00' OR datetime>='2017-12-04 00:00:00';一共删除了1689行数据，再次验证日期工夫的准确性，上面后果满足要求： ...
本文起源：翻译 管长龙*爱可生开源社区出品，原创内容未经受权不得随便应用，转载请分割小编并注明起源。MySQL 8.0.21 GA！重点解读MySQL 8.0.21 版本已于昨日公布（dev.mysql.com），开始对一些术语如 Master / Slave 等做了替换。上面是来自官网团队对此版本的重点性能解读。 更具体的内容请参考：https://dev.mysql.com/doc/rel...InnoDB增加全局禁用 redo log 性能的配置项(WL#13795)反对动静启停 redo log，可使数据库写入速度更快，服务也更容易解体并失落整个实例数据。 ALTER INSTANCE ENABLE|DISABLE INNODB REDO_LOG;次要实用在加载初始数据时，首先禁用 redo log，加载数据，再次开启。 表空间文件名验证变为可选项(WL#14008)通过参数 --innodb-validate-tablespace-paths (ON|OFF) 可决定是否开启表空间文件名验证性能。在 HDD 零碎中扫描表空间开销很大，在咱们晓得用户不会频繁挪动文件的状况下，能够通过跳过验证缩小启动工夫。即便该参数设置为 OFF，仍然能够应用 ALTER TABLESPACE 语法。 锁零碎的优化(WL#10314)以往用单个闩锁爱护爱护所有队列的拜访，扩展性很差，队列治理成为瓶颈，因而引入更细化的闩锁办法。将每个表和每一行都能够视为资源，并且事务能够申请对资源的拜访权限。锁零碎将 GRANTED 和 WAITING 的申请都存在一个队列中。为了容许队列并发操作，提供了一种平安疾速锁定队列的形式。 将所有的 InnoDB 表空间限定为已知的目录 (WL#13065)将表空间文件的地位限定在已知目录(datadir, innodb_data_home_dir, innodb_directories, and innodb_undo_directory)。目标是限度能够在任何地位创立文件从而导致复原过程出现意外的状况。 Undo DDL 反对 ACID (WL#11819)改良 Undo 表空间性能和安全性，可对 Undo 表空间主动截断。对 Undo 表空间的 CREATE / TRUNCATE 操作都被记录到 redo log。长处是防止了之前解决方案在 Undo 截断过程中须要两个检查点，这些检查点可能阻塞零碎。此批改还修复了几个影响到 Undo 的命令：CREATE、DROP 和 TRUNCATE。 ...
前言关键词：乐观锁，乐观锁，表级锁，行级锁，共享锁，排他锁，偏心锁，非偏心锁 乐观锁每次获取数据的时候放心数据被批改, 所以每次获取数据的时候都会进行加锁, 确保本人应用过程中数据不会被他人批改, 应用实现后对数据进行解锁. 因为数据进行加锁, 期间对改数据进行读写的其余线程都会进行期待 CREATE TABLE `tb_goods_stock` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID', `goods_id` bigint(20) unsigned DEFAULT '0' COMMENT '商品ID', `nums` int(11) unsigned DEFAULT '0' COMMENT '商品库存数量', `create_time` datetime DEFAULT NULL COMMENT '创立工夫', `modify_time` datetime DEFAULT NULL COMMENT '更新工夫', PRIMARY KEY (`id`), UNIQUE KEY `goods_id` (`goods_id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='商品库存表';将商品库存数量nums字段类型设为unsigned，保障在数据库层面不会产生正数的状况。 留神，应用乐观锁，须要敞开mysql的主动提交性能，将 set autocommit = 0; 留神，mysql中的行级锁是基于索引的，如果sql没有走索引，那将应用表级锁把整张表锁住。 1、开启事务，查问要卖的商品，并对该记录加锁。 begin;select nums from tb_goods_stock where goods_id = {$goods_id} for update;2、判断商品数量是否大于购买数量。如果不满足，就回滚事务。 ...
作者：王翔飞爱可生研发团队测试成员，负责数据库治理平台的测试工作。本文起源：原创投稿*爱可生开源社区出品，原创内容未经受权不得随便应用，转载请分割小编并注明起源。本文关键字：最大连接数、TCP协定、MySQL协定、参数配置景象在测试某性能时，将 mysql 的最大连接数设置为 120，应用 sysbench 并发 200 插入数据， 上述谬误是预期内的后果，因为 sysbench 的 200 个并发超过了 mysql 实例最大连接数； 随后，批改 sysbench 并发数为 100（小于最大连接数），再次插入数据，失败报错，并发数曾经小于最大连接数了，为什么还报错，报错信息如下： 应用用户 test 独自登录实例，和下面报一样的谬误： 之前失常的能够登录的用户 test，当初无奈登录了。 起因和解决办法起初，并不理解是什么起因造成的登录失败。查问官网文档理解到，是用户的谬误的连接数超过了设置的最大值，这个最大值参数是 max_connect_errors。 解决办法很简略：执行 flush hosts 官网解释：https://dev.mysql.com/doc/ref...剖析对于这个参数 max_connect_errors 之前并不理解，查阅网上文档提到，应用谬误明码屡次登录并不能模仿失败连贯。尝试将此参数批改为 2，而后应用谬误明码登录 2 次，后续再登录仍然胜利。看来应用谬误明码的确不能模仿失败连贯。 查阅官网文档理解到，在 Performance Schema 库表 host_cache 里会保留客户端的连贯信息，其中字段 SUM_CONNECT_ERRORS 就是记录连贯的谬误次数，一旦 SUM_CONNECT_ERRORS 的值达到 max_connect_errors 设定的值，来自此客户端的连贯就会被阻止。SUM_CONNECT_ERRORS 的官网形容：The number of connection errors that are deemed “blocking” (assessed against the max_connect_errors system variable). Only protocol handshake errors are counted, and only for hosts that passed validation (HOST_VALIDATED = YES). ...
mysql事务：MySQL 事务次要用于解决操作量大，复杂度高的数据。比如说，在人员管理系统中，你删除一个人员，你既须要删除人员的根本材料，也要删除和该人员相干的信息，如信箱，文章等等，这样，这些数据库操作语句就形成一个事务！ • 在 MySQL 中只有应用了 Innodb 数据库引擎的数据库或表才反对事务。• 事务处理能够用来保护数据库的完整性，保障成批的 SQL 语句要么全副执行，要么全副不执行。• 事务用来治理 insert,update,delete 语句一般来说，事务是必须满足4个条件（ACID）：：原子性（Atomicity，或称不可分割性）、一致性（Consistency）、隔离性（Isolation，又称独立性）、持久性（Durability）。• 原子性：一个事务（transaction）中的所有操作，要么全副实现，要么全副不实现，不会完结在两头某个环节。事务在执行过程中产生谬误，会被回滚（Rollback）到事务开始前的状态，就像这个事务素来没有执行过一样。• 一致性：在事务开始之前和事务完结当前，数据库的完整性没有被毁坏。这示意写入的材料必须完全符合所有的预设规定，这蕴含材料的精确度、串联性以及后续数据库能够自发性地实现预约的工作。• 隔离性：数据库容许多个并发事务同时对其数据进行读写和批改的能力，隔离性能够避免多个事务并发执行时因为穿插执行而导致数据的不统一。事务隔离分为不同级别，包含读未提交（Read uncommitted）、读提交（read committed）、可反复读（repeatable read）和串行化（Serializable）。• 持久性：事务处理完结后，对数据的批改就是永恒的，即使系统故障也不会失落mysql索引：MySQL索引的建设对于MySQL的高效运行是很重要的，索引能够大大提高MySQL的检索速度。打个比方，如果正当的设计且应用索引的MySQL是一辆兰博基尼的话，那么没有设计和应用索引的MySQL就是一个人力三轮车。拿汉语字典的目录页（索引）打比方，咱们能够按拼音、笔画、偏旁部首等排序的目录（索引）疾速查找到须要的字。索引分单列索引和组合索引。单列索引，即一个索引只蕴含单个列，一个表能够有多个单列索引，但这不是组合索引。组合索引，即一个索引蕴含多个列。创立索引时，你须要确保该索引是利用在 SQL 查问语句的条件(个别作为 WHERE 子句的条件)。实际上，索引也是一张表，该表保留了主键与索引字段，并指向实体表的记录。下面都在说应用索引的益处，但过多的应用索引将会造成滥用。因而索引也会有它的毛病：尽管索引大大提高了查问速度，同时却会升高更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保留数据，还要保留一下索引文件。建设索引会占用磁盘空间的索引文件。 索引的数据结构下面讲了索引的基本原理，数据库的复杂性，以及操作系统的一些内容，目标就是让大家理解到，任何一种数据结构都不是凭空产生的，肯定有它的背景和应用场景。那么，咱们须要这些数据结构可能做什么呢？其实很简略，就是：每次查找数据的时候，把磁盘I/O次数限度在一个很小的数量级，最好是一个常量数量级。那么咱们就想到，如果一个高度可控的多路搜寻树，是否可能满足需要呢？在这样的背景下，B+树应运而生。 详解B+树如上图，是一棵B+树。B+树的定义，童鞋能够自行百度，咱们只说一些重点。图中浅蓝色的块，咱们称之为一个磁盘，能够看到，每个磁盘块蕴含几个数据项（深蓝色）和指针（黄色）。如：磁盘块1蕴含数据17和数据35，蕴含指针P1,P2,P3，P1指向数据小于17的磁盘块，P2指向数据在17到35之间的数据所在磁盘块，P3指向数据大于35的数据所在的磁盘块。实在数据存在于叶子节点，即3，5，9，10，13，15，28，29，36，60，75，79，90，99 。非叶子节点不存储实在数据，只存储指引搜寻方向的数据项，如17、35并不实在存在于数据表中。 B+树的查找过程还是应用下面的B+树。假如，咱们要查找数据项29，那么咱们首先会把磁盘块1由磁盘加载到内存中，此时进行了一次I/O，在内存中用二分查找确定29在17和35之间，锁定磁盘块1的P2指针，内存计算工夫因为十分短（比照于I/O）能够忽略不计，通过磁盘块1的P2指针的磁盘地址指向磁盘块3，由磁盘加载到内存，此时进行了第二次I/O，29在26和30之间，锁定磁盘块3的P2指针，通过指针加载磁盘块8到内存，此时进行了第三次I/O，同时内存中计算二分查找找到29，查问完结。这一过程，一共进行了3次I/O。在实在应用场景中，三层的B+树能够示意上百万的数据，如果上百万的数据查问只须要三次I/O，性能进步将会是微小的。B+树就是一种索引数据结构，如果没有这样的索引，每个数据项产生一次I/O，那么老本将会大大晋升。 B+树的性质在下面的查找例子中，咱们能够剖析出一些B+树的性质： 1，I/O的次数取决于B+树的高度H，假如以后数据表的数据为N，每个磁盘块的数据项的数量是M，则有：H=log(M+1)N，当数据量N肯定的状况下，M越大，H越小；而M=磁盘块大小/数据项大小，磁盘块大小也就是一个数据页的大小，是固定的，如果数据项占的空间越小，数据项的数量越多，树的高度也就越低。这也就是为什么每个数据项，即索引字段要尽量的小，比方int占4个字节，要比bigint的8个字节小一半。这也是为什么B+树要求把实在数据放在叶子节点内而不是内层节点内，一旦放到内层节点内，磁盘块的数据项会大幅度的降落，导致树层级的增高。当数据项为1时，B+树会进化成线性表。 2，B+树的数据项是复合性数据结构，比方（name，age，gender）的时候，B+树是依照从左到右的程序来建设搜寻树的，比方当（小张，22，女）这样的数据来检索的时候，B+树会优先比拟name来确定下一步的搜寻方向，如果name雷同再顺次比拟age和gender，最初失去检索的数据。然而，当（22，女）这样没有name的数据来的时候，B+树就不晓得下一步该查哪个节点，因为建设搜寻树的时候，name就是第一个比拟因子，必须依据name来搜寻才晓得下一步去哪里查问。比方，当（小张，男）这样的数据来检索时，B+树就能够依据name来指定搜寻方向，但下一字段age缺失，所以只能把名字是“小张”的所有数据都找到，而后再匹配性别是“男”的数据了。这个是十分重要的一条性质，即索引的最左匹配个性。 索引的类型在MySQL中，索引分为两大类：聚簇索引和非聚簇索引。聚簇索引是依照数据寄存的物理地位为程序的，而非聚簇索引则不同；聚簇索引可能进步多行检索的速度，而非聚簇索引则对单行的检索速度很快。 在这两大类的索引类型下，还能够将索引分成四个小类： 1，一般索引：最根本的索引，没有任何限度，是咱们大多数状况下应用到的索引。 2，惟一索引：与一般索引类型，不同的是惟一索引的列值必须惟一，但容许为空值。 3，全文索引：全文索引（FULLTEXT）仅能够实用于MyISAM引擎的数据表；作用于CHAR、VARCHAR、TEXT数据类型的列。 4，组合索引：将几个列作为一条索引进行检索，应用最左匹配准则。 建设索引的准则咱们回头来看一开始提到的慢查问，当咱们理解完索引原理之后，对慢查问的优化应该有一些想法，这里咱们先总结一下建设索引的一些准则： 1，最左前缀匹配准则。这是十分重要、十分重要、十分重要（重要的事件说三遍）的准则，MySQL会始终向右匹配直到遇到范畴查问（>,<,BETWEEN,LIKE）就进行匹配，比方：a = 1 AND b = 2 AND c > 3 AND d = 4，如果建设 （a,b,c,d）程序的索引，d是用不到索引的，如果建设（a,b,d,c）的索引，则都能够用到，a,b,d的程序能够任意调整。 2，等于（=）和in 能够乱序。比方，a = 1 AND b = 2 AND c = 3 建设（a,b,c）索引能够任意程序，MySQL的查问优化器会帮你优化成索引能够辨认的模式。 ...
咱们有时候会有这种需要：这种与列值相干的展现有时候十分具备数据的直观性，我将用一个小Demo来实现此类操作。 表构造create table demo1( sname varchar(20) not null comment '学员', course varchar(10) not null comment '科目', score float not null comment '问题')插入如下数据： snamecoursescore张三语文100张三数学90张三英语80李四语文90李四数学70李四英语100MySQL提供了条件分支语法（相似于if/else、case...）语法：1.case key when 条件 then 后果 when 条件 then 后果 …else key（默认为原来的）end2.if(作为列的字段 = '值', 要展现的数据字段,另外的值) 上代码： select sname '姓名', max(if(course = '语文', score,0)) '语文', avg(case course when '数学' then score end) '数学', max(if(course = '英语',score,0)) '英语' from demo1 group by sname;执行后果： 总结：通过下面的sql语句咱们实现了行值到列名的转换，咱们借用了聚合函数来与条件分支实现了此操作，其中course='语文'为条件判断，而score为理论要展现的数据字段，如果条件不成立则输入为0,聚合函数的应用并不谨严，其实avg函数也能够是其余的聚合函数。
分布式事务InnoDB通过XA事务(媒介)来反对分布式事务。InnoDB必须在SERIALIZABLE隔离级别下能力开启分布式事务。 XA事务是连贯 多个不同数据库 来进行分布式事务的媒介。分布式事务的特点：两次提交（two-phase commit），第一阶段，所有分布式节点资源管理器提交筹备(PREPARE)指令,通知事务管理器准备就绪。第二阶段，事务管理器通知所有资源管理器，是commit还是rollback。若在第一阶段有节点不能提交PREPARE,则会rollback。 能够了解为 两次握手，所有的资源管理器与一个核心握手。定义变量： SET @var_name = 1; 没有默认值DECLARE var_name INT DEFAULT 1; 默认值为NULL;备份mysqldump工具 整个table或schemamysqldump --all-databases > dump.sql;mysqldump -databases db1 db2 db3 >dump.sql; 复原: mysql -u root -p < dump.sql;SELECT * INTO OUTFILE ... FROM tbl; 表中的某几列select * into outfile '/home/mysql/a.txt' form a;通用复原: load data infile '/home/mysql/a.txt' into table a;热备工具ibbackupxtrabackup附上一个他人的mysql总结：MySql常见问题总结
上篇文章中具体了介绍了一下changelog 文件的应用，本篇文章将具体说一下 changeSet 变更集 中的细节，以及通常的应用形式 1.变更集分类changeSet 分为 6类： addcreatedroprenamesqlother官网文档：https://docsstage.liquibase.c...，每一个标签都有其必须的参数，应用时依据状况自行设定即可 用法均为如下格局： <changeSet> <xxxx /></changeSet>2.1 add标签形容addAutoIncrement将一个已存在的列转换为自增addColunm减少列addDefaultValue对已存在的列减少默认值addForeignKeyConstraint对已存在的列减少外键束缚addLookupTable创立外键关联的表addNotNullConstraint对已存在的列减少非空束缚addPrimaryKey对已存在的列减少主键束缚ddUniqueConstraint对已存在的列减少主键束缚2.2 create标签形容createIndex创立索引createProcedure创立存储过程createSequence创立序列createTable创立表createView创立视图2.3 drop标签形容dropAllForeignKeyConstraints删除全副的外键束缚dropColumn删除列dropDefaultValue删除默认值设置dropForeignKeyConstraint删除某一列的外键束缚dropNotNullConstraint删除非空束缚dropIndex删除索引dropSequence删除束缚dropProcedure删除存储过程dropPrimaryKey删除主键dropTable删除表dropUniqueConstraint删除唯一性束缚dropView删除视图2.4 rename标签形容renameColumn重命名列renameSequence重命名序列renameTable重命名表renameView重命名视图2.5 sql标签形容sql原生SQLsqlFile引入 SQL 文件2.6 Other标签形容标签形容alterSequence批改序列customChange自定义change类型，须要本人实现liquibase.change.custom.CustomSqlChange、liquibase.change.custom.CustomTaskChange接口delete删除数据empty空操作executeCommand执行系统命令，如 mysqldumpinsert插入数据loadData加载 csv 文件到已存在的表中loadUpdateData加载 csv 文件到已存在的表中，然而会判断是否存在，存在更新，否则新增mergeColumns将两列值合并在一起，存入新列中modifyDataType批改列数据类型output记录一条音讯并继续执行setColumnRemarks列上增加备注setTableRemarks表上增加备注stop通过音讯进行 LiquibasetagDatabase将标签利用于数据库以供将来回滚update更新数据2.罕用变更集下面这么多的标签，置信曾经将很多人的眼睛看花了，不要紧，将它们全副列举出次要还是想大家可能对 Liquibase 有一个更全面的了解，遇到某些场景时可能对症检索。 接下来我会介绍几个罕用的标签，根本可能笼罩大多数场景。 2.1 SQL最罕用以及最棘手的就是 sql 标签，开发人员像应用 Mybatis 一样，写原生SQL，然而如果SQL比较复杂，可读性就不怎么好 <changeSet id="xxxx" author="jiaotd" labels="init" > <sql> USE `db_xxx`; insert into table_t values(1,1,1); </sql> </changeSet>2.2 sqlFilesqlFIle 就是将下面 SQL 中的语句独自应用文件存储，在 sqlFIle 引入。 这样做的益处是 changelog 文件简略、整洁、可读性高、易于保护。 <changeSet id="xxxx" author="jiaotd" labels="init" > <sqlFile path="update/xxxx.sql" relativeToChangelogFile="true"/> </changeSet>2.3 loadDataloadData 通常用于导入数据，个别咱们用于系统升级时导入大量的数据。 <changeSet author="jiaotd" id="loadData-xxx"> <loadData commentLineStartsWith="//" encoding="UTF-8" file="example/users.csv" quotchar="'" relativeToChangelogFile="true" schemaName="public" separator=";" tableName="person" usePreparedStatements="true"> <column header="header1" name="id" type="NUMERIC"/> <column index="3" name="name" type="BOOLEAN"/> </loadData> </changeSet>
InnoDB中的索引:B+树索引全文(Full Text)索引 不反对中文哈希索引这里的哈希索引是自适应的（主动实现的），innodb会主动依据状况生成hash索引，不能人为干涉。B+树的B代表balance而不是binary，B+树不属于二叉树。B+树常利用于磁盘存储中。B+树的演变演化过程： 数组—>二叉查找树(BST)—>均衡二叉树(AVL)—>B-树—>B+树程序查找 二分 左右深度差<=1 m叉树，叶子在同一层 数据全保留在叶子，叶子之间有指针注：b-树（均衡多路查找树）又称B树 B-树与AVL的区别： 变成了m叉树，使得关键字增多，从而树的深度缩小所有叶子节点在同一层，且都为NULLm个关键字的节点至多有 ⌈ m/2⌉个子树。关键字更多。档次更少，查找更快。 B+树与B-树的区别: B+树的分枝节点不再保留关键字指针，只保留索引。叶子节点保留了所有关键字信息。（数据，指针都保留在叶子节点）叶子节点之间有双向指针相连。同一节点中的数据按值从小到大排序。n个关键字的父节点有n个字树。档次更少。查问更稳固（每次都一样）。遍历更快（叶子之间有指针） B+树的三个操作：插入 插入 注：旋转，用于缩小拆页次数。 删除 依据删除后的填充因子来判断是否删除，50%是能够设置的最小值。第一种状况：都没小于填充因子50%第四种状况：索引节点的填充因子 < 50% && 叶子节点不小于。 注：这种情况表中没有列出来，然而下图就是这种。该书的作者此处有笔误。第三种状况 扇(shan)出：一个模块调用其余模块的格数。扇入：被多少个模块调用。B+树索引汇集索引(clustered index)、辅助索引(secondery index) 汇集索引为每张表的主键结构一颗B+树(叶子按程序寄存)，叶子节点寄存表的行记录数据，叶子节点 == 数据页。数据页之间双向指针连贯。一张表只能有一个汇集索引。 范畴查找十分快。（因为有程序，而且有双向指针）汇集索引的字段必须是NOT NULL && UNIQUE辅助索引叶子节点不蕴含所有行记录数据，每个叶子节点还蕴含一个书签(bookmark)，书签指向的就是汇集索引。创立索引的语法创立 ALTER TABLE tbl_nameADD [idx_type] | [idx_name] (clo1,col2...) idx_type蕴含（unique,primary key,fulltext,index）删除 ALTER TABLE tbl_nameDROP PRIMARY KEY| DROP {INDEX|KEY} idx_name创立局部索引(不是整个数据，而是结尾的肯定长度) ALTER TABLE tbl_nameADD KEYidx_b (b(100))这里b字段为varchar(8000)，但只建设前100个字符。查看索引:SHOW INDEX FROM tbl_name;更新基数Cardinality：ANALYZE TABLE tbl_name;基数Cardinality示意索引中 不重复记录数 的预估值。理论利用中，Cardinality应尽可能靠近1。如果值十分小，也示意没必要创立该索引。 什么时候创立索引 当列的值各种各样时，能够思考创索引，如name字段。对于值比拟繁多的列，无需创索引。如：sex字段值只有M/F。 ...
MySql参数参数分为： 动态参数 r只读动静参数 rw读/写SET应用：SET [global|session] sys_var_name = val;SELECT应用：SELECT [@@global|@@session|@@] sys_var_name; 日志文件常见日志文件： error log（谬误日志）binlog（二进制日志）slow query log（慢查问日志）log（查问日志）undo log(回滚日志)redo log(重做日志) 存储引擎文件error log在mysql启动、运行、敞开时进行记录，不仅仅蕴含错误信息，还蕴含正告和其余正确的信息。命令为：show VARIABLES LIKE 'log_error' binlogbinlog(二进制日志)只记录了所有更改(update,insert,delete,create,drop,alert)操作，不包含select,show这类查问操作。即便更改操作未扭转数据库时，仍会记录在内。如：UPDATE t SET a=1 WHERE a=2;用处： 复原(recovery)主从复制(replication)：使得主从mysql数据库实现同步。审计(audit)：判断是否有注入攻打，晋升安全性。 binlog相干参数： max_binlog_size 单个binlog文件大小binlog_cache_size 缓冲大小sync_binlog 每几次缓冲刷回磁盘，默认为0binlog-do-db 写入哪些库的binlog,默认为空，即写入所有库的binlogbinlog-ignore-db 疏忽哪些库的binlog,默认为空，即写入所有库的binloglog-slave-update 默认false,即本人作为slave端时，不会写入从master传过来的binlog到本人的binlog。 m->s->s架构必须配置该参数，否则两头就断了。bilog_format binlog的记录格局,协调不同数据库的 不同事物隔离级别 之间复制，保证数据一致性，可选值[statement|row|mixed] statement：记录的是逻辑 SQL 语句row：记录 表 的更改状况mixed：默认以statement，某些状况会采纳row。应用row状况包含： uuid()等不确定函数insert delay应用了用户自定义函数(UDF)应用了长期表(temporary table) row的开销会比statement大很多文末补充了MYSQL实现主从复制的相干文章（他人写的）。slow log用于定位查问慢的SQL语句，mysql默认不启动慢日志，开启参数 log_slow_queries，默认阈值为10秒，可通过参数long_query_time设置 undo log作用：为保障事务原子性（Atomicity），在事务失败时，进行rollback。原理：在begain/start transaction前进行备份。 redo log实例失败时，如：掉电，mysql存储引擎会应用redo log复原到掉电时刻。 Mysql主从复制相干文章（他人写的）：Mysql主从复制，主主复制后续我也会本人补一篇对于binlog主从复制的应用。
Mysql体系结构连接池、服务与工具治理、SQL接口、查问剖析组件，优化器，缓存/缓冲、插入式引擎、物理文件。 Mysql 引擎仅比拟支流的MyISAM和InnoDBInnoDB: 反对事务，外键，行级锁，反对裸设施(row disk)建设表空间，事务默认隔离级别为REAPTABLE，应用next-key lock算法来防止幻读(Phantom)，提供插入缓冲(insert buffer)，二次写(double write)，自适应哈希索引(adaptive hash index)，预读(read head)等。不反对FullText索引，不保留具体行数。表中的数据采纳汇集(clustered)形式存储，即按主键程序寄存，若没有显示申明主键，则主动生成6字节的ROWID作为主键。注：一张表只能有一个汇集索引，能够有多个非汇集索引。MyISAM: 反对FullText索引；只缓存索引，不缓存数据不反对事务，表锁MyISAM实用于多读少写；Innodb实用于与事务，高并发。附上一个比拟： MYSQL连贯应用TCP/IP套接字连贯:mysql -h 192.168.0.1 -u rookie -p则会连贯ip为192.168.0.1下的mysql实例。rookie为用户名。 缓冲innodb基于磁盘存储，升高io与cpu的差距，引入了缓冲池（一块内存区域）。缓冲池次要蕴含：数据页，索引页，undo页，插入缓冲，锁信息，自适应哈希索引等。结构图如下：当初的innodb引擎反对设置多个缓冲池，该字段为innodb_buffer_pool_instance默认为1。 CheckPoint技术次要是用户在宕机时，疾速复原，不必重做所有的日志，只需复原checkpoint后的日志进行复原。checkpoint的作用就是把脏页刷回磁盘。（脏页：即缓冲池中批改过的数据页） 两种刷回策略Sharp CheckPoint：数据库敞开时全副刷回，默认开启。Fuzzy CheckPoint：局部刷回。 fuzzy：毛茸茸的，含糊的。innoDB要害个性插入缓冲，两次写，自适应哈希索引，异步IO，刷新临界脏页。这里只是简略介绍，如果有须要，能够去查看《MYSQL技术底细 第2版》原书第2章第6大节。 插入缓存应用条件： 索引是非汇集索引索引不惟一两次写通过保留一个页的正本，在写入生效(如宕机)时，先重页的副原本复原，再进行重做日志。进步数据页的可靠性。 自适应哈希索引(AHI:Adaptive Hash Index)定义：主动察看，若建设哈希能提供性能，则建设哈希索引。InnoDB会主动依据拜访频率和模式为拜访热点建设哈希索引。 异步IOAIO作用能够进行IO Merge（IO合并），如拜访页(end,start)为:(8,6),(8,7),(8,8)。这3次IO会合并为(8,6)一次IO。 刷新邻近页当刷新一个脏页时，会检测该页所在区(extent)的其余页。如果是脏页，也一起一并刷回。该字段为innodb_flush_neighbors。
前言 明天不整那些花里胡哨、虚头巴脑的前言了，间接进入正题怼起来。 注释 二狗：不多BB，先怼几道常问的大题目。MySQL 的事务隔离级别有哪些？别离用于解决什么问题？ 次要用于解决脏读、不可反复读、幻读。 脏读：一个事务读取到另一个事务还未提交的数据。 不可反复读：在一个事务中屡次读取同一个数据时，后果呈现不统一。 幻读：在一个事务中应用雷同的 SQL 两次读取，第二次读取到了其余事务新插入的行。 不可反复读重视于数据的批改，而幻读重视于数据的插入。 隔离级别 脏读 不可反复读 幻读 读未提交（Read Uncommitted） 有 有 有 读已提交（Read Committed） 无 有 有 可反复读（Repeatable Read） 无 无 有 串行化（Serializable） 无 无 无 二狗：MySQL 的可反复读怎么实现的？ 应用 MVCC 实现的，即 Mutil-Version Concurrency Control，多版本并发管制。对于 MVCC，比拟常见的说法如下，包含《高性能 MySQL》也是这么介绍的。 InnoDB 在每行记录前面保留两个暗藏的列，别离保留了数据行的创立版本号和删除版本号。每开始一个新的事务，零碎版本号都会递增。事务开始时刻的版本号会作为事务的版本号，用来和查问到的每行记录的版本号比照。在可反复读级别下，MVCC是如何操作的： SELECT：必须同时满足以下两个条件，能力查问到。1）只查版本号早于以后版本的数据行；2）行的删除版本要么未定义，要么大于以后事务版本号。 INSERT：为插入的每一行保留以后零碎版本号作为创立版本号。 DELETE：为删除的每一行保留以后零碎版本号作为删除版本号。 UPDATE：插入一条新数据，保留以后零碎版本号作为创立版本号。同时保留以后零碎版本号作为原来的数据行删除版本号。 MVCC 只作用于 RC（Read Committed）和 RR（Repeatable Read）级别，因为 RU（Read Uncommitted）总是读取最新的数据版本，而不是合乎以后事务版本的数据行。而 Serializable 则会对所有读取的行都加锁。这两种级别都不须要 MVCC 的帮忙。 最后我也是深信这个说法的，然而前面发现在某些场景下这个说法其实有点问题。 举个简略的例子来说：如果线程1和线程2先后开启了事务，事务版本号为1和2，如果在线程2开启事务的时候，线程1还未提交事务，则此时线程2的事务是不应该看到线程1的事务批改的内容的。 ...
前言本文讲述mysql锁定义，lock与latch区别，Innodb中的锁，强制开启S/X锁，自增长锁，行锁的3中算法，锁降级等，次要参照来自《MYSQL技术底细 第2版》这本书，以及参杂了本人的一些了解。 锁定义/作用为反对对 共享资源 的并发拜访，保证数据的一致性与完整性，数据库所提供的一种束缚机制。不同的数据库和不同的引擎有着不同实现形式及反对度，可分为行，页，表锁。（MyISAM是表锁，SQL SERVER是行级锁，InnoDB默认也是行级锁） 行锁页锁表锁MyISAM √Innodb√ √BDB √ SQL Server√ √前三个都是MYSQL的不同引擎。 lock与latch(门闩shuan)latch实用于短期锁定的轻量级锁 （门闩不防盗嘛），锁定工夫过长时，性能会十分差。lock的对象是事务，锁定的是数据库中的对象:行、页、表，锁在commit或rollback时才会开释。latch分为mutex(互斥锁)和rwlock(读写锁)，用于保障并发线程操作临界资源(能够了解为共享资源)的正确性。 latch是面向数据库系统底层的，目前没找供用户开发的api，除非DBA，一般开发者根本用不到，理解即可。 locklatch对象事务线程持续时间整个事务临界资源模式行，页，表锁mutex，rwlock保留在lock manager的哈希表中数据结构的对象中mysql innodb中: 查看latch命令: `show engine innodb latch` 查看lock命令: `show engine innodb status`Innodb引擎中的锁innodb反对行锁和表锁（不反对表锁），默认为行锁。两种行级锁: 共享锁(S lock)：容许事务读1行排他锁(X lock)：容许事务删除或更新1行在A取得S锁后，B可获S锁，但不能加X，否则A会阻塞。（读不影响数据）在A取得X锁后，B不能再取得任何锁。（批改必须独占） S锁能够了解为读锁，X锁能够了解为独占锁，且X优先级高于S。Innodb反对多粒度锁定，容许行级、表级上的锁同时存在。（Innodb不反对页级锁）为此推出一种新的锁形式意向锁(Intention Lock):将锁定的对象分为多个档次，以满足事务在更细的细粒度上加锁。 一句话概括就是，对下层对象(页，表)加同样的意向锁(IS/IX)。同样的意思就是记录加S，下层对象就加IS；同理，记录加X，下层对象就加IX。 所有兼容状况：（兼容就是能获取，不兼容就是要期待阻塞）很显著，是对称矩阵哦 一致性非锁定读一致性非锁定读(consistent nolocking read)：指的是在读取被加X锁的行时，不须要期待行锁的开释，会去读它的快照(snapshot)。一个行有多个快照数据，到底读哪一个呢？READ COMMITED是读最新的，READ REPEATEABLE(Innodb默认隔离级别)是读的是本人事务begin前的。（这里的场景是开启两个事务，一个查，一个改，有先后。）READ COMMITD会毁坏隔离性（Isolation） 一致性锁定读--强制开启锁尽管可反复读（默认隔离级别）保障了隔离性，但为了确保数据的一致性，咱们能够显式强制加锁： SELECT ... IN SHARE MODESELECT ... FOR UPDATE下面的in share mode是加的S锁。for update是加的X锁。 自增长锁对字段设置auto_incrment时，自增计算器会保留在表中，锁会在insert语句之后，事务commit之前开释。（非凡的机制，为进步插入性能，没有抉择事务完结再开释，而是Insert后）。前面MYSQL还新增了innodb_autoinc_mode来进一步管制自增长。 注：Innodb下自增列必须是索引，而且是索引的第一列（组合索引状况下），否则抛出异样。MyISAM没有这要求。行锁的3种算法Record Lock :记录锁，锁定本人的记录Gap Lock：沟锁，锁本人左近的范畴，不包含本人记录（开区间）Next-key Lock：范畴+本人，Record+Gap联合（闭区间）Record Lock是锁住索引记录，主键也是索引之一。Next-key是行查问select默认采纳的锁算法，如有索引：10,13,20。则锁区间为:(-∞,10],(10,13],(13,20],(20,+∞)。显知，gap的话就蕴含本人，]变成了)。 next-key这里的next指的就是右区间为闭区间，推理：previous-key指的是左区间闭合。注：主动降级：若索引中含有惟一属性（**惟一**索引），则next-key会主动降级为record-key。（范畴锁——>只锁本人）next-key机制的创造是为了解决Phantom problem(幻像问题)，是的mysql能够不到serializable就能够解决幻读。幻像：一个事务A在未提交时，两次sql的执行的后果的数据条数不一样，B向其中批改的数据被A在第二次读走了。违反了隔离性。脏读: A读到了B中未提交的数据，若B之后回滚了，则读到了脏数据。不可反复读： B对A读到的值进行了批改，A第二次读到的数据值变了。 ...
谬误: 代理抛出异样谬误: java.rmi.server.ExportException: Port already in use: 1099; nested exception is明天在整合SSM框架时，在最初的运行阶段抛出了代理抛出异样谬误: java.rmi.server.ExportException: Port already in use: 1099; nested exception is这样的一个谬误。这是因为1099端口曾经被占用了，集体是找到了两种解决方案 解决计划1（源于https://blog.csdn.net/itmrche...） 运行 cmd 之后输出 netstat -ano 之后会呈现列表，在外面找到错误信息中的端口(这里是1099) 找到这一行后 记下过程号：这里是55512，之后关上工作管理器，进入到详细信息页面，找到pid为55512的过程，选中 点右下角的结束任务即可 执行完以上步骤再重启容器，容器失常启动了，祝贺！==因为我应用这种办法时，在cmd中并未找到1099端口的占用，因而我采纳了计划2== 解决计划1 关上idea的tomcat配置界面 将JMX port改成一个其余的端口，我这里批改成了1100 执行完以上步骤再重启服务器，服务器失常启动了，祝贺！
磕了一个季度的MySQL，竟然被这道SQL题给搞崩了 明天敌人在群里发了一个SQL题，我蒙圈了，半天没思路。我磕了整个Q2的MySQL，看各种索引优化、MVCC、锁、B+树，此时心里就只有”花里胡哨，心里没点B树？“ 题目： 有一张表b字段包含：用户id，年，月，请查问在2020年每个月都有记录的用户id?且不探讨有没有什么场景会用到这样的一张表以及其合理性。请用SQL实现上边的问题 我看到的时候，第一个想到的就是用group by select user_id from b where year=2020 group by user_id having count(month)=12;没有建表测试，本人也不确定对不对 依照平时写业务代码的思维，获取每个月都有哪些用户id，而后取交加，发现走不通啊【手动捂脸】 那就一步一步来，如果晓得一个SQL执行每一步的过程是什么样的，那还会有难写的SQL？ 之前整顿过一篇SQL执行原理的文章，有趣味的能够看一下：SQL查问执行程序详解 创立一个测试表，构造如下： CREATE TABLE `b` ( `user_id` int(11) NOT NULL, `month` int(10) DEFAULT NULL, `year` int(10) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=latin1;而后造一些测试数据： insert into b values(1,1,2020),(1,2,2020),(1,3,2020),(1,4,2020),(1,5,2020),(1,6,2020),(1,7,2020),(1,8,2020),(1,9,2020),(1,10,2020),(1,11,2020),(1,12,2020),(1,1,2020);insert into b values(2,1,2020),(2,2,2020),(2,3,2020),(2,4,2020),(2,5,2020),(2,6,2020),(2,7,2020),(2,8,2020),(2,9,2020),(2,10,2020),(2,11,2020),(2,12,2020);insert into b values(6,1,2020),(6,2,2020),(6,3,2020),(6,4,2020),(6,5,2020),(6,6,2020),(6,7,2020),(6,8,2020),(6,9,2020),(6,10,2020),(6,11,2020),(6,12,2020);insert into b values(10,1,2020),(10,2,2020),(10,3,2020),(10,4,2020),(10,5,2020),(10,6,2020),(10,7,2020),(10,8,2020),(10,9,2020),(10,10,2020),(10,11,2020),(10,12,2020);insert into b values(25,1,2020),(25,2,2020),(25,3,2020),(25,4,2020),(25,5,2020),(25,6,2020),(25,7,2020),(25,8,2020),(25,9,2020),(25,10,2020),(25,11,2020),(25,12,2020);insert into b values(66,1,2020),(66,2,2020),(66,6,2020),(66,4,2020),(66,5,2020),(66,6,2020),(66,7,2020),(66,8,2020),(66,9,2020),(66,10,2020),(66,11,2020),(66,12,2020);insert into b values(7,1,2020),(7,2,2020),(7,4,2020),(7,5,2020),(7,7,2020),(7,9,2020),(7,10,2020),(7,11,2020);insert into b values(12,1,2020),(12,4,2020),(12,5,2020),(12,7,2020),(12,9,2020),(12,10,2020),(12,11,2020);insert into b values(12,1,2019),(12,4,2019),(12,5,2019),(12,7,2019),(12,9,2019),(12,10,2019),(12,11,2019);第一步，先从简略的开始，我就先查进去year为2020的，然而，因为可能会有反复数据，所以我顺便去重 ...
当应用 MySQL 导入较大文件时，会呈现 MySQL server has gone away 的问题，是因为默认的 max_allowed_packet 变量值过小。应用如下命令查看： show VARIABLES like '%max_allowed_packet%';发现默认值为：4194304（也就是4M） 长期批改应用如下命令能够长期批改该参数，MySQL 重启后会复原至默认值： SET GLOBAL max_allowed_packet = 500 * 1024 * 1024;永恒批改能够批改 MySQL 的配置来实现，减少如下： max_allowed_packet = 500M
概念相当于java中的办法，将一组逻辑语句封装在办法体中，对外裸露办法名1）暗藏了实现细节 2）进步代码的可重用性 应用select 函数名(实参列表)【from 表】 【】中内容可省略 注释想要理解更多Java架构技术的，能够关注我一下，我后续也会整顿更多对于架构技术这一块的知识点分享进去，外面会分享一些：spring，MyBatis，Netty源码剖析，高并发、高性能、分布式、微服务架构的原理，JVM性能优化，并发编程这些成为架构师必备的常识体系。更多交换形式和获取微我：xuanwo013 字符函数：length： 获取字节个数（utf-8 一个汉字为3个字节，gbk为2个字节）SELECT LENGTH('cbuc') # 输入 4SELECT LENGTH('蔡不菜cbuc') # 输入13复制代码concat： 拼接字符串 SELECT CONCAT('C','_','BUC') # 输入 C_BUC复制代码upper：将字母变成大写 SELECT UPPER('cbuc') # 输入 CBUC复制代码lower：将字母变成小写 SELECT LOWER('CBUC') # 输入 cbuc复制代码substr / substring：裁剪字符串该办法进行了重构 substr(str,pos) # str:要裁剪的字符串 ， pos:要裁剪的长度substr(str,pos,len) # str:要裁剪的字符串 , pos/len:从哪个地位开始裁剪几位# substring同理复制代码instr：返回子串第一次呈现的索引，如果没有则返回0 SELECT INSTR('蔡不菜','蔡') # 输入 1 （mysql是从1开始算位数）复制代码trim：字符串去【字符】 SELECT TRIM(' cbuc ') # 输入 cbucSELECT TRIM('a' from 'aaaacbucaaaa') #输入 cbuc复制代码lpad：用指定字符实现左填充指定长度 SELECT LPAD('cbuc',6,'*') # 输入 **cbuc复制代码rpad：用指定字符实现右填充指定长度 SELECT LPAD('cbuc',6,'*') # 输入 cbuc**复制代码replace 替换SELECT REPLACE('小菜爱睡觉','睡觉','吃饭') # 输入 小菜爱吃饭复制代码数学函数round：四舍五入 SELECT round(1.5) # 输入 2SELECT round(-1.5) # 输入 -2 该四舍五入计算形式为：绝对值四舍五入加负号复制代码ceil：向上取整,返回>=该参数的最小整数 SELECT CEIL(1.5); # 输入 2SELECT CEIL(-1.5); # 输入 -1复制代码floor：向下取整，返回<=该参数的最大整数 SELECT FLOOR(1.5); # 输入 1SELECT FLOOR(-1.5); # 输入 -2复制代码truncate：截断SELECT TRUNCATE(3.1415926,2); # 输入 3.14复制代码mod：取余 SELECT MOD(10,3); # 输入 1SELECT MOD(10,-3); # 输入 1复制代码日期函数now：返回以后零碎日期+工夫SELECT NOW() # 输入 2020-02-16 11:43:21复制代码curdate：返回以后零碎日期，不蕴含工夫SELECT CURDATE() # 输入 2020-02-16复制代码curtime：返回以后工夫，不蕴含日期 SELECT CURTIME() # 输入 11:45:35复制代码year/month/day 能够获取指定的局部，年、月、日、小时、分钟、秒SELECT YEAR(NOW()) # 输入 2020 其余用法统一复制代码str_to_date：将字符通过指定的格局转换成日期SELECT STR_TO_DATE('02-17 2020','%c-%d %Y') # 输入 2020-02-17复制代码date_format：将日期转换成字符 SELECT DATE_FORMAT(NOW(),'%Y年%m月%d日') # 输入 2020年02月17日复制代码datediff：两个日期天数之差 SELECT DATEDIFF(NOW(),'2020-02-12') # 输入 5复制代码其余函数VERSION：查看mysql 版本SELECT VERSION(); # 输入 5.7.17复制代码DATABASE：查看以后数据库 SELECT DATABASE() # 输入 cbuc_datebase复制代码USER：查看以后用户 SELECT USER() # 输入 root@localhost复制代码流程管制函数if 函数： 相似三目运算 SELECT IF(10<5,'大','小') # 输入 小复制代码case函数：case 有两种用法1 switch case 的成果 ...
mysql依照数组排序 SELECT*FROM`msp_article`WHERE idIN ( 140, 141, 148)ORDERBY field( id, 141, 148, 140)LIMIT 0 , 30
装置MacOS Windows 10 Centos 7 SQL标准不辨别大小写，然而倡议大写关键词，小写表名、列名每条SQL倡议分号结尾每条SQL依据须要进行换行缩进正文：单行：# --多行：/* */类型数值： 整型： 小数： 定点数 浮点数字符： 短字符: char varcahr 长文本: text blob日期： date 2020-02-03 datetime 2020-02-02 02:02:02 timesiamp 1594279093389 time 02:02:02 year 2020罕用SQLuse test; -- 选中 数据库show tables; -- 事实以后选中的库的所有表show tables from mysql; # 查问mysql下的tablesSHOW INDEX FROM stuinfo; # 显示以后的索引select database(); # 查看以后库/* create table table1( id int, name varchar(24)); */desc table1; -- 查看表构造select * from table1;insert into table1 (id,name) values(1,'测试'); -- 插入update table1 set name='我靠' where name='ces'; -- 批改update table1 set id=0 where name='我靠'; -- 批改delete from table1 where name='我靠'; -- 删除常见函数单行函数解决字符函数SELECT LENGTH('我是谁'); -- 依据以后字符集 失去以后字节长度SELECT CONCAT('我','是','谁呀'); -- 拼接字符串SELECT UPPER('Abc'); -- 转换成大写字符SELECT LOWER('Abc'); -- 转换成小写SELECT SUBSTR('abc123一二三',4,3); -- 从4开始截取3个 蕴含4 索引从1开始SELECT SUBSTRING('abc123一二三',4,3); -- 从4开始截取3个 蕴含4 索引从1开始SELECT INSTR('01234556','234'); -- 查找字符串呈现的地位 没找到就是0SELECT TRIM(' A B C D '); -- 去除前后空格SELECT TRIM('a' FROM 'aaaaA B CaaaDaaaa' ); -- 去除前后的aSELECT LPAD('abc123一二三',20,'*'); -- 左填充/保留右边的SELECT RPAD('abc123一二三',20,'*'); -- 右填充/保留右边的数学函数SELECT ROUND(0.4); -- 四舍五入SELECT ROUND(0.5); -- 四舍五入SELECT ROUND(-0.4); -- 四舍五入SELECT ROUND(-0.5); -- 四舍五入SELECT CEIL(0.2); -- 向上取整SELECT FLOOR(0.9); -- 向下取整SELECT RAND(); -- 随机数SELECT TRUNCATE(0.2345,3); -- 保留多少位小数 不进行解决SELECT MOD(10,3); -- 取余日期函数SELECT NOW(); -- 返回以后的日期工夫SELECT CURDATE(); -- 返回以后的日期SELECT CURTIME(); -- 返回以后工夫SELECT YEAR(NOW()) as `year`, MONTH(NOW()) as `month`, DAY(NOW()) as date as `day`; -- 年/月/日SELECT STR_TO_DATE('2020-03-23 22:32:12','%Y-%m-%d %H:%i:%s'); -- 将字符串解析成工夫SELECT DATE_FORMAT(NOW(),'%Y-%m-%d %H:%i:%s'); -- 格式化工夫其余函数SELECT VERSION(); -- 查看版本号SELECT DATABASE(); -- 查看以后的库 SELECT USER(); -- 以后用户流程管制函数SELECT IF(10<5,'大','小'); -- ifSELECT `last_name`, IF(`commission_pct` IS NULL,TRUE,FALSE) AS isPct from `employees` ORDER BY `isPct` DESC; -- if 例子# case SELECT `salary`,`department_id`,CASE department_id WHEN 80 THEN salary * 1.2 WHEN 40 THEN salary * 1.9 ELSE salary * 0END AS newMoney FROM `employees`ORDER BY department_id DESC;统计函数统计SELECT COUNT(*) FROM `employees`; -- 数量统计SELECT SUM(`salary`) FROM `employees`; -- 相加和SELECT AVG(`salary`) FROM `employees`; -- 平均值SELECT MAX(`salary`) FROM `employees`; -- 最大值SELECT MIN(`salary`) FROM `employees`; -- 最小值SELECT COUNT(*) AS `count`, SUM(`salary`) AS `sum`, AVG(`salary`) AS `avg`, MAX(`salary`) as `max`, MIN(`salary`) as `min`FROM `employees`;# 留神/**/常见束缚一种限度，用于限度表中的数据，用来保障表中数据的精确和可靠性分类： 六大束缚： NOT NULL: 非空,用于保障该字段的值不能为空 DEFAULT: 默认值 PRIMARY KEY: 主键，用于保障该字段具备唯一性（非空） UNIQUE: 惟一（可空） CHECK: 查看 （mysql 不反对） FOREIGN KEY: 外键，用于限度两个表的关系，用于保障该字段必须来自关联表的主键 增加束缚的机会： 1. 创立表 2. 批改表 束缚的增加分类： 列级束缚： 六大束缚语法上都反对，外键束缚有效 表级束缚： 除了非空和默认其余都反对 主键和惟一的区别： 主键：惟一、非空、只能一个 惟一：惟一、可空、多个外键： 1. 从表设置外键关系 2. 主从表类型统一/兼容 3. 主表关联键个别为主键或惟一 4. 必须对应主表数据，删除先删除从表再删除主表DQL 数据查询语言常量、表达式、函数SELECT 1; -- 常量值SELECT 10*20; -- 表达式SELECT VERSION(); -- 函数别名SELECT 1+2 as number;去重SELECT DISTINCT `name`FROM `table`+号SELECT 1+2; -- 数字相加SELECT 1+'123'; -- 字符串会强转成数字非数字转为0SELECT 1 + Null; -- 与Null返回Null字符串连贯 concatSELECT CONCAT('a','b','c'); -- 字符串拼接SELECT CONCAT(`first_name`,`last_name`) as `name` FROM `employees`; -- 拼接字段条件查问条件表达式< > >= <= != <> <=># 等于SELECT CONCAT(`first_name`,`last_name`) as `name` FROM `employees` WHERE `first_name`='Bruce'; # 平安等于 可查 NullSELECT CONCAT(`first_name`,`last_name`) as `name` FROM `employees` WHERE `first_name`<=>'Bruce'; # 大于SELECT *FROM `employees` WHERE `department_id` > 60;# 小于SELECT *FROM `employees` WHERE `department_id` <= 60; # 不等于 # != 不倡议SELECT *FROM `employees` WHERE `department_id` <> 60;逻辑表达式&& || ! AND OR NOT# 且查问# 不倡议 &&SELECT CONCAT(`first_name`,`last_name`) as `name`FROM `employees` WHERE `first_name`='Bruce' AND `last_name`='Ernst';# 或SELECT CONCAT(`first_name`,`last_name`) as `name` FROM `employees` WHERE `first_name`='Bruce' OR `last_name`='K_ing'; # 非SELECT CONCAT(`first_name`,`last_name`) as `name` FROM `employees` WHERE NOT `first_name`='Bruce' 含糊查问like 含糊查问%：任意多个字符_: 任意单个字符\: 本义 ...
苍茫大地一剑尽挽破,何处热闹笙歌落。 难道这普天之下尽没我包容之处？为何这面试官总要与我争风绝对，这不是难为人吗？ 每次经验痛领悟，总会悠然南山下，单独愁楚。记录心中一片大海，唯有单独致力，方能实现行业之豪杰。 前言一个阳光明媚得上午，迎面走来了一位身着银白银白得的红色衬衣，灰白色相间的休闲短裤，还给我露出了彰显男儿本色的彩色腿毛。手拿银色金属质感得 MacBook Pro，外加一双小白鞋。看着那稀少的发量，在灯光的照耀下，甚至还有点反光。透过那慌慌张张的脸色。 我心田不忍一颤，明天怕是要遇到人了。整整一个架构师的大叔呀。 果不其然，他手里拿着我的简历，疾速的扫了一下，而后用眼角余光看了一下我，像是在掂量着什么。上来闭口即问。 <span style="color:#773098;font-weight:bold;"> 事务简介和原理<span style="color:#773098;font-weight:bold;">面试官: 看你简历形容精通 Mysql 优化办法，那你先说说你对Mysql的事务的理解吧。 我吒吒辉心田单独平静了一些，这个不难，就是一个事务定义和执行得调用的办法。殊不知前面的内容都是对我得严刑拷打。<span style="color:#773098;font-weight:bold;">吒吒辉： 好呢，数据库的事务是指一组sql语句拜访各种数据项的数据库逻辑处理单元，在这组 SQL 操作中，要么全副执行胜利，要么全副执行失败。 举个经典的例子就是转账，事务 A 要进行转账，那么，转出的账号要扣钱，转入的账号要加钱，这两操作要么同时执行胜利，要么都全副执行失败。为得就是确保数据的一致性。 <span style="color:#773098;font-weight:bold;">吒吒辉： 个别申请进入MySQL，都是依据不同申请类型先在服务层外部做数据处理，而后在由后盾线程把数据刷到磁盘中。从而防止频繁的读写磁盘。 答复结束后，本人笑笑的模一下头发，打理一下本人的发型，整顿了一下脖子上得领结。 面试官：吆喝，这小子还有点料啊，敢在太岁头上动土，你还要反了天呀你。还在这欺侮我头上的没头发。个小崽子。<span style="color:#773098;font-weight:bold;">面试官： 刚你提到了 MySQL 外部的工作，那你晓得事务的个性吗？ 数据是如何刷盘？ 服务层数据如何批改？ 这样得工作模式有什么益处？ 怎么防止频繁写入? 读申请和写申请在服务层工作是一样的吗？ 我心田一钝，这老秃驴，不要命啦，逮到这么问我。看来本人得正经点，感觉空气中弥漫着杀气腾腾的气味。难堪的冲他笑了一笑。立马说到 <span style="color:#773098;font-weight:bold;">吒吒辉： 在Mysql中事务的四大个性次要蕴含： 原子性（Atomicity） 一致性（Consistent） 隔离性（Isalotion） 持久性(Durable)，简称为 ACID。由它们独特来保证数据的一致性。 <span style="color:#773098;font-weight:bold;">面试官： 嗯，说说你对这四大特的了解？ <span style="color:#773098;font-weight:bold;"> 吒吒辉： 原子性： 故名思议原子是最小的元素单位，所以一个事务是一个不可拆分的最小工作单元。整个事务操作要么全副执行胜利，要么全副失败回滚。因执行胜利和失败都是状态的变动，如果只有执行成果只有一半，数据就会不残缺，从而违反了原子的特点。 一致性： 指数据库从一个一致性的状态转换到另外一个一致性的状态。要保障事务前后的状态要统一隔离性：一个事务所做的批改在最终提交以前，对其余事务是不可见的。持久性：一旦事务提交，则其所做的批改就会永恒保留到磁盘上。是执行后果始终失效。 <span style="color:#773098;font-weight:bold;">面试官： 那数据库底层是如何实现这四大个性的？ 登时,我望向他那张有点清淡的大脸，可他的确气定神闲的望着我，面相是如许的随和。此刻心田一直策马崩腾，第一个问题就给我整了这么多，前面可咋办？ 心田感觉悬了<span style="color:#773098;font-weight:bold;"> 吒吒辉： 原子性：由 undo log 保障，也称回滚日志，次要用于记录数据被批改前的信息undo log 次要记录数据的逻辑变动。所以须要将之前的操作都记录下来，而后在产生谬误或执行事务回滚时能力保障回滚到之前的操作。 <span style="color:#773098;font-weight:bold;">面试官： 那这个申请过程是怎么实现的？ 比方当写申请为 insert 操作进入到数据库服务层时，会先在缓冲池上面的写缓冲中进行数据的写入，而后先把写申请的物理语言写入到 read log中，并记录一条删除以后语句的逻辑语句到 undo log中。 最初由 MySQL 后盾线程定期刷新数据到磁盘外面。 ...
mysql数据实时同步到Elasticsearch注意事项网上有很多推荐mysql数据同步到Elasticsearch的方案，有大哥canal，python的mysqlsmom，还有go的go-mysql-elasticsearch，在我自己项目中用的是go-mysql-elasticsearch，给大家随便讲讲我遇到的一些小问题，先看看官方提到的注意事项。 ES的版本关于ES的版本官方支持为 MySQL supported version < 8.0ES supported version < 6.0我的ES当前版本在7.7.0下运行依然运行成功 开启mysql binlog日志，且必须为ROW格式通常配置文件都是在mysql的my.cnf，不知道在哪的可以用whereis my.cnf找到，然后把binlog_format配置 cat /etc/my.cnf|grep binlog_format修改成ROW，重启！ show variables like '%binlog_format%' mysqldump没有同步成功数据执行./bin/go-mysql-elasticsearch -config=./etc/river.toml命令开始后首先会执行mysqldump同步数据，如果info提示 [2020/07/08 10:40:23] [info] dump.go:180 skip dump, use last binlog replication pos (mysql-bin.000004, 4) or GTID set <nil>说明mysqldump同步并没有成功，很有可能是因为./etc/river.toml里的data_dir指定的目录没有删除，这时候去删除这个目录里面的master.info，再次运行可以看到已经dump成功,参考这个https://github.com/siddontang/go-mysql-elasticsearch/issues/115
Liquibase 是什么粘一段官方的解释 Track, version, and deploy database changes跟踪、管理和应用数据库变化 说白了，就是一个将你的数据库脚本转化为xml格式保存起来。 其中包含了你对数据库的改变，以及数据库的版本信息，方便数据的升级和回滚等操作。 目前支持：MySql、Maria DB、PostgreSQL、Oracle、SQL Server、DB2、HSQL、H2、SQLite等多种主流数据库。 2.为什么需要Liquibase通常在项目正常推进的情况下，我们会有：开发、测试、压测、准生产、生产等多套环境。 伴随着迭代发版，我们需要不断同步多套环境的数据库信息，如果每个环境都需要开发人员手动去修改，那么就是一场灾难。 因为到最后，谁也记不得在哪个环境执行了哪个操作，结果就是测试一直在群里@开发，报错啦！！ 因此我们需要一个可以自动化维护各个环境数据库版本差异的工具，将人力释放出来。 这也是程序的奥义，简化繁琐的操作 3.配置Liquibase环境1.下载Liquibase根据自己的操作系统下载对应的二进制包，下载地址：https://www.liquibase.org/dow... 2.配置环境变量配置方式同 Java ，将压缩文件解压，配置文件夹路径到PATH路径中。 以 Mac 为例，文件夹路径为：/Users/jiaotd/liquibase-4.0.0-beta1 修改 ~/.bash_profile 文件，添加如下配置 export LIQUIBASE_HOME=/Users/jiaotd/liquibase-4.0.0-beta1export PATH=$PATH:$LIQUIBASE_HOME执行 source ~/.bash_profile 加载环境变量 执行 liquibase --version 检查时候配置成功 4.Liquibase支持的集成方式Liquibase 支持集成的方式有多种 Command 命令行模式MavenAntSpring BootMaven 与 Spring Boot 类似，这里先介绍一下 Command 模式，Maven 与 Spring Boot 集成在以后的文章中再做详细介绍。 5.核心文件不管哪种集成方式，Liquibase 最为核心的文件就是 changeSet.xml，它记录了你对数据库的每一步操作，Liquibase 所以的操作都依赖于 changeSet.xml 文件的内容。 空的 changeSet.xml 内容如下： <?xml version="1.0" encoding="utf-8"?><databaseChangeLog xmlns="http://www.liquibase.org/xml/ns/dbchangelog" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.4.xsd"></databaseChangeLog>Liquibase 将每一步的数据库操作定义为一个 changeSet，格式如下: ...
MySQL的CPU达到100%情况复盘解析一、业务场景及问题描述网站总注册用户大约6W，平时日活比较低，但是在活动的时候，高并发问题相继出现，当前，有一次活动，主要是给固定的6位idol进行投票应援，高并发问题也随之而来。 最直接的问题就是，在用户投票的时候突然不能投票，中间mysql大约宕机了8min左右。 二、网站服务背景描述web服务：两台web服务器，均是8核16G一台mysql5.6服务器，4核8G，mysql最大连接数1500一台memcache和一台redis2.8三、问题描述分析mysql的cpu瞬间打满到100%（平时最高不超过40%），mysql的qps瞬间达到1w（平时不超过500），磁盘使用和占用空间变小，慢查询次数极大增加 web服务器的负载瞬间增大到5.7，平时不到1 nginx报错：出现大量的time out和too many open files错误19743#0: accept4() failed (24: Too many open files) 分析：（1）nginx出现time out，说明nginx连接后端服务器比如php的时候，后端处理时间太长，导致出现超时（2）出现超时问题，有两种通常的解决方式：一种就是分析后端为什么处理时间太长，一般是mysql慢查询问题，一种就是直接更改nginx超时时间，设置更长的超时时间。（3）出现too many files ,文件数打开太多，一方面是网站流量太大，一方面可以查看服务器的max open files，可以通过ulimit -a命令查看最大打开文件数是10w。（4）出现too many files，可以重新设置最大打开文件数，具体可以参考：https://learnku.com/articles/... 项目报错：出现大量的mysql连接错误Next yiidbException: SQLSTATE[HY000] [2002] 连接超时 in /srv/www/vendor/yiisoft/yii2/db/Connection.php:624 Stack trace: #0 /srv/www/vendor/yiisoft/yii2/db/Connection.php(996): yiidbConnection->open() #1 /srv/www/vendor/yiisoft/yii2/db/Connection.php(983): yiidbConnection->getMasterPdo() #2 /srv/www/vendor/yiisoft/yii2/db/Command.php(253): yiidbConnection->getSlavePdo() #3 /srv/www/vendor/yiisoft/yii2/db/Command.php(1143): yiidbCommand->prepare(true) #4 /srv/www/vendor/yiisoft/yii2/db/Command.php(425): yiidbCommand->queryInternal('fetchColumn', 0) #5 /srv/www/vendor/yiisoft/yii2/db/Query.php(463): yiidbCommand->queryScalar() #6 /srv/www/vendor/yiisoft/yii2/db/ActiveQuery.php(340): yiidbQuery->queryScalar('COUNT(*)', Object(yiidbConnection)) 分析： （1）由此可以看到，整个服务的根源在于mysql出现了问题，一般可能就是瞬间的高并发，慢查询sql增多，导致mysql直接宕机，整个服务崩溃 （2）可以看到，mysql的qps瞬间增大，cpu直接打满，mysql慢查询次数增加 （3）解决方式，主要是优化慢查询sql，具体可以参考：https://my.oschina.net/sundas... 四、优化对有慢查询sql进行索引等优化,这是最主要的，一般出现cpu打满都是这个原因在业务逻辑上进行优化，尽量避免复杂sql，拆分成查询率更高的短sql慢查询逻辑中，能用redis的，尽量使用redisshow processlist查看当前正在执行的sql，看到有几条有异常的sql语句，发现从表中查询的数据过大，赶紧kill掉这些正在运行的语句
集群配置canal-admin的安装不再累述，可翻看之前文章，本文主要记录canal-admin集群环境的配置和踩坑记录 新建集群填写zk的集群信息修改集群配置参数 主要参数说明canal持久化数据到zookeeper上的更新频率canal.file.flush.period = 1000 memory store RingBuffer size, should be Math.pow(2,n)canal.instance.memory.buffer.size = 16384 memory store RingBuffer used memory unit size , default 1kbcanal.instance.memory.buffer.memunit = 1024 meory store gets mode used MEMSIZE or ITEMSIZEcanal.instance.memory.batch.mode = MEMSIZEcanal.instance.memory.rawEntry = true 是否开启心跳检查canal.instance.detecting.enable = false 心跳检查sqlcanal.instance.detecting.sql = select 1 心跳检查频率canal.instance.detecting.interval.time = 3 心跳检查失败重试次数canal.instance.detecting.retry.threshold = 3 心跳检查失败后，是否开启自动mysql自动切换，mysql主从地址可在具体instance中配置canal.instance.detecting.heartbeatHaEnable = false support maximum transaction size, more than the size of the transaction will be cut into multiple transactions deliverycanal.instance.transaction.size = 1024 ...
MySQL数据库简介MySQL近两年一直稳居第二，随时有可能超过Oracle计晋升为第一名，因为MySQL的性能一直在被优化，同时安全机制也是逐渐成熟，更重要的是开源免费的。 MySQL是一种关系数据库管理系统，关系数据库将数据保存在不同的表中，而不是将所有数据放在一个大仓库内，这样就增加了速度并提高了灵活性。 MySQL所使用的 SQL 语言是用于访问数据库的最常用标准化语言。MySQL 软件采用了双授权政策，分为社区版和商业版，由于其体积小、速度快、总体拥有成本低，尤其是开放源码这一特点，一般中小型网站的开发都选择 MySQL 作为网站数据库。 如果不会安装MySQL请移步：MySQL服务安装 MySQL InnoDB存储引擎存储引擎InnoDB是目前MySQL版本默认的存储引擎，也是MySQL推荐使用的存储引擎，是集高可靠性和高性能于一身的存储引擎。在MySQL5.7版本中，除非在配置文件中显视指定default storage engine或者创建表时显视使用engine=语句指定其它的存储引擎，否则默认都是InnoDB。InnoDB存储引擎的优势： DML语句支持事务功能，保证ACID特性行级锁的使用保证了高并发的属性InnoDB对有主键的表会依据主键优化查询性能，也称聚簇索引，将所有数据存储在聚簇索引上以减少对主键查询的IO消耗为保证数据的一致性，InnoDB还支持外键属性，确保有外键约束的表之间不会有不一致的数据当服务器硬件或者软件故障导致MySQL重启后，InnoDB会自动识别已经在故障之前提交的数据，并回退所有故障时未提交的数据，最大限度的保护数据不会丢失(crash recovery)1、事物（Transaction） 2、MVCC（多版本并发控制） 3、行级锁（Row-level Lock） 4、支持外键 5、ACSR（Auto Crrash safe Recovery）自动的故障安全恢复 6、支持热备份MySQL复制集群原理与实战MySQL复制有两种方法：传统方式：基于主库的bin-log将日志事件和事件位置复制到从库，从库再加以 应用来达到主从同步的目的。Gtid方式：global transaction identifiers是基于事务来复制数据，因此也就不 依赖日志文件位置，同时又能更好的保证主从库数据一致性。MySQL数据库主从同步实战过程 MySQL 主从同步架构中你不知道的“坑”（上） MySQL 主从同步架构中你不知道的“坑”（下） 数据备份多种方式：物理备份是指通过拷贝数据库文件的方式完成备份，这种备份方式适用于数据库很大，数据重要且需要快速恢复的数据库逻辑备份是指通过备份数据库的逻辑结构（create database/table语句)和数据内容（insert语句或者文本文件）的方式完成备份。这种备份方式适用于数据库不是很大，或者你需要对导出的文件做一定的修改，又或者是希望在另外的不同类型服务器上重新建立此数据库的情况通常情况下物理备份的速度要快于逻辑备份，另外物理备份的备份和恢复粒度范围为整个数据库或者是单个文件。对单表是否有恢复能力取决于存储引擎，比如在MyISAM存储引擎下每个表对应了独立的文件，可以单独恢复；但对于InnoDB存储引擎表来说，可能每个表示对应了独立的文件，也可能表使用了共享数据文件物理备份通常要求在数据库关闭的情况下执行，但如果是在数据库运行情况下执行，则要求备份期间数据库不能修改逻辑备份的速度要慢于物理备份，是因为逻辑备份需要访问数据库并将内容转化成逻辑备份需要的格式；通常输出的备份文件大小也要比物理备份大；另外逻辑备份也不包含数据库的配置文件和日志文件内容；备份和恢复的粒度可以是所有数据库，也可以是单个数据库，也可以是单个表；逻辑备份需要再数据库运行的状态下执行；它的执行工具可以是mysqldump或者是select … into outfile两种方式送你一份生产数据库备份方案：高逼格企业级MySQL数据库备份方案 MySQL数据库物理备份方式：Xtrabackup实现数据的备份与恢复 MySQL复制有多种类型：异步复制：一个主库，一个或多个从库，数据异步同步到从库。同步复制：在MySQL Cluster中特有的复制方式。半同步复制：在异步复制的基础上，确保任何一个主库上的事务在提交之前至 少有一个从库已经收到该事务并日志记录下来。延迟复制：在异步复制的基础上，人为设定主库和从库的数据同步延迟时间， 即保证数据延迟至少是这个参数。MySQL主从复制延迟解决方案：高可用数据库主从复制延时的解决方案 MySQL高可用架构设计与实战先来了解一下MySQL高可用架构简介：浅谈MySQL集群高可用架构 MySQL高可用方案：MySQL 同步复制及高可用方案总结 官方也提供一种高可用方案：官方工具｜MySQL Router 高可用原理与实战 MHAMHA（Master High Availability）目前在MySQL高可用方面是一个相对成熟的解决方案，该软件由两部分组成：MHA Manager（管理节点）和MHA Node（数据节点。MHA Manager: 可以单独部署在一台独立的机器上管理多个master-slave集群，也可以部署在一台slave节点上。MHA Node: 行在每台MySQL服务器上。MHA Manager会定时探测集群中的master节点，当master出现故障时，它可以自动将最新数据的slave提升为新的master，然后将所有其他的slave重新指向新的master。整个故障转移过程对应用程序完全透明。MHA高可用方案实战：MySQL集群高可用架构之MHA MGRMysql Group Replication(MGR)是从5.7.17版本开始发布的一个全新的高可用和高扩张的MySQL集群服务。高一致性，基于原生复制及paxos协议的组复制技术，以插件方式提供一致数据安全保证；高容错性，大多数服务正常就可继续工作，自动不同节点检测资源征用冲突，按顺序优先处理，内置动防脑裂机制；高扩展性，自动添加移除节点，并更新组信息；高灵活性，单主模式和多主模式。单主模式自动选主，所有更新操作在主进行；多主模式，所有server同时更新。MySQL性能优化史上最全的MySQL高性能优化实战总结！ MySQL索引原理：MySQL 的索引是什么？怎么优化？ 顾名思义，B-tree索引使用B-tree的数据结构存储数据，不同的存储引擎以不同的方式使用B-Tree索引，比如MyISAM使用前缀压缩技术使得索引空间更小，而InnoDB则按照原数据格式存储，且MyISAM索引在索引中记录了对应数据的物理位置，而InnoDB则在索引中记录了对应的主键数值。B-Tree通常意味着所有的值都是按顺序存储，并且每个叶子页到根的距离相同。B-Tree索引驱使存储引擎不再通过全表扫描获取数据，而是从索引的根节点开始查找，在根节点和中间节点都存放了指向下层节点的指针，通过比较节点页的值和要查找值可以找到合适的指针进入下层子节点，直到最下层的叶子节点，最终的结果就是要么找到对应的值，要么找不到对应的值。整个B-tree树的深度和表的大小直接相关。全键值匹配：和索引中的所有列都进行匹配，比如查找姓名为zhang san，出生于1982-1-1的人匹配最左前缀：和索引中的最左边的列进行匹配，比如查找所有姓为zhang的人匹配列前缀：匹配索引最左边列的开头部分，比如查找所有以z开头的姓名的人匹配范围值：匹配索引列的范围区域值，比如查找姓在li和wang之间的人精确匹配左边列并范围匹配右边的列：比如查找所有姓为Zhang，且名字以K开头的人只访问索引的查询：查询结果完全可以通过索引获得，也叫做覆盖索引，比如查找所有姓为zhang的人的姓名MySQL表分区介绍：一文彻底搞懂MySQL分区 可以允许在⼀个表⾥存储更多的数据，突破磁盘限制或者⽂件系统限制。对于从表⾥将过期或历史的数据移除在表分区很容易实现，只要将对应的分区移除即可。对某些查询和修改语句来说，可以⾃动将数据范围缩⼩到⼀个或⼏个表分区上，优化语句执⾏效率。⽽且可以通过显示指定表分区来执⾏语句，⽐如 select * from temp partition(p1,p2) where store_id < 5;表分区是将⼀个表的数据按照⼀定的规则⽔平划分为不同的逻辑块，并分别进⾏物理存储，这个规则就叫做分区函数，可以有不同的分区规则。MySQL5.7版本可以通过show plugins语句查看当前MySQL是否⽀持表分区功能。MySQL8.0版本移除了show plugins⾥对partition的显示，但社区版本的表分区功能是默认开启的。但当表中含有主键或唯⼀键时，则每个被⽤作分区函数的字段必须是表中唯⼀键和主键的全部或⼀部分，否则就⽆法创建分区表。MySQL分库分表能不分就不分，1000万以内的表，不建议分片，通过合适的索引，读写分离等方式，可以很好的解决性能问题。分片数量尽量少，分片尽量均匀分布在多个DataHost上，因为一个查询SQL跨分片越多，则总体性能越差，虽然要好于所有数据在一个分片的结果，只在必要的时候进 行扩容，增加分片数量。分片规则需要慎重选择，分片规则的选择，需要考虑数据的增长模式，数据的访 问模式，分片关联性问题，以及分片扩容问题，最近的分片策略为范围分片，枚举分片， 一致性Hash分片，这几种分片都有利于扩容。尽量不要在一个事务中的SQL跨越多个分片，分布式事务一直是个不好处理的问题。查询条件尽量优化，尽量避免Select * 的方式，大量数据结果集下，会消耗大量 带宽和CPU资源，查询尽量避免返回大量结果集，并且尽量为频繁使用的查询语句建立索引。数据库分库分表概述：数据库分库分表，何时分？怎样分？ ...
MySQL逻辑架构 - SQL语句的执行都经历了哪些步骤从 MySQL 架构来理解，我们可以把 MySQL 拆解成几个零件，如下图所示 大体来说，MySQL 可以分为 Server层 和 存储引擎层两部分。 Server 层包括连接器、查询缓存、分析器、优化器、执行器，涵盖MySQL的大多数核心服务功能，以及所有的内置函数（如日期、时间、数学和加密函数等），所有跨存储引擎的功能也在这一层实现，包括 存储过程、触发器、视图等。 存储引擎层负责数据的存储和提取。包括 MySQL 常见的存储引擎，包括 MyISAM、InnoDB 和 Memory 等，最常用的是 InnoDB，也是现在 MySQL 的默认存储引擎。存储引擎也可以在创建表的时候手动指定，使用如下语句： CREATE TABLE t (i INT) ENGINE = <Storage Engine>;不同存储引擎的表数据存取方式不同，支持的功能也不同。从图中可以看出，不同的存储引擎共用一个Server层，也就是从连接器到执行器的部分。 连接器首先需要在 MySQL 客户端登陆才能使用，所以需要一个连接器来连接用户和 MySQL 数据库，我们一般是使用 mysql -h<host> -P<port> -u<用户名> -p[<密码>]来进行 MySQL 登陆，和服务端建立连接。 虽然密码可以直接跟在 -p 后面写在命令行中，但这样可能会导致你的密码泄露。强烈建议不输入密码，直接运行命令，然后再再交互对话框中输入密码。在完成 TCP 握手 后，连接器会根据你输入的用户名和密码验证你的登录身份。如果用户名或者密码错误，MySQL 就会提示 Access denied for user，然后客户端程序结束执行。如果用户名密码认证通过，连接器会到权限表里面查出你拥有的权限。之后，这个连接里面的权限判断逻辑，都将依赖于此时读到的权限。 这就意味着，一个用户成功建立连接后，即使你用管理员账号对这个用户的权限做了修改，也不会影响已经存在连接的权限。修改完成后，只有再新建的连接才会使用新的权限设置。连接完成后，如果没有后续的动作，这个连接就处于空闲状态，可以使用 show processlist 命令中看到它。 mysql> show processlist;+--------+-------------+---------------------+--------+---------+------+----------+------------------+| Id | User | Host | db | Command | Time | State | Info |+--------+-------------+---------------------+--------+---------+------+----------+------------------+| 214416 | master | 124.126.130.4:29734 | db_name | Sleep | 13 | | NULL || 214417 | master | 124.126.130.4:29754 | db_name | Query | 0 | starting | show processlist |+--------+-------------+---------------------+--------+---------+------+----------+------------------+2 rows in set (0.07 sec)其中的Command列显示为 Sleep 的这一行，就表示现在系统里面有一个空闲连接。客户端如果太长时间没动静，连接器就会自动将它断开。这个时间是由参数 wait_timeout 控制的，默认值是8小时。 ...
场景业务中有一个日志表，插入数据与同步数据查询强依赖于主键的有序性 数据同步时携带上次同步更新的主键，查询到Max(id)之间的数据，只同步增量部分 意味数据只有一次同步机会 表结构如下 问题Mysql 数据隔离级别是RR 讲道理，两个不同的事务来读取数据后一个事务 B 是无法读取的到先一个事务 A 数据的数据 但是我们这里有个逻辑很特殊Max(id) 这里有两个场景： A先读，B后写假设目前数据最后一条记录是3 A事务携带上次同步主键2来请求，Max(id) = 3 这时B事务前来写数据 A事务先于B事务，根据 Mysql MVCC 视图，查询到了id=2，id=3的数据 同时B事务，写入数据id=4，id=5 下次数据同步携带id=3，继续走，业务无误 图示： A先写，B后读假设目前数据最后一条记录是3 A事务开始写入4，5 事务还没有结束时，B携带2来读 期望同步2~3之间的数据，实际上却读到了2~5，但是时间上可读的数据还是1 2 3 这是为什么呢？ 这就牵扯到了InnoDB 主键自增的规则了 InnoDB 的每个表的自增主键都，保存在内存中偏移量：auto_increment_offset 步长：auto_increment_increment 默认双1 插入数据时获取该值，+后写入1 这个值不受MVCC事务一致性保护！！ 测试方法 事务1:BEGIN;INSERT INTO t1 VALUES(null,1);事务2：SELECT max(id) from t1事务1先通过Begin开始事务，后续每执行一次 insert 插入，事务2都能获取到最新的ID 就种情况就是对业务有损了！！ 4 5 两条数据就被永远的丢失了，阿西吧！！！ 天知道我查了多久啊！！！ 图示： 解决方案1.间隙锁在数据同步查询前加上select max(1) from table for update;增加间隙锁 阻塞读写，但是会有性能瓶颈 注意：间隙锁的范围要控制好 BadCase ...
研发反馈问题，数据库中datetime数据类型存储的值末尾会因四舍五入出现不一致数据，影响查询结果，比如：程序中自动获取带毫秒精度的日期'2019-03-05 01:53:55.63'，存入数据库后变成'2019-03-05 01:53:56’。 抛出问题： 具体情况看例子： mysql> create table t(id int,dt datetime); Query OK, 0 rows affected (0.00 sec) mysql> insert into t values(1,'2019-03-05 01:53:55.63'); Query OK, 1 row affected (0.00 sec) mysql> select * from t; +------+---------------------+ | id | dt | +------+---------------------+ | 1 | 2019-03-05 01:53:56 | +------+---------------------+ 1 row in set (0.00 sec) 问题好理解，数据库自动对毫秒精度进行了四舍五入，取了个近似值。 解决问题： 问题也好解决：1.修改字段类型，给datetime加上精度，改成datetime(2)，这样就把后面的毫秒精度存进数据库了，也不会出现查询时数值错误；2.如果毫秒精度实际意义不大，可以在程序中截断毫秒值，存入数据库的值直接精确到秒，这样数据库层面不需要修改。 mysql> create table t_m(id int,dt datetime(2)); Query OK, 0 rows affected (0.00 sec) ...
一、具体报错信息：mysqldump: Error 2013: Lost connection to MySQL server during query when dumping table xxx at row: 258609 <!--more--> 二、原因：网上看了下，大概说在mysqldump的时候，因为数据量太大，一次性接收不了那么多数据，所以server端发送过来的数据会积压在内存等待发送，这个等待时间就是net_write_timeout的时间。当超过了该时间，则会断开mysqldump的连接，同时抛出错误：error 2013: Lost connection。 三、解决方式：详细讲解可点击
都说“大隐隐于市，高手在深宫”。突如其来的“摆地摊”风潮，让原本冷清的街道热闹非凡，也让众人发现了那些神龙见首不见尾的大神们。 这不，小毛在下班的途中就遇到了大神“菊长”。一位专治数据库技术相关疑难杂症的专家，无论是你再数据库运维中踩过的雷、躺过的坑，他定能专业的角度帮你答疑解惑。不信，你看！菊长1分钟帮助小毛解决了MySQL临时表空间难题。 点击关注，第一时间了解华为云新鲜技术~
前言： 在使用MySQL的过程中，你可能会遇到时区相关问题，比如说时间显示错误、时区不是东八区、程序取得的时间和数据库存储的时间不一致等等问题。其实，这些问题都与数据库时区设置有关，本篇文章将从数据库参数入手，逐步介绍时区相关内容。 1.log_timestamps参数介绍首先说明下log_timestamps参数并不影响时区，只是设置不同会影响某些日志记录的时间。该参数主要是控制 error log、slow log、genera log 日志文件中的显示时间，但不会影响 general log 和 slow log 写到表 (mysql.general_log, mysql.slow_log) 中的显示时间。 log_timestamps是全局参数，可动态修改，默认使用UTC时区，这样会使得日志中记录的时间比北京时间慢8个小时，导致查看日志不方便。可以修改为SYSTEM变成使用系统时区。下面简单测试下该参数的作用及修改方法： # 查看参数值mysql> show global variables like 'log_timestamps';+----------------+-------+| Variable_name | Value |+----------------+-------+| log_timestamps | UTC |+----------------+-------+1 row in set (0.00 sec)# 产生慢日志mysql> select sleep(10),now();+-----------+---------------------+| sleep(10) | now() |+-----------+---------------------+| 0 | 2020-06-24 17:12:40 |+-----------+---------------------+1 row in set (10.00 sec)# 慢日志文件记录内容 发现时间是UTC时间# Time: 2020-06-24T09:12:50.555348Z# User@Host: root[root] @ localhost [] Id: 10# Query_time: 10.000354 Lock_time: 0.000000 Rows_sent: 1 Rows_examined: 1SET timestamp=1592989960;select sleep(10),now();# 修改参数值 再次测试mysql> set global log_timestamps = SYSTEM;Query OK, 0 rows affected (0.00 sec)mysql> select sleep(10),now();+-----------+---------------------+| sleep(10) | now() |+-----------+---------------------+| 0 | 2020-06-24 17:13:44 |+-----------+---------------------+1 row in set (10.00 sec)# 慢日志文件记录内容 时间是对的# Time: 2020-06-24T17:13:54.514413+08:00# User@Host: root[root] @ localhost [] Id: 10# Query_time: 10.000214 Lock_time: 0.000000 Rows_sent: 1 Rows_examined: 1SET timestamp=1592990024;select sleep(10),now();2.time_zone参数介绍time_zone参数用来设置每个连接会话的时区，该参数分为全局和会话级别，可以动态修改。默认值为SYSTEM，此时使用的是全局参数system_time_zone的值，而system_time_zone默认继承自当前系统的时区，即默认情况下MySQL时区和系统时区相同。 ...
1. Explain Analyze 介绍Explain 是我们常用的查询分析工具，可以对查询语句的执行方式进行评估，给出很多有用的线索。 但他仅仅是评估，不是实际的执行情况，比如结果中的 rows，可能和实际结果相差甚大。 Explain Analyze 是 MySQL 8 中提供的新工具，牛X之处在于可以给出实际执行情况。 Explain Analyze 是一个查询性能分析工具，可以详细的显示出 查询语句执行过程中，都在哪儿花费了多少时间。 Explain Analyze 会做出查询计划，并且会实际执行，以测量出查询计划中各个关键点的实际指标，例如耗时、条数，最后详细的打印出来。 2. 实践效果例如有如下一条查询语句： SELECT first_name, last_name, SUM(amount) AS totalFROM staff INNER JOIN payment ON staff.staff_id = payment.staff_id AND payment_date LIKE '2005-08%'GROUP BY first_name, last_name;现在对它执行 Explain Analyze，只需要添加在 SELECT 前边就行了： EXPLAIN ANALYZESELECT first_name, last_name, SUM(amount) AS totalFROM staff INNER JOIN payment ON staff.staff_id = payment.staff_id AND payment_date LIKE '2005-08%'GROUP BY first_name, last_name;执行结果： ...
1、安装MySQLsudo apt-get install mysql-server sudo apt-get install mysql-client sudo apt-get install libmysqlclient-dev2、更改默认密码查看默认配置文件，结果如下图sudo cat /etc/mysql/debian.cnf 图有‘user=debian-sys-maint’，即为自动配置的默认用户；‘password=ol9uVJAxu9L1AzOa’，即为自动配置的密码。以默认配置登陆mysqlmysql -u debian-sys-maint -p // 用户名以自己的配置文件为准提示输入密码，这里要输入的就是上一步的‘password=ol9uVJAxu9L1AzOa’（密码以自己的配置文件为准）。更改密码use mysql; // 下一行，密码改为了yourpassword，可以设置成其他的 update mysql.user set authentication_string=password('yourpassword') where user='root' and Host ='localhost'; update user set plugin="mysql_native_password"; flush privileges; quit;重启MySQL服务sudo service mysql restart mysql -u root -p 新密码 3、配置远程访问编辑配置文件，注释掉bind-address = 127.0.0.1：sudo vi /etc/mysql/mysql.conf.d/mysqld.cnf进入mysql服务，执行授权命令grant all on *.* to root@'%' identified by '你的密码' with grant option; flush privileges;4、重启服务sudo service mysql restart现在可在Navicat软件上远程连接MySQL数据库。
1.选择表中的若干字段db.teacher.find({},{_id:0,name:1})select name from teacher;2.查询满足条件的元组(1)比较大小db.teacher.find({salary : {$gt : 5000}})select * from teacher where salary > 5000;db.teacher.find({salary : {$gte : 5000}})select * from teacher where salary >=5000;db.teacher.find({salary : {$lt : 5000}})select * from teacher where salary < 5000;db.teacher.find({salary : {$lte : 5000}})select * from teacher where salary <= 5000;db.teacher.find({salary : {$ne : 5000}})select * from teacher where salary != 5000;(2)多重条件查询db.teacher.find({salary : {$lt :10000, $gt : 5000}})select * from teacher where salary>5000 AND salary<10000;db.teacher.find({salary: {$gt:5000}, $or: [{birthday: "1997-01-01"},{name: "大明"}]})select * from teacher where salary>5000 AND (birthday = "1997-01-01" OR name = "大明");(3)确定集合db.teacher.find({birthday:{$in:["1997-01-01","2006-01-01"]}})select * from teacher where birthday in ("1997-01-01","2006-01-01"); db.teacher.find({birthday:{$nin:["2007-01-01","2006-01-01"]}})select * from teacher where birthday not in ("2007-01-01","2006-01-01");(4)字符匹配db.teacher.find({name:/大/})select * from teacher where name like "%大%";db.teacher.find({name:/^小/})select * from teacher where name like "大%";db.teacher.find({name:{$not:/^明/}})select * from teacher where name not like "明%";(5)涉及空值的查询db.teacher.find({salary:null})select * from teacher where salary=null;
由于数据表太多，想搞ER图来看看，方便以后维护一、环境Windows操作系统，MySQL 5.7 二、使用工具Navicat 三、前提知识1.什么是主键Primary key，唯一标识一个实体，取值非空唯一。比如，一个人的身份证号。 2.什么是外键Foreign key， 本关系表中的属性的属性值需要参照另外一个表中主键的属性值而存在，可为空也可不为空。 四、实例目前有一个名为shopping的数据库，里面有如下几张表：category：种类表goods ：商品表images： 图片表trolley： 购物车users：用户表users_address：收货地址 1.首先理清这几张表的联系补充说明： 绿色框为各表主键红色箭头表示每两张表之间的主键与外键相对应（从本表外键指向其他表的主键）。3.我只给出了部分主要值。比如 trolley 表中的 users_id 指向 users 表中的users_id 。 设置外键以用户表和收货地址表为例， 这两个表中，外键为收货地址表中的 users_id， 所以在收货地址表建立外键。 2.1 收货地址表 users_address 单击，右键 —>设计表，用户表 users 以同样方法打开。 2.2 检查以下要素（该部分划重点！！！大部分情况下添加外键失败是某个条件没满足导致的~） users_id 在users 表中是否设为主键，users_address 中是否有users_id 这一项(即 是否两表中的项相互对应)？两表中的 users_id 的类型是否设置成一样的？两表中的 users_id 的长度是否设置成一样的？两表中的 users_id 是否设置成无符号？ 2.3 如果检查完无误，记得先保存，然后刷新一下（表 右键 —>刷新）。建议：刷新后先关掉表再重新打开表。 2.4 users_address 表点击 外键，添加外键（这时参考模式会自动变成数据库名），字段选择之前确立好的外键 users_id, 然后确定。参考表选 主键为 users_id 的表，即 users 表。参考字段即主键users_id 。“删除时，更新时” 这两项一般不用管，然后点击保存。 ...
作者：一起web编程链接：www.toutiao.com/i6825148720728769028 点赞功能是目前app开发基本的功能 今天我们就来聊聊 点赞、评论、收藏等这些场景的db数据库设计问题。 1. 我们先来看看场景的需求：显示点赞数量判断用户是否点过赞，用于去重，必须的判断显示个人点赞列表，一般在用户中心显示文章点赞列表我们先看一下头条和微博的例子 这两个都是具有顶级流量的，后端肯定有复杂的架构，我们今天只谈大众化的方案。 方案2.1 mysql方案mysql方案, 随着nosql的流行，大数据的持续热点，但是mysql仍然不可替代，对于大多数的中小项目，低于千万级的数据量，采用mysql分表+cache，是完全可以胜任的，而且稳定性是其他方案无可比拟的： -- 文章表create table post { post_id int(11) NOT NULL AUTO_INCREMENT, ...... star_num int(11) COMMENT '点赞数量'}-- 用户表create table user { user_id int(11) NOT NULL AUTO_INCREMENT, ...... star_num int(11) COMMENT '点赞数量'}-- 点赞表create table star { id int(11) NOT NULL AUTO_INCREMENT, post_id, user_id, ......}常用的查询：查询用户点赞过的文章 select post_id from star where user_id=? 查询文章的点赞用户 select user_id from star where post_id=? ...
1.创建数据库 create database ssm; //mysqluse analysis //mongodb2.切换数据库 use ssm; //mysqluse analysis //mongodb3.删除数据库 drop database ssm; //mysqldb.dropDatabase() //mongodb4.数据定义与数据操纵 //mysqlcreate table `teacher`( `id` int, `name` varchar(20), `salary` decimal(7,2), `birthday` date, `timestamp` timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, primary key (`id`));alter table `teacher` drop `birthday`;alter table `teacher` add `birthday` date;drop table `teacher`;insert into `teacher` ( `id`,`name`,`salary`,`birthday` ) values ( 1,"大明",50000.00,"1997-01-01");update `teacher` set `salary`=5000 where `name`="大明"; delete from `teacher` where `name`="大明";//mongodb.createCollection("student")db.student.drop()db.student.insert({ name: '小明', birthday:"2007-01-01", scholarship: true, success:{"math":80,"data analysis":90}, tags: ['男', '2016级', '清华'], timestamp:parseInt(new Date().getTime()/1000)})db.student.update({name:'小明'},{$set:{name:'小元'}})db.student.remove({name:'小元'})
MySQL 都有哪些零件？连接器：管理连接，权限验证。分析器：词法分析，语法分析。优化器：执行计划生成，索引选择。执行器：操作存储引擎，返回结果。存储引擎：存储数据，提供读写接口。连接器第一步，我们会先连接到 MySQL 数据库，此时就是连接上连接器。连接器负责和客户建立连接，获取权限，维持和管理连接。 mysql -h $ip -u root -p查询缓存建立好连接之后，我们就可以使用 SELECT 语句了，执行逻辑就会来到第二步：查询缓存。MySQL 会现在查询缓存看看之前是不是执行过这条语句，如果有就直接返回。在 MySQL 8.0 之后，此模块已被移除。 分析器如果没有查询缓存，从这里 MySQL 就要开始分析我们要干什么，需要对我们编写 SQL 语句进行分析。分析器会先做词法分析，识别出字符串以及它代表的含义。然后再进行语法分析，判断我们编写的 SQL 语句有没有错误，如果有错误就会抛出错误。 优化器经过了分析器之后，MySQL 知道你要干什么了，此时优化器会根据表结构以及语句目的来决定使用哪个方案。 执行器MySQL 通过分析器知道了我们要做什么，通过优化器知道了该怎么做效率最高。于是就可以进入执行器，真正执行 SQL 语句了。 select * from users where name = ‘operator'假设 users 表中，name 字段上没有建立索引，那么执行器调用 InnoDB 引擎接口取第一行，判断 name 是不是等于 operator，如不是则跳过，如果是就放在结果集中。然后再调用引擎接口取下一行，重复相同的逻辑判断，直到取到这个表的最后一行。最后将结果集返回给客户端。