多版本并发控制（MVCC）：
1、可以认为是行级锁的一个变种，但是很多情况下都避免了加锁操作，不同引擎的实现方式不一样
2、实现是通过保存数据在某个时间的快照来实现的。
3、Innodb中，是通过在每行记录后面保存两个隐藏的列来实现的。一个保存了行的创建时间，一个保存了行的过期时间（删除时间）实际存储的是系统的版本号。每开始一个新的事务，系统版本号都会自动递增。事务开始时刻的版本号作为事务的版本号，用来和查询到的每行记录的版本号比较。在REPEATABLE READ隔离级别下，MVCC具体操作：
（1）SELECT
只查找版本早于当前事务版本的数据行，这样可以确保事务读取的行是事务开始前就已经存在了的，要么是事务自身插入的；行的删除版本要么未定义，要么大于当前事务版本号。这样可以确保事务读取到的行，在事务开始之前未被删除。
（2）INSERT
新插入的每一行数据保存当前系统版本号作为行版本号
（3）DELETE
同INSERT
（4)UPDATE
保存档当前版本号作为行版本号，同时保存当前系统版本号到原来的行作为行删除标识
优点：保存这两个额外的行号，使大多数读操作都可以不加锁。
缺点：每行需要额外的存储空间，需要做更多的行检查，以及额外的维护工作
只在REPEATEABLE READ 和 READ COMMITTED两个隔离级别工作。
实现了一致性非锁定,解决了并发下读写冲突的问题,采用无锁,版本控制的方式.
数据库引擎：
Innodb：（MySQL默认存储引擎，亲儿子，没有特别需求，优先这个引擎）
MySQL体系结构
结构和常用的存储引擎
.frm文件是用于记录表结构的，存在所有存储引擎中
MyISAM存储引擎：
- 系统表和临时表都是用的MyISAM（临时表：在排序、分组操作中，当数量超过一定大小后，由查询优化器建立的临时表）
- MyISAM存储引擎由MYD和MYI组成（还有一个。frm文件，这个文件每个存储引擎都会产生，作用都是一样的）
- 特性：
并发性与锁级别
表损坏修复（check table tablename、repair table tablename）
表支持索引类型
表支持数据压缩（myisampack），缺点：压缩以后就无法修改了，优点：减少磁盘空间占用，减少磁盘IO，从而提升查询性能
限制：
5.0版本前，默认表为4G，存储大表要修改为MAX_Row和AVG_ROW_LENGTH
5.0版本后默认支持256TB
MyIsam压缩后，不能写，只能读
- 适用场景：非事务应用、只读类应用、空间类应用
Innodb存储引擎：
1.使用表空间进行数据存储
innodb的锁都是行锁
索引是聚集索引，设置主键自增会好一些
事务没有提交的时候，别的线程访问的是通过undo.log的备份，所以看到的还是原来的数据
在mysql服务层可以加标记锁 行级锁主要在存储引擎层实现
死锁会自动处理，会回滚资源最小的那个事务
Innodb中的锁：
- 行级锁：共享锁（读锁，允许多个事务读一行数据）、排他锁（写锁，同一时刻，只允许一个事务修改一行数据）
- 意向锁：
意向共享锁（读锁 IS LOCK）：事务想要获取一张表的几行数据的共享锁，事务给一个数据行加共享锁前必须取得该表的IS锁
意向排他锁（写锁 IX LOCK）：事务想要获取一张表中几行数据的排他锁，事务在一个数据行加排他锁前必须先获得该表的IX锁
(加意向锁为了表明某个事务正在锁定一行或者将要锁定一行数据，价值在于节约Innodb对于锁的定位和处理性能，除了全表扫描以外，意向锁不会阻塞)
- 锁算法：
Record Lock： 单行记录上的锁
Gap Lock： 间隙锁，锁定一个范围，并且锁定记录本身
Next-Key Lock：结合上面两种锁。，主要解决幻读。
csv存储引擎：
1、把csv文件作为表来存储
2、通常作为数据交换的存储表
Archive存储引擎：
1、只支持insert和select，
2、缓存和行级锁
Federated
1、本地数据库访问远程数据库，mysql现在默认是关闭的
Memory存储引擎：
1、所有数据都保存在内存中，重启后数据丢失，结构保留
2、场景：查找或映射表，缓存周期性聚合数据的结果，保存数据分析中的中间数据
3、支持hash索引，表级锁，不支持BLOB或TEXT，每行长度固定，指定了VARCHAR实际会转为CHAR。
4、MySQL临时表就是Memory，大小超出Memory后才会转化为MYISAM表（临时表只在单个连接可见，关闭连接后就不可见）
NDB集群引擎
1、分布式、容灾、高可用
参数配置：内存、IO、安全三个方面
配置设置全局后，已存在的连接可能会没有效果，需要断开，然后重新再连接
内存配置都是给每个线程配置的，
而且有一些参数一定是4K的倍数
过大可能会导致mysql内存溢出
缓存数据，缓存索引，
myisqm的缓存只缓存索引。
mysql的系统表还是myisam
mysql事务日志，是写满一个文件再写入下一个文件（先写入缓存区，然后再刷新到日志文件里面）
innodb每个默认页的大小是16K
双写缓存
修改sqlmode的时候可能会导致原来能正常使用的sql应用失败，因为拒绝了一些不符合sql模式的语句，这些语句会执行失败
如何选择引擎：
- 大部分情况下选择Innodb都是正确的选择
- 不要选择混合使用多种存储引擎
- 除非需要用到某些Innodb不具备的特性，并且没有其他办法可以替代，否则都应该优先选择Innodb引擎。
如果需要用到别引擎，考虑因素：
- 事务 ：如果不需要事务，并且主要是SELECT和INSERT，那么可以选择MyISAM
- 备份
- 崩溃恢复
- 特有的特性
转换表的搜索引擎：
- ALTER TABLE ： alter talbe xxx ENGINE = Innodb（耗时，将源数据复制到新的表里，复制期间可能占用系统所有IO能力）
- 导出与导入：mysqldump，然后在文件中修改ENGINE类型
- 创建与查询：创建新表，然后用insert和select来复制数据到新表里面
mysql优化：
sql优化，数据库结构设计影响性能最大
影响性能的常见问题
列太多，关联表太多，多表查询速度比较慢，
使用外键约束效率低，每次都要查询是否满足外键约束
性能优化顺序：
- 数据库结构设计和sql语句
- 数据库存储引擎的选择和参数的优化（最好不要混合使用引擎，调整缓冲池大小等）
- 系统选择及优化（操作系统选择以及其参数设置）
- 硬件升级
数据库设计对性能的影响（常见的）：
1、过分的反范式化为表建立太多的列（列太多）
2、过分的范式话造成太多的表关联（关联表太多）
3、在OLTP环境中使用不恰当的分区表 （分区表是指一个数据库表的位置不是在同一个地方，多个地方有？？）
4、使用外键保证数据的完整性
基准测试：定义：
基准测试是一种测量和评估软件西性能指标的活动，用于建立某个时刻的性能基准，以便系统发生软硬件变化时重新进行基准测试以评估变化对性能的影响（简化了压力的测试）
压力测试：对真实的业务数据进行测试，获得真实系统所能承受的压力（需要针对不同主题，所使用的数据和查询也是真实用到的）
基准测试：直接、简单、易于比较，用于评估服务器处理能力（可能不关心业务逻辑，所使用的查询和业务的真实性可以和业务环境没关系）
基准测试目的：
- 建立MySQL服务器的性能基线（确定当前MySQL服务器运行状况）
- 模拟比当前系统更高的负载，以找出系统扩展瓶颈（例如，增加数据库并发，观察QPS，TPS变化，确定并发量与性能最优的关系）
- 测试不同硬件、软件和操作系统配置
- 证明信的硬件设备是否配置正确
如何进行基准测试：
- 对整个系统进行基准测试：（从系统入口测试，如web前端，app前端）
优点：能够测试整个系统性能，包括web服务其缓存、数据库等; 能反映出系统各个组件接口间的性能问题体现真实性能状况
缺点：测试设计复杂，消耗时间长
- 单独对MySQL进行基准测试
优点：测试设计简单，耗时短
缺点：无法全面了解整个系统哦你的性能基线
常见的测试指标
- 单位时间内所处理的事务数（TPS
- 单位时间内所处理的查询数（QPS
- 响应时间（平均响应时间、最小响应时间、最大响应时间、各时间爱你所占百分比）
- 并发量：同时处理的查询请求的数量（正在工作的并发操作数或同时工作的数量）
基准测试的步骤
计划和设计基准测试：（考虑两个问题：对整个系统还是某一组件;使用什么样的数据）
- 准备基准测试及数据收集脚本(CPU使用率、IO、网络流量、状态与计数器信息等)
- 运行基准测试
- 保存及分析基准测试结果（analyze.sh）
基准测试中容易忽略的问题
- 使用生产环境数据时只使用了一部分数据（使用数据库完全备份来测试）
- 在多用户场景中，只做了单用户的测试（使用多线程并发测试）
- 在单服务器上测试分布式应用（使用相同的架构进行测试）
- 反复执行统一查询（容易缓存命中，无法反应真实查询性能）
基准测试工具：
mysqlslap
特点：
可以模拟服务器负载，并输出相关统计信息
可以指定也可以自动生成查询语句
常用参数说明：
--auto-generate-sql 由系统自动生成SQL脚本进行测试
--auto-generate-sql-add-autoincrement 在生成的表中增加自增id
--auto-generate-sql-load-type 指定测试中使用的查询类型
--auto-generate-sql-write-number 指定初始化数据时生成的数据量
--concurrency 指定并发的线程数量
--engine 指定要测试表的存储引擎，可以用逗号分割多个存储引擎
--no-drop 指定不清理测试数据
--iterations 指定测试运行的次数
--number-of-queries 指定每一个线程执行的查询数量
--debug-info 指定输出额外的内存及cpu统计信息
--number-int-cols 指定测试表中包含的INT类型列的数量
--number-char-cols 指定测试表中哦你包含的varchar类型的数量
--create-schema 指定用于执行测试的数据库的名字
--query 用于指定自定义SQL的脚本
--only-print 并不运行测试脚本，而是把生成的脚本打印出来
sysbench
常用参数：
--test 用于指定所要执行的测试类型，支持以下参数
Fileio 文件系统I/O性能测试
cpu cpu性能测试
memoey 内存性能测试
Oltp 测试要指定具体的lua脚本
Lua 脚本位于 /usr/share/sysbench/
性能优化介绍
数据库结构优化介绍
影响数据库性能的因素有很多：服务器硬件，操作系统，MySQL服务器配置
良好的数据库逻辑设计和物理设计是数据库获得高性能的基础：
例如：反范式化设计能加快一些语句的查询速度，但是也可能会影响其他语句的性能。
数据库结构化优化的目的：
1、减少数据冗余（不是没有，尽量减少）
2、尽量避免数据维护中出现更新，插入和删除异常（一般通过范式化来避免）
插入异常：如果表中的某个实体随着另一个实体而存在
更新异常：如果更改表中的某个实体的单独属性时，需要对多行进行更新
删除异常：如果删除表中的某一实体则会导致其他实体的消失
3、节约数据存储空间
4、提高查询效率
数据库结构设计步骤：
1、需求分析：全面了解产品设计的存储需求（存储需求、数据处理需求、数据的安全性和完整性）
2、逻辑设计：设计数据的逻辑存储结构（进行规划设计，理清数据实体之间的逻辑关系，解决数据冗余和数据维护异常）
3、物理设计：根据所使用的数据库特点进行表结构设计（关系型、非关系型，选择存储引擎：Innodb）
4、维护优化：根据实际情况对索引、存储结构等进行优化
数据库设计范式：
1、目的：设计出没有数据冗余和数据维护异常的数据库结构
2、第一范式：
数据库表中的所有字段都只具有单一属性
单一属性的列是基本的数据类型所构成的
设计出来的表都是简单的二维表
3、第二范式：
要求一个表中具有一个业务主键，也就是说符合第二范式的表中不能存在非主键列只对部分主键的依赖关系，简言之：
如果主键是两个，就不能通过主键的其中一个确定唯一数据
4、第三范式：
指每一个非主属性既不部分依赖于也不传递依赖于业务主键，也就是在第二范式的基础上消除了非主属性对主键的传递依赖，简言之：如果行中的其中一个字段，能通过非主键列唯一确定，那么就不符合第三范式。
范式化：
优点：
可以尽量的减少数据冗余（数据表更新快，体积小）
范式化的更新操作比反范式化更快
范式化的表通常比反范式化更小
缺点：
对于查询需要对多个表进行关联（如果这种查询是经常的，会极大损耗性能）
更难进行索引优化
反范式化：
优点：
可以减少表的关联
更好的进行索引优化
缺点：
存在数据冗余及数据维护异常（）
对数据的修改需要更多的成本（冗余数据多）
一般在需求分析及逻辑分析时，先使用范式化设计，然后结合业务具体操作，进行反范式化改造。
案例：
结论：不能完全按照范式化的要求进行设计，需要考虑以后如何使用表
物理设计：
内容：
1、定义数据库、表及字段的命名规范
2、选择合适的存储引擎
3、为表中的字段选择合适的数据类型
4、建立数据库结构
定义数据库、表及字段命名规范：
1、遵守可读性原则
2、遵守表意性原则
3、遵守长名原则
合适的存储引擎：
为字段选择合适的数据类型：
原则：
1、当一个列可以选择多种数据类型时，应该优先考虑数字类型，其次是日期或二进制类型，最后是字符类型。
2、对于相同级别的数据类型，应该优先选择占用空间小的数据类型。
如何选择正确的整数类型：根据业务整形需要的长度，是否有符号，选择满足前面两者的，尽可能存储空间小的类型
如何选择正确的实数类型：
精确运算都用DECIMAL
如何选择VARCHAR和CHAR类型
VARCHAR类型的存储特点：
1、用于存储变长字符串，只占用必要的存储空间
2、列的最大长度小于255，则只占用一个额外字节用于记录字符串长度，否则用两个额外字节，（每1长度值一个字符）
长度选择问题：
1、使用最小的符合需求的长度
2、varchar(5)和varchar(200)存储 ’mysql‘字符串性能不同（虽然在表中都是6个字节，但是在临时表中是定长的）
使用场景：
1、字符串列的最大长度比平均长度大很多
2、字符串列很少被更新（经常更新会引起存储页的分裂，影响性能）
3、使用了多字节字符集存储字符串（因为别的字符集不同类型的字符需要用来表示的字节数都不一样）
CHAR类型的存储特点：
1、char类型是定长的
2、字符串存储在Char类型的列中会删除末尾的空格
3、char类型的最大宽度为255
场景：
1、适合存储长度近似的值（性别，电话，身份证等）
2、使用存储短字符串
如何存储日期数据：
DateTime类型
1、以YYYY-MM-DD HH:MM:SS[.fraction]格式存储日期时间
datetime = YYYY-MM-DD HH:MM:SS
datetime(6) = YYYY-MM-DD HH:MM:SS.fraction
2、该类型与时区无关，不会因为时区改变而改变，占用8个字节的存储空间
3、时间范围： 1000-01-01 00:00:00 到 9999-12-31 23:59:59
TIMESTAMP类型：
1、存储了格林尼治时间1970年1月1日到当前时间的秒数，以YYYY-MM-DD HH:MM:SS.[.fraction]显示
2、占用4个字节
3、时间范围： 1970-01-01 到2038-01-19
4、有自动修改功能，默认会自动修改第一列的timestamp，如果没有插入时没有指定，会自动填入第一列当前时间，另外一列为全零
date类型
1、占用的字节数比字符串、datetime、int少，只要3个字节
2、可以利用时间函数进行日期计算
3、范围：1000-01-01到9999-12-31之间的日期
time类型： 用于存储时间数据，格式：HH:MM:SS
注意事项：
1、不要使用字符串来存储日期，因为占用空间大，无法使用时间函数，无法用日期对比
2、使用Int存储日期不如使用TimeStamp类型。占用空间小
总结：
物理设计= 存储空间 + 存储引擎（没有特别要求都用innodb） + 数据类型
对于innodb主键选择：
1、主键应该尽可能的小
2、应该是顺序增长的，可以增加数据的插入效率
3、主键和业务主键可以不同，如果业务主键长，可以设置一个自增的主键，然后再设立一个业务主键
索引：优化查询 或者用来order by 排序
索引优化：
索引是由存储引擎实现的，不同
索引类型：
B-tree索引（用B+树的结构来存储数据的）
从根节点
特点：
1、B-tree索引能够加快数据的查询速度
2、B-tree索引更适合进行范围查询
什么时候能使用到：
1、匹配范围查询
2、精确查询匹配左前列并范围匹配另外一列
3、只访问索引的查询（覆盖索引）
4、全值匹配的查询
5、匹配最左前缀的查询
6、匹配列前缀查询（模糊查询的时候xxx%）
索引优化策略：
索引长度：766 innodb innodb的非主键索引后面都会跟有主键信息
索引列上不能使用表达式或者函数：（使用了函数就无法利用索引加快速度，通常尽量灵活改写sql来避免）
前缀索引和索引列的选择性：
前缀索引是指：当索引列很长的时候使用其前面的一部分作为索引，
这时候需要考虑索引列的选择性，必须选择至选择性较大的长度才能增加查询速度，选择性小的索引，查询 优化器甚至不会去使用
联合索引：
如何选择索引列的顺序：
1、经常会被使用到的列优先
2、选择性高的列优先
3、宽度小的列优先
覆盖索引：
1、优点：
可以优化缓存，减少磁盘IO操作
可以减少随机IO，变随机IO操作为顺序IO操作，计算机执行顺序IO会快于随机IO
可以避免对Innodb主键索引的二次查询，一般是通过索引获取关键字，然后通过关键字获取具体的行数据
可以避免MyISAM表进行系统调用，因为MyISAM索引存的是数据的地址。
2、无法使用覆盖索引的情况：
存储引擎不支持覆盖索引
查询中使用了太多的列
使用了双%好的like查询（这种情况连索引都无法使用，别说覆盖索引了）
限制：
1、如果不是按照索引最左列开始查找，则无法使用索引
2、使用索引时不能跳过索引中的列（如 A and B And C 其中AC有索引，B没有，则C的索引无法使用）
3、Not in 和 < > 操作无法使用索引
4、如果查询中有某个列的范围查询，则其右边所有列都无法使用索引
索引占用了大部分数据
用受限制的方式使用索引
哈希索引：
innodb默认会使用的哈希索引，但是是innodb自己根据b+tree索引的使用情况自动去建立的，我们无法自定义创建
特点：
1、基于Hash表实现，只有查询条件精确匹配Hash索引中的所有列时，才能够使用到hash索引
2、对于Hash索引中的所有列，存储引擎都会为每一行计算一个Hash码，Hash索引存储的就是Hash码无法存储数据
场景：
1、对于精确查询比较多的，少范围查询的时候
限制：
1、必须两次查找，先通过哈希，找到索引，然后在找到行数据，只能全键值使用
2、无法用于排序
3、只能精确查询，无法范围查询
4、哈希码的计算，可能有哈希冲突，键值重复值大时候，用哈希索引不合适
为什么要使用索引：
- 索引大大减少了存储引擎需要扫描的数据量（索引的大小比较少，每次扫描一页，大概16K，索引比数据少，因此每页放的索引是很多的）
- 索引可以帮助我们进行排序以避免使用临时表
- 索引可以把随机I/O不变为顺序I/O（索引是顺序存储的，因此Order by的时候也能用到索引）
索引不是越多越好
1、索引会增加写操作的成本（最好加快写操作的办法是删除所有索引，只留下一个自增的主键）
2、太多的索引会增加查询优化器的选择时间
使用索引来优化查询：
1、通过索引扫描来优化排序（对innodb而言，数据逻辑顺序和主键顺序是一样的）
通过排序操作
按照索引顺序扫描数据
要求：
1、索引的列顺序和Order By字句的顺序完全一致
2、索引中所有列的方向（升序、降序）和Order By字句完全一致
3、Order By 中的字段全部在关联表中的第一张表中模拟Hash索引（使用tree模拟）
2、模拟Hash索引优化查询
只能处理键值的全值匹配查找
所使用的Hash函数决定着索引键的大小
使用一些像md5等函数 使用md5 和 本身进行搜索，这样使用md5
进行搜索，然后通过 本身进行过滤，来避免冲突 例如：
create index idx_md5 on film(title_md5); （创建索引）
update film set title_md5=md5(title); （使用md5更新列值）
explain select * from film where tile_md5=md5('dog') and title='dog'\G; 查看查询计划
3、利用索引优化锁
索引可以减少锁定的行数 （innodb使用的是行锁，通过索引过滤掉大部分行，可以减少行锁）
索引可以加快处理速度，同时也加快了锁的释放（通过索引只需要将很少的行加载入内存中，减少过滤还击）
索引的维护和优化：
1、删除重复和冗余的索引
重复索引：（primary key(id), unique key(id), index(id)）这分别为同一列建立了主键索引、唯一索引、单列索引，
主键已经是非空的唯一索引，就没有必要建立唯一索引和同样的单列索引了。
冗余索引： （Index(a), index(a,b)） 联合索引也可以在单独对单列进行索引（联合索引可能会很大，有时候需要额外加入一个单独索引来加速）
重复索引是没有必要的，冗余索引有时候是我们刻意为了优化查询加上的
通过工具来查看重复的索引，并让工具给出建议作为参考：
2、查找未使用过的索引（定时清除整理）
3、更新索引统计信息及减少索引碎片（MySQL优化器会通过查询索引的统计信息来指定查询计划,统计信息不准确的时候会导致优化器做出错误的判断）
analyze table table_name; 重新生成统计信息（成本不一样：MyISAM的索引都是存储在磁盘中的，需要多表进行锁定innodb则是根据我们查询的情况，把常用的索引存在内存中; 使用语句的时候，可能统计的不全面，估算值，但是速度块）
optimize table table_name 维护表的碎片(使用不当会导致锁表)
SQL优化：
获取有性能问题的SQL方法：
1、通过用户反馈
2、通过慢查询日志
3、实时获取（服务器压力突增的时候，等等）
部分语法：
explain select * from rental \G （查看实际执行计划情况）
创建索引：
create index idx_xxx on table(colum);
update table set title_md5=md5(title);
开启事务： begin;
回滚：rollback
提交 ：commit;
给查询加入排他锁：
select * from actor where last_name="WOOD" for update;
MVCC和乐观锁区别
https://www.zhihu.com/question/27876575
乐观锁和悲观锁原理？是什么？
乐观锁实现:
1多版本并发控制+乐观锁
2加version字段
扫一扫
5万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
