MySQL 底层原理+优化

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

1、什么是索引？

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

2、索引的数据结构　　

  1. B+Tree(B-Tree变种)

    1. 非叶子节点不存储data，只存储索引(冗余)，可以放更多的索引

    2. 叶子节点包含所有索引字段

    3. 叶子节点用指针连接，提高区间访问的性能

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

  3. InnoDB索引实现(聚集)

    1. 表数据文件本身就是按B+Tree组织的一个索引结构文件

    2. 叶节点包含了完整的数据记录

    3. 建议InnoDB表必须建主键，并且推荐使用整型的自增主键
    4. 为什么非主键索引结构叶子节点存储的是主键值？(一致性和节省存储空间)


  4. 联合索引数据结构

     

二、Explain工具使用

1、explain？ 

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

  1. explain extended+show warnings：会在 explain 的基础上额外提供一些查询优化的信息。紧随其后通过 show warnings 命令可以得到优化后的查询语句
  2. 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：这一列展示的是额外信息。常见的重要值如下：
  1. Using index：使用覆盖索引
  2. Using where：使用 where 语句来处理结果，并且查询的列未被索引覆盖
  3. Using index condition：查询的列不完全被索引覆盖，where条件中是一个前导列的范围；
  4. Using temporary：mysql需要创建一张临时表来处理查询。出现这种情况一般是要进行优化的，首先是想到用索引来优化。
  5. Using filesort：将用外部排序而不是索引排序，数据较小时从内存排序，否则需要在磁盘完成排序。这种情况下一般也是要考虑使用索引来优化的。
  6. 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、优化例子

  1. 利用最左前缀法则：中间字段不能断，因此查询用到了name索引，从key_len=74也能看出，age索引列用在排序过程中，因为Extra字段里没有using filesort

  2.  从explain的执行结果来看：key_len=74，查询使用了name索引，由于用了position进行排序，跳过了age，出现了Using filesort。

  3. 查找只用到索引name，age和position用于排序，无Using filesort。
  4. 和Case 3中explain的执行结果一样，但是出现了Using filesort，因为索引的创建顺序为name,age,position，但是排序的时候age和position颠倒位置了。 
  5. 与Case 4对比，在Extra中并未出现Using filesort，因为age为常量，在排序中被优化，所以索引未颠倒，不会出现Using filesort
  1.  虽然排序的字段列与索引顺序一样，且order by默认升序，这里position desc变成了降序，导致与索引的排序方式不同，从而产生Using filesort。Mysql8以上版本有降序索引可以支持该种查询方式。
    　　
  2. 对于排序来说，多个相等条件也是范围查询

     

     

  3. 可以用覆盖索引优化 

     

     

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）

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

　　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  阅读(1407)  评论(0)  编辑  收藏  举报