关于数据库sql优化，个人学习的笔记

关于数据库sql优化，简略版笔记

核心要关注的东西

通过explain观察低效率的sql语句执行情况，可以通过添加索引减小扫描行数，提高数据库访问速度。
例：explain select count（orderid） from orders a,user b where a.userid=b.userid and a.goodid=1 and b.userid=1;

可通过Create index ind_goodid on orders(goodid);

添加索引，再执行例句，便可发现type（表连接的类型）得到优化

索引的使用

不使用索引的情况下，查询的行数会很多，使用索引会减少查询行数，提高数据库访问速度

在使用like时，%放在第一位，将不使用索引，%不放在第一位的时候使用索引

如果对大的文本进行搜索，将使用全文索引而不用like ‘%…%’

当索引搜索比全表搜索慢，系统会不使用索引

当使用or连接条件时，第一个条件有索引，但第二个条件没索引的时候，将不使用索引

当使用复合索引时，字段作为条件，但该字段并不是复合索引的第一列，索引也将不被使用

通过show status like ‘Handler_read%’;查看索引使用情况，关键点为handler_read_key，他代表一个行被索引读的次数，他的值越高，代表着索引使用的理想，数据库访问速度提高

handler_read_rnd_next代表下一条数据查询情况，当这个值很高的时候，代表你的表索引不正确或写入的查询没有利用索引

简单实用的优化方法

定期分析表和检查表
分析表可以使系统得到准确的消息 analyze table [tablename];

检查表可以检查一个或多个表是否有错误 check table [tablename];

定期优化表
如果表中含有可变长度行例如varchar，blob，text类型的列，则应该使用optimize table [tablename]对表进行优化，

他可以使表中的空间碎片进行合并，消除由于删除或者更新造成的空间浪费。

注意的是，optimize 的时候，表会被锁定，因此需要在数据库不繁忙时进行碎片整理，并且只对特定表，次数也不需要很多，一周一次或者一个月一次就行

常用的sql优化

对于MyISAM表可以使用DISABLE KEYS和ENABLE KEYS来提高在使用load向一张有数据的表中插入大量数据使用时运行速度，例如
load data infile ‘/home/msql/test1.txt’into table test1; 运行时间为115秒
而使用 alert table test1 disable keys; 运行时间为0秒
再使用load data infile ‘/home/msql/test1.txt’into table test1; 运行时间为6秒
再使用 alert table test1 enable keys；运行时间为12秒 整体运行时间为18秒，大大缩减了运行时间。
而上述方法在innodb中是不能提高导入效率的，在innodb中可以使用以下方法：
文件按照表主键顺序存储，提高导入速率
在导入数据前，关闭唯一性校验，结束后打开，可提高导入速率
执行set unique_checks=0
如果应用使用自动提交的方式，在导入之前关闭自动提交，结束后再打开，可提高导入速率。
执行 set autocommit =0

优化insert

使用insert时，若一个客户需要插入多条数据，推荐采用
insert into test values（1，2），（3，4）…的方式
可以减小客户端与数据库之间的连接、关闭的消耗
如过不同客户插入多行数据，可以使用insert delayed提高速度。
将索引文件和数据文件放在不同的磁盘上存放（建表中的选项）
对于myisam表，进行批量插入的时候，可以增加bulk_insert_buffer_size变量值的方法来提高速度
当从一个文本文件装载一个表时，使用load data infile 通常比使用insert快20倍

优化group by

当使用group by时，explain后发现extra中有using filesort，可以在语句最后面加上order by null禁止排序，因为using filesort非常耗费时间

优化order by

可以使用一个索引来满足order by，但是当order by字段混合desc，asc使用，用于查询的关键字和order by中所使用的不同，对不同的关键字使用order by就不使用索引

优化嵌套查询

对于子查询，当使用连接查询（join）代替嵌套查询时，速度会快得多，原因是使用连接查询不需要创建临时表来完成这个逻辑上需要两个步骤的查询工作

优化or
在使用or时，考虑前后条件是否都具有索引，可以通过show index from [tablename]查看索引情况，如果有一个条件没有独立索引或者都没有索引，则考虑增加索引

使用sql提示

在查询语句中表名字的后面加上use index（indexname）,以此来提供参考的索引列表，可以让mysql不再考虑其他索引

如果不想使用某个索引可以在表名字后面加上ignore index（indexname）

如果想要强制使用某个索引可在表名字后面加上force index（indexname）

重点是经常使用explain 查看sql的状态，有助于sql的优化

本篇笔记来源于在社区的老前辈处学习得来，来前辈住在
https://blog.csdn.net/wildpen/article/details/81335777
想学习的小伙伴可以去看看