MySQL引擎
1、MyISAM：默认表类型，它是基于传统的ISAM类型，ISAM是Indexed Sequential Access Method (有索引的顺序访问方法) 的缩写，它是存储记录和文件的标准方法。不是事务安全的，而且不支持外键，如果执行大量的select，insert MyISAM比较适合。
2、InnoDB：支持事务安全的引擎，支持外键、行锁、事务是他的最大特点。如果有大量的update和insert，建议使用InnoDB，特别是针对多个并发和QPS较高的情况。
主要体现在以下方面 1 若是使用悲观锁方式,对于select for update而言,MyISAM锁整个表,InnoDB锁住指定行,并发时行级锁效率明显要高许多
2 外键的支持性,MyISAM无法使用外键约束,不过在数据库层面较少使用外键约束,而是通过代码来判定
3 MyISAM不支持事务
在开发中可能会用到如下情况:对于千万级数据表拆分时,利用MyISAM的读写效率优势,先设置引擎为MyISAM,进行数据灌入,应用到实际生产时改为InnoDB(eg: ALTER TABLE my_table ENGINE=InnoDB)
索引方式
MySQL的btree索引和hash索引的区别
hash索引:顾名思义就是进行哈希转换,数据结构有点类似于java种的hashMap,用的是横纵关系
btree索引:同样从名字可以看出,是一颗有序的树,从根节点到枝节点的搜索,用的查找算法是“二分查找”,具体算法有兴趣的自己探究
从上面的描述来看可以确定的是: hash索引精确定点查询(比如商品号),btree索引范围定点(比如商品价格),所以在创建索引的时候如果需要用到范围查找用btree,如果是做 等和IN 用hash索引
索引类型
1.添加PRIMARY KEY（主键索引）
表中的一个或多个字段，它的值用于惟一地标识表中的某一条记录
mysql>ALTER TABLE `table_name` ADD PRIMARY KEY ( `column` )
2.添加UNIQUE(唯一索引)
对数据库表中一列或多列的值进行排序的一种结构，只有当经常查询索引列中的数据时，才需要在表上创建索引,在插入数据时检测是否已经存在
mysql>ALTER TABLE `table_name` ADD UNIQUE ( `column` )
3.添加INDEX(普通索引)
对数据库表中一列或多列的值进行排序的一种结构，只有当经常查询索引列中的数据时，才需要在表上创建索引,在插入数据时不检测存在性
mysql>ALTER TABLE `table_name` ADD INDEX index_name ( `column` )
4.添加FULLTEXT(全文索引)
采用的是分词算法,至于算法究竟怎样有兴趣的自己探究
mysql>ALTER TABLE `table_name` ADD FULLTEXT ( `column`)
5.添加多列索引
和普通索引类似,区别在于组合查询,减少索引的扫描次数
mysql>ALTER TABLE `table_name` ADD INDEX index_name ( `column1`, `column2`, `column3` )
详解区别
主键和索引的的区别:主键为该表唯一标识,只有一个且不能为空,可以打个比方有一本书,每一页就是一条记录,书的页码就是这本书的唯一主键;
但是有时候我们也需要其他的本子,通过其他的维度来对这本书快速查找,比如用一本索引本子记录下来
有一种情况:我们要找到课文《鹅鹅鹅》所在页,这时候我们直接去找到相应的页就行了,因为一本书不可能存在两篇一样的课文,不过数据库用的是存储 地址,这就是唯一索引
另外一种情况:我们需要找到描写动物的所有课文,这时候我们在本子上记录 动物-> 1 3 5 6页 植物->2 4 6 动植物同属->6
这就是普通索引
单列索引和多列索引的区别:每个索引都是一份单独的数据,如果有表 Test 存在字段 a, b, c,同时对a,b,c建立索引,我在做如下查找时
select * from Test where a=1 and b=1 and c=1 时,就需要对这3个索引表同时检索,得到共有值,这就是单列索引
如果我们对a b c进行联合索引a_b_c_idx(a,b,c),查询select * from Test where a=1 and b=1 and c=1 时
能确定一串内存地址,也就是说只有一次索引的检索,对于索引检索效率来说提高了很多;
这里需要引入一个最左概念:a_b_c_idx(a,b,c) 创建的时候相当于创建了联合索引 a_b_c_idx(a,b,c),
a_b_idx(a,b) a_idx(a),在这种情况如果查询 select * from Test where c=1
或者 select * from Test where b=1 and c=1 则联合索引失效,所以在创建联合索引时顺序非常重要
MySQL索引失效常见方式
1 索引列的值不能为null, 查询条件不能为null
a.单列索引无法储null值，复合索引无法储全为null的值,NULL值进入索引时，无法确定其应该放在哪里
b.查询时，采用is null条件时，不能利用到索引，只能全表扫描。
2.不适合键值较少的列,如：性别；原因是 不建立索引需要的时间=t1扫描整个表 建立索引需要的时间=t2去索引中取+t3取相应的数据条件 如果t1和t2+t3差不多
或者甚至更大则没有太大意义
3.模糊查询不能使用索引,索引以树和哈希表的形式存在,对于key来说是确定值,如果使用模糊查询则需要对整个索引表进行检索,问题又回到了上面2同样的问题
4.条件中不能带or,则索引失效 假定字段a有索引，a无索引, b肯定会全表检索,那么检索的同时a也会一起检索;
注:举个例子,如果a全表检索b索引检索那么需要的复杂度=t1全表+t2索引检索+t3索引取数据,而如果是a和b同时同时检索那么需要复杂度=t1全表