Mysql千万数据索引查询优化

Mysql千万数据索引查询优化

1.索引的作用

索引就是想是书的目录，能够帮助快速查询。如果数据库没有索引的话，在查询的时候，就会进行全表扫描，在数据量大的情况下(几百万，上千万)的时候，查询会特别慢需要几秒，这个在线上系统是无法满足应用要求的。加上索引后查询只要几十毫秒，百倍的优化。

2. 索引的类型

1. 聚簇索引(主键索引): 叶子节点即是数据节点，索引的顺序就是物理存储的顺序；

2. 普通索引(二级索引)：创建在非主键上的索引，二级的意思是说：需要两次才能拿到数据，二级索引的叶子节点只是保存了数据存放的指针，然后再根据地质获取数据；

3. 组合(联合)索引：用于多字段组合查询的情况，注意需要按照索引顺序来才能利用索引，满足最左侧查询需求，例如(a,b,c)组合索引，则

(a) ，(a,b),(a,b,c)都可以利用索引加速查询。  最左侧查询并不是指的写sql语句的顺序（很多朋友会存在误解），sql在会进行查询优化的。尽量避免中间索引的查询条件过大，比如 >1 之类的，sql执行type会变成range，

​​​​​

4. 唯一约束：可以在数据库系统上保证数据上唯一约束，如果在应用层做查询再判读，在多并发情况容易出现一些脏数据 。​

加索引：  alert table *** add index(索引列);

删除索引： drop index 索引名  on table;

查看索引： show index 索引名 from table;

查看表结构： describe table 表名;

3. 查询速度的优化可以分为两块：

​1）硬件 ： 替换更大的内存、CPU、硬盘大小等

​2）软件 ：

​    ​1.最简单实用的是加索引；

    ​​2.慢查询sql优化；

​    ​3.借用缓存+更新消息失效的方式来减少db的访问量和速度，这里需要考虑缓存预热和命中率，不然瞬间的流量高峰就会把应用和数据库击穿，冲垮；

    4. ​分库分表降低单库单表的容量，提高查询速度。​

​​

1. 注意线上系统的加索引，特别是数据量大的情况，mysql5.6版本之前数据库会锁表(waiting for metadata lock)，会很慢，导致线上数据表暂时不可用情况，花费时间较长。这种大表的表结构变更一定要在流量小的情况下操作，避免影响线上业务。

Historically, many DDL operations on InnoDB tables were expensive. Many ALTER TABLE operations worked by creating a new, empty table defined with the requested table options and indexes, then copying the existing rows to the new table one-by-one, updating the indexes as the rows were inserted. After all rows from the original table were copied, the old table was dropped and the copy was renamed with the name of the original table.

5.6 引入了onlineDDL，另外在一个事物中，修改表结构或者添加索引会锁表。

4. sql执行解释

 我们可以通过简单命令来 explain sql ;  来看一条sql语句的执行过程是怎么样的，有没有用索引和需要检查的行数等。

​​