mysqlin查询索引失效 mysql索引查询语句

转载

AI领域布道师 2024-07-02 06:54:00

文章标签 mysqlin查询索引失效 MySQL 数据库性能优化字段 文章分类 MySQL 数据库

一、查询MySQL的性能参数

Show status like ‘value’

其中，value是要查询的参数值，一些常用的性能参数如下

Connections:连接MySQL服务器的次数
Uptime：MySQL服务器上线时间
Slow_queries:慢查询的次数
Com_select:查询操作的次数
Com_insert:插入操作的次数
Com_update:更新操作的次数
Com_delete:删除操作的次数

二、优化查询

分析查询语句

explain [extended] select 查询语句

如：explain select *from fruits

2.describe select 查询语句

如：describe select *from fruits

使用索引查询

几种索引不起作用的特殊情况

使用like关键字的查询语句

如果匹配字符串的第一个字符为“%”，索引不起作用

如select *from fruits where f_name like’%x’; 【f_name已加索引】

2.使用多列索引的查询语句

MySQL可对多个字段创建索引，一个索引可以包含16个字段，对于多列索引，只有查询条件使用了这些字段中第一个字段时，索引才会被使用

如：create index index_id_price on fruits(f_id,f_price);

Select *from fruits where f_id=’12’;【起作用】

Select *from fruits where f_price=5.2;【不起作用】

3.使用or关键字的查询语句

查询语句的查询条件中只有or关键字，且or前后的两个条件中的列都是索引时，查询中才使用索引，否则，索引将不起作用

如：select *from fruits where f_name=’app%’ or f_id=’12’

【f_name和f_id两者都有索引，才起作用】

4.优化子查询

使用连接（join）查询来代替子查询。因为连接查询不需要在内存中创建临时表来完成查询操作

三. 优化数据库结构

1)将字段很多的表分解成多个表

例：会员表members中有id，姓名，密码，地址，电话，个人描述字段，其中地址，电话，个人描述不常用，将这3个字段抽取为members_detail(加上member_id)

如果要查询会员的所有信息，查询语句可如下：

Select *from menbers left join members_detail on members.id=members.id=members_detail.member_id;

2) 增加中间表

对于需要经常联合查询的表，可以建立中间表以提高查询效率，把需要经常联合查询的数据插入到中间表中，然后将原来的联合查询改为中间表查询

3）增加冗余字段

这个方法有利有弊，冗余字段会导致一些问题，比如冗余字段的值在这个表中被改变了，在其他表就要想办法更新，同时也会浪费一定的磁盘空间。根据实际需求综合分析

4）优化插入记录的速度

插入记录时，影响插入速度的主要是索引，唯一性校验，一次插入记录条数等

禁用索引

问题：插入大量数据，建立索引会降低插入记录的速度

解决：在插入记录之前禁用索引，数据插入完毕后在开启索引

禁用语句：alter table 表名disable keys;

开启语句：alter table 表名 enable keys;

禁用唯一性检查

问题与解决方法同上，先禁后开

禁用语句: set unique_checks=0;

开启语句：set unique_checks=1;

使用批量插入

如：insert into fruits values

(‘x1’,’100’,’apple’,’1.0’),

(‘x2’,’200’,’apple2’,’2.0’),

(‘x3’,’300’,’apple3’,’3.0’),

使用load data infle 批量导入

Load data infle 语句导入数据的速度比insert语句快;对于innodb引擎的表，常见的优化方法如下

a.先禁后开唯一性检查

Set unique_checks=0; Set unique_checks=0;

b.先禁后开外键检查

Set foreign_key_checks=0; Set foreign_key_checks=0;

c.先禁后开自动提交

Set autocommit=0; set autocommit=1;

5) 分析表，检查表，优化表

分析表：主要分析关键字的分布

Analyze [local|no_write_to_binlog] table 表名[,表名]…

如：analyze table fruits;

结果显示信息说明：Table:表示分析表的名称;Op:表示执行的操作

Msg_type:表示信息类型，其值通常是状态（status），信息（info），注意（note），警告（waring），错误（error）之一;Msg_text:显示信息

检查表：主要检查表是否存在错误

Check table 表名[,表名]…
option={quick|fast|medium|extened|changeg};
quick:不扫描行
fast：只检查没有被正常关闭的表
changed：只检查上次检查后被修改的表或没有被正确关闭的表
medium：扫描行
extended：对每行的所有关键字进行一个全面的关键字查找
• 优化表：主要是用于消除删除或者更新造成的空间浪费
Optimize [local|no_write_to_binlog] table 表名[,表名]…

在执行过程会给表加上只读锁，只能哟花表中的varchar,blob,text类型的字段，对innodb和myisam类型的表都有效