高性能MYSQ

结构和历史

 

1. 隔离级别有四种：
   READ UNCOMMITTED（未提交读），同事务中某个语句的修改，即使没有提交，对其他事务也是可见的。这个也叫脏读。
   READ COMMITTED（提交读），另一个事务只能读到该事务已经提交的修改，是大多数据库默认的隔离级别。但是有下列问题，一个事务中两次读取同一个数据，由于这个数据可能被另一个事务提交了两次，所以会出现两次不同的结果，所以这个级别又叫做不可重复读。这里的不一样的数据包括虚读（两次结果不同）和幻读（出现新的或者缺少了某数据）。
   REPEATABLE READ（可重复读），这个级别不允许脏读和不可重复读，比如MYSQL中通过MVCC来实现解决幻读问题。
   SERIALIABLE（可串行化），这儿实现了读锁，级别最高。
2. 显示和隐式锁定：事务执行中，随时可以执行锁定，锁只有在COMMIT或ROLLBACK的时候才释放，而且所有的锁是同时释放的。这些锁定都是隐式锁定。也可以通过特定语句显式锁定，比如SELECT … LOCK IN SHARE MODE等。
3. MVCC（多版本并发控制）：通过保存数据在某个时间点的快照来实现。在INNODB中通过每行记录后保存两个隐藏的列，一个保存行的创建时间，一个保存行的过期（删除）时间，这儿的保存不是时间而是系统版本号，随着事务的数量增加而增加版本号。
   SELECT:只找版本号早于当前事务版本的数据，删除版本要大于当前版本号。
   INSERT：插入时保存当前版本号为行版本号。
   DELETE：为删除的每行保存当前版本号为行的删除标示。
   UPDATE：先为插入的行保存版本号，同时保存当前版本号为行删除标示。
4. INNODB通过MVCC来支持高并发，通过间隙锁来防止幻读。
5. MYISAM支持读取的时候插入（并发插入），支持延迟更新索引键（Delayed Key Write），先写内容最后才更新索引，需要指定DELAY_KEY_WRITE。

SCHEMA与数据类型优化

1. 避免使用NULL。
2. 整数类型中，TINYINT使用8位存储空间，BIGINT为64位，一般做SIMHASH选择64位做特征值应该是基于这个，转成16进制有16位。其中指定的宽度只在命令行中展示时起作用。
3. 实数类型中，DECIMAL用于存储精确的小数，比如货币。
4. VARCHAR比定长CHAR更省空间，因为它只需要使用必要的空间，但是其需要使用1或者2个额外字节用来记录字符串的长度。但是在update的时候，容易造成碎片。
   CHAR是定长的，MYSQL根据定义字符串的长度分配空间，而且其会删除所有末尾空格。比如存”STRING     “的时候，末尾的空格会被删除。
   VARCHAR(5)和VARCHAR(100)存同一个字符虽然空间开销相同，但是在存的时候会消耗更多内存，还有在使用临时表的时候也会比较糟糕。
5. BLOB和TEXT是为存储很大数据而设计的，分别以二进制和字符方式存储。TEXT是SMALLTEXT的同义词，BLOB也是。
6. ENUM类型存储是非常紧凑，其实际存储为整数。
7. BIT可以在一列中存储一个或多个0/1值，最大长度为64。问题是存进去是二进制，但是展示出来却是十进制的。
8. 计数器表的优化，对于单表的a+1操作可能受到锁的影响，可以通过创建100行数据，然后随机选取一行写，取的时候使用SUM(a)进行查询。
9. 高效ALTER TABLE，修改表结构涉及到不需要改变数据只要改frm文件的时候，可以使用语句ALTER COLUMN来操作。
   还有替换frm的高效方法，首先create table like来建立新表，修改新表结构，对旧表数据执行锁定”FLUSH TABLES WITH READ LOCK;”
   执行系统命令，mv new.frm a.frm之类，记得备份。
   UNLOCK TABLES;
10. 高效载入数据到MyISAM表，可以暂时禁用索引。
    ALTER TABLE tab DISABLE KEYS;
    ALTER TABLE tab ENABLE KEYS;
    但是DISABLE KEYS只对非唯一索引有效。

创建高性能的索引

1. B-Tree索引，其意味着所有的值都是按照顺序存储的，并且每一个叶子页到根的距离都相等。
   B-Tree对索引列是顺序存储的，所以很适合查找范围数据。
   缺点是必须按照索引从最左列开始查找，否则无法使用索引。
2. R-Tree（空间数据索引），MyISAM表支持空间索引，可以用作地理数据存储。
3. 独立的列无法使用索引，独立的列是指索引列为表达式的一部分或者函数的参数。
4. 前缀索引，索引很长的字符列会让索引变大变慢，所以选择一个合适的长度来索引是很有效率的。
   首先需要找出合适长度的前缀，用语句：
   select count(*) as cnt,LEFT(city,3) as pref from group by pref order by cnt;
   调整其中LEFT函数的值选择最合适的长度。建索引时如下：
   ALTER TABLE a ADD KEY(city(7))；
   还可以考虑后缀索引，比如查找某个域名的所有电子邮件地址，需要把字符串翻转后存储。
5. 多列索引的顺序非常重要，要选择最有效率的列放到最左边。
6. 聚族索引并不是一种单独的索引类型，而是一种数据存储的方式。
   当表有聚簇索引时，它的数据行实际上存放在索引的叶子页(LEAF PAGE)中，聚簇表示数据行和相邻的键值紧凑地存储在一起。
7. 当存在OR条件的时候，会看到此时使用了index_merge类型索引，这个说明表上的索引很糟糕，这个是由于在OR左右两个条件都建立了索引，应该修改索引，或者使用IGNORE INDEX来会略某些索引。
8. 在选择多列索引的时候，通常把选择性更大的放到前面(该条件下统计数量更小的)。
9. 在INNODB中最好使用自增作为主键，而使用UUID等随机的聚簇索引会对I/O密集型应用造成很坏性能，它使得聚簇索引的插入变得完全随机。
10. 当要查询的字段的值在索引中，就称该索引为覆盖索引。在explain的时候extra显示using index。为了能用到覆盖索引，可以使用延迟关联（deferred join）。书上有很巧妙的例子：）。注意的是，INNODB中二级索引的叶子节点都包含了主键的值，所以查询的值包含主键id时，主键id可以不在所建的联合索引中。关于延迟关联还有个经典例子，大偏移翻页的时候。
11. 当索引类型为index时，说明MYSQL使用了索引扫描来做排序。
12. 在5.1或更新版本中，INNODB在服务器端过滤掉行后就释放锁，而早期版本中则需要在事务提交后才释放锁。
13. EXPLAIN中出现Using where表示在存储引擎返回行后再使用where过滤条件。
14. 一个诀窍，一个符合查询条件的多列索引中，有时候条件里没有包含存在的索引列，这时候使用IN来满足最左前缀。比如多列索引中有sex列，但是用户查询时没有选择sex，则使用IN(‘M’,’F’)来满足使用索引的条件。
    某一些条件比如age，一般是范围查询，而根据最左前缀碰到范围查询后会终止，所以这类一般放在多列索引的最后面。
    而使用开始的IN语句满足最左前缀也不能滥用，3个IN条件，每 个有N个枚举值，则会产生N*N*N中组合，降低效率。
15. 按顺序访问范围数据很快，因为顺序I/O不需要多次磁盘寻道，不需要额外排序操作。
16. 聚簇索引(Clustered Index)，一个索引项直接对应实际数据记录存储页。
    索引项和实际数据行的排序完全一样。
    一个表只能有一个聚簇索引。但是该列能包含多个列，就像电话簿使用姓氏和名字同时进行排序。
17. INNODB支持聚簇索引，其中聚簇索引就是表，必须要像MYISAM那样的行存储。聚簇索引的每个叶子节点都包含了主键值、事务ID、用于事务和MVCC的回滚指针以及所有的剩余列。
    InnoDB的二级索引和聚簇索引很不相同。InnoDB二级索引的叶子节点中存储的不是“行指针”，而是主键值，并以此作为指向行的“指针”。
    在INNODB主键中插入UUID，由于主键会保持有序，会严重影响性能。

查询性能优化

1. 检查响应时间，扫描的行和返回的行，扫描的行数和访问类型（Explain的时候）是三个简单衡量查询的指标。
2. 在进行大查询的时候使用分而治之，比如delete大数据的时候使用limit，使用do while分解操作，避免大语句锁住过多数据，占满事务日志，耗尽系统资源，阻塞很多重要查询。
3. 关联查询拆成简单查询然后在应用层聚合数据，可以让缓存效率更高，单个查询可以减少锁竞争，本身查询效率也更高，在数据库中做关联查询还可能导致需要重复地访问一部分数据。
4. mysql客户端和服务器之间的通信协议是半双工，任何一个时刻只能单向发送数据而不能两边同时进行，像是抛绣球。所以mysql通常需要等所有数据都已经发送给客户端后才能释放这条查询锁占用的资源，这时max_allowed_packet很重要。
5. 一个完整查询包含如下过程包括客户端/服务器端通信->查询缓存->语法解析器和预处理->查询优化器->数据和索引的统计信息->查询执行引擎->返回结果给客户端。下面会一次说说每个步骤。
   
6. 查询状态，一个连接或者线程，在任何时刻都有一个状态。
   sleep，线程正在等待客户端发来新请求。
   query，线程正在执行查询或者将结果发送给客户端。
   locked，该线程正在等待表锁。而存储引擎级别的锁比如innodb的行锁并不会体现在线程状态。
   copying to tmp table[on disk]，线程正在执行查询并且将结果集都复制到一张临时表，一般是group by或者文件排序等操作。on disk表示正在将一个内存临时表放到磁盘上。
   sorting result，线程正在对结果集进行排序。
   sending data，线程可能在多个状态间传送数据，或者正在生成结果集或者正在向客户端返回数据。
   了解这些状态可以很快了解谁正在掷球。
7. 在查询缓存后，先进行语法解析器和预处理，mysql通过关键字将SQL语句进行解析并生成一颗对应的解析树，进行语法规则验证。当语法树被认为合法了，则由优化器将其转化为执行计划，一条语句可能有很多执行方式并返回相同结果，优化器的作用就是找到这其中最好的执行计划。优化器是基于成本来预测。
8. 在很多数据库中IN等同OR，但是在mysql中，会把IN中的数据先进行排序，然后通过二分查找的方式来确定列表中的值是否满足条件，这是一个O(log n)的操作。当IN中有大量数据的时候效率会更快。
9. 关联查询，MySQL认为任何一次查询都是一次关联，不仅仅是UNION，子查询等都可能是。对于UNION，MYSQL现将一系列查询的单个查询结果放到一个临时表中，再重新读出临时表的数据来完成UNION查询。
   MYSQL对任何关联都执行嵌套循环关联操作，即先在一个表中循环取出单条数据，然后嵌套循环到下一个表中寻找匹配的行，依次下去，直到找到所有表中匹配的行为为止。
   当在FROM子句中遇到子查询时，先执行子查询并将其结果放到一个临时表中，然后将这个临时表当做一个普通表对待（派生表）。
10. 执行计划，MYSQL生成查询的一颗指令数。可以使用EXPLAIN EXTENDED后再使用WARNINGS。
    任何多表查询都可以用一棵树来表示，比如四表查询：
    
    而事实上MYSQL总是从一个表开始一直嵌套循环，是一颗左侧深度优先的树。
    
11. 关联查询优化器，用来决定多个表关联时的顺序。
    由于其第二个是从第一个表的结果嵌套循环的，所以第一个表尤为重要，优化器会选择扫描很少的行。
    如果有超过N各表的关联，那么需要检查N的阶乘种关联顺序，这是很糟糕的。
12. 排序优化，如果order by子句中所有列来自第一个表，那么在处理第一个表是就会进行文件排序，会显示Using filesort
    其余情况，都需要先把关联结果放到一个临时表中，最后再进行文件排序。会显示Using filesort，Using temporary;
13. 关联子查询是一个烂查询。select from IN(select id from where)
    因为优化器会改写成select from where exists(select * from where AND a.id=b.id)
    所以子句需要主句的id才能查询，这样MYSQL会首先对主句进行全表扫描来找出所有id，然后使用id对子句进行逐个执行子查询。
    所以方法一是：select * from a
    inner join b USING(id)
    where c=1;
    还有个方法，GROUP_CONCAT。
14. MYSQL目前不支持松散索引扫描，就是最左缀的一个原则，联合索引没法越过左边的字段。虽然其联合索引全部有序。
15. 在同一个表中进行查询和更新，不能用update set a=(select from),要使用生成表的方式来绕过限制，update tb inner join(select) as b using(id) set tb.a=b.a,这样会关联到一个临时表。
16. HINT查询优化器的提示，
    HIGH_PRIORITY和LOW_PRIORITY，优先级设置。HIGH一般用于select语句，会将其放到队列的最前面。LOW会让语句一直处于等待状态，只要队列中还有需要访问同一个表的语句。这两个提示只在表锁的存储引擎中有效。
    DELAYED，对insert和replace有效，会将提示立即返回给客户端，并将插入的行数放到缓冲区，然后在空闲时批量写入。会导致LAST_INSERT_ID()失效。
    FOR UPDATE和LOCK IN SHARE MODE，主要控制SELECT语句的锁机制，只对实现了行级锁的存储引擎有效。
17. count(*)中的*不是指所有的列，而是会忽略所有的列而直接统计行数。
    统计同一个列里不同颜色的商品语句，select sum(if(color=’blue’, 1, 0)) as blue,sum(if(color=’red’, 1, 0)) as red from items;
    其中也可以写成sum(color=’blue’),sum(color=’red’)
    或者使用如下语句
    select count(color=’blue’ OR NULL) as blue, count(color=’red’ OR NULL) as red from items;
18. 关于大数据偏移的一个常用方法是延迟关联，
    select from a inner join(select id from limit 100,10) as b using(id);
    或者计算出具体位置使用between，
    OFFSET会导致MYSQL扫描大量不需要的行并抛弃，避免使用OFFSET，比如计算出主键的id，使用
    select from a where id<2131231 order by id desc limit 20;
19. MYSQL总是通过创建并填充临时表的方式来执行UNION查询。

MySQL高级特性

1. 分区表：是一个独立的逻辑表，底层是由多个物理子表组成。索引也是按照分区的字表定义的，没有全局索引。
   在创建的时候使用PARTITION BY子句定义每个分区存放的数据。在执行查询的时候优化器会根据分区定义过滤那些没有我们需要数据的分区，这样查询就无需扫描所有分区。
   CREATE TABLE sales(date DATETIME NOT NULL,…) ENGINE=InnoDB PARTITION BY RANGE(YEAR(date))(
   PARTITION p_2010 VALUES LESS THAN (2010),
   PARTITION p_2011 VALUES LESS THAN (2011),
   PARTITION p_catchall VALUES LESS THAN MAXVALUE);
2. 查询缓存可以更快得返回数据，但是高并发下可能成为整个服务器的资源竞争点，多核服务器上还可能导致服务器僵死。所以很多时候我们认为应该默认关闭查询缓存。
   在判断缓存是否命中的时候，MySQL不会解析格式化查询语句，而是直接使用SQL语句和一些客户端的信息，比如空格，注释等任何的不同都会导致缓存的不命中。
   当语句中存在不确定数据比如NOW()等函数时都不会被缓存。
   当打开查询缓存时，每个读查询都会先检查是否命中缓存，写入缓存的时候会带来系统消耗，当某个表有变动，则对应表的所有缓存都将设置失效，当查询缓存太大或者碎片太多，都会带来很大系统消耗。
   所以使用查询缓存时要小心，不要设置太大内存，而且要在确定有明确收益的时候才使用查询缓存。

优化服务器设置

 

操作系统和硬件优化

1. 存储引擎通常把数据和索引都保存在一个大文件中，这意味着RAID（磁盘冗余阵列）存储大量数据通常是最可行的方法。
   RAID 0
   这是成本最低和性能最高的RAID配置，因为其没有冗余，建议只在不担心数据丢失的情况下使用。而且其的损坏概率比单块磁盘还要高。
   RAID 1
   提供不错的读性能，而且在不同磁盘间冗余数据，所以有很好的冗余性。
   RAID 5
   通过分布奇偶校验把数据分布到多个磁盘，这样任何一个盘的数据失效，都可以从奇偶校验中重建。但是如果有两块磁盘失效了，则整个卷数据都无法恢复。就每个存储单元的成本而言，这是最经济的，因为只额外消耗了一块磁盘的存储空间。
   RAID 10
   他由分片的镜像组成，所以对读和写都有良好的扩展性。
   RAID 15
   由条带化的RAID5组成。用于存放非常庞大的数据集。
2. vmstat和iostat

 

复制

1. 复制解决的基本问题是让一台服务器的数据与其他服务器保持同步。
2. MYSQL支持两种复制方式：基于行的复制和基于语句的复制。基于语句的复制也叫逻辑复制，很早就存在。基于行的复制5.1版才加进来。这两种方式都是通过在主库记录二进制日志，在备库重放日志的方式来实现异步的数据复制。这意味着同一时间点备库上的数据可能和主库存在不一致。
3. 复制通常不会增加主库的开销，主要是启用二进制日志带来的开销。

可扩展的MySQL

1. 向上扩展：购买更多性能强悍的硬件。
2. 向外扩展，分三部分：复制，拆分以及数据分片。
3. 复制可分为按功能拆分，比如分为论坛、新闻等。
   数据分片，全局数据存储在单点上，而对同功能下的数据增长过多的时候进行分片。比如对用户表进行分片。
4. 生成全局唯一的ID
   比如分片后，使用AUTO_INCREMENT来获取唯一ID会有问题。
   首先可以使用auto_increment_increment和auto_increment_offset两个变量来让MYSQL以期望的值和偏移量增加自增列的值。比如两台服务器，可以配置两台服务器的自增幅度为2，其中一台偏移为1一台为2.所以其中一台总是奇数一台总是偶数。
   还可以在一个全局数据库中创建自增来生成唯一数字。
   还能从全局节点中请求一批数字，用完再申请。
   还可以使用分片号和自增的组合来作为唯一ID。
   可以使用UUID()来生成全局唯一值。因为其值很大且不连续，因此不适合做innodb的主键。这时可以考虑UUID_SHORT()，能生成连续的值，并使用64位代替128位。
5. 通过多实例扩展。
6. 通过集群扩展。
7. 向内扩展，比如对不需要的数据进行归档和清理。

高可用性

云端的MySQL

应用层优化

备份与恢复

1. 热备份指备份不需要任何的停机服务。
2. 逻辑备份指导出，物理备份指复制原始文件。
3. 差异备份是对自上次全备份后所有改变的部分而做的备份，增量备份则是字从任何类型的上次备份后所有的修改做的备份。
4. 生成逻辑备份:
   mysqldump test t1
   select into outfile以符号分割文件格式创建数据的逻辑备份。
5. 文件系统快照。
   快照会在/dev目录下创建一个新的逻辑卷，可以像挂载其他设备一样挂载它。
6. 备份脚本化

 

 

发表评论