【mysql】索引的优化,mysql索引

发布时间:2019-05-25  栏目:SQL  评论:0 Comments

mysql索引以及优化,mysql索引优化

明天看来外人写的某个有关mysql索引的稿子,有部分小收获,就以此开启本人的随笔记录简单摘了有的第3

转发文章:http://www.cnblogs.com/tgycoder/p/5410057.html

mysql索引实现原理

    1.
MyISAM引擎运用B+Tree作为目录结构,叶结点的data域存放的是数额记录的地址,MyISAM的目录情势也叫做“非集中”的,之所以那样称呼是为了与InnoDB的集中索引区分。

  图片 1

 二.
InnoDB也接纳B+Tree作为目录结构,第二个非常重要分歧是InnoDB的数据文件自己正是索引文件,第多个基本点差别是InnoDB的数据文件本身就是索引文件。从上文知道,MyISAM索引文件和数据文件是分别的,索引文件仅保留数据记录的地点。而在InnoDB中,表数据文件本身正是按B+Tree协会的1个索引结构,那棵树的叶结点data域保存了1体化的数据记录。那几个目录的key是数据表的主键,由此InnoDB表数据文件本人正是主索引

   图片 2

InnoDB主索引(同期也是数据文件)的暗意图,可以看出叶结点包罗了总体的数额记录。这种索引叫做集中索引。因为InnoDB的数据文件自己要按主键集中,所以InnoDB须求表必须有主键(MyISAM可以未有),假使未有显式内定,则MySQL系统会自行选拔四个足以唯1标志数据记录的列作为主键,倘使不设有这种列,则MySQL自动为InnoDB表生成一个饱含字段作为主键,这些字段长度为伍个字节,类型为长整形。

  1. 最左前缀与有关优化

   
在此之前笔者理解的最左前缀以为索引的各种是跟where条件查询的同一不平等就利用不到目录,那是不当的

  Ps:最左前缀原则中where字句有or现身依旧会遍历全表

   (一)
其实where条件的1壹不影响使用索引,举例两个字段增加联合索引t_user表联合索引(name,
mobile, create_date)

     select * from t_user where mobile = ‘13256767876’ and
create_date= ‘2017-07-31’ and name = ‘corner’;

   
  理论上索引对各类是灵动的,可是出于MySQL的查询优化器会自动调度where子句的规范顺序以应用符合的目录,所以这么也是能够用到目录的

    (二)查询条件尚未点名索引第二列

   倘若where条件中从不name条件,唯有其余七个无论顺序是何许皆以无能为力用到目录的,借使where条件唯有name,status而尚未mobile那时候不得不用到1列索引,status那壹列的目录是用不到的

     (三)范围查询

            
范围列能够用到目录(必须是最左前缀),可是范围列前边的列不可能用到目录。同有的时候候,索引最多用于七个范围列,因而只要查询条件中有三个范围列则无法全用到目录

             表t_title联合索引(emp_no,title,from_date)

EXPLAIN SELECT * FROM employees.titles
WHERE emp_no < '10010'
AND title='Senior Engineer'
AND from_date BETWEEN '1986-01-01' AND '1986-12-31';

+----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table  | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
+----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+
|  1 | SIMPLE      | titles | range | PRIMARY       | PRIMARY | 4       | NULL |   16 | Using where |
+----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+

  只能用到第一列索引,这里特别要说明MySQL一个有意思的地方,那就是仅用explain可能无法区分范围索引和多值匹配,因为在type中这两者都显示为range。同时,用了“between”并不意味着就是范围查询,例如下面的查询:
全部索引都用到了

EXPLAIN SELECT * FROM employees.titles
WHERE emp_no BETWEEN '10001' AND '10010'
AND title='Senior Engineer'
AND from_date BETWEEN '1986-01-01' AND '1986-12-31';

+----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table  | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
+----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+
|  1 | SIMPLE      | titles | range | PRIMARY       | PRIMARY | 59      | NULL |   16 | Using where |
+----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+

(4)查询条件中含有函数或表达式
如果查询条件中含有函数或表达式,则MySQL不会为这列使用索引
like如果通配符%不出现在开头,则可以用到索引,但根据具体情况不同可能只会用其中一个前缀

EXPLAIN SELECT * FROM employees.titles WHERE emp_no='10001' AND title LIKE 'Senior%';
+----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table  | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
+----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+
|  1 | SIMPLE      | titles | range | PRIMARY       | PRIMARY | 56      | NULL |    1 | Using where |
+----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+

4.索引选择性与前缀索引
   (1)什么情况下判断字段是否应该建立索引,今天刚看到这个"选择性"的概念,除了表数据很少的情况不用建索引因为索引文件本身要消耗存储空间会加重数据库操作的负担,另外一种情况就是索引的选择性比较低:
  所谓索引的选择性(Selectivity),是指不重复的索引值(也叫基数,Cardinality)与表记录数(#T)的比值:Index Selectivity = Cardinality / #T
  显然选择性的取值范围为(0, 1],选择性越高的索引价值越大,这是由B+Tree的性质决定的。

          
那些主题素材就像面试时咨询小编的一个题目:性别列适不吻合创设目录?(答案是或不是认的)

          
举个例子,上文用到的employees.titles表,假诺title字段平常被单独查询,是或不是必要建索引,大家看一下它的选拔性:

SELECT count(DISTINCT(title))/count(*) AS Selectivity FROM employees.titles;
+-------------+
| Selectivity |
+-------------+
|      0.0000 |
+-------------+

title的选取性不足0.000一(正确值为0.0000157九),所以实际上未有怎么要求为其独自建索引。

 
(二)有壹种与索引选用性有关的目录优化战略叫做前缀索引,便是用列的前缀代替整个列作为索引key,当前缀长度合适时,能够完毕既使得前缀索引的选拔性周围全列索引,同期因为索引key变短而缩减了目录文件的深浅和维护花费。上边以employees.employees表为例介绍前缀索引的抉择和平运动用。

从图1二能够见到employees表唯有多个索引<emp_no>,那么壹旦大家想按名字寻觅一人,就只可以全表扫描了:

EXPLAIN SELECT * FROM employees.employees WHERE first_name='Eric' AND last_name='Anido';
+----+-------------+-----------+------+---------------+------+---------+------+--------+-------------+
| id | select_type | table     | type | possible_keys | key  | key_len | ref  | rows   | Extra       |
+----+-------------+-----------+------+---------------+------+---------+------+--------+-------------+
|  1 | SIMPLE      | employees | ALL  | NULL          | NULL | NULL    | NULL | 300024 | Using where |
+----+-------------+-----------+------+---------------+------+---------+------+--------+-------------+

只要频仍按名字寻觅员工,那样鲜明作用非常低,由此大家能够设想建索引。有二种选拔,建<first_name>或<first_name,
last_name>,看下两个目录的选择性:

SELECT count(DISTINCT(first_name))/count(*) AS Selectivity FROM employees.employees;
+-------------+
| Selectivity |
+-------------+
|      0.0042 |
+-------------+
SELECT count(DISTINCT(concat(first_name, last_name)))/count(*) AS Selectivity FROM employees.employees;
+-------------+
| Selectivity |
+-------------+
|      0.9313 |
+-------------+

<first_name>显著选用性太低,<first_name,
last_name>选拔性很好,然而first_name和last_name加起来长度为30,有未有专职长度和选取性的章程?能够设想用first_name和last_name的前多少个字符构建目录,举例<first_name,
left(last_name, 三)>,看看其选拔性:

SELECT count(DISTINCT(concat(first_name, left(last_name, 3))))/count(*) AS Selectivity FROM employees.employees;
+-------------+
| Selectivity |
+-------------+
|      0.7879 |
+-------------+

选择性还不易,但离0.93一三依旧有一些距离,那么把last_name前缀加到4:

SELECT count(DISTINCT(concat(first_name, left(last_name, 4))))/count(*) AS Selectivity FROM employees.employees;
+-------------+
| Selectivity |
+-------------+
|      0.9007 |
+-------------+

此时选取性已经很特出了,而以此目录的尺寸唯有1八,比<first_name,
last_name>短了近似四分之2,我们把这几个前缀索引 建上:

ALTER TABLE employees.employees
ADD INDEX first_name_last_name4 (first_name, last_name(4));

那儿再实践一回按名字查询,相比较解析一下与建索引前的结果:

SHOW PROFILES;
+----------+------------+---------------------------------------------------------------------------------+
| Query_ID | Duration   | Query                                                                           |
+----------+------------+---------------------------------------------------------------------------------+
|       87 | 0.11941700 | SELECT * FROM employees.employees WHERE first_name='Eric' AND last_name='Anido' |
|       90 | 0.00092400 | SELECT * FROM employees.employees WHERE first_name='Eric' AND last_name='Anido' |
+----------+------------+---------------------------------------------------------------------------------+

 

 

http://www.bkjia.com/Mysql/1220430.htmlwww.bkjia.comtruehttp://www.bkjia.com/Mysql/1220430.htmlTechArticlemysql索引以及优化,mysql索引优化
明天看看人家写的一些有关mysql索引的篇章,有1部分小收获,就这些开启自个儿的随笔记录轻便摘了一部分重视…

【mysql】索引的优化,mysql索引

写在前边的话

查询轻巧,优化不易,且写且爱慕

mysql结构

从MySQL逻辑架构来看,MySQL有三层架构,第2层连接,第三层查询解析、深入分析、优化、视图、缓存,第2层,存储引擎

图片 3

 

MySQL有啥样索引类型 ?

从数据结构角度

1、B+树索引(O(log(n))):关于B+树索引,能够参谋 MySQL索引背后的数据结构及算法原理

2、hash索引:
a 仅仅能满意”=”,”IN”和”<=>”查询,不可能采取范围查询
b 其招来效能非常高,索引的查找能够二回定位,不像B-Tree
索引须求从根节点到枝节点,最终才具访问到页节点那样翻来覆去的IO访问,所以
Hash 索引的查询功用要远高于 B-Tree 索引
c 唯有Memory存款和储蓄引擎显示帮忙hash索引

3、FULLTEXT索引(未来MyISAM和InnoDB引擎都协助了)

四、昂科拉-Tree索引(用于对GIS数据类型创制SPATIAL索引)

从物理存款和储蓄角度

一、集中索引(clustered index)

贰、非集中索引(non-clustered index)

从逻辑角度

一、主键索引:主键索引是一种新鲜的唯一索引,不一样意有空值

贰、普通索引只怕单列索引

叁、多列索引(复合索引):复合索引指几个字段上创设的目录,唯有在查询条件中选择了创办索引时的率先个字段,索引才会被利用。使用复合索引时遵守最左前缀集合

肆、唯一索引可能非唯一索引

5、空间索引:空间引得是对空间数据类型的字段建设构造的目录,MYSQL中的空间数据类型有四种,分别是GEOMETSportageY、POINT、LINESTPRADOING、POLYGON。
MYSQL使用SPATIAL关键字张开扩充,使得能够用于创建正规索引类型的语法成立空间引得。成立空间引得的列,必须将其注脚为NOT
NULL,空间引得只还好积攒引擎为MYISAM的表中创造

CREATE TABLE table_name[col_name data type]
[unique|fulltext|spatial][index|key][index_name](col_name[length])[asc|desc]

1、unique|fulltext|spatial为可选参数,分别表示唯一索引、全文索引和空间索引;
2、index和key为同义词,两者作用相同,用来指定创建索引
3、col_name为需要创建索引的字段列,该列必须从数据表中该定义的多个列中选择;
4、index_name指定索引的名称,为可选参数,如果不指定,MYSQL默认col_name为索引值;
5、length为可选参数,表示索引的长度,只有字符串类型的字段才能指定索引长度;
6、asc或desc指定升序或降序的索引值存储

确立目录的尺度

一、基数相当的低的字段不创建索引,更新格外频仍的字段不吻合创设索引

2、MySQL不支持bitmap索引

三、选用第二方系统达成 Text/Blob
的全文索引(Sphinx、Coreseek、Lucene、ElashSearch)

四、常用的 where、O帕杰罗DE奇骏 BY 、GROUP BY 、DISTINCT 字段要树立目录

伍、索引不可能太多,会有负成效

陆、多应用联合索引、少使用独立索引

七、字符型可创立前缀索引(如 username 字段 4/5的数据都低于1几个字符,那么能够创设17个字符的前缀索引

8、字段的逐1对组合索引效用有第二的意义,过滤效果越好的字段须要更靠前

最左前缀相称原则,特别首要的条件,mysql会直接向右匹配直到遭遇范围查询(>、<、between、like)就告1段落匹配,比方a
= 壹 and b = 二 and c > 3 and d = 四假若创制(a,b,c,d)顺序的目录,d是用不到目录的,假若创立(a,b,d,c)的目录则都得以用到,a,b,d的各样能够随意调度尽量的庞大索引,不要新建索引。比方表中已经有a的目录,现在要加(a,b)的目录,那么只供给修改原来的目录就能够=和in可以乱序,例如a
= 一 and b = 2 and c = 3建设构造(a,b,c)索引能够随心所欲顺序,mysql的询问优化器会帮您优化成索引能够分辨的形式

九、MySQL只对以下操作符才使用索引

  • <,<=,=,>,>=,between,
  • 一点时候的like(不以通配符%或_开头的情形)
  • 若已对名叫col_name的列建了目录,则形如”col_name is
    null”的SQL会用到目录
  • 若sql语句中的where条件不只一个原则,则MySQL会开始展览Index
    Merge优化来压缩候选集范围

10、不要过分索引,只维持所需的目录。每一种额外的目录都要占用额外的磁盘空间,并降低写操作的性质。
在修改表的内容时,索引必须开始展览翻新,有的时候恐怕须求重构,由此,索引越来越多,所花的年华越长。

惊慌失措选择索引的气象

一、通过索引围观的记录数超越三成会开始展览全表扫描

二、第2个索引列使用范围查询不能够应用索引

3、内部存款和储蓄器表使用Hash举行全表扫描

4、O库罗德DETiguan BY 、GROUP BY Hash索引只可以举办等于/不等于的物色

伍、SELECT … WHERE key壹 = ? O奇骏DEXC90 BY key二 ASC
对于key一和key贰上的目录,查询优化器会自身看清用哪个(只可以用到1个)

陆、表关联字段类型要萧规曹随(包蕴长度),不然会有类型调换

七、使用函数时不能够用到目录( WHERE func(key一) = ? 不可能用到)( WHERE key一 +
一 = ? 不能够用到)(WHERE key1 = ? + ? 可以动用)

目录有啥样“副作用”

一、增,删,改都亟待修订索引,索引存在额外的护卫开支

二、查找翻阅索引系统要求消耗费时间间,索引存在额外的拜会耗费

三、索引系统须求一个地点来存放在,索引存在额外的空中花费

 

目录工具

mysqlidxchx/pt-index-usage/userstat/check-unused-keys

一、mysqlidxchx工具很短日子尚未立异,但重要用来深入分析general
log、slow.log,来剖断实例中万分索引是足以去除,但那一个工具未有通超过实际战,风险异常的大。

二、pt-index-usage原理来就好像mysqlidxchx,实践进程中质量消耗相比严重,就算要在生产库上安插,最佳在凌晨作业低锋时采纳,pt-index-usage只辅助slow.log格式的文件,如若要健全解析任何实例索引使用情况,需求long_query_time设置成0,才具把所以的sql记录下来,但同期会对磁盘空间造成压力,同有时候pt-index-usage对大文件剖析便是件痛苦的事。当然pt-index-usage能够思索部分表索引使用情状的承认。

三、最看好的userstat,收罗消息品质优越,费用低。这几个patch是google贡献的(userstat_running),percona把它改名成userstat,暗中认可是不张开的,开启是会搜罗客户端、索引、表、线程音信囤积在CLIENT_STATISTICS、INDEX_STATISTICS、TABLE_STATISTICS、THREAD_STATISTICS。Userstat的bug导致的标题太严重,直接形成mysql
crash,到近期Taobao生产情况还并未有行使。

四、RyanLowe的check-unused-keys脚本基于userstat,能够相比有利输出须要删除的目录。

参照他事他说加以考察地址

http://www.mysqlperformanceblog.com/2012/06/30/find-unused-indexes/
http://www.mysqlperformanceblog.com/2012/12/05/quickly-finding-unused-indexes-and-estimating-their-size/
http://www.mysqlperformanceblog.com/2009/06/26/check-unused-keys-a-tool-to-interact-with-index\_statistics/

http://www.bkjia.com/Mysql/1069091.htmlwww.bkjia.comtruehttp://www.bkjia.com/Mysql/1069091.htmlTechArticle【mysql】索引的优化,mysql索引 写在前头的话
查询轻易,优化不易,且写且器重 mysql结构
从MySQL逻辑架构来看,MySQL有三层框架结构,第2层连…

留下评论

网站地图xml地图