python数据库

mysql 数据库存储原理

储存过程是一个可编程的函数，它在数据库中创建并保存。它可以有SQL语句和一些特殊的控制结构组成。当希望在不同的应用程序或平台上执行相同的函数，或者封装特定功能时，存储过程是非常有用的。数据库中的存储过程可以看作是对编程中面向对象的方法的模拟。它允许控制数据的访问方式。存储过程通常有以下优点：

• 存储过程能实现较快的执行速度
• 存储过程允许标准组件是编程
• 存储过程可以用流程控制语句编写，有很强的灵活性，可以完成复杂的判断和较复杂的运算
• 存储过程可被作为一种安全机制来充分利用
• 存储过程能够减少网络流量

事务

• 原子性 Atomicity：事务中的全部操作在数据库中是不可分割的，要么全部执行，要么均不执行；

• 一致性 Consistency：几个并行执行的事务，其执行结果必须与按某一顺序串行执行的结果相一致；

• 隔离性 Isolation：事务的执行不受其他事务的干扰，事务执行的中间结果对其他事务必须是透明的；

• 一致性 Durability：对于任意已提交事务，系统必须保证该事务对数据库的改变不被丢失，即使数据库出现故障；

MySQL事务方法

• 用begin/rollback/commit 实现
  begin 开始一个事务
  rollback 事务回滚
  commit 事务确认

• set改变MySQL的自动提交模式
  set autocommit=0 禁止自动提交
  set autocommit=1 开启自动提交

数据库索引

数据库索引，是数据库管理系统中一个排序的数据结构，以协助快速查询、更新数据库表中数据。索引的实现通常使用B_TREE。
B_TREE索引加速了数据访问，因为存储引擎不会再去扫描整张表得到需要的数据；相反，它从根节点开始，根节点保存了子节点的指针，存储引擎会根据指针快速寻找数据。

在表格上面创建某个一个唯一的索引。唯一索引意味着两个行不能拥有相同的索引值。

create unique index 索引名称 on 表名称（列名称）

数据库索引，是数据库管理系统中一个排序的数据结构，以协助快速查询、更新数据库表中数据。索引的实现通常使用B树及其变种B+树。

在数据库之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查找算法。这种数据结构，就是索引。

索引存在意义

• 通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性；

• 可以大大加快数据的检索速度，这也是创建索引的最主要原因；

• 可以加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义；

• 在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间；

• 通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能。

BTree、Hash

BTree (多路搜索树，并不是二叉树)是一种常见的数据结构。使用BTree结构可以显著减少定位纪录时所经历的中间过程，从而加快存取速度。这个数据结构一般用于数据库的索引，综合效率较高。

不适合：
单列索引的列不能包含null的纪录，符合索引的各个列不能包含同时为null的纪录，否则会全表扫描；
不适合键值较少的列（重复数据较多的列）；前导模糊查询不能利用索引（like ‘%XX’ 或者like '%XX%'）

Hash散列索引是根据HASH算法来构建的索引。虽然Hash索引效率高，但是Hash索引本身由于器特殊性也带来了很多限制和弊端，主要有以下几个方面。

适合：
精确查找非常快（包括= <> 和 in），其检索效率非常高，索引的检索可以一次定位，不像BTree索引需要从根节点到枝节点，所以Hash索引的查询效率要远高于BTree索引。

不适合：
不适合模糊查询和范围查询（包括like,>,<,between and等），由于Hash索引比较的是进行Hash运算之后的Hash值，所以它智能用于等值的过滤，不能用于基于范围的过滤，因为经过相应的Hash算法处理之后的Hash值的大小关系，并不能保证和Hash运算前完全一样；

数据库无法利用索引的数据来提升排序性能，同样是因为Hash值的大小不确定；
符合索引不能利用部分索引字段查询，Hash索引在计算Hash值的时候是组合索引键合并后再一起计算Hash值，而不是单独计算Hash值，所以通过组合索引的前面一个或几个索引键进行查询的时候，Hash索引也无法被利用。
同样不适合键值较少的列（重复值较多的列）。

redis、mysql

redis 是内存数据库，数据保存在内存中，速度块；

MySQL 是关系型数据库，持久化存储，存放在磁盘里面，功能强大。检索的话，会设计到一定的IO，数据访问也就慢。

MySQL引擎

InnoDB 支持事务，MyISAM不支持。事务是一种高级的处理方式，如在一些列增删改中只要那个出错还可以回滚还原，而MyISAM就不可以了；

MyISAM 适合查询以及插入为主的应用，InnoDB 适合频繁修改以及设计到安全性较高的应用；

InnoDB 支持外键，MyISAM 不支持；
MyISAM 是默认的引擎，InnoDB需要指定；
InnoDB 不支持FULLTEXT类型的索引；
InnoDB中保存表的行数，如 select count() from table 时，InnoDB需要扫描仪表整个表来计算有多少行，但是MyISAM只要简单的独处保存好的行数即可。注意的是，当count() 语句包含where条件时MyISAM也需要扫描整个表；

对于自增长的字段，InnoDB中必须包含只有该字段的索引，但是在MyISAM表中可以和其他字段一起简历联合索引；清空整个表时，InnoDB是一行一行的删除，效率非常慢。MyISAM则会重建表；

InnoDB 支持行锁（默写情况下还是锁郑彪，如update table set a=1 where user like '%lee%'）

Redis、MongoDB

MongoDB和Redis 都是Nosql，采用结构性数据存储，而这在使用场景中，存在一定的区别，这也主要由于而这在存储映射的处理过程，持久话的处理方法不同。MongoDB建议集群不熟，更多的考虑到集群方案，Redis更偏向于进程孙旭写入，虽然支持集群，也仅限于主从模式。

redis优点：
读写性能优异，支持数据持久化，支持AOF和RDB两种持久化方式。
支持主从复制，主机会自动将数据同步到从机，可以进行读写分离。
数据结构丰富，除了支持str类型的value外，还支持string、Hash、set、sortedset、list等数据结构。

redis缺点：
redis不具备自动容错和恢复功能，主机从机的宕机都会导致前端部分读写请求失败，需要等待机器重启或者手动切换前端的IP才能恢复；
主机宕机，宕机前有部分数据未能即使同步到从机，切换IP后海辉引入数据不一致的问题，降低了系统的可用性。
Redis的主从复制采用全量赋值，赋值过程中主机会fork出一个子进程对内存做一份快照，并将子进程的内存快照保存为文件发送给从机，这一过程需要确保主机有足够多的空余内存。
若快照文件较大，对集群的服务能力会产生较大的影响，而且赋值过程是在从机新加入集群或者从机和主机网络断开重连时都会进行，也就是网络波动都会造成主机和从机间的一次全量的数据赋值，这对实际的系统运营造成了不小的麻烦。
redis较难支持在线扩容，在集群容量达到上线时在线扩容会变得很复杂，为避免这一问题，运维人员在系统上线时必须确保有足够多的控件，这对资源造成了很大的浪费。

MongoDB优点：
弱一致性（最终一致），更能保证用户的访问速度文档结构的存储方式，能够更便捷的获取数内置GridFS，高效存储二进制大对象（比如照片和视频）。
支持复制集、主备、互为主备、自动分片等特性；
动态查询，全索引支持，扩展到内部对象和内嵌数组；

MonoDB缺点：
不支持事务，MonoDB占用控件过大，维护工具不够成熟。

数据库优化方案

优化索引、SQL语句、分析慢查询；

设计表的时候严格根据数据库的设计范式来设计数据库；

使用缓存，把经常访问到的数据而且不需要经常变化的数据放在缓存中，能节约磁盘IO；

优化硬件；采用SSD，使用磁盘队列计数（RAIDO，RAID，RDID5）等；

采用MySQL内部自带的表分区计数，把数据分层不同的文件，能够提高磁盘的读取效率；

垂直分表，把一些不经常读的数据放在一张表里，节约磁盘I/O;

主从同步读写；采用主从复制把数据库的读操作和写如操作分离开来；

分库分表粪机器（数据量特别大），主要的原理就是数据路由；

选择何时的表引擎，参数上的优化；

进行架构级别的缓存，静态化和分布式；

不采用全文索引；

采用更快的存储方式，例如NoSQL存储经常访问的数据；

海量数据存储

水平切分数据库：可以降低单台机器的负担，同时最大限度的降低了宕机造成的损失；分库降低了单点机器的负载；分表，提高了数据操作的效率；

负载均衡策略：可以降低单台机器的访问负载，降低宕机的可能性；

集群方案：解决了数据库宕机带来的单点数据库布鞥呢访问的问题；

读写分离策略：最大限度了提高应用中读取数据的熟读和并发量；

数据库高并发问题

解决高并发：分表分库、数据库索引、redis缓存数据库、读写分离、负载均衡集群，将大量的并发请求分担道多个处理节点。由于单个处理节点的故障不影响整个服务，负载均衡集群同时也实现了高可用性。

负载均衡

负载均衡集群是由一组相互独立的计算机系统构成，通过常规网络或专用网络进行连接，由路由器衔接在一起，各节点相互协作、共同负载、均衡压力，对客户端来说，整个集群可以视为一台具有超高性能的独立服务器。

实现原理：
实现数据库的堵在均衡技术，首先要有一个可以控制连接数据库的控制端。它阶段了数据库和程序的直接连接，由所有的程序来访问这个中间层，然后再由中间层来访问数据库。这样我门就可以具体控制访问某个数据库了，然后还可以根据数据库的当前负载采取有效的均衡策略，来调整每次连接到哪个数据库；

实现多数据库同步：
对于负载均衡，最重要的就是所有服务器的数据都是实时同步的。这是一个集群锁必须的，因为，如果数据不实时、不同步，那么用户从一台服务器读出的数据就有别于从另一台服务器读出的数据，这是不能允许的。所以必须实现数据库的数据同步。
这样，在查询的时候就可以有多个资源，实现均衡。比较常用的方法是Moebius for SQL Server集群，Moebius for SQLServer 集群采用将核心程序主流在每个机器的数据库中的办法，这个核心程序称为 Moebius for SQL Server 中间件，主要作用是监测数据库内数据的变化并将变化的数据同步到其他数据库中。数据同步完成后客户端才会得到响应，同步过程是并发完成的，所以同步到多个数据库和同步到一个数据库的时间基本相等；

另外同步的过程是在事务的环境下完成的，保证了多分数据在任何时刻数据的一致性。正因为Moebius中间件宿主在数据库中的创新，让中间件不但能知道数据的变化，而且知道引起数据变化的SQL语句，根据SQL语句的类型智能的采取不同的数据同步的策略以保证数据同步成本的最小化。数据条数很少，数据内容也不大，则直接同步数据条数很少，但是里面包含大数据类型，比如文本，二进制数；

先对数据进行压缩然后再同步，从而减少网络贷款的占用和传输所用的时间。数据条数很多，此时中间件会拿到造成数据变化的SQL语句，然后对SQL语句进行解析，分析其执行计划和执行成本，并选择是同步数据还是同步SQL语句到其他的数据库。此种情况应用在对表结构进行调整活着批量更改数据的时候非常有用。

优点：
扩展性强，当系统要更高的数据库处理速度时，只要简单地增加数据库服务器就可以得到扩展；
可维护性，当某节点发生故障时，系统会自动检测故障并转移故障节点的应用，保证数据库的持续工作；
安全性，因为数据会同步的多台服务器上，可以实现数据集的冗余，通过多分数据来保证安全性。另外它成功地将数据库放到了内网之中，更好地保护了数据库的安全性；
易用性，对应用来说完全透明，集群暴露只来的就是一个IP。

缺点：
不能够按照web服务器的处理能力分配负载；负载均衡器（控制端）故障，会导致整个数据库系统瘫痪。

数据库备份

# 备份数据库
mysql dump -h host -u root -p dbname > dbname_backup.sql

# 恢复数据库
mysqladmin -h myhost -u root -p create adname

mysqldump -h host -u root -p dbname_backup.sql

优化查询效率

• 存储引擎选择：如果数据表需要事务处理，应该考虑使用InnoDB，因为它完全符合ACID特征，如果不需要事务处理，使用默认存储引擎MyISAM；

• 分表分库，主从

• 对查询进行优化，要尽量避免全表扫描，首先应考虑在where 及order by 设计的列上简历索引；

• 应尽量避免在where子句中对字段进行null值判断，否则将导致引擎放弃使用索引而进行全表扫描；

• 应尽量避免在where 子句中使用！=或<>操作符，否则将引擎放弃使用索引而进行全表扫描；

• 应尽量避免在where 子句中使用or来连接条件，如果一个字段有索引，一个字段没有索引，将导致引擎放弃使用索引而进行全表扫描；

• update语句，如果值更改1-2个字段，不要update全部字段，否则频繁调用会引起明显的性能消耗，同时带来大量日志；

• 对于多张大数据量的表join,要先分页再join，否则逻辑读会很高，性能很差。

5T数据量优化

尽量使数据库一次性写入Data File

减少数据库的checkpoint 操作

程序上尽量缓冲数据，进行批量式插入与提交

减少系统的IO冲突

查找查询慢的语句

slow——query_log 这个参数设置为on，可以捕获这行时间超过一定数值的SQL语句；

long_query_time 当SQL局域执行时间超过此数值时，就会被纪录到日志中，建议设置为1或者更短。