文章目录- - -DataBase- - -1 知识拓扑2 文件与日志3 关系型数据库4 NoSQL5 非关系型数据库6 MySQL6.1 存储引擎6.1.1 数据库连接6.1.2 MySQL结构6.1.3 存储引擎概念6.1.4 存储引擎区别6.2 索引6.2.1 索引6.2.2 索引分类及使用6.2.3 B树6.2.4 B树6.2.5 聚簇索引6.2.6 非聚簇索引6.2.7 密集索引和稀疏索引6.2.8 索引优化6.3 事务6.3.1 事务概念6.3.2 事务特性6.3.3 事务隔离级别6.4 锁6.4.1 锁分类6.4.2 MyISAM的锁6.4.3 InnoDB的锁6.4.4 悲观锁乐观锁6.5 MVCC6.5.1 定义及工作原理6.5.2 快照6.6 执行计划explain6.6.1 执行计划explain6.6.2 参数详解6.7 优化技术6.7.1 优化技术总结6.7.2 表设计合理化6.7.3 Sql语句优化6.7.4 适当添加索引6.7.5 分库分表技术6.7.6 读写分离7 集群- - -DataBase- - -1 知识拓扑如下图DataBase知识拓扑。如下图MySQL知识拓扑。2 文件与日志你好3 关系型数据库你好4 NoSQL你好5 非关系型数据库你好6 MySQL6.1 存储引擎6.1.1 数据库连接1. 连接① 通信类型同步、异步② 连接方式长连接、短连接③ 通信协议TCP/IP、UDP④ 通信方式单工、半双工、双工⑤ MySQL使用同步通信、长连接、TCP/IP协议和半双工通信方式。2. JDBCJDBCJava DataBase Connectivity是Java和数据库之间的桥梁是一组用Java语言编写的类和接口。6.1.2 MySQL结构如下图MySQL可分为两层架构第一层SQLLayer功能包括权限判断sql解析执行计划优化querycache处理等第二层存储引擎层是底层数据存取操作实现部分。MySQL两层架构中服务层、存储引擎层中涉及的知识点如下图。其中CRUD过程中MySQL服务层中的步骤可分为连接、解析、优化、执行。6.1.3 存储引擎概念存储引擎是数据存储、数据索引、数据CRUD、是否支持事务等技术的实现方式不同存储引擎的特性有一定差别。6.1.4 存储引擎区别特点InnoDBMyISAMMEMORYFEDERATED支持事务是否否否锁机制行锁(适合高并发)表锁表锁-支持外键是否否-支持索引B树索引全文索引集群索引数据索引B树索引全文索引B树索引哈希索引数据索引B树索引其他索引和数据文件不分离索引和数据文件分离-适用于分布式场景连接多个MySQL服务器6.2 索引6.2.1 索引1. 定义索引是对数据库中数据排序的一种数据结构存储在内存或磁盘中使用索引可快速访问数据库中的指定数据。2. 优点① 减少数据查询行数提高效率② 建立唯一索引或者主键索引保证数据字段的唯一性③ 检索时有分组和排序需求时减少服务器排序的时间。3. 缺点① 创建和维护索引存在时间及内存消耗② 索引字段过多数据量过大时索引占据空间可能比表更大③ 更新数据时也需要维护索引增加数据维护复杂度。4. 建立索引的原则① 离散度高② 有序性好③ 索引数目不要多。6.2.2 索引分类及使用1. 单列索引一个单列索引只包含单个列但一个表中可以有多个单列索引。普通索引MySQL中基本索引类型允许在定义索引的列中插入重复值和空值。唯一索引索引列中的值是唯一的但是允许为空值主键索引是一种特殊的唯一索引不允许有空值。2. 组合索引基于多个字段组合创建的索引使用组合索引时遵循最左匹配原则。3. 全文索引在MyISAM引擎上才能使用只能在CHAR、VARCHAR、TEXT类型字段上使用。4. 空间索引空间索引是对空间数据类型的字段建立的索引MySQL中的空间数据类型有四种GEOMETRY、POINT、LINESTRING、POLYGON。使用SPATIAL关键字引擎为MyISAM。6.2.3 B树1. B树定义① 树中每个结点最多含有m个孩子( m 2 );② 除根结点和叶子结点外其他每个结点至少 m/2 个孩子。③ 若根结点不是叶子至少2个孩子。④ 有j个孩子的非叶节点恰好有 j-1 个关键码关键码按递增次序排序。6.2.4 B树1. B树定义① 非叶子节点的值会以最大或最小值出现在其子节点中即叶子节点包含所有元素② 非叶子节点带有索引数据和指向叶子节点的指针不包含实际数据信息叶子节点有所有元素信息③ 所有叶子节点形成一个有序链表。2. B树优势① B树磁盘读写代价低B树存储元素数据B只存储索引可以存储更多节点② B树查询效率稳定非终结点只是关键字的索引查找数据必须走到叶子节点③ B树中叶子结点是一个链表所以B树在面对范围查询时比B树更加高效。6.2.5 聚簇索引1. 定义聚簇索引叶子节点存储的是数据。索引类型依赖存储引擎Innodb使用的是聚簇索引。2. 优点① 减少磁盘IO次数查询数据时索引节点和数据被同时载入内存② 无需维护辅助索引当出现数据页分裂时无需更新索引中的数据块指针。3. Innodb的主键索引和辅助索引如图为Innodb存储引擎生成的主键索引结构非叶子节点存储主键叶子节点存储主键和行数据。如下图为Innodb存储引擎生成的辅助索引结构。叶子节点存储索引字段和对应的主键值索引到主键值后根据主键值再去主键索引中查找对应的数据。6.2.6 非聚簇索引非聚簇索引叶子节点中存储的是指向数据块指针MyISAM使用非聚簇索引。6.2.7 密集索引和稀疏索引InnoDB中主键索引(或首个唯一非空索引)是唯一的密集索引非主键索引为稀疏索引MyISAM中索引均为稀疏索引。6.2.8 索引优化(1) like语句前导模糊查询不使用索引select address from t where name like ‘%xx’; - 不使用索引select address from t where name like ‘xx%’; - 可使用索引(2) 负向条件查询不使用索引select name from t where status ! 1 and status ! 2; - 不使用索引select name from t where status in (0,3,4); - 优化为in可使用索引负向条件有!,,not in,not exists,not like等(设status为0 1 2 3 4)。(3) 范围条件右边的列不使用索引select name from t where no 10010 and title‘xxx’ and date between ‘1986-01-01’ and ‘1986-12-31’;范围条件有,,,,between等索引最多用于一个范围列如上联合索引 (no,title,date)SQL中no使用索引title date不适用索引。(4) 在索引列做任何操作(计算、函数、表达式)不使用索引select name from t where YEAR(create_time) ‘2016’; - 不使用索引select name from t where create_time ‘2016-01-01’; - 可使用索引select name from order where date CURDATE(); - 不使用索引select name from order where date ‘2018-01-2412:00:00’; - 可使用索引select id from t where substring(name,1,3)’abc’; - 不使用索引select id from t where name like ‘abc%’ ; - 可使用索引select id from t where num/2100; - 不使用索引select id from t where num100*2; - 可使用索引(5) where中索引列使用参数会导致索引失效select id from t where numnum; - 不使用索引select id from t with(index) where numnum; - 可以改为强制查询使用索引SQL在运行时解析局部变量优化程序是在编译时选择访问计划但在编译时变量值是未知的。(6) 强制类型转换会导致全表扫描select name from t where phone13800001234; - 不使用索引select name from t where phone‘13800001234’; - 可使用索引字符串类型不加单引号时索引失效因为mysql会做类型转换。(7) is null, is not null无法使用索引mysql的高版本允许使用索引select id from t where num is null; - mysql低版本不使用索引select id from t where num0; - 可在num设置默认值0确保num列没有null值(8) 使用联合索引时要符合最左原则建立联合索引字段数一般少于5个如果在a b c上建立联合索引会自动建立a、(a,b)、(a,b,c) 三组索引。① 建立联合索引时区分度最高的字段在最左边。② 存在等号和非等号混合条件时建立索引应把等号条件的列前置如where a ? and b ?即使a区分度更高也把b放在索引最前列。③ 最左前缀查询时不是指where条件顺序必须和联合索引一致但建议保持一致。④ 假如index(a,b,c), where a3 and b like ‘abc%’ and c4a能用b能用c不能用。6.3 事务6.3.1 事务概念数据库的事务是指一组sql语句组成的逻辑处理单元在这组sql操作中要么全部执行成功要么全部执行失败。6.3.2 事务特性原子性(Atomic)指事务的原子性操作要么同时成功要么同时失败一致性(Consistent)事务操作前后状态要一致隔离性(Isolated)多个事务之间相互隔离持久性(Durable)当事务提交或回滚后数据的新增、修改会持久化到数据库中。6.3.3 事务隔离级别1. 事务隔离级别问题问题描述脏读事务a读取到事务b中没有提交的数据不可重复读(虚读)事务a中针对某条数据两次读到的结果不同幻读事务a按相同条件检索数据事务b添加满足该条件的数据则事务a两次检索到的数据记录数不同隔离级别备注脏读不可重复读幻读Read uncommitted/0未提交读可能可能可能Read committed/1已提交读Oracle默认否可能可能Repeatable read/2可重复读MySQL默认否否可能Serializable/3串行化否否否2. 未提交读执行过程图3. 已提交读执行过程图6.4 锁6.4.1 锁分类1. 按粒度划分锁优缺点支持引擎表锁开销小加锁快不出现死锁粒度大发生锁冲突概率高并发度低MyISAM、MEMORY、InnoDB行锁开销大加锁慢会出现死锁粒度小发生锁冲突概率低并发度高InnoDB2. 按类型划分锁优缺点共享锁/读锁多个事务对同一数据可共享一把锁均可访问数据只能读不能修改。排他锁/写锁事务A获取某数据行的排他锁则其他事务不能获取该行的其他锁包括共享锁和排他锁获取排他锁的事务是可以对数据就行读取和修改3. MySQL调度策略① 写入请求应按其到达的次序进行处理② 写入具有比读取更高的优先权。6.4.2 MyISAM的锁1. 支持表锁① Select操作加共享锁Update、Delete、Insert操作加排它锁② 读写之间、写写之间是串行的读读之间是并行的③ 由于表锁粒度大读写是串行的若更新操作较多可能出现严重的锁等待。2. 并发锁MyISAM的系统变量concurrent_insert默认设置为1。6.4.3 InnoDB的锁1. 支持行锁行锁分类操作说明记录锁/Record lock对索引项加锁即锁定一条记录间隙锁/Gap lock对索引项之间的‘间隙’、对第一条记录前的间隙或最后一条记录后的间隙加锁即锁定一个范围的记录不包含记录本身范围锁/Next-key Lock锁定一个范围的记录并包含记录本身上面两者的结合2. 行锁导致的死锁(1) 死锁原理MySQL中行锁不是锁数据而是锁索引索引分为主键索引和非主键索引若sql语句操作了主键索引MySQL会锁定该主键索引若语句操作了非主键索引MySQL会先锁定该非主键索引再锁定相关的主键索引。在update、delete操作时MySQL不仅锁定where条件扫描的索引记录同时会锁定相邻的键值即next-key locking。(2) 死锁原因当两个事务同时执行事务a锁住了主键索引在等待其他相关索引事务b锁定了非主键索引在等待主键索引则发生死锁。(3) 降低死锁① 不同程序并发存取多个表尽量以相同的顺序访问表② 一个事务尽可能一次锁定需要的所有资源③ 对于容易产生死锁的业务可使用粒度较大的锁如表锁④ 若程序以批量方式处理数据可事先对数据排序保证每个线程按固定的顺序处理记录。3. 行锁的间隙锁用法select * from 表名 where 字段名** for update使用范围条件(所示方法)检索数据时InnoDB除了给索引记录加锁还会给不存在的记录间隙加锁。目的防止幻读避免其他事务插入数据。6.4.4 悲观锁乐观锁1. 悲观锁(1) 悲观锁流程及使用场景① 修改记录前先加排他锁加锁失败则等待或者抛出异常加锁成功则进行修改事务完成后解锁② 行锁、表锁、读锁、写锁都属于悲观锁。③ 悲观并发控制主要用于数据争用激烈的环境(2) 优缺点① 优点 悲观并发控制使用“先取锁再访问”的策略可保证数据处理的安全性② 缺点 (a)效率低处理加锁有额外开销增加死锁概率③ 缺点 (b)只读事务不涉及数据修改无需加锁。2. 乐观锁如果系统并发量非常大悲观锁会带来非常大的性能问题可选择使用乐观锁乐观锁的实现方法有版本控制机制和时间戳。(1) 版本控制机制标志每行数据增加version字段每次更新数据对应版本号1原理读出数据将版本号一同读出之后更新版本号1提交数据版本号大于数据库当前版本号则予以更新否则认为是过期数据重新读取数据。(2) 使用时间戳实现标志每行数据增加time字段原理读出数据将时间戳一同读出之后更新提交数据时间戳等于数据库当前时间戳则予以更新否则认为是过期数据重新读取数据。6.5 MVCC6.5.1 定义及工作原理1. 定义Multi-Version Concurrency Control 一种多版本并发控制协议只在数据库引擎为InnoDB、隔离级别为RC、RR的情况下存在。MVCC是通过版本号和快照/一致性视图实现了事务隔离性但只在事务级别为已提交读和可重复读时有效。MVCC最大的好处是读不加锁读写不冲突。2. 工作原理InnoDB引擎中每行数据都有三个隐藏字段唯一行号(DB_ROW_ID字段)、事务ID(DB_TRX_ID字段)和回滚指针(DB_ROLL_PTR字段)。开启事务时会生成一个事务版本号被操作的数据会生成新的数据行但在提交前对其他事务不可见。数据更新之后事务提交成功后将该事务版本号赋值给数据行的创建版本号图解如下。6.5.2 快照1. 快照创建策略隔离级别创建快照策略read committed级别事务开启每个select操作都会创建快照(Read View)保持最新快照repeatable read级别事务开启首个select操作之后创建快照(Read View)之后均使用该快照2. 快照工作原理如下图版本1、版本2并非实际物理存在的而图中的U1和U2实际就是undo log这v1和v2版本是根据当前v3和undo log计算出来的。快照遵循原则如下自己更新的数据总是可见其他事务更新的数据有三种情况版本未提交的都不可见版本已经提交但是是在创建视图之后提交的也不可见版本已经提交但是是在创建视图之前提交的是可见的。6.6 执行计划explain6.6.1 执行计划explainexplain执行计划包含信息如下图其中比较重要的字段为包含id、type、key、rows。6.6.2 参数详解1. idselect查询的序列号表示执行select子句或操作表的顺序包括三种情况。① id相同执行顺序由上至下② id不同如果是子查询id序号会递增id值越大优先级越高越先被执行③ id相同又不同(两种情况同时存在)id如果相同则为一组从上往下顺序执行在所有组中id值越大优先级越高越先执行 。2. select_type查询类型用于区分普通查询、联合查询、子查询等复杂的查询。其值包含SIMPLE、PRIMARY、SUBQUERY、DERIVED、UNION、UNION RESULT。3. type访问类型sql查询优化的重要指标结果值从好到坏依次是system const eq_ref ref fulltext ref_or_null index_merge unique_subquery index_subquery range index ALL一般来说sql查询至少达到range级别最好能达到ref。type值类型意义system表中只有一行记录匹配是const类型特例const通过索引一次即可查询到数据const用于比较primary key或者unique索引eq_ref唯一性索引扫描表中只有一条记录匹配常见于主键或唯一索引扫描ref非唯一性索引扫描返回匹配某个单独值的所有行range检索给定范围的行使用一个索引来选择行一般是在where语句中出现bettween、、、in等的查询。indexFull Index Scan遍历索引树(Index与ALL都是读全表但index是从索引中读取而ALL是从硬盘读取)ALLFull Table Scan遍历全表以找到匹配的行4. possible_keys查询涉及到的字段上存在索引则该索引将被列出但不一定被查询实际使用5. key实际使用的索引如果为NULL则没有使用索引查询中如果使用了覆盖索引则该索引仅出现在key列表中 。6. key_len表示索引中使用的字节数查询中使用的索引的长度(最大可能长度)并非实际使用长度理论上长度越短越好key_len是根据表定义计算而得的不是通过表内检索出的。7. ref显示索引的那一列被使用如果可能是一个常量const。8. rows根据表统计信息及索引选用情况大致估算出找到所需的记录所需要读取的行数。6.7 优化技术6.7.1 优化技术总结① 表设计合理化(符合“三范式”兼顾“反范式”)② 适当添加索引(包括普通索引、主键索引、唯一索引、全文索引)③ SQL语句优化(包括避免全表扫描、避免嵌套子查询等④ 分表技术(水平分割、垂直分割)⑤ 读写分离其中写包括update/delete/add⑥ 存储过程模块化编程可提高速度但迁移性差服务器压力也会逐渐增大⑦ mysql参数调优(修改my.ini配置文件的参数)⑧ mysql服务器硬件升级⑨ 定时清除不需要的数据定时进行碎片整理(特别是使用MyISAM)。6.7.2 表设计合理化1. 第一范式每列属性都是不可再分确保每列原子性两列属性相同或相似尽量合并属性一样的列确保不产生冗余数据。2. 第二范式表中每列都只和主键相关即一张表中只保存一种数据。3. 第三范式表中每列只能依赖于主键非主键依赖数据用外键做关联。4. 反范式若业务所涉及的表非常多经常会有多表联查其查询效率就会大打折扣可考虑使用“反范式”。增加必要的有效的冗余字段用空间来换取时间,在查询时减少或者是避免过多表之间的联查。6.7.3 Sql语句优化1. Sql执行效率排查方法① SHOW [ SESSION|GLOBAL ] STATUS指令② 慢查询定位与记录。2. 常用Sql优化方法① group by 分组查询涉及到排序使用order by null禁止排序② 使用左/右连接替代多表联查因为MySQL使用JOIN不需要在内存中创建临时表③ 含or的查询语句每个条件列都尽量使用索引没有索引可考虑增加索引④ 选择合适的存储引擎MyISAM引擎不支持事务其查询和添加效率高INNODB引擎支持事务其数据一致性较好。6.7.4 适当添加索引索引知识具体见6.2章节索引技术可大大提高查询速度其代价是降低插入、更新、删除的速度。6.7.5 分库分表技术1. 垂直分表将一张表的若干列提取成子表。如下图商品描述信息访问频次低占用存储空间大商品基本信息访问频次高占用存储空间小。采用垂直分表规则将商品基本信息存放在表1中访问商品描述信息放在表2中。2. 水平分表将一张表分割成多个相同的子表在访问时根据事先定义好的规则操作对应的子表。如下图商品信息及商品描述被分成了两套表如果商品ID为双数将此操作映射至表1如果商品ID为单数将操作映射至表2。3. 垂直分库如下图将SELLER_DB(卖家库)分为了PRODUCT_DB(商品库)和STORE_DB(店铺库)并把这两个库分散到不同服务器。4. 水平分库如下图将店铺ID为单数的和店铺ID为双数的商品信息分别放在两个库中。5. 其他分表分库技术① mysql集群其作用和分表相同集群可将任务分担到多台数据库可进行读写分离减少读写压力。② 自定义规则分表/库按照业务规则来分解为多个表/库常用规则包括Range(范围)、Hash(哈希)等也可自定义规则。6.7.6 读写分离1. 读写分离原理读写分离即在主服务器上增删改在从服务器上读同时将数据从主服务器同步到从服务器。实现了数据备份、优化了数据库性能。2. 常用分离技术① 基于程序代码内部实现② 基于中间代理层实现其结构如下图所示流行的代理中间件有mysql_proxy、Atlas、Amoeba。7 集群你好
数据库 - MySQL
文章目录- - -DataBase- - -1 知识拓扑2 文件与日志3 关系型数据库4 NoSQL5 非关系型数据库6 MySQL6.1 存储引擎6.1.1 数据库连接6.1.2 MySQL结构6.1.3 存储引擎概念6.1.4 存储引擎区别6.2 索引6.2.1 索引6.2.2 索引分类及使用6.2.3 B树6.2.4 B树6.2.5 聚簇索引6.2.6 非聚簇索引6.2.7 密集索引和稀疏索引6.2.8 索引优化6.3 事务6.3.1 事务概念6.3.2 事务特性6.3.3 事务隔离级别6.4 锁6.4.1 锁分类6.4.2 MyISAM的锁6.4.3 InnoDB的锁6.4.4 悲观锁乐观锁6.5 MVCC6.5.1 定义及工作原理6.5.2 快照6.6 执行计划explain6.6.1 执行计划explain6.6.2 参数详解6.7 优化技术6.7.1 优化技术总结6.7.2 表设计合理化6.7.3 Sql语句优化6.7.4 适当添加索引6.7.5 分库分表技术6.7.6 读写分离7 集群- - -DataBase- - -1 知识拓扑如下图DataBase知识拓扑。如下图MySQL知识拓扑。2 文件与日志你好3 关系型数据库你好4 NoSQL你好5 非关系型数据库你好6 MySQL6.1 存储引擎6.1.1 数据库连接1. 连接① 通信类型同步、异步② 连接方式长连接、短连接③ 通信协议TCP/IP、UDP④ 通信方式单工、半双工、双工⑤ MySQL使用同步通信、长连接、TCP/IP协议和半双工通信方式。2. JDBCJDBCJava DataBase Connectivity是Java和数据库之间的桥梁是一组用Java语言编写的类和接口。6.1.2 MySQL结构如下图MySQL可分为两层架构第一层SQLLayer功能包括权限判断sql解析执行计划优化querycache处理等第二层存储引擎层是底层数据存取操作实现部分。MySQL两层架构中服务层、存储引擎层中涉及的知识点如下图。其中CRUD过程中MySQL服务层中的步骤可分为连接、解析、优化、执行。6.1.3 存储引擎概念存储引擎是数据存储、数据索引、数据CRUD、是否支持事务等技术的实现方式不同存储引擎的特性有一定差别。6.1.4 存储引擎区别特点InnoDBMyISAMMEMORYFEDERATED支持事务是否否否锁机制行锁(适合高并发)表锁表锁-支持外键是否否-支持索引B树索引全文索引集群索引数据索引B树索引全文索引B树索引哈希索引数据索引B树索引其他索引和数据文件不分离索引和数据文件分离-适用于分布式场景连接多个MySQL服务器6.2 索引6.2.1 索引1. 定义索引是对数据库中数据排序的一种数据结构存储在内存或磁盘中使用索引可快速访问数据库中的指定数据。2. 优点① 减少数据查询行数提高效率② 建立唯一索引或者主键索引保证数据字段的唯一性③ 检索时有分组和排序需求时减少服务器排序的时间。3. 缺点① 创建和维护索引存在时间及内存消耗② 索引字段过多数据量过大时索引占据空间可能比表更大③ 更新数据时也需要维护索引增加数据维护复杂度。4. 建立索引的原则① 离散度高② 有序性好③ 索引数目不要多。6.2.2 索引分类及使用1. 单列索引一个单列索引只包含单个列但一个表中可以有多个单列索引。普通索引MySQL中基本索引类型允许在定义索引的列中插入重复值和空值。唯一索引索引列中的值是唯一的但是允许为空值主键索引是一种特殊的唯一索引不允许有空值。2. 组合索引基于多个字段组合创建的索引使用组合索引时遵循最左匹配原则。3. 全文索引在MyISAM引擎上才能使用只能在CHAR、VARCHAR、TEXT类型字段上使用。4. 空间索引空间索引是对空间数据类型的字段建立的索引MySQL中的空间数据类型有四种GEOMETRY、POINT、LINESTRING、POLYGON。使用SPATIAL关键字引擎为MyISAM。6.2.3 B树1. B树定义① 树中每个结点最多含有m个孩子( m 2 );② 除根结点和叶子结点外其他每个结点至少 m/2 个孩子。③ 若根结点不是叶子至少2个孩子。④ 有j个孩子的非叶节点恰好有 j-1 个关键码关键码按递增次序排序。6.2.4 B树1. B树定义① 非叶子节点的值会以最大或最小值出现在其子节点中即叶子节点包含所有元素② 非叶子节点带有索引数据和指向叶子节点的指针不包含实际数据信息叶子节点有所有元素信息③ 所有叶子节点形成一个有序链表。2. B树优势① B树磁盘读写代价低B树存储元素数据B只存储索引可以存储更多节点② B树查询效率稳定非终结点只是关键字的索引查找数据必须走到叶子节点③ B树中叶子结点是一个链表所以B树在面对范围查询时比B树更加高效。6.2.5 聚簇索引1. 定义聚簇索引叶子节点存储的是数据。索引类型依赖存储引擎Innodb使用的是聚簇索引。2. 优点① 减少磁盘IO次数查询数据时索引节点和数据被同时载入内存② 无需维护辅助索引当出现数据页分裂时无需更新索引中的数据块指针。3. Innodb的主键索引和辅助索引如图为Innodb存储引擎生成的主键索引结构非叶子节点存储主键叶子节点存储主键和行数据。如下图为Innodb存储引擎生成的辅助索引结构。叶子节点存储索引字段和对应的主键值索引到主键值后根据主键值再去主键索引中查找对应的数据。6.2.6 非聚簇索引非聚簇索引叶子节点中存储的是指向数据块指针MyISAM使用非聚簇索引。6.2.7 密集索引和稀疏索引InnoDB中主键索引(或首个唯一非空索引)是唯一的密集索引非主键索引为稀疏索引MyISAM中索引均为稀疏索引。6.2.8 索引优化(1) like语句前导模糊查询不使用索引select address from t where name like ‘%xx’; - 不使用索引select address from t where name like ‘xx%’; - 可使用索引(2) 负向条件查询不使用索引select name from t where status ! 1 and status ! 2; - 不使用索引select name from t where status in (0,3,4); - 优化为in可使用索引负向条件有!,,not in,not exists,not like等(设status为0 1 2 3 4)。(3) 范围条件右边的列不使用索引select name from t where no 10010 and title‘xxx’ and date between ‘1986-01-01’ and ‘1986-12-31’;范围条件有,,,,between等索引最多用于一个范围列如上联合索引 (no,title,date)SQL中no使用索引title date不适用索引。(4) 在索引列做任何操作(计算、函数、表达式)不使用索引select name from t where YEAR(create_time) ‘2016’; - 不使用索引select name from t where create_time ‘2016-01-01’; - 可使用索引select name from order where date CURDATE(); - 不使用索引select name from order where date ‘2018-01-2412:00:00’; - 可使用索引select id from t where substring(name,1,3)’abc’; - 不使用索引select id from t where name like ‘abc%’ ; - 可使用索引select id from t where num/2100; - 不使用索引select id from t where num100*2; - 可使用索引(5) where中索引列使用参数会导致索引失效select id from t where numnum; - 不使用索引select id from t with(index) where numnum; - 可以改为强制查询使用索引SQL在运行时解析局部变量优化程序是在编译时选择访问计划但在编译时变量值是未知的。(6) 强制类型转换会导致全表扫描select name from t where phone13800001234; - 不使用索引select name from t where phone‘13800001234’; - 可使用索引字符串类型不加单引号时索引失效因为mysql会做类型转换。(7) is null, is not null无法使用索引mysql的高版本允许使用索引select id from t where num is null; - mysql低版本不使用索引select id from t where num0; - 可在num设置默认值0确保num列没有null值(8) 使用联合索引时要符合最左原则建立联合索引字段数一般少于5个如果在a b c上建立联合索引会自动建立a、(a,b)、(a,b,c) 三组索引。① 建立联合索引时区分度最高的字段在最左边。② 存在等号和非等号混合条件时建立索引应把等号条件的列前置如where a ? and b ?即使a区分度更高也把b放在索引最前列。③ 最左前缀查询时不是指where条件顺序必须和联合索引一致但建议保持一致。④ 假如index(a,b,c), where a3 and b like ‘abc%’ and c4a能用b能用c不能用。6.3 事务6.3.1 事务概念数据库的事务是指一组sql语句组成的逻辑处理单元在这组sql操作中要么全部执行成功要么全部执行失败。6.3.2 事务特性原子性(Atomic)指事务的原子性操作要么同时成功要么同时失败一致性(Consistent)事务操作前后状态要一致隔离性(Isolated)多个事务之间相互隔离持久性(Durable)当事务提交或回滚后数据的新增、修改会持久化到数据库中。6.3.3 事务隔离级别1. 事务隔离级别问题问题描述脏读事务a读取到事务b中没有提交的数据不可重复读(虚读)事务a中针对某条数据两次读到的结果不同幻读事务a按相同条件检索数据事务b添加满足该条件的数据则事务a两次检索到的数据记录数不同隔离级别备注脏读不可重复读幻读Read uncommitted/0未提交读可能可能可能Read committed/1已提交读Oracle默认否可能可能Repeatable read/2可重复读MySQL默认否否可能Serializable/3串行化否否否2. 未提交读执行过程图3. 已提交读执行过程图6.4 锁6.4.1 锁分类1. 按粒度划分锁优缺点支持引擎表锁开销小加锁快不出现死锁粒度大发生锁冲突概率高并发度低MyISAM、MEMORY、InnoDB行锁开销大加锁慢会出现死锁粒度小发生锁冲突概率低并发度高InnoDB2. 按类型划分锁优缺点共享锁/读锁多个事务对同一数据可共享一把锁均可访问数据只能读不能修改。排他锁/写锁事务A获取某数据行的排他锁则其他事务不能获取该行的其他锁包括共享锁和排他锁获取排他锁的事务是可以对数据就行读取和修改3. MySQL调度策略① 写入请求应按其到达的次序进行处理② 写入具有比读取更高的优先权。6.4.2 MyISAM的锁1. 支持表锁① Select操作加共享锁Update、Delete、Insert操作加排它锁② 读写之间、写写之间是串行的读读之间是并行的③ 由于表锁粒度大读写是串行的若更新操作较多可能出现严重的锁等待。2. 并发锁MyISAM的系统变量concurrent_insert默认设置为1。6.4.3 InnoDB的锁1. 支持行锁行锁分类操作说明记录锁/Record lock对索引项加锁即锁定一条记录间隙锁/Gap lock对索引项之间的‘间隙’、对第一条记录前的间隙或最后一条记录后的间隙加锁即锁定一个范围的记录不包含记录本身范围锁/Next-key Lock锁定一个范围的记录并包含记录本身上面两者的结合2. 行锁导致的死锁(1) 死锁原理MySQL中行锁不是锁数据而是锁索引索引分为主键索引和非主键索引若sql语句操作了主键索引MySQL会锁定该主键索引若语句操作了非主键索引MySQL会先锁定该非主键索引再锁定相关的主键索引。在update、delete操作时MySQL不仅锁定where条件扫描的索引记录同时会锁定相邻的键值即next-key locking。(2) 死锁原因当两个事务同时执行事务a锁住了主键索引在等待其他相关索引事务b锁定了非主键索引在等待主键索引则发生死锁。(3) 降低死锁① 不同程序并发存取多个表尽量以相同的顺序访问表② 一个事务尽可能一次锁定需要的所有资源③ 对于容易产生死锁的业务可使用粒度较大的锁如表锁④ 若程序以批量方式处理数据可事先对数据排序保证每个线程按固定的顺序处理记录。3. 行锁的间隙锁用法select * from 表名 where 字段名** for update使用范围条件(所示方法)检索数据时InnoDB除了给索引记录加锁还会给不存在的记录间隙加锁。目的防止幻读避免其他事务插入数据。6.4.4 悲观锁乐观锁1. 悲观锁(1) 悲观锁流程及使用场景① 修改记录前先加排他锁加锁失败则等待或者抛出异常加锁成功则进行修改事务完成后解锁② 行锁、表锁、读锁、写锁都属于悲观锁。③ 悲观并发控制主要用于数据争用激烈的环境(2) 优缺点① 优点 悲观并发控制使用“先取锁再访问”的策略可保证数据处理的安全性② 缺点 (a)效率低处理加锁有额外开销增加死锁概率③ 缺点 (b)只读事务不涉及数据修改无需加锁。2. 乐观锁如果系统并发量非常大悲观锁会带来非常大的性能问题可选择使用乐观锁乐观锁的实现方法有版本控制机制和时间戳。(1) 版本控制机制标志每行数据增加version字段每次更新数据对应版本号1原理读出数据将版本号一同读出之后更新版本号1提交数据版本号大于数据库当前版本号则予以更新否则认为是过期数据重新读取数据。(2) 使用时间戳实现标志每行数据增加time字段原理读出数据将时间戳一同读出之后更新提交数据时间戳等于数据库当前时间戳则予以更新否则认为是过期数据重新读取数据。6.5 MVCC6.5.1 定义及工作原理1. 定义Multi-Version Concurrency Control 一种多版本并发控制协议只在数据库引擎为InnoDB、隔离级别为RC、RR的情况下存在。MVCC是通过版本号和快照/一致性视图实现了事务隔离性但只在事务级别为已提交读和可重复读时有效。MVCC最大的好处是读不加锁读写不冲突。2. 工作原理InnoDB引擎中每行数据都有三个隐藏字段唯一行号(DB_ROW_ID字段)、事务ID(DB_TRX_ID字段)和回滚指针(DB_ROLL_PTR字段)。开启事务时会生成一个事务版本号被操作的数据会生成新的数据行但在提交前对其他事务不可见。数据更新之后事务提交成功后将该事务版本号赋值给数据行的创建版本号图解如下。6.5.2 快照1. 快照创建策略隔离级别创建快照策略read committed级别事务开启每个select操作都会创建快照(Read View)保持最新快照repeatable read级别事务开启首个select操作之后创建快照(Read View)之后均使用该快照2. 快照工作原理如下图版本1、版本2并非实际物理存在的而图中的U1和U2实际就是undo log这v1和v2版本是根据当前v3和undo log计算出来的。快照遵循原则如下自己更新的数据总是可见其他事务更新的数据有三种情况版本未提交的都不可见版本已经提交但是是在创建视图之后提交的也不可见版本已经提交但是是在创建视图之前提交的是可见的。6.6 执行计划explain6.6.1 执行计划explainexplain执行计划包含信息如下图其中比较重要的字段为包含id、type、key、rows。6.6.2 参数详解1. idselect查询的序列号表示执行select子句或操作表的顺序包括三种情况。① id相同执行顺序由上至下② id不同如果是子查询id序号会递增id值越大优先级越高越先被执行③ id相同又不同(两种情况同时存在)id如果相同则为一组从上往下顺序执行在所有组中id值越大优先级越高越先执行 。2. select_type查询类型用于区分普通查询、联合查询、子查询等复杂的查询。其值包含SIMPLE、PRIMARY、SUBQUERY、DERIVED、UNION、UNION RESULT。3. type访问类型sql查询优化的重要指标结果值从好到坏依次是system const eq_ref ref fulltext ref_or_null index_merge unique_subquery index_subquery range index ALL一般来说sql查询至少达到range级别最好能达到ref。type值类型意义system表中只有一行记录匹配是const类型特例const通过索引一次即可查询到数据const用于比较primary key或者unique索引eq_ref唯一性索引扫描表中只有一条记录匹配常见于主键或唯一索引扫描ref非唯一性索引扫描返回匹配某个单独值的所有行range检索给定范围的行使用一个索引来选择行一般是在where语句中出现bettween、、、in等的查询。indexFull Index Scan遍历索引树(Index与ALL都是读全表但index是从索引中读取而ALL是从硬盘读取)ALLFull Table Scan遍历全表以找到匹配的行4. possible_keys查询涉及到的字段上存在索引则该索引将被列出但不一定被查询实际使用5. key实际使用的索引如果为NULL则没有使用索引查询中如果使用了覆盖索引则该索引仅出现在key列表中 。6. key_len表示索引中使用的字节数查询中使用的索引的长度(最大可能长度)并非实际使用长度理论上长度越短越好key_len是根据表定义计算而得的不是通过表内检索出的。7. ref显示索引的那一列被使用如果可能是一个常量const。8. rows根据表统计信息及索引选用情况大致估算出找到所需的记录所需要读取的行数。6.7 优化技术6.7.1 优化技术总结① 表设计合理化(符合“三范式”兼顾“反范式”)② 适当添加索引(包括普通索引、主键索引、唯一索引、全文索引)③ SQL语句优化(包括避免全表扫描、避免嵌套子查询等④ 分表技术(水平分割、垂直分割)⑤ 读写分离其中写包括update/delete/add⑥ 存储过程模块化编程可提高速度但迁移性差服务器压力也会逐渐增大⑦ mysql参数调优(修改my.ini配置文件的参数)⑧ mysql服务器硬件升级⑨ 定时清除不需要的数据定时进行碎片整理(特别是使用MyISAM)。6.7.2 表设计合理化1. 第一范式每列属性都是不可再分确保每列原子性两列属性相同或相似尽量合并属性一样的列确保不产生冗余数据。2. 第二范式表中每列都只和主键相关即一张表中只保存一种数据。3. 第三范式表中每列只能依赖于主键非主键依赖数据用外键做关联。4. 反范式若业务所涉及的表非常多经常会有多表联查其查询效率就会大打折扣可考虑使用“反范式”。增加必要的有效的冗余字段用空间来换取时间,在查询时减少或者是避免过多表之间的联查。6.7.3 Sql语句优化1. Sql执行效率排查方法① SHOW [ SESSION|GLOBAL ] STATUS指令② 慢查询定位与记录。2. 常用Sql优化方法① group by 分组查询涉及到排序使用order by null禁止排序② 使用左/右连接替代多表联查因为MySQL使用JOIN不需要在内存中创建临时表③ 含or的查询语句每个条件列都尽量使用索引没有索引可考虑增加索引④ 选择合适的存储引擎MyISAM引擎不支持事务其查询和添加效率高INNODB引擎支持事务其数据一致性较好。6.7.4 适当添加索引索引知识具体见6.2章节索引技术可大大提高查询速度其代价是降低插入、更新、删除的速度。6.7.5 分库分表技术1. 垂直分表将一张表的若干列提取成子表。如下图商品描述信息访问频次低占用存储空间大商品基本信息访问频次高占用存储空间小。采用垂直分表规则将商品基本信息存放在表1中访问商品描述信息放在表2中。2. 水平分表将一张表分割成多个相同的子表在访问时根据事先定义好的规则操作对应的子表。如下图商品信息及商品描述被分成了两套表如果商品ID为双数将此操作映射至表1如果商品ID为单数将操作映射至表2。3. 垂直分库如下图将SELLER_DB(卖家库)分为了PRODUCT_DB(商品库)和STORE_DB(店铺库)并把这两个库分散到不同服务器。4. 水平分库如下图将店铺ID为单数的和店铺ID为双数的商品信息分别放在两个库中。5. 其他分表分库技术① mysql集群其作用和分表相同集群可将任务分担到多台数据库可进行读写分离减少读写压力。② 自定义规则分表/库按照业务规则来分解为多个表/库常用规则包括Range(范围)、Hash(哈希)等也可自定义规则。6.7.6 读写分离1. 读写分离原理读写分离即在主服务器上增删改在从服务器上读同时将数据从主服务器同步到从服务器。实现了数据备份、优化了数据库性能。2. 常用分离技术① 基于程序代码内部实现② 基于中间代理层实现其结构如下图所示流行的代理中间件有mysql_proxy、Atlas、Amoeba。7 集群你好
