mysql

发表评论
150 views

mysql 构成

连接池，SQL 接口，解析器，优化器，缓存，存储引擎等组成，分成，连接层，服务处，引擎层和文件系统层

mysql 物理文件

日志文件包括，错误文件 error log ,二进制日志文件 bin log ,中继日志 relay log 满查询日志 slow log 1. show variables like 'log_error'; 2. show variables like 'slow_query_log_file'; 3. bin log 日志文件 需要在my.cnf 配置 log-bin =/var/log/mysql-bin/bin.log server-id =2

冷备份

关闭SELinux 1. 修改selinux 配置文件，将SELINUX=enforcing 改为SELINUX=disabled 保存后退出 shell vim /etc/slinux/config selinux = disbled 2. 修改后需要重启 reboot 3. sevice mysqld stop 4. 到 数据库所在文件夹 tar -jcvf mysql.tar.bz2 mysql

热备份

mysqldump -uroot -p database > database.sql mysqldump -uroot -p database | gzip > database.sql.gz 压缩备份 gunzip < database.sql.gz | mysql -uroot -p database;

mysql 查询和慢查询的日志分析

性能下降的原因

  1. 等待时间长 锁表导致查询一直处于等待状态，我们需要从mysql 锁的方面去分析sql ，表级锁，行级锁
  2. 执行时间长 查询语句有问题 ， 索引失效 ， 关联查询太多join ，服务器调优以及各个参数设置

需要遵守的优化原则

  1. 只返回需要的结果 一定要为查询语句指定where条件，过滤掉不需要的数据行 避免使用select * from
  2. 确保查询使用了正确的索引 经常出现在where条件字段建立索引，避免全表扫描 将orderby 排序的字段加入索引中，可以避免额外的排序操作 多表连接查询的关联字段建立索引，可以提高连接查询的性能 将group by 分组操作字段加入索引中，利用索引完成分组
  3. 避免让索引失效 在where字句中对索引字段进行表达式运算或者使用函数都会导致索引失效 在使用like 匹配时，如果通配符出现在左侧无法使用索引 如果where 条件中的字段创建索引，尽量设置为NOT null

SQL 执行顺序

  1. 程序 select distinct from joinonwheregroup_by having order by limit
  2. mysql 执行的sql from on join where group by having select distinct order by limit

join 查询

7种join，可以分成4类，内连接，左连接，右连接，全连接 1. 左连接：select * from a left join b on a.id = b.id (A or(A and B)) 2. 右连接：select * from a right join b on a.id = b.id (B or(Aand B)) 3. 内连接：select * from a inner join b on a.id = b.id (A and B) 4. 左连接只包含A ： select * from a left join b on a.id = b.id where b.id is NULL 5. 右连接只包含B ： select * from a right join b on a.id = b.id where a.id is NULL 6. 全连接 select * from a left join b on a.id = b.id union select * from a right join b on a.id = b.id (A or B) 7. 全连接去除Aand B ： select * from a right join b on a.id = b.id where a.id is null union select * from a left join b on a .id = b.id where b.id is null;

``` select * from t_emp e inner join t_dept d on e.deptid = d.id //内连接 select * from t_emp e left join t_dept d on e.deptid = d.id; //左连接 左表+右表共有 select * from t_emp e left join t_dept d on e.deptid= d.id where d.id is NULL;//左连接，获取左表独有 select * from t_emp e right join t_dept d on e.deptid = d.id; //右连接，右表+ 左表公用 select * from t_emp e right join t_dept d on e.deptid =d.id where e.id is NULL;//右连接，获取右表独有 select * from t_emp e left join t_dept d on e.deptid = d.id union select * from t_emp e right join t_dept d on e.deptid =d.id; //全连接 select * from t_emp e left join t_dept d on e.deptid = d.id where d.id is NULL union select * from t_emp e right join t_dept d on e.deptid = d.id where e.id is NULL;//各自独有的

```

慢查询日志

用来记录在mysql 中相应时间超过阈值的语句，默认不开启慢查询日志需要手动设置这个参数，如果不是调优不建议开启，开启后会带来一定的性能损失，慢查询日志 支持将日志记录写入文件和数据库表

慢查询参数

  1. show variables like '%query%';
  2. show_query_log :1 表示开启，0 表示关闭
  3. long_query_time : 慢查询阈值，当查询时间多于设定的阈值时记录日志。

开启慢查询

mysql > set global slow_query_log =1;
cd /etc/my.cnf
增加 slow_query_log = 1
增加 slow_query_log_file = /var/lib/mysql/slow.log
重启mysql
用long_query _time 控制 记录set global long_query_time = 1;
log_queryies_not_using_indexes; 没有用到索引的也记录
重开进入mysql

mysql存储引擎

存储引擎是如何存储数据，如何为存储数据建立索引和如何更新查询数据等技术的实现方法

存储引擎的作用

并发性，事务支持，引用完整性，索引支持

常见的三种数据引擎

两大类 1. 事务安全表：InnoDB 2. 非事务安全表： MyISAM,MEMORY

InnoDB

InnoDB 引入了行级锁定和外键约束

  1. 提供了对数据库ACID 事务支持，实现了SQL 标准的四种隔离
  2. 支持多版本并发的行级锁，粒度小，写和更新操作并发高，速度快
  3. 支持自增长列，外键
  4. 适合大容量数据库系列，支持自动灾难恢复
  5. 没有保存表的函数，当select count(*) from table 需要全表扫描

MyISAM

MyISAM 不支持事务，不支持主键，访问快，对事务完整性没有要求或者以select ,insert 为主的应用可以使用这个引擎

  1. MyISAM 表独立于操作系统的，可以从windows 移植到Linux
  2. MyISAM 查询很快，大批量插入 操作执行速度也比较快
  3. 没有事务支持，不支持行级锁和外键，不适合经常update的表，效率低
  4. 适合已读为主的业务，如图片信息，博客，商品库
  5. 对数据一致性要求不是非常高，硬件资源比较差的可以用

MEMORY

表中数据放在内存中，适用于存储临时数据的临时表和数据仓库中纬度表

  1. memory访问快，存储在内存中
  2. 服务关闭，数据就会丢掉
  3. 支持表所，并发差，不支持 TEXT和BLOB列类型，存储 varchar时是按照char的方式
  4. 适用于目标数据小且非常频繁访问呢，数据是临时必须立刻使用，存储在内存总的数据如果突然丢失对服务不会产生实质影响

MySQL 索引优化

索引就是排好序的，帮助我们进行快速查找的数据结构从某种程度上说，索引就是一个排好序的列表，这个列表中存储索引的值和这个值数据所在行的物理地址，在数据库十分大的时候 可以加快查询的速度，因为使用索引后可以不用扫描全表来定位某行的数据，而是通过索引表找到该行数据对应的物理地址然后访问相应的数据

  1. 没有索引时执行select * from where t.col2 = ,数据从裁判一条一条去最终的结果，效率地下
  2. 为了加快查找，可以维护一个二叉树，左侧节点小于父节点，右侧节点大于父节点，每个节点分别保存字段数据和一个指向对应数据记录物理地址的指针
  3. 查找时，可以使用二叉树查找获取相应的数据，从而快速检索出符合条件的记录

索引

普通索引 唯一索引

create Table user1(
    id int(11) primary key,
    name varchar(20),
    card varchar(50),
    index idx_name(name),
    unique idx_card(card)
)

复合索引

create Table user2(
     id int(11) primary key,
     name varchar(20),
     card varchar(30),
     index idx_name_card(name,card)
);

注意点： 1. 使用复合索引，要根据where条件建立索引，不要过多使用索引否则对更新操作效率有很大影响 2. 如果表已经建立（col1,co2) 就没有必要单独建立 （col1）索引，如果现在有（col1)索引，如果必须要col1和col2查询条件可以建立（col1,col2)复合索引

全文索引

  1. 查询操作在数据量比较少，可以使用like 模糊查询，但是对于大量的文本数据检索，效率很低，如果使用全文索引，查询速度回比like快很多倍，在5.6之前myisam 支持全文索引，5.6之后 myiSAM 和 innodb 都支持
   create fullText index <索引的名字> on tablename(字段名)
   alter table tablename Add fullText [索引的名字](字段名)
   create table tablename ([...], fullText key [ 索引的名字]（字段名）)；
  1. 全文索引方式有自然语言检索IN natural language mode 和布尔检索 in boolean mode 两种
  2. 和常用的like 模糊查询不同，全文索引有自己的语法格式，使用match 和against 比如 select * from users3 where match(name) against("aabb"); select * from users3 where match(name) against('aa*',in boolean mode);
  3. 全文索引使用注意 全文索引必须在字符串和文本字段上建立 全文索引字段值必须在最小字符和最大字符之间的才会生效

索引的劣势和优势

  1. 索引提高了检索的效率，降低数据库的IO成本，另外对索引数据进行排序降低数据排序的成本，降低了CPU 消耗
  2. 创建维护索引需要耗费时间，随着数据量增加而增加，另外索引也占用物理空间，除了数据表之外，每一个索引还占用了一定的物理空间
  3. 当对表中的数据进行增删改操作，索引也要动态维护，降低了数据的维护速度

索引创建原则

  1. 在经常查询的列上创建索引
  2. 主键上通常创建索引，强制该列的唯一性和组织表中数据的排列结构
  3. 经常用的链接列上，这些列主要是一些外键，可以加快链接的速度
  4. 在经常根据范围内搜索列上创建索引，索引已经排序，指定的范围是连续的
  5. 在经常需要排序的列上，因为索引已经排序，这样查询可以利用索引的排序，加快排序查询时间
  6. 在经常使用在where字句中的列上创建索引，加快条件的判断速度。

索引原理

索引中常用数据结构有两种，一种是Hash 一种是BTree

Hash 结构

Hash 底层实现是由Hash 表来实现的，是根据键值存储数据的结构，

  1. 对于每一行数据，存储引擎会对所有的索引列计算一个哈希码，码的值很小，不同的键值行计算出来的哈希码不一样
  2. 哈希索引只包含哈希值和行指针，不存储字段，不能使用索引的值来避免读取行
  3. 哈希索引数据并不是按照索引值顺序存储的，所以无法用来排序
  4. 哈希索引只支持等值比较不支持任何范围查询和部分索引列匹配查询
  5. 适合不包含排序或者范围查询的需求
  6. 访问哈希索引数据非常快，除非有很多哈希冲突

B+Tree 结构

Mysql 索引采用的是B+Tree结构

  1. 非叶子节点不存储数据只存储 索引值，好处便于存储更多的索引值
  2. 叶子节点存储索引的索引值和data 数据
  3. 叶子节点使用指针链接，提高区间的访问性能。

B树索引的应用 1. 全键值查询 where x = 123 2. 键值范围查询 where 45 < x < 123

EXPLAIN 性能分析

使用explain 关键字可以模拟优化器执行SQL 查询语句，分析表结构性能瓶颈

  1. 表的读取顺序（id)
  2. 数据读取操作的操作类型 （select_type)
  3. 哪些索引可以使用（possible_keys)
  4. 哪些索引被实际使用（对应key)
  5. 表直接的引用 （对应ref)

  6. 每张表有多少行被优化器（对应rows)

    subquery 在select 或者 where 列表中包含子查询 derived 在from 列表中包含的子查询被标记为derived (衍生)mysql 会递归执行这些子查询，结果放在临时表中 union 如果第二个select 出现在union 之后标记为union，如果union 包含在 from字句的子查询中，外层select标记为derived union result :union 的结果

type 介绍

type 是链接类型，是一个重要指标，从最佳到最坏类型进行排序 system > const > eq_ref >ref >fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index >all

  1. system 表仅有一行 这是const 连接类型的一个特例很少出现
  2. const 表示通过索引 一次就找到了，const 用于比较primary key 或者unique 索引，因为只匹配一行数据，所以如果将主键放在where条件中 Mysql 就能将该查询转换为一个常量
  3. eq_ref :唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配，常见与主键或唯一索引扫描
  4. rane 只检索给定范围的行，使用一个索引来选择行，key 显示使用了哪个索引，where 字句后面，使用 between ,< ，>,=
  5. index 出现 index是SQL 使用了索引 ，但是没有通过索引进行过过滤，一般是使用索引进行排序分组
  6. all 对于每个来自于先前的表的行组合，进行完整的表扫描
  7. possible_keys :显示可能应用到这张表上的索引，一个或者多个，查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询实际使用
  8. key 实际使用的索引，若为null,则没有使用到索引，一方面是没建立索引，另一方建立索引但是索引失效，查询中若使用了覆盖索引，则该索引仅出现在key 列表中，覆盖索引，一个索引包含或覆盖所有需要查询的字段的值 9.key_len 表示索引中使用的字节数，可以通过该列计算查询中使用索引的长度，key_len 字段能帮你检查是否充分利用索引，key_len 越长，说明索引使用的越充分
  9. ref 显示索引的哪一列被使用了，可以是一个常数 11.rows 表示mysql根据表统计信息以及索引选用情况，估算找到所需的记录所需读取的行数，越少越好，使用 like需要全表扫描，2，使用等值查询，则可以直接找到要查询的记录返回即可，只需要一条

总结：当我们需要优化一个SQL语句，需要知道该sql的执行计划，是全表扫描还是索引扫描，使用explain 关键字可以模拟优化器执行sql语句，知道如何处理sql 一般优化流程，表的读取顺序 id ，数据读取操作类型 select_type 哪些索引可以使用 possible_keys，哪些被实际使用key，每张表有多少行被优化器查询rows，评估sql 质量和效率type

extra 是explain输出中另外一个很重要的列，显示在查询过程中的一些详细信息 using filesort 执行结果extra 为filesort 说名需要对所有记录进行文件排序，这类sql 语句性能极差需要进行优化，主要是因为没有在索引列上进行order by 处罚filesort 常见方法加上索引，避免每次查询都是全量排序 using temporary 需要建立临时表来存储中间结果，常见与group by 和order by sql 语句需要优化 filtered 只返回结果占需要读取到行的百分比 using where 表示使用了where 条件过滤数据

返回所有记录的SQL 不使用where条件过滤 需要优化，使用where 不代表不需要优化，需要配合explain 中的type来综合判断