MySQL

MySQL高级优化

架构

配置

Windows：my.ini

Lunix：/etc/my.cnf

修改字符集

• create database 库名 character set utf8：创建数据库，顺便执行字符集为utf-8

• show create database 库名：查看数据库的字符集

• show variables like '%char%'：查询所有跟字符集相关的信息

• et [字符集属性]=utf8：设置相应的属性为 utf8

  • 只是临时修改，服务重启后会失效

• alter database 库名 character set 'utf8'：修改数据库的字符集

• alter table 表名 convert to character set 'utf8'：修改表的字符集

• 永久修改

  • [client]
    default-character-set=utf8 
    [mysqld] 
    character_set_server=utf8 
    character_set_client=utf8 
    collation-server=utf8_general_ci 
    [mysql] 
    default-character-set=utf8
    **二进制日志log-bin**
    
    - 主从复制
    
    **错误日志log-error**
    
    - 记录验证警告和错误信息，每次启动和关闭的详细信息等
    - 默认关闭
    
    **查询日志log**
    
    - 记录查询的SQL语句，若开启会降低MySQL的整体性能（记录日志也需要消耗系统资源）
    - 默认关闭
    
    **数据文件**
    
    - 两系统
      - Windows：..\MySQL\data，可以挑选很多库
      - Linux：默认路径/var/lib/mysql
    - frm文件：存放表结构
    - myd文件：存放数据
    - myi文件：存放索引
    
    ### 逻辑架构
    
    它的架构可以在多种不同场景中应用并发挥良好作用，主要体现在存储引擎的架构上，**插件式的存储引擎架构将查询处理和其它的系统任务以及数据的存储提取相分离**。这种架构可以根据业务的需求和实际需要选择合适的存储引擎
    
    #### 连接层
    
    最上层是一些客户端和连接服务，包含本地sock通信和大多数基于客户端/服务端工具实现的类似于tcp/ip 的通信。主要完成一些类似于连接处理、授权认证、及相关的安全方案。在该层上引入了线程池的概念，为通过认证安全接入的客户端提供线程。同样在该层上可以实现基于SSL的安全链接。服务器也会为安全接入的每个客户端验证它所具有的操作权限
    
    #### 服务层
    
    **组件**
    
    - Management Serveices & Utilities：系统管理和控制工具
    - SQL Interface：SQL接口。
      - 接受用户的SQL命令，并且返回用户需要查询的结果。比如select from就是调用SQL Interface
    - Parser：解析器
      - SQL命令传递到解析器的时候会被解析器验证和解析
    - Optimizer：查询优化器
      - SQL语句在查询之前会使用查询优化器对查询进行优化，比如有where条件时，优化器来决定先投影还是先过滤
    - Cache和Buffer：查询缓存
      - 如果查询缓存有命中的查询结果，查询语句就可以直接去查询缓存中取 数据。这个缓存机制是由一系列小缓存组成的。比如表缓存，记录缓存，key 缓存， 权限缓存等
    
    #### 引擎层
    
    存储引擎层，存储引擎真正的负责了MySQL中数据的存储和提取，服务器通过 API 与存储引擎进行通信。不同的存储引擎具有的功能不同，这样我们可以根据自己的实际需要进行选取
    
    **查看所有的数据库引擎**：`show engines`
    
    **查看默认的数据库引擎**：`show variables like '%storage_engine%'`
    
    | 对比项 | MyISAM                                                       | InnoDB                                                       |
    | ------ | ------------------------------------------------------------ | ------------------------------------------------------------ |
    | 外键   | 不支持                                                       | 支持                                                         |
    | 事务   | 不支持                                                       | 支持                                                         |
    | 行表锁 | **表锁**，即使操作一条记录也会锁住整个表， 不适合高并发的操作 | **行锁**,操作时只锁某一行，不对其它行有影响， **适合高并发**的操作 |
    | 缓存   | 只缓存索引，不缓存真实数据                                | 不仅缓存索引还要缓存真实数据，对内存要求较高，而且内 存大小对性能有决定性的影响 |
    | 关注点          | 读性能 | 并发写、事务、资源 |
    | 默认安装        | Y      | Y                  |
    | 默认使用        | N      | Y                  |
    | 自带系统表使用 | Y      | N                  |
    
    #### 存储层
    
    数据存储层，主要是将数据存储在运行于裸设备的文件系统之上，并完成与存储引擎的交互
    
    ## 搜索优化
    
    性能下降，SQL慢：
    
    - 现象：
      - 执行时间长
      - 等待时间长
    - 原因：
      - 查询语句写的烂
      - 索引失效
      - 关联查询太多Join（设计缺陷或不得已的需求）
      - 服务器调优及各个参数设置（缓冲、线程数等）
    
    ### Join
    
    **执行顺序**
    
    - 手写顺序
    
      ```mysql
      SELECT DISTINCT <select_list>
      FROM <left_table> <join_type> JOIN <right_table> ON <join_condition>
      WHERE <where_condition>
      GROUP BY <group_by_list>
      HAVING <having_condition>
      ORDER BY <order_by_condition>
      LIMIT <limit_number>
    

• 机读顺序

  FROM <left_table>
  ON <join_condition>
  <join_type> JOIN <right_table>
  WHERE <where_condition>
  GROUP BY <group_by_list>
  HAVING <having_condition>
  SELECT
  DISTINCT <select_list>
  ORDER BY <order_by_condition>
  LIMIT <limit_number>

• 总结

      																										  offset
      笛卡尔积	不符合的ON也添加		改变对表引用			DISTINCT		 rows
       FROM			 JOIN				    GROUP BY				SELECT			LIMIT
  			|					|					    		|	 						|						|
  SQL解析++--------+-----------------+---+---------+---+-------+
  			 |				|								 			|							|
  			 ON			 WHERE								HAVING				ORDER BY
  		 主表保留		 非聚集						  只作用分组后		可使用SELECT别名
  		 					 非SELSCT别称

Join

# 内连接：A [ (#] ) B
SELECT <select_list> FROM TableA A INNER JOIN TableB B ON A.Key = B.Key

# 左连接：A [#(#] ) B
SELECT <select_list> FROM TableA A LEFT JOIN TableB B ON A.Key = B.Key

# 右连接：A [ (#]#) B
SELECT <select_list> FROM TableA A RIGHT JOIN TableB B ON A.Key = B.Key

# A [#( ] ) B
SELECT <select_list> FROM TableA A LEFT JOIN TableB B ON A.Key = B.Key WHERE B.Key IS NULL

# A [ ( ]#) B
SELECT <select_list> FROM TableA A RIGHT JOIN TableB B ON A.Key = B.Key WHERE A.Key IS NULL

# 全连接：A [#(#]#) B
# MySQL不支持
SELECT <select_list> FROM TableA A FULL OUTER JOIN TableB B ON A.Key = B.Key
# 解决方案
SELECT <select_list> FROM TableA A LEFT JOIN TableB B ON A.Key = B.Key
UNION
SELECT <select_list> FROM TableA A RIGHT JOIN TableB B ON A.Key = B.Key

# A [#( ]#) B
# MySQL不支持
SELECT <select_list> FROM TableA A FULL OUTER JOIN TableB B ON A.Key = B.Key WHERE A.Key IS NULL OR B.Key IS NULL
# 解决方案
SELECT <select_list> FROM TableA A LEFT JOIN TableB B ON A.Key = B.Key WHERE B.Key IS NULL
UNION
SELECT <select_list> FROM TableA A RIGHT JOIN TableB B ON A.Key = B.Key WHERE A.Key IS NULL

索引

简介

是帮助 MySQL 高效获取数据的数据结构，索引的本质：索引是数据结构

可以简单理解为排好序的快速查找数据结构

在数据之外数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据

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

若没有特别指明都指B树（多路搜索树，并不一定是二叉的）结构组织的索引，其中聚集索引，次要索引，覆盖索引，复合索引，前缀索引，唯一索引默认使用B+树索引，统称索引，此外还有哈希索引（Hash Index）

建议一张表索引最多不超过5个

优势

• 提高数据检索效率，降低数据库IO成本
• 通过索引对数列进行排序，降低数据排序成本和CPU消耗

劣势

• 索引实际上也是一张保存了主键和索引字段并指向实体表记录的表，会占用空间
• 会降低更新表的速度（INSERT/UPDATE/DELETE），更新表时不仅要保存数据还要报错索引文件每次更新添加了索引列的字段，都会调整因为更新所带来的键值变化后的索引信息
• 若有大量数据的表就需要花时间研究建立最优秀的索引或优化查询

分类

• 单值索引：一个索引只包含一个单例，一个表可有多个单例索引
• 唯一索引：索引列的值必须唯一，但允许有空值
• 复合索引：一个索引包含多个列

语法

• 创建

  CREATE [UNIQUE] INDEX indexName ON mytable(columname(length))
  
  ALTER mytable ADD [UNIQUE] INDEX [indexName] ON mytable(columname(length))

• 删除

  DROP INDEX [indexName] ON mytable

• 查看

  SHOW INDEX FROM table_name

• ALTER命令

  # 添加一个主键，索引值必须唯一且不为NULL
  ALTER TABLE tbl_name ADD PRIMARY KEY (column_list)
  
  # 添加一个唯一索引
  ALTER TABLE tbl_name ADD UNIQUE index_name (column_list)
  
  # 添加一个普通索引
  ALTER TABLE tbl_name ADD INDEX index_name (column_list)
  
  # 指定索引为FULLTEXT，用于全文索引
  ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list)

结构

• BTree：

  											   +----------+
  											 	 | 17 || 35 |	
  											   |P1||P2||P3|	
  											   |  磁盘块2  |	
      									   +----------+
  										↙        ↓         ↘
   			 +----------+		   +----------+       +----------+
         | 8  || 12 |		   | 26 || 38 |		    | 65 || 87 |
         |P1||P2||P3|		   |P1||P2||P3|		    |P1||P2||P3|
  	     |  磁盘块2  |		   |  磁盘块3  |		    |  磁盘块4  |
  	     +----------+			 +----------+       +----------+
   	     ↙     ↓    ↘           ↓            ↙     ↓     ↘
  +------+ +------+ +------+ +------+ +------+ +-------+ +-------+
  |3 ||5 | |9 ||10| |13||15| |28||29| |36||60| |75| |79| |90| |99|
  |磁盘块5| |磁盘块6| |磁盘块7| |磁盘块8| |磁盘块9| |磁盘块10| |磁盘块11|
  +------+ +------+ +------+ +------+ +------+ +-------+ +-------+

  • 叶子结点存放真实数据：3，5，9，10，13，15……
  • 非叶子结点只存储指引搜索方向的数据项（不是真实数据）：17，35……
  • 三层的B+树可以容纳上百万的数据，性能提高巨大

• B+Tree：

  +----+											   +----------+
  |DATA|											 	 |5 ||28||65|	
  +----+											   |P1||P2||P3|	
    ↓ 									         +----------+
  	↓								      	↙          ↓          ↘
    ↓			  +----------+		     +----------+         +----------+
    ↓       |5 ||10||20|         |28||35||56|         |65||80||90|
    ↓       |P1||P2||P3|		     |P1||P2||P3|		      |P1||P2||P3|
  	↓       +----------+			   +----------+         +----------+
    ↓	      ↙     ↓    ↘        ↙     ↓      ↘        ↙    ↓     ↘
    ↓	   +--+   +--+   +--+   +--+   +--+   +--+   +--+   +--+   +--+
    ↓	   |5 |   |10|   |20|   |28|   |35|   |56|   |65|   |80|   |90|
    ↓ 	 |8 |   |15|   |26|   |30|   |38|   |60|   |73|   |85|   |96|
    ↓	   |9 |   |18|   |27|   |33|   |50|   |63|   |79|   |88|   |99|
    ↳ → →|Q | → |Q | → |Q | → |Q | → |Q | → |Q | → |Q | → |Q | → |Q |
   	     +--+   +--+   +--+   +--+   +--+   +--+   +--+   +--+   +--+

  • B树的关键字和记录是放在一起的，叶子节点可以看作外部节点，不包含任何信息
  • B+树的非叶子节点中只有关键字和指向下一个节点的索引，记录只放在叶子节点中

• Hash

• Full-Text全文索引

• RTree

需要建立索引的情况

• 主键自动建立唯一索引
• 频繁作为查询条件的字段
• 查询中与其它表关联的字段，外间关系建立索引
• 高并发下倾向创建组合索引
• 查询中排序的字段：考虑会不会与Order By排序诉求撞车
• 统计或分组字段

不要建立索引的情况

• 表记录太少
• 频繁更新的字段：每次更新数据时还会更新索引
• Where条件里用不到的字段
• 数据重复且分布平均的字段

分析

MySql Query Optimizer

• 专门负责优化SELECT语句的优化器模块
• 通过计算分析系统中收集到的统计信息为客户端请求的Query提供它认为最优的执行计划，这部分可能最耗时间

MySQL常见瓶颈

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

Explain

• 使用EXPLAIN关键字可以模拟优化器执行SQL查询语句从而知道MySQL是如何处理你的SQL语句，可用来分析你的查询语句或是表结构的性能瓶颈

• 作用

  • 表的读取顺序：id
  • 数据读取操作的操作类型：select_type
  • 哪些索引可以使用：possible_keys
  • 哪些索引被十几使用：key
  • 表之间的引用：ref
  • 每张表有多少行被优化器查询：rows

• 命令

  • Explain + SQL语句

• 返回信息

  • *id：select查询的序列号，包含一组数字，表示查询中执行select子句或操作表的顺序

    • id相同：执行顺序由上至下
    • id不同：如果是子查询id的序号会递增，id值越大优先级越高越先被执行
    • 有相同也有不同：id如果相同可以认为是一组，从上往下顺序执行
      • 在所有组中id值越大，优先级越高越先执行衍生（DERIVED）

  • select_type：查询的类型

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

      EXPLAIN SELECT * FROM t1
      # 1: t1: SIMPLE

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

      EXPLAIN SELECT * FROM (SELECT t1.content FROM t1) a
      # 2: t1: DERIVED
      # 1: <derived2>: PRIMARY

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

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

      EXPLAIN SELECT t2.id FROM t2 WHERE t2.id = (SELECT t3.id FROM t3 WHERE t3.id = 1)
      # 2: t3: SUBQUERY
      # 1: t2: PRIMARY

    • UNION：若第二个SELECT出现在UNION之后，则被标记为UNION

      • 若UNION包含在FROM子句的子查询中，外层SELECT将被标记为DERIVED

      EXPLAIN SELECT t2.id, t2.content FROM t2
      UNION ALL
      SELECT t3.id, t3.content FROM t3
      # 2: t3: UNION
      # 1: t2: PRIMARY
      # NULL: <union1,2>: UNION RESULT

    • UNION RESULT：从UNION表获取结果的SELECT

  • table：数据来自哪张表

  • *type：访问类型，从好到坏依次为（一般来说得保证查询至少达到range级别，最好能达到ref）

    1. system：表只有一行记录（等于系统表），是const类型的特列，平时不会出现，可以忽略不计

    2. const：表示通过索引一次就找到了，用于比较primary key或unique索引，因为只匹配一行数据所以很快，单表

       • 如将主键置于where列表中MySQL就能将该查询转换为一个常量

       EXPLAIN SELECT * FROM (SELECT * FROM t1 WHERE t1.id = 1) a
       # 2: t1: const
       # 1: <derived2>: system

    3. eq_ref：唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配，常见于主键或唯一索引扫描，多表

       EXPLAIN SELECT * FROM t1, t2 WHERE t1.id = t2.id
       # 1: t2: ALL
       # 1: t1: eq_ref

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

       • 本质上也是一种索引访问，它返回所有匹配某个单独值的行， 然而它可能会找到多个符合条件的行，所以他应该属于查找和扫描的混合体

       CREATE INDEX idx_ctnt ON t2(content);
       EXPLAIN SELECT * FROM t1, t2 WHERE t1.content = t2.content
       # 1: t1: ALL
       # 1: t2: ref

    5. range：只检索给定范围的行，key列显示使用了哪个索引

       • 一般就是在where语句中出现between、<、>、in等的查询这种范围扫描索引扫描比全表扫描要好，因为它只需要开始于索引的某一点结束于另一点，不用扫描全部索引

       EXPLAIN SELECT * FROM t1 WHERE t1.id < 10
       # 1: t1: range

    6. index：Full Index Scan，全索引扫描

       • index只遍历索引树，因为索引文件通常比数据文件小所以通常比ALL快（index从索引读取，ALL从硬盘中读取）

       EXPLAIN SELECT id FROM t1
       # 1: t1: index

    7. ALL：Full Table Scan，全表扫描，将遍历全表以找到匹配的行

       EXPLAIN SELECT * FROM t1
       # 1: t1: ALL

  • possible_keys：可能应用在此表中的索引，一或多个，不一定被查询实际使用

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

    • 若查询中若使用覆盖索引，则索引仅出现在Key列表中

      • 覆盖索引：查询的字段和所建的复合索引的个数顺序一致

      CREATE INDEX idx_col1_col2 ON t2(col1, col2);
      EXPLAIN SELECT col1, col2 from t1
      # id	table		type		possible_keys		key
      # 1		t1			index		NULL						idx_col1_col2

  • key_len：索引字段的最大可能长度，并非实际使用长度，不损失精度的情况下越短越好

    EXPLAIN SELECT * FROM t1 WHERE col1 = 'ab'
    # id	table		type		possible_keys		key							key_len
    # 1		t1			ref			idx_col1_col2		idx_col1_col2		13
    EXPLAIN SELECT * FROM t1 WHERE col1 = 'ab' AND col2 = 'ac'
    # id	table		type		possible_keys		key							key_len
    # 1		t1			ref			idx_col1_col2		idx_col1_col2		26

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

    EXPLAIN SELECT * FROM t1, t2 WHERE t1.col1 = t2.col1 AND t1.col2 = 'ac'
    # id	table	type	p_keys		key				ref
    # 1		t2		ALL		NULL			NULL			NULL
    # 1		t1		ref		idx_c1_c2	idx_c1_c2	shared.t2.col1.const
    # col1匹配t2表的col1，col2匹配一个常量‘ac’

  • *rows：MySQL认为它执行查询时必须检查的行数，越少越好

  • *Extra：其他的额外重要的信息

    • Using filesort：MySQL中无法利用索引完成的排序操作称为“文件排序”
      • 说明MySQL会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取
    • Using temporary：使了用临时表保存中间结果，常见于排序order by和分组查询group by
    • Using index：代表表示相应的select操作中使用了覆盖索引（Covering Index），避免访问了表的数据行，效率不错！
      • 同时出现using where：索引被用来执行索引键值的查找
      • 没有同时出现using where：索引只用来读取数据而非利用索引执行查找
    • Using where：表明使用了where过滤
    • Using join buffer：使用了连接缓存
    • impossible where：where子句的值总是false，不能用来获取任何元组
    • select tables optimized away：在没有GROUPBY子句的情况下基于索引优化 MIN/MAX操作或者对于MyISAM存储引擎优化COUNT(*)操作
    • distinct：优化distinct操作，在找到第一匹配的元素后停止查找同样的值

案例

EXPLAIN SELECT d1.name, (SELECT id FROM t3) d2 FROM (SELECT id, name FROM t1 WHERE other_column = '') d1
UNION
(SELECT name, id FROM t2)

# id	s_type	table		type	p_keys	key		k_len	ref		rows	Extra
# 1 	Primary	<der3>	sys		NULL		NULL	NULL	NULL	1			
# 3		DERIVD	t1			ALL 	NULL		NULL	NULL	NULL	1			U where
# 2 	SUBQUEY	t3			index	NULL 		PRI		4			NULL	1			U index
# 4 	UNION		t2			ALL		NULL		NULL	NULL	NULL	1
# NU	U REST	<u1,4>	ALL		NULL		NULL	NULL	NULL	NULL

• 执行顺序
  1. SELECT name, id FROM t2
  2. SELECT id, name FROM t1 WHERE other_column = ''
  3. SELECT id FROM t3
  4. SELECT d1.name ...
  5. 两个结果UNION操作

优化

分析

• 范围后的索引会失效

  • 按照BTree索引工作原理，先排序category_id，若遇到相同值再排序comments，若遇到相同值再排序views
  • 当comments字段为范围值且在联合索引处于中间的位置时MySQL无法利用索引再对后面的views部分进行检索

  # 查询category_id为1且comments大于1的情况下views最火的article_id
  explain
  select id, author_id
  from article
  where category_id = 1
    and comments > 1
  order by views desc
  limit 1;
  # 1,SIMPLE,article,,ALL,,,,,3,33.33,Using where; Using filesort
  
  # 添加category_id, comments, views复合索引
  alter table article
      add index idx_article_ccv (category_id, comments, views);
  # 1,SIMPLE,article,,range,idx_article_ccv,idx_article_ccv,8,,1,100,Using index condition; Using filesort
  explain
  select id, author_id
  from article
  where category_id = 1
    and comments = 1
  order by views desc
  limit 1;
  # 1,SIMPLE,article,,ref,idx_article_ccv,idx_article_ccv,8,"const,const",1,100,Backward index scan
  # 范围后的索引会失效，comments = 1不会失效
  
  # 删除category_id, comments, views复合索引
  drop index idx_article_ccv on article;
  
  # 添加category_id, views复合索引
  alter table article
      add index idx_article_cv (category_id, views);
  # 1,SIMPLE,article,,ref,idx_article_cv,idx_article_cv,4,const,2,33.33,Using where; Backward index scan

• 左连接时索引加在右表上

  • 由于左连接特性，LEFT JOIN条件用于确定如何从右表搜索行，左边一定都有
  • 右边才是关键点，一定需要建立索引
  • 同理，右连接加左表上

  explain
  select *
  from class
           left join book on class.card = book.card;
  # 1,SIMPLE,class,,ALL,,,,,20,100,
  # 1,SIMPLE,book,,ALL,,,,,20,100,Using where; Using join buffer (hash join)
  
  # 为右表添加索引
  alter table book
      add index idx_right (card);
  # 1,SIMPLE,class,,ALL,,,,,20,100,
  # 1,SIMPLE,book,,ref,idx_right,idx_right,4,pz_mysql.class.card,1,100,Using index
  
  # 删除右表索引
  drop index idx_right on book;
  
  # 为左表添加索引
  alter table class
      add index idx_left (card);
  # 1,SIMPLE,class,,index,,idx_left,4,,20,100,Using index
  # 1,SIMPLE,book,,ALL,,,,,20,100,Using where; Using join buffer (hash join)

• 索引最好设置在需要经常查询的字段中

  explain
  select *
  from class
           left join book on class.card = book.card
           left join phone on book.card = phone.card;
  # 1,SIMPLE,class,,ALL,,,,,20,100,
  # 1,SIMPLE,book,,ALL,,,,,20,100,Using where; Using join buffer (hash join)
  # 1,SIMPLE,phone,,ALL,,,,,20,100,Using where; Using join buffer (hash join)
  
  # 为book和phone分别建立索引
  alter table book
      add index idx_book_card (card);
  alter table phone
      add index idx_phone_card (card);
  # 1,SIMPLE,class,,ALL,,,,,20,100,
  # 1,SIMPLE,book,,ref,idx_book_card,idx_book_card,4,pz_mysql.class.card,1,100,Using index
  # 1,SIMPLE,phone,,ref,idx_phone_card,idx_phone_card,4,pz_mysql.book.card,1,100,Using index

• 总结

  • 尽可能减少Join语句中NestedLoop循环总次数，永远用小表驱动大表
  • 优先优化NestedLoop的内层循环
  • 保证Join语句中被驱动表上Join条件字段已经被索引
  • 无法保证被驱动表的Join条件字段被索引且内存资源充足的前提下，不要太吝啬JoinBuffer的设置

索引失效

• 全值匹配最佳
• 最佳左前缀法则：查询从索引的最左前列开始且不跳过索引中的列
• 不在索引列上做任何操作（计算/函数/自动手动类型转换），会导致索引失效而转向全表扫描
• 存储引擎不能使用索引中范围条件右边的列
  • 范围查询着重于排序，也会用到索引，但范围后面的索引会失效
• 尽量使用覆盖索引（只访问索引的查询（索引列和查询列一致）），减少SELECT *
• MySQL在使用不等于/大于/下于时无法使用索引导致全表扫描
• IS NULL，IS NOT NULL无法使用索引
• LIKE 以通配符开头（’%abc…’）无法使用索引导致全表扫描
  • 若要使用’%abc%’可以使用覆盖索引解决：建立的索引覆盖查询字段，若顺序个数一致更佳
  • LIKE ‘abc%’后面的索引也会用到
• 字符串不加单引号索引失效
• 用OR连接索引失效，需少用
• 定值范围还是怕需要，一般order by给个范围
• group by基本上都需要进行排序，会产生临时表

一般性建议

• 对于单建索引尽量选择正对当前query过滤性能更好的索引
• 选择组合索引时当前query过滤性能最好的字段位置越靠前越好
• 选择组合索引时尽量选择能包含当前query中where子句中更多字段的索引
• 尽量通过分析统计信息和调整query的写法来达到选择合适索引的目的

总结

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

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

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

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

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

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

查询截取

优化

优化流程

1. 慢查询开启并捕获
2. EXPLAIN + 慢SQL分析
3. SHOW PROFILE查询SQL在MySQL服务器中执行细节和声明周期情况
4. SQL数据库服务器的参数调优

永远小表驱动大表

• EXISTS：SELECT * FROM table WHERE EXISTS (subquery)
  • 将主查询的数据放到子查询中做条件验证，根据验证结果（TRUE/FALSE）决定主查询的数据结果是否得以保留
  • EXISTS (subquery)只返回TRUE/FALSE，实际执行时会忽略SELECT清单

# B表数据集必须小于A表时，in优于exists
select * from A where id in (select id from B);
# 等价于
for select id from B;
for select * from A where A.id = B.id;

# A表数据集必须小于B表时，exists优于in
select * from A where exists(select * from B where B.id = A.id);
# 等价于
for select * from A;
for select * from B where B.id = A.id;

ORDER BY优化

• 尽量使用Index遵循最佳左前缀方式排序，避免使用FileSort方式

  • 满足两种情况会使用Index排序：
    • ORDER BY使用索引最左前列
    • 使用WHERE子句与ORDER BY子句条件列组合满足索引最左前列

• FileSort算法：

  • 双路排序：
    • 两次扫描磁盘得到数据，读取行指针和ORDER BY列并对他们进行排序，然后扫描已经排序好的列表，按照列表中的值重新从列表中读取对应的数据输出
    • 从磁盘取排序字段，在buffer进行排序，再从磁盘取其他字段
  • 单路排序：
    • 从磁盘读取查询需要的所有列，按照ORDER BY列在buffer对它们进行排序，然后扫描排序后的列表进行输出， 它的效率更快一些但是会使用更多的空间
    • 总体好过双路但也可能有问题：sort_butter不够时得不偿失

• 优化策略

  • ORDER BY最好同升同降

  • ORDER BY时不要SELECT *

  • 如果WHERE使用索引的最左前缀定义为常量则ORDER BY可以使用索引

    # 可以使用索引
    WHERE a = const AND b > const ORDER BY b, c

  • 增大sort_butter_size参数的设置

  • 增大max_length_for_sort_data参数的设置

GROUP BY优化

• 使用索引的原则几乎跟ORDER BY一致 ，唯一区别是GROUP BY即使没有过滤条件用到索引，也可以直接使用索引
• ORDER BY实质是先排序后分组，遵循索引最佳左前缀
• 无法使用索引列时增大sort_butter_size和max_length_for_sort_data参数的设置
• WHERE高于HAVING，能在WHERE限定的条件不要去HAVING限定

慢查询日志

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

long_query_time的默认值为10，意思是运行10秒以上的语句

默认不开启慢查询日志，如果不是调优需要的话一般不建议启动该参数

命令

• SHOW VARIABLES LIKE '%slow_query_log%'：查看慢查询日志是否开启

  • 默认情况下slow_query_log的值为 OFF， 表示慢查询日志是禁用的

• SET GLOBAL slow_query_log=1：开启慢查询日志

  • 只对当前数据库生效且重启后失效

• SHOW VARIABLES LIKE 'long_query_time%'：查看慢查询设定阈值，单位秒

• SET long_query_time=1：设定慢查询阈值，单位秒

  • 需要重新连接或新开会话才能看到修改值

• SHOW GLOBAL STATUS LIKE '%Slow_queries%'：显示慢日志的数量

• 如永久生效需要修改配置文件my.cnf中[mysqld]下配置

  [mysqld]
  slow_query_log=1 
  slow_query_log_file=/var/lib/mysql/mousse-slow.log 
  long_query_time=3
  log_output=FILE

mysqldumpslow

• 日志分析工具

• 命令参数：

  • -s：按照何种方式排序
  • c：访问次数
  • l：锁定时间
  • r：返回记录
  • t：查询时间
  • al：平均锁定时间
  • ar：平均返回记录数
  • at：平均查询时间
  • -t：即为返回前面多少条的数据
  • -g：后边搭配一个正则匹配模式，大小写不敏感的

  # 得到返回记录集最多的 10 个 SQL
  mysqldumpslow -s r -t 10 /var/lib/mysql/atguigu-slow.log
  # 得到访问次数最多的 10 个 SQL
  mysqldumpslow -s c -t 10 /var/lib/mysql/atguigu-slow.log
  # 得到按照时间排序的前 10 条里面含有左连接的查询语句 
  mysqldumpslow -s t -t 10 -g "left join" /var/lib/mysql/atguigu-slow.log
  # 另外建议在使用这些命令时结合 | 和 more 使用 ，否则有可能出现爆屏情况 
  mysqldumpslow -s r -t 10 /var/lib/mysql/atguigu-slow.log | more

批量数据脚本

创建函数报错：This function has none of DETERMINISTIC

• 由于开启过慢查询，开启了bin-log，必须为function指定一个参数：

  • SHOW VARIABLES LISK 'log_bin_trust_fuction_creators'
  • SET GLOBAL log_bin_trust_fuction_creators=1

• 永久设置方法：

  [mysqld]
  log_bin_trust_fuction_creators=1

# P48
create table dept
(
    id     int unsigned primary key auto_increment,
    deptno mediumint unsigned not null default 0,
    dname  varchar(20)        not null default '',
    loc    varchar(13)        not null default ''
) engine = innodb
  default charset = GBK;

CREATE TABLE emp
(
    id       int unsigned primary key auto_increment,
    empno    mediumint unsigned not null default 0,
    ename    varchar(20)        not null default '',
    job      varchar(9)         not null default '',
    mgr      mediumint unsigned not null default 0,
    hiredate date               not null,
    sal      decimal(7, 2)      not null,
    comm     decimal(7, 2)      not null,
    deptno   mediumint unsigned not null default 0
) ENGINE = INNODB
  DEFAULT CHARSET = GBK;

# 新建函数，获得随机字符串
# 定义结束符号
DELIMITER $$
CREATE FUNCTION rand_string(n INT) RETURNS VARCHAR(255)
BEGIN
    DECLARE chars_str VARCHAR(100) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';
    DECLARE return_str VARCHAR(255) DEFAULT '';
    DECLARE i INT DEFAULT 0;
    WHILE i < n
        DO
            SET return_str = CONCAT(return_str, SUBSTRING(chars_str, FLOOR(1 + RAND() * 52), 1));
            SET i = i + 1;
        END WHILE;
    RETURN return_str;
END $$

# 用于随机产生部门编号
CREATE FUNCTION rand_num() RETURNS INT(5)
BEGIN
    DECLARE i INT DEFAULT 0;
    SET i = FLOOR(100 + RAND() * 10);
    RETURN i;
END $$

# 创建存储过程
delimiter $$
create procedure insert_emp(in start int(10), in max_num int(10))
begin
    declare i int default 0;
#     关闭自动提交
    set autocommit = 0;
    repeat
        set i = i + 1;
        insert into emp(empno, ename, job, mgr, hiredate, sal, comm, deptno)
        values ((start + i), ran_string(6), 'salesman', 0001, curdate(), 2000, 400, rand_num());
    until i = max_num
        end repeat;
    commit;
end $$

delimiter $$
create procedure insert_dept(in start int(10), in max_num int(10))
begin
    declare i int default 0;
    set autocommit = 0;
    repeat
        set i = i + 1;
        insert into dept(deptno, dname, loc) values ((start + i), ran_string(10), ran_string(8));
    until i = max_num
        end repeat;
    commit;
end $$

# 调用存储过程
delimiter ;
call insert_dept(100, 10);
call insert_emp(100001, 500000);

Show Profile

用来分析当前会话中语句执行的资源消耗情况

默认参数关闭并保存最近15次运行结果

步骤

1. SHOW VARIABLES LIKE 'profiling%'：查看状态
2. SET profiling=on：打开
3. 运行SQL
4. SHOW profils：查询结果
5. SHOW profiling CPU, BLOCK IO FOR QUERY <Query_ID>：诊断SQL
6. 需要注意的结论：
   • converting HEAP to MyISAM：查询结果太大，内存不够用了往磁盘上搬
   • creating tmp table：创建临时表
     • 拷贝数据到临时表用完再删
   • copying to tmp table on disk：把内存中临时表复制到磁盘，危险
   • locked

全局查询日志

只允许在测试环境用，绝不可以在生产环境用

配置启用

# 开启
general_log=1
# 记录日志文件路径
general_log_file=/path/logfile
# 输出格式
log_output=FILE

编码启用

1. SET GLOBAL general_log=1
2. SET GLOBAL log_output='TABLE'：此后编写的SQL语句都会记录在general_log表
3. SELECT * FROM mysql.general_log

锁机制

对数据操作的类型分

• 读锁：共享锁，对一份数据多个读操作可以同时进行而不会互相影响
• 写锁：排它锁：当前写操作没有完成前会阻断其它的读锁和写锁

对数据操作的粒度分

• 表锁
  • 偏读
  • 偏向MyISAM存储引擎，开销小，加锁快，无死锁，锁定粒度大，发生锁冲突的概率最高，并发度最低
  • 加读锁
    • LOCK TABLES 表名 read, 表名2 read, 其它
    • UNLOCK TABLES
    • Session1锁Table1时：
      • Session1：可查看Table1，更新Table1时会报错，不能查其它表，除非解锁
      • Session2：可查看Table1，更新Table1时会阻塞，除非Session1解锁
    • 阻塞写
  • 加写锁
    • LOCK TABLES 表名 write, 表名2 write, 其它
    • UNLOCK TABLES
    • Session1锁Table1时：
      • Session1：可查看Table1，可以更新Table1，不能查其它表，除非解锁
      • Session2：查看Table1会阻塞，除非Session1解锁
    • 阻塞读
  • 命令
    • SHOW OPEN TABLES：查看哪些表被加锁
    • SHOW STATUS LIKE 'table%'：分析表锁定
      • Table_locks_immediate：产生表级锁定的次数，可以立即获取锁的查询次数，每立即获取锁值加1
      • Table_locks_waited：出现表级锁定争用而发生等待的次数，不能立即获取锁的次数，每等待一次锁值加1
  • MyISAM读写锁调度为写优先，所以不适合做写为主表的引擎
• 行锁
  • 偏写
  • 偏向InnoDB存储引擎，开销大，加锁慢，会出现死锁，锁定粒度小，发生锁冲突的概率最低，并发度最高
    • InnoDB支持事务，采用行锁
      • 事务并发带来的问题：
        • 更新丢失：多个事务同时操作一行时最后更新覆盖其它事务的更新
          • 可解决问题的隔离级别：未提交读
            • 最低级别，只能保证不读区物理上损坏的数据
        • 脏读：事务A读到事务B已修改未提交的数据
          • 可解决问题的隔离级别：已提交读
            • 语句级
        • 不可重复读：事务A读到事务B已提交的修改数据，不符合隔离性
          • 可解决问题的隔离级别：可重复读
            • 事务级，默认
        • 幻读：事务A读到事务B提交的新增数据，不符合隔离性
          • 可解决问题的隔离级别：可序列化
            • 最高级别，事务级
      • SHOW VARIABLES LIKE 'tx_isolation'：查看当前数据库事务隔离级别
  • Session1和Session2都关闭自动提交，Session1先对Table1的A行做修改时
    • SET autocommit=0：设置当前Session关闭自动提交
    • Session1：可读可修改
    • Session2：
      • 对同一行做修改时会阻塞，直到Session1提交（Session2也需提交，不然看不到Session1修改的数据）
      • 可修改其它行
  • 索引失效时行锁会变表锁
  • 间隙锁
    • 当用范围条件而不是相等条件检索数据请求共享或排他锁时，InnoDB会给符合条件的已有数据记录的索引项加锁，对于键值在条件范围内但并不存在的记录叫做间隙，InnoDB也会这个间隙加锁，即为间隙锁
    • Query执行过程中范围查找时会锁定整个范围内所有的索引值即是这个值不存在
    • Table1的A字段有1，3，4，5值
      • Session1对A字段的1到5进行范围更新
      • Session2添加A字段的值2时被阻塞
  • 锁定行
    1. BEGIN
    2. SELECT ... FOR UPDATE：锁定某一行，其他操作会被阻塞直到锁定的会话被提交
    3. COMMIT
  • 命令
    • SHOW STATUS LIKE 'innodb_row_lock%'：查看锁情况
      • Innodb_row_lock_current_waits：当前正在等待锁定的数量
      • Innodb_row_lock_time_avg：每次等待的平均时间
      • Innodb_row_lock_waits：系统启动后到现在总共等待的次数
  • 优化建议
    • 尽量让所有数据检索通过索引完成，避免无索引行锁升级为表锁
    • 合理设计索引，尽量缩小锁的范围
    • 尽量减少索引条件，避免间隙锁
    • 尽量控制事务大小，减小锁定资源量和时间长度
    • 尽量低级别事务隔离
• 页锁：开销加锁时间锁定粒度均介于表锁和行锁之间，会出现死锁，并发度一般，仅了解

主从复制

原理

• 从机会从主机读取binlog进行数据同步
  1. 主机将改变记录到二进制文件中binary log events
  2. 从机将主机的binlog拷贝到它的中继日志realy log
  3. 从机重做中继日志中的事件，将改变应用到自己数据库中，MySQL复制时异步且串行化的

原则

• 每个从机只有一个主机
• 每个从机只能有一个唯一的服务器ID
• 每个主机有多个从机

问题

• 时延迟

常见配置

• 一主一从

  1. MySQL版本一致且后台以服务运行

  2. 主从都配置在[mysql]结点下，都是小写

  3. 主机配置文件

     [mysql]
     # [必须]主服务器唯一ID
     server-id=1
     # [必须]启用二进制文件
     log-bin=自己的路径/mysqlbin
     # 启用错误日志
     log-error=自己的路径/mysqlerror
     # 根目录
     basedir="自己的路径"
     # 临时目录
     tmpdir="自己的路径"
     # 数据目录
     datadir="自己的路径/data"
     # 主机读写都可以
     read-only=0
     # 设置不要复制的数据库
     binlog-ignore-db=mysql
     # 设置需要复制的数据库
     binlog-do-db=数据库名

  4. 从机配置文件

     [mysql]
     # [必须]服务器唯一ID
     server-id=2
     # 启用二进制文件
     log-bin=自己的路径/mysqlbin

  5. 主机和从机都重启后台MySQL服务

  6. 主机从机都关闭防火墙

  7. 主机建立账户并授权slave

     GRANT REPLICATION SLAVE ON *.* TO 'phineasz'@'从机数据库IP' IDENTIFIED BY '123456';
     # 刷新
     FLUSH PRIVILEGES;
     # 查看主机状态，记录File和Position的值
     SHOW MASTER STATUS

  8. 从机上配置需要复制的主机

     CHANGE MASTER TO MASTER_HOST='主机IP', MASTER_USER='phineasz', MASTER_PASSWORD='123456'， MASTER_LOG_FILE='File名', MASTER_LOG_POS=Position数字;
     # 启动主从复制功能
     START SLAVE
     # 查看从机状态
     SHOW SLAVE STATUS\G
     # Slave_IO_Running:Yes
     # Slave_SQL_Running:Yes

  9. 主机新建库/表/记录，从机复制

  10. 停止从机复制功能

      STOP SLAVE

  11. 每次连接前都要先SHOW MASTER STATUS（Position会变）