SQL优化

索引的特点
MySQL中索引的储存结构有两种：B 树结构和 HASH 结构，具体和表的储存引擎相关：MylSAM 和INNODB 储存引擎只支持 B 树结构的索引；还有另外两种 Memory 储存引擎和 Archive 储存引擎。
索引的优点：
1.通过创建唯一索引，可以保证数据库表中每一行数据的唯一性。
2.可以大大加快数据的查询速度。
3.在实现数据的引用完整性方面，可以加速表盒表之间的连接。
4.在使用分组和排序进行查询数据时，也可以显著减少查询中分组和排序的时间。
索引的缺点：
1.创建索引和维护索引要耗费时间。
2.索引需要占用磁盘空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间（InnoDB 数据表上的索引是表空间的一个组成部分),如果有大量的索引，索引文件可能比数据文件更快达到最大文件尺寸。占时间，占空间。
3.索引也会动态的维护，这样就降低了数据的维护速度。
索引的分类
1.普通分类
普通索引是 MySQL 中的基本索引类型，允许在定义索引的列中插入重复值和空值。
2.唯一索引
索引列的值必须唯一，但允许有空值（但只能有一个）
3.主键索引
主键索引是一种特殊的唯一索引，不允许有空值。主键索引一定是唯一索引，但是唯一索引不一定是主键索引。
4 组合索引
组合索引指在表的多个字段组合上包上创建的索引，只有在查询条件中使用了这些字段的左法字段时，案引才会被使用。使用组合索引时遵循龙前缀集合。
创建组合索引时需要注意以下三点:

A. 当需要频繁地将两列或多列作为个整体进行搜索时，可以创建组合索引。
B. 创建组合索引时先列出唯一性最好的列。
C. 组合索引中列的顺序和数量会影响查询的性能。

全文索引
全文索引类型为FULLTEXT,在定义索引的列上支持值的全文查找，允许在这些索引列中插入重复值和空值。全文索引可以在CHAR、VARCHAR或者TEXT类型的列上设置，同时在MySQL中只有MyISAM存储引擎支持全文索引。
空间索引
空间索引是对空间数据类型的字段建立的索引，MySQL中的空间数据类型有4种，分别是:GEOMETRY、POINT、 LINESTRING 和POLYGON。MySQL使用SPATIAL关键字进行扩展，使得能够用于创建正规索引类似的语法创建空间索引。创建控件索引的字段，必须设置NOT NULL约束，空间索引只能在存储引擎为MyISAM的表中创建。
索引的设计原则：
1.索引并非越多越好，一个表中如果有大量的索引，不仅占用磁盘空间，而且会影响 INSERT 、DELETE 、UPDATE 等语句的性能，因为当表中数据更改时，索引也会进行调整和更新。
2.避免对经常更新的列设置索引，并且索引中的列进可能的少。而对经常用于查询的字段应该创建索引，但要避免添加不必要的字段。
3.数据量小的表最好不要使用索引，由于数据较少，查询花费的时间可能比遍历索引的时间还短，索引可能不会产生优化效果。
4.在不同值较少的字段上不必要建立索引，如性别字段。

create database two20210611 default charset=utf8;

use two20210611;

create table student (
    studentid char(10) PRIMARY key,
    studentname varchar(10) not null,
    gender varchar(2) ,
    age int ,
    index(studentname)
);

show create table student;

insert into student values ('1' , '丘处机' , '男' , 46 );
select * from student;

-- 执行计划 explain
explain select * from student where studentname="丘处机";

-- 组合索引
create table student2 (
    studentid char(10) PRIMARY key,
    studentname varchar(10) not null,
    age int ,
    city varchar(10),
    dormitoryNo varchar(5),     # 宿舍编号
    index MultiIndex(city,dormitoryNo)  # 组合索引
);

show create table student2;
insert into student2 values ('1' , '杨过' , 19 , '活死人墓' , '002') ;

select * from student2 where studentname="杨过";
explain select * from student2 where studentname="杨过";

select * from student2 where city="活死人墓" and dormitoryNo ='002';
explain select * from student2 where city="活死人墓" and dormitoryNo ='002';

-- 删除索引
alter table student drop index studentname;
-- 创建索引
create index studentname on student(studentname) ;

-- 事务
create table user(
    u_name varchar(20) ,
    unique(u_name)
);

insert into user values ("小龙女");

select * from user;

begin;
insert into user values ("李莫愁");
commit;

begin;  -- 事务的开始
insert into user values ("尹志平");
commit;  -- 事务的结束


begin ;
insert into user values ("郭靖");
rollback; -- 回滚


-- 回滚点
begin;
insert into user values ("A");
savepoint first_point;
insert into user values ("B");
rollback to savepoint first_point;
insert into user values("C");
commit;

select * from user ;

SQL优化

推荐阅读更多精彩内容