数据库Tips

/@TOC

1. 偏移量

① select * from table limit 2,1; 含义是跳过2条取出1条数据，limit后面是从第2条开始读，读取1条信息，即读取第3条数据，即跳过两条取一条

② select * from table limit 2 offset 1; 含义是从第1条（不包括）数据开始取出2条数据，limit后面跟的是2条数据，offset后面是从第1条开始读取，即读取第2,3条，即跳过一条取两条

PS：offset和limit后面只能跟常量，要用变量，可以用set在外面声明
N = N-1

2. count（列名），count（*），count（1）

https://blog.csdn.net/ifumi/article/details/77920767
https://blog.csdn.net/u012674931/article/details/52711137

特点：
count（*）：sql会自动优化指定到哪一个字段
count（字段）：统计该字段出现的次数，会忽略字段为null的情况。字段为null不会统计，0和空字符串会统计
count（*）和count（1）：若字段为null，也会统计
执行效率上：
列名为主键，count(列名)会比count(1)快
列名不为主键，count(1)会比count(列名)快
如果表多个列并且没有主键，则 count（1） 的执行效率优于 count（*）
如果有主键，则 select count（主键）的执行效率是最优的
如果表只有一个字段，则 select count（星）最优。
COUNT(主键)更直接

3. Sql explain

https://segmentfault.com/a/1190000008131735
https://www.cnblogs.com/yycc/p/7338894.html
联接类型：
下面给出各种联接类型,按照从最佳类型到最坏类型进行排序:
system: 表仅有一行(=系统表)。这是const联接类型的一个特例。
const: 表最多有一个匹配行,它将在查询开始时被读取。因为仅有一行,在这行的列值可被优化器剩余部分认为是常数。const表很快,因为它们只读取一次!
eq_ref: 对于每个来自于前面的表的行组合,从该表中读取一行。这可能是最好的联接类型,除了const类型。
ref: 对于每个来自于前面的表的行组合,所有有匹配索引值的行将从这张表中读取。
ref_or_null: 该联接类型如同ref,但是添加了MySQL可以专门搜索包含NULL值的行。
index_merge: 该联接类型表示使用了索引合并优化方法。
unique_subquery: 该类型替换了下面形式的IN子查询的ref: value IN (SELECT primary_key FROM single_table WHERE some_expr) unique_subquery是一个索引查找函数,可以完全替换子查询,效率更高。
index_subquery: 该联接类型类似于unique_subquery。可以替换IN子查询,但只适合下列形式的子查询中的非唯一索引: value IN (SELECT key_column FROM single_table WHERE some_expr)
range: 只检索给定范围的行,使用一个索引来选择行。
index: 该联接类型与ALL相同,除了只有索引树被扫描。这通常比ALL快,因为索引文件通常比数据文件小。
ALL: 对于每个来自于先前的表的行组合,进行完整的表扫描。

能用函数解决的，尽量别让数据库去处理
explain select FROM_UNIXTIME(create_time,’%Y-%m-%d’) day,COUNT(*) cnt from device where (create_time >= ‘2018-09-01’ and create_time <= ‘2018-09-18’) group by FROM_UNIXTIME(create_time,’%Y-%m-%d’);

FROM_UNIXTIME: 将时间戳格式化为指定时间格式

索引建立原则

1、表的主键、外键必须有索引；
2、数据量超过300的表应该有索引；
3、经常与其他表进行连接的表，在连接字段上应该建立索引；
4、经常出现在Where子句中的字段，特别是大表的字段，应该建立索引；
5、索引应该建在选择性高的字段上；
6、索引应该建在小字段上，对于大的文本字段甚至超长字段，不要建索引；
7、复合索引的建立需要进行仔细分析；尽量考虑用单字段索引代替：
A、正确选择复合索引中的主列字段，一般是选择性较好的字段；
B、复合索引的几个字段是否经常同时以AND方式出现在Where子句中？单字段查询是否极少甚至没有？如果是，则可以建立复合索引；否则考虑单字段索引；
C、如果复合索引中包含的字段经常单独出现在Where子句中，则分解为多个单字段索引；
D、如果复合索引所包含的字段超过3个，那么仔细考虑其必要性，考虑减少复合的字段；
E、如果既有单字段索引，又有这几个字段上的复合索引，一般可以删除复合索引；
8、频繁进行数据操作的表，不要建立太多的索引；
9、删除无用的索引，避免对执行计划造成负面影响；

4. 数据库类型

tingint 一字节，8位
int（1）和int（20）都是存储±（0，255），对于计算和存储来说，是一样的，只是数据宽度不一样

5. 三星索引

第一颗星，where后面的谓词和索引列匹配的越多，索引片越窄，最终扫描的数据行也是越小 选的列在索引里
第二颗星，是避免排序，如果结果集采用现有顺序读取，那么就会避免一次排序，避免提前物化结果集 选的列在索引里，不用排序
第三颗星，避免每一个索引行查询，都需要去聚簇索引进行一次随机IO查询 选的列在索引里，不用排序，且索引包含的列等于要选的列

5. 索引的顺序

一般经验法则：索引列的选择性大的的放在前面(不重复的索引值和记录总数的比值）越大，放在前面，where的条件尽量按照索引的顺序，否则索引有可能不会生效

6. 查询优化路径

①开启慢查询
②打开开关：set global slow_query_log=‘ON’;
设置时间 set global long_query_time=1;
explain
③show profiling = 1 进行性能分析
④定期更新表：optimize table 整理空间，整理碎片，提高索引的效率
删除数据时，mysql并不会回收删除数据的占据的存储空间，以及索引位，而是空在那里，等待新的数据来弥补这个空缺，这样就有一个缺少，如果一时半会，没有数据来填补这个空缺，那这样就太浪费资源。不清理，数据库文件不会变小。但是执行命令时，表会被锁定。

7.修改字段和字段属性

modify：修改字段属性：alter table tb_name modify 字段名称 字段类型 [完整性约束条件]
change：修改字段名和属性：alter table 表名 change 原字段名 新字段名 字段类型 约束条件

8. 聚簇索引和非聚簇索引

https://github.com/CyC2018/CS-Notes/blob/master/notes/MySQL.md
https://blog.csdn.net/travel_1/article/details/54092039
聚簇索引：内容本身就是一种按照一定规律排列的目录，比如（时间顺序）正文和索引在一起，一张表只能有一个聚簇索引，比如自增id，只能有一个，资源宝贵，慎重。该索引一定要建在最频繁使用的字段上， order by聚簇索引效率最高，在索引中保存了数据，从而避免直接读取磁盘，因此对查询性能有很大的提升。索引叶子节点直接包含数据，非聚簇的叶子节点包含的是数据的指针
非聚簇索引 ：目录纯粹是目录，正文纯粹是正文的排序方式，正文和索引分离
聚簇索引只能有一个的原因：聚簇索引的数据行实际存放在索引的叶子页，数据行和相邻的键值紧凑得存储在一起，因为无法将数据行存放在两个不同的地方，所以聚簇索引一个表只有一个（不过覆盖索引可以模仿多个聚簇索引）

9. 大量数据插入和查询处理方法：

https://blog.csdn.net/aiib69/article/details/78806268
把一个小时之前的数据建立索引，当前一个小时的数据就不建立索引。也就是，不要在创建表的时候建立索引。
可以尝试读写分离，写两个库，一个是实时库，一个是只读库。一个小时内的数据查询实时库，一个小时之前的数据查询只读库；只读库定时存储，然后建立索引；超过一个星期的数据，进行分析处理再存储

10. 前缀索引：适用于索引列很长的字段

用索引的一部分来进行索引， 逐渐增加索引长度，然它的结果接近完整列的数据使用前缀索引的选择性能能接近0.03就可以用了，该索引无法做orderBy和groupBy，BLOB、TEXT 和 VARCHAR 类型的列

全文索引：使用倒排索引实现，实现根据属性找记录（FULLTEXT）
使用：
select*from ta1 where match (columnName)against (‘string’)
建议：在多列数据创建一个联合的全文索引
全文索引只能创建在char、varchar或者text字符串类型的字段上，且全文索引不支持前缀索引

11. 哈希索引：

哈希索引能以 O(1) 时间进行查找，但是失去了有序性
无法用于排序与分组
只支持精确查找，无法用于部分查找和范围查找

12. 覆盖索引

索引包含所有需要查询的字段的值
索引通常远小于数据行的大小，只读取索引能大大减少数据访问量。
一些存储引擎（例如 MyISAM）在内存中只缓存索引，而数据依赖于操作系统来缓存。因此，只访问索引可以不使用系统调用（通常比较费时）
对于 InnoDB 引擎，若覆盖索引能够覆盖查询，则无需访问主索引

13. InnoDB和MyISAM

事务：前者支持事务
并发：前者支持行级锁，后者表级锁
外键：前者支持外键，后者不支持
备份：前者支持在线热备份
崩溃恢复：前者修复能力更强
使用场景：前者适用于更新较多，后者适用于查询较多

MyISAM查询比较快的原因：
①InnoDB做select时维护的东西比较多，InooDB要缓存数据块，MyISAM只缓存索引块
②InooDB要维护MVCC（多版本并发控制），虽然自己的场景没有，但是它还是要维护
③InooDB寻址要映射到块再到行，后者记录的直接是文件的offset，定位快

14. 数据库的主从复制

binlog 线程 ：负责将主服务器上的数据更改写入二进制日志（Binary log）中
I/O 线程 ：负责从主服务器上读取二进制日志，并写入从服务器的重放日志（Replay log）中
SQL 线程 ：负责读取重放日志并重放其中的 SQL 语句

15. 事务的ACID

①原子性：全部成功或者全部失败
②一致性：所有的数据具有一致的状态，即一个数据改变了，其他的数据都是改变后的状态
③隔离性：两个事务之间互不相干，互不影响
④持久性：事务完成后，更改便持久得保存在数据库中，不会被回滚

16. 隔离级别

https://www.cnblogs.com/fjdingsd/p/5273008.html
① Serializable (串行化)：可避免脏读、不可重复读、幻读的发生
② Repeatable read (可重复读)：可避免脏读、不可重复读的发生
③ Read committed (读已提交)：可避免脏读的发生
④ Read uncommitted (读未提交)：最低级别，任何情况都无法保证。
串行化级别最高，执行效率最低，用锁表方式实现，mysql默认为③

脏读：事务1读取了事务2正在处理的数据
幻读：事务1正在处理数据，将1=>2,此时事务2存入了一个数据为1的数据，导致事务1处理了这个数据
不可重复读：事务1在处理数据，但是此时事务2处理了这些数据并提交，事务1就会得到不同的结果

17. 最左前缀原理

联合索引（abc），如果使用时是bca，查询优化器会自动优化成abc，如果使用时时bc，那么该索引不会生效
原因：InnoDb的索引结构是B+树，先序遍历，必须先从根开始

18. 悲观锁和乐观锁

乐观锁：
更新原则：提交的版本必须大于数据库当前的版本
共享锁：
操作：在查询语句后面加 lock in share mode，多个操作可以读这条资源，但是不能修改，直到共享锁释放
悲观锁：
操作：在查询语句后面加 for update，其它操作不能对这个资源进行任何操作

19. 数据库的连接池

原理，实现，优点
优点：
①反应时间更快，不用重复创建，释放连接
②资源重用，避免频繁创建，释放连接引起的大量性能开销
③统一连接管理，避免数据库连接泄露
原理：在内部对象池中维护一定数量的数据库连接，并对外暴露数据库连接获取和返回方法

20. 索引失效的情况

①like查询以 "%"开头，结尾的花不会失效
②条件中有or，要想生效，只能将or中每个条件加上索引
③多列索引，没有符合最左原则
④索引列进行了操作（计算，函数，类型转换）
⑤索引列使用了不等于，is null，is not null
⑥字符串不加单引号
⑦范围查询右边的列，一个列进行了范围查询，(>,<,like)，后面的列会失效

PS：多个单列索引在多条件查询一样只会生效第一个索引，所以多条件联合查询时最好建联合索引 https://blog.csdn.net/Abysscarry/article/details/80792876
多个单列索引也遵循最左原则

21.什么字段适合建立索引，索引的规则

①表的主键，外键
②和其它表的连接字段
③where子句的字段，分组的字段
④建立在小字段上，文本字段不要建立索引
⑤联合索引建立应该考虑索引顺序，前面的为选择性高的
⑥频繁更新的字段，尽量不要加索引
⑦删除无用的索引
索引优化：
https://www.jfox.info/2017/20-tiao-mysql-xing-nen-you-hua-de-zui-jia-jing-yan.html

22. 数据库范式

第一范式：原子性，每一列都是不可分割
第二范式：实体的属性完全依赖于关键字，每一行的数据只与其中一列相关，重复数据应该拆表,表里面不存在和id没关系的数据
第三范式：消除传递依赖，非主属性不得传递依赖主属性，即每个属性和主键有直接关系而不是间接关系，表里面不存在其它字段的关联（a->b->c）

23. 读写分离

主服务器处理写操作和实时性要求比较高的操作，从数据库处理读操作
提高性能的原因：
①各自读写，很大程度缓解锁的争用
②从服务器可以采用MyISAM,提升查询性能，节约系统开销
③适当增加冗余，提高可用性

24. B+，B-

介绍B+树：https://www.sohu.com/a/156886901_479559
https://github.com/CyC2018/CS-Notes/blob/master/notes/MySQL.md#b-tree-原理

B+树：
所有叶子节点位于同一层，且叶子节点按顺序存放，每个叶子节点带指向下一个节点的指针，形成一个有序链表，叶子节点层包含全部元素
查询：首先在根节点进行二分查找，然后递归子节点，直到找到叶子节点，然后再叶子节点进行二分查找。
插入删除：会破坏树的平衡性，需要对树进行分裂，合并，旋转

快的原因：
①查询稳定：每次的查询相同，都要从根节点走到叶子节点
②IO次数少：对比B-树，数据全在叶子节点，更加矮胖，IO次数少
③范围查询更快：直接遍历叶子节点层就行了，B-树需要中序遍历
B-树：