mysql高手进阶优化篇

 

MySql理论

逻辑架构

连接层-->服务层-->引擎层-->存储层

存储引擎

查看方式

1.查看mysql现在提供的搜索引擎--->show engines

2.查看mysql当前默认存储引擎show variables like 'storageenginestorage_enginestoragee​ngine'

存储引擎对比

MyISAM:  B+Tree叶节点的data域存放的是数据记录的地址。在索引检索的时候，首先按照B+Tree搜索算法搜索索引，如果指定的Key存在，则取出其 data 域的值，然后以 data 域的值为地址读取相应的数据记录。这被称为“非聚簇索引”。

InnoDB:  其数据文件本身就是索引文件。相比MyISAM，索引文件和数据文件是分离的，其表数据文件本身就是按B+Tree组织的一个索引结构，树的叶节点data域保存了完整的数据记录。这个索引的key是数据表的主键，因此InnoDB表数据文件本身就是主索引。这被称为“聚簇索引（或聚集索引）”。而其余的索引都作为辅助索引，辅助索引的data域存储相应记录主键的值而不是地址，这也是和MyISAM不同的地方。在根据主索引搜索时，直接找到key所在的节点即可取出数据；在根据辅助索引查找时，则需要先取出主键的值，再走一遍主索引。  因此，在设计表的时候，不建议使用过长的字段作为主键，也不建议使用非单调的字段作为主键，这样会造成主索引频繁分裂

锁机制与InnoDB算法

MyISAM和InnoDB存储引擎使用的锁：

• MyISAM采用表级锁(table-level locking)。
• InnoDB支持行级锁(row-level locking)和表级锁,默认为行级锁

表级锁和行级锁对比：

• 表级锁： MySQL中锁定 粒度最大 的一种锁，对当前操作的整张表加锁，实现简单，资源消耗也比较少，加锁快，不会出现死锁。其锁定粒度最大，触发锁冲突的概率最高，并发度最低，MyISAM和 InnoDB引擎都支持表级锁。
• 行级锁： MySQL中锁定 粒度最小 的一种锁，只针对当前操作的行进行加锁。 行级锁能大大减少数据库操作的冲突。其加锁粒度最小，并发度高，但加锁的开销也最大，加锁慢，会出现死锁。

InnoDB存储引擎的锁的算法有三种：

• Record lock：单个行记录上的锁
• Gap lock：间隙锁，锁定一个范围，不包括记录本身
• Next-key lock：record+gap 锁定一个范围，包含记录本身

性能下降SQL慢的原因

1.查询语句写的不好

2.索引失效

3.关联查询太多join

4.服务器调优及各个参数设置(缓冲，线程数等 )

Mysql索引

定义:是帮助MySql高效获取数据的数据结构(排好序的快速查找的数据结构)

如果没有特别指明，都是B树(多路搜索树 并不一定是二叉树)结构组织索引。

优点

通过索引对数据进行排序。降低数据排序的成本，降低了CPU的消耗

缺点

1.索引也是一张表，该表保存了主键与索引字段，并指向实体表记录，所以索引列也是占用空间的

2.虽然索引提高了查询速度，但是降低更新表的速度(insert update delete) 。因为更新表Mysql不仅要保存数据，还要保存一下索引文件每次更新添加了索引列的字段，都会因为调整更新所带来的减值变化的的索引信息

3.索引只是提高效率的一个因素，如果Mysql有大数据量的表，就需要花时间研究建立最优秀的    索引或优化查询

索引的建立

创建索引:create [unique] index 索引名字 on 表名(列名)

alter 表名 ADD [unique] index 索引名字 on(列名) 

删除索引: drop index [索引名字] on 表名

查看索引: show index from 表名

 

索引结构

B-Tree

树中每个节点最多包含m个孩子 (最多有m个 分支)

出根节点与叶子节点以外，每个节点至少有[ceil(m/2)]个孩子(中间的节点)

若根节点不是叶子节点，则至少有两个孩子（根节点最少有两个分支）

所有叶子节点都在同一层 (没有子节点的都在一层)

每个非叶子节点有n个key和n+1个指针组成其中[ceil(m/2)-1]<=n<=m-1 (指针数最多和m相等)

以5叉B-Tree(m=5)为例[ceil(m/2)-1]<=n<=m-1 的 2<=n<=4.当n>4中间节点分裂到父节点，两边节点分裂。

B+Tree索引

B树变种

n叉B+Tree最多含有n个key，而BTree最多含有n-1个key

B+Tree的叶子节点保留索引key信息，依key大小进行顺序排列

所有非叶子节点都可以看着是key的索引部分

 

 

B+Tree查找数据必须到叶子节点，查询任何key都有从root走到叶子节点，查询稳定性高‘

mysql索引数据结构对经典的 B+Tree进行了优化。在原B+Tree的基础上，增加一个指向相邻叶子节点的链表指针，就形成了带有顺序指针的B+Tree,提高区间访问性能(范围查找)

 

2.Hash索引 

3.full-text全文索引

4.R-Tree索引

创建索引的条件

那些情况需要创建索引

1. 主键自动建立唯一索引
2. 频繁作为查询条件的字段应该创建索引
3. 查询中与其他表关联的字段，外键关系应该创建索引
4. 查询后排序的字段，排序字段若通过索引去访问将大大提高排序速度
5. 查询中统计或者分组字段

那些情况不需要创建索引

1. 表太少
2. 经常增删改的表
3. 数据重复且分布平均的表字段

explain 

作用

 

表的读取顺序
数据读取操作的操作类型
哪些索引可以使用 
哪些索引实际被使用
表之间的引用
每张表有多少行被优化其查到

使用
explain 查询语句  
eg：explain select * from orders

 

id 

select查询的序列号，包含一组数字，表示查询中执行select子句或操作表数据
三种情况:1.id相同：执行顺序由上到下
				2.id不同:如果是子查询id号会递增，id值越大优先级越高，越先被执行
        3.id相同不同，同时存在:不同的-id越大优先级越高，相同的 执行顺序由上到下

 select_type

代表查询的类型，主要是用于区别普通查询、联合查询、子查询等的复杂查询。
 
simple 简单的 select 查询,查询中不包含子查询或者 UNION
primary 查询中若包含任何复杂的子部分，最外层查询则被标记为 Primary
DERIVED 在 FROM 列表中包含的子查询被标记为 DERIVED(衍生)
MySQL 会递归执行这些子查询, 把结果放在临时表里。
SUBQUERY 在SELECT或WHERE列表中包含了子查询
DEPEDENT SUBQUERY 在SELECT或WHERE列表中包含了子查询,子查询基于外层
UNCACHEABLE SUBQUERY 无法使用缓存的子查询
UNION 若第二个SELECT出现在UNION之后，则被标记为UNION；若UNION包含在FROM子句的子查询中,外层SELECT将被标记为：DERIVED
UNION RESULT 从UNION表获取结果的SELECT

 table
这个数据是基于哪张表的。
partitions
显示的为分区表命中的分区情况。非分区表该字段为空（null）。
type

type 是查询的访问类型。是较为重要的一个指标，结果值从最好到最坏依次是：
system > const > eq_ref > ref > range > index >all(简化版 常见到的 优化到range就可以 最好ref)
===========================================================================================
system：表只有一行记录（等于系统表），这是 const 类型的特列，平时不会出现，这个也可以忽略不计 只在Myisam和memory
const:表示通过索引一次就找到了,const 常出现在使用 primary key 或者 unique 索引查询时。 如将主键置于 where 列表中
eq_ref:唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描。
ref:非唯一性索引扫描，返回匹配某个单独值的所有行.本质上也是一种索引访问，它返回所有匹配某个单独值的行，然而，它可能会找到多个符合条件的行，所以他应该属于查找和扫描的混合体。
range:只检索给定范围的行,使用一个索引来选择行。key 列显示使用了哪个索引一般就是在你的 where 语句中出现了 between、<、>、in 等的查询这种范围扫描索引扫描比全表扫描要好
index:出现index是sql使用了索引但是没有通过索引进行过滤，一般是使用了覆盖索引或者是利用索引进行了排序分组。
all:将遍历全表以找到匹配的行

 possible_keys

显示可能应用在这张表中的索引，一个或多个。查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询实际使用。

 

key

实际使用的索引。如果为NULL，则没有使用索引。

key_len

表示索引 使用的字节数，可通过该列计算查询中使用的索引的长度。 key_len 字段能够帮你检查是否充分的 利用上了索引。ken_len 越长，说明索引使用的越充分。

ref

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

rows

rows 列显示 MySQL 认为它执行查询时必须检查的行数。越少越好

filtered

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

extra

其他的额外重要的信息。

 

Using filesort mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。MySQL中无法利用索引完成的排序操作称为“文件排序”--也就是说排序的时候索引有没使用到的或者没有按顺序使用
Using temporary 使了用临时表保存中间结果,MySQL 在对查询结果排序时使用临时表。常见于排序 order by 和分组查询 group by。
Using index  代表表示相应的 select 操作中使用了覆盖索引(Covering Index)，避免访问了表的数据行，效率不错！
Using where 查询列未被索引覆盖
Using join buffer  使用了连接缓存区
impossible where 语句不合理
select tables optimized away 在没有 GROUPBY 子句的情况下，基于索引优化 MIN/MAX 操作或者对于 MyISAM 存储引擎优化 COUNT(*)操作，不必等到执行阶段再进行计算，查询执行计划生成的阶段即完成优化。

优化实战

单表索引优化法则

【优化总结口诀】

全值匹配我最爱，最左前缀要遵守 ；

带头大哥不能死，中间兄弟不能断；

索引列上少计算，范围之后全失效；

Like百分写最右，覆盖索引不写星；

不等空值还有or，索引失效要少用；

VAR引号不可丢，SQL高级也不难！

1.全值匹配我最爱

查询的字段按照顺序在索引中都可以匹配到 

CREATE INDEX idx_age_deptid_name ON emp(age,deptid,NAME);

 

如果查询时索引给的是一个确定值(a=2 ,a="a")情况多个索引是可以颠倒顺序的索引

2.最佳左前缀法则

过滤条件要使用索引必须按照索引建立时的顺序，依次满足，一旦跳过某个字段，索引后面的字段都无法被使用

 

3.不要在索引列上做任何计算、类型转换

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

4.索引列上不能有范围查询

将可能做范围查询的字段的索引顺序放在最后 范围之后的索引会失效

 

 

5.尽量使用覆盖索引

即查询列和索引列一致，不要写 select *

 

 

6.不要使用不等于(!= 或者<> 不等于)的时候

mysql 在使用不等于(!= 或者<>)时，有时会无法使用索引会导致全表扫描

 

 

7.字段的 is not null 和 is null

is not null 用不到索引，is null 可以用到索引 实际上是没有使用到

 

 

8.like 的前后模糊匹配

前缀不能出现模糊匹配

 

 如果必须用% 在左右 要覆盖索引 且查询字段必须是索引中的不能含有没创建索引的字段

 

 

9.减少使用 or

使用 union all 或者 union 来替

 

 

10.字符串不加双引号会导致索引失效

查询语句中有字符串是必须加上 ' '双引号

优化原则

小表驱动大表

 

 

 

 

关联查询优化

1. 在优化关联查询时，只有在被驱动表上建立索引才有效！ 
2. left join 时，左侧的为驱动表，右侧为被驱动表！ 也就是右侧建索引 （join操作表小于百万级别）
3. right join时，就是左侧建立索引 （join操作表小于百万级别） 多用单表查询
4. inner join 时，mysql 会自己帮你把小结果集的表选为驱动表 索引结果是一样的
5. 子查询尽量不要放在被驱动表，有可能使用不到索引,
6. 在范围判断时，尽量不要使用 not in 和 not exists，使用 left join on xxx is null 代替

Join的实现原理

mysql只支持一种join算法

嵌套循环连接--多重for循环

但嵌套循环有三种变种 

简单嵌套循环 在被驱动表建立索引

索引嵌套循环 通过驱动表中的索引吧需要的数据获取出来 在跟被驱动表进行匹配

块嵌套循环 利用join buffer 从驱动表中读取一批数据进行跟被驱动表中的数据进行匹配

优化思路：

尽量减少join语句中嵌套循环的总次数

优先优化嵌套循环的内层循环

保证join语句中被驱动表上join条件字段已被索引

无法保证被驱动表join条件字段被索引条件允许情况下加大join buffer

show variables like '%join_buffer%'

注意点：

并发量太高的时候，系统整体性能 可能会急剧下降

复杂的join语句，所需要锁定的资源也就越多，所以阻塞的其他线程也就越多

Order By 和 Group By优化

1.无过滤不索引

无过滤，不索引。where，limit 都相当于一种过滤条件，所以才能使用上索引！

2.顺序错，必排序

算上where过滤使用上的索引的到order by使用的索引顺序必须和建立索引的顺序相同 且中间不能出现无索引字段

3.方向反，必排序

在使用order by时出现多个字段要排序 如果都是升序或者降序 是可以的 不能出现一个升序一个降序

4.Group By

group by 使用索引的原则几乎跟 order by 一致 ，唯一区别是 groupby 即使没有过滤条件用到索引，也可以直 接使用索引。

慢查询日志

（1）MySQL的慢查询日志是MySQL提供的一种日志记录，它用来记录在MySQL中响应时间超过阀值的语句，具 体指运行时间超过long_query_time值的SQL，则会被记录到慢查询日志中。 
（2）具体指运行时间超过long_query_time值的SQL，则会被记录到慢查询日志中。long_query_time的默认值为 10，意思是运行10秒以上的语句。 
（3）由他来查看哪些SQL超出了我们的最大忍耐时间值，比如一条sql执行超过5秒钟，我们就算慢SQL，希望能 收集超过5秒的sql，结合之前explain进行全面分析。

 

show variables like '%query%' 全部配置信息

show variables like '%slow_query%' 查看慢查询日志位置和是否开启

set global slow_query_log=0 设置慢查询日中是否开启 1开启 0关闭

show variables like '%long_query%' 查看慢查询时间

set global long_query_time=1 修改慢查询时间默认秒

show processlist 查看正在执行的sql

日志分析工具 mysqldumpslow

得到返回记录集最多的 10 个 SQL
mysqldumpslow -s r -t 10 /var/lib/mysql/atguigu-slow.log
得到访问次数最多的 10 个 SQL
mysqldumpslow -s c -t 10 /var/lib/mysql/atguigu-slow.log
得到按照时间排序的前 10 条里面含有左连接的查询语句
mysqldumpslow -s t -t 10 -g "left join" /var/lib/mysql/atguigu-slow.log
另外建议在使用这些命令时结合 | 和 more 使用 ，否则有可能出现爆屏情况
mysqldumpslow -s r -t 10 /var/lib/mysql/atguigu-slow.log | more

锁
InnoDB是行锁  
mysql中锁的是索引或者主键 如果是查询等操作时，如果没有用到索引或主键就锁的是整个表 如果使用到了就是锁的行 锁定的行对一个行的操作不影响对其他行的操作 
共享锁
lock in share mode 共享锁
读锁

begin ---开启事务
 
select * from customers where Id = 1 lock in share mode ---lock in share mode 对这个查询语句使用共享锁
 
commit ---提交

 

mysql 共享锁 (lock in share mode)

结论

1.允许其它事务也增加共享锁读取

2.不允许其它事物增加排他锁 (for update)

3.当事务同时增加共享锁时候，事务的更新必须等待先执行的事务 commit 后才行，如果同时并发太大可能很容易造成死锁

共享锁，事务都加，都能读。修改是惟一的，必须等待前一个事务 commit，才可以

排它锁

for update

写锁

当一个事物加入排他锁后，不允许其他事务加共享锁或者排它锁读取，更加不允许其他事务修改加锁的行。

begin
 
select * from customers where Id = 1 for update ---for date 对这个查询语句使用排他锁
 
commit

总结
1.事务之间不允许其它排他锁或共享锁读取，修改更不可能
2.一次只能有一个排他锁执行 commit 之后，其它事务才可执行
不允许其它事务增加共享或排他锁读取。修改是惟一的，必须等待前一个事务 commit，才可
意向锁
表锁 意向共享锁和意向排它锁都是互相兼容的
意向共享锁

意向排它锁
临键锁
是一个范围锁 左开右闭 范围查询并且有数据命中

 

begin
--查询的字段必须是索引 且必须是范围查询 (2,4(不包含4 可以理解为到3)]这个范围1,2是没有锁住的 3+都是被锁住了 临检索锁住的是一个范围 这个范围的操作会被阻塞
select * from customers where id > 2 and id <4 for update  --for update 查询范围的时候就是临键锁
 
commit

间隙锁

是一个范围锁 查询没有记录命中，就退化成间隙锁了

begin
--查询的字段必须是索引 且必须是范围查询 (5,8(不包含8 可以理解为到7)]没有查到数据 这时候1,2，3,4,5是没有锁住的 8+都是被锁住了 这个范围的操作会被阻塞
--间隙锁锁住的就是没有查到这个范围的右边 左边不会被锁住 和临键锁类似
select * from customers where id > 5 and id <8 for update  --for update 查询范围的时候就是临键锁
 
commit