数据库
1.共享锁和排他锁
共享锁
共享锁又称读锁，是读取操作创建的锁。如果事务T对数据A加上共享锁后，则其他事务只能对A再加共享锁，不能加排他锁。获准共享锁的事务只能读数据，不能修改数据。
排他锁
排他锁又称写锁，如果事务T对数据A加上排他锁后，则其他事务不能再对A加任何类型的封锁。获准排他锁的事务既能读数据，又能修改数据。
意向锁
意向锁是表级锁，但表示事务正在读或写某一行记录，而不是整个表。所以意向锁之间不会产生冲突，真正的冲突在加行锁时检查。
作用：意向锁提高了锁定父节点时的效率（相当于一个全局标记位）
原理：可以直接通过目标节点的意向锁便得知是否可以对目标表进行加锁，而不需要遍历该节点的所有子节点。
1）意向共享锁（IS）：表示事务准备给数据行加入共享锁，也就是说一个数据行加共享锁前必须先取得该表的IS锁
2）意向排他锁（IX）：表示事务准备给数据行加入排他锁，说明事务在一个数据行加排他锁前必须先取得该表的IX锁。
2.MySQL中的行级锁、表级锁、页级锁
（1）行级锁
行级锁是MySQL中锁定粒度最细的一种锁，表示只针对当前操作的行进行加锁。行级锁能减少数据库操作的冲突。加锁粒度最小，但加锁的开销也最大。行级锁分为共享锁和排他锁。
特点：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度最高。
（2）表级锁
表级锁是MySQL中锁定粒度最大的一种锁，表示对当前操作的整张表加锁，实现简单，资源消耗较少，被大部分MySQL引擎支持。最常使用的MYISAM与INNODB都支持表级锁。
特点：开销小，加锁快；不会出现死锁；锁定粒度大，发出锁冲突的概率最高，并发度最低。
（3）页级锁
页级锁是MySQL中锁定粒度介于行级锁和表级锁中间的一种锁。表级锁速度快，但冲突多，行级冲突少，但速度慢。所以取了折衷的页级，一次锁定相邻的一组记录。BDB（Berkeley DB）支持页级锁。
特点：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般。
3.事务及其ACID特性
事务就是一组原子性的SQL查询，或者说一个独立的工作单元。
（1）原子性（Atomicity）
事务作为一个整体被执行，包含在其中的对数据库的操作要么全部被执行，要么都不执行。
例如：银行取款事务分为2个步骤(1)存折减款(2)提取现金。不可能存折减款，却没有提取现金。2个步骤必须同时完成或者都不完成。
（2）一致性（Consistency）
事务应确保数据库的状态从一个一致状态转变为另一个一致状态。一致状态的含义是数据库中的数据应满足完整性约束。
例如：完整性约束a+b=10，一个事务改变了a，那么b也应随之改变。
（3）隔离性（Isolation）
多个事务并发执行时，一个事务的执行不应影响其他事务的执行。
（4）持久性（Durability）
已被提交的事务对数据库的修改应该永久保存在数据库中。
4.事务隔离性级别
我们先看看如果不考虑事务的隔离性，会发生的几种问题：
（1）脏读
脏读是指在一个事务处理过程里读取了另一个未提交的事务中的数据。
（2）不可重复读
不可重复读是指对于数据库中的某个数据，一个事务范围内多次查询却返回了不同的数据值，这是由于在查询间隔，被另一个事务修改并提交了。
不可重复读和脏读的区别是，脏读是某一事务读取了另一个事务未提交的脏数据，而不可重复读则是读取了前一事务提交的数据。
（3）虚读(幻读)
幻读是事务非独立执行时发生的一种现象。例如事务T1对一个表中所有的行的某个数据项做了从“1”修改为“2”的操作，这时事务T2又对这个表中插入了一行数据项，而这个数据项的数值还是为“1”并且提交给数据库。而操作事务T1的用户如果再查看刚刚修改的数据，会发现还有一行没有修改，其实这行是从事务T2中添加的，就好像产生幻觉一样，这就是发生了幻读。
幻读和不可重复读都是读取了另一条已经提交的事务（这点与脏读不同），所不同的是不可重复读查询的都是同一个数据项，而幻读针对的是一批数据整体。
不可重复读的重点是修改：同样的条件，读取过的数据，再次读取出来发现值不一样了。
幻读的重点在于新增或者删除：同样的条件，第1次和第2次读出来的记录数不一样。
事务隔离性级别：
ØSerializable
(串行化)：可避免脏读、不可重复读、幻读的发生。
ØRepeatable
read (可重复读)：可避免脏读、不可重复读的发生。
ØRead committed
(读已提交)：可避免脏读的发生。
ØRead
uncommitted (读未提交)：最低级别，任何情况都无法保证。
以上四种隔离级别最高的是Serializable级别，最低的是Read uncommitted级别，当然级别越高，执行效率就越低。像Serializable这样的级别，就是以锁表的方式使得其他的线程只能在锁外等待，所以平时选用何种隔离级别应该根据实际情况。在MySQL数据库中默认的隔离级别为Repeatable
read (可重复读)。
5.MySQL中隔离级别实现
隔离级别用于表述并发事务之间的相互干扰程度，基于锁机制进行并发控制。
1）可序列化Serializable
读加共享锁，写加排他锁，读写互斥。使用的悲观锁的理论，实现简单，数据更加安全，但是并发能力非常差。
2）可重复读Repeatable read和读已提交Readcommitted
无论RC还是RR，MySQL都是通过MVCC(Multi-Version Concurrency Control )机制去实现的。MVCC是多版本的并发控制协议，以乐观锁为理论基础。它和基于锁的并发控制最大的区别和优点是：读不加锁，读写不冲突。它将每一个更新的数据标记一个版本号，在更新时进行版本号的递增，插入时新建一个版本号，同时旧版本数据存储在undo日志中。而对于读操作，因为多版本的引入，就分为快照读和当前读。快照读只是针对于目标数据的版本小于等于当前事务的版本号，也就是说读数据的时候可能读到旧的数据，但是这种快照读不需要加锁，性能很高。当前读是读取当前数据的最新版本，但是更新等操作会对数据进行加锁，所以当前读需要获取记录的行锁，存在锁争用的问题。
RC和RR都是基于MVCC实现，但是读取的快照数据是不同的。RC级别下，读取的总是最新的数据，也就出现了上面的例子，一个事务中两次读到了不同的结果。而RR级别总是读到小于等于此事务的数据，也就实现了可重复读。
3）读未提交Read uncommitted
一个事务可以读取到其它事务未提交的更改。
6.MySQL存储引擎以及各个引擎的区别
存储引擎负责MySQL中数据的存储和提取。在Oracle和SQL Server等数据库中只有一种存储引擎，所有数据存储管理机制都是一样的。而MySql数据库提供了多种存储引擎。
（1）InnoDB存储引擎
5.5版本后Mysql的默认数据库，事务型数据库的首选引擎，支持ACID事务，支持行级锁定。该存储引擎提供了具有提交、回滚和崩溃恢复能力的事务安全。但是对比MyISAM引擎，写的处理效率会差一些，并且会占用更多的磁盘空间以保留数据和索引。支持自动增长列，支持外键约束。
（2）MyISAM存储引擎
不支持事务、也不支持外键，优势是访问速度快，对事务完整性没有要求或者以select，insert为主的应用基本上可以用这个引擎来创建表。建立MyISAM引擎的表时，会在本地磁盘上创建三个文件，文件名为表名。例如创建了XXX表，会生成三个文件：XXX.frm存储表定义、XXX.MYD存储数据和XXX.MYI存储索引。支持表锁。
MyISAM表是独立于操作系统的，这说明可以轻松地将其从Windows服务器移植到Linux服务器。MyISAM存储引擎特别适合在以下几种情况下使用：
Ø选择密集型的表。MyISAM在筛选大量数据时非常迅速，这是它最突出的优点。
Ø插入密集型的表。MyISAM的并发插入特性允许同时选择和插入数据。例如：MyISAM存储引擎很适合管理邮件或Web服务器日志数据。
（3）MERGE存储引擎
Merge存储引擎是一组MyISAM表的组合，这些MyISAM表必须结构完全相同，merge表本身并没有数据，对merge类型的表可以进行查询，更新，删除操作，这些操作实际上是对内部的MyISAM表进行的。
（4）MEMORY存储引擎
Memory存储引擎使用存在于内存中的内容来创建表。memory类型的表访问非常的快，因为它的数据是放在内存中的，并且默认使用HASH索引，但是一旦服务关闭，表中的数据就会丢失掉。MEMORY存储引擎的表可以选择使用BTREE索引或者HASH索引，两种不同类型的索引有其不同的使用范围。
Hash索引
优点：Hash索引检索效率非常高，索引的检索可以一次定位，不像B-Tree索引需要从根节点到枝节点，最后才能访问到叶子节点这样多次的IO访问，所以Hash索引的查询效率要远高于B-Tree索引。
缺点：Hash算法是基于等值计算的，所以对于“like”等范围查找hash索引无效。
（5）CSV
CSV存储引擎是基于CSV格式文件存储数据。
ØCSV存储引擎因为自身文件格式的原因，所有列必须强制指定NOT NULL。
ØCSV引擎也不支持索引，不支持分区。
ØCSV存储引擎也会包含一个存储表结构的.frm文件，还会创建一个.csv存储数据的文件，还会创建一个同名的元信息文件，该文件的扩展名为.CSM，用来保存表的状态及表中保存的数据量。
Ø每个数据行占用一个文本行。
CSV引擎可以在数据库运行时拷入或烤出文件，可以将Excel等电子表格软件中的数据存储为CSV文件，然后复制到MySQL数据目录下，就能在MySQL中打开使用。同样，如果将数据写入到一个CSV引擎表，其他的外表程序也能立即从表的数据文件中读取CSV格式的数据。因此，CSV引擎可以作为一种数据交换的机制，非常有用。
（6）ARCHIVE
Archive是归档的意思，在归档之后很多的高级功能就不再支持了，仅仅支持最基本的插入和查询两种功能。在MySQL 5.5版以前，Archive是不支持索引，但是在MySQL 5.5以后的版本中就开始支持索引了。Archive拥有很好的压缩机制，它使用zlib压缩库，在记录被请求时会实时压缩，所以它经常被用来当做仓库使用。
（7）BLACKHOLE
黑洞存储引擎，所有插入的数据并不会保存，BLACKHOLE引擎表永远保持为空，写入的任何数据都会消失，
7.索引及其实现方式B树和B+树的区别
索引定义：索引是对数据库表中一个或多个列的值进行排序的结构。
索引目的：数据库索引好比是一本书前面的目录，提高查询效率。
为表设置索引要付出代价的：
Ø增加了数据库的存储空间
Ø在插入和修改数据时要花费较多的时间(因为索引也要随之变动)。
索引分类
聚集索引：索引项的顺序与表中记录的物理顺序一致。对于聚集索引，叶子结点即存储了真实的数据行，不再有另外单独的数据页。
非聚集索引：表数据存储顺序与索引顺序无关。对于非聚集索引，叶结点包含索引字段值及指向数据页数据行的逻辑指针，其行数量与数据表行数据量一致。
注意：一个表只能有一个聚集索引，但是可以有多个非聚集索引。
唯一索引：不允许具有索引值相同的行，但可以为NULL，不能有多个NULL。
主键索引：主键索引是唯一索引的特殊类型。数据库表经常有一列或多列组合，其值唯一标识表中的每一行，该列称为表的主键。在数据库中为表定义主键将自动创建主键索引。
（1）磁盘IO与预读
考虑到磁盘IO是非常高昂的操作，计算机操作系统做了一些优化，当一次IO时，不光把当前磁盘地址的数据，而是把相邻的数据也都读取到内存缓冲区内，因为局部预读性原理告诉我们，当计算机访问一个地址的数据的时候，与其相邻的数据也会很快被访问到。每一次IO读取的数据我们称之为一页(page)。具体一页有多大数据跟操作系统有关，一般为4k或8k，也就是我们读取一页内的数据时候，实际上才发生了一次IO，这个理论对于索引的数据结构设计非常有帮助。
（2）索引数据结构
目前大部分数据库系统及文件系统都采用B-Tree或其变种B+Tree作为索引结构。
（3）B-树和B+树
B-树
又叫平衡多路查找树。一棵m阶的B-树(m叉树)的特性如下：
1)树中每个结点至多有m个孩子；
2)除根结点和叶子结点外，其它每个结点至少有⌈m/2⌉个孩子；
3)若根结点不是叶子结点，则至少有2个孩子；
4)所有叶子结点都出现在同一层，叶子结点不包含任何关键字信息(可以看做是外部接点或查询失败的接点，实际上这些结点不存在，指向这些结点的指针都为空)；
5)每个非终端结点中包含有n个关键字信息：(n，A0，K1，A1，K2，A2，......，Kn，An)。其中，
a) Ki (i=1...n)为关键字，且关键字按顺序排序Ki < K(i-1)。
b) Ai为指向子树根的接点，且指针A(i-1)指向子树种所有结点的关键字均小于Ki，但都大于K(i-1)。
c)关键字的个数n必须满足：⌈m/2⌉-1 <= n<= m-1
首先在最低层的某个非终端结点中添加一个关键字，若该结点的关键字个数不超过m-1，则插入完成。否则，要产生结点的"分裂"。取一个新结点，将插入key后的原结点从中间位置将其中的关键字分为两部分，左部分包含的关键字放在原结点中，右部分包含的关键字放在新的结点中，中间位置插入到原结点的父结点中，父结点也进行类似操作。
现在我们模拟查找文件29的过程：
1)根据根结点指针找到文件目录的根磁盘块1，将其中的信息导入内存。【磁盘IO操作1次】
2)此时内存中有两个文件名17，35和三个存储其他磁盘页面地址的数据。根据算法我们发现17<29<35，因此我们找到指针p2。
3)根据p2指针，我们定位到磁盘块3，并将其中的信息导入内存。【磁盘IO操作2次】
4)此时内存中有两个文件名26，30和三个存储其他磁盘页面地址的数据。根据算法我们发现26<29<30，因此我们找到指针p2。
5)根据p2指针，我们定位到磁盘块8，并将其中的信息导入内存。【磁盘IO操作3次】
6)此时内存中有两个文件名28，29。根据算法我们查找到文件29，并定位了该文件内存的磁盘地址。
分析一下上面的过程，我们发现需要3次磁盘IO操作和3次内存查找操作。关于内存中的文件名查找，由于是一个有序表结构，可以利用折半查找提高效率。至于3次磁盘IO操作时影响整个B-树查找效率的决定因素。
B+树
一棵m阶的B+树和m阶的B-树的差异在于：
Ø有n棵子树的结点中含有n个关键字
Ø所有的叶子结点中包含了全部关键字的信息，及指向含这些关键字记录的指针，且叶子结点本身依关键字的大小自小而大排序链接。
Ø所有的非终端结点可以看成是索引部分，结点中仅含有其子树（根结点）中的最大（或最小）关键字。
创建索引注意事项
一般来说，应该在这些列上创建索引：
1）在经常需要搜索的列上，可以加快搜索的速度；
2）在作为主键的列上，强制该列的唯一性和组织表中数据的排列结构；
3）在经常用在连接的列上，这些列主要是一些外键，可以加快连接的速度，但字段应有相同的类型；
4）在经常需要根据范围进行搜索的列上创建索引，因为索引已经排序，其指定的范围是连续的；
5）在经常需要排序的列上创建索引，因为索引已经排序，这样查询可以利用索引的排序，加快排序查询时间；
6）在经常使用在WHERE子句中的列上面创建索引，加快条件的判断速度。
建索引的几大原则
1）最左前缀匹配原则，mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配，比如a = 1 and b = 2 and c
> 3 and d = 4如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。
2）=和in可以乱序，比如a = 1 and b = 2 and c = 3建立(a,b,c)索引可以任意顺序，mysql的查询优化器会帮你优化成索引可以识别的形式。
3）尽量选择区分度高的列作为索引，区分度的公式是count(distinct
col)/count(*)，表示字段不重复的比例，比例越大我们扫描的记录数越少，唯一键的区分度是1，而一些状态、性别字段可能在大数据面前区分度就是0，那可能有人会问，这个比例有什么经验值吗？使用场景不同，这个值也很难确定，一般需要join的字段我们都要求是0.1以上，即平均1条扫描10条记录。
4）索引列不能参与计算，保持列“干净”，比如from_unixtime(create_time)
= ’2014-05-29’就不能使用到索引，原因很简单，b+树中存的都是数据表中的字段值，但进行检索时，需要把所有元素都应用函数才能比较，显然成本太大。所以语句应该写成create_time
= unix_timestamp(’2014-05-29’)。
5）尽量的扩展索引，不要新建索引。比如表中已经有a的索引，现在要加(a,b)的索引，那么只需要修改原来的索引即可。
6）用LIKE关键字的查询，在使用LIKE关键字进行查询的查询语句中，如果匹配字符串的第一个字符为“%”，索引不起作用，只有“%”不在第一个位置，索引才会生效。
7）当修改性能远远大于检索性能时，不应该创建索引。这是因为，修改性能和检索性能是互相矛盾的。当增加索引时，会提高检索性能，但是会降低修改性能。当减少索引时，会提高修改性能，降低检索性能。因此，当修改操作远远多于检索操作时，不应该创建索引。
索引失效
1）查询条件包含or，当or左右查询字段只有一个是索引，该索引失效
2）组合索引，最左前缀原则
3）使用like模糊查询，当%在前缀时，索引失效
4）隐式转换，如果列类型是字符串，where时一定用引号括起来，否则索引失效
5）当全表扫描速度比索引速度快时，mysql会使用全表扫描，此时索引失效
6）查询条件使用函数在索引列上或者对索引列进行运算
7）没有查询条件
8.范式
1NF【无重复的列】
数据库表的每一列都是不可分割的原子数据项。
2NF【消除部分函数依赖】
在1NF基础上消除非主属性对主码的部分函数依赖。
Ø主属性：一个属性只要在任何一个候选码中出现过，这个属性就是主属性。
Ø非主属性：与上面相反，没有在任何候选码中出现过，这个属性就是非主属性。
简而言之，第二范式就是在第一范式的基础上非主属性完全依赖于主键。
部分函数依赖
设X,Y是关系R的两个属性集合，存在X→Y，若X’是X的真子集，存在X’→Y，则称Y部分函数依赖于X。
3NF【消除传递依赖】
在1NF基础上，任何非主属性不依赖于其它非主属性（在2NF基础上消除传递依赖）
传递函数依赖
在R(U)中，如果X->Y, Y->Z,则称Z对X传递函数依赖。
9.数据库优化策略
数据库设计方面
1）范式与反范式
第三范式是肯定需要遵守的，但第三范式也有缺点：查询时经常需要大量Join，导致查
询效率不高。所以有时候为了性能的考虑，适度的违反第三范式，做适当的冗余设计。
2）增加中间表
对于需要经常联合查询的表，可以建立中间表以提高查询效率。
3）适当的建立索引
索引的字段必须是经常作为查询条件的字段，因为查询速度的提高是以更新、创建、删除的性能为代价的。
4）对表进行水平切分
好的水平切分，有利于程序的实现，也能充分利用到水平分表的优势。比如数据条数很多，上千万级别，而且系统界面中只提供按月份进行查询，那么就可以考虑将大表水平拆分为12个表。
5）对表进行垂直切分
如果表的字段非常多，会占用大量的空间，检索表时需要执行大量I/O，影响性能。这时可以把一些字段切分到另一个表，新表和旧表是一对一映射。如果有些字段不常用又很长，那更应该拆分出去。
6）选择适当的字段类型
主键用自增，char和varchar的选择。
7）选择合适的存储引擎
SQL语句优化
要善于利用工具：慢查询日志slow query log。知道是哪些语句慢了之后，可以通过explain来分析执行计划。
慢查询日志：MySQL的慢查询日志是MySQL提供的一种日志记录，它用来记录在MySQL中响应时间超过阀值的语句，具体指运行时间超过long_query_time值的SQL，则会被记录到慢查询日志中。long_query_time的默认值为10，意思是运行10S以上的语句。默认情况下，MySQL数据库并不启动慢查询日志，需要我们手动来设置这个参数，当然，如果不是调优需要的话，一般不建议启动该参数，因为开启慢查询日志会或多或少带来一定的性能影响。慢查询日志支持将日志记录写入文件，也支持将日志记录写入数据库表。
数据库参数配置
最主要的参数是调整内存。可以用show status看状态，然后确定内存大小。
Ø关闭不必要的二进制日志和慢查询日志；
Ø适度使用查询缓存；
Ø增加MYSQL的最大连接数；
Ø从表中删除大量行后，可运行OPTIMIZE
TABLE数据表名进行碎片整理
合理的硬件资源和操作系统
如果内存超过4G，要使用64位OS和64位MySQL。
10.使用explain查询SQL的执行计划
执行计划是数据库根据SQL语句和相关表的统计信息作出的一个查询方案，这个方案是由查询优化器自动分析产生的。
explain返回各项的含义：
1）id：这一列总是包含一个编号，标识select所属的行。如果在语句中没有子查询或联合，那么只会有唯一的select，于是每一位在这个列中都将显示一个1。否则，内层的select语句一般会顺序编号，对应其在原始语句中的位置。
注意：id列数字越大越先执行，如果说数字一样大，那么就从上往下依次执行。
2）select_type：显示本行是简单或复杂select。如果查询有任何复杂的子查询，则最外层标记为PRIMARY（DERIVED、UNION、UNION RESUlT）。
3）table：对应行正在访问哪一个表，表名或者别名（如果SQL中定义了别名）
4）type:这是重要的列,表明MySQL如何查找表中的行。
ØNULL：MySQL在优化过程中分解语句，执行时甚至不用访问表或索引，例如从一个索引列里选取最小值可以通过单独索引查找完成。
Øsystem：这是const连接类型的一种特例，表仅有一行满足条件。
Øconst：当确定最多只会有一行匹配的时候，MySQL优化器会在查询前读取它而且只读取一次，因此非常快。当主键放入where子句时，mysql把这个查询转为一个常量（高效）
Øeq_ref：类似ref，区别就在使用的索引是唯一索引，对于每个索引键值，表中只有一条记录匹配，简单来说，就是多表连接中使用primary key或者unique key作为关联条件
Øref：使用非唯一索引扫描或者唯一索引的前缀扫描，返回匹配某个单独值的记录行
Ørange范围扫描，一个有限制的索引扫描。key列显示使用了哪个索引。当使用=、<>、>、>=、<、<=、IS NULL、<=>、BETWEEN或者IN操作符,用常量比较关键字列时,可以使用range
Øindex和全表扫描一样。只是扫描表的时候按照索引次序进行而不是行。主要优点就是避免了排序,但是开销仍然非常大。如在Extra列看到Using index，说明正在使用覆盖索引，只扫描索引的数据，它比按索引次序全表扫描的开销要小很多
ØALL全表扫描
5）possible_keys:显示查询使用了哪些索引，表示该索引可以进行高效地查找，但是列出来的索引对于后续优化过程可能是没有用的
6）key：实际使用的索引,如果为空表示没有使用索引。
7）key_len：用于处理查询的索引长度，如果是单列索引，那就整个索引长度算进去，如果是多列索引，那么查询不一定都能使用到所有的列，具体使用到了多少个列的索引，这里就会计算进去，没有使用到的列，这里不会计算进去。
8）ref：显示索引的哪一列被使用了，如果可能的话，是一个常数。
9）rows：表扫描的行数。
10）Extra包含不适合在其他列中显示但十分重要的额外信息
11.慢查询优化基本步骤
（1）开启MySQL慢查询日志
（2）分析慢查询日志，定位有问题的SQL
（3）通过Explain查询和分析SQL的执行计划
（4）优化SQL
常用的套路是在定位到慢查询语句之后，使用EXPLAIN+SQL来了解MySQL在执行这条数据时的一些细节，比如是否进行了优化、是否使用了索引等等。基于Explain的返回结果我们就可以根据MySQL的执行细节进一步分析是否应该优化搜索、怎样优化索引。
12.如何优化一个SQL查询
1）不使用子查询。子查询在MySQL5.5版本里，内部执行计划器是这样执行的：先查外表再匹配内表，而不是先查内表，当外表的数据很大时，查询速度会非常慢。
2）避免函数索引
1）LIKE双百分号无法使用到索引，只有“%”不在第一个位置，索引才会生效
2）读取适当的记录LIMIT M, N
3）避免数据类型不一致
4）建立适当的索引
5）开启了查询缓存
6）分组统计可以禁止排序，默认情况下，MySQL对所有GROUP BY col1，col2...的字段进行排序。如果查询包括GROUP BY，想要避免排序结果的消耗，则可以指定ORDER BY NULL禁止排序。
7）禁止不必要的ORDER BY排序
10）只选择你需要的字段，避免select*
11）切分查询，将大查询划分为小查询。
12）拆分大的DELETE或INSERT语句
13）选择优化的数据类型
a)更小的通常更好：一般情况下，应该尽量使用可以正确存储数据的最小数据类型。更小的数据类型通常更快，因它们占用更少的磁盘、内存和CPU缓存，并且处理时需要的CPU周期也更少。
b)简单就好：简单数据类型的操作通常需要更少的CPU周期。例如：整型比字符操作代价更低，因为字符集和校对规则（排序规则）使字符比较比整型比较更复杂。例如用data,time,datatime来存储日期和时间而不是使用字符串。
c)尽量避免NULL：NULL的列使得索引、索引统计和值比较都更复杂。如果计划在列上建立索引应该避免设计成可为Null的列
14）schema设计陷阱
a)太多的列：服务器层和存储引擎层之间通过行缓冲格式拷贝数据，然后在服务器层解码成各个列，如果列太多，转换的代价会非常高。
b)太多关联：如果希望查询执行快速并且并发性好，单个查询最好在12个表以内做关联。
c)null：该用还得用，有时候可能用-1之类的值标示空缺业务逻辑会比null复杂很多。
13.MySQL海量数据查询优化方法
（1）创建索引
（2）分析sql语句的影响结果集，查慢查询日志，定位慢查询的sql语句
（3）利用缓存或NoSql代替现有一些热点查询操作，减轻Mysql压力
（4）读写分离
（5）分库分表，垂直拆分和水平拆分
（6）使用集群
14.MySQL逻辑架构
由上图可以主要分为三层：
第一层架构：连接处理，授权认证，安全等。
第二层架构：大多数MySQL的核心服务都在这一层：查询解析、分析、优化、缓存以及所有的内置函数，所有跨存储引擎的功能都在这一层实现：存储过程、触发器、视图等。
第三层架构：包含了存储引擎。服务器通过API与存储引擎进行通信，API屏蔽了不同引擎之间的差异。
15.MySQL数据类型
在mysql中有3中基本数据类型，分别如下：
ØNumeric Type数值型
ØDate and
Time Type日期和时间
ØString Type字符型
这三种类型需要不同的存储空间，一般来说，选择列数据类型的时候，基本原则是选择可以满足数据的最小类型。
数值型
整型、浮点型
定点数
字符串(char,varchar,text)
char和varchar：
1）char(n)若存入字符数小于n，则以空格补于其后，查询之时再将空格去掉。所以char类型存储的字符串末尾不能有空格，varchar不限于此。
2）char(n)固定长度，char(4)不管是存入几个字符，都将占用4个字节，varchar是存入的实际字符数+1个字节（n<=255）或2个字节(n>255)，所以varchar(4)，存入3个字符将占用4个字节。
3）char类型的字符串检索速度要比varchar类型的快。
varchar和text：
1）varchar可指定n，text不能指定，内部存储varchar是存入的实际字符数+1个字节（n<=255）或2个字节(n>255)，text是实际字符数+2个字节。
2）text类型不能有默认值。
3）varchar可直接创建索引，text创建索引要指定前多少个字符。varchar查询速度快于text，在都创建索引的情况下，text的索引似乎不起作用。
二进制数据(Blob)
1）BLOB和TEXT存储方式不同，TEXT以文本方式存储，英文存储区分大小写，而Blob是以二进制方式存储，不分大小写。
2）BLOB存储的数据只能整体读出。
3）TEXT可以指定字符集，BLOB不用指定字符集。
16.数据库5种连接
INNER JOIN（内连接）：如果表中有至少一个匹配，则返回行
LEFT JOIN（左外连接）：即使右表中没有匹配，也从左表返回所有的行
RIGHT JOIN（右外连接）：即使左表中没有匹配，也从右表返回所有的行
FULL JOIN（全连接）：只要其中一个表中存在匹配，则返回行
CROSS JOIN（交叉连接）：没有WHERE子句的交叉连接将产生连接所涉及的表的笛卡尔积。第一个表的行数乘以第二个表的行数等于笛卡尔积结果集的大小。（cross join不带where）
17.数据库连接池
连接池的工作原理
连接池的核心思想是连接的复用，通过建立一个数据库连接池以及一套连接使用、分配和管理策略，使得该连接池中的连接可以得到高效、安全的复用，避免了数据库连接频繁建立和关闭的开销。
连接池的工作原理主要由三部分组成，分别为连接池的建立，连接池中连接的使用管理，连接池的关闭。
1）连接池的建立。一般在系统初始化时，连接池会根据系统配置建立，并在池中建立几个连接对象，以便使用时能从连接池中获取，连接池中的连接不能随意创建和关闭，这样避免了连接随意建立和关闭造成的系统开销。Java中提供了很多容器类，可以方便的构建连接池，例如Vector、stack等。
2）连接池的管理。连接池管理策略是连接池机制的核心，连接池内连接的分配和释放对系统的性能有很大的影响。其策略是：
当客户请求数据库连接时，首先查看连接池中是否有空闲连接，如果存在空闲连接，则将连接分配给客户使用；如果没有空闲连接，则查看当前所开的连接数是否已经达到最大连接数，例如如果没有达到就重新创建一个请求的客户；如果达到，就按设定的最大等待时间进行等待，如果超出最大等待时间，则抛出异常给客户。
当客户释放数据库连接时，先判断该连接的引用次数是否超过了规定值，如果超过了就从连接池中删除该连接，否则就保留为其他客户服务。该策略保证了数据库连接的有效复用，避免了频繁建立释放连接所带来的系统资源的开销。
3）连接池的关闭。当应用程序退出时，关闭连接池中所有的链接，释放连接池相关资源，该过程正好与创建相反。
private String name;//数据库名字
private String type;//连接池类型
private String driver;//连接池驱动
private String url;//连接池地址
private String username;//用户名
private String password;//密码
private int maxCount;//最大连接数
18.数据库备份
备份方式
热备份指的是当数据库进行备份时，数据库的读写操作均不是受影响。
温备份指的是当数据库进行备份时，数据库的读操作可以执行,但是不能执行写操作。
冷备份指的是当数据库进行备份时，数据库不能进行读写操作,即数据库要下线。
针对不同的场景下,我们应该制定不同的备份策略对数据库进行备份，备份策略一般为以下四种：
1）直接cp、tar复制数据库文件
2）mysqldump+复制BIN LOGS（二进制日志）
3）lvm2快照+复制BIN LOGS
4）xtrabackup
以上的几种解决方案分别针对于不同的场景
1）如果数据量较小，可以使用第一种方式，直接复制数据库文件
2）如果数据量还行，可以使用第二种方式，先使用mysqldump对数据库进行完全备份,然后定期备份BINARY LOG达到增量备份的效果
3）如果数据量一般，而又不过分影响业务运行，可以使用第三种方式，使用lvm2的快照对数据文件进行备份，而后定期备份BINARY LOG达到增量备份的效果
4）如果数据量很大，而又不过分影响业务运行，可以使用第四种方式，使用xtrabackup进行完全备份后，定期使用xtrabackup进行增量备份或差异备份
Mysqldump是一个逻辑备份命令；意思就是将数据库中的数据备份成一个文本文件；也可以说是将表的结构和数据存储在文本文件中。mysqldump命令的工作原理：它先查出需要备份的表的结构，再在文本文件中生成一个CREATE语句。然后，将表中的所有记录转换成一条INSERT语句。然后通过这些语句，就能够创建表并插入数据。
mysqlbinlog使用二进制日志进行增量恢复。
binlog也称为二进制日志，记录对数据发生或潜在发生更改的SQL语句，并以二进制的形式保存在磁盘中，可以用来查看数据库的变更历史（具体的时间点所有的SQL操作）、数据库增量备份和恢复（增量备份和基于时间点的恢复）、Mysql的复制（主主数据库的复制、主从数据库的复制）。
Xtrabackup是一个对InnoDB做数据备份的工具，支持在线热备份（备份时不影响数据读写）。
19.读写分离、主从复制、分库分表
读写分离：让主数据库（master）处理事务性增、改、删操作（INSERT、UPDATE、DELETE），而从数据库（slave）处理SELECT查询操作。数据库复制被用来把事务性查询导致的变更同步到集群中的从数据库。
好处：
1.增加冗余
2.增加了机器的处理能力
3.对于读操作为主的应用，使用读写分离是最好的场景，因为可以确保写的服务器压力更小，而读又可以接受点时间上的延迟。
20.数据库相关注意点
1）limit m，n表示从m+1条记录开始检索，n代表取出n条数据。当一个数据库表过于庞大，LIMIT offset, length中的offset值过大，则SQL查询语句会非常缓慢。
select* from product limit 866613, 2037.44秒
这次我们之间查询最后一页的数据（利用覆盖索引，只包含id列），如下：
select id from product limit 866613, 20
0.2秒
相对于查询了所有列的37.44秒，提升了大概100多倍的速度
那么如果我们也要查询所有列，有两种方法，一种是id>=的形式，另一种就是利用join，
看下实际情况：
SELECT * FROM product WHERE ID > =(select id from product limit 866613, 1) limit 20
查询时间为0.2秒。
另一种写法
SELECT * FROM product a JOIN (select id from product limit 866613, 20) b ON a.ID=b.id
2）select的执行顺序：from，where，group，having，(select)，order by，limit
3）财务数据decimal，decimal(m,d)参数m<65是总个数，d<30且d
4）复合索引的最左前缀原则
建了一个索引：(a, b, c)，执行下面两个SQL。
SELECT * FROM tableWHERE a = 1 AND c = 3;
SELECT * FROM tableWHERE a = 1 AND b < 2 AND c = 3;
那么第一句的a=1会走索引，c=3不走索引；第二句的a=1 and b<2会走索引，c=3不走索引。
比如有一条语句是这样的：select *from users where area=’beijing’ and age=22;
如果我们是在area和age上分别创建单一索引的话，由于mysql查询每次只能使用一个索引，所以虽然这样已经相对不做索引时全表扫描提高了很多效率，但是如果在area、age两列上创建复合索引的话将带来更高的效率。如果我们创建了(area, age, salary)的复合索引，那么其实相当于创建了(area,age,salary)、(area,age)、(area)三个索引。因此我们在创建复合索引时应该将最常用作限制条件的列放在最左边，依次递减。
5）将大量数据批量写入数据库，直接使用程序语言和SQL写入往往很耗时间，其中有一种方案就是使用load data
infile导入文件的形式导入数据，这样可大大缩短数据导入时间。
6）SHOW PROFILE和SHOW PROFILES命令可以用来分析当前会话中语句执行的资源消耗情况，诸如IO，上下文，CPU，MEMORY等。其是否启用是根据会话级的变量profiling，默认是关闭的。SHOW PROFILES显示最近发给服务器的多条语句，条数根据会话变量profiling_history_size定义，默认是15，最大值为100。设为0等价于关闭分析功能。
SHOW PROFILE FOR QUERY n，这里的n就是对应SHOW PROFILES输出中的Query_ID。
