Mysql
一:索引的优缺点
优势:可以快速检索,减少I/O次数,加快检索速度;根据索引分组和排序,可以加快分组和排序;
劣势:索引本身也是表,因此会占用存储空间,一般来说,索引表占用的空间的数据表的1.5倍;索引表的维护和创建需要时间成本,这个成本随着数据量增大而增大;构建索引会降低数据表的修改操作(删除,添加,修改)的效率,因为在修改数据表的同时还需要修改索引表;
二:索引的分类
常见的索引类型:主键索引、唯一索引、普通索引、全文索引、组合索引
1、主键索引:即主索引,根据主键pk_clolum(length)建立索引,不允许重复,不允许空值;
ALTER TABLE 'table_name' ADD PRIMARY KEY pk_index('col');
2、唯一索引:用来建立索引的列的值必须是唯一的,允许空值
ALTER TABLE 'table_name' ADD UNIQUE index_name('col');
3、普通索引:用表中的普通列构建的索引,没有任何限制
ALTER TABLE 'table_name' ADD INDEX index_name('col');
4、全文索引:用大文本对象的列构建的索引
ALTER TABLE 'table_name' ADD FULLTEXT INDEX ft_index('col');
5、组合索引:用多个列组合构建的索引,这多个列中的值不允许有空值
ALTER TABLE 'table_name' ADD INDEX index_name('col1','col2','col3');
*遵循“最左前缀”原则,把最常用作为检索或排序的列放在最左,依次递减,组合索引相当于建立了col1,col1col2,col1col2col3三个索引,而col2或者col3是不能使用索引的。
*在使用组合索引的时候可能因为列名长度过长而导致索引的key太大,导致效率降低,在允许的情况下,可以只取col1和col2的前几个字符作为索引:
ALTER TABLE 'table_name' ADD INDEX index_name(col1(4),col2(3));
表示使用col1的前4个字符和col2的前3个字符作为索引
三、索引的实现原理
MySQL支持诸多存储引擎,而各种存储引擎对索引的支持也各不相同,因此MySQL数据库支持多种索引类型,如BTree索引,B+Tree索引,哈希索引,全文索引等等
1、哈希索引:只有memory(内存)存储引擎支持哈希索引,哈希索引用索引列的值计算该值的hashCode,然后在hashCode相应的位置存执该值所在行数据的物理位置,因为使用散列算法,因此访问速度非常快,但是一个值只能对应一个hashCode,而且是散列的分布方式,因此哈希索引不支持范围查找和排序的功能。
2、全文索引:FULLTEXT(全文)索引,仅可用于MyISAM和InnoDB,针对较大的数据,生成全文索引非常的消耗时间和空间。对于文本的大对象,或者较大的CHAR类型的数据,如果使用普通索引,那么匹配文本前几个字符还是可行的,但是想要匹配文本中间的几个单词,那么就要使用LIKE %word%来匹配,这样需要很长的时间来处理,响应时间会大大增加,这种情况,就可使用FULLTEXT索引了,在生成FULLTEXT索引时,会为文本生成一份单词的清单,在索引时及根据这个单词的清单来索引。FULLTEXT可以在创建表的时候创建,也可以在需要的时候用ALTER或者CREATE INDEX来添加:
//创建表的时候添加FULLTEXT索引
CTREATE TABLE my_table(
id INT(10) PRIMARY KEY,
name VARCHAR(10) NOT NULL,
my_text TEXT,
FULLTEXT(my_text)
)ENGINE=MyISAM DEFAULT CHARSET=utf8;
//创建表以后,在需要的时候添加FULLTEXT索引
ALTER TABLE my_table ADD FULLTEXT INDEX ft_index(column_name);
全文索引的查询也有自己特殊的语法,而不能使用LIKE %查询字符串%的模糊查询语法
SELECT * FROM table_name MATCH(ft_index) AGAINST('查询字符串');
注意:
*对于较大的数据集,把数据添加到一个没有FULLTEXT索引的表,然后添加FULLTEXT索引的速度比把数据添加到一个已经有FULLTEXT索引的表快。
*5.6版本前的MySQL自带的全文索引只能用于MyISAM存储引擎,如果是其它数据引擎,那么全文索引不会生效。5.6版本之后InnoDB存储引擎开始支持全文索引
*在MySQL中,全文索引支队英文有用,目前对中文还不支持。5.7版本之后通过使用ngram插件开始支持中文。
*在MySQL中,如果检索的字符串太短则无法检索得到预期的结果,检索的字符串长度至少为4字节,此外,如果检索的字符包括停止词,那么停止词会被忽略。
3:BTree索引:BTree是平衡搜索多叉树,设树的度为2d(d>1),高度为h,那么BTree要满足以一下条件:
每个叶子结点的高度一样,等于h;
每个非叶子结点由n-1个key和n个指针point组成,其中d<=n<=2d,key和point相互间隔,结点两端一定是key;
叶子结点指针都为null;
非叶子结点的key都是[key,data]二元组,其中key表示作为索引的键,data为键值所在行的数据;
在BTree的结构下,就可以使用二分查找的查找方式,查找复杂度为h*log(n),一般来说树的高度是很小的,一般为3左右,因此BTree是一个非常高效的查找结构。
4:B+Tree索引:B+Tree是BTree的一个变种,设d为树的度数,h为树的高度,B+Tree和BTree的不同主要在于:
B+Tree中的非叶子结点不存储数据,只存储键值;
B+Tree的叶子结点没有指针,所有键值都会出现在叶子结点上,且key存储的键值对应data数据的物理地址
B+Tree的每个非叶子节点由n个键值key和n个指针point组成;
5:B+Tree对比BTree的优点:
1、磁盘读写代价更低
一般来说B+Tree比BTree更适合实现外存的索引结构,因为存储引擎的设计专家巧妙的利用了外存(磁盘)的存储结构,即磁盘的最小存储单位是扇区(sector),而操作系统的块(block)通常是整数倍的sector,操作系统以页(page)为单位管理内存,一页(page)通常默认为4K,数据库的页通常设置为操作系统页的整数倍,因此索引结构的节点被设计为一个页的大小,然后利用外存的“预读取”原则,每次读取的时候,把整个节点的数据读取到内存中,然后在内存中查找,已知内存的读取速度是外存读取I/O速度的几百倍,那么提升查找速度的关键就在于尽可能少的磁盘I/O,那么可以知道,每个节点中的key个数越多,那么树的高度越小,需要I/O的次数越少,因此一般来说B+Tree比BTree更快,因为B+Tree的非叶节点中不存储data,就可以存储更多的key。
2、查询速度更稳定
由于B+Tree非叶子节点不存储数据(data),因此所有的数据都要查询至叶子节点,而叶子节点的高度都是相同的,因此所有数据的查询速度都是一样的。
- 带顺序索引的B+TREE
很多存储引擎在B+Tree的基础上进行了优化,添加了指向相邻叶节点的指针,形成了带有顺序访问指针的B+Tree,这样做是为了提高区间查找的效率,只要找到第一个值那么就可以顺序的查找后面的值。
四:索引的使用策略
什么时候要使用索引?
1:主键自动建立唯一索引;
2:经常作为查询条件在WHERE或者ORDER BY 语句中出现的列要建立索引;
3:作为排序的列要建立索引;
4:查询中与其他表关联的字段,外键关系建立索引
5:高并发条件下倾向组合索引;
6:用于聚合函数的列可以建立索引,例如使用了max(column_1)或者count(column_1)时的column_1就需要建立索引
什么时候不要使用索引?
1:经常增删改的列不要建立索引;
2:有大量重复的列不建立索引;
3:表记录太少不要建立索引。只有当数据库里已经有了足够多的测试数据时,它的性能测试结果才有实际参考价值。如果在测试数据库里只有几百条数据记录,它们往往在执行完第一条查询命令之后就被全部加载到内存里,这将使后续的查询命令都执行得非常快--不管有没有使用索引。只有当数据库里的记录超过了1000条、数据总量也超过了MySQL服务器上的内存总量时,数据库的性能测试结果才有意义。
索引失效的情况:
1:在组合索引中不能有列的值为NULL,如果有,那么这一列对组合索引就是无效的。
2:在一个SELECT语句中,索引只能使用一次,如果在WHERE中使用了,那么在ORDER BY中就不要用了。
3:LIKE操作中,'%aaa%'不会使用索引,也就是索引会失效,但是‘aaa%’可以使用索引。
4:在索引的列上使用表达式或者函数会使索引失效,例如:select * from users where YEAR(adddate)<2007,将在每个行上进行运算,这将导致索引失效而进行全表扫描,因此我们可以改成:select * from users where adddate<’2007-01-01′。其它通配符同样,也就是说,在查询条件中使用正则表达式时,只有在搜索模板的第一个字符不是通配符的情况下才能使用索引。
5:在查询条件中使用不等于,包括<符号、>符号和!=会导致索引失效。特别的是如果对主键索引使用!=则不会使索引失效,如果对主键索引或者整数类型的索引使用<符号或者>符号不会使索引失效。(经erwkjrfhjwkdb同学提醒,不等于,包括<符号、>符号和!,如果占总记录的比例很小的话,也不会失效)
6:在查询条件中使用IS NULL或者IS NOT NULL会导致索引失效。
7:字符串不加单引号会导致索引失效。更准确的说是类型不一致会导致失效,比如字段email是字符串类型的,使用WHERE email=99999 则会导致失败,应该改为WHERE email='99999'。
8:在查询条件中使用OR连接多个条件会导致索引失效,除非OR链接的每个条件都加上索引,这时应该改为两次查询,然后用UNION ALL连接起来。
9:如果排序的字段使用了索引,那么select的字段也要是索引字段,否则索引失效。特别的是如果排序的是主键索引则select * 也不会导致索引失效。
尽量不要包括多列排序,如果一定要,最好为这队列构建组合索引;
五、索引的优化
1、最左前缀
索引的最左前缀和和B+Tree中的“最左前缀原理”有关,举例来说就是如果设置了组合索引<col1,col2,col3>那么以下3中情况可以使用索引:col1,<col1,col2>,<col1,col2,col3>,其它的列,比如<col2,col3>,<col1,col3>,col2,col3等等都是不能使用索引的。
根据最左前缀原则,我们一般把排序分组频率最高的列放在最左边,以此类推。
2、带索引的模糊查询优化
在上面已经提到,使用LIKE进行模糊查询的时候,'%aaa%'不会使用索引,也就是索引会失效。如果是这种情况,只能使用全文索引来进行优化(上文有讲到)。
3、为检索的条件构建全文索引,然后使用
SELECT * FROM tablename MATCH(index_colum) ANGAINST(‘word’);
4、使用短索引
对串列进行索引,如果可能应该指定一个前缀长度。例如,如果有一个CHAR(255)的 列,如果在前10 个或20 个字符内,多数值是惟一的,那么就不要对整个列进行索引。短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作。
Mysql事务
事务四大特征(ACID)
- 原子性(A):事务是最小单位,不可再分
- 一致性(C):事务要求所有的DML语句操作的时候,必须保证同时成功或者同时失败
- 隔离性(I):事务A和事务B之间具有隔离性
- 持久性(D):是事务的保证,事务终结的标志(内存的数据持久到硬盘文件中)
关于事务的一些术语
- 开启事务:Start Transaction
- 事务结束:End Transaction
- 提交事务:Commit Transaction
- 回滚事务:Rollback Transaction
事物与数据库底层数据
在事物进行过程中,未结束之前,DML语句是不会更改底层数据,只是将历史操作记录一下,在内存中完成记录。只有在事物结束的时候,且是成功的结束的时候,才会修改底层硬盘文件中的数据
事务四大特性之一————隔离性(isolation)
- 事物A和事物B之间具有一定的隔离性
- 隔离性有隔离级别(4个)
- 读未提交:read uncommitted
- 读已提交:read committed
- 可重复读:repeatable read
- 串行化:serializable
1、 read uncommitted
- 事物A和事物B,事物A未提交的数据,事物B可以读取到
- 这里读取到的数据叫做“脏数据”
- 这种隔离级别最低,这种级别一般是在理论上存在,数据库隔离级别一般都高于该级别
2、read committed
- 事物A和事物B,事物A提交的数据,事物B才能读取到
- 这种隔离级别高于读未提交
- 换句话说,对方事物提交之后的数据,我当前事物才能读取到
- 这种级别可以避免“脏数据”
- 这种隔离级别会导致“不可重复读取”
- Oracle默认隔离级别
3、repeatable read
- 事务A和事务B,事务A提交之后的数据,事务B读取不到
- 事务B是可重复读取数据
- 这种隔离级别高于读已提交
- 换句话说,对方提交之后的数据,我还是读取不到
- 这种隔离级别可以避免“不可重复读取”,达到可重复读取
- 比如1点和2点读到数据是同一个
- MySQL默认级别
- 虽然可以达到可重复读取,但是会导致“幻像读”
4、serializable
- 事务A和事务B,事务A在操作数据库时,事务B只能排队等待
- 这种隔离级别很少使用,吞吐量太低,用户体验差
- 这种级别可以避免“幻像读”,每一次读取的都是数据库中真实存在数据,事务A与事务B串行,而不并发
设置事务隔离级别
方式一
可以在my.ini文件中使用transaction-isolation选项来设置服务器的缺省事务隔离级别
– READ-UNCOMMITTED
– READ-COMMITTED
– REPEATABLE-READ
– SERIALIZABLE
例如:
[mysqld]
transaction-isolation = READ-COMMITTED
方式二
通过命令动态设置隔离级别
· 隔离级别也可以在运行的服务器中动态设置,应使用SET TRANSACTION ISOLATION LEVEL语句。
· 其语法模式为:
SET [GLOBAL | SESSION] TRANSACTION ISOLATION LEVEL <isolation-level>
其中的<isolation-level>可以是:
– READ UNCOMMITTED
– READ COMMITTED
– REPEATABLE READ
– SERIALIZABLE
· 例如: SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
隔离级别的作用范围
事务隔离级别的作用范围分为两种:
– 全局级:对所有的会话有效
– 会话级:只对当前的会话有效