Mysql

mysql

索引

1. 普通索引（index）

• 数据可以重复，没有任何限制

2. 唯一索引（unique）

• 引列的值必须唯一，但允许有空值

3. 主键索引

• 特殊的唯一索引，不允许有空值

4. 复合索引

• 在多个字段上创建的索引，只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用

5. 外键索引

• 只有InnoDB类型的表才可以使用外键索引，保证数据的一致性、完整性和实现级联操作

explain

1. table：表名
2. type：显示了连接使用了哪种类别

• system：表只有一行
• const：表只有一行匹配，并在查询开始时就被读取，因此被优化器视为常数
• eq_reg：每次匹配时只会读取一行。当主键或者唯一非空索引的全部部分被使用时会出现
• ref：每次匹配时会读取所有匹配行，当用到了非唯一索引或者唯一索引的一部分时会出现
• ref_or_null：与ref相同，并且mysql还会搜索包含null值的行
• range：根据索引选取一定范围内的行
• ken_len：表示使用到的最长的索引部分
• index：从索引中获取所有的数据，不需要访问原表；根据索引的顺序进行全表扫描
• ALL：全表扫描

3. possible_keys：可能用到的索引
4. key：实际使用的索引，不一定在possible_keys中存在
5. key_len：表示使用索引的长度，可以用来判断多列索引中的哪些部分被使用到
6. rows：检查的行数，innodb引擎是个估计值
7. filtered：过滤后得到行数的百分比，rows*filtered/100 表示实际用于join的行数
8. extra

• using where：使用了where子句来筛选数据
• using index：从索引中获取全部数据，不需要访问原表
• using temporary：需要使用临时表来保存结果
• using filesort：需要对取得的行进行排序，并保存排序的索引，行数据会按照索引的顺序被检索

视图

1. 一个表或者多个表的行或列的子集。视图本身不独立存储在数据库中，是一个虚表
2. 优点

• 视图能够简化用户的操作
• 能以多种角度看待同一数据
• 为数据库提供了一定程度的逻辑独立性
• 对机密数据提供安全保护

三范式

1. 1NF是对属性的原子性约束，要求字段具有原子性，不可再分解
2. 2NF是在满足第一范式的前提下，非主键字段不能出现部分依赖主键

• 消除复合主键就可避免出现部分依赖，可增加单列关键字

3. 3NF是在满足第二范式的前提下，非主键字段不能出现传递依赖

• 将一个实体信息的数据放在一个表内实现

脏读、不可重复读、幻读

1. 脏读

• 事务T1将某一值修改，然后事务T2读取该值，此后T1因为某种原因撤销对该值的修改，这就导致了T2所读取到的数据是无效的

2. 不可重复读

• 一个事务范围内的两次相同查询却返回了不同数据

3. 幻读

• 当事务不是独立执行时发生的一种现象，比如第一个事务对一个表中的数据进行了修改，这种修改涉及到表中的全部数据行。同时，第二个事务也修改这个表中的数据，这种修改是向表中插入一行新数据。那么就会发生，操作第一个事务的用户发现表中还有没有修改的数据行，就好象发生了幻觉一样

事务隔离级别

1. Read Uncommitted读未提交

• B事务还没提交，A事务就能实时读到数据，出现脏读

2. Read Committed读提交（Oracle默认）

• 当每个对象在写的时候，数据库会记住已经被提交的旧值和当前事务持有写锁设置的新值。当一个事务还在进行中，那么其他事务读取到的就是旧值。只有当新值被提交的时候才会被其他事务读取到
• 如果其他事务想要去写相同的数据对象，它必须等到上一个事务提交或者回滚然后去得到这把锁
• 会出现不可重复读或者读倾斜

3. Repeatableread可重复读（mysql默认级别）

• 确保事务可以多次从一个字段中读取相同的值，在此事务持续期间，禁止其他事务对此字段的更新

4. Serializable序列化

• 不允许读写并发操作，写执行时，读必须等待

MyISAM、InnoDB

1. InnoDB

• 支持事务
• 支持外键
• 支持并发
• 支持行级锁

2. MyISAM

• 不支持事务
• 查询速度快，不能读写操作太频繁
• 支持表级锁

CHAR、VARCHAR

1. CHAR

• 固定长度的类型
• 使用场景
  • 存储具有近似得长度（md5值，身份证，手机号）,长度比较短小得字符串
  • 适合经常更新得字符串，更新时不会出现页分裂得情况，避免出现存储碎片，获得更好的io性能

2. VARCHAR

• 可变长度的类型
• 使用场景
  • 字符串很少被更新，容易产生存储碎片
  • 字符串列得最大长度比平均长度大很多
  • 使用多字节字符集存储字符串

存储过程

1. 预编译的SQL语句
2. 只需创建一次，以后在该程序中就可以调用多次

sql调优

1. 创建必要的索引

• 在 where 及 order by 涉及的列上建立索引

2. 使用预编译查询

• 不仅可以避免SQL注入漏洞攻击
• 数据库会对这些参数化SQL进行预编译
• 第一次执行时会为这个SQL进行查询优化并执行预编译，以后再执行这个SQL时就直接使用预编译的结果

3. 调整Where字句中的连接顺序

• 自下而上的顺序解析where字句
• 将from后面的多张表，按照记录最少的表排在最后
• where条件中，能筛选掉最多记录的条件放在最后

4. 用where字句替换HAVING字句

• HAVING只会在检索出所有记录之后才对结果集进行过滤
• where则是在聚合前筛选记录

5. 使用表的别名

• 减少解析时间、并减少那些列名歧义引起的语法错误

6. 用EXISTS替代IN、用NOT EXISTS替代NOT IN
7. 避免在索引列上使用计算
8. 避免在 where 子句中对字段进行 null 值判断
9. 能用 between 就不要用 in

SQL注入

1. 程序开发过程中不注意规范书写sql语句和对特殊字符进行过滤，导致客户端可以通过全局变量POST和GET提交一些sql语句正常执行
2. 开启配置文件中的magic_quotes_gpc 和 magic_quotes_runtime设置
3. 执行sql语句时使用addslashes进行sql语句转换

索引使用 B+ 树

1. 二叉树

• 任意节点左子树不为空,则左子树的值均小于根节点的值
• 任意节点右子树不为空,则右子树的值均大于于根节点的值
• 任意节点的左右子树也分别是二叉查找树
• 没有键值相等的节点

2. AVL树
3. 红黑树
4. B树
5. B+树

• 非叶子节点只保存索引，数据都保存在叶子节点中
• 只需要去遍历叶子节点就可以实现整棵树的遍历
• 磁盘读写代价更低
  • B+树的内部节点并没有指向关键字具体信息的指针，因此其内部节点相对B树更小，如果把所有同一内部节点的关键字存放在同一盘块中，那么盘块所能容纳的关键字数量也越多，一次性读入内存的需要查找的关键字也就越多，相对IO读写次数就降低了
• 查询效率更加稳定
  • 于非终结点并不是最终指向文件内容的结点，而只是叶子结点中关键字的索引。所以任何关键字的查找必须走一条从根结点到叶子结点的路。所有关键字查询的路径长度相同，导致每一个数据的查询效率相当

sql

1. select * from 数据表 WHERE 重复记录字段 in ( select 重复记录字段 from 数据表 group by 重复记录字段 having count(重复记录字段)>1)