sql优化

sql优化

索引： 索引是一个单独的、存储在磁盘上的数据库结构，它们包含着对数据库表里的所有记录的引用指针。使用索引用于快速找出某个或多个列中有一特定值的行，所有Mysql列类型都可以被索引。

索引优点： 1.创建唯一索引，可以保证数据库表中每一行数据的唯一性
　　　　　２.可以大大加快数据的查询速度，这是创建索引的主要原因
　　　　　3.可以加速表与表之间的连接
　　　　　4.使用分组和排序查询时，可以显著减少查询中分组和排序的时间
索引缺点： 1.创建和维护索引需要消耗时间，随着数据量的增加耗费的时间也会增加
　　　　　２.索引需要占据磁盘空间，除数据表占数据空间之外，每一个索引还要占用一定的物理空间
　　　　　3.每当对表中数据进行增加、修改、删除时，索引也要动态维护，这样降低数据维护速度

索引分类：

1.普通索引： mysql中基本索引类型，允许在定义索引的列中插入重复值和空值
2.唯一索引：索引列值必须唯一，但允许有空值。
3.主键索引：特殊的唯一索引，不允许有空值
4.组合索引：在多个字段组合上创建的索引，最左前缀原则
5.全文索引：在索引值的列上支持全文查找，允许重复值和空值。可以在char、varchar、text类型的列上创建，只有MyISAM存储引擎支持
6.空间索引：对空间数据类型的字段建立，不允许空值，只有MyISAM存储引擎支持

索引分类原则：

1.索引并非越多越好
2.避免对经常更新的表建立过多的索引，并且索引中的列尽可能少。对经常查询的字段建立索引，但要避免添加不必要的字段
3.数据量小的表最好不要使用索引，由于数据少，查询花费的时间可能比遍历索引的时间还要短
4.表达式中经常用到的不同值较多的列建立索引，不同值很少的列不要建立索引，如 性别 列
5.当唯一性是数据本身特征时，指定唯一索引
6.在频繁分组和排序的列上建立索引，如果待排序的列有多个，可以在这些列上建立组合索引
7.对短小的值建立索引
8.为字符串前缀编索引。如果一个字符串通过前几位就可以确定唯一性，可以给出前缀长度
9.使用“=”或“<=>”进行精确匹配，散列索引极快
10.B树索引："<"、"<="、"="、">="、">"、"<>"、"!="和between进行的精确比较或范围比较很有效率，还可以用like模糊查询

索引相关sql语句：

1.建立索引：alter table 表名 add （索引类型）index 索引名 (要建立索引的字段) 或者
　　　　　　create (索引类型) index 索引名 on 表名（建立索引的字段）
２.查看索引：show index from 表名
3.查看查询语句是否使用索引：查询语句前加 explain
4.删除索引：alert table 表名 drop index 索引名 或者 drop index 索引名 on 表名

sql查询不使用索引的情况：

使用的： group by、order by 、and
不使用的 ：in、not in 、between and 、or 、like、!=、is null

除了or,is null以外，只要查询语句后面有and使用索引，查询就使用索引

or有人说两边都有索引就使用索引、like有人说如果是%不在第一位就使用索引，我试过都不使用索引

优化表结构：1.将字段很多的表分解成多个表：将使用频率低的表分解出来
　　　　　　２.增加中间表：对需要经常联合查询的表，可以建立中间表
　　　　　　3.增加冗余字段：根据实际情况分析，对于经常查询的字段可以将它放入另一张表中
　　　　　　4.优化插入记录的速度：MyISAM引擎
　　　　　　　　　　　　　　　　（1）禁用索引：插入大量记录时可以禁用索引。
　　　　　　　　　　　　　　　　　禁用索引语句：alert table 索引名 disable keys
　　　　　　　　　　　　　　　　　开启索引语句：alert table 索引名 enable keys
　　　　　　　　　　　　　　　　　空表批量导入数据，MyISAM引擎不需要禁用索引，它是在导入数据之后才建立索引的
　　　　　　　　　　　　　　　　　（2）禁用唯一性检查
　　　　　　　　　　　　　　　　　禁用唯一性检查语句：set unique_checks = 0;
　　　　　　　　　　　　　　　　　开启唯一性检查语句：set unique_checks = 1;
　　　　　　　　　　　　　　　　　3.使用批量插入
　　　　　　　　　　　　　　　　　InnoDB引擎
　　　　　　　　　　　　　　　　　（1）禁用唯一性检查
　　　　　　　　　　　　　　　　　（2）禁用外键检查
　　　　　　　　　　　　　　　　　（3）禁止自动提交

存储引擎：

MyISAM： 数据表的数据行在数据文件里，索引值在索引文件里，索引用b树
InnoDB： 使用一个表空间，在这里管理着数据库的数据和索引的存储，索引用b树