千万级数据库如何提高查询效率
1、数据库设计方面
- 对尽量避免全表扫描，首先应考虑在where及order by 设计的列上建立索引。
- 尽量避免在where字句中对字段进行null值判断，否则将导致引擎放弃使用索引而进行全表扫描。
- 当索引列由大量数据重复时，查询可能不会利用索引，比如【性别】这种列建了索引也对查询效率起不了作用。
- 索引不是越多越好，索引可以提高响应的select效率，但是同时也降低了insert和update的效率，因为insert或者update时有可能会重建索引。所以怎样建立索引需要慎重考虑，视具体情况而定。一个表的索引数最好不要超过6个。
- 尽可能避免更新索引数据列，因为索引数据列的顺序就是表记录的物理存储顺序，一旦该列值改变将导致整个表记录的顺序的调整，会耗费相当大的资源，如果一个索引数据列需要被频繁更新，应该考虑是否将该索引建为索引。
- 尽量使用数字型字段，若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，并会增加存储开销。因为引擎在处理查询和连接时会逐个比较字符串中每一个字符，而对于数字型而言只需要比较一次就够了。
- 尽可能使用varchar、nvarchar代替char/nchar，因为变长字段存储空间小，可以节省存储空间，而且查询中，一个相对较小的字段内搜索效率要高些。
- 尽量使用表变量来代替临时表，如果表变量包含大量数据，注意索引只有主键索引。
- 避免频繁创建和删除临时表，以减少系统表资源的消耗。
- 在新建临时表时，如果一次性插入数据量很大，那么可以使用select into 代替 crate table，避免造成大量log，以提高速度；如果数据量不大，为了缓和系统表的资源，应先create table 然后insert。
- 如果使用到了临时表，在存储过程的最后务必将所有的临时表显式删除，先truncate table 再 drop table ，这样可以避免系统表较长时间锁定。
2、SQL语句方面
- 尽量避免在where子句中使用!=、<>操作符和or作为连接条件，否则将会被引擎弃用索引进行全表扫描。
- 对于连续的数值，能用between就不要用in，in和not in会导致全表扫描。
- 类似的会让引擎进行全表扫描的还有很多，详看https://www.cnblogs.com/smallzhen/p/12547384.html。
- 如果在where字句中使用参数，也会导致全表扫描。因为SQL只有在运行时才会解析局部变量，它必须在编译时才进行选择，编译时建立访问计划，变量的值是未知的，因而无法作为索引选择输入项。如：select id from t where num=@num 可改为强制查询使用索引select id from t with(index(索引名)) where num=@num
- 很多时候用 exists 代替 in 是一个好的选择：select num from a where num in(select num from b) 改成select num from a where exists(select 1 from b where num=a.num)
- 不要使用select * ，用具体字段列表代替 “*”，不要返回用不到的字段。
- 尽量避免使用游标，因为游标效率较差。
- 避免向客户端返回大数据量，数据量过大，应该考虑相应需求是否合理。
- 尽量避免大事务操作，提高系统并发能力。
- 尽量使用limit。数据库引擎会在找到limit的数据量后停止搜索，而不是继续往后查下一条符合记录的数据。
- 不要order by rand()。
- 能使用enum类型就不要varchar，enum类型非常快和紧凑，实际上保存的是tinyint，但外表显示为字符串，如果有些字段的取值还有限且为固定的，那么应该使用enum而不是varchar。
- 把IP地址存成unsigned int，用整型的ip而不是varchar（15）存放字符串形式的ip会为你带来查询上的优势。
