数据库 
首页 > 数据库 > 浏览文章

MySql 存储引擎和索引相关知识总结

(编辑：jimmy 日期: 2024/11/15 浏览：3 次 )

存储引擎

什么是数据库存储引擎？

数据库引擎是数据库底层软件组件，不同的存储引擎提供不同的存储机制，索引技巧，锁定水平等功能，使用不同的数据库引擎，可以获得特定的功能

如何查看引擎？

--如何查看数据库支持的引擎
show engines;
"htmlcode">



create table yingqin (id int,name varchar(20)) engine='InnoDB';





修改表的引擎





alter table 表名 engine='引擎名称';





修改默认引擎


    
  
  1. vi /etc/my.cnf   （配置文件地址根据安装情况）
    2. 
  
  2. [mysqld]下面
    3. 
  
  3. default-storage-engine=MyIsAM
    4. 
  
  4. 记得保存后重启服务
    
  
    5. 



MyISAM 与 InnoDB 的区别


MyISAM：支持全文索引（full text）；不支持事务;表级锁;保存表的具体行数;奔溃恢复不好。


Innodb：支持事务；以前的版本是不支持全文索引，但在5.6之后的版本就开始支持这个功能了；行级锁（并非绝对，当执行sql语句时不能确定范围时，也会进行锁全表，例如： update table set id=3 where name like 'a%';）；不保存表的具体行数；奔溃恢复好。


什么时候选择什么引擎比较好


MyISAM：


    
  
  • 不需要用到事务的时候
    • 
  
  • 做很多 count 计算
    
  
    • 



InnoDB：


    
  
  • 可靠性要求高的，或者要求支持事务
    • 
  
  • 想要用到外键约束的时候（MyISAM建立的外键是无效的）"htmlcode">

    

    --创建表的时候创建
create table test (
  id int(7) zerofill auto_increment not null,
  username varchar(20),
  servnumber varchar(30),
  password varchar(20),
  createtime datetime,
  unique (id)
)DEFAULT CHARSET=utf8;

--直接为表添加索引
--语法：alter table 表名 add index 索引名称 (字段名称);
--注意：假如没有指定索引名称时，会以默认的字段名为索引名称
alter table test add unique unique_username (username);
"htmlcode">

    

    --语法：show index from 表名\G
show index from test\G
    

    


    如何删除索引
    


    

    --语法：drop index 索引名称 on 表名;
drop index unique_username on test;
--语法：alter table 表名 drop index 索引名;
alter table test drop index createtime;
    

    


    主键索引
    

    什么是主键索引？
    

    把主键添加索引就是主键索引，它是一种特殊的唯一索引，不允许有空值，而唯一索引（unique是允许为空值的）。指定为“PRIMARY KEY”
    

    主键：主键是表的某一列，这一列的值是用来标志表中的每一行数据的。注意：每一张表只能拥有一个主键
    

    创建主键：
    


    

    --1）创建表的时候创建

--2）直接为表添加主键索引
--语法：alter table 表名 add primary key (字段名);
alter table test add primary key (id);
    

    


    删除主键：
    


    

    --语法：alter table 表名 drop primary key;
alter table test drop primary key;
    

    


    注意：在有自增的情况下，必须先删除自增，才可以删除主键
    


    

    "htmlcode">

    

    --创建练习表的sql：
create table command (
  id int(5) unsigned primary key auto_increment,
  name varchar(10),
  instruction varchar(60)
)engine=MyISAM;

--插入数据sql：
insert into command values('1','ls','list directory contents');
insert into command values('2','wc','print newline, word, and byte counts for each file');
insert into command values('3','cut','remove sections from each line of files');
insert into command values('4','sort','sort lines of text files');
insert into command values('5','find','search for files in a directory hierarchy');
insert into command values('6','cp','复制文件或者文件夹');
insert into command values('7','top','display Linux processes');
insert into command values('8','mv','修改文件名，移动');
insert into command values('9','停止词','is,not,me,yes,no ...');
    

    


    添加全文索引：
    


    

    --1）创建表的时候创建全文索引

--2）通过alter添加
alter table command add fulltext(instruction);
    

    


    使用全文索引：
    


    

    --语法：select * from 表名 where match (字段名) against ('检索内容');
select * from command where match(instruction) against ('sections');
    

    


    查看匹配度：
    


    

    select * from command where match(instruction) against ('directory');
    

    


    停止词：
    

    出现频率很高的词，将会使全文索引失效。
    

    in boolean mode 模式：
    

    in boolean mode：意思是指定全文检索模式为布尔全文检索（简单可以理解为是检索方式）
    


    

    --语法：select * from 表名 where match (字段名) against ('检索内容' in boolean mode);
select * from command where match(instruction) against ('direct*' in boolean mode);
    

    


    注意点：使用通配符*时，只能放在词的后边，不能放前边。
    

    删除全文索引：
    


    

    alter table command drop index instruction;
    

    


    注意点总结：
    

      
  
    1. 一般情况下创建全文索引的字段数据类型为 char、varchar、text 。其它字段类型不可以
      2. 
  
    2. 全文索引不针对非常频繁的词做索引。比如 is，no，not，you，me，yes 这些，我们称之为停止词
      3. 
  
    3. 对英文检索时忽略大小写
      4. 

    

    外键约束
    

    

    什么是外键？
    

    外键就是作用于两个表数据之间的链接的一列或多列，用来保证表与表之间的数据的完整性和准确性。
    

    添加外键约束：
    


    

    --语法：foreign key (字段名) references 关联的表名(关联表的字段名)
--"htmlcode">

    

    mysql> alter table employee drop index deptnu;
ERROR 1553 (HY000): Cannot drop index 'deptnu': needed in a foreign key constraint
mysql> 
mysql> alter table employee drop foreign key employee_ibfk_1;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> 
mysql> alter table employee drop index deptnu;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
    

    


    注意点总结：
    

      
  
    1. 俩个表，主键跟外键的字段类型一定要相同
      2. 
  
    2. 要使用外键约束表的引擎一定得是 InnoDB 引擎，MyISAM 是不起作用的
      3. 
  
    3. 在干掉外键索引之前必须先把外键约束删除，才能删除索引
      4. 

    

    联合索引
    

    

    什么是联合索引？
    

    联合索引又称组合索引或者复合索引，是建立在俩列或者多列以上的索引。
    

    创建联合索引
    


    

    --语法：alter table 表名 add index(字段1,字段2,字段3);
alter table test add index(username,servnumber,password);
    

    


    删除联合索引
    


    

    --语法：alter table test drop index 索引名;
alter table test drop index username;
    

    


    为什么要使用联合索引，而不使用多个单列索引？
    

    联合索引的效率远远高于单列索引。假如创建了三个单列索引，并且查询条件中也存在这三列，但是 MySQL 只会选择最优的列索引，而不会三个索引都用上
    

    联合索引的最左原则
    

    以上面的索引为例，查询条件中必须有 username，才会去使用这个索引，否则不会去使用该索引
    

    注意点总结：
    

      
  
    • 索引并非越多越好，过多的索引会增加数据的维护速度还有磁盘空间的浪费。
      • 
  
    • 当表的数据量很大的时候，可以考虑建立索引。
      • 
  
    • 表中经常查数据的字段，可以考虑建立索引。
      • 
  
    • 想要保证表中数据的唯一性，可以考虑建立唯一索引。
      • 
  
    • 想要保证俩张表中的数据的完整性跟准确性，可以考虑建立外键约束。
      • 
  
    • 经常对多列数据进行查询时，可以考虑建立联合索引。
      • 

    

    以上就是MySql 存储引擎和索引相关知识总结的详细内容，更多关于MySql 存储引擎和索引的资料请关注其它相关文章！
    

            
    
           
            上一篇：mysql数据库是做什么的
    
            下一篇：SQL Server数据库错误5123解决方案