Mysql：Sql优化

一：存储引擎

1.ISAM:通过索引查询数据

• 优点

1. 读取很快
2. 不占用大量内存

• 缺点

1. 不支持事务处理
2. 也不能够容错，如果硬盘奔溃了，数据无法恢复

• 注意

如果使用，必须经常备份所有实时数据

2.MyISAM：对ISAM的优化，5.5之前默认引擎

• 优点：

有ISAM的优点，并且加入了一种表格锁定机制，来优化多并发的读写操作

• 缺点：

有ISAM的缺点

3.InnoDB:5.之后的默认引擎

• 优点

会对每一条Sql默认包装事务，自动提交，它是聚集索引，必须要有主键，先查询到主键，在通过主键查询到数据，如果我们建表时没有设置主键，他会自动添加主键，只是我们看不到。

1. 加入了事务（ACID） 他的默认事务隔离级别：readcommitted
2. 外键的支持
3. 锁定机制的改进

• 缺点

读取慢

4.NBDCluster

主要用于Mysql集群式部署

5.操作引擎

• 查看库所支持的引擎

• 查看表的引擎

•  修改表的引擎

二：索引

• 优点：

1. 创建索引可以大大提升数据库的查询性能
2. 主键查询其实也是索引查询
3. 加速表与表之间的链接（为何外键必须是其他表的主键）

• 缺点

1. 创建索引和维护索引耗费数据库性能，随着数据量的增加，所耗费的时间也越长
2. 索引存储在物理空间，索引越多，所需内存越大
3. 当表中的数据需要增删该时，索引也要动态维护，这样就降低了数据的维护速度

• 什么样的字段适合创建索引？

1. 经常需要查询的列上
2. 主键
3. 外键
4. 需要经常排序的列上
5. where   f1  and  f2  需要f1，f2上同时常见索引才有效

• 什么样的字段不适合创建索引？

1. 查询中很少使用的列，如果创建索引，并不会提升查询速度，反而会降低系统维护速度，增加空间需求
2. 对于数量很少的列，这些列的取值很少
3. 对于定义为image，text，bit的列，这些列的取值要么很大，要么很少，
4. 当修改性能需求大于查询性能需求时，因为修改性能和查询性能是互相矛盾的

1.B-Tree索引

所有的索引节点都是按照平衡树的结构来存储，所有的索引节点都在叶节点

2.Full-Text索引

也是b-tree存储，用来解决like查询低效的问题

3.创建与删除索引

• .普通索引（index）

 

创建索引

1长索引

 

2.短索引 

 

 查看索引

删除索引

• 直接索引（unique index）

 该索引不能重复

•  全文索引（fulltext index）

该索引仅用于MyISAM引擎所创建的表

•  组合索引（最左索引）

当查询条件既有p_name又有p_age时，走该索引，只有 p_name，没有p_age是也走该索引，只有p_age没有p_name时，不走该索引

4.Mysql索引优化

1. 索引不会包含null的列（所以设计表时，列的默认值不要为null）
2. 使用短索引（短索引不仅可以提升查询速度，还可以节省磁盘空间）
3. 索引列排序：where子句如果已经使用该索引，那么order by 中若继续使用该索引，则索引不会在order by中生效，需要另外创建索引
4. like语句操作：一般不要使用like，如果非用不可，like‘%aa%’不会走索引查询，like‘aa%’可以使用索引
5. 不要在索引列上进行运算：会导致索引失效而进行全表扫描

• 总结：

Mysql只对< ,>,<=,>=,=. between，and, in以及like（不以%或_开头）才会使用索引，而且每张表理论上最多16个索引

5.Sql优化

1. 尽量避免全表扫表，在where及order by 所在的列上建立索引
2. 尽量避免，where子句中避免null判断，会导致引擎放弃索引，进行全表扫描，可以设置默认值为0
3. 尽量避免，where子句中避免使用<>,!=，会导致引擎放弃索引，进行全表扫描
4. 尽量避免，where子句中避免使用or，会导致引擎放弃索引，进行全表扫描,
5. 
6. 尽量避免，where子句中避免使用in或not in，会导致引擎放弃索引，进行全表扫描,对于连续的数值in（1.2.3）可以使用between 1 and 3
7.  尽量避免like'%a%',会导致引擎放弃索引，进行全表扫描,
8. 尽量避免，where子句中对列进行值运算，会导致引擎放弃索引，进行全表扫描,如：where a/2=50应改为where a=50*2
9. 尽量避免，where子句中对列进行函数运算，例如where substring（name，1，3）=‘abc’应改为like ‘abc%’
10. 尽量使用exists代替in
11.  一张表索引最好不要超过6个
12. 创建表时，尽量使用数字类型，不要使用varchar，如果该列会被设为索引
13. 尽量使用varchar代替char
14. 不要使用select *，除非全表查询