MySQL学习笔记
  • 零散知识点
  1. ACID:原子性、一致性、隔离性、持久性
  2. 事务的隔离级别,四种:(1)未提交读(可能出现脏读);(2)提交读(可能出现不可重复读);(3)可重复读(可能出现幻读,但mysql的innodb通过mvcc解决了这个问题);(4)可串行化读(行行加锁,太重了,一般不用)

  • 索引篇
  1. 索引主要分为B+树索引和Hash索引。
  2. 索引的最左匹配原则:比如设定一个索引为(col1,col2,col3),那么必须在where子句中含有最左边的列col1的时候,索引才会起作用。其他如where col2=val and col3= val。。。。都是不使用索引的。
  3. 聚集索引定义:一种逻辑索引和物理索引相同的索引。一张表只能有一个聚集索引,因为物理索引只有一种。根据这个特性,适用于范围查找。主键列默认是聚集索引。
  4. 聚集索引的优点:(1)适合范围查找  缺点:(1)对于数据更新开销大
  5. 非聚集索引与聚集索引的区别:(1)叶子节点并非数据节点    (2)叶子节点为每一个真正的数据行存储一个“键-指针对     (3)叶子节点中还存储了一个指针偏移量,根据页指针及指针偏移可以定位到具体的数据行。  (4)在除叶节点外的其他索引节点,存储的是类似内容,只不过是指向下一级索引页。
  6. 自适应hash索引:对某些多次访问的列,自动加上hash索引方便查询。
  • 聚集索引
  1. InnoDB的聚集索引实际上在同样的结构中保存了B-树索引和数据行
  2. 数据行实际保存在索引的叶子页。(B-树特性)
  3. “聚集”的定义:指实际的数据行和相关的键值都保存在一起
  4. 优点:(1)适合范围查找,比如可以按照学号聚集,查出一个学生所有的成绩。(2)数据访问快,因为是聚集的,所以索引和数据都保存到了同一颗B树上。(3)使用覆盖索引的查询可以使用包含在叶子节点中的主键值
  5. 缺点:(1)聚集索引适合I/O密集型,如果数据量很小,能够装入内存,那就没有太大的意义。(2)更新操作困难,插入依赖于插入顺序,最快就是按照主键插入。
  • B+树索引
  1. 特点:B+树和B树类似,都是平衡的M叉多路访问树。但B+树在叶子节点上增加了前后指针,方便快速访问叶节点。且B+树的非叶节点是索引叶节点才存储需要的关键字数据。
  2. 对于B树索引来说,索引的列顺序很重要,因为需要遵守最左匹配原则
  3. 优点:(1)是平衡树,从根节点到叶节点路径短,且同层级有指针方便快速移动(2)能够减少磁盘I/O次数,因为索引文件是也要占空间的,而B类树(B-/B+)有大量的非叶节点存储索引。且B+树中的非叶节点没有数据域,空间更大,存储的容量更多,即减少磁盘读写。
  4. 缺点:(1)对于重复性低的表查询速度没有hash快。(2)等值查询没有hash快

  • Hash索引
  1. 优点:我们知道hash索引在查找上性能非常好,在合理的碰撞情况下可以认为是O(1);这样对于磁盘的I/O次数就相对更少,我们知道磁盘的I/O非常花时间。
  2. 缺点:(1)有利有弊,因为hash是通过散列值一次确定位置,所以无法使用于范围查找。(2)hash索引无法用来避免排序操作:我们知道在构建索引的时候可以顺便将索引的列进行排序,这样在后期使用的时候就不用再排序了,而hash索引做不到这点。(3)hash索引任何时候都无法避免表扫描,因为碰撞的存在。
  3. 特点:Hash索引不支持最左匹配原则
  • 全文索引
  1. 出现在Myisam引擎中
  2. 它不是使用where子句来比较相等性,而是执行相关的关键字操作,是基于数据的关联性,而非比较。
  3. 在表中通过连接字符列构造索引,并把他们当成很长的字符串进行索引
  4. 数据结构是双重平衡B树,容易出现 碎片
  5. 全文索引的添加/删除/修改 都很慢
  • 覆盖索引
  1. 定义:包含(覆盖)所有满足查询需要的数据的索引。 
  2. 原理:索引中保存了它包含列的数据;因此hash索引、全文索引都不能满足,只有B-树索引能够支持。
  3. 想要创建一个覆盖索引,这个索引必须包含指定表上包括WHERE语句、ORDER BY语句、GROUP BY语句(如果有的话)以及SELECT语句中的所有列。    
  • 关于索引建立的方式
  1. 习惯性的想法是不要在选择性很差的列上建索引,比如sex,可选范围只有man/woman,因为这样会返回大量满足条件的记录。但也有特殊情况
  2. 针对上一条,如果这个列虽然选择性很差,但是很多查询的where子句中都会用到它,那么可以采取变通的方法:(1)将该列设为索引的最左,即引用最左前缀匹配,那么对于大多数查询来说是可以用的上索引的。 (2)其次,如果不使用这列,那么可以使用sex in('male','female')这样来过滤。它实际上不会过滤任何行,但我们依然能使用上索引。
  3. 将范围判据放到索引的最后,这样优化器就会尽可能多的使用索引。同时请注意不要忘记:B树索引只能优化第一个范围条件左边的列。比如 where last_name='Smith' and first_name like 'J%' and bir_day='1978-12-31',那么只能优化last_name和first_name列。
  4. 总结:(1)选择性低的一般不用作索引 (2)如果查询中经常用到选择性低的列,那就把它放在索引列的前面,使用In()替换 (3)对于范围判据,放在索引列的后面,因为范围后面的列不会被优化 (5)如果有多个范围,将其中可以的转换为In() (6)如果不能转换为In(),那么转换为一个等值性的判断。

  • 关于索引的使用
  1. 如果在查询中没有隔离索引的列,MySQL通常不会使用索引。
  2. 隔离列:既不是表达式的一部分,也没有位于函数中。比如 select* ......where to_days(current_date)-to_days(date_col)<=10,这个就不会使用索引,因为“-”既不是函数,也没有在表达式中。如果要使用索引应该是where date_col>=date_sub(current_date,interval 10 days)
  3. 如果是bolb/text或者很长的varchar,必须定义前缀索引,因为不允许索引它们的全文
  4. 对于大的表,应当使它的前缀选择率接近它的全列选择性。
  5. 前缀索引不能用在order by和group by子句中
  • 关于索引对排序的优化
  1. 我们知道,B-树索引可以帮助排序。而MySQL有两种排序方法:(1)文件排序(2)索引排序,而索引排序只有在满足条件的情况下才能使用;在其他不满足的情况下我们使用索引排序。
  2. 按理来说,索引排序的速度应该是比文件排序快的,但这建立在索引是可以被覆盖查询的情况下,否则就是得顺序查找,这就成了近似于随机I/O。
  3. 索引排序成立条件:(1)所有列排序方向一致(DESC,ASC)(2)索引中列的顺序和order by子句中的顺序一致 (3)满足最左前缀原则 
  4. 举个反例:这个里面我定义了两个索引,一个id,一个stu_name,然后order by子句里面是按sex的,这个时候因为order by子句中不含有索引列,所以不满足,可以看到Extra中是Using filesort(文件排序)的。

  • 引擎篇 

  1. MySQL中最常用的两种引擎分别是:MyISAM,InnoDB。MySQL5.1前的默认引擎是MyISAM。较为常用的是InnoDB

  • MyISAM
  1. 支持全文索引
  2. 采用表锁
  3. 不支持事务,这也是为什么很多人以前会认为MySQL不是事务型数据库
  4. 采用压缩前缀索引,因此适用于I/O密集型。
  5. 不支持外键
  6. 使用B+树索引,但在子节点存储的不是主键,而是数据对应行的id(ROWID)
  7. 在没有使用任何where子句的情况下,使用Count(*)函数的性能会很好。
  8. 将每个表存储为两个文件:数据文件和索引文件,.MYD/.MYI,有跨平台性
  9. myisam表最大可用支持256TB数据处理,并有6字节的指针记录数据、
  10. 具有延迟更新索引,适用于数据经常改变且频繁使用的表,大大提高了表的处理性能。

  • InnoDB
  1. 支持事务,默认的事务隔离级别是第三级(区别于其他数据库的第二级不可重复读),可重复读。理论上这一级的传播级别难以避免会出现幻读,但InnoDB通过MVCC(多版本并发控制)解决了幻读的问题。
  2. 采用的是行级锁,区别于MyISAM的表锁
  3. 也是为了解决幻读的情况,InnoDB还采取了间隙锁(next-key lock),即不但锁定当前行,也锁定前后范围的行。
  4. 索引有B+树索引和自适应hash索引,引擎采用聚簇索引,主键查找效率高。
  5. InnoDB不会压缩索引。且它的辅助索引也会包含主键列,因此尽量把主键定义的小一点。
  6. InnoDB的聚集索引在实际同样的结构中保存了B-树索引和数据行
  7. InnoDB按照主键进行聚集;如果没有主键就会在唯一的非空索引代替;否则就会定义一个隐的主键,在上面聚集。
  8. InnoDB的第二索引(非聚集索引)的叶子节点包含了对应行的主键值作为指向行的“指针”,而不是“行指针”。即,比如一行数据,col1是主键列,大小为18;col2是第二索引,大小为9;那么9中的叶子节点存储的不是行指针,而是指向主键列18那行的指针。这使得索引变的更大,但是也意味着innodb能够移动行而无需更新指针。
  9. 前面提到,聚集索引插入最优情况就是按照主键顺序来,即设置为auto_increment。如果是类似于UUID(通用唯一识别码 )这种,每次新生成的键不一定比原来的键大,则引擎不得不为它另外寻找插入位置,而这时候可能出现:(1)换页产生,目标页面被移除缓存,这时候不得不进行磁盘I/O

  • varchar和char的区别
  •  varchar
  1. varchar是可变长度的字符串,即它不是固定的,它只占用了自己需要的空间,也就是较短的值占用的空间就会比较少。比如varchar(10),我插入了一个"hello"进去,那么那个字段实际的长度就是5而不是10.
  2. varchar在使用row_format=fixed的myisam表中是固定长度的
  3. varchar还会使用额外的1~2字节才存储值的长度列的最大长度<255使用1个,否则2个
  4. 缺点:varchar虽然能节省空间,但是正因为是可变的,所以在更新的时候可能会引起额外的操作。所以varchar的列最好不要频繁更新
  5. 适用情况:(1)最大长度远大于平均长度,且很少发生更新的时候。(2)使用复杂可变字符集,比如utf-8的时候
  • char
  1. char是固定长度的
  2. 针对上面描述varchar可变的情况,char通过在右边填充空格到字符串末尾解决的,因此是固定长度
  3. 对于字符串后面的空格,在select查询结果的时候,右边的字符串空格不会被保存。我们可以看见string3右边的空格是没有被保存的,而string2左边的空格是被保存了的。如果是varchar的话,那么string3右边的空格也会被保存。(基于InnoDB)
  4. 适用:(1)很短的字符串或长度相似的字符串,比如密码的MD5散列值 (2)经常需要更新的值  (3)长度很短的列,比如一个字节的(ASCII),前面提到VARCHAR还会拿1~2字节存储长度,所以一个字节长度的char更短。
  • 具体区别
  1. varchar是可变长度,char是固定长度,不足的用空格填充
  2. 对于字符串右边的空格字符,varchar保留,char不保留(针对InnoDB 5.6)
  3. varchar会用1~2字节存储它的实际长度(取决于最长长度大小是否大于255 store=maxLength<255?1:2),而char没有
  4. varchar不适合常更新的列,而char表现不错
  5. char还适合存储固定格式(长度相近)的字段,或者长度很短的(原因为3)
  6. 虽然varchar是可变的,但在设定大小的时候还是要尽量贴合实际。因为在内存中还是固定分配分区算法,(比如创建临时表)

  • 日期和时间类型

  • DATETIME
  1. 能保存大范围的值,从1001年到9999年,精度为秒,格式为YYYYMMDDHHMMSS的整数中,与时区无关,使用了8字节的存储空间
  2. MySQL以可排序的格式显示,符合ANSI标准
  • TIMESTAMP
  1. 依赖于时区,MySQL服务器、操作系统和客户端连接都有设置
  2. 只能保存1970-2038年,
  3. 4字节的大小
  • 二者的比较
  1. 通常应该使用timestamp,因为它占有的空间更小
  2. timestamp默认的是not null
  3. 两者的精度都只到秒,如果要使用毫秒级的,可以使用bigint/double型的保存秒的分数部分。

  • 查询优化

  • 向服务器请求了不必要的数据
  1. 尽量不要使用select * ,一是容易带上不必要的列,二是如果对表列进行增删操作可能会返回意想不到的结果。
  2. 在多表连接的时候,注意提取的列。
  • MySQL检查了太多的数据吗?
  1. 检验开销的指标主要有三个:执行时间、检查的行数、返回的行数
  2. 执行时间:可以通过慢速查询日志检查。
  3. 尽量使检查行和返回行的比例小一些。
  4. 使用合理的索引
  • 重构查询的方式
  1. 考虑将一个大的复杂的查询分解成多个简单的查询,因为现代网络已经很快了,而且MySQL能高效的连接/断开服务器
  2. 分治法:让查询在本质上不改变,但每次只执行一小部分,以减少受影响的行数。   例子:清理陈旧数据,这需要大量的查询,会长时间的锁住多行数据,塞满日志;而采取细化语句,可以改进性能,并在复制时减少延迟。(采用Limit限制一次删除的数量)
  3. 分解联接:将一个多表联接分解成多个单个查询,然后在服务器端实现联接操作 适用情形:(1)可以缓存早期查询的数据 (2)使用了多个myisam表 (3)数据分布在不同的服务器上 (4)对大表使用了In替代联接 (5)一个联接引用了一张表多次
MySQL架构

1.1.1 连接管理与安全性
  1.  每个客户连接在服务器的进程中都拥有自己的线程(线程独立),客户端连接到MySQL可以基于SSL方式
1.2.2锁粒度

  1. 在给定的资源上,被加锁的数据量越小,并发性越好。
  2. 但加锁也会消耗资源
  3. 所谓锁策略,就是在锁开销和数据安全间寻求平衡
  4. 表级锁:开销最小;写锁互斥,读/读可并发;写锁的优先级高于读锁,写锁在读锁前,读锁不能在写锁前。
  5. 行级锁:并发性最好,开销最大
1.3.4 mysql中的事务
  1. MySQL默认操作模式是autocommit,即如果不是显式的开启一个事务,否则它将会把每一个查询视为一个单独的事务自动执行
  2. InnoDB的事务在任何时候都可以获得锁,但只有在提交/回滚时才能释放锁(所有),这是一种隐式锁定
  3. 除非是在一个事务中,且autocommit被禁止,否则不要使用lock tables命令。
1.4 MVCC(多版本并发控制)
  1. 产生原因:解决事务隔离级别为可重复读情况下产生的“幻读”情况
  2. 实现技术:通过及时保存某些时刻的数据快照而得以实现的。这意味着同一事务的多个实例,在同时运行的时候,它们看到的数据视图是一致的。
  3. 具体方法:InnoDB为每个行增加两个隐含值来实现,记录了行的创建时间以及它的过期时间(或删除事件)。每一行都存储了事件发生时的系统版本号。每一次开始一个新事务的时候,版本号都会递增。每个事物都会保存它在开始时的“当前版本号
  4. 对操作的要求:(1)只查询<=当前版本号的数据并返回 (2)数据行的删除版本必须是未定义或大于当前版本的,用于确保事务开始时数据行是未删除的。 
  5. 好处:(1)避免幻读  (2)避免了很多情况下的加锁操作,降低了系统开销 (3)允许非锁定行的数据读取
  6. 工作范围:只工作在第二级和第三级,即提交读和可重复读
阅读更多
个人分类: MySQL 关系型数据库
想对作者说点什么? 我来说一句

mysql 个人学习笔记

2010年06月07日 12.56MB 下载

MYSQL学习笔记-索引

2010年08月28日 7KB 下载

MYSQL tools

2008年11月25日 1.66MB 下载

学习笔记Mysql入门很简单-笔记

2018年03月11日 77.42MB 下载

MySql学习笔记

2011年12月13日 46KB 下载

MySQL学习笔记.chm

2010年01月16日 2.97MB 下载

mysql学习笔记

2015年12月11日 49KB 下载

没有更多推荐了,返回首页

不良信息举报

MySQL学习笔记

最多只允许输入30个字

加入CSDN,享受更精准的内容推荐,与500万程序员共同成长!
关闭
关闭