MySQL高级使用和特性


MySQL体系结构

什么是体系结构?

MySQL部件包括哪些?

各个部件之间的执行顺序是什么?


MySQL存储引擎

MySQL常见的存储引擎有哪些?

MyISAM和InnoDB有什么不同?如何选择存储引擎?

MySQL存储引擎MyISAM与InnoDB的优劣

× MyISAM InnoDB
存储结构 每张表被存放在三个文件:frm-表格定义MYD(MYData)-数据文件MYI(MYIndex)-索引文件 所有的表都保存在同一个数据文件中(也可能是多个文件,或者是独立的表空间文件),InnoDB表的大小只受限于操作系统文件的大小,一般为2GB
存储空间 MyISAM可被压缩,存储空间较小 InnoDB的表需要更多的内存和存储,它会在主内存中建立其专用的缓冲池用于高速缓冲数据和索引
可移植性、备份及恢复 由于MyISAM的数据是以文件的形式存储,所以在跨平台的数据转移中会很方便。在备份和恢复时可单独针对某个表进行操作 免费的方案可以是拷贝数据文件、备份 binlog,或者用 mysqldump,在数据量达到几十G的时候就相对痛苦了
事务安全 不支持 每次查询具有原子性 支持 具有事务(commit)、回滚(rollback)和崩溃修复能力(crash recovery capabilities)的事务安全(transaction-safe (ACID compliant))型表
AUTO_INCREMENT MyISAM表可以和其他字段一起建立联合索引 InnoDB中必须包含只有该字段的索引
SELECT MyISAM更优  
INSERT   InnoDB更优
UPDATE   InnoDB更优
DELETE   InnoDB更优 它不会重新建立表,而是一行一行的删除
COUNT without WHERE MyISAM更优。因为MyISAM保存了表的具体行数 InnoDB没有保存表的具体行数,需要逐行扫描统计,就很慢了
COUNT with WHERE 一样 一样,InnoDB也会锁表
只支持表锁 支持表锁、行锁 行锁大幅度提高了多用户并发操作的新能。但是InnoDB的行锁,只是在WHERE的主键是有效的,非主键的WHERE都会锁全表的
外键 不支持 支持
FULLTEXT全文索引 支持 不支持 可以通过使用Sphinx从InnoDB中获得全文索引,会慢一点

InnoDB 还是 MyISAM?

如果你需要事务处理或是外键,那么InnoDB 可能是比较好的方式。如果你需要全文索引,那么通常来说 MyISAM是好的选择,因为这是系统内建的,然而,我们其实并不会经常地去测试两百万行记录。所以,就算是慢一点,我们可以通过使用Sphinx从InnoDB中获得全文索引。

数据的大小,是一个影响你选择什么样存储引擎的重要因素,大尺寸的数据集趋向于选择InnoDB方式,因为其支持事务处理和故障恢复。数据库的在小决定了故障恢复的时间长短,InnoDB可以利用事务日志进行数据恢复,这会比较快。而MyISAM可能会需要几个小时甚至几天来干这些事,InnoDB只需要几分钟。

操作数据库表的习惯可能也会是一个对性能影响很大的因素。比如: COUNT() 在 MyISAM 表中会非常快,而在InnoDB 表下可能会很痛苦。而主键查询则在InnoDB下会相当相当的快,但需要小心的是如果我们的主键太长了也会导致性能问题。大批的inserts 语句在MyISAM下会快一些,但是updates 在InnoDB 下会更快一些——尤其在并发量大的时候。

根据经验来看,如果是一些小型的应用或项目,那么MyISAM 也许会更适合。当然,在大型的环境下使用MyISAM 也会有很大成功的时候,但却不总是这样的。如果你正在计划使用一个超大数据量的项目,而且需要事务处理或外键支持,那么你真的应该直接使用InnoDB方式。但需要记住InnoDB 的表需要更多的内存和存储,转换100GB 的MyISAM 表到InnoDB 表可能会让你有非常坏的体验。

存储时间类型字段用int还是datetime类型?为什么?

int

  1. 占用4个字节
  2. 建立索引之后,查询速度快
  3. 条件范围搜索可以使用使用between
  4. 不能使用mysql提供的时间函数

结论:适合需要进行大量时间范围查询的数据表

datetime

  1. 占用8个字节
  2. 允许为空值,可以自定义值,系统不会自动修改其值。
  3. 实际格式储存(Just stores what you have stored and retrieves the same thing which you have stored.)
  4. 与时区无关(It has nothing to deal with the TIMEZONE and Conversion.)
  5. 不可以设定默认值,所以在不允许为空值的情况下,必须手动指定datetime字段的值才可以成功插入数据。
  6. 可以在指定datetime字段的值的时候使用now()变量来自动插入系统的当前时间。

结论:datetime类型适合用来记录数据的原始的创建时间,因为无论你怎么更改记录中其他字段的值,datetime字段的值都不会改变,除非你手动更改它。

timestamp

  1. 占用4个字节
  2. 允许为空值,但是不可以自定义值,所以为空值时没有任何意义。
  3. TIMESTAMP值不能早于1970或晚于2037。这说明一个日期,例如’1968-01-01’,虽然对于DATETIME或DATE值是有效的,但对于TIMESTAMP值却无效,如果分配给这样一个对象将被转换为0。 4.值以UTC格式保存( it stores the number of milliseconds) 5.时区转化 ,存储时对当前的时区进行转换,检索时再转换回当前的时区。
  4. 默认值为CURRENT_TIMESTAMP(),其实也就是当前的系统时间。
  5. 数据库会自动修改其值,所以在插入记录时不需要指定timestamp字段的名称和timestamp字段的值,你只需要在设计表的时候添加一个timestamp字段即可,插入后该字段的值会自动变为当前系统时间。
  6. 以后任何时间修改表中的记录时,对应记录的timestamp值会自动被更新为当前的系统时间。

结论:timestamp类型适合用来记录数据的最后修改时间,因为只要你更改了记录中其他字段的值,timestamp字段的值都会被自动更新。

如果是字符串类型,字段选char好还是选varchar好?为什么?

CHAR和VARCHAR类型声明的长度表示你想要保存的最大字符数。

枚举类型应用在什么场景下?有什么缺陷?

布尔类型

MySQL数据库产品没有真正实现对布尔类型的支持,建议大家不要使用MySQL布尔类型BOOL或布尔类型BOOLEAN,而是使用数据库类型微整型TINYINT替代。

枚举类型

缺点:

MySQL数据库枚举类型是一种有应用场景广泛的数据类型,若是抛开网站程序或软件版本发布,可能会导致开发工程师与数据库维护人员之间没有配合好的问题,非常推荐大家把枚举类型引入到生产环境的数据库应用中,对企业而言也可以起到节省人力、物理等成本。建议大家使用枚举类型的时候,尽量把可能需要用到的枚举元素,都写到MySQL数据库表字段的定义属性中,减少出现漏做DDL变更的故障。

集合类型

缺点:

人才招聘网站的用户,设置工作意向城市一项,则往往会选择1-3个城市,甚至更多城市,采用集合类型字段作为数据存储结果的话,将可以大量简化程序复杂度,以及大规模降低数据存储的容量,唯一的遗憾则是集合元素限制为64个,会导致无法满足招聘网站后期业务发展需要。

表锁和行锁的优缺点?

MySQL有三种锁的级别:页级、表级、行级。

MySQL的表级锁有两种模式:表共享读锁(Table Read Lock)和表独占写锁(Table Write Lock)。MyISAM在执行查询语句(SELECT)前,会自动给涉及的所有表加读锁,在执行更新操作(UPDATE、DELETE、INSERT等)前,会自动给涉及的表加写锁。 所以对MyISAM表进行操作,会有以下情况:

原则上数据表有一个读锁时,其它进程无法对此表进行更新操作,但在一定条件下,MyISAM表也支持读取和插入操作的并发进行。MyISAM存储引擎有一个系统变量concurrent_insert,专门用以控制其并发插入的行为,其值分别可以为0、1或2

由于MySQL认为写请求一般比读请求要重要,所以如果有读写请求同时进行的话,MYSQL将会优先执行写操作。这样MyISAM表在进行大量的更新操作时(特别是更新的字段中存在索引的情况下),会造成查询操作很难获得读锁,从而导致查询阻塞。

当数据库的表有锁的时候,此时有插入数据的操作,是直接插入还是等待解锁后插入?

事务一般应用在哪些场景下?

PHP如何调用MySQL的事务?

如果事务出现脏读或者幻读,如何解决?

MySQL InnoDB事务的隔离级别有四级,默认是“可重复读”(REPEATABLE READ)。

四个级别逐渐增强,每个级别解决一个问题。


MySQL驱动与API

MySQL的驱动是php_mysql.so或php_mysql.dll吗?

如何选择一个驱动,后来又想换一个驱动怎么办?

可以通过ORM 工具做到,Propel 和 Doctrine。或者数据库框架,Medoo

PHP的MySQL API有几种?他们各有什么优缺点?

The PHP MySQL extensions are lightweight wrappers on top of a C client library. There are 3 PHP MySQL extensions:

~ PDO MySQLi
Database support 12 different drivers MySQL only
API OOP OOP + procedural
Connection Easy Easy
Named parameters Yes No
Object mapping Yes Yes
Prepared statements (client side) Yes No
Performance Fast Fast
Stored procedures Yes Yes

普通的mysql连接肯定是会被抛弃的 因为每次都要防止sql注入的问题 而且相对来说比较慢
mysqli是在普通mysql的基础上做的一次优化 说实话 很成功 预处理方式完全解决了sql注入的问题,但是唯一的不足点 就是只支持mysql数据库 当然 如果你要是不操作其他的数据库或者 当然这无疑是最好的选择
PDO则是最新出来的一种 连接方式 兼容大部分数据库 也解决了sql注入 但是也有缺点 它只支持php5以上的版本 不过听说在未来的php6中 只支持这种连接

如果有多条SQL语句要一起执行,怎么办?

如何防止SQL语句注入?


MySQL索引

索引有哪些?

如何选择索引?

怎么创建全文索引?

InnoDB引擎上可以创建全文索引吗?

MySQL 5.6.4里才添加了InnoDB引擎的Full-Text索引支持。
推荐用Lucene(ElasticSearch比较容易部署)或Sphinx这样的第三方解决方案。

什么是最左前缀原则?应用在什么索引上?

最左前缀匹配原则,非常重要的原则,mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配,比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引,d是用不到索引的,如果建立(a,b,d,c)的索引则都可以用到,a,b,d的顺序可以任意调整。

索引是怎么实现的?

索引(Index)是帮助MySQL高效获取数据的数据结构。

目前大部分数据库系统及文件系统都采用B-Tree或其变种B+Tree作为索引结构


SQL语句优化

SQL语句执行的逻辑顺序是怎样的?

如何判断SQL语句执行的效率

explain输出解释

+----+-------------+-------+-------+-------------------+---------+---------+-------+------+-------+
| id | select_type | table | type  | possible_keys     | key     | key_len | ref   | rows | Extra |
+----+-------------+-------+-------+-------------------+---------+---------+-------+------+-------+
Column Meaning
id The SELECT identifier
select_type The SELECT type
table The table for the output row
type The join type
possible_keys The possible indexes to choose
key The index actually chosen
key_len The length of the chosen key
ref The columns compared to the index
rows Estimate of rows to be examined
Extra Additional information
  1. id select标识,SQL从大到小的执行.
  2. select_type 就是select类型,可以有以下几种
    • SIMPLE 简单SELECT(不使用UNION或子查询)
    • PRIMARY 最外面的
    • UNION UNION中的第二个或后面的SELECT语句
    • DEPENDENT UNION UNION中的第二个或后面的SELECT语句,取决于外面的查询
    • UNION RESULT UNION的结果
    • SUBQUERY 子查询中的第一个
    • DEPENDENT SUBQUERY 子查询中的第一个SELECT,取决于外面的查询
    • DERIVED 导出表的SELECT(FROM子句的子查询)
  3. talbe 表明这一行涉及了哪张表,也可能是一下的值:
    • <unionM,N>: The row refers to the union of the rows with id values of M and N.
    • <derivedN>: The row refers to the derived table result for the row with an id value of N. A derived table may result, for example, from a subquery in the FROM clause.
  4. type 联接类型。下面给出各种联接类型,按照从最佳类型到最坏类型进行排序:
    • system 表仅有一行(=系统表)。这是const联接类型的一个特例。
    • const 表最多有一个匹配行,它将在查询开始时被读取。因为仅有一行,在这行的列值可被优化器剩余部分认为是常数。const表很快,因为它们只读取一次! const用于用常数值比较PRIMARY KEY或UNIQUE索引的所有部分时。
    • eq_ref 对于每个来自于前面的表的行组合,从该表中读取一行。这可能是最好的联接类型,除了const类型。它用在一个索引的所有部分被联接使用并且索引是UNIQUE或PRIMARY KEY。 eq_ref可以用于使用= 操作符比较的带索引的列。比较值可以为常量或一个使用在该表前面所读取的表的列的表达式。
    • ref 对于每个来自于前面的表的行组合,所有有匹配索引值的行将从这张表中读取。如果联接只使用键的最左边的前缀,或如果键不是UNIQUE或PRIMARY KEY(换句话说,如果联接不能基于关键字选择单个行的话),则使用ref。如果使用的键仅仅匹配少量行,该联接类型是不错的。ref可以用于使用=或<=>操作符的带索引的列。
    • ref_or_null 该联接类型如同ref,但是添加了MySQL可以专门搜索包含NULL值的行。在解决子查询中经常使用该联接类型的优化。
    • index_merge 该联接类型表示使用了索引合并优化方法。在这种情况下,key列包含了使用的索引的清单,key_len包含了使用的索引的最长的关键元素。
    • unique_subquery 该类型替换了下面形式的IN子查询的ref: value IN (SELECT primary_key FROM single_table WHERE some_expr) unique_subquery是一个索引查找函数,可以完全替换子查询,效率更高。
    • index_subquery 该联接类型类似于unique_subquery。可以替换IN子查询,但只适合下列形式的子查询中的非唯一索引:value IN (SELECT key_column FROM single_table WHERE some_expr)
    • range 只检索给定范围的行,使用一个索引来选择行。key列显示使用了哪个索引。key_len包含所使用索引的最长关键元素。在该类型中ref列为NULL。当使用=、<>、>、>=、<、<=、IS NULL、<=>、BETWEEN或者IN操作符,用常量比较关键字列时,可以使用range:SELECT * FROM tbl_name WHERE key_column = 10;SELECT * FROM tbl_name WHERE key_column BETWEEN 10 and 20; SELECT * FROM tbl_name WHERE key_column IN (10,20,30); SELECT * FROM tbl_name WHERE key_part1= 10 AND key_part2 IN (10,20,30);
    • index 该联接类型与ALL相同,除了只有索引树被扫描。这通常比ALL快,因为索引文件通常比数据文件小。当查询只使用作为单索引一部分的列时,MySQL可以使用该联接类型。
    • ALL 对于每个来自于先前的表的行组合,进行完整的表扫描。如果表是第一个没标记const的表,这通常不好,并且通常在它情况下很差。通常可以增加更多的索引而不要使用ALL,使得行能基于前面的表中的常数值或列值被检索出。
  5. possible_keys列指出MySQL能使用哪个索引在该表中找到行。注意,该列完全独立于EXPLAIN输出所示的表的次序。这意味着在possible_keys中的某些键实际上不能按生成的表次序使用。
  6. key列显示MySQL实际决定使用的键(索引)。如果没有选择索引,键是NULL。要想强制MySQL使用或忽视possible_keys列中的索引,在查询中使用FORCE INDEX、USE INDEX或者IGNORE INDEX。对于MyISAM和BDB表,运行ANALYZE TABLE可以帮助优化器选择更好的索引。对于MyISAM表,可以使用myisamchk –analyze。
  7. key_len列显示MySQL决定使用的键长度。如果键是NULL,则长度为NULL。注意通过key_len值我们可以确定MySQL将实际使用一个多部关键字的几个部分。
  8. ref列显示使用哪个列或常数与key一起从表中选择行。
  9. rows列显示MySQL认为它执行查询时必须检查的行数。
  10. Extra 列包含MySQL解决查询的详细信息。下面解释了该列可以显示的不同的文本字符串:
    • Distinct MySQL发现第1个匹配行后,停止为当前的行组合搜索更多的行。
    • Not exists MySQL能够对查询进行LEFT JOIN优化,发现1个匹配LEFT JOIN标准的行后,不再为前面的的行组合在该表内检查更多的行。
    • range checked for each record (index map: #) MySQL没有发现好的可以使用的索引,但发现如果来自前面的表的列值已知,可能部分索引可以使用。对前面的表的每个行组合,MySQL检查是否可以使用range或index_merge访问方法来索取行
    • Using filesort MySQL需要额外的一次传递,以找出如何按排序顺序检索行。通过根据联接类型浏览所有行并为所有匹配WHERE子句的行保存排序关键字和行的指针来完成排序。然后关键字被排序,并按排序顺序检索行。
    • Using index 从只使用索引树中的信息而不需要进一步搜索读取实际的行来检索表中的列信息。当查询只使用作为单一索引一部分的列时,可以使用该策略。
    • Using temporary 为了解决查询,MySQL需要创建一个临时表来容纳结果。典型情况如查询包含可以按不同情况列出列的GROUP BY和ORDER BY子句时。
    • Using where WHERE子句用于限制哪一个行匹配下一个表或发送到客户。除非你专门从表中索取或检查所有行,如果Extra值不为Using where并且表联接类型为ALL或index,查询可能会有一些错误。
    • Using sort_union(…), Using union(…), Using intersect(…)这些函数说明如何为index_merge联接类型合并索引扫描。
    • Using index for group-by 类似于访问表的Using index方式,Using index for group-by表示MySQL发现了一个索引,可以用来查询GROUP BY或DISTINCT查询的所有列,而不要额外搜索硬盘访问实际的表。并且,按最有效的方式使用索引,以便对于每个组,只读取少量索引条目。

SQL语句优化的原则是什么?

  1. 减少 IO 次数 IO永远是数据库最容易瓶颈的地方,这是由数据库的职责所决定的,大部分数据库操作中超过90%的时间都是 IO 操作所占用的,减少 IO 次数是 SQL 优化中需要第一优先考虑,当然,也是收效最明显的优化手段。
  2. 降低 CPU 计算 除了 IO 瓶颈之外,SQL优化中需要考虑的就是 CPU 运算量的优化了。order by, group by,distinct … 都是消耗 CPU 的大户(这些操作基本上都是 CPU 处理内存中的数据比较运算)。当我们的 IO 优化做到一定阶段之后,降低 CPU 计算也就成为了我们 SQL 优化的重要目标

select * 的方式到底好不好?为什么?

从数据库里读出越多的数据,那么查询就会变得越慢。并且,如果你的数据库服务器和WEB服务器是两台独立的服务器的话,这还会增加网络传输的负载。

当一条SQL语句要选择出所有的男性成员的时候怎么才能快速的差出来?

性别字段加索引

limit 10000,10这样的语句查询起来可能会很慢,该怎么优化才能快速的差出来呢?

  1. 对于数据量较大数据表,可以建立主键和索引字段建立索引表,通过索引表查询相应的主键,在通过主键查询数据量的数据表;
  2. 如果对于有where 条件,又想走索引用limit的,必须设计一个索引,将where 放第一位,limit用到的主键放第2位,而且只能select 主键!这样能提高读取速度
  3. 利用in:先通过where条件取得相应的主键值,然后利用主键值查询相应的字段值。

估计查询性能

在大多数情况下,可以通过计算磁盘搜索来估计性能。对小的表,通常能在1次磁盘搜索中找到行(因为索引可能被缓存)。对更大的表,可以使用B-树索引进行估计,将需要log(row_count)/log(index_block_length/3 * 2/(index_length + data_pointer_length))+1次搜索才能找到行。 在MySQL中,索引块通常是1024个字节,数据指针通常是4个字节,这对于有一个长度为3(中等整数)的索引的500,000行的表,通过公式可以计算出log(500,000)/log(1024/3*2/(3+4))+1= 4次搜索。 上面的索引需要大约500,000 * 7 * 3/2 = 5.2MB,(假设典型情况下索引缓存区填充率为2/3),可以将大部分索引保存在内存中,仅需要1-2调用从OS读数据来找出行。 然而对于写,将需要4次搜索请求(如上)来找到在哪儿存放新索引,并且通常需要2次搜索来更新这个索引并且写入行。 注意,上述讨论并不意味着应用程序的性能将缓慢地以logN 退化!当表格变得更大时,所有内容缓存到OS或SQL服务器后,将仅仅或多或少地更慢。在数据变得太大不能缓存后,将逐渐变得更慢,直到应用程序只能进行磁盘搜索(以logN增加)。为了避免这个问题,随数据增加而增加 键高速缓冲区大小。对于MyISAM表, 由key_buffer_size系统变量控制 键高速缓冲区大小


MySQL分表

垂直拆分和水平拆分分别应用到什么场景下?

垂直切分的最大特点就是规则简单,实施也更为方便,尤其适合各业务之间的耦合度非常低,相互影响很小,业务逻辑非常清晰的系统。在这种系统中,可以很容易做到将不同业务模块所使用的表分拆到不同的数据库中。根据不同的表来进行拆分,对应用程序的影响也更小,拆分规则也会比较简单清晰。(这也就是所谓的”share nothing”)。

垂直切分后,需要对shard内表格的数据量和增速进一步分析,以确定是否需要进行水平切分。

如何设置水平拆分的hash算法?

  1. Range拆分 sharding key按连续区间段路由,一般用在有严格自增ID需求的场景上,如Userid, Userid Range的小例子:以userid 3000W 为Range进行拆分 1号cluster userid 1-3000W 2号cluster userid 3001W-6000W
  2. List拆分 List拆分与Range拆分思路一样,都是通过给不同的sharding key来路由到不同的cluster,但是具体方法有些不同,List主要用来做sharding key不是连续区间的序列落到一个cluster的情况
  3. Hash拆分 通过对sharding key 进行哈希的方式来进行拆分,常用的哈希方法有除余,字符串哈希等等,除余如按userid%n 的值来决定数据读写哪个cluster,其他哈希类算法这里就不细展开讲了。

设置水平拆分的原则是什么?

数据库表水平拆分是非常复杂的,需要综合各个方面考虑完善,套用网友cauherk的说法“系统的切分是个很复杂的技术活,要综合考虑,而不仅仅从数据库层面考虑。业务的使用、分库的原则、数据的割接、开发的侵入、可操作的易难程度、后期的管理等等都是需要考虑的因素。”

设置垂直拆分的原则是什么?

垂直切分的依据原则是:将业务紧密,表间关联密切的表划分在一起,例如同一模块的表。


MySQL读写分离

设置数据库读写分离的原因是什么?

1.增加冗余 2.增加了机器的处理能力(硬件资源增加了) 3.对于读操作为主的应用,使用读写分离是最好的场景…..因为可以确保写的服务器压力更小….而读又可以接受点时间上的延迟

如何设置MySQL的主从同步

PHP如何写代码才能满足查询时调用读库,写入更新时调用写库?

写库和读库能不能实时同步?如果不能实时同步而业务要求高必须要同步看到更新的数据怎么办?

强制读主库


MySQL高可用方案

什么是MySQL的高可用,怎么才能高可用?

当主服务器down掉后,如何才能让备用服务器立即提供服务?

heartbeat + keepalive

主服务器与备用服务器之间的数据如何同步?


参考

MySQL DATETIME vs TIMESTAMP vs INT performance and benchmarking with MyISAM
MySQL DATETIME vs TIMESTAMP vs INT performance and benchmarking with InnoDB
MySQL: InnoDB 还是 MyISAM?
MySQL数据库之布尔类型、枚举类型和集合类型的应用场景详解
MySQL行锁深入研究
MYSQL数据丢失讨论
MySQL InnoDB四个事务级别 与 脏读、不重复读、幻读
PDO vs. MySQLi: Which Should You Use?
漫谈数据库索引
MySQL索引背后的数据结构及算法原理
MySQL索引原理及慢查询优化
Mysql Explain 详解
MySQL性能优化的最佳20+条经验
数据库Sharding的基本思想和切分策略
MySQL在大型网站的应用架构演变
数据库分库分表(sharding)系列\

Yan Peipan 29 December 2014