Mysql入门MySQL数据库优化技术之索引使用技巧总结
|
《Mysql入门MySQL数据库优化技术之索引使用技巧总结》要点： 本文实例总结了MySQL数据库优化技术的索引用法.分享给大家供大家参考,具体如下：MYSQL入门 这里紧接上一篇《MySQL数据库优化技术之配置技巧总结》,进一步分析索引优化的技巧：MYSQL入门 (七)表的优化MYSQL入门 1. 选择合适的数据引擎MYSQL入门 MyISAM:适用于大量的读操作的表MYSQL入门 InnoDB:适用于大量的写读作的表MYSQL入门 2.选择合适的列类型MYSQL入门 使用 SELECT * FROM TB_TEST PROCEDURE ANALYSE()可以对这个表的每一个字段进行分析,给出优化列类型建议MYSQL入门 3.对于不保存NULL值的列使用NOT NULL,这对你想索引的列尤其重要MYSQL入门 4.建立合适的索引MYSQL入门 5.使用定长字段,速度比变长要快MYSQL入门 (八)建立索引原则MYSQL入门 1.合理使用索引MYSQL入门 一个Table在一次query中只能使用一个索引,使用EXPLAIN语句来检验优化程序的操作情况MYSQL入门 使用analyze赞助优化程序对索引的使用效果做出更准确的预测MYSQL入门 2.索引应该创建在搜索、排序、归组等操作所涉及的数据列上MYSQL入门 3.尽量将索引建立在重复数据少的数据列中,唯一所以最好MYSQL入门 例如：生日列,可以建立索引,但性别列不要建立索引MYSQL入门 4.尽量对比较短的值进行索引MYSQL入门 降低磁盘IO操作,索引缓冲区中可以容纳更多的键值,提高命中率MYSQL入门 如果对一个长的字符串建立索引,可以指定一个前缀长度MYSQL入门 5.合理使用多列索引MYSQL入门 如果多个条件经常需要组合起来查询,则要使用多列索引(因为一个表一次查询只能使用一个索引,建立多个单列索引也只能使用一个)MYSQL入门 6.充分利用最左前缀MYSQL入门 也就是要合理支配多列索引中各列的顺序,将最常用的排在前面MYSQL入门 7.不要建立过多的索引MYSQL入门 只有经常应用于where,order by,group by中的字段需要建立索引.MYSQL入门 8.利用慢查询日志查找出慢查询(log-slow-queries,long_query_time)MYSQL入门 (九)充分利用索引MYSQL入门 1.尽量比较数据类型相同的数据列MYSQL入门 2.尽可能地让索引列在比较表达式中独立,WHERE mycol < 4 / 2 使用索引,而WHERE mycol * 2 < 4不使用MYSQL入门 3.尽可能不对查询字段加函数,MYSQL入门 如：WHERE YEAR(date_col) < 1990改造成WHERE date_col < '1990-01-01'MYSQL入门 WHERE TO_DAYS(date_col) - TO_DAYS(CURDATE()) < cutoff 改造成WHERE date_col < DATE_ADD(CURDATE(),INTERVAL cutoff DAY)MYSQL入门 4.在LIKE模式的开头不要使用通配符MYSQL入门 5.使用straight join可以强制优化器依照FROM子句的次序来进行联结,可以select straight join,强制所有联结,也可以select * from a straight join b强制两个表的顺序.MYSQL入门 6.使用force index强制使用指定的索引.如 select * from song_lib force index(song_name) order by song_name比不用force index效率高MYSQL入门 7.尽量避免使用MySQL自动类型转换,否则将不能使用索引.如将int型的num_col用where num_col='5'MYSQL入门 (十)SQL语句的优化MYSQL入门 1.创建合适的统计中间结果表,降低从大表查询数据的几率MYSQL入门 2.尽量避免使用子查询,而改用连接的方式.例如：MYSQL入门 SELECT a.id,(SELECT MAX(created) FROM posts WHERE author_id = a.id) AS latest_post FROM authors a 可以改成：MYSQL入门 SELECT a.id,MAX(p.created) AS latest_post FROM authors AS a INNER JOIN posts p ON (a.id = p.author_id) GROUP BY a.id select song_id from song_lib where singer_id in (select singer_id from singer_lib where first_char='A' ) limit 2000 改成:MYSQL入门 select song_id from song_lib a inner join singer_lib b on a.singer_id=b.singer_id and first_char='A' limit 2000 3.插入判断重复键时,使用ON DUPLICATE KEY UPDATE : 4.避免使用游标MYSQL入门 游标的运行效率极低,可以通过增加临时表,运用多表查询,多表更新等方式完成任务,不要使用游标.MYSQL入门 (十一)使用Explain分析SQL语句使用索引的情况MYSQL入门 当你在一条SELECT语句前放上关键词EXPLAIN,MySQL解释它将如何处理SELECT,提供有关表如何联结和以什么次序联结的信息,借助于EXPLAIN,可以知道什么时候必须为表加入索引以得到一个使用索引来寻找记录的更快的SELECT,你也能知道优化器是否以一个最佳次序联结表.为了强制优化器对一个SELECT语句使用一个特定联结次序,增加一个STRAIGHT_JOIN子句. .MYSQL入门 EXPLAIN命令的一般语法是：EXPLAIN <SQL命令> 如：explain select * from a inner join b on a.id=b.idMYSQL入门 EXPLAIN的分析结果参数详解:MYSQL入门 1.table：这是表的名字.MYSQL入门 2.type：连接操作的类型.MYSQL入门 system:表中仅有一条记录(实际应用很少只有一条资料的表)MYSQL入门 const:表最多有一个匹配行,用于用常数值比较PRIMARY KEY或UNIQUE索引的所有部分时,MYSQL入门 如：MYSQL入门 select * from song_lib where song_id=2 (song_id为表的primary key)MYSQL入门 eq_ref:对于每个来自于前面的表的行组合,从该表中用UNIQUE或PRIMARY KEY的索引读取一行,MYSQL入门 如： (b的type值为eq_ref) ref:对于每个来自于前面的表的行组合,从该表中用非UNIQUE或PRIMARY KEY的索引读取一行MYSQL入门 如： 和 代码如下: select * from singer_lib b where singer_name='ccc'(b的type值为ref,因为b.singer_name是普通索引) ref_or_null:该联接类型如同ref,但是添加了MySQL可以专门搜索包含NULL值的行,MYSQL入门 如： index_merge:该联接类型表示使用了索引合并优化办法MYSQL入门 Key： 它显示了MySQL实际使用的索引的名字.如果它为空(或NULL),则MySQL不使用索引.MYSQL入门 key_len： 索引中被使用部分的长度,以字节计.MYSQL入门 3.ref：ref列显示使用哪个列或常数与key一起从表中选择行MYSQL入门 4.rows： MySQL所认为的它在找到正确的结果之前必须扫描的记录数.显然,这里最理想的数字就是1.MYSQL入门 5.Extra：这里可能出现许多不同的选项,其中大多数将对查询产生负面影响.一般有：MYSQL入门 using where：表示使用了where条件MYSQL入门 using filesort: 表示使用了文件排序,也就是使用了order by子句,并且没有用到order by 里字段的索引,从而需要额外的排序开销,所以如果出现using filesort就表示排序的效率很低,需要进行优化,比如采用强制索引的办法(force index)MYSQL入门 更多关于MySQL相关内容感兴趣的读者可查看本站专题：《MySQL索引操作技巧汇总》、《MySQL日志操作技巧大全》、《MySQL事务操作技巧汇总》、《MySQL存储过程技巧大全》、《MySQL数据库锁相关技巧汇总》及《MySQL常用函数大汇总》MYSQL入门 希望本文所述对大家MySQL数据库计有所赞助.MYSQL入门 欢迎参与《Mysql入门MySQL数据库优化技术之索引使用技巧总结》讨论，分享您的想法，编程之家PHP学院为您提供专业教程。 （编辑：李大同） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |