这是一篇比较长的关于Mysql高级的一部分相关知识点，里边有很多小细节，需要注意的点，希望对大家有所帮助，有耐心的小伙伴可以借鉴看一看，欢迎指出不足，多多评论！！！
小细节 行锁会出现死锁 csv 简单数据传输 一直在用左外连接，很少用又外连接 BTree平衡树 mysql的底层是B+Tree =@@字符串 sql语句这种格式是系统变量
key_len range 范围查询 命中效率越高 越好查询越快
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用户权限与管理=====
=============================================
=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;
举例： 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
增加掌门人字段 ALTER TABLE t_dept add CEO INT(11) ;
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【 表达式错误 】
