MySQL高级部分学习
ubuntu上安装MySQL
- 更新apt-get索引：
sudo apt-get update - 安装MySQL：
sudo apt-get install mysql-server
（mysql 5.7.30会在安装时要求设置登录密码） - 查看MySQL版本：
mysql -V - 进入MySQL：
mysql -u root -p 自己设置的密码 - 退出mysql界面：
exit
其他mysql相关操作：
- 启动mysql服务：
sudo service mysql start（安装完默认启动） - 重启mysql服务：
sudo service mysql restart - 停止mysql服务：
sudo service mysql stop
MySQL的主要配置文件
| 文件名称 | 作用 |
|---|---|
| 二进制日志log-bin | 用于主从复制 |
| 错误日志log-error | 用于记录严重警告信息，启停信息 |
| 查询日志log | 记录查询的sql语句 |
| .frm文件 | 存放表结构 |
| .myd | 存放表数据 |
| .myi | 存放表索引 |
MySQL的存储引擎
在mysql中用命令 show engines;查看所有的存储引擎。以下对两种常用引擎对比：
| 对比项 | MyISAM | Innodb |
|---|---|---|
| 主外键 | 不支持 | 支持 |
| 事务 | 不支持 | 支持 |
| 行表锁 | 表锁，操作一条记录锁住整个表，不适合高并发 | 行锁，操作时只锁住一行，适合高并发 |
| 缓存 | 只缓存索引，不缓存真实数据 | 索引和数据都缓存，对内存要求高 |
| 表空间 | 小 | 大 |
| 关注点 | 性能 | 事务 |
| 默认安装 | 是 | 是 |
MySQL的索引
查询性能下降的可能原因：
- 查询语句写的差
- 索引失效
- 关联查询，表的连接太多
- 服务器调优及各个参数的设置
MySQL读取语句时的顺序：
FROM、ON、WHERE、GROUP BY、HAVING、SELECT、DISTINCT、ORDER BY、LIMIT
索引：帮助MySQL高效获取数据的数据结构，这些数据结构以某种方式指向数据，这样就可以在该数据结构的基础上实现高级算法查找，默认说的索引就是B树索引（聚集索引、次要索引、复合索引、前缀索引、唯一索引等都是B树索引），但还有其他索引，比如哈希索引。缺点就是需要额外的空间保存索引信息，并且每次增、删、改数据的同时还需要修改索引信息，降低了效率
索引分类：
- 单值索引：一个索引包含单个列，一张表可以有多个单值索引
- 唯一索引：索引列的值必须唯一，可以包含空值
- 复合索引：一个索引包含多个列
语法:
- 创建：
create index 索引名 on 表名(属性) - 修改：
alter 表名 add index 索引名 on 表名(属性) - 删除：
drop index 索引名 on 表名 - 查看索引：
show index from 表名
MySQL的索引结构：
B-tree索引、哈希索引、全文索引、R-tree索引
哪些字段适合建立索引：
- 主键自动建立唯一索引
- 频繁作为查询条件的字段适合建立索引
- 查询中与其他表关联的字段，外键关系建立索引
- 查询中统计或分组的字段
哪些字段不适合建立索引：
- 频繁更新的表不适合建立索引
- 出现在where子句中的片段
- 当某列的值重复值极多的情况下不用建立索引
- 表记录太少不适合建立索引
- 高并发下倾向于复合索引
当客户端向MySQL请求一条查询语句时，命令解析器模块完成请求分类，区别出是MySQL
explain性能分析
使用explain关键字可以模拟MySQL的查询优化模块去执行SQL查询语句，从而知道MySQL是如何处理查询语句的。从而可以得出性能瓶颈
作用：通过explain+SQL来实现，执行计划包含的信息
- id： select查询的序列号，表示查询中执行select子句或操作表的顺序 ，id越大越先执行，id相等时从上到下执行
- select_type：查询类型，包括以下几种。
- SIMPLE：简单查询，表示单表查询，查询中不包含子查询或者UNION
- PRIMARY：查询中若包含任何复杂的查询，最外层的查询被标记为该类。
- SUBQUERY：在where中包含的子查询，并且该查询查的是一个值，若是一组值则不是SUBQUERY
- DEPENDENT SUBQUERY：在where中包含的子查询，并且该查询查的是一组值，MySQL5.7取消了该类型
- DERIVED：在from列表中包含的子查询被标记为该类，MySQL会递归的执行这些子查询，将结果放到临时表。
- UNION：若第二个SELECT出现在UNION之后，则被标记为UNION。若UNION出现在from的子查询中，外层的SELECT则被标记为DERIVED
- UNION RESULT：两种表的合并，5.7也没有
- table：这行数据是关于哪张表的
- partitions：查询数据的分区
- type：查询的访问类型。最好到最差排序依次是system > const > eq_ref > ref > range > index > all，一般来说优化要达到range，最好达到ref级别
- system：只有一行记录，是const的特例
- const：针对主键或唯一索引的等值查询扫描, 最多只返回一行数据。 查询速度非常快, 因为它仅仅读取一次即可
- eq_ref：唯一性索引。此类型通常出现在多表的 join 查询, 表示对于前表的每一个结果, 都只能匹配到后表的一行结果. 并且查询的比较操作通常是 =, 查询效率较高。
- ref：非唯一性索引，此类型通常出现在多表的 join 查询, 表示对于前表的每一个结果, 都只能匹配到后表的多个结果，要求后表的该字段具有非唯一性索引。
- range：只检索给定范围的行，一般用在where中的between and、in等操作
- index：sql使用了索引但没有通过索引过滤，一般是索引覆盖或者利用了索引进行了排序分组
- all：全表扫描
- possible_keys：显示可能应用到这张表的索引，一个或多个，查询涉及到的字段上若存在索引则会被列出来，但不一定被实际使用。
- key：实际用的索引
- key_len：表示索引中使用的字节数，越小越好，显示的值为索引字段的最大可能长度，并非实际使用长度。
- ref：显示索引的哪一列被使用了，最好是常量
- rows：表示MySQL执行查询时必须检查的行数，越小越好。
- extra：包含不适合在其他列展示的但重要的额外信息
- using filesort：说明MySQL会对数据使用外部的索引排序，比如对一个没有索引的列使用order by时会出现该信息，给对应字段添加索引就能使用内部索引了。
- using temporary：用临时表保存中间结果，常见于order by 和 group by。只需要给分组字段或排序字段建立索引就能取消该信息。
- using index：效率不错。表示相应的select操作中使用了覆盖索引，比如查的字段为包含索引的字段时会有该信息
- using where：表明使用了where过滤
- using join buffer：连接查询时会出现
- Select tables optimized away：select中使用了在sum或者count函数时会出现
单表索引失效情况
-
最佳左前缀法则：如果使用了复合索引，要遵循该准则，指的是查询从最左前列开始并且不能跳过索引中的列。如果符合索引为（属性1，属性2，属性3），那么过滤条件中出现属性1才能用到属性2，出现属性2才能用到属性3
-
索引列上做计算：不在索引列上做任何操作，比如计算、函数、类型转换等。否则会导致索引失效而转向全表扫描。
-
索引列上进行范围查询：过滤条件中某字段出现范围过滤的话，该字段后边的字段用不到索引，所以建议在建立索引时，将可能出现范围查询的字段放到最后一列
-
覆盖索引：尽量不要写
select *，select后面尽量用索引中的字段 -
使用
!=或者<> -
使用
IS NOT NULL，但是IS NULL可以用到索引 -
使用 like时，将%放到前面不可用，只有
like 'a%'的形式可以用到索引 -
使用or也会使索引失效，尽量使用union或者union all代替
关联查询优化
- 外连接：从表上建立的索引有效，给从表连接字段建立索引
- 内连接：给任意一个表建立索引都可以，优化器会自动将建立索引的表放到后面执行（inner join 没有主从表之分，straight join和 inner join作用相同，但是强制将左表设置为主表）
- 小表驱动大表：将小表放到前面
子查询优化
进行优化时，尽量不使用not null，not in ，not exist等，可以通过连接加条件判断代替
例子：
原SQL：select age,count(*) from emp where id not in （select ceo from dept where ceo is not null） group by age；
优化SQL：select age,count(*) from emp left join dept on emp.id=dept.ceo where dept.id is null group by age；
排序分组优化
- order by若要使用到索引必须使用到过滤条件，可以用where或者是limit来实现过滤。因此排序分组时若没有任何过滤条件也不会用到索引。此处的过滤必须是包含在索引字段内的。group by不用过滤也可以直接使用索引。
- 排序分组语句中有多个索引包含的字段时，这些字段的顺序不能错，否则索引不起作用
- 排序分组语句中有多个索引包含的字段时，这些字段的方向不能反，不能是一个字段升序一个字段降序。否则索引不起作用
慢查询日志
MySQL的日志记录，用来记录执行时间超过long_query_time的查询语句。
- 查看慢查询日志是否开启：
show variables like "%slow_query_log%" - 开启慢查询日志：
set global slow_query_log=1 - 查看超时阈值：
show variables like "%long_query_time%" - 修改超时阈值：
set long_query_time=0.001 - 查看超时的SQL条数：
show status like 'slow_queries'
日志分析工具：MySQL dump slow（Linux命令行使用）
参数介绍：
得到返回记录集最多的10个SQL：mysqldumpslow -s r -t 10 日志文件
得到访问次数最多的10个SQL：mysqldumpslow -s c -t 10 日志文件
MySQL锁机制
从不同的粒度划分，锁可以分为读锁和写锁。
- 读锁：即共享锁，针对同一个数据，多个操作可以同时运行而不会相互影响
- 写锁：即排他锁，在对数据进行操作时，它会阻断其他的写锁和读锁。
还可以分为表锁，行锁，页锁。
-
表锁：偏向MyIsam引擎，在执行查询语句之前会给所涉及的表加读锁，在执行修改操作之前会给所涉及表加写锁。表锁的开销小，加锁快，无死锁，发生锁冲突的概率最高，并发度最低。
例子：比如有两个session在操作一张表。- 加读锁，session1对某个表加了读锁后就只可以读该表不能修改该表，并且也不能读其他表；但是sesssion2可以读所有表，但是如果修改锁住的表时会发生堵塞，当session1将锁删除后刚刚堵塞的操作会立即执行。
- 加写锁，session1对某个表加了写锁后只可以读或者修改该表，并且也不能读其他表；此时，session2如果读取或者更新该表则会发生阻塞，直至session1将写锁删除。
表锁有关SQL语句：
- 查看所有上锁的表：
show open tables； - 上锁的SQL语句：
lock table 表1 read或者write，表2 read或者write - 解锁的SQL语句：
unlock tables - 查看具体的表级锁定信息：
show status like 'table%'包括表级锁锁定的次数，竞争锁的次数
-
行锁：偏向Innodb引擎，开销大，加锁慢，会出现死锁，发生锁冲突的概率低，并发度最高。
例子：比如有两个session在操作一条记录。- 若session1对id为1的记录进行修改后，session2就无法对该记录修改，但是可以修改其他行的记录
行锁有关SQL语句：
- 锁定一行：
sql语句 for update - 产看具体的行锁信息：
show status like 'innodb_row_lock'
注意：
- 行锁变表锁：当某个属性有索引时，若在一定情况下造成了索引失效，那么会引起行锁变表锁，性能极大降低。
- 间隙锁：当用范围条件检索数据，并请求读锁或者写锁时，Innodb会给符合条件的已有数据记录的索引项加锁，对于键值在范围内，但并不存在的记录叫做间隙。比如当session1修改id为1，2，3，4四条记录时，若session2插入id为2的数据时会造成阻塞。因此很容易对性能造成影响
-
页锁：开销和加锁时间介于表锁和行锁直接，会出现死锁并发度一般，了解。
主从复制
所有的读写操作都在一个MySQL服务器上面进行会造成服务器压力很大，那么可以将读写操作分开，将写操作放到主机上面，读操作放到从机上，从机可以有多个。主机会将所有的操作记录到binary log（二进制日志）中。从机会去读取主机的binary log，并将其放到各自的Relay log（中继日志）中，再由中继日志同步数据。
MySQL（HA）高可用
一般有两个或者两个以上的节点，分为活动节点和备用节点。用keepalived软件实现主备替换，搭建略。
699
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
