MySQL入门及安装

什么是数据

数据(data)是事实或观察的结果，是对客观事物的逻辑归纳，是用于表示客观事物的未经加工的的原始素材。
数据可以是连续的值，比如声音、图像，称为模拟数据。也可以是离散的，如符号、文字，称为数字数据。
在计算机系统中，数据以二进制信息单元0,1的形式表示。

数据的定义: 数据是指对客观事件进行记录并可以鉴别的符号，是对客观事物的性质、状态以及相互关系等进行记载的物理符号或这些物理符号的组合。它是可识别的、抽象的符号。*

什么是数据库管理系统

DBMS（database management system）

数据库管理系统分类

关系型数据库（RDBMS）
- 以多张二维表的方式来存储，又给多张表建立了一定的关系（关系型数据库）
非关系型数据库（NoSQL）
- 左边rdbms右边nosql 很多以json格式进行存储数据的（mogodb）
- key:value
- 姓名:周杰伦

关系型数据库和非关系型数据库对比

功能对比

关系型数据库（RDBMS）的特点：

二维表
典型产品Oracle传统企业，MySQL互联网企业
数据存取是通过SQL（Structured Query Language结构化查询语言）
最大特点数据安全性方面强（ACID）

非关系型数据库（NoSQL：Not only SQL）的特点：

不是否定关系型数据库，而是做关系型数据库的补充。
想做老大，先学会做老二
时代特点对比:
web1.0时代
- 企业提供内容，用户浏览，所以关系型数据库够用，并发并不高，所以不需要nosql。
web2.0时代
- 核心是企业提供平台，用户参与提供内容，这个时代关系型数据库无法满足需求了。
2003NoSQL出现
- memcache的诞生，关注的点是性能，但是针对安全性能关注比较低，随着安全性能需求不断提升，所以有了redis。
redis的特点
- 依然高性能高并发
- 数据持久化功能
- 支持多数据类型，主从复制和集群
- 管理不再使用SQL了

NoSQL特性总览

1. 不是否定关系型数据库，而是做关系型数据库的补充，现在也有部分替代的趋势mongodb。
1. 关注高性能，高并发，灵活性，忽略和上述无关的功能。
1. 现在也在提升安全性和使用功能。
1. 典型产品：redis（持久化缓存，两个半天）、MongoDB（最接近关系型数据库的NoSQL）、memcached。
1. 管理不适用SQL管理，而是用一些特殊的API或数据接口。

NoSQL的分类、特点、典型产品

键值（KV）存储：memcached、redis
列存储（column-oriented）：HBASE（新浪、360）Cassandra（200台服务器集群）
文档数据库（document-oriented）：MongoDB（最接近关系型数据库的NoSQL）
图形存储（Graph）：Neo4j

MySQL安装

yum
源码
二进制

版本选型

5.6：GA 6-12个月，小版本号是偶数版

5.7：GA 6-12个月，小版本号是偶数版，必须是5.7.20以上版本（MGR）

MySQL源码安装

MySQL官网：https://www.mysql.com/

# 0.安装MySQL依赖
[root@m01 mysql-5.6.50]# yum install -y cmake zlib-devel openssl-devel autoconf ncurses-devel

# 1.下载MySQL5.6
[root@m01 ~]# wget https://downloads.mysql.com/archives/get/p/23/file/mysql-5.6.50.tar.gz

# 2.解压
[root@m01 ~]# tar xf mysql-5.6.50.tar.gz 

# 3.生成
# MySQL的安装目录
cmake . -DCMAKE_INSTALL_PREFIX=/application/mysql-5.6.50 \
#数据存放位置
-DMYSQL_DATADIR=/application/mysql-5.6.50/data \
#socket文件存放位置
-DMYSQL_UNIX_ADDR=/application/mysql-5.6.50/tmp/mysql.sock \
#使用utf8字符集
-DDEFAULT_CHARSET=utf8 \
#校验规则
-DDEFAULT_COLLATION=utf8_general_ci \
#使用其他额外的字符集
-DWITH_EXTRA_CHARSETS=all \
#支持的存储引擎
-DWITH_INNOBASE_STORAGE_ENGINE=1 \
-DWITH_FEDERATED_STORAGE_ENGINE=1 \
-DWITH_BLACKHOLE_STORAGE_ENGINE=1 \
#禁用的存储引擎
-DWITHOUT_EXAMPLE_STORAGE_ENGINE=1 \
#启用zlib库支持（zib、gzib相关）
-DWITH_ZLIB=bundled \
#启用SSL库支持（安全套接层）
-DWITH_SSL=bundled \
#启用本地数据导入支持
-DENABLED_LOCAL_INFILE=1 \
#编译嵌入式服务器支持
-DWITH_EMBEDDED_SERVER=1 \
# mysql5.6支持了google的c++mock框架了，允许下载，否则会安装报错。
-DENABLE_DOWNLOADS=1 \
#禁用debug（默认为禁用）
-DWITH_DEBUG=0

#删除cmake文件
rm -f Cmake.txt

cmake . -DCMAKE_INSTALL_PREFIX=/application/mysql-5.6.50 \
-DMYSQL_DATADIR=/application/mysql-5.6.50/data \
-DMYSQL_UNIX_ADDR=/application/mysql-5.6.50/tmp/mysql.sock \
-DDEFAULT_CHARSET=utf8 \
-DDEFAULT_COLLATION=utf8_general_ci \
-DWITH_EXTRA_CHARSETS=all \
-DWITH_INNOBASE_STORAGE_ENGINE=1 \
-DWITH_FEDERATED_STORAGE_ENGINE=1 \
-DWITH_BLACKHOLE_STORAGE_ENGINE=1 \
-DWITHOUT_EXAMPLE_STORAGE_ENGINE=1 \
-DWITH_ZLIB=bundled \
-DWITH_SSL=system \
-DENABLED_LOCAL_INFILE=1 \
-DWITH_EMBEDDED_SERVER=1 \
-DENABLE_DOWNLOADS=1 \
-DWITH_DEBUG=0

# 4.编译 && 安装
[root@m01 mysql-5.6.50]# make && make install

====================================================== 华丽的分割线 ======================================================

# 5.做软链接
[root@m01 mysql-5.6.50]# ln -s /application/mysql-5.6.50 /application/mysql

#### 目录结构
[root@m01 mysql-5.6.50]# cd /application/mysql
[root@m01 mysql]# ll
total 220
drwxr-xr-x  2 root root   4096 Aug  2 12:48 bin
drwxr-xr-x  3 root root     18 Aug  2 12:48 data
drwxr-xr-x  2 root root     55 Aug  2 12:48 docs
drwxr-xr-x  3 root root   4096 Aug  2 12:48 include
drwxr-xr-x  3 root root    291 Aug  2 12:48 lib
-rw-r--r--  1 root root 198041 Sep 23  2020 LICENSE
drwxr-xr-x  4 root root     30 Aug  2 12:48 man
drwxr-xr-x 10 root root   4096 Aug  2 12:48 mysql-test
-rw-r--r--  1 root root    587 Sep 23  2020 README
drwxr-xr-x  2 root root     30 Aug  2 12:48 scripts
drwxr-xr-x 28 root root   4096 Aug  2 12:48 share
drwxr-xr-x  4 root root   4096 Aug  2 12:48 sql-bench
drwxr-xr-x  2 root root    136 Aug  2 12:48 support-files

# 6.拷贝配置文件
[root@m01 mysql]# cp support-files/my-default.cnf /etc/my.cnf

# 7.拷贝启动脚本
[root@m01 mysql]# cp support-files/mysql.server /etc/init.d/mysqld

# 8.做初始化
+------------------+
|   Database       |
+------------------+
|information_schema|
|performence_schema|
|test              |
|mysql             |
+------------------+

[root@m01 scripts]# cd /application/mysql-5.6.50/scripts
[root@m01 scripts]# ./mysql_install_db --basedir=/application/mysql --datadir=/application/mysql/data

# 9.创建socket存放目录
[root@m01 scripts]# mkdir /application/mysql-5.6.50/tmp

# 10.创建MySQL用户
[root@m01 scripts]# useradd mysql -s /sbin/nologin -M

# 11.授权MySQL安装目录
[root@m01 scripts]# chown mysql.mysql -R /application/mysql-5.6.50/

# 12.启动数据库
[root@m01 scripts]# /etc/init.d/mysqld start

# 13.添加环境变量
[root@m01 scripts]# echo 'PATH="/application/mysql/bin:$PATH"' >> /etc/profile.d/mysql.sh

# 14.加载环境变量
[root@m01 scripts]# source /etc/profile

# 15.连接数据库
[root@m01 scripts]# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.50 Source distribution
Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>

初始化报错

1 2	#没有安装Perl模块 [root@m01 scripts]# yum install -y autoconf

MySQL二进制安装

# 0.安装依赖
[root@db02 scripts]# yum install -y autoconf libaio-devel

# 1.下载
[root@db02 ~]# wget https://downloads.mysql.com/archives/get/p/23/file/mysql-5.6.50-linux-glibc2.12-x86_64.tar.gz

# 2.解压
[root@db02 ~]# tar xf mysql-5.6.50-linux-glibc2.12-x86_64.tar.gz

# 3.创建安装目录
[root@db02 ~]# mkdir /application

# 4.移动到安装目录
[root@db02 ~]# mv mysql-5.6.50-linux-glibc2.12-x86_64 /application/mysql-5.6.50

# 5.做软链接
[root@db02 ~]# ln -s /application/mysql-5.6.50 /application/mysql

# 6.拷贝配置文件
[root@db02 mysql]# cp support-files/my-default.cnf /etc/my.cnf

# 7.拷贝脚本
[root@db02 mysql]# cp support-files/mysql.server /etc/init.d/mysqld

# 8.创建MySQL用户
[root@db02 scripts]# useradd mysql -s /sbin/nologin -M

# 9.初始化
[root@db02 scripts]# ./mysql_install_db --basedir=/application/mysql --datadir=/application/mysql/data

# 10.替换脚本中的/usr/local为/application
:%s#/usr/local#/application#g

# 11.授权
[root@db02 scripts]# chown -R mysql.mysql /application/mysql-5.6.50/

# 12.添加环境变量
[root@db02 scripts]# echo 'PATH="/application/mysql/bin:$PATH"' >> /etc/profile.d/mysql.sh

# 13.加载环境变量
[root@db02 scripts]# source /etc/profile

优化

## 查看MySQL用户
mysql> select user,host from mysql.user;
+------+-----------+
| user | host      |
+------+-----------+
| root | 127.0.0.1 |
| root | ::1       |
|      | localhost |
| root | localhost |
|      | m01       |
| root | m01       |
+------+-----------+

## 优化MySQL用户
mysql> truncate mysql.user;

## 退出重启
[root@m01 scripts]# /etc/init.d/mysqld restart
Shutting down MySQL. SUCCESS! 
Starting MySQL.. SUCCESS!

MySQL体系管理

客户端与服务端模型

C/S架构的服务

Client/Server

客户端

mysql
mysqladmin
mysqldump

MySQL的连接方式

TCP/IP

1	mysql -uroot -p123 -h10.0.0.51

Socket

1	mysql -uroot -p123 -S /application/mysql/tmp/mysql.sock

问题：

1.mysql默认的连接方式是什么？  # Socket
不需要经过TCP三次握手四次挥手

2.mysql -uroot -p123  # Socket
什么都不加，走默认连接方式

3.mysql -uroot -p123 -hlocalhost  # Socket
不是所有的-h都是TCP

4.mysql -uroot -p123 -h127.0.0.1  # TCP
只要-h后面加IP地址，就是TCP

## 错误写法
5.mysql -uroot -p123 -h10.0.0.61 -S /application/mysql/tmp/mysql.sock  # TCP
6.mysql -uroot -p123 -S /application/mysql/tmp/mysql.sock -h10.0.0.61  # TCP
只要加-h后面是IP地址，不管socket写在什么位置，一律TCP连接

MySQL服务器构成

实例

mysqld守护进程的程序结构

连接层

1.提供两种连接方式
	- TCP
	- Socket
2.验证用户的合法性
3.提供了一个专用的线程，接收SQL和SQL层交互

SQL层

1.接收连接层传递来的有权限的SQL语句
2.验证SQL语句（syntax）的语法
3.验证SQL语句的语义，执行的是查询，删除，修改....之类的操作
	- DML  #改数据
	- DDL  #改库改表
	- DCL  #权限相关
	- DQL  #查询相关
	- DTL  #事务相关
4.解析器，解析SQL语句，生成多种执行计划
5.优化器，根据解析器生成的多种执行计划，选择最优的一条
6.执行器，执行最优的一条SQL语句
	- 提供一个专用的线程，和存储引擎层交互
7.接收存储引擎层返回的数据，将数据传递给连接层
8.如果前面有缓存，将数据写入缓存一份
9.如果开启了binlog，记录日志（binlog）

存储引擎层

1
2
3

1.接收SQL层传递来的SQL语句信息
2.去对应的库下找对应的表中数据，结构化成表的形式，返回给SQL层
3.提供了一个专用的线程，和SQL交互

MySQL的结构

逻辑结构
- MySQL数据库管理员的操作对象
  - 库
  - 表
    - 元数据
      - 列（字段，列名）
      - 其它属性（数据类型、是否非空、默认值、主键、唯一、自增…….）
    - 真实数据
物理结构
- 最底层的数据文件

MySQL 表结构划分

段：由多个区构成，一张表，就是一个段

区：由多个页构成，一个区，由64个页构成，1M

块、页 block：MySQL最小的存储单元，默认16k

使用systemd管理MySQL

[root@m01 ~]# vim /usr/lib/systemd/system/mysqld.service
[Unit]
Description=MySQL Server
Documentation=man:mysqld(8)
Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html
After=network.target
After=syslog.target
[Install]
WantedBy=multi-user.target
[Service]
User=mysql
Group=mysql
ExecStart=/application/mysql/bin/mysqld --defaults-file=/etc/my.cnf
LimitNOFILE = 5000

MySQL 版本区别及管理

MySQL5.6与MySQL5.7安装的区别

# 1.编译，需要下载boost
# 2.编译参数
cmake . -DCMAKE_INSTALL_PREFIX=/app/mysql-5.7.40 \
-DMYSQL_DATADIR=/app/mysql-5.7.40/data \
-DMYSQL_UNIX_ADDR=/app/mysql-5.7.40/tmp/mysql.sock \
-DDOWNLOAD_BOOST=1 \   ## mysql-5.7.38.tar.gz没有boost需要添加该参数
-DWITH_BOOST=/root/mysql-5.7.40/boost/boost_1_59_0 \
-DDEFAULT_CHARSET=utf8 \
-DDEFAULT_COLLATION=utf8_general_ci \
-DWITH_EXTRA_CHARSETS=all \
-DWITH_INNOBASE_STORAGE_ENGINE=1 \
-DWITH_FEDERATED_STORAGE_ENGINE=1 \
-DWITH_BLACKHOLE_STORAGE_ENGINE=1 \
-DWITHOUT_EXAMPLE_STORAGE_ENGINE=1 \
-DWITH_ZLIB=bundled \
-DWITH_SSL=system \
-DENABLED_LOCAL_INFILE=1 \
-DWITH_EMBEDDED_SERVER=1 \
-DENABLE_DOWNLOADS=1 \
-DWITH_DEBUG=0

# 3.初始化
5.6：mysql_install_db
5.7：mysqld --initialize  ## 有初始密码
5.7：mysqld --initialize-insecure  ## 没有初始密码

yum安装MySQL

# 1.下载MySQL的yum源
[root@db01 ~]# wget https://dev.mysql.com/get/mysql80-community-release-el7-6.noarch.rpm

# 2.安装MySQL的yum源
[root@db01 ~]# rpm -ivh mysql80-community-release-el7-6.noarch.rpm

# 3.查看仓库中mysql版本
[root@db01 ~]# yum list|grep mysql

# 4.修改yum源
[root@db01 yum.repos.d]# vim mysql-community.repo

[mysql57-community]
enabled=0 改成 1

[mysql80-community]
enabled=1 改成 0

# 5.安装mysql5.7
[root@db01 yum.repos.d]# yum install -y mysql-server

# 6.查看日志
[root@db01 yum.repos.d]# cat  /var/log/mysqld.log

# 7.找密码
[root@db01 yum.repos.d]# grep 'root' /var/log/mysqld.log 
2022-08-04T09:48:49.008364Z 1 [Note] A temporary password is generated for root@localhost: Q6>kAhmpY:0w

# 8.连接
[root@db01 yum.repos.d]# mysql -uroot -p'Q6>kAhmpY:0w'

# 9.修改配置文件MySQL使用简单密码
validate_password_length=1
validate_password_mixed_case_count=0
validate_password_number_count=0       
validate_password_special_char_count=0 

[root@db01 yum.repos.d]# systemctl restart mysqld

# 10.改密码
mysql> alter user root@'localhost' identified by '123@qqdianCOM';
Query OK, 0 rows affected (0.00 sec)

mysql> alter user root@'localhost' identified by '123';
Query OK, 0 rows affected (0.00 sec)

MySQL用户权限管理

MySQL用户操作

Linux用户的作用:

1）登陆系统
2）管理系统文件

Linux用户管理：

1）创建用户：useradd adduser
2）删除用户：userdel
3）修改用户：usermod

MySQL用户的作用:

1）登陆MySQL数据库
2）管理数据库对象

MySQL中的用户定义

在MySQL中，用户不仅仅是用户名

用户名@主机域

主机域：

localhost
127.0.0.1
%
10.0.0.%
10.0.%.%
10.%.%.%
10.0.0.5% (10.0.0.50-10.0.0.59 包括 10.0.0.5)
db01(不能直接连，需要做解析)
10.0.0.0/255.255.255.0 == 10.0.0.%
10.0.0.0/24 (不支持)

用户管理

增

# 5.6创建用户
mysql> create user roger2@'localhost';
mysql> create user roger2@'localhost' identified by '123';

# 5.7创建用户（必须给密码创建）
mysql> create user roger2@'localhost' identified by '123';

# 注意：以前的5.7不支持grant在用户不存在的情况下执行，现在版本支持

mysql> grant all on *.* to roger3@'localhost' identified by '123';

grant授权命令
all == all privileges：所有权限，但不包括授权权限
grant all on root@'localhost' identified by '123' with grant option;
*.*：所有库，下面所有表
wordpress.*
wordpress.user
roger3@'localhost'：完整用户
identified by '123'：密码123

删

# drop user 用户名@'主机域';
mysql> drop user roger1@'10.0.0.0/255.255.255.0';    相当于在linux删系统用户 userdel -r roger

# 直接操作表删除用户：
mysql> delete from mysql.user where user='roger2' and host='localhost';  相当于在linux删系统用户，直接从/etc/passwd中删

改

## 改权限
# 添加权限
mysql> grant update on *.* to roger4@'localhost';

# 回收权限
mysql> revoke select on *.* from roger4@'localhost';

## 改密码
[root@db01 ~]# mysqladmin -uroot -p123 password '456'
mysql> grant all on *.* to root@'localhost' identified by '789';
mysql> alter user root@'localhost' identified by '123';

# 只能修改当前连接上的用户
mysql> set password=PASSWORD('JQK');

# 只有使用update想修改密码时，才需要执行flush privilege;
5.6:
mysql> update mysql.user set password=PASSWORD('abc') where user='root' and host='localhost';
mysql> flush privileges;

5.7:
mysql> update mysql.user set authentication_string=PASSWORD('abc') where user='root' and host='localhost';
mysql> flush privileges;

忘记root密码

## 方法一:
[root@db01 ~]# /etc/init.d/mysqld stop
[root@db01 ~]# ./mysqld_safe --skip-grant-tables  --skip-networking &
[root@db01 ~]# mysql
mysql> INSERT INTO mysql.user SET User='root',Host='localhost',ssl_cipher='',x509_issuer='',x509_subject='';
mysql> insert into mysql.user
values (
'root',
'localhost',
PASSWORD('123'),
'Y',
'Y',
'Y',
'Y',
'Y',
'Y',
'Y',
'Y',
'Y',
'Y',
'Y',
'Y',
'Y',
'Y',
'Y',
'Y',
'Y',
'Y',
'Y',
'Y',
'Y',
'Y',
'Y',
'Y',
'Y',
'Y',
'Y',
'Y',
'Y',
'',
'',
'',
'',
0,
0,
0,
0,
'mysql_native_password',
'',
'N');
mysql> flush privileges;

## MySQL5.7 密码字段
authentication_string: *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE

# 7.重启数据库
root@db02,172.16.1.52:~ # /etc/init.d/mysqld restart

## 方法二:
# 刷新授权表
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> grant all on *.* to root@'localhost' identified by '123';
Query OK, 0 rows affected (0.00 sec)

mysql> grant all on *.* to root@'localhost' identified by '123'
with grant option;

mysql> update mysql.user set grant_priv='Y' where user='root' and
host='localhost';

mysql> flush privileges;

查

# 查看所有用户
mysql> select * from mysql.user\G

# 查看指定字段
mysql> select user,host from mysql.user;

# 查看用户的权限
mysql> show grants for root@'localhost';

用户的权限

INSERT,SELECT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN,  PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE

作用对象分解

*.* [当前MySQL实例中所有库下的所有表]
wordpress.* [当前MySQL实例中wordpress库中所有表（单库级别）]
wordpress.user [当前MySQL实例中wordpress库中的user表（单表级别）]

脱敏：脱离敏感信息

1	mysql> grant select(user,host) on mysql.user to test@'%' identified by '123'; ## 单字段级别

企业中给开发开权限

1 2	INSERT,SELECT, UPDATE, DELETE INSERT,SELECT, UPDATE

开发：请给我开一个用户

沟通：

1.你需要对哪些库、表进行操作
2.你从哪里连接过来
3.用户名有没有要求
4.密码要求
5.发邮件(*****)

开发：你把root用户给我呗？

权限小练习

#创建wordpress数据库
create database wordpress;
#使用wordpress库
use wordpress;
#创建t1、t2表
create table t1 (id int);
create table t2 (id int);
#创建blog库
create database blog;
#使用blog库
use blog;
#创建t1表
create table tb1 (id int);

wordpress库
	- t1表
	- t2表
blog库
	- tb1表
	
grant select on *.* to wordpress@'10.0.0.5%' identified by '123';
给wordpress@'10.0.0.5%'用户查看所有库下的所有表权限,密码是123

grant insert,delete,update on wordpress.* to wordpress@'10.0.0.5%' identified by '123';
给wordpress@'10.0.0.5%'用户wordpress库下的所有表insert,delete,update权限，密码是123

grant all on wordpress.t1 to wordpress@'10.0.0.5%' identified by '123';
给wordpress@'10.0.0.5%'用户wordpress库下的t1表所有的权限，密码是123

问：
一个客户端程序使用wordpress用户登陆到10.0.0.51的MySQL后，

1.对t1表的管理能力？    # 所有权限 1+2+3
2.对t2表的管理能力？    # select，insert,delete,update   1+2
3.对tb1表的管理能力？   # select  1

结论：
1.如果在不同级别都包含某个表的管理能力时，权限是相加关系。
2.但是我们不推荐在多级别定义重复权限。
3.最常用的权限设定方式是单库级别授权，即：wordpress.*

MySQL多实例

单实例：一个后台进程 + 多个线程 + 一个预分配的内存结构

多实例：多个后台进程 + 多个线程 + 多个预分配的内存结构

多个配置文件

多个端口
data目录
socket文件
多个日志文件
pid文件

多个启动脚本

# 1.准备多个配置文件
[root@m01 ~]# vim /data/3307/my.cnf
[mysqld]
basedir=/app/mysql
datadir=/data/3307/data
socket=/data/3307/data/3307.sock
log_error=/data/3307/data/3307.err
pid_file=/data/3307/data/3307.pid
port=3307

[root@m01 ~]# vim /data/3308/my.cnf
[mysqld]
basedir=/app/mysql
datadir=/data/3308/data
socket=/data/3308/data/3308.sock
log_error=/data/3308/data/3308.err
pid_file=/data/3308/data/3308.pid
port=3308

[root@m01 ~]# vim /data/3309/my.cnf
[mysqld]
basedir=/app/mysql
datadir=/data/3309/data
socket=/data/3309/data/3309.sock
log_error=/data/3309/data/3309.err
pid_file=/data/3309/data/3309.pid
port=3309

# 2.创建数据目录
[root@m01 ~]# mkdir -p /data/330{7..9}

# 3.初始化
[root@m01 ~]# cd /app/mysql/scripts/
[root@m01 scripts]# ./mysql_install_db --defaults-file=/data/3307/my.cnf --user=mysql --basedir=/app/mysql --datadir=/data/3307/data

[root@m01 scripts]# ./mysql_install_db --defaults-file=/data/3308/my.cnf --user=mysql --basedir=/app/mysql --datadir=/data/3308/data

[root@m01 scripts]# ./mysql_install_db --defaults-file=/data/3309/my.cnf --user=mysql --basedir=/app/mysql --datadir=/data/3309/data

# 4.查看目录结构
[root@m01 scripts]# tree -L 2 /data/
/data/
├── 3307
│   ├── data
│   └── my.cnf
├── 3308
│   ├── data
│   └── my.cnf
└── 3309
    ├── data
    └── my.cnf

# 5.启动
[root@m01 scripts]# mysqld --defaults-file=/data/3307/my.cnf --user=mysql &
[root@m01 scripts]# mysqld --defaults-file=/data/3308/my.cnf --user=mysql &
[root@m01 scripts]# mysqld --defaults-file=/data/3309/my.cnf --user=mysql &
#### 使用systemd管理

# 6.连接
[root@m01 scripts]# mysql -uroot -p -S /data/3307/data/3307.sock
[root@m01 scripts]# mysql -uroot -p -S /data/3308/data/3308.sock
[root@m01 scripts]# mysql -uroot -p -S /data/3309/data/3309.sock

# 7.连接小技巧
[root@m01 scripts]# vim /usr/bin/mysql3309
mysql -uroot -p -S /data/3309/data/3309.sock
[root@m01 scripts]# chmod +x /usr/bin/mysql3309

[root@m01 scripts]# vim /usr/bin/mysql3308
mysql -uroot -p -S /data/3308/data/3308.sock
[root@m01 scripts]# chmod +x /usr/bin/mysql3308

[root@m01 scripts]# vim /usr/bin/mysql3307
mysql -uroot -p -S /data/3307/data/3307.sock
[root@m01 scripts]# chmod +x /usr/bin/mysql3307

SQL语句入门

MySQL的连接管理

自带的连接工具（客户端）

mysql

-u：user指定MySQL的用户
-p：password指定MySQL用户的密码
-S：socket指定socket文件的位置
-h：host指定主机IP地址

-e：exec执行SQL语句
-P：port指定端口

--protocol=name：指定连接方式

mysqladmin
mysqldump（逻辑备份）

第三方连接工具（客户端）

Navicat
SQLmanager
SQLyog

MySQL启动关闭流程

启动

1
2
3

/etc/init.d/mysqld start
systemctl start mysqld
mysqld_safe --选项

关闭

/etc/init.d/mysqld stop
systemctl stop mysqld
mysqladmin -uroot  -S /data/3309/data/3309.sock shutdown

kill -9 pid ?
killall mysqld ?
pkill mysqld ?

625某电商网站数据库宕机故障解决实录（上）

625某电商网站数据库宕机故障解决实录（下）

MySQL实例初始化配置

预编译
命令行
配置文件(读取顺序)
- /etc/my.cnf 10
- /etc/mysql/my.cnf 20
- $basedir/my.cnf 30
- –defaults-extra-file=/opt/my.cnf
- ~/.my.cnf 40

优先级

~/.my.cnf > –defaults-extra-file=/opt/my.cnf > $basedir/my.cnf > /etc/mysql/my.cnf > /etc/my.cnf

注意：如果启动MySQL加了--defaults-file=/etc/my.cnf选项，其它位置的配置文件都不读取

思考

#cmake：
socket=/application/mysql/tmp/mysql.sock
#命令行：
--socket=/tmp/mysql.sock
#配置文件：
/etc/my.cnf中[mysqld]标签下：socket=/opt/mysql.sock
#default参数：
--defaults-file=/tmp/a.txt配置文件中[mysqld]标签下：socket=/tmp/test.sock

/application/mysql/tmp/mysql.sock
/tmp/mysql.sock
/opt/mysql.sock
/tmp/test.sock

mysqld --defaults-file=/tmp/a.txt --socket=/tmp/mysql.sock

/tmp/mysql.sock

## 结论
默认配置，优先级
1.命令行
2.配置文件
	- ~/.my.cnf
	- --defaults-extra-file=/opt/my.cnf
	- $basedir/my.cnf
	- /etc/mysql/my.cnf
	- /etc/my.cnf
3.编译参数

初始化配置的作用

1.影响实例的启动（mysqld）
2.影响到客户端的连接

[mysqld]  [server] ## 这两个标签下的配置，都是来影响服务端启动的

[mysql][mysqladmin][mysqldump] ## 这几个标签，影响对应的客户端命令
[client]  ## 这个标签，影响所有的客户端命令

注意：修改客户端配置，不需要重启mysql，修改服务端配置[mysqld]需要重启mysql

[mysqld]
skip_name_resolve
basedir=/application/mysql
datadir=/application/mysql/data
server_id=10
socket=/opt/mysql.sock

[client]
user=root
password=abc
socket=/opt/mysql.sock

MySQL的SQL语句

客户端命令

mysql

## 查看命令帮助
?   \?   help   \h

## 查看状态
status   \s

## 退出
exit quit \q

## 结束当前的SQL语句
\c

## Ctrl + c
MySQL5.6中：退出MySQL
MySQL5.7中：结束当前SQL语句，类似于\c

## 在MySQL中执行系统命令
system  \!

## 临时将操作记录到指定的文件中
tee  \T
tee /tmp/mysql.log
\T /tmp/mysql.log

## 切换数据库
use \u
use mysql
\u mysql

## 导入数据
source  \.
source /tmp/zls.sql
\. /tmp/zls.sql

## 格式化（key:value）方式，显示数据
\G
mysql> select * from mysql.user\G


### 客户端配置，显示当前所在数据库及登录用户
[client]
prompt="\u@\h:\d>"

mysqladmin

mysqladmin password '密码' # 修改密码或者设置密码  
mysqladmin shutdown # 关闭mysql服务
mysqladmin ping # 检测MySQL是否存活
mysqladmin status # 查看MySQL的状态
mysqladmin variables # 查看MySQL的默认配置（内置变量）
mysqladmin create 库名 # 在库外创建数据库
mysqladmin drop 库名 # 在库外删除数据库
mysqladmin reload # 重新加载数据库
mysqladmin flush-log # 刷新授权表

注意：有密码，-u  -p

SQL层的SQL语句

什么是SQL语句

结构化的查询语句
标准：SQL-92

SQL语句的分类

Database Definition Language
数据     定义        语言

# 开发规范：
(01) 表名不能大写,数字开头,16个字符串
(02) 表名和业务有关
(03) drop 语句禁止
(04) 选择合适的数据类型
(05) 必须要有主键
(06) 列尽量非空约束
(07) 减少外键约束
(08) 必须设置存储引擎和字符集
(09) 列必须要有注释
(10) 对于非负数设置数据类型约束--无符号

## 库
# 增
语法：
Syntax:
CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name
    [create_option] ...

create_option: [DEFAULT] {
    CHARACTER SET [=] charset_name
  | COLLATE [=] collation_name
}

create database 库名;
create schema 库名;
create database 库名 character set utf8;
create database 库名 charset utf8;
create database if not exists 库名 charset utf8 collate utf8_bin;

# 删
drop database 库名;

# 改
修改字符集
alter database roger1 charset latin1;

#### DQL查看数据库的字符集
show create database roger1;
+----------+-----------------------------------------------------------------+
| Database | Create Database                                                 |
+----------+-----------------------------------------------------------------+
| roger1     | CREATE DATABASE `roger1` /*!40100 DEFAULT CHARACTER SET latin1 */ |
+----------+-----------------------------------------------------------------+


## 表
# 增
create table 表名(字段1 数据类型 约束,字段 数据类型 约束....)
建表，至少要给的是 字段名称和数据类型
create table roger.student(id int not null primary key auto_increment,name varchar(10),age tinyint,gender enum('0','1'));

create table roger.student2(
id int,
name varchar(10),
age tinyint,
gender enum('0','1'));

## 数据类型
int： 整数 -2^31 ~ 2^31 -1
varchar：字符类型 （变长）
char： 字符类型 （定长）

name varchar(10)
roger
huanglong

name char(10)
roger
huanglong

phone char(11)


tinyint： 整数 -128 ~ 127
enum： 枚举类型
enum('A','B','C','D')
datetime： 时间类型 年月日时分秒

## 约束
not null： 非空
primary key： 主键（唯一且非空的）一张表只能有一个主键
auto_increment： 自增（此列必须是：primary key或者unique key）
unique key： 唯一键，单独的唯一的  唯一键 + not null
default： 默认值
unsigned： 无符号（非负）
comment： 注释

create table roger.student5(
id int primary key auto_increment comment '学号',
name varchar(10) not null comment '学生姓名',
age tinyint unsigned not null default 18 comment '学生年龄',
gender enum('0','1') not null default '1' comment '学生性别');


id	name	age	gender	phone	bir	come
整型	字符串类型	最小整型	枚举类型	字符串类型	datetime  timestamp	datetime  timestamp
int	varchar、char	tynint	enum			

# 删
drop table roger.roger_tb;


## 删用户
drop user roger@'%';

# 改
## 修改表名
alter table 表名 rename 新表名;
alter table student rename student1;

## 添加字段（将字段添加在表的最后一列）
alter table stu add huanglong varchar(10) not null;
alter table student3 add huanglong varchar(10) not null;
alter table student3 add hl char(11) default 'sb';  ## 带默认值添加字段

## 添加多个字段
alter table student3 add liquanyi varchar(10) not null,add liangkang char(10);

## 按指定位置添加字段
alter table student3 add sujing char(1) after liquanyi;

## 将字段添加在最前面一列
alter table student3 add hujunxian char(1) first;

## 删除字段
alter table student3 drop liquanyi;

## 修改数据类型和属性
alter table student3 modify gender enum('f','m') default 'f';

## 修改字段名 数据类型 属性
alter table student3 change hl huangl varchar(10);

Data Manipulation Language
数据  操作         语言

所有的DML都是操作表内容的
## 增
# 插入单条数据
insert into stu(name,gender,age,date,phone,bir,id) value('huanglong','f',255,'2022-08-10','133',NOW(),1);

# 插入多条数据
insert into stu(name,gender,age,date,phone,bir,id)
value('huanglong1','f',255,'2022-08-10','136',NOW(),4),
('huanglong2','m',38,'2022-09-10','137',NOW(),5);

# 默认字段不加
insert into stu(name,bir,phone) value('liquanyi',NOW(),'138');

# 不规范写法
insert stu value(7,'liangkang',18,'m',NOW(),'139',NOW());

## 删(危险)
使用delete一定要加条件
delete from student3; （不要这么删）

# 加条件
delete from stu where name='huanglong1';

# 想全部删除也要加
delete from stu where 10=10;
delete from stu where 1=1;

注意：一般在删除数据时，我们会根据唯一性的字段，进行删除
delete from mysql.user where user='roger' and host='%';

## 改
使用update一定要加条件
update
update student2 set id=3 where name='wyk';

insert update delete select
insert update select

## 使用update替代delete删除数据
1.给表中，加一个状态列
alter table student2 add status enum('0','1') default '1';

2.使用update删除数据
update student2 set status='0' where name='roger';

3.查询的时候，使用where条件查询
select * from student2 where status='1';

注意：update修改数据库用户的密码，是要flush privileges;修改其他数据，不需要。

Database Control Language
数据      控制    语言

### 赋予权限
5.6和5.7区别：5.7老版本，grant赋予权限，如果该用户不存在，则无法创建，5.6和5.7新版本可以直接创建用户
grant all on *.* to test@'%' identified by '123';
grant 权限1,权限 on 库.表 to 用户@'主机域' identified by '密码';
grant all on *.* to test@'%' identified by '123' with grant option;

grant all on *.* to roger4@'%' identified by '123' with max_queries_per_hour 3;

max_queries_per_hour 3 ## 限制该用户一小时内，只能查询3次
grant all on *.* to roger6@'%' identified by '123' 
without max_queries_per_hour 3
max_updates_per_hour 1 		## 限制用户一小时，只能执行一次update
max_connections_per_hour 1  ## 限制用户一小时内，只能连接一次数据库
max_user_connections 1	    ## 限制用户，只能同时一个用户连接
grant option;

grant all on *.* to roger6@'%' identified by '123' 
with max_queries_per_hour 999
max_updates_per_hour 999
max_connections_per_hour 999
max_user_connections 999;

### 回收权限
revoke
revoke 权限 on 库.表 from 用户@'主机域';
revoke delete on *.* from dev@'%';

Database Query Language
数据     查询   语言

## show
show databases;		           # 查看数据库
show tables;		           # 查看表
show tables from roger;           # 查看指定数据库下的表，不用切换数据库
show create database 库名;       # 查看建库语句
show create table 表名;          # 查看建表语句，也是为了查看注释
show create table 库名.表名;	 # 查看建表语句，也是为了查看注释（不用切换数据库）
show grants for 用户名@'主机域';  # 查看用户授权语句，也是为了查看该用户的权限
show variables;                  # 查看所有的内置变量（默认配置）
show variables like '%server';   # 模糊查询（过滤）
show variables like '%server%';  # 模糊查询（过滤）
show variables like 'server%';   # 模糊查询（过滤）

show processlist;                # 查看后台执行的sql语句
show full processlist;           # 查看完整的后台执行的sql语句
show collation;                  # 查看所有校验规则
show charset;                    # 查看所有字符集以及该字符集的默认校验规则

## desc
desc 库.表       # 查看表结构
查看执行计划

## explain  查看执行计划  （索引）

## select
wget http://test.driverzeng.com/MySQL_File/world.sql
root@localhost:(none)> source /root/world.sql

root@localhost:(none)> show databases;
+--------------------+
| Database           |
+--------------------+
| world              |
+--------------------+
root@localhost:(none)> use world
root@localhost:world> show tables;
+-----------------+
| Tables_in_world |
+-----------------+
| city            |
| country         |
| countrylanguage |
+-----------------+

## select 常用简单查询
root@localhost:world> select * from city;  ## 查看该表中所有的字段的记录（所有数据）
root@localhost:world> select id,name,countrycode from city; ## 查看指定字段的所有数据

## select 行级查询（翻页功能）
root@localhost:world> select id,name,countrycode from city limit 10;

root@localhost:world> select id,name,countrycode from city limit 60;			
root@localhost:world> select id,name,countrycode from city limit 120,60;			## 翻页功能，从121开始查 60个
root@localhost:world> select id,name,countrycode from city limit 180,60;             ## 翻页功能，从181开始查 60个

## select 条件查询
select * from city where countrycode='CHN';
select * from city where countrycode='CHN' or countrycode='USA';
select * from city where countrycode='CHN' and district='heilongjiang';

### 条件查询结合行级查询
select * from city where countrycode='CHN' and district='heilongjiang' limit 10;

## select 模糊查询
select * from city where countrycode like '%HN' or countrycode='JPN';
select * from city where countrycode like '%HN%' or countrycode='JPN';
select * from city where countrycode like 'HN%' or countrycode='JPN';

## select 排序查询 order by
select * from city where countrycode='CHN' order by population;       ## 顺序排序
select * from city where countrycode='CHN' order by population desc;  ## 倒序排序

select * from city order by population;							    ## 不加条件 顺序排序
select * from city order by population desc;                          ## 不加条件 倒序排序

## select 范围查询
mysql[world]> select * from city where population > 10000000;
> < >= <= <> !=

# in：
select * from city where countrycode in ('CHN','USA');

# or：
select * from city where countrycode='CHN' or countrycode='USA';

# union all 联合查询
select * from city where countrycode='CHN' union all select * from city where countrycode='USA';

## select 分组查询 group by
group by + 聚合函数
聚合函数：
max()：最大值
min()：最小值
sum()：求和
avg()：求平均值`
count()：统计

1.遇到统计想函数
2.形容词前groupby
3.函数中央是名词
4.列名select后添加

# 统计世界上每个国家的总人口数
遇到统计想函数：sum()
形容词前groupby：group by countrycode
函数中央是名词：sum(population)
列名select后添加：国家  人口数    countycode,population

mysql[world]> select countrycode,sum(population) from city group by countrycode;

# 统计中国各个省的人口数量(练习)
遇到统计想函数：sum()
形容词前groupby：group by district
函数中央是名词：sum(population)
名select后添加：省 人口数 district,population

select district,sum(population) from city where countrycode='CHN' group by district;

# 统每个国家的城市数量(练习)
遇到统计想函数：count()
形容词前groupby：group by countrycode
函数中央是名词：count(name)
名select后添加：国家，城市数量 countrycode,count(name)

select countrycode,count(name) from city group by countrycode;

select countrycode as 国家,count(name) as 城市数量 from city group by countrycode;

select 高级用法

传统连接

## 多表连查
[zhang3,li4,wang5]
[50,70,80]

t1:student
sid	sname
001	zhang3
002	li4
003	wang5

create table student(
sid int(3) zerofill,
sname varchar(10)
);

insert into student(sid,sname) value(1,'zhang3'),(2,'li4'),(3,'wang5');

mysql[roger1]> select * from student;
+------+--------+
| sid  | sname  |
+------+--------+
|  001 | zhang3 |
|  002 | li4    |
|  003 | wang5  |
+------+--------+

t2:score
sid	smark
001	50
002	70
003	80

create table score(
sid int(3) zerofill,
smark float(3,1)
);

insert into score value(1,50),(2,70),(3,80);

mysql[roger1]> show tables;
+----------------+
| Tables_in_roger1 |
+----------------+
| score          |
| student        |
+----------------+

## 查询出wang5的成绩

### 传统连接
select student.sname,score.smark from student,score where student.sid=score.sid and student.sname='wang5';

连表查询的前提：两张表，必须有关联的字段

#世界上小于100人的人口城市是哪个国家的？
select country.name as 国家,city.name as 城市,city.population as 城市人口数
from city,country
where city.countrycode=country.code
and city.population < 100;


select city.countrycode,country.name as 国家,city.name as 城市,city.population as 城市人口数 from city left join country on city.countrycode=country.code and city.population=1410000;


+----------+-----------+-----------------+
| 国家     | 城市      | 城市人口数      |
+----------+-----------+-----------------+
| Pitcairn | Adamstown |              42 |
+----------+-----------+-----------------+


## 世界上大于10000000人口数量的城市在哪个国家，说什么语言？
国家名    城市名     城市人口数量   语言

select country.name,city.name,city.populati
on,countrylanguage.language
from country,city,countrylanguage
where country.code=city.countrycode
and country.code=countrylanguage.countrycode
and city.population > 10000000;

自连接（NATURAL JOIN）

自动找到等价条件，前提：两张表的等价条件字段名，必须一样
city   国家代码：countrycode
country 国家代码： code
countrylanguage 国家代码： countrycode

SELECT city.name,city.countrycode ,countrylanguage.language ,city.population
FROM  city NATURAL  JOIN  countrylanguage 
WHERE population > 1000000;

## 说english的城市有哪些，他们的国家代码是什么？
select city.countrycode,city.name,countrylanguage.language
from city natural join countrylanguage
where countrylanguage.language='english';

## 每个国家有几个城市说英语，他们的国家代码是什么？
select city.countrycode as 国家代码,count(city.name) as 城市数量,countrylanguage.language as 语言
from city natural join countrylanguage
where countrylanguage.language='english'
group by countrycode;

## 每个国家有几个城市说英语，他们的国家代码是什么？ 按城市数量排序
select city.countrycode as 国家代码,count(city.name) as 城市数量,countrylanguage.language as 语言
from city natural join countrylanguage
where countrylanguage.language='english'
group by countrycode order by 城市数量;

内连接（join on）

select 字段1,字段2,字段3
from 表1 jion 表2
on 等价条件
where 自己的条件;

## 每个国家有几个城市说英语，他们的国家代码是什么？
select city.countrycode as 国家代码,count(city.name) as 城市数量,countrylanguage.language as 语言
from city join countrylanguage
on city.countrycode=countrylanguage.countrycode
where countrylanguage.language='english'
group by city.countrycode;


## 世界上大于10000000人口数量的城市在哪个国家，说什么语言？
1 jion 2 on A

表1 jion 表2 on 等价条件A join 表3 on 等价条件B

****   传统连接  ****
select country.name,city.name,city.population,countrylanguage.language
from country,city,countrylanguage
where country.code=city.countrycode
and country.code=countrylanguage.countrycode
and city.population > 10000000;

****   内连接 三表连查  ****
select country.name,city.name,city.population,countrylanguage.language
from country 
join city 
on country.code=city.countrycode
join countrylanguage 
on country.code=countrylanguage.countrycode
where city.population > 10000000;

join on 注意：大表在前，小表在后

外连接

## 左外连接 left join
select city.name,city.countrycode,country.name  from city left join country  on city.countrycode=country.code  and city.population<100;

## 右外连接 right join
select city.name,city.countrycode,country.name  from city right join country  on city.countrycode=country.code  and city.population<100;

联合查询

#范围查询OR语句
mysql> select * from city where countrycode='CHN' or countrycode='USA';
#范围查询IN语句
mysql> select * from city where countrycode in ('CHN','USA');

# 替换为：
mysql> select * from city where countrycode='CHN' union all select * from city where countrycode='USA';
union：去重复合并
union all ：不去重复
使用情况：union < union all

视图：view
触发器：trigger

1
2
3

# 事务控制语句
Transaction Control Language
Database Transaction Language

字符集设置

操作系统设置字符集

## CentOS6
[root@db01 ~]# source /etc/sysconfig/i18n
[root@db01 ~]# echo $LANG
zh_CN.UTF-8

## CentOS7
[root@db04 tmp]# vim /etc/locale.conf 
LANG="en_US.UTF-8"

[root@db04 tmp]# echo $LANG
en_US.UTF-8

连接工具设置字符集

Xshell

1
2
3

## windows
GB2312  国标
GBK     国标扩

建库建表级别设置字符集

## 建库设置字符集
create database roger111 charset utf8 collate utf8_general_ci;

## 建表设置字符集
create table test(
id int,
name varchar(10)
) default charset utf8;

数据库配置设置字符集

1
2
3

vim /etc/my.cnf
[mysqld]
character_set_server=utf8

MySQL索引及执行计划

索引的算法类型

BTree索引(Innodb存储引擎)
- B-tree
- B+tree
- B*tree
HASH索引
FULLTEXT索引
RTree索引

索引类型

主键索引
- 联合索引
唯一键索引
- 前缀索引
- 联合索引
普通索引
- 前缀索引
- 联合索引

## 增 mysql> alter table city add 字段名 数据类型 属性; 添加字段 # 创建普通索引
mysql> alter table city add index 索引名(字段名);
mysql> alter table city add index idx_population(population);

# 创建主键索引
mysql> alter table student add primary key(id);

# 创建唯一键索引(如何判断，该列能否创建唯一键索引) 
mysql> select count(doa) from student3; 
+------------+
| count(doa) |
+------------+ 
|          7 | 
+------------+

mysql> select count(distinct(doa)) from student3;
+----------------------+
| count(distinct(doa)) |
+----------------------+
|                    5 | 
+----------------------+

mysql> alter table student3 add unique key 索引名(字段名); 
mysql> alter table student3 add unique key uni_doa(doa);

## 删
# 删除普通索引和唯一键索引
mysql> alter table student3 drop index uni_doa; mysql> alter table student3 drop index 索引名;

# 删除主键索引
mysql> alter table student drop primary key;
## 查
mysql> desc student3; 
+--------+---------------------------+------+-----+
| Field  | Type                      | Null | Key |
+--------+---------------------------+------+-----+
| id     | int(10) unsigned zerofill | NO   | PRI |
| name   | varchar(10)               | NO   |     |
| age    | tinyint(3) unsigned       | NO   |     |
| gender | enum('f','m')             | NO   |     |
| phone  | char(11)                  | NO   | UNI |
| doa    | datetime                  | NO   | UNI |
| status | enum('0','1')             | NO   |     |
+--------+---------------------------+------+-----+
mysql> show index from student3;

前缀索引

id  text  
1   Shallabcxc
2   Shxllasdasd
3   xxxxxxzxczxc

作用:给内容比较多或比较长的字段创建索引，提升创建索引的排序效率

## 普通索引创建前缀索引
mysql> alter table student4 add index idx_name(name(3));

## 唯一键索引创建前缀索引
mysql> alter table student4 add unique key uni_name(name(2));

联合索引

原则：把最常用来做查询条件的列放在最前面

## 创建普通索引的联合索引
mysql> alter table people add index idx_all(gender,age,money);
mysql> desc people;
+--------+---------------+------+-----+---------+-------+
| Field  | Type          | Null | Key | Default | Extra |
+--------+---------------+------+-----+---------+-------+
| id     | int(11)       | YES  |     | NULL    |       |
| name   | varchar(20)   | YES  |     | NULL    |       |
| age    | tinyint(4)    | YES  |     | NULL    |       |
| money  | int(11)       | YES  |     | NULL    |       |
| gender | enum('m','f') | YES  | MUL | NULL    |       |
+--------+---------------+------+-----+---------+-------+

## 创建主键联合索引
mysql> alter table people add primary key(id,name);
mysql> desc people;
+--------+---------------+------+-----+---------+-------+
| Field  | Type          | Null | Key | Default | Extra |
+--------+---------------+------+-----+---------+-------+
| id     | int(11)       | NO   | PRI | NULL    |       |
| name   | varchar(20)   | NO   | PRI | NULL    |       |
| age    | tinyint(4)    | YES  |     | NULL    |       |
| money  | int(11)       | YES  |     | NULL    |       |
| gender | enum('m','f') | YES  |     | NULL    |       |
+--------+---------------+------+-----+---------+-------+

## 唯一键联合索引
mysql> alter table people add unique key uni_all(gender,age,money);
mysql> desc people;
+--------+---------------+------+-----+---------+-------+
| Field  | Type          | Null | Key | Default | Extra |
+--------+---------------+------+-----+---------+-------+
| id     | int(11)       | NO   |     | NULL    |       |
| name   | varchar(20)   | NO   |     | NULL    |       |
| age    | tinyint(4)    | YES  |     | NULL    |       |
| money  | int(11)       | YES  |     | NULL    |       |
| gender | enum('m','f') | YES  | MUL | NULL    |       |
+--------+---------------+------+-----+---------+-------+

## 注意:联合索引在查询时，也必须按照创建索引的顺序进行查询，否则，不走索引

索引建立原则和规范

优先选择唯一键索引

1 2	select count(字段名) from 表; select count(distinct(字段名)) from 表;

唯一键索引的是唯一的，可以更快速的通过该索引来确定某条记录

重复值较多情况下，优先选择使用联合索引
为经常需要排序、分组和联合操作的字段建立索引
经常需要ORDER BY、GROUP BY、DISTINCT和UNION等操作的字段，排序操作会浪费很多时间。
如果为其建立索引，可以有效地避免排序操作
经常查询
列值的重复值少
尽量使用前缀索引(在唯一键或联合索引基础之上创建前缀)

注意：

1.一定要限制索引的数目

索引的数目不是越多越好。每个索引都需要占用磁盘空间，索引越多，需要的磁盘空间就越大。

修改表时，对索引的重构和更新很麻烦。越多的索引，会使更新表变得很浪费时间。

2.删除不再使用或很少使用的索引

表中的数据被大量更新，或者数据的使用方式被改变后，原有的一些索引可能不再需要。数

据库管理员应当定期找出这些索引，将它们删除，从而减少索引对更新操作的影响。

企业问题：

我们网站响应速度很慢？如何排查？如何处理？

1）网站的服务器，负载高

stress是Linux系统压力测试工具，这里我们用作异常进程模拟平均负载升高的场景。

-------------------
mpstat是多核CPU性能分析工具，用来实时检查每个CPU的性能指标，以及所有CPU的平均 指标。 
pidstat是一个常用的进程性能分析工具，用来实时查看进程的CPU，内存，IO，以及上下 文切换等性能指标。

查找出哪个进程占用CPU、内存、磁盘比较多，就看对应进程的日志

2）代码逻辑问题，代码有bug

1 2	查看程序日志日志内容交给开发

3）网络带宽、网络波动

1 2	升级带宽指定地区放一台服务器，打通内网，做缓存(CDN)

4）缓存失效

1	错峰设置缓存失效周期

5）数据库查询速度慢

1.找出哪一条SQL语句执行的慢(开启慢查询日志)

[mysqld]
#指定是否开启慢查询日志
slow_query_log = 1|ON 0|OFF 
#指定慢日志文件存放位置(默认在data) 
slow_query_log_file=/application/mysql/data/slow.log 
#设定慢查询的阀值(默认10s)
long_query_time=0.05
#不使用索引的慢查询是否记录到日志 
log_queries_not_using_indexes 
#查询检查返回少于该参数指定行的SQL不被记录到慢查询日志 
min_examined_row_limit=100(鸡肋)

2.分析SQL语句为什么慢

# Time: 2023-04-11T01:03:33.279720Z
# User@Host: root[root] @ localhost []  Id:     9
# Query_time: 17.607624  Lock_time: 0.000351 Rows_sent: 4096
Rows_examined: 16707584
# SET timestamp=1681175013;

select * from city2 where population<100;
mysql> desc select * from city2 where population<100;
+----+-------------+-------+------------+------+---------------+
| id | select_type | table | partitions | type | possible_keys |
+----+-------------+-------+------------+------+---------------+
|  1 | SIMPLE      | city2 | NULL       | ALL  | NULL          |
+----+-------------+-------+------------+------+---------------+

mysql> explain select * from city2 where population<100;
+----+-------------+-------+------------+------+---------------+
| id | select_type | table | partitions | type | possible_keys |
+----+-------------+-------+------------+------+---------------+
|  1 | SIMPLE      | city2 | NULL       | ALL  | NULL          |
+----+-------------+-------+------------+------+---------------+

全表扫描
1
type:ALL 全表扫描
该字段没有创建索引
有索引但是没走索引

SQL语句执行效率级别

0)ALL
---------
1)index // 全索引扫描
mysql> explain select population from city;
+----+-------------+-------+------------+-------+
| id | select_type | table | partitions | type  |
+----+-------------+-------+------------+-------+
|  1 | SIMPLE      | city  | NULL       | index |
+----+-------------+-------+------------+-------+

2)range // 通常我们认为只要一条SQL语句能达到range级别 
mysql> explain select * from city where population<1000;
+----+-------------+-------+------------+-------+
| id | select_type | table | partitions | type  |
+----+-------------+-------+------------+-------+
|  1 | SIMPLE      | city  | NULL       | range |
+----+-------------+-------+------------+-------+

3)ref // 使用联合查询时，才会出现的级别
mysql> explain select * from city where countrycode='CHN' union all select * from city where countrycode='USA';
select * from city where countrycode='CHN' union all select * from city where countrycode='USA';

4)eq_ref
只有在连表查询时，并且使用 join on语句才能达到 AjoinBon 条件

5)const  // 主键索引使用精确查询 

6)system  // 主键索引使用精确查询

7)null  // 整个表中没有该数据
mysql> explain select * from city where population>1000000000000000000000;

key_len：越小越好（前缀索引去控制）

rows：越小越好

不走索引的情况

# 1.全表扫描
select * from city; // 不接条件，请在后面加上where条件

# 2.没有创建索引
mysql> explain select * from city where district='shanghai'; // 给 需要经常查询的字段创建索引

# 3.有索引，不走索引
1) 查询的结果集大于原表的数据25%以上
mysql> explain select * from city where population>100;
优化:翻页方式解决
mysql> explain select * from city where population>100 limit
120,60;

2)使用字段进行计算
mysql> explain select * from city where id-1=10;
优化:程序员，杀了祭天

3)隐式转换导致不走索引
mysql> create table test_index(id int,name varchar(10),phone char(11));
mysql> alter table test_index add index idx_phone(phone);
mysql> insert into test_index value(1,'aaa','12345678911');
Query OK, 1 row affected (0.00 sec)
mysql> insert into test_index value(2,'bbb','12341678911');
Query OK, 1 row affected (0.01 sec)
mysql> insert into test_index value(3,'ccc','12341578911');
Query OK, 1 row affected (0.02 sec)
mysql> select * from test_index;
+------+------+-------------+
| id   | name | phone       |
+------+------+-------------+
|    1 | aaa  | 12345678911 |
|    2 | bbb  | 12341678911 |
|    3 | ccc  | 12341578911 |
+------+------+-------------+

mysql> explain select * from test_index where phone=12341578911;
mysql> select * from test_index where phone='12341578911';
+------+------+-------------+
| id   | name | phone       |
+------+------+-------------+
|    3 | ccc  | 12341578911 |
+------+------+-------------+

优化:查看建表语句，查看该字段的数据类型，查询时，根据数据类型选择是否加引号

4)不等于、not in
mysql> explain select * from city where population <> 100;
优化:
# 1.加limit优化结果集
mysql> explain select * from city where population <> 100 limit 10;
# 2.使用联合查询
mysql> explain select * from city where population>100 union all select * from city where population <100;

5)使用like模糊查询，%在前面的
mysql> explain select * from city where countrycode like '%HN';
mysql> explain select * from city where countrycode like 'C%N';
mysql> explain select * from city where countrycode like 'CH%';
优化:不要使用MySQL，使用elasticsearch(倒排索引) 

6)使用联合索引时，不按创建索引的顺序查询
优化:
# 1.首先要对数据进行分析，了解用户喜好 
# 2.按照顺序创建索引

7)索引本身失效 优化:删了重建