千篇一律的MySql总结

MySql

主键和唯一索引

• 主键一定会创建唯一索引，但唯一索引不一定是主键
• 主键不允许为空，唯一索引列允许为空
• 一个表只能有一个主键，但可以有多个唯一索引（联合主键只能看成一个主键，因为是多个主键字段表示唯一行）
• 主键可以被其他表引用为外键，唯一索引不行
• 主键是一种约束，唯一索引是一种索引，是表的冗余数据结构，用来快速查找，两者有本质区别

索引类型

hash索引

• 优点：采用哈希表，对key值分散的等值查询速度较快
• 缺点：不支持范围查询，不支持多列索引的最左匹配规则，不支持索引排序，在大量重复的键值情况下效率较低，因为哈希碰撞问题

B+ Tree索引

• 优点：不支持范围查询，不支持多列索引的最左匹配规则，不支持索引排序，在大量重复的键值情况下效率较低，因为哈希碰撞问题
• 缺点：等值查询速度相对Hash索引会较慢点，一般都用B+索引

事务

事务特性

• 原子性(Atomicity)：事务是个不可分割的最小执行单位，执行的所有动作要不全部完成，要不都不起作用
• 一致性（Consistency）：事务前后保证业务逻辑上的数据一致性，比如A转账200给B，事务完成后B一定+200，A一定-200
• 隔离性（Isolation）：并发环境中，不同的事务操作相同的数据，每个事务都有自己的完整数据空间，不会相互干扰
• 持久性（Durability）：事务一旦被提交，会永久保存到数据库中，即使系统崩溃，重启后还能恢复到事务成功后的状态

并发事务问题

• 脏读
• 丢失修改（多个事务，修改覆盖）
• 不可重复读（一次事务，两次查询不一致）
• 幻读(针对插入)

事务的隔离级别

• 读未提交：会出现脏读、不可重复读、丢失修改、幻读
• 读提交：会出现不可重复读、丢失修改、幻读
• 重复读：会出现幻读
• 串行化：效率低下

mysql存储引擎

• MyISAM（不是事务安全、不支持外键、insert MyISAM比较适合、只支持表级锁）
• Inner（支持事务、外键、行锁、）

锁

• 死锁：多线程会遇到的问题，两个线程同时争抢同一把锁造成死循环
• 悲观锁：悲观锁认为同一个数据并发操作一定会发生修改，所以要对一个数据的并发操作加锁，悲观锁是比较重量级的
• 乐观锁：乐观锁认为同一个数据的并发操作是不会发生修改的，可以通过版本机制，当更新数据的时候会进行不断尝试修改

性能优化

• count(主键)往往是性能最优的
• 索引失效的情况
  • 字符串不加单引号，会失效
  • 使用的索引列不是复合索引列表中的第一部分，会失效
  • 避免where子句中对字段进行null值判断，否则可能导致mysql放弃使用索引全表扫描
  • 字段尽量加上默认值，比如0，’’
  • 尽量避免where子句中使用!=或<>操作符
  • 避免where子句中使用or来连接条件（若or前的条件有索引，后面的列没有索引，那么涉及的索引都不会被用到），可以用union all来代替
  • 避免在where条件中like以%打头
  • 避免在where子句中对字段进行表达式操作
  • 避免where子句中对=左边字段进行函数操作
• 开启mysql慢查询，配置慢查询时间，超过执行时间的语句将会记下来
• 需要事务处理的表使用innoDB，不需要的可以使用MyIsam，查询效率更高
• group by后面的字段会默认进行排序，如果不想有这个损耗，可以用order by null来避免
• 重复执行相同的sql语句，传入参数中条件最好用外部传入，这样可以使用到mysql自己的缓存，比如where条件里包含日期字段=curdate()，这个curdate()最好外部传入，否则mysql每次都会执行一遍

SQL慢的原因

• 偶尔慢：

  • 数据库进行数据增删改操作会进行缓存，不会立即刷新到磁盘，当数据一直频繁增删改时，可能导致刷盘的频率加快，在刷新的时候会造成停顿延迟

  • 遇到表锁或行锁，需要等待锁的释放，偶尔会慢

• 一直很慢

  • 字段加了索引，但没有命中索引，有可能是多字段联合索引，a,b,c的顺序，但条件只用到b，这时可能索引不起作用
  • 字段没加索引，数据量过大时造成全表查询，就慢了

SQL优化深入

• 大多数人知道EXPLAIN用来分析sql的索引命中情况，事实上我们可以进一步查看mysql优化过后的语句是什么样的，看右边图标

  ```
  
  EXPLAIN EXTENDED
  
  SELECT * from test where ... //中间放你的sql语句
  
  show WARNINGS;
  
  ```
  
  如果你用的是navcat，在结果2的message字段就可以看到优化后的语句
  

• 何时建联合索引？

  • 根据的是区分度，当字段A=x能搜出数万条记录，字段B=y能搜出数万条记录，而字段A=x，B=y同时作为条件能搜出数百条记录，说明这个区分度是比较明显的，此时就可以把AB字段作为联合索引；若是AB同时作为条件仍然搜出了数万条记录，说明区分度不明显，就不必要建联合索引了

进阶知识

• 聚簇索引

  • 索引B+ Tree的叶子节点存储了整行数据的是主键索引也叫聚簇索引
  • 索引B+ Tree的叶子节点存储了主键的值和索引覆盖的列的数据是非主键索引，也叫非聚簇索引

• 覆盖索引

  • 指查询语句的执行结果只从索引中就能够取得，不必从数据表二次查找，称之为索引覆盖，比如：见右侧

  • 表covering\_index\_sample中有一个普通索引 idx\_key1\_key2(key1,key2)。当我们通过SQL语句：select key2 from covering\_index\_sample where key1 = 'keytest';的时候，就可以通过覆盖索引查询，无需回表。
    

• MySQL 5.6做的重要优化

  • 索引下推（Index Condition Pushdown Optimization）

  • ```
    
    Index Condition Pushdown（索引下推） MySQL 5.6引入了索引下推优化，默认开启，使用SET optimizer_switch = 'index_condition_pushdown=off';可以将其关闭。官方文档中给的例子和解释如下： people表中（zipcode，lastname，firstname）构成一个索引
    
    
    
    SELECT * FROM people WHERE zipcode='95054' AND lastname LIKE '%etrunia%' AND address LIKE '%Main Street%';
    
    
    
    如果没有使用索引下推技术，则MySQL会通过zipcode='95054'从存储引擎中查询对应的数据，返回到MySQL服务端，然后MySQL服务端基于lastname LIKE '%etrunia%'和address LIKE '%Main Street%'来判断数据是否符合条件。 如果使用了索引下推技术，则MYSQL首先会返回符合zipcode='95054'的索引，然后根据lastname LIKE '%etrunia%'和address LIKE '%Main Street%'来判断索引是否符合条件。如果符合条件，则根据该索引来定位对应的数据，如果不符合，则直接reject掉。 有了索引下推优化，可以在有like条件查询的情况下，减少回表次数。
    
    ```
    

• 查询优化器

  • 优化器会对SQL执行进行执行计划的优化，首先根据搜索条件找到所有可能用到的索引
  • 计算全表扫描的代价
  • 计算使用不同索引执行查询的代价
  • 对比各种执行方案的代价，找出成本最低的那个

• InnerDB实现MVCC方式

  • 事务以排他锁的形式修改原始数据
  • 把修改前的数据存放undo log，通过回滚指针与主数据关联
  • 修改成功（commit）啥都不做，失败则恢复undo log中的数据（rollback）