MySQL数据库优化
数据库优化的目的
1、避免出现页面访问错误
1)由于数据库连接timeout产生页面5xx错误
2)由于慢查询造成页面无法加载
3)由于阻塞造成数据无法提交
增加数据库的稳定性
1)很多数据库问题都是由于低效的查询引起的
优化用户体验
1)流畅也没按的访问速度
2)良好的网站功能体验
数据库优化方面
SQL及索引：最重要，结构良好的SQL和建立适量有效的索引
数据库表结构：设计符合数据库三范式的数据表结构
系统配置：文件数限制
硬件：CPU，IO，内存
SQL及索引优化
如何分析SQL查询？
数据库基于MySQL5.5版本，不同MySQL版本的优化器有一定的差别
如何发现有问题的SQL？
使用MySQL慢查日志对有效率问题的SQL进行监控
--查看慢查询日志存储位置
showvariables like ‘slow_query_log’;
默认状态下是关闭的。
setglobal slow_query_log = on;
--设置慢查询日志存储位置
setglobal slow_query_log_file=’/home/mysql/sql_log/lian-slow.log’;
设置慢查询日志存储位置
--设置没有使用索引的SQL记录到慢查询日志中
setglobal log_queries_not_using_indexes=on;
默认状态下是关闭的。
--设置超过100毫秒的SQL查询记录慢查询日志中
setglobal long_query_time=0.1;
默认情况下是0秒
慢查询日志的存储格式
慢查询日志所包含的内容
#Time: 170904 22:44:18
执行SQL的主机信息
#User@Host: root[root] @ localhost [::1]
SQL的执行新
#Query_time: 0.109618 Lock_time: 0.105615Rows_sent: 2 Rows_examined: 2
SQL执行时间
SETtimestamp=1504536258;
SQL的内容
select* from store limit 10;
慢查询日志的分析工具—mysqldump/ mysqldumpslow
mysqldump(windows)
mysqldumpslow(Linux）
慢查询日志的分析工具—pt-query-digest
如何通过慢查询日志发现有问题的SQL
Rowsexamine表示扫描行数
Rowssend表示发送函数
如何分析SQL查询
使用explain查询SQL的执行计划
explain返回各列的含义
count()和max()的优化方法
通过添加索引进行优化，
createindex idx_paydate on payment(payment_date);
count(*)和count(id)区别
count(1)等价于count(*)，count(null)是记录空值
子查询优化
groupby优化
优化后
limit查询优化
优化后1
优化后2
索引优化
索引维护
数据库性能优化
第一：优化SQL查询语句（选择合适的存储引擎)和使用索引优化
最重要的是优化SQL查询语句
1、尽量不使用*查询所有字段，要指定具体查询哪些字段
2、尽量不使用!=，<>，
放弃使用索引而进行全表扫描。
3、尽量不使用like进行模糊匹配查询，可以使用全文搜索引擎代替
4、尽量不使用in，可以使用exists代替
5、尽量不使用not in，可以使用not exists代替,
因为使用not in会放弃使用表的索引，进行全表扫描
6、使用执行计划优化SQL查询语句
7、尽量不使用子查询，可以使用join连接查询进行优化
使用适量合理有效的索引
索引目的：
提高查询效率
索引原理：
索引是对记录集的多个字段进行排序的方法。在一张表中为一个字段创建一个索引，将创建另外一个数据结构，包含字段数值以及指向相关记录的指针，然后对这个索引结构进行排序，允许在该数据上进行二分法排序。
副作用是索引需要额外的磁盘空间，对于MyISAM引擎而言，这些索引是被统一保存在一张表中的，这个文件将很快到达底层文件系统所能够支持的大小限制，如果很多字段都建立了索引的话。
数据库索引有什么作用？带来的问题是是什么？
选择合适的数据库存储引擎
MyISAM是MySQL5.5之前的默认存储引擎，基于传统的ISAM类型，支持全文搜索，但不是事务安全的，而且不支持外键。每张MyISAM表存放在三个文件中：frm 文件存放表格定义；数据文件是MYD (MYData)；索引文件是MYI (MYIndex)。
InnoDB是MySQL5.5之后的默认支持的事务型存储引擎，支持回滚、崩溃恢复能力、多版本并发控制、ACID事务，支持行级锁定（InnoDB表的行锁不是绝对的，如果在执行一个SQL语句时MySQL不能确定要扫描的范围，InnoDB表同样会锁全表，如like操作时的SQL语句），以及提供与Oracle类型一致的不加锁读取方式。InnoDB存储它的表和索引在一个表空间中，表空间可以包含数个文件。
主要区别：
1)MyISAM是非事务安全型的，而InnoDB是事务安全型的。
2)MyISAM锁的粒度是表级，而InnoDB支持行级锁定。
3)MyISAM支持全文类型索引，而InnoDB不支持全文索引。
4)MyISAM相对简单，所以在效率上要优于InnoDB，小型应用可以考虑使用MyISAM。
5)MyISAM表是保存成文件的形式，在跨平台的数据转移中使用MyISAM存储会省去不少的麻烦。
6)InnoDB表比MyISAM表更安全，可以在保证数据不会丢失的情况下，切换非事务表到事务表（altertable tablename type=innodb）。
MyISAM管理非事务表。它提供高速存储和检索，以及全文搜索能力。如果应用中需要执行大量的SELECT查询，那么MyISAM是更好的选择。
InnoDB用于事务处理应用程序，具有众多特性，包括ACID事务支持。如果应用中需要执行大量的INSERT或UPDATE操作，则应该使用InnoDB，这样可以提高多用户并发操作的性能。
第二：使用缓存
使用缓存机制，比如使用redis，减少数据库的查询次数。读取内存的速度是比使用IO读取数据库的速度快的多的
第三：读写分离（主从复制或者主主复制）
第四：使用MySQL分区表
第五：
进行数据表的垂直拆分和水平拆分
事务
Spring事务管理
编程式事务
注解式事务
MySQL乐观锁和悲观锁的概念？原理机制？
悲观锁
悲观锁是指假设并发更新冲突会发生，所以不管冲突是否真的发生，都会使用锁机制。
悲观锁会完成以下功能：锁住读取的记录，防止其它事务读取和更新这些记录。其它事务会一直阻塞，直到这个事务结束.
悲观锁是在使用了数据库的事务隔离功能的基础上，独享占用的资源，以此保证读取数据一致性，避免修改丢失。
悲观锁可以使用Repeatable Read事务，它完全满足悲观锁的要求。
乐观锁
乐观锁不会锁住任何东西，也就是说，它不依赖数据库的事务机制，乐观锁完全是应用系统层面的东西。
如果使用乐观锁，那么数据库就必须加版本字段，否则就只能比较所有字段，但因为浮点类型不能比较，所以实际上没有版本字段是不可行的。
MySQL InnoDB默认Row-Level Lock，所以只有「明确」地指定主键，MySQL 才会执行Row lock (只锁住被选取的数据) ，否则MySQL 将会执行Table Lock (将整个数据表单给锁住)。
(明确指定主键，并且有此数据，row lock
明确指定主键，若查无此数据，无lock
无主键，table lock
主键不明确，table lock
关于数据库主键对MySQL锁级别的影响实例，需要注意的是，除了主键外，使用索引也会影响数据库的锁定级别
(明确指定索引，并且有此数据，row lock
明确指定索引，若查无此数据，无lock
悲观锁并不是适用于任何场景，它也有它存在的一些不足，因为悲观锁大多数情况下依靠数据库的锁机制实现，以保证操作最大程度的独占性。如果加锁的时间过长，其他用户长时间无法访问，影响了程序的并发访问性，同时这样对数据库性能开销影响也很大，特别是对长事务而言，这样的开销往往无法承受。所以与悲观锁相对的，我们有了乐观锁
共享锁和排他锁（行锁，间隙锁，next-key lock）都属于悲观锁。
乐观锁介绍：
乐观锁（ Optimistic Locking ） 相对悲观锁而言，乐观锁假设认为数据一般情况下不会造成冲突，所以在数据进行提交更新的时候，才会正式对数据的冲突与否进行检测，如果发现冲突了，则让返回用户错误的信息，让用户决定如何去做。那么我们如何实现乐观锁呢，一般来说有以下2种方式：
1. 使用数据版本（Version）记录机制实现，这是乐观锁最常用的一种实现方式。何谓数据版本？即为数据增加一个版本标识，一般是通过为数据库表增加一个数字类型的 “version” 字段来实现。当读取数据时，将version字段的值一同读出，数据每更新一次，对此version值加一。当我们提交更新的时候，判断数据库表对应记录的当前版本信息与第一次取出来的version值进行比对，如果数据库表当前版本号与第一次取出来的version值相等，则予以更新，否则认为是过期数据。
2.
如上图所示，如果更新操作顺序执行，则数据的版本（version）依次递增，不会产生冲突。但是如果发生有不同的业务操作对同一版本的数据进行修改，那么，先提交的操作（图中B）会把数据version更新为2，当A在B之后提交更新时发现数据的version已经被修改了，那么A的更新操作会失败。
2.乐观锁定的第二种实现方式和第一种差不多，同样是在需要乐观锁控制的table中增加一个字段，名称无所谓，字段类型使用时间戳（timestamp）, 和上面的version类似，也是在更新提交的时候检查当前数据库中数据的时间戳和自己更新前取到的时间戳进行对比，如果一致则OK，否则就是版本冲突。
悲观锁与乐观锁的实现方式
悲观锁的实现依靠的是数据库提供的锁机制来实现，例如select * from news where id=12 for update,而乐观锁依靠的是记录数据版本来实现，即通过在表中添加版本号字段来作为是否可以成功提交的关键因素。
在数据库中有两种基本的锁类型：排它锁（Exclusive Locks，即X锁）和共享锁（Share Locks，即S锁）。当数据对象被加上排它锁时，其他的事务不能对它读取和修改。加了共享锁的数据对象可以被其他事务读取，但不能修改。数据库利用这两种基本的锁类型来对数据库的事务进行并发控制。
什么是共享锁，什么是排他锁？
共享锁也叫读锁，简称S锁，原理：一个事务获取了一个数据行的共享锁，其他事务能获得该行对应的共享锁，但不能获得排他锁，即一个事务在读取一个数据行的时候，其他事务也可以读，但不能对该数据行进行增删改。
排他锁也叫写锁，简称x锁，原理：一个事务获取了一个数据行的排他锁，其他事务就不能再获取该行的其他锁（排他锁或者共享锁），即一个事务在读取一个数据行的时候，其他事务不能对该数据行进行增删改查。
如何设置共享锁和排他锁？
设置共享锁：SELECT ... LOCK IN SHARE MODE;
设置排他锁：SELECT ... FOR UPDATE;
通过各个算法之间的算法复杂度的比较，设计者发现效率比较高的查询算法只能应用于特定的数据结构，比如二分查找要求被检索数据有序，二叉树查找只能应用于二叉查找树等。
实际应用中，mysql中主要使用b+tree索引，至于其他类型的索引，基本上不使用的。
当开发人员执行数据表的某个字段加上索引（这些都是b+tree索引）命令的时候，此时，mysql系统内部会额外的开辟一块磁盘空间，用来存储相关的b+tree数据结构（也叫索引），同时这种数据结构会通过某种方式与数据表中的每一条数据产生联系（指向）。
在mysql系统查询数据的时候，mysql引擎会通过一定的算法（例如二分法）可以很快的在这个数据结构上定位到索引值，从而快速查找到数据。
悲观锁：假定会发生并发冲突，屏蔽一切可能违反数据完整性的操作。[1]
乐观锁：假设不会发生并发冲突，只在提交操作时检查是否违反数据完整性。[1]乐观锁不能解决脏读的问题。
乐观锁应用
1. 使用自增长的整数表示数据版本号。更新时检查版本号是否一致，比如数据库中数据版本为6，更新提交时version=6+1,使用该version值(=7)与数据库version+1(=7)作比较，如果相等，则可以更新，如果不等则有可能其他程序已更新该记录，所以返回错误。
2. 使用时间戳来实现.
注：对于以上两种方式,hibernate自带实现方式：在使用乐观锁的字段前加annotation: @Version, Hibernate在更新时自动校验该字段。
需要使用数据库的锁机制，比如SQL SERVER 的TABLOCKX（排它表锁） 此选项被选中时，SQL Server 将在整个表上置排它锁直至该命令或事务结束。这将防止其他进程读取或修改表中的数据。
数据库锁机制：
共享锁：由读表操作加上的锁，加锁后其他用户只能获取该表或行的共享锁，不能获取排它锁，也就是说只能读不能写。
排它锁：由写表操作加上的锁，加锁后其他用户不能获取该表或行的任何锁。
锁的范围:
行锁:对某行记录加上锁
表锁:对整个表加上锁
这样组合起来就有，行级共享锁，表级共享锁，行级排他锁，表级排他锁。
悲观锁的特点是先获取锁，再进行业务操作，即“悲观”的认为获取锁是非常有可能失败的，因此要先确保获取锁成功再进行业务操作。
乐观锁的特点先进行业务操作，不到万不得已不去拿锁。
每个MyISAM在磁盘上存储成3个文件，其中文件名和表名都相同，但是扩展名分别为：
- .frm(存储表定义)
- MYD(MYData，存储数据)
- MYI(MYIndex，存储索引)
，其实就是一个底层负责存储的一个程序，而这些存储程序会按照各自的存储格式或策略来实现具体sql数据存储和查询的。
锁
数据库和操作系统一样，是一个多用户使用的共享资源。当多个用户并发地存取数据时，在数据库中就会产生多个事务同时存取同一数据的情况。若对并发操作不加控制就可能会读取和存储不正确的数据，破坏数据库的一致性。加锁是实现数据库并发控制的一个非常重要的技术。在实际应用中经常会遇到的与锁相关的异常情况，当两个事务需要一组有冲突的锁，而不能将事务继续下去的话，就会出现死锁，严重影响应用的正常执行。
在数据库中有两种基本的锁类型：排它锁（Exclusive Locks，即X锁）和共享锁（Share Locks，即S锁）。当数据对象被加上排它锁时，其他的事务不能对它读取和修改。加了共享锁的数据对象可以被其他事务读取，但不能修改。数据库利用这两种基本的锁类型来对数据库的事务进行并发控制。
死锁的几种情况
死锁的第一种情况
一个用户A 访问表A(锁住了表A),然后又访问表B；另一个用户B 访问表B(锁住了表B)，然后企图访问表A；这时用户A由于用户B已经锁住表B，它必须等待用户B释放表B才能继续，同样用户B要等用户A释放表A才能继续，这就死锁就产生了。
解决方法：
这种死锁比较常见，是由于程序的BU*生的，除了调整的程序的逻辑没有其它的办法。仔细分析程序的逻辑，对于数据库的多表操作时，尽量按照相同的顺序进行处理，尽量避免同时锁定两个资源，如操作A和B两张表时，总是按先A后B的顺序处理， 必须同时锁定两个资源时，要保证在任何时刻都应该按照相同的顺序来锁定资源。
死锁的第二种情况
用户A查询一条纪录，然后修改该条纪录；这时用户B修改该条纪录，这时用户A的事务里锁的性质由查询的共享锁企图上升到独占锁，而用户B里的独占锁由于A 有共享锁存在所以必须等A释放掉共享锁，而A由于B的独占锁而无法上升的独占锁也就不可能释放共享锁，于是出现了死锁。这种死锁比较隐蔽，但在稍大点的项目中经常发生。如在某项目中，页面上的按钮点击后，没有使按钮立刻失效，使得用户会多次快速点击同一按钮，这样同一段代码对数据库同一条记录进行多次操作，很容易就出现这种死锁的情况。
解决方法：
1、对于按钮等控件，点击后使其立刻失效，不让用户重复点击，避免对同时对同一条记录操作。
2、使用乐观锁进行控制。乐观锁大多是基于数据版本（Version）记录机制实现。即为数据增加一个版本标识，在基于数据库表的版本解决方案中，一般是通过为数据库表增加一个“version”字段来实现。读取出数据时，将此版本号一同读出，之后更新时，对此版本号加一。此时，将提交数据的版本数据与数据库表对应记录的当前版本信息进行比对，如果提交的数据版本号大于数据库表当前版本号，则予以更新，否则认为是过期数据。乐观锁机制避免了长事务中的数据库加锁开销（用户A和用户B操作过程中，都没有对数据库数据加锁），大大提升了大并发量下的系统整体性能表现。Hibernate 在其数据访问引擎中内置了乐观锁实现。需要注意的是，由于乐观锁机制是在我们的系统中实现，来自外部系统的用户更新操作不受我们系统的控制，因此可能会造成脏数据被更新到数据库中。
3、使用悲观锁进行控制。悲观锁大多数情况下依靠数据库的锁机制实现，如Oracle的Select … for update语句，以保证操作最大程度的独占性。但随之而来的就是数据库性能的大量开销，特别是对长事务而言，这样的开销往往无法承受。如一个金融系统，当某个操作员读取用户的数据，并在读出的用户数据的基础上进行修改时（如更改用户账户余额），如果采用悲观锁机制，也就意味着整个操作过程中（从操作员读 出数据、开始修改直至提交修改结果的全过程，甚至还包括操作员中途去煮咖啡的时间），数据库记录始终处于加锁状态，可以想见，如果面对成百上千个并发，这样的情况将导致灾难性的后果。所以，采用悲观锁进行控制时一定要考虑清楚。
死锁的第三种情况
如果在事务中执行了一条不满足条件的update语句，则执行全表扫描，把行级锁上升为表级锁，多个这样的事务执行后，就很容易产生死锁和阻塞。类似的情况还有当表中的数据量非常庞大而索引建的过少或不合适的时候，使得经常发生全表扫描，最终应用系统会越来越慢，最终发生阻塞或死锁。
解决方法：
SQL语句中不要使用太复杂的关联多表的查询；使用“执行计划”对SQL语句进行分析，对于有全表扫描的SQL语句，建立相应的索引进行优化。
总体上来说，产生内存溢出与锁表都是由于代码写的不好造成的，因此提高代码的质量是最根本的解决办法。有的人认为先把功能实现，有BUG时再在测试阶段进 行修正，这种想法是错误的。正如一件产品的质量是在生产制造的过程中决定的，而不是质量检测时决定的，软件的质量在设计与编码阶段就已经决定了，测试只是对软件质量的一个验证，因为测试不可能找出软件中所有的BUG。
如何避免死锁
1 使用事务时，尽量缩短事务的逻辑处理过程，及早提交或回滚事务；
2 设置死锁超时参数为合理范围，如：3分钟-10分种；超过时间，自动放弃本次操作，避免进程悬挂；
3 所有的SP都要有错误处理（通过@error）
4 一般不要修改SQL SERVER事务的默认级别。不推荐强行加锁
5 优化程序，检查并避免死锁现象出现；
1）合理安排表访问顺序
2）在事务中尽量避免用户干预，尽量使一个事务处理的任务少些。
3）采用脏读技术。脏读由于不对被访问的表加锁，而避免了锁冲突。在客户机/服务器应用环境中，有些事务往往不允许读脏数据，但在特定的条件下，我们可以用脏读。
4）数据访问时域离散法。数据访问时域离散法是指在客户机/服务器结构中，采取各种控制手段控制对数据库或数据库中的对象访问时间段。主要通过以下方式实现: 合理安排后台事务的执行时间，采用工作流对后台事务进行统一管理。工作流在管理任务时，一方面限制同一类任务的线程数（往往限制为1个），防止资源过多占 用; 另一方面合理安排不同任务执行时序、时间，尽量避免多个后台任务同时执行，另外，避免在前台交易高峰时间运行后台任务
5）数据存储空间离散法。数据存储空间离散法是指采取各种手段，将逻辑上在一个表中的数据分散到若干离散的空间上去，以便改善对表的访问性能。主要通过以下方法实现: 第一，将大表按行或列分解为若干小表; 第二，按不同的用户群分解。
6）使用尽可能低的隔离性级别。隔离性级别是指为保证数据库数据的完整性和一致性而使多用户事务隔离的程度，SQL92定义了4种隔离性级别：未提交读、 提交读、可重复读和可串行。如果选择过高的隔离性级别，如可串行，虽然系统可以因实现更好隔离性而更大程度上保证数据的完整性和一致性，但各事务间冲突而死锁的机会大大增加，大大影响了系统性能。
7）使用Bound Connections。Boundconnections 允许两个或多个事务连接共享事务和锁，而且任何一个事务连接要申请锁如同另外一个事务要申请锁一样，因此可以允许这些事务共享数据而不会有加锁的冲突。
8）考虑使用乐观锁定或使事务首先获得一个独占锁定。
索引的好处和坏处
创建索引的好处：
1、 加快经常被搜索字段的搜索速度。
2、利用索引的唯一性来控制记录的唯一性
3、 等
创建索引的坏处：
1、额外的存储空间（单列索引占原表5%至15%空间，想象一下如果为一个表创建三四个索引）
2、额外的创建和维护时间：执行数据修改操作(INSERT、UPDATE、DELETE)产生索引维护
solr的检索方式有哪些
MySQL面试题
简述union和union all的区别
Union，对两个结果集进行并集操作，不包括重复行，同时进行默认规则的排序；
Union All，对两个结果集进行并集操作，包括重复行，不进行排序；效率更好
Intersect，对两个结果集进行交集操作，不包括重复行，同时进行默认规则的排序；
Minus，对两个结果集进行差操作，不包括重复行，同时进行默认规则的排序。
合同表和借据表是一对多的关系，
借据表load
合同表cont
将合同表的的证件号码和客户姓名更新到借据表中
合同表数据
借据表数据
使用以下SQL语句
updateloan a, cont b set a.id_no = b.id_no, a.cusr_name = b.cusr_name where a.cont_no= b.cont_no;
更新完成
也可以使用
updateloan a inner join cont b on a.cont_no = b.cont_no set a.id_no = b.id_no,a.cusr_name = b.cusr_name;
查询借据表同一证件号码有一条以上数据的证件号及数量
使用select id_no,count(loan_no) from loan group by id_no havingcount(loan_no) > 1;
Mysql中查询系统时间的方法
第一种方法：select current_date；
mysql> select current_date as Systemtime;
+------------+
| Systemtime |
+------------+
| 2009-07-29 |
+------------+
第二种方法：select now()
mysql> select now() as Systemtime;
+---------------------+
| Systemtime |
+---------------------+
| 2009-07-29 19:06:07 |
+---------------------+
第三种方法：select sysdate()
mysql> select sysdate() as Systemtime;
+---------------------+
| Systemtime |
+---------------------+
| 2009-07-29 19:06:45 |
+---------------------+
当前Oracle系统时间的查询方法
Oracle系统时间的查询方法很多，下面就为您介绍几个常见的查询当前Oracle系统时间方法，如果您对Oracle系统时间方面感兴趣的话，不妨一看。
SYSDATE：可将Sysdate视为一个其结果为当前日期和时间的函数，在任何可以使用Oracle函数的地方都可以使用Sysdate。也可以将它视为每个表的一个隐藏的列或伪列。
1. SQL> select sysdate from dual;
2.
3. SYSDATE
4. ----------
5. 22-7月 -08
CURRENT_DATE：报告会话的时区中的系统日期。注：可以设置自己的时区，以区别于数据库的时区。
1. SQL> select Current_date from dual;
2.
3. CURRENT_DA
4. ----------
5. 22-7月 -08
SYSTIMESTAMP：报告TIMESTAMP数据类型格式的系统日期。
1. SQL> select SYSTIMESTAMP from dual;
2.
3. SYSTIMESTAMP
4. ---------------------------------------------------------------------------
5. 22-7月 -08 10.20.32.734000 上午 +08:00
(摘)