mysql关系型数据库的认识

----基于mysql 5.7.30版本。----

1. 事务的ACID属性：原子性、一致性、隔离性、持久性

　　a) 原子性：这组语句要么全部执行，要么全部不执行。如果事务执行到一半出现错误， 数据库就滚回到事务开始执行的地方。
　　b) 一致性：事务开始前和结束后，数据库的完整性约束没有被破坏。比如a向b转账，不可能a扣了钱，b却没有收到。
　　c) 隔离性：同一时间，只允许一个事务请求同一数据，不同的事务之间没有任何干扰。否则就会出现脏读，不可重复读，幻读
　　d) 持久性：事务完成后，事务对数据库的所有更新被保存到数据库，不能回滚

2. InnoDB是MySQL默认的存储引擎，默认的隔离级别是RR（Repeatable read可重复读），实现了串行化级别的效果，保留了较好的并发性能

innodb引擎
    1.支持事务,四个级别的事务
    2.锁定机制一般是行级锁定.更新时只锁定当前行,其它的行.没关系,可以继续读写.全表扫描.还是表锁*
    3.读写阻塞与事务的隔离相关,读取速度一般*
    4.可以缓存数据和索引,高效的缓存特性
    5.支持分区,表空间.
    5.适合读写业务比较多的环境,比如BBS等.一般的生产环境,也推荐用innodb.效率高
    6.服务器资源开销大
    8.支持外键约束,不支持全文索引.

innodb调优
    1.my.cnf中参数
        [root@ser200 3306]# grep -i "innodb" my.cnf 
        default_table_type = InnoDB
        innodb_additional_mem_pool_size = 4M
        innodb_buffer_pool_size = 32M
        innodb_data_file_path = ibdata1:128M:autoextend
        innodb_file_io_threads = 4
        innodb_thread_concurrency = 8
        innodb_flush_log_at_trx_commit = 2
        innodb_log_buffer_size = 2M
        innodb_log_file_size = 4M
        innodb_log_files_in_group = 3
        innodb_max_dirty_pages_pct = 90
        innodb_lock_wait_timeout = 120
        innodb_file_per_table = 0

        生产环境16G内存服务器调优实例:
        default_table_type = InnoDB
        innodb_additional_mem_pool_size = 16M
        innodb_buffer_pool_size = 2048M            #Innodb核心参数,生产环境会给的很大.16G内存,双实例,会给2G的缓存
        innodb_data_file_path = ibdata1:1024M:autoextend
        innodb_file_io_threads = 4
        innodb_thread_concurrency = 8
        innodb_flush_log_at_trx_commit = 1 #0/1/2
        innodb_log_buffer_size = 16M
        innodb_log_file_size = 128M
        innodb_log_files_in_group = 3
        innodb_max_dirty_pages_pct = 90
        innodb_lock_wait_timeout = 120
        innodb_file_per_table = 0
　　生产环境设置好参数,不怎么会改动上面的参数.主要是SQL语句的调优,改上面参数作用有限.最核心优化是每个语句走索引
    2.主键尽可能小.包括索引
    3.尽量避免全表扫描.因为会造成表锁
    4.尽量缓存所有的数据和索引,提高响应速度,减少磁盘IO消耗
        发现:数据库核心调优.让用户尽量去缓存上找数据,到内存找数据,少到磁盘上找数据.
    5.大批量小插入,尽量自己控制事务,而不要使用autocommit自己提交
    6.合理设置flush_log_at_trx_commit,不要过份追求安全性.
　　0，表示每隔一秒把log buffer刷到文件系统中(os buffer)去，并且调用文件系统的“flush”操作将缓存刷新到磁盘上去.
　　1, 表示在每次事务提交的时候，都把log buffer刷到文件系统中(os buffer)去，并且调用文件系统的“flush”操作将缓存刷新到磁盘上去。(现在IO基本可以支持)
　　2, 表示在每次事务提交的时候，都把log buffer刷到文件系统中(os buffer)去, 然后每秒写入到磁盘上去。

3. 事务的并发问题

　　1、脏读：事务A读取了事务B更新的数据，然后B回滚操作，那么A读取到的数据是脏数据
　　2、不可重复读：事务 A 多次读取同一数据，事务 B 在事务A多次读取的过程中，对数据作了更新并提交，导致事务A多次读取同一数据时，结果 不一致。
　　3、幻读：系统管理员A将数据库中所有学生的成绩从具体分数改为ABCDE等级，但是系统管理员B就在这个时候插入了一条具体分数的记录，当系统管理员A改结束后发现还有一条记录没有改过来，就好像发生了幻觉一样，这就叫幻读。
　　小结：不可重复读的和幻读很容易混淆，不可重复读侧重于修改，幻读侧重于新增或删除。解决不可重复读的问题只需锁住满足条件的行，解决幻读需要锁表
　　　　　 mysql默认隔离级别：不可重复读
　　　　　事务的传播行为(一般都用这个): PROPAGATION_REQUIRED 如果当前没有事务，就创建一个新事务

4. 联合索引：
　　在mysql建立联合索引时会遵循最左前缀匹配的原则，即最左优先，在检索数据时从联合索引的最左边开始匹配
　　KEY test_col1_col2_col3 on test(col1,col2,col3);
　　联合索引 test_col1_col2_col3 实际建立了(col1)、(col1,col2)、(col,col2,col3)三个索引。
　　对于联合索引(col1,col2,col3)，查询语句SELECT * FROM test WHERE col2=2;是否能够触发索引？大多数人都会说NO，实际上却是YES
　　EXPLAIN SELECT * FROM test WHERE col1=1;
　　EXPLAIN SELECT * FROM test WHERE col2=2;
　　观察上述两个explain结果中的type字段。查询中分别是：
　　type: ref
　　type: index
　　ref：这种类型表示mysql会根据特定的算法快速查找到某个符合条件的索引，而不是会对索引中每一个数据都进行一一的扫描判断，
　　也就是所谓你平常理解的使用索引查询会更快的取出数据。而要想实现这种查找，索引却是有要求的，要实现这种能快速查找的算法，
　　索引就要满足特定的数据结构。简单说，也就是索引字段的数据必须是有序的，才能实现这种类型的查找，才能利用到索引。
　　index：这种类型表示mysql会对整个该索引进行扫描。要想用到这种类型的索引，对这个索引并无特别要求，只要是索引，
　　或者某个联合索引的一部分，mysql都可能会采用index类型的方式扫描。但是呢，缺点是效率不高，
　　mysql会从索引中的第一个数据一个个的查找到最后一个数据，直到找到符合判断条件的某个索引。所以，上述语句会触发索引。
　　MySQL5.5.3之后支持使用utf8mb4字符集。可以存储表情、生僻字占4字符的数据。

5. 如何避免死锁
　　事务尽可能小，不要将复杂逻辑放进一个事务里。
　　涉及多行记录时，约定不同事务以相同顺序访问。
　　业务中要及时提交或者回滚事务，可减少死锁产生的概率。
　　表要有合适的索引。
　　可尝试将隔离级别改为 RC 。