简介: 索引是一种数据结构,在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用它来连接时会索引失效。