MySQL进阶：存储结构和索引

-- 存储引擎

-- 查看建表语句  默认存储引擎：ENGINE=InnoDB
show create table account;

-- 展示当前数据库支持的存储引擎
show engines;

-- 创建 my_myisam 表，存储引擎为myisam
create table my_myisam(
    id int primary key auto_increment comment'自增主键',
    name varchar(30) not null
) engine = MYISAM;

-- 创建 my_memory 表，存储引擎为memory
create table my_memory(
    id int primary key auto_increment comment'自增主键',
    name varchar(30) not null
) engine = memory;

show create table my_memory;

-- 更改表的存储引擎
alter table my_memory engine = innodb;

drop table my_memory;
drop table my_myisam;


-- 1.default engine innodb（默认存储引擎）
/*
 特点：


 */


-- 2.myisam存储引擎
/*
 特点：

 */

use test;

select * from employee;
-- 索引

-- 展示指定表的索引
show index from employee;

-- 创建指定表指定列的索引
create index employee_IDX on employee (age desc );
create index emp_idx on employee(salary);

show index from employee;


-- 删除索引
drop index employee_IDX on test.employee;
drop index emp_idx on employee;
show index from employee;
drop index employee_IDX on employee;

-- 修改索引
alter table employee rename index employee_IDX to emp_idx;

--

-- ①可以大大加快数据的检索速度，这也是创建索引的最主要原因。
-- ②可以加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义。
-- ③在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间。

use test;

-- 显示各常用语句的使用次数
show global status  like 'com_______';

select * from  employee;


-- 性能分析: explain
-- 注意possible key是可能用到的索引，key是实际用到的索引
explain select * from employee;
explain select * from employee where id = 7;   -- 10ms,没有设置索引，默认添加主键索引
explain select * from employee where name = '王天逸';   -- 23ms


-- 索引使用法则：最左前缀（对于联合索引）
select * from employee;
create index emp_age_de_sa_index on employee(age,depart,salary);   -- 在表employee上创建联合索引
show index from employee;
-- 接下来关注key_len索引长度
select * from employee where age = 31 and depart = '宣传部' and salary >=10000;
explain select * from employee where age = 31 and depart = '宣传部' and salary >=10000;  -- 132
explain select * from employee where age = 31 and depart = '宣传部';   -- 127
explain select * from employee where age = 31;    -- 5
explain select * from employee where depart = '宣传部' and salary >=10000;  -- null, 而且key和possible key也是null说明没有用到索引

/*
如果联合索引设置在了A，B，C，（....）等字段上，（注意，这个顺序是指字段在表里的顺序），
那么使用select 语句的时候在where条件中必须用到A,否则在执行时所有字段上的索引会失效。
如果使用了A,没有用到B字段的条件限制，那么B,以及以后的C，D...字段上的索引都会失效。
如果使用了A,B没有C,规律以此类推。
*/