MySQL简单介绍——换个角度认识MySQL

MySQL简单介绍——换个角度认识MySQL,第1张

1、InnoDB存储引擎
Mysql版本>=55 默认的存储引擎,MySQL推荐使用的存储引擎。支持事务,行级锁定,外键约束。事务安全型存储引擎。更加注重数据的完整性和安全性。
存储格式 : 数据,索引集中存储,存储于同一个表空间文件中。
InnoDB的行锁模式及其加锁方法: InnoDB中有以下两种类型的行锁:共享锁(读锁: 允许事务对一条行数据进行读取)和 互斥锁(写锁: 允许事务对一条行数据进行删除或更新), 对于update,insert,delete语句,InnoDB会自动给设计的数据集加互斥锁,对于普通的select语句,InnoDB不会加任何锁。
InnoDB行锁的实现方式: InnoDB行锁是通过给索引上的索引项加锁来实现的,如果没有索引,InnoDB将通过隐藏的聚簇索引来对记录加锁。InnoDB这种行锁实现特点意味着:如果不通过索引条件检索数据,那么InnoDB将对表中的所有记录加锁,实际效果跟表锁一样。
(1)在不通过索引条件查询时,InnoDB会锁定表中的所有记录。
(2)Mysql的行锁是针对索引加的锁,不是针对记录加的锁,所以虽然是访问不同行的记录,但是如果使用相同的索引键,是会出现冲突的。
(3)当表有多个索引的时候,不同的事务可以使用不同的索引锁定不同的行,但都是通过行锁来对数据加锁。
优点:
1、支持事务处理、ACID事务特性;
2、实现了SQL标准的四种隔离级别( 原子性( Atomicity )、一致性( Consistency )、隔离性(Isolation )和持续性(Durability ));
3、支持行级锁和外键约束;
4、可以利用事务日志进行数据恢复。
5、锁级别为行锁,行锁优点是适用于高并发的频繁表修改,高并发是性能优于 MyISAM。缺点是系统消耗较大。
6、索引不仅缓存自身,也缓存数据,相比 MyISAM 需要更大的内存。
缺点:
因为它没有保存表的行数,当使用COUNT统计时会扫描全表。

使用场景:
(1)可靠性要求比较高,或者要求事务;(2)表更新和查询都相当的频繁,并且表锁定的机会比较大的情况。
2、 MyISAM存储引擎
MySQL<= 55 MySQL默认的存储引擎。ISAM:Indexed Sequential Access Method(索引顺序存取方法)的缩写,是一种文件系统。擅长与处理,高速读与写。
功能:
(1)支持数据压缩存储,但压缩后的表变成了只读表,不可写;如果需要更新数据,则需要先解压后更新。
(2)支持表级锁定,不支持高并发;
(3)支持并发插入。写 *** 作中的插入 *** 作,不会阻塞读 *** 作(其他 *** 作);
优点:
1高性能读取;
2因为它保存了表的行数,当使用COUNT统计时不会扫描全表;
缺点:
1、锁级别为表锁,表锁优点是开销小,加锁快;缺点是锁粒度大,发生锁冲动概率较高,容纳并发能力低,这个引擎适合查询为主的业务。
2、此引擎不支持事务,也不支持外键。
3、INSERT和UPDATE *** 作需要锁定整个表;
使用场景:
(1)做很多count 的计算;(2)插入不频繁,查询非常频繁;(3)没有事务。
InnoDB和MyISAM一些细节上的差别:
1、InnoDB不支持FULLTEXT类型的索引,MySQL56之后已经支持(实验性)。
2、InnoDB中不保存表的 具体行数,也就是说,执行select count() from table时,InnoDB要扫描一遍整个表来计算有多少行,但是MyISAM只要简单的读出保存好的行数即可。注意的是,当count()语句包含 where条件时,两种表的 *** 作是一样的。
3、对于AUTO_INCREMENT类型的字段,InnoDB中必须包含只有该字段的索引,但是在MyISAM表中,可以和其他字段一起建立联合索引。
4、DELETE FROM table时,InnoDB不会重新建立表,而是一行一行的删除。
5、LOAD TABLE FROM MASTER *** 作对InnoDB是不起作用的,解决方法是首先把InnoDB表改成MyISAM表,导入数据后再改成InnoDB表,但是对于使用的额外的InnoDB特性(例如外键)的表不适用。
6、另外,InnoDB表的行锁也不是绝对的,如果在执行一个SQL语句时MySQL不能确定要扫描的范围,InnoDB表同样会锁全表。

1.索引概述
利用关键字,就是记录的部分数据(某个字段,某些字段,某个字段的一部分),建立与记录位置的对应关系,就是索引。索引的关键字一定是排序的。索引本质上是表字段的有序子集,它是提高查询速度最有效的方法。一个没有建立任何索引的表,就相当于一本没有目录的书,在每次查询时就会进行全表扫描,这样会导致查询效率极低、速度也极慢。如果建立索引,那么就好比一本添加的目录,通过目录的指引,迅速翻阅到指定的章节,提升的查询性能,节约了查询资源。
2.索引种类
从索引的定义方式和用途中来看:主键索引,唯一索引,普通索引,全文索引。
无论任何类型,都是通过建立关键字与位置的对应关系来实现的。索引是通过关键字找对应的记录的地址。
以上类型的差异:对索引关键字的要求不同。
关键字:记录的部分数据(某个字段,某些字段,某个字段的一部分)。
普通索引,index:对关键字没有要求。
唯一索引,unique index:要求关键字不能重复。同时增加唯一约束。
主键索引,primary key:要求关键字不能重复,也不能为NULL。同时增加主键约束。
全文索引,fulltext key:关键字的来源不是所有字段的数据,而是从字段中提取的特别关键词。
PS:这里主键索引和唯一索引的区别在于:主键索引不能为空值,唯一索引允许空值;主键索引在一张表内只能创建一个,唯一索引可以创建多个。主键索引肯定是唯一索引,但唯一索引不一定是主键索引。
3索引原则
如果索引不遵循使用原则,则可能导致索引无效。
(1)列独立
如果需要某个字段上使用索引,则需要在字段参与的表达中,保证字段独立在一侧。否则索引不会用到索引, 例如这条sql就不会用到索引:select from A where id+1=10;
(2)左原则
Like:匹配模式必须要左边确定不能以通配符开头。例如:select from A where name like '%小明%' ,不会用到索引,而select from A where name like '小明%' 就可以用到索引(name字段有建立索引),如果业务上需要用到'%小明%'这种方式,有两种方法:1可以考虑全文索引,但mysql的全文索引不支持中文;2只查询索引列或主键列,例如:select name from A where name like '%小明%' 或 select id from A where name like '%小明%' 或 select id,name from A where name like '%小明%' 这三种情况都会用到name的索引;
复合索引:一个索引关联多个字段,仅仅针对左边字段有效果,添加复合索引时,第一个字段很重要,只有包含第一个字段作为查询条件的情况才会使用复合索引(必须用到建索引时选择的第一个字段作为查询条件,其他字段的顺序无关),而且查询条件只能出现and拼接,不能用or,否则则无法使用索引
(3)OR的使用
必须要保证 OR 两端的条件都存在可以用的索引,该查询才可以使用索引。
(4)MySQL智能选择
即使满足了上面说原则,MySQL也能弃用索引,例如:select from A where id > 1;这里弃用索引的主要原因:查询即使使用索引,会导致出现大量的随机IO,相对于从数据记录的第一条遍历到最后一条的顺序IO开销,还要大。
4索引的使用场景
(1)索引检索:检索数据时使用索引。
(2)索引排序: 如果order by 排序需要的字段上存在索引,则可能使用到索引。
(3)索引覆盖: 索引拥有的关键字内容,覆盖了查询所需要的全部数据,此时,就不需要在数据区获取数据,仅仅在索引区即可。覆盖就是直接在索引区获取内容,而不需要在数据区获取。例如: select name from A where name like '小明%';
建立索引索引时,不能仅仅考虑where检索,同时考虑其他的使用场景。(在所有的where字段上增加索引,就是不合理的)
5前缀索引
前缀索引是建立索引关键字一种方案。通常会使用字段的整体作为索引关键字。有时,即使使用字段前部分数据,也可以去识别某些记录。就比如一个班级里,我要找王xx,假如姓王的只有1个人,那么就可以建一个关键字为'王'的前缀索引。语法:Index `index_name` (`index_field`(N))使用index_name前N个字符建立的索引。
6索引失效
(1) 应尽量避免在 where 子句中使用 != 或 > *** 作符,否则将引擎放弃使用索引而进行全表扫描;
(2) 应尽量避免在 where 子句中使用 or 来连接条件,如果一个字段有索引,一个字段没有索引,将导致引擎放弃使用索引而进行全表扫描;
(3) 应尽量避免在 where 子句中对字段进行 null 值判断,否则将导致引擎放弃使用索引而进行全表扫描;
(4)应尽量避免在 where 子句中对字段进行表达式 *** 作,这将导致引擎放弃使用索引而进行全表扫描;如select id from t where num/2 = 100;
(5) 应尽量避免在where子句中对字段进行函数 *** 作,这将导致引擎放弃使用索引而进行全表扫描;如:select id from t where substring(name,1,3) = ’abc’ ;
(6)应尽量避免在where子句中对字段进行类型转换,这将导致引擎放弃使用索引而进行全表扫描; 如果列类型是字符串,那一定要在条件中将数据使用引号引用起来,如select id from t where id = 1;如果id字段在表设计中是varchar类型,那么即使id列上存的是数字,在查询时也一定要用varchar去匹配,sql应改为select id from t where id = '1';
(7)应尽量避免在where子句中单独引用复合索引里非第一位置的索引;

join 的两种算法:BNL 和 NLJ
NLJ(Nested Loop Join)嵌套循环算法;以如下 SQL 为例:
select from t1 join t2 on t1a=t2a
SQL 执行时内部流程是这样的:
1 先从 t1(假设这里 t1 被选为驱动表)中取出一行数据 X;
2 从 X 中取出关联字段 a 值,去 t2 中进行查找,满足条件的行取出;
3 重复1、2步骤,直到表 t1 最后一行循环结束。
这就是一个嵌套循环的过程,如果在被驱动表上查找数据时可以使用索引,总的对比计算次数等于驱动表满足 where 条件的行数。假设这里 t1、t2都是1万行,则只需要 1万次计算,这里用到的是Index Nested-Loops Join(INLJ,基于索引的嵌套循环联接)。
如果 t1、t2 的 a 字段都没有索引,还按照上述的嵌套循环流程查找数据呢?每次在被驱动表上查找数据时都是一次全表扫描,要做1万次全表扫描,扫描行数等于 1万+1万1万,这个效率很低,如果表行数更多,扫描行数动辄几百亿,所以优化器肯定不会使用这样的算法,而是选择 BNL 算法;
BNLJ(Block Nested Loop Join)块嵌套循环算法;
1 把 t1 表(假设这里 t1 被选为驱动表)满足条件的数据全部取出放到线程的 join buffer 中;
2 每次取 t2 表一行数据,去 joinbuffer 中进行查找,满足条件的行取出,直到表 t2 最后一行循环结束。
这个算法下,执行计划的 Extra 中会出现 Using join buffer(Block Nested Loop),t1、t2 都做了一次全表扫描,总的扫描行数等于 1万+1万。但是由于 joinbuffer 维护的是一个无序数组,每次在 joinbuffer 中查找都要遍历所有行,总的内存计算次数等于1万1万。另外如果 joinbuffer 不够大放不下驱动表的数据,则要分多次执行上面的流程,会导致被驱动表也做多次全表扫描。

BNLJ相对于NLJ的优点在于,驱动层可以先将部分数据加载进buffer,这种方法的直接影响就是将大大减少内层循环的次数,提高join的效率。
例如:
如果内层循环有100条记录,外层循环也有100条记录,这样的话,每次外层循环先将10条记录放到buffer中,内层循环的100条记录每条与这个buffer中的10条记录进行匹配,只需要匹配内层循环总记录数次即可结束一次循环(在这里,即只需要匹配100次即可结束),然后将匹配成功的记录连接后放入结果集中,接着,外层循环继续向buffer中放入10条记录,同理进行匹配,并将成功的记录连接后放入结果集。后续循环以此类推,直到循环结束,将结果集发给client为止。
可以发现,若用NLJ,则需要100 100次才可结束,BNLJ则需要100 / block_size 100 = 10 100次就可结束,大大减少了循环次数。

JOIN 按照功能大致分为如下三类:
JOIN、STRAIGHT_JOIN、INNER JOIN(内连接,或等值连接):取得两个表中存在连接匹配关系的记录。
LEFT JOIN(左连接):取得左表(table1)完全记录,即是右表(table2)并无对应匹配记录。
RIGHT JOIN(右连接):与 LEFT JOIN 相反,取得右表(table2)完全记录,即是左表(table1)并无匹配对应记录。
注意:mysql不支持Full join,不过可以通过UNION 关键字来合并 LEFT JOIN 与 RIGHT JOIN来模拟FULL join。
mysql 多表连接查询方式,因为mysql只支持NLJ算法,所以如果是小表驱动大表则效率更高;反之则效率下降;因此mysql对内连接或等值连接的方式做了一个优化,会去判断join表的数据行大小,然后取数据行小的表为驱动表。
INNER JOIN、JOIN、WHERE等值连接和STRAIGHT_JOIN都能表示内连接,那平时如何选择呢?一般情况下用INNER JOIN、JOIN或者WHERE等值连接,因为MySQL 会按照"小表驱动大表的策略"进行优化。当出现需要排序时,才考虑用STRAIGHT_JOIN指定某张表为驱动表。

两表JOIN优化
a当无order by条件时,根据实际情况,使用left/right/inner join即可,根据explain优化 ;
b当有order by条件时,如select from a inner join b where 1=1 and other condition order by acol;使用explain解释语句;
1)如果第一行的驱动表为a,则效率会非常高,无需优化;
2)否则,因为只能对驱动表字段直接排序的缘故,会出现using temporary,所以此时需要使用STRAIGHT_JOIN明确a为驱动表,来达到使用acol上index的优化目的;或者使用left join且Where条件中不含b的过滤条件,此时的结果集为a的全集,而STRAIGHT_JOIN为inner join且使用a作为驱动表。注:使用STRAIGHT_JOIN虽然不会using temporary,但也不是一定就能提高效率,如果a表数据远远超过b表,那么有可能使用STRAIGHT_JOIN时比原来的sql效率更低,所以怎么使用STRAIGHT_JOIN,还是要视情况而定。

在使用left join(或right join)时,应该清楚的知道以下几点:
(1) on与 where的执行顺序
ON 条件(“A LEFT JOIN B ON 条件表达式”中的ON)用来决定如何从 B 表中检索数据行。如果 B 表中没有任何一行数据匹配 ON 的条件,将会额外生成一行所有列为 NULL 的数据,在匹配阶段 WHERE 子句的条件都不会被使用。仅在匹配阶段完成以后,WHERE 子句条件才会被使用。它将从匹配阶段产生的数据中检索过滤。
所以我们要注意:在使用Left (right) join的时候,一定要在先给出尽可能多的匹配满足条件,减少Where的执行。
(2)注意ON 子句和 WHERE 子句的不同
即使右表的数据不满足ON后面的条件,也会在结果集拼接一条为NULL的数据行,但WHERE后面的条件不一样,右表不满足WHERE的条件,左表关联的数据也会被过滤掉。
(3)尽量避免子查询,而用join
往往性能这玩意儿,更多时候体现在数据量比较大的时候,此时,我们应该避免复杂的子查询。

(1)in 和 not in 要慎用,如:select id from t where num in(1,2,3)对于连续的数值,能用 between 就不要用 in:select id from t where num between 1 and 3很多时候用 exists 代替 in 是一个好的选择:select num from a where num in(select num from b)用下面的语句替换:select num from a where exists(select 1 from b where num=anum)
(2)Update 语句,如果只更改1、2个字段,不要Update全部字段,否则频繁调用会引起明显的性能消耗,同时带来大量日志。
(3)join语句,MySQL里面的join是用小表去驱动大表,而由于MySQL join实现的原理就是做循环,比如left join就是对左边的数据进行循环去驱动右边的表,左边有m条记录匹配,右边有n条记录那么就是做m次循环,每次扫描n行数据,总扫面行数是mn行数据。左边返回的结果集的大小就决定了循环的次数,故单纯的用小表去驱动大表不一定的正确的,小表的结果集可能也大于大表的结果集,所以写join的时候尽可能的先估计两张表的可能结果集,用小结果集去驱动大结果集值得注意的是在使用left/right join的时候,从表的条件应写在on之后,主表应写在where之后否则MySQL会当作普通的连表查询;
(4)select count() from table;这样不带任何条件的count会引起全表扫描,并且没有任何业务意义,是一定要杜绝的;
(5)select from t 这种语句要尽量避免,使用具体的字段代替,更有实际意义,需要什么字段就返回什么字段;
(6)数据量大的情况下,limit要慎用,因为使用limit m,n方式分页时,mysql每次都是查询前m+n条,然后舍弃前m条,所以m越大,偏移量越大,性能就越差。比如:select from A limit 1000000,20这钟,查询效率就会非常低,当分页的页数大于一定的数量之后,就可以换种方式来分页:select from A a join (select id from A limit 1000000,20) b on aid=bid;

MySQL名字的来历
MySQL最初的开发者的意图是用mSQL和他们自己的快速低级例程(ISAM)去连接表格。不管怎样,在经过一些测试后,开发者得出结论:mSQL并没有他们需要的那么快和灵活。这导致了一个使用几乎和mSQL一样的API接口的用于他们的数据库的新的SQL接口的产生,这样,这个API被设计成允许为用于mSQL而写的第三方代码更容易移植到MySQL。
MySQL这个名字是怎么来的已经不清楚了。基本指南和大量的库和工具带有前缀“my”已经有10年以上,而且不管怎样,MySQL AB创始人之一的Monty Widenius的女儿也叫My。这两个到底是哪一个给出了MySQL这个名字至今依然是个迷,包括开发者在内也不知道。
MySQL的海豚标志的名字叫“sakila”,它是由MySQL AB的创始人从用户在“海豚命名”的竞赛中建议的大量的名字表中选出的。获胜的名字是由来自非洲斯威士兰的开源软件开发者Ambrose Twebaze提供。根据Ambrose所说,Sakila来自一种叫SiSwati的斯威士兰方言,也是在Ambrose的家乡乌干达附近的坦桑尼亚的Arusha的一个小镇的名字。
[编辑本段]MySQL的概述
MySQL是一个小型关系型数据库管理系统,开发者为瑞典MySQL AB公司。在2008年1月16号被Sun公司收购。目前MySQL被广泛地应用在Internet上的中小型网站中。由于其体积小、速度快、总体拥有成本低,尤其是开放源码这一特点,许多中小型网站为了降低网站总体拥有成本而选择了MySQL作为网站数据库。MySQL的官方网站的网址是:
[编辑本段]MySQL的特性
1使用C和C++编写,并使用了多种编译器进行测试,保证源代码的可移植性
2支持AIX、FreeBSD、HP-UX、Linux、Mac OS、Novell Netware、OpenBSD、OS/2 Wrap、Solaris、Windows等多种 *** 作系统
3为多种编程语言提供了API。这些编程语言包括C、C++、Eiffel、Java、Perl、PHP、Python、Ruby和Tcl等。
4支持多线程,充分利用CPU资源
5优化的SQL查询算法,有效地提高查询速度
6既能够作为一个单独的应用程序应用在客户端服务器网络环境中,也能够作为一个库而嵌入到其他的软件中提供多语言支持,常见的编码如中文的GB 2312、BIG5,日文的Shift_JIS等都可以用作数据表名和数据列名
7提供TCP/IP、ODBC和JDBC等多种数据库连接途径
8提供用于管理、检查、优化数据库 *** 作的管理工具
9可以处理拥有上千万条记录的大型数据库
[编辑本段]MySQL的应用
与其他的大型数据库例如Oracle、DB2、SQL Server等相比,MySQL自有它的不足之处,如规模小、功能有限(MySQL Cluster的功能和效率都相对比较差)等,但是这丝毫也没有减少它受欢迎的程度。对于一般的个人使用者和中小型企业来说,MySQL提供的功能已经绰绰有余,而且由于MySQL是开放源码软件,因此可以大大降低总体拥有成本。
目前Internet上流行的网站构架方式是LAMP(Linux+Apache+MySQL+PHP),即使用Linux作为 *** 作系统,Apache作为Web服务器,MySQL作为数据库,PHP作为服务器端脚本解释器。由于这四个软件都是遵循GPL的开放源码软件,因此使用这种方式不用花一分钱就可以建立起一个稳定、免费的网站系统。
[编辑本段]MySQL管理
可以使用命令行工具管理MySQL数据库(命令mysql 和 mysqladmin),也可以从MySQL的网站下载图形管理工具MySQL Administrator和MySQL Query Browser。
phpMyAdmin是由php写成的MySQL资料库系统管理程式,让管理者可用Web介面管理MySQL资料库。
phpMyBackupPro也是由PHP写成的,可以透过Web介面创建和管理数据库。它可以创建伪cronjobs,可以用来自动在某个时间或周期备份MySQL 数据库。
另外,还有其他的GUI管理工具,例如早先的mysql-front 以及 ems mysql manager,navicat 等等。
[编辑本段]Mysql存储引擎
MyISAM Mysql的默认数据库,最为常用。拥有较高的插入,查询速度,但不支持事务
InnoDB 事务型数据库的首选引擎,支持ACID事务,支持行级锁定
BDB 源自Berkeley DB,事务型数据库的另一种选择,支持COMMIT和ROLLBACK等其他事务特性
Memory 所有数据置于内存的存储引擎,拥有极高的插入,更新和查询效率。但是会占用和数据量成正比的内存空间。并且其内容会在Mysql重新启动时丢失
Merge 将一定数量的MyISAM表联合而成一个整体,在超大规模数据存储时很有用
Archive 非常适合存储大量的独立的,作为历史记录的数据。因为它们不经常被读取。Archive拥有高效的插入速度,但其对查询的支持相对较差
Federated 将不同的Mysql服务器联合起来,逻辑上组成一个完整的数据库。非常适合分布式应用
Cluster/NDB 高冗余的存储引擎,用多台数据机器联合提供服务以提高整体性能和安全性。适合数据量大,安全和性能要求高的应用
CSV 逻辑上由逗号分割数据的存储引擎
BlackHole 黑洞引擎,写入的任何数据都会消失,一般用于记录binlog做复制的中继
另外,Mysql的存储引擎接口定义良好。有兴趣的开发者通过阅读文档编写自己的存储引擎。
[编辑本段]Mysql最常见的应用架构
单点(Single),适合小规模应用
复制(Replication),适合中小规模应用
集群(Cluster),适合大规模应用
[编辑本段]mysql历史版本
MySQL公司目前在同时开发两个版本的软件,41版以及50版。41版本的代码已经发布并有望在8个月后公布最终代码。而50版本的最后产品将在6个月后发布。
MySQL41版本中增加了不少新的性能,包括对主键的更高速度的缓存,对子查询的更好的支持,以及应网络约会网站所要求的,基于地理信息的查询。
而其同步开发的50版本则把目标对准了企业用户,对于41版本中的所有新特性,50版本悉数收入囊中,并且独具以下特点:对外键的良好支持;系统自动报错机制以及对存储过程的充分支持。

Mysql现在现已开发出51版本支持视图!
[编辑本段]Mysql中文视频教学

左光华的mysql网络数据库开发教学视频
Mysql60的alpha版于2007年初发布,新版增加了对falcon存储引擎的支持。Falcon是Mysql社区自主开发的引擎,支持ACID特性事务,支持行锁,拥有高性能的并发性。Mysql AB公司想用Falcon替代已经非常流行的InnoDB引擎,因为拥有后者技术的InnoBase已经被竞争对手Oracle所收购。
2008年1月16日,Sun Microsystems宣布收购MySQL AB,出价约10亿美元现金包括期权。
[编辑本段]MySQL的基本命令
[ ]中的内容为可选项
--创建数据库
mysql> create database 数据库名称
--创建表
mysql> create table 表名 (
列的名字(id)类型(int(4))primary key(定义主键) auto_increment(描述 自增),
……,
);
--查看所有数据库
mysql> show databases 数据库名称;
--使用某个数据库
mysql> use database 数据库名称;
--查看所使用数据库下所有的表
mysql> show tables;
--显示表的属性结构
mysql> desc 表名;
--选择表中数据的显示
-- 代表选择所有列 ,
mysql> select from 表名 where id=[and name=?] [or name=];
mysql> select id,name from 表名order by 某一列的名称 desc(降序,asc为升序)
--删除表中的数据
mysql> delete from table where id=?[or name= (and name=)];
--删除表
mysql> drop table;
--删除数据库
mysql> drop database;
索引是一种特殊的文件(InnoDB数据表上的索引是表空间的一个组成部分),它们包含着对数据表里所有记录的引用指针。
注:[1]索引不是万能的!索引可以加快数据检索 *** 作,但会使数据修改 *** 作变慢。每修改数据记录,索引就必须刷新一次。为了在某种程序上弥补这一缺陷,许多SQL命令都有一个DELAY_KEY_WRITE项。这个选项的作用是暂时制止 MySQL在该命令每插入一条新记录和每修改一条现有之后立刻对索引进行刷新,对索引的刷新将等到全部记录插入/修改完毕之后再进行。在需要把许多新记录插入某个数据表的场合,DELAY_KEY_WRITE选项的作用将非常明显。[2]另外,索引还会在硬盘上占用相当大的空间。因此应该只为最经常查询和最经常排序的数据列建立索引。注意,如果某个数据列包含许多重复的内容,为它建立索引就没有太大的实际效果。
从理论上讲,完全可以为数据表里的每个字段分别建一个索引,但MySQL把同一个数据表里的索引总数限制为16个。
1 InnoDB数据表的索引
与MyISAM数据表相比,在 InnoDB数据表上,索引对InnoDB数据表的重要性要在得多。在InnoDB数据表上,索引不仅会在搜索数据记录时发挥作用,还是数据行级锁定机制的苊、基础。"数据行级锁定"的意思是指在事务 *** 作的执行过程中锁定正在被处理的个别记录,不让其他用户进行访问。这种锁定将影响到(但不限于)SELECTLOCK IN SHARE MODE、SELECTFOR UPDATE命令以及INSERT、UPDATE和DELETE命令。
出于效率方面的考虑,InnoDB数据表的数据行级锁定实际发生在它们的索引上,而不是数据表自身上。显然,数据行级锁定机制只有在有关的数据表有一个合适的索引可供锁定的时候才能发挥效力。
2 限制
如果WEHERE子句的查询条件里有不等号(WHERE coloum != ),MySQL将无法使用索引。
类似地,如果WHERE子句的查询条件里使用了函数(WHERE DAY(column) = ),MySQL也将无法使用索引。
在JOIN *** 作中(需要从多个数据表提取数据时),MySQL只有在主键和外键的数据类型相同时才能使用索引。
如果WHERE子句的查询条件里使用比较 *** 作符LIKE和REGEXP,MySQL 只有在搜索模板的第一个字符不是通配符的情况下才能使用索引。比如说,如果查询条件是LIKE 'abc%',MySQL将使用索引;如果查询条件是LIKE '%abc',MySQL将不使用索引。
在ORDER BY *** 作中,MySQL只有在排序条件不是一个查询条件表达式的情况下才使用索引。(虽然如此,在涉及多个数据表查询里,即使有索引可用,那些索引在加快ORDER BY方面也没什么作用)
如果某个数据列里包含许多重复的值,就算为它建立了索引也不会有很好的效果。比如说,如果某个数据列里包含的净是些诸如"0/1"或"Y/N"等值,就没有必要为它创建一个索引。
普通索引、唯一索引和主索引
1 普通索引
普通索引(由关键字KEY或INDEX定义的索引)的唯一任务是加快对数据的访问速度。因此,应该只为那些最经常出现在查询条件(WHERE column = )或排序条件(ORDER BY column)中的数据列创建索引。只要有可能,就应该选择一个数据最整齐、最紧凑的数据列(如一个整数类型的数据列)来创建索引。
2 唯一索引
普通索引允许被索引的数据列包含重复的值。比如说,因为人有可能同名,所以同一个姓名在同一个"员工个人资料"数据表里可能出现两次或更多次。
如果能确定某个数据列将只包含彼此各不相同的值,在为这个数据列创建索引的时候就应该用关键字UNIQUE把它定义为一个唯一索引。这么做的好处:一是简化了MySQL对这个索引的管理工作,这个索引也因此而变得更有效率;二是 MySQL会在有新记录插入数据表时,自动检查新记录的这个字段的值是否已经在某个记录的这个字段里出现过了;如果是,MySQL将拒绝插入那条新记录。也就是说,唯一索引可以保证数据记录的唯一性。事实上,在许多场合,人们创建唯一索引的目的往往不是为了提高访问速度,而只是为了避免数据出现重复。
3 主索引
在前面已经反复多次强调过:必须为主键字段创建一个索引,这个索引就是所谓的"主索引"。主索引与唯一索引的唯一区别是:前者在定义时使用的关键字是PRIMARY而不是UNIQUE。
4 外键索引
如果为某个外键字段定义了一个外键约束条件,MySQL就会定义一个内部索引来帮助自己以最有效率的方式去管理和使用外键约束条件。
5 复合索引
索引可以覆盖多个数据列,如像INDEX(columnA, columnB)索引。这种索引的特点是MySQL可以有选择地使用一个这样的索引。如果查询 *** 作只需要用到columnA数据列上的一个索引,就可以使用复合索引INDEX(columnA, columnB)。不过,这种用法仅适用于在复合索引中排列在前的数据列组合。比如说,INDEX(A, B, C)可以当做A或(A, B)的索引来使用,但不能当做B、C或(B, C)的索引来使用。
6 索引的长度
在为CHAR和VARCHAR类型的数据列定义索引时,可以把索引的长度限制为一个给定的字符个数(这个数字必须小于这个字段所允许的最大字符个数)。这么做的好处是可以生成一个尺寸比较小、检索速度却比较快的索引文件。在绝大多数应用里,数据库中的字符串数据大都以各种各样的名字为主,把索引的长度设置为10~15个字符已经足以把搜索范围缩小到很少的几条数据记录了。
在为BLOB和TEXT类型的数据列创建索引时,必须对索引的长度做出限制;MySQL所允许的最大索引jlkjljkjlj全文索引
文本字段上的普通索引只能加快对出现在字段内容最前面的字符串(也就是字段内容开头的字符)进行检索 *** 作。如果字段里存放的是由几个、甚至是多个单词构成的较大段文字,普通索引就没什么作用了。这种检索往往以LIKE %word%的形式出现,这对MySQL来说很复杂,如果需要处理的数据量很大,响应时间就会很长。
这类场合正是全文索引(full-text index)可以大显身手的地方。在生成这种类型的索引时,MySQL将把在文本中出现的所有单词创建为一份清单,查询 *** 作将根据这份清单去检索有关的数据记录。全文索引即可以随数据表一同创建,也可以等日后有必要时再使用下面这条命令添加:
ALTER TABLE tablename ADD FULLTEXT(column1, column2)
有了全文索引,就可以用SELECT查询命令去检索那些包含着一个或多个给定单词的数据记录了。下面是这类查询命令的基本语法:
SELECT FROM tablename
WHERE MATCH(column1, column2) AGAINST('word1', 'word2', 'word3')
上面这条命令将把column1和column2字段里有word1、word2和word3的数据记录全部查询出来。
注解:InnoDB数据表不支持全文索引。
查询和索引的优化
只有当数据库里已经有了足够多的测试数据时,它的性能测试结果才有实际参考价值。如果在测试数据库里只有几百条数据记录,它们往往在执行完第一条查询命令之后就被全部加载到内存里,这将使后续的查询命令都执行得非常快--不管有没有使用索引。只有当数据库里的记录超过了1000条、数据总量也超过了MySQL服务器上的内存总量时,数据库的性能测试结果才有意义。
在不确定应该在哪些数据列上创建索引的时候,人们从EXPLAIN SELECT命令那里往往可以获得一些帮助。这其实只是简单地给一条普通的SELECT命令加一个EXPLAIN关键字作为前缀而已。有了这个关键字,MySQL将不是去执行那条SELECT命令,而是去对它进行分析。MySQL将以表格的形式把查询的执行过程和用到的索引(如果有的话)等信息列出来。
在EXPLAIN命令的输出结果里,第1列是从数据库读取的数据表的名字,它们按被读取的先后顺序排列。type列指定了本数据表与其它数据表之间的关联关系(JOIN)。在各种类型的关联关系当中,效率最高的是system,然后依次是const、eq_ref、ref、range、index和All(All的意思是:对应于上一级数据表里的每一条记录,这个数据表里的所有记录都必须被读取一遍--这种情况往往可以用一索引来避免)。
possible_keys数据列给出了MySQL在搜索数据记录时可选用的各个索引。key数据列是MySQL实际选用的索引,这个索引按字节计算的长度在key_len数据列里给出。比如说,对于一个INTEGER数据列的索引,这个字节长度将是4。如果用到了复合索引,在key_len数据列里还可以看到MySQL具体使用了它的哪些部分。作为一般规律,key_len数据列里的值越小越好(意思是更快)。
ref数据列给出了关联关系中另一个数据表里的数据列的名字。row数据列是MySQL在执行这个查询时预计会从这个数据表里读出的数据行的个数。row数据列里的所有数字的乘积可以让我们大致了解这个查询需要处理多少组合。
最后,extra数据列提供了与JOIN *** 作有关的更多信息,比如说,如果MySQL在执行这个查询时必须创建一个临时数据表,就会在extra列看到using temporary字样
[编辑本段]安装MySQL时候的注意事项
1、如果您是用MySQL+Apache,使用的又是FreeBSD网路 *** 作系统的话,安装时候你应按注意到FreeBSD的版本问题,在FreeBSD的30以下版本来说,MySQL Source内含的MIT-pthread运行是正常的,但在这版本以上,你必须使用native threads,也就是加入一个with-named-thread-libs=-lc_r的选项。
2、如果您在COMPILE过程中出了问题,请先检查你的gcc版本是否在281版本以上,gmake版本是否在375以上。
3、如果不是版本的问题,那可能是你的内存不足,请使用/configure -- with-low-memory来加入。
4、如果您要重新做你的configure,那么你可以键入rm configcache和make clean来清除记录。
5、我们一般把MySQL安装在/usr/local目录下,这是缺省值,您也可以按照你的需要设定你所安装的目录。

从技术的角度,架构的角度,用通俗的语言来跟大家讲讲,我对区块链的一些理解。

究竟啥是区块链?Block chain,一句话来说,区块链是一个存储系统,存储系统更细一点,区块链是一个没有管理员,每个节点都拥有全部数据的分布式存储系统。

那常见的存储系统,是什么样子的呢?

如上图所示,底部是数据,上面可以写入数据。一个空间存储数据,一个软件管理数据,提供接口写入数据,这就是存储系统。比如MySQL就是最常见的存储系统。

普通的存储系统,容易存在什么问题呢?至少有两个常见的问题

第一个是非高可用的问题,数据存在一个地方很危险。用技术的话说,就是数据不高可用。

第二个问题是,它存在写入的单点,写入点只有一个。用技术的话说,就是它是一个单点控制。

那普通的存储系统通常是如何解决这两个问题的呢?

首先看一下如何保证高可用?

普通的存储系统通常是用“冗余”的方式来解决高可用问题的。图上图所示如果能够把数据复制成几份,冗余到多个地方,就能够保证高可用。一个地方的数据挂了,另外的地方还存有数据,例如MySQL的主从集群就是这个原理,磁盘的RAID也是这个原理。

这个地方需要强调的两点是:数据冗余,往往会引发一致性的问题

1、例如MySQL的主从集群中中其实读写会有延时的,它其实就是有一个短的时间内读写不一致。这个是数据冗余,带来的一个副作用。

2、第二个点是数据冗余往往会降低写入的效率,因为数据同步也是需要消耗资源的。你看单点写入,如果加了两个从库之后,其实写入的效率会受影响。普通的存储系统,就是采用冗余的方式,保证数据的高可用的。

那么第二个问题,普通的存储系统,能否多点写入呢?

答案是可以的,比如说以这个图为例:

其实MySQL的话可以做一个双主的主从同步,双主的主从同步,两个节点,同时可以写入。如果要做多机房多活的数据中心,其实多机房多活也是进行数据同步的。这里要强调的是多点写入,往往会引发写写冲突的一致性问题,以MySQl为例,假设有一个表的属性是自增ID,那么现在数据库中的数据是1234,那么其中一个节点写入,插入了一条数据,那它可能变成5了,然后这5条数据,向另外一个主节点进行数据同步,同步完成之前,如果另外一个写入节点,也插入了一条数据,也生成了一条这个自增id为5的数据。那么,生成之后,往另外一个节点同步,然后同步数据到达之后会与本地的这两条5冲突,就会同步失败,会引发写写的一致性冲突问题。这个多点写入的话都会出现这个问题。

多点写入,如何保证一致?

维新“天鹅大咖课”给你更多的技术干活


欢迎分享,转载请注明来源:内存溢出

原文地址: http://outofmemory.cn/dianzi/13224430.html

(0)
打赏 微信扫一扫 微信扫一扫 支付宝扫一扫 支付宝扫一扫
上一篇 2023-06-23
下一篇 2023-06-23

发表评论

登录后才能评论

评论列表(0条)

保存