MySQL相关

MySQL相关

三大范式

第一范式:每个列都不可以再拆分

第二范式:在第一范式的基础上,非主键列完全依赖于主键,而不能是依赖于主键的一部分

第三范式:在第二范式的基础上,非主键列只依赖于主键,不依赖于其他非主键

MySQL优化

  1. 使用缓存:redis,Memcached
  2. 优化查询:连接(JOIN)来代替子查询,使用联合(UNION)来代替手动创建的临时表,使用外键
  3. 为搜索字段建索引:避免全表扫描,首先应考虑在 where 及 order by 涉及的列上建立索引
  4. 选择适用的数据类型,如:使用 ENUM 而不是 VARCHAR。ENUM 类型是非常快和紧凑的。在实际上,其保存的是 TINYINT,但其外表上显示为字符串。如果你有一个字段,比如“性别”,“国家”,“民族”,“状态”或“部门”,你知道这些字段的取值是有限而且固定的,那么,你应该使用 ENUM 而不是VARCHAR
  5. 选择正确的存储引擎
  6. 分表:垂直分表:就是将一个表按照字段来分,每张表保证有相同的主键就好。一般来说,将常用字段和不常用字段分表来放。 优势:比没有分表来说,提高了查询速度,降低了查询结果所用内存;劣势:没有解决大量记录的问题,对于单表来说随着记录增多,性能还是下降很快;file。水平拆分:把原来一个表拆分成多个表,每个表的结构都一样,解决单表数据量大的问题。
  7. 数据库集群,主从复制与读写分离:主数据库负责写操作,并做双击热备,多台从数据库做负载均衡,负责读操作。主从复制方案读写分离方案
  8. 分库,分区;分库:根据业务不同把相关的表切分到不同的数据库中;或者水平分库,可以采用通过一个关键字取模的方式,来对数据进行访问。分区:表面上还是一张表,但数据散列在硬盘多个位置,这样一来,多块硬盘同时处理不同的请求,从而提高磁盘I/O读写性能
  9. 硬件配置升级

索引:

索引需要存放在硬盘上,这样在硬盘上进行查询时,就会产生硬盘的I/O操作,索引的查找次数也就是硬盘I/O的操作次数,所以索引需要减少硬盘的I/O操作

为什么使用数据索引能提高效率

  1. 数据索引的存储是有序的
  2. 在有序的情况下,通过索引查询一个数据是无需遍历索引记录的
  3. 极端情况下,数据索引的查询效率为二分法查询效率,趋近于 log2(N)

B+树索引

B+树是一个平衡的多叉树,从根节点到每个叶子节点的高度差值不超过1,而且同层级的节点间有指针相互链接,是有序的 B+树是从根节点到叶子节点逐级查找,类似目录->章->节->页码 file (扩展:哈希索引就是采用一定的哈希算法,把键值换算成新的哈希值,只需一次哈希算法即可,是无序的)

Mysql不用二叉树做索引的原因

普通的二分查找树,有可能退化成一条链表,这是查找数据的时间复杂度为 O(n)。二叉树深度越大,查找的次数越多,性能越差

为此,有平衡二叉搜索树(AVL树, 树堆,红黑树、伸展树等)搜索时间复杂度是 O(log2n)。

对于 数据库索引来说,O(log2n)仍然太大。

B+树相对于 二叉树,更加矮胖,可以减少查找次数(I/O次数)

MySQL联合索引

联合索引是两个或更多个列上的索引

对于联合索引:Mysql从左到右的使用索引中的字段,一个查询可以只使用索引中的一部份但只能是最左侧部分

例如索引是key index (a,b,c). 可以支持a 、 a,b 、 a,b,c 3种组合进行查找,但.不支持 b,c进行查找 当最左侧字段是常量引用时,索引就十分有效

利用索引中的附加列,您可以缩小搜索的范围,但使用一个具有两列的索引不同于使用两个单独的索引

复合索引的结构与电话簿类似,人名由姓和名构成,电话簿首先按姓氏对进行排序,然后按名字对有相同姓氏的人进行排序。 如果您知道姓,电话簿将非常有用;如果您知道姓和名,电话簿则更为有用,但如果您只知道名不知道姓,电话簿将没有用处

什么情况下应不建或少建索引

  1. 表记录太少
  2. 经常插入、删除、修改的表
  3. 数据重复且分布平均的表字段,假如一个表有10万行记录,有一个字段A只有T和F两种值,且每个值的分布概率大约为50%,那么对这种表A字段建索引一般不会提高数据库的查询速度。
  4. 经常和主字段一块查询但主字段索引值比较多的表字段

mysql字段数据类型优化

链接

存储引擎

mysql常用存储引擎分为InnoDB,MyISAM

InnoDB与MylSAM区别

  1. InnoDB支持事务,MyISAM不支持(如在一些列增删改中只要哪个出错还可以回滚还原,而MyISAM就不可以了)
  2. InnoDB支持外键,MyISAM不支持
  3. InnoDB支持行锁
  4. MyISAM适合查询以及插入为主的应用,InnoDB适合频繁修改以及涉及到安全性较高的应用
  5. InnoDB不支持FULLTEXT类型的索引
  6. InnoDB中不保存表的行数,InnoDB需要扫描一遍整个表来计算有多少行,但是MyISAM只要简单的读出保存好的行数即可。注意的是,当查询条数语句包含where条件时MyISAM也需要扫描整个表
  7. 清空整个表时,InnoDB是一行一行的删除,效率非常慢。MyISAM则会重建表
  8. 对于自增长的字段,InnoDB中必须包含只有该字段的索引,但是在MyISAM表中可以和其他字段一起建立联合索引
  9. MyISAM是默认引擎,InnoDB需要指定
  10. MYISAM的性能更优,占用的存储空间少

分表

垂直分表

就是将一个表按照字段来分,每张表保证有相同的主键就好。一般来说,将常用字段和不常用字段分表来放。

优势:比没有分表来说,提高了查询速度,降低了查询结果所用内存;

劣势:没有解决大量记录的问题,对于单表来说随着记录增多,性能还是下降很快;

file

水平拆分

把原来一个表拆分成多个表,每个表的结构都一样,解决单表数据量大的问题。

如:现在有100万用户在线访问,就要进行至少100万次请求,如果将它分成100个表即user0~user99,那么利用一定的算法我们就分担了单个表的访问压力,每个表只有1万个请求(当然,这是理想情况下!) 实现mysql 分表的关键在于:设计良好的算法来确定"什么时候情况下访问哪个表"。一般采用有多少分表就 取模(%)多少的方式,确定数据在哪个表中或插入数据到那个表中,分表后每个分表的主键不要自增,否则对程序员带来不必要麻烦

主从复制与读写分离

主从复制

分为同步复制和异步复制,实际复制架构中大部分为异步复制。 链接 复制的基本过程如下:

  1. Slave上面的IO进程连接上Master,并请求从指定日志文件的指定位置(或者从最开始的日志)之后的日志内容;
  2. Master接收到来自Slave的IO进程的请求后,通过负责复制的IO进程根据请求信息读取制定日志指定位置之后的日志信息,返回给Slave 的IO进程。返回信息中除了日志所包含的信息之外,还包括本次返回的信息已经到Master端的bin-log文件的名称以及bin-log的位置;  
  3. Slave的IO进程接收到信息后,将接收到的日志内容依次添加到Slave端的relay-log文件的最末端,并将读取到的Master端的 bin-log的文件名和位置记录到master-info文件中,以便在下一次读取的时候能够清楚的告诉Master“我需要从某个bin-log的哪个位置开始往后的日志内容,请发给我”;   4.Slave的Sql进程检测到relay-log中新增加了内容后,会马上解析relay-log的内容成为在Master端真实执行时候的那些可执行的内容,并在自身执行。

读写分离

基本原理是让主数据库处理写方面事务,让从库处理SELECT查询链接

使用mysql-proxy来实现或者使用Amoeba for MySQL实现,其是一款优秀的中间件软件,同样可以实现读写分离,负载均衡等功能,并且稳定性也高于mysql-proxy

分库

随着数据量增加也许单台DB的存储空间不够,随着查询量的增加单台数据库服务器已经没办法支撑。这个时候可以再对数据库进行水平区分或按照业务区分。

分区

表分区,是指根据一定规则,将数据库中的一张表分解成多个更小的,容易管理的部分。从逻辑上看,只有一张表,但是底层却是由多个物理分区组成

表分区与分表的区别

分表:指的是通过一定规则,将一张表分解成多张不同的表。比如将用户订单记录根据时间成多个表。 分表与分区的区别在于:分区从逻辑上来讲只有一张表,而分表则是将一张表分解成多张表。

表分区有什么好处

  1. 与单个磁盘或文件系统分区相比,可以存储更多的数据
  2. 分区表更容易维护。例如:想批量删除大量数据可以清除整个分区
  3. 优化查询,在where语句中包含分区条件时,可以只扫描一个或多个分区表来提高查询效率;涉及sum和count语句时,也可以在多个分区上并行处理,最后汇总结果。 4.避免某些特殊的瓶颈,例如InnoDB的单个索引的互斥访问,ext3问价你系统的inode锁竞争等。

分区表的限制因素

  1. 一个表最多只能有1024个分区
  2. 如果分区字段中有主键或者唯一索引的列,那么所有有主键列和唯一索引列都必须包含进来。即:分区字段要么不包含主键或者索引列,要么包含全部主键和索引列。
  3. 分区表中无法使用外键约束
  4. MySQL的分区适用于一个表的所有数据和索引,不能只对表数据分区而不对索引分区,也不能只对索引分区而不对表分区,也不能只对表的一部分数据分区。

MySQL支持的分区类型有哪些

  1. RANGE分区: 这种模式允许将数据划分不同范围。例如可以将一个表通过年份划分成若干个分区
  2. LIST分区: 这种模式允许系统通过预定义的列表的值来对数据进行分割。按照List中的值分区,与RANGE的区别是,range分区的区间范围值是连续的。
  3. HASH分区 :这中模式允许通过对表的一个或多个列的Hash Key进行计算,最后通过这个Hash码不同数值对应的数据区域进行分区。例如可以建立一个对表主键进行分区的表。
  4. KEY分区 :上面Hash模式的一种延伸,这里的Hash Key是MySQL系统产生的。
猜你喜欢