1 Star 0 Fork 1

gtzhong / note-mysql-senior

forked from 茅伟凡 / note-mysql-senior 
Gitee —— 企业级 DevOps 研发管理平台
加入 Gitee
该仓库未指定开源许可证,未经作者的许可,此代码仅用于学习,不能用于其他用途。
克隆/下载
取消
提示: 由于 Git 不支持空文件夾,创建文件夹后会生成空的 .keep 文件
Loading...
README.md

MySQL高级

索引Index

==排好序的快速查找数据结构==

查看表的索引
SHOW INDEX FROM `user`;
创建索引
方式一:
CREATE INDEX idx_user_age ON `user`(age);

idx_user_age : 索引名(idx_表名_索引字段名

````user`(age): 表名(索引字段名)```

方式二:
ALTER TABLE `user` ADD INDEX idx_user_gender(gender);

````user`(age): 表名(索引字段名)```

idx_user_gender(gender) : 索引名(索引字段名)

删除索引
方式一:
ALTER TABLE `user` DROP INDEX idx_user_gender;
方式二:
DROP INDEX idx_user_age ON `user`;
哪些情况需要创建索引
  1. 主键自动建立唯一索引
  2. 频繁作为查询条件的字段应该创建索引
  3. 查询中与它表关联的字段,外键关系建立索引
  4. 查询中排序的字段,排序字段若通过索引去访问将大大提高排序速度
  5. 查询中统计|分组字段
  6. 单键|组合索引的选择问题,在高并发下倾向创建组合索引
哪些情况不要创建索引
  1. 频繁更新的字段不适合创建索引,因为每次更新不单单是更新了记录还会更新索引
  2. Where条件里用不到的字段不适合创建索引
  3. 表记录太少不适合创建索引
  4. 经常增删改的表,提高了查询速度,同时却会降低更新表的速度,如对表进行INSERT,UPDATE和DELETE。因为更新表时,MySQL不仅要保存数据,还要保存一下索引文件
  5. 数据重复且分布平均的表字段,因此应该只为最经常查询最经常排序的数据列建立索引。注意:如果某个数据列包含许多重复的内容,为它建立索引就没用太大的实际效果。

性能分析

MySQL Query optimizer

MySQL创建瓶颈
  1. CPU:CPU在饱和的时候一般发送在数据装入内存或磁盘上读取时候
  2. IO:磁盘I/O瓶颈发生在装入数据远大于内存容量的时候
  3. 服务器硬件的性能瓶颈:topfreeiostatvmstat来查看系统的性能状态
Explain
  1. 是什么

    查看执行计划

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

  2. 能干嘛

    explain中的各字段的作用

    1. 表的读取顺序(id)
    2. 数据读取操作的操作类型(select_type)
    3. 哪些索引可以使用(prossible_keys)
    4. 哪些索引被实际使用(key)
    5. 表之间的引用(ref)
    6. 每张表有多少行被优化器查询(rows)
  3. 怎么用

    Explain+SQL语句

    EXPLAIN SELECT * FROM `user`;

    执行计划包含的信息

  4. 各字段解释
    1. id

      select查询的序列号,包含一组数字,标配是查询中执行select子句或操作表的顺序

      三种情况

      • id相同,执行顺序由上至下

      • id不同,如果是子查询,id的序号会递增,id值越大优先级越高,越先被执行

      • id相同不同,同时存在

        id如果相同,可以认为是一组,从上往下顺序执行;在所有组中,id值越大,优先级越高,越先执行

    2. select_type

      常用值:

      查询的类型,主要是用于区别

      • SIMPLE :简单的select查询,查询中不包含子查询或者UNION

      • PRIMARY :查询中若包含任何复杂的子部分,最外层查询则标记为

      • SUBQUERY :在SELECT或者WHERE列表中包含了子查询

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

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

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

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

    3. table

      显示这一行的数据是关于哪张表的

    4. partitions

      该列显示的为分区表命中的分区情况。非分区表该字段为空(null)

    5. type

      访问类型排序

      显示查询使用了何种类型,从最好到最差依次是

      system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

      一般来说,得保证查询至少达到range级别,最好能达到ref。

      • system : 表只有一行记录(等于系统表),这是const类型的特列,平时不会出现,这个也可以忽略不计
      • const : 表示通过索引一次就找到了,const用于比较peimary key或者unique索引。因为只匹配一行数据,所以很快。如将主键置于where列表中,MySQL就能将该查询转换为一个常量
      • eq_ref : 唯一性索引扫描,对于每个索引键,表中只有一条记录与之匹配。常见于主键或唯一索引扫描
      • ref :非唯一性索引扫描,返回匹配某个单独值的所有行。本质上也是一种索引访问,它返回所有匹配某个单独值的行,然而,它可能会找到多个符合条件的行,所以他应该属于查找和扫描的混合体
      • range :只检索给定范围的行,使用一个索引来选择行。key列显示使用了哪个索引,一般就是在你的where语句中出现了between、<、>、 in等的查询,这种范围扫描索引扫描比全表扫描要好,因为它只需要开始于索引的某一点,而结束于另一点,不用扫描全部索引。
      • index : FullIndexScan,indexALL区别为index类型只遍历索引树。这通常比ALL快,因为索引文件通常比数据文件小。(也就是说虽然ALLindex都是读全表,但index是从索引中读取的,而ALL是从硬盘中读取的)
      • ALLFullTableScan,将遍历全表以找到匹配的行
    6. possible_keys

      显示可能应用在这张表中的索引,一个或多个。

      查询涉及到的字段上若存在索引,则该索引将被列出,但不一定被查询实际使用

    7. key

      实际使用的索引。如果为null,则没有使用索引。查询中若使用了覆盖索引,则该索引仅出现在key列表中

    8. key_len

      表示索引中使用的字节数,可通过该列计算查询中使用的索引的长度。在不损失精确性的情况下,长度越短越好。key_len显示的值为索引字段的最大可能长度,并非实际使用长度,即key_len是根据表定义计算而得,不是通过表内检索出的

    9. ref

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

    10. rows

      根据表统计信息及索引选用情况,大致估算出找到所需的记录所需要读取的行数

    11. filtered

      表示存储引擎返回的数据在server层过滤后,剩下多少满足查询的记录数量的比例,注意是百分比,不是具体记录数。

    12. Extra

      包含不适合在其他列中显示但十分重要的额外信息

      • Using filesort :说明MySQL会对数据使用一个外部的索引排序,而不是按照表内的索引进行读取。MySQL中无法利用索引完成的排序操作成为‘’文件排序‘’。

      • Using temporary :使用了临时表保存中间结果,MySQL在对查询结果排序时使用临时表。常见于排序order by和分组查询group by

      • Using index :表示相应的select操作中使用了覆盖索引(Covering Index),避免访问了表的数据行,效率不错!

        如果同时出现Using where,表明索引被用来执行索引键值的查询;

        如果没有同时出现Using where,表明索引用来读取数据而非执行查询动作。

        覆盖索引(Covering Indexselect的数据列只用从索引中就能够读取,不必读取数据行,MySQL可以利用索引返回select列表中的字段,而不必根据索引再次读取数据文件,换句话说==查询列要被索引覆盖==。注意:如果使用覆盖索引,一定要注意select列表中只取出需要的列,不可select *,因为如果将所有字段一起做所有会导致索引文件过大,查询性能下降。

      • Using where :表明使用了where过滤

      • Using join buffer :使用了连接缓存,可根据情况适当提高配置文件中的buffer缓冲区大小。

      • impossible where :where子句的值总是false(不满足条件),不能用来获取任何元组。

      • select tables optimized away :在没有group by子句的情况下,基于索引优化MIN/MAX操作或者对于 MyISAM存储引擎优化count(*)操作,不必等到执行阶段再进行计算,查询执行计划生成的阶段即完成优化。

      • distinct :优化distinct操作,在找到第一匹配的元组后即停止找同样值的动作


索引优化

索引失效

应该避免

  1. 全值匹配

    建立了复合索引(a,b,c)
    	根据最佳左前缀法则,查询不带a将不能使用索引
    	查询
    		a,b,c(覆盖索引,最好)
            a,b(部分使用索引,a和b皆使用到了索引)
            a(部分使用索引,a使用到了索引)
            b,c(违背最佳左前缀法则,索引最左列不能丢,索引失效)
            a,c(违背最佳左前缀法则,不能跳过索引的列,a使用索引,c索引失效)
  2. ==最佳左前缀法则==

    如果索引了多列,要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列

  3. 不在索引列上做任何操作(计算、函数、(自动|手动)类型转换),会导致索引失效而转向全表扫描

  4. 存储引擎不能使用索引中范围条件右边的列

  5. 尽量使用覆盖索引(只访问索引的查询(索引列和查询列一致)),减少select *

  6. MySQL在使用不等于(!=|<>)的时候无法使用索引会导致全表扫描

  7. is nullis not null也无法使用索引

  8. like以通配符开头(如%abc),MySQL索引失效会编程全表扫描的操作

    **解决like %字符串%**时索引不被使用的方法

    使用==覆盖索引==

  9. 字符串不加单引号索引失效

  10. 少用or,用它来连接时会索引失效

建议
  1. 对于单键索引,尽量选择针对当前query过滤性更好的索引
  2. 在选择组合索引的时候,当前query中过滤性最好的字段在索引字段顺序中,位置越靠前越好
  3. 在选择组合索引的时候,尽量选择可以能够包含当前querywhere字句中更多字段的索引
  4. 尽可能通过分析统计信息和调整query的写法来达到选择合适索引的目的


查询截取

查询优化

==永远小表驱动大表==:即小的数据集驱动大的数据集

-- EXISTS:将著擦好像的数据,放到子查询中做条件验证,根据验证结果(TRUE|FALSE)来决定主查询的数据结果是否得以保留
SELECT * FROM  WHERE EXISTS (子查询)

order by关键字优化

  1. order by子句,尽量使用Index方式排序,避免使用FileSort方式排序

    order by满足两种情况,会使用Index方式排序

    1. order by语句使用索引最左前列
    2. 使用where子句与order by子句条件列组合满足索引最左前列
  2. 尽可能在索引列上完成排序操作,遵照索引建的最佳左前缀

  3. 如果不在索引列上,FileSort有两种算法:

    mysql就要启动双路排序和单路排序

    双路排序:

    mysql4.1之前是使用双路排序,字面意思就是两次扫描磁盘,最终得到数据,读取行指针和order by列,对他们进行排序,然后扫描已经排序好的列表,按照列表中的值重新从列表中读取对应的数据输出。

    从磁盘取排序字段,在buffer进行排序,再从磁盘取其他字段。

    取一批数据,要对磁盘进行了两次扫描,众所周知,I/O是很耗时的,所以在mysql4.1之后,出现了第二种改进的算法,单路排序

    ==单路排序:==

    从磁盘读取查询需要的所有列,按照order by列再buffer对它们进行排序,然后扫描排序后的列表进行输出,它的效率更快一些,避免了第二次读取数据。并且把随机I/O变成了顺序I/O,但是它会使用更多的空间,因为它把每一行都保存在内存中了。

    由于单路是后出的,总体而言好过双路,但是==用单路有问题==

  4. 优化策略

    1. 增大sort_buffer_size参数的设置
    2. 增大max_length_for_sort_data参数的设置

总结

group by关键字优化

  1. group by实质是先排序后进行分组,遵照索引建的最佳左前缀
  2. 当无法使用索引列时,增大max_length_for_sort_data参数的设置 + 增大sort_buffer_size参数的设置
  3. where高于having,能写在where限定的条件就不要取having限定了
慢查询日志
是什么

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

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

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

怎么用

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

当然,如果不是调优需要,一般不建议启动该参数,因为开启慢查询日志会或多或少带来一定的性能影响。慢查询日志支持将日志记录写入文件。

  1. 查看是否开启及如何开启

    1. 默认:SHOW VARIABLES LIKE '%slow_query_log%';

    2. 开启:set global slow_query_log = 1; ==此处的1为true(ON),0为false(OFF);==

      使用set global slow_query_log = 1;开启慢查询日志只对当前数据库生效,如果Mysql重启后则会失效。

  2. 什么样的SQL为慢SQL?

    这个是由参数long_query_time控制的,默认情况下long_query_time的值为10秒,10是大于,并非大于等于,因此等于10秒的SQL不会被记录下来

    通过SHOW VARIABLES LIKE 'long_query_time%'查看;

    设置慢的阙值时间:set global long_query_time = 3;,设置完成后需要重写开启一个mysql会话才能通过SHOW GLOBAL VARIABLES LIKE 'long_query_time%'查看更改

  3. 查看当前系统中有多少条慢查询记录:SHOW GLOBAL STATUS LIKE '%Slow_queries%'

  4. 使用配置文件

==日志分析工具mysqldumpslow==

在生产环境中,如果要手工分析日志,查找、分析SQL,显然是个体力活,Mysql提供了日志分析工具mysqldumpslow

mysqldumpslow --help

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

工作常用参考

Show Profile
是什么

是mysql提供可以用来分析当前会话中语句执行的资源消耗情况。可以用于SQL的调优的测量

官网

分析步骤

  1. 是否支持,看看当前的mysql版本是否支持

    • Show variables like 'profiling;'默认是关闭的,使用前需要开启
    • 或者:Show variables like 'profiling%;'
  2. 开启功能,默认是关闭,使用前需要开启

    • set profiling = on;
  3. 运行SQL

    默认情况下,参数处于关闭状态,并保存最近15次运行结果

  4. 查看结果,show profiles

  5. 诊断SQL,show profile cpu,block io for query 查询id(查询id通过show profiles获取)

    Type:

    • all --显示所有的开销信息
    • block io --显示块IO相关开销
    • context switches --上下文切换相关开销
    • cpu --显示cpu相关开销信息
    • ipc --显示发送和接收相关开销信息
    • memory --显示内存相关开销信息
    • page faults --显示页面错误相关开销信息
    • source --显示和Source_function,Source_file,Source_line相关的开销相信
    • swaps --显示交换次数相关开销信息
  6. 日常开发需要注意的结论

    show profile cpu,block io for query 查询id查看Status出现以下一种需要优化

    1. converting HEAP to MyISAM:查询结果太大,内存都不够用了往磁盘上搬了
    2. Creating tmp table:创建临时表(拷贝数据到临时表,用完再删除)
    3. Copying to tmp table on disk:把内存中临时表复制到磁盘,==危==
    4. locked:锁表
全局查询日志

仅允许再测试环境中使用,==永远不要再生产环境开启这个功能==

编码启用

set global general_log = 1;

set global log_output = 'TABLE';

此后,你所编写的sql语句,都将会记录到mysql库里面的general_log表,

可以用select * from mysql.general_log查看

MySQL锁机制

锁的分类

对数据的操作类型分:

  1. 读锁(共享锁)

    针对同一份数据,多个读操作可以同时进行而不会相互影响。

  2. 写锁(排他锁)

    当前写操作没有完成前,它会阻断其他写锁和读锁。

对数据的操作粒度分:表锁,行锁

三锁

开销,加锁速度,死锁,粒度,并发性能

只能就具体应用的特点来说哪种锁更合适

表锁(偏读)

偏向于MyISAM存储引擎,开销小,加锁快;无死锁;锁定粒度大,发送锁冲突的概率最高,并发度最低。

手动增加表锁

lock table 表名字 read(|write),表名字2 read(|write),其他; 

查看表上加过的锁

show open tables;

释放表锁

unlock tables;

总结

加了读锁的的session,不能执行对该表的写操作,不能读其他表,不能写其他表,仅允许读被锁的表

其他session,可以读被加读锁的表,不允许写该表(会被阻塞),其他表可读可写

加了写锁的session,仅能对自己锁定的表进行读写

其他session不允许读(阻塞)写(阻塞)加了写锁的session,其他表可读可写

表锁分析

行锁(偏写)

偏向InnoDB存储引擎,开销大,加锁慢;会出现死锁;锁定粒度最小,发送锁冲突的概率最低,并发度也最高。

==InnoDBMyISAM最大的不同有两点==

一:支持事务(Transaction)

二:采用行级锁

事务(Transaction)及其ACID属性:

并发事务处理带来的问题

  1. 更新丢失(Lost Update)

  2. 脏读(Dirty Reads)

  3. 不可重复读(Non-Repeatable Reads)

  4. 幻读(Phantom Reads)

事务的隔离级别

==行锁变表锁==

==当 Where 查询条件中的字段没有索引时,更新操作会锁住全表!索引失效也会导致行锁变表锁。==

间隙锁

当我们用范围条件而不是相等条件检索数据,并请求共享或排他锁时,InnoDB会给符合条件的已有数据记录的索引项加锁;对于键值在条件范围内但并不存在的记录,叫做“间隙(GAP)”,InnoDB也会对这个“间隙”加锁,这种锁机制就是所谓的间隙锁(Next-Key锁)。

危害:因为Query执行过程中通过范围查找的话,他会锁定整个范围内所有的索引键值,即使这个键值并不存在。

间隙锁有一个比较致命的弱点,就是当锁定一个范围键值之后,即使某些不存在的键值也会被无辜的锁定,而造成在锁定的时候无法插入锁定键值范围内的任何数据。在某些场景下这可能会对性能造成很大的危害。

如何锁定一行

总结

行锁分析

优化建议

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

开销和加锁时间界于表锁和行锁之间;会出现死锁;锁定粒度界于表锁和行锁之间,并发度一般。

MySQL主从复制

复制的基本原理

slave会从master读取binlog来进行数据同步

Mysql复制过程分为三步:

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

复制的基本原则

每一个slave只有一个master

每个slave只能由一个唯一的服务器ID

每个master可以由多个salve

复制的最大问题

延时

一主(windows)一从(linux)常见配置
  1. mysql版本一致且后台以服务运行

  2. 主从斗鱼配置在==[mysqld]==节点下,都是小写

  3. 主机修改my.ini(windows)配置文件

    1. [必须]主服务器唯一IDserver-id=1
    2. [必须]启用二进制日志log-bin=自己本地的路径/mysqlbin
    3. [可选]启用错误日志log-err=自己本地的路径/mysqlerr
    4. [可选]根目录basedir="自己本地路径"
    5. [可选]临时目录tmpdir="自己本地路径"
    6. [可选]数据目录datadir="自己本地路径/Data"
    7. read-only=0主机,读写都可以
    8. [可选]设置不要复制的数据库binlog-ignore-db=不要复制的数据库名字
    9. [可选]设置需要复制的数据库binlog-do-db=需要复制的主数据库名字
  4. 从机修改my.cnf(linux)配置文件

    1. [必须]从服务器唯一ID
    2. [可选]启用二进制日志
  5. 因修改过配置文件,主机和从机都要重启后台mysql服务

  6. 主机从机关闭防火墙(相互ip ping得通)

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

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

  9. 主机新建库,表,insert记录,从机复制

  10. 如何停止从服务复制功能

    stop slave;

点评 ( 0 )

你可以在登录后,发表评论

简介

mysql高级笔记 展开 收起
SQL
取消

发行版

暂无发行版

贡献者

全部

近期动态

加载更多
不能加载更多了
SQL
1
https://gitee.com/yeafy/note-mysql-senior.git
git@gitee.com:yeafy/note-mysql-senior.git
yeafy
note-mysql-senior
note-mysql-senior
master

搜索帮助

122604 9befe709 551147 122411 94cd1624 551147