浅谈如何建mysql的高效的索引

无论是在工作还是面试中，只有谈到mysql，避不开的就是索引，什么索引？索引内部的实现原理是什么？如何建立高效的索引？带着这些问题，我又重新阅读了一下《高性能mysql》一书，今天就来记录一下自己的阅读体会。

首先强调一下，mysql中的引擎有很多种，其中应用最广就是innoDB，本文里面的索引优化和介绍都是innoDB引擎，如果使用了其他类型的引擎（例如MyISAM）章中有些策略可能不太适用，但是思路差不多，可以参考。

索引其实就是按照某些列进行排序后的数据结构，另外索引是在存储引擎层而非服务器层实现的。

实际开发中常见的几种数据场景：

当数表中数据特别少呢，全表扫描的效率肯定是最快的，因为索引查询还会涉及“回表”的操作；当数据量在十几万到几千万之间（大概的值，不一定准确）这时候使用索引的效率是最高的；当数据特别大，这个其实需要换个方向去思考（比如利用分区表）了，索引建立比较多呢，数据的修改成本会很高；

索引的好处：

索引大大减少了服务器需要扫描的数据量。索引可以帮助服务器避免排序和临时表。索引可以将随机的IO变为顺序的IO。

mysql 中数据的存储结构

在mysql中数据都被逻辑地存放在表空间中，表空间（tablespace）是存储引擎中最高的存储逻辑单位，在表空间的下面又包括段（segment）、区（extent）、页（page），他们之间的关系如下：

mysql中表的结构

页是mysql中磁盘和内存交换的基本单位，也是mysql管理存储空间的基本单位，页的默认大小是16KB，也可以通过改变 innodb_page_size 选项对默认大小进行修改例，一次最少从磁盘读取16KB内容到内存中，一次最少把内存中16KB内容刷新到磁盘中，当然了单页读取代价也是蛮高的，一般都会进行预读；关于页的数据结构如图：

页的数据结构

页中字段的解释

主键索引（聚簇索引）

一个表中只能有一个聚簇索引，当数据进行插入的时候，都是以自增的主键作为索引来插入到页中（如果主键不是int类型，那么新曾默认的rowid作为索引插入到页中），最终会形成一个B+ tree的结构，如图：

主键索引结构

关于聚集索引好处：

可以把相关的数据保存在一起。索引占用的空间比较小；数据访问更快，聚集索引是把索引和数据都保存在同一个B-tree中，因此从聚集索引中获取u数据通常比从非聚集索引中获取更快（减少了回表的操作）；使用覆盖索引扫描查询可以直接使用页节点的主键值；

有优点肯定也就有缺点，那么缺点都是对应着优点而来的，这些都是概念理解的性的东西，我再总结一下：

聚集索引数据最大限度地提高了IO密集型应用的性能，但如果数据都存在内存中时则访问的顺序就没有那么重要了；插入速度严重依赖于顺序插入，如果主键是UUID类型，那么它的插入性能就会下降，需要在加载完成后使用OPTIMIZE TABLE命令重新组织一下；聚集索引插入新行的时候，可能导致页分裂（page split）问题mysql表索引，当主键插入某个已满的页中时存储引擎可能用两个页来容纳当前内容；

非聚集索引

通常我们在实际开发中，使用主键查询的场景比较少，大部分是联合索引，也就是非聚簇索引。例如表中以列b,c,d建立的联合索引，就是按照b，c，d列中的数据生成一个B+树，如图所示：

非聚簇索引数据结构

自定义的hash索引：

如果索引字段是字段，并且比较内容比较长的时候，可以考虑使用自定义hash索引，32位的hash索引在数据大于十万的时候会出现hash冲突，可以考虑自己实现以下64位的hash就可以存储更多的数据了；

select * from table where url_hash64='' and url='http://www.toutiao.com'

建立索引的原则：

独立列原则:例如 select * from table where id + 1= 4，查询解析器不认识id +1 这个操作，所以 这个查询就不会走index(id)索引查询，索引需要改成 select * from table where id=3;选择合理的索引顺序：关于选择性和基数的经验法则值得去研究，也需要考虑where 中的排序，分组以及范围条件；

补充：

select * from table where age（类型是int）='1' 可以走index(age)索引；

select * from table where name（类型是char） = 1 不会走index(name)索引； 原因是字符串的索引排序规则是字符集编码来排序的，不同的语言对应的类型不一样，所以不会走索引；

select * from table where age > 0 不走index(age)索引；

select * from table where age > 5 不走index(age)索引； 原因是查询分析会先判断一下哪一个查询会更快才会走哪种类型的索引；

索引和锁

如果你的查询从不访问那些不需要的行，那么就会锁定更少的行，从两个方面可以来看这对性能有好处

虽然innodb的行锁效率很高，内存使用也很少，但是锁定行的时候仍然会带来额外的开销锁定超过需要的行会增加锁征用并减少并发性。

innodb只有在访问行的时候才会对其加锁，而索引能够减少innodb访问的行数，从而减少锁的数量。

但这只有当innodb在存储引擎层能够过滤掉所有不需要的行时才有效。如果索引无法过滤掉无效的行，那么在innodb检索到数据并返回给服务层以后，MySQL服务器才能应用where子句。这时已经无法避免锁定行了：innodb已经锁定了这些行，到适当的时候才释放。innodb可以在服务器端过滤掉行后就释放。

mysql> set autocommit = 0;
Query OK, 0 rows affected (0.00 sec)
 
mysql> select actor_id from actor where actor_id < 5 and actor_id <> 1 for update;

这条查询仅仅会返回2-4行之间的数据，但是实际上获取了1-4之间的行的排他锁。innodb会锁住第1行，这时因为MySQL为该查询选择的执行计划是索引范围的扫描：

mysql> explain select actor_id from actor where actor_id < 5 and actor_id <> 1 for update;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | actor | NULL       | range | PRIMARY       | PRIMARY | 2       | NULL |    4 |   100.00 | Using where; Using index |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.01 sec)

第二个查询就能证明第一行确实已经被锁定(重新开启一个MySQL的consule)。保持第一个连接的打开，然后开启第二个连接并执行如下查询：这个查询会一直堵塞，直到第一个查询commit。

mysql> set autocommit = 0;
Query OK, 0 rows affected (0.00 sec)
 
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
 
mysql> select actor_id from actor where actor_id = 1 for update;

（编辑：武汉站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!