mysql千万级数据优化查询

我们在做一个项目，一个网站或一个app时，用户量巨增，当使用的mysql数据库中的表数据达到千万级时，可以从以下方面考滤优化：

　　1、在设计数据库表的时候就要考虑到优化

　　2、查询sql语句上的优化

　　3、从数据库设计上进行结大框架的设计：如分区、分表、分库

 

 

1、在设计数据库表的时候就要考虑到优化

　　1、尽可能使用not null定义字段，避免null值字段出现，null值会占用额外的索引空间

　　2、使用固定长度的字段类型如char而不是varchar

　　3、添加索引，在查询频繁的字段上加索引，如在where，group by， order by，on中出现的字段加索引

　　4、字符字段只建立前缀索引，字符字段最好不要做主键

 

 

 

2、查询sql语句上的优化

　　1、不要使用select * 查询，将要查找的字段写出来

　　2、使用join来代替子查询

　　3、使用limit 对查询结果的记录进行限定，千万级别的数据太多，后面的数据没必要查出来

　　4、OR改写成IN：因为OR的效率是n级别，IN的效率是log(n)级别

　　5、少使用触发器和函数，可在应用程序代码实现

　　6、也尽量少用join

　　7、尽量避免在where子句中使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描

　　8、列表数据不要拿全表的数据，要使用limit 来分页

 

　　来说说MySql的引擎：

　　　　目前广泛使用的是MyISAM和InnoDB两种引擎：

　　　　　　MyISAM引擎，MySQL5.1之前版本默认引擎，它的特点是：不支持行锁，不支持事务，不支持外键

 

　　　　　　InnoDB引擎，MySQL5.5后默认引擎，它的特点：支持行锁，采用MVCC来支持高并发，支持事务，支持外键，不支持全文索引

 

　　

　　分区：

　　　　　　分区我不太明白，没怎么看懂

 

　　分表：

　　　　分表就是把一张超多数据的表，分成多张表，把一次查询，分成多次查询，然后把结果组合返回给用户。

 

　　　　分表：又有两种方式，分为垂直拆分和水平拆分，通常以某个字段做拆分项，比如以id字段拆分为100张表：表名为tableName_id%100

　　　　分表只适合开发初期做好分表处理，不适合应用上线后再做修改，成本太高。

 

 

　　分库：

　　　　分库：就是把一个数据库分成多个，然后做读写分离。

 

　　有钱的话，使用其他方法，阿里云数据库POLARDB，阿里云OcenanBase （淘宝使用，扛得住双十一），阿里云HybridDB for MySQL (原PetaData)  ，腾讯云数据库：腾讯云DCDB等等

 

 

 

另一种情况：数据量过亿

　

 

使用子查询，优化分页查询：

　　

 

　　如果只是用普通分页查询：select * from t_order limit 0,10 查一页10条数据，几万条数据也就分个百页速度没啥影响，但是如果是几千万条数据，查100万页以后的数据，那就有问题了，用以下子查询来优化：

　　这也就是深度分页问题

select * FROM Product where id >= (select p.id from Product p where p.timeCreated > "2020-09-12 13:34:20" limit 10000000, 1) LIMIT 10;

 

　　也可使用inner join查询优化：

select * from InventorySku isk inner join (select id from InventorySku where inventoryId = 6058 limit 109500,500 ) as d on isk.id = d.id

 

 

海量数据还是要走ES搜索引擎，redis集群缓存等技术问题解决，没有哪家大公司会对着mysql的语句钻牛角尖

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

。。。