索引
1，索引相关
1，索引概念：
- 是存储引擎中一种数据结构，数据的组织方式，又称之为key
（为数据建立索引类似给书本建目录）
2，为何使用索引？
- 创建完索引会降低增，删，改的效率，但是数据库的读写效率是10:1，会优化查询效率
3，innodb存储引擎索引分类：
- 1）hash索引（更适合等值查询）
- 2）B+树索引
2.1） 聚集索引 / 聚簇索引 -->以主键字段的值作为key创建索引（一张表中只有一个）
PS：如果不创建索引，innodb机制会默认创建8个字符的隐藏索引，没有实际索引意义
2.2） 辅助索引 ，针对非主键字段创建的索引（一张表中可以有多个）
回表查询：通过辅助索引拿到主键值，然后再回到聚集索引从跟再查一下
覆盖查询：不需要回表就能拿到要的全部数据
4，B+树优点（在二叉树，平衡二叉树，B树的基础上做了进一步优化）
- 1） 只有叶子结点放真正的数据，意味着在等量额数据的前提下，B+树的高度是最低的
- 2） B+树的叶子节点都是排好序的，意味着在查找范围上，B+树比B树更快；快在一旦找到一个树叶节点，就不需要再从树根查起
#250w条ibd文件大小为167兆#
2，MySQL创建，删除索引
# mysql中的primary key，unique，联合唯一也都是索引，这些索引除了加速查找以外，还有约束的功能
1,创建索引
#方式一（在创建表时创建索引）
mysql> create table test(id int primary key,name varchar(10),gender enum('f','m'),year(int),index (id,gender)，);
Query OK, 0 rows affected (0.00 sec)
#方式二（在已有表格上为某列加索引）
mysql> create index index_name on test(name);
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
#方式三
mysql> alter table test add index index_idd (year);
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
2,删除索引
#方式一
mysql> drop index index_idd on test;
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
#方式二
mysql> alter table test drop index index_name;
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
方式三
mysql> alter table test drop primary key;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
3，索引测试
建表过程省略... ...
1,在没有建立索引的情况下，查看表s1有多少列，使用1.25秒
mysql> select count(*) from s1;
+----------+
| count(*) |
+----------+
| 2999999 |
+----------+
1 row in set (1.25 sec)
2，查看innodb的表达式，一共查找了2990038条数据（rows值，该值是sql估出来的数字）
mysql> explain select count(*) from s1;
+----+-------------+-------+------+---------------+------+---------+------+---------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+---------+-------+
| 1 | SIMPLE | s1 | ALL | NULL | NULL | NULL | NULL | 2990038 | NULL |
+----+-------------+-------+------+---------------+------+---------+------+---------+-------+
1 row in set (0.00 sec)
3，查找id为5555555耗时1.63，搜索了2990038条数据
mysql> select * from s1 where id=5555555;
Empty set (1.63 sec)
mysql> explain select * from s1 where id=5555555;
+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
| 1 | SIMPLE | s1 | ALL | NULL | NULL | NULL | NULL | 2990038 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
1 row in set (0.00 sec)
4，为该表建立索引（用上时5.37秒，非常慢）
mysql> create index a on s1(id);
Query OK, 0 rows affected (5.37 sec)
Records: 0 Duplicates: 0 Warnings: 0
5，添加索引后，再次查看表的行数，耗时0.79秒
mysql> select count(*) from s1;
+----------+
| count(*) |
+----------+
| 2999999 |
+----------+
1 row in set (0.79 sec)
5，添加索引后，再次查看id为55555的耗时结果，只搜索了一条数据，耗时0.01秒
mysql> select * from s1 where id=5555555;
Empty set (0.01 sec)
mysql> explain select * from s1 where id=5555555;
+----+-------------+-------+------+---------------+------+---------+-------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+-------+------+-------+
| 1 | SIMPLE | s1 | ref | a | a | 5 | const | 1 | NULL |
+----+-------------+-------+------+---------------+------+---------+-------+------+-------+
1 row in set (0.00 sec)
4，索引总结 （表格接上）
1，如果范围大，查询效率低，可以采取分段取值，一段一段取最终把大范围取完
1.1）示例1
# 定位准，耗时非常快
mysql> select count(*) from s1 where id=1000;
+----------+
| count(*) |
+----------+
| 1 |
+----------+
1 row in set (0.00 sec)
# 在200w行的数据情况下查找id>1000的范围非常广，耗时依然很慢即使有索引
mysql> select count(*) from s1 where id>1000;
+----------+
| count(*) |
+----------+
| 2998999 |
+----------+
1 row in set (0.97 sec)
#缩小范围，速度明显增快
mysql> select count(*) from s1 where id>1000 and id<2000;
+----------+
| count(*) |
+----------+
| 999 |
+----------+
1 row in set (0.00 sec)
1.1）示例2
mysql> select count(*) from s1 where id between 1 and 30;
+----------+
| count(*) |
+----------+
| 30 |
+----------+
1 row in set (0.00 sec)
mysql> select count(*) from s1 where id between 1 and 300000;
+----------+
| count(*) |
+----------+
| 300000 |
+----------+
1 row in set (0.11 sec)
2，索引下推技术
（如果多个索引即联合索引情况下，会默认最优key取值）
# 把name也加上索引
mysql> create index b on s1(name);
Query OK, 0 rows affected (6.08 sec)
Records: 0 Duplicates: 0 Warnings: 0
#查找名字为egon的用时也比较久（因为name为egon的有很多）
mysql> select count(*) from s1 where name='egon';
+----------+
| count(*) |
+----------+
| 2999999 |
+----------+
1 row in set (1.03 sec)
#查找名字为egon且性别为male的耗时7.05秒（此时gender没有加索引）
mysql> select count(*) from s1 where name='egon' and gender='male';
+----------+
| count(*) |
+----------+
| 2999999 |
+----------+
1 row in set (7.05 sec)
#把gender也加上索引
mysql> create index c on s1(gender);
Query OK, 0 rows affected (6.35 sec)
Records: 0 Duplicates: 0 Warnings: 0
#再次查找名字为egon且性别为male的耗时耗时2.9（在区分度低的情况下，会依次往右找到一个区分度高的索引字段，加速查询）
mysql> select count(*) from s1 where name='egon' and gender='male';
+----------+
| count(*) |
+----------+
| 2999999 |
+----------+
1 row in set (2.90 sec)
3，不要把查询字段放到函数或者参与运算
#耗时也会慢
mysql> select count(*) from s1 where id*3=3000;
+----------+
| count(*) |
+----------+
| 1 |
+----------+
1 row in set (0.88 sec)
4，对区分度高并且占据空间小的字段建立索引
#这里的name，gender条件成立但是区分度低，但三个id>300的范围太广，第四个的区分度虽然低但是没有加索引，查询速度是7.34
mysql> select count(*) from s1 where name='egon' and gender='male' and id>300 and email='xxx';
+----------+
| count(*) |
+----------+
| 0 |
+----------+
1 row in set (5.55 sec)
#给email也加上索引
mysql> create index d on s1(email);
Query OK, 0 rows affected (7.34 sec)
Records: 0 Duplicates: 0 Warnings: 0
#再次查看，会以区分度高并且占据空间小的字段email来搜索
mysql> select count(*) from s1 where name='egon' and gender='male' and id>300 and email='xxx';
+----------+
| count(*) |
+----------+
| 0 |
+----------+
1 row in set (0.03 sec)
网友评论