mysql~高级学习~初

it2022-05-06  3

这是一篇比较长的关于Mysql高级的一部分相关知识点,里边有很多小细节,需要注意的点,希望对大家有所帮助,有耐心的小伙伴可以借鉴看一看,欢迎指出不足,多多评论!!!

小细节 行锁会出现死锁 csv 简单数据传输 一直在用左外连接,很少用又外连接 BTree平衡树 mysql的底层是B+Tree =@@字符串 sql语句这种格式是系统变量

key_len range 范围查询 命中效率越高 越好查询越快

rows 物理扫描函数 越小越快 char 可变 varchar不可变

quit退出mysql

mysql -uroot -p123456 登录mysql

区别为什么不加分号 mysql> create database mydb;这是sql语句 Query OK, 1 row affected (0.00 sec)

mysql> use mydb 这是 命令 Database changed mysql> =Mysql简介===== MySQL是一个关系型数据库管理系统,支持大型的数据库。可以处理拥有上千万条记录的大型数据库。使用标准的SQL数据语言形式。 =Mysql_Linux版本的安装===== =5.7= ①检查工作【 *检查当前系统是否安装过mysql rpm -qa|grep mariadb 如果存在如下: mariadb- libs-5.5.56-2.el7.x86_64 请先执行卸载命令:rpm -e --nodeps mariadb-libs *检查当前mysql依赖环境 rpm -qa|grep libaio 存在如下

rpm -qa|grep net-tools 存在如下

*检查/tmp文件夹权限 LL / 查看根目录 看见 tmp 就行 】 ②安装【 在mysql的安装文件目录下执行:(必须按照顺序执行) rpm -ivh mysql-community-common-5.7.16-1.el7.x86_64.rpm rpm -ivh mysql-community-libs-5.7.16-1.el7.x86_64.rpm rpm -ivh mysql-community-client-5.7.16-1.el7.x86_64.rpm rpm -ivh mysql-community-server-5.7.16-1.el7.x86_64.rpm

如在检查工作时,没有检查mysql依赖环境在安装mysql-community-server会报错 】 ③查看MySQL安装版本【 执行 mysqladmin --version命令 出现: mysqladmin Ver 8.42 Distrib 5.7.16, for Linux on x86_64 】 ④mysql服务的初始化【 为了保证数据库目录为与文件的所有者为 mysql 登陆用户,如果你是以 root 身份运行 mysql 服务,需要执行下面的命令初始化 mysqld --initialize --user=mysql

另外 --initialize 选项默认以“安全”模式来初始化,则会为 root 用户生成一个密码并将该密码标记为过期,登陆后你需要设置一个新的密码

查看密码:cat /var/log/mysqld.log

root@localhost: 后面就是初始化的密码 】 ⑤mysql服务的启+停【 启动:systemctl start mysqld.service 】 ⑥ ⑦自启动mysql服务【 是不是自启 如果不是 得设置 [root@study ~]# systemctl list-unit-files |grep mysqld 查看自启软件 mysqld.service enabled 代表自启 &如不是enabled可以运行如下命令设置自启动 启动:systemctl enable mysqld.sercice 关闭:systemctl stop mysqld.service 】 ⑧修改字符集问题【 navicat 链接不到虚拟机(ubuntu)的mysql 怎么办 修改mysql字符集 vim /etc/my.cnf 进入配置文件 character_set_Server=utf8 最后一行 systemctl restart mysqld 重启mysql systemctl status mysqld 查看 alter database mydb character set ‘utf8’; 修改库字符集 alter table mytbl convert to character set ‘utf8’; 修改表字符集 】

=Mysql用户权限与管理=====

use mysql 修改远程访问 select * from user\G; 列出表 select host,user,authentication_string from user; 查看远程访问用户 (localhost 代表自己访问) create user ljw01 identified by ‘123456’; 创建一个远程用户访问 (%代表所有的远程访问) 必须是在某一数据库下 重点 必须关一遍数据库 然后在按地底下步骤 就可以连接 注意:修改完用户的密码后 一定要执行这一句 flush privileges 才会生效

创建一个root用户并给它授权 grant all privileges on . to root@’%’ identified by ‘123456’;

关闭防火墙 systemctl stop firewalld.service 查看防火墙 firewall-cmd --state 查看状态 not running 表示没有运行 systemctl disable firewalld.service 关闭防火墙

=============================================

=Mysql一些杂项配置(了解)===== 1.show variables like ‘sql_mode’; 查看 常用值

ONLY_FULL_GROUP_BY: 对于GROUP BY聚合操作,如果在SELECT中的列,没有在GROUP BY中出现,那么这个SQL是不合法的,因为列不在GROUP BY从句中

NO_AUTO_VALUE_ON_ZERO: 该值影响自增长列的插入。默认设置下,插入0或NULL代表生成下一个自增长值。如果用户 希望插入的值为0,而该列又是自增长的,那么这个选项就有用了。

STRICT_TRANS_TABLES: 在该模式下,如果一个值不能插入到一个事务表中,则中断当前的操作,对非事务表不做限制

NO_ZERO_IN_DATE: 在严格模式下,不允许日期和月份为零

NO_ZERO_DATE: 设置该值,mysql数据库不允许插入零日期,插入零日期会抛出错误而不是警告。

ERROR_FOR_DIVISION_BY_ZERO: 在INSERT或UPDATE过程中,如果数据被零除,则产生错误而非警告。如 果未给出该模式,那么数据被零除时MySQL返回NULL

NO_AUTO_CREATE_USER: 禁止GRANT创建密码为空的用户

NO_ENGINE_SUBSTITUTION: 如果需要的存储引擎被禁用或未编译,那么抛出错误。不设置此值时,用默认的存储引擎替代,并抛出一个异常

PIPES_AS_CONCAT: 将"||"视为字符串的连接操作符而非或运算符,这和Oracle数据库是一样的,也和字符串的拼接函数Concat相类似

ANSI_QUOTES: 启用ANSI_QUOTES后,不能用双引号来引用字符串,因为它被解释为识别符 ORACLE: 设置等同:PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE, NO_KEY_OPTIONS, NO_TABLE_OPTIONS, NO_FIELD_OPTIONS, NO_AUTO_CREATE_USER. =Mysql逻辑架构介绍(重)===== sal执行流程 ----重点

①先和连接池沟通连接 ②做缓存,缓冲查询 ③SQL接口分析sql ④解析器解析复杂sql ⑤优化器,不影响结果进行优化,生成执行计划 ⑥存入引擎按计划分类型执行 ⑦存入缓存,并返回结果 ==============利用show profile 查看sql的执行周期= ①先进入配置文件 vim /etc/my.cnf ②添加这一句话 query_cache_type=1 ③然后重启 systemctl restart mysqld ④查看启动成功没有 systemctl status mysqld ⑤查看是否开启 show variables like ‘%profiling%’; ±-----------------------±------+ | Variable_name | Value | ±-----------------------±------+ | have_profiling | YES | | profiling | OFF | 代表未开 | profiling_history_size | 15 | ±-----------------------±------+ ⑥开启密令 set profiling=1; ⑦可以使用 查表了 ⑧显示最近几次查询 show Profiles ⑨查看程序的执行步骤 (查更详细 但是得带上id) show profile cpu,block io for query id(?); 注意:命中缓冲 sql 得一样 mysql执行顺序 手写 和 机读 是不一样的 Distinct 去重 =Mysql存储引擎= 1.查看命令: 看你的mysql现在已提供什么存储引擎: mysql> show engines; 看你的mysql当前默认的存储引擎: mysql> show variables like ‘%storage_engine%’; 2. 对比项 MyIsAM InnoDB(默认使用) 外键 不支持 支持 事物 不支持 支持 表 行 锁 表锁,即使操作一条记录,整个表都会被锁住,不适合高并发 行锁 操作是只会锁定某一行,不会影响其它行,适合高并发 缓存 只缓存索引,不缓存真是数据 缓存索引,而且缓存真是数据,对内存要求比较高,内存大小对性能有直接的影响 关注点 节省资源,消耗少,简单业务 并发写,事物,更大资源 默认安装 Y Y 默认使用 N Y 自带系统表使用 Y N 各个引擎简介(了解) 1、InnoDB存储引擎 InnoDB是MySQL的默认事务型引擎,它被设计用来处理大量的短期(short-lived)事务。除非有非常特别的原因需要使用其他的存储引擎,否则应该优先考虑InnoDB引擎。

2、MyISAM存储引擎 MyISAM提供了大量的特性,包括全文索引、压缩、空间函数(GIS)等,但MyISAM不支持事务和行级锁,有一个毫无疑问的缺陷就是崩溃后无法安全恢复。

3、Archive引擎 Archive档案存储引擎只支持INSERT和SELECT操作,在MySQL5.1之前不支持索引。 Archive表适合日志和数据采集类应用。 根据英文的测试结论来看,Archive表比MyISAM表要小大约75%,比支持事务处理的InnoDB表小大约83%。

4、Blackhole引擎 Blackhole引擎没有实现任何存储机制,它会丢弃所有插入的数据,不做任何保存。但服务器会记录Blackhole表的日志,所以可以用于复制数据到备库,或者简单地记录到日志。但这种应用方式会碰到很多问题,因此并不推荐。

5、CSV引擎 以逗号的形式展现 CSV引擎可以将普通的CSV文件作为MySQL的表来处理,但不支持索引。 CSV引擎可以作为一种数据交换的机制,非常有用。 CSV存储的数据直接可以在操作系统里,用文本编辑器,或者excel读取。

6、Memory引擎 如果需要快速地访问数据,并且这些数据不会被修改,重启以后丢失也没有关系,那么使用Memory表是非常有用。Memory表至少比MyISAM表要快一个数量级。

7、Federated 联合引擎 Federated引擎是访问其他MySQL服务器的一个代理,尽管该引擎看起来提供了一种很好的跨服务器的灵活性,但也经常带来问题,因此默认是禁用的

=索引优化分析===== 1.SQL JOINS 一共7种 全连接mysql 没有 orcal有 注意:面试问道 有哪些连接的时候,顺带加一句话,我们一般用左外连接

内连接(INNER JOIN) 外连接(OUTER JOIN)【 左外链接(LEFT JOIN) 右外连接(RIGHT JOIN) 全连接(FULL JOIN)包含左、右两个表的全部行,不管另外一边的表中是否存在与它们匹配的行。不符合条件的,以空值代替 】 当查询上下结果都一样时 用 UNION ALL 快 ==建表= CREATE TABLE t_dept ( id INT(11) NOT NULL AUTO_INCREMENT, deptName VARCHAR(30) DEFAULT NULL, address VARCHAR(40) DEFAULT NULL, PRIMARY KEY (id) ) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

CREATE TABLE t_emp ( id INT(11) NOT NULL AUTO_INCREMENT, name VARCHAR(20) DEFAULT NULL, age INT(3) DEFAULT NULL, deptId INT(11) DEFAULT NULL, empno int not null, PRIMARY KEY (id), KEY idx_dept_id (deptId) ) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

举例: 1 所有有门派的人员信息 ( A、B两表共有) select * from t_emp a inner join t_dept on a.deptid=b.id; 2.列出所有用户,并显示其机构信息 (A的全集) select * from t_emp a left join t_dept b on a.deptid=b.id; 3 列出所有门派 (B的全集) select * from t_dept b

4 所有不入门派的人员 (A的独有) SELECT * FROM t_emp a LEFT JOIN t_dept b ON a.deptId=b.id WHERE b.id IS NULL; 5 所有没人入的门派 (B的独有) SELECT * FROM t_emp a RIGHT JOIN t_dept b ON a.deptId = b.id WHERE a.deptId IS NULL; 6 列出所有人员和机构的对照关系 (AB全有) MySQL Full Join的实现 因为MySQL不支持FULL JOIN,下面是替代方法 #left join + union(可去除重复数据)+ right join

SELECT * FROM t_emp A LEFT JOIN t_dept B ON A.deptId = B.id UNION SELECT * FROM t_emp A RIGHT JOIN t_dept B ON A.deptId = B.id

7 列出所有没入派的人员和没人入的门派 (A的独有+B的独有) SELECT * FROM t_emp a LEFT JOIN t_dept b ON a.deptId = b.id WHERE b.id IS NULL UNION SELECT * FROM t_emp a RIGHT JOIN t_dept b ON a.deptId = b.id WHERE a.deptId IS NULL;

增加掌门人字段 ALTER TABLE t_dept add CEO INT(11) ;

update t_dept set CEO=2 where id=1; update t_dept set CEO=4 where id=2; update t_dept set CEO=6 where id=3; update t_dept set CEO=8 where id=4; update t_dept set CEO=9 where id=5;

8.求各个门派对应的掌门人名称: SELECT * FROM t_emp a INNER JOIN t_dept b ON b.CEO=a.id; 9.求所有当上掌门人的平均年龄 SELECT AVG(a.age) FROM t_emp a INNER JOIN t_dept b ON b.CEO=a.id; 10.求所有人物对应的掌门名称: SELECT c.name,ab.name ceoname FROM t_emp c LEFT JOIN (SELECT b.id,a.name FROM t_emp a INNER JOIN t_dept b ON b.CEO=a.id)ab ON c.deptId=ab.id;

SELECT ab.name,c.name ceoname FROM (SELECT a.name,b.ceo FROM t_emp a LEFT JOIN t_dept b ON a.deptId=b.id)ab LEFT JOIN t_emp c ON ab.ceo=c.id;

SELECT a.name,c.name ceoname FROM t_emp a LEFT JOIN t_dept b ON a.deptId=b.id LEFT JOIN t_emp c ON b.CEO=c.id;

SELECT a.name,(SELECT c.name FROM t_emp c WHERE c.id=b.CEO) ceoname FROM t_emp a LEFT JOIN t_dept b ON a.deptid=b.id;

索引的简介= 1.索引是什么? ①索引(Index)是帮助MySQL高效获取数据的数据结构。 可以得到索引的本质:索引是数据结构。 ②可以简单理解为“排好序的快速查找数据结构”。 ③一般来说索引本身也很大,不可能全部存储在内存中,因此索引往往以索引文件的形式存储的磁盘上 2.优缺点? 优: 通过索引列对数据进行排序,降低数据排序的成本,降低了CPU的消耗 类似大学图书馆建书目索引,提高数据检索的效率,降低数据库的IO成本 缺: 虽然索引大大提高了查询速度,同时却会降低更新表的速度,如对表进行INSERT、UPDATE和DELETE。 因为更新表时,MySQL不仅要保存数据,还要保存一下索引文件每次更新添加了索引列的字段, 都会调整因为更新所带来的键值变化后的索引信息

3.mysql的索引结构?

①BTree索引:无限的内存下,使用它比较好 数据 向下的指针 指向数据指针

②B+Tree索引 :有限内存下,使用它 数据 向下的指针 ③B树和B+树的区别 B+树的查询效率更加稳定 B+树的磁盘读写代价更低 ④聚簇和非聚簇索引 是一种数据存储方式。 ‘聚簇’表示数据行和相邻的键值聚簇的存储在一起。 注意点: 对于mysql数据库目前只有innodb数据引擎支持聚簇索引,而Myisam并不支持聚簇索引。 由于数据物理存储排序方式只能有一种,所以每个Mysql的表只能有一个聚簇索引。一般情况下就是该表的主键。 为了充分利用聚簇索引的聚簇的特性,所以innodb表的主键列尽量选用有序的顺序id,而不建议用无序的id,比如uuid这种。 4.mysql索引的分类 查看索引:show index from 表名 单独增加单值索引: CREATE INDEX idx_customer_name ON customer(customer_name);

删除索引: DROP INDEX idx_customer_name on customer;

单独建唯一索引: CREATE UNIQUE INDEX idx_customer_no ON customer(customer_no);

删除索引: DROP INDEX idx_customer_no on customer ; 使用ALTER 命令创建索引 有四种方式来添加数据表的索引: ALTER TABLE tbl_name ADD PRIMARY KEY (column_list): 该语句添加一个主键,这意味着索引值必须是唯一的,且不能为NULL。

ALTER TABLE tbl_name ADD UNIQUE index_name (column_list): 这条语句创建索引的值必须是唯一的(除了NULL外,NULL可能会出现多次)。

ALTER TABLE tbl_name ADD INDEX index_name (column_list): 添加普通索引,索引值可出现多次。

ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list):该语句指定了索引为 FULLTEXT ,用于全文索引。 什么情况下创建索引

什么情况下不创建索引

性能分析== 1.查看执行计划:使用EXPLAIN关键字可以模拟优化器执行SQL查询语句,从而知道MySQL是 如何处理你的SQL语句的。分析你的查询语句或是表结构的性能瓶颈 2.能干啥? ①哪些索引被实际使用 ②每张表有多少行被物理查询 ③表的读取顺序 ④哪些索引可以使用 ⑤数据读取操作的操作类型 ⑥表之间的引用

3.使用方式 Explain + SQL语句 4.各字段解释 id【 相同 : 自上而下 不相同:id不同,如果是子查询,id的序号会递增,id值越大优先级越高,越先被执行 相同不同 同时存在:不相同 先从大到小,相同 从上到下 关注点:id号每个号码,表示一趟独立的查询。一个sql 的查询趟数越少越好。 】 type【 type显示的是访问类型,是较为重要的一个指标,结果值从最好到最坏依次是:

system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

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

一般来说,得保证查询至少达到range级别,最好能达到ref 】 possible_keys【 显示可能应用在这张表中的索引,一个或多个。 查询涉及到的字段上若存在索引,则该索引将被列出,但不一定被查询实际使用 】 key_len【 数值越大越好 】 rows【 ows列显示MySQL认为它执行查询时必须检查的行数。 越小越好 】

Extar重【 group by 包含order by Using filesort 文件排序 Using temporary using join buffer 】 impossible where【 表达式错误 】


最新回复(0)