mysql 索引

1：查看sql语句的执行计划，用explain语句来查看

mysql> explain select ename,sal from emp where sal=5000;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | emp   | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   14 |    10.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

2：注意：主键和具有unique约束的字段自动会添加索引。

3：设置索引 index

create index  索引名称 on 表名（）



mysql> create index index_sal on emp(sal);
Query OK, 0 rows affected (1.18 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> select ename,sal from emp where sal=5000;
+-------+---------+
| ename | sal     |
+-------+---------+
| KING  | 5000.00 |
+-------+---------+
1 row in set (0.00 sec)

mysql> explain select ename,sal from emp where sal=5000;
+----+-------------+-------+------------+------+---------------+-----------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key       | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+-----------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | emp   | NULL       | ref  | index_sal     | index_sal | 9       | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+-----------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

4：索引底层采用的数据结构：B+TREE

5:什么时候索引失效

模糊查询的时候，第一个通配符是%

select ename from emp where ename like''%A%'.

6:索引的分类

单一索引：给单个字段添加索引

复合索引：给多个字段联合起来添加1个索引

主键索引：主键上会自动添加索引

唯一索引：有unique约束的字段上会自动添加索引

7：通过视图修改和删除原表数据

create table emp_bak as select * from emp;
create view myview1 as select empno,ename,sal from emp bak;
update myview1 set ename=' hehe',sal=1 where empno=7369;
delect from myview1 where empno=7369

8:视图的作用

视图可以隐藏表的的细节，保密级别较高的系统。只对视图对象进行CRUD。