MySQL学习笔记
MYSQL学习笔记
MYSQL分为三层架构，第一层负责连接，线程处理，授权认证，安全等。第二层负责查询解析，分析，优化，缓存以及所有的内置函数，存储过程，触发器，视图。第三层是存储引擎，负责数据的存储和提取。
对于select语句，会先查询缓存，缓存中有直接返回，就不解析查询，优化和执行了。
两种重要的锁策略，1. 表锁，对整张表加锁。 2. 行级锁。
InnoDB 处理死锁的方式：将持有最少行级排他锁的事务进行回滚。
多版本并发控制MVCC是通过保存数据在某个时间点的快照来实现的。InnoDB会给每行记录增加一个开始时间和删除时间。保证事务查询到的结果在事务的时间内。
InnoDB是默认事务型引擎。MyISAM不支持事务和行级锁，并且崩溃后无法安全恢复。
除非用到某些InnoDB不具备的特性并且没有其他办法可以替代，否则都应该选择InnoDB引擎
如果需要事务支持，选InnoDB.如果不需要事务,选择MyISAM.
如果可以定期关闭服务器来备份,可以选择MyISAM,需要热备份就选择InnoDB.
InnoDB有更好的崩溃恢复性能.
MYSQL性能分析三方法:
- show profiles; 会把每个查询的耗时打印出来.
- show status;
- 慢查询日志
MYSQL优化
- 选择优化的数据类型,更小的通常更好.简单的数据类型更好.尽量避免null.如果可以,最好指定列为not null.varchar尽量小.能用char就不用varchar,尽量少用blob和text.使用枚举代替字符串类型.
- 主键最好使用int,不要用字符串当作主键.尽量少用ENUM和SET以及BIT。
- 尽量在只对小数进行精确计算是才使用decimal。
- 对于varchar只分配真正需要的空间。
- 对于datetime和timestamp，除了特殊情形外，尽量使用timestamp，因为后者空间效率更高。
- 合理结合范式和非范式。
- 对于alter table操作可以使用影子拷贝。（先复制，然后更改，之后重命名，这样可以避免影响原数据表）
- 尽量使用相同的数据类型存储相似或相关的值，尤其是在要在关联条件中使用的列。
- 尽量使用整型定义标志列（即主键列）。。
数据库的范式
第一范式 属性的原子性
所谓的第一范式就是数据库中的每一列都是不可分割的基本数据项，同一列中不能有多个值，即实体中的某个属性不能有多个值或者不能有重复的属性，如果出现重复的属性则需要重新构建实体，新的实体由重复的属性构成。
第二范式 属性完全依赖于主键
第二范式是在第一范式的基础上建立起来的，即满足第二范式必须先满足第一范式，第二范式要求数据库的每个实例或行必须可以被唯一的区分，即表中要有一列属性可以将实体完全区分，这个属性就是主键，即每一个属性完全依赖于主键，在员工管理中，员工可以通过员工编号进行唯一区分,
完全依赖概念：即非主属性不能依赖于主键的部分属性，必须依赖于主键的所有属性。
第三范式
满足第三范式必须先满足第二范式，第三范式要求一个数据库表中不包含已在其他表中已包含的非主关键字信息， 例如 存在一个课程表，课程表中有课程号(Cno),课程名(Cname),学分(Ccredit)，那么在学生信息表中就没必要再把课程名，学分再存储到学生表中，这样会造成数据的冗余， 第三范式就是属性不依赖与其他非主属性，也就是说，如果存在非主属性对于码的传递函数依赖，则不符合第三范式
索引
用到索引的四个语句, where. join. group by. order by.
常见的索引有以下几类
B-Tree索引
InnoDB其实使用的是B+树。只适用于以下几种查询：
- 全值匹配
- 匹配最左前缀
- 匹配列前缀
- 匹配范围值
- 精确匹配某一列，并范围匹配另外一列。
- 只访问索引的查询。
下面几种查询就不行
- 不是按照索引的最左列开始查找。
- 跳过索引中的列。
- 如果查询中有某个列的范围查询，那么其右边所有的列都无法使用索引优化查询。
哈希索引
基于哈希表实现，只有精确匹配所有的列的查询才有效。
InnoDB有一个特殊的功能叫做 “自适应哈希索引”，当某些索引值被使用的非常频繁时，会在内存中基于Btree索引之上再创建一个哈希索引。
空间数据索引
从所有纬度来索引数据，主要用在GIS开发。
全文索引
一种特殊类型的索引，他查找的是文本中的关键字，而不是直接比较索引中的值，有点类似于全文检索，像搜索引擎。
索引的优点
- 索引大大减少了服务器需要扫描的数据量
- 索引可以帮助服务器避免排序和临时表。
- 索引可以将随机I/O变为顺序I/O。
索引优化
- 独立的列，索引列不能是表达式的一部分，也不能是函数的参数。
- 对于前缀索引，要选择足够长的前缀以保证较高的选择性，同时又不能太长，以便节约空间
- 多列索引注意调整SQL以使用合并索引。
- 选择合适的索引列顺序。
- 聚簇索引 并不是一种单独的索引，而是一种数据存储方式，就是把数据行和索引存在了一起，你查到了索引，就找到了数据行。innodb就用的聚簇索引。而myisam存的是数据行的指针。
- 最好使用顺序性的数字来做索引，不要用uuid这种完全随机的字符串。这样的索引，更新起来可能会有很大的开销。
- 覆盖索引，你要查询的值直接就在索引里。这才叫快。查到索引就查到你要的数据了。
- 使用索引扫描来做排序，扫描的同时排好了序。这就要索引列的顺序和order by子句的顺序完全一致，而且所有列的排序方向一致。
- 压缩索引虽然能节省空间，但是只能顺序查找，无法二分查找，所以时间会变长。
- 增加索引会导致delete， insert， update等操作变慢。所以要删除冗余和重复索引。但有些索引很有用，删除慎重。
- innodb在二级索引上使用的是读锁，访问主键索引使用的是写锁。
- 当设计索引时，不要只为现在的查询考虑需要哪些索引，还要考虑对查询进行优化。
- 尽可能将需要范围查询的列放在索引的后面。可以用in（）的方式来覆盖那些不在where子句中的列。比如这个语句
where eye_color in ('brown', 'blue', 'hazel') and hair_color in('black', 'red', 'blonde', 'brown') and sex in ('M', 'F')这条语句会被优化器转化为24条语句。但是要是上千个组合的话就不适合了。
查询优化
- 不要查询不需要的记录，慎用select * ， 不要重复查询相同的数据，使用缓存更好。
- 将大查询切分成多个小的查询。
- 分解关联查询，对每一个表进行一次单表查询，然后将结果在应用程序中进行关联。
MYSQL主从同步
主从同步使得数据可以从一个数据库服务器复制到其他服务器上，在复制数据时，一个服务器充当主服务器（master），其余的服务器充当从服务器（slave）。因为复制是异步进行的，所以从服务器不需要一直连接着主服务器，从服务器甚至可以通过拨号断断续续地连接主服务器。通过配置文件，可以指定复制所有的数据库，某个数据库，甚至是某个数据库上的某个表。
使用主从同步的好处：
- 通过增加从服务器来提高数据库的性能，在主服务器上执行写入和更新，在从服务器上向外提供读功能，可以动态地调整从服务器的数量，从而调整整个数据库的性能。
- 提高数据安全-因为数据已复制到从服务器，从服务器可以终止复制进程，所以，可以在从服务器上备份而不破坏主服务器相应数据
- 在主服务器上生成实时数据，而在从服务器上分析这些数据，从而提高主服务器的性能
