《Mysql实战45讲》笔记及总结归纳

前言

作为一名软件开发者，对数据库无疑是要非常熟悉了。不光要在执行sql语句的时候，明白它在数据库中如何执行，更要掌握诸多的数据库优化技巧，让你的软件系统超级流畅。

基础篇

基础架构：sql语句如何执行？

MySql大致分为两层：server层和存储引擎。
server层：存储过程、触发器、视图。
存储引擎：数据的存储和提取。如InnoDB等
常用操作：
解决长连接大量占用内存问题：mysql_reset_connection
在查询缓存里查询：select SQL_CACHE * from T where ID=10

日志系统：更新语句如何执行？

update adm_log_alarm set c=c+1 where id = 2

1、分析器知道了这是一条更新语句，遂清空查询缓存（我们不提倡使用查询缓存的原因）
2、更新操作涉及到了日志模块：redo log(重做日志)、bin log(归档日志)，来实现WAL技术：先写日志，后写磁盘。此技术可使数据库异常重启数据不丢失（crash-safe）
3、redo log是InnoDB特有的日志系统，是物理日志，循环写入；bin log是Mysql的server层实现的，是逻辑日志，追加写入（保证数据库可恢复到之前某时刻的状态）

事物隔离：为什么你改了我还看不见

事务：保证一组数据库操作，要么全部成功，要么全部失败。
事务隔离级别：

• 读未提交（一个事务未提交时，它做的变更可以被别的事务看到）
• 读提交（一个事务未提交后，它做的变更才被别的事务看到），oracel
• 可重复度（一个事务执行过程中的数据，与启动时保持一致，当然在提交前，对外界不可见）,
• 串行化（读、写加锁、若冲突，后访问的事务在等前访问的事务执行完）
  查询事务隔离级别：show variables like "transaction_isolation"

启动事务方式：

begin 或 start transaction      //结束用commit，回滚用rillback
set autocomit = 0        //执行sql即开启事务commit、rollback结束事务

我们建议set autocommit = 1通过显示方式启动事务

深入浅出索引

InnoDB使用了B+树索引模型，每一个索引对应着一颗B+树。

creat table T (id int primary key auto_increment, k int not null, name varchar(16), index(k) )engine = InnoDB

主键索引的叶子结点为整行数据，也称为聚簇索引，而非主键索引的叶子结点是主键的值，需要回到主键索引（回表），所以也称为二级索引。因此尽可能用主键查询
因此，从性能和存储空间考量，主键要尽可能的少占空间，普通索引的占用空间才越小
建一个带索引的表：

CREATE TABLE T (
id int NOT NULL,
id_card VARCHAR(32) DEFAULT NULL,
name VARCHAR(32) DEFAULT NULL,
age int DEFAULT NULL,
PRIMARY KEY (id),
KEY id_card (id_card),      //key  添加索引
KEY name_age (name,age)        
)

覆盖索引：select ID from T where k between 3 and 5,此过程不需要回表。此为重用的一个优化手段。
最左前缀原则：联合索引的最左N个字段，重复使用索引。
索引下推：MySQL5.6引入索引下推，在索引遍历过程中，直接过滤掉不满足条件的记录，如select * from T where name like "张*" and age=10

全局锁和表锁：给表加个字段怎么这么多阻碍？

Mysql中的锁：全局锁、表锁、行锁
全局锁

flush table with read lock   //FTWRL     应用场景：全库逻辑备份。

备用方案：
1.事务隔离，需支持
2.set global readonly = true //存在一些弊端：readonly有时用于判断主从库；客户端异常不会更新readonly状态，而FTWRL会释放这个全局锁
表级锁：表锁、元数据锁（访问表时自动加上）
读操作不互斥，读写互斥，写写互斥

lack tables T1 read,T2 write

如何安全的给小表加字段？

alter table tbl_name NOWAIT/WAIT N add column

行锁功过：如何减少行锁对性能的影响？

持续更新中。。。

数据库优化总结

1、使用主键索引（聚簇索引），因为非主键索引会多一次回表的过程
2、主键长度越小越好，比如自增主键（有些场景不适合），理由如下：
从性能上考虑，主键长度较长的话，由于B+算法的树结构，会多增加分页和页合并操作，影响效率和存储空间。且在非主键索引的时候，每个主键作为二级索引，占的空间会很多，影响查询效率。
3、

数据库日志

show variables like 'general_log';  -- 查看日志是否开启
show variables like 'log_output';  -- 看看日志输出类型  table或file
show variables like 'general_log_file';  -- 看看日志文件保存位置
set global general_log_file='tmp/general.lg'; -- 设置日志文件保存位置
set global general_log=on; -- 开启日志功能
set global log_output='table'; -- 设置输出类型为 table
set global log_output='file';   -- 设置输出类型为file
mysql查看历史记录 里面的语句经过了base64加密，所以需要解开
mysqlbinlog --base64-output=DECODE-ROWS -v -v Storage.000005
mysqlbinlog --no-defaults --base64-output=decode-rows -v /usr/local/mysql/mysql-bin.003271 
配置防火墙的数据库访问白名单
/sbin/iptables -I INPUT -p tcp -s ip --dport 3306 -j ACCEPT （把ip换成自己的IP）