可以帮助选择更好的索引和优化查询语句, 写出更好的优化语句。 通常我们可以对比较复杂的尤其是涉及到多表的 SELECT 语句, 把关键字 EXPLAIN 加到前面, 查看执行计划。例如: explain select * from news
用具体的字段列表代替“*” , 不要返回用不到的任何字段。
mysql innodb上的理解。
1,不需要的字段会增加数据传输的时间,即使mysql服务器和客户端是在同一台机器上,使用的协议还是tcp,通信也是需要额外的时间。
2,要取的字段、索引的类型,和这两个也是有关系的。举个例子,对于user表,有name和phone的联合索引,select name from user where phone= 12345678912 和 select * from user where phone= 12345678912 ,前者要比后者的速度快,因为name可以在索引上直接拿到,不再需要读取这条记录了。
3,大字段,例如很长的varchar,blob,text。准确来说,长度超过728字节的时候,会把超出的数据放到另外一个地方,因此读取这条记录会增加一次io *** 作。
比如from_unixtime(create_time) = ’2014-05-29’就不能使用到索引,原因很简单,b+树中存的都是数据表中的字段值,但进行检索时,需要把所有元素都应用函数才能比较,显然成本太大。所以语句应该写成create_time = unix_timestamp(’2014-05-29’)
使用 procedure analyse()函数对表进行分析, 该函数可以对表中列的数据类型提出优化建议。 能小就用小。 表数据类型第一个原则是: 使用能正确的表示和存储数据的最短类型。 这样可以减少对磁盘空间、 内存、 cpu 缓存的使用。
使用方法: select * from 表名 procedure analyse()
通过拆分表可以提高表的访问效率。 有 2 种拆分方法
1.垂直拆分
把主键和一些列放在一个表中, 然后把主键和另外的列放在另一个表中。 如果一个表中某些列常用, 而另外一些不常用, 则可以采用垂直拆分。
2.水平拆分
根据一列或者多列数据的值把数据行放到二个独立的表中。
创建中间表, 表结构和源表结构完全相同, 转移要统计的数据到中间表, 然后在中间表上进行统计, 得出想要的结果。
选择多核和主频高的 CPU。
使用更大的内存。 将尽量多的内存分配给 MYSQL 做缓存。
4.3.1 使用磁盘阵列
RAID 0 没有数据冗余, 没有数据校验的磁盘陈列。 实现 RAID 0至少需要两块以上的硬盘, 它将两块以上的硬盘合并成一块, 数据连续地分割在每块盘上。
RAID1 是将一个两块硬盘所构成 RAID 磁盘阵列, 其容量仅等于一块硬盘的容量, 因为另一块只是当作数据“镜像”。使用 RAID-0+1 磁盘阵列。 RAID 0+1 是 RAID 0 和 RAID 1 的组合形式。 它在提供与 RAID 1 一样的数据安全保障的同时, 也提供了与 RAID 0 近似的存储性能。
4.3.2 调整磁盘调度算法
选择合适的磁盘调度算法, 可以减少磁盘的寻道时间
对 MySQL 自身的优化主要是对其配置文件 my.cnf 中的各项参数进行优化调整。 如指定 MySQL 查询缓冲区的大小, 指定 MySQL 允许的最大连接进程数等。
它的作用是存储 select 查询的文本及其相应结果。 如果随后收到一个相同的查询, 服务器会从查询缓存中直接得到查询结果。 查询缓存适用的对象是更新不频繁的表, 当表中数据更改后, 查询缓存中的相关条目就会被清空。
我们都知道,服务器数据库的开发一般都是通过java或者是PHP语言来编程实现的,而为了提高我们数据库的运行速度和效率,数据库优化也成为了我们每日的工作重点,今天,昌平IT培训http://www.kmbdqn.cn/就一起来了解一下mysql服务器数据库的优化方法。
为什么要了解索引真实案例案例一:大学有段时间学习爬虫,爬取了知乎300w用户答题数据,存储到mysql数据中。
那时不了解索引,一条简单的“根据用户名搜索全部回答的sql“需要执行半分钟左右,完全满足不了正常的使用。
案例二:近线上应用的数据库频频出现多条慢sql风险提示,而工作以来,对数据库优化方面所知甚少。
例如一个用户数据页面需要执行很多次数据库查询,性能很慢,通过增加超时时间勉强可以访问,但是性能上需要优化。
索引的优点合适的索引,可以大大减小mysql服务器扫描的数据量,避免内存排序和临时表,提高应用程序的查询性能。
索引的类型mysql数据中有多种索引类型,primarykey,unique,normal,但底层存储的数据结构都是BTREE有些存储引擎还提供hash索引,全文索引。
BTREE是常见的优化要面对的索引结构,都是基于BTREE的讨论。
B-TREE查询数据简单暴力的方式是遍历所有记录如果数据不重复,就可以通过组织成一颗排序二叉树,通过二分查找算法来查询,大大提高查询性能。
而BTREE是一种更强大的排序树,支持多个分支,高度更低,数据的插入、删除、更新更快。
现代数据库的索引文件和文件系统的文件块都被组织成BTREE。
btree的每个节点都包含有key,data和只想子节点指针。
btree有度的概念d>=1。
假设btree的度为d,则每个内部节点可以有n=[d+1,2d+1)个key,n+1个子节点指针。
树的大高度为h=Logb[(N+1)/2]。
索引和文件系统中,B-TREE的节点常设计成接近一个内存页大小(也是磁盘扇区大小),且树的度非常大。
这样磁盘I/O的次数,就等于树的高度h。
假设b=100,一百万个节点的树,h将只有3层。
即,只有3次磁盘I/O就可以查找完毕,性能非常高。
索引查询建立索引后,合适的查询语句才能大发挥索引的优势。
另外,由于查询优化器可以解析客户端的sql语句,会调整sql的查询语句的条件顺序去匹配合适的索引。
1. 保证在实现功能的基础上,尽量减少对数据库的访问次数;通过搜索参数,尽量减少对表的访问行数,最小化结果集,从而减轻网络负担;能够分开的 *** 作尽量分开处理,提高每次的响应速度;、使用SQL时,尽量把使用的索引放在选择的首列;算法的结构尽量简单;在查询时,不要过多地使用通配符,而且要用到几列就选择几列,如:SELECT C1,C2 FROM T1;
在可能的情况下尽量限制尽量结果集行数,如:
SELECT TOP 300 C1,C2FROM T1,因为某些情况下用户是不需要那么多的数据的, 避免用!=或<>ISNULL或IS NOT NULL、IN ,NOT IN等这样的 *** 作符,因为这会使系统无法使用索引,而只能直接搜索表中的数据。例如:
SELECT C1 FROM T1 WHERE C1 != 'B%'
2. 合理使用EXISTS,NOT EXISTS子句。如下所示:
1)SELECT SUM(T1.C1)FROM T1 WHERE((SELECTCOUNT(1)FROM T2 WHERE T2.C2=T1.C2)>0)
2)SELECT SUM(T1.C1) FROM T1 WHERE EXISTS( SELECT 1 FROM T2 WHERET2.C2=T1.C2)两者产生相同的结果,但是后者的效率显然要高于前者。因为后者不会产生大量锁定的表扫描或是索引扫描。如果你想校验表里是否存在某条纪录,不要用count(*)那样效率很低,而且浪费服务器资源。可以用EXISTS代替。如:
IF (SELECT COUNT(1) FROM table_name WHEREcolumn_name = 'xxx')>0
可以写成:
IF EXISTS (SELECT 1 FROM table_name WHEREcolumn_name = 'xxx')
3. 经常需要写一个T_SQL语句比较一个父结果集和子结果集,从而找到是否存在在父结果集中有而在子结果集中没有的记录,如:
1) SELECTa.C1 FROM T1 a
WHERE NOT EXISTS (SELECT 1 FROM T2 b WHERE a.C1= b.C1)
2) SELECT a.C1 FROM T1 a
LEFT JOIN T2 b ON a.C1 = b.C1 WHERE b.C1IS NULL
3) SELECT a.C1 FROM T1 a
WHERE a.C1 NOT IN (SELECT C1 FROM T2)
三种写法都可以得到同样正确的结果,但是效率依次降低。
4. 能够用BETWEEN的就不要用IN
SELECT * FROM T1 WHERE ID IN (10,11,12,13,14)
改成:
SELECT* FROM T1 WHERE ID BETWEEN 10 AND 14
因为IN会使系统无法使用索引,而只能直接搜索表中的数据。
欢迎分享,转载请注明来源:内存溢出
评论列表(0条)