Mysql(二)--索引使用及优化

强烈推荐一个大神的人工智能的教程:http://www.captainbed.net/zhanghan

索引简介

索引是什么?索引是类似于书的目录这样的结构吗?可以这么讲,但是到底索引是什么,索引是排好序的快速查找数据结构。索引最常用的类型是BTree类型,这种类型同样可以体现出索引是排好序的快速查找数据结构,没有索引之前要想查找符合某个条件的数据就需要全表扫描,这样性能是非常低的,当有了BTree类型,可以按照一定的规则进行查找,如下是BTree结构: ![这里写图片描述](https://img-blog.csdn.net/20180522164837647?watermark/2/text/aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3poMTU3MzI2MjE2Nzk=/font/5a6L5L2T/fontsize/400/fill/I0JBQkFCMA==/dissolve/70)

索引分类

1. 单值索引
一个索引只包含单个列,一个表可以包含多个单值索引。例如我们想查name字段等于特定值,
表结构:

select * from user where name='张三01';
+----+----------+
| id | name     |
+----+----------+
|  1 | 张三01   |
+----+----------+

在没有加索引的情况下:

explain select * from user where name='张三01';
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | user  | ALL  | NULL          | NULL | NULL    | NULL |    1 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+

加索引:

create index idx_user_name on user(name);

加完索引之后:

 explain select * from user where name='张三01';
+----+-------------+-------+------+---------------+---------------+---------+-------+------+-----------------------+
| id | select_type | table | type | possible_keys | key           | key_len | ref   | rows | Extra                 |
+----+-------------+-------+------+---------------+---------------+---------+-------+------+-----------------------+
|  1 | SIMPLE      | user  | ref  | idx_user_name | idx_user_name | 63      | const |    1 | Using index condition |
+----+-------------+-------+------+---------------+---------------+---------+-------+------+-----------------------+

单值索引的创建方式有三种:

create index index_name on table_name(column)
alter table table_name add index index_name(column)
create table table_name(column1,column2...PRIMARY KEY (`id`),INDEX index_name (column)

2. 复合索引
一个索引包含多个列
如下表结构:

select * from article;
+----+-----------+-------------+-------+----------+-------+---------+
| id | author_id | category_id | views | comments | title | content |
+----+-----------+-------------+-------+----------+-------+---------+
|  1 |         1 |           1 |     1 |        1 | 1     | 1       |
|  2 |         2 |           2 |     2 |        2 | 2     | 2       |
|  3 |         1 |           1 |     3 |        3 | 3     | 3       |
+----+-----------+-------------+-------+----------+-------+---------+

创建复合索引:

create index idx_article_cv on article(category_id,views);

3. 唯一索引
索引值必须唯一,允许有空值

create unique index idx_test on article(content);

4. 全文索引
FULLTEXT索引仅可用于 MyISAM 表;他们可以从CHAR、VARCHAR或TEXT列中作为CREATE TABLE语句的一部分被创建,或是随后使用ALTER TABLE 或CREATE INDEX被添加,索引也是占用内存空间的,所以在数据量特别大的表上不要使用全文索引,因为在更新数据库数据的时候,会同时更新索引表,因此,性能会特别慢。

索引法则及优化

  • 最左前缀原则,不跳过索引中的列
    在name,age,pos三个字段上建立索引:
create index idx_staffs_nameAgePos on staffs;

举一个不遵从最左前缀的例子,where之后的条件是age和pos,没有索引的最左列name,因此是不遵从最左前缀的,导致索引失效,可以看到下图中的key为NULL,没有用到索引:
这里写图片描述
不能跳过索引中的列,如果跳过了,如果最左前缀列还在,则会使用部分索引,如果最左前缀列没有,则直接导致索引失效,例如,我们只使用最左前缀查,发现索引是使用了,key_len是74:
这里写图片描述
跳过一个索引列,跳过中间的age列,如下,发现key_len依旧是74,说明是用到了部分索引:
这里写图片描述
2. 不在索引列上做任何操作,否则会导致索引失效而转向全表扫描
这里写图片描述
3. 存储引擎不能使用索引中范围条件右边的列,范围之后全失效,将导致name和age被用到了,pos失效

select * from staffs where name='July' and age >25 and pos='manager';

4.尽量用覆盖索引(覆盖索引:查询的列和所建立的索引的列个数相同,字段相同),减少select * 的使用
5. 尽量不使用!=或<>,如果使用,则无法使用索引,会导致全表扫描

explain select * from article where category_id != '1';
+----+-------------+---------+------+----------------+------+---------+------+------+-------------+
| id | select_type | table   | type | possible_keys  | key  | key_len | ref  | rows | Extra       |
+----+-------------+---------+------+----------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | article | ALL  | idx_article_cv | NULL | NULL    | NULL |    3 | Using where |
+----+-------------+---------+------+----------------+------+---------+------+------+-------------+

6.is null, is not null无法使用索引

explain select * from article where category_id is null;
+----+-------------+-------+------+---------------+------+---------+------+------+------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra            |
+----+-------------+-------+------+---------------+------+---------+------+------+------------------+
|  1 | SIMPLE      | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL | Impossible WHERE |
+----+-------------+-------+------+---------------+------+---------+------+------+------------------+

7.like查询,%要在最右侧(‘字符串%’),否则会进行全表扫描,那么如何解决like查询时’%字符串%'时索引不被使用的方法(可以使用覆盖索引)
8. 字符串类型不加单引号会导致索引失效,因为mysql会自己做类型转换,相当于在索引列上进行了操作
如下例子中category_id是一个字符串,但是由于没有加单引号,就会降低性能

select * from article where category_id =1;

9.少用or,用它会索引失效

小试牛刀

![这里写图片描述](https://img-blog.csdn.net/20180522173754957?watermark/2/text/aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3poMTU3MzI2MjE2Nzk=/font/5a6L5L2T/fontsize/400/fill/I0JBQkFCMA==/dissolve/70)
已标记关键词 清除标记
©️2020 CSDN 皮肤主题: 精致技术 设计师:CSDN官方博客 返回首页