您的位置: 首页  >  文章  >  mysql索引说一说

mysql索引说一说

分类: 文章 • 2024-09-18 15:31:28

第一部分：基础介绍：

Myisam文件和数据分离只有非聚集索引

Innodb 数据文件就是索引文件，有非聚集和聚集之分，存储方式决定了索引的设计方式

第二部分：MYISAM和INNODB索引结构

1、 简单介绍B-tree B+ tree树

1.1）B-Tree

为了描述B-Tree，首先定义一条数据记录为一个二元组[key, data]，key为记录的键值，对于不同数据记录，key是互不相同的；data为数据记录除key外的数据。那么B-Tree是满足下列条件的数据结构：

d为大于1的一个正整数，称为B-Tree的度。

h为一个正整数，称为B-Tree的高度。

每个非叶子节点由n-1个key和n个指针组成，其中d<=n<=2d。

每个叶子节点最少包含一个key和两个指针，最多包含2d-1个key和2d个指针，叶节点的指针均为null 。

所有叶节点具有相同的深度，等于树高h。

key和指针互相间隔，节点两端是指针。

一个节点中的key从左到右非递减排列。

所有节点组成树结构。

每个指针要么为null，要么指向另外一个节点。

如果某个指针在节点node最左边且不为null，则其指向节点的所有key小于v(key1)，其中v(key1)为node的第一个key的值。

如果某个指针在节点node最右边且不为null，则其指向节点的所有key大于v(keym)，其中v(keym)为node的最后一个key的值。

如果某个指针在节点node的左右相邻key分别是keyi和keyi+1且不为null，则其指向节点的所有key小于v(keyi+1)且大于v(keyi)。

下图是一个d=2的B-Tree示意图。

mysql索引说一说

在了解了B-Tree结构后，假如一个度为d的B-Tree，设其索引N个key，则其树高h的上限为logd((N+1)/2)，

检索一个key，其查找节点个数的渐进复杂度为O(logdN)。从这点可以看出，B-Tree是一个非常有效率的索引数据结构

1.2）B+tree

每个节点的指针上限为2d而不是2d+1。

内节点不存储data，只存储key；叶子节点不存储指针。

图3是一个简单的B+Tree示意。

mysql索引说一说

1.3）总结

    MySql的InnoDb，MyIsam所运用的索引树结构是在这之上的改进，在每个叶子节点后添加一个指针指向下一个节点，这点很大利用传统磁盘，基于如果一个数据被读取

那么其临近的数据也将会很快被用到的原理，所设计的磁盘预读的原理。这一点大大提高MySql的数据查询。

2、 MyIsam索引结构

MyIsam引擎设计，将索引文件和数据文件分离开来，MYI文件存储索引，MYD文件存储数据，这奠定了MyIsam索引设计的基础

使用B+tree来储存数据，叶节点的data域存放的是数据记录的地址，如下图（MyIsam主键索引和非主键索引除了是否要求unique,其他的无差别）：

mysql索引说一说

3、 Innodb索引结构

InnoDB中，表结构会在没有人为指定主键的情况下会默认使用自增列做主键，如果没有这样列会自己隐式创建一个列作为主键，这个字段长度为6个字节，类型为长整形

所以表数据文件本身就是按B+Tree组织的一个索引结构,，这棵树的叶节点data域保存了完整的数据记录

下图就是Innodb的主键

mysql索引说一说

Innodb的主键和辅助索引的有很大差别，辅助索引都引用主键作为data域，这点也和Myisam的辅助索引有特别大的区别

mysql索引说一说

从上面可以看出来InnoDB,主键索引的性能应该是远高于辅助索引，因为使用辅助索引后还会去使用主键索引。

也容易明白为什么不建议使用过长的字段作为主键，因为所有辅助索引都引用主索引，过长的主索引会令辅助索引变得过大

也容易明白用非单调递增或递减的字段作为主键在InnoDB中不是个好主意，因为InnoDB数据文件本身是一颗B+Tree，

非单调的主键会造成在插入新记录时数据文件为了维持B+Tree的特性而频繁的分裂调整，十分低效，

而使用自增字段或者有单调性的属性作为键则是一个很好的选择。

第三部分：MYSQL优化

mysql优化是表数据类型选择，sql语句优化，系统配置与维护优化三类。

1、  表数据类型选择

（1） 能小就用小。表数据类型第一个原则是：使用能正确的表示和存储数据的最短类型。这样可以减少对磁盘空间、内存、cpu缓存的使用。

（2）避免用NULL，这个也是网上优化技术博文传的最多的一个。理由是额外增加字节，还有使索引，索引统计和值更复杂。

（3） 字符串如何选择char和varchar？一般phper能想到就是char是固定大小，varchar能动态储存数据。这里整理一下这两者的区别：

属性 Char Varchar
值域大小 最长字符数是255（不是字节），不管什么编码，超过此值则自动截取255个字符保存并没有报错。 65535个字节，开始两位存储长度，超过255个字符，用2位储存长度，否则1位，具体字符长度根据编码来确定，如utf8

则字符最长是21845个
如何处理字符串末尾空格 去掉末尾空格，取值出来比较的时候自动加上进行比较 Version<=4.1，字符串末尾空格被删掉，version>5.0则保留
储存空间 固定空间，比喻char(10)不管字符串是否有10个字符都分配10个字符的空间 Varchar内节约空间，但更新可能发生变化，若varchar(10),开始若储存5个字符，当update成7个时有myisam可能把行拆开，innodb可能分页，这样开销就增大
适用场合 适用于存储很短或固定或长度相似字符，如MD5加密的密码char(33)、昵称char(8)等 当最大长度远大于平均长度并且发生更新的时候。

 注意当一些英文或数据的时候，最好用每个字符用字节少的类型，如latin1

（4） 整型、整形优先原则

Tinyint、smallint、mediumint、int、bigint，分别需要8、16、24、32、64。

值域范围：-2^(n-1)~ 2^(n-1)-1

很多程序员在设计数据表的时候很习惯的用int，压根不考虑这个问题

笔者建议：能用tinyint的绝不用smallint

误区：int(1) 和int(11)是一样的，唯一区别是mysql客户端显示的时候显示多少位。

整形优先原则：能用整形的不用其他类型替换，如ip可以转换成整形保存，如商品价格‘50.00元’则保存成50

（5）精确度与空间的转换。在存储相同数值范围的数据时，浮点数类型通常都会比DECIMAL类型使用更少的空间。FLOAT字段使用4字节存储

数据。DOUBLE类型需要8 个字节并拥有更高的精确度和更大的数值范围，DECIMAL类型的数据将会转换成DOUBLE类型。

2、  sql语句优 这块的优化后面会专门写一篇博客

(2)    Order by 优化

(2.1)filesort优化算法.mysql version()>=4.1,更新了一个新算法，就是在第一次读的时候也把selcet的列也读出来，然后在sort_buffer_size中排序（不够大则建临时表保存排序顺序），这算法只需要一次读取数据。所以增大sort_buffer_size 就可以在一定程度上优化性能。Filesort第二种算法要用到更大的空间，sort_buffer_size不够大反而会影响速度，所以mysql开发团队定了个变量max_length_for_sort_data，当算法中读出来的需要列的数据的大小超过该变量的值才使用，所以一般性能分析的时候会尝试把max_length_for_sort_data改小。

(2.2)单独order by 用不了索引，索引考虑加where 或加limit

先建一个索引(last_login),建的过程就不给出了

  1. mysql> explain select * from one order by last_login desc;

  2. +—-+————-+——-+——+—————+——+———+——+——-+—————-+

  3. | id | select_type | table | type | possible_keys | key  | key_len | ref  | rows

  4.   | Extra          |

  5. +—-+————-+——-+——+—————+——+———+——+——-+—————-+

  6. |  1 | SIMPLE      | one   | ALL  | NULL          | NULL | NULL    | NULL | 2046

  7. 3 | Using filesort |

  8. +—-+————-+——-+——+—————+——+———+——+——-+—————-+

  9. 1 row in set (0.00 sec)


  11. mysql> explain select * from one order by last_login desc limit 10;

  12. +—-+————-+——-+——-+—————+————+———+——+——+——-+

  13. | id | select_type | table | type  | possible_keys | key      | key_len | ref

  14.  | rows | Extra |

  15. +—-+————-+——-+——-+—————+————+———+——+——+——-+

  16. |  1 | SIMPLE   | one   | index | NULL      | last_login  | 4     | NULL

  17.  |   10 |       |

  18. +—-+————-+——-+——-+—————+————+———+——+——+——-+

  19. 1 row in set (0.00 sec)

开始没limit查询是遍历表的，加了limit后，索引可以使用，看key_len 和key

(2.3)where + orerby 类型，where满足最左前缀原则，且orderby的列和where子句用到的索引的列的子集。即是(a,b,c)索引，where满足最左前缀原则且order by中列a、b、c的任意组合

  1. mysql> explain select * from one where username=’abgvwfnt’ and password =’123456

  2. ‘ and last_login=’1338251001′ order by password desc,last_login desc;


  4. +—-+————-+——-+——+—————+———-+———+——————-+——+————-+

  5. | id | select_type | table | type | possible_keys | key      | key_len | ref


  7.            | rows | Extra       |

  8. +—-+————-+——-+——+—————+———-+———+——————-+——+————-+

  9. |  1 | SIMPLE      | one   | ref  | username      | username | 83      | const,c

  10. onst,const |    1 | Using where |

  11. +—-+————-+——-+——+—————+———-+———+——————-+——+————-+

  12. 1 row in set (0.00 sec)


  14. mysql> explain select * from one where username=’abgvwfnt’ and password =’123456

  15. ‘ and last_login=’1338251001′ order by password desc,level desc;

  16. +—-+————-+——-+——+—————+———-+———+——————-+——+—————————-+

  17. | id | select_type | table | type | possible_keys | key      | key_len | ref| rows | Extra                       |

  18. +—-+————-+——-+——+—————+———-+———+——————-+——+—————————–+

  19. |  1 | SIMPLE      | one   | ref  | username      | username | 83      | const,c

  20. onst,const |    1 | Using where; Using filesort |

  21. +—-+————-+——-+——+—————+———-+———+——————-+——+—————————–+


  23. 1 row in set (0.00 sec)

上面两条语句明显的区别是多了一个非索引列level的排序，在extra这列对了Using filesort

笔者测试结果：where满足最左前缀且order by中的列是该多列索引的子集时（也就是说orerby中没最左前缀原则限制），不管是否有asc ,desc混合出现，都能用索引来满足order by。


(2.3) where + orerby+limit

这个其实也差不多，只要where最左前缀，orderby也正确，limit在此影响不大

(2.4)如何考虑order by来建索引

这个回归到创建索引的问题来，在比较常用的oder by的列和where中常用的列建立多列索引，这样优化起来的广度和扩张性都比较好，当然如果要考虑UNION、JOIN、COUNT、IN等进来就复杂很多了

(3)    隔离列

隔离列是只查询语句中把索引列隔离出来，也就是说不能在语句中把列包含进表达式中，如id+1=2、inet_aton(’210.38.196.138′)—ip转换成整数、convert(123,char(3))—数字转换成字符串、date函数等mysql内置的大多函数。

非隔离列影响性能很大甚至是致命的，这也就是赶集网石展的《三十六军规》中的一条，虽然他没说明是隔离列。

以下就测试一下：

首先建立一个索引(last_login )，这里就不给出建立的代码了，且把last_login改成整型（这里只是为了方便测试，并不是影响条件）

  1. mysql> explain select * from one where last_login = 8388605;

  2. +—-+————-+——-+——+—————+————+———+——-+——-+————-+

  3. | id | select_type | table | type | possible_keys | key        | key_len | ref | rows  | Extra       |

  4. +—-+————-+——-+——+—————+————+———+——-+——-+————-+

  5. |  1 | SIMPLE      | one   | ref  | last_login    | last_login | 3       | const

  6.  | 1 | Using where |

  7. +—-+————-+——-+——+—————+————+———+——-+——-+————-+

  8. 1 row in set, 1 warning (0.00 sec)

容易看出建的索引已起效

  1. mysql> explain select * from one where last_login +1= 8388606 ;

  2. +—-+————-+——-+——+—————+——+———+——+——-+————-+

  3. | id | select_type | table | type | possible_keys | key  | key_len | ref  | rows

  4.   | Extra       |

  5. +—-+————-+——-+——+—————+——+———+——+——-+————-+

  6. |  1 | SIMPLE      | one   | ALL  | NULL          | NULL | NULL    | NULL | 2049

  7. 7 | Using where |

  8. +—-+————-+——-+——+—————+——+———+——+——-+————-+

  9. 1 row in set (0.00 sec)

last_login +1=8388608非隔离列的出现导致查找的列20197，说明是遍历整张表且索引不能使用。

这是因为这条语句要找出所有last_login的数据，然后+1再和20197比较，优化器在这方面比较差，性能很差。

所以要尽可能的把列隔离出来，如last_login +1= 8388606改成login_login=8388607,或者把计算、转换等操作先用php函数处理过再传递给mysql服务器

(4)    OR、IN、UNION ALL，可以尝试用UNION ALL

(4.1)or会遍历表就算有索引

  1. mysql> explain select * from one where username = ’abgvwfnt’ or password=’123456′;

  2. +—-+————-+——-+——+—————+——+———+——+——-+————-+

  3. | id | select_type | table | type | possible_keys | key  | key_len | ref  | rows|  Extra       |

  4. +—-+————-+——-+——+—————+——+———+——+——-+————-+

  5. |  1 | SIMPLE   | one  | ALL  | username   | NULL | NULL    | NULL | 20259 | Using where |

  6. +—-+————-+——-+——+—————+——+———+——+——-+————-+

  7. 1 row in set (0.00 sec)

(4.2) 对于in，在in里面如果是个数是可枚举的且量较少的话，in也是会用上索引的。但是不建议in中嵌套一个子查询，因为MySQL的查询重写可能会产生一个不好的执行计划。

(4.3)UNION All 直接返回并集，可以避免去重的开销。之所说“尝试”用UNION All 替代 OR来优化sql语句，因为这不是一直能优化的了，这里只是作为一个方法去尝试。

(5)    索引选择性

索引选择性是不重复的索引值也叫基数（cardinality）表中数据行数的比值，索引选择性=基数/数据行，基数可以通过“show index from 表名”查看。

高索引选择性的好处就是mysql查找匹配的时候可以过滤更多的行，唯一索引的选择性最佳，值为1。

那么对于非唯一索引或者说要被创建索引的列的数据内容很长，那就要选择索引前缀。这里就简单说明一下：

  1. mysql> select count(distinct(username))/count(*)  from one;

  2. +————————————+

  3. | count(distinct(username))/count(*) |

  4. +————————————+

  5. |                             0.2047 |

  6. +————————————+

  7. 1 row in set (0.09 sec)

count(distinct(username))/count(*)就是索引选择性的值，这里0.2太小了。

假如username列数据很长，则可以通过

select count(distinct(concat(first_name, left(last_name, N))/count(*)  from one;测试出接近1的索引选择性，其中N是索引的长度，穷举法去找出N的值，然后再建索引。

(6)    重复或多余索引

很多phper开始都以为建索引相对多点性能就好点，压根没考虑到有些索引是重复的，比如建一个(username),(username,password), (username,password,last_login),很明显第一个索引是重复的，因为后两者都能满足其功能。

要有个意识就是，在满足功能需求的情况下建最少索引。对于INNODB引擎的索引来说，每次修改数据都要把主键索引，辅助索引中相应索引值修改，这可能会出现大量数据迁移，分页，以及碎片的出现。

3、系统配置与维护优化

(1)    重要的一些变量

l  key_buffer_size索引块缓存区大小, 针对MyISAM存储引擎,该值越大,性能越好.但是超过操作系统能承受的最大值,反而会使mysql变得不稳定. —-这是很重要的参数

l  sort_buffer_size 这是索引在排序缓冲区大小，若排序数据大小超过该值，则创建临时文件，注意和myisam_sort_buffer_size的区别—-这是很重要的参数

l  read_rnd_buffer_size当排序后按排序后的顺序读取行时，则通过该缓冲区读取行，避免搜索硬盘。将该变量设置为较大的值可以大大改进ORDER BY的性能。但是，这是为每个客户端分配的缓冲区，因此你不应将全局变量设置为较大的值。相反，只为需要运行大查询的客户端更改会话变量

l  join_buffer_size用于表间关联(join)的缓存大小

l  tmp_table_size缓存表的大小

l  table_cache允许 MySQL 打开的表的最大个数，并且这些都cache在内存中

l  delay_key_write针对MyISAM存储引擎,延迟更新索引.意思是说,update记录时,先将数据up到磁盘,但不up索引,将索引存在内存里,当表关闭时,将内存索引,写到磁盘

更多参数查看http://www.phpben.com/?post=70

(2)    optimize、Analyze、check、repair维护操作

l  optimize 数据在插入，更新，删除的时候难免一些数据迁移，分页，之后就出现一些碎片，久而久之碎片积累起来影响性能，这就需要DBA定期的优化数据库减少碎片，这就通过optimize命令。

如对MyisAM表操作：optimize table 表名

对于InnoDB表是不支持optimize操作，否则提示“Table does not support optimize, doing recreate + analyze instead”，当然也可以通过命令：alter table one type=innodb; 来替代。

l  Analyze 用来分析和存储表的关键字的分布，使得系统获得准确的统计信息，影响 SQL 的执行计划的生成。对于数据基本没有发生变化的表，是不需要经常进行表分析的。但是如果表的数据量变化很明显，用户感觉实际的执行计划和预期的执行计划不 同的时候，执行一次表分析可能有助于产生预期的执行计划。

Analyze table 表名

l  Check检查表或者视图是否存在错误，对 MyISAM 和 InnoDB 存储引擎的表有作用。对于 MyISAM 存储引擎的表进行表检查，也会同时更新关键字统计数据

l  Repair  optimize需要有足够的硬盘空间，否则可能会破坏表，导致不能操作，那就要用上repair，注意INNODB不支持repair操作

以上的操作出现的都是如下这是check

  1. +———-+——-+————–+————-+

  2. | Table  | Op  | Msg_type| Msg_text |

  3. +———-+——-+————–+————-+

  4. | test.one | check | status  | OK     |

  5. +———-+——-+————–+————-+

其中op是option 可以是repair check optimize，msg_type 表示信息类型，msg_text 表示信息类型，这里就说明表的状态正常。如在innodb表使用repair就出现note | The storage engine for the table doesn’t support repair

注意：以上操作最好在数据库访问量最低的时候操作，因为涉及到很多表锁定，扫描，数据迁移等操作，否则可能导致一些功能无法正常使用甚至数据库崩溃。

(3)表结构的更新与维护

l  改表结构。当要在数据量千万级的数据表中使用alter更改表结构的时候，这是一个棘手问题。一种方法是在低并发低访问量的时候用平常的alter更改表。另外一种就是建另一个与要修改的表，这个表除了要修改的结构属性外其他的和原表一模一样，这样就能得到一个相应的.frm文件，然后用flush with read lock 锁定读，然后覆盖用新建的.frm文件覆盖原表的.frm，最后unlock table 释放表。

l  建立新的索引。一般方法这里不说。

1、  创建没索引的a表，导入数据形成.MYD文件。

2、  创建包括索引b表，形成.FRM和.MYI文件

3、  锁定读写

4、  把b表的.FRM和.MYI文件改成a表名字

5、  解锁

6、  用repair创建索引。

这个方法对于大表也是很有效的。这也是为什么很多dba坚持说“先导数据库在建索引，这样效率更快”

l  定期检查mysql服务器

定期使用show status、show processlist等命令检查数据库。这里就不细说，这说起来也篇幅是比较大的，笔者对这个也不是很了解

第四部分：图说mysql查询执行流程

 

mysql索引说一说

1、  查询缓存，判断sql语句是否完全匹配，再判断是否有权限，两个判断为假则到解析器解析语句，为真则提取数据结果返回给用户。

2、  解析器解析。解析器先词法分析，语法分析，检查错误比如引号有没闭合等，然后生成解析树。

3、  预处理。预处理解决解析器无法决解的语义，如检查表和列是否存在，别名是否有错，生成新的解析树。

4、  优化器做大量的优化操作。

5、  生成执行计划。

6、  查询执行引擎，负责调度引擎获取相应数据

7、  返回结果。

 参考：1）http://ourmysql.com/archives/1171

           2）《高性能MySql第三版》

相关推荐