本文共 6860 字，大约阅读时间需要 22 分钟。

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时，索引物理文件全扫，速度非常慢。

转载地址：http://habai.baihongyu.com/