文章目录
rpm 安装
- 检查系统是否安装过mysql:rpm -qa|grep -i mysql
- rpm -ivh MySQL…rpm (i安装v日志h进度条)
- 其他
mysql配置文件
- log-bin: 二进制日志(my.ini | /etc/my.cnf) --主从复制
- log-err: 错误日志,默认关闭
- 查询日志log:记录查询的sql语句–慢查询等
- 数据文件
- frm文件:存放表结构
- myd:存放表数据
- myi:存放表索引
MyISAM和InnoDB对比,sql执行顺序
- InnoDB和MySIAM最大的不同有两点:一是支持事务,二是采用了行级锁。
- 表锁开销小,加锁快,无死锁,锁定粒度大,发生锁冲突的概率高,因此并发度最低
- 行锁开销大,加锁慢,会出现死锁,但锁的粒度小,锁冲突概率最低,因此并发度最高。
索引
- 索引是帮助mysql高效获取数据的数据结构
- 查找和排序(where和order by)
索引结构以及原理
- BTree索引(磁盘块,页,非叶子节点不包含数据,只包含key)
- 索引失效,索引覆盖
explian关键字
-
explian 关键词 + sql语句
-
作用:
- 表的读取顺序
- 数据读取操作的操作类型
- 哪些索引可以使用
- 哪些索引被实际使用
- 表之间的引用
- 每张表有多少行被优化器查询
-
id : select查询的序列号,包含一组数字,表示查询中执行select子句和操作的顺序
- id相同,执行顺序由上到下
- 如果是子查询,id的序号会递增,id越大的优先级越大,越先执行
- id相同或不同均存在
-
select_type: 表示查询的类型,区分普通查询,联合查询,子查询等复杂查询
- simple 简单查询,不包含子查询或者union
- 查询中包含任何的子查询,最外层的为primary
- subquery select或where中包含了子查询
- derived:在from列表中包含的子查询被标记为derived,mysql会递归执行这些子查询,把结果放在临时表中(后面跟的id为子查询的表序列id)
- union:第二个select出现在union之后,则被标记为union。若union包含在from子句的子查询中,外层select被标记为derived
- union result:从union表中获取结果的select
-
table:显示这一行的数据是关于哪张表的
-
type: 访问类型序列
- 从最好到最坏依次如下:system > const > eq_ref > ref > range > index > ALL
- all : 全表扫描
- 至少查询可以达到range,最好可以达到ref
- system:表只有一行记录(系统表),这是const类型的特例,平时不会出现
- const:通过一次查询就可以找到,const用于比较primry key或者unique索引。
- eq_ref:唯一性索引扫描,对于每个索引键,表中只有一条记录与之匹配,常用于主键和唯一索引扫描
- ref:非唯一性索引扫描,返回匹配某个单独值的所有行。本质上来说也是一种索引访问,它返回的是匹配某个单独值的所有行,属于查找和扫描的结合
- range:给定范围的行,使用一个索引来选择行。比全表扫描要好,只需要扫描部分索引
- index:full index scan,index和all的区别是只遍历索引树。比all快:索引文件比数据文件小
-
possible_keys:显示可能应用在这张表的索引,一个或多个。查询涉及到的字段上若存在索引,则列出。但不一定会被实际使用到
-
key:显示实际使用到的索引。查询中若使用了覆盖索引,则该索引仅出现在key列表中
-
key_len: 表示索引使用的字节数,可通过该列计算查询中使用的索引的长度。在不损失精确性的情况下,长度越短越好。key_len表示的是索引字段的最大可能长度,并非实际使用长度,是通过计算得到的而不是通过表内检索得到的。
-
ref:显示索引被哪一列使用到了,如果可能的话,是一个常数。哪些列或者常量被用于查找索引列上的值。
-
rows: 根据表统计信息及索引选用情况,估算出找到所需的记录所需要读取的行数。
-
Extra: 额外信息(重要)
- Using filesort:mysql会对数据使用一个外部的索引排序,而不是按照表内的索引顺序进行读取。MYSQL无法利用索引完成的排序成为“文件排序”。
- Using temporary:使用了临时表保存中间结果,mysql在对查询结果使用排序时使用了临时表。常见于order by和分组查询group by。
- using index 使用了索引覆盖,如果同时使用了using where,表明index用来执行索引键值的查找,否则索引用来读取数据而非进行查找动作(索引覆盖)。
- 索引覆盖:select的数据列只从索引中就能够得到,不必读取数据行,mysql可以利用索引返回select列表中的字段,而不必根据索引再次读取数据文件,即查询列要被所建的索引覆盖。
- 如果要使用覆盖索引,select中只能取出需要的列,不可以用*。因为所有字段一起做索引会导致索引文件过大,查询性能下降。
- using join buffer:使用了连接缓存(join的时候使用了缓存,如果需要的缓存较大可以通过配置文件调整)
- impossi where: where==flase
- select tables optimized away:当我们使用某些聚合函数来访问存在索引的某个字段时,优化器会通过索引直接一次定位到所需要的数据行完成整个查询。在使用某些聚合函数如min, max的query,直接访问存储结构(B树或者B+树)的最左侧叶子节点或者最右侧叶子节点即可,这些可以通过index解决。Select count(*) from table(不包含where等子句),MyISAM保存了记录的总数,可以直接返回结果,而Innodb需要全表扫描。Query中不能有group by操作。
- distinct: 优化 distinct操作,在找到第一匹配的元组之后即停止找同样值的操作
- EXTRA中的USING INDEX,USING WHERE,USING INDEX CONDITION
- Using filesort:mysql会对数据使用一个外部的索引排序,而不是按照表内的索引顺序进行读取。MYSQL无法利用索引完成的排序成为“文件排序”。
索引单表优化
- range之后的索引会失效,多个字段查找时可以绕过range查询的字段建立索引
两表查询join优化
- join: 左右连接相反加:左连接的特性,left join用于确定如何从右表搜索行,左边一定都有,所以右边时搜索的关键点,一定要建立索引。
- 多表join优化类似,尽量小表驱动大表,优先优化neetedLoop内层循环,减少neetedLoop次数
索引优化分析
索引原则,索引失效
- 性能下降原因:
- sql语句写的不好
- 索引失效
- 关联查询太多(join)
- 服务器调优及各个参数设置
- 索引全值匹配最佳
- 最佳左前缀原则:组合索引,要遵守最佳左前缀法则。查询从索引的最左前列开始并且不跳过索引中的列
- 不在索引列上做任何操作(计算,函数,类型转换),会导致索引失效而进行全表扫描。
- 存储引擎不能使用索引范围条件右边的列
- 尽量使用覆盖索引,减少select *。
- mysql在使用了不等于(!= 或者 <>)的时候无法使用索引会导致全表扫描
- is null,is not null无法使用索引
- like 以通配符开头的会导致索引失效
- 如何解决like%索引失效的问题:使用覆盖索引/通配符写右边
- 字符串不加单引号索引失效
- 少用or,连接时索引失效
Q/A
- 对于复合索引,where后的定制查找颠倒顺序,索引是否失效
- 不会失效
- mysql底层的查询优化器会对查询语句解析优化,重排序等
- 不会失效
- 对于以上索引,在c3字段使用了order by,问多少字段的查询使用了索引
- c1,c2使用了索引,c3的索引作用在排序而非查找
- 对于以上索引,在c4字段使用了order by(跳过了c3),问多少字段的查询使用了索引
- 会使用到索引,但是会出现文件排序,因为绕过了c3,后面的索引失效
- order by/group by:定值,范围还是排序,一般order by时给个范围,group by虽是分组但是基本上也会排序,会有临时表产生。
查询截取分析
小表驱动大表
- 小表驱动大表,即小的数据集驱动大的数据集
- exists 只返回true或者false,因此子查询中的select * 也可以是select 1,实际执行时会忽略select清单,因此没有区别
- exists子查询实际执行过程可能经过了优化而不是逐条对比
- exists子查询往往也可以用条件表达式、其他子查询或者join代替,因该根据实际性能做选择。
order by优化
- mysql支持两种方式的排序,filesort和index。index效率高,指mysql扫描索引本身完成排序。Filesort方式效率较低。
- order by满足两种情况时会使用index排序:
- order语句使用索引最左前列
- 使用where子句和order by子句条件列组合条件满足索引最左前列
- 如果不在索引列上面,filesort有两种算法:双路排序和单路排序:
- mysql4.1之前使用的是双路排序,进行两次磁盘扫描,最终读取数据。读取行指针和order by列,对他们进行排序,在扫描已经排好序的列表,按照列表重新从表中读取对于select数据输出。即从磁盘读取排序字段,在buffer进行排序,再从磁盘读取其他字段。
- 取一批数据要进行两次磁盘访问,因此后面mysql做出了优化———单路排序。从磁盘读取查询做需要的所有列,按照order by列在buffer进行排序,然后扫描排序后的列表进行输出。减少了io次数,并且把随机io变成了顺序io(速度较快),但是会使用更多的空间。(buffer中存的数据较多)。而且当缓冲区的大小不足够保存所需数据时,还需要分多路读取在合并,可能消耗更大。
- 提高order by速度
- case
慢查询日志
- 开启慢查询日志,配置慢查询时间,查询慢查询日志文件
- 日志分析工具mysqldumpslow
- show profile
- show profile默认是关闭的,并且开启后只存活于当前会话,也就说每次使用前都需要开启。
- 通过show profiles查看sql语句的耗时时间,然后通过show profile命令对耗时时间长的sql语句进行诊断。
- 注意show profile诊断结果中出现相关字段的含义,判断是否需要优化sql语句。
数据库锁
表锁(MySIAM偏多)
- 读锁 lock table mylock read
- session1获取表mylock的read锁定,session可以查询该表记录,不可以查询其他没有锁定的表,修改该表会报错
- session2也可以查询锁定的表,或者更新其他未锁定的表,但是更新锁定的表会阻塞(一直等待获得锁)
- session1释放锁之后,session2就获得锁,完成插入操作
- 写锁 lock table mylock write
- session1可以修改锁定的表,但是不可以访问其他表。
- session2查询锁定的表,发生阻塞。session1释放锁后session2返回查询结果。
行锁(InnoDB)
- 行锁支持事务(ACID)
- 并发事务带来的问题
- 更新丢失
- 脏读
- 不可重复读
- 幻读
- 事务隔离级别
- 行锁case
- 设置两个会话更新但不提交 set autocommit=0;
- session1更新某一行,执行成功,在session1可以查到更新后结果
- session2查看该行,由于未提交,只能查询到事务之前的结果。更新该行,会发生阻塞。session12均提交之后,数据可以保证一致性。
- session12更新不同行,不会发生阻塞,commit后数据一致。
- 索引失效行锁升级为表锁:某个sql语句导致索引失效之后,行锁升级为表锁,查询的表会锁定。
- 间隙锁危害:当使用范围条件而不是相等条件进行检索数据,请求共享或者互斥锁时,innoDB会给符合条件的已有记录的索引项加锁。对于键值在条件范围内但并不存在的记录,称为间隙(GAP)。InnoDB会对这个间隙加锁。因为Query执行过程中通过范围查找的话,会锁定整个范围内所有的索引键值,即使这个键并不存在。当锁定一个范围键值之后,即使不存在的键值也会被锁定,从而造成在锁定的时候无法插入键值范围内的所有数据,可能造成长时间的阻塞。
- 如何锁定一行:
begin;
select * from table1 where a = 1 for update;
commit;
- 页锁
小结
- 视频资源来自atguigu,可以在b站上看
- 零散的一些关于Mysql索引优化的笔记,由于看的demo是Mysql5.5的版本,在5.6版本中还新引入了mrr以及索引下推来提高一些索引的性能,这里的一些仅供参考。