Mysql索引学习笔记

什么是索引？

一个索引是存储的表中一个特定列的值数据结构（最常见的是B+Tree）。索引是在表的列上创建。所以，要记住的关键点是索引包含一个表中列的值，并且这些值存储在一个数据结构中。
什么样的数据结构可以作为索引？
B+Tree是最常见的用于索引的数据结构。因为他们是时间复杂度低，查找、删除、插入操作都可以在短时间内完成。另外一个重要原因存储在B+Tree中的数据是有序的。

为什么索引能够提高查询速度？

索引就是通过事先排好序，从而在查找时可以应用二分查找等高效率的算法。
一般的顺序查找，复杂度为O(n),而二分查找复杂度为O(log2n).当n很大时，二者的效率相差极其悬殊。

Mysql中的MyISAM和InnoDB的区别由哪些？

1.InnoDB支持事务，MyISAM不支持事务。这是MySQL将默认存储引擎从MyISAM变成InnoDB的重要原因之一。

2.InnoDB支持外键，而MyISAM不支持。对一个包含外键的InnoDB表转为MyISAM会失败。

3.InnoDB是聚集索引，MYISAM是非聚集索引。聚集索引的文件存放在主键索引的叶子节点上，因此InnoDB必须要有主键，通过主键索引效率很高。但是辅助索引需要两次查询，先查询到主键，然后再通过主键查询到数据，因此，主键不应该过大，因为主键太大，其他索引也都会很大，而MyISAM是非聚集索引，数据文件是分离的，索引保存的是数据文件的指针，主键索引和辅助索引是独立的。

4.InnoDB不保存表的具体行数，执行select couunt(*) from table 时需要全表扫描。而MyISAM用一个变量保存了整个表的行数，执行上述语句只需要读出该变量即可，速度很快。

5.InnoDB最小的锁粒度是行锁。MyISAM最小的锁粒度是表锁。一个更新语句会锁住整个张表，导致其他查询和更新都会被阻塞，因此并发访问受限。这也是MySQL将默认存储引擎从MyISAM变成InnoDB的重要原因之一；

如何选择：

1.是否要支持事务，如果要请选择InnoDB,如果不需要可以考虑MyISAM;

2.如果表中绝大多数都只是读查询，可以考虑MyISAM，如果既有读，写也挺频繁，请使用InnoDB.

3.系统崩溃后，MyISAM恢复起来更困难，能否接受，不能接受就选InnoDB.

4.MySQL5.5版本开始InnoDB已经成为Mysql的默认引擎（之前是MyISAM）,说明其优势是有目共睹的，如果你不知道用什么存储引擎，那就用InnoDB，至少不会差。

数据库查询数据方式

1. 普通查询数据方式：从数据表第一条位置开始查询,进行逐条查询,直至查找到最后一条,将所有符合的数据找到.

2. 索引查询数据方式：先从索引表中查询，定位到大致的区间，再查真实的表

创建索引语法

创建索引

单列索引是基于单个列所建立的索引:

create index 索引名 on 表名(列名)

查看表中的索引

SHOW INDEX FROM 表名

删除索引

DROP INDEX 索引名 ON 表名

代码演示

 显示指定表中所有的索引
SHOW INDEX FROM emp;

-- 用名字创建索引值
CREATE INDEX index_name ON emp(ename);

-- 删除上面创建的索引
DROP INDEX index_name ON emp;

-- 创建名字和工资的复合索引（了解）
CREATE INDEX index_ename_salary ON emp(ename,salary);

-- 删除复合索引()
DROP INDEX index_ename_salary ON emp;

索引的使用原则

1. 在数据量大的表上建立索引才有意义==(最起码十万级别数据才会考虑索引)==

2. 在经常查询的字段上使用索引，用在 where子句后面或者是连接条件上的字段建立索引

3. 表中数据修改频率高时，不建议建立索引（每插入、或者修改一条记录mysql都要维护索引的顺序,这个过程不需要你们参与，但是非常消耗性能）

索引的不足

上面都在说使用索引的好处，但过多的使用索引将会造成滥用。因此索引也会有它的缺点：虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件。

建立索引会占用磁盘空间的索引文件。

索引失效的几种情况

1. 如果条件中有or，即使其中有条件带索引也不会使用。(例外：or两边条件都是同一个字段 )

2. 模糊like查询已 '%xxx’开头的不使用索引，以’xxx%'结尾会使用索引。

3. where语句中使用不等于 <>和 != 不使用索引

4. 如果mysql估计使用全表扫描要比使用索引快，则不使用索引。

日期函数(重点)

函数	说明	案例
addDate(d,n)	计算起始日期d加上n天的日期	SELECT ADDDATE(“2017-06-15”, 10);
curDate()	返回当前日期	SELECT CURDATE();
dateDiff(d1,d2)	计算日期 d1->d2 之间相隔的天数	SELECT DATEDIFF(‘2001-01-01’,‘2001-02-02’)
now()	返回当前日期和时间	SELECT NOW()
year(日期)	获取指定日期的年份	SELECT YEAR(NOW())

示例代码

-- 统计每个员工入职的天数
SELECT e.ename,  DATEDIFF(CURDATE() ,e.joindate)  FROM emp e ; 

-- 统计每个员工的工龄 
SELECT e.ename,  ROUND(DATEDIFF(CURDATE(),e.joindate)/365,0)  FROM emp e ; 


-- 查询2001年入职的员工

SELECT  *  FROM emp e WHERE YEAR(e.`joindate`)=2001; 



-- 统计入职19年以上的员工有多少个
SELECT  *  FROM emp e WHERE DATEDIFF(CURDATE(),e.joindate)/365 >19;