mysql知识点汇总

#检测是否已安装MySQL
rpm -qa | grep mysql
#普通删除
rpm -e mysql
#强力删除
rpm -e --nodeps mysql

#安装
wget http://repo.mysql.com/mysql-community-release-el7-5.noarch.rpm
rpm -ivh mysql-community-release-el7-5.noarch.rpm
yum update
yum install mysql-community-server

#启动
systemctl start mysqld

systemctl stop mysqld #停止
systemctl restart mysqld #重启
systemctl enable mysqld #设置开机启动
systemctl status mysqld #查看数据库状态
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

MySQL更改数据目录

#更改数据目录属主
chown -R mysql:mysql /home/mysql/

#修改/etc/my.cnf，datadir="/home/mysql"
vi /etc/my.cnf
1
2
3
4
5

数据库常用操作

--创建用户
CREATE USER 'userName' IDENTIFIED BY 'psssword';

--赋权 privileges：all,select,insert,update,delete,create,drop,index,alter,grant,references,reload,shutdown,process,file
GRANT privileges ON dbName.* TO 'userName'@'%' IDENTIFIED BY 'psssword' ;
--刷新权限
flush privileges;
--查看权限
show grants for 'userName'@'%'
1
2
3
4
5
6
7
8
9

MySQL常用存储引擎

1. MyISAM
   不支持事务
   支持表级锁
   存储表的总行数
   一个表有三个文件：表结构文件（.frm），索引文件（.myi），数据文件（.myd）
   采用非聚集索引，索引文件的数据域存储指向数据文件的指针
2. InnoDB
   支持事务
   支持行锁
   不存储表的总行数
   一个表有两个文件：表结构文件（.frm），索引及数据文件（.ibd（独享表空间，每个表一个）或.ibdata（共享表空间所有表一个））
   主键索引采用聚集索引，辅索引的数据区存储主键的值

MySQL索引的数据结构

1. B+Tree
   InnoDB默认使用B+Tree。
2. hash
   =条件查询快，但范围查询效率低

B+Tree在Btree上做了哪些修改

1. 非叶子节点冗余，不保存data，可以保存更多的节点
2. B+Tree的data仅存储在叶子节点，包含了整张表数据
3. B+Tree的的叶子节点有指向下一个节点的指针，提高查询效率

聚集索引和非聚集索引

1. 聚集索引：数据和索引存在一起
2. 非聚集索引：数据和索引不存在一起，索引的数据区存储的是数据的地址

覆盖索引和非覆盖索引

索引对数据库性能的影响
提高查询速度
降低插入、删除、更新的速度，因为在执行写操作时，还要维护索引
索引占用物理空间

什么时候会索引失效

Innerdb主键为什么推荐整型自增

1. 如果不建主键，Innerdb会选择一列不重复的作为主键，如果没有这样的列，InnoDB会创建一个隐藏列作为主键，这样会损耗数据库性能
2. 整型相比字符串，可以存更多数据
3. 整型数据在查询索引时，提高了比较性能
4. 自增使数据在写入时是顺序的，直接在末尾追加。否则，可能会引起页分裂再平衡影响性能

MVCC多版本并发控制

事务的四大特性
一致性，原子性，隔离性，持久化

事务的原子性是如何保证的

事务的隔离级别

1. 读未提交
2. 读已提交
3. 可重复读
4. 可串行化

锁
基于锁的属性：共享锁，排他锁
基于锁的粒度：行锁，表锁，页锁，记录锁，间隙锁，临键锁
根据锁的状态：意向共享锁，意向排他锁
共享锁：共享锁又称读锁（S锁），数据加了读锁后可以继续加读锁，但不能加写锁
排他锁：排他锁又称写作（X锁），数据加了排他锁后，不能再加任何锁

记录锁：是行锁的一种，但只锁住一行。命中的条件字段必须是唯一索引
行锁：锁住的是表的一行或多行，其他事务不能访问锁住的行，但可以访问其他行
间隙锁：是行锁的一种，锁住的是一个区间，当表的相邻ID间存在空隙就会形成一个区间，遵循左开右闭原则。只会出现在可重复读的事务级别中，防止幻读
临键锁：是行锁的一种，是InnoDB的行锁默认算法，是记录锁和间隙锁的结合，会把查询出来的记录和该范围内的间隙，以及相邻的下一个区间都锁住。可以避免脏读，重复读，幻读。必须命中索引，并且是范围查询才会触发
页锁：锁住的是页
表锁：锁住的是整个表，其他事务不能访问该表

意向共享锁：
意向排他锁：

乐观锁和悲观锁

mysql的执行计划怎么看
使用explain

1. id：查询的顺序号，有几个select就对应几个id。id越大越先执行
2. selectType：查询类型
   - SIMPLE：表示不包含UNION和自查询
   - PRIMARY：表示最外层的查询（包含子查询）
   - SUBQUERY：子查询的字一个SELECT
   - UNION：UNION的第二或随后的查询
   - DEPENDENT UNION：子查询的UNION的第二或随后的查询
   - UNION RESULT：UNION的结果
   - DEPENDENT SUBQUERY：子查询中的第一个SELECT
   - DERIVED：位于from语句的子查询
3. table：表示该语句查询的表
4. type：查询的类型
   - const：索引一次命中，返回的数据至多一条
   - system：表中只有一条数据，返回的数据至多一条
   - eq_ref：唯一性索引扫描，返回的数据至多一条
   - ref：非唯一性索引扫描
   - rang：检索索引的给定范围，
   - index：
   - ALL：
5. possible_keys：
6. key：
7. key_len：
8. rows：
9. filtered：
10. extra：

数据库优化

主从同步过程
mysql的主从复制有3个线程：master（binlog dump thread）、slave（IO thread、SQL thread）

1. master记录所有变更记录到binlog。binlog是数据库启动后，保存的所有修改数据库结构和内功的文件
2. master的log dump thread在binlog有变动时，读取其position偏移量后的内容并发送给slave
3. slave的IO thread接收position和binlog内容，保存position，并将其写入relay log文件
4. slave的SQL thread读取relay log文件，并执行

分库分表

1. 什么时候分库分表
   单表数据超过500W或者2GB（可预估3年的数据量）。
2. 常用分库分表策略
   垂直分库分表、水平分库分表