处理上百万条的数据库如何提高处理查询速度_MySQL

1、优化查询时，应尽量避免全表扫描。
首先应该考虑对where和orderby涉及到的列创建索引。
2、尽量避免在where子句中判断字段的null值，否则引擎会放弃使用索引而进行全表扫描。
例如：selectidfromtwherenumisnull可以给num设置默认值0，以保证表中的num列不存在空值，所以这样查询：selectidfromtwherenum=03尽量避免在where子句中使用!=或运算符，否则引擎将放弃使用索引并执行全表扫描。
。
4、尽量避免在where子句中使用or来连接条件，否则引擎会放弃使用索引而进行全表扫描。
例如：selectidfromtwherenum=10ornum=20可以这样查询：selectidfromtwherenum=10unionallselectidfromtwherenum=205。
另外，in和notin要谨慎使用，否则会导致全表扫描，例如：selectidfromtwherenumin(1,2,3)对于连续值，如果可以使用between，就不要使用in：selectidfromtwherenumBetween1and36另外以下查询将导致全表扫描：selectidfromtwherenamelike'%abc%。
'为了提高效率，可以考虑全文检索。
7、如果where子句中使用了参数，也会进行全表扫描。
由于SQL仅在运行时解析局部变量，因此优化器不能将访问计划的选择推迟到运行时，而必须在编译时进行选择；但是，如果在编译时创建访问计划，则变量的值仍然未知，并且不能用作索引选择的输入。
例如，以下语句将执行全表扫描：selectidfromtwherenum=@num可以修改为强制查询使用索引：selectidfromtwith(index(indexname))wherenum=@num8应尽量避免对表达式进行操作在where子句中的字段上。
这将导致引擎放弃使用索引并执行全表扫描。
例如：selectidfromtwherenum/2=100应改为：selectidfromtwherenum=100*29应尽量避免对where子句中的字段执行函数操作，这将导致其放弃使用索引并运行一个完整的表。
扫描。
例如：selectidfromtwheresubstring(name,1,3)='abc'--以abc开头的名称生成的IDselectidfromtwheredatediff(day,createdate,'2005-11-30')=0--'2005-11-30'应改为：selectidfromtwherenamelike'abc%'selectidfromtwherecreatedate>='2005-11-30'andcreatedate10不要在where子句中“=”左侧进行函数、算术运算或其他表达式操作，否则系统可能无法使用他们。
索引正确。
11、使用索引字段作为条件时，如果索引是复合索引，则必须使用索引中的第一个字段作为条件，以保证系统使用该索引。
否则，不会使用索引，应尽可能使字段的顺序与索引的顺序一致。
12.不要写无意义的查询。
比如需要生成一个空表结构：selectcol1,col2into#tfromtwhere1=0这类代码不会返回任何结果集，但是会消耗系统资源这样：createtable#t(...)13.很多时候它是使用exists代替in是一个不错的选择：selectnumfromawherenumin(selectnumfromb)替换以下语句：selectnumfromawhereexists(select1frombwherenum=a.num)＼14。
并不是所有的索引都对查询有效，当索引列中存在大量重复数据时，SQL会根据表中的数据来优化查询。
SQL查询可能无效。
将使用索引。
例如，如果表中有一个性别字段，并且几乎一半是男性，一半是女性，那么即使索引是基于性别的，它也不会有任何索引。
对查询效率的影响。
15、索引越多越好索引虽然可以提高匹配选择的效率，但是同时也降低了插入和更新的效率，因为在插入或更新的过程中索引可能会被重建，那么就需要创建索引。
必须仔细考虑。
这取决于具体情况。
一个表上的索引最好不要超过6个。
如果太多，就应该考虑是否需要对一些不常用的列建立索引。
16.应尽可能避免更新聚集索引数据列，因为聚集索引数据列的顺序就是表记录的物理存储顺序。
一旦列值改变，表中所有记录的顺序都会改变，这将消耗大量资源。
如果应用系统需要频繁更新聚集索引数据，则需要考虑是否将该索引创建为聚集索引。
17.尝试使用数字字段。
如果您的字段仅包含数字信息，请尽量不要将它们设计为字符字段。
这将降低查询和连接性能并增加存储开销。
这是因为引擎在处理查询和连接时会逐一比较字符串中的每个字符，而对于数字类型只需比较一次就足够了。
18、尽量使用varchar/nvarchar代替char/nchar，因为首先变长字段的存储空间较小，可以节省存储空间。
其次，对于查询来说，在相对较小的领域内搜索效率明显更高。
19.不要在任何地方使用select*fromt，用特定的字段列表替换“*”，并且不要返回任何未使用的字段。
20.尝试使用表变量而不是临时表。
如果表变量包含大量数据，请记住索引非常有限（仅限主键索引）。
21、避免频繁创建和删除临时表，以减少系统表资源的消耗。
22.临时表并非不可用，适当使用它们可以使某些例程更加高效，例如，当您需要重复引用大表或常用表中的数据集时。
但是，对于一次性事件，最好使用导出表。
23、新建临时表时，如果一次插入的数据量较大，可以使用selectinto代替createtable，避免造成大量日志，数据量不大时提高速度；要释放系统表的资源，必须先使用createtable，然后再insert。
24、如果使用临时表，必须在存储过程结束时显式删除所有临时表，先truncatetable，再droptable，以免长期阻塞系统表。
25、尽量避免使用游标，因为游标效率较低如果游标处理的数据超过10000行，就应该考虑重写它。
26.在使用基于游标的方法或临时表方法之前，应该首先寻找基于集合的解决方案来解决问题。
基于集合的方法通常更有效。
27.与临时表一样，游标也不是不可用。
对小数据集使用FAST_FORWARD游标通常优于其他逐行处理方法，特别是当您需要引用多个表来获取所需数据时。
在结果集中包含“总计”的例程通常比使用游标更快。
如果开发时间允许，您可以尝试基于游标的方法和基于集合的方法，看看哪种方法效果最好。
28.在所有存储过程和触发器的开头设置SETNOCOUNTON，在结尾设置SETNOCOUNTOFF。
每个存储过程和触发器语句执行后无需向客户端发送DONE_IN_PROC消息。
29、尽量避免大事务操作，提高系统的并发能力。
30.尽量避免向客户返回大量数据。
如果数据量太大，则需要考虑相应的要求是否合理。

MySQL百万级数据量分页查询方法及其优化建议

当数据表超过100万条记录时，使用offset+constraint的方式进行分页查询，性能会很差。
主要原因是offsetlimit分页方式是从头开始查询，然后丢弃第一个offset记录，所以offsetlimit越大，查询速度越慢。
例如：读取第10000行到第10019行的元素（pk为主键/唯一键，使用orderbyid查询时使用主键的索引）。
但是如果id是uuid的话这个方法就会出现问题。
这个问题可以通过使用：条件查询：如果在分页查询中加入像type='a'这样的条件，sql就变成：这样的话，由于type没有使用索引，所以查询速度也会变慢向下。
向下。
。
但仅仅添加一个类型作为索引查询仍然很慢，因为请求的数据量太大。
现在，考虑添加组合索引。
组合索引的顺序应该是条件字段在前，id在后，如(type,id)，因为组合索引查询中使用的是type索引，还有type和id。
与综合指数挂钩。
如果只选择ID，则可以直接根据组合索引返回ID，无需再运行其他查询返回ID。
使用uuid作为主键不仅会导致性能问题，而且还会导致性能问题。
请求过程中也出现问题。
因为使用selectidfromtablelimit10000,10查询ID数据时，默认对ID进行排序，并返回排序后的ID结果。
如果我们想按照插入顺序查询结果，结果将不能满足我们的需求。
聚集索引和非聚集索引：聚集索引类似于新华拼音词典。
根据拼音检索信息是连续的，可以快速获取前后信息。
非聚集索引类似于激进查询，信息不能存储在单个区域中。
考虑对经常按范围查询的字段使用聚集索引。
InnoDB中的索引分为聚集索引（有主键的索引）和非聚集索引（没有主键的索引）。
聚集索引的叶节点存储整行记录，而非聚集索引的叶节点存储整行记录。
保存整行记录。
行条目的主键值。
如果您的表定义了主键，则主键索引就是聚集索引。
如果没有为表定义主键，MySQL将采用第一个唯一索引（unique）且仅包含非空列（NOTNULL）作为主键，而InnoDB将其用作聚集索引。
如果没有这样的列，InnoDB将自己生成这样的标识符值。
count(*)最好选择indexkey_len较小的索引。
避免使用聚集索引。
count(*)和count(constant)，如果有非聚集索引，mysql会自动选择非聚集索引，因为非聚集索引占用的空间很小。
如果没有非聚集索引，则有聚集索引。
将使用该索引。
count(primarykey)主键id是聚集索引，使用聚集索引。
如果存在“where”子句，则将根据“where”子句确定索引的使用情况。