MySQL高级

检查系统下是否安装过MySQL

rpm -qa |grep -i mysql (检查是否有Mysql的rpm包)

删除

rpm -e –nodeps mysql*

yum remove mysql*

安装

先去官网下载yum的源rpm包,然后使用yum安装

tar.gz安装

1
./mysqld --initialize --user=mysql --datadir=/usr/local/mysql/data --basedir=/usr/local/mysql

路径

路径 解释 备注
/var/lib/mysql/ mysql数据库文件存放的地址 参考:https://blog.csdn.net/qq_26514509/article/details/88918036
/usr/share/mysql 配置文件目录 mysql.server等配置文件
/usr/bin 相关命令目录 mysqladmin,mysqldump等命令
/etc/init.d/mysql 启动停止脚本目录

可以使用mysqld –verbose –help |grep -A 1 ‘Default options’ 查看默认配置文件存放路径

打开远程链接

1
2
3
4
5
6
use mysql;
update user set user.Host='%' where user.User='root';
flush privileges;

##修改初始密码
alter user 'root'@'localhost' identified by '123456';

配置文件

二进制日志 log-bin

用于主从复制

错误日志 log-error

默认关闭,纪录严重的警告和错误信息,每次启动和关闭的详细信息等

查询日志 log

默认关闭,记录查询的sql语句,如果开启会降低mysql的整体性能,记录日志也是需要消耗资源的

数据文件

  • 系统数据库文件
    • linux:默认在var/lib/mysql
    • windows:在mysql server安装目录下的data
  • frm文件:存放表结构
  • myd文件:存放数据
  • myi文件:存放索引

存储引擎

对比项 MyIsAm InnoDb
主外键 不支持 支持
事务 不支持 支持
行表锁 表锁,即支持操作一条记录也会锁住整个表,并发效果差 行锁,操作时只锁某一行,不对其他行有影响,适合高并发
缓存 只缓存索引,不缓存真实数据 不仅缓存索引,且缓存真实数据,对内存要求高,内存大小对性能有决定性影响
表空间
关注点 性能 事务
默认安装 安装 安装

索引优化分析

性能下降SQL慢,执行时间长,等待时间长

  • 索引失效

    • 单值

      1
      create index idx_table_column on 表名(字段名);
    • 复合

      1
      create index id_table_column1column2 on 表名(字段1,字段2);
  • 关联查询join太多

常见的通用的Join查询

  • AB交集

    1
    select *from table A inner join table B on A.key = B.key ;
  • A的独有加AB共有

    1
    select *from table A left join table B on A.key = B.key;
  • B的独有加AB共有

    1
    select *from table A right join table B on A.key = B.key;
  • A的独有

    1
    select *from table A left join table B on A.key = B.key where B.key is null
  • B的独有

    1
    select *from table A right join table B on A.key = B.key where A.key is null
  • AB的笛卡尔乘积

    1
    2
    select *from table A full outer join table B on A.key = B.key;
    ## mysql支持 full outer join 可以使用Union
  • A的独有和B的独有

    1
    2
    select *from table A full out join table B on A.key = B.key where A.key is null 
    or B.key is null;

索引

描述

Mysql官方为索引定义:索引(index)是帮助Mysql高校获取数据的数据结构,本质上索引是数据结构,索引的的目的在与提高查询效率,可以类比字典。

排好序的快速查找数据结构

在数据之外,数据库系统还维护着满足特定查找算法的数据结构,这些数据结构以某种方式引用(指向数据),这样就可以在这些数据结构上实现高级查找算法。这种数据结构就是索引。

平时所说的索引基本都是指B树索引

优势

  • 提高数据检索的效率,降低数据库的IO成本
  • 通过索引列对数据排序,降低数据排序成本,降低CPU消耗

劣势

  • 实际上,索引也是一个表,该表保存了主键与索引字段,并指向实体记录表,所以索引列也是要占空间的。
  • 虽然索引大大提高了查询速度,但同时会降低更新表的速度,例如向表insert,update和delete,因为更新时,Mysql不仅要保存数据,还要保存一下索引文件每次更新添加了索引列的字段,都会调整因为更新带来的键值变化后的索引信息。

索引分类

单值索引

即一个索引只包含一个列,一个表可以有多个单索引列

唯一索引

索引列的值必须唯一,但允许为空置

复合索引

即一个索引包含多个列

基本语法

  • 创建

    1
    2
    3
    4
    5
    create [unique] index 索引名 on 表名(字段名(length));
    alter table add primary key (字段名); #添加一个主键索引,必须唯一,且不能为空
    alter table add unique 索引名 on (字段名); #添加一个唯一索引,可以为空
    alter table add index 索引名 on (字段名); #添加一个普通索引,索引值可以多次出现
    alter table add fulltext 索引名 on (字段名); #添加一个全文索引
  • 删除

    1
    drop index 索引名 on 表名;
  • 查看

    1
    show index from 表名;

需要创建索引情况

  • 主键自动创建索引
  • 频繁作为查询条件的字段应该创建索引
  • 查询中与其他表关联的字段,外键关系应该创建索引
  • 频繁更新的字段不适合创建索引
  • where条件里用不到的字段不创建索引
  • 查询中排序的字段
  • 查询中统计或分组的字段

不需要创建索引的情况

  • 记录太少
  • 经常需要变更的字段

  • 数值重复率高的字段

性能分析

  • Mysql Query Optimizer
  • Mysql常见瓶颈
    • CPU在饱和的时候一般发生在数据装入内存或在磁盘读取数据时
    • IO饱和
  • Expain:使用expain关键字可以模拟优化器执行sql语句,从而得知mysql是如何处理sql语句,分析性能瓶颈

Expain

  • 使用:expain sql语句

  • 表头:

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    | id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |

    id:表的加载顺序,越大越先执行,相同按顺序执行
    select_type:
    simple 简单的查询,不包含子查询和union
    primay 包含子查询的最外面的语句
    subquery 子查询,在select where
    derived 临时表
    union
    union result
    table:语句关联的表
    type:访问类型
    all
    index
    range
    ref
    eq_ref:唯一性扫描
    const:表示通过一次就索引到了
    system:表只有一行记录
    null
    最好到最差:system>const>eq_ref>ref>range>index>all
    possible_keys:显示在这个表上的索引,但不一定会使用
    key:实际用到的索引
    key_length:同样的查询条件下,越小越好
    ref:显示索引那几个被引用了
    rows:有多少行记录被优化器优化