mysql性能优化

Mysql性能优化

1.   为什么是mysql5.6？

Mysql5.6的性能远远高于历史Mysql版本。我的Mysql version 5.6.26。

2.   什么是优化:

(1)    优化是多方面的，包括查询、更新、服务器等。

(2)    性能优化：资源占用率低，系统运转速度快。

3.   怎么优化

3.1传统三范式与冗余涉及

        (1)三范式：(牺牲时间换空间)

        ①第一范式（1NF）：强调的是列的原子性，即列不能够再分成其他几列。

        ②第二范式（2NF）：首先是 1NF，另外包含两部分内容，一是表必须有一个主键；二是没有包含在主键中的列必须完全依赖于主键，而不能只依赖于主键的一部分。

        ③第三范式（2NF）：3NF在2NF的基础之上，消除了非主属性对于主属性的传递依赖。

        注意：遵循三范式的目的是为了建立冗余较小、结构合理的数据库。

 

(3)冗余设计：(牺牲空间换时间)

在追求性能的年代，出现了冗余设计。即增加一些冗余字段来提高查询性能。一般一个系统，增删改sql占据10%，而查询占90%。所以提高查询效率至关重要。在数据库的实践过程中，我们可能遇到数据量非常大的数据表，这时候去做join查询是非常损耗性能的，甚至导致数据库连接超时、挂掉等问题。（参考我们的运单表就是一个例子）。

3.2 sql语句的优化

(1)定位慢查询

        分析mysql日志来找出那些查询时间超过预期时间的sql语句，然后进行优化。

在默认情况下，mysql是不会记录慢查询。一旦开启慢查询日志后，日志文件的位置在my.ini文件中去查找。

(2)参数说明

slow_query_log 慢查询开启状态

slow_query_log_file 慢查询日志存放的位置（这个目录需要MySQL的运行帐号的可写

权限，一般设置为MySQL的数据存放目录）

long_query_time 查询超过多少秒才记录

(3)修改配置文件my.cnf，在[mysqld]下的下方加入。  setglobal slow_query_log='ON';

[mysqld]

slow_query_log = ON

slow_query_log_file="slow.log"

long_query_time=1

(4)重启mysql

 net stop mysql56;

 net start mysql56;

(5)测试：

1.执行一条sql语句：select sleep(2); 2.查看是否生成慢查询日志。

        (2)索引：

①普通索引：

CREATE INDEX index_name ON table(column(length))

        例如：createindex tb_item_id_index on tb_item(id)

        修改表结构：

        　ALTER mytableADD INDEX [indexName] ON (username(length))

        创建表的时候直接指定

CREATE TABLEmytable( ID INT NOT NULL, username VARCHAR(16) NOT NULL, INDEX [indexName](username(length)) );

        删除索引：DROP INDEX index_name ON table

        Dropindex tb_item_id_index on tb_item

       

        ②唯一索引:

与前面的普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。

        CREATE UNIQUE INDEX indexName ON table(column(length))

create unique indextb_item_id_index on tb_item(id)

③主键索引

        是一种特殊的唯一索引，一个表只能有一个主键，不允许有空值。一般是在建表的时候同时创建主键索引：

④     复合索引

复合索引create index title_sub_index ontb_content(title,sub_title)

⑤     全文索引

     利用查询关键字和查询列内容之间的相关度进行检索，可提高匹配的速度。

(1)CREATE TABLE article (

    id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,

    title VARCHAR(200),

    content TEXT,

    FULLTEXT (title, content) --在title和content列上创建全文索引

);

(2)注意：在mysql5.6.24之后可以在innodb中添加全文索引。

ALTER TABLE tb_item_desc ADD FULLTEXT INDEX fulltext_desc (item_desc)

EXPLAIN select * from tb_item_desc where match(item_desc) against('iphone');

EXPLAIN select * from tb_item_desc where item_desc like '%iphone%';

       注意：全文索引关键字和查询列内容之间的相关度超过50%则失效。（可以设置）

⑥     如何使用索引--explain分析sql语句。

1.如果使用主键去查询，自动使用索引。

2.若创建的是复合索引，只有左边的可以用，右边的不可用。

3.模糊查询，%或者_写在左边不会使用索引，写在右边就用索引。

4.在条件查询中使用or，or两边的字段都必须使用索引，有一个没有索引就没法继续使用。

5.若一个字段是字符型，使用’’包裹起来效率高。

  若是Int,bigint，无需使用’’包裹起来。

3.3 mysql存储引擎

 

Myisam

1.     不支持事务处理。

2.     查询和添加的效率很高，支持全文索引

3.     碎片多

Innodb

1.     支持事务 用来保存比较重要的数据

2.     5.6.24版本之后支持全文索引

memory

数据频繁更改，但不在数据库中永久保存

(1)   创建表的时候指定存储引擎

CREATE TABLE mytable (id int, title char(20)) ENGINE = INNODB  

(2)   改变现有的表使用的存储引擎，用以下语句:

ALTER TABLE mytable ENGINE = MyISAM 

myisam 不支持事务，你试试开两个窗口，其中一个插入一条数据，再没有commit的情况下，另外一个也能看到。

3.4 mysql碎片整理

使用optimize来对myisam数据库进行碎片整理。

例如：Optimize table test2;