计算机基础——MySQL

MySQL

• 架构
  • 如果能在查询缓存中找到该查询，就不需要解析优化执行过程，直接返回查询缓存中的结果集。
    
• 连接/线程处理
  • 管理：服务器为每个客户端创建一个线程，连接的查询在这个线程中单独进行。并不会为每个新建的连接创建或者销毁线程，因为服务器会负责缓存线程，因为可以利用线程池中少量的线程服务大量的连接。
  • 安全：认证客户端（应用）的用户名、原始主机信息、密码，可以使用安全套接字（SSL）的方式连接。连接成功还会验证某个特定查询的权限？？。
• 优化与执行：负责创建内部数据结构，然后优化，包括重写查询，决定表的读取顺序，以及选择合适的索引。
• 并发：服务器和存储引擎都会控制
  • 锁粒度(存储引擎实现)：表锁、行级锁。
• 事务(存储引擎实现)
  • ACID特性：
    • 原子性：不可分割，要么一起执行，要么一起回滚。
    • 一致性：从要一个一致性的状态转到另一个一致性的状态，不会到不安全的状态。（转账）
    • 隔离性：只要不是未提交读，外部不可见。
    • 持久性：事务提交后会永久保存到数据库
  • 隔离级别：
    • RU未提交读：脏读、不可重复读、幻读
    • RC提交读：不可重复读、幻读。
    • RR可重复读：其实mysql在RR级别，是没有幻读情况。用行锁解决了不可重复读，也就是读取到数据后就加锁，其它事务无法修改这些数据，但解决不了A事务开启后，B事务插入一条数据，A事务发现莫名其妙多了一条数据的幻读问题。行锁会禁止update和delete但无法阻止insert。当RR级别，update时，如果事务update的是根据主键去查找的数据行，间隙锁会锁上大于等于该主键字段最小值的数据行，防止别的事物insert；如果事务update的是根据非主键去查找的数据行，那么会给全表加上gap锁。因此行锁间隙锁组成的Next-Key锁防止了幻读。InnoDB用乐观锁原理的MVCC也能防止幻读。
    • S可串行化：读加共享锁（可以并发读数据，但任何事务都不能修改数据），写加排他锁，写锁排读和写。强制事务串行执行，每行数据都加锁。悲观锁。
  • 解决死锁
    • 等待超时后放弃锁请求
    • 持有最少行级排它锁的事务回滚
  • 预写日志：修改表时，把行为记录在硬盘的事务日志中，然后再处理这些行为。两次写磁盘：一次写日志、一次写数据。若系统崩溃，就是要恢复这个日志。
  • 自动提交：
    • =1：每个语句都是一个事务。
    • =0：所有的语句都在一个事务中，直到遇到COMMIT或ROLLBACK。
  • 应禁用的功能：
    • 在事务中混用事务型和非事务型的表。
    • 不要用显示锁定。
  • InnoDB的MVCC(不同存储引擎的MVCC不同，只在提交读和可重复读下工作)：在每行后面加入两个隐藏的列，创建时间和删除时间。只查找创建时间在当前事务时间之前、删除时间在当前事务之后的数据行。
• 存储引擎
  MySQL为每个表创建.frm文件来保存表定义
  • InnoDB
    • 优点：支持事务、MVCC、行锁、热备份、自动灾难恢复、Savepoints（它在一个事务中可以出现多次，可以控制代码回滚到savepoint指定的位置）。
    • 缺点：占用空间较大
  • Memory
    • 优点：支持哈希索引，查询速度最快，比MyISAM快，适合数据量小，内存中存放数据。支持B树索引，因此满足部分查询。
    • 缺点：不支持事务、行锁(整表加锁)，数据丢失无法恢复。
  • MyISAM
    • 优点：支持全文索引。插入、查询速度快。适合只读的数据、小表。地理空间搜索
    • 缺点：不支持事务、行锁(整表加锁)、外键，崩溃后无法安全恢复。
  • SphinxSE：第三方存储引擎、全文索引
• 基准测试
  • 作用：验证假设、重新异常、当前运行情况、模拟负载、硬件条件，并发的性能、测试配置。
  • 指标：吞吐量(单位时间内事务处理数)、响应时间、并发性(同时发生的并发请求的个数)、可扩展性
• 索引(存储引擎层实现)：
  • 普通索引，INDEX：没有任何限制，B树
  • 唯一索引，UNIQUE：值必须唯一，允许有空值，B树
  • 主键索引，PRIMARY KEU：值必须唯一，不允许有空值。只能有一个。如果没有创建主键索引，就会自动创建。
  • 全文索引，FULLTEXT：仅可用于 MyISAM 表，针对较大的数据，生成全文索引很耗时耗空间。
  • 复合索引：为了更多的提高mysql效率可建立组合索引，遵循”最左前缀“原则。ALTER TABLE `table_name` ADD INDEX index_name ( `column1`, `column2`, `column3` ) 
  • 外键：
    保持数据的一致性和完整性
    • 创建前提：Innodb为存储引擎，两个个表中外键的列必须类型一致，如果为已存在数据的表设置外键，那么字表的数据必须和父表对应。
    • 创建键：两个表，只要有唯一索引或者主键索引，都可以给这两个表的索引字段创建外键foreign key。alter table A add foreign key(id) references B(id);
    • 删除键：alter table A drop foreign key 键的名称。键的名称通过show create table A;查看。
    • 插入数据：父表存在，字表才能插入。对子表(外键所在的表)的作用：子表在进行写操作的时候，如果外键字段在父表中找不到对应的匹配，操作就会失败。
    • 删除数据：字表删除，父表才能删除。对父表的作用：对父表的主键字段进行删和改时，如果对应的主键在子表中被引用，操作就会失败。
• 数据类型
  • 整数：TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT。分别1(UNSIGNED 0~255或-128~127)、2、3、4、8字节。
  • 实数：FLOAT、DOUBLE(内部计算)、DECIMAL。技巧：使用BIGINT代替DECIMAL，避免计算精度不够。
  • 字符串：VARCHAR、CHAR。VARCHAR需要额外字节纪录长度，使用它的情况：该列最大字符串长度比平均长度大很多，列很少更新。
  • 大的字符串：BLOB(二进制)、TEXT(有字符集和排序规则)。
  • 枚举：e ENUM('','','')。MySQL将元素存储为整数，还要转换字符串，因此不要用字符串进行表关联。
  • 时间日期：DATETIME年月日、TIMESTAMP时间戳
  • 位：BIT
• 范式
  • 第一：
    • 单个字段中用逗号隔开多个值(一个数据里面有两个数据，那就要多创建一行分开说明)
    • 必须有一列可以创建唯一索引。
    • 多个字段表达同一个意思(比如不喜欢吃的食物123)
  • 第二：一是表必须有一个主键(满足第一)；二，主键外的列不能部分依赖主键。 不然要把表拆分。
  • 第三：不是键的字段互相没有因果(依赖)关系。消除传递依赖。
  • 优点：更新操作快、没有数据冗余，要修改的数据更少、表小能放到内存、很少使用DISTINCT和GROUP BY语句。
  • 缺点：常需要关联。比如要从全部客户中找出数量很少的会员，还要关联另一个表中与会员有关的信息，那么遍历的过程非常耗时。如果将两表合并并创建（会员类型、信息）索引，或在两个表中都加入会员类型信息，就高效。
• 索引：
  • B+树：InnoDB用B+树。适用于：全部索引匹配、索引的第一列、第一列的第一个字母、第一个索引的范围查找、第一个索引精确第二个索引范围查找(>、<、between、like)、只访问索引即覆盖索引。
  • 哈希：只有Memory支持。每行数据都有一个哈希码。哈希表存储指向行的指针。局限很多：不能排序、不能只匹配部分列、哈希冲突。另：InnoDB会在B+树上为频繁查询内容建哈希索引。
  • 自定义伪哈希索引：为哈希值创建B+树。建索引：触发器。过程：查询时在where中用哈希值查找，在B+树中查找该哈希值，最后找到很长的url。
  • 全文索引：搜索引擎，查找文本中的关键词。
• 选择合适的索引列：因素一：选择性最高DISTINCT col/count(*)，因素二：查询频率最高的列
• 高性能索引：
  • 最左前缀匹配原则，选择区分度高的列作为索引，字段不重复的比例，比例越大我们扫描的记录数越少，一般需要join的字段我们都要求是0.1以上，即平均1条扫描10条记录。
  • 列不能参与计算，如from_unixtime(create_time) = ’2014-05-29’。
  • =和in可以乱序，比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序，mysql的查询优化器会帮你优化成索引可以识别的形式
  • 尽量的扩展索引，不要新建索引。比如表中已经有a的索引，现在要加(a,b)的索引，那么只需要修改原来的索引即可
  • 不能为每个列都建索引，要用UNION。因为耗费太大，而且老版本会全局扫描，还不会计算到查询成本中优化器不会识别这个查询。
• 
• InnoDB的聚簇索引：
  • 概念：是数据存储的方式，一张表一个，索引顺序和物理位置顺序一致。InnoDB选择主键来聚集数据。聚簇索引的叶子节点就是数据节点（整行数据），而非聚簇索引的叶子节点仍然是索引节点，只不过有指向对应数据块的指针。
  • 优点：
    • 范围查询效率高，而且要排序的时候也很方便
    • 索引和数据一个B+树中通常获取数据更快
    • 使用覆盖索引扫描的查询可以直接使用叶节点中的主键值
  • 缺点：
    • 如果数据全都放在内存中了，聚簇索引就没什么作用了
    • 如果不是按照主键顺序插入，速度很慢，且更新时代价高。因为产生“页分裂”，页变得稀疏，已经存入磁盘的数据还要读取到内存再修改，占用更多磁盘空间？，耗时。(也会导致全表扫描可能慢)
    • 二级索引（非聚簇）叶子节点要包括主键索引，可能很大，而且二级索引要两次查找索引，存储引擎第一次通过二级索引找到叶子节点、找到主键，然后在聚簇索引中用主键再找索引，再找到数据。
• 覆盖索引(B+树才能实现)
  • 概念：索引包含要查询的数据。“Using index”是EXPLAIN的Extra显示的信息。当只查找索引列时有效。
  • 优点：
    • 减少磁盘数据访问
    • 相比随机io，范围查询减少磁盘io的访问
    • MyISAM在内存中只缓存索引，操作系统缓存数据，如果可以用覆盖索引提高效率
    • InnoDB的聚簇索引中，如果二级索引覆盖了查询，就不用对主键索引查询。
  • 注意：通配符开头的LIKE查询，存储引擎做不了，会提取所有数据拿到服务器进行匹配。所以，先查询有条件限制的列，再查询所有列
• 实现排序
  • 排序
  • 按索引排序：“index”是EXLPAIN显示的信息。ORDER BY的顺序、正逆都和索引一样才能用。
• MyISAM的前缀压缩索引
• having和where：GROUP BY之前执行WHERE，之后执行HAVING。
• 内联结和外联结：INNER JOIN … ON，LEFT OUTER JOIN … ON
• 优化查询：
  • 指标：响应时间、扫描行数、返回行数
  • 工具：慢查询日志
• where的三个层次
  • 在存储引擎过滤where
  • 存储引擎过滤where后，服务器直接返回结果，因为使用了覆盖索引，不需要回表
  • 存储引擎把数据取出，服务器过滤。
• MySQL九大对象
• 表：
• 索引：
• 视图：虚拟的表，在数据库中并不实际存。从一个或多个表中取出我们需要的字段，组成一个类似于表的对象。
• 图表：
• 触发器：当对一个表进行插入、更改、删除时，触发器会执行另一个sql语句。
• 缺省值：对没有指定其具体值的列或列数据项赋予事先设定好的值。
• 规则：规则是对数据库表中数据信息的限制。它限定的是表的列。
• 存储过程：存储过程就是一种类似函数的脚本，可以把多个sql语句组合起来，然后使用 call 存储过程名 来调用，从而执行这些SQL语句。特点：一次编译，下次直接运行
• 用户：有权限访问数据库的人。