一、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性能下降
原因：
- 查询语句写的不好
- 索引失效
- 关联查询太多连接
- 服务器调优及各个参数设置（缓存、线程数等）
- …
3.2 SQL的执行顺序
3.3 索引
索引即排好序的快速查找的数据结构。
最常用的是B+树索引。
B和B+树区别是什么？为什么使用B+树而不是B树呢？
B树和B+树都是多路搜索树，区别是B+树的非叶子结点不存放数据，数据全部存放于叶子结点。B+树的叶子结点用指针从小到大连接在一起。
B+树的优势在于：
- B+树的非叶子结点不包含数据，可以容纳更多的结点元素。这将意味着IO访问次数会降低。
- 因为叶子结点用指针连接在一起，所以范围查询很方便。
优势：
- 提高数据检索的效率，降低数据库的IO成本。
- 通过索引对数据进行排序，降低排序成本，降低CPU消耗。
缺点：
- 占用空间。
- 降低更新表的速度。
- 需要花费时间建立最优秀的索引或优化查询。
#创建
create [unique] index indexName on tableName(columnName(length));
alter table tableName add [unique] index [indexName] on (columnName(length));
哪些情况下需要创建索引
- 主键自动建立唯一索引
- 频繁作为查询条件的字段应该创建索引
- 外键关系建立索引
- 查询中排序的字段
- 查询中统计或分组的字段
哪些情况下不要创建索引
- 表记录太少
- 频繁更新的字段
- where条件里用不到的
- 数据重复且分布平均的表字段。
3.4 性能分析
MySQL Query Optimizer 查询优化器：
通过计算分析系统中收集到的统计信息，为客户端请求的Query提供它认为最优的执行计划。
步骤：
当客户端向MySQL请求一条Query，命令解析器完成请求分类，区别出select转发给查询优化器。优化器首先对整条查询进行优化，处理掉一些常量表达式的预算，直接换成常量值，对查询中的查询条件进行简化和转换，然后分析query中的hint信息，看显示Hint信息是否可以完全确定该Query的执行计划。如果没有Hint或Hint信息还不足以完全确定执行计划，则会读取所涉及对象的统计信息，根据Query进行写相应的计算分析，然后再得出最后的执行计划。
Explain（执行计划）
使用explain命令可以模拟优化器执行sql查询语句，从而知道分析查询语句的性能。
explain 查询语句
- id
select查询的序列号。表示select执行的顺序。
id相同，执行顺序从上到下。
id不同，id越大优先级越高，越先执行 - select_type
查询的类型，主要用于区别普通查询、联合查询、子查询等复杂查询。
simple：简单select查询
primary：查询若包含任何复杂的子部分，最外层查询。
subquery：在select或where后包含了子查询。
derived：在from列表中包含的子查询被标记为derived。
union：若第二个select出现在union之后，则被标记为union。
union result：从union表获取结果的select。 - table
显示该行数据关于哪张表。 - 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：将遍历全表以找到匹配的行。 - possible_keys
显示可能应用在这张表中的索引。 - key
实际用到的索引。 - key_len
表示索引中使用的字节数。 - ref
显示索引的哪一列被使用了。 - rows
根据表统计信息及索引选用情况，大致估算出找到所需的记录所需要读取的行数。 - 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语句的优化：
- 尽可能减少join语句中的NestedLoop的循环总次数；“永远用小结果集驱动大结果集”
- 优先优化NestedLoop的内层循环；
- 保证Join语句中被驱动表上Join条件字段已经被索引；
- 当无法保证被驱动表的Join条件字段被索引且内存资源充足的前提下，不要太吝惜JoinBuffer的设置。
索引失效
- 尽量全值匹配，也就是where中的筛选条件完全符合一个索引（顺序、字段）。
- 最佳左前缀匹配，查询从索引的最左列开始并且不跳跃索引中的字段。
- 不在索引列上做任何操作，如计算、函数、（自动或手动）类型转换，导致全表扫描。
- 存储引擎不能使用索引中范围条件右边的字段。
- 尽量使用覆盖索引（只访问索引的值），减少select查询数据列。
- MySQL中使用**!=或<>**时，导致全表扫描。
- is null或is not null也无法使用索引。
- like以通配符开头会导致全表扫描。
- 字符串不加单引号会导致全表扫描。
10.少用 or ，用它来连接时会索引失效。
四、查询截取分析
4.1 查询优化
分析
- 开启慢查询日志，设置阙值。
- explain+慢查询分析
- show profile，查看SQL在MySQL服务器里面的执行细节和生命周期情况。
- SQL数据库参数调优。
总结 - 永远小表驱动大表
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 清单，因此没有区别。
- Order by优化
尽量使用index方式排序，避免使用filesort排序
尽可能在索引列上完成排序操作，遵照索引建的最佳左前缀
filesort有两种算法
提高Order By速度（SQL数据库服务器调优）- 尝试增大sort_buffer_size参数
不管用哪种算法，提高这个参数都会提高效率（根据系统的能力，因为这个参数是针对每个进程的）。 - 尝试增大max_length_for_sort_data参数
提高这个参数，会增加用改进算法的概率。但是如果设的太高，数据总容量超出sort_buffer_size的概率就会增加，明显症状是高的磁盘IO活动和低CPU使用率。 - order by时select *是一个大忌只query需要的字段。
- 当query字段大小综合小于max_length_for_sort_data而且排序字段不是text|blob类型时，会用单路排序，否则使用多路排序。
- 两种算法的数据都有可能超出sort_buffer的容量，超出之后，会创建tmp文件进行合并排序，导致多次IO，但是用单路排序风险更大，所以要提高sort_buffer_size。
- 尝试增大sort_buffer_size参数
- Group by优化
- group by实质是先排序后进行分组，遵照索引最左前缀
- 当无法使用索引列，增大max_length_for_sort_data和sort_buffer_size参数
- 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次的情况。
分析步骤
- show variables like ‘profiling’;查看是否支持
- set profiling=1;开启
- 运行SQL
- 查看结果，show profiles;
- 诊断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，显示交换次数相关开销的信息
- 日常开发需要注意的结论
- 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;#：系统启动后到现在总共等待的次数。
优化建议：
- 尽可能让所有数据检索都通过索引来完成，避免无索引行锁升级为表锁。
- 合理设计索引，尽量缩小锁的范围。
- 尽可能较少检索条件，避免间隙锁。
- 尽量控制事务大小，减少锁定资源量和时间长度。
- 尽可能低级别事务隔离。
六、主从复制
slave从master那里读取bin-log二进制文件进行数据同步。
复制过程
- master将改变记录到二进制日志（binary log）。这些记录过程叫做二进制日志事件，binary log events。
- slave将master的binary log events拷贝到它的中继日志（relay log）；
- slave重做中继日志中的事件，将改变应用到自己的数据库中。MySQL复制是异步的且串行化的。
扫一扫
8129
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
