MYSQL性能优化总结（2）

索引原理及优化

索引优化可以说是数据库查询优化中最常用的优化手段之一，需要深入理解索引原理以及各种索引区别才能设计或使用最优的索引，大幅度提升查询的效率。

mysql的索引并不是越多越好，索引会占磁盘空间，索引就好比一本书的目录，目录过多会导致查询效率降低。

一、mysql索引数据结构

mysql主要有两种结构b+tree索引和hash索引 

hash索引：mysql中只有只有Memory（Memory表只存在内存中，断电会消失，适用于临时表）存储引擎显示支持Hash索引，是Memory表的默认索引类。hash索引是以hash顺序存储，所以在查找一条数据的时候会非常快。因为是hash结构，一个键值只对应一个值，以散列的方式存储，所以不适用于排序和范围查找。

b+tree索引：该索引是mysql中使用最为频繁的索引数据结构，是Inodb和Myisam存储引擎模式的索引类型。相对于hash索引，在查找单条记录上的速度比不上hash索引，但更适合排序等操作。

b+tree的所有索引数据都在叶子节点上，并且增加了顺序访问指针，每个叶子节点都有指向相邻节点的指针，这样做的目的是提高区间查找效率，例如查询key为19-48的所有记录，当找到19之后，只要按照指针顺序遍历一次就可以将想要的数据查询出来，极大提高区间查询效率。

数据库系统的设计者巧妙利用了磁盘预读原理，将一个节点的大小设为等于一个页，这样每个节点只需要一次I/O就可以完全载入。

二、索引优劣势

优势：提高检索效率、降低数据库io成本、降低数据排序成本、降低cpu消耗。

劣势：实际上索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录，所以索引列也是要占空间的。虽然索引大大提高了查询速度，同时确会降低更新表的速度，如对表进行INSERT、UPDATE、DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件每次更新添加了索引列的字段。都会调整因为更新所带来的键值变化后的索引信息。

三、索引数据类型选取

mysql索引支持多种数据类型，选择合适的数据类型对性能有很大影响。通常可以遵循以下原则：

(1) 越小的数据类型通常更好：越小的数据类型通常在磁盘、内存和CPU缓存中都需要更少的空间，处理起来更快。

(2) 简单的数据类型更好：整型数据比起字符，处理开销更小，因为字符串的比较更复杂。在MySQL中，应该用内置的日期和时间数据类型，而不是用字符串来存储时间；

(3) 尽量避免NULL：应该指定列为NOT NULL，除非你想存储NULL。在MySQL中，含有空值的列很难进行查询优化，因为它们使得索引、索引的统计信息以及比较运算更加复杂。你应该用0、一个特殊的值或者一个空串代替空值。

(4)   主键索引类型：选择合适的标识符是非常重要的。选择时不仅应该考虑存储类型，而且应该考虑MySQL是怎样进行运算和比较的。一旦选定数据类型，应该保证所有相关的表都使用相同的数据类型。

整型：通常是作为标识符的最好选择，因为可以更快的处理，而且可以设置为AUTO_INCREMENT。

字符串：尽量避免使用字符串作为标识符，它们消耗更好的空间，处理起来也较慢。而且，通常来说，字符串都是随机的，所以它们在索引中的位置也是随机的，这会导致页面分裂、随机访问磁盘，聚簇索引分裂（对于使用聚簇索引的存储引擎）。

四、mysql常见索引类型

mysql常见的索引类型有：主键索引、唯一索引、普通索引、全文索引、组合索引

(1) 主键索引（PRIMARY KEY）：

   是一种特殊的唯一索引，不允许有空值

ALTER TABLE `table_name` ADD PRIMARY KEY ( `col`)

(2) 唯一索引（UNIQUE）:

   与"普通索引"类似，不同的就是：索引列的值必须唯一，但允许有空值。

ALTER TABLE `table_name` ADD UNIQUE (`col`)

(3) 普通索引（INDEX）：

基本索引无任何限制

ALTER TABLE `table_name` ADD INDEX  index_name ( `col`)

(4) 主键索引（FULLTEXT）：

   是一种特殊的唯一索引，不允许有空值

ALTER TABLE `table_name` ADD FULLTEXT ( `col`)

(5) 组合索引：

   为了更多的提高mysql效率可建立组合索引，遵循”最左前缀“原则。创建复合索引时应该将最常用（频率）作限制条件的列放在最左边，依次递减

ALTER TABLE `table_name` ADD INDEX index_name (`col1`, `col2`, `col3` )

五、索引优化

创建索引和使用索引的时候要记住以下规则

(1) 如果建立组合索引，建立几个复合索引，最好都用上，并且按照组合索引建立的顺序来使用。若组合索引中有三个索引字段，若不使用第一个字段则会导致索引失效，若只使用第一第三索引字段，则第三索引会失效。

(2) 不在索引列上作任何计算、函数、类型转换，否则会导致索引失效

(3) 不在组合索引的中间索引中使用范围相关查询，比如<、>、like等字段。

(4) 不在索引列中使用左模糊查询

(5) is null 和not null 索引无效

(6) 数据类型要匹配，字符串不加‘’会导致索引失效。

(7) 尽量使用覆盖索引（避免select * ）

(8) 区分度不大的字段如性别，则无需建立索引