首页 / MYSQL / MySQL索引优化20210412

MySQL索引优化20210412

内容导读

互联网集市收集整理的这篇技术教程文章主要介绍了MySQL索引优化20210412，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含8689字，纯文字阅读大概需要13分钟。

内容图文

MySQL索引优化20210412

概述

使用索引的最终目的:提高数据检索效率。

索引是-种供服务器在表中快速查找-一个行的数据库结构。合理使用索引能够大大提高数据库的运行效率。

在数据库中建立索引主要有以下作用。

(1)快速存取数据。

(2)既可以改善数据库性能，又可以保证列值的唯一性。

(3)实现表与表之间的参照完整性

(4)在使用orderby、groupby子句进行数据检索时，利用索引可以减少排序和分组的时间。

索引的类型

UNIQUE唯一索引，不可以出现相同的值，可以有NULL值。

INDEX普通索引，允许出现相同的索引内容。

PRIMARY KEY主键索引，不允许出现相同的值，钚能为NULL值，一个表只能有一个primary__key索引。

fulltext index 全文索引，以上述三种索引都是针对列的值发挥作用，但全文索引，可以针对值中的某个单词，比如一篇文章中的某个词

一索引优化工具SQLAdvisor使用

SQLAdvisor是美团开源的一款SQL索引优化建议工具，是由美团点评公司技术工程部DBA团队(北京)

开发维护的一个分析SQL给出索引优化建议的工具。

它基于MySQL原生态词法解析，结合分析SQL中的where条件、聚合条件、多表Join关系给出索引优化建议。

目前SQLAdvisor在美团点评内部广泛应用，公司内部对SQLAdvisor的开发全面转到github上，开源和内部使用保持一致。

1.1 SQLAdvisor安装

1 下载文件

https://github.com/Meituan-Dianping/SQLAdvisor

安装依赖包

yum install cmake libaio-devel libffi-devel glib2 glib2-devel -y

2 Percona-Server-shared-共享兼容包:

下载安装Percona-Server-shared-56 yum源及安装共享兼容包:

https://www.percona.com/downloads/Percona-Server-5.6/LATEST/

rpm -ivh Percona-Server-shared-56-5.6.51-rel91.0.1.el6.x86_64.rpm

warning: Percona-Server-shared-56-5.6.51-rel91.0.1.el6.x86_64.rpm: Header V4 RSA/SHA256 Signature, key ID 8507efa5: NOKEY

Preparing... ################# [100%]

1:Percona-Server-shared-5############## [100%]

3 编译sqladvisor的依赖项sqlparser

unzip SQLAdvisor-master.zip

cd SQLAdvisor-master

cmake -DBUILD_CONFIG=mysql_release -DCMAKE_BUILD_TYPE=debug -DCMAKE_INSTALL_PREFIX=/usr/local/sqlparser ./

make && make install

4 安装SQLAdvisor源码

cd SQLAdvisor

cmake -DCMAKE_BUILD_TYPE=debug ./

make

在本路径下生成一个sqladvisor可执行文件，这即是我们想要的。

/usr/bin/ld: cannot find -lperconaserverclient_r

collect2: ld 返回 1

make[2]: *** [sqladvisor] 错误 1

make[1]: *** [CMakeFiles/sqladvisor.dir/all] 错误 2

make: *** [all] 错误 2

[root@mysqldb sqladvisor]# find / -name *perconaserverclient_r*

/usr/lib64/libperconaserverclient_r.so.18

/usr/lib64/libperconaserverclient_r.so.18.1.0

cd /usr/lib64/

[root@mysqldb lib64]# ln -s libperconaserverclient_r.so.18 libperconaserverclient_r.so

cmake -DCMAKE_BUILD_TYPE=debug ./

-- Configuring done

-- Generating done

-- Build files have been written to: /mysql/app/SQLAdvisor-master/sqladvisor

make

Linking CXX executable sqladvisor

cp sqladvisor /usr/bin/sqladvisor

5 sqladvisor --help 帮助

sqladvisor --help

用法： sqladvisor [选项...] sqladvisor

SQL Advisor Summary 帮助选项：

-?, --help 显示帮助选项

应用程序选项：

-f, --defaults-file sqls file

-u, --username username

-p, --password password

-P, --port port

-h, --host host

-d, --dbname database name

-q, --sqls sqls

-v, --verbose 1:output logs 0:output nothing

1.2 SQLAdvisor使用

1 命令行传参调用

sqladvisor -h xx? -P xx? -u xx -p 'xx' -d xx -q "sql" -v 1

sqladvisor -h 192.168.247.131 -p 3306 -u root -p 'root' -d syj -q "select? * from m1? where name='itpux30200854'" -v 1

2021-04-13 23:10:26 21391 [Note] 第9步：开始输出表m1索引优化建议:

2021-04-13 23:10:26 21391 [Note] Create_Index_SQL：alter table m1 add index idx_name(name)

2 配置文件传参调用

cat sql.cnf

[sqladvisor]

username=xx

password=xx

host=xx

port=xx

dbname=xx

sqls=sql1;sql2;sql3....

cmd: ./sqladvisor -f sql.cnf? -v 1

`二` `索引优化案例`

优化SQL最大的特点之一，就是如何让SQL使用索引。

analyze table m1db;

可以使用下面三个SQL来清理查询缓存:

FLUSH QUERY CACHE

清理查询缓存内存碎片。

RESET QUERY CACHE

从查询缓存中移出所有查询。

FLUSH TABLES

/关闭所有打开的表，同时该操作将会清空查询缓存中的内容。

RESET QUERY CACHE;

FLUSH QUERY CACHE;

FLUSH TABLES;

analyze table m1db;

show table status like 'm1db';

临时关闭

set global query_cache_size=0;

set global query_cache_type=0;

select * from m1db where name='itpux30010028';

8秒

explain select * from m1db where name='itpux30010028';

sqladvisor -h 192.168.247.131 -p 3306 -u root -p 'root' -d syjdb -q 'select * from m1db where name='itpux30010028'' -v 1

2021-04-15 20:12:09 11349 [Note] Create_Index_SQL：alter table m1db add index idx_name(name)

2021-04-15 20:12:09 11349 [Note] 第10步: SQLAdvisor结束!

explain select * from m1db where name='itpux30010028';

type ref 使用索引

2.1 如何建立索引

千万级/亿级的大表,生产环境如何快速创建索引?

亿级的表,一般20分钟内可以搞定。

千万级表:可以考虑到业务低峰期的时间去操作。

亿级表:不建议业务时间了，建议使用percona toolkit(pt-online-schema-change)在线在表加索引或改结构。

2.2 MySQL 线上CPU100%

1top分析性能，确认mysqld进程占用了所有的资源，如果是多实例，要先分析是哪一个实例占用了CPU

2查看iostat, vmstat ,free,分析OS性能。

3查看error日志

4show eninge innodb status/G,死锁。

5show full processlist，查时间长的对象，看并发，是不是大量并发/锁引起的。

6 慢查询日志，找出执行长的SQL,是不是走索引，优化SQL。

2.2 组合索引优化

注意，这两种称呼是对建立索引技巧的一种称呼，并非索引的类型。

应用场景一:SQL查询列很少，建立查询列的联合索引可以有效消除回表，但一般超过3个字段的联合索引都是不合适的.

应用场景二:在字段A返回记录多，在字段B返回记录多，在字段A,B同时查询返回记录少，比如执行查询，

结果c1,c2都很多，c3却很少。

--等值查询:

一个索引最多不能超过3个，MySQL数据库。

select *from ITPUX_YG where name='itpux20000' and dept='技术部’and salary=18000;

create index idx1_ITPUX_YG on ITPUX_YG(name,dept,salary);

explain select *from ITPUX_YG where name='itpux20000' and dept='技术部’ and salary=18000;

2.3 索引的害处

表上有过多索引主要会严重影响插入性能;

对delete操作，删除少量数据索引可以有效快速定位，提升删除效率，但是如果删除大量数据就会有负面影响;对update操作类似delete，而且如果更新的是非索引列则无影响。

1查询那些表没有索引，甚至没有主键

select * from INFORMATION_SCHEMA.tables

where table_schema = 'syjdb'

and table_name not in

(

select table_name -- , count(*)

from (

SELECT table_name, index_name

FROM information_schema.statistics

WHERE table_schema = 'syjdb'

GROUP BY table_name, index_name) tab_ind_cols

group by table_name

)

三高效使用索引

3.1避免在索引列上使用NOT(!=、<>)

select name, age,sex from YG where name='itpux12345'; --索引生效高效

select name , age,sex from YG where name!='itpux12345';--索引失效低效

select name , age,sex from YG where name<> 'itpux12345'; --索引失效低效

select name , age,sex from YG where name not in ( 'itpux12345'); --索引失效低效

3.2避免在索引列上使用计算.

create index idx_ YG_salary on ITPUX_YG(salary);

select age,sex from YG where salary*12>25000;-- 低效

select count(*) from ITPUX_YG where salary > 25000/12;--高效

3. 3避免在索引列上使用IS NULL和IS NOT NULL

create index idx_ITPUX_YG_name on ITPUXYG(name) ;

select *from ITPUX_YG where name is not null; --低效

select*from ITPUX_YG where name > '0'; --高效

select *from ITPUX_YG where name is null; --低效

select* from ITPUX_YG where name = '0';--高效

3.4注意通配符%的影响

select *from ITPUX_YG where name like '%itpux123%';--低效,索引失效select * from ITPUX_YG where name like 'itpux123%';--高效

3.5索引在什么情况下效果不好

--查询结果超过30%的表记录。

--有时候索引比全表慢，但这是同一个数据量级的区别，小表。

单表索引最多不超过4个，一个索引最多不能超过3个字段。

3.6避免在索引上使用函数

create index idx_ITPUX_YG_joindate on ITPUX_YG(joindate);

select count(*) from ITPUX_MEMBER where DATE(joindate)> '20161115';-- 低效

select count(*) from ITPUX_MEMBER where joindate > DATE('20161115');--高效

select count(*) from ITPUX_YG where round (salary+11.11)>5000;--低效

3.7 用(UNION)UNION ALL替换OR(适用于索引列)

select* from ITPUX_YG where jobid=12345 union all select *from ITPUx_YG where region='四川省'; --高效

select * from ITPUX_YG where jobid=12345 or region='四川省';--低效

3.8如果语句能够避免子查询的使用，就尽量不用子查询。因为子查询的开销是相当昂贵的。

3.9 活用COMMIT

经常将几个相互联系的DML语句写在BEGIN...END，如果不影响事务的完整性，

则建议在每个END前面写一个COMMIT，以达到对DML的及时提交和释放事务所占的资源的目的。

COMMIT释放的资源包括:

UNDO段上用于恢复数据的信息

事物中DML语句获得的锁

重做日志缓冲区中的空间

MySQL管理相关资源(如上述资源)而开销的内部资源

3.10 MySQL在join操作中，超过三个表最好不要join.

3.11 explain 中的 type

SQL 性能优化 explain 中的 type:至少要达到range级别，要求是ref 级别，如果可以是consts最好。consts:单表中最多只有一个匹配行（主键或者唯一索引)，在优化阶段即可读取到数据。

ref:使用普通的索引(Normal Index) 。

range:对索引进行范围检索。

当type=index时，索引物理文件全扫，速度非常慢。

内容总结

以上是互联网集市为您收集整理的MySQL索引优化20210412全部内容，希望文章能够帮你解决MySQL索引优化20210412所遇到的程序开发问题。如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。

内容备注

版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容，请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。

内容手机端

扫描二维码推送至手机访问。

本文链接：https://qyyshop.com/info/858974.html

来源：【匿名】

【上一篇】mysql 索引【下一篇】用IE远程创建Mysql数据库的简易程序

更多 ►

【MySQL索引优化20210412】教程文章相关的互联网学习教程文章

理解MySQL——索引与优化【图】

转自：理解MySQL——索引与优化写在前面：索引对查询的速度有着至关重要的影响，理解索引也是进行数据库性能调优的起点。考虑如下情况，假设数据库中一个表有10^6条记录，DBMS的页面大小为4K，并存储100条记录。如果没有索引，查询将对整个表进行扫描，最坏的情况下，如果所有数据页都不在内存，需要读取10^4个页面，如果这10^4个页面在磁盘上随机分布，需要进行10^4次I/O，假设磁盘每次I/O时间为10ms(忽略数据传输时间)，则总共需...

MySQL索引优化实例说明

下面分别创建三张表，并分别插入1W条简单的数据用来测试，详情如下：[1] test_a 有主键但无索引 CREATE TABLE `test_a` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `title` varchar(100) NOT NULL, `content` text NOT NULL, `number` int(10) unsigned NOT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8; [2] test_b 有主键和单列索引 CREATE TABLE `test_b` ( `id` int(10) uns...

MySQL函数索引及优化【代码】【图】

很多开发人员在使用MySQL时经常会在部分列上进行函数计算等，导致无法走索引，在数据量大的时候，查询效率低下。针对此种情况本文从MySQL5.7 及MySQL8.0中分别进行不同方式的优化。1、 MySQL5.7MySQL5.7版本中不支持函数索引，因此遇到函数索引的时候需要进行修改，否则即使查询的字段上有索引，执行时也无法使用索引而进行全表扫描，数据量大的表查询时间会比较长。具体案例如下：1.1 创建测试表及数据mysql>use testdb; Databas...

MYSQL索引优化【图】

1.索引的作用：　　（1）提高查询速度　　（2）提高排序速度　　（3）提高分组统计的速度2.索引的解析：explain select * from t4 where a1=3 and a2>4 and a3= 53.聚簇索引和非聚簇索引myisam 存储引擎使用的是非聚簇索引innodb 采用的是聚簇索引（主键使用），其他字段的索引是次级索引 innodb的主索引文件上，直接存放该行数据称为聚簇索引，次索引指向对主键的引用。myisam 中，主索引和次索引，都指向物理行（磁盘位置）4.索引...

MYSQL索引优化之单表示例【图】

1. 创建表　CREATE TABLE IF NOT EXISTS `article` ( `id` BIGINT(10) NOT NULL AUTO_INCREMENT, `author_id` INT(10) NOT NULL, `category_id` INT(10) NOT NULL, `views` INT(10) NOT NULL, `comments` INT(10) NOT NULL, `title` VARCHAR(10) COLLATE utf8_unicode_ci NOT NULL, `content` TEXT COLLATE utf8_unicode_ci NOT NULL, PRIMARY KEY (`id`)) ENGINE=INNODB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 COLLATE=u...

MySQL——索引优化实战

上篇文章中介绍了索引的基本内容，这篇文章我们继续介绍索引优化实战。在介绍索引优化实战之前，首先要介绍两个与索引相关的重要概念，这两个概念对于索引优化至关重要。本篇文章用于测试的user表结构：索引相关的重要概念基数单个列唯一键（distict_keys）的数量叫做基数。SELECT COUNT(DISTINCT name),COUNT(DISTINCT gender) FROM user;user表的总行数是5，gender 列的基数是 2，说明 gender 列里面有大量重复值，name 列的基数...

mysql优化工具（索引优化）

mysql优化工具1.pt-duplicate-key-checker(检查数据库的重复索引),这款工具可以帮助我们找到重复的索引并且还会给你删除重复索引的建议语句，非常好用。 2. 原文：https://www.cnblogs.com/ccw869476711/p/11910095.html

MySQL的索引及其优化【图】

前言索引对查询的速度有着至关重要的影响，理解索引也是进行数据库性能调优的起点。考虑如下情况，假设数据库中一个表有10^6条记录，DBMS的页面大小为4K，并存储100条记录。如果没有索引，查询将对整个表进行扫描，最坏的情况下，如果所有数据页都不在内存，需要读取10^4个页面，如果这10^4个页面在磁盘上随机分布，需要进行10^4次I/O，假设磁盘每次I/O时间为10ms(忽略数据传输时间)，则总共需要100s(但实际上要好很多很多)。如果对...

MySQL Order By索引优化方法

尽管 ORDER BY 不是和索引的顺序准确匹配，索引还是可以被用到，只要不用的索引部分和所有的额外的 ORDER BY 字段在 WHERE 子句中都被包括了。使用索引的MySQL Order By 下列的几个查询都会使用索引来解决 ORDER BY 或 GROUP BY 部分：复制代码代码如下:SELECT * FROM t1 ORDER BY key_part1,key_part2,... ; SELECT * FROM t1 WHERE key_part1=constant ORDER BY key_part2; SELECT * FROM t1 WHERE key_part1=constant GROUP ...

MySQL索引优化--对前缀索引使用like模糊匹配时的实际索引选择

由于我在最近的项目中对mysql的某张表的varchar列加上前缀索引后，在查询语句中即使where子句里只有course_num like "4%"这个条件，通过使用explain发现还是会走all类型进行全表查询。随后，我发现用绝大多数的博文中的数据进行复盘测试时，得到的结果和他们的对不上，在翻阅MySQL的优化器相关知识时，绝大多数博文中写的形如"4%"这种百分号在后面的一定会走range类型用前缀索引进行查询，这不是绝对正确的，在一些情况下不会走前缀...

MySQL架构、存储索引、缓存、索引优化【代码】【图】

文章目录 MySQL的架构详解存储引擎MyISAMInnoDB其他存储引擎管理存储引擎 MySQL中的系统数据库mysql 数据库performance_schema 数据库information_schema 数据库sys 数据库 MySQL服务器配置及状态命令行选项及服务器选项服务器系统变量服务器状态变量服务器变量SQL_MODE 查询缓存 Query Cache查询缓存的原理查询缓存相关的服务器变量SELECT语句的缓存控制查询缓存相关的状态变量查询的优化命中率和内存使用率估算参考文献：MySQL的...

小蚂蚁学习mysql性能优化（3）--SQL以及索引优化--慢查日志分析工具和explain说明

昨天在测试操作数据库的时候碰到两个问题忘了记录下来，今天补充上去，接上篇 1. 安装测试数据库sakila时报错。Mysql server has gone away的问题。解决方法：查看 show global variables like ‘max_allowed_packet‘; 一般来说会显示 max_allowed_packet 1048576 修改为 set global max_allowed_packet = 1024*1024*16; 问题解决。不要问我为什么，我也不知道 T_T 2. 我用 mysql 版本 5.6.* 在设置l...

mysql5.6.20开启慢查询日志以及创建索引优化慢查询

root@localhost : 100.00% (6) of query, 100.00% (6) of all usersQuery abstract:SELECT user_id,COUNT(id) AS num,MAX(login_time) AS last_login_time FROM tab_user_login_record WHERE login_time BETWEEN N AND N GROUP BY user_id;Query sample:SELECT `user_id`,COUNT(id) AS num,MAX(login_time) AS last_login_time FROM `tab_user_login_record` WHERE `login_time` BETWEEN 1501862400 AND 1503158399 GROUP BY user...

MySQL第9天：MySQL索引优化分析之join查询【代码】【图】

MySQL索引优化分析之join查询 #编写时间：2017.3.12 #编写地点：广州常见join查询： 1、SQL执行顺序：手写、机读、总结（1）手写（2）机读（3）总结2、join图 3、建立SQL 建立部门表和员工表4、7种join （1）练习1（2）练习2（3）练习3（4）练习4（5）练习5（6）练习6注意：mysql不支持FULL OUTER；union表示合并且去重。（7）练习7

MySQL如何利用索引优化ORDER BY排序语

MySQL索引通常是被用于提高WHERE条件的数据行匹配或者执行联结操作时匹配其它表的数据行的搜索速度。 MySQL也能利用索引来快速地执行ORDER BY和GROUP BY语句的排序和分组操作。通过索引优化来实现MySQL的ORDER BY语句优化：1、ORDER BY的索引优化。如果一个SQL语句形如： SELECT [column1],[column2],…. FROM [TABLE] ORDER BY [sort];在[sort]这个栏位上建立索引就可以实现利用索引进行order by 优化。2、WHERE + ORDER BY的索引...

MYSQL - 技术教程分类

MySQL 教程 MySQL 安装 MySQL 管理 MySQL PHP 语法 MySQL 连接 MySQL 创建数据库 MySQL 删除数据库 MySQL 选择数据库 MySQL 数据类型 MySQL 创建数据表 MySQL 删除数据表 MySQL 插入数据 MySQL 查询数据 MySQL WHERE 子句 MySQL UPDATE 更新 MySQL DELETE 语句 MySQL LIKE 子句 MySQL UNION MySQL 排序 MySQL 分组 MySQL 连接的使用 MySQL NULL 值处理 MySQL 事务 MySQL ALTER命令 MySQL 索引 MySQL 临时表 MySQL 复制表 MySQL 元数据 MySQL 序列使用 MySQL 处理重复数据 MySQL 及 SQL 注入 MySQL 导出数据 MySQL 导入数据 MySQL 函数 MySQL 运算符 mysql 全部

MYSQL - 最热教程

sql分组取最大记录方法 mysql如何设置默认值 mysql创建数据表时指定默认值教程 MySQL 5.7 的初始化操作（root初始密码...Oracle中合并数据集(多行变一行)mysql 相同内容的字段合并为一条的方法解决mysql设置时区时的错误Unknown or ...解决ubuntu下mysql的'Access denied fo...ubuntu系统中MysqlERROR1045(28000)报错...mysql数据库设置不区分大小写

首页 / MYSQL / MySQL索引优化20210412

MySQL索引优化20210412

内容导读

内容图文

概述

一 索引优化工具SQLAdvisor使用

1.1 SQLAdvisor安装

1 下载文件

2 Percona-Server-shared-共享兼容包:

3 编译sqladvisor的依赖项sqlparser

4 安装SQLAdvisor源码

5 sqladvisor --help 帮助

1.2 SQLAdvisor使用

1 命令行传参调用

2 配置文件传参调用

二 索引优化案例

2.1 如何建立索引

2.2 MySQL 线上CPU100%

2.2 组合索引优化

2.3 索引的害处

1查询那些表没有索引，甚至没有主键

三 高效使用索引

3.1避免在索引列上使用NOT(!=、<>)

3.2避免在索引列上使用计算.

3. 3避免在索引列上使用IS NULL和IS NOT NULL

3.4注意通配符%的影响

3.5索引在什么情况下效果不好

3.6避免在索引上使用函数

3.7 用(UNION)UNION ALL替换OR(适用于索引列)

3.8如果语句能够避免子查询的使用，就尽量不用子查询。因为子查询的开销是相当昂贵的。

3.9 活用COMMIT

3.10 MySQL在join操作中，超过三个表最好不要join.

3.11 explain 中的 type

内容总结

内容备注

内容手机端

【MySQL索引优化20210412】教程文章相关的互联网学习教程文章

MYSQL - 相关标签

MYSQL - 技术教程分类

MYSQL - 最新教程

MYSQL - 最热教程

一索引优化工具SQLAdvisor使用

`二` `索引优化案例`

三高效使用索引