天天看点
返回

《高性能MYSQL》

MYSQL的架构和历史

1.1 MYSQL逻辑架构

《高性能MYSQL》

第一层：连接处理，授权认证，安全 等等

第二层：查询解析、分析、优化、缓存 以及 所有的内置函数。包含跨存储引擎的功能：存储过程、触发器、视图等。

第三层：存储引擎。负责MySQL中数据的存储和提取。

1.2并发控制

无论何时，只要有多个查询需要在同一时刻修改数据，都会产生并发控制的问题。

讨论mysql两个层面的并发控制：存储引擎层 与 服务器层

读写锁

在处理并发写或并发读写时，可以通过由两种类型的锁组成的锁系统来解决问题，这两种锁就是 共享锁（读锁） 和 排它锁（写锁）。读锁是共享的，互相不阻塞，写锁是排它的，只有一个线程能进行写操作，其他读锁和写锁都是阻塞的。

并且，写锁拥有更高的优先级。在一个锁队列中，写锁可以插到读锁的前面。

** 锁粒度**

一种理想的锁方式是，尽量只锁定需要修改的资源，而不是所有资源。锁定的数据量越小，并发程度越高。

但是锁也是需要时空开销的，判断是否有锁、加锁、释放锁的操作都需要额外的开销，如果锁粒度太小，虽然并发程度高，但系统花大量资源去管理锁，而不是存取数据，也是得不偿失。

锁策略

就是在 锁的开销 和 数据安全性 之间寻求平衡。大多数据库都是行级锁 ，而mysql提供了更多锁的可能性。每种存储引擎都可以实现自己的锁策略和锁粒度。将锁粒度固定在某一级别，可以为特定的应用场景提供更好的性能，但同时也会失去对一些应用场景的支持。但好在mysql支持多个存储引擎。

表锁

表锁是mysql中最基本、开销最小的锁策略。尽管存储引擎可以设计管理自己的锁，但mysql服务器还是利用表锁来实现不同的目的。例如：会为alter table之类的语句使用表锁，而忽略存储引擎的锁机制。

行级锁

行级锁是mysql中支持并发量最大、开销最大的锁策略。行级锁只在存储引擎层实现，服务器层没有实现。并且服务器层完全不了解存储引擎层的锁实现。

1.3 事务

事务是一组原子性的操作，是一个独立执行单元。事务内的语句，要么全部执行成功，要么全部执行失败。

事务的四个标准特征（ACID）

  • 原子性（atomicity）：一个事务必须被视为不可分割的最小工作单元，整个事务的所有操作要么全部提交成功，要么全部失败回滚。
  • 一致性（consistency）：数据库总是保持一致性的状态
  • 隔离性（isolation）：通常来说，一个事务在没有最终提交以前，对其它事务是不可见的。
  • 持久性（durability）：一旦事务提交，则其所做的唏嘘该就会永久保存到数据库中。

隔离级别

  • 未提交读（READ UNCOMMITTED）：事务中的修改，即使没有提交，对其他事务也都是可见的。脏读问题（事务1改，事务2读，事务1回滚）。
  • 提交读（READ COMMITTED）：大多数数据库的默认隔离级别是提交读（MySQL不是）。事务从开始直到提交之前，所作的修改对其他事务都是不可见的。也叫不可重复读。不可重复读问题（事务1读，事务2改，事务1读）。
  • 可重复读（REPEATABLE READ）：保证在同一个事务中多次读取同样记录的结果是一致的。可重复读是MySQL的默认事务隔离级别。幻读（事务1范围操作，事务2插入数据，事务1读，产生幻行）
  • 可串行化（SERIALIZABLE）：非常需要数据的一致性和接受没有并发的情况性，才考虑该级别。

死锁

当两个或多个事物占用着自己的资源，而都在等待对方占用的资源时，会形成死锁。

start transaction
update a
update b
commit

start transaction
update b
update a
commit

一种是在事务开始前检测死锁的循环依赖，若有可能导致死锁，则报错。

一种是发生死锁时，设置最长等待时间，大于这个时间则放弃资源（不推荐，性能变差）。

一种是发生死锁时，将拥有最少行级写锁的事务回滚。

1.4 多版本并发控制

以InnoDB的MVCC为例，InnoDB以 在每行记录后面保存两个隐藏的列 来实现MVCC。这两个列，一列保存 这一行的创建时间，一列保存 这一行的过期时间。但存储的并不是真正的时间，而是 系统版本号。每开始一个事务，系统版本号就会自增。事务通过自身的系统版本号与这两个隐藏的列对比，来操作数据。

在可重复度的隔离级别下，InnoDB的MVCC的具体操作如下：

  • select InnoDB查找 创建系统版本号 <= 事务系统版本号的行，即在事务开始前就存在的行，或者由事务自身插入或修改的行。同时该行的过期系统版本号要么未定义，要么 > 事务系统版本号。
  • insert 新插入的每一行 创建时间都是当前事务的系统版本号。
  • delete 删除的每一行 过期时间都是当前事务的系统版本号。
  • update 插入一条新数据，创建时间是当前事务的系统版本号，将原来行的过期时间置为当前事务的系统版本号。

MVCC只在 提交读 和 可重复读 两个隔离级别下工作，因为未提交读总是能读取到最新的行，而不是符合当前事务版本的行，而串行化会对所有行加锁。

MyISAM和InnoDB的区别

事务： MyISAM不支持事务，InnoDB支持事务。

锁粒度： MyISAM只支持表锁。InnoDB支持行锁。

存储： MyISAM存三个文件(.frm .MYD .MYI)，支持动态，静态和压缩三种存储格式。InnoDB存一个文件(.frm)。

外键： MyISAM不支持外键，InnoDB支持外键。

索引： MyISAM是索引+行指针，InnoDB是聚簇+非聚簇（这个点决定了其主键的必要性）。

安全： MyISAM不支持崩溃安全回复，InnoDB支持（redo_log）。

行数统计： MyISAM中维护一个计数器记录总行数，select(*)时很快，而InnoDB需要全表扫描，所以较慢。

Schema与数据类型优化

4.1选择优化的数据类型

  • 更小的通常更好。

    ​ 应该尽量使用可以正确存储数据的最小类型，更小的数据类型通常更快，因为他们占用更少的磁盘，内存和CPU缓存，并且处理时需要的CPU周期更少。

  • 简单就好

    ​ 更简单的数据类型的操作通常需要更少的CPU周期。例如，整型数字比字符操作代价更低，因为字符集和校对规则（排序规则）使字符比较相对整型数字比较更复杂。比如，应使用INTERGER存储IP地址（inet_aton）

  • 尽量避免NULL

    ​ 通常情况下，最好指定列为NOT NULL。如果查询中包含可为NULL的列，对MySQL来说更难优化，因为可为NULL的列使得索引，索引统计和值比较非常复杂，可为NULL的列会使用更多的存储空间，当可谓NULL的列被索引时，每个索引记录需要一个额外的字节。但是把可为NULL的列改成NOT NULL带来的性能提升比较小，但如果计划在列上创建索引，就应该避免设计成可为NULL的列。

4.1.1整数类型

整数类型 占用空间 范围
TINYINT 8 [-2^7,2^7-1]
SMALLINT 16 [-2^15,2^15-1]
MEDIUMINT 24 [-2^23,2^23-1]
INT 32 [-2^31,2^31-1]
BIGINT 64 [-2^63,2^63-1]
INT(11)只是指定显示字符的范围。不会限制值得范围。

4.1.1.2实数类型

实数是带有小数部分的数字，可以使用DECIMAL存储比BIGINT还大的整数。

DECIMAL类型用于存储精确的小数，支持精确计算。

4.1.3字符串类型

varchar类型用于存储可变长字符串，比定长更节省空间。

char 定长字符串，MySQL在存储时会去除char尾部的空格。

blob

​ 采用二进制的方式存储，没有排序规则和字符集。包含tinyblob,blob,mediumblob,longblob

text

​ 采用字符串的方式存储，有排序规则和字符集，包含tinytext,text,mediumtext,longtext。

4.1.4日期和时间类型

DATETIME和TIMESTAMP

​ 现在推荐使用DATETIME，范围更大，与时区无关，占用8个字节

datetime：大范围的值 1001 9999 s YYYYMMDDHHMMSS 与时区无关 8字节。

timestamp：1970 2038，1970 1 1以来的秒数，时区 4字节 。

4.2MySQL 模式设计的陷阱

太多的列

太多的关联

NULL值

4.3范式和反范式

​ 在范式化的数据库中，每个事实数据只会出现一次，

​ 反范式化的数据库中，信息是冗余的，可能会存储在多个地方。

范式优点：

​ 范式化的更新操作更快，只需要更改较少的数据。

​ 范式化的表更小，可以更好的放在内存里，执行操作会更快。

​ 没有多余的数据，可以减少distinct或GROUP BY的操作。

范式缺点：

​ 通常需要关联，关联代价昂贵，也可能使一些索引策略无效。

反范式优点:

​ 所有的数据都在一张表中，可以避免关联。

​ 不关联的时候即使全表扫描，也是顺序IO。

反范式缺点：

​ 冗余的多余数据，更新更慢

​ 表大，放到内存中，占用大，容易挤出热数据

4.4缓存表和汇总表

4.5 加快Alter table 的速度

​ ALTER TABLE操作对特大表来说，是个大问题。

只修改frm（表结构）文件

创建高性能的索引

索引是存储引擎用于快速找到记录的一种数据结构。

5.1 索引基础

要理解MySQL中索引是如何工作的，最简单的方法就是去看看一本书的 “索引” 部分：如果想在一本书中找到某个特定主题，一般会先看书的“索引”，找到对应的页码。

在MySQL中， 存储引擎用类似的方法使用索引， 其先在索引中找到对应值， 然后根据匹配的索引记录找到对应的数据行。 假如要运行下面的查询：

mysql> SELECT first_name FRom sakila.actor WHERE actor_id = 5;

如果在actor_id列上建有索引， 则MySQL将使用该索引找到actor_id为5的行， 也就是说， MySQL先在索引上按值进行查找， 然后返回所有包含该值的数据行。

索引可以包含一个列或多个列的值。如果索引包含多个列，那么列的顺序也很重要。因为MYSQL只能高效地使用索引的最左前缀列。

索引的类型

在MySQL中， 索引是在存储引擎层而不是服务器层实现的。

《高性能MYSQL》

B-Tree索引

  • 全值匹配：指和索引中所有的列进行匹配。例如匹配key的所有字段(last_name,first_name,dob)。
  • 匹配最左前缀：只使用索引的第一列。例如只使用last_name.
  • 匹配列前缀：也可匹配某列的值开头部分。比如以J开头的人。这里只使用索引的第一列。
  • 匹配范围值：查找姓在Allen到Barry之间的人。这里只使用索引的第一列。
  • 精确匹配某一列并范围匹配另一列：第一列last_name精确匹配，第二列first_name范围匹配。
  • 只访问索引的查询：即查询只需要访问索引。

B-Tree索引限制

  • 如果不是按照索引的最左列开始查找，则无法使用索引。如上面例子的索引无法查找名称为BIll的人。也无法查找某个特定生日的人。

    不能跳过索引中的列。无法查找姓为Smath并且在特定日期出生的人，因为跳过了first_name 列。

  • 如果查询中有某个列的范围查询，则其右边所有的列都无法使用索引优化查找。例如查询Where last_name = ‘Smath’ and first_name like ‘%J’ and dob=‘1970-02-01’.这个查询只能使用索引的前两列，因为like是一个范围条件。

哈希索引

《高性能MYSQL》

哈希索引使用哈希表实现，只有精确匹配索引所有列的查询才有效。

哈希索引限制

  • 哈希索引只能包含哈希值和行指针。所以不能用索引的值避免读行。
  • 哈希索引并不是按照索引值顺序存储的，所以无法进行排序。
  • 哈希索引不支持部分索引匹配列查找。
  • 哈希索引只支持等值比较查询。
  • 访问哈希索引非常快，除非有哈希冲突。当哈希出现冲突时，会进行连表存储。

  • 哈希冲突时，索引重建

    代价会很高。

InnoDB引擎有一个特殊的功能叫做 “自适应哈希索引(adaptivehash index)"。当InnoDB注意到某些索引值被使用得非常频繁时，它会在内存中基千B-Tree索引之上再创建一个哈希索引，这样就让B-Tree索引也具有哈希索引的一些优点，比如快速的哈希 查找。

5.2 索引的优点

  1. 索引大大减少了服务器需要扫描的数据量。
  2. 索引可以帮助服务器避免排序和临时表。
  3. 索引可以将随机I/O变为顺序I/O。

5.3 高性能的索引策略

独立的列

slelect actor_id from actor where actor_id +1=5

actor_id +1=5无法被解析成actor_id = 4。所以要将索引列单独存放在比较符合的一侧。

前缀索引和索引选择性

有时候需要索引很长的字符列， 这会让索引变得大且慢。

通常可以索引开始的部分字符，这样可以大大节约索引空间， 从而提高索引效率。但这样也会降低索引的选择性。索引的选择性是指，不重复的索引值（也称为基数，cardinality)和数据表的记录总数(#T)的比值，范围从1/#T到1之间。索引的选择性越高则查询效率越高，因为选择性高的索引可以让MySQL在查找时过滤掉更多的行。唯一索引的选择性是1, 这是最好的索引选择性，性能也是最好的。

不重复索引值/记录总数接近0.031就可以使用了。

slelect count(DISTINT city) /count(*) from city_dome.
ALERT TABLE city ADD KEY (city(7)).

多列索引

单列索引会引起索引的合并，并不是最优策略。

选择合适的索引列顺序

  • 当不需要考虑排序和分组时，将选择性最高的列放在前面通常是很好的。这时候索引的作用只是用于优化WHERE条件的查找。 
slelect * from payment where staff_id=584 and (customer_id=30.

通过执行

slelect sum(customer_id=30),sum(staff_id=584) from payment

哪个列的基数小，需要把customer_id放在最前列。

前缀索引的条件值基数比正常值高的时候，索引基本没什么用。比如索引的列满足全表所有的行。

聚簇索引

聚簇索引不是一种单独的索引类型，而是一种数据存储方式。InnoDB的聚簇索引保存了B-Tree索引和数据行。

《高性能MYSQL》

覆盖索引

如果索引包含所需要查询字段的值，成为覆盖索引。

覆盖索引好处：

  • 索引条目通常远小于数据行大小， 所以如果只需要读取索引， 那MySQL就会极大地减少数据访问量。
  • 因为索引是按照列值顺序存储的（至少在单个页内是如此）， 所以对千1/0密集型的范围查询会比随机从磁盘读取每一行数据的I/O要少得多。
  • 由于InnoDB的聚簇索引， 覆盖索引对lnnoDB表特别有用。InnoDB的二级索引在叶子节点中保存了行的主键值，所以如果二级主键能够覆盖查询， 则可以避免对主键索引的二次查询。

覆盖索引缺点：

1、插入速度严重依赖于插入顺序，按照主键的顺序插入是加载数据页到InnoDB表中速度最快的方式。但是如果不按照主键顺序加载数据，那么在加载完成后最好使用 optimize table 命令重新组织一下表。

2、更新聚簇索引列的代价很高，因为会强制InnoDB将每个被更新的行移动到新的位置。插入的时候会面临页分裂的问题。页分裂会导致表占用更多的磁盘空间。

3、二级索引可能比想象的大，因为二级索引的叶子结点保存了引用行的主键

4、二级索引访问需要两次索引查找，要回表，对于innodb，自适应hash索引能够减少这样的重复工作。

二级索引查找行，需要找到叶子节点所对应的主键值，再去聚簇索引对应的值

使用了覆盖索引 EXPLAIN的Extra 列会显示Using index。

在Innodb中按主键顺序插入行

随机插入缺点：

  • 写入的目标页可能不在内存缓存区，那么插入记录的时候需要先从磁盘读取目标页到内存中。这会导致大量的随机IO.如果是顺序插入，由于是插入到上一个记录的后面，则大多数情况下（不需要开辟新页的情况）磁盘页是已经加载到内存了的。
  • 因为写入是乱序的，InnoDB可能需要不断的的做页分裂操作，以便为新的行分配空间。而页分裂会导致移动大量的数据，而且一次分裂至少要修改三个页而不是一个页。
  • 由于频繁的分页，页面会变得稀疏并被不规则的填充，最后会导致数据碎片。

顺序的主键什么时候会造成更坏的结果？

对于高并发工作负载，在InnoDB中按主键顺序插入可能会造成明显的争用。主键的上届会变成“热点”。因为所有的插入都发生在这里，所以并发插入可能导致间隙锁竞争。另一个热点可能是AUTO_INCREMENT锁机制；如果遇到这个问题，则可能需要考虑重新设计表或者应用，或者更改innodb_autoinc_lock_mode配置。如果你的服务器版本还不支持innodb_autoinc_lock_mode参数，可以升级到新版本的InnoDB，可能对这种场景会工作得更好。

假如索引覆盖了where 条件中的字段，但不是整个查询涉及的字段，还是会回表获取数据行。

select * from products where actor='SEAN' and title like  '%APOLLO%'

可以使用延迟关联解决：

SELECT
	*
FROM
	products
JOIN (
	SELECT
		product_id
	FROM
		products
	WHERE
		actor = 'SEAN'
	AND title LIKE '%APOLLO%'
) t1 ON t1.product_id = products.id

使用索引扫描来做排序

MySQL 有两种方式可以生成有序的结果：通过排序操作; 或者按索引顺序扫描注。 如果 EXPLAIN 出来的 type 列的值为 “index”, 则说明 MySQL 使用了索引扫描来做排序（不 要和 Extra 列的 “Using index” 搞混淆了）。

只有当索引的列顺序和ORDER BY子句的顺序完全一致， 井且所有列的排序方向（倒序或正序）都一样时，MySQL才能够使用索引来对结果做排序注。

如果查询需要关联多张表，则只有当ORDE R BY 子句引用的字段全部为第一个表时，才能使用索引做排序。ORDE R BY 子句和查找型查询的限制是一样的：需要满足索引的最左前缀的要求；否则，MySQL都需要执行排序操作，而无法利用索引排序。

压缩索引

MyISAM使用前缀压缩来减少索引的大小。

冗余和重复索引

重复索引是没有必要的。

冗余索引可以满足不同条件的查询。

冗余索引和重复索引有一些不同。 如果创建了索引(A, B)， 再创建索引 (A) 就是冗余索引，因为这只是前一个索引的前缀索引。 因此索引(A, B) 也可以当作索引 (A) 来使用（这种冗余只是对B-Tree索引来说的）。 但是如果再创建索引,(B, A), 则不是冗余索引，索引 (B)也不是，因为 B不是索引 (A, B) 的最左前缀列。 另外，其他不同类型的索引（例如哈希索引或者全文索引） 也不会是B-Tree索引的冗余索引，而无论覆盖的索引列是什么。

索引和锁

InnoDB 在二级索引上使用共享锁，主键索引使用排他锁。

查询性能优化

6.1 为什么查询会慢

如果把查询看做一个任务的话，它是由一系列子任务构成的。每个子任务执行都会消耗一定的时间。

如果要优化查询，实际上要优化其子任务，要么清除其中一些子任务，要么减少子任务的执行次数，要么让任务运行得更快。

6.2 慢查询基础：优化数据访问

  • 确认应用程序是否在检索大量超过需要的数据。这通常意味着访问了太多的行或列。
  • 确认MySQL服务器层是否在分析大量超过需要的数据行。

是否向数据库请求了不必要的数据

  • 查询不需要的记录

    一个常见的错误是常常会误以为MySQL会只返回需要的数据，实际上MySQL却是先返回全部结果集再进行计算。最简单的办法锁加上LIMIt。

  • 多表关联时返回全部列

    查询所有在电影Academy Dinosaur中出现的演员，千万不要按下面的写法编写查询。

  • 总是取出全部列

    每次看到SELECT *的时候都需要要用怀疑的眼光审视，是不是真的需要返回全部的列？很可能不是必须的。取出全部的列，会让优化器无法完成索引覆盖扫描这类优化，还会为服务器带来额外的I/O、内存和CPU消耗。

  • 重复查询相同的数据

    初次查询时将数据缓存，在需要时在缓存中取出来。

MySQL是否在扫描额外的记录

对于MySQL, 最简单的衡量查询开销的三个指标如下：

  • 响应时间
  • 扫描的行数
  • 返回的行数

响应时间

响应时间是两个部分之和：服务时间和排队时间。

扫描的行数和返回的行数

分析查询时，查看该查询扫描的行数是非常有帮助的。 这在一定程度上能够说明该查询找到需要的数据的效率高不高。

理想情况下扫描的行数和返回的行数应该是相同的。

扫描行数与访问类型

explain的type的显示了访问类型，速度从慢到快，分别是：

all(全表扫描)、index(索引扫描)、range(范围扫描)、ref(范围索引)、ref_eq(唯一性索引)、const(常数引用)。

6.3 重构查询的方式

一个复杂查询还是多个简单查询

设计查询的时候需要考虑是否需要将多个复杂查询拆分为多个简单查询。

切分查询

定时删除数据时，一次删除可能需要锁定很多数据，可以拆分多次执行。

** 分解关联查询**

6.4 查询执行的基础

MYSQL执行查询的过程：

《高性能MYSQL》

MySQL执行查询的过程：

（1）客户端发送一条查询给服务器

（2）服务器先检查查询缓存，如果命中了缓存，则立即返回存储在缓存中的结果，否则进入下一个阶段。

（3）服务器端进行SQL解析、预处理，再由优化器生成对应的执行计划

（4）将结果返回给客户端。

查询缓存

在解析一个查询语句之前，如果查询缓存是打开的，那会优先检测这个查询是否命中缓存中的数据。

查询优化处理

首先，mysql会将sql语句解析成语法树，然后验证语法是否错误。

语法树合法后会被优化器转成执行计划。

MySQL如何执行关联查询

mysql对于任何关联都执行嵌套循环关联操作，即先在一个表中循环取出单条数据，然后再嵌套循环到下一个表中寻找匹配的行，依次进行。

《高性能MYSQL》

6.7 优化特定类型的查询

优化count()查询

count()可以统计某个列的数值，也可以统计行数。

在统计列值是要求列是非空的(不统计NULL)。count(*) 统计行数。

//如果num > 0, 将num的值累加到total_num, 否则将0累加到total_num.
//如果num < 0, 将1累加到negative_num, 否则将0累加到negative_num.
SELECT
	sum(IF(num > 0, num, 0)) AS total_num,
	sum(IF(num < 0, 1, 0)) AS negative_num_count
FROM
	inventory_product
GROUP BY
	product_i


SELECT
	COUNT(color = 'blue' OR NULL) AS blue,
	COUNT(color = 'red' OR NULL) AS red
FROM
	items

SELECT
	sum(IF(color = 'blue', 1, 0)) AS blue,
	sum(IF(color = 'red', 1, 0)) AS red
FROM
	items
	
SELECT
	sum(color = 'blue') AS blue,
	sum(color = 'red') AS red
FROM
	items

优化limit分页

偏移量非常大的时候，例如LIMIT 10000,20可能需要查询100020条结果，然后返回20条。

可以使用书签记录上一次查询的位置，那么下次查询就会从书签位置开始扫描。

select * from user where id > 10000 limit 10000,20

复制

1.2 复制如何工作

MySQL复制数据的三个步骤：

1.在主库上把数据更改记录到二进制日志中（二进制日志事件）；

2.备库将主库上的日志复制到自己的中继日志中；

3.备库读取中继日志中的事件，将其重放到备库数据之上。

第一步就是在主库上记录二进制日志。在每个事务更新数据完成之前，主库将数据更新事件记录到二进制日志记录中。MySQL会按照事务的提交顺序，而非每条语句的执行顺序记录二进制日志。在记录二进制日志完成后，主库会告诉存储引擎提交事务。

下一步备库主库的二进制日志复制到其本地中继日志。首先，备库启动一个工作线程(I/O线程)。I/O线程跟主库上建立一个普通的连接，然后再主库启动一个二进制转储线程读取主库的二进制日志中的事件，如果已经跟上主库，它会睡眠并等待主库产生新的事件。I/O线程将这些事件写入中继日志。

SQL slave thread（SQL从线程）处理该过程的最后一步。SQL线程从中继日志读取事件，并重放其中的事件而更新slave的数据，使其与主库中的数据一致。只要该线程与I/O线程保持一致，中继日志通常会位于OS的缓存中，所以中继日志的开销很小。

阅读书籍 mysql 数据库
上一篇: 高并发解决方案（缓存、降级、限流）---读书笔记
下一篇: leetcode刷题笔记六十八题 文本左右对齐

继续阅读