网站设计师简介,霸州有做滤芯网站的吗,wordpress主题 幻灯,做微课的网站MYSQL避免全表扫描
1.对查询进行优化#xff0c;应尽量避免全表扫描#xff0c;首先应考虑在 where 及 order by 涉及的列上建立索引
2.应尽量避免在 where 子句中对字段进行 null 值判断#xff0c;否则将导致引擎放弃使用索引而进行全表扫描
如#xff1a;select id f…MYSQL避免全表扫描
1.对查询进行优化应尽量避免全表扫描首先应考虑在 where 及 order by 涉及的列上建立索引
2.应尽量避免在 where 子句中对字段进行 null 值判断否则将导致引擎放弃使用索引而进行全表扫描
如select id from t where num is null可以在num上设置默认值0确保表中num列没有null值然后这样查询select id from t where num0
3.应尽量避免在 where 子句中使用或操作符否则引擎将放弃使用索引而进行全表扫描。
4.应尽量避免在 where 子句中使用or 来连接条件否则将导致引擎放弃使用索引而进行全表扫描可以使用union
5.in 和 not in 也要慎用否则会导致全表扫描能用 between 就不要用 in
6.下面的查询也将导致全表扫描。
select id from t where name like ‘%李%’select id from t where name like ‘%李’
若要提高效率可以使用此格式select id from t where name like ‘李%’也可以考虑全文检索。
7.避免在索引列上使用计算也就是说应尽量避免在 where 子句中对字段进行表达式操作和函数操作这将导致引擎放弃使用索引而进行全表扫描。
如select id from t where num/2100应改为select id from t where num100*2
8.很多时候用 exists 代替 in 是一个好的选择exists用于检查子查询是否至少会返回一行数据该子查询实际上并不返回任何数据而是返回值true或false。
select num from a where num in(select num from b)
用下面的语句替换select num from a where exists (select 1 from b where numa.num)
9.任何地方都不要使用 select from t 用具体的字段列表代替“”不要返回用不到的任何字段。
10.用替代
高效 SELECT * FROM EMP WHERE DEPTNO 4
低效 SELECT * FROM EMP WHERE DEPTNO 3
两者的区别在于 前者DBMS将直接跳到第一个DEPT等于4的记录而后者将首先定位到DEPTNO3的记录并且向前扫描到第一个DEPT大于3的记录。
11.用Where子句替换having子句
如何查看sql查询是否用到索引(mysql)
问题发现
我认为一条很简单的SQL然后跑了很久明明我已经都建立相应的索引逻辑也不需要优化。
SELECT a.custid, b.score, b.xcreditscore, b.lrscore
FROM (SELECT DISTINCT custidFROM sync.credit_applyWHERE SUBSTR(createtime, 1, 10) 2019-12-15AND rejectrule xxxx
) aLEFT JOIN (SELECT *FROM sync.credit_creditchannel) bON a.custid b.custid;查看索引状态
credit_apply表
mysql show index from sync.credit_apply;
---------------------------------------------------------------------------------------------------------------------------------------------------
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
---------------------------------------------------------------------------------------------------------------------------------------------------
| credit_apply | 0 | PRIMARY | 1 | applyId | A | 1468496 | NULL | NULL | | BTREE | | |
| credit_apply | 1 | index2 | 1 | custId | A | 666338 | NULL | NULL | | BTREE | | |
| credit_apply | 1 | index2 | 2 | createTime | A | 1518231 | NULL | NULL | | BTREE | | |
---------------------------------------------------------------------------------------------------------------------------------------------------或者
CREATE TABLE credit_apply (applyId bigint(20) NOT NULL AUTO_INCREMENT,custId varchar(128) COLLATE utf8mb4_unicode_ci NOT NULL,ruleVersion int(11) NOT NULL DEFAULT 1,rejectRule varchar(128) COLLATE utf8mb4_unicode_ci DEFAULT DP0000,status tinyint(4) NOT NULL DEFAULT 0,extra text COLLATE utf8mb4_unicode_ci,createTime timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,updateTime timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,mobile varchar(128) COLLATE utf8mb4_unicode_ci DEFAULT ,PRIMARY KEY (applyId) USING BTREE,KEY index2 (custId,createTime)
) ENGINEInnoDB AUTO_INCREMENT1567035 DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_unicode_cisync.credit_creditchannel表
mysql show index from sync.credit_creditchannel ;
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| credit_creditchannel | 0 | PRIMARY | 1 | recId | A | 450671 | NULL | NULL | | BTREE | | |
| credit_creditchannel | 1 | nationalId_custid | 1 | nationalId | A | 450770 | NULL | NULL | | BTREE | | |
| credit_creditchannel | 1 | nationalId_custid | 2 | custId | A | 450770 | NULL | NULL | YES | BTREE | | |
| credit_creditchannel | 1 | credit_creditchannel_custId | 1 | custId | A | 450770 | 10 | NULL | YES | BTREE | | |
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------或者
CREATE TABLE credit_creditchannel (recId bigint(20) NOT NULL AUTO_INCREMENT,nationalId varchar(128) NOT NULL DEFAULT ,identityType varchar(3) NOT NULL DEFAULT ,brief mediumtext,score decimal(10,4) NOT NULL DEFAULT 0.0000,npaCode varchar(128) NOT NULL DEFAULT ,basic mediumtext,createTime timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,updateTime timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,request mediumtext,custId varchar(128) DEFAULT ,xcreditScore decimal(10,4) DEFAULT 0.0000,queryTime varchar(24) DEFAULT ,lrScore decimal(10,4) DEFAULT 0.0000,PRIMARY KEY (recId) USING BTREE,KEY nationalId_custid (nationalId,custId),KEY credit_creditchannel_custId (custId(10))
) ENGINEInnoDB AUTO_INCREMENT586557 DEFAULT CHARSETutf8 我们都可以看到相应的索引。以现在简单的sql逻辑理论上走custid这个索引就好了
解释函数explain
mysql explain SELECT a.custid, b.score, b.xcreditscore, b.lrscore FROM(
SELECT DISTINCT custid FROM sync.credit_apply WHERE SUBSTR(createtime, 1, 10) 2019-12-15 AND rejectrule xxx) a
LEFT JOIN
(select * from sync.credit_creditchannel) b
ON a.custid b.custid;
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 1 | PRIMARY | derived2 | NULL | ALL | NULL | NULL | NULL | NULL | 158107 | 100.00 | NULL |
| 1 | PRIMARY | credit_creditchannel | NULL | ALL | NULL | NULL | NULL | NULL | 450770 | 100.00 | Using where; Using join buffer (Block Nested Loop) |
| 2 | DERIVED | credit_apply | NULL | index | index2 | index2 | 518 | NULL | 1581075 | 10.00 | Using where |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
3 rows in set (0.06 sec)如何去看我们的SQL是否走索引
我们只需要注意一个最重要的type 的信息很明显的提现是否用到索引
type结果
type结果值从好到坏依次是
system const eq_ref ref fulltext ref_or_null index_merge unique_subquery index_subquery range index ALL
一般来说得保证查询至少达到range级别最好能达到ref否则就可能会出现性能问题。
possible_keyssql所用到的索引
key显示MySQL实际决定使用的键索引。如果没有选择索引键是NULL
rows: 显示MySQL认为它执行查询时必须检查的行数。
分析
我们的credit_creditchannel是ALL而possible_keys是NULL索引在查询该表的时候并没有用到索引怪不得这么慢
分析和搜索解决办法
换着法的改sql也没用换着群问大神也没用各种搜索引擎搜才总算有点思路。
索引用不上的原因可能是字符集和排序规则不相同。
于是看了了两张表的字符集和两张表这个字段的字符集以及排序规则: 修改数据库和表的字符集
alter database sync default character set utf8mb4;//修改数据库的字符集
alter table sync.credit_creditchannel default character set utf8mb4;//修改表的字符集修改表排序规则
alter table sync.credit_creditchannel convert to character set utf8mb4 COLLATE utf8mb4_unicode_ci;由于数据库中的数据表和表字段的字符集和排序规则不统一批量修改脚本如下
1. 修改指定数据库中所有varchar类型的表字段的字符集为ut8mb4并将排序规则修改为utf8_unicode_ci
SELECT CONCAT(ALTER TABLE , table_name, MODIFY , column_name, , DATA_TYPE, (, CHARACTER_MAXIMUM_LENGTH, ) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci, CASE WHEN IS_NULLABLE NO THEN NOT NULLELSE END, ;)
FROM information_schema.COLUMNS
WHERE (TABLE_SCHEMA databaseNameAND DATA_TYPE varcharAND (CHARACTER_SET_NAME ! utf8mb4OR COLLATION_NAME ! utf8mb4_unicode_ci));2. 修改指定数据库中所有数据表的字符集为UTF8并将排序规则修改为utf8_general_ci
SELECT CONCAT(ALTER TABLE , table_name, CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;)
FROM information_schema.TABLES
WHERE TABLE_SCHEMA sync_rsexplain 查看是否用到了索引
mysql explain SELECT a.custid, b.score, b.xcreditscore, b.lrscore FROM(
SELECT DISTINCT custid FROM sync.credit_apply WHERE SUBSTR(createtime, 1, 10) 2019-12-15 AND rejectrule xxx) a
LEFT JOIN
(select * from sync.credit_creditchannel) b
ON a.custid b.custid;
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 1 | PRIMARY | derived2 | NULL | ALL | NULL | NULL | NULL | NULL | 146864 | 100.00 | NULL |
| 1 | PRIMARY | credit_creditchannel | NULL | ref | credit_creditchannel_custId | credit_creditchannel_custId | 43 | a.custid | 1 | 100.00 | Using where |
| 2 | DERIVED | credit_apply | NULL | index | index2 | index2 | 518 | NULL | 1468644 | 10.00 | Using where |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------就是这样
补充大全
可以看到结果中包含10列信息分别为
id、select_type、table、type、possible_keys、key、key_len、ref、rows、Extra对应的简单描述如下
id: select查询的序列号包含一组数字表示查询中执行select子句或操作表的顺序id如果相同可以认为是一组从上往下顺序执行在所有组中id值越大优先级越高越先执行select_type: 表示查询的类型。用于区别普通查询、联合查询、子查询等的复杂查询。table: 输出结果集的表 显示这一步所访问数据库中表名称显示这一行的数据是关于哪张表的有时不是真实的表名字可能是简称例如上面的ed也可能是第几步执行的结果的简称partitions:匹配的分区type:对表访问方式表示MySQL在表中找到所需行的方式又称“访问类型”。possible_keys:表示查询时可能使用的索引key:表示实际使用的索引key_len:索引字段的长度ref:列与索引的比较rows:扫描出的行数(估算的行数)filtered:按表条件过滤的行百分比Extra:执行情况的描述和说明
挑选一些重要信息详细说明 select_type SIMPLE 简单的select查询查询中不包含子查询或者UNIONPRIMARY 查询中若包含任何复杂的子部分最外层查询则被标记为PRIMARYSUBQUERY 在SELECT或WHERE列表中包含了子查询DERIVED 在FROM列表中包含的子查询被标记为DERIVED衍生MySQL会递归执行这些子查询把结果放在临时表中UNION 若第二个SELECT出现在UNION之后则被标记为UNION若UNION包含在FROM子句的子查询中外层SELECT将被标记为DERIVEDUNION RESULT 从UNION表获取结果的SELECT type mysql找到数据行的方式效率排名NULL system const eq_ref ref range index all
一般来说得保证查询至少达到range级别最好能达到ref。
system 表只有一行记录等于系统表这是const类型的特列平时不会出现这个也可以忽略不计const 通过索引一次就找到了const用于比较primary key 和 unique key因为只匹配一行数据所以很快。如果将主键置于where列表中mysql就能将该查询转换为一个常量eq_ref 唯一性索引扫描对于每个索引键表中只有一条记录与之匹配。常见于主键索引和唯一索引 区别于const eq_ref用于联表查询的情况ref 非唯一索引扫描返回匹配某个单独值的所有行本质上也是一种索引访问它返回所有匹配某个单独值的行然而他可能会找到多个符合条件的行所以他应该属于查找和扫描的混合体range 只检索给定范围的行使用一个索引来选择行一般是在where中出现between、、、in等查询范围扫描好于全表扫描因为他只需要开始于索引的某一点而结束于另一点不用扫描全部索引index Full Index ScanIndex与All区别为index类型只遍历索引树。通常比All快因为索引文件通常比数据文件小。也就是说虽然all和index都是读全表但是index是从索引中读取的而all是从硬盘读取的ALL Full Table Scan,将遍历全表以找到匹配的行
possible_keys
指出mysql能使用哪个索引在表中找到记录查询涉及到的字段若存在索引则该索引被列出但不一定被查询使用该查询可以利用的索引如果没有任何索引显示null 实际使用的索引如果为NULL则没有使用索引。可能原因包括没有建立索引或索引失效 查询中若使用了覆盖索引select 后要查询的字段刚好和创建的索引字段完全相同则该索引仅出现在key列表中 possible_keys为null
key
key列显示mysql实际决定使用的索引必然包含在possible_keys中。如果没有选择索引键是NULL。想要强制使用或者忽视possible_keys列中的索引在查询时指定FORCE INDEX、USE INDEX或者IGNORE index
key_len
表示索引中使用的字节数可通过该列计算查询中使用的索引的长度在不损失精确性的情况下长度越短越好。key_len显示的值为索引字段的最大可能长度并非实际使用长度即key_len是根据表定义计算而得不是通过表内检索出的。
ref
显示索引的那一列被使用了如果可能的话最好是一个常数。哪些列或常量被用于查找索引列上的值。
rows
根据表统计信息及索引选用情况大致估算出找到所需的记录所需要读取的行数也就是说用的越少越好
extra
包含不适合在其他列中显式但十分重要的额外信息 Using Index:表示相应的select操作中使用了覆盖索引Covering Index避免访问了表的数据行效率不错。如果同时出现using where表明索引被用来执行索引键值的查找如果没有同时出现using where表明索引用来读取数据而非执行查找动作。Using where:不用读取表中所有信息仅通过索引就可以获取所需数据这发生在对表的全部的请求列都是同一个索引的部分的时候表示mysql服务器将在存储引擎检索行后再进行过滤Using temporary表示MySQL需要使用临时表来存储结果集常见于排序和分组查询常见 group by ; order byUsing filesort当Query中包含 order by 操作而且无法利用索引完成的排序操作称为“文件排序”Using join buffer表明使用了连接缓存,比如说在查询的时候多表join的次数非常多那么将配置文件中的缓冲区的join buffer调大一些。Impossible wherewhere子句的值总是false不能用来获取任何元组Select tables optimized away这个值意味着仅通过使用索引优化器可能仅从聚合函数结果中返回一行No tables usedQuery语句中使用from dual 或不含任何from子句
以上两种信息表示mysql无法使用索引
using filesort 表示mysql会对结果使用一个外部索引排序而不是从表里按索引次序读到相关内容可能在内存或磁盘上排序。mysql中无法利用索引完成的操作称为文件排序using temporary: 使用了用临时表保存中间结果MySQL在对查询结果排序时使用临时表。常见于排序order by和分组查询group by。 大多数人都以为是才智成就了科学家他们错了是品格。—爱因斯坦
