mysql简记

show profile

利用 show profile 可以查看 sql 的执行周期！

开启 profile

查看 profile 是否开启： show variables like '%profiling%'

set profiling=1 开启！

show prifiles 命令，可以查看最近的几次查询。

show profile cpu,block io for query Query_id 来查看 sql 的具体执行步骤。

SQL 的执行顺序

from / on / join / where / group by / having / select / distinct / order by / limit

MyISAM 和 InnoDB

MyISAM不支持外键，不支持事物，表锁，只缓存索引不缓存真实数据

InnoDB支持外键，支持事物，行锁，缓存索引和真实数据，内存要求高，内存大小影响性能

索引优化分析

索引（Index）是 排好序的快速查找的数据结构

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

索引优缺点

优势 ：

提高数据 检索 的效率，降低数据库的 IO 成本。

通过索引列对数据进行排序，降低数据排序的成本，降低了 CPU 的消耗。

劣势 ：

虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行 INSERT 、 UPDATE 和 DELETE 。因为更新表时，MySQL 不仅要保存数据，还要保存一下索引文件每次更新添加了索引列的字段，都会调整因为 更新所带来的键值变化后的索引信息。

实际上索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录，所以索引列也是要 占用空间

B+树

B+ 树的非叶子节点中只有关键字和指向下一个节点的索引，记录只放在叶子节点中。

索引分类

单值索引：即一个索引只包含单个列，一个表可以有多个单列索引

CREATE INDEX idx_customer_name ON customer(customer_name);

唯一索引： 索引列的值必须唯一，但允许有空值

CREATE UNIQUE INDEX id x_customer_no ON customer(customer_no);

主键索引： 设定为主键后数据库会自动建立索引， innodb 为聚簇索引

ALTER TABLE customer add PRIMARY KEY customer(customer_no);

复合索引： 即一个索引包含多个列

CREATE INDEX idx_no_name ON customer(customer_no,customer_name);

适合创建索引的情况

主键自动建立唯一索引；

频繁作为查询条件的字段应该创建索引

查询中与其它表关联的字段，外键关系建立索引

单键 / 组合索引的选择问题， 组合索引性价比更高

查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度

查询中统计或者分组字段

不适合创建索引的情况

表记录太少

经常增删改的表或者字段

Explain 性能分析

 

使用 EXPLAIN 关键字分析你的查询语句或是表结构的性能

Explain 执行后返回的信息：

id: select 查询的序列号 , 包含一组数字，表示查询中执行 select 子句或操作表的顺序。id 值越大优先级越高，越先被执行

select_type: select_type 代表查询的类型，主要是用于区别普通查询、联合查询、子查询等的复杂查询。

 

type:type 是查询的访问类型。是较为重要的一个指标，结果值从最好到最坏依次是：

system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index >ALL,一般来说，得保证查询至少达到 range 级别，最好能达到 ref

possible_subquery:显示可能应用在这张表中的索引，一个或多个。查询涉及到的字段上若存在索引，则该索引将被列出，但不一 定被查询实际使用

key: 实际使用的索引。如果为 NULL ，则没有使用索引

key_len:表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度。 key_len 字段能够帮你检查是否充分的利用上了索引。ken_len 越长，说明索引使用的越充分

ref: 显示索引的哪一列被使用了，如果可能的话，是一个常数。哪些列或常量被用于查找索引列上的值

rows: rows 列显示 MySQL 认为它执行查询时必须检查的行数。越少越好！

extra:其他的额外重要的信息。 Using filesort说明 mysql 会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。MySQL 中无法利用索引完成的排序操作称为“文件排序”。 Using temporary使了用临时表保存中间结果,MySQL 在对查询结果排序时使用临时表。常见于排序 order by 和分组查询 group by。 Using filesort和Using temporary最好不要出现

单表使用索引常见的索引失效

全值匹配我最爱 ： 查询的字段按照顺序在索引中都可以匹配到！

最佳左前缀法则 ： 查询字段与索引字段顺序的不同会导致，索引无法充分使用，甚至索引失效！

原因：使用复合索引，需要遵循最佳左前缀法则，即如果索引了多列，要遵守最左前缀法则。指的是查询从索 引的 最左前列开始并且不跳过索引中的列

结论：过滤条件要使用索引必须按照索引建立时的顺序，依次满足，一旦跳过某个字段，索引后面的字段都无

法被使用

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

索引列上不能有范围查询 ： 建议：将可能做范围查询的字段的索引顺序放在最后

尽量使用覆盖索引 ： 查询列和索引列一直，不要写 select *!

使用不等于 (!= 或者 <>) 的时候 ： mysql 在使用不等于 (!= 或者 <>) 时，有时会无法使用索引会导致全表扫描。

字段的 is not null 和 is null ： is not null 用不到索引， is null 可以用到索引。

like 的前后模糊匹配 ：通配符%最好放在关键字的后面（‘aa%’）

减少使用 or : 使用 union all 或者 union 来替代：

口诀

全职匹配我最爱，最左前缀要遵守；

带头大哥不能死，中间兄弟不能断；

索引列上少计算，范围之后全失效；

LIKE 百分写最右，覆盖索引不写 * ；

不等空值还有 OR ，索引影响要注意；

VAR 引号不可丢， SQL 优化有诀窍

关联查询优化

left join:

①在优化关联查询时，只有在被驱动表上建立索引才有效！

② left join 时，左侧的为驱动表，右侧为被驱动表！

inner join:

④结论： inner join 时， mysql 会自己帮你把小结果集的表选为驱动表。

⑤ straight_join: 效果和 inner join 一样，但是会强制将左侧作为驱动表！

结论 ：

子查询尽量不要放在被驱动表，有可能使用不到索引；

left join时，尽量让实体表作为被驱动表

子查询优化

结论： 在范围判断时，尽量不要使用 not in 和 not exists ，使用 left join on xxx is null 代替。

排序分组优化

where 条件和 on 的判断这些过滤条件，作为优先优化的部门，是要被先考虑的！其次，如果有分组和排序，那么 也要考虑 grouo by 和 order by 。

结论： 无过滤，不索引。 where ， limt 都相当于一种过滤条件，所以才能使用上索引！

所有的排序都是在条件过滤之后才执行的，所以如果条件过滤了大部分数据的话，几百几千条数据进行排序

其实并不是很消耗性能，即使索引优化了排序但实际提升性能很有限。

结论： 当范围条件和 group by 或者 order by 的字段出现二选一时 ，优先观察条件字段的过滤数量，如果过滤的

数据足够多，而需要排序的数据并不多时，优先把索引放在范围字段上。反之，亦然。

group by 使用索引的原则几乎跟 order by 一致 ，唯一区别是 groupby 即使没有过滤条件用到索引，也可以直 接使用索引

截取查询分析

1. 慢查询日志

（1） MySQL 的慢查询日志是 MySQL 提供的一种日志记录，它用来记录在 MySQL 中响应时间超过阀值的语句，具 体指运行时间超过long_query_time 值的 SQL ，则会被记录到慢查询日志中。

（2）具体指运行时间超过 long_query_time 值的 SQL ，则会被记录到慢查询日志中。 long_query_time 的默认值为 10，意思是运行 10 秒以上的语句。

（3）由他来查看哪些 SQL 超出了我们的最大忍耐时间值，比如一条 sql 执行超过 5 秒钟，我们就算慢 SQL ，希望能 收集超过5 秒的 sql ，结合之前 explain 进行全面分析。

默认情况下， MySQL 数据库没有开启慢查询日志 ，需要我们手动来设置这个参数。

当然，如果不是调优需要的话，一般不建议启动该参数 ，因为开启慢查询日志会或多或少带来一定的性能影响。

慢查询日志支持将日志记录写入文件

 

 日志分析工具 mysqldumpslow

 SHOW PROCESSLIST

查询 mysql 进程列表，可以杀掉故障进程

主从复制

 

MySQL 复制过程分成三步：

master 将改变记录到二进制日志（binary log）。这些记录过程叫做二进制日志事件， binary log events ；

slave 将 master 的 binary log events 拷贝到它的中继日志（

relay log ）；

slave 重做中继日志中的事件，将改变应用到自己的数据库中。 MySQL 复制是异步的且串行化的