MySQL索引、锁学习笔记

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

索引单表优化

• 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以及索引下推来提高一些索引的性能，这里的一些仅供参考。