MYSQL基础自学-EW帮帮网

优先选择满足存储数据需求的最小数据类型;
避免使用TEXT,LONGTEXT,ENUM等字符串类型;
- TEXT在读取时还要使用二次查找，一般也用不到这么长，MYSQL内存临时表是支持不了这种大数据的,如果要进行排序等操作,可能需要走到磁盘临时表里;
- 支持索引检索比较复杂,只能进行前缀索引,因为非前缀索引也太长了且没必要.
- 同财务相关的数值型数据,必须使用decimal类型

2.3.2.1 整数类型

TINYINT: 1字节
SMALLINT：２字节
MEDIUMINT：３字节
INT: 4字节
BIGINT：８字节
区分SIGNED和UNSIGNED

2.3.2.2 浮点类型

FLOAT: 4个字节,　不精确
DOUBLE：８个字节，非精确，小数部分经过计算部分可能会不精准
DECIMAL：每４个字节存９个数字，小数点占用１个字节，是精确的．举例子：123456789.987654321占用９个字节，４＋１＋４＝９,格式是DECIMAL(18,9)
‌decimal(10,2)的含义是：总共能存储10位数字，其中末尾2位是小数。

[root@imooc mysql]# mysql -u root -p
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.02 sec)

mysql> create database test;
Query OK, 1 row affected (0.01 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test               |
+--------------------+
5 rows in set (0.00 sec)

mysql> use test;
Database changed

mysql> create table t(d1 double,d2 decimal(10,3));
Query OK, 0 rows affected (0.01 sec)

mysql> insert into t values(11.2,11.2),(2.56,2.56);
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from t; //看，就后面的末尾不一样，明明设置的是一个值；
+------+--------+
| d1   | d2     |
+------+--------+
| 11.2 | 11.200 |
| 2.56 |  2.560 |
+------+--------+
2 rows in set (0.00 sec)
mysql> select sum(d1),sum(d2) from t
    -> ;
+---------+---------+
| sum(d1) | sum(d2) |
+---------+---------+
|   13.76 |  13.760 |
+---------+---------+
1 row in set (0.00 sec)

mysql>

2.3.3.3 时间类型

DATE 3字节 YYYY-MM-DD 从1000-01-01到9999-12-31
TIME 3-6字节 HH:MM:SS 从-838:59:59(us)到 838:59:59(us)
YEAR 1字节 YYYY 从1901到2155
DATETIME 5-8字节 YYYY-MM-DD HH:MM:SS（us微秒) 从 1000-01-01 00:00:00 到9999-12-31 23:59:59
TIMESTAMP 4-7字节 YYYY-MM-DD HH:MM:SS（us微秒) 从1970-01-01 00:00:01 UTC到2038-01-19 03:14:07 UTC

mysql> create table t_timestamp(dt TIMESTAMP);
Query OK, 0 rows affected (0.02 sec)

//需要重点关注的函数now(),它设置的永远是字段被检索到的那一刻的时间戳；
mysql> insert into t_timestamp select now();
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> insert into t_timestamp select now();
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> select * from t_timestamp;
+---------------------+
| dt                  |
+---------------------+
| 2024-10-15 11:58:58 |
| 2024-10-15 12:00:36 |
+---------------------+
2 rows in set (0.00 sec)


mysql> set time_zone='+10:00';//调整时区从东八区到东10区
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t_timestamp;
+---------------------+
| dt                  |
+---------------------+
| 2024-10-16 01:58:58 |
| 2024-10-16 02:00:36 |
+---------------------+
2 rows in set (0.00 sec)

mysql>

2.3.3.4 字符串类型

CHAR（M）：M in (1,255), 固定长度
VARCHAR(M): 一行中所有varchar类型的列所占用的字节数不能超过65535个字节. 编码方式是utf8mb4的情况下 varchar(10)占用40个字节. 存储可变长度的M个字符
TINYTEXT:　最大长度255个字节, 可变长度
TEXT: 最大长度65535个字节,可变长度
ENUM：集合最大数目是65535, 只能插入列表中的值;

create table test(
视频格式 enum('avi','mp4','mpeh')
);

LONGTEXT: 最长

字符串类型几乎可以存储所有类型的数据

2.3.3 命名规则

必须使用小写字母,使用下划线分割,不可以使用中文
不可以使用MYSQL的保留文字
数据库命名不要超过32位字符,表名中最好包括数据库名
所有存储相同类型和内容的数据要具有同样的名字
tmp_名_日期后缀 backup_名_日期后缀

三. 数据库实战部分

常用的SQL语言分类:DCL\DDL\DML\TCL

3.1 访问控制

3.1.1 创建用户账号

设置哪些IP可以访问,怎么设置密码,允许链接的线程数,链接方式是否是SSL链接等.创建用户还会有权限区分,是DBA|RESOURCE|CONNECT;

DBA:可以创建USER|SCHEMA|DATABASE|TABLE
RESOURCE:只可以创建TABLE,不可以创建别的,可以进行数据查询和操纵
CONNECT：都不能创建，但是可以视情况被分配select和操纵的权限

建立数据库账号：create user
授权：grant
收回用户权限：revoke

mysql> create user test@'192.168.124.%' identified with 'mysql_native_password'//默认认证插件
    -> by 'password' with max_user_connections 1;
//只允许网段192.168.124.%（IP访问控制列表）的IP使用账号test访问数据库服务器
//max_user_connections 1代表用户同一时刻在最多只可以有一个线程来链接

Query OK, 0 rows affected (0.00 sec)

另起一个终端：
[lsy@imooc ~]$ mysql -u test -p -h 192.169.124.10
Enter password: 
输入正确密码后也无法连接成功，因为被max_user_connections限制了

mysql> help create user
Name: 'CREATE USER'
Description:
Syntax:
CREATE USER [IF NOT EXISTS]
    user [auth_option] [, user [auth_option]] ...
    DEFAULT ROLE role [, role ] ...
    [REQUIRE {NONE | tls_option [[AND] tls_option] ...}]
    [WITH resource_option [resource_option] ...]
    [password_option | lock_option] ...
    [COMMENT 'comment_string' | ATTRIBUTE 'json_object']

user:
    (see )

auth_option: {
    IDENTIFIED BY 'auth_string' [AND 2fa_auth_option]
  | IDENTIFIED BY RANDOM PASSWORD [AND 2fa_auth_option]
  | IDENTIFIED WITH auth_plugin [AND 2fa_auth_option]
  | IDENTIFIED WITH auth_plugin BY 'auth_string' [AND 2fa_auth_option]
  | IDENTIFIED WITH auth_plugin BY RANDOM PASSWORD [AND 2fa_auth_option]
  | IDENTIFIED WITH auth_plugin AS 'auth_string' [AND 2fa_auth_option]
  | IDENTIFIED WITH auth_plugin [initial_auth_option]
}

2fa_auth_option: {
    IDENTIFIED BY 'auth_string' [AND 3fa_auth_option]
  | IDENTIFIED BY RANDOM PASSWORD [AND 3fa_auth_option]
  | IDENTIFIED WITH auth_plugin [AND 3fa_auth_option]
  | IDENTIFIED WITH auth_plugin BY 'auth_string' [AND 3fa_auth_option]
  | IDENTIFIED WITH auth_plugin BY RANDOM PASSWORD [AND 3fa_auth_option]
  | IDENTIFIED WITH auth_plugin AS 'auth_string' [AND 3fa_auth_option]
}

3fa_auth_option: {
    IDENTIFIED BY 'auth_string'
  | IDENTIFIED BY RANDOM PASSWORD
  | IDENTIFIED WITH auth_plugin
  | IDENTIFIED WITH auth_plugin BY 'auth_string'
  | IDENTIFIED WITH auth_plugin BY RANDOM PASSWORD
  | IDENTIFIED WITH auth_plugin AS 'auth_string'
}

initial_auth_option: {
    INITIAL AUTHENTICATION IDENTIFIED BY {RANDOM PASSWORD | 'auth_string'}
  | INITIAL AUTHENTICATION IDENTIFIED WITH auth_plugin AS 'auth_string'
}

tls_option: {//如果是SSL链接，借助这里进行链接
   SSL
 | X509
 | CIPHER 'cipher'
 | ISSUER 'issuer'
 | SUBJECT 'subject'
}

resource_option: {
    MAX_QUERIES_PER_HOUR count
  | MAX_UPDATES_PER_HOUR count
  | MAX_CONNECTIONS_PER_HOUR count
  | MAX_USER_CONNECTIONS count
}

password_option: {
    PASSWORD EXPIRE [DEFAULT | NEVER | INTERVAL N DAY]
  | PASSWORD HISTORY {DEFAULT | N}
  | PASSWORD REUSE INTERVAL {DEFAULT | N DAY}
  | PASSWORD REQUIRE CURRENT [DEFAULT | OPTIONAL]
  | FAILED_LOGIN_ATTEMPTS N
  | PASSWORD_LOCK_TIME {N | UNBOUNDED}
}

lock_option: {
    ACCOUNT LOCK
  | ACCOUNT UNLOCK
}

The CREATE USER statement creates new MySQL accounts. It enables
authentication, role, SSL/TLS, resource-limit, password-management,
comment, and attribute properties to be established for new accounts.
It also controls whether accounts are initially locked or unlocked.

To use CREATE USER, you must have the global CREATE USER privilege, or
the INSERT privilege for the mysql system schema. When the read_only
system variable is enabled, CREATE USER additionally requires the
CONNECTION_ADMIN privilege (or the deprecated SUPER privilege).

As of MySQL 8.0.27, these additional privilege considerations apply:

o The authentication_policy system variable places certain constraints
  on how the authentication-related clauses of CREATE USER statements
  may be used; for details, see the description of that variable. These
  constraints do not apply if you have the AUTHENTICATION_POLICY_ADMIN
  privilege.

o To create an account that uses passwordless authentication, you must
  have the PASSWORDLESS_USER_ADMIN privilege.

As of MySQL 8.0.22, CREATE USER fails with an error if any account to
be created is named as the DEFINER attribute for any stored object.
(That is, the statement fails if creating an account would cause the
account to adopt a currently orphaned stored object.) To perform the
operation anyway, you must have the SET_USER_ID privilege; in this
case, the statement succeeds with a warning rather than failing with an
error. Without SET_USER_ID, to perform the user-creation operation,
drop the orphan objects, create the account and grant its privileges,
and then re-create the dropped objects. For additional information,
including how to identify which objects name a given account as the
DEFINER attribute, see
https://dev.mysql.com/doc/refman/8.0/en/stored-objects-security.html#st
ored-objects-security-orphan-objects.

CREATE USER either succeeds for all named users or rolls back and has
no effect if any error occurs. By default, an error occurs if you try
to create a user that already exists. If the IF NOT EXISTS clause is
given, the statement produces a warning for each named user that
already exists, rather than an error.

URL: https://dev.mysql.com/doc/refman/8.0/en/create-user.html

3.1.2 创建角色

角色是权限的载体,可以把这个载体分配给批量的用户,进行用户权限的统一管理

mysql> create role r1;
Query OK, 0 rows affected (0.00 sec)

mysql> grant select,update,delete,insert on table t_timestamp to r1;
Query OK, 0 rows affected (0.00 sec)

mysql> grant r1 to test;
ERROR 3523 (HY000): Unknown authorization ID `test`@`%`
mysql> grant r1 to test@'192.168.124.%';
Query OK, 0 rows affected (0.00 sec)

3.1.3 常见的权限

INSERT|DELETE|UPDATE|SELECT
EXECUTE:执行存储过程的权限
全部权限:ALL PRIVILEGES
REFERENCES
CREATE TABLE|SCHEMA|INDEX|VIEW
ALTER TABLE

mysql> show privileges;
+------------------------------+---------------------------------------+-------------------------------------------------------+
| Privilege                    | Context                               | Comment                                               |
+------------------------------+---------------------------------------+-------------------------------------------------------+
| Alter                        | Tables                                | To alter the table                                    |
| Alter routine                | Functions,Procedures                  | To alter or drop stored functions/procedures          |
| Create                       | Databases,Tables,Indexes              | To create new databases and tables                    |
| Create routine               | Databases                             | To use CREATE FUNCTION/PROCEDURE                      |
| Create role                  | Server Admin                          | To create new roles                                   |
| Create temporary tables      | Databases                             | To use CREATE TEMPORARY TABLE                         |
| Create view                  | Tables                                | To create new views                                   |
| Create user                  | Server Admin                          | To create new users                                   |
    
| Delete                       | Tables                                | To delete existing rows                               |
| Drop                         | Databases,Tables                      | To drop databases, tables, and views                  |
| Drop role                    | Server Admin                          | To drop roles                                         |
| Event                        | Server Admin                          | To create, alter, drop and execute events             |
| Execute                      | Functions,Procedures                  | To execute stored routines                            |
| File                         | File access on server                 | To read and write files on the server                 |
| Grant option                 | Databases,Tables,Functions,Procedures | To give to other users those privileges you possess   |
| Index                        | Tables                                | To create or drop indexes                             |
| Insert                       | Tables                                | To insert data into tables                            |
| Lock tables                  | Databases                             | To use LOCK TABLES (together with SELECT privilege)   |
| Process                      | Server Admin                          | To view the plain text of currently executing queries |
| Proxy                        | Server Admin                          | To make proxy user possible                           |
| References                   | Databases,Tables                      | To have references on tables                          |
| Reload                       | Server Admin                          | To reload or refresh tables, logs and privileges 
and so on....     |

3.1.4 授权

授权的时候,数据库账户或者数据库角色必须存在,否则会报错

能进行grant的用户必须具有grant options的权限,不能给别人自己的权限

\h grant 命令帮助了解帮助信息

这个授权原理是自主存取控制(DAC)方法

mysql> grant select(user,host) on mysql.user to test@'192.168.124.%';
Query OK, 0 rows affected (0.00 sec)

mysql> grant select on mysql.user to test@'192.168.124.%';
Query OK, 0 rows affected (0.00 sec)

mysql> grant select,update on mysql.* to test@'192.168.124.%';
Query OK, 0 rows affected (0.00 sec)

//只有有with grant option的用户才能在获取权限后继续传播授权该权限，否则不能授权给别人，只能使用。
mysql> grant select,update on mysql.* to test@'192.168.124.%' with grant option;
Query OK, 0 rows affected (0.00 sec)

3.1.5 收回权限

mysql> revoke select,update on mysql.* from test@'192.168.124.%' cascade;
Query OK, 0 rows affected (0.01 sec)

//cascade 级联收回test@域名用户授权给别的用户的权限；
//restrict 不会级联收回；

3.2 创建数据对象

3.2.1 DDL

建立/修改/删除数据库
- create database db_test set charset_name=? and collation_name(排序规则)=?
- drop database db_test;
- alter database;
建立/修改/删除表: create/alter/drop table table_name;
建立/删除索引: create/drop index
清空表:truncate table约等于drop table+create table,且不保存删除日志,无法通过日志恢复.delete可以恢复;
重命名表:rename table tt_test to tt_project
建立/修改/删除视图:create|alter|drop view view_name;

create database newtest;
use newtest;
create table if not exists tt_test(
index_order INT UNSIGNED auto_increment,
test_project VARCHAR(10) NOT NULL default '' comment 'kechengbiaoti',
test_project_desc varchar(50) not null default '' comment 'kechengmiaoshu',
create_time timestamp not null default current_timestamp comment 'kechengchuangjianshijian',
primary key(index_order),
unique key project_name_index(test_project asc)
)comment 'kecheng';
create index project_create_time on tt_test(create_time);
drop index project_create_time on tt_test;

rename table tt_test to tt_project;
drop table tt_project;
drop database newtest;

3.3 操作数据对象

3.3.1 DML 操作-插入insert

insert into delete update select

-- 确认数据插入到哪个表中，确认表中的数据结构，哪些列不能为空，哪些列有默认值，确认哪些列输入--
show create table tt_project;

3.3.2 select

3.3.2.1 比较运算符

-- 确认数据插入到哪个表中，确认表中的数据结构，哪些列不能为空，哪些列有默认值，确认哪些列输入--
show create table tt_project;

select 'HELLO','MYSQL',2018+1;
-- HELLO	MYSQL	2019 -- 

-- like -- 
select title from imc_course where title like '%MYSQL%';

-- = > < <> != >- < = -- 
select title,study_cnt from imc_course where study_cnt>1000;
-- between and 是约等于 >= <=--
select title,study_cnt from imc_course where study_cnt between 1000 and 2000;

-- is not --
create table test_is(id INT,c1 varchar(10),primary key(id));
insert into test_is values (1,'aa'),(2,NULL),(3,'cc');
select * from test_is where c1 is null;

-- in not in --
select * from imc_course where course_id not in (1,3,5,7,9,10);

3.3.2.2 逻辑运算符

任何运算符和NULL值运算结果都为NULL

-- and && --
select title,study_cnt from imc_course where title like '%mysql%' and study_cnt > 5000;
-- or || --
select title,study_cnt from imc_course where title like '%mysql%' or study_cnt > 5000;
-- xor 异或 两个条件一真一假的时候返回真，都真或都假返回假--
select title,study_cnt from imc_course where title like '%mysql%'  xor study_cnt > 5000;

3.3.2.3 多表查询

(1)利用join

-- 关联表 多表查询 inner join,outerjoin,leftouterjoin,rightouterjoin join默认是内关联 --
select a.course_id,a.title,b.chapter_name
from imc_course a
join/left join/right join imc_chapter b on a.course_id=b.course_id;

insert into imc_course(title,tile_desc,type_id,class_id,level_id,online_time,user_id)
values('MYSQL关联测试','test',8,1,1,NOW(),29);

(2) in not in内查询子查询

select a.course_id,a.title from imc_course a where a.course_id not in (select b.course_id from imc_chapter b);

(3) 分组查询

group by后面可以跟两个条件列

group by 需要结合聚合函数使用

-- 统计每个分类下不同难度课程的数量 --

select class_name,level_name,count(*) as count
from imc_course a join imc_class b on b.class_id=a.class_id
join imc_level c on c.level_id=a.level_id
group by class_name,level_name
having count(*)>3;

(4) 聚合函数

聚合函数可以独立使用,不需要和group by组合

count(*) or count(col)
sum(*sum(col_name)
avg(col)

（1）执行效果：

①count(*)包括了所有的列，相当于行数，在统计结果的时候，不会忽略列值为NULL。

②count(1)忽略所有列，用1代表代码行，在统计结果的时候，不会忽略列值为NULL。

③count(列名)只包括列名那一列，在统计结果的时候，会忽略列值为空。

（2）执行效率：

①列名为主键，count(列名)会比count(1)快

②列名不为主键，count(1)会比count(列名)快

③如果表多个列并且没有主键，则 count（1）的执行效率优于 count（*）

④如果有主键，则 select count（主键）的执行效率是最优的

⑤如果表只有一个字段，则 select count（*）最优

3.4 CTE是什么？

四、SQL优化

4.1 发现有性能问题的SOL

4.1.1 分析慢查询日志发现存在问题的SQL

4.1.1.1 配置开启

默认不启动慢查询日志，需要配置 set global slow_query_log=ON|OFF(默认关闭）
通过set global slow_query_log_file=/sql-log/slowlog.log来设置慢查询日志的存储位置
配置慢的阈值 set global long_query_time=0.001s
配置将没使用索引的SQL也打印出来：set global log_queries_not_using_indexes=ON|OFF，没使用索引的SQL计划都需要优化

4.1.1.2 慢查询日志分析工具

set global long_query_time=0；//测试环境设置为0，这样能看到所有的SQL日志
set global slow_query_log=ON;

mysql> show variables like 'slow_query_log_file';
+---------------------+-------------------------------+
| Variable_name       | Value                         |
+---------------------+-------------------------------+
| slow_query_log_file | /var/lib/mysql/imooc-slow.log |
+---------------------+-------------------------------+
1 row in set (0.05 sec)

mysql> show tables;
+------------------+
| Tables_in_imc_db |
+------------------+
| tt_test          |
+------------------+
1 row in set (0.00 sec)

mysql> select* from tt_test;
Empty set (0.01 sec)

mysql> exit
Bye
[root@imooc mysql]# more imooc-slow.log

mysqldumpslow

mysqldumpslow imooc-slow.log

pt-query-digest(percona工具集）

pt-query-digest imooc-slow.log

pct:百分比
total:总执行次数
rows sent:返回的结果是多少行
rows examine:实际上检索查找了多少行
query size: 检索大小

4.1.2 数据库实时监控长时间运行的SQL

select id, user, host,db,command
from information_schema.processlist
where time > 60;

4.2 分析SQL执行计划

了解SQL中的索引、查询类型（是否是关联查询等），检索什么数据

4.2.1 获取SQL的执行计划

explain + sql语句

4.2.2 怎么分析执行计划

覆盖索引：一个索引包含了满足查询结果的数据就叫做覆盖索引，不需要回表等操作。

ID：表示查询执行的顺序
ID相同的时候从上往下执行,举例，join查询
ID不同的时候由大到小执行，举例，子查询

select_type:执行的查询类型
取值范围：
simple：不包含子查询或是UNION操作的简单查询
primary: 包含子查询的查询的外部查询
subquery：包含子查询的查询的子查询
dependency subquery：被依赖的子查询
union: union查询的第二个查询以及以后查询
dependency union: union查询作为子查询时，union查询的第二个查询以及以后查询

table:哪个表在被查询
partitions：分区表显示分区id，非分区表为NULL；

type: 体现了MYSQL检索数据的方式
取值范围：
system：性能最高，这是const连接类型的一个特例，匹配条件是常量，当查询的表结果只有一行时使用。
const:表中有且只有一个匹配的行，如对主键或者唯一索引的查询，是效率最高的连接方式；
eq_ref:唯一索引或者主键查找，对于每个索引键，表中只有一条记录与之匹配；
ref:非唯一索引查找，给谁匹配某个单独值的所有行
ref_or_null:类似于ref，但是附加了对NULL值的查询
index_merged:该连接类型表示索引合并优化方法
range: 索引范围扫描，between,>,<
index: 全索引扫描，遍历的不是全表，而是全索引树
all：全表扫描


possible_keys：检索的时候可能使用到的索引
keys：检索的时候实际使用到的索引
key_len:实际使用索引的最大长度，比如联合索引里实际只使用了部分列，key_len是以定义的列长度确定的，不是以列中实际存储的长度来确定的。

rows: 根据统计信息预估检索到的行，或者内部循环的次数
filtered: 预估的结果的行数在rows中的占比

extra：
distinct：不需要再次找相同值，有助于优化MYSQL性能
not exists:
using index：使用了覆盖索引进行查询
using temporary:使用临时表来处理查询，常见于排序、子查询和分组查询
using where:
select tables optimized away:直接用索引进行覆盖查询就可以，不需要访问表。

4.3 优化索引

4.3.1 索引简介

索引：告诉存储引擎如何快速的查找到所需要的数据。MYSQL的索引是在存储引擎层实现的，不是线上实现。

索引类型：BTREE（B+数结构，自己看数据结构算法书，INNODB中叶子节点存储主键信息）索引、自适应哈希索引、全文索引（对中文支持不好，建议用搜索引擎类服务替代）、空间索引

4.3.2 应该在哪些列上建立索引？

where字句中这样的列：每行取值都不同或者说有差异的行很多的列；
order by（索引的列的顺序和order by的顺序是一致的，升降序一致，多个表关联中，查询中，order的所有列都在第一个表里）,group by,distinct中的字段，避免临时表。
多表关联查询的列，一定要建立索引；

4.3.3 复合索引键的内部列顺序

区分度最高的在左侧；
使用最频繁的放左侧；
字段长度小的列放在联合索引键的最左侧；

4.3.4 B树索引的适用场景

适合in查询（in内条件字段数目不多的情况），适合全值匹配的查询
适合范围查询（BETWEEN and and and > and <）

4.3.5 哪些SQL用不了B树索引

针对联合索引，B树索引必须从联合索引的最左侧进行匹配，如果条件不包含能联合索引的最左侧部分匹配上的列字段，那就使用不了这个索引了。注意联合索引内部的字段是不能调过的。
not in 和<>的操作用不了索引；
索引列上不能使用表达式或者函数；

4.3.6 索引的效果

索引别太多，太多了影响更新、插入、删除性能
IN列表可以使用索引，但IN列表内容别太多，不然就从索引切换到全文查找
查询过滤顺序和索引键顺序不同，也可以使用

4.4 改写SQL

使用outer join代替not in，因为not in 的条件列表太多的时候就不要用索引了。
- select class_name from imc_class where class_id not in(select class_id from imc_course)
- select class_name from imc_class a left join imc_course b on a.class_id=b.class_id where b.class_id is null;
- 现在已经自动支持；
使用CTE代替子查询；
拆分复杂的大SQL为多个简单的小SQL；因为一个SQL只能使用一个CPU的核心；
巧用计算列优化查询；
- select * from imc_classvalue where (content_score+level_score+logic_score)>28.
- 这种方式用不到索引
- alter table imc_classvalue add column total_score decimal(3,1) as (content_score+level_score+logic_score)
- create index idx_totalScore on imc_classvalue(total_score)
- 这样就能用到索引了

4.5 对数据库进行垂直切分和水平切分

五、事务

5.1 事务是什么？

事务是数据库执行操作的最小逻辑单元，可以由一个SQL或者多个SQL组合完成，要么全部执行成功要么全部执行失败；

begin
select
update
insert
commit/rollback

事务具有ACID特性

原子性A：一个事务中的某些操作要么全部完成，要么全部不完成
一致性C：在事务开始前和事务结束后，数据库的完整性没有被破坏
隔离性I：每个事务提交前对其他事务都不可见
持久性D：事务一旦提交，且结果就是永久性的，就算宕机，数据也得能恢复

5.2 并发带来的问题

脏读：一个事务读取了另一个事务未提交的数据
不可重复读：一个事务前后两次读取的同一数据不一致
幻读：一个事务两次查询的结果集记录数不一致

5.3 INNODB的隔离级别

顺序读SERIALIZE：三种并发问题都没有，但是并发性能最差，隔离性最好。
可重复读：没办法避免幻读，但是INNODB支持避免幻读，有锁的处理
读已提交：只能避免脏读
读未提交：啥并发问题都可能有

5.4 事务的实战

set session transaction isolation level serializable;
show variables like '%ios%';
-- 事务1 --
begin;
select course_id,title from imc_course where score>9.6;
commit;

-- 事务2 --
begin;
update imc_course set score=9.8 where course_id=34;
commit;

顺序读的话事务1没commit事务2执行不了
可重复读的话事务1没提交的时候，事务2可以执行和提交但是提交以后事务1的select语句重新执行，这个时候还不能读到事务2更新的值，因为事务1开始的时候事务2没提交。

5.5 事务阻塞的产生

可重复读的情况下也有事务阻塞。

事务1
begin;
update imc_course
set score=score+0.1
where score>9.6;

commit;

事务2
begin;
update imc_course
set score=score-0.1
where score>9.6;
commit;

事务1执行期间没提交的时候事务2没法执行，这就叫做阻塞

5.6 产生阻塞的原因——锁

INNODB中的锁：

共享锁（S）：查询加共享锁（S）。被加锁的对象只能被具有锁的对象进行读取但不能修改，其他对象也可以对该对象进行读取和叠加共享锁。
资源排他锁（X）：修改加排它锁（S）。被加锁的对象只能被具有锁的对象进行读取和修改，其他对象不可以对该对象加锁、读取和修改。
只有共享锁和共享锁之间兼容，排它锁和任何锁都不兼容。

阻塞就是因为不同锁之间的兼容关系，造成的一个事务需要等待另一个事务释放其所占用资源的现象。往往是这另一个事务占用使用的太慢了太久了导致了。

5.7 如何检测阻塞？

可以通过innodb_lock_waits表检索

先确定当前的connection_id
select connection_id()  --20/27

检索锁等待表(只记录没执行完的sql)找等待时间太长的connection_id
select blocked_pid（被阻塞的进程）,running_pid（哪个进程导致的） from sys.innodb_lock_waits where (UNIX_TIMESTAMP()-UNIX_TIMESTAMP(wait_started))>30

杀掉导致阻塞的进程
kill running_pid;
kill 20;

27号回滚一下
rollback;

5.8 如何处理阻塞？

手动终止占用资源的事务
对占用资源的事务的SQL进行优化，令其尽快释放资源

5.9 什么是死锁？

并行执行的多个事务相互之间占有了对方需要的资源；

MYSQL内部会对死锁进行监控并主动将占用资源较少的事务进行回滚释放资源；

5.10 如何发现和处理死锁？

set global innodb_print_all_deadlocks=ON

这样记录的日志的SQL记录的不全，很可能发现不了互相僵持的两个SQL

需要从日志里一点点筛选

处理方式：银行家算法，考虑设计并发的顺序。或者并发按相同的顺序进行执行，这样只会阻塞，然后很快结束。

MYSQL基础自学

一. 数据库选型

1.1 SQL和NOSQL区别

1.1.1 关系数据库特点

1.1.2 关系数据库的使用场景

1.1.3 非关系型数据库的特点

1.1.4 非关系型数据库的适用场景

1.2 关系数据库的选型原则

1,3 MYSQL作为关系数据库的选型优势

1.4 MYSQL的安装规则

二. 根据需求进行数据库结构设计

2.1 业务分析

2.2 逻辑设计

2.2.1 宽表模式

2.2.2 数据库的设计范式

2.3 物理设计

2.3.1 存储引擎

2.3.1.1 存储引擎分类

2.3.1.2 怎么根据存储引擎进行优化(一小部分优化)

2.3.2 数据类型