数据引擎,MySQL索引
数据引擎
数据库引擎是数据库用于存储、处理和保护数据的核心服务，不同的数据库引擎有其各自的特点，如存储机制、索引技巧、主键的处理、锁的粒度等特点便随着引擎的不同而变化。因此，针对自己项目特点选择合适的数据库引擎可以改善服务器端存储性能。
engine=innodb MyISAM
create table t_users(…) engine=innodb default charset utf8;
SHOW ENGINES命令来查看MySQL使用的引擎
在MySQL中不需要在整个服务器中使用同一种存储引擎，针对具体的要求，可以对每一个表使用不同的存储引擎
support列的值表示某种引擎是否能使用：YES表示可以使用、NO表示不能使用、DEFAULT表示该引擎为当前默认的存储引擎
简单理解：存储引擎就是把数据存储到磁盘上的规则,数据的存储方式,针对表
一个命令的基本执行过程：
1、服务端开启，客户端连接服务端的接口，进入连接池（包括权限、线程、连接限制）
2、客户端发送指令给mysql的服务端
3、服务端接收到这个指令，解析成对应的底层操作，然后解析器解析接受到的指令，进行操作，然后优化（包括查询速度加快等）
4、解析完后，要去磁盘中拿数据，在这个过程中要经过存储引擎
5、找自己指定的存储引擎，然后进入磁盘文件系统，在发送给客户端
InnoDB存储引擎
InnoDB是一个事务型存储引擎，提供了对数据库ACID事务的支持，并实现了SQL标准的四种隔离级别，具有行级锁定（这一点说明锁的粒度小，在写数据时，不需要锁住整个表，因此适用于高并发情形）及外键支持（所有数据库引擎中独一份，仅有它支持外键）
支持事务：支持4个事务隔离界别，支持多版本读
行级锁定(更新时一般是锁定当前行)：通过索引实现，全表扫描仍然会是表锁，注意间隙锁的影响
支持外键约束，不支持全文索引(5.5之前)，以后的都支持了
InnoDB缺点
- 该引擎不支持FULLTEXT类型的索引
- 没有保存表的行数，在执行select count(*) from 表名时，需要遍历扫描全表
InnoDB适合
- 可靠性要求比较高或者要求事务，适合处理多重并发的更新请求
- 支持事务和外键约束
- 表更新和查询都相当的频繁，并且表锁定的机会比较大的情况指定数据引擎的创建
- 可以从灾难中恢复（通过bin-log日志等）
- 支持自动增加列属性auto_increment
MyISAM存储引擎
MyISAM基于ISAM存储引擎，并对其进行扩展。它是在Web、数据仓储和其他应用环境下最常使用的存储引擎之一。MyISAM拥有较高的插入、查询速度，但不支持事务
MyIsam引擎是MySQL主流引擎之一，但它相比起InnoDB，没有提供对数据库事务的支持，不支持细粒度的锁（行锁）及外键，当表Insert与update时需要锁定整个表，因此效率会低一些，在高并发时可能会遇到瓶颈，但MyIsam引擎独立于操作系统，可以在windows及linux上使用。
MyISAM引擎是MySQL5.5版本之前的默认引擎，是对最初的ISAM引擎优化的产物。
MyISAM缺点
- 不能在表损坏后恢复数据
MyISAM适合
- 极度强调快速读取
- 表中自动存储了表的行数，需要时直接获取即可，即支- - 持快速count(*)操作
- 插入不频繁，查询非常频繁
- 没有事务、外键功能及需要对整个表加锁的情形
一般选择
- 如果要提供提交、回滚、崩溃恢复能力的事物安全（ACID兼容）能力，并要求实现并发控制，InnoDB是一个好的选择
- 如果数据表主要用来插入和查询记录，则MyISAM引擎能提供较高的处理效率
- 如果只是临时存放数据，数据量不大，并且不需要较高的数据安全性，可以选择将数据保存在内存中的Memory引擎，MySQL中使用该引擎作为临时表，存放查询的中间结果
图形化客户端工具
- SQLyog
- 一般公司中使用较多的是navicat
MySQL索引
数据库中提供了存储共享数据的功能，但是更重要的是快速高效的获取指定的数据。
在没有索引的情况下，如果要寻找特定行，数据库可能要遍历整个数据库，使用索引后，数据库可以根据索引找出这一行，极大提高查询效率。
什么是索引
MySQL官方对索引的定义为：索引Index是帮助MySQL高效获取数据的数据结构。
索引的本质：索引是数据结构。
数据库查询是数据库的最主要功能之一，希望查询数据的速度能尽可能的快，因此数据库系统的设计者会从查询算法的角度进行优化。
MySQL目前主要有普通索引、唯一索引、主键索引、组合索引和全文索引共5种索引类型。
普通索引和唯一索引、主键索引
普通索引和唯一索引是根据索引的功能来划分。
普通索引是MySQL中的基本索引类型，允许在定义索引的列中插入重复值和空值。
CREATE INDEX index_name ON table(column(length))
ALTER TABLE table_name ADD INDEX index_name ON (column(length))
DROP INDEX index_name ON table
唯一索引的索引列的值必须唯一，但允许空值。如果是组合索引，则列的值得纸盒必须唯一。主键索引是一种特殊的唯一索引，不允许有空值。
CREATE UNIQUE INDEX indexName ON table(column(length))
ALTER TABLE table_name ADD UNIQUE indexName ON (column(length))
主键索引，一种特殊的唯一索引，不允许有空值，一般在建表的时候同时建立主键索引
单列索引和组合索引
单列索引和组合索引的划分是根据创建索引时所引用的列的数量来划分。
单列索引是只一个索引只包含单个列，一个表可以有多个单列索引。
组合索引指在表的多个字段组合上创建索引，但只有在查询条件中使用了这些字段的左边字段时，索引才会被使用。
ALTER TABLE `table` ADD INDEX name_city_age (name,city,age);
组合索引可以起到多个索引的作用，但是使用时并不是随便哪个字段都可以使用索引的，而是遵从“最左前缀”：利用索引中最左边的列集列匹配行，这样的列集称为最左前缀。例如由id、name、age这3个字段字段构成的索引，索引行中按id/name/age的顺序存放，索引可以搜索下面字段组合：（id, name , age）、（id , name）、（id）。如果不构成索引的最左边的前缀，MySQL不能使用局部索引，如（age）、（name, age）都不能使用索引进行查询。
全文索引
全文索引类型为FULLTEXT，在定义索引的列上至此值需得全文查找，允许在这些索引列上插入空值和重复值
like + % 在文本比较少时是合适的，但是对于大量的文本数据检索，是不可想象的。全文索引在大量的数据面前，能比 like + % 快 N 倍，速度不是一个数量级，但是全文索引可能存在精度问题。
只有字段的数据类型为 char、varchar、text 及其系列才可以建全文索引
create fulltext index cft on test(content,tag);
在具体的应用中，一般不会使用数据库管理系统的全文索引，而是引入ElasticSearch这种NoSQL数据库实现
空间索引
空间索引可以用作地理数据存储，通过R树来实现。空间索引会从所有维度来索引数据，使得空间搜索变得高效。查询时可以有效地使用任意维度来组合查询。必须使用MySQL的GIS相关函数来维护数据。
space geometry not null,
spatial index index6_sp(space)
创建了一个名为index6_sp的空间索引
索引的优点
- 可以通过建立唯一索引或者主键索引,保证数据库表中每一行数据的唯一性
- 建立索引可以大大提高检索的数据,以及减少表的检索行数
- 在表连接的连接条件 可以加速表与表直接的相连
- 在分组和排序字句进行数据检索,可以减少查询时间中 分组 和 排序时所消耗的时间(数据库的记录会重新排序)
- 建立索引,在查询中使用索引 可以提高性能，但是会影响修改的性能
索引的缺点
- 在创建索引和维护索引 会耗费时间,随着数据量的增加而增加，所以一般建议一个表上创建的索引数不要超过6个
- 索引文件会占用物理空间,除了数据表需要占用物理空间之外,每一个索引还会占用一定的物理空间
- 当对表的数据进行 INSERT,UPDATE,DELETE 的时候,索引也要动态的维护,这样就会降低数据的维护速度,(建立索引会占用磁盘空间的索引文件。一般情况这个问题不太严重，但如果你在一个大表上创建了多种组合索引，索引文件的会膨胀很快)
注意事项
1、索引不会包含有null值的列，在数据库设计时不要让索引字段的默认值为null。
2、使用短索引
3、索引列排序
4、like语句操作， like “%aaa%” 不会使用索引而like “aaa%”可以使用索引。
5、不要在列上进行运算
6、不使用not in和<>操作
创建索引
创建索引的方式有直接创建、修改表添加和创建时直接创建三种
CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name ON table_name (column(length),…) [ASC|DESC]
-- 普通索引
create index bknameIndex on book(bookname) ;
-- 唯一索引
create unique index unique_Index on book(bookId) ;
--单列索引
create index single_Index on book(comment(10)) ;
-- 多列索引
create index mutil_Index on book(authors(20) , info(20)) ;
--全文索引
create fulltext index fulltext_Index on book(info) ;
ALTER TABLE table_name ADD [UNIQUE|FULLTEXT|SPATIAL] [INDEX|KEY] index_name (column(length),…) [ASC|DESC]
-- 普通索引
alter table book add index bkName(bookname(30)) ;
--唯一索引
alter table book add unique index uniqueIndex(bookId) ;
--单列索引
alter table book add index signalIndex2 on(comment(50)) ;
--多列索引
alter table book add index mutilIndex (authors(30) , info(50)) ;
--创建全文索引
alter table book add fulltext index fulltextIndex (info) ;
CREATE TABLE table_name (
……（创建字段和约束）,
[UNIQUE|FULLTEXT|SPATIAL] [INDEX|KEY] [INDEX|KEY] [index_name] (col_name [length]) [ASC|DESC]
)
-- 普通索引
create table book(
……
index pub_index(year_publication)
);
-- 唯一索引
create table book(
……
unique index pub_index(bookid)
);
-- 单列索引
create table book(
……
index single_index(authors)
);
-- 多列索引
create table book(
……
index mutil_index(bookid, authors)
);
-- 全文索引,需要修改表的存储引擎为MyISAM
create table book(
……
fulltext index fulltext_index(info)
);
使用ALTER TABLE删除索引
ALTER TABLE table_name DROP INDEX index_name ;
有AUTO_INCREMENT约束的字段的唯一索引不能被删除
删除book表中名为fulltext_Index的索引alter table book drop index fulltext_Index ;
使用DROP INDEX删除索引
DROP INDEX index_name ON table_name ;
删除book表中名为fulltext_Index的索引drop index mutil_index on book ;
不适合创建索引
- 二进制多媒体数据
- 超大文本数据
- web应用中活跃性大但是数据量不是很大的数据
索引的设计原则
-
索引设计不合理或者缺少索引都会对数据库性能造成不良影响
- 索引并非越多越好，一个表中如果有大量的索引，不仅浪费磁盘空间，而且会影响INSERT、DELETE、UPDATE等语句的性能，因为当表中的数据更改的同时，索引也会进行调整和更新
- 数据量小的表最好不要使用索引，由于数据量较小，查询所花费的时间可能比表里索引的时间还要短，索引可能不会产生优化的效果
- 避免对经常更新的表进行过多的索引，并且索引中的列尽可能少。对经常用于查询的字段应该创建少量索引，但要避免添加不必要的字段
- 在条件表达式中经常用到的不同值较多的列上建立索引，在不同值很少的列（例如性别字段，只有男女两个取值）不要建立索引
- 当唯一性是数据本身的特征时，指定唯一索引，可确保数据完整性并提高查询速度
- 在频繁进行排序、分组的列上建立索引，如果排序的列有多个，可以在这些列上建立组合索引。
使用索引的场景
在建立索引的时候应该考虑索引应该建立在数据库表中的某些列上面 哪一些索引需要建立,哪一些所以是多余的
1、在经常需要搜索的列上,可以加快索引的速度
2、主键列上可以确保列的唯一性
3、在表与表的而连接条件上加上索引,可以加快连接查询的速度
4、在经常需要排序(order by),分组(group by)和的distinct 列上加索引 可以加快排序查询的时间, (单独order by 用不了索引，索引考虑加where 或加limit)
4、在一些where 之后的 < <= > >= BETWEEN IN 以及某个情况下的like 建立字段的索引(B-TREE)
5、like语句的 如果你对nickname字段建立了一个索引.当查询的时候的语句是 nickname lick ‘%ABC%’ 那么这个索引讲不会起到作用.而nickname lick ‘ABC%’ 那么将可以用到索引
6、索引不会包含NULL列,如果列中包含NULL值都将不会被包含在索引中,复合索引中如果有一列含有NULL值那么这个组合索引都将失效,一般需要给默认值0或者 ’ '字符串
7、使用短索引,如果你的一个字段是Char(32)或者int(32),在创建索引的时候指定前缀长度 比如前10个字符 (前提是多数值是唯一的…)那么短索引可以提高查询速度,并且可以减少磁盘的空间,也可以减少I/0操作
8、不要在列上进行运算,这样会使得mysql索引失效,也会进行全表扫描
9、选择越小的数据类型越好,因为通常越小的数据类型通常在磁盘,内存,cpu,缓存中 占用的空间很少,处理起来更快
不创建索引的场景
查询中很少使用到的列 不应该创建索引,如果建立了索引然而还会降低mysql的性能和增大了空间需求
很少数据的列也不应该建立索引,比如 一个性别字段 0或者1,在查询中,结果集的数据占了表中数据行的比例比较大,mysql需要扫描的行数很多,增加索引,并不能提高效率
定义为text和image和bit数据类型的列不应该增加索引
当表的修改(UPDATE,INSERT,DELETE)操作远远大于检索(SELECT)操作时不应该创建索引,这两个操作是互斥的关系
扫一扫
10万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
