mysql学习之——查询优化

 

mysql 查询优化

 

索引

 

索引优点：

1，没有索引的表是没有排序的数据结合，有索引的表是在索引列上排序的，在

     myisam数据表中，索引文件和数据文件放在两个文件中，BDB与innodb放在一个文件中

2，多表关联查询中，索引的作用很大

3，min()或max()函数的查询，有索引mysql可以直接找到

4，索引加快order by 或者group by的操作

5，在数值型数据上查询数据，该列有索引，mysql能直接索引取值而不用读该数据行

 

索引缺点：

1，索引需要占用额外的磁盘空间

2，索引会减慢在索引数据列上的插入，删除，修改操作

 

索引的选择：

1，索引应该创建在搜索，排序，分组所涉及的数据列上，where,order by,group by子句，<, <=,=,>=,>,BETWEEN等操作上

2，尽量使用短索引，字符串应该指定长度进行索引

3，索引排序

4，复合索引尽量用左前缀

5，利用慢查询日志来找出性能差的查询，通过mysqldumpshow可查看日志，针对性能

 

差的查询可利用索引来加快查询速度

 

查询的优化：

1，尽量对同类型数据进行比较

2，索引数据应出现在简单表达式，如表达式过于复杂，索引作用无效

3，尽量避免模糊查询

4，设计不同的查询语句，用日志或者explain语句来分析查询效率

5，选择小数据列进行查询

6，myisam表中char的查询速度快，innodb中varchar可以有效的减少磁盘空间和io

7，数据列尽量指定为not null

8，对于取值有限的数据列，指定为enum更好，使用procedure analyse()可用来分析select * from table_name procedure analyse();

9，myisam 用optimize table语句对数据表进行整理，通用的方法是用mysqldump导出数据表，再删除后重建

 

   mysqldump --opt db_name table_name > dump.sql

   mysql db_name < dump.sql

 

10，blob和text剥离到另外一个表中，原有的表其他列转变成固定长度数据，减少碎 

 

   片

 

减少io操作：

1，load data 比insert语句效率快

2，load data 比load data local效率快

3，insert语句尽量在一个事务中执行，对于不支持事务表，进行锁定，在锁定时insert

begin:

insert info table_name values(...);

insert info table_name values(...);

commit;

 

   lock tables table_name write:

insert info table_name values(...);

insert info table_name values(...);

unlock tables;

5，实现服务器的db cache，这个要针对具体需求实现，客户程序读写数据只改变db cache里数据，

     db cache实际上就是内存，dbcache根据具体需求制定策略读写数据库