MySQL

mysql5

mysql8

find / -name mysql

mysql5

mysql8

rm -f /etc/my.cnf* rm -f /var/log/mysqld.log

rpm -qa|grep libaio rpm -qa|grep net-tools

ll -a /

chmod -R 777 /tmp

cd /opt tar xvf mysql-8.0.29-1.el7.x86_64.rpm-bundle.tar

rpm -ivh mysql-community-common-8.0.29-1.el7.x86_64.rpm rpm -ivh mysql-community-client-plugins-8.0.29-1.el7.x86_64.rpm rpm -ivh mysql-community-libs-8.0.29-1.el7.x86_64.rpm rpm -ivh mysql-community-icu-data-files-8.0.29-1.el7.x86_64.rpm rpm -ivh mysql-community-client-8.0.29-1.el7.x86_64.rpm rpm -ivh mysql-community-server-8.0.29-1.el7.x86_64.rpm # -i, --install 安装软件包 # -v, --verbose 提供更多的详细信息输出 # -h, --hash 软件包安装的时候列出哈希标记 (和 -v 一起使用效果更好)，展示进度条

rpm -qa|grep -i mysql mysqladmin --version

#初始化数据目录并生成初始密码 mysqld --initialize --user=mysql #查看数据目录 ls /var/lib/mysql/

#mysql安装完成之后，在/var/log/mysqld.log文件中给root生成了一个默认密码。通过下面的方式找到root默认密码，然后登录mysql。 grep 'temporary password' /var/log/mysqld.log 或者使用 more /var/log/mysqld.log

ALTER USER 'root'@'localhost' IDENTIFIED BY '123456'; -- 或者 SET password FOR 'root'@'localhost'= '123456';

ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY '123456';

（1） `CREATE和DROP权限` 可以创建新的数据库和表，或删除已有的数据库和表。 （2） `SELECT、INSERT、UPDATE和DELETE权限` 允许在一个数据库现有的表上实施操作。 （3） `INDEX权限` 允许创建或删除索引。 （4） `ALTER权限` 可以使用ALTER TABLE来更改表的结构和重新命名表。 （5） `GRANT权限` 允许授权给其他用户，可用于数据库、表和保存的程序。

语法： GRANT 权限1,权限2,…权限n ON 数据库名称.表名称 TO 用户名@用户地址 [IDENTIFIED BY ‘密码口令’];

GRANT ALL PRIVILEGES ON *.* TO 'tian7'@'%'; --授权所有限权，除给其他用户授权外

放到授权最后，给其他用户授予 授权 的权限 WITH GRANT OPTION

GRANT SELECT, INSERT, UPDATE ON atguigudb.* TO 'zhao6'@'%'; --授权

当前用户：SHOW GRANTS;

其他用户：SHOW GRANTS FOR '用户名'@'主机地址';

REVOKE 权限1, 权限2, …权限n ON 数据库名称.表名称 FROM '用户名'@'主机地址';

收回某库限权：REVOKE SELECT ON atguigudb.* FROM 'zhao6'@'%';

收回所有限权：REVOKE ALL PRIVILEGES ON *.* FROM 'zhao6'@'%';

设置sql_mode 模式 ： SET SESSION sql_mode = 'ONLY_FULL_GROUP_BY, STRICT_TRANS_TABLES, NO_ZERO_IN_DATE, NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO, NO_ENGINE_SUBSTITUTION';

sql_mode说明

普通索引指在数据表中非唯一和主键值的列

唯一索引是给唯一键建立索引

主键索引InnoDB执行引擎自动创建，手动创建表格时创建索引也可以

全文索引不建议创建，会导致表与表关系错综复杂且难以管理

单例索引是指一个字段建立索引；

联合索引指多字段建立索引;使用多字段查找时会速度更快

聚簇索引 是 指 主键索

非聚簇索引 是 指 非主键的普通索引

ALTER TABLE ADD ( 索引值1，索引值2，........ )
ALTER TABLE customer1 ADD PRIMARY KEY (id);-- 主键索引
ALTER TABLE customer1 ADD UNIQUE INDEX uk_no (customer_no);-- 唯一索引
ALTER TABLE customer1 ADD INDEX idx_name (customer_name);-- 普通索引
ALTER TABLE customer1 ADD INDEX idx_no_name (customer_no,customer_name); -- 复合索引

CREATE 索引名 ON 表名 ( 索引值1，索引值2，....... )
CREATE PRIMARY KEY (id); --主键索引
CREATE UNIQUE INDEX uk_no ON customer1(customer_no); -- 唯一索引
CREATE INDEX idx_name ON customer1(customer_name);-- 普通索引
CREATE INDEX idx_no_name ON customer1(customer_no,customer_name); -- 复合索引

对于Varchar类型的数据，如果数据过长，可以通过创建部分长度索引；
需要注意的是，前缀索引和函数索引都会导致一些查询时索引失效，因此需要根据实际情况进行权衡和选择。
CREATE INDEX  索引名  ON  表名(字段名(截取长度));
CREATE INDEX idx_address_prefix ON table_name(address(12));--前缀索引
CREATE INDEX  索引名  ON  表名(字段名(截取长度));
CREATE INDEX idx_address_func ON table_name(LEFT(address, 12));--函数索引

SHOW INDEX FROM customer;

DROP INDEX ON ; -- 删除单值、唯一、复合索引
DROP INDEX idx_name ON customer;
ALTER TABLE <表名>  MODIFY<列名> INT,DROP PRIMARY KEY; -- 删除主键索引(有主键自增)
ALTER TABLE customer MODIFY id INT, DROP PRIMARY KEY; -- 删除主键索引(有主键自增)
ALTER TABLE <表名>DROP PRIMARY KEY; -- 删除主键索引(没有主键自增)
ALTER TABLE customer DROP PRIMARY KEY; -- 删除主键索引(没有主键自增)

**哪些情况适合创建索引：**

（1）主键自动建立唯一索引
（2）字段的值有唯一性的限制
（3）频繁作为WHERE查询条件的字段
（4）UPDATE、DELETE的WHERE条件列
（5） 经常GROUP BY 和 ORDER BY的列
（6）DISTINCT字段需要创建索引- 多表JOIN时，对连接字段创建索引
（7） 使用字符串前缀创建索引
（8） 区分度高的列（重复的数据少）适合作为索引- 使用频繁的列，放到联合索引的左侧- 多个字段都需要创建索引时，联合索引优于单值索引

**哪些情况不要创建索引：**

（1）WHERE里用不到的字段不创建索引
（2） 表的数据记录太少
（3） 有大量重复数据的列上
（4）避免对经常增删改的表创建索引
（5）要定义冗余或重复的索引
（6） 删除不再使用或很少使用的索引

B树简单示意图(一)

（1）B+树中非叶子节点的关键字也会同时存在在子节点中，并且是在子节点中所有关键字的最大值（或最小）。
（2）B+树中非叶子节点仅用于索引，不保存数据记录，跟记录有关的信息都放在叶子节点中。
（3）B+树中所有关键字都在叶子节点出现，叶子节点构成一个有序链表，而且叶子节点本身按照关键字的大小从小到大顺序链接。
（4）补充：IO的次数和树的高度有关，减少IO次数就需要降低树的高度；

B+树单数据页横 示意图(一)

B+树 完整模块示意图(一)

**SIMPLE：**简单查询。查询中不包含子查询或者UNION。

**PRIMARY：**主查询。查询中若包含子查询，则最外层查询被标记为PRIMARY。
**SUBQUERY：**子查询。在SELECT或WHERE列表中包含了子查询。

**DEPENDENT SUBQUREY：**如果包含了子查询，并且查询语句不能被优化器转换为连接查询，并且子查询是`相关子查询（子查询基于外部数据列）`，则子查询就是DEPENDENT SUBQUREY。

**UNCACHEABLE SUBQUREY：**表示这个subquery的查询要受到外部系统变量的影响

**UNION：**对于包含UNION或者UNION ALL的查询语句，除了最左边的查询是PRIMARY，其余的查询都是UNION。
**UNION RESULT：**UNION会对查询结果进行查询去重，MYSQL会使用临时表来完成UNION查询的去重工作，针对这个临时表的查询就是"UNION RESULT"。

**DEPENDENT UNION：**子查询中的UNION或者UNION ALL，除了最左边的查询是DEPENDENT SUBQUREY，其余的查询都是DEPENDENT UNION。

**DERIVED：**在包含`派生表（子查询在from子句中）`的查询中，MySQL会递归执行这些子查询，把结果放在临时表里。

**MATERIALIZED：**优化器对于包含子查询的语句，`如果选择将子查询物化后再与外层查询连接查询`，该子查询的类型就是MATERIALIZED。如下的例子中，查询优化器先将子查询转换成物化表，然后将t1和物化表进行连接查询。

结果值从最好到最坏依次是： 
system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

阿里巴巴开发手册要求：SQL 性能优化的目标：至少要达到 `range` 级别，要求是 `ref` 级别，最好是 `consts`级别。

计算公式：

**Impossible WHERE**：where子句的值总是false

**Using where：**使用了where，但在where上有字段没有创建索引

**Using temporary：**使了用临时表保存中间结果

**Using filesort：**排序操作无法使用到索引，只能在内存中（记录较少时）或者磁盘中（记录较多时）进行排序（filesort）

**Using index：**使用了覆盖索引，表示直接访问索引就足够获取到所需要的数据，不需要通过索引回表

**Using index condition：**叫作  `Index Condition Pushdown Optimization （索引下推优化ICP）：找到满足条件的数据后进行判断是否满足Index中剩余条件是否符合，符合返回给server层，不满足就再找下一条

**Using join buffer：**在连接查询时，当`被驱动表`不能有效的利用索引时，MySQL会提前申请一块内存空间（join buffer）来加快查询速度

数据库中的数据的索引列的NULL值达到比较高的比例的时候`，即使在IS NOT NULL 的情况下 MySQL的查询优化器会选择使用索引，此时type的值是range（范围查询）

EXPLAIN SELECT * FROM emp WHERE name= '123' ; 
EXPLAIN SELECT * FROM emp WHERE name=  123 ; --索引失效
name 是 varchar 类型数据 输入查询条件 是 int 类型，类型转换

被驱动表没有索引 ： 驱动表全部数据 * 被驱动表所有数据 （ 16 * 20 次）
被驱动表有索引 ： 驱动表数据 * 被驱动表数据（ 16 * 1 次）