MySql-性能优化

建索引的几大原则

1. 最左前缀匹配原则，非常重要的原则，mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配，比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。

2. =和in可以乱序，比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序，mysql的查询优化器会帮你优化成索引可以识别的形式。

3. 尽量选择区分度高的列作为索引，区分度的公式是count(distinct col)/count(*)，表示字段不重复的比例，比例越大我们扫描的记录数越少，唯一键的区分度是1，而一些状态、性别字段可能在大数据面前区分度就是0，那可能有人会问，这个比例有什么经验值吗？使用场景不同，这个值也很难确定，一般需要join的字段我们都要求是0.1以上，即平均1条扫描10条记录。

4. 索引列不能参与计算，保持列“干净”，比如from_unixtime(create_time) = ’2014-05-29’就不能使用到索引，原因很简单，b+树中存的都是数据表中的字段值，但进行检索时，需要把所有元素都应用函数才能比较，显然成本太大。所以语句应该写成create_time = unix_timestamp(’2014-05-29’)。

5. 尽量的扩展索引，不要新建索引。比如表中已经有a的索引，现在要加(a,b)的索引，那么只需要修改原来的索引即可。

使用explain命令

关于explain命令相信大家并不陌生，具体用法和字段含义可以参考官网explain-output，这里需要强调rows是核心指标，绝大部分rows小的语句执行一定很快（有例外，下面会讲到）。所以优化语句基本上都是在优化rows。

慢查询优化基本步骤

1. 先运行看看是否真的很慢，注意设置SQL_NO_CACHE

2. where条件单表查，锁定最小返回记录表。这句话的意思是把查询语句的where都应用到表中返回的记录数最小的表开始查起，单表每个字段分别查询，看哪个字段的区分度最高

3. explain查看执行计划，是否与1预期一致（从锁定记录较少的表开始查询）

4. order by limit 形式的sql语句让排序的表优先查

5. 了解业务方使用场景

6. 加索引时参照建索引的几大原则

7. 观察结果，不符合预期继续从1分析

引擎介绍

	Innodb	Myisam
存储文件	.frm 表定义文件 .ibd 数据文件	.ibd 数据文件 .frm 表定义文件 .myd 数据文件 .myi 索引文件
锁	表锁、行锁	表锁
事务	ACID	不支持
CRDU	读、写	读多
count	扫表	专门存储的地方
索引结构	B+ Tree	B+ Tree

提高性能

索引

种类

1. B-Tree索引

2. Hash索引

3. Fulltext索引

4. R-Tree索引

优点

1. 提高检索效率

2. 降低排序成本（排序分组主要消耗的是我们的内存和CPU资源）

缺点

1. 更新索引的IO量

2. 调整索引所致的计算量

3. 占用存储空间

是否创建索引

1. 较频繁的作为查询条件的字段应该创建索引

2. 唯一性太差的字段不适合单独创建索引

3. 更新非常频繁的字段不适合创建索引

4. 不会出现在where子句中的字段不该创建索引

锁

行锁

• 优点：粒度小

• 缺点:获取、释放所做的工作更多、容易发生死锁

• 实现Innodb

  1.共享锁

  2.排他锁： 通过在指向数据记录的第一个索引键之前和最后一个索引键之后的空域空间上标记锁定信息来实现的（间接锁）

  3.锁优化：

  尽可能让所有的数据检索都通过索引来完成

  合理设计索引

  减少基于范围的数据是检索顾虑条件

  尽量控制事务的大小

  业务允许的情况下，尽量使用较低级别的事务隔离

表锁

• 优点：实现逻辑简单、获取、释放快、避免死锁

• 缺点：粒度太大、并发不够搞

• 实现：MyISAM

page-level

• 介于row和table之间

• 实现BerkeleyDB

join

• 有序

• 无序

1. 1. 排序字段和指针在Sort buffer排序，然后用指针去取数据

   2. 排序字段和所有数据全部取出->排序字段+指针Sort buffer排序（其他数据放到内存中）-指针到内存里取数据然后返回（节省IO、耗内存、空间换时间）

• 优化

1. 1. 索引顺序一致的话不需要再排序

   2. 加大max_length_for_sort_data 从而使用第二种排序方法（排序只针对需要排序的字段）

   3. 内存不充足时去掉必要的返回字段

   4. 增多sort_buffer_size，减少在排序过程中需要排序的数据进行分段