MySQL 底层原理+优化

一、索引的底层数据结构与算法

1、什么是索引？

　　索引是帮助MySQL高效获取数据的排好序的数据结构。

2、索引的数据结构　　

B+Tree(B-Tree变种)
1. 非叶子节点不存储data，只存储索引(冗余)，可以放更多的索引
2. 叶子节点包含所有索引字段
3. 叶子节点用指针连接，提高区间访问的性能
MyISAM索引文件和数据文件是分离的(非聚集)
InnoDB索引实现(聚集)
1. 表数据文件本身就是按B+Tree组织的一个索引结构文件
2. 叶节点包含了完整的数据记录
3. 建议InnoDB表必须建主键，并且推荐使用整型的自增主键
4. 为什么非主键索引结构叶子节点存储的是主键值？(一致性和节省存储空间)
联合索引数据结构

二、Explain工具使用

1、explain？

　　在 select 语句之前增加 explain 关键字，MySQL 会在查询上设置一个标记，执行查询会返回执行计划的信息，而不是执行这条SQL

explain extended+show warnings：会在 explain 的基础上额外提供一些查询优化的信息。紧随其后通过 show warnings 命令可以得到优化后的查询语句
explain partitions：相比 explain 多了个 partitions 字段，如果查询是基于分区表的话，会显示查询将访问的分区。

2、explain中的列

id:

有几个 select 就有几个id，并且id的顺序是按 select 出现的顺序增长的。id列越大执行优先级越高，id相同则从上往下执行，id为NULL最后执行。

select_type:

对应行是简单还是复杂的查询

1. simple：简单查询。查询不包含子查询和union
2. primary：复杂查询中最外层的 select
3. subquery：包含在 select 中的子查询（不在 from 子句中）
4. derived：包含在 from 子句中的子查询。MySQL会将结果存放在一个临时表中，也称为派生表（derived的英文含义）
5. union：在 union 中的第二个和随后的 select

table这一列表示 explain 的一行正在访问哪个表。
type:

system > const > eq_ref > ref > range > index > ALL,一般来说要保证达到range级别

1. const, system：mysql能对查询的某部分进行优化并将其转化成一个常量（可以看show warnings 的结果）。
2. eq_ref：primary key 或 unique key 索引的所有部分被连接使用，最多只会返回一条符合条件的记录。
3. ref：相比 eq_ref，不使用唯一索引，而是使用普通索引或者唯一性索引的部分前缀，索引要和某个值相比较，可能会找到多个符合条件的行。
4. range：范围扫描通常出现在 in(), between ,> ,<, >= 等操作中。使用一个索引来检索给定范围的行。
5. index：扫描全索引就能拿到结果，一般是扫描某个二级索引，这种扫描不会从索引树根节点开始快速查找，而是直接对二级索引的叶子节点遍历和扫描，速度还是比较慢的，这种查询一般为使用覆盖索引，二级索引一般比较小，所以这种通常比ALL快一些。
6. ALL：即全表扫描，扫描你的聚簇索引的所有叶子节点。通常情况下这需要增加索引来进行优化了。

possible_keys：显示查询可能使用哪些索引来查找。
key：实际采用哪个索引来优化对该表的访问。
key_len：通过这个值可以算出具体使用了索引中的哪些列。

- 字符串：char(n)：3n ， varchar(n)： 3n + 2 ，加的2字节用来存储字符串长度，因为varchar是变长字符串

- 数值类型：tinyint：1 smallint：2 int：4 bigint：8字节

- 时间类型：date：3 timestamp：4 datetime：8

　　　　如果字段允许为 NULL，需要1字节记录是否为 NULL索引最大长度是768字节，当字符串过长时，mysql会做一个类似左前缀索引的处理，将前半部分的字符提取出来做索引。

ref：这一列显示了在key列记录的索引中，表查找值所用到的列或常量，常见的有：const（常量），字段名（例：film.id）
rows：这一列是mysql估计要读取并检测的行数，注意这个不是结果集里的行数。
Extra：这一列展示的是额外信息。常见的重要值如下：

Using index：使用覆盖索引
Using where：使用 where 语句来处理结果，并且查询的列未被索引覆盖
Using index condition：查询的列不完全被索引覆盖，where条件中是一个前导列的范围；
Using temporary：mysql需要创建一张临时表来处理查询。出现这种情况一般是要进行优化的，首先是想到用索引来优化。
Using filesort：将用外部排序而不是索引排序，数据较小时从内存排序，否则需要在磁盘完成排序。这种情况下一般也是要考虑使用索引来优化的。
Select tables optimized away：使用某些聚合函数（比如 max、min）来访问存在索引的某个字段是

3、优化建议

三、bin-log归档

1、如果误删了数据库，可以使用bin-log进行归档

Binlog在MySql的Server层实现
bin-log为逻辑日志，记录每条语句的原始逻辑
bin-log不限大小，追加写入，不会覆盖以前日志

2、开启bin-log

#配置开启binlog，my.conf
log‐bin=/usr/local/mysql/data/binlog/mysql‐bin
#注意5.7以及更高版本需要配置本项：
server‐id=123454（自定义,保证唯一性）;
#binlog格式，有3种statement,row,mixed
binlog‐format=ROW
#表示每1次执行写入就与硬盘同步，会影响性能，为0时表示，事务提交时mysql不做刷盘操作，由系统决定
sync‐binlog=1

bin-log 命令

1 mysql> show variables like '%log_bin%'; 查看bin‐log是否开启 
2 mysql> flush logs; 会多一个最新的bin‐log日志 
3 mysql> show master status; 查看最后一个bin‐log日志的相关信息 
4 mysql> reset master; 清空所有的bin‐log日志

查看bin-log内容

1 mysql> /usr/local/mysql/bin/mysqlbinlog ‐‐no‐defaults /usr/local/mysql/data/binlog/mysql‐bin. 000001 查看binlog内容

数据归档操作

1 从bin‐log恢复数据  
2 恢复全部数据 
3 /usr/local/mysql/bin/mysqlbinlog ‐‐no‐defaults /usr/local/mysql/data/binlog/mysql‐bin.000001 |mysql ‐uroot ‐p tuling(数据库名) 
4 恢复指定位置数据 
5 /usr/local/mysql/bin/mysqlbinlog ‐‐no‐defaults ‐‐start‐position="408" ‐‐stop‐position="731" /usr/local/mysql/data/binlog/mysql‐bin.000001 |mysql ‐uroot ‐p tuling(数据库) 
6 恢复指定时间段数据 
7 /usr/local/mysql/bin/mysqlbinlog ‐‐no‐defaults /usr/local/mysql/data/binlog/mysql‐bin.000001 ‐‐stop‐date= "2018‐03‐02 12:00:00" ‐‐start‐date= "2019‐03‐02 11:55:00"|mysql ‐uroot ‐p test(数 据库)

四、性能调优

1、索引设计原则

1、代码先行，索引后上

　　不知大家一般是怎么给数据表建立索引的，是建完表马上就建立索引吗？这其实是不对的，一般应该等到主体业务功能开发完毕，把涉及到该表相关sql都要拿出来分析之后再建立索引。

2、联合索引尽量覆盖条件

　　比如可以设计一个或者两三个联合索引(尽量少建单值索引)，让每一个联合索引都尽量去包含sql语句里的where、order by、group by的字段，还要确保这些联合索引的字段顺序尽量满足sql查询的最左前缀原则。

3、不要在小基数字段上建立索引

　　索引基数是指这个字段在表里总共有多少个不同的值，比如一张表总共100万行记录，其中有个性别字段，其值不是男就是女，那么该字段的基数就是2。如果对这种小基数字段建立索引的话，还不如全表扫描了，因为你的索引树里就包含男和女两种值，根本没

法进行快速的二分查找，那用索引就没有太大的意义了。一般建立索引，尽量使用那些基数比较大的字段，就是值比较多的字段，那么才能发挥出B+树快速二分查找的优势来。

4、长字符串我们可以采用前缀索引

　　尽量对字段类型较小的列设计索引，比如说什么tinyint之类的，因为字段类型较小的话，占用磁盘空间也会比较小，此时你在搜索的时候性能也会比较好一点。当然，这个所谓的字段类型小一点的列，也不是绝对的，很多时候你就是要针对varchar(255)这种字段建立

索引，哪怕多占用一些磁盘空间也是有必要的。对于这种varchar(255)的大字段可能会比较占用磁盘空间，可以稍微优化下，比如针对这个字段的前20个字符建立索引，就是说，对这个字段里的每个值的前20个字符放在索引树里，类似于 KEY

index(name(20),age,position)。此时你在where条件里搜索的时候，如果是根据name字段来搜索，那么此时就会先到索引树里根据name字段的前20个字符去搜索，定位到之后前20个字符的前缀匹配的部分数据之后，再回到聚簇索引提取出来

完整的name字段值进行比对。但是假如你要是order by name，那么此时你的name因为在索引树里仅仅包含了前20个字符，所以这个排序是没法用上索引的， group by也是同理。所以这里大家要对前缀索引有一个了解。

5、where与order by冲突时优先where

　　在where和order by出现索引设计冲突时，到底是针对where去设计索引，还是针对order by设计索引？到底是让where去用上索引，还是让order by用上索引?一般这种时候往往都是让where条件去使用索引来快速筛选出来一部分指定的数据，接着再进行排序。

因为大多数情况基于索引进行where筛选往往可以最快速度筛选出你要的少部分数据，然后做排序的成本可能会小很多。

6、基于慢sql查询做优化

　　可以根据监控后台的一些慢sql，针对这些慢sql查询做特定的索引优化。

2、索引下推（like'Lilei%'）

　可以在索引遍历过程中，对索引中包含的所有字段先做判断，过滤掉不符合条件的记录之后再回表，可以有效的减少回表次数。

　　为什么范围查找Mysql没有用索引下推优化？

　是Mysql认为范围查找过滤的结果集过大，like KK% 在绝大多数情况来看，过滤后的结果集比较小，所以这里Mysql选择给 likeKK% 用了索引下推优化，当然这也不是绝对的，有时like KK% 也不一定就会走索引下推。

3、Order by与Group by优化

1、优化例子

利用最左前缀法则：中间字段不能断，因此查询用到了name索引，从key_len=74也能看出，age索引列用在排序过程中，因为Extra字段里没有using filesort
从explain的执行结果来看：key_len=74，查询使用了name索引，由于用了position进行排序，跳过了age，出现了Using filesort。
查找只用到索引name，age和position用于排序，无Using filesort。
和Case 3中explain的执行结果一样，但是出现了Using filesort，因为索引的创建顺序为name,age,position，但是排序的时候age和position颠倒位置了。
与Case 4对比，在Extra中并未出现Using filesort，因为age为常量，在排序中被优化，所以索引未颠倒，不会出现Using filesort

虽然排序的字段列与索引顺序一样，且order by默认升序，这里position desc变成了降序，导致与索引的排序方式不同，从而产生Using filesort。Mysql8以上版本有降序索引可以支持该种查询方式。
　　
对于排序来说，多个相等条件也是范围查询
可以用覆盖索引优化

2、优化总结

　　1、MySQL支持两种方式的排序filesort和index，Using index是指MySQL扫描索引本身完成排序。index效率高，filesort效率低。

　　2、order by满足两种情况会使用Using index。

　　　　1) order by语句使用索引最左前列。

　　　　2) 使用where子句与order by子句条件列组合满足索引最左前列。

　　3、尽量在索引列上完成排序，遵循索引建立（索引创建的顺序）时的最左前缀法则。

　　4、如果order by的条件不在索引列上，就会产生Using filesort。

　　5、能用覆盖索引尽量用覆盖索引

　　6、group by与order by很类似，其实质是先排序后分组，遵照索引创建顺序的最左前缀法则。对于groupby的优化如果不需要排序的可以加上order by null禁止排序。注意，where高于having，能写在where中的限定条件就不要去having限定了。

4、分页查询优化

　　很多分页查询的sql实现，看似只查询了 10 条记录，实际这条 SQL 是先读取 10010条记录，然后抛弃前 10000 条记录，然后读到后面 10 条想要的数据。因此要查询一张大表比较靠后的数据，执行效率是非常低的。

 mysql> select * from employees limit 10000,10;

　　常见技巧：

　　1、根据自增且连续的主键排序的分页查询
　　

　　满足以下两个条件：

- 主键自增且连续

- 果是按照主键排序的

　　2、根据非主键字段排序的分页查询　　　

select * from employees ORDER BY name limit 90000,5;  （没有使用name索引）
select * from employees e inner join (select id from employees order by name limit 90000,5) ed on e.id = ed.id; （使用了索引，并且时间减少了一半以上）

　　3、Join关联查询优化
　　　　3.1 标关联常见的两种算法　　　　　　

- - 嵌套循环连接 Nested-Loop Join(NLJ) 算法（走索引）
    
    一次一行循环地从第一张表（称为驱动表）中读取行，在这行数据中取到关联字段，根据关联字段在另一张表（被驱动表）里取出满足条件的行，然后取出两张表的结果合集。
  - Block Nested-Loop Join 算法（不走索引）
    
    把驱动表的数据读入到 join_buffer 中，然后扫描被驱动表，把被驱动表每一行取出来跟 join_buffer 中的数据做对比。
    
    对于关联sql的优化
    
    关联字段加索引：让mysql做join操作时尽量选择NLJ算法
    
    小表驱动大表：写多表连接sql时如果明确知道哪张表是小表可以用straight_join写法固定连接驱动方式，省去mysql优化器自己判断的时间

　　　　　3.2 in和exsits优化

 select * from A where id in (select id from B)

　　　　　　　　in：当B表的数据集小于A表的数据集时，in优于exists

　　　　　　　　exists：当A表的数据集小于B表的数据集时，exists优于in

　　　　　　3.3 count(*)查询优化

　　　　　　　　字段有索引：count(*)≈count(1)>count(字段)>count(主键 id) //字段有索引，count(字段)统计走二级索引，二级索引存储数据比主键索引少，所以count(字段)>count(主键 id)

　　　　　　　　字段无索引：count(*)≈count(1)>count(主键 id)>count(字段) //字段没有索引count(字段)统计走不了索引，count(主键 id)还可以走主键索引，所以count(主键 id)>count(字段)

5、阿里Mysql规范解读

　　1、数值类型
　　

优化建议

1. 如果整形数据没有负数，如ID号，建议指定为UNSIGNED无符号类型，容量可以扩大一倍。

2. 建议使用TINYINT代替ENUM、BITENUM、SET。

3. 避免使用整数的显示宽度(参看文档最后)，也就是说，不要用INT(10)类似的方法指定字段显示宽度，直接用

INT。

4. DECIMAL最适合保存准确度要求高，而且用于计算的数据，比如价格。但是在使用DECIMAL类型的时候，注意

长度设置。

5. 建议使用整形类型来运算和存储实数，方法是，实数乘以相应的倍数后再操作。

6. 整数通常是最佳的数据类型，因为它速度快，并且能使用AUTO_INCREMENT。

2、日期和时间
　　

　　优化建议

　　1. MySQL能存储的最小时间粒度为秒。

　　2. 建议用DATE数据类型来保存日期。MySQL中默认的日期格式是yyyy-mm-dd。

　　3. 用MySQL的内建类型DATE、TIME、DATETIME来存储时间，而不是使用字符串。

　　4. 当数据格式为TIMESTAMP和DATETIME时，可以用CURRENT_TIMESTAMP作为默认（MySQL5.6以后），MySQL会自动返回记录插入的确切时间。

　　5. TIMESTAMP是UTC时间戳，与时区相关。

　　6. DATETIME的存储格式是一个YYYYMMDD HH:MM:SS的整数，与时区无关，你存了什么，读出来就是什么。

　　7. 除非有特殊需求，一般的公司建议使用TIMESTAMP，它比DATETIME更节约空间，但是像阿里这样的公司一般会用DATETIME，因为不用考虑TIMESTAMP将来的时间上限问题。

　　8. 有时人们把Unix的时间戳保存为整数值，但是这通常没有任何好处，这种格式处理起来不太方便，我们并不推荐它。

3、字符串
　　

　　优化建议

　　1. 字符串的长度相差较大用VARCHAR；字符串短，且所有值都接近一个长度用CHAR。

　　2. CHAR和VARCHAR适用于包括人名、邮政编码、电话号码和不超过255个字符长度的任意字母数字组合。那些要用来计算的数字不要用VARCHAR类型保存，因为可能会导致一些与计算相关的问题。换句话说，可能影响到计算的准确性和完整性。

　　3. 尽量少用BLOB和TEXT，如果实在要用可以考虑将BLOB和TEXT字段单独存一张表，用id关联。

　　4. BLOB系列存储二进制字符串，与字符集无关。TEXT系列存储非二进制字符串，与字符集相关。

　　5. BLOB和TEXT都不能有默认值。

五、事务

　　1、事务的特点（ACID）

原子性：一个事务是对数据库操作的最小单位，不可细分。
隔离性：事务之间可以同时执行，不会相互干扰。
一致性：事务执行成功数据库变更，事务执行失败则不会变更。
持久性：事务执行成功以后，结果是持久的。

　　2、事务的隔离级别

　　　　常看当前数据库的事务隔离级别: show variables like 'tx_isolation';

　　　　设置事务隔离级别：set tx_isolation='REPEATABLE-READ';

　　　　Mysql默认的事务隔离级别是可重复读，用Spring开发程序时，如果不设置隔离级别默认用Mysql设置的隔离级别，如果Spring设置了就用已经设置的隔离级别

　　3、锁

　　　　结论1

　　　　1、对MyISAM表的读操作(加读锁) ,不会阻寒其他进程对同一表的读请求,但会阻赛对同一表的写请求。只有当读锁释放后,才会执行其它进程的写操作。

　　　　2、对MylSAM表的写操作(加写锁) ,会阻塞其他进程对同一表的读和写操作,只有当写锁释放后,才会执行其它进程的读写操作

　　　　3、MyISAM在执行查询语句SELECT前，会自动给涉及的所有表加读锁,在执行update、insert、delete操作会自动给涉及的表加写锁。

　　　　4、InnoDB在执行查询语句SELECT时(非串行隔离级别)，不会加锁。但是update、insert、delete操作会加行锁。

　　　　锁优化建议

　　　　1、尽可能让所有数据检索都通过索引来完成，避免无索引行锁升级为表锁

　　　　2、合理设计索引，尽量缩小锁的范围

　　　　3、尽可能减少检索条件范围，避免间隙锁

　　　　4、尽量控制事务大小，减少锁定资源量和时间长度，涉及事务加锁的sql尽量放在事务最后执行

　　　　5、尽可能低级别事务隔离

posted @ 2020-10-10 23:19 vvning 阅读(1426) 评论(0) 编辑收藏举报

刷新页面返回顶部

vvning

MySQL 底层原理+优化

一、索引的底层数据结构与算法

1、什么是索引？

2、索引的数据结构

B+Tree(B-Tree变种)

MyISAM索引文件和数据文件是分离的(非聚集)

InnoDB索引实现(聚集)

联合索引数据结构

二、Explain工具使用

1、explain？

2、explain中的列

id:

select_type:

table这一列表示 explain 的一行正在访问哪个表。

type:

system > const > eq_ref > ref > range > index > ALL,一般来说要保证达到range级别

3、优化建议

三、bin-log归档

1、如果误删了数据库，可以使用bin-log进行归档

2、开启bin-log

四、性能调优

1、索引设计原则

1、代码先行，索引后上

2、联合索引尽量覆盖条件

3、不要在小基数字段上建立索引

4、长字符串我们可以采用前缀索引

5、where与order by冲突时优先where

6、基于慢sql查询做优化

2、索引下推（like'Lilei%'）

为什么范围查找Mysql没有用索引下推优化？

3、Order by与Group by优化

1、优化例子

2、优化总结

4、分页查询优化

常见技巧：

1、根据自增且连续的主键排序的分页查询

2、根据非主键字段排序的分页查询

3、Join关联查询优化 3.1 标关联常见的两种算法

3.2 in和exsits优化

3.3 count(*)查询优化

5、阿里Mysql规范解读

1、数值类型

2、日期和时间

3、字符串

五、事务

1、事务的特点（ACID）

2、事务的隔离级别

3、锁

结论1

锁优化建议

公告

2、索引的数据结构　　

　　为什么范围查找Mysql没有用索引下推优化？

　　常见技巧：

　　1、根据自增且连续的主键排序的分页查询
　　

　　2、根据非主键字段排序的分页查询　　　

　　3、Join关联查询优化
　　　　3.1 标关联常见的两种算法　　　　　　

　　　　　3.2 in和exsits优化

　　　　　　3.3 count(*)查询优化

　　1、数值类型
　　

2、日期和时间
　　

3、字符串
　　

　　1、事务的特点（ACID）

　　2、事务的隔离级别

　　3、锁

　　　　结论1

　　　　锁优化建议