mysql 优化

mysql 优化

1.show status命令介绍

Show status 命令了解各种sql的执行频率

 

查看当前有多少个连接

 

Show status like  'connections'

查看数据库启用了多久

Show status like  ‘uptime’

查看慢查询的次数

Show status like ‘slow_queries’

查询慢查询的时间

Show variables like ‘long_query_time’

修改慢查询的时间

set long_query_time

2.开启慢查询 找到比较慢的sql语句

mysql 数据库默认慢查询的时间是10s

默认情况不记录慢查询 需要手动开启

使用安全模式打开mysql

Bin/mysql.exe --safe-mode  --slow-query-log

3.explain分析sql语句

该命令可以在不真正执行时，就可以告诉程序员效率

Id 表示序列号

Select_type  simple 查询类型(primary  subquery dependent subquery union )

Table 查询的表

Type 类别 all 表示全表扫描 system 表示一行 const 表最多有一个匹配行

Possible_keys 表示有哪些索引可用 null 表示没有索引可用

Key 表示具体在使用哪些索引

Rows 扫描后有多少结果

Extra 表示什么方式排序  ( useing filesort useing temporary using where )

4.建立适当的索引

提高数据库性能 只要适当增加索引就能增加速度,但是同时会影响dml语句的效率

创建索引会使用数据库的存放索引的文件增大。每次执行dml语句的时候 会维护索引，造成i/o读写次数变慢。(以空间换时间)

索引分类:主键索引(primary) 唯一索引(unique) 普通索引 全文索引

使用索引注意事项

在下面的情况可能会使用到索引

对于创建的多列索引。只要查询条件使用最左边的列

索引一般都会被用到

create index index_name on table_name(colum1,colum2)

最左边的colum1将会被用到

当使用 like ‘%’ 索引不会被用到 ‘aa%’会使用到索引

下列的情况不使用索引

1.如果条件中有or 即使其中有条件带索引 也不会使用

2.对于多列索引，不是使用的第一部分，则不会使用索引

3.Like 查询 %开头

4.如果列是字符串 那一定要在条件中将数据使用引号，不然不会用到索引

 

对于大批量插入数据的时候

对于myisam 

Alter table table_name disable keys;

Loading data/insert 语句

Alter table table_name enable keys

对于innodb 

将要导入的数据按照主键排序

Set unique_checks=0 关闭唯一性校验

Set autocommit=0 关闭自动提交

常见sql语句的优化

 

1.group by

默认情况下 mysql 对所有的group by col co2进行排序 

 

如果只想分组 不排序 可使用order by null 禁用排序

 

 2.join

使用左连接或者右连接 替代普通多表连接查询

选择合适的存储引擎

 

如果应用是以读写操作和插入操作为主，只有很少的更新和删除操作，并且对事务依赖性不高的选择MyISAM

Innodb 提供了具有提交 回滚和崩溃恢复能力的事务安全。但是对于MyISAM 写的处理效率差一些 并且会占用更多的的磁盘空间

 

 

对于存储引擎是myisam 的数据库  需要定时优化

 

因为myisam引擎 删除数据后 不会存放数据的文件大小不会减少

 

要定时执行 optimize table table_name 

 

数据库参数的合理配置

 

最重要的参数是内存，使用innodb引擎

 

所以innodb_additionnal_mem_pool_size=64

 

Innodb_buffer_pool_size=1g

 

对于Myisam 需要调整key_buffer_size

 

在my.ini修改端口 默认存储引擎和最大连接数

 

max_connections=512

 

如果机器内存超过4g 建议使用64位的操作系统