mysql之sql优化

mysql 数据库是被广泛应用的关系型数据库，其体积小、支持多处理器、开源并免费的特性使其在 Internet 中小型网站中的使用率尤其高。　　　　

一、库表设计

1.1 引擎选择

存储引擎就是指表的类型以及表在计算机上的存储方式。存储引擎的概念是MySQL的特点，Oracle中没有专门的存储引擎的概念，Oracle有OLTP和OLAP模式的区分。不同的存储引擎决定了MySQL数据库中的表可以用不同的方式来存储。我们可以根据数据的特点来选择不同的存储引擎。

mysql 常用的存储引擎包括 MYISAM、Innodb 和 Memory，其中各自的特点如下：

1.MYISAM（表锁定） : 优势在于占用空间小，处理速度快。缺点是不支持事务的完整性和并发性。

2.Innodb（行锁定）：优势在于提供了良好的事务处理、崩溃修复能力和并发控制。缺点是读写效率较差，占用的数据空间相对较大。

3.Memory（表锁定）: 存储在内存当中，速度快，但安全性不高。

1.2 分表设计

在数据库表使用过程中，为了减小数据库服务器的负担、缩短查询时间，常常会考虑做分表设计。

分表分两种：纵向分表（根据活跃度分表、根据重要性等，划分为多个不同结构的表，解决列过长问题）

横向分表（根据时间等特定规则，划分为同样结构的不同表，解决表行数过大问题）。

1.3 索引设计

建立索引的目的是加快对表中记录的查找或排序，索引也并非越多越好，因为创建索引是要付出代价的：一是增加了数据库的存储空间，二是在插入和修改数据时要花费较多的时间维护索引。

索引数据结构：

在MySQL中，索引属于存储引擎级别的概念，不同存储引擎对索引的实现方式是不同的。

1.MyISAM引擎使用B+Tree作为索引结构，叶节点的data域存放的是数据记录的地址。

MyISAM索引文件和数据文件是分离的，索引文件仅保存数据记录的地址。（非聚集索引）

因此，MyISAM中索引检索的算法为首先按照B+Tree搜索算法搜索索引，如果指定的Key存在，则取出其data域的值，然后以data域的值为地址，读取相应数据记录。

2.InnoDB也使用B+Tree作为索引结构，叶节点data域保存了完整的数据记录。

数据文件本身就是主索引（聚集索引）

InnoDB也使用B+Tree作为索引结构

索引适用范围：

哪些情况需要创建索引

频繁作为查询条件的字段
查询中与其他表关联的字段，外键关系建立索引
经常出现在关键字order by、group by、distinct后面的字段，如果建立的是复合索引，索引的字段顺序要和这些关键字后面的字段顺序一致，否则索引不会被使用。
对于需要在指定范围内的快速或频繁查询的数据列(如时间)

哪些情况不要创建索引

　　1.表记录太少

　　2.经常增删改的表。提高了查询速度，同时却会降低更新表的速度

　　3.注意，如果某个数据列包含许多重复的内容，为它建立索引就没有太大的实际效果。

索引类型：

1.普通索引：
2.唯一索引：索引列的值必须唯一，但允许有空值。
3.主键索引：是一种特殊的唯一索引，不允许有空值。
4.组合索引：指多个字段上创建的索引，只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用。使用组合索引时遵循最左前缀集合
5.全文索引：主要用来查找文本中的关键字，而不是直接与索引中的值相比较。

二、慢 SQL 问题

2.1 导致慢 SQL 的原因

1. SQL编写问题
2. 锁
3. 业务实例相互干绕对 IO/CPU 资源争用
4. 服务器硬件
5. MYSQL BUG

2.2 由 SQL 编写导致的慢 SQL 优化

1.避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引。

2.尽量避免在 where 子句中对列进行运算--- 如函数转换，算术运算

3.避免在 where 子句中对字段进行 null 值判断

4.避免在 where 子句中使用 != 或 <> 操作符

5.避免在 where 子句中使用 or 来连接条件，如果一个字段有索引，一个字段没有索引，将导致引擎放弃使用索引而进行全表扫描

7.join时，使用小结果集驱动大结果集

from table_a a  left join table_b b on a.id = b.id  where a.id > 100 and b.id < 200   
优化后：
from (select * from table_a  where id > 100  ) a  left join(select * from table_b  where id < 200  )b  on a.id = b.id

8.仅列出需要查询的字段

9.删除表所有记录请用 truncate，不要用 delete

三、分析sql语句工具

1.mysql explain--使用explain分析sql语句

2.mysql profile--使用profiling分析sql语句时间开销

3.mysqldumpslow