看了这篇MySQL，开发功力又升级

[quote]本文主要介绍 Mysql开发和面试中所必知的
本文较长，分为上下篇（可收藏，勿吃尘）
如有需要，可以参考
如有帮助，不忘 点赞 ❥
[/code]"[size=".(7 - 1)."]"一、MySQL架构

"[size=".(7 - 1)."]"1）MySQL简介

[quote]MySQL是一个关系型数据库管理系统，由瑞典MYSQL AB公司开发，目前属于Oracle公司。
MySQL 是一种关联数据库管理系统，将数据保存在不同的表中，而不是将所有数据放在一个大仓库中，这样就增加了速度并提高了灵活性。
Mysql是开源的，是可以定制的，采用了GPL协议，你可以修改源码来开发自己的MySQL系统。
MySQL支持大型的数据库。可以处理拥有上千万条记录的大型数据库。MySQL可以允许于多个系统上，并且支持多种语言。这些编程语言包括C、C++、Python、Java、Perl、PHP、Eiffel、Ruby和Tcl等。
MySQL支持大型数据库，支持5000条记录的数据仓库，32位系统表文件最大可支持4GB，64位系统支持最大的表文件为8TB。
[/code]"[size=".(7 - 1)."]"2）MySQL配置文件

• binlog(二进制日志)
  用于主从复制及备份恢复：binlog中存放了所有操作记录，可用于恢复。相当于Redis中的AOF，my.ini中binlog配置（默认是关闭的）如何开启：
  

复制

复制

常用设置：

复制

• 简单理解为 “排好序的快速查找数据结构” ：
  在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据。
  

看了这篇MySQL，开发功力又升级

左边是数据表，一共有两列七条数据，最左边是数据记录的物理地址，为了加快Col2 的查找，可以维护一个右边所示的二叉查找树，每个节点分别包含索引值和一个指向对应数据记录物理地址的指针，这样就可以运用二叉查找在一定的复杂度内获取到相应数据，从而快速的检索出符合条件的记录。
二叉树： 二叉树很可能会发生两边不平衡的情况。
B-Tree： 会自动根据两边的情况自动调节，使两端无限趋近于平衡状态，可以使性能最稳定。但是插入/修改操作过多时，B-TREE会不断调整平衡，消耗性能。

• 一般来说索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储在磁盘上。
  
• 我们平常所说的索引，如果没有特别指明，都是指B树 （多路搜索树，并不一定是二叉的）结构组织的索引。其中聚集索引，次要索引，覆盖索引，复合索引，前缀索引，唯一索引默认都是使用B+树这种类型的索引之外，还有哈希索引（hash index）等。
  

索引优势

• 类似图书馆简历书目索引，提高数据检索的效率，降低数据库的IO成本。
  
• 通过索引列对数据进行排序，降低数据排序的成本，降低了CPU的消耗。
  

索引劣势
实际上索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录，所以索引列也是要占用空间的。
虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE、和DELETE，因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件每次更新添加了索引列的字段，都会调整因为更新所带来的键值变化后的索引信息。
索引只是提高效率的一个因素，如果你的MySQL有大量的表，就需要花时间研究建立最优秀的索引，或优化查询语句
索引结构
1.BTree索引：
真实数据存在于叶子节点，即3、5、9、10、13、15、28、29、36、60、75、79、90、99.
非叶子节点不存储真实的数据，只存储指引搜索方向的数据项，如17、35并不真实存在于数据表中。

看了这篇MySQL，开发功力又升级

【查找过程】
如果要查找数据29，那么首先会把磁盘块1有磁盘加载到内存，此时发生一次IO，在内存中用二分查找确定29在17和35之间，锁定磁盘块1的P2指针，内存时间因为非常短（相比磁盘的IO）可以忽略不计，通过磁盘块1的的P2指针的磁盘的磁盘地址把磁盘块3由磁盘加载到内存，发生第二次IO，29在26和30之间，锁定磁盘块3的P2指针，通过指针加载磁盘块8到内存，发生第三次IO，同时内存中做二分查找到29，结束查询，总计三次IO
2.B+Tree索引：

看了这篇MySQL，开发功力又升级

B+Tree 第二级的数据并不能直接取出来，只作索引使用。在内存有限的情况下，查询效率高于BTree
BTree第二级可以直接取出来，树形结构比较重，在内存无限大到时候有优势。
【B+Tree 和 BTree 的区别】
1） 内存有限的情况下，B+Tree永远比BTree好，无限内存则反之
2） B树的关键字和记录是放在一起的，叶子节点可以看做外部节点，不包含任何信息；B+树叶子节点中国你只有关键字和指向下一个节点的索引，记录只放在叶子节点中。（一次查询可能进行两次I/O操作）
3） 在B树中，越靠近根节点的记录查找时间越快，只要找到关键字即可确定记录存在；而B+树每个记录的查找时间基本是一样的，都需要从根节点走到叶子节点，而且在叶子节点中还要在比较关键字。从这个角度看B树的性能好像会比B+树好，而在实际应用中却是B+树的性能要好些。因为B+树的非叶子节点不存放实际的数据，这样每个节点可容纳的元素个数比B数多，树高比B树小，这样带来的好处是减少磁盘访问次数。尽管B+树找到一个记录所需的比较次数比B树多但是一次磁盘访问时间相当于成百上千次内存比较时间，因此实际中B+树的性能可能还会好写，而且B+树的叶子节点使用指针连接在一起，方便顺序遍历（例如查看一个目录下的所有文件，一个表中的所有记录等）
4） B+树的磁盘读写代价更低，相对来说IO读写次数也就降低了。
5） B+树的查询效率更加稳定。由于非终结点并不是指向文件内容的节点，而只是叶子节点中关键字的索引。所以任何关键字的查找必须走一条从根节点到叶子节点的路。所以关键字查询的路径长度相同，导致每一个数据的查询效率相当。
聚簇索引

看了这篇MySQL，开发功力又升级

好处：
按照聚簇索引排序顺序，查询显示一定范围数据的时候，由于数据都是紧密相连，数据库不用从多个数据块中提取数据，所以节省了大量的IO操作。
限制：

• 对于MySQL数据库目前只有InnoDB数据引擎支持聚簇索引，而MyISAM并不支持聚簇索引。
  
• 由于数据物理存储排序方式只能有一种，所以每个MySQL的表只能有一个聚簇索引。一般情况下就是该表的主键。
  

3.full-text全文索引
[quote]全文索引（也称全文检索）是目前搜索引擎使用的一种关键技术。它能够利用分词技术等多种算法智能分析出文本文字中关键词的频率和重要性，然后按照一定的算法规则智能地筛选出我们想要的搜索结果。
[/code]

• 查询：
  

[code][mysqld]log-error=/data/mysql_error.log复制代码6</pre>

• 限制： MySQL5.6.4之前只用MyISAM 支持，5.6.4以后 InnoDB才支持，但是官方版不支持中文分词，需要第三方分词插件。
  

4.Hash索引

• Hash 索引只有Memory，NDB两种引擎支持，Memory引擎默认支持。
  
• Hash索引，如果多个Hash值相同，出现哈希碰撞，那么索引以链表的方式存储。
  
• NoSql采用此索引结构。
  

5.RTree索引

• R-Tree在MySQL很少使用，仅支持geometry 数据结构，支持该类型的存储引擎只有MyISAM、bdb、InnoDB、ndb、archive几种。相对于B-Tree，R-Tree的优势在于查找。
  

索引分类
1.主键索引

• 设定为主键后数据库会自动简历索引，InnoDB采用聚簇索引
  语法：
  

[code][mysqld]log-error=/data/mysql_error.log复制代码7</pre>2.单值索引

• 即一个索引只包含单个列，一个表可以有多个单列索引。
  语法：
  

[code][mysqld]log-error=/data/mysql_error.log复制代码8</pre>3.唯一索引

• 索引列的值必须唯一，但允许有空值。
  建立唯一索引是必须保证所有的值是唯一的(除了null)，若有重复数据，会报错
  

[code][mysqld]log-error=/data/mysql_error.log复制代码9</pre>4.复合索引
在数据库操作期间，复合索引比单值索引所需要的开销更小（对于相同的多个列建索引）;
当表的行数远大于索引列的数目时可以使用复合索引。
[code][mysqld]slow_query_log = ONslow_query_log_file = /usr/local/mysql/data/slow.log     //linuxlong_query_time = 1复制代码0</pre>【基本语法】
[code][mysqld]slow_query_log = ONslow_query_log_file = /usr/local/mysql/data/slow.log     //linuxlong_query_time = 1复制代码1</pre>哪些情况需要建立索引

• 主键自动建立唯一索引
  
• 频繁作为查询条件的字段应该创建索引（where后面的语句）
  
• 查询中与其他表关联的字段，外键关系建立索引
  
• 单键/组合索引的选择问题（在高并发下倾向创建组合索引）
  
• 查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度
  

查询中统计或者分组字段
哪些情况不需要建立索引

• 表记录太少
  
• 经常增删改的表（因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件）
  
• where 条件里用不到的字段不创建索引
  
• 数据重复且分布平均的表字段，因此应该只为最经常查询和最经常排序的数据列建立索引。注意，如果某个数据列包含许多重复的内容，为它建立索引就没有太大的实际效果。
  

"[size=".(7 - 1)."]"4）性能分析

• MySQL常见瓶颈
  

CPU
SQL中对大量数据进行比较、关联、排序、分组（最大的压力在于比较）
IO
实例内存满足不了缓存数据或排序等需要，导致产生大量物理IP。查询执行效率低，扫描过多数据行。
锁
不适宜的锁的设置，导致线程阻塞，性能下降。死锁，线程之间交叉调用资源，导致死锁，程序卡主。
服务器硬件的性能瓶颈：
top，free，iostat和vmstat来查看系统的性能状态
Explain的使用
使用Explain关键字可以模拟优化器执行SQL查询语句，从而知道MySQL是如何处理你的SQL语句的。分析你的查询语句或是表结构的性能瓶颈

• 可以查看的内容
  

• 表的读取顺序
  
• 哪些索引可以使用
  
• 哪些索引被实际使用
  
• 表之间的引用
  
• 每张表有多少行被优化器查询
  

• 怎么用
  

explain + SQL语句
包含的信息：

看了这篇MySQL，开发功力又升级

• 各字段解释
  

1.【 id】
select查询的序列号，包含一组数字，表示查询中执行select字句或操作表的顺序。
三种情况：

• id相同，执行顺序由上至下
  

看了这篇MySQL，开发功力又升级

id不同，如果是子查询，id的序号会递增，id值越大优先级越高，越被先执行

看了这篇MySQL，开发功力又升级

id如果相同，可以认为是一组，从上往下顺序执行；在所有组中，id值越大，优先级越高，越先执行。
2.【select_type】

看了这篇MySQL，开发功力又升级

SIMPLE
简单的select查询，查询中不包含子查询或者UNION

看了这篇MySQL，开发功力又升级

PRIMARY
查询中若包含任何复杂的子部分，最外层查询则被标记为Primary

看了这篇MySQL，开发功力又升级

DERIVED
在FROM列表中包含的子查询被标记为DERIVED（衍生）MySQL会递归执行这些子查询，把结果放在临时表里。

看了这篇MySQL，开发功力又升级

SUBQUERY
在SELECT或WHERE列表中包含了子查询

看了这篇MySQL，开发功力又升级

DEPENDENT SUBQUERY
在SELECT或WHERE列表中包含了子查询，子查询基于外层

看了这篇MySQL，开发功力又升级

• 【dependent subquery 和 subquery 的区别】
  依赖子查询：子查询结果为多值
  子查询：查询结果为单值
  
• UNCACHEABLE SUBQUERY
  无法被缓存的子查询
  

看了这篇MySQL，开发功力又升级

• @@表示查的是环境参数，没办法缓存
  
• UNION
  若第二个SELECT出现在UNION之后，则被标记为UNION；
  若UNION 包含在FROM字句的子查询中，外层SELECT将被标记为：DERIVED
  

看了这篇MySQL，开发功力又升级

UNION RESULT
从UNION表获取结果的SELECT

看了这篇MySQL，开发功力又升级

3.【table】
显示这一行的数据是关于哪张表的
4.【type】
type显示的是访问类型，是较为重要的一个指标，结果值从最好到最坏的依次排序：
system > const > eq_ef > ref > range(尽量保证) > index > ALL
一般来说，得保证查询至少达到range级别，最好能达到ref

• system
  表只有一行记录（等于系统表），这是const类型的特列，平时不会出现，这个也可以忽略不计
  
• const
  表示通过索引一次就找到了，const 用于比较primary key或者 unique索引。因为只匹配一行数据，所以很快将主键置于where列表中，MySQL就能将该查询转换为一个常量
  
• eq_ref
  唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描
  
• ref
  非唯一性索引扫描，返回匹配某个单独值的所有行，本质上也是一种索引访问，它返回所有匹配某个单独值的行，然而，他可能会找到多个符合条件的行，所以他应该属于查找和扫描的混合体
  
• range
  只检索给定范围的行，使用一个索引来选择行。key列显示使用了哪个索引，一般就是在你的where语句中出现了between、<、>、in等查询。这种范围扫描索引比全表扫描要好，因为它只需要开始于索引的某一个点，而结束于另一点，不用扫描全部索引。
  
• index
  Full Index Scan，index与ALL区别为index类型只遍历索引树。这通常比ALL快，因为索引文件通常比数据文件小。（也就是说虽然all和index都是读全表的），但index是从索引中读取的，而all是从硬盘中读的。
  
• all
  Full Table Scan，将遍历全表以找到匹配的行
  

看了这篇MySQL，开发功力又升级

看了这篇MySQL，开发功力又升级

5.【possible_keys】
显示可能应用到这张表中的索引，一个或多个。查询涉及到的字段上若存在的索引，则该索引将被列出，但不一定被查询实际使用
6.【key】
实际使用的索引，如果为NULL，则没有使用索引。查询中若使用了覆盖索引，则该索引和查询的select字段重叠。
7.【key_len】
表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度。key_len字段能够帮你检查是否充分利用上了索引。

看了这篇MySQL，开发功力又升级

计算方式：

看了这篇MySQL，开发功力又升级

动态类型包括：varchar，detail text（）截取字符串
本章的表结构如下：

看了这篇MySQL，开发功力又升级

第一组计算结果：
key_len=deptno(int)+null+ename(varchar(20)3+动态=4+1+203+2=67
第二组计算为：
key_len=deptno(int)+null=4+1=5
8.【ref】
显示索引的哪一列被使用了，如果可能的话，是一个常数。哪些列或者常量被用于查找索引列上的值

看了这篇MySQL，开发功力又升级

9.【row】
rows列显示MySQL认为它执行查询时必须检查的行数（越少越好）

看了这篇MySQL，开发功力又升级

10.【Extra】
包含不适合在其他列中显示但十分重要的额外信息。

• Using filesort
  说明mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。MySQL中无法利用索引完成的排序操作称为“文件排序”。
  
• Using temporary
  使用了临时表保存中间结果，MySQL在对查询结果排序时使用临时表。常见于排序order by 和分组查询 group by。
  
• Using index
  表示相应的select操作中使用了覆盖索引（Covering Index），避免了表的数据行，效率不错。如果同时出现using where，表明索引被用来执行索引键值得查找；如果没有同时出现using where，表明索引只是用来读取数据而非利用索引执行查找。
  覆盖索引：
  一个索引包含了（或覆盖了）【select子句】与查询条件【where 子句】中所有需要的字段就叫做覆盖索引。
  注意： 只取出需要的列，不可select *，不可将所有字段一起做索引
  
• Using where
  表明使用了 where 过滤
  
• Using join buffer
  使用了连接缓存
  

"[size=".(7 - 1)."]"5）查询优化

• 索引的使用
  

• 全值匹配我最爱
  staffs 表建立索引 idx_staffs_nameAgePos,以name，age，pos的顺序建立，全值匹配标识按顺序匹配。
  

看了这篇MySQL，开发功力又升级

2.最佳左前缀原则
如果索引了多列，要遵守最左前缀原则，值得是查询从索引的最左前列开始，并且不跳过索引中的列
and 忽略左右关系，即使没有按顺序，由于优化器的存在，也会自动优化
3.不在索引列上做任何操作（计算、函数、（自动或手动）类型转换），会导致索引失效而转向全表扫描。

看了这篇MySQL，开发功力又升级

4.存储引擎不能使用索引中范围条件右边的列
范围若有索引则能使用到索引，范围条件右边的索引会失效（范围条件右边与范围条件使用的同一个组合索引，右边的才会失效，若是不同索引则不会失效）
5.**尽量使用覆盖索引（只访问索引的查询（索引列和查询列一致）），减少select ***

看了这篇MySQL，开发功力又升级

6.MySQL在使用不等于（!= 或 <>）的时候无法使用索引，会导致全表扫描。
where age != 10 and name = &#39;xxx&#39; 这种情况下，mysql会自动优化将 name = &#39;xxx&#39; 放在 age != 10 之前，name依然能使用索引，只是age的索引失效

看了这篇MySQL，开发功力又升级

7.is not null 也无法使用索引，但是 is null 是可以使用索引

看了这篇MySQL，开发功力又升级

8.like 以通配符开头（&#39;%xxx&#39;）索引失效变成全表扫描
like &#39;%xxx&#39;：type 类型会变成all
like &#39;xxx%&#39;：type 类型为range，算是范围，可以使用索引

看了这篇MySQL，开发功力又升级

9.字符串不加单引号索引失效
底层进行类型转换时索引失效，使用了函数造成了索引失效

看了这篇MySQL，开发功力又升级

10.少用or，用它连接时索引会失效

看了这篇MySQL，开发功力又升级

【例子小结】
此时复合索引index（a,b,c）

看了这篇MySQL，开发功力又升级

【使用建议】

• 对于单键索引，尽量选择针对当前query过滤性更好的索引
  
• 在选择组合索引的时候，当前query中过滤性最好的字段在索引字段顺序中，位置越靠前越好。（避免索引过滤性不好的索引失效）
  
• 在选择组合索引的时候，尽量选择可以能够包含当前query中where字句中更多字段的索引
  
• 尽可能通过分析统计信息和调整query的写法来达到选择合适索引的目的
  

• 关联查询优化
  1、保证被驱动表的join字段已经被索引（join后的表为驱动表）
  2、left join时，选择小表作为驱动表，大表作为被驱动表（left join一定是左边是驱动表，右边是被驱动表）
  3、inner join时，MySQL会自己帮你把小结果集选为驱动表。因为驱动表无论如何都会被全表扫描，所以扫描次数越少越好。
  4、子查询尽量不要放在被驱动表，有可能使用不到索引。
  

[code]# 未加索引，type为ALLexplain select * from class left join book on class.card = book.card# 添加索引优化，第二行的type变成了refalter table book add index idx_card_B(card);# 这是由左连接特效决定的，left join条件用于确定如何从右表搜索行，左边一定都有# 继续优化，删除旧索引，新建新索引drop index idx_card_B on book;alter table class a