mysql优化器可以执行的优化

mysql优化器会对sql进行优化,到底会执行哪些优化呢?换句话说哪些不经意写出来的sql会被优化呢?

1、对联接表重新排序,并不按照sql中的顺序进行联接,mysql自己会做优化,所以糟糕的顺序影响并不大

2、将外联转换成内联,mysql会将外联转换成等价的内联,如果可能的话

3、代数等价法则,主要指where里用到的一些逻辑表达式,会转换成等价逻辑表达式,最常见的是1=1,会被优化掉

4、优化count min max等函数,比如myisam的无where的count,可以用到索引的min max实际上就是最左端和最右端的记录

5、计算和减少常量表达式,主要是在优化联接语句时,使用using联接的条件是常量,就可以从a表传递到b表

6、覆盖索引,当索引包含所查询的字段时,可以直接使用索引,避免读行数据。google:覆盖索引的概念

7、子查询优化,某些类型的子查询会优化成索引查找,哪些子查询并没有给出具体说明和例子,所以还是尽量避免子查询

8、早期终结,主要指limit或者where里使用了不可能的条件等等

9、相等传递,还是在联接查询中,联接字段使用了某条件,mysql会自动传递到另外一个表,无需在sql里显式申明,如Ta left join Tb using (Fc) where Ta.Fc > 1,mysql会自动优化并加上 and Tb.Fc>1

10、in(非子查询),mysql的in不等同与多个0r,mysql会对in里的项进行排序,如果in的字段可以用到索引,是相当快的,千万不要反过来,将多个in的选项改写成多个or

 

 

mysql查询sql优化

1、缩短锁时间,批量delete,加limit多次执行会快于一次delete

2、分解联接,将一个表联接多表,拆分为多个sql,然后用in()替代

分解联接的优点:

一、分拆sql,更容易匹配查询缓存

二、myisam表锁,分拆sql,每次只锁一个表,而联接要同时锁多个表

三、方便数据库扩展,可以将表分拆在多个库上

四、in的效率比join效率更高

五、减少多余行的访问,联接的行数会更多

六、分拆sql等同于手工执行哈希连接,而mysql内部执行联接用的是嵌套循环算法,哈希联接效率更高

问题:什么时候再应用程序端进行联接(分拆sql)效率更高?

一、可以缓存早期查询的大量数据

二、使用了多个myisam表

三、数据分布在不同的服务器上

四、对于大表使用in替换连接

五、一个联接引用了同一个表很多次

mysql执行查询的一般过程

一、客户端将查询发送到服务器

二、服务器检查查询缓存,如果找到了,就从缓存中返回结果,否则进行下一步(这里采用的是哈希sql匹配的方式,所以要想能准确匹配到查询缓存,sql的大小写,查询字段和条件等顺序一定要统一)

三、服务器解析,预处理和优化查询,生成执行计划

四、执行引擎调用存储引擎API执行查询(注意是存储引擎通过API调用执行具体的查询,所以同一条语句对于不同的引擎,会有不同的效率和行为)

五、服务器将结果发送回客户端

MAX_PACKET_SIZE配置对大数据查询效率的影响,mysql的c/s协议是半双工的,同一时刻要么是发送要么是接收,不能做到全双工,服务器端返回查询结果,分开发送的数据包必须全部返回完毕才放开控制权,所以这个最大包的size很重要。

待续

mysql两大引擎比较-比较官方的说法

这也是经常遇到的面试笔试题,请比较mysql两大引擎的优缺点或者特点等等,以下摘自高性能mysql第二版。

1、myisam

表锁,可能成为性能瓶颈。

不支持自动数据恢复,服务器宕机、异常停电等容易造成表损坏

不支持事务

只有索引被缓存在内存中,只缓存了mysql进程内部的索引,并保存在键缓冲区。操作系统缓存了表的数据。

紧密存储,行被紧紧地保存在一起,这样磁盘上的数据就能得到小的磁盘占用和快速的全表扫描。

2、innodb

事务性,支持事务和四种事务隔离级别。(哪四种,请google)

外键

行级锁

多版本,innodb使用多版本并发控制,这样在默认情况下可能会选择读取陈旧的数据,事实上,它的MVCC架构增添了很多复杂的和意料之外的行为。

按主键聚集

所有索引包含主键列,就算申明索引里不包含主键,也会自动加上主键

未压缩的索引,索引没有使用前缀压缩,索引会比myisam大很多

数据装载缓慢

阻塞AUTO_INCREMENT,新的自增,使用表级锁产生

没有缓存的count(*),没有where子句的count(*)查询,需要全表或索引扫描,没有表级计数器

mysql索引优化学习笔记1

索引
1、where中,按照索引顺序从左到右全匹配,遇到部分匹配或跳过终止
2、order中,从索引最左匹配有效,且排序顺序按照索引来,最左前缀的例外情况是左前缀是常量,也就是where中左边的字段都是=;有连接的情况下,order里只有第一个表的字段有效
索引 index(a ,b ,c)
可以索引的情况:
1、where a=’xx’ order by b
2、where a>’xx’ order by a,b
不能或只能使用部分索引的情况:
1、where a=’xx’ order by b desc,c asc(order里两个字段排序和索引不一致)
2、where a=’xx’ order by b,d (order里引用了不在索引里的字段)
3、where a=’xx’ order by c(跳过中间的b,也不能索引)
4、where a>’xx’ order by b,c(a是范围查询,最多只是where里可以用到索引)
5、where a=’xx’ and b in (1,2) order by c(b是范围查询)
避免多余索引,比如
index1(a,b)
index2(a)
一般情况下,应去掉index2

 

joomla模板beez_20大数据量500问题

用joomla框架搭了个站,用了beez_20模板,也可能不是这个模板问题,没深挖,反正导入contents表3万条数据后,首页直接打不开,跟踪慢sql,发现一个超级庞大的sql,如下:

SELECT a.id, a.title, a.alias, a.title_alias, a.introtext, a.checked_out, a.checked_out_time, a.catid, a.created, a.created_by, a.created_by_alias, CASE WHEN a.modified = 0 THEN a.created ELSE a.modified END as modified, a.modified_by, uam.name as modified_by_name,CASE WHEN a.publish_up = 0 THEN a.created ELSE a.publish_up END as publish_up,a.publish_down, a.images, a.urls, a.attribs, a.metadata, a.metakey, a.metadesc, a.access, a.hits, a.xreference, a.featured, LENGTH(a.fulltext) AS readmore,CASE WHEN badcats.id is not null THEN 0 ELSE a.state END AS state,c.title AS category_title, c.path AS category_route, c.access AS category_access, c.alias AS category_alias,CASE WHEN a.created_by_alias > ' ' THEN a.created_by_alias ELSE ua.name END AS author,ua.email AS author_email,contact.id as contactid,parent.title as parent_title, parent.id as parent_id, parent.path as parent_route, parent.alias as parent_alias,ROUND(v.rating_sum / v.rating_count, 0) AS rating, v.rating_count as rating_count,c.published, CASE WHEN badcats.id is null THEN c.published ELSE 0 END AS parents_published
FROM n285s_content AS a
LEFT JOIN n285s_content_frontpage AS fp ON fp.content_id = a.id
LEFT JOIN n285s_categories AS c ON c.id = a.catid
LEFT JOIN n285s_users AS ua ON ua.id = a.created_by
LEFT JOIN n285s_users AS uam ON uam.id = a.modified_by
LEFT JOIN (
SELECT contact.user_id, MAX(contact.id) AS id, contact.language
FROM n285s_contact_details AS contact
WHERE contact.published = 1
GROUP BY contact.user_id, contact.language) AS contact ON contact.user_id = a.created_by
LEFT JOIN n285s_categories as parent ON parent.id = c.parent_id
LEFT JOIN n285s_content_rating AS v ON a.id = v.content_id
LEFT OUTER JOIN (SELECT cat.id as id FROM n285s_categories AS cat JOIN n285s_categories AS parent ON cat.lft BETWEEN parent.lft AND parent.rgt WHERE parent.extension = 'com_content' AND parent.published != 1 GROUP BY cat.id ) AS badcats ON badcats.id = c.id
WHERE a.access IN (1,1) AND c.access IN (1,1) AND CASE WHEN badcats.id is null THEN a.state ELSE 0 END = 1 AND (a.catid = 14 OR a.catid IN (
SELECT sub.id
FROM n285s_categories as sub
INNER JOIN n285s_categories as this ON sub.lft > this.lft AND sub.rgt < this.rgt
WHERE this.id = 14)) AND (a.publish_up = '0000-00-00 00:00:00' OR a.publish_up <= '2013-08-13 01:27:11') AND (a.publish_down = '0000-00-00 00:00:00' OR a.publish_down >= '2013-08-13 01:27:11')
GROUP BY a.id, a.title, a.alias, a.title_alias, a.introtext, a.checked_out, a.checked_out_time, a.catid, a.created, a.created_by, a.created_by_alias, a.created, a.modified, a.modified_by, uam.name, a.publish_up, a.attribs, a.metadata, a.metakey, a.metadesc, a.access, a.hits, a.xreference, a.featured, a.fulltext, a.state, a.publish_down, badcats.id, c.title, c.path, c.access, c.alias, uam.id, ua.name, ua.email, contact.id, parent.title, parent.id, parent.path, parent.alias, v.rating_sum, v.rating_count, c.published, c.lft, a.ordering, parent.lft, fp.ordering, c.id, a.images, a.urls
ORDER BY  c.lft,   CASE WHEN a.publish_up = 0 THEN a.created ELSE a.publish_up END  DESC ,  a.created;

真是无从下手去优化,找到可能是com_content组件的articles模型里getListQuery方法,应该是这个方法制造了这个超级sql,不过实在不想去看里面逻辑,200多行代码就是为了拼凑这个sql,没办法再开启fpm慢日志,抓到

execute() /var/www/html/hz/msdsheet/components/com_content/content.php:16

发现只是首页在跑道这里的时候会产生这个sql,内页都没这个问题,而首页在注释掉第16行也是可以的,于是乎做了个简单的判断,如果是首页就跳过此句,最终绕过了这个问题,这也是懒人的解决办法,抓住表面问题,解决就好。