索引

<div id="music163player">
              <iframe frameborder="no" border="0" marginwidth="0" marginheight="0" width=330 height=86 src="//music.163.com/outchain/player?type=2&id=5271400&auto=0&height=66"></iframe>
          </div>

索引及执行计划

5.1 索引

1.作用

1.优化查询
select 查询有三种情况：
    缓存查询：不在mysql中查询数据，速度最快，需要配合其他产品
    全表扫描：
    索引扫描

5.2 索引的种类（mysql中的一种对象）

Btree（Btree B+tree B*tree）
Rtree
Hash
fulltext

btree

b+tree

b*tree

5.3 Btree分类

磁盘叶存储

1.聚集索引：基于主键，自动生成的，一般建表时创建主键，如果没有主键，自动选择唯一键作为聚集索引

1.会影响数据排列
2.叶子节点就是主键对应的数据行，按照顺序排列
3.避免随机io

为什么用id做索引

每个数据页大小是一定的16kb，当数据量越大，当一个数据页装不下时，就会增加枝节点，从而增加B树的层级，影响查询效率。
eg：
id 短，数据有限，所以树高低，快
name 长，数据有限，所以树高，慢

2.辅助索引：人为创建的（普通，覆盖），只存索引键的值

查2此相邻的会快些

先通过辅助索引，在叶子节点找到辅助索引的值，以及对应的主键
再通过主键到聚集索引的B树，查出详细数据

覆盖索引：没有回表查询，多个辅助索引

注意：
如果判断区间：只能全表扫描，

5.4 唯一索引：人为创建（普通索引）

得保证列中的值没有重复

5.5 聚集索引和辅助索引的区别

1.聚集索引：叶子节点，按照主键列的顺序，存 是真正的数据页，
	数据页，16kb，（datapage）

2.辅助索引：叶子节点，列值排序过后，存储到叶子节点 + 对应的主键的值  有回表的操作

5.6 索引管理的命令

索引键（key），表中的某一个列

1.普通的辅助索引：mul

创建索引：
alter table blog add key idx_email(email); --- mul 普通的辅助索引
create index idx_phone on blog_userinfo(phone);

删除索引：
查看索引
show index from blog_userinfo;

删除
alter table blog_userinfo drop index idx_email;
drop index idx_phone on blog_userinfo;


前缀索引：截取数据量大的列前多少个，如果比较前10个字符，就可以了，就没必要存所有列数据;将来用的可能多点
alter table blog_userinfo add index idx(password(10));
MySQL 前缀索引能有效减小索引文件的大小，提高索引的速度。但是前缀索引也有它的坏处：MySQL 不能在 ORDER BY 或 GROUP BY 中使用前缀索引，也不能把它们用作覆盖索引(Covering Index)。
下面总结一下什么情况下使用前缀索引：

字符串列(varchar,char,text等)，需要进行全字段匹配或者前匹配。也就是=‘xxx’ 或者 like ‘xxx%’

字符串本身可能比较长，而且前几个字符就开始不相同。比如我们对中国人的姓名使用前缀索引就没啥意义，因为中国人名字都很短，另外对收件地址使用前缀索引也不是很实用，因为一方面收件地址一般都是以XX省开头，也就是说前几个字符都是差不多的，而且收件地址进行检索一般都是like ’%xxx%’，不会用到前匹配。相反对外国人的姓名可以使用前缀索引，因为其字符较长，而且前几个字符的选择性比较高。同样电子邮件也是一个可以使用前缀索引的字段。
前一半字符的索引选择性就已经接近于全字段的索引选择性。如果整个字段的长度为20，索引选择性为0.9，而我们对前10个字符建立前缀索引其选择性也只有0.5，那么我们需要继续加大前缀字符的长度，但是这个时候前缀索引的优势已经不明显，没有太大的建前缀索引的必要了。

2.唯一索引：人为创建（普通索引 uni

alter table blog_userinfo add unique key  uni_email(email);

3.覆盖索引：（联合索引）

作用

作用：不用回表查询，不需要聚集索引，所有查询的数据都从辅助索引中获取
其实就是多个列建立索引

语法：
alter table t1 add index idx_gam(gender,age,money);

bu不要查*,否则覆盖索引无效

where查的最多的放前面

实例

				
假如相亲网站：
男找女：gender, age, money分析得出这3个查询频次最高
select * from where gender, age, money
where a b c
where a b

alter table t1 add index idx_gam(gender,age,money); 使用全列

where作为第一条件列，必须符合既定的规则

情况一：不走覆盖索引
where b c a
where c a b 
where c 
where b

情况二：尽量查询按照顺序去查询，减少回表查询的几率
a
ab
abc 

情况三：走部分覆盖索引,a走覆盖索引，c/b 不走覆盖索引
ac
acb

5.7 索引维护应该注意的地方

大批量插入会锁表

1.业务繁忙时可能会出现锁表现象
2.尽量放在业务不繁忙的时候，比如夜里

注意：实际工作中索引去掉过后，一定要在繁忙时间之前，将索引加上

5.8 如何解决索引导致的插入数据效率低的问题

1.产生的原因

因为会影响现有的B树的结构，所以插入效率低

2.解决方式

1.降低隔离级别。
2.业务不繁忙的时候，比如夜里操作。

关闭索引功能

5.9 索引的优化

查询优化神器: explain

1.索引的合理创建
2.sql的语句的优化

注意: 索引的最高深的优化

改写sql语句/优化索引

6.0 面试可以怎么写

精通索引的基础优化

---

6. mysql 5.7 初始化配置

6.1 数据库的初始化

mysqld --initialize-insecure  --user=mysql --datadir=/data/3307/data --basedir=/opt/mysql

6.2 配置文件的作用

1.影响mysqld的启动
2.影响客户端连接
3.影响到初始化

6.3 如何书写配置文件

1.服务端

[mysqld] ---标签 [server] [mysqld_safe]

必须加的参数(基础的配置信息)：
    [mysqld]
    basedir = /opt/mysql
    datadir = /opt/mysql/data
    user = mysql
    socket = /tmp/mysql.sock
    port = 3306
    server_id = 6

2.客户端

[client] [mysql] [mysqldump] .....标签

必须加的参数(基础的配置信息)：
[mysql]
socket = /tmp/mysql.sock  ----应该和服务端配置一样  省略 -S

6.4 一台机器多个实例的架构（3307,3308,3309）

1.创建相关目录

mkdir -p /data/330{7...9}/data

2.创建每个实例配置文件

vim /data/3307/my.cnf

[mysqld]
basedir = /opt/mysql
datadir = /data/3307/data
user = mysql
socket = /data/3307/mysql.sock
port = 3307
server_id = 3307

3.拷贝

1.拷贝
cp /data/3307/my.cnf /data/3308
cp /data/3307/my.cnf /data/3309

2.修改里面不同的地方
sed -i 's#3307#3308#g' /data/3308/my.cnf 
sed -i 's#3307#3309#g' /data/3309/my.cnf

4.初始化数据

mysqld --initialize-insecure  --user=mysql --datadir=/data/3307/data --basedir=/opt/mysql

mysqld --initialize-insecure  --user=mysql --datadir=/data/3308/data --basedir=/opt/mysql

mysqld --initialize-insecure  --user=mysql --datadir=/data/3309/data --basedir=/opt/mysql

5.启动多实例

1.先授权
chown -R mysql.mysql /data/*

mysqld_safe --defaults-file=/data/3307/my.cnf &
mysqld_safe --defaults-file=/data/3308/my.cnf &
mysqld_safe --defaults-file=/data/3309/my.cnf &

6.验证：查询一下

netstat -lnp|grep 330

7.如何连接多实例(本地方式）

mysql -S /data/3307/mysql.sock
mysql -S /data/3308/mysql.sock
mysql -S /data/3309/mysql.sock

8.关闭实例

原始方式
mysqladmin -S /data/3307/mysql stop

9.systemd管理多实例，生成启动脚本

cat >> /etc/systemd/system/mysqld3307.service <<EOF
[Unit]
Description=MySQL Server
Documentation=man:mysqld(8)
Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html
After=network.target
After=syslog.target
[Install]
WantedBy=multi-user.target
[Service]
User=mysql
Group=mysql
ExecStart=/opt/mysql/bin/mysqld --defaults-file=/data/3307/my.cnf
LimitNOFILE = 5000
EOF

cp  /etc/systemd/system/mysqld3307.service   /etc/systemd/system/mysqld3308.service 
cp  /etc/systemd/system/mysqld3307.service   /etc/systemd/system/mysqld3309.service 

sed -i 's#3307#3308#g'   /etc/systemd/system/mysqld3308.service
sed -i 's#3307#3309#g'   /etc/systemd/system/mysqld3309.service

10.开机自启动

systemctl enable  mysqld3307
systemctl enable  mysqld3308
systemctl enable  mysqld3309

6.5 隐私相关的操作

注意: 保护公司隐私：不要暴露关键信息

1.设置密码

mysqladmin -uroot -p password 123

2.查看数据库已有的用户信息

select user,authentication_string,host from mysql.user;

6.6 忘记密码如何操作

1.先关闭数据库

/etc/init.d/mysqld stop

2.关闭连接层:skip-networking关闭远程连接

mysqld_safe --skip-grant-tables --skip-networking  &

3.修改用户表密码

update mysql.user set authentication_string=PASSWORD('456') where user='root' and host='localhost';

4.重启数据库

/etc/init.d/mysqld restart

---

7.判断优化器是否选择索引

7.1 explain（desc）命令的应用，

获取优化器选择后的执行计划

7.2 使用

explain/desc + 查询语句

7.3 信息详解

+----+-------------+----------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table    | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+----------+------------+-------+---------------+---------+---------+-------+------+----------+-------+

---

type:作用

作用 ：1.能够判断是全表扫描还是索引扫描（all就是全表扫描，其他就是索引扫描）
	  2.对于索引扫描来讲，又可以细化，可以判断是哪一种类的索引扫描

type:具体类型

1. all：全表扫描，理论上应该避免全表扫描，具体要看数据量
	select * from city;

2. index：全索引扫描，即使走了索引，效率也不是特别高
例子：
	desc select countrycode from city

3. range:索引范围扫描
eg：
	desc select * from city where id>2000;

"""
where > < >= <=   -----   B+tree 可以享受额外的优化的
in or between and    -----不是相邻的叶子节点
like 'CH%'
"""

in 或者 or 转化成 union
select * from city where countrycode='CHN'
union all ----不去重
select * from city where countrycode='USA'

4. ref：辅助索引的等值查询

5. eq_ref:多表连接查询(join on )  on后面条件列，达到主键和唯一键的情况下才会出现

6. const,system:主键或者唯一键的等值查询 

7. NULL：找不到数据，性能最好，没啥意义

1.表结构
mysql> desc userinfo;
+-------+-------------+------+-----+------------+----------------+
| Field | Type        | Null | Key | Default    | Extra          |
+-------+-------------+------+-----+------------+----------------+
| id    | int(11)     | NO   | PRI | NULL       | auto_increment |
| name  | varchar(32) | NO   | MUL | NULL       |                |
| phone | varchar(32) | NO   |     | 1111111111 |                |
+-------+-------------+------+-----+------------+----------------+
3 rows in set (0.34 sec)

2.辅助索引的全索引扫描
mysql> desc select name from userinfo;
+----+-------------+----------+------------+-------+---------------+----------+---------+------+------+----------+-------------+
| id | select_type | table    | partitions | type  | possible_keys | key      | key_len | ref  | rows | filtered | Extra       |
+----+-------------+----------+------------+-------+---------------+----------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | userinfo | NULL       | index | NULL          | idx_name | 34      | NULL |    3 |   100.00 | Using index |
+----+-------------+----------+------------+-------+---------------+----------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)


3.主键索引的全索引扫描
mysql> desc select id from userinfo;
+----+-------------+----------+------------+-------+---------------+----------+---------+------+------+----------+-------------+
| id | select_type | table    | partitions | type  | possible_keys | key      | key_len | ref  | rows | filtered | Extra       |
+----+-------------+----------+------------+-------+---------------+----------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | userinfo | NULL       | index | NULL          | idx_name | 34      | NULL |    3 |   100.00 | Using index |
+----+-------------+----------+------------+-------+---------------+----------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

4.非索引键的全表扫描
mysql> desc select phone from userinfo;
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table    | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | userinfo | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    3 |   100.00 | NULL  |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)


5.主键的范围索引扫描
mysql> desc select phone from userinfo where id>1;
+----+-------------+----------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table    | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+----------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | userinfo | NULL       | range | PRIMARY       | PRIMARY | 4       | NULL |    2 |   100.00 | Using where |
+----+-------------+----------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

注意：至少是range以上的级别，才能算的上优化

---

Extra

注意：filesort 会增加cpu压力

优化方案

orderby/  group by  /distinct 等会需要排序
排序列建立合理的索引

解决方法：
结合前面where条件一起构建联合索引

eg：
mysql> desc select * from userinfo order by phone;
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+----------------+
| id | select_type | table    | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra          |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+----------------+
|  1 | SIMPLE      | userinfo | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    2 |   100.00 | Using filesort |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+----------------+

注意：
主键排序是index扫描

---

其他字段意义

possible_keys：可能走的索引

key: 具体用的索引名

key_len:索引列的长度

rows:结果集的行数

---

7.4 什么是业务

1. 产品的功能
2. 用户的行为

---

8.建立索引的原则（运维规范）

8.1 建表时一定要有主键,如果相关列可以作为主键,

做一个无关列

8.2 选择唯一性索引

唯一性索引的值是唯一的，可以更快速的通过该索引来确定某条记录。

例如，学生表中学号是具有唯一性的字段。为该字段建立唯一性索引可以很快的确定某个学生的信息。
如果使用姓名的话，可能存在同名现象，从而降低查询速度。

注意

主键索引和唯一键索引，在查询中使用是效率最高的。

select count(*) from world.city;
select count(distinct countrycode) from world.city;
select count(distinct countrycode,population ) from world.city;
 
注意：如果重复值较多，可以考虑采用联合索引

8.3 为经常需要排序、分组和联合操作的字段建立索引

经常需要ORDER BY、GROUP BY,join on等操作的字段，排序操作会浪费很多时间。
如果为其建立索引，可以有效地避免排序操作。

8.4 为常作为where查询条件的字段建立索引

如果某个字段经常用来做查询条件，那么该字段的查询速度会影响整个表的查询速度。因此，
为这样的字段建立索引，可以提高整个表的查询速度。
    1. 经常查询
    2. 列值的重复值少(业务层面调整)

注：如果经常作为条件的列，重复值特别多，可以建立联合索引。

8.5 尽量使用前缀来索引

如果索引字段的值很长，最好使用值的前缀来索引。例如，TEXT和BLOG类型的字段，进行全文检索
会很浪费时间。如果只检索字段的前面的若干个字符，这样可以提高检索速度。

---

8.6 限制索引的数目

索引的数目不是越多越好。每个索引都需要占用磁盘空间，索引越多，需要的磁盘空间就越大。
修改表时，对索引的重构和更新很麻烦。越多的索引，会使更新表变得很浪费时间。

8.7 删除不再使用或者很少使用的索引(percona toolkit)

表中的数据被大量更新，或者数据的使用方式被改变后，原有的一些索引可能不再需要。数据库管理
员应当定期找出这些索引，将它们删除，从而减少索引对更新操作的影响。

8.8 大表加索引,要在业务不繁忙期间操作

---

9. 不走索引的情况（开发规范）

9.1 没有查询条件，或者查询条件没有建立索引

select * from tab;   全表扫描。
select  * from tab where 1=1;

在业务数据库中，特别是数据量比较大的表。
是没有全表扫描这种需求。

1、对用户查看是非常痛苦的。
2、对服务器来讲毁灭性的。

实例一：
select * from tab;

SQL改写成以下语句：
selec  * from tab  order by  price  limit 10      # 需要在price列上建立索引


实例二：
select  * from  tab where name='zhangsan'          # name列没有索引

改：
	1、换成有索引的列作为查询条件
	2、将name列建立索引

9.2 查询结果集是原表中的大部分数据，应该是25％以上。

查询的结果集，超过了总数行数25%，优化器觉得就没有必要走索引了。

假如：tab表 id，name    id:1-100w  ，id列有索引

select * from tab  where id>500000;

如果业务允许，可以使用limit控制。

怎么改写 ？
结合业务判断，有没有更好的方式。如果没有更好的改写方案
尽量不要在mysql存放这个数据了。放到redis里面。

9.3 索引本身失效，统计数据不真实

索引有自我维护的能力。
对于表内容变化比较频繁的情况下，有可能会出现索引失效。

9.4 查询条件使用函数在索引列上，或者对索引列进行运算，运算包括(+，-，*，/，! 等)

例子： 
错误的例子：select * from test where id-1=9; 
正确的例子：select * from test where id=10;

算术运算
函数运算
desc select  *   from blog_userinfo  where DATE_FORMAT(last_login,'%Y-%m-%d') >= '2019-01-01';

子查询:不走索引

9.5 隐式转换导致索引失效.这一点应当引起重视.也是开发中经常会犯的错误.

比如 telnum这个字段是varcher类型
select * from t1 where telnum=110; # 不走索引的
select * from t1 where telnum=110; # 走索引

9.6 <> ，not in 不走索引

9.7 like “%_” 百分号在最前面不走

EXPLAIN SELECT * FROM teltab WHERE telnum LIKE '31%'   # 走range索引扫描

EXPLAIN SELECT * FROM teltab WHERE telnum LIKE '%110'  # 不走索引

注意: %linux%类的搜索需求，可以使用elasticsearch 专门做搜索服务的数据库产品

9.8 单独引用联合索引里非第一位置的索引列.作为条件查询时不走索引.

列子：
复合索引：

DROP TABLE t1
CREATE TABLE t1 (id INT,NAME VARCHAR(20),age INT ,sex ENUM('m','f'),money INT);

ALTER TABLE t1 ADD INDEX t1_idx(money,age,sex);

DESC t1
SHOW INDEX FROM t1

走索引的情况测试：
EXPLAIN SELECT NAME,age,sex,money FROM t1 WHERE money=30 AND age=30  AND sex='m';
EXPLAIN SELECT NAME,age,sex,money FROM t1 WHERE money=30 AND age=30  ;
EXPLAIN SELECT NAME,age,sex,money FROM t1 WHERE money=30  AND sex='m';    ----->部分走索引
不走索引的：
EXPLAIN SELECT  NAME,age,sex,money FROM t1 WHERE  age=20
EXPLAIN SELECT NAME,age,sex,money FROM t1 WHERE  age=30  AND sex='m';
EXPLAIN SELECT NAME,age,sex,money FROM t1 WHERE   sex='m';

---

10. 压力测试

10.1 模拟数据库数据模拟数据库数据

为了测试我们创建一个oldboy的库创建一个t1的表，然后导入50万行数据，脚本如下：
vim slap.sh
#!/bin/bash  
HOSTNAME="localhost" 
PORT="3306" 
USERNAME="root" 
PASSWORD="123" 
DBNAME="oldboy" 
TABLENAME="t1" 
#create database 
mysql -h ${HOSTNAME} -P${PORT} -u${USERNAME} -p${PASSWORD} -e "drop database if exists ${DBNAME}" 
create_db_sql="create database if not exists ${DBNAME}" 
mysql -h ${HOSTNAME} -P${PORT} -u${USERNAME} -p${PASSWORD} -e "${create_db_sql}" 
#create table 
create_table_sql="create table if not exists ${TABLENAME}(stuid int not null primary key,stuname varchar(20) not null,stusex char(1)   
not null,cardid varchar(20) not null,birthday datetime,entertime datetime,address varchar(100)default null)" 
mysql -h ${HOSTNAME} -P${PORT} -u${USERNAME} -p${PASSWORD} ${DBNAME} -e "${create_table_sql}" 
#insert data to table 
i="1" 
while [ $i -le 500000 ]  
do  
insert_sql="insert into ${TABLENAME}  values($i,'alexsb_$i','1','110011198809163418','1990-05-16','2017-09-13','oldboyedu')" 
mysql -h ${HOSTNAME} -P${PORT} -u${USERNAME} -p${PASSWORD} ${DBNAME} -e "${insert_sql}" 
let i++  
done  
#select data  
select_sql="select count(*) from ${TABLENAME}" 
mysql -h ${HOSTNAME} -P${PORT} -u${USERNAME} -p${PASSWORD} ${DBNAME} -e "${select_sql}"

执行脚本：
sh slap.sh

10.2 检查数据可用性

mysql -uroot -p123
select count(*) from oldboy.t1;

10.3 在没有优化之前我们使用mysqlslap来进行压力测试

mysqlslap --defaults-file=/etc/my.cnf \
 --concurrency=100 --iterations=1 --create-schema='oldboy' \
--query="select * from oldboy.t1 where stuname='alexsb_100'" engine=innodb \
--number-of-queries=2000 -uroot -p123 -verbose

---

11. mysql的存储引擎

11.1 作用

真正和磁盘数据打交道的
mysql 基于存储引擎管理 表空间数据 --- 数据文件

11.2 innodb存储引擎简介

1.实际存储的文件格式

ibd文件： 存储表的数据行和索引
frm文件： 表结构相关信息

11.3 myisam存储引擎简介

1.实际存储的文件格式

frm文件： 表结构相关信息
myi文件： 存储索引
myd文件： 存数据行

11.4 innodb的核心特性：事务、行锁定粒度，备份和恢复，自动故障修复

1.事务的作用：保证交易的完整性

2.事务的特性：ACID

1.atomic（原子性）
所有的语句作为一个单元全部成功执行或全部取消，不应该有中间状态
默认一条语句就是一个事务，需要将autocommit关闭掉

2.consistent（一致性）
如果数据库在事务开始处于一致状态，则在执行该事务期间将保留一致的状态。

3.isolated（隔离性）
事务之间不相互影响，同一行操作时，不能同时修改数据 。
两个方面：修改同一行，一致性读 --- 悲观锁的概念

4.durable（持久性）
事务成功完成后，所做的所有更改都会准确的记录在数据库中，所做的更改不会丢失。

**注意：

1.
redo 和 undo实现了ACD
2.
lock（行级锁）+ isolation（隔离级别）实现了I

行级锁：事务修改行，会持有这行的锁 ---只有有锁的事务才能进行修改

隔离级别：
RU: 读未提交
RC: 读已提交
RR: 可重复读  读的是undo  mysql默认的是RR
S: 可串行，序列化

11.5 mysql的隔离级别

1.RU：读未提交 脏读（没有提交的数据）/不可重复读/幻读

1.定义：就是一个事务可以读取另一个未提交事务的数据。

2.实例
老板要给程序员发工资，程序员的工资是3.6万/月。但是发工资时老板不小心按错了数字，按成3.9万/月，该钱已经打到程序员的户口，但是事务还没有提交，就在这时，程序员去查看自己这个月的工资，发现比往常多了3千元，以为涨工资了非常高兴。但是老板及时发现了不对，马上回滚差点就提交了的事务，将数字改成3.6万再提交。

3.分析：实际程序员这个月的工资还是3.6万，但是程序员看到的是3.9万。他看到的是老板还没提交事务时的数据。这就是脏读。

2.RC：读已提交 不可重复读/幻读

1.定义：就是一个事务要等另一个事务提交后才能读取数据。

2.实例
程序员拿着信用卡去享受生活（卡里当然是只有3.6万），当他埋单时（程序员事务开启），收费系统事先检测到他的卡里有3.6万，就在这个时候！！程序员的妻子要把钱全部转出充当家用，并提交。当收费系统准备扣款时，再检测卡里的金额，发现已经没钱了（第二次检测金额当然要等待妻子转出金额事务提交完）。程序员就会很郁闷，明明卡里是有钱的…

3.分析：这就是读提交，若有事务对数据进行更新（UPDATE）操作时，读操作事务要等待这个更新操作事务提交后才能读取数据，可以解决脏读问题。但在这个事例中，出现了一个事务范围内两个相同的查询却返回了不同数据，这就是不可重复读。

3.RR：可重复读 不会出现幻读，查询的数据都是一样的

1. 就是在开始读取数据（事务开启）时，不再允许修改操作
2. 实例
程序员拿着信用卡去享受生活（卡里当然是只有3.6万），当他埋单时（事务开启，不允许其他事务的UPDATE修改操作），收费系统事先检测到他的卡里有3.6万。这个时候他的妻子不能转出金额了。接下来收费系统就可以扣款了。

3.分析：
重复读可以解决不可重复读问题。写到这里，应该明白的一点就是，不可重复读对应的是修改，即UPDATE操作。但是可能还会有幻读问题。因为幻读问题对应的是插入INSERT操作，而不是UPDATE操作。

幻读实例：
程序员某一天去消费，花了2千元，然后他的妻子去查看他今天的消费记录（全表扫描FTS，妻子事务开启），看到确实是花了2千元，就在这个时候，程序员花了1万买了一部电脑，即新增INSERT了一条消费记录，并提交。当妻子打印程序员的消费记录清单时（妻子事务提交），发现花了1.2万元，似乎出现了幻觉，这就是幻读。

4.S：可串行，序列化

定义：在事务操作期间，禁止其他事务进行更新，插入，删除等操作

---

11.6 事务的控制语句

正常
begin;
xxxx
xxxx
commit;

夭折
begin;
xxxx
xxxx

自杀
begin;
xxxxx
xxxx
rollback

11.7 隐式提交：（默认方式）

1. 设置关闭默认提交

1.当前会话生效
set autocommit=0; 当前会话生效，关闭自动事务

2.永久生效
vim /etc/my.cnf
autocommit=0;

---

2. 例子

1.例子一： 一个事务中有多个事务开启
begin
xxxx
xxxx
begin


2.例子二： 事务中有DDL/DCL的语句
begin
xxxx
xxxx
create drop alter grant  DDL /DCL

---

12. 日志

12.1 错误日志

log_error=/var/log/mysql.log

分析[error]------分析日志中带有中括号error的日志

12.2 二进制日志

1.作用

1.记录所有变更类的语句
DDL,DCL：以语句方式（statement）记录
DML（已提交的事务语句）：默认是以行模式记录（row模式，数据行的变化）

2.可以做数据恢复和操作的审计

2.配置方法

log_bin = /opt/mysql/data/mysql-bin
binlog_format = row
server_id = 6
sync_binlog  = 1 每次事务提交都立即刷新磁盘

3.row模式和statement模式区别

insert into tq values (now())
row模式更加严谨

4.查看日志

show binary logs; ---查看
show master status; --- 查看正在使用的日志

5.内容查看

1.按事件查看日志内容
show binlog events in 'mysql-bin.000012'

2.直接查看内容：
mysqlbinlog /opt/mysql/data/mysql-bin

6.截取二进制命令

mysqlbinlog --start-position=219 --stop-position=186613 /opt/mysql/data/mysql-bin.000012 >/tmp/binlog.sql

---

12.3 慢日志：记录慢语句的日志；默认情况下是没有开启这个功能–需要手动开启

1.如何开启：配置文件中添加

slow_query_log=1
slow_query_log_file=/opt/mysql/data/standby-slow.log
long_query_time=1	
log_queries_not_using_indexes=1

2.时间维度

long_query_time = 1
0.1s以下算优化还可以的

3.索引维度：判断没有走索引的语句

log_queries_not_using_indexes=1

---

13. 备份恢复

13.1 备份的分类

逻辑备份：sql语句的备份  做迁移的很好的工具
物理备份：数据页备份

13.2 逻辑备份工具介绍

1.工具一

select xxxx from t1  into outfile '/tmp/redis.txt'

2.工具二

mysql -uroot -p123 -e "select concat('hmset city_',id,' id ', id,' name ',name,' countrycode ',countrycode,' district ',district,' population ',population) from world.city limit 10 "|redis-cli

13.3 mysqldump的使用和参数

1.参数：基本参数

mysqldump
-u
-p
-s
-h
-p

2.参数：其他参数

-A 全库备份
mysqldump -uroot -p123 -A >/backup/full.sql

-B  局部备份
mysqldump -uroot -p123 -B world bbs >/backup/full.sql

备份单库或者库中的表
mysqldump -uroot -p123  bbs >/backup/full.sql

备份单库中的表：
mysqldump -uroot -p123 world city country >/backup/ccc.sql

--master-data=2   备份时记录二进制日志的状态位置信息/锁表的功能
mysqldump -uroot -p123 --master-data=2 -B world bbs >/backup/full.sql

--single-transaction  开启innodb的热备功能
温备：锁门
热备：快照备份，拍个照片，备份的是undo的数据
除了innodb的引擎外的，都需要加锁备份

注意：上面2个必须加

-R
-trigger  触发器备份

1.完整实例
mysqldump -uroot -p123 -A --master-data=2 --single-transaction  -R   --triggers  >/backup/full.sql

---

14. 主从复制：基于二进制日志实现的

14.1 为什么要有主从复制

单纯的备份恢复，要耗费大量的时间

14.2 主从复制的核心：读写分离

（读多写少 -适合– 读写分离）

14.3 模拟主从复制功能

1.前提

1.以mysql实例3307作为主库，以3308作为从库
主				从
3307 -------> 3308

2. 主库3307中创建复制用户

1.主库开启二进制日志
vim /data/3307/my.cnf 
log_bin=/data/3307/mysql-bin

2.重启实例
[root@standby 3307]# systemctl restart mysqld3307

3.进入主库的mysql
[root@standby backup]# mysql -S /data/3307/mysql.sock

4.创建用户复制用户
mysql> grant replication slave on *.* to repl@'10.0.0.%' identified by '123';

5.查看主库日志开启状态
mysql> show master status

3. 从库3308节点开启主从复制功能

1.进入从库的mysql
[root@standby 3307]# mysql -S /data/3308/mysql.sock

2.关联到指定的主库
mysql> CHANGE MASTER TO
  MASTER_HOST='10.0.0.200',
  MASTER_USER='repl',
  MASTER_PASSWORD='123',
  MASTER_PORT=3307,
  MASTER_LOG_FILE='mysql-bin.000001',
  MASTER_LOG_POS=154;  ----position

3.开启从库的服务
mysql> start slave;

4.查看开启状态
mysql> show slave status\G

注意：看到以下2行，代表主从复制已经搭建好
Slave_IO_Running: Yes
Slave_SQL_Running: Yes

---

加中间件

15. mysql高可用架构（安全性）

15.1 “高可用”架构

99%          1%*365=3.65D = 87.6h      
99.9%        			    8.76h
99.99%					    0.876h  ---大型企业
99.999%                     0.0876h ---银行企业

注意：没有百分百高可用的架构

15.2 MySQL高可用架构介绍

1.MHA------------自愈性的架构，分钟内完成自愈  
# 需要具备的功能：
    监控
    选主
    数据补偿
    切换
    
2.MGR+Mysql router+mysql shell.....===>Mysql Innodb Cluster    ======>mongodb sharding cluster
3.PXC      
4.galera cluster


注意：
第二种未来会火

---

16. 高性能架构（安全性+高性能）

16.1 基本概念

分布式架构
分片集群  
中间件方法，来管理

垂直拆分：
    分库
    表关联的非常麻烦

水平拆分：
    分表

16.2 目前主流的读写分离架构

1.atlas  360   C++
2.maxscale mariadb
3.proxySQL 
4.DRDS(买)
5.mysql router

16.3 目前主流的分布式架构

分片集群
1.Mycat 
2.DBLE 
3.TDDL
4.DRDS