|
1.数据库优化的可以从如下几个方面着手: 1.>sql语句和索引. 2.>数据库表结构. 3.>系统配置. 4.>硬件 2.使用MySQL的慢查询日志对效率有问题的sql进行监控 1.>查看慢查询日志是否开启:show variables like 'slow_query_log' 2.>根据查看出来的日志存放路径,开启慢查询日志set global slow_query_log_file='/var/lib/mysql/mylinux-slow.log' (file后面的地址可以通过show variables like '%log%';获取) 3.>开启没有用索引的查询记录在日志中:set global log_queries_not_using_indexes=on; 4.>设置最长的查询时间,如果超过该时间,则被记录到日志中.set global long_query_time=1; 3.SQL及索引优化. 3.1MySQL自带的慢查询日志分析工具: mysqldumpslow -h 可以查看相关参数. mysqldumpslow -t 3 /var/lib/mysql/mylinux-slow.log | more 查看前3条性能比较差的sql. 3.2专业的慢查询分析工具:pt-query-digest. 3.3使用该工具进行查看慢查询日志:pt-query-digest /var/lib/mysql/mylinux-slow.log | more 查看详情. 4.通过慢查询日志发现有问题的SQL 5.SQL优化 5.1 max和count查询的优化 explain select max(box_lunch_time) from cart_personal /G (竖着显示) 如果查询数据较多,比较慢的话,可以为box_lunch_time做索引,eg: create index idxboxlunchtime on cart_personal(box_lunch_time); count函数中值得注意的: count*会把空值也都计算进去,而count id则不会 5.2子查询优化 当采用子查询效率上不如连接查询时,将子查询改为连接查询,但有一点需要值得注意,就是连接查询不会屏蔽一对多时出现数据重复的情况,此时如果业务需要,可以采用distinct关键字来处理. eg: select * from t where t.id in (select t1.tid from t1); 可以用: select t.id from t join t1 on t.tid =t1.tid;代替,当t1中的tid有两条相同的数据时,那么第一条查询语句查询出来的结果包只含1条数据,而第二条查询语句能查出2条数据,此时可以改为: select distinct t.id from t join t1 on t.tid =t1.tid; 这样查询结果就与第一条sql查询结果一致了! 5.3group by 优化 优化前: 优化后: 5.4 limit查询优化 优化前: 优化步骤一: 在order by操作中,尽量使用主键或者索引列来进行order by,这样效率高.
优化步骤二: 如果数据量比较大时,Limit50000就要扫描500000次,IO比较大,因此需要进一步优化.值得注意的是,在这种情况下,id必须是连续的,顺序增长的,如果Id不连续,需要添加一列,idindex,保证它是自增且连续的,为它添加索引,即可.
6.索引优化 6.1如何选择合适的列建立索引? 1.在where从句,group by从句,order by从句 on从句中出现的列建立索引 2.索引字段越小越好 3.离散度大的列放到联合索引的前面.(如何判断离散度? 可以使用count语句,比如两个字段staff_id和customer_id,可以使用select count distinct(staff_id), count distinct(customer_id) from payment; 查询结果中对应的数字越大,说明离散度越大)
6.2索引优化sql的方法 1.重复索引
2.冗余索引
3.如何查询出重复索引和冗余索引? SELECT a.TABLE_SCHEMA, a.TABLE_NAME, a.COLUMN_NAME, a.INDEX_NAME AS 'index1', b.INDEX_NAME AS 'index2' FROM information_schema.STATISTICS a JOIN information_schema.STATISTICS b ON a.TABLE_SCHEMA = b.TABLE_SCHEMA AND a.TABLE_NAME = b.TABLE_NAME AND a.SEQ_IN_INDEX = b.SEQ_IN_INDEX AND a.COLUMN_NAME = b.COLUMN_NAME WHERE a.SEQ_IN_INDEX = 1 AND a.INDEX_NAME <> b.INDEX_NAME 4.查找重复/冗余索引的工具:pt-duplicate-key-checker 工具检查重复及冗余索引 下载地址：http://www./redir/downloads/percona-toolkit/percona-toolkit-1.0.1.tar.gz 安装方法： 先安装依赖:yum install -y perl perl-IO-Socket-SSL perl-DBD-MySQL perl-Time-HiRes perl-Digest-MD5 perl-ExtUtils-MakeMaker 然后进入解压后的目录 执行: perl Makefile.PL；make；make install 使用:pt-duplicate-key-checker --host=localhost --user=root --password=123456 --database=ordering 进行查询,根据查询出的结果和工具给出的建议进一步优化索引. 4.索引维护 pt-index-usage --host=localhost --user=root --password=123456 /var/lib/mysql/mylinux-slow.log 删除那些不经常使用的索引. |
|
|
