MySQL简单性能优化

一、查询MySQL的性能参数

        Show status like ‘value’

       其中，value是要查询的参数值，一些常用的性能参数如下

• Connections:连接MySQL服务器的次数
• Uptime：MySQL服务器上线时间
• Slow_queries:慢查询的次数
• Com_select:查询操作的次数
• Com_insert:插入操作的次数
• Com_update:更新操作的次数
• Com_delete:删除操作的次数

二、 优化查询

• 分析查询语句

1. explain [extended] select 查询语句

如：explain select *from fruits

     2.describe select 查询语句

如：describe select *from fruits

• 使用索引查询

     几种索引不起作用的特殊情况

1. 使用like关键字的查询语句

         如果匹配字符串的第一个字符为“%”，索引不起作用

         如select *from fruits where f_name like’%x’;  【f_name已加索引】

     2.使用多列索引的查询语句

        MySQL可对多个字段创建索引，一个索引可以包含16个字段，对于多列索引，只有查询条件使用了这些字段中第一个字段时，索引才会被使用

        如：create index index_id_price on fruits(f_id,f_price);

               Select *from fruits where f_id=’12’;【起作用】

               Select *from fruits where f_price=5.2;【不起作用】

   3.使用or关键字的查询语句

      查询语句的查询条件中只有or关键字，且or前后的两个条件中的列都是索引时，查询中才使用索引，否则，索引将不起作用

      如：select *from fruits where f_name=’app%’ or f_id=’12’

           【f_name和f_id两者都有索引，才起作用】

  4.优化子查询

     使用连接（join）查询来代替子查询。因为连接查询不需要在内存中创建临时表来完成查询操作

   三. 优化数据库结构

   1)将字段很多的表分解成多个表

      例：会员表members中有id，姓名，密码，地址，电话，个人描述字段，其中地址，电话，个人描述不常用，将这3个字段抽  取为members_detail(加上member_id)

     如果要查询会员的所有信息，查询语句可如下：

    Select *from menbers left join members_detail on members.id=members.id=members_detail.member_id;

   2) 增加中间表

       对于需要经常联合查询的表，可以建立中间表以提高查询效率，把需要经常联合查询的数据插入到中间表中，然后将原来的联合查询改为中间表查询

   3）增加冗余字段

        这个方法有利有弊，冗余字段会导致一些问题，比如冗余字段的值在这个表中被改变了，在其他表就要想办法更新，同时也会浪费一定的磁盘空间。根据实际需求综合分析

   4）优化插入记录的速度

插入记录时，影响插入速度的主要是索引，唯一性校验，一次插入记录条数等

•    禁用索引

          问题：插入大量数据，建立索引会降低插入记录的速度

解决：在插入记录之前禁用索引，数据插入完毕后在开启索引

禁用语句：alter table 表名disable keys;

开启语句：alter table 表名 enable keys;

• 禁用唯一性检查

问题与解决方法同上，先禁后开

禁用语句: set unique_checks=0;

开启语句：set unique_checks=1;

• 使用批量插入

如：insert into fruits values

       (‘x1’,’100’,’apple’,’1.0’),

       (‘x2’,’200’,’apple2’,’2.0’),

       (‘x3’,’300’,’apple3’,’3.0’),

• 使用load data infle 批量导入

Load data infle 语句导入数据的速度比insert语句快;对于innodb引擎的表，常见的优化方法如下

   a.先禁后开唯一性检查

      Set unique_checks=0;        Set unique_checks=0;

   b.先禁后开外键检查

      Set foreign_key_checks=0;    Set foreign_key_checks=0;

   c.先禁后开自动提交

      Set autocommit=0;    set autocommit=1;

       5) 分析表，检查表，优化表

• 分析表：主要分析关键字的分布

Analyze [local|no_write_to_binlog] table 表名[,表名]…

如：analyze table fruits;

结果显示信息说明：Table:表示分析表的名称;Op:表示执行的操作

Msg_type:表示信息类型，其值通常是状态（status），信息（info），注意（note），警告（waring），错误（error）之  一;Msg_text:显示信息

• 检查表：主要检查表是否存在错误

Check table 表名[,表名]…

option={quick|fast|medium|extened|changeg};

quick:不扫描行

fast：只检查没有被正常关闭的表

changed：只检查上次检查后被修改的表或没有被正确关闭的表

medium：扫描行

extended：对每行的所有关键字进行一个全面的关键字查找

• 优化表：主要是用于消除删除或者更新造成的空间浪费

Optimize [local|no_write_to_binlog] table 表名[,表名]…

在执行过程会给表加上只读锁，只能哟花表中的varchar,blob,text类型的字段，对innodb和myisam类型的表都有效

 四、优化MySQL服务器

• 配置较大的内存，配置高速磁盘系统，合理分布磁盘IO，分配多处理器
• 一些MySQL服务的配置参数，在my.cnf或者my.ini文件的【mysqld】数组中，关于配置参数的修改得综合考虑
• Key_buffer_size:表示索引缓冲区的大小
• Table_cache :表示同时打开的表的个数
• Query_cache_size:表示缓冲区的大小
• Sort_buffer_size:表示排序缓冲区的大小
• Read_buffer_size:表示每个线程连续扫描时为扫描的每个表分配的缓冲区的大小（字节）
• Read_rnd_buffer_size:表示为每个线程保留的缓冲区的大小
• Innodb_buffer_pool_size:表示innodb类型的表和索引的最大缓存
• Max_connections：表示数据库的最大连接数
• Innodb_flush_log_at_trx_commit:表示何时将缓冲区的数据写入日志文件，并且将日志文件写入到磁盘中
• Back_log:表示在MySQL暂时停止回答新请求之前的短暂时间内，多少个请求可以被存在堆栈中
• Interactive_timeout:表示服务器关闭连接前等待行动的秒数
• Sort_buffer_size:表示每个需要在排序的线程分配的缓冲区的大小
• Thread_cache_size:表示可以复用的线程的数量
• Wait_timeout:表示服务器在关闭一个连接时等待动作的秒数