优化sql

1.合适的数据类型

1.使用可存下数据的最小的数据类型，整型 < date,time < char,varchar < blob。
2.使用简单的数据类型，整型比字符处理开销更小，因为字符串的比较更复杂。如，int类型存储时间类型，bigint类型转ip函数。
3.使用合理的字段属性长度，固定长度的表会更快。使用enum、char而不是varchar。
4.尽可能使用not null定义字段。
5.尽量少用text，非用不可最好分表。

2.索引

索引是对数据库表中一个或多个列的值进行排序的数据结构，以协助快速查询、更新数据库表中数据，要记住的关键点是索引包含一个表中列的值，并且这些值存储在一个数据结构中，请记住记住这一点：索引是一种数据结构 。

索引是怎么提升性能的？
使用索引的全部意义就是通过缩小一张表中需要查询的记录/行的数目来加快搜索的速度。
因为索引基本上是用来存储列值的数据结构，这使查找这些列值更加快速。如果索引使用最常用的数据结构-B-Tree-那么其中的数据是有序的。有序的列值可以极大的提升性能

使用数据库索引会有什么代价？
使用数据库索引有什么缺点呢？其一，索引会占用空间 - 你的表越大，索引占用的空间越大。其二，性能损失（主要值更新操作），当你在表中添加、删除或者更新行数据的时候， 在索引中也会有相同的操作。记住：建立在某列（或多列）索引需要保存该列最新的数据。

基本原则是只如果表中某列在查询过程中使用的非常频繁，那就在该列上创建索引。
1.不用外键，尽量不用UNIQUE，由程序保证约束。
2.字符字段最好不要做主键。
3.使用多列索引时主意顺序和查询条件保持一致，同时删除不必要的单列索引。

简言之就是使用合适的数据类型，选择合适的索引

3.优化建议

1.查询SQL尽量不要使用select *，而是select具体字段。

2.优化嵌套查询：使用连接（join）来代替子查询。

3.拆分大的delete或insert语句。

4.可通过开启慢查询日志来找出较慢的sql。

5.不做列运算：select id where age + 1 = 10，任何对列的操作都将导致表扫描，它包括数据库教程函数、计算表达式等等，查询时要尽可能将操作移至等号右边。

6.sql语句尽可能简单：一条sql只能在一个cpu运算；大语句拆小语句，减少锁时间；一条大sql可以堵死整个库。

7.or改写成in：or的效率是n级别，in的效率是log(n)级别，in的个数建议控制在200以内。

8.尽量避免在索引列上使用mysql内置函数，不用函数和触发器，在应用程序实现。
反例：Date_ADD(loginTime,Interval 7 DAY) >=now()
正例：loginTime >= Date_ADD(NOW(),INTERVAL - 7 DAY);

9.避免%xxx式查询，例子：like ‘%123’ 索引失效；like ‘123%’ 走索引。

10.尽量用union all替换 union。

11.使用同类型进行比较，比如用’123’和’123’比，123和123比。

12.尽量避免在where子句中使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描。

13.对于连续数值，使用between不用in：select id from t where num between 1 and 5。

14.distinct 关键字一般用来过滤重复记录，以返回不重复的记录。在查询一个字段或者很少字段的情况下使用时，给查询带来优化效果。但是在字段很多的时候使用，却会大大降低查询效率。

15.Inner join 、left join、right join，优先使用Inner join，如果是left join，左边表结果尽量小。

16.列表数据不要拿全表，要使用limit来分页，每页数量也不要太大，使用limit对查询结果的记录进行限定，limit 当偏移量特别大的时候，查询效率低下。
方案一 ：返回上次查询的最大记录(偏移量)
方案二：order by + 索引

如果知道查询结果只有一条或者只要最大/最小一条记录，建议用limit 1，因为limit的存在主要就是为了防止全表扫描。

4.解决思路

1、查看slowlog，分析slowlog，分析出查询慢的语句。
2、按照一定优先级，进行一个一个的排查所有慢语句。
3、分析top sql，进行explain调试，查看语句执行时间。
4、调整索引或语句本身。

5.explain字段的含义

id : 表示SQL执行的顺序的标识,SQL从大到小的执行
select_type：表示查询中每个select子句的类型
table：显示这一行的数据是关于哪张表的，有时不是真实的表名字
type：表示MySQL在表中找到所需行的方式，又称“访问类型”。常用的类型有：ALL, index, range, ref, eq_ref, const, system, NULL（从左到右，性能从差到好）
possible_keys：指出MySQL能使用哪个索引在表中找到记录，查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询使用
Key：key列显示MySQL实际决定使用的键（索引），如果没有选择索引，键是NULL。
key_len：表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度（key_len显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出的）
ref：表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值
rows：表示MySQL根据表统计信息及索引选用情况，估算的找到所需的记录所需要读取的行数，理论上行数越少，查询性能越好
Extra：该列包含MySQL解决查询的详细信息

查看优化后的语句 EXPLAIN EXTENDED sql。

原创不易，欢迎点赞分享