mysql优化笔记

爱死你了

　　        默认情况下，mysql将执行时间超过10s的sql语句称为慢查询。一般情况下，普通的（字段数量一般的）表没有4000万条记录的话，对它进行crub都不会超过10s。当然这个慢查询的时间上的规定我们是可以（session级别上）更改的。要知道的是:当重启mysql的时候慢查询的时间标准又会恢复到10s,因此有需要再修改！！
　　                           show variables like ‘long_query_time’;//显示慢查询
　　                          set long_query_time=1;修改慢查询的时间标准为1s
　　                          show status like ‘slow_queris’;显示慢查询的sql的数量（但是知道数量是没多大意义的，还应该知道具体是哪个sql语句慢）
　　         delimiter  +（任意）字符 :指的是将sql命令结束符更改为“字符”。这一般是在存储过程中会用到，因为在存储过程中也会有分号，而mysql引擎遇到分号就会停止执行，因此在执行存储过程前需要将默认的分号修改为其他符号，以确保存储过程的正常执行！
　　 
l      如何在一个大项目中定位到执行效率很低的sql语句（定位慢查询）？
1.      先将慢查询的定义的执行时间缩短，例如修改为1（默认是10）,
2.      启动mysql的日志记录慢查询（默认是不启动的！），
3.      然后再执行sql即可;
      指定表的索引的查询的三种方法：
1.      desc table_name;
2.      explain table_name;
3.      show indexes from table_name \G;（查询结果是具有索引的每一列各自的索引信息）
4.      show keys from table_name \G;(仅用于主键)
l      BTREE索引的二叉树算法示意图：
　　

    新理解：mysql中，当我们输入“create index ind_id on test(id)”让DBMS执行时，DBMS先将“列id+磁盘位置”取出，排序后存放于一个.MYI后缀的文件中。当我们的查询要用到索引列时，会在.MYI文件中按照二叉树算法找到对应的列值和磁盘位置（然后回表）。
      全文索引：
　　1. mysql自带的一种索引，仅仅处理英文。如果要处理中文的话，就得使用sphinx(coreSeek) 索引。 2.停止词：对一篇英文文章的每个单词建立全文索引，其工作量是无穷大的，因此mysql 中的全文索引仅仅对于mysql认为会是生僻词的单词建立索引，是故，我们的搜索字符串如果过于简短（少于4个字母）或者太常见（就是：在此文本中出现的次数非常高，频率超过50%，例如：a，I等，就被mysql认为是 停止词），我们就无法搜索得到！！   3.匹配度（或称 相关值）：搜索字符串和这文本的匹配程度有多高（并非是：在此文本中出现的概率是多少），4.要注意分隔符，mysql自带的全文索引对于字母，数字，下划线 外的字符都当作是分隔符，用以区分一个一个的单词方可建立索引，并且建立索引时会将之忽烈掉。5.全文索引只能在myisam引擎中使用
l  唯一索引：1.当列给加上unique约束时，就自动添加了唯一索引了！唯一索引也是BTREE索引！！2.当然也可以创建表过后再添加上去：create unique index ind_uni on table_name(column);
l  所有的索引删除都可以使用>
但是对于主键索引，则还可以：altertable table_name drop primary key;
l  如果真要修改索引：只能先删除之，再重建之！！
l  什么时候适合建索引？
答：1.频繁用于作为where子句条件的列。
l  什么时候不适合建立索引?
答：1. 不频繁用于作为where子句条件的列；2.频繁更改的列；3.列值重复率非常高的列；4.like字符串以%或_开头的时候；5.where子句中有or;  6.如果mysql估计到全文扫描效率会比索引高的话，就不会使用索引，例如：表中只有一行数据时，很明显全文索引的效率更高！！
l  一个非常细的盲区I:sql语句中的where子句中有or，而又想使用到索引，那么我们就可以得为where句中所用到的所有字段都建立索引！！因此，我们项目中尽量避免使用or;
l  一个非常细的盲区II：sql语句中，模糊查询（like）要注意%和_的位置！！！
Select * from>一班’;-------à%或_放在字符串前面，是无法使用到索引的；但是%和_放置在字符串的其他位置就可以！！
但是有时候业务要求必须得将%或_放置在字符串的前头，那么如果想要使用到索引的话，就只能使用全文索引àsphinx！！！
l  一个非常细的盲区III：sql语句中含有 group by 时，默认会对搜索的结果集进行排序，这样就得消耗资源。于是，当我们的项目中不要求对结果进行排序的话，就应该关闭掉这个默认功能！做法是：在原语句后加上：order by null.
例如：select * from tempgroup by name order by null.
l  一个非常细的盲区IV：有些情况下，sql语句使用连接的方式会比使用子查询更有效率，因为连接的话，就避免了建立临时表。
例如： select * fromtempA ,tempB where tempA.id= tempB.id;会建立临时表
                  select * from tempA left jointempB where tempA.id= tempB.id; 不会建立临时表
l  项目技巧：在执行select语句前可以先估算一下这语句的执行过程和结果：使用explain +“select 语句” 就可以不用真正的执行也能大致估算出执行过程和结果。
l  项目技巧：当需要插入大量的数据时，例如在表之间迁移数据时，我们就应该将接受表的索引（唯一索引和普通索引等）先停掉；待迁移完毕后再恢复！！否则就会导致：接受表每接收一条数据就得整顿一次索引，这样就很费时间了！