MySQL科普的Mysql数据库优化总结

索引

1.主键索引

意:唯一约束与提高查询速度

#建表时创建主键索引
create table `table_name`(
    `id` int unsigned not null auto_increment,
    primary key(`id`)
);

#删除主键索引
alter table `table_name` drop primary key(`id`);

#已有表增加主键索引
alter table `table_name` add primary key(`id`);

2.日常索引

企图:提高查询速度

#建表时创建普通索引
create table `table_name`(
    `name` char(50) not null,
    index [index_name] (`name`)
);

#创建普通索引
create index index_name on table_name (name);

#删除普通索引
drop index index_name from table_name;

#查看索引
show indx from table_name;

3.唯一索引

打算:唯一约束与增强查询速度

#建表时创建唯一索引
create table `table_name`(
    `name` char(50) not null,
    unique [index_name] (`name`)
);

#创建唯一索引
create unique index index_name on table_name (name);

#删除唯一索引
drop unique index index_name from table_name;

4.主键和唯一索引的界别

  主键是同样栽约束,唯一索引是同一种植索引,两者在本质上是差之。主键创建后必带有一个唯一性索引,唯一性索引并不一定就是主键。唯一性索引列允许空值,而主键列非容许吗空值。主键列在开创时,已经默认为空值

  • 唯索引了。

  主键可以给外表引用为外键,而唯一索引不可知。一个表明最多只能创造一个主键,但可创建多个唯一索引。主键更合乎那些不容易改的绝无仅有标识,如自行递增列、身份证号等。

  在 RBO 模式下,主键的实行计划先级要逾唯一索引。
两者可以提高查询的快慢。

 

少用SELECT *

  使用select时,应该抱我们设用的数,而非是全取。因为当我们select
*每每,会增多web服务器的当,增加网络传输的负载,查询速度自然就是暴跌 。

 

EXPLAIN SELECT

  explain显示了mysql如何使用索引来处理select语句以及连接表。可以辅助选择重复好之目和描写来更优化的询问语句。主要为此法虽是当select前加上explain即可。

EXPLAIN SELECT [查找字段名] FROM tab_name ...

  在 EXPLAIN
的帮助下,你就是理解啊时该让表添加索引,以使用索引来查找记录从而给
SELECT 运行更快。

 

展查询缓存

  大多数底MySQL服务器都被了询问缓存。这是提高性最实惠的计有,而且就是让MySQL的数据库引擎处理的。当起成百上千一如既往之询问被实施了往往底当儿,这些查询结果会受停放一个缓存中,这样,后续的同样的查询就不要操作表而直白看缓存结果了。

  第一步将query_cache_type设置为ON,然后查询系统变量have_query_cache是否可用:

show variables like 'have_query_cache'

  之后,分配内存大小为查询缓存,控制缓存查询结果的顶老价值。相关操作以布置文件中进行改动。

 

使用NOT NULL

  很多表都包含可为 NULL (空值) 的排,即使应用程序井无需要保留 NULL
也是如此 ,这是坐可也 NULL 是排的默认属性。通常情况下最为好指定列为 NOT
NULL,除非真 的需要仓储 NULL 值。
苟查询中带有可也 NULL 的排,对 MySQL 来说再难优化 ,因为可也 NULL
的列使 得索引、索引统计和值比较还更复杂 。可也NULL
的列会使用更多的贮存空间 ,在 MySQL 里呢得非常处理 。当可也NULL
的列被索引肘,每个索引记录得一个额 外的字节,在 MyISAM
里竟还可能引致固定大小 的索引 (例如只发一个整数排列的 索引)
变成可变大小的目录。
  通常将可也 NULL 的列改为 NOT NULL 带来的特性提升比较小 ,所以
(调优时) 没有 必要首先在存活schema中查找井修改掉这种情形
,除非确定就会招致问题。但是, 如果计划于列上建索引
,就应尽量避免设计改为可为 NULL 的排。当然也有章外
,例如值得一提的凡,InnoDB 使用单独的位 (bit ) 存储 NULL 值 ,所
以对稀疏数据由有异常好的半空中效率 。但随即等同碰未适用于MyISAM 。

 

积存引擎的选择

  对于怎样挑选MyISAM和InnoDB,如果您用事务处理或是外键,那么InnoDB可能是于好之法。如果您待全文索引,那么普通来说MyISAM是好之挑选,因为马上是网内建的,然而,我们实际并无会见常地失去测试两百万尽记录。所以,就终于慢一点,我们可以经过动用Sphinx从InnoDB中得全文索引。
  数据的分寸,是一个震慑您挑怎么存储引擎的第一因素,大尺寸的数目集趋向于选InnoDB方式,因为那个支持事务处理和故障恢复。数据库的于微决定了故障恢复的时长度,InnoDB可以动用工作日志进行数据恢复,这会比快。而MyISAM可能会见待几只钟头还几上来波及这些事,InnoDB只需要几分钟。
  你操作数据库表的习惯或啊会是一个对准性影响很老的元素。比如:
COUNT() 在
MyISAM表中会生急匆匆,而在InnoDB表下可能会见怪痛苦。而主键查询则当InnoDB下会一定相当之赶快,但待小心的凡如我们的主键太长了吧会招致性问题。大批底inserts语句以MyISAM下会快一些,但是updates在InnoDB
下会再次快有——尤其以并发量大之时。
  所以,到底你查看使用啊一个也?根据经验来拘禁,如果是片微型的用或项目,那么MyISAM也许会再次切合。当然,在大型的环境下用MyISAM也会出老酷成功的下,但却未连续这么的。如果您方计划以一个重特大数据量的种类,而且要事务处理或外键支持,那么您真正应该直接以InnoDB方式。但待牢记InnoDB的表需要双重多的内存和储存,转换100GB的MyISAM
表到InnoDB 表可能会见叫您生出特别深的体验。

 

避免以 where 子句被使 or 来连接

  如果一个字段有目录,一个字段没有索引,将招致发动机放弃行使索引而进行全表扫描,如:

select id from table where num=10 or name = 'admin'

  可以这样查询:

select id from table where num = 10
union all
select id from table where name = 'admin'

 

避大数据量返回

这里要考虑动用limit,来界定返回的数据量,如果每次回大量和好无欲之数据,也会下滑查询速度。

 

where子句优化

  where
子句被运用参数,会造成全表扫描,因为SQL只发生于运转时才见面分析局部变量,但优化程序不可知以访问计划的挑推迟至运行时;它要于编译时展开精选。然
而,如果在编译时植访问计划,变量的价还是不解的,因而无法作为目录选择的输入项。
  应尽量避免以 where
子句被针对字段进行表达式操作,避免以where子句被对字段进行函数操作就将招致发动机放弃采用索引而开展全表扫描。不要以
where
子句被之“=”左边进行函数、算术运算或任何表达式运算,否则系统将可能无法正确使用索引。

 

(注:文章出处)

网站地图xml地图