MySQL高级篇来了！索引优化？分库分表？通通安排！！_mysql execution: 103 ms, fetching: 66 ms-优快云博客

本文链接：https://blog.youkuaiyun.com/weixin_43873227/article/details/107275273

文章目录

第二部分、MySQL高级

第二部分、MySQL高级

13、Linux安装MySQL

检查是否安装了MySQL
```
rpm -qa|grep mysql
```
若有使用rpm -e mysqlxxx或者yum remove mysql一一卸载
rpm按顺序安装四个文件

安装client时可能遇到这个报错
查看是否安装成功

启动Mysql服务

systemctl start mysql # 启动服务
systemctl stop mysql # 停止服务

然后获取临时密码(存放位置：/var/logs/mysqld.log中)
```
cat /var/log/mysqld.log|grep password
```

使用临时密码登录后，进入首先需要修改密码

SET PASSWORD = PASSWORD('newpassword'); 
# 或者使用
ALTER USER USER() IDENTIFIED BY 'newpassword';

默认的密码策略要求严格（在默认密码的长度最小值为 4 ，由大/小写字母各一个 + 阿拉伯数字一个 + 特殊字符一个），可以进行修改降低
```
set global validate_password_policy=LOW; # 将密码验证强度将为低
set global validate_password_length=6; # 将密码长度设置为6
```

初次进行远程连接会被拒绝报错1130，是因为只允许localhost连接

mysql>use mysql;

mysql>select 'host' from user where user='root';

mysql>update user set host = '%' where user ='root';

mysql>flush privileges;

mysql>select 'host'   from user where user='root';

开启服务开机自启

systemctl enable mysqld # 设置自启

ntsysv # 查看服务自启状态

14、配置文件

配置文件在Windows下是my.ini，在Linux中是 my.cnf位置：/etc/my.cnf

以下是默认配置

路径	解释
/var/lib/mysql	mysql数据文件的存放路径，datadir
/usr/share/mysql	配置文件目录
/usr/bin/mysql*	mysql命令存放目录

修改字符集编码

mysql> show variables like '%char%'; # 查看字符集编码

修改配置文件：

# 在mysqld下添加
character-set-server = utf8mb4
collation-server = utf8mb4_general_ci
# 在mysql下添加
default-character-set = utf8mb4

重新启动Mysql服务，然后重新建库，建表就可以使用中文了。

其他主要配置项、配置文件
- 二进制日志：log-bin(主要用于主从复制)
- 错误日志：log-error
  
  默认是关闭的，记录严重警告和错误记录，以及每次启动和关闭的详细信息。
- 查询日志
  
  默认关闭，记录查询的SQL语句，开启会降低Mysql的整体性能，有利于我们排查慢SQL;
- 数据文件
  
  Windows系统：安装目录下的data文件夹中存放数据库的数据文件
  
  Linux：/var/lib/mysql 目录下，使用ls -lF|grep ^d目录即可查看所有的库
  
  MyIASM引擎：
  - .frm:表结构
  - .myd:表数据
  - .myi:表索引
  InnoDB引擎：由于InnoDB的索引和其数据文件是一体的，所以只有两个文件
  - .frm(在Mysql8.0+后移除)
  - .ibd(数据/索引文件)

15、MySQL Server分层

连接层
服务处
引擎层
存储层

Mysql是可拔插的，可高度定制化的。在应对不同场景时发挥稳定。

插件式的存储引擎架构将查询处理举额其它的系统任务以及数据的存储提取相分离。

可以根据业务的需求和实际情况来选择合适的存储引擎。

16、MySQL引擎

mysql中show engines可以查看所有的引擎，以及当前默认使用的engine

show variables like ‘%storage_engine%’可以获取当前使用存储引擎的信息

MyISAM和InnoDB对比

对比项	MyISAM	InnoDB
外键	不支持	支持
事务	不支持	支持
行/表锁	表锁，即使操作一条记录，就会锁住整张表。不适合高并发操作。	行锁，操作时只锁定某一行，对其他行不影响，适合高并发操作
缓存	只缓存索引，不缓存真实数据。	不仅缓存索引，还缓存真实数据，对内存要求较高，内存大小对性能有决定性影响。
表空间	小	大（由于同时存放了索引和数据）
侧重点	性能，适合用于查找	事务
默认安装	YES	YES

Alibaba使用的MySQL

Percona为MySQL数据库服务器进行了改进，在功能和性能上较MySQL有着很显著的提升。该版本提升了在高负载情况下的InnoDB的性能，衍生出来一种新的存储引擎：XtraDB.
XtraDB完全可以代替InnoDB,并且它在高并发和性能方面表现更加出色。
阿里巴巴大部分MySQL数据库其实就是使用的Percona的原型(XtraDB)并加以修改。
AliSQL+AliRedis 已经进行了开源

17、索引优化分析

慢SQL形成的原因

SQL写的烂
索引失效（建了索引，但是由于SQL语句不合理索引没用上）

创建索引：
```
# 单值索引
create idx_user_name on `user`(name); 

# 联合索引
create (idx_user_name, idx_user_age) on `user`(name,age);
```
删除索引
```
drop index [index_name] on `table_name`
```
查看索引

infomation_schema是MySQL的元数据库，所有的元数据都存放在这个库中，其中statistics表存放着所有索引的相关信息。
```
show index from `table_name`
```
关联查询(join)太多
服务器调优及各种参数的设置(缓冲，线程数等)

机器执行SQL的顺序：

from `table_name`
on 'join_condition'
join_type join `join_table`
where 'select_condition'
group by 'group_by_list'
having 'having_condition'
select
distinct `select_list`
order by 'order_by_condition'
limit 'offset,rows'

索引选择

复合索引的选择优先于单值索引
每张表的索引数量不超过5个
索引并不是越多越好，索引也会占用空间！
- 推荐创建索引的情况
  1. 主键自动建立索引(InnoDB自动创建)
  2. 频繁作为查询条件的字段建立索引
  3. 查询中与其他表关联的字段（外键关系）建立索引
  4. 查询中排序的字段(Order BY),建立索引会大大调高排序的速度
  5. 查询中统计或者分组的字段(Group BY)
- 不推荐建立索引的情况
  1. 频繁修改的字段不推荐建立索引
  2. 表记录较少(<2000)
  3. 频繁增删改的字段
  4. 选择性不高的字段
    
    选择性：列数据的基数/列数据的总记录数 (0,1]

性能分析

MySQL Query Optimizer(查询优化分析器)

MySQL服务内置，通过计算分析系统中收集到的统计信息，自动优化SQL语句达到MySQL认为最优的执行计划。(但是不一定是DBA认为的最优)

Explain关键字

可以模拟优化器执行SQL查询语句，从而知道MySQL是如何处理我们的SQL语句的。方便我们对SQL语句性能进行分析。

使用这个关键字能获取那些信息？

表的读取顺序
数据读取操作的操作类型
那些索引可以被使用
用到了那些索引
表之间的引用
每张表多少行被优化器查询

性能分析表字段解析

id：
select查询的序列号，包含一组数字，表示查询中执行select子句或操作表的顺序。
- id相同：表示执行顺序从上到下依次执行。
- id不同：id递增，一般常见在嵌套子查询，之间有父子关系，id越大执行优先级越高，越先被执行。
- id相同、不同同时存在：结合前两条的规则，先按照优先级执行，然后相同id再顺序执行。
select_type
查询的类型
1. SIMPLE: 查询语句中不包含子查询和union
2. PRIMARY: 当查询中包含若干子查询，最外层的查询则为PRIMARY
3. SUBQUERY: 在Select和Where列表中包含了子查询，或子查询本身
4. DERIVED: 在from列表中包含的子查询,被标记未DERIVED(衍生)，MySQL会递归执行这些子查询，将结果放在临时表中。
5. UNION: 若第二个Select出现在union之后，会被标记为UNION, 若union出现在from列表的子查询中，外层select会被标记会DERIVED.
6. UNION_RESULT: 从union表获取结果的select会被标记为UNION_RESUL
table

执行过程中查询了哪张表。
type
查询访问表的访问类型

从优到劣：

system > const > eq_ref > ref > range > index > ALL

优化至少达到range
- system: 表只有一行记录(相当于系统表)，是const的特例。
- const: 表示通过一次索引就定位到数据,用于比较primary key或者unique索引。因为只匹配一行数据，速度很快。如将主键置于where条件中，MySQL就可以将查询转化为一个常量。
- eq_ref: 使用索引，且索引的每个key,有且仅有唯一一条记录与之匹配，常见于primary key和unique索引。
- ref: 非唯一性索引扫描，使用索引，但是满足条件的可能是多条记录，属于扫描和查找的结合体。
- range: 只检索给定范围的行，使用一个索引来选择行。常见于between、in、<、>查询。
- index: 全索引扫描。虽然和ALL一样是读全表，但是index只扫描索引树，速度要比ALL快。
- ALL: 遍历全表。(记录较大时，ALL需要进行优化)
possible_key

列出所有可能用到的索引。
key
实际使用的索引。NULL：未使用索引

若查询中使用了覆盖索引，则该索引仅出现在key列表中。
- 通常开发人员会根据查询的where条件来创建合适的索引，但是优秀的索引设计应该考虑到整个查询。其实mysql可以使用索引来直接获取列的数据。**如果索引的叶子节点包含了要查询的数据，那么就不用回表查询了，也就是说这种索引包含（亦称覆盖）所有需要查询的字段的值，我们称这种索引为覆盖索引。**即当我们的select列表中的字段包含在了索引的字段中，就会用到覆盖索引。
  
  注意：如果要使用覆盖索引，一定要保证查询选择字段是能够被索引的字段覆盖的而避免使用select *；但是如果将所有字段一起创建索引则会导致索引文件变大，同时降低了查询性能。
ken_len

表示使用中使用的字节数，可以通过该列计算查询中使用的索引长度。在不损失精确性的情况下，长度越小也好。

key_len显示的是值为索引字段的最大可能长度，并非实际使用的长度，是通过表定义计算得出的，而不表检索得出的。
ref

索引的哪一列被使用，显示哪些列或者常量被使用查找索引上的key

const：表示是常量,一般出现在条件中等值匹配一个定值时，例如 where stu.name=‘zs’;
row

根据表统计信息及索引的选用情况，大致估算出定位数据需要读取的记录行数。越小越好
Extra
一些额外信息：
- Using filesort（危险）：表示mysql对数据使用了一个外部的索引排序，而不是按照表内的索引顺序进行读取。==MySQL中无法利用索引完成的排序称作文件内排序==一般出现在使用了order BY和groupBY的查询中。
- Using temporary（十分危险）：表示建立了临时表保存中间结果。常见于order BY和group BY.
所以在使用group by时，尽量保证groupBY的字段与索引的字段保持相同顺序和数量。
- USING index：表示查询语句中用到了覆盖索引，减少了回表操作，速度和效率提高。
  - 如果同时出现了using where 表示索引被用于执行索引键值的查找。
  - 如果没有出现using where 表示索引用于读取数据而非执行查找动作。常见于没有where条件的
- Using where: 使用了where条件语句
- Impossible where: where条件存在逻辑错误。

18、索引性能测试

如何快速进行批量数据的创建？

Java多线程插入
批量数据脚本（SQL编程）

自建函数、存储过程

CREATE TABLE `dept`
(
    `id`       INT(11) not NULL auto_increment,
    `deptName` VARCHAR(30) DEFAULT NULL,
    `address`  VARCHAR(40) DEFAULT NULL,
    ceo        int     NULL,
    PRIMARY KEY (`id`)
) ENGINE = INNODB
  auto_increment = 1
  DEFAULT charset = utf8;

CREATE TABLE `emp`
(
    `id`    INT(11) not NULL auto_increment,
    `empno` INT     not NULL,
    `name`  varchar(20) default null,
    `age`   int(3)      default null,
    deptId  int(11)     default null,
    primary key (`id`)
#     constraint `fk_dept_id` foreign key (deptId) references `dept`(id);
) engine = INNODB
  auto_increment = 1
  default charset = utf8;

# mysql的二进制日志 默认关闭
show variables like 'log_bin_trust_function_creators';
# 全局开启
set global log_bin_trust_function_creators = 1;

# 随机生成字符串
delimiter $$
create function rand_string(n int) returns varchar(255)
begin
    declare char_str varchar(100) default
        'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';
    declare return_str varchar(255) default '';
    declare i int default 0;
    while i < n
        do
            # 每次取出一个字母拼接到返回字符串中
            set return_str = concat(return_str, substr(char_str, 1 + floor(RAND() * 52), 1));
            set i = i + 1;
        end while;
    return return_str;
end $$

# 随机生成编号
delimiter $$
create function rand_num(from_num int, to_num int) returns int(11)
begin
    declare return_num int(11) default 0;
    set return_num = from_num + floor(RAND() * (to_num - from_num + 1));
    return return_num;
end $$

# 设置用户变量进行测试
set @test_str = rand_string(13);
select @test_str;
# 删除用户变量
set @test_str = null;

# 删除函数：drop function rand_string;

# 创建存储过程 创建员工数据
delimiter $$
create procedure insert_emp(start int, max_num int)
begin
    # 计数器
    declare count int default 0;
    # 关闭自动提交
    set AUTOCOMMIT = false;
    repeat
        set count = count + 1;
        # 插入数据
        insert into emp(empno, name, age, deptId)
        VALUES (start + count,
                rand_string(6),
                rand_num(20, 50),
                rand_num(1, 10000));
    until count = max_num end repeat;
    # 提交
    commit;
end $$

# 创建存储过程 生成部门信息
delimiter $$
create procedure insert_dept(max_num int)
begin
    declare count int default 0;
    set autocommit = false;
    repeat
        set count = count + 1;
        insert into dept(deptName, address, ceo)
        VALUES (rand_string(8),
                rand_string(10),
                rand_num(1,500000));
    until count=max_num end repeat;
    commit;
end $$

delimiter ;
call insert_dept(10000);
call insert_emp(10000,500000);

# 清空表的数据，并重置自增列
truncate table emp;

# 查看自增计算器
select AUTO_INCREMENT
from information_schema.TABLES
where TABLE_NAME = 'dept';

游标使用，删除表的除主索引以外的其他索引

# 创建存储过程，删除表的全部索引
delimiter $$
create procedure proc_drop_index(dbname varchar(200), tablename varchar(200))
begin
    declare done int default 0;
    declare ct int default 0;
    declare _index varchar(200) default '';
    # 创建游标
    declare _cur cursor for (
        select index_name
        from information_schema.STATISTICS
        where TABLE_SCHEMA = dbname
          and TABLE_NAME = tablename
          and SEQ_IN_INDEX = 1
          and INDEX_NAME <> 'PRIMARY'
    );
    declare continue handler for not found set done = 2;
    open _cur;
    # 获取当前游标指向的索引名
    fetch _cur into _index;
    while _index <> '' do
        # 拼接sql
        set @sql = concat('"drop index ',_index,' on ',tablename);
        # sql预编译
        prepare real_sql from @`sql`;
        execute real_sql;
        deallocate prepare real_sql;
        set _index = '';
        fetch _cur into _index;
    end while;
    close _cur;
end $$;

索引正确使用(最左前缀原则)

场景一：全列匹配

对索引的字段全匹配，且都使用等值匹配(‘=’或 in) Y

# 创建 age+deptId 的复合索引
create index idx_age_deptId on emp(age,deptId);
/*
 使用索引前：execution: 103 ms, fetching: 21 ms
 使用索引后：execution: 5 ms, fetching: 24 ms
 */
explain select sql_no_cache * from emp where age = 30 and deptId = 9988;