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;