数据量很大时，查询慢的问题；
数据量巨大时，索引无效
索引定义：是一个排好序的，便于快速查找的，数据结构。
主键是一种特殊的索引，
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 表名;
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：索引名称
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)
创建全文索引
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
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
-
窗口一查看，可以看到操作结果，窗口二查看，可以看到操作结果，完成事务操作。