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

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

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

一、什么是索引

索引在MySQL中也叫做键(key),它相当于字典的音序表或者书籍的目录,可以让存储引擎快速检索查询(即SELECT操作)到需要的数据。在MySQL的设计中,当查询结果达到了原表中的一定比例(大概30%左右),MySQL优化器会认为没有必要再使用索引,而直接采用全表扫描,这个比例与数据库的预读能力与参数有关。


二、MySQL索引分类

· 主键索引(PRIMARY KEY)

主键索引也被称为聚簇索引(clustered index),是一种特殊的唯一索引,它要求字段的值不能重复、不能为空,并且一张表只能有一个主键索引。主键索引最好建立在跟业务不相关、很少修改和删除并且最好是自增的字段上,比如ID字段。对于聚簇索引来说索引即数据,用主键查询数据至需要一次索引查找自增主键的插入模式符合递增插入的场景。每次插入一条新记录都是追加操作,不涉及挪动其他记录,也不会触发叶子节点的分裂。而有业务逻辑的字段做主键往往不容易保证有序插入,这样写数据成本相对较高。


· 非主键索引(secondary index)

非主键索引也被称为二级索引(secondary index),对于二级索引来说,存储的是主键信息,还需要根据主键再进行一次查询才能获取最终数据,这称为回表。

非主键索引又可以分为以下类型:

· INDEX:普通索引,也叫辅助索引。允许字段内容相同,是最常用的索引类型

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

· FULLTEXT INDEX:全文索引

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


· 主键索引和非主键索引的查询区别

select * from T where ID=500  #假设ID列为主键索引,该语句则使用主键查询,只需要搜索ID列这棵B+树
select * from T where name='tanglu'  #假设name列为普通索引,该语句需要先搜索name索引树,得到ID后再到ID索引树搜索,这个过程称为回表


· 覆盖索引

当一个索引包含了需要查询的所有字段时就称为覆盖索引,无需回表。比如worker表有索引(type,salary),以下查询就会使用到覆盖索引特性

select type,salary from worker where type='b'


三、索引创建原则

1、索引并不是越多越好

虽然索引可以提高查询速度,但是当执行数据更新操作时因为数据更新带来的索引重建反而会降低更新速度。这也是为什么导入大量数据时,先导入数据再建立索引先创建索引再导入数据更快的原因。除此之外,每个索引都会占据额外的存储空间。所以索引创建不能过度,通常单表不应该超过5个。否则就应该考虑表设计的合理性。


2、适合建立索引的字段

选择有唯一值的字段来创建索引,比如身份证号、电话号码;建议为经常作为where条件或者GROUP BY、ORDER BY进行分组排序的字段建立索引;


3、无需创建索引的情况

少量数据无需建立索引,比如200万行以内的小表可以不用建索引,全表扫描可能会更快


4、索引失效的原因

避免在索引字段上使用计算、NOT(!=、<>)、IS NULL、IT NOT NULL、%模糊查询、varchar与int隐式转换(比如数据类型是字符串,使用select * from table where id = '123'会走索引,而select * from table where id=123就不会)等情况,否则会导致索引失效慎用IN、NOT IN。IN和NOT IN所定义的值是不确定的,所以MySQL不会对这两种查询使用索引。对于连续的数值使用BETWEEN来代替IN。BETWEEN定义的是一个连续的区间,可以使用到索引

SELECT id, name, salary FROM worker WHERE salary IN (1, 2, 3);  #不走索引
SELECT id, name, salary FROM worker WHERE salary BETWEEN 1 AND 3;  #走索引


5、索引变慢的可能性

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



四、MySQL索引语法

1、查询表索引

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


2、创建索引

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

#方法1:alter table增加索引
alter table student add idx_name(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,说明没有匹配到索引 


3、删除索引

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


4、使用force index语句指定索引。常用于处理MySQL选错索引的情况,当MySQL优化器不能准确地判断出扫描行数时就会出现用错索引的问题。也可以用analyze table table_name命令重新统计索引信息

select * from test_table force index(test_index) where name='tanglu';


五、使用SQLAdvisor工具优化索引

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 -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

SQLADVISOR.png


温馨提示如有转载或引用以上内容之必要,敬请将本文链接作为出处标注,谢谢合作!
et_highlighter51
版权所有:《Linux运维技术学习站点
文章标题:《MySQL基础教程(8)MySQL索引管理与SQLAdvisor索引优化
除非注明,文章均为 《Linux运维技术学习站点》 原创
转载请注明本文短网址:http://www.linuxe.cn/post-294.html  [生成短网址]

已有 0/3052 人参与

发表评论:

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

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