MySQL数据库分享三

1. 视图

• 定义：视图(view)是一个虚拟表，视图中的内容是真实表数据的查询结果

• 本质：根据SQL语句获取动态的数据集，并为其命名，用户使用时只需使用名称可获取结果集，可以将该结果集当做表来使用

• 描述：使用视图我们可以把查询过程中的临时表摘出来，用视图去实现，这样以后再想操作该临时表的数据时就无需重写复杂的sql了，直接去视图中查找即可，但视图有明显地效率问题，并且视图是存放在数据库中的，如果我们程序中使用的sql过分依赖数据库中的视图，即强耦合，那就意味着扩展sql极为不便，因此并不推荐使用。视图中数据的任何增删改操作都会影响原始表中的数据，此操作危险性比较大，所以，不建议使用视图

• 优点：

  • 可以隐藏非公开的和关键性字段的数据，灵活的开放指定字段的数据

  • 不用每次都重写子查询的sql语句，节省sql语句的代码量，可读性比较高

• 缺点：

  • 视图的效率低，没有子查询效率高

  • 视图中的数据是依赖数据库中的数据，针对视图做增删改的操作会影响正式的数据，安全性不可靠

• 创建视图：

create view 视图名称 as sql查询语句;

• 修改视图：

alter view 视图名称 as sql查询语句;

• 查看视图：

show create view 视图名称;select * from information_schema.views;

• 使用视图：

select * from 视图名称;

• 删除视图：

drop view 视图名称;

2. 触发器

• 定义：触发器(trigger)定制用户对表进行【增、删、改】操作时前后的行为(监视某种情况，并触发某种操作)

• 触发器的创建语法四要素：

  • 监视地点(table)

  • 监视事件(insert/update/delete)

  • 触发时间(after/before)

  • 触发事件(insert/update/delete)

• 触发时间：BEFORE/AFTER，任选其一；BEFORE表示前置触发，AFTER表示后置触发

• 监视事件：INSERT/UPDATE/DELETE，任选其一；INSERT表示执行新增语句时触发，UPDATE表示执行修改语句时触发，DELETE表示执行删除语句时触发

• 触发中动态获取参数：

  • NEW：表示即将插入的数据行(insert/update)

  • OLD：表示即将删除的数据行(update/delete)

• 创建触发器：

create trigger 触发器名称 触发时间(before/after) 触发事件(insert/update/delete) on 表名 for each rowbegin    # 触发执行的sql语句;end

• 查看触发器：

show triggers;

• 删除触发器：

drop trigger 触发器名称;show create trigger 触发器名称;

3. 存储过程

• 定义：存储过程(PROCEDURE)为了完成某个数据库中的特定功能而编写的语句集合，该语句集包括SQL语句(增删改查)、条件语句和循环语句等，类似于程序中的函数(方法)

• 说明：存储过程包含了一系列可执行的sql语句，存储过程存放于MySQL中，通过调用它的名字可以执行其内部的一堆sql；MySQL数据库在5.0版本后开始支持存储过程

• 优点：

  • 用于替代程序写的SQL语句，实现程序与sql解耦

  • 增强了SQL语言灵活性

  • 减少网络流量，降低网络负载

  • 基于网络传输，传别名的数据量小，而直接传sql数据量大

  • 存储过程只在创造时进行编译，以后每次执行存储过程都不需再重新编译，而一般SQL语句每执行一次就编译一次,所以使用存储过程可提高数据库执行速度

  • 系统管理员通过设定某一存储过程的权限实现对相应的数据的访问权限的限制，避免了非授权用户对数据的访问，保证了数据的安全

• 缺点：不利于扩展

• 创建存储过程：

  • 无参数：

  create procedure 存储过程名称()begin    # 调用存储过程运行的sql语句;end

  • 有参数(存储过程可以接收的参数有三种方式)：

    • in：传入参数

    • out：返回值

    • inout：既可以当作参数传入又可以当作返回值

  create procedure 存储过程名称(参数方式,参数名,参数类型)begin    # 调用存储过程运行的sql语句;    select 参数名 # 调用时输出返回值(如果不指定select,也可以在外部调用时select)end

• 调用存储过程：

  • 无参数调用：

  call 存储过程名称();

  • 带参数(只有in)调用：

  call 存储过程名称(参数值);

  • 带参数(in/out/inout)调用：

  set @变量名1 = 变量值1;set @变量名2 = 变量值2;call 存储过程名称(参数值1,参数值2,@变量名1,@变量名2);

  • 返回值调用：

  select 变量名;

• 查看存储过程：

show procedure status;show create procedure 存储过程名称;

• 删除存储过程：

drop procedure 存储过程名称;

4. 函数

• 定义：函数(FUNCTION)是封装一些功能逻辑集合，用于sql中的应用

• 注意：

  • 函数中不能写sql语句，会报错

  • 函数仅仅是一个功能，是一个在sql中应用的功能

  • 需要在begin...end中写sql实现功能，需要使用存储过程

• 创建函数：

create function 函数名(参数名1 参数1类型,参数名2 参数2类型)returns 返回值类型;begin    # 实现功能的函数体;    return 返回值;end

• 调用函数：

  • 直接调用：

  select 函数名(参数值1,参数值2);

  • 在sql语句中调用：

  select 函数名(参数值1,参数值2) from 表名 where 字段 条件判断;

• 查看函数：

show function status;show create funcion 函数名;

• 删除函数：

drop function 函数名;

• MySQL常见函数：

  • length(str)：返回str所占的字节数

  • char_length(str)：返回str中的字符数

  • concat(str1,str2...)：字符串拼接，如果有一个参数为NULL，则返回值就是NULL

  • concat_kw(Connector,str1,str2...)：根据指定分隔符进行字符串拼接，空字符串也会被拼接，如果有参数为NULL，则NULL会被忽略

  • conv(str,O,N)：进制转换，把str从O进制转换位N进制

  • format(X,D)：数字格式化，以字符串形式返回。把X的格式转为人民币的格式，保留D位小数，如果D为0，则结果不带小数位

  • insert(str,pos,len,newstr)：在str的指定位置插入新字符串。pos表示要替换的起始位置，len表示要替换的长度，newstr表示新字符串。如果pos超过原str的长度，则返回str；如果len超过原str的长度，则由新str完全替换

  • instr(str,substr)：返回字符串中第一个子字符串出现的位置

  • left(str,len)：返回字符串str 从开始的len位置的子序列字符

  • upper(str)：转换为大写

  • lower(str)：转换为小写

  • ltrim(str)：删除字符串左边的空格

  • rtrim(str)：删除字符串右边的空格

  • substring(str,pos,len)：获取字符串的子序列

  • locate(substr,str,pos)：获取子序列索引位置

  • repeat(str,count)：返回一个由重复的字符串str组成的字符串，字符串str的数目等于count。count<=0，返回空字符串，str或count为NULL，则返回NULL

  • replace(str,old,new)：把字符串中的old替换为new

  • reverse(str,len)：从字符串str开始，返回从后边开始len个字符组成的子序列

  • space(n)：返回一个由N空格组成的字符串

• MySQL常用函数：date_format(日期格式化函数)

date_format(日期字符串，日期格式)--%a：缩写星期名%b：缩写月名%c：月，数值%D：带有英文前缀的月中的天%d：月的天，数值(00-31)%e：月的天，数值(0-31)%f：微秒%H：小时(00-23)%h：小时(01-12)%I：小时(01-12)%i：分钟，数值(00-59)%j：年的天(001-366)%k：小时(0-23)%l：小时(1-12)%M：月名%m：月，数值(00-12)%p：AM或PM%r：时间，12小时(hh:mm:ss AM或PM)%S：秒(00-59)%s：秒(00-59)%T：时间, 24小时(hh:mm:ss)%U：周(00-53)，星期日是一周的第一天%u：周(00-53)，星期一是一周的第一天%V：周(01-53)，星期日是一周的第一天，与%X使用%v：周(01-53，,星期一是一周的第一天，与%x使用%W：星期名%w：周的天(0=星期日, 6=星期六)%X：年，其中的星期日是周的第一天，4位，与%V使用%x：年，其中的星期一是周的第一天，4位，与%v使用%Y：年，4位%y：年，2位

5. 流程控制

• 变量：

  • 变量定义：DECLARE 变量名 变量类型 [DEFAULT 变量值](定义变量并直接赋值)

  • 变量赋值：SET 变量名 = 变量值

  • 用户变量：以'@'符开头

    • 在MySQL客户端可以直接使用用户变量

    • 在存储过程中可以使用用户变量

    • 在存储过程间可以传递全局范围的用户变量

• 注释：

  • 单行注释：双模杠(--)

  • 多行注释：C风格(/*注释内容*/)

• 条件语句：

  • if：

  if 条件判断 then    执行结果;elseif 条件判断 then    执行结果;else    执行结果;end if;

  • case：

  case    when 判断条件1 then 结果1    when 判断条件2 then 结果2    else 其他条件结果end;

  case 判断名    when 判断值1 then 结果1    when 判断值2 then 结果2    else 其他判断结果end;

• 循环语句：

  • while：

  while 循环条件 do    执行循环体;end while;

  • repeat：

  repeat    执行循环体;    until 循环条件;end repeat;

  • loop：

  loop_name(循环名称): loop    if 判断条件 then        leave loop_name; # 退出循环(类似break)    end if;end loop;

6. 事务

• 定义：事务是用于将某些操作的多个SQL作为原子性操作，一旦有某一个出现错误，即可回滚到原来的状态，从而保证数据库数据完整性

• 描述：事务是应用程序中一系列严密的操作，所有操作必须成功完成，否则在每个操作中所作的所有更改都会被撤消。也就是事务具有原子性，一个事务中的一系列的操作要么全部成功，要么一个都不做。事务的结束有两种，当事务中的所以步骤全部成功执行时，事务提交。如果其中一个步骤失败，将发生回滚操作，撤消撤消之前到事务开始时的所以操作。总结：事务就是一组操作，要么全部完成，要么全部失败

• 特性(ACID)：

  • 原子性(Atomicity)：事务内的操作，要么全部成功,要么全部失败。事务是数据库的逻辑工作单位，事务中包含的各操作要么都做，要么都不做

  • 一致性(Consistency)：事务之前之后,前后数据的一致性。事务执行的结果必须是使数据库从一个一致性状态变到另一个一致性状态。因此当数据库只包含成功事务提交的结果时，就说数据库处于一致性状态　如果数据库系统运行中发生故障，有些事务尚未完成就被迫中断，这些未完成事务对数据库所做的修改有一部分已写入物理数据库，这时数据库就处于一种不正确的状态，或者说是不一致的状态

  • 隔离性(Isolation)：多个事务时，相互不能干扰。一个事务的执行不能被其它事务干扰。即一个事务内部的操作及使用的数据对其它并发事务是隔离的，并发执行的各个事务之间不能互相干扰

  • 持续性(Durability)：也称永久性，一旦事务提交之后，是不可回滚的。指一个事务一旦提交，它对数据库中的数据的改变就应该是永久性的。接下来的其它操作或故障不应该对其执行结果有任何影响

• 事务操作：

  • 开启事务：start transaction

  • 提交事务：commit

  • 回滚事务：rollback

• 注意：

  • select查询语句不牵扯事务处理

  • 只有insert/update/delete语句才能添加到事务中处理

  • 事务执行完之后会暂存在缓存区中，如果不commit，数据不会生效，只有执行了commit才会执行

  • 事务执行完之后发现需要修改或删除，可以使用rollbak进行回滚，也是清空缓存区事务的操作

  • 事务中的sql语句如果有错误的，错误sql之后不会加载到缓存区中，但是之前sql的会加载到缓存区

  • 每次开启事务之前，会先自动提交一次事务，保证缓存区是干净的

  • 不同的表开启不同的事务相互不会影响

• 启动/回滚事务的语法：

start transection;# 需要事务执行的sql语句;rollback;commit;

7. 锁机制

• 描述：Mysql中不同的存储引擎支持不同的锁机制。MySQL各存储引擎使用了三种类型(级别)的锁定机制：表级锁定，行级锁定和页级锁定。

• 表级锁定(table-level)：表级别的锁定是MySQL各存储引擎中最大颗粒度的锁定机制。该锁定机制最大的特点是实现逻辑非常简单，带来的系统负面影响最小。所以获取锁和释放锁的速度很快。由于表级锁一次会将整个表锁定，所以可以很好的避免困扰我们的死锁问题。锁定颗粒度大所带来最大的负面影响就是出现锁定资源争用的概率也会最高，致使并大度大打折扣。使用表级锁定的主要是MyISAM，MEMORY，CSV等一些非事务性存储引擎

• 行级锁定(row-level)：行级锁定最大的特点就是锁定对象的颗粒度很小，也是目前各大数据库管理软件所实现的锁定颗粒度最小的。由于锁定颗粒度很小，所以发生锁定资源争用的概率也最小，能够给予应用程序尽可能大的并发处理能力而提高一些需要高并发应用系统的整体性能。虽然能够在并发处理能力上面有较大的优势，但是行级锁定也因此带来了不少弊端。由于锁定资源的颗粒度很小，所以每次获取锁和释放锁需要做的事情也更多，带来的消耗自然也就更大了。此外，行级锁定也最容易发生死锁。使用行级锁定的主要是InnoDB存储引擎

• 页级锁定(page-level)：页级锁定是MySQL中比较独特的一种锁定级别，在其他数据库管理软件中也并不是太常见。页级锁定的特点是锁定颗粒度介于行级锁定与表级锁之间，所以获取锁定所需要的资源开销，以及所能提供的并发处理能力也同样是介于上面二者之间。另外，页级锁定和行级锁定一样，会发生死锁。在数据库实现资源锁定的过程中，随着锁定资源颗粒度的减小，锁定相同数据量的数据所需要消耗的内存数量是越来越多的，实现算法也会越来越复杂。不过，随着锁定资源颗粒度的减小，应用程序的访问请求遇到锁等待的可能性也会随之降低，系统整体并发度也随之提升。使用页级锁定的主要是BerkeleyDB存储引擎

• 锁特性：

  • 表级锁：开销小，加锁块；不会出现死锁，锁定粒度大，发生锁冲突的概率最高，并发度最低

  • 行级锁：开销大，加锁慢；会出现死锁，锁定粒度最小，发生锁冲突的概率最低，并发性也最高

  • 页面锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般

• 锁的适用性：

  • 表级锁更适合于以查询为主，只有少量按索引条件更新数据的应用，如Web应用

  • 行级锁则更适合于有大量按索引条件并发更新少量不同数据，同时又有并发查询的应用，如一些在线事务处理(OLTP)系统

8. 表锁

• 说明：MyISAM存储引擎使用的锁定机制完全是由MySQL提供的表级锁定实现

• 描述：当一个线程获得对一个表的写锁后，只有持有锁线程可以对表进行更新操作。其他线程的读、写操作都会等待，直到锁被释放为止

• 表锁的两种模式：

  • 表共享锁(Table Read Lock)

  • 表独占写锁(Table Write Lock)

• 特点：

  • 对MyISAM的读操作，不会阻塞其他用户对同一表请求，但会阻塞对同一表的写请求

  • 对MyISAM的写操作，则会阻塞其他用户对同一表的读和写操作

  • MyISAM表的读操作和写操作之间，以及写操作之间是串行的

• 加表锁：MyISAM在执行查询语句select前，会自动给涉及的所有表加读锁，在执行更新操作insert/update/delete前，会自动给涉及的表加写锁。在满足MyISAM表并发插入条件的情况下，允许其他用户在表尾插入记录；给表显式加表锁时，必须同时取得所有涉及表的锁，并且MySQL支持锁升级，锁表后只能访问显式加锁的这些表，不能访问未加锁的表，如果加的是读锁，那么只能执行查询操作，而不能执行更新操作

• 并发锁：MyISAM支持查询和操作的并发进行，MyISAM存储引擎有一个系统变量concurrent_insert，专门用以控制其并发插入的行为，其值分别可以为0、1或2；可以利用MyISAM存储引擎的并发插入特性，来解决应用中对同一表查询和插入锁争用

  • 当concurrent_insert设置为0时，不允许并发插入

  • 当concurrent_insert设置为1时，如果MyISAM允许在一个读表的同时，另一个进程从表尾插入记录，这也是MySQL的默认设置

  • 当concurrent_insert设置为2时，无论MyISAM表中有没有空洞，都允许在表尾插入记录，都允许在表尾并发插入记录

• MyISAM锁调度问题：MyISAM存储引擎的读锁和写锁是互斥的，读写操作室串行的，那么如果读写两个进程同时请求同一张表，Mysql将会使写进程先获得锁。不仅仅如此，即使读请求先到达锁等待队列，写锁后到达，写锁也会先执行。因为mysql因为写请求比读请求更加重要。这也正是MyISAM不适合含有大量更新操作和查询操作应用的原因。调节办法：

  • 通过指定启动参数low-priority-updates，使MyISAM引擎默认给与读请求优先的权限

  • 通过执行set low_PRIORITY_UPDATES=1，降低更新请求的优先级

  • 指定INSERT、UPDATE、DELETE语句的LOW_PRIORITY属性

• MyISAM表锁优化建议：

  • 查询表级锁争用情况

  • 缩短锁定时间

  • 分离能并行的操作

  • 合理利用读写优先级

9. 行锁

• 说明：行级锁定不是MySQL自己实现的锁定方式，而是由其他存储引擎自己所实现的，InnoDB是目前事务型存储引擎中使用最为广泛的存储引擎

• 描述：当并发事务同时访问一个资源时，有可能导致数据不一致，因此需要一种机制来将数据访问顺序化，以保证数据库数据的一致性。MySQL的innodb引擎默认是行锁，只有明确的指定主键，才会执行行锁，否则都是执行表锁

• 行锁的基本类型：读和写。多个事务同时读取一个对象的时候，是不会有冲突的；同时读和写，或者同时写才会产生冲突

• 行锁的模式：

  • 共享锁(Shared Lock，也叫S锁)：共享锁(S)表示对数据进行读操作。因此多个事务可以同时为一个对象加共享锁

  • 排他锁(Exclusive Lock，也叫X锁)：排他锁也叫写锁。表示对数据进行写操作。如果一个事务对对象加了排他锁，其他事务就不能再给它加任何锁了

• MySQL数据库的自动提交：

  • 开启自动提交(默认)：set autocommit = 1;

  • 关闭自动提交：set autocommit = 0;

• 行锁的种类：

  • 悲观锁(Pessimistic Lock)：每次执行查询语句都会上锁，其他查询语句都处于阻塞状态，无法进行查询操作，直到运行完被解锁之后才能释放。使用悲观锁，必须关闭mysql数据库的自动提交属性(默认是开启模式)，因为mysql执行一个操作都会进行提交。启动悲观锁的方式是在事务中的查询语句后加上'for update'。悲观锁的优点是确保了数据的安全性，操作数据时锁定数据不被访问；缺点就是性能不好，效率比较低

  start transection;# 执行查询sql语句 for update;# 执行增删改sql语句操作;commit;

  • 乐观锁(Optimistic Lock)：执行查询语句的时候不会对数据上锁，但是会进行条件的判断。乐观锁用于读操作较多的情况下，可以提高吞吐量。使用乐观锁的两种机制：

    • 使用数据版本记录的机制实现：为数据新增一个版本控制的字段，查询数据时会把版本号一同查出来，数据每被修改一次，版本号会修改一次，提交操作时，需要先判断版本字段是否跟查询的结果一致

    • 使用时间戳更新的机制实现：使用时间戳和版本号的方式类似，判断和修改的条件字段使用时间戳存储，在提交操作之前会先判断时间戳是否跟查询的结果一直

• 查看行锁的争夺情况：

show status like 'InnoDB_row_lock%';-- InnoDB_row_lock_current_waits：当前正在等待锁定的数量-- InnoDB_row_lock_time：从系统启动到现在锁定总时间长度-- InnoDB_row_lock_time_avg：每次等待所花平均时间-- InnoDB_row_lock_time_max：从系统启动到现在等待最常的一次所花的时间-- InnoDB_row_lock_waits：系统启动后到现在总共等待的次数

• InnoDB行锁优化建议：

  • 尽可能让所有的数据检索都通过索引来完成，从而避免InnoDB因为无法通过索引键加锁而升级为表级锁定

  • 合理设计索引，让InnoDB在索引键上面加锁的时候尽可能准确，尽可能的缩小锁定范围，避免造成不必要的锁定而影响其他Query的执行

  • 尽可能减少基于范围的数据检索过滤条件，避免因为间隙锁带来的负面影响而锁定了不该锁定的记录

  • 尽量控制事务的大小，减少锁定的资源量和锁定时间长度

  • 在业务环境允许的情况下，尽量使用较低级别的事务隔离，以减少MySQL因为实现事务隔离级别所带来的附加成本

• 减少死锁概率的建议：

  • 类似业务模块中，尽可能按照相同的访问顺序来访问，防止产生死锁

  • 在同一个事务中，尽可能做到一次锁定所需要的所有资源，减少死锁产生概率

  • 对于非常容易产生死锁的业务部分，可以尝试使用升级锁定颗粒度，通过表级锁定来减少死锁产生的概率

10. 索引

• 产生原理：一般的应用系统，读写比例在10:1左右，而且插入操作和修改、删除操作很少出现性能问题，查询操作占用比较多，还会出现一些复杂的查询操作，这个时候就需要针对查询做一些优化与加速了

• 说明：索引在MySQL中也叫做“键”，是存储引擎用于快速找到记录的一种数据结构。索引对于良好的性能非常关键，尤其是当表中的数据量越来越大时，索引对于性能的影响愈发重要。索引优化应该是对查询性能优化最有效的手段了。索引能够轻易将查询性能提高好几个数量级。索引相当于字典的音序表，如果要查某个字，如果不使用音序表，则需要从几百页中逐页去查。数据库利用各种各样的快速定位技术，能够大大提高查询效率。特别是当数据量非常大，查询涉及多个表时，使用索引往往能使查询速度加快成千上万倍

• 使用索引的误区：索引是应用程序设计和开发的一个重要方面。若索引太多，应用程序的性能可能会受到影响。而索引太少，对查询性能又会产生影响，要找到一个平衡点，这对应用程序的性能至关重要。一些开发人员总是在事后才想起添加索引----我一直认为，这源于一种错误的开发模式。如果知道数据的使用，从一开始就应该在需要处添加索引。开发人员往往对数据库的使用停留在应用的层面，比如编写SQL语句、存储过程之类，他们甚至可能不知道索引的存在，或认为事后让相关DBA加上即可。DBA往往不够了解业务的数据流，而添加索引需要通过监控大量的SQL语句进而从中找到问题，这个步骤所需的时间肯定是远大于初始添加索引所需的时间，并且可能会遗漏一部分的索引。索引也并不是越多越好，如果添加不合适会造成cpu一直过高，可能会直接 把服务器搞宕机，所以，要合理使用索引才能达到真正意义上的性能提升

• 目的：提高查询效率

• 本质：通过不断地缩小想要获取数据的范围来筛选出最终想要的结果，同时把随机的事件变成顺序的事件，也就是说，有了这种索引机制，我们可以总是用同一种查找方式来锁定数据

• 索引的类型：普通索引、主键索引、唯一索引、联合索引、全文索引

• 索引的方式：BTREE和HASH

• primary key、unique、联合唯一也都是索引，这些索引除了加速查找以外，还有约束的功能

• 普通索引：INDEX(加速查找)

• 主键索引PRIMARY KEY：加速查找+约束（不为空、不能重复）

• 唯一索引UNIQUE KEY：加速查找+约束（不能重复）

• 联合索引：

  • PRIMARY KEY(字段1,字段2...)：联合主键索引

  • UNIQUE(字段1,字段2...)：联合唯一索引

  • INDEX(字段1,字段2...)：联合普通索引

• 全文索引(FULL TEXT)：只有MyISAM引擎支持，而且只有 CHAR、VARCHAR、TEXT的类型可以创建全文索引。FULLTEXT索引是按照分词原理建立索引的。西文中，大部分为字母文字，分词可以很方便的按照空格进行分割，而对于中文需要使用mysql中文分词插件，原理是把中文转为拼音之后再进行分词(不推荐使用)

• hash

  • 说明：hash就是一种（key=>value）形式的键值对,允许多个key对应相同的value，但不允许一个key对应多个value,为某一列或几列建立hash索引，就会利用这一列或几列的值通过一定的算法计算出一个hash值，对应一行或几行数据。hash索引的key是有序的，可以一次定位，不需要像树形索引那样逐层查找,因此具有极高的效率

  • 优缺点：查询单条快，范围查询慢

• btree：将索引值按一定的算法，存入一个树形的数据结构中(二叉树)。btree方式也是最常用的索引方式

• 不同的存储引擎支持的索引类型也不一样：

  • InnoDB 支持事务，支持行级别锁定，支持 B-tree、Full-text 等索引，不支持 Hash 索引

  • MyISAM 不支持事务，支持表级别锁定，支持 B-tree、Full-text 等索引，不支持 Hash 索引

  • Memory 不支持事务，支持表级别锁定，支持 B-tree、Hash 等索引，不支持 Full-text 索引

  • NDB 支持事务，支持行级别锁定，支持 Hash 索引，不支持 B-tree、Full-text 等索引

  • Archive 不支持事务，支持表级别锁定，不支持 B-tree、Hash、Full-text 等索引

• MySAM索引文件和数据文件是分离的，索引文件仅保存数据记录的地址。而在innodb中，表数据文件本身就是按照B+Tree（BTree即Balance True）组织的一个索引结构，这棵树的叶节点data域保存了完整的数据记录。这个索引的key是数据表的主键，因此innodb表数据文件本身就是主索引。因为inndob的数据文件要按照主键聚集，所以innodb要求表必须要有主键（Myisam可以没有），如果没有显式定义，则mysql系统会自动选择一个可以唯一标识数据记录的列作为主键，如果不存在这种列，则mysql会自动为innodb表生成一个隐含字段作为主键，这字段的长度为6个字节，类型为长整型

• 联合索引：指对表上的多个列合起来做一个索引。联合索引的创建方法与单个索引的创建方法一样，不同之处在仅在于有多个索引列。使用联合索引的好处是在第一个键相同的情况下，已经对第二个键进行了排序处理

• 覆盖索引：InnoDB存储引擎支持覆盖索引（covering index，或称索引覆盖），即从辅助索引中就可以得到查询记录，而不需要查询聚集索引中的记录。使用覆盖索引的好处是不包含整行记录的所有信息，故其大小要远小于聚集索引，因此可以减少大量的IO操作(覆盖索引技术最早是在InnoDB Plugin中完成并实现，这意味着对于InnoDB版本小于1.0的，或者MySQL数据库版本为5.0以下的，InnoDB存储引擎不支持覆盖索引特性)

11. MySQL中EXPLAIN

• 说明：explain显示了mysql如何使用索引来处理select语句以及连接表，可以帮助选择更好的索引和写出更优化的查询语句

• 注意：mysql explain只能解释select查询，并不会对存储程序调用和insert、update、delete或其他语句做解释。然而，可以重写某些非select查询以利用explain。为了达到这个目的，只需要将该语句转化成一个等价的访问所有相同列的select，任何体积的列都必须在select列表，关联子句，或者where子句中

• 限制：

  • explain不会显示触发器、存储过程或UDF会如何影响查询

  • 不支持存储过程，尽管可以手动抽取查询并单独地对其进行explain操作

  • 不会体现出mysql在执行计划中所做的特定优化

  • 不会显示关于查询的执行计划的所有信息

  • 不区分具有相同名字的事物

  • 查询索引的精准度不高

• 使用方法：直接在sql语句select前面加上explain

• EXPLAIN列的解释：

  • id列：这一列包含一个编号，识别select所属的行，如果在语句当中没有子查询或联合，只会有唯一的select，每一行在这个列中都将显示一个1，否则内层的select语句一般会顺序编号，对应于其在原始语句中的位置。mysql将select查询氛围简单和复杂类型，复杂类型可分为三大类：简单子查询，所谓的派生表（在from子句中的子查询），以及union查询

  • select_type列：一列显示了对应行是简单还是复杂的select（如果是后者，那么是三种复杂类型中的哪一种）。simple值意味着查询不包括子查询和union，如果查询有任何负责的子部分，则最外层部分标记为primary

    • SUBQUERY：包含在select列表中的子查询中的select（换句话说，不在from子句中）标记为SUBQUERY

    • DERIVED：用来表示包含在FROM子句的子查询中的select，mysql会递归执行并将结果放到一个临时表中。服务器内部称其“派生表”，因为该临时表是从子查询中派生来的

    • UNION：在UNION中的第二个和随后的select被标记为unoin，第一个select被标记就好像它以部分外查询来执行。如果union被from子句中的子查询包含，那么它的第一个select会被标记为derived

    • UNION RESULT：用来从union的匿名临时表检索结果的select被标记为UNION RESULT

    • DEPENDENT：表示select依赖于外层查询中发现的数据

    • UNCACHEABLE：表示select中的某些特性阻止结果被缓存于一个Item_cache中

  • table列：这一列显示了对应行正在访问的表

  • type列：显示连接使用的类型。从最好到最差的连接类型为const、eq_ref、ref、range、index和ALL

    • ALL：表示全表扫描，意味着mysql必须扫描整张表，从头到尾，去找到需要的行

    • index：跟全表扫描一样，只是mysql扫描表时按索引次序而不是行，它的主要优点是避免了排序；最大缺点是要承担按索引次序读取整个表的开销。这通常意味着若是按随机次序访问行，开销将非常大。如果在extra列中看到“Using index”，说明mysql正在使用索引覆盖，它只扫描索引的数据，而不是按索引次序的每一行，它比按索引次序全表扫描的开销要少很多

    • range：范围扫描就是一个有限制的索引扫描，它开始与索引里的某一点，返回匹配这个值域的行，这比全索引扫描要好一点，因为它用不着遍历全部索引，显而易见的扫描是带有between或在where子句里带有>的查询。当mysql使用索引去查找一系列值时，例如in()和or列表，也会显示为范围扫描，然而，这两者其实是相当不同的访问类型，在性能上有重要的差异。range扫描的开销跟索引类型的相当

    • ref：这是一种索引访问（有时也叫做索引查找），它返回所有匹配某个单个值的行，然而，它可能会找到多个符合条件的行，因此，它是查找和扫描的混合体，此类索引访问只有当使用非唯一性索引或者唯一性索引的非唯一性前缀时才会发生。把它叫做ref是因为索引要跟某个参考值相比较。这个参考值或者是一个常数，或者是来自多表查询前一个表里的结果值。ref_or_null是ref之上的一个变体，它意味着mysql必须在初次查找的结果里进行第二次查找以找出null条目

    • eq_ref：使用这种索引查找，mysql知道最多只返回一条符合条件的记录，这种访问方法可以在mysql使用主键或者唯一性索引查找时看到，它会将它们与某个参考值做比较。mysql对于这类访问类型的优化做得非常好，因为它知道无需估计匹配行的范围或在找到匹配行后再继续查找

    • const/system：当mysql能对查询的某部分进行优化并将其转换成一个常量时，他就会使用这些访问类型，举例来说，如果你通过将某一行的主键放入where子句里的方式来选取此行的主键，mysql就能把这个查询转换为一个常量，然后就可以高效地将表从联接执行中移除

    • null：这种访问方式意味着mysql能在优化阶段分解查询语句，在执行阶段甚至用不着再访问表或者索引。例如，从一个索引列里选取最小值可以通过单独查找索引来完成，不需要在执行时访问表

  • possible_keys列：这一列显示了查询可以使用的所有索引(可能应用在这张表中的索引)。如果为空，没有可能的索引。可以为相关的域从WHERE语句中选择一个合适的语句。 这是基于查询访问的列和使用的比较操作符来判断的。这个列表是在优化过程的早期创建的，因此有些罗列出来的索引可能对于后续优化过程是没用的。possible_keys揭示了哪一个索引能有助于高效地进行查找

  • key列：这一列显示了mysql决定采用的具体的索引来优化对该表的访问(实际使用的索引)。如果为NULL，则没有使用索引。如果该索引没有出现在possible_keys列中，那么mysql选用它是处于另外的原因，可能选择了一个覆盖索引。key显示的是优化采用哪一个索引可以最小化查询成本

  • key_len列：该列显示了mysql在索引里使用的字节数(使用的索引长度)，如果mysql正在使用的只是索引里的某些列，那么就可以用这个值来算出具体的列，mysql5.5及之前的版本只能使用索引的最左前缀

  • ref列：这一列显示了之前的表在key列记录的索引中查找值所用的列或常量(显示使用索引的列)

  • rows列：这一列是mysql为了找到所需的行而要读取或返回请求数据的行数。这个数字是内嵌循环关联计划里的循环数目，也就是说它不是mysql认为它最终要从表里读取出来的行数，而是mysql为了找到符合查询的每一点上标准的那些行而必须读取的行的平均数。这个数字是mysql认为它要检查的行数，而不是结果集里的行数，根据表的统计信息和索引的选用情况，这一列显示的值可能不精确

  • Extra列：这一列包含的是不适合在其他列显示的额外信息

    • Distinct：mysql一旦找到了与行相联合匹配的行，就不再搜索了

    • Not exists：mysql优化了left join，一旦它找到了匹配LEFT join标准的行，就不再搜索了

    • Using index：表示mysql将使用覆盖索引，以避免访问表

    • Using where：表示mysql服务器将在存储引擎检索行后再进行过滤

    • Using temporary：表示mysql在对查询结果排序时会使用一个临时表

    • Using filesort：表示mysql会对结果使用一个外部索引排序，而不是按索引次序从表里读取行

    • Range checked for each record(index map: N)：表示没有好用的索引，新的索引将在联接的每一行上重新估算，N是显示在possible_keys列中索引的位图，并且是冗余的

12. MySQL的慢查询

• 描述：查询慢的日志记录，指mysql记录所有执行超过long_query_time参数设定的时间阈值的SQL语句的日志.慢查询日志默认是关闭的，要使用慢查询日志功能，首先要开启慢查询日志功能

• 开启慢查询日志：

-- 修改my.cnf/my.ini配置文件的[mysqld]中的选项slow-query-log = 1 # 1或on都代表开启slow-query-log-file=slow.log # 指定日志路径long_query_time=3 # sql执行时间阈值-- 修改之后重启mysqld服务生效

• 慢查询相关查询语句：

show variables like 'slow_query%';show variables like 'long_query_time';

• 慢查询优化的常见步骤参考：

  • 确定是否真的很慢，设置sql模式：SQL_NO_CACHE

  • where条件单表查，锁定最小返回记录表。(把查询语句的where都应用到表中返回的记录数最小的表开始查起，单表每个字段分别查询，看哪个字段的区分度最高)

  • explain查看执行计划，是否与预期一致(从锁定记录较少的表开始查询)

  • order by limit形式的sql语句让排序的表优先查

  • 结合应用场景和业务运营方式

  • 查询索引是否被合理使用与命中

  • 持续观察结果，逐步轮询

13. MySQL日志管理

• 错误日志：记录MySQL服务器启动、关闭及运行错误等信息

• 二进制日志：又称binlog日志，以二进制文件的方式记录数据库中除SELECT以外的操作

• 查询日志: 记录查询的信息

• 慢查询日志：记录执行时间超过指定时间的操作

• 中继日志：备库将主库的二进制日志复制到自己的中继日志中，从而在本地进行重放

• 通用日志：审计哪个账号、在哪个时段、做了哪些事件

• 事务日志或称redo日志：记录Innodb事务相关的如事务执行时间、检查点等

14. 数据库设计三范式：

• 第一范式：确保每列保持原子性

• 第二范式：确保表中的每列都和主键相关

• 第三范式：确保表中每一列都和主键列直接相关,而不是间接相关

15. 数据库表关系：

• 一对一关系(至少一张表)

• 一对多关系(至少两张表)

• 多对多关系(至少三张表)

16. MySQL优化：

• sql语句优化

  • 避免select *，将需要查找的字段列出来

  • 拆分大的delete或insert语句

  • 使用连接(join)来代替子查询

  • 使用limit对查询结果的记录进行限定

• 使用合适的数据类型

  • 使用可存下数据的最小的数据类型

  • 使用简单的数据类型，整型比字符处理开销更小，字符串比较更复杂

  • 使用合理的字段属性长度，固定长度的表会更快

  • 尽可能使用not null定义字段

  • 尽量少用text，必须用时最好分表

• 使用合适的索引列

  • 查询频繁的列，在where，group by，order by，on从句中出现的列

  • where条件中<，<=，=，>，>=，between，in，以及like 字符串+通配符（%）出现的列

  • 长度小的列，索引字段越小越好，因为数据库的存储单位是页，一页中能存下的数据越多越好

  • 离散度大（不同的值多）的列，放在联合索引前面。查看离散度，通过统计不同的列值来实现，count越大，离散程度越高

• 使用命令分析

  • show查看状态

  -- 状态信息(session表示取出当前窗口的执行，golbal表示从mysql启动到现在)show session|global status like '%status_name%';-- 查询次数('com_insert'、'com_update'、'com_delete')show status like '%com_select%';-- 连接数(登录次数)show status like 'connections';-- 数据库运行时间show status like 'uptime';-- 慢查询次数show status like 'slow_queries';-- 索引使用情况(handler_read_key表示使用索引查询的次数，handler_read_rnd_next表示在数据文件中读下一行的请求数)show status like 'handler_read%';

  • 显示系统变量

  show variables like '%变量名%';

  • 显示InnoDB存储引擎的状态

  show engine innodb status;

  • explain分析查询

  explain select 字段名 from 表名;

  • profiling分析sql语句

  -- 开启profile，查看当前sql执行时间set profiling = on;show profiles;

  • 查看所有用户的当前连接

  show processlist;

  • procedure analyse()取得建议(通过分析select查询结果对现有的表的每一列给出优化的建议)

  select 字段名 from 表名 procedure analyse();

  • optimize table回收闲置的数据库空间

  optimize table 表名;

  • repair table修复被破坏的表

  repair table 表名;

  • check table检查表是否有错误

  check table 表名;

• 定位慢查询

show variables like 'slow_query%';show variables like 'long_query_time';

• 分表和分区

  • 分表和分区的意义：日常开发中经常会遇到大表的情况，所谓的大表是指存储了百万级乃至千万级条记录的表。这样的表过于庞大，导致数据库在查询和插入的时候耗时太长，性能低下，如果涉及联合查询的情况，性能会更加糟糕。分表和表分区的目的就是减少数据库的负担，提高数据库的效率，通常点来讲就是提高表的增删改查效率

  • 分表的意义：分表是将一个大表按照一定的规则分解成多张具有独立存储空间的实体表，我们可以称为子表，每个表都对应三个文件，MYD数据文件，.MYI索引文件，.frm表结构文件。这些子表可以分布在同一块磁盘上，也可以在不同的机器上。app读写的时候根据事先定义好的规则得到对应的子表名，然后去操作它

  • 分区的意义：分区和分表相似，都是按照规则分解表。不同在于分表将大表分解为若干个独立的实体表，而分区是将数据分段划分在多个位置存放，可以是同一块磁盘也可以在不同的机器。分区后，表面上还是一张表，但数据散列到多个位置了。app读写的时候操作的还是大表名字，db自动去组织分区的数据

  • 分表和分区的关系：

    • 都能提高mysql的性高，在高并发状态下都有一个良好的表现

    • 分表和分区不矛盾，可以相互配合的，对于那些大访问量，并且表数据比较多的表，可以采取分表和分区结合的方式（如果merge这种分表方式，不能和分区配合的话，可以用其他的分表方式），访问量不大，但是表数据很多的表，可以采取分区的方式

    • 分表技术是比较麻烦的，需要手动去创建子表，app服务端读写时候需要计算子表名。采用merge好一些，但也要创建子表和配置子表间的union关系

    • 表分区相对于分表，操作方便，不需要创建子表

  • 分区的类型：

    • Range：把连续区间按范围划分

    • List：把离散值分成集合，按集合划分，适合有固定取值列的表

    • Hash：随机分配，分区数固定

    • Key：类似Hash，区别是只支持1列或多列,且mysql提供自身的Hash函数

  • 分区的注意事项：

    • 做分区时，要么不定义主键，要么把分区字段加入到主键中

    • 分区字段不能为NULL，不然无法确定分区范围，所以尽量NOT NULL

  • 分表的类型：

    • 垂直分表：又叫纵向分表，把原来有很多列的表拆分成多个表

      • 方式：

        • 把常用、不常用的字段分开放

        • 把大字段独立存放在一个表中

      • 好处：

        • 解决了表与表之间资源争用问题

        • 锁争用机率小

        • 实现核心与非核心的分级存储

        • 解决了数据库同步压力问题

    • 水平分表：又叫横向分表，为了解决单表数据量过大的问题，每个水平拆分表的结构完全一致

      • 方式：

        • 按时间结构：平板式和归档式

        • 按版块结构：对应式和冷热式

        • 按哈希结构

      • 好处：

        • 解决了单表过大造成的性能问题

        • 解决了单表过大造成的单服务器空间问题

• 配置优化

  • 全局配置：

    • max_connections：最大连接数

    -- 最大连接数show variables like 'max_connections';-- 相应连接数show status like 'max_connections';

    • back_log：mysql能暂存的连接数量，默认值是80，最多512，可设置为128。如果MySQL的连接数据达到max_connections时，新来的请求将会被存在堆栈中，以等待某一连接释放资源，该堆栈的数量即back_log。如果等待连接的数量超过back_log，将不被授予连接资源。当主要MySQL线程在一个很短时间内得到非常多的连接请求，这就起作用

    • key_buffer_size：索引缓冲区的大小，它决定索引处理的速度，尤其是索引读的速度。通过检查状态值Key_read_requests和Key_reads，可以知道key_buffer_size设置是否合理。默认配置数值是8388608(8M)，主机有4GB内存，可改为268435456(256M)

    show status like 'key_read%';

    • query_cache_size：使用查询缓存(query cache)，MySQL将查询结果存放在缓冲区中，今后对于同样的SELECT语句（区分大小写），将直接从缓冲区中读取结果。最佳选项是将其从一开始就停用，设为0（现在MySQL 5.6的默认值）并利用其他方法加速查询：优化索引、增加拷贝分散负载或者启用额外的缓存（比如Redis或Memcached）

    • read_buffer_size：MySQL读入缓冲区的大小，将对表进行顺序扫描的请求将分配一个读入缓冲区，MySQL会为它分配一段内存缓冲区，read_buffer_size变量控制这一缓冲区的大小，如果对表的顺序扫描非常频繁，并你认为频繁扫描进行的太慢，可以通过增加该变量值以及内存缓冲区大小提高其性能。默认数值是131072(128K)，可改为16773120(16M)

    • read_rnd_buffer_size：随机读缓冲区大小。当按任意顺序读取行时，将分配一个随机读缓存区。进行排序查询时，MySQL会首先扫描一遍该缓冲，以避免磁盘搜索，提高查询速度，如果需要排序大量数据，可适当调高该值。但MySQL会为每个客户连接发放该缓冲空间，所以应尽量适当设置该值，以避免内存开销过大。，默认数值是262144(256K)，可改为16777208(16M)

    • sort_buffer_size：每个需要进行排序的线程分配该大小的一个缓冲区。增加这值加速ORDER BY或GROUP BY操作。默认数值是10485760(1M)，可改为16777208(16M)

    • join_buffer_size：联合查询操作所能使用的缓冲区大小

    • table_open_cache：表高速缓存的大小。每当MySQL访问一个表时，如果在表缓冲区中还有空间，该表就被打开并放入其中，这样可以更快地访问表内容。1G内存机器，推荐值是128-256。内存在4GB左右的服务器该参数可设置为256M或384M

    • max_heap_table_size：用户可以创建的内存表(memory table)的大小。这个值用来计算内存表的最大行数值

    • tmp_table_size：临时表的大小

    • thread_cache_size：线程缓存。当客户端断开之后，服务器处理此客户的线程将会缓存起来以响应下一个客户而不是销(前提是缓存数未达上限)。建议设置接近Threads_connected值，再结合物理内存：1G-8；2G-16；3G-32

    show status like 'threads%';-- Threads_cached：代表当前此时此刻线程缓存中有多少空闲线程。如果过大，表明MySQL服务器一直在创建线程，这也是比较耗资源，可以适当增加thread_cache_size-- Threads_connected：代表当前已建立连接的数量，因为一个连接就需要一个线程，所以也可以看成当前被使用的线程数-- Threads_created：代表从最近一次服务启动，已创建线程的数量-- Threads_running：代表当前激活的（非睡眠状态）线程数。并不是代表正在使用的线程数，有时候连接已建立，但是连接处于sleep状态，这里相对应的线程也是sleep状态

    • interactive_timeout：一个交互连接在被服务器在关闭前等待行动的秒数。默认值是28800（8小时），可设置为7200

    • wait_timeout：一个非交互连接在被服务器在关闭前等待行动的秒数。要同时设置interactive_timeout和wait_timeout才会生效

  • InnoDB配置：

    • innodb_buffer_pool_size：缓冲池的大小，缓存数据和索引，对InnoDB整体性能影响较大，相当于MyISAM的key_buffer_size。如果只用Innodb，可以把这个值设为内存的70%-80%。越大越好，这能保证你在大多数的读取操作时使用的是内存而不是硬盘

    • innodb_log_buffer_size：尚未执行的事务的缓存大小，默认值为8M，一般8M-16M。如果你有很多事务的更新，插入或删除操作，通过这个参数会大量的节省了磁盘I/O。但是如果你的事务中包含有二进制大对象或者大文本字段的话，这点缓存很快就会被填满并触发额外的I/O操作。看看Innodb_log_waits状态变量，如果它不是0，应该增大这个值。但太大了也是浪费内存，因为1秒钟总会flush一次，所以不需要设到超过1秒的需求

    • innodb_flush_log_at_trx_commit：把log buffer的数据写入日志文件并flush磁盘的策略，该值对插入数据的速度影响非常大。取值分别为0、1(默认值)、2(推荐值)。

      • 0：事务提交时，不写入磁盘，而是每秒把log buffer的数据写入日志文件，并且flush(刷到磁盘)。速度最快，但不安全。mysqld进程的崩溃会导致上一秒钟所有事务数据的丢失

      • 1：每次事务提交时把log buffer的数据写入日志文件，并且flush(刷到磁盘)。最安全，但也最慢。确保了事务的ACID

      • 2：每次事务提交时把log buffer的数据写入日志文件，每秒flush(刷到磁盘)。速度较快，比0安全。操作系统崩溃或者系统断电会导致上一秒钟所有事务数据的丢失

    • innodb_log_file_size：在一个日志组每个日志文件的大小，用于确保写操作快速而可靠并且在崩溃时恢复。一般用64M-512M，具体取决于服务器的空间。大的文件提供更高的性能，但数据库恢复时会用更多的时间

    • innodb_additional_mem_pool_size：存储数据字典和其他内部数据结构的内存池大小。默认为1M，对于2G内存的机器，推荐值是20M，通常不用太大，应该与表结构的复杂度有关系。如果不够用，MySQL会在错误日志中写入一条警告信息

    • innodb_buffer_pool_instances：可以开启多个内存缓冲池，这样可以并行的内存读写。默认为8，一般为1-8。最常1s就会刷新一次，故不用太大。对于较大的事务，可以增大缓存大小。如果InnoDB缓存池被划分成多个区域，建议每个区域不小于1GB的空间