MySQL 高级

1. MySQL 的架构介绍

1. MySQL 的内部架构

• 连接层
• 服务层
• 引擎层
• 存储层

2. 查看MySQL存储引擎

• show engines;
• show variables like '%storate_engine%';: 查看默认的存储引擎

2.1 MyISAM 和 InnoDB 比较

3. SQL优化步骤

• 观察,至少跑一天,看看生产的慢SQL情况;
• 开启慢查询日志,设置阈值,比如超过5秒钟的就是慢SQL,并将它抓取出来;
• EXPLAIN+慢SQL分析;
• SHOW profile,查询SQL在MySQL服务器里面的执行细节和生命周期情况;
• 运维经理或DBA,进行SQL数据库服务器的参数调优;

索引优化分析

1. 常用Join查询

1.1 SQL 执行顺序

1.2 Join 图

2. 索引

• 索引(Index)是帮助MySQL高效获取数据的数据结构;索引的本质就是排好序的快速查找数据结构;
• 索引的目的在于提高查询效率,可以类比字典;
• 索引优势
  • 提高数据检索的效率,降低数据库的IO成本;
  • 通过索引列对数据进行排序,降低数据排序的成本,降低了CPU的消耗;
• 索引劣势
  • 实际上,索引也是一张表,该表保存了主键与索引字段,并指向实体表的记录,所以索引列也是要占用磁盘空间的;
  • 虽然索引大大提高了查询速度,同时却会降低更新表的速度,例如对表进行INSERT,UPDATE 和 DELETE,
    因为更新表时,MySQL不仅要保存数据,还要保存一下索引文件,每次更新添加了索引列的字段,都会调整因为
    更新所带来的键值变化后的索引信息;

2.1 索引的分类

• 单值索引: 即一个索引只包含单个列,一个表可以有多个单列索引;
• 唯一索引: 索引列的值必须唯一,但允许有空值;
• 复合索引: 即一个索引包含多个列;
• 基本语法:

// 创建
CREATE [UNIQUE] INDEX indexName ON mytable(columnname(length));

ALTER mytable ADD [UNIQUE] INDEX [indexName] ON (columnname(length));

// 删除
DROP INDEX [indexName] ON mytable;

// 查看
SHOW INDEX FROM table_name


// 有四种方式来添加数据表的索引:
// 1. 该语句添加一个主键,这意味着索引值必须是唯一的,且不能为NULL
ALTER TABLE tbl_name ADD PRIMARY KEY (column_list);

// 2. 这条语句创建索引的值必须是唯一的(除了NULL外,NULL可能会出现多次)
ALTER TABLE tbl_name ADD UNIQUE index_name(column_list);

// 3. 添加普通索引,索引值可出现多次
ALTER TABLE tbl_name ADD INDEX index_name(column_list);

// 4. 该语句指定了索引为 FULLTEXT, 用于全文索引
ALTER TABLE tbl_name ADD FULLTEXT index_name(column_list);

2.2 索引的结构

• BTree 索引
• Hash 索引
• full-text 索引
• R-Tree 索引

2.3 是否创建索引

• 需要创建索引的情况
  • 主键自动建立唯一索引;
  • 频繁作为查询条件的字段应该创建索引;
  • 查询中与其他表关联的字段,外键关系建立索引;
  • 频繁更新的字段不适合创建索引;
  • WHERE 条件里用不到的字段不要创建索引;
  • 高并发情况下,倾向创建组合索引;
  • 查询中排序的字段,排序字段若通过索引去访问将大大提高索引速度;
  • 查询中统计或者分组字段
• 不需要创建索引的情况
  • 表记录太少;
  • 经常增删改的表;
  • 如果某个数据列包含许多重复的内容,为它建立索引就没有太大的实际效果;

2.4 性能优化

• MySQL 常见瓶颈

  • CPU: CPU在饱和的时候,一般发生在数据装入内存或从磁盘上读取数据的时候;
  • IO: 磁盘I/O瓶颈发生在装入数据远大于内存容量的时候;
  • 服务器硬件的性能瓶颈: top, free, iostat 和 vmstat 来查看系统的性能;

• Explain

  • 使用EXPLAIN关键字,可以模拟优化器执行SQL查询语句,从而知道MySQL是如何处理SQL语句的,从而分析
    查询语句或是表结构的性能瓶颈;
  • 语法: Explain + SQL语句
  • 从EXPLAIN结果中获取:
    • 表的读取顺序;
    • 数据读取操作的操作类型;
    • 哪些索引可以使用;
    • 哪些索引被实际使用;
    • 表之间的引用;
    • 每张表有多少行被优化器查询;

2.4.1 Explain 各字段解释

• id: 表示查询中执行select子句或操作表的顺序
  • id相同,执行顺序由上至下;
  • id不同,如果是子查询,id的序号会递增,id值越大优先级越高,越先被执行;
  • id相同不同,都存在;
• select_type
  • 表示查询的类型,主要用于区别普通查询,联合查询,子查询等复杂查询;
  • SIMPLE: 简单的select查询,查询中不包含子查询或者UNION;
  • PRIMARY: 查询中若包含任何复杂的子部分,最外层查询则被标记为PRIMARY;
  • SUBQUERY: 在SELECT或WHERE列表中包含了子查询;
  • DERIVED: 在FROM列表中,包含的子查询被标记为DERIVED(衍生),MySQL会递归执行这些子查询,
    把结果放在临时表里;
  • UNION: 若第二个SELECT出现在UNION之后,则被标记为UNION;若UNION包含在FROM子句的子查询中,
    外侧SELECT将被标记为 DERIVED;
  • UNION RESULT: 从UNION表获取结果的SELECT;
• table: 显示这一行的数据是关于哪张表的;
• type
  • 显示查询使用了何种类型,从最好到最差依次是: system>const>eq_ref>ref>range>index>ALL;
  • 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,将遍历全表以找到匹配的行;
  • 一般来说,得保证查询至少达到range级别,最好能达到ref;
• possible_keys:
  • 显示可能应用在这张表中的索引,一个或多个;查询涉及到的字段上若存在索引,则该索引将被列出,但不一定被查询
    实际使用;
• key:
  • 表示实际使用的索引,如果为NULL,则没有使用索引;
  • 查询中若使用了覆盖索引,则该索引仅出现在key列表中;
• key_len:
  • 表示索引中使用的字节数,可通过该列计算查询中使用的索引的长度;在不损失精确性的情况下,长度越短越好;
    key_len显示的值为索引字段的最大可能长度,而非实际使用长度,即 key_len 是根据表定义计算而得,不是
    通过表内检索出的;
• ref:
  • 显示索引的哪一列被使用了,如果可能的话,是一个常数;说明哪些列或常量被用于查找索引列上的值;
• rows:
  • 根据表统计信息及索引选用情况,大致估算出找到所需的记录所需要读取的行数;
• Extra:
  • 包含不适合在其他列中显示,但十分重要的额外信息;
  • Using filesort: 说明MySQL会对数据使用一个外部的索引排序,而不是按照表内的索引顺序进行读取,
    MySQL中无法利用索引完成的排序操作称为"文件排序";
  • Using temporary: 使用了临时表保存中间结果,MySQL在对查询结果排序时,使用临时表;常见于排序
    order by和分组查询group by;
  • Using index: 表示相应的select操作中使用了覆盖索引(Covering Index),避免访问了表的数据行,效率
    不错!如果同时出现using where,表明索引被用来执行索引键值的查找;如果没有同时出现using where,表明
    索引用来读取数据而非执行查找操作
  • 覆盖索引:就是select的数据列只用从索引中就能够取得,不必读取数据行,MySQL可以利用索引返回select列表
    中的字段,而不必根据索引再次读取数据文件,换句话说,查询列要被所建的索引覆盖;
  • Using where: 表明使用了where过滤;
  • Using join buffer: 使用了连接缓存;
  • impossible where: where 子句的值总是false,不能用来获取任何元组;

2.5 索引优化

• Join语句的优化
  • 尽可能减少Join语句中的NestedLoop的循环总次数,"永远用小结果集驱动大的结果集";
  • 优先优化NestedLoop的内层循环;
  • 保证Join语句中被驱动表上Join条件字段已经被索引;
  • 当无法保证被驱动表的Join条件字段被索引且内存资源充足的前提下,不要太吝啬JoinBuffer的设置;
• 索引失效
  • 全值匹配;
  • 最佳左前缀法则,指的是查询从索引的最左前列开始并且不跳过索引中的列;
  • 不在索引列上做任何操作(计算,函数,(自动or手动)类型转换),会导致索引失效而转向全表扫描;
  • 存储引擎不能使用索引中范围条件右边的列;
  • 尽量使用覆盖索引(只访问索引的查询(索引列或查询列一致)),减少select *;
  • MySQL在使用不等于(!= 或者 <>)的时候,无法使用索引会导致全表扫描;
  • is null, is not null也无法使用索引;
  • like 以通配符开头(%abc...),MySQL索引失效,会变成全表扫描的操作;
    因此,可以使用like abc%,或者使用覆盖索引解决like '%字符串%'索引失效的问题;
  • 字符串不加单引号,索引失效;
  • 少用or,用它来连接时,会导致索引失效;
• 总结:
  • 对于单键索引,尽量选择针对当前Query过滤性更好的索引;
  • 在选择组合索引的时候,当前Query中过滤型最好的字段在索引字段顺序中,位置越靠前越好;
  • 在选择组合索引的时候,尽量选择可以能够包含当前Query中的WHERE子句中更多字段的索引;
  • 尽可能通过分析统计信息和调整Query的写法来达到选择合适索引的目的;
• 优化总结口诀
  • 全值匹配我最爱,最左前缀要遵守;
  • 带头大哥不能死,中间兄弟不能断;
  • 索引列上少计算,范围之后全失效;
  • LIKE百分写最右,覆盖索引不写星;
  • 不等空值还有or,索引失效要少用;
  • VAR引号不可丢,SQL高级也不难!

3. 查询截取分析

3.1 查询优化

• 小表驱动大表,即小的数据集驱动大的数据集;
• ORDER BY关键字优化
  • ORDER BY子句,尽量使用Index方式排序,避免使用FileSort方式排序;
  • 尽可能在索引列上完成排序操作,遵照索引建的最佳左前缀;
  • 如果不再索引列上,FileSort有两种算法:
    • 双路排序:取一批数据,要到磁盘进行两次扫描, MySQL4.1之前;
    • 单路排序
  • 增大"sort_buffer_size"参数的设置;
  • 增大"max_length_for_sort_data"参数的设置;
• GROUP BY关键字优化
  • GROUP BY实质是先排序后进行分组,遵照索引建的最佳左前缀;
  • 当无法使用索引列时,增大max_length_for_sort_data参数的设置,增大sort_buffer_size参数的设置;
  • WHERE性能高于HAVING,能写在WHERE限定的条件,就不要去HAVING限定了;

3.2 慢查询日志

• MySQL的慢查询日志是MySQL提供的一种日志记录,它用来记录在MySQL中响应时间超过阈值的语句,具体指运行时间
  超过long_query_time值的SQL,则会被记录到慢查询日志中;
• long_query_time 的默认值为10,即运行10秒以上的语句;
• MySQL数据库默认没有开启慢查询日志,可以使用SHOW VARIABLES LIKE '%slow_query_log%';查看;
• 开启慢查询日志:set global slow_query_log=1;,只对当前数据库生效,如果MySQL重启后,则会失效;
• SHOW VARIABLES LIKE 'long_query_time%'; 查看系统默认慢的阈值时间;
• set global long_query_time=3; 设置慢的阈值时间;
• 日志分析工具mysqldumpslow;

3.3 Show Profile

• 是MySQL提供的可以用来分析当前会话中语句执行的资源消耗情况,可以用于SQL的调优的测量;
• show variables like 'profiling';默认为关闭状态;
• set profiling=on; 设置为开启;

4. MySQL 锁机制

• 表锁(偏向MyISAM存储引擎)
  • MyISAM 在执行查询语句(SELECT)前,会自动给涉及的所有表加读锁,在执行增删改操作前,会自动给涉及
    的表加写锁;
  • 对MyISAM表的读操作(加读锁),不会阻塞其他进程对同一表的读请求,但会阻塞对同一表的写请求;只有当读锁
    释放后,才会执行其他进程的写操作;
  • 对MyISAM表的写操作(加写锁),会阻塞其他进程对同一表的读和写操作,只有当写锁释放后,才会执行其他进程
    的读写操作;
  • 简而言之,读锁会阻塞写,但是不会阻塞读;而写锁则会把读和写都阻塞;
  • show open tables;查看哪些表被加锁了;
• 行锁(偏向InnoDB存储引擎)
  • show status like 'innodb_row_lock%';: 通过检查InnoDB_row_lock状态变量,来分析系统上的行锁
    争夺情况;
• 优化建议:
  • 尽可能让所有数据检索都通过索引来完成,避免无索引行锁升级为表锁;
  • 合理设计索引,尽量缩小锁的范围;
  • 尽可能减少索引条件,避免间隙锁;
  • 尽量控制事务大小,减少锁定资源量和时间长度;
  • 尽可能低级别事务隔离;

5. 主从复制

• MySQL 复制过程分为三步:
  • master将改变记录到二进制日志(binary log),这些记录过程叫做二进制日志事件,binary log events;
  • slave 将 master 的 binary log events 拷贝到它的中继日志(relay log);
  • slave 重做中继日志中的事件,将改变应用到自己的数据库中,MySQL复制是异步的且串行化的;
• 复制的基本原则
  • 每个slave只有一个master;
  • 每个slave只能有一个唯一的服务器ID;
  • 每个master可以有多个slave;
• 复制的最大问题: 延时;

---

参考资料

• MySQL索引原理及慢查询优化
• MySQL分库分表方案
• MySQL 行转列 列转行