数据库基本特性
- 原子性（Atomicity）： 要么全部成功，要么全部失败回滚
- 一致性（Consistency）： 数据库事务必须从一个一致性状态变为另一个一致性状态
- 隔离性（Isolation）： 默认隔离级别下一个事务提交前对另一个事务不可见
- 持久性（Durability）： 数据永久保存在硬盘中
事务隔离级别：
- READ UNCOMMITTED（读未提交数据）： 允许事务读取未被其他事务提交的变更数据，会出现脏读、不可重复读和幻读问题；
- READ COMMITTED（读已提交数据）： 只允许事务读取已经被其他事务提交的变更数据，可避免脏读，仍会出现不可重复读和幻读问题；
- REPEATABLE READ（可重复读）： 读取数据时，事务不结束，别的事务就不可以修改这条记录，仍会出现幻读问题。REPEATABLE - - READ是MySQL默认的事务隔离级别；
- SERIALIZABLE（序列化）： 运行完一个事务的所有子事务之后才可以执行另外一个事务里面的所有子事务；
脏读： 事务A读到了事务B还没有提交的数据
不可重复读： 在一个事务里面读取了两次某个数据，读出来的数据不一致
幻读： 一个事务读取时另一个事务写入或删除数据，导致两次读取的数据不一致
查询MySQL事务隔离命令：
-> select @@tx_isolation;
MySQL架构
MySQL总架构图
MySQL常用引擎
innodb架构图
Undo Tablespaces：操作数据之前，首先将数据备份到回滚段（位于共享表空间），然后进行数据的修改。如果出现了错误或者用户执行了ROLLBACK语句，系统可以利用回滚段中的备份将数据恢复到事务开始之前的状态。
Redo Log：为了提高效率，对于内存中页的修改，首先写入重做日志缓冲，合并后写入重做日志(追加，顺序IO)，最后才写入磁盘数据文件。
innodb重要属性：
优点：
- innodb数据更新时采用的是行锁，具有较好的并发性能；
- 支持数据库事务，并且实现了SQL标准的四种隔离级别；
- 支持外键（尽量少使用外键，而是通过程序来保证关联约束）；
- innodb会在内存中建立缓冲池，用于缓冲数据和索引，但是该引擎不支持fulltext类型的索引；
缺点：
- InnoDB不保存表的具体行数，执行select count(*) from table时需要全表扫描；
- 不支持fulltext类型的索引；
其他的引擎都可以忽略不计，只有在很特殊的情况下才会考虑其他的引擎。这里不过多描述。
执行计划
讲索引优化之前先复习下MySQL的执行计划。
SQL执行顺序
FROM—>ON—>JOIN—>WHERE—>GROUP BY(开始使用select中的别名)—>SUM(聚合函数)—>HAVING—>SELECT—>DISTINCT—>UNION—>ORDER BY—>LIMIT
SQL实例：
select a.SESSION_ID,a.CONTENT_TYPE,a.ANS_TIME,sum(a.QA_NUM)
from buss_session_qa a
join buss_session s
on a.SESSION_ID=s.SESSION_ID
where a.SESSION_ID > 1000
group by a.SESSION_ID
order by a.SESSION_ID desc
limit 100;
explain命令
概要描述
id:选择标识符，id值越大越先被执行，id值相同，执行顺序由上到下。
select_type:表示查询的类型。
table:输出结果集的表
partitions:匹配的分区
type:表示表的连接类型
possible_keys:表示查询时可能使用的索引，实际查询中不一定使用
key:表示实际使用的索引
key_len:索引字段的长度
ref:列与索引的比较
rows:扫描出的行数(估算的行数)
filtered:按表条件过滤的行百分比
Extra:执行情况的描述和补充信息
关键属性说明
type:
system > const > eq_ref > ref > range > index > all（性能从左到右依次变差）
system：表中只有一行数据，是const的特例，比较少出现
const：通过索引一次就找到了，通常是试用primary key 或者unique索引。(官网描述：The table has at most one matching row, which is read at the start of the query. Because there is only one row, values from the column in this row can be regarded as constants by the rest of the optimizer. const tables are very fast because they are read only once.)
eq_ref：唯一性索引扫描，常见于主键或unique索引，检索结果只有一条与之匹配
ref：非唯一性索引扫描，通过索引匹配，可以返回多行结果
range：通过索引范围检索
index：Full Index Scan，遍历索引树，通常索引文件比数据文件小，速度比all块
all：Full Table Scan，全表扫描
extra:
using index：查询使用了覆盖索引。
using where：表示存储引擎返回的记录并不是所有的都满足查询条件，需要在server层进行过滤。
using index condition：5.6之前，存储引擎只能根据限制条件扫描数据并返回，然后server层根据检查条件进行过滤再返回真正符合查询的数据。5.6.x之后支持ICP特性，可以把检查条件也下推到存储引擎层，不符合检查条件和限制条件的数据，直接不读取，这样就大大减少了存储引擎扫描的记录数量。
using filesort：排序时无法使用到索引时，就会出现这个。常见于order by和group by语句中。
using temporary：表示使用了临时表存储中间结果。临时表可以是内存临时表和磁盘临时表，执行计划中看不出来。
索引
索引算法
B+树
哈希
二级索引
索引类型
normal：普通索引
unique：唯一索引，建立索引的字段不能重复
fulltext：全文索引
联合索引：两个或以上的字段建立一个索引，组合索引的生效遵循“最左前缀”原则
例如：字段a,b,c建立了一个组合索引
索引生效的查询：a | a,b | a,b,c | a,c(a索引生效，c索引是不生效的)
索引无效的查询：b | c | b,c
索引设计原则
索引选择原则
- 索引选择性：不重复的索引值和表中所有数据行数的比值。索引选择性越高查询效率越高，因为它使MySQL在查找匹配的时候可以过滤掉更多的行。唯一索引的选择率为1，是索引选择性最好的；
- B+树索引本来最适合的就是点查询，和小范围的range查询，当预估返回的数据量超过一定比例的时候，再根据索引一条一条去查反而不如全表扫描快。实际上，这时优化器会不走索引而使用全表扫描；
- 多条件查询时尽量使用联合索引而不是在每个字段上建立索引。联合索引是按从左到右的顺序依次搜索，因此把索引选择性高或者直接匹配的字段放到最左边，选择性低或范围匹配的字段放在右边；
查询优化原则
- 覆盖索引：一个查询语句的结果只要从索引中就能获取，不需要从数据表中读取。
-- 覆盖索引实例
explain select SESSION_ID,ROBOT_ID from buss_session_qa order by SESSION_ID limit 1000;
-- 在大数据分页查询中使用覆盖索引实现延迟加载
-- 原SQL
explain select * from buss_session_qa where SESSION_ID > 1 order by SESSION_QA_ID limit 5000,10;
-- 优化后的SQL
explain select a.* from buss_session_qa a
inner join
(select SESSION_QA_ID from buss_session_qa where SESSION_ID > 1 order by SESSION_QA_ID limit 5000,10) b
on b.SESSION_QA_ID = a.SESSION_QA_ID;
- 前缀索引：有时候建立索引的字段是较长的字符串，会降低索引的效率，可以将索引建立在字符串开头的部分字符，可以大大节省索引空间，但同时也降低了索引选择性。实际开发中很少把索引建立在长字符串的字段上，如果有，说明设计不大合理，因此前缀索引通常比较少使用；
- in查询条件的参数列表会在排序后二分查找，查询的字段没有索引的查找时间复杂度为log(n)；
- or查询条件查询的字段没有索引的时间复杂度为log(n)，并且用or连接带索引和不带索引的检索条件，会使索引失效。有这类需求可以使用union连接；
- where和on同时使用时，优先把筛选条件放到on中，让初次筛选过滤更多的数据；
- 排序同时出现asc,desc，后半部分索引是不生效的；
实战分析
前缀索引实战：
在大数据分页查询中使用覆盖索引实现延迟加载：
-- 原SQL
explain select * from buss_session_qa where SESSION_ID > 1 order by SESSION_QA_ID limit 5000,10;
-- 优化后的SQL
explain select a.* from buss_session_qa a
inner join
(select SESSION_QA_ID from buss_session_qa where SESSION_ID > 1 order by SESSION_QA_ID limit 5000,10) b
on b.SESSION_QA_ID = a.SESSION_QA_ID;
其它补充
binlog
mysql-binlog是MySQL数据库的二进制日志，用于记录用户对数据库操作的SQL语句（除了数据查询语句）信息。可以使用mysqlbin命令查看二进制日志的内容。
binlog的格式也有三种：STATEMENT、ROW、MIXED 。
STATMENT模式
基于SQL语句的复制(statement-based replication, SBR)，每一条会修改数据的sql语句会记录到binlog中。
优点：不需要记录每一条SQL语句与每行的数据变化，这样子binlog的日志也会比较少，减少了磁盘IO，提高性能。
缺点：在某些情况下会导致master-slave中的数据不一致(如sleep()函数， last_insert_id()，以及user-defined functions(udf)等会出现问题)
基于行的复制(row-based replication, RBR)
不记录每一条SQL语句的上下文信息，仅需记录哪条数据被修改了，修改成了什么样子了。
优点：不会出现某些特定情况下的存储过程、或function、或trigger的调用和触发无法被正确复制的问题。
缺点：会产生大量的日志，尤其是alter table的时候会让日志暴涨。
混合模式复制(mixed-based replication, MBR)
以上两种模式的混合使用，一般的复制使用STATEMENT模式保存binlog，对于STATEMENT模式无法复制的操作使用ROW模式保存binlog，MySQL会根据执行的SQL语句选择日志保存方式。
binlog数据恢复
binlog数据恢复工具：
binlog2sql
解析出标注SQL是恢复误操作现场的所有执行SQL。
解析出回滚SQL是直接将误操作现场逆向（例如：误更新操作解析出来的SQL是更新前的值），解析出来的SQL可以直接执行用于恢复更新之前的值。
具体详细参数详解参考官方文档：binlog2sql
数据导出
# 参数详解：
# -h 要连接的MySQL数据库ip
# -u MySQL登录账号
# -p MySQL登录账号密码，可以不填写运行命令后会提示输入密码
# schema_name 要导出的数据库名称
# table_name 要导出的表名
# --where 直接写where条件即可
# filePath 生成的文件全路径
mysqldump -h [hostname] -u [username] -p --skip-lock-tables [schema_name] [table_name] --where="search_column='search_value'" --triggers=false --replace>[filePath];
# 命令示例：
mysqldump -h localhost -u root -p --skip-lock-tables test_schema user_table --where="user_name in('zhangsan','lisi','xiaoming') and is_deleted=0" --triggers=false --replace>/var/lib/mysql-files/user_table.sql;
# 通过这个方式导出需要设置/etc/my.cnf配置文件中的 secure_file_priv=<file path> 属性，该配置表示允许mysqld在<file path>目录下进行数据导入导出
# 如果导出中文乱码问题，需要设置CHARACTER SET gbk
# 该命令可以导出成SQL文件也可以导出成 .xls 的Excel文件
select * from [table_name] where [search_column]='search_value' into outfile 'file path' CHARACTER SET gbk;
select * from user_table where user_name in('zhangsan','lisi','xiaoming') and is_deleted=0 into outfile '/home/app/temp/user_export.xls' CHARACTER SET gbk;
444
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
