Mysql学习

数据库索引的数据结构有很多种，比如：哈希索引、平衡二叉树索引、B树索引、B+树索引等等。
目前最流行的是B+树索引，那大家有没有想过为什么是B+树索引最流行，为什么其他索引应用不广泛。
Hash索引不能使用范围查询
Hash索引仅仅能满足"=","IN"和"<=>"查询(注意<>和＜＝＞是不同的操作），不能使用范围查询，例如WHERE price > 100。
由于Hash索引比较的是进行Hash运算之后的Hash值，所以它只能用于等值的过滤，不能用于基于范围的过滤。
Hash索引不能利用部分索引键查询。
对于复合索引，Hash索引在计算Hash值的时候，是组合索引键合并后再一起计算Hash值，而不是单独计算Hash值。
所以通过复合索引的前面一个或几个索引键进行查询的时候，Hash索引也无法被利用。
Hash索引在任何时候都不能避免表扫描
Hash索引是将索引键通过Hash运算之后，将 Hash运算结果的Hash值和所对应的行指针信息存放于一个Hash表中。
由于不同索引键存在相同Hash值，所以无法从Hash索引中直接完成查询，还是要通过访问表中的实际数据进行相应的比较，并得到相应的结果。
平衡二叉树

淘汰原因:树的高度过高，高度越高，查找速度越慢，他支持范围查找，但是他需要进行回旋查找
比如我要找到大于5的数据
第一步我先定位到5，然后在树上按照二叉树规则去回旋查找大于5其他数据6、7、8、9、10。。。
B数索引：

B树和二叉树最大的区别在于：它一个节点可以存储两个值，这就意味着它的树高度，比二叉树的高度更低，它的查询速度就更快。这是他的优点
那为什么最终还是不用它呢，还是因为他在范围查找的时候，存在回旋查询的问题。同样order by排序的时候效率也很低，因为要把树上的数据手动排序一遍。
B+树:

它是B数的升级版，B+树相比B树，新增叶子节点与非叶子节点关系。
叶子节点中包含了key和value，key存储的是1-10这些数字，value存储的是数据存储地址，非叶子节点中只是包含了key，不包含value。
所有相邻的叶子节点包含非叶子节点，使用链表进行结合，有一定顺序排序，从而范围查询效率非常高.

MYSQL explain字段详细讲解

1.select_type
SIMPLE 简单的select查询，查询中不包含子查询或者UNION
PRIMARY 查询中若包含任何复杂的子部分，最外层查询则被标记为PRIMARY
SUBQUERY 在SELECT或WHERE列表中包含了子查询
DERIVED 在FROM列表中包含的子查询被标记为DERIVED（衍生），MySQL会递归执行这些子查询，把结果放在临时表中
UNION 若第二个SELECT出现在UNION之后，则被标记为UNION：若UNION包含在FROM子句的子查询中，外层SELECT将被标记为：DERIVED
UNION RESULT 从UNION表获取结果的SELECT
3.table
指的就是当前执行的表
4.type
type所显示的是查询使用了哪种类型，type包含的类型包括如下图所示的几种：

从好到差依次是：
system > const > eq_ref > ref > range > index > all
一般来说，得保证查询至少达到range级别，最好能达到ref。
system:表只有一行记录（等于系统表），这是const类型的特列，平时不会出现，这个也可以忽略不计
const:表示通过索引一次就找到了，const用于比较primary key 或者unique索引。因为只匹配一行数据，所以很快。如将主键置于where列表中，MySQL就能将该查询转换为一个常量。
eq_ref:唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描
ref:非唯一性索引扫描，返回匹配某个单独值的所有行，本质上也是一种索引访问，它返回所有匹配某个单独值的行，然而，它可能会找到多个符合条件的行，所以他应该属于查找和扫描的混合体
range:只检索给定范围的行，使用一个索引来选择行，key列显示使用了哪个索引，一般就是在你的where语句中出现between、< 、>、in等的查询，这种范围扫描索引比全表扫描要好，因为它只需要开始于索引的某一点，而结束于另一点，不用扫描全部索引
index:Full Index Scan，Index与All区别为index类型只遍历索引树。这通常比ALL快，因为索引文件通常比数据文件小。（也就是说虽然all和Index都是读全表，但index是从索引中读取的，而all是从硬盘读取的）
all:Full Table Scan 将遍历全表以找到匹配的行
possible_keys：显示可能应用在这张表中的索引，一个或多个。查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询实际使用。
key:实际使用的索引，如果为NULL，则没有使用索引
查询中若使用了覆盖索引（select 后要查询的字段刚好和创建的索引字段完全相同），则该索引仅出现在key列表中
key_len:表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度，在不损失精确性的情况下，长度越短越好。key_len显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出的.
rows:根据表统计信息及索引选用情况，大致估算出找到所需的记录所需要读取的行数，也就是说，用的越少越好
Extra:包含不适合在其他列中显式但十分重要的额外信息
Using filesort:说明mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。MySQL中无法利用索引完成的排序操作称为“文件排序"
Using temporary:使用了用临时表保存中间结果，MySQL在对查询结果排序时使用临时表。常见于排序order by和分组查询group by
Using index:表示相应的select操作中使用了覆盖索引（Covering Index），避免访问了表的数据行，效率不错。如果同时出现using where，表明索引被用来执行索引键值的查找；如果没有同时出现using where，表明索引用来读取数据而非执行查找动作。
Using where:表明使用了where过滤
Using join buffer:表明使用了连接缓存,比如说在查询的时候，多表join的次数非常多，那么将配置文件中的缓冲区的join buffer调大一些.
MYSQL 锁相关:
行级别的锁：
共享锁：允许事务读一行数据.
排他锁：允许事务删除或者更新一行数据.
行锁:修改一条语句的时候会出现，
表锁：当索引失效，行锁会升级为表锁.索引失效的其中一个方法是对索引自动 or 手动的换型。a 字段本身是 integer，我们加上引号，就变成了 String，这个时候索引就会失效了。
间隙锁:当我们采用范围条件查询数据时，InnoDB 会对这个范围内的数据进行加锁。比如有 id 为：1、3、5、7 的 4 条数据，我们查找 1-7 范围的数据。那么 1-7 都会被加上锁。2、4、6 也在 1-7 的范围中，但是不存在这些数据记录，这些 2、4、6 就被称为间隙。
间隙锁的危害:
范围查找时，会把整个范围的数据全部锁定住，即便这个范围内不存在的一些数据，也会被无辜的锁定住，比如我要在 1、3、5、7 中插入 2，这个时候 1-7 都被锁定住了，根本无法插入.
MYSQL死锁：
死锁是指两个或多个事务在同一资源上相互占用，并请求锁定对方占用的资源，从而导致恶性循环
乐观锁:假设不会发生并发冲突，只在提交操作时检查是否违反数据完整性。 乐观锁不能解决脏读的问题。
顾名思义，就是很乐观，每次去拿数据的时候都认为别人不会修改，所以不会上锁，但是在更新的时候会判断一下在此期间别人有没有去更新这个数据，可以使用版本号等机制
悲观锁：假定会发生并发冲突，屏蔽一切可能违反数据完整性的操作.
顾名思义，就是很悲观，每次去拿数据的时候都认为别人会修改，所以每次在拿数据的时候都会上锁，这样别人想拿这个数据就会block直到它拿到锁
2.在某些场景下会对性能产生很大的影响.
MYSQL隔离级别:

脏读：一个事务读取到另一个事务未提交的数据

事务（ACID)
原子性:通过undo log来实现
隔离性：通过(mvcc+读写锁）来实现的
持久性:通过Redo log来实现的
一致性:是通过原子性，持久性和隔离性来实现的
binlog属于mysql服务端的，undolog,redolog属于innodb存储引擎的
Redo log包括两部分：一是内存中的日志缓冲(redo log buffer)，该部分日志是易失性的；二是磁盘上的重做日志文件(redo log file)，该部分日志是持久的。
innodb通过force log at commit机制实现事务的持久性，即在事务提交的时候，必须先将该事务的所有事务日志写入到磁盘上的redo log file和undo log file中进行持久化。
Undo Log:提供回滚和多个行版本控制(MVCC)
Undo log和redo log记录物理日志不一样，它是逻辑日志。可以认为当delete一条记录时，undo log中会记录一条对应的insert记录，反之亦然，当update一条记录时，它记录一条对应相反的update记录。
当执行rollback时，就可以从undo log中的逻辑记录读取到相应的内容并进行回滚。有时候应用到行版本控制的时候，也是通过undo log来实现的：当读取的某一行被其他事务锁定时，它可以从undo log中分析出该行记录以前的数据是什么，从而提供该行版本信息，让用户实现非锁定一致性读取。
SQL注入-预防:
1.输入验证：检查用户输入的合法性，确信输入的内容只包含合法的数据
2.输入转义:每个DBMS都有一个字符转义机制来告知DBMS输入的是数据而不是代码，如果将用户的输入都进行转义，那么DBMS就不会混淆数据和代码，也不会出现SQL注入了
3.缩小权限:把每个数据库用户的权限尽可能缩小，在给用户权限时是基于用户需要什么样的权限
4.InnoDB和MYISAM如何选择:

1. 是否要支持事务，如果要请选择innodb，如果不需要可以考虑MyISAM；
2. 如果表中绝大多数都只是读查询，可以考虑MyISAM，如果既有读也有写，请使用InnoDB。
3. 系统奔溃后，MyISAM恢复起来更困难，能否接受；

InnoDB为什么推荐使用自增ID作为主键？
答：自增ID可以保证每次插入时B+索引是从右边扩展的，可以避免B+树和频繁合并和分裂（对比使用UUID）。如果使用字符串主键和随机主键，会使得数据随机插入，效率比较差。
为什么MyISAM会比Innodb的查询速度快?
数据块，INNODB要缓bai存du，MYISAM只缓存索引块，  这中间还zhi有换进换出的减少dao；
innodb寻址要映射到块，再到行，MYISAM记录的直接是文件的OFFSET，定位比INNODB要快
INNODB还需要维护MVCC一致；虽然你的场景没有，但他还是需要去检查和维护
MVCC (Multi-Version Concurrency Control)多版本并发控制

Mysql建立索引的原则
1.最左匹配原则 between,like,>,<
2.频繁作为查询条件的字段才创建索引
3.如果该字段频繁被更新，不适合创建索引
4.如果不能有效区分数据的字段，不适合建立索引
5.尽量扩展索引，而不是新建索引.
6.有外键的列，一定要建立索引
7.查询中比较少涉及到的字段，重复值比较多的列，不要建立索引
8.text,image,bit不要建立索引.
Mysql Mongodb应用场景对比:

Mongdb应用场景：
1）表结构不明确且数据不断变大
MongoDB是非结构化文档数据库，扩展字段很容易且不会影响原有数据。内容管理或者博客平台等，例如圈子系统，存储用户评论之类的。
2）更高的写入负载
MongoDB侧重高数据写入的性能，而非事务安全，适合业务系统中有大量“低价值”数据的场景。本身存的就是json格式数据。例如做日志系统。
3）数据量很大或者将来会变得很大
Mysql单表数据量达到5-10G时会出现明细的性能降级，需要做数据的水平和垂直拆分、库的拆分完成扩展，MongoDB内建了sharding、很多数据分片的特性，容易水平扩展，比较好的适应大数据量增长的需求。
4）高可用性
自带高可用，自动主从切换（副本集）
不适用的场景
1）MongoDB不支持事务操作，需要用到事务的应用建议不用MongoDB。
2）MongoDB目前不支持join操作，需要复杂查询的应用也不建议使用MongoDB。
关系型数据库适合存储结构化数据，如用户的帐号、地址：
1）这些数据通常需要做结构化查询，比如join，这时候，关系型数据库就要胜出一筹
2）这些数据的规模、增长的速度通常是可以预期的
3）事务性、一致性
　　
NoSQL适合存储非结构化数据，如文章、评论：
1）这些数据通常用于模糊处理，如全文搜索、机器学习
2）这些数据是海量的，而且增长的速度是难以预期的，
3）根据数据的特点，NoSQL数据库通常具有无限（至少接近）伸缩性
4）按key获取数据效率很高，但是对join或其他结构化查询的支持就比较差

Mongdb为什么速度快:
1.写操作MongoDB比传统数据库快的根本原因是Mongo使用的内存映射技术 － 写入数据时候只要在内存里完成就可以返回给应用程序，这样并发量自然就很高。而保存到硬体的操作则在后台异步完成
2.读操作快的原因是：Mongo是分布式集群所以可以平行扩展,
1.mysql查看当前正在执行的语句
show processlist
查看慢查询日志:
show VARIABLES like '%slow_query_log%'
慢查询时间设置:
show variables like '%long_query_time%'

mysql隐式类型转换,索引失效解析文章
https://www.cnblogs.com/guitu18/p/12113495.html