MySql数据库的优化_MySQL

bitsCN.com

MySql数据库的优化

 

一、SQL语句的优化

 

二、建立索引

 

三、表的水平划分/垂直划分

 

四、数据库表的合理设计

 

五、读写分离技术

 

MySQL数据库的优化

 

2

 

—通过show global status命令了解各种SQL的执行频率

 

SQL语句优化的步骤1

 

MySQL客户端连接成功后，通过使用

show global status like ‘Com_select’   show global status like ‘Com_insert’         show global status like ‘Com_update’         show global status like ‘Com_delete’

 

得到增删改查语句所执行的次数。

 

 

 

 

3

 

—定位慢查询

 

SQL语句优化的步骤2

 

 在默认情况下mysql不记录慢查询日志，需要在启动的时指定bin/mysqld.exe - -slow-query-log

 

设定慢查询时间：

 

set long_query_time=2;

 

通过慢查询日志定位执行效率较低的SQL语句。慢查询日志记录了所有执行时间超过long_query_time所设置的SQL语句。

 

 

 

 

4

 

—explain分析问题

 

SQL语句优化的步骤3

Explain select * from emp where ename=“zrlcHd”

 

 

select_type:表示查询的类型。

table:输出结果集的表

type:表示表的连接类型

possible_keys:表示查询时，

 

可能使用的索引

key:表示实际使用的索引     

key_len:索引字段的长度

rows:扫描的行数

Extra:执行情况的描述和说明

 

5

 

—不用加内存，不用改程序，最物美价廉

 

建立索引

 

好外：

 

   加快了查询速度(select )

 

坏处: 

 

          降低了增,删,改的速度(update/delete/insert)

 

   增大了表的文件大小

 

原则：

 

    不过度索引

 

    较频繁的作为查询条件字段应该创建索引

 

    唯一性太差的字段不适合单独创建索引。例如:给性别"男","女"加索引,意义不大

 

 

 

6

 

索引的使用

 

索引的种类：普通索引，主键索引，唯一索引，全文索引

 

建立索引create [UNIQUE|FULLTEXT]  index index_name on tbl_name (col_name [(length)] [ASC | DESC] , …..);alter table table_name ADD INDEX [index_name] (index_col_name,...)         添加主键(索引) ALTER TABLE 表名 ADD PRIMARY KEY(列名,..); 联合主键删除索引DROP INDEX index_name ON tbl_name;alter table table_name drop index index_name;         删除主键(索引)比较特别: alter table t_b drop primary key;查询索引：show index from table_name;

 

 

 

7

 

表的水平划分

 

  如果一个表的记录数太多了，比如上千万条，而且需要经常检索，那么我们就有必要化整为零了如果我拆成100个表，那么每个表只有10万条记录。

 

表的垂直划分

 

  有些表记录数并不多，可能也就2、3万条，但是字段却很长，表占用空间很大，检索表时需要执行大量I/O，严重降低了性能。这个时候需要把大的字段拆分到另一个表，并且该表与原表是一对一的关系。

 

8

 

数据库表的合理设计

 

  尽量符合3NF,有时为了提高运行效率适当降低范式标准，适当保留冗余数据.

 

  选择字段的一般原则是保小不保大，能用占用字节小的字段就不用大字段。比如主键，建议使用自增类型，这样省空间。

 

         在精度要求高的应用中，建议使用定点数来存储数值，以保证结果的准确性。

 

选择合适的存储引擎：

 

   MyISAM:如果应用是以读操作和插入操作为主，只有很少的更新和删除操作，并且对事务的完整性、并发性要求不是很高。其优势是访问的速度快。

 

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

 

9

 

数据库表的合理设计

 

  对于存储引擎是MyISAM的数据库,如果经常做删除和修改记录的操作，定时执行optimize tabletable_name; 功能对表进行碎片整理。

 

  优化group by 语句

默认情况，MySQL对所有的group by col1,col2进行排序。这与在查询中指定order by col1, col2类似。如果查询中包括group by但用户想要避免排序结果的消耗，则可以使用order by null禁止排序。

 

         如果想要在含有or的查询语句中利用索引，则or之间的每个条件列都必须用到索引，如果没有索引，则应该考虑增加索引

 

  

select * from 表名 where col1=‘**’or col2=‘**’

 

 

 

 

10

 

读写分离技术

 

Master

 

　　Slave1

 

　　Slave2

 

　　Slave3

 

主库master用来写入（增删改），slave1—slave3都用来做读出（select）。

 

目的：减少每个数据库的压力，提高效率。

 

实现要求：

 

    程序控制使写都操作master，读都操作slave。

 

    实现master到slave的同步，官方有个mysql-proxy

bitsCN.com