保姆级教程系列（二）之MySQL高级教程

一、 MySQL存储引擎：

什么是存储引擎？
答：数据库存储引擎是数据库底层软件组件，数据库管理系统使用数据引擎进行创建、查询、更新和删除数据操作。不同的存储引擎提供不同的存储机制、索引技巧、锁定水平等功能，使用不同的存储引擎还可以获得特定的功能。
查看存储引擎命令： show engines;或者 show variables like '%storage_engine%';
MySQL 默认存储引擎是？
InnoDB 是系统的默认引擎，支持可靠的事务处理。
修改数据库临时的默认存储引擎命令：
SET default_storage_engine=< 存储引擎名 >
MyISAM & InnoDB
MyISAM：不支持事务，也不支持外键；表锁（即使操作一条记录，也会锁住整个表，不适合高并发的操作）；只缓存索引，不缓存真实数据。[偏向读更多的业务]
InnoDB：支持事务，也支持外键；行锁（操作时只锁某一行，不对其他行有影响）；适合高并发的操作，不仅缓存索引还要缓存真实数据。[偏向写更多的业务]

二、索引优化

性能下降，SQL慢，执行时间长，等待时间长，主要有以下情况：
（1）查询语句写得烂
（2）索引失效（单值、复合）
（3）关联查询太多join（设计缺陷或者不得已的需求）
（4）服务器调优及各个参数设置（缓冲、线程数等）
常见通用的join查询(七种，结合图)

内连接：
select * from table a inner join table b on a.key = b.key;（A∩B）
左外连接：
select * from tabel a left join table b on a.key = b.key; (A)
select * from table a left join table b on a.key = b.key where b.key is null;(A - (A∩B))
右外连接：
select * from tabel a right join table b on a.key = b.key; (B)
select * from table a right join table b on a.key = b.key where b.key is null;(B - (A∩B))
全连接：
select * from table a full outer join table b on a.key = b.key;（A∪B）
select * from table a full outer join table b on a.key = b.key where a.key is null or b.key is null; ((A∪B)) -（A∩B)
SQL执行顺序
MySQL的语句一共分为11步，如下图所标注的那样，最先执行的总是FROM操作，最后执行的是LIMIT操作。其中每一个操作都会产生一张虚拟的表，这个虚拟的表作为一个处理的输入，只是这些虚拟的表对用户来说是透明的，但是只有最后一个虚拟的表才会被作为结果返回。如果没有在语句中指定某一个子句，那么将会跳过相应的步骤。

（1）FORM: 对FROM的左边的表和右边的表计算笛卡尔积。产生虚表VT1

（2）ON: 对虚表VT1进行ON筛选，只有那些符合的行才会被记录在虚表VT2中。

（3）JOIN：如果指定了OUTER JOIN（比如left join、 right join），那么保留表中未匹配的行就会作为外部行添加到虚拟表VT2中，产生虚拟表VT3, rug from子句中包含两个以上的表的话，那么就会对上一个join连接产生的结果VT3和下一个表重复执行步骤1~3这三个步骤，一直到处理完所有的表为止。

（4）WHERE：对虚拟表VT3进行WHERE条件过滤。只有符合的记录才会被插入到虚拟表VT4中。

（5）GROUP BY: 根据group by子句中的列，对VT4中的记录进行分组操作，产生VT5.

（6）CUBE | ROLLUP: 对表VT5进行cube或者rollup操作，产生表VT6.

（7）HAVING：对虚拟表VT6应用having过滤，只有符合的记录才会被插入到虚拟表VT7中。

（8）SELECT：执行select操作，选择指定的列，插入到虚拟表VT8中。

（9）DISTINCT：对VT8中的记录进行去重。产生虚拟表VT9.

（10）ORDER BY: 将虚拟表VT9中的记录按照<order_by_list>进行排序操作，产生虚拟表VT10.

（11）LIMIT：取出指定行的记录，产生虚拟表VT11, 并将结果返回。
MySql官方对索引的定义：索引是帮助MySQL高效获取数据的数据结构。【简单理解为，排好序的快速查找数据结构。】

理解：一般来说，索引本身也很大，不可能全部存在内存中，因此索引往往以文件的形式存储在磁盘上。

索引的优势：
（1）提高数据检索的效率，降低数据库的IO成本；
（2）通过索引列数据进行排序，降低数据排序的成本，降低了CPU的消耗。

索引的劣势：
（1）实际上而言，索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录。虽然查询速度高了，但是增、删、改时需要维护索引的变更。
（2）花时间研究建立优秀的索引。[难]
MySQL索引的分类
单值索引：一个索引只包含单个列，一个表可以有多个单列索引。
唯一索引：索引列的值必须唯一，但允许有空值。
复合索引：一个索引包含多个列
基本语法：
创建索引： create [unique] index indexName on tableName(columnName(length))
alter tableName add [unique] index [indexName] on (columnName(length))
删除索引： drop index [indexName] on tableName
查看索引： show index from tableName \ G（有\G不需要分号）
哪些情况适合建立索引?
（1）主键自动建立唯一索引；
（2）频繁作为查找条件的字段应该建立索引；
（3）查询中与其他表关联的字段，外键关系建立索引；
（4）查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度。
哪些情况不适合建立索引?
（1）表的记录很少；
（2）经常增删改的字段；
（3）频繁更新的字段不适合建立索引；
（4）where条件里用不到的字段不建立索引；
（5）重复且平均分配的字段，或差异小(如：性别)的字段不适合。
【划重点】 MySQL性能分析
Explain关键字（查询执行计划）
语法： explain + sql语句;

功能：
（1）表的读取顺序；
（2）数据读取操作的操作类型；
（3）哪些索引可以使用；
（4）哪些索引被实际使用；
（5）表之间的引用；
（6）每张表有多少行被优化器查询。
各字段解析：
id：select查询的***，表示查询中执行select字句或操作的顺序。
（1）完全相同：id完全相同的时候，顺序执行由上至下。

（2）完全不同：如果是子查询，id序号会递增，id值越大优先级越高，越先被执行。
（3）部分相同：id值大先执行，相同的则顺序执行。

select_type：
（1）simple：简单的select查询，查询中不包含子查询或者union。
（2）primary：查询中如果包含任何复杂的子查询，最外层的查询会被标记为primary。
（3）subquery：子查询。
（4）derived：在from列表中包含的子查询，mysql会递归执行这些子查询，把结果放在临时表中。
（5）union：如果第二个select出现在union之后，则被标记为union；如果union包含在from子句的子查询中，外层的select将被标记为 derived。
（6）union result：从union表获取结果的select；
table：显示这一行的数据是关于哪张表。
tpye：
（1）system：表只有一行记录，一般不现实。
（2）const：表示通过索引一次就查询到了。

（3）eq_ref：唯一性索引扫描(查出的结果仅有一条)，对于每个索引键，表中有一条记录与之匹配。常见用于主键或唯一性索引扫描。例如：查公司的CTO，只有一个。

（4）ref：非唯一性索引扫描，返回匹配某个单独值的所有行。

（5）range：只检索给定范围的行，使用一个索引来选择行。

（6）index：全索引扫描，只遍历索引树。

（7）all：全表扫描

从最好到最差的排序是：system > const > eq_ref > ref > range > index > all 【工作中常用】一般来说得保证查询至少达到range级别，最好能到ref级别。
possible_keys：涉及该字段上存在索引，则会被列出，但是不一定被实际使用该索引。
key：哪些索引被实际使用（不请自来的情况也会有。）
key_len：索引中使用的字节数。保姆级教程系列（二）之MySQL高级教程

（1）最大可能长度，并非实际长度。目标：数值越小越好。
ref：显示索引的哪一列被使用了，如果有值，则为常量 const。
rows：根据表统计信息及索引选用情况，大致估算出找到所需的记录所需要读取的行数，目标，数值越小越好。
Extra：
（1）using filesort(九死一生)： mysql无法利用索引完成排序操作称为"文件排序"。
保姆级教程系列（二）之MySQL高级教程
（2）using temporary(必死无疑)：使用了临时表保存中间结果，常见于排序 order by 和分组查询 group by。

（3）using index(发财了)：表示相应的select操作使用了覆盖索引，避免访问了表的数据行，效率不错。如果同时出现using where，表明索引被用来执行索引键值的查找；如果没有同时出现using where，表明索引用来读取数据而非执行查找动作。
保姆级教程系列（二）之MySQL高级教程

覆盖索引：就是select的数据列只用从索引中就能够取得，不必读取数据行，MySQL可以利用索引返回select列表中的字段，而不必根据索引再次读取数据文件，换句话说查询列要被所建的索引覆盖。
注意：如果要使用覆盖索引，一定要注意select列表中只取出需要的列，不可select*，因为如果将所有字段一起做索引会导致索引文件过大，查询性能下降。
（4）using where：
（5）using join buffer：
（6）imossible where： … where name = “a” and name =“b”;
（7）select tables optimized away：
（8）distinct：

索引分析
单表

双表

多表

保姆级教程系列（二）之MySQL高级教程

索引失效(建立好了索引，但是没有用上)
(1)：全值匹配我最爱

(2)：最佳左前缀原则【带头大哥不能死，中间兄弟不能断】

(3)：不在索引列上做任何操作（计算、函数、（自动or手动）类型转换），会导致索引失效而转向全表扫描

(4)：存储引擎不能使用索引中范围条件右边的列

(5)：尽量使用覆盖索引（只访问索引的查询（索引列和查询列一致）），减少select*。【查询的字段的个数，顺序和索引列都一样。】

(6)：mysql在使用不等于（！=或者<>）的时候无法使用索引会导致全表扫描图

(7)：is null，is not null 也无法使用索引

(8)：like以通配符开头（’%ab…）mysq|l索引失效会变成全表扫描的操作。【 %xx%->all ; %xx->all ; xx%->range】
实际面试题：如何解决 like “%字符串%” 时索引不被使用的方法？
(9)：字符串不加单引号索引失效【varchar类型写错了，会被骂死】

(10)：少用or，用它来连接时会索引失效目
group by 基本上都需要进行排序，会有临时表产生。

------小技巧--------- 口诀-----
全值匹配我最爱，最左前缀要遵守；
带头大哥不能死，中间兄弟不能断；
索引列上少计算，范围之后全失效；
LIKE百分写最右，覆盖索引不写星；
不等空值还有or，索引失效要少用；
VAR引号不可丢， SQL高级也不难！

------------SQL调优步骤–分析-------------
1. 观察，至少跑一天，查看生产中慢的SQL情况
2. 开启慢查询日志，设置阈值，比如超过5秒钟的就是慢SQL，并将其抓取出来。
3. explain+慢SQL 分析
4. show profile 查询SQL在MySQL服务器里面执行细节和生命周期情况
5. 进行数据库服务器的参数调优

-------SQL调优步骤------总结----------------
1. 慢查询的开启并捕获
2. explain+慢SQL 分析
3. show profile 查询SQL在MySQL服务器里面执行细节和生命周期情况
4. SQL数据库服务器的参数调优

三、查询截取分析

查询和排序的优化
（1）：永远小表驱动大表，（大表用上索引，就很快） in关键字、exist关键
（2）：order by 关键字排序优化：尽量使用index排序，避免使用filesort排序
案例：index(age, birth)
（a）explain select * from table where age>20 order by age; // 不会filesort
（b）explain select * from table where age>20 order by birth;// 会产生filesort
（c）explain select * from table where age>20 order by birth,age;// 会产生filesort
（d）explain select * from table order by birth;// 会产生filesort
（e）explain select * from table where birth > "xxxx-xx-xx xx:xx:xx" order by birth;// 会产生filesort
（f）explain select * from table where birth > "xxxx-xx-xx xx:xx:xx" order by age;// 不会filesort
（g）explain select * from table worder by age asc, birth desc;// 会产生filesort
(3)：双路排序，单路排序（总体而言，如果一次不能完成，则需要多于两次）。
(4)：优化策略
（a）不要select *
（b）尝试提高sort_buffer_size
（c）尝试提高max_length_for_sort_data
(5)：group by 和 order by 几乎一致，group by先排序后进行分组。
慢查询日志
MySQL的慢查询日志是MySQL提供的一种日志记录，它用来记录在MySQL.中响应时间超过阀值的语句，具体指运行时间超过long__query_time值的SQL，则会被记录到慢查询日志中。具体指运行时间超过long__query__time值的SQL，则会被记录到慢查询日志中。long_query__time的默认值为10，意思是运行10秒以上的语句。由他来查看哪些SQL超出了我们的最大忍耐时间值，比如一条sq|执行超过5秒钟，我们就算慢SQL，希望能收集超过5秒的sql，结合之前explain进行全面分析。
默认关闭，如果不是需要调优，不建议开启，影响性能。
查看状态：show variables like "%slow_query_log%";
开启： set global slow_query_log=1;
查询默认阈值：show variables like "%long_query_time%"; 10秒
设置阈值：set global long_query_time = 3;(需要从新开启一个会话，才有效)
长时间的SQL语句：select sleep(4);
查看当前系统中有多少慢查询记录：show global status like "%Slow_queries%";
日志分析工具：mysqldumpslow
s：是表示按照何种方式排序；
c：访问次数
l：锁定时间
r：返回记录
t：查询时间
al：平均锁定时间
ar：平均返回记录数
at：平均查询时间
t：即为返回前面多少条的数据；
g：后边搭配一个正则匹配模式，大小写不敏感的；
案例
批量数据脚本
DELIMITER;
函数，有返回值

存储过程
show profile 是mysql提供可以用来分析当前会话中国语句执行的资源消耗情况。
默认是关闭的，并保存最近15次的运行结果。
查看： show variables like 'profiling';
查看结果： show profiles; Query_ID Duration Query
诊断SQL： show profile cpu, block io for query Query_ID;
是病，得治：
converting HEAP to MyISAM 查询结果太大，内存都不够用了往磁盘上搬
creating temp table 创建临时表
copying to tmp table on disk 把内存中临时表复制到磁盘，危险
locked

四、 MySQL锁机制（锁是计算机协调多个进程或线程并发访问某一资源的机制。）

读锁（共享锁）
写锁（排它锁）
表锁：偏向MyISAM存储引擎。
加表锁SQL语句：lock table tableName read(write), tableName read(write);
查看锁情况SQL语句：show open tables; 或者 show status like 'tables';// 对应添加了锁的表中 In_use 字段的值为1。
当添加表锁后，当前session读别的表会出错。
释放表锁SQL语句：unlock;
另外一个session要更新加了表锁的表，会阻塞。
行锁：偏向InnoDB存储引擎，开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。索引失效后，行锁会升级为表锁！！
面试题：如何锁定一行？
间隙锁：当我们采用范围条件查询数据时，InnoDB 会对这个范围内的数据进行加锁。比如有 id 为：1、3、5、7 的 4 条数据，我们查找 1-7 范围的数据。那么 1-7 都会被加上锁。2、4、6 也在 1-7 的范围中，但是不存在这些数据记录，这些 2、4、6 就被称为间隙。

保姆级教程系列（二）之MySQL高级教程

一、 MySQL存储引擎：

二、 索引优化

三、 查询截取分析

四、 MySQL锁机制（锁是计算机协调多个进程或线程并发访问某一资源的机制。）

相关推荐

二、索引优化

三、查询截取分析