MySql 浅析
0-MySql 面试题
数据库 Sql 优化
数据库 Sql 优化的核心在于对于查询语句，因此，对于其最佳的思路，就从查询语句结构下手即可。
SELECT * | [字段 1,字段 2,...,字段 N]
FROM 表 1
[,LEFT JOIN 表 2 ON 条件]
[,RIGHT JOIN 表 2 ON 条件]
[,INNER JOIN 表 2 ON 条件]
[WHERE 条件]
[GROUP BY 字段 1,字段 2,...,字段 N]
[HAVING 字段 1,字段 2,...,字段 N]
[ORDER BY 字段 1,字段 2,...,字段 N]
[UNION [ALL]
相似查询条件
]
1、不要使用*，应当指明查询的数据列；
2、增加查询条件，即使用 WHERE，避免直接查询所有数据；
3、联合查询 JOIN 代替子查询
4、使用 UNION 代替创建临时表，UNION 和 UNION ALL 最直观的区别在于是否去重
5、建立数据列索引，增加查询效率，避免全表扫描，但不是索引越多越好；
数据列建立索引，数据中应当不要保存有 NULL，否则，会导致索引失效，从而全部查询。
使用索引，注意以下条件会导致全表查询，也即索引失效：
1）WHERE 子句进行索引字段表达式操作，包含+、-、*、/、!等操作符
2）使用不等于操作符!=
3）OR，两边应当都是索引数据列时索引才能起作用
4）IN 和 NOT IN，这还取决于何种索引类型
5）左模糊查询'%...'
6）字符型字段没有使用单引号
7）当变量采用的是 times 变量，而表的字段采用的是 date 变量时，或相反情况
8）B-tree 索引 is null 不会走，is not null 会走，位图索引 is null，is not null 都会走
9）单独引用复合索引里非第一位置的索引列
10）联合索引 is not null，只要在建立的索引列（不分先后）都会走，is null 时必须要和建立索引第一列一起使用。当建立索引第一位置条件是 is null 时，其他建立索引的列可以是 is null（但必须在所有列 都满足 is null 的时候），或者=一个值；当建立索引的第一位置是=一个值时，其他索引列可以是任何情况（包括 is null =一个值），以上两种情况索引都会走，其他情况不会走。
6、ORDER BY 不会使用索引进行
7、可以使用 EXPLAIN 对于 SQL 进行分析解释，从而实现最佳查询效果
1-MySql 安装
由于安装的是 mysql8.0 版本，与之前的 mysql5.7 不同，故不能按照以往那样先安装一堆软件
cmake 为什么安装的是
./bootstrap
而不是./configure
更新 mysql8.0 安装
这里下载的是 tar.xz 文件
需要添加用户组
添加用户组：mysql、用户：mysql，并将其添加到 mysql 用户组中
groupadd mysql
useradd -r -g mysql mysql
mysql 安装需要使用 libiao，则需要判断是否安装
rpm -qa | grep libiao
没有的话，可以使用
yum search libaio
然后安装
yum -y install libaio
然后，在/etc/目录下配置 mysql.cnf
内容如下
[mysql]
# 设置 mysql 客户端默认字符串
default-character-set=utf8
socket=/tmp/mysql.sock
[mysqld]
#skip-name-resolve
#设置 3306 端口
port=3306
socket=/tmp/mysql.sock
skip-grant-tables
#设置 mysql 安装目录
basedir=/usr/local/mysql
#设置 mysql 数据库的数据存放目录
datadir=/usr/local/mysql/data
#允许最大连接数
max_connections=200
#服务端使用的字符集默认为 8 比特编码的 latin1 字符集
character-set-server=utf8
#创建新表时使用的默认存储引擎
default-storage-engine=INNODB
#lower_case_table_name=1
max_allowed_packet=16M
default-authentication-plugin=mysql_native_password
初始化 mysqld 生成初始化密码
--initialize --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data
设置开机启动
复制 mysql.server 脚本到资源目录，并赋予执行权限：
cp ./support-files/mysql.server /etc/rc.d/init.d/mysqld
chmod +x /etc/rc.d/init.d/mysqld
将 mysqld 服务加入到系统服务并检测是否生效：
chkconfig --add mysqld
chkconfig --list mysqld
因为 my.cnf 中加了 skip-grant-tables，所以为无密码登录
2-存储引擎
一、存储引擎
二、存储引擎分类
SHOW ENGINES;
3-索引
一、索引
二、存储引擎与索引
三、B-Tree
根节点（Root Node）：一个 B 树只有一个根节点，位于树的最顶端的节点；
分支节点（Branch Node）：包含的指针指向其他的分支节点或者叶子节点；
叶子节点（Leaf Node）：指针指向为 NULL 的节点；
四、B+Tree
带有顺序访问指针的 B+Tree
五、MyISAM 的 B+Tree 索引
主索引（Primary Key）：主索引的 key 是唯一的
辅助索引（Secondary Key）：辅助索引的 key 可以重复
为了与 InnoDB 的聚集索引相区分，MyISAM 索引方式叫做“非聚集索引”。
六、InnoDB 的 B+Tree 索引
主索引
辅助索引
MyISAM 与 InnoDB 的比较
七、Memory 的 Hash 索引
4-高性能索引策略
一、高性能索引策略
联合索引
SHOW INDEX FROM 表
二、索引选择性
Index Selectivity = Cardinality / #T
三、InnoDB 的主键选择与插入优化
自增主键
非自增主键
5-事务
数据库事务
数据库事务(Database Transaction) ，是指作为单个逻辑工作单元执行的一组数据库操作，要么完全地执行，要么完全地不执行。 MySQL 的事务支持不是绑定在 MySQL 服务器本身，而是与存储引擎相关。
1、MyISAM：不支持事务，用于只读程序提高性能；
2、InnoDB：支持 ACID 事务，行级锁，并发处理；
BEGIN、COMMIT、ROLLBACK
MYSQL 的事务处理主要有两种方法
1、使用 begin、rollback、commit 实现当前连接下的事务
begin 开始一个事务
rollback 事务回滚
commit 事务确认
2、使用 set 改变 mysql 的自动提交模式
mysql 默认是自动提交模式，也就是你提交一个 query，就直接执行！可以通过修改 autocommit 值实现事务处理。
set autocommit = 0 禁止自动提交
set autocommit = 1 开启自动提交
但要注意当用 set autocommit = 0 的时候，以后所有的 sql 都将作为事务处理，直到用 commit 确认或 rollback 结束，注意当结束这个事务的同时也开启了一个新的事务。
事务四大特征
(1)原子性 Atomicity
事务必须是原子工作单元；对于其数据修改，要么全都执行，要么全都不执行。
(2)一致性 Consistency
一致性指的是在一个事务执行之前和执行之后数据库都必须处于一致性状态。事务执行的结果必须是使数据库从一个一致性状态变到另一个一致性状态。
(3) 隔离性 Isolation（关于事务的隔离性数据库提供了多种隔离级别）
一个事务的执行不能干扰其它事务，即一个事务内部的操作及使用的数据对其它并发事务是隔离的，并发执行的各个事务之间不能互相干扰。
(4)持久性 Durability
事务完成之后，它对于数据库中的数据改变是永久性的，该修改即使出现系统故障也将一直保持。
事务并发
脏读
一个事务在处理的过程中读取了另一个事务未提交但修改的数据；
举例如下。
不可重复读
一个事务在不同时间读取数据不一致，也可以理解为读取了另一条已经提交的事务数据，不可重复读重点在于 update 和 delete。
举例如下。
幻读
幻读和不可重复读都是读取了另一条已经提交的事务，幻读的重点在于 insert。
举例如下：
更新丢失
两个事务对同一数据进行更新，后者会覆盖先者的更新。
举例如下。
MySql 隔离级别
事务的隔离级别就是为了针对并发出现的问题，不同的级别可以保证不同的一致性，在 MySQL 数据库中默认的隔离级别为 Repeatable read (可重复读)
现在来看看 MySQL 数据库为我们提供的四种隔离级别。
在可重复读中，该 sql 第一次读取到数据后，就将这些数据加锁，其它事务无法修改这些数据，就可以实现可重复读了。但这种方法却无法锁住 insert 的数据，所以当事务 A 先前读取了数据，或者修改了全部数据，事务 B 还是可以 insert 数据提交，这时事务 A 就会发现莫名其妙多了一条之前没有的数据，这就是幻读，不能通过行锁来避免。需要 Serializable 隔离级别，读用读锁，写用写锁，读锁和写锁互斥，这么做可以有效的避免幻读、不可重复读、脏读等问题，但会极大的降低数据库的并发能力。
乐观锁和悲观锁
乐观锁（Optimistic Locking）相对悲观锁而言，乐观锁假设认为数据一般情况下不会造成冲突，所以在数据进行提交更新的时候，才会正式对数据的冲突与否进行检测，如果发现冲突了，则返回用户错误的信息，让用户决定如何去做。
相对于悲观锁，在对数据库进行处理的时候，乐观锁并不会使用数据库提供的锁机制。一般的实现乐观锁的方式就是记录数据版本。
悲观锁，正如其名，它指的是对数据被外界（包括本系统当前的其他事务，以及来自外部系统的事务处理）修改持保守态度(悲观)，因此，在整个数据处理过程中，将数据处于锁定状态。 悲观锁的实现，往往依靠数据库提供的锁机制（也只有数据库层提供的锁机制才能真正保证数据访问的排他性，否则，即使在本系统中实现了加锁机制，也无法保证外部系统不会修改数据）
6-MySql 锁机制
MySql 锁机制
MySql 存储引擎主要使用三种类型的锁定机制：行级锁、页级锁和表级锁。
在 MySQL 数据库中，使用表级锁定的主要是 MyISAM、Memory、CSV 等一些非事务性存储引擎，而使用行级锁定的主要是 Innodb 存储引擎和 NDBCluster 存储引擎，页级锁定主要是 BerkeleyDB 存储引擎的锁定方式。
行级锁
行级锁定是目前各大数据库管理软件所实现的锁定颗粒度最小的，所以发生锁定资源争用的概率也最小，能够给予应用程序尽可能大的并发处理能力而提高一些需要高并发应用系统的整体性能。
但是由于锁定资源的颗粒度很小，所以每次获取锁和释放锁消耗的资源也更多，带来的消耗自然也就更大了。此外，行级锁定也最容易发生死锁。
Innodb 锁定模式及其实现机制
Innodb 可以说分为共享锁、排他锁、意向共享锁和意向排他锁。Innodb 的行级锁定同样分为两种类型，共享锁和排他锁，而在锁定机制的实现过程中为了让行级锁定和表级锁定共存，Innodb 也同样使用了意向锁（表级锁定）的概念，也就有了意向共享锁和意向排他锁这两种。
当对某个资源加锁时，如果
有共享锁，可以再加一个共享锁，不过不能加排他锁；
有排它锁，就在表上添加意向共享锁或意向排他锁；
有意向共享锁，可以同时并存多个意向共享锁；
有意向排他锁，则只能有一个存在。
表级锁
表级别的锁定是 MySQL 各存储引擎中最大颗粒度的锁定机制。该锁定机制最大的特点是实现逻辑简单，带来的系统负面影响最小。所以获取锁和释放锁的速度很快。由于表级锁一次会将整个表锁定，所以可以很好的避免死锁问题。
当然，锁定颗粒度大所带来最大的负面影响就是出现锁定资源争用的概率也会最高，支持并发度较低。
MySQL 的表级锁定主要分为两种类型，一种是读锁定，另一种是写锁定。
在 MySQL 中，主要通过四个队列来维护这两种锁定：两个存放当前正在锁定中的读和写锁定信息，另外两个存放等待中的读写锁定信息，如下：
Current read-lock queue (lock->read) 持有读锁的所有线程
Pending read-lock queue (lock->read_wait) 等待读锁的所有线程
Current write-lock queue (lock->write) 持有写锁的所有线程
Pending write-lock queue (lock->write_wait) 等待写锁的所有线程
当前持有读锁的所有线程的相关信息都能够在 Currentread-lockqueue 中找到，队列中的信息按照获取到锁的时间依序存放。而正在等待锁定资源的信息则存放在 Pendingread-lockqueue 里面，另外两个存放写锁信息的队列也按照上面相同规则来存放信息。
读锁获取
一个新的客户端请求在申请获取读锁定资源的时候，需要满足两个条件：
资源没有被写锁定
写锁定等待队列（Pendingwrite-lockqueue）中没有更高优先级的写锁定等待
如果满足了上面两个条件之后，该请求会被立即通过，并将相关的信息存入 Currentread-lockqueue 中，否则会被迫进入等待队列 Pendingread-lockqueue 中等待资源的释放。
写锁获取
一个新的客户端请求在申请获取写锁定资源的时候，被申请资源需要满足两个条件：
没被写锁定 Currentwrite-lockqueue
没被写锁定等待(Pendingwrite-lockqueue)
没被读锁定 Currentread-lockqueue
页级锁
页级锁定的特点是锁定颗粒度介于行级锁定与表级锁之间，所以获取锁定所需要的资源开销，以及所能提供的并发处理能力也同样是介于上面二者之间。另外，页级锁定和行级锁定一样，会发生死锁。
7-主从配置
对于服务器架构而言，最初的阶段，使用一台数据库主机可以达到很好的访问处理，但是，随着大数据时代的来临，单节点的数据库已经无法支撑高并发量的请求，这个原理很好理解，就好像一个仓库，而要进入仓库获取资源，那就需要通过仓库的大门。当请求的人较少时，可以快速进出，但是，当请求的人大量时，就会容易造成通道杜塞，有着大量的人等待。对于数据库而言，也是这样的道理。
单节点的数据库已不能满足要求，那么，可以增加数据库的数量。方案可以使用集群，或者主从模式。
主从模式
主服务负责更新操作，从服务服务查询操作，用户可以根据功能模块访问从服务器。主从服务器通过 MySql 的二进制文件，进行数据的同步，二进制由主服务器产生，从服务器相应获取同步数据库。
主服务器
一、主服务器创建从服务器能够访问的用户和密码
create user 'test173'@'192.168.199.173' identified by 'test173';
二、为从服务器设置权限，例如复制，然后刷新权限
grant replication slave on *.* to 'test173'@'192.168.199.173';
flush privileges;
三、查看主服务的日志文件名称和路径
show master status
四、从服务器设置访问主服务器信息
change master to master_host = '192.168.199.228',master_user='test173',master_password='test173',master_log_file='mysql-bin.000002',master_log_pos=1969;
从服务器
一、启动从服务器
start SLAVE
二、查看从服务器信息
show slave status
这里需要注意，一开始从服务器数据为空，然后，将主服务器中的所有数据写出，发送给从服务器，让从服务器同步主服务器，最后，再启动从服务器，开始主从复制模式。
主服务器
$ mysqldump -u root -p --all-databases --master-data > all.sql
$ scp all.sql root@192.168.78.130:/tmp
从服务器
$ mysql –u root -p
8-处理高并发，防止库存超卖
对于访问量激增，上千上万甚至更加巨大的用户量进行同一时间同一资源争夺的问题，如何保证数据的一致性，如何保证合理存在于一定范围内。这样类型的问题，在实际生活中比比皆是。诸如淘宝、京东等等电商平台推出的团购、秒杀、特价之类的活动，12306 春运期间购买火车票的情况。如何控制，这是一个关键，既保证数据是一致性的，又保证不会超卖。
传统做法
基于事务，先查询进行判断。然后，判断符合条件，进行数据更新操作。
分析缘由
以 Java 作为开发语言进行分析。对于事务，由于 Spring 已经提供了很好的事务支持，故程序中并没有体现事务开始 begin，事务提交 commit 和事务回滚 rollback 等操作。
select count from product where id = 123;
获取个数，进行判断，如果 count > 0，则说明还有库存量，然后便进行数据库修改。
update product set count = count - buynum where id - 123;
单个用户访问，不会出现问题，但是，在高并发的情况下，就会造成修改 count 为负数的情况，比如三个用户 a，购买数 1，用户 b，购买数 2，用户 c，购买数 3，而 count 数则是 4。
查询语句，对于 MySql 数据库而言，此时产生一个共享锁，多个用户获取到的 count 数据都是 4。而当进行更新操作时，此时产生了排他锁，多个线程会顺序执行，故最终造成更新的数据变为负数。
正确做法
方式一
先修改，再重新进行查询判断。
update product set count = count - buynum where id = 123;
select count from product where id = 123;
方式二
直接进行更新操作，限制条件中进行个数判断
update product set count = count - buynum = where count >= buynum and id = 123;
扩展
1、直接操作数据库，会容易造成数据库的压力，可以使用线程池进行数据库连接的限制。
通过设置数据库连接池的个数，进行访问的控制，避免请求过多造成数据库压力。
2、不能大并发量的直接访问数据库，也可以使用缓存进行处理。
可以利用 memcached 或者 redis 实现计数器，控制数据量。
还未添加个人签名 2019-11-21 加入
还未添加个人简介
评论