Mysql

mysql
1.索引
1.1.普通索引（index）
1.1.1.数据可以重复，没有任何限制
1.2.唯一索引（unique）
1.2.1.引列的值必须唯一，但允许有空值
1.3.主键索引
1.3.1.特殊的唯一索引，不允许有空值
1.4.复合索引
1.4.1.在多个字段上创建的索引，只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用
1.5.外键索引
1.5.1.只有InnoDB类型的表才可以使用外键索引，保证数据的一致性、完整性和实现级联操作
2.explain
2.1.table
2.1.1.表名
2.2.type
2.2.1.显示了连接使用了哪种类别
system
• 表只有一行
const
• 表只有一行匹配，并在查询开始时就被读取，因此被优化器视为常数
eq_reg
• 每次匹配时只会读取一行。当主键或者唯一非空索引的全部部分被使用时会出现
ref
• 每次匹配时会读取所有匹配行，当用到了非唯一索引或者唯一索引的一部分时会出现
ref_or_null
• 与ref相同，并且mysql还会搜索包含null值的行
range
• 根据索引选取一定范围内的行
• ken_len列表示使用到的最长的索引部分
index
• 从索引中获取所有的数据，不需要访问原表
• 根据索引的顺序进行全表扫描
ALL
• 全表扫描
2.3.possible_keys
2.3.1.可能用到的索引
2.4.key
2.4.1.实际使用的索引，不一定在possible_keys中存在
2.5.key_len
2.5.1.表示使用索引的长度，可以用来判断多列索引中的哪些部分被使用到
2.6.rows
2.6.1.检查的行数，innodb引擎是个估计值
2.7.filtered
2.7.1.过滤后得到行数的百分比，rows*filtered/100 表示实际用于join的行数
2.8.extra
2.8.1.using where
使用了where子句来筛选数据
2.8.2.using index
从索引中获取全部数据，不需要访问原表
2.8.3.using temporary
需要使用临时表来保存结果
2.8.4.using filesort
需要对取得的行进行排序，并保存排序的索引，行数据会按照索引的顺序被检索
3.视图
3.1.一个表或者多个表的行或列的子集。视图本身不独立存储在数据库中，是一个虚表
3.2.优点
3.2.1.视图能够简化用户的操作
3.2.2.能以多种角度看待同一数据
3.2.3.为数据库提供了一定程度的逻辑独立性
3.2.4.对机密数据提供安全保护
4.三范式
4.1.1NF是对属性的原子性约束，要求字段具有原子性，不可再分解
4.2.2NF是在满足第一范式的前提下，非主键字段不能出现部分依赖主键
4.2.1.消除复合主键就可避免出现部分依赖，可增加单列关键字
4.3.3NF是在满足第二范式的前提下，非主键字段不能出现传递依赖
4.3.1.将一个实体信息的数据放在一个表内实现
5.脏读、不可重复读、幻读
5.1.脏读
5.1.1.务T1将某一值修改，然后事务T2读取该值，此后T1因为某种原因撤销对该值的修改，这就导致了T2所读取到的数据是无效的
5.2.不可重复读
5.2.1.一个事务范围内的两次相同查询却返回了不同数据
5.3.幻读
5.3.1.当事务不是独立执行时发生的一种现象，比如第一个事务对一个表中的数据进行了修改，这种修改涉及到表中的全部数据行。同时，第二个事务也修改这个表中的数据，这种修改是向表中插入一行新数据。那么就会发生，操作第一个事务的用户发现表中还有没有修改的数据行，就好象发生了幻觉一样
6.事务隔离级别
6.1.Read Uncommitted读未提交
6.1.1.B事务还没提交，A事务就能实时读到数据，出现脏读
6.2.Read Committed读提交（Oracle默认）
6.2.1.当每个对象在写的时候，数据库会记住已经被提交的旧值和当前事务持有写锁设置的新值。当一个事务还在进行中，那么其他事务读取到的就是旧值。只有当新值被提交的时候才会被其他事务读取到
6.2.2.如果其他事务想要去写相同的数据对象，它必须等到上一个事务提交或者回滚然后去得到这把锁
6.2.3.会出现不可重复读或者读倾斜
6.3.Repeatableread可重复读（mysql默认级别）
6.3.1.确保事务可以多次从一个字段中读取相同的值，在此事务持续期间，禁止其他事务对此字段的更新
6.4.Serializable序列化
6.4.1.不允许读写并发操作，写执行时，读必须等待
7.MyISAM、InnoDB
7.1.InnoDB
7.1.1.支持事务
7.1.2.支持外键
7.1.3.支持并发
7.1.4.支持行级锁
7.2.MyISAM
7.2.1.不支持事务
7.2.2.查询速度快，不能读写操作太频繁
7.2.3.支持表级锁
8.CHAR、VARCHAR
8.1.CHAR
8.1.1.固定长度的类型
8.1.2.使用场景
 存储具有近似得长度（md5值，身份证，手机号）,长度比较短小得字符串
适合经常更新得字符串，更新时不会出现页分裂得情况，避免出现存储碎片，获得更好的io性能
8.2.VARCHAR
8.2.1.可变长度的类型
8.2.2.使用场景
字符串很少被更新，容易产生存储碎片
字符串列得最大长度比平均长度大很多
使用多字节字符集存储字符串
9.存储过程
9.1.预编译的SQL语句
9.2.只需创建一次，以后在该程序中就可以调用多次
10.sql调优
10.1.创建必要的索引
10.1.1.在 where 及 order by 涉及的列上建立索引
10.2.使用预编译查询
10.2.1.不仅可以避免SQL注入漏洞攻击
10.2.2.数据库会对这些参数化SQL进行预编译
10.2.3.第一次执行时会为这个SQL进行查询优化并执行预编译，以后再执行这个SQL时就直接使用预编译的结果
10.3.调整Where字句中的连接顺序
10.3.1.自下而上的顺序解析where字句
10.3.2.将from后面的多张表，按照记录最少的表排在最后
10.3.3.where条件中，能筛选掉最多记录的条件放在最后
10.4.用where字句替换HAVING字句
10.4.1.HAVING只会在检索出所有记录之后才对结果集进行过滤
10.4.2.where则是在聚合前筛选记录
10.5.使用表的别名
10.5.1.减少解析时间、并减少那些列名歧义引起的语法错误
10.6.用EXISTS替代IN、用NOT EXISTS替代NOT IN
10.7.避免在索引列上使用计算
10.8.避免在 where 子句中对字段进行 null 值判断
10.9.能用 between 就不要用 in
11.SQL注入
11.1.程序开发过程中不注意规范书写sql语句和对特殊字符进行过滤，导致客户端可以通过全局变量POST和GET提交一些sql语句正常执行
11.2.开启配置文件中的magic_quotes_gpc 和 magic_quotes_runtime设置
11.3.执行sql语句时使用addslashes进行sql语句转换
12.索引使用 B+ 树
12.1.二叉树
12.1.1.任意节点左子树不为空,则左子树的值均小于根节点的值
12.1.2.任意节点右子树不为空,则右子树的值均大于于根节点的值
12.1.3.任意节点的左右子树也分别是二叉查找树
12.1.4.没有键值相等的节点
12.2.AVL树
12.3.红黑树
12.4.B树
12.5.B+树
12.5.1.非叶子节点只保存索引，数据都保存在叶子节点中
12.5.2.只需要去遍历叶子节点就可以实现整棵树的遍历
12.5.3.磁盘读写代价更低
B+树的内部节点并没有指向关键字具体信息的指针，因此其内部节点相对B树更小，如果把所有同一内部节点的关键字存放在同一盘块中，那么盘块所能容纳的关键字数量也越多，一次性读入内存的需要查找的关键字也就越多，相对IO读写次数就降低了
12.5.4.查询效率更加稳定
于非终结点并不是最终指向文件内容的结点，而只是叶子结点中关键字的索引。所以任何关键字的查找必须走一条从根结点到叶子结点的路。所有关键字查询的路径长度相同，导致每一个数据的查询效率相当
13.sql
13.1.select * from 数据表 WHERE 重复记录字段 in ( select 重复记录字段 from 数据表 group by 重复记录字段 having count(重复记录字段)>1)