sql

~1、SQL中IN和EXISTS用法的区别
结论
1. in()适合B表比A表数据小的情况
2. exists()适合B表比A表数据大的情况
当A表数据与B表数据一样大时,in与exists效率差不多,可任选一个使用.
~2、数据库主从配置、读写分离
原理：主服务器（Master）负责网站NonQuery操作，从服务器负责Query操作，用户可以根据网站功能模特性块固定访问Slave服务器，
或者自己写个池或队列，自由为请求分配从服务器连接。主从服务器利用MySQL的二进制日志文件，实现数据同步。二进制日志由主服务器
产生，从服务器响应获取同步数据库。
prv：一主几备，主用来cud，备们用来读r，利用haproxy来实现读写自动调用不同的数据库或者mycat
spring实现：继承extends AbstractRoutingDataSource
转载链接：https://blog.csdn.net/ycb1689/article/details/50952547
1、extends SimpleJdbcDaoSupport
2、配置Spring多数据源，这里配置了一个主库和一个从库，他们可以共同继承一个父的数据源
3、新建一个datasource继承自AbstractRoutingDataSource，并且覆盖determineCurrentLookupKey()方法，每次用这个datasource获取数据库连接的时候都会回调这个方法获得key，根据返回的字符串key（也可以是枚举值，数字类型），动态地通过datasource配置的id来在Spring的配置文件中找到相应的datasource来获取connection（见代码4）。那么如果每次访问都需要根据key来决定如何选择数据源，那么这个key必须要保证线程安全，并发情况下每个线程都会去寻找本应该属于自己的key获取数据源，所以CustomerContextHolder类中就用到了ThreadLocal来保证（见代码5）
转载链接：https://www.cnblogs.com/crazylqy/p/5542558.html
影响MySQL-A数据库的操作，在数据库执行后，都会写入本地的日志系统A中。
假设，实时的将变化了的日志系统中的数据库事件操作，在MYSQL-A的3306端口，通过网络发给MYSQL-B。
MYSQL-B收到后，写入本地日志系统B，然后一条条的将数据库事件在数据库中完成。
那么，MYSQL-A的变化，MYSQL-B也会变化，这样就是所谓的MYSQL的复制，即MYSQL replication。
在上面的模型中，MYSQL-A就是主服务器，即master，MYSQL-B就是从服务器，即slave。
日志系统A，其实它是MYSQL的日志类型中的二进制日志，也就是专门用来保存修改数据库表的所有动作，即bin log。【注意MYSQL会在执
行语句之后，释放锁之前，写入二进制日志，确保事务安全】
日志系统B，并不是二进制日志，由于它是从MYSQL-A的二进制日志复制过来的，并不是自己的数据库变化产生的，有点接力的感觉，称为
中继日志，即relay log。
可以发现，通过上面的机制，可以保证MYSQL-A和MYSQL-B的数据库数据一致，但是时间上肯定有延迟，即MYSQL-B的数据是滞后的。
【即便不考虑什么网络的因素，MYSQL-A的数据库操作是可以并发的执行的，但是MYSQL-B只能从relay log中读一条，执行下。因此MYSQL
-A的写操作很频繁，MYSQL-B很可能跟不上。】
2.主从复制的几种方式
同步复制
所谓的同步复制，意思是master的变化，必须等待slave-1,slave-2,…,slave-n完成后才能返回。
这样，显然不可取，也不是MYSQL复制的默认设置。比如，在WEB前端页面上，用户增加了条记录，需要等待很长时间。
异步复制
如同AJAX请求一样。master只需要完成自己的数据库操作即可。至于slaves是否收到二进制日志，是否完成操作，不用关心。MYSQL的默认设置。
半同步复制
master只保证slaves中的一个操作成功，就返回，其他slave不管。
这个功能，是由google为MYSQL引入的。
3.主从复制分析
问题1：master的写操作，slaves被动的进行一样的操作，保持数据一致性，那么slave是否可以主动的进行写操作？
假设slave可以主动的进行写操作，slave又无法通知master，这样就导致了master和slave数据不一致了。因此slave不应该进行写操作，
至少是slave上涉及到复制的数据库不可以写。实际上，这里已经揭示了读写分离的概念。
问题2：主从复制中，可以有N个slave,可是这些slave又不能进行写操作，要他们干嘛？
可以实现数据备份。
类似于高可用的功能，一旦master挂了，可以让slave顶上去，同时slave提升为master。
异地容灾，比如master在北京，地震挂了，那么在上海的slave还可以继续。
主要用于实现scale out,分担负载,可以将读的任务分散到slaves上。
【很可能的情况是，一个系统的读操作远远多于写操作，因此写操作发向master，读操作发向slaves进行操作】
问题3：主从复制中有master,slave1,slave2,…等等这么多MYSQL数据库，那比如一个JAVA WEB应用到底应该连接哪个数据库?
当 然，我们在应用程序中可以这样，insert/delete/update这些更新数据库的操作，用connection(for master)进行操作，select
用connection(for slaves)进行操作。那我们的应用程序还要完成怎么从slaves选择一个来执行select，例如简单的轮循算法。
这样的话，相当于应用程序完成了SQL语句的路由，而且与MYSQL的主从复制架构非常关联，一旦master挂了，某些slave挂了，那么应
用程序就要修改了。能不能让应用程序与MYSQL的主从复制架构没有什么太多关系呢？
找一个组件，application program只需要与它打交道，用它来完成MYSQL的代理，实现SQL语句的路由。
mysql proxy并不负责，怎么从众多的slaves挑一个？可以交给另一个组件(比如haproxy)来完成。mycat貌似也行
这就是所谓的MYSQL READ WRITE SPLITE，MYSQL的读写分离。
问题4：如果mysql proxy , direct , master他们中的某些挂了怎么办？
总统一般都会弄个副总统，以防不测。同样的，可以给这些关键的节点来个备份。
问题5：当master的二进制日志每产生一个事件，都需要发往slave，如果我们有N个slave,那是发N次，还是只发一次？
如果只发一次，发给了slave-1，那slave-2,slave-3,…它们怎么办？
显 然，应该发N次。实际上，在MYSQL master内部，维护N个线程，每一个线程负责将二进制日志文件发往对应的slave。master既要负责
写操作，还的维护N个线程，负担会很重。可以这样，slave-1是master的从，slave-1又是slave-2,slave-3,…的主，同时slave-1不
再负责select。 slave-1将master的复制线程的负担，转移到自己的身上。这就是所谓的多级复制的概念。
问题6：当一个select发往mysql proxy，可能这次由slave-2响应，下次由slave-3响应，这样的话，就无法利用查询缓存了。
应该找一个共享式的缓存，比如memcache来解决。将slave-2,slave-3,…这些查询的结果都缓存至mamcache中。
问题7：随着应用的日益增长，读操作很多，我们可以扩展slave，但是如果master满足不了写操作了，怎么办呢？
scale on ?更好的服务器？ 没有最好的，只有更好的，太贵了。。。
scale out ? 主从复制架构已经满足不了。
可以分库【垂直拆分】，分表【水平拆分】。
~3、索引
转载链接：http://www.yuanrengu.com/index.php/2017-01-13.html
alter table aid_info add index aid(aid(4));//短索引只取前4位
还有短索引要小心使用. 特别是唯一约束的字段上面. MYSQL在执行约束时, 只检查索引. 这样有可能会导致原来不一样的值误报违反唯一约束.
转载链接：http://www.yuanrengu.com/index.php/2017-01-13.html
使用索引时，有一些技巧：
1.索引不会包含有NULL的列
只要列中包含有NULL值，都将不会被包含在索引中，复合索引中只要有一列含有NULL值，那么这一列对于此符合索引就是无效的。
2.使用短索引
对串列进行索引，如果可以就应该指定一个前缀长度。例如，如果有一个char（255）的列，如果在前10个或20个字符内，多数值是唯一的，那么就不要对整个列进行索引。短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作。
3.索引列排序
mysql一张表查询只能用到一个索引。因此如果where子句中已经使用了索引的话，那么order by中的列是不会使用索引的。因此数据库默
认排序可以符合要求的情况下不要使用排序操作，尽量不要包含多个列的排序，如果需要最好给这些列建复合索引。这一点是很多程序猿
容易忽略的，如where子句的字段建了索引，排序的字段建了索引，但是分开建的，以为会走索引，其实这样的话排序的字段不会使用索
引的，除非建复合索引，切记。
4.like语句操作
一般情况下不鼓励使用like操作，如果非使用不可，注意正确的使用方式。like ‘%aaa%’不会使用索引，而like ‘aaa%’可以使用索引。
5.不要在列上进行运算
6.不使用NOT IN 、<>、！=操作，但<,<=，=，>,>=,BETWEEN,IN是可以用到索引的
7.索引要建立在经常进行select操作的字段上。
8.索引要建立在值比较唯一的字段上。
9.对于那些定义为text、image和bit数据类型的列不应该增加索引。因为这些列的数据量要么相当大，要么取值很少。
10.在where和join中出现的列需要建立索引。
11.where的查询条件里有不等号(where column != …),mysql将无法使用索引。
12.如果where字句的查询条件里使用了函数(如：where DAY(column)=…),mysql将无法使用索引。
13.在join操作中(需要从多个数据表提取数据时)，mysql只有在主键和外键的数据类型相同时才能使用索引，否则即使建立了索引也不会使用。
14.在进行联表查询时，建立关联的表的字段类型最好一样且长度一致，这样能更好的发挥索引的作用。
15.组合索引时切记此条约束：组合索引中有多个字段，其中一个字段是有范围判断，则需将此字段在最后面。如
ALTER TABLE USER_DEMO ADD INDEX name_age (NAME,AGE); 因为age会有范围判断，则建组合索引时将AGE字段放在后面。
16.字符集字段比较，UTF8与UTF-BIN联合查询是不能走索引的。
如某张表的order_no字段类型为varchar(50),另一张表的order_no字段类型为varchar(50) COLLATE utf8_BIN。则此时联合查询时不能走索引的，切记。
即两张表的字段类型如下：
order_no varchar(50) COLLATE utf8_bin NOT NULL DEFAULT ‘’ COMMENT ‘订单号’,
order_no varchar(50) NOT NULL DEFAULT ‘’ COMMENT ‘订单号’,
17.以下几种情况不适合建索引：
表记录太少
经常插入、删除、修改的表
数据重复且分布平均的表字段。如一个表有10万行记录，其中字段column1只有A和B两种值，且每个值的分布概率大约为50%，那么对这种表column1字段建索引一般不会提高数据库的查询速度
18.给表创建主键，对于没有主键的表，在查询和索引定义上有一定的影响。
19.避免表字段为null，建议设置默认值（如int类型设置默认值为0），这样在索引查询上，效率会高很多。
建立索引的几大原则：
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)的索引，那么只需要修改原来的索引即可