本站所有文章均为原创,如对您有帮助,恳请帮忙点击任何一处广告
  • 首页
  • MySQL
  • MySQL基础教程(8)MySQL索引管理与SQLAdvisor索引优化

MySQL基础教程(8)MySQL索引管理与SQLAdvisor索引优化

发布:TangLu2019-1-3 12:47分类: MySQL 标签: mysql 数据库 索引

一、MySQL索引的作用

索引在MySQL中也叫做键(key),可以让存储引擎快速找到所需数据,就像是通过字典的音序表或者书籍的目录去找正文一样,比一页一页翻要快N倍。但有一点要知道的是在MySQL的设计中,当查询结果达到了原表中的一定比例(大概30%左右),MySQL优化器会认为没有必要再使用索引,而直接采用全表扫描,这个比例与数据库的预读能力与参数有关。在MySQL中对索引也有一些自优化机制,了解即可:

· 自适应HASH索引(AHI):可以理解为对索引做索引,自动评估热索引,然后生成hash表。可以让InnoDB快速找到需要的索引

· 索引下推(ICP):解决联合索引只能部分字段应用的情况。

除了ICP外还有很多的优化器算法

show variables like '%optimizer_switch%' 


二、MySQL索引的分类

1、INDEX:普通索引,允许字段内容相同,是最常用的索引类型

2、PRIMARY KEY:主键索引,不允许字段值重复,不能有空值,一个表只能有一个主键索引。主键索引最好建立在跟业务不相关的字段上,且很少修改和删除,最好是自增的,比如ID字段。

3、UNIQUE INDEX:唯一索引,字段不允许重复,只可以有一个值为空

4、FULLTEXT INDEX:全文索引

5、组合索引:一个索引中包含了多列


三、索引的创建原则

1、索引不是越多越好,因为修改表时会对索引重构和更新。

2、选择有唯一值的字段来创建索引,比如身份证号、电话号码等唯一值

3、为常作为查询条件的字段建立索引。如某个字段经常用来做查询条件,那该字段的查询速度就会影响整个SQL,因为必要添加索引

4、经常排序和分组的数据列要建立索引。经常需要ORDER BY、GROUP BY等操作的字段可以添加索引。

5、200万行以内的小表不用建索引,全表扫描可能会更快

6、primary key和unique key字段,系统会自动创建对应的索引

7、很少变动的表可以多创建索引,经常变动的表有索引也有可能出现索引丢失,所以一旦出现本来很快的查询语句突然变慢了,就要考虑这个问题,进行索引重建。

8、在导入大量数据时,“先导入数据再为表建立索引”会比“先为表建立索引再导入数据”快许多,因为一个表已经建立过索引的话,每新增加一行数据都会导致重写索引,增加了额外操作

9、避免在索引字段上使用计算、NOT(!=、<>)、IS NULL、IT NOT NULL、%模糊查询、varchar与int隐式转换(比如数据类型是字符串,使用select * from table where id = '123'会走索引,而select * from table where id=123就不会)等情况,否则会导致索引失效


四、MySQL索引的增删改查

1、查询索引

mysql > select * from informaton_schema.statistics where table_schema=''database_test;  #查询指定库中的所有索引
mysql > show index from table_name;  #查询表中存在的索引


2、创建索引

为一张已存在的表新增普通索引

# create index 索引名 on 表名(列名)
create index idx_name on student(name)  #为student表的name列创建名为idx_name的索引


在创建MySQL表的同时创建唯一索引、组合索引

create table table1 (id int , name varchar(10) , age int , index idx_name ( name(10) ) ); #创建table1这张表时给name列建立普通索引,长度为10,名为name_index 
create table table2 (id int , name varchar(10) , age int , unique index id_index (id) ); #给ID列创建一个名为id_index的唯一索引 
create table table3 (id int , name varchar(10) , age int , index zuhe_index (id,name,age) );  #组合索引遵循从左匹配原则,必须要带有最左边ID列的查询才会使用到索引

# 展示下组合索引的最左匹配使用效果
explain select name,age from table3 where id<3 \G 
explain select name,age from table3 where id<3 and age <50 \G 
explain select name,age from table3 where  age <50 \G  #possible_keys是null,说明没有匹配到索引 


通过alter语句修改表结构来创建索引

alter table table1 add index index_name(name);  #为name字段建立索引,索引名为index_name 
alter table table1 drop index index_name; 

3、删除索引

#方法1:drop index 索引名 on 表名
drop index idx_name on student
#方法2:alter table 表名 drop index 索引名


五、使用SQLAdvisor工具优化索引

这是美团开源的一款索引优化建议工具,根据分析SQL中的where条件、聚合条件、多表join等给出优化建议。

1、下载SQLAdvisor:https://github.com/Meituan-Dianping/SQLAdvisor


2、安装依赖包

#安装依赖包
yum install cmake libaio-devel libffi-devel glib2 glib2-devel
#安装percona yum仓库
yum install http://www.percona.com/downloads/percona-release/redhat/0.1-3/percona-release-0.1-3.noarch.rpm
#编译安装SQLAdvisor会依赖perconaserverclient_r,由percona-server-shared-56提供
yum install Percona-Server-shared-56 --nogpgcheck
# ln -s libperconaserverclient_r.so.18 libperconaserverclient_r.so  #做软连接,已存在的话可以忽略


3、编译依赖项

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


4、安装SQLadvisor

cd SQLAdvisor/sqladvisor/
cmake -DCMAKE_BUILD_TYPE=debug ./
make  #make后生成一个sqladvisor可执行文件,这就是需要用到的工具,拷贝到/usr/bin下


5、SQLadvisor常用选项与示例

  -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


6、命令运行后,对于一些不是非常复杂的SQL都会给出一个索引优化建议,非常好用SQLADVISOR.png

sqladvisor -h 10.3.0.230  -P 3307  -u dba -p 'Hzdba666#@888' -d wp_ark_test -q "SELECT * FROM \`ark_attach\` ORDER BY create_time DESC" -v 1
温馨提示如有转载或引用以上内容之必要,敬请将本文链接作为出处标注,谢谢合作!
et_highlighter51
版权所有:《Linux运维技术学习站点
文章标题:《MySQL基础教程(8)MySQL索引管理与SQLAdvisor索引优化
除非注明,文章均为 《Linux运维技术学习站点》 原创
转载请注明本文短网址:http://www.linuxe.cn/post-294.html  [生成短网址]

已有 0/2389 人参与

发表评论:

欢迎分享Linux运维技术学习站点

欢迎使用手机扫描访问本站,还可以关注微信哦~