Tue Sep 7

架构之路

mysql添加索引和性能分析

简介： 索引是一种数据结构，在mysql中建立合适辅助索引给我们的唯一索引打辅助，让我们的查询更加的高效

mysql的索引

（1）索引的优劣势

优势：

1. 提高数据检索的效率，降低数据库的I0成本

2. 降低数据排序的成本，降低了CPU的消耗

劣势：

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

（2）创建索引基本命令

1.创建：CREATE [UNIQUE ] INDEX indexName ON mytable(columnname(length);
	   ALTER mytable ADD [UNIQUE ] INDEX [indexName] ON (columnname(length));
2.删除：DROP INDEX [indexName] ON mytable;
3.查看：SHOW INDEX FROM table_ name;
4.alter命令：
	ALTER TABLE tblL name ADD PRIMARY KEY (column_ lst): 该语句添加一个主键，这意味着索引值必须是唯一的， 且不能为NULL.
	ALTER TABLE tbl _name ADD UNIQUE index_ name (column_ Jist). 这条语句创建索引的值必须是唯一的(除了NULL外，NULL可能会出现多次)。
	ALTER TABLE tbl name ADD INDEX index _name (column_ lst: 添加普通索引，索引值可出现多次。
	ALTER TABLE tbl _name ADD FULLTEXT index_ name (column_ Ist)该语句指定了索引为FULLTEXT,用于全文索引。

（3）什么时候适合创建索引

合适

主键自动建立主键索引
频繁作为查询条件的字段应该创建索引
查询中与其它表关联的字段，外键关系建立索引
查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度
查询中统计或者分组字段

不合适

表记录太少
经常增删改的表
如果每列保护很多相同的数据，不适合，没有太大的效果

（4）建立索引前性能分析explain关键字

explain的使用，让我们知道sql语句怎么执行的，这样才能和sql执行器打配合。

1.表的读取顺序
2.数据读取操作的操作类型
3.哪些索引可以使用
4.哪些索引被实际使用
5.表之间的引用
6.每张表有多少行被优化器查询

explain + sql 查看执行计划

1）id字段：

id相同：执行顺序由上到下
id不同：id越大优先级越大，越先被执行
id相同又不同：id越大越先执行，相同顺序执行

2）select_type字段：

1.SIMPLE:简单查询，查询不包括子查询或者不含UNION
2.PRIMARY:查询中若包含任何复杂的子部分，最外层查询则被标记为(最后一个执行的)
3.SUBQUERY:子查询部分
4.DERIVED:在FROM列表中包含的子查询被标记为DERIVED(衍生),MySQL会递归执行这些子查询,把结果放在临时表里。
5.UNION:union后的select就会被标记为union
6.UNION_RESULT:union查询出来的结果就会被标记为union_result

3）type字段：

字段值：

ALL
index
range
ref
eq_ref
const
system

//访问类型,结果值从好到坏：
system>const>eq_ref>ref>range>index>ALL

4）possible_keys和key字段

possible_keys：

显示可能应用在这张表中的索引，一个或多个。
查询涉及到的字段上存在的索引，则该索引将被列出，但不一定被查询实际使用

key:

实际使用的索引。如果为NULL，则没有使用索引
查询中若使用了覆盖索引，则该索引仅出现在key列表中

5）key_len字段

表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度。在key_len显示的值为索引字段的最大可能长度，并非实际使用长度。

6）ref字段

显示索引的哪一列被使用了，哪些列或常量被用于查找索引列上的值。

7）row字段

每张表有多少行被优化器执行过，即查询到这些数据优化器需要读取的行数。

8）Extra字段

Using filesort：mysql会对其又建立了一个外部索引去排序，之后在删除索引，而不是我们按照表内的索引去排序，这样会大大降低我们的性能。
Using temporary：使了用临时表保存中间结果,MySQL在对查询结果排序时使用临时表。常见于排序orderby和分组查询groupby。

ps: 我们在排序和分组的时候，要让其仿照我们建立索引的顺序，防止使用文件内排序和临时表。

Using index：表示相应的select操作中使用了覆盖索引(Covering Index)，避免访问了表的数据行。

如果同时出现using where，表明索引被用来执行索引键值的查找（索引为：name,age我们通过name找age）。

如果没有同时出现using where，表明索引用来读取数据而非执行查找动作。
Using where：使用了where过滤。
Using join bufer：使用了连接缓存。
impossible where：where的值为false，不能获取任何元素（**ps：**where age=27 and age =32）
select tables optimized away：在没有GROUPBY子句的情况下，基于索引优化MIN/MAX操作或者对于MyISAM存储引擎优化COUNT(*)操作，不必等到执行阶段再进行计算，查询执行计划生成的阶段即完成优化。
distinct：优化distinct操作，在找到第一匹配的元组后即停止找同样值的动作。

（**ps:**后两个用的很少。）

（5）利用索引简单优化

1.单表优化

我们有一张订单表，找到近期购买A商品，消费2.0以上的时间最靠前的用户名字和订单号

SELECT  `orderid`,`name` FROM `goods_fastorder` WHERE goodid=4 AND money>2.0 ORDER BY ordertime DESC LIMIT 1  //我们的sql语句

1）执行计划：

我们使用了全表扫描（type=ALL）,还进行了文件内排序（Extra=Using filesort）是一个很糟糕事情。

2）建立索引通过索引来进行查找：

CREATE INDEX fastorder_gmo ON `goods_fastorder`(goodid,money,ordertime)
SHOW INDEX FROM `goods_fastorder`//查看索引

再次执行：

//很明显我们使用了索引,type已经成了范围查看，但是我们依然进行了文件内排序,因为我们的money>2.0是一个范围值,让我们在后面中索引失效了。

3）跳过money从新建立索引

DROP INDEX fastorder_gmo ON `goods_fastorder` //删除索引
CREATE INDEX fastorder_go ON `goods_fastorder`(goodid,ordertime)//跳过money列

//type变成了ref,Extra也没有文件内排序,这样的结果已经很理想了。

2.多表连接查询优化

当我们进行两表连接查询的时候，我们可以通过explain命令性能分析

1.左连接右边建立索引
2.右连接左边建立索引

explain extended 语句 //一些优化建议

（6）索引失效问题

**最佳左前缀：**如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列。
不在索引列上做任何操作(计算、函数、(自动or手动)类型转换)，会导致索引失效而转向全表扫描。
不能使用索引中右边的列是范围条件（>,<）。
尽量使用覆盖索引(只访问索引的查询(索引列和查询列- -致))，减少select *。
使用(!=或者<>)的时候无法使用索引会导致全表扫描。
isnull,isnotnull也无法使用索引。
like以通配符开头(%abe，%abe% )mysq|索引失效会变成全表扫描的操作，我们非要使用(%abe%)可以使用覆盖索引来解决索引失效。
字符串不加单引号索引失效（varchar类型name，name=2000和name='2000'都可以查出数据，但是底层是不一样的）。
少用or用它来连接时会索引失效。

# 基础数据库