金沙棋牌app手机下载

当前位置:金沙棋牌 > 金沙棋牌app手机下载 > 开发进阶篇系列金沙棋牌app手机下载,mysql查询优

开发进阶篇系列金沙棋牌app手机下载,mysql查询优

来源:http://www.logblo.com 作者:金沙棋牌 时间:2019-11-05 18:51

一. 使用sql提示

         sql 提示(sql hint)是优化数据库的一个重要手段, 是在sql语句中加入一些人为的提示来达到优化操作的目的。

  1.1 use index
    在查询语句中表名的后面,添加use index 强制mysql使用该索引,不考虑其它索引。

EXPLAIN SELECT * FROM city USE INDEX(ix1) WHERE city_id=1;

  1.2 ignore index
    在查询语句中表名的后面,添加ignore index,使用mysql忽视一个或者多个索引。

EXPLAIN SELECT * FROM city IGNORE INDEX(ix1) WHERE  city_id=14;

  1.3 force index
    在查询语句中表名的后面,添加force index,当mysql不走索引时,强制走索引。

-- 某些情况下,有索引但mysql不走索引,强制使用
EXPLAIN SELECT * FROM city FORCE INDEX (PRIMARY) WHERE  city_id>0;

CentOS下Mysql 优化

1.调试 mysql 命令方法: mysql>explain select * from t1 G 或: mysql>desc select * from t1 G 主要查看以下属性: (1)type 是否用到索引 (2)key 索引名称 (3)rows 查询影响的行数(越少说明优化的越好) 2.用 mysql 命令获得一些服务优化信息: (1)show status like 'com_%'; #主要看 com_select,com_insert,com_update,com_delete,查到都是本次服务启动后的会话结果,当然其中包括所有的数据为存储类型 (2)就想查看 innodb 的这些信息: show status like 'innodb_rows_%'; 主 要 查看 innodb_rows_read,innodb_rows_inesrted,innodb_rows_updated,innodb_rows_deleted 这四个参数 (3)show status like 'connections'; #查看连接 mysql 服务器的次数 (4)show status like 'uptime'; #mysql 服务器的工作时间 (5)show status like 'slow_queries'; #慢查询的次数 3.索引优化问题: (1)一般的要加索引的字段为:where 后面字段 (2)order by 后面的字段 (3)like 后 3%这种形式而不是%3 这种形式 (4)or 前后字段都要用到索引 (5)复合索引的第一个字段 (6)创建索引:mysql>create index ind_name on t1(name); (7)查看索引使用情况: mysql>show status like 'handler_read%'; #如果其中 handler_read_rnd_next 的值高则索引低效,需要去优化索引,而如果低则说明索引高效. 4.常用的 sql 表的导入导出优化: (1)mysql>alter table t1 disable keys mysql>load data infile 'c:/t1.txt'; mysql>alter table t1 enable keys; (2)mysql>set unique_checks=0; mysql>load data...; msyql>set unique_checks=1; (3)mysql>autocommit=0; mysql>load data...; mysql>autocommit=1; 5.优化 insert 语句,最好用一行多值的这种形式: insert into t1(name) values(1),(2),(3) 6.当一个文件装载一个表时,用 load data infile 要比很多 insert 语句快 20 倍,而 mysqlimport 这种导入也很快,因为它用的本来就是 load data infile 这种函数接口. 7.查询包括 group by 但如何避免排序结果的消耗: mysql>desc select id from t1 group by id order by null G 8.优化嵌套查询: (1)mysql>desc select * from t1 where s_id not in (select id from comany2) G (2)mysql>desc select * form t1 left join company2 on t1.s_id=comany2.id where t1.s_id is null G #以上 left join 这种形式明显快于 not in ()这种子查询,因为 join 不需要在内存中建立临时表来完成这个逻辑上需要两个步骤的查询工作. 9.sql 索引提示: (1)use index mysql>desc select * from t1 use index (ind_id) where id=3 G (2)ignore index mysql>desc select * from t1 ignore index (ind_id) where id=3G (3)force index mysql>desc select * from t1 force index (ind_id) where id>0 G #注意这种 where 后这种带范围判断的字段的索引是不起作用的,但可以人为的强制去用 index,虽然对影响 rows 起不到作用,这是 mysql 留给用户的一个自行选择计划的权力而已. 10.与删除表有关的优化: 1)用 delete 来删除 mysql> delete from t1; Query OK, 10 rows affected (0.01 sec) 2)用 truncate 来删除 mysql> truncate table t2; Query OK, 0 rows affected (0.00 sec) #用两种方法都可以把表中数据清空,但是用第二种明显比第一种速度要快,而且节省内存.

优化 1.调试 mysql 命令方法: mysqlexplain select * from t1 G 或: mysqldesc select * from t1 G 主要查看以下属性: (1)type 是否用到索引 (2)key 索...

目录:

mysql force index() 强制索引的使用

 

之前跑了一个SQL,由于其中一个表的数据量比较大,而在条件中有破坏索引或使用了很多其他索引,就会使得sql跑的非常慢。。。

那我们怎么解决呢? 

这时候我么可以使用mysql force index() 强制索引来优化查询语句;

使用MySQL force index 强制索引的目的是对目标表添加最关键的索引,使其优先使用该索引筛选数据;

select * from ws_shop a 
where date(create_time-interval 6 hour) > '2016-10-01 06:00:00'

如果表中的数据是百万级的,这样查询是比较慢的;虽然你有可能在字段create_time上面加了索引,但是在where条件中又破坏了索引;导致索引失效;

这是我们优化为:

select * from ws_shop a force index(create_time)
where date(create_time-interval 6 hour) > '2016-10-01 06:00:00'

 

二 .优化数据库对象

  1. 优化表的数据类型
    在mysql中,可以使用函数procedure analyse()对当前应用的表进行分析。对表列中的数据类型给出合理的改进建议,用户可以根据实际情况来考虑。
  例如:下面生产库中有一个菜单表,字段类型及长度如下:

金沙棋牌app手机下载 1

 -- 使用procedure analyse()分析
SELECT * FROM Adm_Menu PROCEDURE ANALYSE(16,256);

    下面remark 字段里面值的最大长度的是30长度, 所以系统建议给出30长度:

金沙棋牌app手机下载 2

  2. 通过折分提高表的访问效率
    折分可以是垂直拆分和水平拆分,这是一种设计思路,这篇不讲。

  3. 逆规范化
    逆规范化也叫提高表的冗余,有利于提高查询性能。这是一种设计思路,这篇不讲。

  4. 使用中间表提高统计查询速度
    比如有一个大表记录了客户的每天消费记录,需要按月统计总消费金额, 可以放入到中间表,减轻大表的频繁查询. 这是一种设计思路,这篇不讲。

 

《MySQL中的两种临时表》--强制使用临时表 SQL_BUFFER_RESULT

《MySQL 多表关联更新及删除》

《mysql查询优化之三:查询优化器提示(hint)》

《MySQL锁之三:MySQL的共享锁与排它锁编码演示》 --for update 和 lock in share mode 

 《mysql实战优化之九:MySQL查询缓存总结》--关闭查询缓冲 SQL_NO_CACHE / 强制查询缓冲 SQL_CACHE**

 

上文我们有提及到优化器的一些相关信息,如优化器的一些优化特性和限制,由此看出mysql优化器也并不是万能的。

所以mysql提供了另一种神奇的功能让我们去引导优化器进行更好的优化。

它就是 查询优化提示(Query Optimizer Hints);

查询优化提示会提示优化器按照一定的方式去优化,让你的sql语句更具灵活性,这会让你的查询更快,当然也可能更慢,这完全取决于你对优化器的理解和场景的了解。

现在让我们来了解有哪些查询优化提示:

优先操作 HIGH_PRIORITY

HIGH_PRIORITY可以使用在select和insert操作中,让MYSQL知道,这个操作优先进行。
SELECT HIGH_PRIORITY * FROM TABLE1;

滞后操作 LOW_PRIORITY

LOW_PRIORITY可以使用在select,delete,insert和update操作中,让mysql知道,这个操作滞后。
update LOW_PRIORITY table1 set field1= where field1= …

这两个提示都只在基于表锁的存储引擎非常有效。在innoDB和其他基于行锁的存储引擎,你可能永远用不上。在MyISAM中使用它们时,也要十分小心,因为它们会让并发插入失效,可能会严重下降性能。

延时插入 DELAYED
这个操作只能用于 insert 和 replace
INSERT DELAYED INTO table1 set field1= …
INSERT DELAYED INTO,是客户端提交数据给MySQL,MySQL返回OK状态给客户端。而这是并不是已经将数据插入表,而是存储在内存里面等待排队。
当mysql有 空余时,再插入。另一个重要的好处是,来自许多客户端的插入被集中在一起,并被编写入一个块。这比执行许多独立的插入要快很多。
坏处是,不能返回自动递增 的ID,以及系统崩溃时,MySQL还没有来得及插入数据的话,这些数据将会丢失。并且导致last_insert_id()无法正常工作。

强制连接顺序straight_join
SELECT TABLE1.FIELD1, TABLE2.FIELD2 FROM TABLE1 STRAIGHT_JOIN TABLE2 WHERE...;

由上面的SQL语句可知,通过STRAIGHT_JOIN强迫MySQL按TABLE1、TABLE2的顺序连接表。如果你认为按自己的顺序比MySQL推荐的顺序进行连接的效率高的话,就可以通过STRAIGHT_JOIN来确定连接顺序。

分组使用临时表 SQL_BIG_RESULT和SQL_SMALL_RESULT

SELECT SQL_BUFFER_RESULT FIELD1, COUNT(*) FROM TABLE1 GROUP BY FIELD1;
这两个提示只对select语句有效,它们告诉优化器对 group by 或者 distinct 查询如何使用临时表及排序。
sql_small_result 告诉优化器结果集会很小,可以将结果集放在内存中的索引临时表,以避免排序操作;
sql_big_result 则告诉优化器结果集会很大,建议使用磁盘临时表做排序操作;

强制使用临时表sql_buffer_result

SELECT SQL_BUFFER_RESULT * FROM TABLE1 WHERE …;
这个提示告诉优化器将查询放入到一个临时表,然后尽可能地释放锁。这和前面提到的由客户端缓存结果不同。当你设法使用客户端缓存的时候,使用服务器端的缓存通常很有效。
带来的好处是无须在客户端消耗太多的内存,还可以尽可能快的释放对应的表锁。代价是,服务器端需要更多的内存。

查询缓冲
sql_cache 和 sql_no_cache
这个提示告诉mysql是否讲结果集缓存在查询缓存中。

关闭查询缓冲 SQL_NO_CACHE

SELECT SQL_NO_CACHE field1, field2 FROM TABLE1;

有一些SQL语句需要实时地查询数据,或者并不经常使用(可能一天就执行一两次),这样就需要把缓冲关了,不管这条SQL语句是否被执行过,服务器都不会在缓冲区中查找,每次都会执行它。

强制查询缓冲 SQL_CACHE

SELECT SQL_CALHE * FROM TABLE1;

如果在my.ini中的query_cache_type设成2,这样只有在使用了SQL_CACHE后,才使用查询缓冲。
sql_calc_found_rows
严格来说,这并不是一个优化器提示。它不会告诉优化器任何关于执行计划的东西。
它会让mysql返回的结果集包含更多的信息。查询中加上该提示,mysql会计算出去limit子句后这个查询返回的结果集的总数。
而实际上只返回limit要求的结果集。可以通过函数found_row()获得这个值。

锁相关 for update 和 lock in share mode
这两个提示主要控制select 语句的锁机制。但只对实现了行级锁的存储引擎有效。使用该提示会对符合查询条件的数据加锁。
对于insert...select 语句不需要这两个提示,因为会默认添加上锁。
唯一内置的支持这两个提示的引擎是innoDB。另外需要记住的是,这两个提示会让某些优化无法进行。例如索引覆盖扫描。
innoDB不能在不访问主键的情况下用排他锁锁定行,因为行的信息锁定在主键中。

详细见《MySQL锁之三:MySQL的共享锁与排它锁编码演示》

索引相关

use index, ignore index 和 force index:这几个提示用来告诉优化器是否使用索引来查询记录。

force index 和 use index 基本相同,除了一点:force index 会告诉优化器全表扫描的成本会远远高于索引扫描,哪怕实际该索引用处不大。

强制索引 FORCE INDEX
SELECT * FROM TABLE1 FORCE INDEX (FIELD1);
以上的SQL语句只使用建立在FIELD1上的索引,而不使用其它字段上的索引。
忽略索引 IGNORE INDEX

SELECT * FROM TABLE1 IGNORE INDEX (FIELD1, FIELD2);

在上面的SQL语句中,TABLE1表中FIELD1和FIELD2上的索引不被使用。

 

新增参数控制优化器的行为:
optimizer_search_depth

是否跳过执行计划optimizer_prune_level
该参数默认打开的,这让优化器会根据需要扫描的行数来决定是否跳过某些执行计划。

optimizer_switch
这个变量包含了一些开启/关闭优化器特性的标志位。例如mysql5.1 可以通过控制这个参数来控制禁用索引合并的特性。

本文由金沙棋牌发布于金沙棋牌app手机下载,转载请注明出处:开发进阶篇系列金沙棋牌app手机下载,mysql查询优

关键词: