mysql的内存限制 mysql 最大内存配置

一、MySQL简介

MySQL是一个关系型数据库管理系统。具有开源、灵活、速度快等特性。支持大型数据库，可以支持5000万条记录的数据仓库，32位系统表文件最大可支持4G，64位系统最大可支持8T。

MySQL的主要配置文件有：

• 二进制文件
  主要用于主从复制。
• 错误日志
  默认关闭，记录严重的警告和错误信息，每次启动和关闭的详细信息。
• 查询日志
  默认关闭，记录查询的sql语句（用于sql优化查找慢查询sql语句）。
• 数据文件
  frm文件，存放表结构；myd文件，存放表数据；myi文件，存放表索引。

二、存储引擎

#查看存储引擎
show engines;
show variables like '%storage_engine%';

主要的存储引擎有三个：MyISAM、InnoDB、Memory
MyISAM（偏读）和InnoDB（偏写）

	MyISAM	InnoDB
主外键	不支持	支持
事务	不支持	支持
行表锁	使用表锁，不适合高并发	使用行锁，适合高并发
缓存	只索引缓存，不缓存真实数据	既缓存索引，也缓存真实数据，对内存要求高
表空间	较小	较大
关注点	性能	事务

Memory
默认使用哈希索引。数据文件都存储在内存中。

三、索引优化分析

3.1 SQL性能下降

原因：

1. 查询语句写的不好
2. 索引失效
3. 关联查询太多连接
4. 服务器调优及各个参数设置（缓存、线程数等）
5. …

3.2 SQL的执行顺序

3.3 索引

索引即排好序的快速查找的数据结构。
最常用的是B+树索引。

B和B+树区别是什么？为什么使用B+树而不是B树呢？

B树和B+树都是多路搜索树，区别是B+树的非叶子结点不存放数据，数据全部存放于叶子结点。B+树的叶子结点用指针从小到大连接在一起。
B+树的优势在于：

1. B+树的非叶子结点不包含数据，可以容纳更多的结点元素。这将意味着IO访问次数会降低。
2. 因为叶子结点用指针连接在一起，所以范围查询很方便。

优势：

1. 提高数据检索的效率，降低数据库的IO成本。
2. 通过索引对数据进行排序，降低排序成本，降低CPU消耗。

缺点：

1. 占用空间。
2. 降低更新表的速度。
3. 需要花费时间建立最优秀的索引或优化查询。

#创建
create [unique] index indexName on tableName(columnName(length));
alter table tableName add [unique] index [indexName] on (columnName(length));

哪些情况下需要创建索引

1. 主键自动建立唯一索引
2. 频繁作为查询条件的字段应该创建索引
3. 外键关系建立索引
4. 查询中排序的字段
5. 查询中统计或分组的字段

哪些情况下不要创建索引

1. 表记录太少
2. 频繁更新的字段
3. where条件里用不到的
4. 数据重复且分布平均的表字段。

3.4 性能分析

MySQL Query Optimizer 查询优化器：

通过计算分析系统中收集到的统计信息，为客户端请求的Query提供它认为最优的执行计划。
步骤：
当客户端向MySQL请求一条Query，命令解析器完成请求分类，区别出select转发给查询优化器。优化器首先对整条查询进行优化，处理掉一些常量表达式的预算，直接换成常量值，对查询中的查询条件进行简化和转换，然后分析query中的hint信息，看显示Hint信息是否可以完全确定该Query的执行计划。如果没有Hint或Hint信息还不足以完全确定执行计划，则会读取所涉及对象的统计信息，根据Query进行写相应的计算分析，然后再得出最后的执行计划。

Explain（执行计划）

使用explain命令可以模拟优化器执行sql查询语句，从而知道分析查询语句的性能。

explain 查询语句

1. id
   select查询的序列号。表示select执行的顺序。
   id相同，执行顺序从上到下。
   id不同，id越大优先级越高，越先执行
2. select_type
   查询的类型，主要用于区别普通查询、联合查询、子查询等复杂查询。
   simple：简单select查询
   primary：查询若包含任何复杂的子部分，最外层查询。
   subquery：在select或where后包含了子查询。
   derived：在from列表中包含的子查询被标记为derived。
   union：若第二个select出现在union之后，则被标记为union。
   union result：从union表获取结果的select。
3. table
   显示该行数据关于哪张表。
4. type
   访问类型，从最好到最差依次是：
   system>const>eq_ref>ref>fulltext>ref_or_null>index_merge>unique_subquery>index_subquery>range>index>ALL
   system：表只有一行记录（等于系统表）
   const：通过索引一次就找到了。如将主键置于where列表中，MySQL就能将该查询转换为一个常量。
   eq_ref：唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。
   ref：非唯一性索引扫描，返回匹配某个单独值的所有行，本质上也是一种索引访问，它返回所有匹配某个单独值的行，然而，它可能会找到多个符合条件的行。
   range：只检索给定范围的行，使用一个索引来选择行，key列显示使用了哪个索引，一般就是在你的where语句中出现了between、<、>、in等的查询。
   index：index与All的区别为index类型只遍历索引树。
   ALL：将遍历全表以找到匹配的行。
5. possible_keys
   显示可能应用在这张表中的索引。
6. key
   实际用到的索引。
7. key_len
   表示索引中使用的字节数。
8. ref
   显示索引的哪一列被使用了。
9. rows
   根据表统计信息及索引选用情况，大致估算出找到所需的记录所需要读取的行数。
10. Extra
    Using filesort（危险）：
    说明mysql会对数据使用排序，而不是按照表内的索引顺序进行读取。称为“文件内排序”
    Using temporary（危险）：
    使用了临时表保存中间结果。常见于order by和group by中。
    Using index（好结果）：
    表示select中使用了覆盖索引。
    Using where：表明使用了where过滤
    Using join buffer：使用了连接缓存。
    impossible where：where子句的值总是false，不能用来获取任何元组。
    select table optimized away：在没有groupby子句的情况下，基于索引优化min/max操作或者对于MyISAM存储引擎优化count(*)操作，不必等到执行阶段再进行计算，查询执行计划生成的阶段即完成优化。
    优化distinct操作：在找到第一个匹配的元组后即停止找同样值的操作。

索引优化

在多表查询中，应该在从表中建立索引。

Join语句的优化：

1. 尽可能减少join语句中的NestedLoop的循环总次数；“永远用小结果集驱动大结果集”
2. 优先优化NestedLoop的内层循环；
3. 保证Join语句中被驱动表上Join条件字段已经被索引；
4. 当无法保证被驱动表的Join条件字段被索引且内存资源充足的前提下，不要太吝惜JoinBuffer的设置。

索引失效

1. 尽量全值匹配，也就是where中的筛选条件完全符合一个索引（顺序、字段）。
2. 最佳左前缀匹配，查询从索引的最左列开始并且不跳跃索引中的字段。
3. 不在索引列上做任何操作，如计算、函数、（自动或手动）类型转换，导致全表扫描。
4. 存储引擎不能使用索引中范围条件右边的字段。
5. 尽量使用覆盖索引（只访问索引的值），减少select查询数据列。
6. MySQL中使用**!=或<>**时，导致全表扫描。
7. is null或is not null也无法使用索引。
8. like以通配符开头会导致全表扫描。
9. 字符串不加单引号会导致全表扫描。
   10.少用 or ，用它来连接时会索引失效。

四、查询截取分析

4.1 查询优化

分析

1. 开启慢查询日志，设置阙值。
2. explain+慢查询分析
3. show profile，查看SQL在MySQL服务器里面的执行细节和生命周期情况。
4. SQL数据库参数调优。
   总结
5. 永远小表驱动大表

select * from A where id in(select id from B);
#等价于
for select id from B
	for select * from A where A.id=B.id
#当B表的数据集必须小于A表的数据集时，用in优于exists。
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
#当A表的数据集小于B表的数据集时，用exists优于in。
#exists(subquery)只返回true或false，因此子查询中的select*也可以是select 1或其他，官方说法是实际执行时会忽略select 清单，因此没有区别。

3. Order by优化
   尽量使用index方式排序，避免使用filesort排序
   尽可能在索引列上完成排序操作，遵照索引建的最佳左前缀
   filesort有两种算法 提高Order By速度（SQL数据库服务器调优）
1. 尝试增大sort_buffer_size参数
   不管用哪种算法，提高这个参数都会提高效率（根据系统的能力，因为这个参数是针对每个进程的）。
2. 尝试增大max_length_for_sort_data参数
   提高这个参数，会增加用改进算法的概率。但是如果设的太高，数据总容量超出sort_buffer_size的概率就会增加，明显症状是高的磁盘IO活动和低CPU使用率。
4. order by时select *是一个大忌只query需要的字段。
1. 当query字段大小综合小于max_length_for_sort_data而且排序字段不是text|blob类型时，会用单路排序，否则使用多路排序。
2. 两种算法的数据都有可能超出sort_buffer的容量，超出之后，会创建tmp文件进行合并排序，导致多次IO，但是用单路排序风险更大，所以要提高sort_buffer_size。
5. Group by优化

1. group by实质是先排序后进行分组，遵照索引最左前缀
2. 当无法使用索引列，增大max_length_for_sort_data和sort_buffer_size参数
3. where高于having，能写在where限定的条件就不要去having限定了

4.2 慢查询日志

运行时间超过long_query_time值的SQL，则会被记录到慢查询日志中。
long_query_time默认是10，并且默认关闭。
show variables like ‘%slow_query_log%’;
set global slow_query_log=1; 重启后失效

###MySQL日志分析工具
mysqldumpslow

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

#得到返回记录集最多的10个SQL
mysqldumpslow -s r -t 10 /xxx/xxx/xxx-slow.log
#得到访问次数最多的10个SQL
mysqldumpslow -s c -t 10 /xxx/xxx/xxx-slow.log
#得到按照时间排序的前10条里面含有左连接的查询语句
mysqldumpslow -s t -t 10 -g "left join" /xxx/xxx/xxx-slow.log
#建议使用 |

4.3 show profile

用来分析当前会话中语句执行的资源消耗情况。可以用于SQL调优的测量。
默认处于关闭状态，保存最近15次的情况。

分析步骤

1. show variables like ‘profiling’;查看是否支持
2. set profiling=1;开启
3. 运行SQL
4. 查看结果，show profiles;
5. 诊断SQL，show profile cpu, block io for query Query_ID;

• all，显示所有的开销信息
• block io，显示块IO相关开销
• context switches，上下文切换相关开销
• cpu，显示CPU相关开销信息
• ipc，显示发送和接收相关开销信息
• memory，显示内存相关开销信息
• page faults，显示页面错误相关开销信息
• source，显示和Source_function，Source_file，Source_line相关的开销信息
• swaps，显示交换次数相关开销的信息

6. 日常开发需要注意的结论

• converting heap to MyISAM 查询结果太大，内存都不够用了往磁盘上搬了。
• creating tmp table 创建临时表，拷贝数据到临时表，用完再删除
• copying to tmp table on disk 把内存中临时表复制到磁盘，危险
• locked

五、MySQL锁机制

锁的分类

从数据操作分：

• 读锁（共享锁）：针对同一份数据，多个读操作可以同时进行，互不受影响。
• 写锁（排它锁）：当前写操作没有完成前，它会阻断其他写锁或读锁。

从操作粒度分：
表锁和行锁

没有索引或者索引失效的时候，InnoDB行锁变表锁。

间隙锁（Next-Key锁）：
当我们使用范围条件进行查询时，并请求共享锁或排它锁，InnoDB会给范围内的所有记录加锁，包括键值在范围内但是不存在的记录，这种记录叫做间隙。

危害就是降低了性能。

通过检查InnoDB_row_lock状态变量来分析系统上的行锁的争夺情况。

show status like 'innodb_row_lock%';
Innodb_row_lock_current_waits;#：当前正在等待锁定的数量；
Innodb_row_lock_time;#：从系统启动到现在锁定总时间长度；
Innodb_row_lock_time_avg;#：每次等待所花平均时间；
Innodb_row_lock_time_max;#：从系统启动到现在等待最长的一次所花时间；
Innodb_row_lock_waits;#：系统启动后到现在总共等待的次数。

优化建议：

1. 尽可能让所有数据检索都通过索引来完成，避免无索引行锁升级为表锁。
2. 合理设计索引，尽量缩小锁的范围。
3. 尽可能较少检索条件，避免间隙锁。
4. 尽量控制事务大小，减少锁定资源量和时间长度。
5. 尽可能低级别事务隔离。

六、主从复制

slave从master那里读取bin-log二进制文件进行数据同步。

复制过程

1. master将改变记录到二进制日志（binary log）。这些记录过程叫做二进制日志事件，binary log events。
2. slave将master的binary log events拷贝到它的中继日志（relay log）；
3. slave重做中继日志中的事件，将改变应用到自己的数据库中。MySQL复制是异步的且串行化的。