MySQL之索引原理 - Python

TOP

MySQL之索引原理(二)

2018-12-12 22:08:46 【大中小】浏览:179次

---------+-------+---------------+---------+---------+------+------+----------+-------+ row in set, 1 warning (0.04 sec) View Code

　　范围查找，即如果查找主键某一范围内的数据，通过叶子节点的上层中间节点就可以得到页的范围，之后直接读取数据既可。

mysql> alter table s1 drop primary key;
Query OK, 2699998 rows affected (24.23 sec)
Records: 2699998  Duplicates: 0  Warnings: 0

mysql> desc s1;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id     | int(11)     | NO   |     | NULL    |       |
| name   | varchar(20) | YES  |     | NULL    |       |
| gender | char(6)     | YES  |     | NULL    |       |
| email  | varchar(50) | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
rows in set (0.12 sec)

mysql> explain select * from s1 where id > 1 and id < 1000000; #没有聚集索引，预估需要检索的rows数如下，explain就是预估一下你的sql的执行效率
+----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows    | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------------+
|  1 | SIMPLE      | s1    | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 2690100 |    11.11 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------------+
row in set, 1 warning (0.00 sec)

mysql> alter table s1 add primary key(id);
Query OK, 0 rows affected (16.25 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> explain select * from s1 where id > 1 and id < 1000000; #有聚集索引，预估需要检索的rows数如下
+----+-------------+-------+------------+-------+---------------+---------+---------+------+---------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows    | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+---------+----------+-------------+
|  1 | SIMPLE      | s1    | NULL       | range | PRIMARY       | PRIMARY | 4       | NULL | 1343355 |   100.00 | Using where |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+---------+----------+-------------+
row in set, 1 warning (0.09 sec)

View Code

　　2，辅助索引

　　当我们把id作为主键，但我们查找的时候，并不是以id为条件，而写的是where name=‘dd’时，就没法用到主键索引，此时查找的效率就会很慢，从而我们就需要引入辅助索引，给name添加一个辅助索引。

　　聚集索引的叶子节点上存放的是一整行的数据，但辅助索引就不是，它的叶子节点只存放了对应字段的数据加上主键。如果我们用辅助索引拿到对应字段的数据，如select name from t1 where name=‘kk’这种我们称为覆盖索引。如果通过辅助索引拿得不是对应字段的数据，如select sex from t1 where name= ‘kk’，我们通过辅助索引是不能直接拿到sex，但我们可以通过辅助索引拿到对应的主键id，再通过聚集索引找到一整行数据，再从一整行数据中拿到sex值，种操作叫回表，

　　三、MySQL索引管理

　　1，功能

　　索引的功能就是加快查找，mysql中primary key，unique ，联合唯一都是索引，这些索引除了加速查找之外，还有约束的功能。

　　2，MySQL常用的索引

普通索引INDEX：加速查找

唯一索引：
    -主键索引PRIMARY KEY：加速查找+约束（不为空、不能重复）
    -唯一索引UNIQUE:加速查找+约束（不能重复）

联合索引：
    -PRIMARY KEY(id,name):联合主键索引
    -UNIQUE(id,name):联合唯一索引
    -INDEX(id,name):联合普通索引

　　3，创建/删除索引的语法

#方法一：创建表时
    　　CREATE TABLE 表名 (
                字段名1  数据类型 [完整性约束条件…],
                字段名2  数据类型 [完整性约束条件…],
                [UNIQUE | FULLTEXT | SPATIAL ]   INDEX | KEY
                [索引名]  (字段名[(长度)]  [ASC |DESC]) 
                );


#方法二：CREATE在已存在的表上创建索引
        CREATE  [UNIQUE | FULLTEXT | SPATIAL ]  INDEX  索引名 
                     ON 表名 (字段名[(长度)]  [ASC |DESC]) ;


#方法三：ALTER TABLE在已存在的表上创建索引
        ALTER TABLE 表名 ADD  [UNIQUE | FULLTEXT | SPATI

首页上一页 1 2 3 4 5 6 下一页尾页 2/6/6
【大中小】【打印】【繁体】【投稿】【收藏】【推荐】【举报】【评论】【关闭】【返回顶部】

上一篇：python的部分内置函数	下一篇：python面向对象之抽象工厂设计模式