mysql简介

目录

MySQL架构

索引

索引类型

按字段特性分类

从物理存储角度

按索引字段个数分类

索引优化

SQL执行过程

explain用法

---

MySQL架构

Server层包括连接器、 查询缓存、 分析器、 优化器、 执行器等， 涵盖MySQL的大多数核心服务功能， 以及所有的内置函数（如日期、 时间、 数学和加密函数等） ，所有跨存储引擎的功能都在这一层实现， 比如存储过程、 触发器、 视图等。

存储引擎层负责数据的存储和提取。 其架构模式是插件式的， 支持InnoDB、 MyISAM、Memory等多个存储引擎。 现在最常用的存储引擎是InnoDB，它从MySQL 5.5.5版本开始成为了默认存储引擎。

MySQL拿到一个查询请求后， 会先到查询缓存看看， 之前是不是执行过这条语句。 之前执行过的语句及其结果可能会以key-value对的形式，被直接缓存在内存中。 key是查询的语句， value是查询的结果。可以将参数query_cache_type设置成DEMAND， 这样对于默认的SQL语句都不使用查询缓存。 

MySQL 8.0版本直接将查询缓存的整块功能删掉了。

索引

索引类型

按字段特性分类

• 主键索引

建立在主键上的索引被称为主键索引，一张数据表只能有一个主键索引，索引列值不允许有空值，通常在创建表时一起创建。

• 唯一索引

建立在UNIQUE字段上的索引被称为唯一索引，一张表可以有多个唯一索引，索引列值允许为空，列值中出现多个空值不会发生重复冲突。

• 普通索引

建立在普通字段上的索引被称为普通索引。

从物理存储角度

• 聚集索引

索引与数据存放在一起，找到索引的同时也找到了数据；聚簇索引具有唯一性，一张表只有一个聚簇索引。聚簇索引默认是主键，如果表中没有定义主键，InnoDB 会选择一个非空唯一索引代替。如果没有，InnoDB 会使用隐藏的_rowid 列来作为聚簇索引。

• 非聚集索引

索引与数据分开存放，索引结构的叶子节点指向了数据的对应行。

按索引字段个数分类

• 单列索引

单列索引就是索引只包含原表的一个列。在表中的单个字段上创建索引，单列索引只根据该字段进行索引。

• 多列索引

组合索引也称为复合索引或多列索引，组合索引是将原表的多个列共同组成一个索引，在MySQL中使用联合索引时要遵循最左前缀匹配原则。

索引优化

• 主键索引最好是自增的

InnoDB 创建主键索引默认为聚簇索引，每次插入的新数据就会按顺序添加到当前索引节点的位置，不需要移动已有的数据，当页面写满，就会自动开辟一个新页面，因此这种插入数据的方法效率非常高。如果使用非自增主键，由于每次插入主键的索引值都是随机的，因此每次插入新的数据时，就可能会插入到现有数据页中间的某个位置，这将不得不移动其它数据来满足新数据的插入，甚至需要从一个页面复制数据到另外一个页面，我们通常将这种情况称为页分裂。页分裂还有可能会造成大量的内存碎片，导致索引结构不紧凑，从而影响查询效率。

• 索引最好设置为NOT NULL

第一原因：索引列存在 NULL 就会导致优化器在做索引选择的时候更加复杂，更加难以优化，因为可为 NULL 的列会使索引、索引统计和值比较都更复杂，比如进行索引统计时，count 会省略值为NULL 的行。

第二个原因：NULL 值是一个没意义的值，但是它会占用物理空间，所以会带来的存储空间的问题，会导致更多的存储空间占用，因为 InnoDB 默认行存储格式COMPACT，会用 1 字节空间存储 NULL 值列表。

• 前缀索引优化

前缀索引顾名思义就是使用某个字段中字符串的前几个字符建立索引，使用前缀索引是为了减小索引字段大小，可以增加一个索引页中存储的索引值，有效提高索引的查询速度。在一些大字符串的字段作为索引时，使用前缀索引可以帮助我们减小索引项的大小。不过，前缀索引有一定的局限性，例如order by 就无法使用前缀索引，另外也无法把前缀索引用作覆盖索引；

• 覆盖索引优化

覆盖索引是指 SQL 中 query 的所有字段，在索引 B+Tree 的叶子节点上都能找得到的那些索引，从二级索引中查询得到记录，而不需要通过聚簇索引查询获得，可以避免回表的操作。

• 防止索引失效

使用左或者左右模糊匹配的时候，也就是 like %xx 或者 like %xx%这两种方式都会造成索引失效；

查询条件中对索引列做了计算、函数、类型转换操作，这些情况下都会造成索引失效；

联合索引要能正确使用需要遵循最左匹配原则，也就是按照最左优先的方式进行索引的匹配，否则就会导致索引失效；

在 WHERE 子句中，如果在 OR 前的条件列是索引列，而在 OR 后的条件列不是索引列，那么索引会失效；

SQL执行过程

explain用法

id	SELECT识别符。这是SELECT的查询序列号
select_type	SELECT类型,可以为以下任何一种: SIMPLE:简单SELECT(不使用UNION或子查询) PRIMARY:最外面的SELECT UNION:UNION中的第二个或后面的SELECT语句 DEPENDENT UNION:UNION中的第二个或后面的SELECT语句,取决于外面的查询 UNION RESULT:UNION 的结果 SUBQUERY:子查询中的第一个SELECT DEPENDENT SUBQUERY:子查询中的第一个SELECT,取决于外面的查询 DERIVED:导出表的SELECT(FROM子句的子查询)
table	输出的行所引用的表
type	联接类型。下面给出各种联接类型,按照从最佳类型到最坏类型进行排序: system:表仅有一行(=系统表)。这是const联接类型的一个特例。 const:表最多有一个匹配行,它将在查询开始时被读取。因为仅有一行,在这行的列值可被优化器剩余部分认为是常数。const表很快,因为它们只读取一次。常量连接 eq_ref:对于每个来自于前面的表的行组合,从该表中读取一行。这可能是最好的联接类型,除了const类型。主键索引(primary key)或者非空唯一索引(unique not null)等值扫描。简单来说，就是多表连接中使用primary key或者 unique key作为关联条件。 ref:对于每个来自于前面的表的行组合,所有有匹配索引值的行将从这张表中读取。非主键非唯一索引等值扫描。 ref_or_null:该联接类型如同ref,但是添加了MySQL可以专门搜索包含NULL值的行。 index_merge:该联接类型表示使用了索引合并优化方法。 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:只检索给定范围的行,使用一个索引来选择行。 index:该联接类型与ALL相同,除了只有索引树被扫描。这通常比ALL快,因为索引文件通常比数据文件小。 ALL:对于每个来自于先前的表的行组合,进行完整的表扫描。
possible_keys	指出MySQL能使用哪个索引在该表中找到行
key	显示MySQL实际决定使用的键(索引)。如果没有选择索引,键是NULL。
key_len	表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度。（key_len显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出的）不损失精确性的情况下，长度越短越好 。
ref	显示使用哪个列或常数与key一起从表中选择行。
rows	显示MySQL认为它执行查询时必须检查的行数。多行之间的数据相乘可以估算要处理的行数。
filtered	显示了通过条件过滤出的行数的百分比估计值。
Extra	该列包含MySQL解决查询的详细信息 Distinct:MySQL发现第1个匹配行后,停止为当前的行组合搜索更多的行。 Not exists:MySQL能够对查询进行LEFT JOIN优化,发现1个匹配LEFT JOIN标准的行后,不再为前面的的行组合在该表内检查更多的行。 range checked for each record (index map: #):MySQL没有发现好的可以使用的索引,但发现如果来自前面的表的列值已知,可能部分索引可以使用。 Using filesort:MySQL需要额外的一次传递,以找出如何按排序顺序检索行。 Using index:从只使用索引树中的信息而不需要进一步搜索读取实际的行来检索表中的列信息。 Using temporary:为了解决查询,MySQL需要创建一个临时表来容纳结果。 Using where:WHERE 子句用于限制哪一个行匹配下一个表或发送到客户。 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查询的所有列,而不要额外搜索硬盘访问实际的表。 Using index condition: 索引下推

Extra这个字段中的“Using filesort”表示的就是需要排序， MySQL会给每个线程分配一块内存用于排序， 称为sort_buffer。可能在内存中完成， 也可能需要使用外部排序， 这取决于排序所

需的内存和参数sort_buffer_size。sort_buffer_size， 就是MySQL为排序开辟的内存（sort_buffer） 的大小。 如果要排序的数据量小于sort_buffer_size， 排序就在内存中完成。 但如果排序数据量太大， 内存放不下， 则不得不利用磁盘临时文件辅助排序。

全字段排序 VS rowid排序

• 如果MySQL实在是担心排序内存太小， 会影响排序效率， 才会采用rowid排序算法， 这样排序过程中一次可以排序更多行， 但是需要再回到原表去取数据。
• 如果MySQL认为内存足够大， 会优先选择全字段排序， 把需要的字段都放到sort_buffer中， 这样排序后就会直接从内存里面返回查询结果了， 不用再回到原表去取数据。