数据库优化之索引

索引

MySql官方对索引的定义为：索引(Index)是帮助MySql高效获取数据的数据结构。可以知道索引的本质是数据结构。

我们可以简单的理解为"排好序的快速查找数据结构"

实际上，索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录。过多的使用索引将会造成滥用。因此索引也会有它的缺点：虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件。建立索引会占用磁盘空间的索引文件。

优势和劣势

优势

提高数据检索的效率，降低数据库的IO成本
通过索引列对数据进行排序，降低数据排序的成本，降低了CPU的消耗

劣势

实际上索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录，所以索引列也是要占用空间的
虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE更新表时，MYSQL不仅要保存数据，还要保存一下索引文件每次更新添加了索引列的字段，都会调整因为更新所带来的键值变化后的索引信息
索引只是提高效率的一个因素，如果你的MYSQL有大数据量的表，就需要花时间研究建立最优秀的索引，或优化查询语句

索引种类

基本语法

创建索引

CREATE [UNIQUE] INDEX indexName ON tableName(columnName);
ALTER table tableName ADD [UNIQUE] INDEX [indexName] (columnName);

示例

添加一个主键索引
ALTER TABLE tableName PRIMARY_KEY (column_list);

添加一个唯一索引
ALTER TABLE tableName ADD UNIQUE indexName (column_list);

添加普通索引
ALTER TABLE tableName ADD INDEX indexName (column_list);

添加全文索引
ALTER TABLE tableName ADD FULLTEXT indexName (column_list);

删除索引

DROP INDEX [indexName] ON tableName;

查看索引

SHOW INDEX FROM tablename;\G

\G 来格式化输出信息
showIndex

建立时机

需要建立索引

主键自动建立索引
频繁作为查询条件的字段应该创建索引
查询中与其它表关联的字段，外键关系建立索引
频繁更新的字段不适合创建索引（更新数据的同时也会更新索引）
WHERE条件里用不到的字段不创建索引
单键/组合索引的选择问题（在高并发下倾向创建组合索引）
查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度
查询中统计或者分组字段

不需要建立索引

表记录太少
经常增删改的表
数据重复且分布平均的表字段，因此应该只为最经常查询和最经常排序的数据列建立索引。（如果某个数据列包含许多重复的内容，为它建立索引就没有太大的实际效果）

性能分析

MySql Query Optimizer

MySql中有专门负责优化SELECT语句的优化器模块，主要功能：通过计算分析系统中收集到的统计信息，为客户端请求的Query提供他认为最优的执行计划(他认为最优的数据检索方式，但不见得是DBA认为是最优的，这部分最耗费时间)
当客户端向MySql请求一条Query，命令解析器模块完成请求分类。区别出是SELECT并转发给MySql Query Optimizer时，MySql Query Optimizer首先会对整条Query进行优化，处理掉一些常量表达式的预算，之间换算成常量值。并对Query中的查询条件进行简化和转换，如去掉一些无用或显而易见的条件、结构调整等。然后分析Query中的Hint信息(如果有)，看显示Hint信息是否可以完全确定该Query的执行计划。如果没有Hint或Hint信息还不足以完全确定执行计划，则会读取所涉及对象的统计信息，根据Query进行写相应的计算分析，然后再得出最后的执行计划。

常见瓶颈

CPU：CPU在饱和的时候一般发生在数据装入内存或从磁盘上读取数据时
IO：磁盘IO瓶颈发生在装入数据远大于内存容量的时候
服务器硬件的性能瓶颈：top、free、iostat和vmstat来查看系统的性能状态

Explain

使用EXPLAIN关键字可以模拟优化器执行SQL查询语句，从而知道MySQL是如何处理你的SQL语句的。分析你的查询语句或是表结构的性能瓶颈（下篇文章专门介绍Explain）。

版权属于：带翅膀的猫
本文链接：https://www.chengpengper.cn/archives/90/
转载时须注明出处及本声明

索引

优势和劣势

优势

劣势

索引种类

基本语法

创建索引

示例

删除索引

查看索引