Mysql目前主要有以下几种索引类型:FULLTEXT,HASH,BTREE,RTREE。
1.FULLTEXT
即为全文索引,目前只有MyISAM引擎支持。其可以在CREATETABLE,ALTERTABLE,CREATEINDEX使用,不过目前只有CHAR、VARCHAR,TEXT列上可以创建全文索引。
全文索引并不是和MyISAM一起诞生的,它的出现是为了解决WHEREnameLIKE“%word%"这类针对文本的模糊查询效率较低的问题。
RTREE在MySQL很少使用,仅支持geometry数据类型,支持该类型的存储引擎只有MyISAM、BDb、InnoDb、NDb、Archive几种。
相对于BTREE,RTREE的优势在于范围查找。
2.HASH
由于HASH的唯一(几乎%的唯一)及类似键值对的形式,很适合作为索引。
HASH索引可以一次定位,不需要像树形索引那样逐层查找,因此具有极高的效率。但是,这种高效是有条件的,即只在“=”和“in”条件下高效,对于范围查询、排序及组合索引仍然效率不高。
3.BTREE
BTREE索引就是一种将索引值按一定的算法,存入一个树形的数据结构中(二叉树),每次查询都是从树的入口root开始,依次遍历node,获取leaf。这是MySQL里默认和最常用的索引类型。
mysql索引实现原理采用B+Tree
MyISAM索引实现
MyISAM引擎使用B+Tree作为索引结构,叶节点的data域存放的是数据记录的地址
辅助索引
在MyISAM中,主索引和辅助索引(Secondarykey)在结构上没有任何区别,只是主索引要求key是唯一的,而辅助索引的key可以重复
InnoDB索引实现
1.第一个重大区别是InnoDB的数据文件本身就是索引文件。从上文知道,MyISAM索引文件和数据文件是分离的,索引文件仅保存数据记录的地址
2.第二个与MyISAM索引的不同是InnoDB的辅助索引data域存储相应记录主键的值而不是地址。换句话说,InnoDB的所有辅助索引都引用主键作为data域。
而在InnoDB中,表数据文件本身就是按B+Tree组织的一个索引结构,这棵树的叶点data域保存了完整的数据记录。这个索引的key是数据表的主键,因此InnoDB表数据文件本身就是主索引。
三、索引种类
普通索引:仅加速查询
唯一索引:加速查询+列值唯一(可以有null)
主键索引:加速查询+列值唯一(不可以有null)+表中只有一个
组合索引:多列值组成一个索引,专门用于组合搜索,其效率大于索引合并
全文索引:对文本的内容进行分词,进行搜索
索引合并,使用多个单列索引组合搜索
覆盖索引,select的数据列只用从索引中就能够取得,不必读取数据行,换句话说查询列要被所建的索引覆盖
建索引的几大原则
一般来说,在WHERE和JOIN中出现的列需要建立索引,但也不完全如此,因为MySQL只对,=,BETWEEN,IN,以及某些时候的LIKE才会使用索引(mysql在使用like查询的时候只有使用后面的%时,才会使用到索引。)
1.最左前缀匹配原则,非常重要的原则,mysql会一直向右匹配直到遇到范围查询(、3andd=4如果建立(a,b,c,d)顺序的索引,d是用不到索引的,如果建立(a,b,d,c)的索引则都可以用到,a,b,d的顺序可以任意调整。
2.=和in可以乱序,比如a=1andb=2andc=3建立(a,b,c)索引可以任意顺序,mysql的查询优化器会帮你优化成索引可以识别的形式。
3.尽量选择区分度高的列作为索引,区分度的公式是count(distinctcol)/count(*),表示字段不重复的比例,比例越大我们扫描的记录数越少,唯一键的区分度是1,而一些状态、性别字段可能在大数据面前区分度就是0,那可能有人会问,这个比例有什么经验值吗?使用场景不同,这个值也很难确定,一般需要join的字段我们都要求是0.1以上,即平均1条扫描10条记录。
4.索引列不能参与计算,保持列“干净”,比如from_unixtime(create_time)=’-05-29’就不能使用到索引,原因很简单,b+树中存的都是数据表中的字段值,但进行检索时,需要把所有元素都应用函数才能比较,显然成本太大。所以语句应该写成create_time=unix_timestamp(’-05-29’)。
5.尽量的扩展索引,不要新建索引。比如表中已经有a的索引,现在要加(a,b)的索引,那么只需要修改原来的索引即可。
1.MySQL数据库作发布系统的存储,一天五万条以上的增量,预计运维三年,怎么优化?
a.设计良好的数据库结构,允许部分数据冗余,尽量避免join查询,提高效率。
b.选择合适的表字段数据类型和存储引擎,适当的添加索引。
c.mysql库主从读写分离。
d.找规律分表,减少单表中的数据量提高查询速度。
e。添加缓存机制,比如memcached,apc等。
f.不经常改动的页面,生成静态页面。
g.书写高效率的SQL。比如SELECT*FROMTABEL改为SELECTfield_1,field_2,field_3FROMTABLE.
2.实践中如何优化MySQL
最好是按照以下顺序优化:
1.SQL语句及索引的优化
2.数据库表结构的优化
3.系统配置的优化
4.硬件的优化
3.优化数据库的方法
选取最实用的字段属性,尽可能减少定义字段宽度,尽量把字段设置NOTNULL,例如’省份’、’性别’最好适用ENUM
使用连接(JOIN)来代替子查询
适用联合(UNION)来代替手动创建的临时表
事务处理
锁定表、优化事务处理
适用外键,优化锁定表
建立索引
优化查询语句
4.如何通俗地理解三个范式?
答:第一范式:1NF是对属性的原子性约束,要求属性具有原子性,不可再分解;
第二范式:2NF是对记录的唯一性约束,要求记录有唯一标识,即实体的唯一性;
第三范式:3NF是对字段冗余性的约束,即任何字段不能由其他字段派生出来,它要求字段没有冗余。。
范式化设计优缺点:
优点:
可以尽量地减少数据冗余,使得更新快,体积小
缺点:对于查询需要多个表进行关联,减少写得效率增加读得效率,更难进行索引优化
反范式化:
优点:可以减少表的关联,可以更好地进行索引优化
缺点:数据冗余以及数据异常,数据的修改需要更多的成本
5.说说对SQL语句优化有哪些方法?(选择几条)
(1)Where子句中:where表之间的连接必须写在其他Where条件之前,那些可以过滤掉最大数量记录的条件必须写在Where子句的末尾.HAVING最后。
(2)用EXISTS替代IN、用NOTEXISTS替代NOTIN。
(3)避免在索引列上使用计算
(4)避免在索引列上使用ISNULL和ISNOTNULL
(5)对查询进行优化,应尽量避免全表扫描,首先应考虑在where及orderby涉及的列上建立索引。
(6)应尽量避免在where子句中对字段进行null值判断,否则将导致引擎放弃使用索引而进行全表扫描
(7)应尽量避免在where子句中对字段进行表达式操作,这将导致引擎放弃使用索引而进行全表扫描
6、锁
表锁:开销小,加锁快;不会出现死锁;锁定力度大,发生锁冲突概率高,并发度最低行锁:
行锁:开销大,加锁慢;会出现死锁;锁定粒度小,发生锁冲突的概率低,并发度高页锁:
MyISAM的表级锁有两种模式:表共享读锁和表独占写锁,在兼容性方面,除了读锁与读锁之间互相兼容之外,其余互不兼容。此外,MyISAM表的读操作与写操作之间,以及写操作之间是串行的。
InnoDB实现了以下类型的行锁,
1)共享锁(S):允许一个事务去读一行,阻止其他事务获得相同数据集的排他锁。
2)排他锁(X):允许获得排他锁的事务更新数据,阻止其他事务取得相同数据集的共享读锁和排他写锁。
另外,为了允许行锁和表锁共存,实现多粒度锁机制,InnoDB还有两种内部使用的意向锁(IntentionLocks),这两种意向锁都是表锁。
1)意向共享锁(IS):事务打算给数据行加行共享锁,事务在给一个数据行加共享锁前必须先取得该表的IS锁。
2)意向排他锁(IX):事务打算给数据行加行排他锁,事务在给一个数据行加排他锁前必须先取得该表的IX锁。