《高性能MySQL》阅读笔记

基础知识

事务的特性：

原子性：指处于同一个事务中的多条语句是不可分割的。

一致性：事务必须使数据库从一个一致性状态变换到另外一个一致性状态。比如转账，转账前两个账户余额之和为2k，转账之后也应该是2K。

隔离性：指多线程环境下，一个线程中的事务不能被其他线程中的事务打扰

持久性：事务一旦提交，就应该被永久保存起来。

四种事务隔离级别：读未提交，读已提交，重复读，可串行化

不可重复读：所谓的虚读，也就是大家经常说的不可重复读，是指在数据库访问中，一个事务范围内两个相同的查询却返回了不同数据。

幻读：并不是说两次读取获取的结果集不同，幻读侧重的方面是某一次的 select 操作得到的结果所表征的数据状态无法支撑后续的业务操作。更为具体一些：select 某记录是否存在，不存在，准备插入此记录，但执行 insert 时发现此记录已存在，无法插入，此时就发生了幻读。

（具体详解：https://segmentfault.com/a/1190000016566788）

• mysql如何实现避免幻读

  • 在快照读读情况下，mysql通过mvcc来避免幻读。
  • 在当前读读情况下，mysql通过next-key来避免幻读

• 什么是mvcc

  mvcc全称是multi version concurrent control（多版本并发控制）。mysql把每个操作都定义成一个事务，每开启一个事务，系统的事务版本号自动递增。每行记录都有两个隐藏列：创建版本号和删除版本号

  • select：事务每次只能读到创建版本号小于等于此次系统版本号的记录，同时行的删除版本号不存在或者大于当前事务的版本号。

  • update：插入一条新记录，并把当前系统版本号作为行记录的版本号，同时保存当前系统版本号到原有的行作为删除版本号。

  • delete：把当前系统版本号作为行记录的删除版本号

  • insert：把当前系统版本号作为行记录的版本号

• 什么是next-key锁（临键锁）

  可以简单的理解为X锁+GAP锁

• 什么是快照读和当前读

  • 快照读：简单的select操作，属于快照读，不加锁。

    • select * from table where ?;

  • 当前读：特殊的读操作，插入/更新/删除操作，属于当前读，需要加锁。

    • select * from table where ? lock in share mode;
    • select * from table where ? for update;
    • insert into table values (…);
    • update table set ? where ?;

MySQL的默认隔离级别是重复读（即一个事务内可以，可以重复读取其他事务，已提交的数据）

大多数MySql事务引擎是使用MVCC(多版本并发控制)和行加锁机制关联使用，使用 MMVC机制 实现可重复读

MVCC只工作在：读已提交和重复读两个隔离级别

MySQL这3种锁的特性可大致归纳如下。

开销、加锁速度、死锁、粒度、并发性能

          表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高,并发度最低。

          行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低,并发度也最高。

          页面锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般。

benchmark简单测试函数的执行效率

select benchmark(100000,MD5('hello wlord'));

select benchmark(100000,SHA1('hello wlord'));

需要了解视图的内在实现过程才能更好的达到优化的作用

视图通常有临时表算法和合并算法两种实现方式

explain select * from <view_name>

结果是derived时表示的是临时表算法

索引

创建索引的原则有哪些？

1最左前缀匹配原则，非常重要的原则，mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配，比如建立(a,b,c,d)顺序的索引那么a = 1 and b = 2 and c > 3 and d = 4 只能用到（a,b,c）索引，a,b的顺序可以任意调整。
2.=和in可以乱序，比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序，mysql的查询优化器会帮你优化成索引可以识别的形式
3.在大表的常用且值重复几率小的字段上创建索引（B+树二分查找的特性决定的）
4.索引列不能参与计算，保持列“干净”，比如from_unixtime(create_time) = ’2014-05-29’就不能使用到索引，原因很简单，b+树中存的都是数据表中的字段值，但进行检索时，需要把所有元素都应用函数才能比较，显然成本太大。所以语句应该写成create_time = unix_timestamp(’2014-05-29’);
5.尽量的扩展索引，不要新建索引。比如表中已经有a的索引，现在要加(a,b)的索引，那么只需要修改原来的索引即可

6.索引不会包含NULL列,如果列中包含NULL值都将不会被包含在索引中,复合索引中如果有一列含有NULL值那么这个组合索引都将失效,一般需要给默认值0或者 ' '字符串。

什么情况下不创建索引？

1.查询中很少使用到的列 不应该创建索引,如果建立了索引然而还会降低mysql的性能和增大了空间需求。

2.很少数据的列也不应该建立索引,比如 一个性别字段 0或者1,在查询中,结果集的数据占了表中数据行的比例比较大,mysql需要扫描的行数很多,增加索引,并不能提高效率。

3.定义为text和image和bit数据类型的列不应该增加索引。

4.当表的修改(UPDATE,INSERT,DELETE)操作远远大于检索(SELECT)操作时不应该创建索引,这两个操作是互斥的关系

MySQL中同时存在单独索引和联合索引,优先使用哪个？

优先使用单独索引，单独索引里有重复项时才用到联合索引。能用单独索引时，MySQL会认为没必要用到组合索引，

一个sql语句只能用一个索引

百万级数据库查询优化策略

 1使用union来避免大数据表行的顺序存取如：

select * from order where (customer_num=104 and order_num>1001) or order_num=108

虽然customer_num和order_num上建有索引但是上面的语句还是使用顺序存储来扫描整张表，因为这个语句要检索的是分离行的集合

优化为：

select * from order where (customer_num=104 and order_num>1001)

union

select * from order whereorder_num=108

2、避免相关子查询（子查询会创建临时表）

3、避免困难的正则表达式否则索引失效

4、where的第一个查询条件要在复合索引的列内否则索引失效

5、where条件里不要有!=和 <>否则索引失效

6、in和not in要慎用尽量用between代替否则索引失效

7、where条件里不要有null的判断否则索引失效

8、尽量用数字型字段少用字符型

9、创建临时表时如果插入的数据过多可以用select into代替create table

10、尽量使用int代替字符类型的

11、or语句会转化为union处理，复合索引的列用or条件查询无法走索引，两个单索引的列用or查可以走索引

慢查询优化基本步骤

0.先运行看看是否真的很慢，注意设置SQL_NO_CACHE
1.where条件单表查，锁定最小返回记录表。这句话的意思是把查询语句的where都应用到表中返回的记录数最小的表开始查起，单表每个字段分别查询，看哪个字段的区分度最高
2.explain查看执行计划(explain的用法MySQL性能分析及explain的使用 - 51CTO.COM），是否与1预期一致（从锁定记录较少的表开始查询）
3.order by limit 形式的sql语句让排序的表优先查
4.了解业务方使用场景
5.加索引时参照建索引的几大原则
6.观察结果，不符合预期继续从0分析

千万级数据库插入速度和读取速度的调整

提高数据库插入性能中心思想：尽量将数据一次性写入到Data File和减少数据库的checkpoint 操作。这次修改了下面四个配置项：

1）将 innodb_flush_log_at_trx_commit 配置设定为0；

2）将 innodb_autoextend_increment 配置由于默认8M 调整到 128M

3）将 innodb_log_buffer_size 配置由于默认1M 调整到 16M

4）将 innodb_log_file_size 配置由于默认 8M 调整到 128M

提升数据库读取速度，从数据库层面上读取速度提升主要由于几点：简化SQL、加索引和分区； 经过检查程序SQL已经是最简单，查询条件上已经增加索引。我们只能用武器：表分区。分区类型：RANGE分区，LIST分区，HASH分区，KEY分区

详情可以查看

http://blog.itpub.net/23490154/viewspace-1063390/

https://my.oschina.net/ydsakyclguozi/blog/393583

事务的四个特征

A:原子性(Atomicity)

       事务是数据库的逻辑工作单位，事务中包括的诸操作要么全做，要么全不做。
B:一致性(Consistency)
       事务执行的结果必须是使数据库从一个一致性状态变到另一个一致性状态。一致性与原子性是密切相关的。
C:隔离性(Isolation)
      一个事务的执行不能被其他事务干扰。
D:持续性/永久性(Durability)
      一个事务一旦提交，它对数据库中数据的改变就应该是永久性的。

 

MySQLl各种索引区别

普通索引(INDEX)：最基本的索引，没有任何限制
唯一索引(UNIQUE)：与"普通索引"类似，不同的就是：索引列的值必须唯一，但允许有空值。
主键索引(PRIMARY)：它 是一种特殊的唯一索引，不允许有空值。
全文索引(FULLTEXT )：仅可用于 MyISAM 表， 用于在一篇文章中，检索文本信息的, 针对较大的数据，生成全文索引很耗时好空间。
组合索引：为了更多的提高mysql效率可建立组合索引，遵循”最左前缀“原则。

聚簇索引和非聚簇索引

聚簇索引（主键索引）是按照数据存放的物理位置为顺序的一般来说，将通过主键作为聚簇索引的索引列，也就是通过主键聚集数据。聚簇索引对于范围查询的效率很高，因为其数据是按照大小排列的。但是更新代价比较高，如果更新了行的聚簇索引列，就需要将数据移动到相应的位置。这可能因为要插入的页已满而导致“页分裂”。

非聚簇索引（又叫二级索引）非聚簇索引，又叫二级索引。二级索引的叶子节点中保存的不是指向行的物理指针，而是行的主键值。当通过二级索引查找行，存储引擎需要在二级索引中找到相应的叶子节点，获得行的主键值，然后使用主键去聚簇索引中查找数据行，这需要两次B-Tree查找。

InnoDB里的聚簇索引的应用：

每个InnoDB表都有一个称之为聚簇索引(clustered index)的特殊索引，存储记录行数据。通常，聚簇索引和主索引是近义的。

1.当在表上定义一个主键时, InnoDB把它当聚簇索引用。

2.如果没为表定义主键，mysql定位所有索引列都为NOT NULL的第一个唯一索引，并把它当聚簇索引使用

3.如果表没有主键或合适的唯一索引，InnoDB会在某个包含row ID值的合成列上生成一个隐藏的聚簇索引。记录行按表中InnoDB赋予行的row ID排序。当有新行被插入时会自动增加，所以，按row ID排序的行物理上为按插入顺序排序。 

参考文章

InnoDB中的聚簇索引和非聚簇索引_付石头的博客-CSDN博客_innodb的聚簇索引和非聚簇索引