MySQL--06(索引)
数据量很大时,查询慢的问题;
数据量巨大时,索引无效
索引定义:是一个排好序的,便于快速查找的,数据结构。
主键是一种特殊的索引,
index
pid : parent id ->pid
可以在任何字段上创建索引,但不是每个字段都适合做索引;
查看索引
show index from 表名;
创建索引的命令
create index idx_索引名 on 表名(字段名(索引长度))
索引类型
主键、唯一索引、普通索引、联合(复合索引)、全文索引
添加唯一索引命令
alter table 表名 add unique index idx_索引名(要索引的字段(长度))
ERROR 1062 (23000):** Duplicate** entry '湖南省永州市' for key 'idx_address'
删除索引
drop index 索引名称 on 表名;
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
MariaDB [books]> drop index idx_address on student; Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 MariaDB [books]> show index from student; +---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | student | 0 | PRIMARY | 1 | id | A | 6 | NULL | NULL | | BTREE | | | | student | 1 | idx_name | 1 | name | A | 6 | 10 | NULL | | BTREE | | | +---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ 2 rows in set (0.00 sec)
Key_name:索引名称
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
MariaDB [books]> show index from student; +---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | student | 0 | PRIMARY | 1 | id | A | 6 | NULL | NULL | | BTREE | | | | student | 1 | idx_name | 1 | name | A | 6 | 10 | NULL | | BTREE | | | | student | 1 | idx_fuhe | 1 | name | A | 6 | NULL | NULL | | BTREE | | | | student | 1 | idx_fuhe | 2 | address | A | 6 | NULL | NULL | YES | BTREE | | | +---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ 4 rows in set (0.00 sec)
创建全文索引
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
MariaDB [books]> alter table student add FULLTEXT idx_full(address); Query OK, 0 rows affected, 1 warning (0.18 sec) Records: 0 Duplicates: 0 Warnings: 1
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
MariaDB [books]> create table idx( -> id int primary key auto_increment, -> name varchar(20), -> email varchar(60), -> unique idx_email (email(20)) -> )engine=innodb default charset=utf8; Query OK, 0 rows affected (0.02 sec) MariaDB [books]> show index from idx; +-------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +-------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | idx | 0 | PRIMARY | 1 | id | A | 0 | NULL | NULL | | BTREE | | | | idx | 0 | idx_email | 1 | email | A | 0 | 20 | NULL | YES | BTREE | | | +-------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ 2 rows in set (0.00 sec)
事务:一系列的mysql操作,它的特性具有acid特性。
acid:原子性,一致性,隔离性,持久性。
start transaction;/begin;
commit;
提交事务的案例
-
开启 窗口1
-
开启窗口2
-
两个窗口都使用同一数据库
-
窗口一 start transaction;或者begin开启事务
-
窗口一 执行插入或修改的操作,
-
窗口一查看,这时可以看到操作的结果
-
窗口二查看,这时看不到操作的结果
-
窗口一commit
-
窗口一查看,可以看到操作结果,窗口二查看,可以看到操作结果,完成事务操作。